-- ============================================================
-- iBilik Capital Database v2.3
-- Recycled Capital Pool Core
-- Target: MySQL 5.7.44
-- Baseline: Database v2.2
-- 所有新增 TABLE / COLUMN 均使用中文 COMMENT
-- ============================================================

SET NAMES utf8mb4;
SET @db_name := DATABASE();

-- ============================================================
-- 1. Pool Eligibility Assessment
-- ============================================================

CREATE TABLE IF NOT EXISTS pool_eligibility_assessments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 資金資格評估主鍵',
    assessment_code VARCHAR(70) NOT NULL COMMENT 'Pool 資格評估唯一編號',
    original_collection_id BIGINT UNSIGNED NOT NULL COMMENT '原 Deal 實際回款主鍵',
    original_collection_allocation_id BIGINT UNSIGNED NOT NULL COMMENT '原回款中本金性質拆分主鍵',
    investor_subscription_id BIGINT UNSIGNED NOT NULL COMMENT '此本金最終對應的單一投資者資金批次；Atomic Source Lot 基礎',
    liability_schedule_id BIGINT UNSIGNED NULL COMMENT '若適用，對應投資者本金或收益負債排程',
    recovered_principal_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本次實際回收且可評估的本金金額',
    minimum_principal_repayment DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '依資金責任至少必須保留或償還的投資者本金',
    minimum_contractual_return DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '至少必須保留或支付的投資者原合約收益；不包含 Recycling Bonus',
    required_reserve_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT 'Capital 依政策需保留不可再次部署的風險準備金',
    eligible_surplus_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '扣除最低本金償還、原合約收益及 Reserve 後可考慮進入 Pool 的本金',
    approved_pool_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '經批准實際可轉入 Pool 的本金；不得高於 eligible_surplus_amount',
    assessment_date DATE NOT NULL COMMENT 'Pool 資格評估業務日期',
    assessment_status VARCHAR(20) NOT NULL DEFAULT 'DRAFT' COMMENT '評估狀態：DRAFT、APPROVED、REJECTED、CANCELLED',
    calculation_run_id BIGINT UNSIGNED NULL COMMENT '若最低還款或資格金額源自公式計算，記錄 Calculation Run',
    approved_by_user_id BIGINT UNSIGNED NULL COMMENT '批准資格評估的一般使用者主鍵',
    approved_by_super_admin_id BIGINT UNSIGNED NULL COMMENT '批准資格評估的超級管理員主鍵',
    approved_at DATETIME(6) NULL COMMENT '資格評估批准時間',
    notes_zh VARCHAR(1000) NULL COMMENT 'Pool 資格評估中文備註',
    notes_en VARCHAR(1000) NULL COMMENT 'Pool 資格評估英文備註；英文介面使用',
    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 uq_pool_eligibility_code (assessment_code),
    KEY idx_pool_eligibility_collection (original_collection_id, original_collection_allocation_id),
    KEY idx_pool_eligibility_subscription (investor_subscription_id, assessment_status),
    KEY idx_pool_eligibility_status (assessment_status, assessment_date),
    CONSTRAINT fk_pool_eligibility_collection FOREIGN KEY (original_collection_id) REFERENCES collections(id),
    CONSTRAINT fk_pool_eligibility_alloc FOREIGN KEY (original_collection_allocation_id) REFERENCES collection_allocations(id),
    CONSTRAINT fk_pool_eligibility_subscription FOREIGN KEY (investor_subscription_id) REFERENCES investor_subscriptions(id),
    CONSTRAINT fk_pool_eligibility_liability FOREIGN KEY (liability_schedule_id) REFERENCES investor_liability_schedules(id),
    CONSTRAINT fk_pool_eligibility_calc FOREIGN KEY (calculation_run_id) REFERENCES calculation_runs(id),
    CONSTRAINT fk_pool_eligibility_user FOREIGN KEY (approved_by_user_id) REFERENCES sys_users(id),
    CONSTRAINT fk_pool_eligibility_super FOREIGN KEY (approved_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='回收本金進入 Recycled Capital Pool 前的資格評估；嚴格區分原合約應付與可再利用本金';

-- ============================================================
-- 2. Pool General Ledger
-- ============================================================

CREATE TABLE IF NOT EXISTS pool_ledger_entries (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 權威資金流水主鍵',
    ledger_code VARCHAR(70) NOT NULL COMMENT 'Pool Ledger 唯一事件編號',
    pool_id BIGINT UNSIGNED NOT NULL COMMENT '所屬 Capital Pool 主鍵',
    source_lot_id BIGINT UNSIGNED NULL COMMENT '若事件能歸屬單一 Source Lot，記錄其主鍵',
    pool_loan_id BIGINT UNSIGNED NULL COMMENT '若事件與二次借貸有關，記錄 Pool Loan 主鍵',
    pool_loan_collection_id BIGINT UNSIGNED NULL COMMENT '若事件源自二次借貸回款，記錄實際回款主鍵',
    event_type VARCHAR(50) NOT NULL COMMENT 'Pool 事件類型，例如 TRANSFER_IN、DEPLOYMENT_OUT、PRINCIPAL_RECOVERY、INTEREST_RECOVERY、BONUS_OUT',
    direction VARCHAR(10) NOT NULL COMMENT '現金方向：IN 或 OUT',
    business_date DATE NOT NULL COMMENT 'Pool 流水業務日期',
    principal_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本金性質金額；本金轉移不視為收入',
    interest_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT 'Pool 二次利息收益金額',
    fee_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT 'Pool 二次 Fee 收益金額',
    bonus_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '支付給 Investor 的 Recycling Bonus 金額',
    cash_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '此事件實際影響 Pool Cash 的絕對金額；方向由 direction 決定',
    capital_ledger_id BIGINT UNSIGNED NULL COMMENT '對應 Capital Ledger 事件主鍵，供總帳與 Pool 子帳對帳',
    reference VARCHAR(190) NULL COMMENT '交易、銀行、業務或外部參考號',
    reversal_of_entry_id BIGINT UNSIGNED NULL COMMENT '若為沖銷事件，指向被沖銷的原 Pool Ledger Entry',
    created_by_user_id BIGINT UNSIGNED NULL COMMENT '建立此流水的一般使用者主鍵',
    created_by_super_admin_id BIGINT UNSIGNED NULL COMMENT '建立此流水的超級管理員主鍵',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '流水建立時間',
    PRIMARY KEY (id),
    UNIQUE KEY uq_pool_ledger_code (ledger_code),
    KEY idx_pool_ledger_pool_date (pool_id, business_date, event_type),
    KEY idx_pool_ledger_lot_date (source_lot_id, business_date),
    KEY idx_pool_ledger_loan (pool_loan_id),
    CONSTRAINT fk_pool_ledger_pool FOREIGN KEY (pool_id) REFERENCES capital_pools(id),
    CONSTRAINT fk_pool_ledger_lot FOREIGN KEY (source_lot_id) REFERENCES pool_source_lots(id),
    CONSTRAINT fk_pool_ledger_loan FOREIGN KEY (pool_loan_id) REFERENCES pool_loans(id),
    CONSTRAINT fk_pool_ledger_collection FOREIGN KEY (pool_loan_collection_id) REFERENCES pool_loan_collections(id),
    CONSTRAINT fk_pool_ledger_capital FOREIGN KEY (capital_ledger_id) REFERENCES capital_ledger(id),
    CONSTRAINT fk_pool_ledger_reversal FOREIGN KEY (reversal_of_entry_id) REFERENCES pool_ledger_entries(id),
    CONSTRAINT fk_pool_ledger_user FOREIGN KEY (created_by_user_id) REFERENCES sys_users(id),
    CONSTRAINT fk_pool_ledger_super FOREIGN KEY (created_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='Recycled Capital Pool 權威資金流水；Pool Balance 必須可由此表重算，不得只依賴 Cache';

-- ============================================================
-- 3. Source Lot Movement
-- ============================================================

CREATE TABLE IF NOT EXISTS pool_source_lot_movements (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool Source Lot 本金移動主鍵',
    movement_code VARCHAR(70) NOT NULL COMMENT 'Source Lot Movement 唯一編號',
    source_lot_id BIGINT UNSIGNED NOT NULL COMMENT '被追蹤的原始 Pool Source Lot',
    pool_loan_id BIGINT UNSIGNED NULL COMMENT '若本金移動與某 Pool Loan 有關，記錄其主鍵',
    pool_loan_allocation_id BIGINT UNSIGNED NULL COMMENT '若本金移動與某 Source Lot Allocation 有關，記錄其主鍵',
    pool_loan_collection_id BIGINT UNSIGNED NULL COMMENT '若移動源自部分或全部本金回收，記錄 Pool Loan Collection',
    movement_type VARCHAR(40) NOT NULL COMMENT '本金移動類型：TRANSFER_IN、ALLOCATED_TO_LOAN、PRINCIPAL_RECOVERED、REDEPLOYED、INVESTOR_REPAID、RESERVE_OUT、WRITE_OFF、ADJUSTMENT',
    business_date DATE NOT NULL COMMENT 'Source Lot 本金移動業務日期',
    principal_delta DECIMAL(19,2) NOT NULL COMMENT '本次 Source Lot 可用本金變化；增加為正、減少為負',
    available_after DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '此事件完成後 Source Lot 可再次部署本金餘額',
    deployed_after DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '此事件完成後 Source Lot 仍在外二次部署本金',
    recovered_lifetime_after DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '此事件完成後該 Source Lot 歷史累計本金回收額',
    reference VARCHAR(190) NULL COMMENT '本次本金移動參考說明',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '移動記錄建立時間',
    PRIMARY KEY (id),
    UNIQUE KEY uq_pool_lot_movement_code (movement_code),
    KEY idx_pool_lot_movement_lot (source_lot_id, business_date),
    KEY idx_pool_lot_movement_loan (pool_loan_id),
    CONSTRAINT fk_pool_lot_move_lot FOREIGN KEY (source_lot_id) REFERENCES pool_source_lots(id),
    CONSTRAINT fk_pool_lot_move_loan FOREIGN KEY (pool_loan_id) REFERENCES pool_loans(id),
    CONSTRAINT fk_pool_lot_move_alloc FOREIGN KEY (pool_loan_allocation_id) REFERENCES pool_loan_allocations(id),
    CONSTRAINT fk_pool_lot_move_collection FOREIGN KEY (pool_loan_collection_id) REFERENCES pool_loan_collections(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='逐筆追蹤每個 Source Lot 本金目前可用、在外、部分回收及再次部署狀態';

-- ============================================================
-- 4. Redeployment Risk Check
-- ============================================================

CREATE TABLE IF NOT EXISTS pool_redeployment_checks (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool 再部署風險檢查主鍵',
    check_code VARCHAR(70) NOT NULL COMMENT 'Pool 再部署風險檢查唯一編號',
    source_lot_id BIGINT UNSIGNED NOT NULL COMMENT '準備再次部署的 Source Lot',
    pool_loan_id BIGINT UNSIGNED NULL COMMENT '若已建立 Pool Loan，記錄其主鍵',
    check_date DATE NOT NULL COMMENT '重新評估是否可再部署的日期',
    investor_maturity_date DATE NOT NULL COMMENT '檢查當下所保存的 Investor 最終本金到期日快照',
    red_alert_date DATE NOT NULL COMMENT 'Investor maturity 往前 6 個月的紅色警報日期',
    days_to_maturity INT NOT NULL COMMENT 'check_date 距 Investor maturity 的剩餘日數；負數代表逾期',
    planned_disbursement_date DATE NULL COMMENT '新二次借貸預計撥款日期',
    planned_loan_maturity_date DATE NULL COMMENT '新二次借貸預計回收完成日期',
    threshold_months INT UNSIGNED NOT NULL DEFAULT 6 COMMENT '禁止新部署的到期警戒月數；目前標準為 6 個月',
    risk_status VARCHAR(20) NOT NULL COMMENT '本次檢查風險狀態 NORMAL、WATCH、RED、MATURED、OVERDUE',
    deployment_allowed TINYINT(1) NOT NULL DEFAULT 0 COMMENT '本次檢查是否允許新增二次部署；1允許0禁止',
    block_reason_zh VARCHAR(500) NULL COMMENT '禁止部署的中文原因',
    block_reason_en VARCHAR(500) NULL COMMENT '禁止部署的英文原因；英文介面使用',
    checked_by_user_id BIGINT UNSIGNED NULL COMMENT '執行檢查的一般使用者主鍵',
    checked_by_super_admin_id BIGINT UNSIGNED NULL COMMENT '執行檢查的超級管理員主鍵',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '檢查記錄建立時間',
    PRIMARY KEY (id),
    UNIQUE KEY uq_pool_redeploy_check_code (check_code),
    KEY idx_pool_redeploy_lot_date (source_lot_id, check_date),
    KEY idx_pool_redeploy_allowed (deployment_allowed, risk_status),
    CONSTRAINT fk_pool_redeploy_lot FOREIGN KEY (source_lot_id) REFERENCES pool_source_lots(id),
    CONSTRAINT fk_pool_redeploy_loan FOREIGN KEY (pool_loan_id) REFERENCES pool_loans(id),
    CONSTRAINT fk_pool_redeploy_user FOREIGN KEY (checked_by_user_id) REFERENCES sys_users(id),
    CONSTRAINT fk_pool_redeploy_super FOREIGN KEY (checked_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='每次 Source Lot 再次出借前的 Investor 到期及 6 個月紅線風險檢查快照';

-- ============================================================
-- 5. Source Lot Reuse Cycle
-- ============================================================

CREATE TABLE IF NOT EXISTS pool_source_lot_cycles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Pool Source Lot 循環利用週期主鍵',
    cycle_code VARCHAR(70) NOT NULL COMMENT 'Source Lot Cycle 唯一編號',
    source_lot_id BIGINT UNSIGNED NOT NULL COMMENT '被循環利用的原始 Source Lot',
    cycle_no INT UNSIGNED NOT NULL COMMENT '此 Source Lot 的第幾次實際部署 Cycle',
    pool_loan_id BIGINT UNSIGNED NOT NULL COMMENT '本 Cycle 對應的 Pool Loan',
    allocated_principal DECIMAL(19,2) NOT NULL COMMENT '本 Cycle 實際再次部署本金',
    deployed_at DATE NOT NULL COMMENT '本 Cycle 本金實際撥出日期',
    principal_recovered DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本 Cycle 累計已回收本金；允許部分回收',
    interest_generated DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本 Cycle 已實現二次利息收益',
    fee_generated DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '本 Cycle 已實現二次 Fee 收益',
    fully_recovered_at DATE NULL COMMENT '本 Cycle 本金全數回收日期；部分回收時保持空白',
    cycle_status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT 'Cycle 狀態 ACTIVE、PARTIAL、RECOVERED、CANCELLED、WRITTEN_OFF',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Cycle 建立時間',
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT 'Cycle 最後更新時間',
    PRIMARY KEY (id),
    UNIQUE KEY uq_pool_cycle_code (cycle_code),
    UNIQUE KEY uq_pool_lot_cycle_no (source_lot_id, cycle_no),
    KEY idx_pool_cycle_loan (pool_loan_id),
    KEY idx_pool_cycle_status (source_lot_id, cycle_status),
    CONSTRAINT fk_pool_cycle_lot FOREIGN KEY (source_lot_id) REFERENCES pool_source_lots(id),
    CONSTRAINT fk_pool_cycle_loan FOREIGN KEY (pool_loan_id) REFERENCES pool_loans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='記錄每個 Source Lot 第 N 次再部署的本金、部分回收與二次收益；支援無限循環利用';

-- ============================================================
-- 6. Investor Recycling Bonus Allocation
-- ============================================================

CREATE TABLE IF NOT EXISTS pool_investor_bonus_allocations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Investor Recycling Bonus 分配主鍵',
    bonus_code VARCHAR(70) NOT NULL COMMENT 'Recycling Bonus 唯一編號',
    source_lot_id BIGINT UNSIGNED NOT NULL COMMENT '此 Bonus 所對應的 Pool Source Lot',
    investor_subscription_id BIGINT UNSIGNED NOT NULL COMMENT '獲得額外 Bonus 的 Investor Subscription；不得改寫原合約收益',
    investor_id BIGINT UNSIGNED NOT NULL COMMENT '獲得額外 Bonus 的投資者主鍵',
    recycled_profit_available DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '建立 Bonus 時此 Source Lot 尚可分配的已實現 Recycling Profit 快照',
    bonus_method VARCHAR(20) NOT NULL COMMENT 'Bonus 方法：PERCENTAGE、FIXED、SPECIAL、LOYALTY、YEAR_END',
    bonus_rate DECIMAL(12,8) NULL COMMENT '若採比例分配，保存 Bonus 比率；例如 0.20 代表20%',
    bonus_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '經申請或批准的額外 Recycling Bonus 金額',
    capital_retained_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT '此 Bonus 決策後 Capital 保留的 Recycling Profit 金額快照',
    bonus_status VARCHAR(20) NOT NULL DEFAULT 'DRAFT' COMMENT 'Bonus 狀態 DRAFT、APPROVED、PARTIALLY_PAID、PAID、CANCELLED',
    approved_by_user_id BIGINT UNSIGNED NULL COMMENT '批准 Bonus 的一般使用者主鍵',
    approved_by_super_admin_id BIGINT UNSIGNED NULL COMMENT '批准 Bonus 的超級管理員主鍵',
    approved_at DATETIME(6) NULL COMMENT 'Bonus 批准時間',
    notes_zh VARCHAR(1000) NULL COMMENT 'Investor Recycling Bonus 中文說明',
    notes_en VARCHAR(1000) NULL COMMENT 'Investor Recycling Bonus 英文說明；英文介面使用',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Bonus 建立時間',
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT 'Bonus 最後更新時間',
    PRIMARY KEY (id),
    UNIQUE KEY uq_pool_bonus_code (bonus_code),
    KEY idx_pool_bonus_lot_status (source_lot_id, bonus_status),
    KEY idx_pool_bonus_subscription (investor_subscription_id, bonus_status),
    CONSTRAINT fk_pool_bonus_lot FOREIGN KEY (source_lot_id) REFERENCES pool_source_lots(id),
    CONSTRAINT fk_pool_bonus_subscription FOREIGN KEY (investor_subscription_id) REFERENCES investor_subscriptions(id),
    CONSTRAINT fk_pool_bonus_investor FOREIGN KEY (investor_id) REFERENCES investors(id),
    CONSTRAINT fk_pool_bonus_user FOREIGN KEY (approved_by_user_id) REFERENCES sys_users(id),
    CONSTRAINT fk_pool_bonus_super FOREIGN KEY (approved_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='Capital 從已實現 Recycled Capital Profit 中額外分配給 Investor 的非合約 Bonus；與 Contractual Return 永久分離';

-- ============================================================
-- 7. Investor Recycling Bonus Payment
-- ============================================================

CREATE TABLE IF NOT EXISTS pool_investor_bonus_payments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Investor Recycling Bonus 實際付款主鍵',
    payment_code VARCHAR(70) NOT NULL COMMENT 'Recycling Bonus Payment 唯一編號',
    bonus_allocation_id BIGINT UNSIGNED NOT NULL COMMENT '所屬 Recycling Bonus 分配主鍵',
    payment_date DATE NOT NULL COMMENT 'Bonus 實際支付日期',
    amount DECIMAL(19,2) NOT NULL COMMENT '本次實際支付 Bonus 金額；可支援分次付款',
    payment_reference VARCHAR(190) NULL COMMENT '銀行付款、交易或內部參考號',
    capital_ledger_id BIGINT UNSIGNED NULL COMMENT '對應 Capital Ledger 付款事件主鍵',
    created_by_user_id BIGINT UNSIGNED NULL COMMENT '登錄 Bonus 付款的一般使用者主鍵',
    created_by_super_admin_id BIGINT UNSIGNED NULL COMMENT '登錄 Bonus 付款的超級管理員主鍵',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Bonus 付款記錄建立時間',
    PRIMARY KEY (id),
    UNIQUE KEY uq_pool_bonus_payment_code (payment_code),
    KEY idx_pool_bonus_payment_alloc (bonus_allocation_id, payment_date),
    CONSTRAINT fk_pool_bonus_payment_alloc FOREIGN KEY (bonus_allocation_id) REFERENCES pool_investor_bonus_allocations(id),
    CONSTRAINT fk_pool_bonus_payment_ledger FOREIGN KEY (capital_ledger_id) REFERENCES capital_ledger(id),
    CONSTRAINT fk_pool_bonus_payment_user FOREIGN KEY (created_by_user_id) REFERENCES sys_users(id),
    CONSTRAINT fk_pool_bonus_payment_super FOREIGN KEY (created_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='Recycling Bonus 實際付款流水；支援部分付款並保持與 Investor 原合約收益分離';

-- ============================================================
-- 8. Upgrade pool_source_lots
-- ============================================================

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='eligibility_assessment_id'),
    'SELECT ''SKIP pool_source_lots.eligibility_assessment_id'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN eligibility_assessment_id BIGINT UNSIGNED NULL COMMENT ''產生此 Source Lot 的 Pool 資格評估主鍵'' AFTER original_collection_allocation_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='original_calculation_run_id'),
    'SELECT ''SKIP pool_source_lots.original_calculation_run_id'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN original_calculation_run_id BIGINT UNSIGNED NULL COMMENT ''原 Deal 回收與 Pool 資格判斷所對應的 Calculation Run'' AFTER eligibility_assessment_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='original_waterfall_schedule_id'),
    'SELECT ''SKIP pool_source_lots.original_waterfall_schedule_id'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN original_waterfall_schedule_id BIGINT UNSIGNED NULL COMMENT ''原本金回收所對應的 Waterfall Schedule 主鍵'' AFTER original_calculation_run_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='original_waterfall_line_id'),
    'SELECT ''SKIP pool_source_lots.original_waterfall_line_id'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN original_waterfall_line_id BIGINT UNSIGNED NULL COMMENT ''原本金回收月份所對應的 Waterfall 明細主鍵'' AFTER original_waterfall_schedule_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='source_capital_ledger_id'),
    'SELECT ''SKIP pool_source_lots.source_capital_ledger_id'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN source_capital_ledger_id BIGINT UNSIGNED NULL COMMENT ''正式形成此可再利用本金的 Capital Ledger 事件主鍵'' AFTER original_waterfall_line_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='red_alert_date'),
    'SELECT ''SKIP pool_source_lots.red_alert_date'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN red_alert_date DATE NULL COMMENT ''Investor maturity 往前 6 個月的紅色警報日期；到達後禁止新部署'' AFTER investor_maturity_date'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='total_deployed_amount'),
    'SELECT ''SKIP pool_source_lots.total_deployed_amount'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN total_deployed_amount DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT ''Source Lot 歷史累計部署本金；可超過原始本金，作為 Capital Turnover KPI Cache'' AFTER remaining_available_amount'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='total_principal_recovered'),
    'SELECT ''SKIP pool_source_lots.total_principal_recovered'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN total_principal_recovered DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT ''Source Lot 歷史累計從二次借貸回收的本金 Cache'' AFTER total_deployed_amount'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='total_secondary_interest'),
    'SELECT ''SKIP pool_source_lots.total_secondary_interest'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN total_secondary_interest DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT ''Source Lot 歷史累計已實現 Pool 二次利息收益 Cache'' AFTER total_principal_recovered'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND COLUMN_NAME='total_secondary_fee'),
    'SELECT ''SKIP pool_source_lots.total_secondary_fee'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD COLUMN total_secondary_fee DECIMAL(19,2) NOT NULL DEFAULT 0 COMMENT ''Source Lot 歷史累計已實現 Pool 二次 Fee 收益 Cache'' AFTER total_secondary_interest'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 9. Upgrade Pool Loan Allocation / Loan
-- ============================================================

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_loan_allocations' AND COLUMN_NAME='redeployment_check_id'),
    'SELECT ''SKIP pool_loan_allocations.redeployment_check_id'' AS migration_message',
    'ALTER TABLE pool_loan_allocations ADD COLUMN redeployment_check_id BIGINT UNSIGNED NULL COMMENT ''建立此 Allocation 前通過的 Redeployment Risk Check 主鍵'' AFTER source_lot_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_loan_allocations' AND COLUMN_NAME='cycle_id'),
    'SELECT ''SKIP pool_loan_allocations.cycle_id'' AS migration_message',
    'ALTER TABLE pool_loan_allocations ADD COLUMN cycle_id BIGINT UNSIGNED NULL COMMENT ''此 Allocation 對應 Source Lot 第幾輪循環利用 Cycle 主鍵'' AFTER redeployment_check_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_loan_allocations' AND COLUMN_NAME='investor_red_alert_date_snapshot'),
    'SELECT ''SKIP pool_loan_allocations.investor_red_alert_date_snapshot'' AS migration_message',
    'ALTER TABLE pool_loan_allocations ADD COLUMN investor_red_alert_date_snapshot DATE NULL COMMENT ''建立 Allocation 時 Investor 六個月紅線日期快照'' AFTER allocation_ratio'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_loan_allocations' AND COLUMN_NAME='investor_maturity_date_snapshot'),
    'SELECT ''SKIP pool_loan_allocations.investor_maturity_date_snapshot'' AS migration_message',
    'ALTER TABLE pool_loan_allocations ADD COLUMN investor_maturity_date_snapshot DATE NULL COMMENT ''建立 Allocation 時 Investor 最終本金到期日快照'' AFTER investor_red_alert_date_snapshot'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_loans' AND COLUMN_NAME='deployment_status'),
    'SELECT ''SKIP pool_loans.deployment_status'' AS migration_message',
    'ALTER TABLE pool_loans ADD COLUMN deployment_status VARCHAR(20) NOT NULL DEFAULT ''PLANNED'' COMMENT ''二次部署狀態 PLANNED、DISBURSED、PARTIAL_RECOVERY、RECOVERED、CANCELLED'' AFTER status'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_loans' AND COLUMN_NAME='risk_status'),
    'SELECT ''SKIP pool_loans.risk_status'' AS migration_message',
    'ALTER TABLE pool_loans ADD COLUMN risk_status VARCHAR(20) NOT NULL DEFAULT ''NORMAL'' COMMENT ''由所有 Source Lot 到期風險彙總出的 Loan 風險狀態 NORMAL、WATCH、RED、MATURED、OVERDUE'' AFTER deployment_status'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_loans' AND COLUMN_NAME='earliest_source_red_alert_date'),
    'SELECT ''SKIP pool_loans.earliest_source_red_alert_date'' AS migration_message',
    'ALTER TABLE pool_loans ADD COLUMN earliest_source_red_alert_date DATE NULL COMMENT ''供應此 Pool Loan 的 Source Lots 中最早紅色警報日期'' AFTER risk_status'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_loans' AND COLUMN_NAME='accelerated_recovery_flag'),
    'SELECT ''SKIP pool_loans.accelerated_recovery_flag'' AS migration_message',
    'ALTER TABLE pool_loans ADD COLUMN accelerated_recovery_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''是否已進入加速回收監控；任何 Source Lot 進入 RED 後應設為1'' AFTER earliest_source_red_alert_date'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 10. Upgrade Maturity Alert
-- ============================================================

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_maturity_alerts' AND COLUMN_NAME='alert_code'),
    'SELECT ''SKIP pool_maturity_alerts.alert_code'' AS migration_message',
    'ALTER TABLE pool_maturity_alerts ADD COLUMN alert_code VARCHAR(70) NULL COMMENT ''Pool 到期警報唯一編號'' AFTER id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_maturity_alerts' AND COLUMN_NAME='alert_status'),
    'SELECT ''SKIP pool_maturity_alerts.alert_status'' AS migration_message',
    'ALTER TABLE pool_maturity_alerts ADD COLUMN alert_status VARCHAR(20) NOT NULL DEFAULT ''OPEN'' COMMENT ''警報處理狀態 OPEN、ACKNOWLEDGED、CLEARED'' AFTER alert_level'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_maturity_alerts' AND COLUMN_NAME='required_action_date'),
    'SELECT ''SKIP pool_maturity_alerts.required_action_date'' AS migration_message',
    'ALTER TABLE pool_maturity_alerts ADD COLUMN required_action_date DATE NULL COMMENT ''要求管理層完成回收、退出或風險處理的期限'' AFTER days_to_maturity'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_maturity_alerts' AND COLUMN_NAME='acknowledged_at'),
    'SELECT ''SKIP pool_maturity_alerts.acknowledged_at'' AS migration_message',
    'ALTER TABLE pool_maturity_alerts ADD COLUMN acknowledged_at DATETIME(6) NULL COMMENT ''管理人員確認已閱讀並接手警報的時間'' AFTER action_owner_user_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_maturity_alerts' AND COLUMN_NAME='acknowledged_by_user_id'),
    'SELECT ''SKIP pool_maturity_alerts.acknowledged_by_user_id'' AS migration_message',
    'ALTER TABLE pool_maturity_alerts ADD COLUMN acknowledged_by_user_id BIGINT UNSIGNED NULL COMMENT ''確認警報的一般使用者主鍵'' AFTER acknowledged_at'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='pool_maturity_alerts' AND COLUMN_NAME='acknowledged_by_super_admin_id'),
    'SELECT ''SKIP pool_maturity_alerts.acknowledged_by_super_admin_id'' AS migration_message',
    'ALTER TABLE pool_maturity_alerts ADD COLUMN acknowledged_by_super_admin_id BIGINT UNSIGNED NULL COMMENT ''確認警報的超級管理員主鍵'' AFTER acknowledged_by_user_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 11. Foreign Keys for upgraded columns
-- ============================================================

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND CONSTRAINT_NAME='fk_pool_source_eligibility'),
    'SELECT ''SKIP fk_pool_source_eligibility'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD CONSTRAINT fk_pool_source_eligibility FOREIGN KEY (eligibility_assessment_id) REFERENCES pool_eligibility_assessments(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND CONSTRAINT_NAME='fk_pool_source_calc_run'),
    'SELECT ''SKIP fk_pool_source_calc_run'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD CONSTRAINT fk_pool_source_calc_run FOREIGN KEY (original_calculation_run_id) REFERENCES calculation_runs(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND CONSTRAINT_NAME='fk_pool_source_waterfall'),
    'SELECT ''SKIP fk_pool_source_waterfall'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD CONSTRAINT fk_pool_source_waterfall FOREIGN KEY (original_waterfall_schedule_id) REFERENCES waterfall_schedules(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND CONSTRAINT_NAME='fk_pool_source_waterfall_line'),
    'SELECT ''SKIP fk_pool_source_waterfall_line'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD CONSTRAINT fk_pool_source_waterfall_line FOREIGN KEY (original_waterfall_line_id) REFERENCES waterfall_schedule_lines(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db_name AND TABLE_NAME='pool_source_lots' AND CONSTRAINT_NAME='fk_pool_source_capital_ledger'),
    'SELECT ''SKIP fk_pool_source_capital_ledger'' AS migration_message',
    'ALTER TABLE pool_source_lots ADD CONSTRAINT fk_pool_source_capital_ledger FOREIGN KEY (source_capital_ledger_id) REFERENCES capital_ledger(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db_name AND TABLE_NAME='pool_loan_allocations' AND CONSTRAINT_NAME='fk_pool_alloc_redeploy_check'),
    'SELECT ''SKIP fk_pool_alloc_redeploy_check'' AS migration_message',
    'ALTER TABLE pool_loan_allocations ADD CONSTRAINT fk_pool_alloc_redeploy_check FOREIGN KEY (redeployment_check_id) REFERENCES pool_redeployment_checks(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db_name AND TABLE_NAME='pool_loan_allocations' AND CONSTRAINT_NAME='fk_pool_alloc_cycle'),
    'SELECT ''SKIP fk_pool_alloc_cycle'' AS migration_message',
    'ALTER TABLE pool_loan_allocations ADD CONSTRAINT fk_pool_alloc_cycle FOREIGN KEY (cycle_id) REFERENCES pool_source_lot_cycles(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db_name AND TABLE_NAME='pool_maturity_alerts' AND CONSTRAINT_NAME='fk_pool_alert_ack_user'),
    'SELECT ''SKIP fk_pool_alert_ack_user'' AS migration_message',
    'ALTER TABLE pool_maturity_alerts ADD CONSTRAINT fk_pool_alert_ack_user FOREIGN KEY (acknowledged_by_user_id) REFERENCES sys_users(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db_name AND TABLE_NAME='pool_maturity_alerts' AND CONSTRAINT_NAME='fk_pool_alert_ack_super'),
    'SELECT ''SKIP fk_pool_alert_ack_super'' AS migration_message',
    'ALTER TABLE pool_maturity_alerts ADD CONSTRAINT fk_pool_alert_ack_super FOREIGN KEY (acknowledged_by_super_admin_id) REFERENCES sys_super_admins(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 12. Pool Permissions
-- ============================================================

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_VIEW','POOL','FUNCTION','查看 Recycled Capital Pool','View Recycled Capital Pool','允許查看 Pool、Source Lot、二次借貸、收益及風險資料','Allows viewing pool, source lots, redeployments, returns and risk data',1,300,NOW(6)),
('POOL_ELIGIBILITY_MANAGE','POOL','ACTION','管理 Pool 資格評估','Manage Pool Eligibility','允許建立及批准回收本金進入 Pool 的資格評估','Allows creation and approval of recycled capital eligibility assessments',1,310,NOW(6)),
('POOL_TRANSFER_MANAGE','POOL','ACTION','管理資金轉入 Pool','Manage Pool Transfers','允許將已批准可再利用本金正式轉入 Pool','Allows approved reusable principal to be transferred into the Pool',1,320,NOW(6)),
('POOL_REDEPLOY_MANAGE','POOL','ACTION','管理 Pool 再部署','Manage Pool Redeployment','允許建立 Pool Loan、Source Lot Allocation 及再部署風險檢查','Allows Pool loans, source lot allocations and redeployment risk checks',1,330,NOW(6)),
('POOL_COLLECTION_MANAGE','POOL','ACTION','管理 Pool 二次回款','Manage Pool Collections','允許登錄部分或全部本金、利息及 Fee 回收','Allows posting partial or full principal, interest and fee collections',1,340,NOW(6)),
('POOL_BONUS_MANAGE','POOL','ACTION','管理 Investor Recycling Bonus','Manage Investor Recycling Bonus','允許從已實現 Recycling Profit 中建立及管理額外 Investor Bonus','Allows discretionary investor bonus allocations from realized recycling profit',1,350,NOW(6)),
('POOL_BONUS_APPROVE','POOL','ACTION','批准 Investor Recycling Bonus','Approve Investor Recycling Bonus','允許批准額外 Bonus；不得改寫投資者原合約收益','Allows approval of discretionary bonus without changing contractual return',1,360,NOW(6)),
('POOL_RISK_VIEW','POOL','REPORT','查看 Pool 到期風險','View Pool Maturity Risk','允許查看 6 個月紅色警報、加速回收及 Investor 到期風險','Allows viewing six-month red alerts, accelerated recovery and investor maturity risk',1,370,NOW(6)),
('POOL_PROFIT_VIEW','POOL','REPORT','查看 Pool Recycling Profit','View Pool Recycling Profit','允許查看 Gross Recycling Profit、Investor Bonus 與 Capital Retained Profit','Allows viewing gross recycling profit, investor bonus and retained capital profit',1,380,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);

-- ============================================================
-- 13. Verification
-- ============================================================

SELECT 'VERIFY NEW POOL TABLES' AS verify_section;

SELECT TABLE_NAME,TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA=@db_name
AND TABLE_NAME IN (
'pool_eligibility_assessments',
'pool_ledger_entries',
'pool_source_lot_movements',
'pool_redeployment_checks',
'pool_source_lot_cycles',
'pool_investor_bonus_allocations',
'pool_investor_bonus_payments'
)
ORDER BY TABLE_NAME;

SELECT 'VERIFY SOURCE LOT UPGRADE' AS verify_section;

SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA=@db_name
AND TABLE_NAME='pool_source_lots'
AND COLUMN_NAME IN (
'eligibility_assessment_id',
'original_calculation_run_id',
'original_waterfall_schedule_id',
'original_waterfall_line_id',
'source_capital_ledger_id',
'red_alert_date',
'total_deployed_amount',
'total_principal_recovered',
'total_secondary_interest',
'total_secondary_fee'
)
ORDER BY ORDINAL_POSITION;

SELECT 'VERIFY POOL PERMISSIONS' AS verify_section;

SELECT permission_code,name_zh,name_en
FROM permission_catalog
WHERE permission_code LIKE 'POOL_%'
ORDER BY sort_order,permission_code;

SELECT 'iBilik Capital Database v2.3 Recycled Capital Pool Core migration completed.' AS migration_status;

-- ============================================================
-- END
-- ============================================================
