ALTER TABLE `tblinvoicepaymentrecords` ADD `post_data` TEXT DEFAULT NULL;
ALTER TABLE `tblinvoicepaymentrecords` ADD `retry_count` TINYINT(4) DEFAULT ‘0’;
ALTER TABLE `tblinvoicepaymentrecords` ADD `status` TINYINT(4) DEFAULT ‘1’;
ALTER TABLE `tblinvoicepaymentrecords` ADD `transaction_headerid` VARCHAR(4) DEFAULT NULL;



ALTER TABLE `tblinvoicepaymentrecords` CHANGE `amount` `amount` DECIMAL(15,2) NOT NULL COMMENT 'Total Amount (Including tip_amount + transaction_fee_amount)';
ALTER TABLE `tblinvoicepaymentrecords` ADD `base_amount` DECIMAL(15,2) NULL DEFAULT '0' AFTER `amount`;
ALTER TABLE `tblinvoicepaymentrecords` ADD `tip_amount` DECIMAL(15,2) NULL DEFAULT '0' AFTER `base_amount`;
ALTER TABLE `tblinvoicepaymentrecords` ADD `pass_processing_fee_to_client` TINYINT NULL DEFAULT '0' AFTER `tip_amount`;
ALTER TABLE `tblinvoicepaymentrecords` ADD `transaction_fee_amount` DECIMAL(15,2) NULL DEFAULT '0' AFTER `pass_processing_fee_to_client`;

ALTER TABLE `tblinvoices` ADD `wepay_pass_processing_fee_to_client` TINYINT NULL DEFAULT '0' AFTER `total`;

INSERT INTO tbloptions_static (name, value, autoload) VALUES ('wepay_pass_processing_fee_to_client', '0', '0');
INSERT INTO tbloptions (tenant_id, name, value, autoload) SELECT id, 'wepay_pass_processing_fee_to_client', '0', '0' FROM tbltenants;



INSERT INTO `tblplan_features` (`id`, `name`, `codename`, `description`, `type`, `added_on`) VALUES (NULL, 'WePay (Pass processing fee to client)', 'wepay_pass_processing_fee_to_client', 'WePay (Pass processing fee to client)', 'allow', CURRENT_TIMESTAMP);

INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '9', '<tblplan_features_id>', '0', NULL, CURRENT_TIMESTAMP);
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '10', '<tblplan_features_id>', '1', NULL, CURRENT_TIMESTAMP);
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '11', '<tblplan_features_id>', '1', NULL, CURRENT_TIMESTAMP);
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '12', '<tblplan_features_id>', '1', NULL, CURRENT_TIMESTAMP);



INSERT INTO `tblplan_features` (`id`, `name`, `codename`, `description`, `type`, `added_on`) VALUES (NULL, 'WePay (Transaction Fees)', 'wepay_transaction_fees', 'WePay (Transaction Fees)', 'custom', CURRENT_TIMESTAMP);

INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '9', '<tblplan_features_id>', '{"cc_percent": "2.9","cc_flat": "30","ach_percent": "1","ach_flat": "0"}', NULL, CURRENT_TIMESTAMP);
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '10', '<tblplan_features_id>', '{"cc_percent": "2.9","cc_flat": "30","ach_percent": "1","ach_flat": "0"}', NULL, CURRENT_TIMESTAMP);
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '11', '<tblplan_features_id>', '{"cc_percent": "2.9","cc_flat": "30","ach_percent": "1","ach_flat": "0"}', NULL, CURRENT_TIMESTAMP);
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '12', '<tblplan_features_id>', '{"cc_percent": "2.9","cc_flat": "30","ach_percent": "1","ach_flat": "0"}', NULL, CURRENT_TIMESTAMP);



CREATE TABLE tblinvoice_payment_refunds(
    id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tbltenants (id) ON DELETE CASCADE ON UPDATE CASCADE,
    payment_record_id BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (payment_record_id) REFERENCES tblinvoicepaymentrecords (id) ON DELETE CASCADE ON UPDATE CASCADE,
    amount DECIMAL(15,2) NOT NULL,
    transaction_id VARCHAR(500) NULL DEFAULT NULL,
    mode ENUM('wepay', 'manual') NULL DEFAULT NULL,
    status ENUM('pending', 'processed', 'failed') DEFAULT 'pending',
    status_comment VARCHAR(500) NULL DEFAULT NULL,
    refunded_by BIGINT NOT NULL,
    added_on DATETIME NOT NULL,
    updated_on DATETIME NOT NULL
);