-- ============================================================
-- iBilik Capital System - MySQL Database v1.0
-- Database: saibadns_icapital
-- Generated: 2026-08-16
-- Target: MySQL 8.x / MariaDB 10.6+ (InnoDB, utf8mb4)
-- 說明：所有資料表及主要欄位均使用中文註解，方便後續維護與接手。
-- 安全：本檔不包含任何資料庫密碼、Super Admin 密碼或 MID 明文。
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+08:00';
SET FOREIGN_KEY_CHECKS = 0;
USE `saibadns_icapital`;

-- ============================================================
-- A. 帳戶、安全、權限與稽核
-- ============================================================
CREATE TABLE `sys_super_admins` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '超級管理員內部主鍵',
  `admin_code` VARCHAR(30) NOT NULL COMMENT '超級管理員公開識別碼',
  `username` VARCHAR(80) NOT NULL COMMENT '超級管理員登入帳號',
  `display_name` VARCHAR(120) NOT NULL COMMENT '超級管理員顯示名稱',
  `email` VARCHAR(190) NULL COMMENT '超級管理員電子郵件',
  `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '帳戶狀態，例如 ACTIVE、SUSPENDED、DISABLED',
  `last_login_at` DATETIME(6) NULL COMMENT '最後成功登入時間',
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '資料建立時間',
  `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '資料最後更新時間',
  PRIMARY KEY (`id`), UNIQUE KEY `uk_super_admin_code` (`admin_code`), UNIQUE KEY `uk_super_admin_username` (`username`), UNIQUE KEY `uk_super_admin_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='超級管理員帳戶主資料表；只有此類帳戶可建立一般系統使用者';

CREATE TABLE `sys_users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '一般使用者內部主鍵',
  `user_code` VARCHAR(30) NOT NULL COMMENT '一般使用者公開識別碼',
  `username` VARCHAR(80) NOT NULL COMMENT '登入帳號',
  `display_name` VARCHAR(120) NOT NULL COMMENT '使用者顯示名稱',
  `email` VARCHAR(190) NULL COMMENT '使用者電子郵件',
  `department` VARCHAR(120) NULL COMMENT '部門或職能名稱',
  `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '帳戶狀態，例如 ACTIVE、SUSPENDED、DISABLED',
  `created_by_super_admin_id` BIGINT UNSIGNED NOT NULL COMMENT '建立此帳戶的超級管理員',
  `last_login_at` DATETIME(6) NULL COMMENT '最後成功登入時間',
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '資料建立時間',
  `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '資料最後更新時間',
  PRIMARY KEY (`id`), UNIQUE KEY `uk_user_code` (`user_code`), UNIQUE KEY `uk_user_username` (`username`), UNIQUE KEY `uk_user_email` (`email`), KEY `idx_user_creator` (`created_by_super_admin_id`),
  CONSTRAINT `fk_user_creator` FOREIGN KEY (`created_by_super_admin_id`) REFERENCES `sys_super_admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='一般系統使用者帳戶主資料表；帳戶只能由超級管理員建立';

CREATE TABLE `sys_user_credentials` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '憑證資料主鍵',
  `account_type` VARCHAR(20) NOT NULL COMMENT '帳戶類型 SUPER_ADMIN 或 USER',
  `account_id` BIGINT UNSIGNED NOT NULL COMMENT '對應帳戶主鍵；依 account_type 指向超管或一般使用者',
  `password_hash` VARCHAR(255) NOT NULL COMMENT '登入密碼安全雜湊值；禁止存放明文',
  `mid_hash` VARCHAR(255) NOT NULL COMMENT '9字元 MID 安全雜湊值；禁止存放明文',
  `mid_mask` VARCHAR(20) NULL COMMENT 'MID 遮罩提示資訊，不可還原完整 MID',
  `password_failed_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '目前密碼連續失敗次數',
  `mid_failed_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '目前 MID 連續失敗次數',
  `password_changed_at` DATETIME(6) NULL COMMENT '密碼最後更新時間',
  `mid_changed_at` DATETIME(6) NULL COMMENT 'MID 最後更新時間',
  `credential_version` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '憑證版本；更新密碼或 MID 時可遞增使舊 Session 失效',
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '資料建立時間',
  `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '資料最後更新時間',
  PRIMARY KEY (`id`), UNIQUE KEY `uk_credentials_account` (`account_type`,`account_id`), KEY `idx_credentials_failures` (`password_failed_count`,`mid_failed_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='帳戶登入安全憑證表；分開統計密碼與 MID 失敗次數';

CREATE TABLE `auth_login_attempts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '登入嘗試紀錄主鍵',
  `account_type` VARCHAR(20) NULL COMMENT '辨識出的帳戶類型',
  `account_id` BIGINT UNSIGNED NULL COMMENT '辨識出的帳戶主鍵；未知帳戶時可為空',
  `username_attempted` VARCHAR(120) NULL COMMENT '本次嘗試輸入的帳號識別資訊',
  `ip_address` VARCHAR(45) NOT NULL COMMENT '來源 IPv4 或 IPv6 位址',
  `user_agent_hash` CHAR(64) NULL COMMENT '瀏覽器 User-Agent SHA-256 摘要',
  `password_result` VARCHAR(20) NULL COMMENT '密碼驗證結果，例如 PASS、FAIL、SKIPPED',
  `mid_result` VARCHAR(20) NULL COMMENT 'MID 驗證結果，例如 PASS、FAIL、SKIPPED',
  `failure_component` VARCHAR(20) NULL COMMENT '本次失敗元件 PASSWORD、MID、ACCOUNT 或 LOCKED',
  `success_flag` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否完整登入成功；1為成功',
  `occurred_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '登入嘗試發生時間',
  PRIMARY KEY (`id`), KEY `idx_login_ip_time` (`ip_address`,`occurred_at`), KEY `idx_login_account_time` (`account_type`,`account_id`,`occurred_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='登入嘗試不可變紀錄；用於分開統計密碼與 MID 錯誤及風控稽核';

CREATE TABLE `auth_ip_locks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'IP 凍結紀錄主鍵',
  `ip_address` VARCHAR(45) NOT NULL COMMENT '被凍結的來源 IP 位址',
  `reason` VARCHAR(255) NOT NULL COMMENT '凍結原因說明',
  `password_failures` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '觸發時密碼錯誤累計',
  `mid_failures` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '觸發時 MID 錯誤累計',
  `combined_failures` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '觸發時密碼與 MID 綜合錯誤次數；達6次即凍結',
  `locked_at` DATETIME(6) NOT NULL COMMENT '開始凍結時間',
  `locked_until` DATETIME(6) NOT NULL COMMENT '凍結截止時間；標準規則為3小時',
  `released_at` DATETIME(6) NULL COMMENT '提前人工解除時間',
  `released_by_super_admin_id` BIGINT UNSIGNED NULL COMMENT '提前解除凍結的超級管理員',
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '紀錄建立時間',
  PRIMARY KEY (`id`), KEY `idx_ip_lock_active` (`ip_address`,`locked_until`,`released_at`), KEY `idx_ip_lock_releaser` (`released_by_super_admin_id`),
  CONSTRAINT `fk_ip_lock_releaser` FOREIGN KEY (`released_by_super_admin_id`) REFERENCES `sys_super_admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='登入來源 IP 凍結紀錄；密碼與 MID 綜合錯誤達6次時凍結3小時';

CREATE TABLE `auth_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Session 內部主鍵',
  `session_id_hash` CHAR(64) NOT NULL COMMENT 'Session Token 雜湊值；禁止存放可直接登入的明文 Token',
  `account_type` VARCHAR(20) NOT NULL COMMENT '登入帳戶類型 SUPER_ADMIN 或 USER',
  `account_id` BIGINT UNSIGNED NOT NULL COMMENT '登入帳戶主鍵',
  `credential_version` INT UNSIGNED NOT NULL COMMENT '建立 Session 時的憑證版本',
  `ip_address` VARCHAR(45) NOT NULL COMMENT 'Session 建立來源 IP',
  `user_agent_hash` CHAR(64) NULL COMMENT '瀏覽器 User-Agent SHA-256 摘要',
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Session 建立時間',
  `expires_at` DATETIME(6) NOT NULL COMMENT 'Session 到期時間',
  `revoked_at` DATETIME(6) NULL COMMENT 'Session 被撤銷時間',
  `revoke_reason` VARCHAR(255) NULL COMMENT 'Session 撤銷原因',
  PRIMARY KEY (`id`), UNIQUE KEY `uk_session_hash` (`session_id_hash`), KEY `idx_session_account` (`account_type`,`account_id`), KEY `idx_session_expiry` (`expires_at`,`revoked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='已驗證登入 Session 管理表；支援憑證更新後撤銷舊 Session';

CREATE TABLE `permission_catalog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '權限項目主鍵',
  `permission_code` VARCHAR(80) NOT NULL COMMENT '權限唯一代碼',
  `module_code` VARCHAR(60) NOT NULL COMMENT '所屬功能模組代碼',
  `permission_type` VARCHAR(20) NOT NULL COMMENT '權限類型 FUNCTION、REPORT 或 ACTION',
  `name_zh` VARCHAR(120) NOT NULL COMMENT '權限中文名稱',
  `description_zh` VARCHAR(255) NULL COMMENT '權限中文用途說明',
  `active_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用此權限項目',
  `sort_order` INT NOT NULL DEFAULT 0 COMMENT '後台勾選畫面排序值',
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '資料建立時間',
  PRIMARY KEY (`id`), UNIQUE KEY `uk_permission_code` (`permission_code`), KEY `idx_permission_module` (`module_code`,`permission_type`,`active_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系統功能、操作與報表的細項權限目錄；供超級管理員逐項勾選';

CREATE TABLE `user_permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '使用者權限授權紀錄主鍵',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '被授權的一般使用者',
  `permission_id` BIGINT UNSIGNED NOT NULL COMMENT '被授權或拒絕的權限項目',
  `is_allowed` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1代表允許，0代表明確拒絕',
  `granted_by_super_admin_id` BIGINT UNSIGNED NOT NULL COMMENT '進行授權設定的超級管理員',
  `granted_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '授權生效時間',
  `revoked_at` DATETIME(6) NULL COMMENT '權限被撤銷時間',
  PRIMARY KEY (`id`), UNIQUE KEY `uk_user_permission` (`user_id`,`permission_id`), KEY `idx_permission_granter` (`granted_by_super_admin_id`),
  CONSTRAINT `fk_user_perm_user` FOREIGN KEY (`user_id`) REFERENCES `sys_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_perm_perm` FOREIGN KEY (`permission_id`) REFERENCES `permission_catalog` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_perm_granter` FOREIGN KEY (`granted_by_super_admin_id`) REFERENCES `sys_super_admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='一般使用者細項功能及報表權限表；由超級管理員逐項授權';

CREATE TABLE `role_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '角色範本主鍵',
  `role_code` VARCHAR(60) NOT NULL COMMENT '角色範本唯一代碼',
  `name_zh` VARCHAR(120) NOT NULL COMMENT '角色範本中文名稱',
  `description_zh` VARCHAR(255) NULL COMMENT '角色範本中文說明',
  `active_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用角色範本',
  `created_by_super_admin_id` BIGINT UNSIGNED NOT NULL COMMENT '建立角色範本的超級管理員',
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '資料建立時間',
  PRIMARY KEY (`id`), UNIQUE KEY `uk_role_code` (`role_code`), CONSTRAINT `fk_role_creator` FOREIGN KEY (`created_by_super_admin_id`) REFERENCES `sys_super_admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='可選用的權限角色範本；用於快速套用但不取代最終逐項權限';

CREATE TABLE `role_template_permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '角色範本權限組合主鍵',
  `role_template_id` BIGINT UNSIGNED NOT NULL COMMENT '角色範本主鍵',
  `permission_id` BIGINT UNSIGNED NOT NULL COMMENT '角色包含的權限項目',
  `is_allowed` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '角色範本是否允許此權限',
  PRIMARY KEY (`id`), UNIQUE KEY `uk_role_perm` (`role_template_id`,`permission_id`),
  CONSTRAINT `fk_role_perm_role` FOREIGN KEY (`role_template_id`) REFERENCES `role_templates` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_role_perm_perm` FOREIGN KEY (`permission_id`) REFERENCES `permission_catalog` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色範本與權限項目的對應表';

CREATE TABLE `user_role_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '使用者套用角色範本紀錄主鍵',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '一般使用者主鍵',
  `role_template_id` BIGINT UNSIGNED NOT NULL COMMENT '套用的角色範本主鍵',
  `assigned_by_super_admin_id` BIGINT UNSIGNED NOT NULL COMMENT '套用角色的超級管理員',
  `assigned_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '套用角色時間',
  PRIMARY KEY (`id`), UNIQUE KEY `uk_user_role_template` (`user_id`,`role_template_id`),
  CONSTRAINT `fk_user_role_user` FOREIGN KEY (`user_id`) REFERENCES `sys_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_role_role` FOREIGN KEY (`role_template_id`) REFERENCES `role_templates` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_role_assigner` FOREIGN KEY (`assigned_by_super_admin_id`) REFERENCES `sys_super_admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='一般使用者套用角色範本的對應紀錄';

CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '稽核紀錄主鍵',
  `actor_type` VARCHAR(20) NOT NULL COMMENT '操作者類型 SUPER_ADMIN、USER 或 SYSTEM',
  `actor_id` BIGINT UNSIGNED NULL COMMENT '操作者帳戶主鍵；系統自動事件可為空',
  `event_code` VARCHAR(100) NOT NULL COMMENT '稽核事件代碼',
  `entity_type` VARCHAR(80) NULL COMMENT '被操作的資料實體類型',
  `entity_id` VARCHAR(80) NULL COMMENT '被操作資料的識別值',
  `before_json` JSON NULL COMMENT '異動前資料快照',
  `after_json` JSON NULL COMMENT '異動後資料快照',
  `ip_address` VARCHAR(45) NULL COMMENT '操作來源 IP',
  `checksum` CHAR(64) NULL COMMENT '稽核資料摘要，用於偵測內容被非預期變更',
  `occurred_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '事件發生時間',
  PRIMARY KEY (`id`), KEY `idx_audit_actor_time` (`actor_type`,`actor_id`,`occurred_at`), KEY `idx_audit_entity` (`entity_type`,`entity_id`), KEY `idx_audit_event_time` (`event_code`,`occurred_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='全系統不可變稽核紀錄；涵蓋登入、權限、交易、計算、付款、Pool及敏感報表操作';

-- ============================================================
-- B. 法人、交易對手、物業與文件
-- ============================================================
CREATE TABLE `legal_entities` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '法人主鍵', `entity_code` VARCHAR(40) NOT NULL COMMENT '法人唯一代碼', `legal_name` VARCHAR(190) NOT NULL COMMENT '法人正式名稱', `registration_no` VARCHAR(80) NULL COMMENT '公司註冊編號', `entity_type` VARCHAR(50) NULL COMMENT '法人類型，例如 CAPITAL、HOTEL、SPV', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '主要記帳幣別', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '法人狀態', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_legal_entity_code` (`entity_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='iBilik Capital、營運公司及其他法律實體主資料表';

CREATE TABLE `counterparties` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '交易對手主鍵', `counterparty_code` VARCHAR(40) NOT NULL COMMENT '交易對手唯一代碼', `name` VARCHAR(190) NOT NULL COMMENT '交易對手名稱', `counterparty_type` VARCHAR(50) NOT NULL COMMENT '交易對手類型，例如 HOTEL_OWNER、BORROWER、VENDOR', `registration_no` VARCHAR(80) NULL COMMENT '公司或身份註冊編號', `kyc_status` VARCHAR(30) NULL COMMENT 'KYC 審查狀態', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '交易對手狀態', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_counterparty_code` (`counterparty_code`), KEY `idx_counterparty_type_status` (`counterparty_type`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='業主、酒店公司、借款人、供應商及其他交易對手主資料表';

CREATE TABLE `properties` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '物業主鍵', `property_code` VARCHAR(40) NOT NULL COMMENT '物業唯一代碼', `name` VARCHAR(190) NOT NULL COMMENT '酒店或物業名稱', `legal_entity_id` BIGINT UNSIGNED NULL COMMENT '營運此物業的法人', `counterparty_id` BIGINT UNSIGNED NULL COMMENT '主要業主或交易對手', `address_line1` VARCHAR(190) NULL COMMENT '地址第一行', `address_line2` VARCHAR(190) NULL COMMENT '地址第二行', `city` VARCHAR(100) NULL COMMENT '城市', `state` VARCHAR(100) NULL COMMENT '州屬', `postcode` VARCHAR(20) NULL COMMENT '郵遞區號', `country_code` CHAR(2) NOT NULL DEFAULT 'MY' COMMENT '國家代碼', `room_count` INT UNSIGNED NULL COMMENT '房間數量', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '物業狀態', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_property_code` (`property_code`), KEY `idx_property_entity` (`legal_entity_id`), KEY `idx_property_counterparty` (`counterparty_id`), CONSTRAINT `fk_property_entity` FOREIGN KEY (`legal_entity_id`) REFERENCES `legal_entities` (`id`), CONSTRAINT `fk_property_counterparty` FOREIGN KEY (`counterparty_id`) REFERENCES `counterparties` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='酒店及物業主資料表；保存房量、地址及營運歸屬';

CREATE TABLE `counterparty_contacts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '交易對手聯絡人主鍵', `counterparty_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬交易對手', `name` VARCHAR(120) NOT NULL COMMENT '聯絡人姓名', `position` VARCHAR(120) NULL COMMENT '職位', `email` VARCHAR(190) NULL COMMENT '電子郵件', `phone` VARCHAR(50) NULL COMMENT '電話號碼', `is_primary` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否主要聯絡人', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), KEY `idx_contact_counterparty` (`counterparty_id`,`is_primary`), CONSTRAINT `fk_contact_counterparty` FOREIGN KEY (`counterparty_id`) REFERENCES `counterparties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='交易對手聯絡人資料表';

CREATE TABLE `bank_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '銀行帳戶主鍵', `owner_type` VARCHAR(30) NOT NULL COMMENT '帳戶持有人類型 LEGAL_ENTITY、COUNTERPARTY、INVESTOR 或 FUNDER', `owner_id` BIGINT UNSIGNED NOT NULL COMMENT '帳戶持有人主鍵', `bank_name` VARCHAR(160) NOT NULL COMMENT '銀行名稱', `account_name` VARCHAR(190) NOT NULL COMMENT '銀行帳戶名稱', `account_no_masked` VARCHAR(80) NOT NULL COMMENT '遮罩後銀行帳號；完整值應加密保存於安全層', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '銀行帳戶幣別', `verification_status` VARCHAR(30) NOT NULL DEFAULT 'PENDING' COMMENT '銀行帳戶驗證狀態', `active_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否可使用', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新時間', PRIMARY KEY (`id`), KEY `idx_bank_owner` (`owner_type`,`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='法人、投資者、交易對手及資金方銀行帳戶資料表';

CREATE TABLE `documents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '文件主鍵', `document_code` VARCHAR(50) NOT NULL COMMENT '文件唯一代碼', `entity_type` VARCHAR(60) NOT NULL COMMENT '文件所屬實體類型', `entity_id` BIGINT UNSIGNED NOT NULL COMMENT '文件所屬實體主鍵', `document_type` VARCHAR(60) NOT NULL COMMENT '文件類型，例如 AGREEMENT、KYC、BOQ、INVOICE', `file_name` VARCHAR(255) NOT NULL COMMENT '原始檔名', `storage_path` VARCHAR(500) NOT NULL COMMENT '檔案儲存位置或物件路徑', `sha256` CHAR(64) NULL COMMENT '文件 SHA-256 摘要', `review_status` VARCHAR(30) NOT NULL DEFAULT 'PENDING' COMMENT '文件審查狀態', `virus_scan_status` VARCHAR(30) NULL COMMENT '病毒掃描狀態', `uploaded_by_user_id` BIGINT UNSIGNED NULL COMMENT '上傳文件的一般使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '上傳時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_document_code` (`document_code`), KEY `idx_document_entity` (`entity_type`,`entity_id`), CONSTRAINT `fk_document_uploader` FOREIGN KEY (`uploaded_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合約、KYC、BOQ、發票與其他支持文件的中繼資料表';

-- ============================================================
-- C. 金融產品、公式與計算版本
-- ============================================================
CREATE TABLE `products` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '金融產品主鍵', `product_code` VARCHAR(40) NOT NULL COMMENT '金融產品唯一代碼', `name_zh` VARCHAR(120) NOT NULL COMMENT '金融產品中文名稱', `name_en` VARCHAR(160) NULL COMMENT '金融產品英文名稱', `product_category` VARCHAR(60) NULL COMMENT '產品分類', `active_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用產品', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_product_code` (`product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='五大金融產品主資料表；包含四個 Excel 原始產品與第五回收資金再利用 Pool';

CREATE TABLE `product_configurations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '產品設定版本主鍵', `product_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬金融產品', `version_no` VARCHAR(30) NOT NULL COMMENT '設定版本號', `status` VARCHAR(20) NOT NULL DEFAULT 'DRAFT' COMMENT '版本狀態 DRAFT、ACTIVE、RETIRED', `effective_from` DATE NULL COMMENT '設定生效日期', `effective_to` DATE NULL COMMENT '設定失效日期', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '產品主要幣別', `rate_basis` VARCHAR(30) NULL COMMENT '利率計算基準', `day_count_basis` VARCHAR(30) NULL COMMENT '日數計算基準', `rounding_method` VARCHAR(30) NULL COMMENT '金額或利率四捨五入方式', `terms_json` JSON NULL COMMENT '產品一般商業條件設定', `accounting_map_json` JSON NULL COMMENT '產品會計科目映射設定', `created_by_user_id` BIGINT UNSIGNED NULL COMMENT '建立設定的一般使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_product_config_version` (`product_id`,`version_no`), CONSTRAINT `fk_product_config_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`), CONSTRAINT `fk_product_config_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='金融產品有效日期式設定版本表；控制商業、風控與會計通用設定';

CREATE TABLE `formula_versions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '公式版本主鍵', `product_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬金融產品', `formula_code` VARCHAR(60) NOT NULL COMMENT '公式集合代碼', `version_no` VARCHAR(30) NOT NULL COMMENT '公式版本號', `status` VARCHAR(20) NOT NULL DEFAULT 'DRAFT' COMMENT '公式狀態 DRAFT、ACTIVE、RETIRED', `effective_from` DATE NULL COMMENT '公式生效日期', `effective_to` DATE NULL COMMENT '公式失效日期', `source_workbook` VARCHAR(255) NULL COMMENT '原始 Golden Reference Excel 檔名', `source_sheet` VARCHAR(120) NULL COMMENT '主要來源工作表名稱', `specification_json` JSON NULL COMMENT '公式規格與計算順序的機器可讀描述', `checksum` CHAR(64) NULL COMMENT '公式規格摘要；用於確認版本內容未被改動', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_formula_version` (`product_id`,`formula_code`,`version_no`), CONSTRAINT `fk_formula_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='由四張 Excel 及第五產品規則轉換而成的計算公式版本表；舊 Deal 永久綁定其批准版本';

CREATE TABLE `formula_parameters` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '公式參數定義主鍵', `formula_version_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬公式版本', `parameter_code` VARCHAR(80) NOT NULL COMMENT '參數唯一代碼', `label_zh` VARCHAR(160) NOT NULL COMMENT '參數中文名稱', `data_type` VARCHAR(30) NOT NULL COMMENT '參數資料型別，例如 DECIMAL、INTEGER、DATE、TEXT、BOOLEAN', `unit` VARCHAR(30) NULL COMMENT '參數單位，例如 MYR、百分比、月、天、房間', `default_value` VARCHAR(255) NULL COMMENT '預設值；以文字保存後由計算層依 data_type 解析', `min_value` DECIMAL(19,8) NULL COMMENT '允許的最低數值', `max_value` DECIMAL(19,8) NULL COMMENT '允許的最高數值', `editable_flag` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否允許人工作業修改', `required_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '計算時是否必填', `sort_order` INT NOT NULL DEFAULT 0 COMMENT '輸入畫面排序', PRIMARY KEY (`id`), UNIQUE KEY `uk_formula_parameter` (`formula_version_id`,`parameter_code`), CONSTRAINT `fk_formula_param_version` FOREIGN KEY (`formula_version_id`) REFERENCES `formula_versions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公式參數定義與人工可修改範圍表；對應 Excel 輸入格及系統衍生值';

-- D01 deals must exist before calculation_runs because runs reference deals
CREATE TABLE `deals` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '交易項目主鍵', `deal_code` VARCHAR(50) NOT NULL COMMENT '交易項目唯一公開編號', `product_id` BIGINT UNSIGNED NOT NULL COMMENT '所使用的金融產品', `property_id` BIGINT UNSIGNED NULL COMMENT '相關酒店或物業', `counterparty_id` BIGINT UNSIGNED NOT NULL COMMENT '主要交易對手或借款方', `status` VARCHAR(30) NOT NULL DEFAULT 'DRAFT' COMMENT 'Deal 狀態，例如 DRAFT、UNDERWRITING、APPROVED、ACTIVE、CLOSED', `requested_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '申請或規劃資金金額', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '交易幣別', `target_funding_date` DATE NULL COMMENT '預計放款日期', `purpose` VARCHAR(500) NULL COMMENT '資金用途說明', `source_reference` VARCHAR(120) NULL COMMENT '外部或舊系統參考編號', `created_by_user_id` BIGINT UNSIGNED NULL COMMENT '建立 Deal 的一般使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_deal_code` (`deal_code`), KEY `idx_deal_product_status` (`product_id`,`status`), KEY `idx_deal_property` (`property_id`), KEY `idx_deal_counterparty` (`counterparty_id`), CONSTRAINT `fk_deal_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`), CONSTRAINT `fk_deal_property` FOREIGN KEY (`property_id`) REFERENCES `properties` (`id`), CONSTRAINT `fk_deal_counterparty` FOREIGN KEY (`counterparty_id`) REFERENCES `counterparties` (`id`), CONSTRAINT `fk_deal_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='所有金融產品共用的 Deal/交易項目主檔；四大原始產品與 Pool 再部署均可關聯';

CREATE TABLE `calculation_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '計算執行主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `formula_version_id` BIGINT UNSIGNED NOT NULL COMMENT '本次計算使用的固定公式版本', `run_no` INT UNSIGNED NOT NULL COMMENT 'Deal 內計算執行序號', `run_type` VARCHAR(30) NOT NULL COMMENT '計算類型 PREVIEW、UNDERWRITING、APPROVAL 或 RECALC', `status` VARCHAR(20) NOT NULL DEFAULT 'COMPLETED' COMMENT '計算狀態', `input_checksum` CHAR(64) NULL COMMENT '所有輸入值摘要', `result_checksum` CHAR(64) NULL COMMENT '所有輸出值摘要', `created_by_user_id` BIGINT UNSIGNED NULL COMMENT '觸發計算的一般使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '計算時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_calc_run_no` (`deal_id`,`run_no`), KEY `idx_calc_formula` (`formula_version_id`), CONSTRAINT `fk_calc_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`), CONSTRAINT `fk_calc_formula` FOREIGN KEY (`formula_version_id`) REFERENCES `formula_versions` (`id`), CONSTRAINT `fk_calc_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='金融模型每一次不可變計算執行紀錄；保存使用的公式版本與輸入輸出摘要';

CREATE TABLE `calculation_inputs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '計算輸入值主鍵', `calculation_run_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬計算執行', `parameter_code` VARCHAR(80) NOT NULL COMMENT '參數代碼', `numeric_value` DECIMAL(19,8) NULL COMMENT '數字輸入值', `text_value` TEXT NULL COMMENT '文字輸入值', `date_value` DATE NULL COMMENT '日期輸入值', `source_type` VARCHAR(20) NOT NULL COMMENT '輸入來源 USER、IMPORTED、SYSTEM', `manually_overridden` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否由人工覆寫原本預設或衍生值', `prior_value` VARCHAR(255) NULL COMMENT '人工覆寫前的原始值', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_calc_input_param` (`calculation_run_id`,`parameter_code`), CONSTRAINT `fk_calc_input_run` FOREIGN KEY (`calculation_run_id`) REFERENCES `calculation_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='每次金融模型計算的正規化輸入值；保留人工修改痕跡';

CREATE TABLE `calculation_outputs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '計算輸出值主鍵', `calculation_run_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬計算執行', `output_code` VARCHAR(80) NOT NULL COMMENT '輸出指標代碼', `label_zh` VARCHAR(160) NULL COMMENT '輸出指標中文名稱', `numeric_value` DECIMAL(19,8) NULL COMMENT '數值型輸出', `text_value` TEXT NULL COMMENT '文字型輸出', `unit` VARCHAR(30) NULL COMMENT '輸出單位', PRIMARY KEY (`id`), UNIQUE KEY `uk_calc_output` (`calculation_run_id`,`output_code`), CONSTRAINT `fk_calc_output_run` FOREIGN KEY (`calculation_run_id`) REFERENCES `calculation_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='每次金融模型計算的摘要輸出與 KPI 資料表';

CREATE TABLE `calculation_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '計算完整快照主鍵', `calculation_run_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬計算執行', `snapshot_json` JSON NOT NULL COMMENT '完整保存輸入、公式版本、Waterfall 與輸出，用於日後重現', `checksum` CHAR(64) NULL COMMENT '完整快照摘要', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '快照建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_calc_snapshot_run` (`calculation_run_id`), CONSTRAINT `fk_calc_snapshot_run` FOREIGN KEY (`calculation_run_id`) REFERENCES `calculation_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='批准或重要計算的完整不可變 JSON 快照；確保未來公式升級仍可重現舊結果';

CREATE TABLE `sensitivity_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '敏感度分析主鍵', `calculation_run_id` BIGINT UNSIGNED NOT NULL COMMENT '基準計算執行', `scenario_code` VARCHAR(60) NOT NULL COMMENT '情境代碼', `scenario_name_zh` VARCHAR(160) NULL COMMENT '情境中文名稱', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_sensitivity_scenario` (`calculation_run_id`,`scenario_code`), CONSTRAINT `fk_sensitivity_run` FOREIGN KEY (`calculation_run_id`) REFERENCES `calculation_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='對應 Excel Sensitivity 的敏感度與情境分析主檔';

CREATE TABLE `sensitivity_values` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '敏感度分析數值主鍵', `sensitivity_run_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬敏感度情境', `value_type` VARCHAR(20) NOT NULL COMMENT '數值類型 INPUT、OUTPUT 或 AXIS', `value_code` VARCHAR(80) NOT NULL COMMENT '數值代碼', `numeric_value` DECIMAL(19,8) NULL COMMENT '敏感度數值', `text_value` VARCHAR(255) NULL COMMENT '非數字情境值', PRIMARY KEY (`id`), UNIQUE KEY `uk_sensitivity_value` (`sensitivity_run_id`,`value_type`,`value_code`), CONSTRAINT `fk_sensitivity_value_run` FOREIGN KEY (`sensitivity_run_id`) REFERENCES `sensitivity_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='敏感度分析每個情境的輸入軸與輸出結果明細';

-- ============================================================
-- D. Deal 條款、審批與風控
-- ============================================================
CREATE TABLE `deal_terms` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Deal 條款版本主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `version_no` INT UNSIGNED NOT NULL COMMENT '條款版本序號', `status` VARCHAR(20) NOT NULL DEFAULT 'DRAFT' COMMENT '條款版本狀態', `terms_json` JSON NOT NULL COMMENT 'Deal 專屬商業條款完整資料', `effective_at` DATETIME(6) NULL COMMENT '條款生效時間', `created_by_user_id` BIGINT UNSIGNED NULL COMMENT '建立條款的使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_deal_term_version` (`deal_id`,`version_no`), CONSTRAINT `fk_deal_term_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_deal_term_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Deal 專屬商業條款版本表；條款變更以新版本保存';

CREATE TABLE `deal_financial_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Deal 財務快照主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `calculation_run_id` BIGINT UNSIGNED NOT NULL COMMENT '批准時綁定的計算執行', `approved_principal` DECIMAL(19,2) NULL COMMENT '批准本金', `expected_total_return` DECIMAL(19,2) NULL COMMENT '預計總回報或收益', `expected_maturity_date` DATE NULL COMMENT '預計到期日期', `snapshot_json` JSON NULL COMMENT '其他批准財務摘要', `approved_at` DATETIME(6) NULL COMMENT '正式批准時間', PRIMARY KEY (`id`), KEY `idx_deal_financial_deal` (`deal_id`), CONSTRAINT `fk_deal_fin_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_deal_fin_calc` FOREIGN KEY (`calculation_run_id`) REFERENCES `calculation_runs` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Deal 批准時的經濟條件與計算結果不可變快照';

CREATE TABLE `deal_status_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Deal 狀態歷史主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `from_status` VARCHAR(30) NULL COMMENT '變更前狀態', `to_status` VARCHAR(30) NOT NULL COMMENT '變更後狀態', `reason` VARCHAR(500) NULL COMMENT '狀態變更原因', `changed_by_user_id` BIGINT UNSIGNED NULL COMMENT '進行變更的使用者', `changed_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '狀態變更時間', PRIMARY KEY (`id`), KEY `idx_deal_status_history` (`deal_id`,`changed_at`), CONSTRAINT `fk_deal_status_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_deal_status_user` FOREIGN KEY (`changed_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Deal 全生命週期狀態異動時間線';

CREATE TABLE `scoring_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '評分模板主鍵', `template_code` VARCHAR(50) NOT NULL COMMENT '評分模板代碼', `name_zh` VARCHAR(160) NOT NULL COMMENT '模板中文名稱', `product_id` BIGINT UNSIGNED NULL COMMENT '限定使用的金融產品；空值代表共用', `version_no` VARCHAR(30) NOT NULL COMMENT '模板版本', `active_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用', PRIMARY KEY (`id`), UNIQUE KEY `uk_score_template` (`template_code`,`version_no`), CONSTRAINT `fk_score_template_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='承銷與風險評分模板主資料表';

CREATE TABLE `scoring_criteria` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '評分條件主鍵', `scoring_template_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬評分模板', `criteria_code` VARCHAR(60) NOT NULL COMMENT '評分條件代碼', `name_zh` VARCHAR(160) NOT NULL COMMENT '評分條件中文名稱', `weight` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '此條件權重', `max_score` DECIMAL(12,4) NOT NULL DEFAULT 0 COMMENT '最高分數', `rules_json` JSON NULL COMMENT '評分規則設定', `sort_order` INT NOT NULL DEFAULT 0 COMMENT '顯示順序', PRIMARY KEY (`id`), UNIQUE KEY `uk_score_criteria` (`scoring_template_id`,`criteria_code`), CONSTRAINT `fk_score_criteria_template` FOREIGN KEY (`scoring_template_id`) REFERENCES `scoring_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='承銷評分模板的各項評分條件與權重';

CREATE TABLE `deal_scores` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Deal 評分結果主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `scoring_template_id` BIGINT UNSIGNED NOT NULL COMMENT '使用的評分模板', `criteria_id` BIGINT UNSIGNED NULL COMMENT '個別評分條件；總分列可為空', `score` DECIMAL(12,4) NOT NULL COMMENT '實際得分', `weighted_score` DECIMAL(12,4) NULL COMMENT '套用權重後得分', `evidence_json` JSON NULL COMMENT '評分依據與證據', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '評分時間', PRIMARY KEY (`id`), KEY `idx_deal_score_deal` (`deal_id`,`scoring_template_id`), CONSTRAINT `fk_deal_score_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_deal_score_template` FOREIGN KEY (`scoring_template_id`) REFERENCES `scoring_templates` (`id`), CONSTRAINT `fk_deal_score_criteria` FOREIGN KEY (`criteria_id`) REFERENCES `scoring_criteria` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Deal 承銷評分明細與總分結果';

CREATE TABLE `hard_gate_results` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '硬性條件檢查主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `gate_code` VARCHAR(60) NOT NULL COMMENT '硬性條件代碼', `gate_name_zh` VARCHAR(160) NOT NULL COMMENT '硬性條件中文名稱', `result` VARCHAR(20) NOT NULL COMMENT '檢查結果 PASS、FAIL、WAIVED', `evidence` TEXT NULL COMMENT '檢查證據或說明', `checked_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '檢查時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_hard_gate` (`deal_id`,`gate_code`), CONSTRAINT `fk_hard_gate_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Deal 必須通過的硬性承銷條件結果';

CREATE TABLE `policy_exceptions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '政策例外主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `exception_code` VARCHAR(60) NOT NULL COMMENT '例外代碼', `description` TEXT NOT NULL COMMENT '政策例外原因及內容', `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '例外狀態 PENDING、APPROVED、REJECTED', `requested_by_user_id` BIGINT UNSIGNED NULL COMMENT '提出例外申請的使用者', `approved_by_user_id` BIGINT UNSIGNED NULL COMMENT '批准例外的使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '申請時間', `decided_at` DATETIME(6) NULL COMMENT '批准或拒絕時間', PRIMARY KEY (`id`), KEY `idx_policy_exception_deal` (`deal_id`,`status`), CONSTRAINT `fk_policy_exception_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_policy_exception_requester` FOREIGN KEY (`requested_by_user_id`) REFERENCES `sys_users` (`id`), CONSTRAINT `fk_policy_exception_approver` FOREIGN KEY (`approved_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='承銷或政策例外申請與批准紀錄';

CREATE TABLE `approval_authorities` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '審批權限規則主鍵', `authority_code` VARCHAR(60) NOT NULL COMMENT '審批權限規則代碼', `name_zh` VARCHAR(160) NOT NULL COMMENT '審批權限中文名稱', `min_amount` DECIMAL(19,2) NULL COMMENT '適用最低金額', `max_amount` DECIMAL(19,2) NULL COMMENT '適用最高金額', `required_permission_code` VARCHAR(80) NULL COMMENT '進行此級審批所需權限代碼', `active_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用規則', PRIMARY KEY (`id`), UNIQUE KEY `uk_approval_authority_code` (`authority_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Deal、付款與例外審批的授權層級及金額門檻';

CREATE TABLE `approvals` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '審批紀錄主鍵', `entity_type` VARCHAR(60) NOT NULL COMMENT '被審批實體類型，例如 DEAL、DISBURSEMENT、ADJUSTMENT', `entity_id` BIGINT UNSIGNED NOT NULL COMMENT '被審批實體主鍵', `approval_authority_id` BIGINT UNSIGNED NULL COMMENT '適用審批授權規則', `step_no` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '審批步驟序號', `decision` VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '審批決定 PENDING、APPROVED、REJECTED', `approver_user_id` BIGINT UNSIGNED NULL COMMENT '審批的一般使用者', `comments` TEXT NULL COMMENT '審批意見', `decided_at` DATETIME(6) NULL COMMENT '決定時間', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '紀錄建立時間', PRIMARY KEY (`id`), KEY `idx_approval_entity` (`entity_type`,`entity_id`,`step_no`), CONSTRAINT `fk_approval_authority` FOREIGN KEY (`approval_authority_id`) REFERENCES `approval_authorities` (`id`), CONSTRAINT `fk_approval_user` FOREIGN KEY (`approver_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='各類交易、付款與調整的 Maker-Checker/審批流程紀錄';

CREATE TABLE `stress_tests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '壓力測試主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `scenario_name_zh` VARCHAR(160) NOT NULL COMMENT '壓力情境中文名稱', `assumptions_json` JSON NOT NULL COMMENT '壓力測試假設', `results_json` JSON NOT NULL COMMENT '壓力測試結果', `pass_flag` TINYINT(1) NULL COMMENT '是否通過風險門檻', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), KEY `idx_stress_deal` (`deal_id`,`created_at`), CONSTRAINT `fk_stress_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Deal 壓力測試與風險承受能力結果';

CREATE TABLE `commitments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '已批准承諾主鍵', `commitment_code` VARCHAR(50) NOT NULL COMMENT '承諾唯一編號', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `amount` DECIMAL(19,2) NOT NULL COMMENT '已批准但尚未完全撥出的資金承諾', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '承諾幣別', `committed_at` DATETIME(6) NOT NULL COMMENT '承諾生效時間', `expiry_date` DATE NULL COMMENT '承諾失效日期', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '承諾狀態', PRIMARY KEY (`id`), UNIQUE KEY `uk_commitment_code` (`commitment_code`), KEY `idx_commitment_deal` (`deal_id`,`status`), CONSTRAINT `fk_commitment_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='已批准但尚未完全撥款的資金承諾表；納入流動性預測';

-- ============================================================
-- E. 投資者與外部資金方負債
-- ============================================================
CREATE TABLE `investors` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '投資者主鍵', `investor_code` VARCHAR(50) NOT NULL COMMENT '投資者唯一編號', `name` VARCHAR(190) NOT NULL COMMENT '投資者姓名或公司名稱', `investor_type` VARCHAR(40) NULL COMMENT '投資者類型 INDIVIDUAL、COMPANY 等', `registration_no` VARCHAR(80) NULL COMMENT '身份或公司註冊編號', `kyc_status` VARCHAR(30) NOT NULL DEFAULT 'PENDING' COMMENT 'KYC 狀態', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '投資者狀態', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_investor_code` (`investor_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='投資者主資料及 KYC 狀態';

CREATE TABLE `investor_subscriptions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '投資者資金批次主鍵', `subscription_code` VARCHAR(60) NOT NULL COMMENT '投資者每一筆資金或 tranche 唯一編號', `investor_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬投資者', `principal` DECIMAL(19,2) NOT NULL COMMENT '原始投入本金', `subscription_date` DATE NOT NULL COMMENT '本金投入日期', `annual_return_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '約定年化回報率', `payment_frequency` VARCHAR(30) NULL COMMENT '回報支付頻率，例如 MONTHLY、QUARTERLY、MATURITY', `maturity_date` DATE NOT NULL COMMENT '投資者本金必須全額償還的到期日；Pool 6個月警報的權威日期', `outstanding_principal` DECIMAL(19,2) NOT NULL COMMENT '尚未歸還投資者的本金餘額', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '投資幣別', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '資金批次狀態', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_subscription_code` (`subscription_code`), KEY `idx_subscription_investor_maturity` (`investor_id`,`maturity_date`,`status`), CONSTRAINT `fk_subscription_investor` FOREIGN KEY (`investor_id`) REFERENCES `investors` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='每一筆投資者本金批次；是 Pool 資金到期風險與最終償還責任的核心來源';

CREATE TABLE `investor_return_schedule` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '投資者回報排程主鍵', `subscription_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬投資資金批次', `period_no` INT UNSIGNED NOT NULL COMMENT '回報期數', `period_start` DATE NULL COMMENT '計息期間開始日', `period_end` DATE NULL COMMENT '計息期間結束日', `due_date` DATE NOT NULL COMMENT '回報應付日期', `opening_principal` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期起始本金', `return_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期應付投資回報', `return_paid` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期已付投資回報', `status` VARCHAR(20) NOT NULL DEFAULT 'SCHEDULED' COMMENT '排程狀態', PRIMARY KEY (`id`), UNIQUE KEY `uk_investor_return_period` (`subscription_id`,`period_no`), KEY `idx_investor_return_due` (`due_date`,`status`), CONSTRAINT `fk_investor_return_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `investor_subscriptions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='投資者回報的應計、到期及已付排程';

CREATE TABLE `investor_principal_repayments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '投資者本金還款主鍵', `repayment_code` VARCHAR(60) NOT NULL COMMENT '本金還款唯一編號', `subscription_id` BIGINT UNSIGNED NOT NULL COMMENT '被償還的投資資金批次', `amount` DECIMAL(19,2) NOT NULL COMMENT '實際歸還本金金額', `payment_date` DATE NOT NULL COMMENT '實際付款日期', `bank_account_id` BIGINT UNSIGNED NULL COMMENT '付款使用的銀行帳戶', `reference` VARCHAR(190) NULL COMMENT '銀行或付款參考號', `created_by_user_id` BIGINT UNSIGNED NULL COMMENT '登錄付款的使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_investor_principal_repay_code` (`repayment_code`), KEY `idx_investor_repay_subscription` (`subscription_id`,`payment_date`), CONSTRAINT `fk_investor_repay_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `investor_subscriptions` (`id`), CONSTRAINT `fk_investor_repay_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`), CONSTRAINT `fk_investor_repay_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='實際歸還投資者本金的付款紀錄';

CREATE TABLE `funders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '外部資金方主鍵', `funder_code` VARCHAR(50) NOT NULL COMMENT '資金方唯一編號', `name` VARCHAR(190) NOT NULL COMMENT '資金方名稱', `registration_no` VARCHAR(80) NULL COMMENT '公司註冊編號', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '資金方狀態', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_funder_code` (`funder_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='銀行、基金或其他外部貸款資金方主資料';

CREATE TABLE `funding_facilities` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '融資額度主鍵', `facility_code` VARCHAR(60) NOT NULL COMMENT '融資額度唯一編號', `funder_id` BIGINT UNSIGNED NOT NULL COMMENT '提供額度的資金方', `facility_limit` DECIMAL(19,2) NOT NULL COMMENT '核准總額度', `annual_interest_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '年化融資利率', `fee_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '融資手續費率', `start_date` DATE NULL COMMENT '額度開始日期', `maturity_date` DATE NULL COMMENT '額度到期日期', `covenants_json` JSON NULL COMMENT '融資契約限制條件', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '融資額度狀態', PRIMARY KEY (`id`), UNIQUE KEY `uk_facility_code` (`facility_code`), CONSTRAINT `fk_facility_funder` FOREIGN KEY (`funder_id`) REFERENCES `funders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='外部資金方核准融資額度、利率、費用與到期日';

CREATE TABLE `funding_drawdowns` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '融資提款主鍵', `drawdown_code` VARCHAR(60) NOT NULL COMMENT '融資提款唯一編號', `facility_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬融資額度', `amount` DECIMAL(19,2) NOT NULL COMMENT '實際提款本金', `drawdown_date` DATE NOT NULL COMMENT '實際提款日期', `maturity_date` DATE NULL COMMENT '本次提款到期日期', `outstanding_principal` DECIMAL(19,2) NOT NULL COMMENT '尚未償還資金方的本金', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '提款狀態', PRIMARY KEY (`id`), UNIQUE KEY `uk_drawdown_code` (`drawdown_code`), KEY `idx_drawdown_facility` (`facility_id`,`status`), CONSTRAINT `fk_drawdown_facility` FOREIGN KEY (`facility_id`) REFERENCES `funding_facilities` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='外部融資額度每次實際提款紀錄';

CREATE TABLE `funder_repayment_schedule` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '資金方還款排程主鍵', `drawdown_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬提款批次', `period_no` INT UNSIGNED NOT NULL COMMENT '還款期數', `due_date` DATE NOT NULL COMMENT '應付款日期', `opening_principal` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期起始本金', `principal_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期應還本金', `interest_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期應付利息', `fee_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期應付費用', `status` VARCHAR(20) NOT NULL DEFAULT 'SCHEDULED' COMMENT '排程狀態', PRIMARY KEY (`id`), UNIQUE KEY `uk_funder_schedule_period` (`drawdown_id`,`period_no`), KEY `idx_funder_schedule_due` (`due_date`,`status`), CONSTRAINT `fk_funder_schedule_drawdown` FOREIGN KEY (`drawdown_id`) REFERENCES `funding_drawdowns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='外部資金方本金、利息及費用的應付還款排程';

CREATE TABLE `funder_repayments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '資金方實際還款主鍵', `repayment_code` VARCHAR(60) NOT NULL COMMENT '實際還款唯一編號', `drawdown_id` BIGINT UNSIGNED NOT NULL COMMENT '被償還的提款批次', `principal_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '實際償還本金', `interest_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '實際支付利息', `fee_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '實際支付費用', `payment_date` DATE NOT NULL COMMENT '實際付款日期', `reference` VARCHAR(190) NULL COMMENT '付款參考號', PRIMARY KEY (`id`), UNIQUE KEY `uk_funder_repayment_code` (`repayment_code`), KEY `idx_funder_repayment_drawdown` (`drawdown_id`,`payment_date`), CONSTRAINT `fk_funder_repayment_drawdown` FOREIGN KEY (`drawdown_id`) REFERENCES `funding_drawdowns` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='實際償還外部資金方本金、利息及費用的付款紀錄';

-- ============================================================
-- F. 撥款、回收與 Waterfall
-- ============================================================
CREATE TABLE `disbursement_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '撥款申請主鍵', `request_code` VARCHAR(60) NOT NULL COMMENT '撥款申請唯一編號', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `requested_amount` DECIMAL(19,2) NOT NULL COMMENT '申請撥款金額', `requested_date` DATE NOT NULL COMMENT '申請日期', `beneficiary_type` VARCHAR(30) NOT NULL COMMENT '收款方類型', `beneficiary_id` BIGINT UNSIGNED NOT NULL COMMENT '收款方主鍵', `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '撥款申請狀態', `requested_by_user_id` BIGINT UNSIGNED NULL COMMENT '提出申請的使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_disbursement_request_code` (`request_code`), KEY `idx_disb_req_deal` (`deal_id`,`status`), CONSTRAINT `fk_disb_req_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`), CONSTRAINT `fk_disb_req_user` FOREIGN KEY (`requested_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='資金實際離開 iBilik Capital 前的受控撥款申請';

CREATE TABLE `disbursements` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '實際撥款主鍵', `disbursement_code` VARCHAR(60) NOT NULL COMMENT '實際撥款唯一編號', `request_id` BIGINT UNSIGNED NULL COMMENT '來源撥款申請', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `amount` DECIMAL(19,2) NOT NULL COMMENT '實際撥出金額', `disbursement_date` DATE NOT NULL COMMENT '實際撥款日期', `bank_account_id` BIGINT UNSIGNED NULL COMMENT '出款銀行帳戶', `reference` VARCHAR(190) NULL COMMENT '銀行交易參考號', `status` VARCHAR(20) NOT NULL DEFAULT 'POSTED' COMMENT '撥款狀態', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_disbursement_code` (`disbursement_code`), KEY `idx_disbursement_deal_date` (`deal_id`,`disbursement_date`), CONSTRAINT `fk_disbursement_request` FOREIGN KEY (`request_id`) REFERENCES `disbursement_requests` (`id`), CONSTRAINT `fk_disbursement_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`), CONSTRAINT `fk_disbursement_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='經批准後實際撥出的資金紀錄';

CREATE TABLE `waterfall_schedules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Waterfall 排程主鍵', `schedule_code` VARCHAR(60) NOT NULL COMMENT 'Waterfall 唯一編號', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `calculation_run_id` BIGINT UNSIGNED NOT NULL COMMENT '產生此排程的固定計算執行', `version_no` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '排程版本序號', `status` VARCHAR(20) NOT NULL DEFAULT 'APPROVED' COMMENT '排程狀態', `start_date` DATE NULL COMMENT '排程開始日期', `end_date` DATE NULL COMMENT '排程結束日期', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_waterfall_code` (`schedule_code`), UNIQUE KEY `uk_waterfall_deal_version` (`deal_id`,`version_no`), CONSTRAINT `fk_waterfall_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`), CONSTRAINT `fk_waterfall_calc` FOREIGN KEY (`calculation_run_id`) REFERENCES `calculation_runs` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='由批准計算產生的每月 Waterfall 排程主檔';

CREATE TABLE `waterfall_schedule_lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Waterfall 月度明細主鍵', `schedule_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Waterfall 排程', `period_no` INT UNSIGNED NOT NULL COMMENT '期數', `period_date` DATE NOT NULL COMMENT '該期代表日期', `opening_principal` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '期初本金', `gross_inflow` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '預計或模型總流入', `principal_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本金回收額', `interest_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '利息或投資回報額', `fee_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '服務費、管理費等費用', `funding_cost_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '外部資金成本', `owner_cash` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '業主或酒店剩餘現金', `closing_principal` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '期末本金', `dimensions_json` JSON NULL COMMENT '產品特有欄位，例如營收、入住率、管理費基礎等', PRIMARY KEY (`id`), UNIQUE KEY `uk_waterfall_line_period` (`schedule_id`,`period_no`), CONSTRAINT `fk_waterfall_line_schedule` FOREIGN KEY (`schedule_id`) REFERENCES `waterfall_schedules` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='四大金融產品 Waterfall 每月本金、收益、費用與資金成本明細';

CREATE TABLE `collections` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '實際回款主鍵', `collection_code` VARCHAR(60) NOT NULL COMMENT '回款唯一編號', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '回款所屬 Deal', `amount` DECIMAL(19,2) NOT NULL COMMENT '實際收到總金額', `collection_date` DATE NOT NULL COMMENT '實際收款日期', `bank_account_id` BIGINT UNSIGNED NULL COMMENT '入款銀行帳戶', `reference` VARCHAR(190) NULL COMMENT '銀行或收款參考號', `status` VARCHAR(20) NOT NULL DEFAULT 'POSTED' COMMENT '回款狀態', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_collection_code` (`collection_code`), KEY `idx_collection_deal_date` (`deal_id`,`collection_date`), CONSTRAINT `fk_collection_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`), CONSTRAINT `fk_collection_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='從酒店、業主或借款方實際收回的現金主紀錄';

CREATE TABLE `collection_allocations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '回款拆分主鍵', `collection_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬實際回款', `allocation_type` VARCHAR(30) NOT NULL COMMENT '回款分類 PRINCIPAL、INTEREST、RETURN、FEE、TAX、ARREARS、UNIDENTIFIED', `amount` DECIMAL(19,2) NOT NULL COMMENT '分配到此類別的金額', `waterfall_line_id` BIGINT UNSIGNED NULL COMMENT '若適用，對應原 Waterfall 期數', `notes` VARCHAR(500) NULL COMMENT '拆分說明', PRIMARY KEY (`id`), KEY `idx_collection_alloc_collection` (`collection_id`,`allocation_type`), CONSTRAINT `fk_collection_alloc_collection` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_collection_alloc_waterfall` FOREIGN KEY (`waterfall_line_id`) REFERENCES `waterfall_schedule_lines` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='實際回款拆分為本金、利息、費用、稅項或逾期等類別；Pool 只可引用合資格本金';

CREATE TABLE `arrears_ledger` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '逾期應收主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `waterfall_line_id` BIGINT UNSIGNED NULL COMMENT '原應收 Waterfall 期數', `arrears_type` VARCHAR(30) NOT NULL COMMENT '逾期類型 PRINCIPAL、INTEREST、FEE 等', `original_due_date` DATE NOT NULL COMMENT '原應付日期', `original_amount` DECIMAL(19,2) NOT NULL COMMENT '原應付金額', `outstanding_amount` DECIMAL(19,2) NOT NULL COMMENT '目前仍未收回金額', `status` VARCHAR(20) NOT NULL DEFAULT 'OPEN' COMMENT '逾期狀態 OPEN、PARTIAL、SETTLED、WRITEOFF', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), KEY `idx_arrears_deal_status` (`deal_id`,`status`,`original_due_date`), CONSTRAINT `fk_arrears_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`), CONSTRAINT `fk_arrears_waterfall` FOREIGN KEY (`waterfall_line_id`) REFERENCES `waterfall_schedule_lines` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='逾期本金、利息及費用的獨立追蹤帳；避免混入正常回款表現';

CREATE TABLE `waterfall_adjustments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Waterfall 調整主鍵', `schedule_id` BIGINT UNSIGNED NOT NULL COMMENT '被調整的 Waterfall 排程', `period_no` INT UNSIGNED NULL COMMENT '被調整期數；整體調整可為空', `adjustment_type` VARCHAR(40) NOT NULL COMMENT '調整類型', `before_json` JSON NULL COMMENT '調整前資料', `after_json` JSON NOT NULL COMMENT '調整後資料', `reason` TEXT NOT NULL COMMENT '人工調整原因', `status` VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '調整審批狀態', `requested_by_user_id` BIGINT UNSIGNED NULL COMMENT '提出調整的使用者', `approved_by_user_id` BIGINT UNSIGNED NULL COMMENT '批准調整的使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '申請時間', PRIMARY KEY (`id`), KEY `idx_waterfall_adjust_schedule` (`schedule_id`,`status`), CONSTRAINT `fk_waterfall_adjust_schedule` FOREIGN KEY (`schedule_id`) REFERENCES `waterfall_schedules` (`id`), CONSTRAINT `fk_waterfall_adjust_requester` FOREIGN KEY (`requested_by_user_id`) REFERENCES `sys_users` (`id`), CONSTRAINT `fk_waterfall_adjust_approver` FOREIGN KEY (`approved_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Waterfall 人工修改的受控申請、前後值與審批紀錄';

CREATE TABLE `revenue_reconciliations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '營收及回款對帳主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `period_date` DATE NOT NULL COMMENT '對帳月份或期間代表日期', `scheduled_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '模型或排程預計金額', `actual_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '實際收到或營運確認金額', `variance_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '實際與排程差異', `variance_reason` VARCHAR(500) NULL COMMENT '差異原因', `status` VARCHAR(20) NOT NULL DEFAULT 'OPEN' COMMENT '對帳狀態', PRIMARY KEY (`id`), UNIQUE KEY `uk_revenue_recon_period` (`deal_id`,`period_date`), CONSTRAINT `fk_revenue_recon_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='模型預計營收或回款與實際結果的月度對帳表';

-- ============================================================
-- G. Capital Ledger、Source-Use、現金與預測
-- ============================================================
CREATE TABLE `capital_deployments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '本金部署主鍵', `deployment_code` VARCHAR(60) NOT NULL COMMENT '本金部署唯一編號', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '本金部署到的 Deal', `disbursement_id` BIGINT UNSIGNED NULL COMMENT '對應實際撥款', `principal_amount` DECIMAL(19,2) NOT NULL COMMENT '部署本金金額', `deployment_date` DATE NOT NULL COMMENT '本金實際部署日期', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '本金部署狀態', PRIMARY KEY (`id`), UNIQUE KEY `uk_capital_deployment_code` (`deployment_code`), KEY `idx_capital_deployment_deal` (`deal_id`,`deployment_date`), CONSTRAINT `fk_capital_deployment_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`), CONSTRAINT `fk_capital_deployment_disb` FOREIGN KEY (`disbursement_id`) REFERENCES `disbursements` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='實際部署到 Deal 的本金主紀錄；只記本金不混入費用與收益';

CREATE TABLE `source_use_allocations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '資金來源用途分配主鍵', `deployment_id` BIGINT UNSIGNED NOT NULL COMMENT '被資助的本金部署', `source_type` VARCHAR(30) NOT NULL COMMENT '資金來源 INVESTOR_SUBSCRIPTION、FUNDER_DRAWDOWN、EQUITY 或 POOL_SOURCE_LOT', `source_id` BIGINT UNSIGNED NOT NULL COMMENT '依 source_type 指向具體資金來源主鍵', `allocated_principal` DECIMAL(19,2) NOT NULL COMMENT '從該來源分配到此部署的本金', `allocation_date` DATE NOT NULL COMMENT '資金分配日期', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), KEY `idx_source_use_source` (`source_type`,`source_id`), KEY `idx_source_use_deployment` (`deployment_id`), CONSTRAINT `fk_source_use_deployment` FOREIGN KEY (`deployment_id`) REFERENCES `capital_deployments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='本金 Source-to-Use 血緣分配表；確保每筆部署可追溯到投資者、資金方、股東或 Pool 來源';

CREATE TABLE `cash_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '現金帳戶主鍵', `cash_account_code` VARCHAR(50) NOT NULL COMMENT '現金帳戶唯一代碼', `legal_entity_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬法人', `bank_account_id` BIGINT UNSIGNED NULL COMMENT '對應實體銀行帳戶', `name_zh` VARCHAR(160) NOT NULL COMMENT '現金帳戶中文名稱', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '帳戶幣別', `account_type` VARCHAR(30) NOT NULL COMMENT '現金帳戶類型 BANK、POOL_CASH、CLEARING 等', `active_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用', PRIMARY KEY (`id`), UNIQUE KEY `uk_cash_account_code` (`cash_account_code`), CONSTRAINT `fk_cash_account_entity` FOREIGN KEY (`legal_entity_id`) REFERENCES `legal_entities` (`id`), CONSTRAINT `fk_cash_account_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Treasury 現金與銀行帳戶控制主檔';

CREATE TABLE `capital_ledger` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '資本子分類帳主鍵', `ledger_code` VARCHAR(70) NOT NULL COMMENT '分類帳事件唯一編號', `event_type` VARCHAR(60) NOT NULL COMMENT '資金事件類型，例如 INVESTOR_IN、DEAL_DEPLOYMENT、PRINCIPAL_RECOVERY、POOL_TRANSFER_IN、POOL_INTEREST_RECOVERY', `business_date` DATE NOT NULL COMMENT '交易業務日期', `cash_account_id` BIGINT UNSIGNED NULL COMMENT '受影響現金帳戶', `entity_type` VARCHAR(60) NULL COMMENT '事件關聯實體類型', `entity_id` BIGINT UNSIGNED NULL COMMENT '事件關聯實體主鍵', `principal_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本金性質金額', `interest_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '利息或回報性質金額', `fee_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '費用性質金額', `cash_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '實際現金流入為正、流出為負', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '交易幣別', `reference` VARCHAR(190) NULL COMMENT '外部或來源參考號', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '事件入帳時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_capital_ledger_code` (`ledger_code`), KEY `idx_capital_ledger_date_event` (`business_date`,`event_type`), KEY `idx_capital_ledger_entity` (`entity_type`,`entity_id`), CONSTRAINT `fk_capital_ledger_cash` FOREIGN KEY (`cash_account_id`) REFERENCES `cash_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='iBilik Capital 不可變資金子分類帳；本金、利息、費用與現金分開記錄，Pool 轉移不視為收入';

CREATE TABLE `cash_balances` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '現金餘額快照主鍵', `cash_account_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬現金帳戶', `balance_date` DATE NOT NULL COMMENT '餘額日期', `opening_balance` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '期初餘額', `inflows` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '當日或期間流入', `outflows` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '當日或期間流出', `closing_balance` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '期末餘額', `source` VARCHAR(30) NOT NULL DEFAULT 'SYSTEM' COMMENT '餘額來源 SYSTEM 或 BANK_RECON', PRIMARY KEY (`id`), UNIQUE KEY `uk_cash_balance_date` (`cash_account_id`,`balance_date`), CONSTRAINT `fk_cash_balance_account` FOREIGN KEY (`cash_account_id`) REFERENCES `cash_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Treasury 每日或期間現金餘額快照';

CREATE TABLE `obligations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '未來義務主鍵', `obligation_code` VARCHAR(60) NOT NULL COMMENT '未來義務唯一編號', `obligation_type` VARCHAR(40) NOT NULL COMMENT '義務類型 INVESTOR_PRINCIPAL、INVESTOR_RETURN、FUNDER、COMMITMENT 等', `entity_type` VARCHAR(60) NOT NULL COMMENT '義務來源實體類型', `entity_id` BIGINT UNSIGNED NOT NULL COMMENT '義務來源實體主鍵', `due_date` DATE NOT NULL COMMENT '應支付或履行日期', `amount` DECIMAL(19,2) NOT NULL COMMENT '義務金額', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '義務幣別', `status` VARCHAR(20) NOT NULL DEFAULT 'OPEN' COMMENT '義務狀態', PRIMARY KEY (`id`), UNIQUE KEY `uk_obligation_code` (`obligation_code`), KEY `idx_obligation_due` (`due_date`,`status`), KEY `idx_obligation_entity` (`entity_type`,`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='未來投資者、資金方及已承諾撥款義務；供流動性與到期預測';

CREATE TABLE `forecast_scenarios` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '現金預測情境主鍵', `scenario_code` VARCHAR(50) NOT NULL COMMENT '預測情境唯一代碼', `name_zh` VARCHAR(160) NOT NULL COMMENT '預測情境中文名稱', `forecast_type` VARCHAR(30) NOT NULL COMMENT '預測類型 30D、13W、MONTHLY 等', `as_of_date` DATE NOT NULL COMMENT '預測基準日期', `status` VARCHAR(20) NOT NULL DEFAULT 'DRAFT' COMMENT '預測情境狀態', `created_by_user_id` BIGINT UNSIGNED NULL COMMENT '建立預測的使用者', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_forecast_scenario_code` (`scenario_code`), CONSTRAINT `fk_forecast_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='30天、13週及月度現金流預測情境主檔';

CREATE TABLE `forecast_lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '預測明細主鍵', `forecast_scenario_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬現金預測情境', `forecast_date` DATE NOT NULL COMMENT '預計現金流日期', `flow_type` VARCHAR(40) NOT NULL COMMENT '預測流向或類型', `source_type` VARCHAR(60) NULL COMMENT '預測來源實體類型', `source_id` BIGINT UNSIGNED NULL COMMENT '預測來源實體主鍵', `inflow_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '預計流入', `outflow_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '預計流出', `confidence_pct` DECIMAL(8,4) NULL COMMENT '預測可信度百分比', `notes` VARCHAR(500) NULL COMMENT '預測說明', PRIMARY KEY (`id`), KEY `idx_forecast_line_date` (`forecast_scenario_id`,`forecast_date`), CONSTRAINT `fk_forecast_line_scenario` FOREIGN KEY (`forecast_scenario_id`) REFERENCES `forecast_scenarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='現金預測情境的每日或期間預計流入與流出明細';

-- ============================================================
-- H. 四個 Excel 產品專屬條款
-- ============================================================
CREATE TABLE `forward_rental_terms` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Flexible Forward Rental 專屬條款主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `monthly_guaranteed_rental` DECIMAL(19,2) NOT NULL COMMENT '每月保證租金', `advance_period_months` INT UNSIGNED NOT NULL COMMENT '預付月份', `owner_discount_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '業主折扣率', `funding_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '外部融資利率', `funding_tenure_months` INT UNSIGNED NULL COMMENT '融資期限月數', `funding_method` VARCHAR(30) NULL COMMENT '資金成本方法 FLAT 或 REDUCING', `processing_fee_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '融資處理費率', `owner_service_fee_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '業主服務費率', `agent_commission_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '代理佣金率', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_forward_rental_deal` (`deal_id`), CONSTRAINT `fk_forward_rental_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='P01 Flexible Forward Rental Model 的 Deal 專屬輸入條款；計算公式仍以 formula_versions 版本控制';

CREATE TABLE `revenue_management_terms` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Revenue Management 專屬條款主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `number_hotels` INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '酒店數量', `average_rooms_per_hotel` DECIMAL(12,4) NOT NULL DEFAULT 0 COMMENT '每間酒店平均房數', `signup_bonus_per_room` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '每房簽約獎金本金', `renovation_capex_per_room` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '每房裝修 CAPEX 本金', `hotel_flat_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '酒店 Flat Rate 利率', `hotel_term_months` INT UNSIGNED NULL COMMENT '酒店還款期限月數', `funder_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '外部資金方利率', `funder_term_months` INT UNSIGNED NULL COMMENT '外部資金方期限月數', `funder_ratio` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '外部資金佔總本金比例', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_revenue_management_deal` (`deal_id`), CONSTRAINT `fk_revenue_management_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='P02 Revenue Management Program 的 Deal 專屬輸入條款';

CREATE TABLE `capex_funding_terms` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'CAPEX Funding 專屬條款主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `rooms` INT UNSIGNED NOT NULL COMMENT '納入模型的房間數', `capex_required` DECIMAL(19,2) NOT NULL COMMENT '所需 CAPEX 本金', `adr` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '平均房價 ADR', `occupancy_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '入住率', `days_per_month` DECIMAL(8,4) NOT NULL DEFAULT 30 COMMENT '每月計算天數', `management_fee_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '管理費率', `monthly_return_component_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '每月目標回報佔原始 CAPEX 比例', `monthly_principal_recovery_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '每月本金回收佔原始 CAPEX 比例', `funding_cost_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '資金成本率', `processing_fee_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '處理費率', `horizon_months` INT UNSIGNED NULL COMMENT '模型期數月數', `max_topline_share_rate` DECIMAL(12,8) NULL COMMENT '最高可抽取營收比例', `operating_cost_ratio` DECIMAL(12,8) NULL COMMENT '營運成本佔營收比例', `annual_revenue_growth_rate` DECIMAL(12,8) NULL COMMENT '年度營收成長率', `fee_basis` VARCHAR(40) NULL COMMENT '管理費或費用計算基礎', `alternative_rental` DECIMAL(19,2) NULL COMMENT '替代租金或比較基準', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_capex_funding_deal` (`deal_id`), CONSTRAINT `fk_capex_funding_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='P03 CAPEX Funding Waterfall 的 Deal 專屬輸入條款';

CREATE TABLE `advance_profit_terms` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Advance Profit 專屬條款主鍵', `deal_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal', `forecast_monthly_owner_profit` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '預測每月業主利潤', `advance_months` INT UNSIGNED NOT NULL COMMENT '預支月份；Golden Reference 主要為1、2、3、6、12月', `gross_override` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '人工覆寫總預測權益；大於0時優先於月利潤乘月份', `discount_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '依預支月份選定的折扣率', `owner_service_fee_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '業主服務費率', `fee_basis` VARCHAR(30) NULL COMMENT '服務費計算基礎 GROSS 或 AFTER_DISCOUNT', `hotel_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '酒店端利率', `hotel_interest_method` VARCHAR(30) NULL COMMENT '酒店利息方法 FLAT 或 REDUCING', `hotel_tenure_override_months` INT UNSIGNED NULL COMMENT '酒店期限人工覆寫月數', `funder_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '外部資金方利率', `funder_processing_fee_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '外部資金處理費率', `funder_ratio` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '外部資金比例', `funder_tenure_override_months` INT UNSIGNED NULL COMMENT '外部資金方期限人工覆寫月數', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_advance_profit_deal` (`deal_id`), CONSTRAINT `fk_advance_profit_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='P04 Advance Profit Waterfall 的 Deal 專屬輸入條款';

-- ============================================================
-- I. 第五金融產品：體制內回收資金再利用 Pool
-- ============================================================
CREATE TABLE `capital_pools` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '資金池主鍵', `pool_code` VARCHAR(60) NOT NULL COMMENT '資金池唯一編號', `name_zh` VARCHAR(160) NOT NULL COMMENT '資金池中文名稱', `currency` CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '資金池幣別', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '資金池狀態 ACTIVE、CLOSED、SUSPENDED', `opened_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '資金池啟用時間', `closed_at` DATETIME(6) NULL COMMENT '資金池關閉時間', `current_cash_balance` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '目前可用現金快取值；權威值應由 Ledger 與 Source Lot 推導', `total_source_principal` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '累計轉入資金池的來源本金', `total_deployed` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '累計二次部署本金', `total_recovered` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '累計二次回收本金', `total_secondary_interest` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '累計二次利息收益', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_pool_code` (`pool_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='第五金融產品資金池主資料表；集中管理合資格回收本金再利用';

CREATE TABLE `pool_source_lots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 資金來源批次主鍵', `source_lot_code` VARCHAR(70) NOT NULL COMMENT 'Pool 資金追蹤編號；每一筆回收本金轉入 Pool 後產生', `pool_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬資金池', `original_product_id` BIGINT UNSIGNED NOT NULL COMMENT '此回收本金原本源自哪一個金融產品', `original_deal_id` BIGINT UNSIGNED NOT NULL COMMENT '此回收本金原本源自哪一個 Deal', `original_collection_id` BIGINT UNSIGNED NOT NULL COMMENT '實際回款來源紀錄', `original_collection_allocation_id` BIGINT UNSIGNED NOT NULL COMMENT '回款中被分類為合資格本金的拆分紀錄', `original_investor_subscription_id` BIGINT UNSIGNED NULL COMMENT '若能直接追溯，記錄此本金最終對應的原投資者資金批次', `recovery_period` CHAR(7) NOT NULL COMMENT '原始回收月份 YYYY-MM；回答源自哪個月的回收資金', `recovery_date` DATE NOT NULL COMMENT '原始本金回收日期', `transferred_to_pool_date` DATE NOT NULL COMMENT '正式挪入 Pool 日期', `transferred_amount` DECIMAL(19,2) NOT NULL COMMENT '本次轉入 Pool 的本金金額', `remaining_available_amount` DECIMAL(19,2) NOT NULL COMMENT '目前仍可供再次部署的本金餘額', `investor_maturity_date` DATE NULL COMMENT '原投資者本金必須全額歸還的到期日；由原 Subscription 快照保存', `risk_status` VARCHAR(20) NOT NULL DEFAULT 'NORMAL' COMMENT '到期風險 NORMAL、WATCH、RED、MATURED、OVERDUE', `deployment_blocked` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否禁止新二次部署；距投資者到期少於等於6個月時必須為1', `block_reason` VARCHAR(255) NULL COMMENT '禁止二次部署原因', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_pool_source_lot_code` (`source_lot_code`), KEY `idx_pool_source_pool_status` (`pool_id`,`risk_status`,`deployment_blocked`), KEY `idx_pool_source_maturity` (`investor_maturity_date`,`deployment_blocked`), KEY `idx_pool_source_origin` (`original_product_id`,`original_deal_id`,`recovery_period`), CONSTRAINT `fk_pool_source_pool` FOREIGN KEY (`pool_id`) REFERENCES `capital_pools` (`id`), CONSTRAINT `fk_pool_source_product` FOREIGN KEY (`original_product_id`) REFERENCES `products` (`id`), CONSTRAINT `fk_pool_source_deal` FOREIGN KEY (`original_deal_id`) REFERENCES `deals` (`id`), CONSTRAINT `fk_pool_source_collection` FOREIGN KEY (`original_collection_id`) REFERENCES `collections` (`id`), CONSTRAINT `fk_pool_source_alloc` FOREIGN KEY (`original_collection_allocation_id`) REFERENCES `collection_allocations` (`id`), CONSTRAINT `fk_pool_source_subscription` FOREIGN KEY (`original_investor_subscription_id`) REFERENCES `investor_subscriptions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='每一筆回收本金進入 Pool 後的資金血緣批次；保存原產品、Deal、月份、投資者到期日及剩餘可用本金';

CREATE TABLE `pool_transfers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 轉入紀錄主鍵', `transfer_code` VARCHAR(70) NOT NULL COMMENT '回收本金轉入 Pool 唯一編號', `source_lot_id` BIGINT UNSIGNED NOT NULL COMMENT '所建立或增加的 Pool Source Lot', `amount` DECIMAL(19,2) NOT NULL COMMENT '實際挪入 Pool 本金金額；此動作不是收入', `transfer_date` DATE NOT NULL COMMENT '實際挪入 Pool 日期', `source_ledger_id` BIGINT UNSIGNED NULL COMMENT '來源本金回收 Ledger 事件', `destination_ledger_id` BIGINT UNSIGNED NULL COMMENT 'Pool 轉入 Ledger 事件', `approved_by_user_id` BIGINT UNSIGNED NULL COMMENT '批准資金轉移的使用者', `reference` VARCHAR(190) NULL COMMENT '轉移參考資訊', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_pool_transfer_code` (`transfer_code`), KEY `idx_pool_transfer_lot_date` (`source_lot_id`,`transfer_date`), CONSTRAINT `fk_pool_transfer_lot` FOREIGN KEY (`source_lot_id`) REFERENCES `pool_source_lots` (`id`), CONSTRAINT `fk_pool_transfer_source_ledger` FOREIGN KEY (`source_ledger_id`) REFERENCES `capital_ledger` (`id`), CONSTRAINT `fk_pool_transfer_dest_ledger` FOREIGN KEY (`destination_ledger_id`) REFERENCES `capital_ledger` (`id`), CONSTRAINT `fk_pool_transfer_approver` FOREIGN KEY (`approved_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合資格回收本金實際挪入 Pool 的不可變轉移紀錄；只屬本金調動，不計收入';

CREATE TABLE `pool_loans` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 二次借貸主鍵', `pool_loan_code` VARCHAR(70) NOT NULL COMMENT 'Pool 二次借貸唯一編號', `pool_id` BIGINT UNSIGNED NOT NULL COMMENT '出資的資金池', `counterparty_id` BIGINT UNSIGNED NOT NULL COMMENT '二次借貸的借款方', `related_deal_id` BIGINT UNSIGNED NULL COMMENT '若二次借貸同時建立正式 Deal，記錄其主鍵', `principal` DECIMAL(19,2) NOT NULL COMMENT '二次借貸本金', `interest_rate` DECIMAL(12,8) NOT NULL DEFAULT 0 COMMENT '二次借貸利率', `interest_method` VARCHAR(30) NULL COMMENT '二次借貸利息方法 FLAT、REDUCING 或其他', `disbursement_date` DATE NOT NULL COMMENT '二次借貸實際撥款日期', `maturity_date` DATE NOT NULL COMMENT '二次借貸預計完成回收日期', `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '二次借貸狀態', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_pool_loan_code` (`pool_loan_code`), KEY `idx_pool_loan_pool_status` (`pool_id`,`status`,`maturity_date`), CONSTRAINT `fk_pool_loan_pool` FOREIGN KEY (`pool_id`) REFERENCES `capital_pools` (`id`), CONSTRAINT `fk_pool_loan_counterparty` FOREIGN KEY (`counterparty_id`) REFERENCES `counterparties` (`id`), CONSTRAINT `fk_pool_loan_deal` FOREIGN KEY (`related_deal_id`) REFERENCES `deals` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pool 回收本金再次借貸出去的二次部署主檔';

CREATE TABLE `pool_loan_allocations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool Loan 資金來源分配主鍵', `pool_loan_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬二次借貸', `source_lot_id` BIGINT UNSIGNED NOT NULL COMMENT '供應此借貸本金的原回收資金批次', `allocated_principal` DECIMAL(19,2) NOT NULL COMMENT '從 Source Lot 分配的本金', `allocation_date` DATE NOT NULL COMMENT '分配日期', `remaining_allocated_principal` DECIMAL(19,2) NOT NULL COMMENT '此來源目前仍在二次借貸中未收回的本金', `allocation_ratio` DECIMAL(12,8) NULL COMMENT '此 Source Lot 佔 Pool Loan 原始出資比例；可用於收益歸因', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_pool_loan_source_lot` (`pool_loan_id`,`source_lot_id`), KEY `idx_pool_alloc_source` (`source_lot_id`,`remaining_allocated_principal`), CONSTRAINT `fk_pool_alloc_loan` FOREIGN KEY (`pool_loan_id`) REFERENCES `pool_loans` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_pool_alloc_lot` FOREIGN KEY (`source_lot_id`) REFERENCES `pool_source_lots` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='每筆 Pool 二次借貸由哪些原回收 Source Lot 出資；RED 或 blocked Source Lot 禁止新增分配';

CREATE TABLE `pool_loan_schedule` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 二次借貸還款排程主鍵', `pool_loan_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Pool Loan', `period_no` INT UNSIGNED NOT NULL COMMENT '期數', `due_date` DATE NOT NULL COMMENT '應付款日期', `opening_principal` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '期初本金', `principal_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期應收本金', `interest_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期應收二次利息', `fee_due` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本期應收費用', `closing_principal` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '期末本金', `status` VARCHAR(20) NOT NULL DEFAULT 'SCHEDULED' COMMENT '排程狀態', PRIMARY KEY (`id`), UNIQUE KEY `uk_pool_loan_schedule_period` (`pool_loan_id`,`period_no`), KEY `idx_pool_schedule_due` (`due_date`,`status`), CONSTRAINT `fk_pool_schedule_loan` FOREIGN KEY (`pool_loan_id`) REFERENCES `pool_loans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pool 二次借貸預計本金、利息與費用回收排程';

CREATE TABLE `pool_loan_collections` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 二次借貸實際回款主鍵', `receipt_code` VARCHAR(70) NOT NULL COMMENT 'Pool 二次回款唯一編號', `pool_loan_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬 Pool Loan', `amount` DECIMAL(19,2) NOT NULL COMMENT '實際收到總金額', `collection_date` DATE NOT NULL COMMENT '實際回款日期', `reference` VARCHAR(190) NULL COMMENT '銀行或收款參考號', `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_pool_receipt_code` (`receipt_code`), KEY `idx_pool_receipt_loan_date` (`pool_loan_id`,`collection_date`), CONSTRAINT `fk_pool_receipt_loan` FOREIGN KEY (`pool_loan_id`) REFERENCES `pool_loans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pool 二次借貸實際收回現金主紀錄';

CREATE TABLE `pool_collection_allocations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 二次回款拆分主鍵', `pool_loan_collection_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬二次實際回款', `allocation_type` VARCHAR(30) NOT NULL COMMENT '回款分類 PRINCIPAL、INTEREST、FEE 或 OTHER', `amount` DECIMAL(19,2) NOT NULL COMMENT '分配到此類別的金額', `source_lot_id` BIGINT UNSIGNED NULL COMMENT '若已直接歸因，記錄對應原 Source Lot', `allocation_ratio` DECIMAL(12,8) NULL COMMENT '歸因到 Source Lot 時使用的比例', PRIMARY KEY (`id`), KEY `idx_pool_collection_alloc_receipt` (`pool_loan_collection_id`,`allocation_type`), KEY `idx_pool_collection_alloc_lot` (`source_lot_id`), CONSTRAINT `fk_pool_collection_alloc_receipt` FOREIGN KEY (`pool_loan_collection_id`) REFERENCES `pool_loan_collections` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_pool_collection_alloc_lot` FOREIGN KEY (`source_lot_id`) REFERENCES `pool_source_lots` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pool 二次回款拆分為本金、利息及費用，並可直接對應原 Source Lot';

CREATE TABLE `pool_source_returns` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool Source Lot 二次收益歸因主鍵', `source_lot_id` BIGINT UNSIGNED NOT NULL COMMENT '被歸因收益的原回收資金批次', `pool_loan_id` BIGINT UNSIGNED NOT NULL COMMENT '產生此收益的 Pool Loan', `pool_loan_collection_id` BIGINT UNSIGNED NOT NULL COMMENT '產生此收益的實際回款', `attributed_interest` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '歸因到此 Source Lot 的二次利息', `attributed_fee` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '歸因到此 Source Lot 的二次費用收入', `attribution_ratio` DECIMAL(12,8) NOT NULL COMMENT '本次收益歸因比例', `attributed_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '完成收益歸因時間', PRIMARY KEY (`id`), KEY `idx_pool_return_source` (`source_lot_id`,`attributed_at`), KEY `idx_pool_return_loan` (`pool_loan_id`), CONSTRAINT `fk_pool_return_lot` FOREIGN KEY (`source_lot_id`) REFERENCES `pool_source_lots` (`id`), CONSTRAINT `fk_pool_return_loan` FOREIGN KEY (`pool_loan_id`) REFERENCES `pool_loans` (`id`), CONSTRAINT `fk_pool_return_receipt` FOREIGN KEY (`pool_loan_collection_id`) REFERENCES `pool_loan_collections` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='將 Pool 二次借貸產生的利息及費用回歸到原始回收資金 Source Lot；回答每條資金額外賺多少';

CREATE TABLE `pool_maturity_alerts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 到期警報主鍵', `source_lot_id` BIGINT UNSIGNED NOT NULL COMMENT '觸發到期風險的 Source Lot', `alert_level` VARCHAR(20) NOT NULL COMMENT '警報等級 NORMAL、RED、MATURED、OVERDUE', `days_to_maturity` INT NULL COMMENT '評估時距原投資者到期剩餘天數；負數代表已逾期', `message_zh` VARCHAR(500) NOT NULL COMMENT '中文風險警報內容', `first_alerted_at` DATETIME(6) NOT NULL COMMENT '第一次進入此風險狀態時間', `latest_evaluated_at` DATETIME(6) NOT NULL COMMENT '最後重新評估時間', `action_owner_user_id` BIGINT UNSIGNED NULL COMMENT '負責加速回收或退出的人員', `cleared_at` DATETIME(6) NULL COMMENT '警報解除時間', `clear_reason` VARCHAR(500) NULL COMMENT '警報解除原因', PRIMARY KEY (`id`), KEY `idx_pool_alert_active` (`alert_level`,`cleared_at`,`latest_evaluated_at`), KEY `idx_pool_alert_lot` (`source_lot_id`,`latest_evaluated_at`), CONSTRAINT `fk_pool_alert_lot` FOREIGN KEY (`source_lot_id`) REFERENCES `pool_source_lots` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_pool_alert_owner` FOREIGN KEY (`action_owner_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pool 資金原投資者到期風險警報歷史；剩餘少於等於6個月即紅色並禁止新部署';

-- ============================================================
-- J. 會計、報表、Watchlist 與控制警報
-- ============================================================
CREATE TABLE `gl_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '總帳科目主鍵', `account_code` VARCHAR(40) NOT NULL COMMENT '會計科目代碼', `name_zh` VARCHAR(160) NOT NULL COMMENT '會計科目中文名稱', `account_type` VARCHAR(30) NOT NULL COMMENT '科目類型 ASSET、LIABILITY、EQUITY、INCOME、EXPENSE', `parent_id` BIGINT UNSIGNED NULL COMMENT '上層會計科目', `active_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用', PRIMARY KEY (`id`), UNIQUE KEY `uk_gl_account_code` (`account_code`), KEY `idx_gl_parent` (`parent_id`), CONSTRAINT `fk_gl_parent` FOREIGN KEY (`parent_id`) REFERENCES `gl_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='iBilik Capital 會計科目表';

CREATE TABLE `reporting_periods` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '會計報告期間主鍵', `period_code` VARCHAR(20) NOT NULL COMMENT '期間代碼，例如 2026-08', `start_date` DATE NOT NULL COMMENT '期間開始日', `end_date` DATE NOT NULL COMMENT '期間結束日', `status` VARCHAR(20) NOT NULL DEFAULT 'OPEN' COMMENT '期間狀態 OPEN、SOFT_CLOSED、CLOSED', `closed_at` DATETIME(6) NULL COMMENT '正式關帳時間', `closed_by_user_id` BIGINT UNSIGNED NULL COMMENT '執行關帳的使用者', PRIMARY KEY (`id`), UNIQUE KEY `uk_reporting_period_code` (`period_code`), CONSTRAINT `fk_period_closer` FOREIGN KEY (`closed_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='會計與管理報表期間開關控制表';

CREATE TABLE `journal_entries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '會計傳票主鍵', `journal_code` VARCHAR(70) NOT NULL COMMENT '傳票唯一編號', `reporting_period_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬會計期間', `journal_date` DATE NOT NULL COMMENT '傳票日期', `description_zh` VARCHAR(500) NOT NULL COMMENT '傳票中文說明', `status` VARCHAR(20) NOT NULL DEFAULT 'DRAFT' COMMENT '傳票狀態 DRAFT、POSTED、REVERSED', `idempotency_key` VARCHAR(100) NULL COMMENT '防止同一交易重複過帳的唯一鍵', `source_type` VARCHAR(60) NULL COMMENT '來源交易類型', `source_id` BIGINT UNSIGNED NULL COMMENT '來源交易主鍵', `reversal_of_journal_id` BIGINT UNSIGNED NULL COMMENT '若為沖銷傳票，指向原傳票', `posted_by_user_id` BIGINT UNSIGNED NULL COMMENT '執行正式過帳的使用者', `posted_at` DATETIME(6) NULL COMMENT '正式過帳時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_journal_code` (`journal_code`), UNIQUE KEY `uk_journal_idempotency` (`idempotency_key`), KEY `idx_journal_source` (`source_type`,`source_id`), CONSTRAINT `fk_journal_period` FOREIGN KEY (`reporting_period_id`) REFERENCES `reporting_periods` (`id`), CONSTRAINT `fk_journal_reversal` FOREIGN KEY (`reversal_of_journal_id`) REFERENCES `journal_entries` (`id`), CONSTRAINT `fk_journal_poster` FOREIGN KEY (`posted_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='會計傳票主檔；正式過帳後應禁止直接修改並使用沖銷方式更正';

CREATE TABLE `journal_lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '會計傳票分錄主鍵', `journal_entry_id` BIGINT UNSIGNED NOT NULL COMMENT '所屬傳票', `line_no` INT UNSIGNED NOT NULL COMMENT '分錄行號', `gl_account_id` BIGINT UNSIGNED NOT NULL COMMENT '會計科目', `description_zh` VARCHAR(500) NULL COMMENT '分錄中文說明', `debit_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '借方金額', `credit_amount` DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '貸方金額', `entity_type` VARCHAR(60) NULL COMMENT '輔助追蹤實體類型', `entity_id` BIGINT UNSIGNED NULL COMMENT '輔助追蹤實體主鍵', PRIMARY KEY (`id`), UNIQUE KEY `uk_journal_line` (`journal_entry_id`,`line_no`), KEY `idx_journal_line_account` (`gl_account_id`), CONSTRAINT `fk_journal_line_entry` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_journal_line_account` FOREIGN KEY (`gl_account_id`) REFERENCES `gl_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='會計傳票借貸分錄明細；每張正式傳票借貸總額必須平衡';

CREATE TABLE `watchlist_cases` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Watchlist 案件主鍵', `case_code` VARCHAR(60) NOT NULL COMMENT 'Watchlist 唯一編號', `entity_type` VARCHAR(60) NOT NULL COMMENT '被監控實體類型 DEAL、INVESTOR、POOL_SOURCE_LOT 等', `entity_id` BIGINT UNSIGNED NOT NULL COMMENT '被監控實體主鍵', `risk_level` VARCHAR(20) NOT NULL COMMENT '風險等級', `reason_zh` VARCHAR(500) NOT NULL COMMENT '中文列入原因', `status` VARCHAR(20) NOT NULL DEFAULT 'OPEN' COMMENT '案件狀態 OPEN、MONITORING、CLOSED', `owner_user_id` BIGINT UNSIGNED NULL COMMENT '負責跟進人員', `opened_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '列入時間', `closed_at` DATETIME(6) NULL COMMENT '解除時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_watchlist_code` (`case_code`), KEY `idx_watchlist_entity` (`entity_type`,`entity_id`,`status`), CONSTRAINT `fk_watchlist_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Deal、投資者、Pool Source Lot 等風險 Watchlist 追蹤案件';

CREATE TABLE `control_alerts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '系統控制警報主鍵', `alert_code` VARCHAR(70) NOT NULL COMMENT '警報唯一編號', `alert_type` VARCHAR(60) NOT NULL COMMENT '警報類型 LIQUIDITY、COVENANT、MATURITY、ARREARS、POOL_RED 等', `severity` VARCHAR(20) NOT NULL COMMENT '嚴重程度 INFO、WATCH、RED、CRITICAL', `entity_type` VARCHAR(60) NULL COMMENT '警報關聯實體類型', `entity_id` BIGINT UNSIGNED NULL COMMENT '警報關聯實體主鍵', `message_zh` VARCHAR(1000) NOT NULL COMMENT '警報中文內容', `status` VARCHAR(20) NOT NULL DEFAULT 'OPEN' COMMENT '警報狀態 OPEN、ACKNOWLEDGED、RESOLVED', `owner_user_id` BIGINT UNSIGNED NULL COMMENT '負責處理人員', `triggered_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '警報觸發時間', `resolved_at` DATETIME(6) NULL COMMENT '警報解除時間', PRIMARY KEY (`id`), UNIQUE KEY `uk_control_alert_code` (`alert_code`), KEY `idx_control_alert_open` (`severity`,`status`,`triggered_at`), KEY `idx_control_alert_entity` (`entity_type`,`entity_id`), CONSTRAINT `fk_control_alert_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='全系統中央風險警報表；統一呈現流動性、到期、逾期、Pool 紅色警報等';

CREATE TABLE `report_definitions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '報表定義主鍵', `report_code` VARCHAR(70) NOT NULL COMMENT '報表唯一代碼', `name_zh` VARCHAR(160) NOT NULL COMMENT '報表中文名稱', `description_zh` VARCHAR(500) NULL COMMENT '報表中文說明', `permission_code` VARCHAR(80) NOT NULL COMMENT '查看此報表所需的權限代碼', `sensitive_flag` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否屬敏感投資者或資本報表', `active_flag` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用報表', PRIMARY KEY (`id`), UNIQUE KEY `uk_report_code` (`report_code`), KEY `idx_report_permission` (`permission_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系統報表目錄；每份報表綁定獨立查看權限';

CREATE TABLE `report_access_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '報表存取稽核主鍵', `report_definition_id` BIGINT UNSIGNED NOT NULL COMMENT '被查看的報表', `account_type` VARCHAR(20) NOT NULL COMMENT '查看者帳戶類型', `account_id` BIGINT UNSIGNED NOT NULL COMMENT '查看者帳戶主鍵', `accessed_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '報表查看時間', `ip_address` VARCHAR(45) NULL COMMENT '查看來源 IP', `parameters_json` JSON NULL COMMENT '本次報表查詢條件；敏感值需避免直接記錄', `exported_flag` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否執行匯出', PRIMARY KEY (`id`), KEY `idx_report_access_report_time` (`report_definition_id`,`accessed_at`), KEY `idx_report_access_account` (`account_type`,`account_id`,`accessed_at`), CONSTRAINT `fk_report_access_report` FOREIGN KEY (`report_definition_id`) REFERENCES `report_definitions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='敏感投資者、Capital 與 Pool 報表的查看及匯出稽核紀錄';

-- ============================================================
-- 初始資料：五大產品、核心權限、核心報表
-- ============================================================
INSERT INTO `products` (`product_code`,`name_zh`,`name_en`,`product_category`) VALUES
('P01_FORWARD_RENTAL','彈性預付租金模式','Flexible Forward Rental','ORIGINAL_PRODUCT'),
('P02_REVENUE_MANAGEMENT','營收管理方案','Revenue Management Program','ORIGINAL_PRODUCT'),
('P03_CAPEX_FUNDING','資本支出融資方案','CAPEX Funding Waterfall','ORIGINAL_PRODUCT'),
('P04_ADVANCE_PROFIT','預支利潤方案','Advance Profit Waterfall','ORIGINAL_PRODUCT'),
('P05_RECYCLED_CAPITAL_POOL','體制內回收資金再利用池','Recycled Capital Pool','RECYCLED_CAPITAL');

INSERT INTO `permission_catalog` (`permission_code`,`module_code`,`permission_type`,`name_zh`,`description_zh`,`sort_order`) VALUES
('DASHBOARD_VIEW','DASHBOARD','FUNCTION','查看主控台','允許登入後查看獲授權範圍內的主控台資訊',10),
('USER_VIEW','SECURITY','FUNCTION','查看使用者','允許查看一般使用者帳戶與狀態',20),
('USER_PERMISSION_EDIT','SECURITY','ACTION','設定使用者權限','允許調整一般使用者功能與報表權限；實際操作仍限超級管理員',30),
('DEAL_VIEW','DEAL','FUNCTION','查看 Deal','允許查看 Deal 主資料及獲授權財務資訊',100),
('DEAL_CREATE','DEAL','ACTION','建立 Deal','允許建立新的 Deal',110),
('DEAL_EDIT','DEAL','ACTION','編輯 Deal','允許在流程規則內修改尚未鎖定的 Deal',120),
('CALCULATION_RUN','CALCULATION','ACTION','執行金融模型計算','允許使用批准公式版本執行預覽或重新計算',200),
('CALCULATION_OVERRIDE','CALCULATION','ACTION','覆寫可人工修改參數','允許修改被公式定義為 editable 的參數',210),
('INVESTOR_VIEW','INVESTOR','FUNCTION','查看投資者','允許查看投資者及其資金批次',300),
('INVESTOR_EDIT','INVESTOR','ACTION','維護投資者','允許新增或維護投資者資料',310),
('COLLECTION_POST','CASH','ACTION','登錄實際回款','允許登錄及拆分實際回款',400),
('DISBURSEMENT_REQUEST','CASH','ACTION','提出撥款申請','允許建立撥款申請',410),
('DISBURSEMENT_APPROVE','CASH','ACTION','批准撥款','允許依權限層級批准撥款',420),
('POOL_VIEW','POOL','FUNCTION','查看回收資金池','允許查看 Pool、Source Lot、借貸與二次收益',500),
('POOL_TRANSFER','POOL','ACTION','回收本金轉入 Pool','允許把合資格已回收本金挪入 Pool',510),
('POOL_DEPLOY','POOL','ACTION','Pool 二次部署','允許把未被風控封鎖的 Source Lot 再次借貸出去',520),
('POOL_COLLECTION','POOL','ACTION','登錄 Pool 回款','允許登錄二次借貸本金、利息及費用回收',530),
('ACCOUNTING_VIEW','ACCOUNTING','FUNCTION','查看會計資料','允許查看會計傳票與科目',600),
('ACCOUNTING_POST','ACCOUNTING','ACTION','正式過帳','允許正式過帳已平衡傳票',610),
('REPORT_INVESTOR','REPORT','REPORT','投資者報表','查看投資者本金、回報、到期及還款資訊',700),
('REPORT_CAPITAL','REPORT','REPORT','Capital 資金報表','查看 Capital 資金來源、用途及 Ledger',710),
('REPORT_POOL','REPORT','REPORT','Pool 資金血緣報表','查看 Source Lot 來源、再部署、二次收益及到期風險',720),
('REPORT_LIQUIDITY','REPORT','REPORT','流動性預測報表','查看現金餘額、未來義務及現金流預測',730),
('REPORT_AUDIT','REPORT','REPORT','稽核報表','查看登入、權限與敏感操作稽核資料',740);

INSERT INTO `report_definitions` (`report_code`,`name_zh`,`description_zh`,`permission_code`,`sensitive_flag`) VALUES
('RPT_INVESTOR_POSITION','投資者資金與到期報表','投資者每筆本金、已付回報、尚欠本金及到期日','REPORT_INVESTOR',1),
('RPT_CAPITAL_LINEAGE','Capital 資金血緣報表','從投資者或資金方追蹤至 Deal、回收、Pool 與最終償還','REPORT_CAPITAL',1),
('RPT_POOL_LINEAGE','Pool 回收資金再利用報表','顯示每個 Source Lot 原始來源、回收月份、二次借貸、利息歸因及距投資者到期時間','REPORT_POOL',1),
('RPT_LIQUIDITY_FORECAST','流動性與到期預測','顯示現金、未來投資者及資金方義務與 30天/13週/月度預測','REPORT_LIQUIDITY',1),
('RPT_SECURITY_AUDIT','帳戶安全與稽核報表','顯示登入失敗、IP 凍結、權限異動及敏感操作紀錄','REPORT_AUDIT',1);

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- v1.0 建庫完成
-- 注意：
-- 1) Super Admin 初始帳戶應由後續安全初始化程式建立，密碼與 MID 均只存安全雜湊。
-- 2) MID 固定 9 字元，必須含至少 1 大寫、1 小寫、1 數字、1 符號；應由應用程式安全亂數產生。
-- 3) Password 與 MID 錯誤分開計數，綜合達 6 次時由登入服務寫入 auth_ip_locks，locked_until = NOW()+3小時。
-- 4) pool_source_lots 距 investor_maturity_date <= 6 個月時，應由排程或交易前檢查設為 RED 且 deployment_blocked=1。
-- 5) 所有 approved/posting/ledger 類資料後續應採不可直接覆寫原值原則，以沖銷或新版本處理。
-- ============================================================
