-- ============================================================
-- iBilik Capital Database v2.4
-- Phase 6E - Pool Automation & Controls
-- Target: MySQL 5.7.44
-- Baseline: Database v2.3
-- 所有新增 TABLE / COLUMN 均使用中文 COMMENT
-- ============================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS pool_control_settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 自動控制設定主鍵',
    setting_key VARCHAR(80) NOT NULL COMMENT '控制設定唯一代碼',
    setting_value VARCHAR(255) NOT NULL COMMENT '控制設定值；由程式依設定類型解析',
    value_type VARCHAR(20) NOT NULL DEFAULT 'STRING' COMMENT '設定值類型 STRING、INTEGER、DECIMAL、BOOLEAN',
    name_zh VARCHAR(160) NOT NULL COMMENT '設定中文名稱',
    name_en VARCHAR(160) NULL COMMENT '設定英文名稱；英文介面使用',
    description_zh VARCHAR(500) NULL COMMENT '設定用途中文說明',
    description_en VARCHAR(500) NULL COMMENT '設定用途英文說明',
    active_flag TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用此設定；1啟用0停用',
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '最後更新時間',
    PRIMARY KEY (id),
    UNIQUE KEY uq_pool_control_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='Pool 自動控制與風險門檻設定；避免將六個月紅線、對帳容差等重要規則硬寫死於程式';

CREATE TABLE IF NOT EXISTS pool_control_runs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 自動控制執行主鍵',
    run_code VARCHAR(70) NOT NULL COMMENT 'Pool Control Run 唯一編號',
    control_date DATE NOT NULL COMMENT '本次控制程序所使用的業務日期',
    control_type VARCHAR(30) NOT NULL DEFAULT 'DAILY_FULL' COMMENT '控制類型 DAILY_FULL、RISK_ONLY、RECONCILIATION_ONLY',
    execution_source VARCHAR(20) NOT NULL DEFAULT 'MANUAL' COMMENT '執行來源 MANUAL 或 CRON',
    status VARCHAR(20) NOT NULL DEFAULT 'RUNNING' COMMENT '執行狀態 RUNNING、COMPLETED、FAILED',
    source_lots_evaluated INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次重新評估的 Source Lot 數量',
    alerts_opened INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次新增的到期風險警報數量',
    alerts_updated INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次更新既有警報的數量',
    alerts_cleared INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次解除的到期風險警報數量',
    loans_accelerated INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次被標記為加速回收的 Pool Loan 數量',
    pools_reconciled INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次完成對帳的 Capital Pool 數量',
    discrepancies_found INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次發現的資金或收益對帳差異數量',
    executed_by_user_id BIGINT UNSIGNED NULL COMMENT '手動執行時的一般使用者主鍵',
    executed_by_super_admin_id BIGINT UNSIGNED NULL COMMENT '手動執行時的超級管理員主鍵',
    started_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '控制程序開始時間',
    finished_at DATETIME(6) NULL COMMENT '控制程序完成或失敗時間',
    summary_json LONGTEXT NULL COMMENT '本次控制程序統計與摘要 JSON',
    error_message VARCHAR(1000) NULL COMMENT '執行失敗時的安全錯誤摘要；不得保存登入憑證',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '控制執行記錄建立時間',
    PRIMARY KEY (id),
    UNIQUE KEY uq_pool_control_run_code (run_code),
    KEY idx_pool_control_run_date (control_date,status,execution_source),
    CONSTRAINT fk_pool_control_run_user FOREIGN KEY (executed_by_user_id) REFERENCES sys_users(id),
    CONSTRAINT fk_pool_control_run_super FOREIGN KEY (executed_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='Pool 每日風險重算、到期警報、加速回收與資金對帳的控制程序執行紀錄';

CREATE TABLE IF NOT EXISTS pool_reconciliation_issues (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 對帳差異主鍵',
    issue_code VARCHAR(70) NOT NULL COMMENT 'Pool 對帳差異唯一編號',
    control_run_id BIGINT UNSIGNED NOT NULL COMMENT '發現此差異的 Pool Control Run 主鍵',
    pool_id BIGINT UNSIGNED NULL COMMENT '若差異屬於整個 Pool，記錄 Capital Pool 主鍵',
    source_lot_id BIGINT UNSIGNED NULL COMMENT '若差異屬於 Source Lot，記錄其主鍵',
    pool_loan_id BIGINT UNSIGNED NULL COMMENT '若差異屬於 Pool Loan，記錄其主鍵',
    issue_type VARCHAR(50) NOT NULL COMMENT '差異類型 POOL_CASH、SOURCE_AVAILABLE、BONUS_OVERALLOCATION、LOAN_RISK 或其他',
    severity VARCHAR(20) NOT NULL DEFAULT 'WARNING' COMMENT '差異嚴重度 INFO、WARNING、CRITICAL',
    expected_amount DECIMAL(19,2) NULL COMMENT '依權威流水或計算推導出的正確金額',
    actual_amount DECIMAL(19,2) NULL COMMENT '目前 Cache 或業務資料所保存的實際金額',
    difference_amount DECIMAL(19,2) NULL COMMENT '實際值與預期值之間的差額',
    issue_status VARCHAR(20) NOT NULL DEFAULT 'OPEN' COMMENT '處理狀態 OPEN、ACKNOWLEDGED、CLEARED',
    message_zh VARCHAR(1000) NOT NULL COMMENT '對帳差異中文說明',
    message_en VARCHAR(1000) NULL COMMENT '對帳差異英文說明',
    detected_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '首次檢測到差異時間',
    cleared_at DATETIME(6) NULL COMMENT '差異確認已解除時間',
    resolution_zh VARCHAR(1000) NULL COMMENT '差異處理方式中文說明',
    resolution_en VARCHAR(1000) NULL COMMENT '差異處理方式英文說明',
    PRIMARY KEY (id),
    UNIQUE KEY uq_pool_reconciliation_issue_code (issue_code),
    KEY idx_pool_recon_run (control_run_id,issue_status,severity),
    KEY idx_pool_recon_pool (pool_id,issue_status),
    KEY idx_pool_recon_lot (source_lot_id,issue_status),
    CONSTRAINT fk_pool_recon_run FOREIGN KEY (control_run_id) REFERENCES pool_control_runs(id),
    CONSTRAINT fk_pool_recon_pool FOREIGN KEY (pool_id) REFERENCES capital_pools(id),
    CONSTRAINT fk_pool_recon_lot FOREIGN KEY (source_lot_id) REFERENCES pool_source_lots(id),
    CONSTRAINT fk_pool_recon_loan FOREIGN KEY (pool_loan_id) REFERENCES pool_loans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='Pool 權威流水與 Cache、Source Lot 狀態、Bonus 可用利潤等自動對帳所發現的差異紀錄';

INSERT INTO pool_control_settings
(setting_key,setting_value,value_type,name_zh,name_en,description_zh,description_en,active_flag)
VALUES
('POOL_RED_ALERT_MONTHS','6','INTEGER','Pool 紅色警報月數','Pool Red Alert Months','Investor 到期前多少個月禁止新增再部署','Number of months before investor maturity when new redeployment is blocked',1),
('POOL_WATCH_MONTHS','12','INTEGER','Pool 觀察期月數','Pool Watch Months','Investor 到期前多少個月進入 WATCH 狀態','Number of months before investor maturity when a source lot enters WATCH status',1),
('POOL_RECON_TOLERANCE','0.01','DECIMAL','Pool 對帳容差','Pool Reconciliation Tolerance','Pool 金額自動對帳允許的最大差額','Maximum permitted difference for automated pool reconciliation',1)
ON DUPLICATE KEY UPDATE
setting_value=VALUES(setting_value),
value_type=VALUES(value_type),
name_zh=VALUES(name_zh),
name_en=VALUES(name_en),
description_zh=VALUES(description_zh),
description_en=VALUES(description_en),
active_flag=1;

INSERT INTO permission_catalog
(permission_code,module_code,permission_type,name_zh,name_en,description_zh,description_en,active_flag,sort_order,created_at)
VALUES
('POOL_CONTROL_RUN','POOL','ACTION','執行 Pool 自動控制','Run Pool Controls','允許手動執行風險重算、到期警報與自動對帳','Allows manual execution of risk refresh, maturity alerts and reconciliation',1,390,NOW(6)),
('POOL_RECONCILIATION_VIEW','POOL','REPORT','查看 Pool 對帳差異','View Pool Reconciliation','允許查看 Pool Ledger、Source Lot 與 Bonus 自動對帳差異','Allows viewing automated Pool ledger, source lot and bonus reconciliation issues',1,400,NOW(6))
ON DUPLICATE KEY UPDATE
module_code=VALUES(module_code),
permission_type=VALUES(permission_type),
name_zh=VALUES(name_zh),
name_en=VALUES(name_en),
description_zh=VALUES(description_zh),
description_en=VALUES(description_en),
active_flag=1,
sort_order=VALUES(sort_order);

SELECT 'iBilik Capital Database v2.4 Pool Automation & Controls migration completed.' AS migration_status;
