-- Financial segregation, approvals and period close. Ledger rows remain authoritative and immutable.
CREATE TABLE IF NOT EXISTS finance_approval_policies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 action_key VARCHAR(40) NOT NULL,
 threshold_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 require_separate_approver TINYINT(1) NOT NULL DEFAULT 1,
 active TINYINT(1) NOT NULL DEFAULT 1,
 updated_by BIGINT UNSIGNED NOT NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_finance_approval_policy(unit_id,action_key),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(updated_by) REFERENCES users(id), CHECK(threshold_amount>=0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS finance_approval_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 province_id BIGINT UNSIGNED NOT NULL,
 action_key VARCHAR(40) NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 payload_json LONGTEXT NOT NULL,
 submitted_by BIGINT UNSIGNED NOT NULL,
 submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 status ENUM('pending','approved','rejected','executed','cancelled') NOT NULL DEFAULT 'pending',
 approved_by BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 rejected_by BIGINT UNSIGNED NULL,
 rejected_at DATETIME NULL,
 decision_note VARCHAR(1000) NULL,
 executed_ledger_entry_id BIGINT UNSIGNED NULL,
 executed_at DATETIME NULL,
 KEY idx_finance_approval_unit(unit_id,status,submitted_at),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(province_id) REFERENCES provinces(id),
 FOREIGN KEY(submitted_by) REFERENCES users(id), FOREIGN KEY(approved_by) REFERENCES users(id), FOREIGN KEY(rejected_by) REFERENCES users(id),
 FOREIGN KEY(executed_ledger_entry_id) REFERENCES finance_ledger_entries(id), CHECK(amount>=0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS finance_period_closes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 province_id BIGINT UNSIGNED NOT NULL,
 period_start DATE NOT NULL,
 period_end DATE NOT NULL,
 statement_balance DECIMAL(12,2) NULL,
 ledger_balance DECIMAL(12,2) NOT NULL,
 reconciliation_difference DECIMAL(12,2) NULL,
 checklist_json LONGTEXT NOT NULL,
 source_hash CHAR(64) NOT NULL,
 status ENUM('closed') NOT NULL DEFAULT 'closed',
 closed_by BIGINT UNSIGNED NOT NULL,
 closed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 reopened_by BIGINT UNSIGNED NULL,
 reopened_at DATETIME NULL,
 reopen_reason VARCHAR(1000) NULL,
 UNIQUE KEY uq_finance_period_close(unit_id,period_start,period_end), KEY idx_finance_period_close(unit_id,status,period_end),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(closed_by) REFERENCES users(id), FOREIGN KEY(reopened_by) REFERENCES users(id), CHECK(period_start<=period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS finance_period_close_exceptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 close_id BIGINT UNSIGNED NOT NULL,
 exception_key VARCHAR(60) NOT NULL,
 reason VARCHAR(1000) NOT NULL,
 approved_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_finance_close_exception(close_id), FOREIGN KEY(close_id) REFERENCES finance_period_closes(id), FOREIGN KEY(approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS finance_period_reopens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 close_id BIGINT UNSIGNED NOT NULL,
 reason VARCHAR(1000) NOT NULL,
 reopened_by BIGINT UNSIGNED NOT NULL,
 reopened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_finance_period_reopen(close_id), FOREIGN KEY(close_id) REFERENCES finance_period_closes(id), FOREIGN KEY(reopened_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TRIGGER finance_period_close_no_update BEFORE UPDATE ON finance_period_closes FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Period close records are immutable';
CREATE TRIGGER finance_period_close_no_delete BEFORE DELETE ON finance_period_closes FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Period close records are immutable';