SET NAMES utf8mb4;
START TRANSACTION;

CREATE TABLE finance_ledger_entries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 meeting_id BIGINT UNSIGNED NOT NULL, booking_id BIGINT UNSIGNED NOT NULL,
 entry_type ENUM('charge','adjustment','exemption','payment','refund','reversal') NOT NULL,
 amount DECIMAL(12,2) NOT NULL, balance_delta DECIMAL(12,2) NOT NULL,
 original_entry_id BIGINT UNSIGNED NULL, effective_at DATETIME NOT NULL,
 method VARCHAR(50) NULL, safe_reference VARCHAR(100) NULL, finance_note_cipher LONGTEXT NULL,
 source ENUM('legacy','officer','reconciliation') NOT NULL DEFAULT 'officer', created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_finance_reversal(original_entry_id), KEY idx_finance_booking(booking_id,id),
 KEY idx_finance_event(meeting_id,effective_at,id), KEY idx_finance_unit(unit_id,effective_at,id),
 FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id),
 FOREIGN KEY(meeting_id) REFERENCES meetings(id), FOREIGN KEY(booking_id) REFERENCES bookings(id),
 FOREIGN KEY(original_entry_id) REFERENCES finance_ledger_entries(id), FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 CHECK(amount>0), CHECK((entry_type IN('charge','refund') AND balance_delta>0) OR (entry_type IN('exemption','payment') AND balance_delta<0) OR entry_type IN('adjustment','reversal'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_allocations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, payment_entry_id BIGINT UNSIGNED NOT NULL,
 charge_entry_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_finance_allocation(payment_entry_id,charge_entry_id), KEY idx_finance_charge_allocation(charge_entry_id),
 FOREIGN KEY(payment_entry_id) REFERENCES finance_ledger_entries(id), FOREIGN KEY(charge_entry_id) REFERENCES finance_ledger_entries(id),
 CHECK(amount>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_bank_accounts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_key VARCHAR(80) NOT NULL,
 scope_type ENUM('unit','province') NOT NULL, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 issuer_name VARCHAR(180) NOT NULL, account_name_cipher LONGTEXT NOT NULL,
 sort_code_cipher LONGTEXT NOT NULL, account_number_cipher LONGTEXT NOT NULL,
 sort_code_mask VARCHAR(30) NOT NULL, account_number_mask VARCHAR(50) NOT NULL,
 updated_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_finance_bank_scope(scope_key), FOREIGN KEY(province_id) REFERENCES provinces(id),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_bank_account_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bank_account_id BIGINT UNSIGNED NOT NULL,
 scope_key VARCHAR(80) NOT NULL, old_sort_mask VARCHAR(30) NULL, old_account_mask VARCHAR(50) NULL,
 new_sort_mask VARCHAR(30) NOT NULL, new_account_mask VARCHAR(50) NOT NULL,
 changed_by BIGINT UNSIGNED NOT NULL, changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(bank_account_id) REFERENCES finance_bank_accounts(id), FOREIGN KEY(changed_by) REFERENCES users(id),
 KEY idx_bank_history(bank_account_id,changed_at,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO finance_ledger_entries(province_id,unit_id,meeting_id,booking_id,entry_type,amount,balance_delta,effective_at,source)
SELECT u.province_id,m.unit_id,b.meeting_id,b.id,'charge',b.total_charge,b.total_charge,b.created_at,'legacy'
FROM bookings b JOIN meetings m ON m.id=b.meeting_id JOIN units u ON u.id=m.unit_id WHERE b.total_charge>0;
INSERT INTO finance_ledger_entries(province_id,unit_id,meeting_id,booking_id,entry_type,amount,balance_delta,effective_at,method,safe_reference,source)
SELECT u.province_id,m.unit_id,b.meeting_id,b.id,'payment',b.amount_received,-b.amount_received,COALESCE(b.paid_at,b.updated_at),b.payment_method,b.payment_reference,'legacy'
FROM bookings b JOIN meetings m ON m.id=b.meeting_id JOIN units u ON u.id=m.unit_id WHERE b.amount_received>0;
INSERT INTO finance_ledger_entries(province_id,unit_id,meeting_id,booking_id,entry_type,amount,balance_delta,effective_at,source)
SELECT u.province_id,m.unit_id,b.meeting_id,b.id,'refund',b.refund_amount,b.refund_amount,COALESCE(b.refunded_at,b.updated_at),'legacy'
FROM bookings b JOIN meetings m ON m.id=b.meeting_id JOIN units u ON u.id=m.unit_id WHERE b.refund_amount>0;
INSERT INTO finance_ledger_entries(province_id,unit_id,meeting_id,booking_id,entry_type,amount,balance_delta,effective_at,source)
SELECT u.province_id,m.unit_id,b.meeting_id,b.id,'exemption',b.total_charge,-b.total_charge,b.updated_at,'legacy'
FROM bookings b JOIN meetings m ON m.id=b.meeting_id JOIN units u ON u.id=m.unit_id WHERE b.payment_status='exempt' AND b.total_charge>0;

CREATE TRIGGER finance_entry_no_update BEFORE UPDATE ON finance_ledger_entries FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Finance ledger entries are immutable';
CREATE TRIGGER finance_entry_no_delete BEFORE DELETE ON finance_ledger_entries FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Finance ledger entries are immutable';
CREATE TRIGGER finance_allocation_no_update BEFORE UPDATE ON finance_allocations FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Finance allocations are immutable';
CREATE TRIGGER finance_allocation_no_delete BEFORE DELETE ON finance_allocations FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Finance allocations are immutable';

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609280038','Immutable finance ledger and protected bank accounts',SHA2('202609280038_finance_ledger_v1',256),0);
COMMIT;