-- FALLBACK MANUAL v4.34.1
-- Jalankan hanya jika upgrade_v4_34_1.php tidak dapat dijalankan.
-- Kompatibel dengan MySQL/MariaDB shared hosting tanpa CREATE DATABASE / USE database.

SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='barber_staff' AND COLUMN_NAME='bank_name')=0,'ALTER TABLE barber_staff ADD COLUMN bank_name VARCHAR(120) NULL','SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='barber_staff' AND COLUMN_NAME='bank_code')=0,'ALTER TABLE barber_staff ADD COLUMN bank_code VARCHAR(20) NULL','SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='barber_staff' AND COLUMN_NAME='bank_account_no')=0,'ALTER TABLE barber_staff ADD COLUMN bank_account_no VARCHAR(50) NULL','SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='barber_staff' AND COLUMN_NAME='bank_account_name')=0,'ALTER TABLE barber_staff ADD COLUMN bank_account_name VARCHAR(150) NULL','SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='barber_staff' AND COLUMN_NAME='kopra_transfer_method')=0,"ALTER TABLE barber_staff ADD COLUMN kopra_transfer_method VARCHAR(20) NOT NULL DEFAULT 'BI FAST'",'SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;

SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='employee_staff' AND COLUMN_NAME='bank_name')=0,'ALTER TABLE employee_staff ADD COLUMN bank_name VARCHAR(120) NULL','SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='employee_staff' AND COLUMN_NAME='bank_code')=0,'ALTER TABLE employee_staff ADD COLUMN bank_code VARCHAR(20) NULL','SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='employee_staff' AND COLUMN_NAME='bank_account_no')=0,'ALTER TABLE employee_staff ADD COLUMN bank_account_no VARCHAR(50) NULL','SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='employee_staff' AND COLUMN_NAME='bank_account_name')=0,'ALTER TABLE employee_staff ADD COLUMN bank_account_name VARCHAR(150) NULL','SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='employee_staff' AND COLUMN_NAME='kopra_transfer_method')=0,"ALTER TABLE employee_staff ADD COLUMN kopra_transfer_method VARCHAR(20) NOT NULL DEFAULT 'BI FAST'",'SELECT 1'); PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;

CREATE TABLE IF NOT EXISTS kopra_transfer_batches(
 id BIGINT AUTO_INCREMENT PRIMARY KEY,batch_no VARCHAR(60) NOT NULL UNIQUE,batch_name VARCHAR(180) NOT NULL,
 payroll_period_id BIGINT NULL,debit_account_no VARCHAR(35) NOT NULL,transfer_date DATE NOT NULL,purpose_code VARCHAR(4) NOT NULL DEFAULT '1',
 beneficiary_type VARCHAR(3) NOT NULL DEFAULT '1',beneficiary_resident CHAR(1) NOT NULL DEFAULT 'Y',beneficiary_citizen CHAR(1) NOT NULL DEFAULT 'Y',
 status ENUM('draft','exported','paid','cancelled') NOT NULL DEFAULT 'draft',total_items INT NOT NULL DEFAULT 0,total_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
 exported_at DATETIME NULL,paid_at DATETIME NULL,created_by INT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(payroll_period_id) REFERENCES payroll_periods(id) ON DELETE SET NULL,FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_kopra_batch_status(status,transfer_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS kopra_transfer_items(
 id BIGINT AUTO_INCREMENT PRIMARY KEY,batch_id BIGINT NOT NULL,payroll_item_id BIGINT NULL,staff_type ENUM('barber','employee','manual') NOT NULL DEFAULT 'manual',staff_id INT NULL,
 destination_bank_name VARCHAR(120) NOT NULL,destination_bank_code VARCHAR(20) NULL,destination_account_no VARCHAR(50) NOT NULL,destination_account_name VARCHAR(150) NOT NULL,
 destination_address VARCHAR(105) NULL,transfer_method ENUM('In-House','BI FAST','Online','SKN','RTGS') NOT NULL DEFAULT 'BI FAST',amount DECIMAL(18,2) NOT NULL,
 transaction_reference VARCHAR(19) NULL,remark VARCHAR(200) NULL,status ENUM('pending','exported','paid','cancelled') NOT NULL DEFAULT 'pending',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(batch_id) REFERENCES kopra_transfer_batches(id) ON DELETE CASCADE,FOREIGN KEY(payroll_item_id) REFERENCES payroll_items(id) ON DELETE SET NULL,
 UNIQUE KEY uq_kopra_batch_payroll(batch_id,payroll_item_id),INDEX idx_kopra_item_batch_status(batch_id,status),INDEX idx_kopra_item_payroll(payroll_item_id)
) ENGINE=InnoDB;

INSERT INTO settings(setting_key,setting_value) VALUES
 ('kopra_debit_account_no',''),('kopra_default_transfer_method','BI FAST'),('kopra_default_purpose_code','1'),
 ('kopra_default_beneficiary_type','1'),('kopra_beneficiary_resident','Y'),('kopra_beneficiary_citizen','Y'),('schema_release','4.34.1')
ON DUPLICATE KEY UPDATE setting_value=IF(setting_key='schema_release','4.34.1',setting_value);