-- ============================================================
-- iBilik Capital Database v2.5
-- Phase 7B - Accounting Foundation
-- Target: MySQL 5.7.44
-- Baseline: Database v2.4
-- 所有新增 TABLE / COLUMN 均使用中文 COMMENT
-- ============================================================

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

-- ============================================================
-- 1. bank_accounts -> GL Account Mapping
-- ============================================================

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA=@db_name
          AND TABLE_NAME='bank_accounts'
          AND COLUMN_NAME='gl_account_id'
    ),
    'SELECT ''SKIP bank_accounts.gl_account_id'' AS migration_message',
    'ALTER TABLE bank_accounts
       ADD COLUMN gl_account_id BIGINT UNSIGNED NULL
       COMMENT ''此銀行帳戶對應的總帳現金科目；供自動會計過帳判斷 Dr/Cr Cash Account''
       AFTER active_flag'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA=@db_name
          AND TABLE_NAME='bank_accounts'
          AND INDEX_NAME='idx_bank_gl_account'
    ),
    'SELECT ''SKIP idx_bank_gl_account'' AS migration_message',
    'ALTER TABLE bank_accounts ADD KEY idx_bank_gl_account (gl_account_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='bank_accounts'
          AND CONSTRAINT_NAME='fk_bank_gl_account'
          AND CONSTRAINT_TYPE='FOREIGN KEY'
    ),
    'SELECT ''SKIP fk_bank_gl_account'' AS migration_message',
    'ALTER TABLE bank_accounts
       ADD CONSTRAINT fk_bank_gl_account
       FOREIGN KEY (gl_account_id) REFERENCES gl_accounts(id)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================
-- 2. Journal / Period Super Admin Attribution
-- ============================================================

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA=@db_name
          AND TABLE_NAME='journal_entries'
          AND COLUMN_NAME='posted_by_super_admin_id'
    ),
    'SELECT ''SKIP journal_entries.posted_by_super_admin_id'' AS migration_message',
    'ALTER TABLE journal_entries
       ADD COLUMN posted_by_super_admin_id BIGINT UNSIGNED NULL
       COMMENT ''由超級管理員執行正式過帳時的帳戶主鍵''
       AFTER posted_by_user_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA=@db_name
          AND TABLE_NAME='journal_entries'
          AND INDEX_NAME='idx_journal_poster_super'
    ),
    'SELECT ''SKIP idx_journal_poster_super'' AS migration_message',
    'ALTER TABLE journal_entries ADD KEY idx_journal_poster_super (posted_by_super_admin_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='journal_entries'
          AND CONSTRAINT_NAME='fk_journal_poster_super'
          AND CONSTRAINT_TYPE='FOREIGN KEY'
    ),
    'SELECT ''SKIP fk_journal_poster_super'' AS migration_message',
    'ALTER TABLE journal_entries
       ADD CONSTRAINT fk_journal_poster_super
       FOREIGN KEY (posted_by_super_admin_id) REFERENCES sys_super_admins(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='reporting_periods'
          AND COLUMN_NAME='closed_by_super_admin_id'
    ),
    'SELECT ''SKIP reporting_periods.closed_by_super_admin_id'' AS migration_message',
    'ALTER TABLE reporting_periods
       ADD COLUMN closed_by_super_admin_id BIGINT UNSIGNED NULL
       COMMENT ''由超級管理員執行 SOFT CLOSE 或正式關帳時的帳戶主鍵''
       AFTER closed_by_user_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='reporting_periods'
          AND CONSTRAINT_NAME='fk_period_closer_super'
          AND CONSTRAINT_TYPE='FOREIGN KEY'
    ),
    'SELECT ''SKIP fk_period_closer_super'' AS migration_message',
    'ALTER TABLE reporting_periods
       ADD CONSTRAINT fk_period_closer_super
       FOREIGN KEY (closed_by_super_admin_id) REFERENCES sys_super_admins(id)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================
-- 3. Accounting Event Mapping
-- ============================================================

CREATE TABLE IF NOT EXISTS accounting_event_mappings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '會計事件映射主鍵',
    mapping_code VARCHAR(80) NOT NULL COMMENT '會計事件映射唯一代碼',
    source_module VARCHAR(40) NOT NULL COMMENT '來源模組 INVESTOR、DEAL、COLLECTION、POOL、BONUS、FUNDER 等',
    source_type VARCHAR(60) NOT NULL COMMENT '來源業務資料類型，例如 INVESTOR_CASH_RECEIPT、POOL_LOAN_COLLECTION',
    event_type VARCHAR(60) NOT NULL COMMENT '會計事件類型，例如 PRINCIPAL、INTEREST、FEE、BONUS_APPROVAL',
    debit_gl_account_id BIGINT UNSIGNED NULL COMMENT '固定借方總帳科目；若需依銀行帳戶或產品動態解析可為空',
    credit_gl_account_id BIGINT UNSIGNED NULL COMMENT '固定貸方總帳科目；若需依銀行帳戶或產品動態解析可為空',
    debit_account_role VARCHAR(40) NULL COMMENT '動態借方科目角色，例如 BANK_ACCOUNT、PRODUCT_INCOME、POOL_BANK',
    credit_account_role VARCHAR(40) NULL COMMENT '動態貸方科目角色，例如 BANK_ACCOUNT、PRODUCT_INCOME、POOL_BANK',
    amount_source VARCHAR(30) NOT NULL DEFAULT 'TOTAL' COMMENT '金額來源 PRINCIPAL、INTEREST、FEE、BONUS、TOTAL、CASH',
    currency_mode VARCHAR(20) NOT NULL DEFAULT 'SOURCE' COMMENT '幣別來源 SOURCE 或 FIXED',
    fixed_currency CHAR(3) NULL COMMENT 'currency_mode=FIXED 時使用的固定幣別',
    description_zh VARCHAR(500) NOT NULL COMMENT '自動傳票中文說明模板',
    description_en VARCHAR(500) NULL 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),
    UNIQUE KEY uq_accounting_event_mapping (source_type,event_type),
    KEY idx_accounting_mapping_module (source_module,active_flag),
    CONSTRAINT fk_accounting_mapping_debit FOREIGN KEY (debit_gl_account_id) REFERENCES gl_accounts(id),
    CONSTRAINT fk_accounting_mapping_credit FOREIGN KEY (credit_gl_account_id) REFERENCES gl_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='業務事件到 General Ledger 借貸科目的可配置映射；避免會計科目硬寫死於 PHP';

-- ============================================================
-- 4. Accounting Posting Run
-- ============================================================

CREATE TABLE IF NOT EXISTS accounting_posting_runs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自動會計過帳批次主鍵',
    run_code VARCHAR(70) NOT NULL COMMENT 'Accounting Posting Run 唯一編號',
    business_date DATE NOT NULL COMMENT '本次過帳使用的業務日期',
    source_module VARCHAR(40) NOT NULL COMMENT '本次掃描來源模組，例如 INVESTOR、DEAL、POOL、ALL',
    execution_source VARCHAR(20) NOT NULL DEFAULT 'MANUAL' COMMENT '執行來源 MANUAL 或 CRON',
    status VARCHAR(20) NOT NULL DEFAULT 'RUNNING' COMMENT '過帳批次狀態 RUNNING、COMPLETED、FAILED',
    events_scanned INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次掃描到的可處理業務事件數量',
    journals_created INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次成功建立並正式過帳的傳票數量',
    events_skipped INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '因已過帳、金額為零或不符合條件而跳過的事件數量',
    errors_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_accounting_posting_run_code (run_code),
    KEY idx_accounting_posting_run_date (business_date,status,source_module),
    CONSTRAINT fk_accounting_post_run_user FOREIGN KEY (executed_by_user_id) REFERENCES sys_users(id),
    CONSTRAINT fk_accounting_post_run_super FOREIGN KEY (executed_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='自動掃描業務事件並建立正式會計傳票的批次執行紀錄';

-- ============================================================
-- 5. Accounting Source Posting / Idempotency
-- ============================================================

CREATE TABLE IF NOT EXISTS accounting_source_postings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '業務來源與會計傳票對應主鍵',
    source_type VARCHAR(60) NOT NULL COMMENT '原始業務資料類型，例如 INVESTOR_CASH_RECEIPT、DEAL_COLLECTION、POOL_COLLECTION',
    source_id BIGINT UNSIGNED NOT NULL COMMENT '原始業務資料主鍵',
    event_type VARCHAR(60) NOT NULL COMMENT '同一業務資料內的會計事件類型，例如 PRINCIPAL、INTEREST、FEE、BONUS',
    journal_entry_id BIGINT UNSIGNED NOT NULL COMMENT '此業務事件產生的正式 Journal Entry 主鍵',
    posting_run_id BIGINT UNSIGNED NULL COMMENT '若由自動批次建立，記錄 Accounting Posting Run',
    posting_status VARCHAR(20) NOT NULL DEFAULT 'POSTED' COMMENT '過帳狀態 POSTED、REVERSED',
    active_marker TINYINT NULL DEFAULT 1 COMMENT '防重複控制；目前有效過帳為1，已沖銷改為NULL，Unique Key 允許保留多筆歷史沖銷',
    posted_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '業務事件正式過帳時間',
    reversed_by_source_posting_id BIGINT UNSIGNED NULL COMMENT '若此過帳已沖銷，記錄新的沖銷 Source Posting 主鍵',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '資料建立時間',
    PRIMARY KEY (id),
    UNIQUE KEY uq_accounting_source_active (source_type,source_id,event_type,active_marker),
    UNIQUE KEY uq_accounting_source_journal (journal_entry_id),
    KEY idx_accounting_source_lookup (source_type,source_id,event_type,posting_status),
    KEY idx_accounting_source_run (posting_run_id),
    CONSTRAINT fk_accounting_source_journal FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id),
    CONSTRAINT fk_accounting_source_run FOREIGN KEY (posting_run_id) REFERENCES accounting_posting_runs(id),
    CONSTRAINT fk_accounting_source_reversal FOREIGN KEY (reversed_by_source_posting_id) REFERENCES accounting_source_postings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='防止同一業務事件重複過帳並將每筆 General Ledger 傳票追回原始業務資料';

-- ============================================================
-- 6. Accounting Reconciliation Run
-- ============================================================

CREATE TABLE IF NOT EXISTS accounting_reconciliation_runs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '會計自動對帳批次主鍵',
    run_code VARCHAR(70) NOT NULL COMMENT 'Accounting Reconciliation Run 唯一編號',
    reconciliation_date DATE NOT NULL COMMENT '本次對帳業務日期',
    reconciliation_type VARCHAR(30) NOT NULL DEFAULT 'FULL' COMMENT '對帳類型 FULL、BANK、INVESTOR、RECEIVABLE、RECYCLING',
    execution_source VARCHAR(20) NOT NULL DEFAULT 'MANUAL' COMMENT '執行來源 MANUAL 或 CRON',
    status VARCHAR(20) NOT NULL DEFAULT 'RUNNING' COMMENT '對帳狀態 RUNNING、COMPLETED、FAILED',
    checks_run INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '本次執行的對帳檢查數量',
    issues_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 '批次失敗時的安全錯誤摘要',
    PRIMARY KEY (id),
    UNIQUE KEY uq_accounting_recon_run_code (run_code),
    KEY idx_accounting_recon_run_date (reconciliation_date,status,reconciliation_type),
    CONSTRAINT fk_accounting_recon_run_user FOREIGN KEY (executed_by_user_id) REFERENCES sys_users(id),
    CONSTRAINT fk_accounting_recon_run_super FOREIGN KEY (executed_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='Bank、Investor Liability、Receivable、Recycling Profit 與 General Ledger 的自動對帳批次紀錄';

-- ============================================================
-- 7. Accounting Reconciliation Issues
-- ============================================================

CREATE TABLE IF NOT EXISTS accounting_reconciliation_issues (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '會計對帳差異主鍵',
    issue_code VARCHAR(70) NOT NULL COMMENT '會計對帳差異唯一編號',
    reconciliation_run_id BIGINT UNSIGNED NOT NULL COMMENT '發現此差異的 Accounting Reconciliation Run',
    issue_type VARCHAR(60) NOT NULL COMMENT '差異類型 BANK_CASH、INVESTOR_PRINCIPAL、INVESTOR_RETURN、RECEIVABLE、RECYCLING_INCOME、BONUS_PAYABLE 等',
    severity VARCHAR(20) NOT NULL DEFAULT 'WARNING' COMMENT '差異嚴重度 INFO、WARNING、CRITICAL',
    entity_type VARCHAR(60) NULL COMMENT '差異關聯實體類型，例如 BANK_ACCOUNT、INVESTOR、DEAL、POOL',
    entity_id BIGINT UNSIGNED NULL COMMENT '差異關聯實體主鍵',
    gl_account_id BIGINT UNSIGNED NULL COMMENT '若差異可對應單一 GL Account，記錄其主鍵',
    expected_amount DECIMAL(19,2) NULL COMMENT '由 Business/Capital Ledger 推導的預期金額',
    gl_amount DECIMAL(19,2) NULL COMMENT 'General Ledger 對應金額',
    difference_amount DECIMAL(19,2) NULL COMMENT 'GL 與預期金額之間的差額',
    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_accounting_recon_issue_code (issue_code),
    KEY idx_accounting_recon_issue_run (reconciliation_run_id,issue_status,severity),
    KEY idx_accounting_recon_issue_gl (gl_account_id,issue_status),
    KEY idx_accounting_recon_issue_entity (entity_type,entity_id,issue_status),
    CONSTRAINT fk_accounting_recon_issue_run FOREIGN KEY (reconciliation_run_id) REFERENCES accounting_reconciliation_runs(id),
    CONSTRAINT fk_accounting_recon_issue_gl FOREIGN KEY (gl_account_id) REFERENCES gl_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COMMENT='Business Ledger、Capital Ledger 與 General Ledger 之間的自動對帳差異紀錄';

-- ============================================================
-- 8. Chart of Accounts Seed
-- Existing gl_accounts structure is reused.
-- ============================================================

INSERT INTO gl_accounts (account_code,name_zh,name_en,account_type,parent_id,active_flag)
VALUES
('1000','資產','Assets','ASSET',NULL,1),
('2000','負債','Liabilities','LIABILITY',NULL,1),
('3000','權益','Equity','EQUITY',NULL,1),
('4000','收入','Income','INCOME',NULL,1),
('5000','費用','Expenses','EXPENSE',NULL,1)
ON DUPLICATE KEY UPDATE
name_zh=VALUES(name_zh),name_en=VALUES(name_en),account_type=VALUES(account_type),active_flag=1;

INSERT INTO gl_accounts (account_code,name_zh,name_en,account_type,parent_id,active_flag)
SELECT '1100','現金與銀行','Cash & Bank','ASSET',id,1 FROM gl_accounts WHERE account_code='1000'
UNION ALL SELECT '1200','融資應收','Financing Receivables','ASSET',id,1 FROM gl_accounts WHERE account_code='1000'
UNION ALL SELECT '1300','其他資產','Other Assets','ASSET',id,1 FROM gl_accounts WHERE account_code='1000'
UNION ALL SELECT '2100','投資者負債','Investor Liabilities','LIABILITY',id,1 FROM gl_accounts WHERE account_code='2000'
UNION ALL SELECT '2200','其他負債','Other Liabilities','LIABILITY',id,1 FROM gl_accounts WHERE account_code='2000'
UNION ALL SELECT '3100','股本','Share Capital','EQUITY',id,1 FROM gl_accounts WHERE account_code='3000'
UNION ALL SELECT '3200','保留盈餘','Retained Earnings','EQUITY',id,1 FROM gl_accounts WHERE account_code='3000'
UNION ALL SELECT '3300','本年度損益','Current Year Earnings','EQUITY',id,1 FROM gl_accounts WHERE account_code='3000'
UNION ALL SELECT '4100','原金融產品收入','Original Product Income','INCOME',id,1 FROM gl_accounts WHERE account_code='4000'
UNION ALL SELECT '4200','Pool 循環資金收入','Recycling Income','INCOME',id,1 FROM gl_accounts WHERE account_code='4000'
UNION ALL SELECT '4300','其他收入','Other Income','INCOME',id,1 FROM gl_accounts WHERE account_code='4000'
UNION ALL SELECT '5100','資金與投資者成本','Funding / Investor Cost','EXPENSE',id,1 FROM gl_accounts WHERE account_code='5000'
UNION ALL SELECT '5200','Pool 額外分配','Recycling Distribution','EXPENSE',id,1 FROM gl_accounts WHERE account_code='5000'
UNION ALL SELECT '5300','營運費用','Operating Expenses','EXPENSE',id,1 FROM gl_accounts WHERE account_code='5000'
ON DUPLICATE KEY UPDATE
name_zh=VALUES(name_zh),name_en=VALUES(name_en),account_type=VALUES(account_type),parent_id=VALUES(parent_id),active_flag=1;

INSERT INTO gl_accounts (account_code,name_zh,name_en,account_type,parent_id,active_flag)
SELECT '1110','銀行－營運','Bank - Operating','ASSET',id,1 FROM gl_accounts WHERE account_code='1100'
UNION ALL SELECT '1120','銀行－回款','Bank - Collection','ASSET',id,1 FROM gl_accounts WHERE account_code='1100'
UNION ALL SELECT '1130','銀行－投資者資金','Bank - Investor Funding','ASSET',id,1 FROM gl_accounts WHERE account_code='1100'
UNION ALL SELECT '1140','銀行－Pool','Bank - Pool','ASSET',id,1 FROM gl_accounts WHERE account_code='1100'
UNION ALL SELECT '1210','Deal 本金應收','Deal Principal Receivable','ASSET',id,1 FROM gl_accounts WHERE account_code='1200'
UNION ALL SELECT '1220','Pool Loan 本金應收','Pool Loan Principal Receivable','ASSET',id,1 FROM gl_accounts WHERE account_code='1200'
UNION ALL SELECT '1230','利息應收','Interest Receivable','ASSET',id,1 FROM gl_accounts WHERE account_code='1200'
UNION ALL SELECT '1240','費用應收','Fee Receivable','ASSET',id,1 FROM gl_accounts WHERE account_code='1200'
UNION ALL SELECT '1310','未分配現金','Unallocated Cash','ASSET',id,1 FROM gl_accounts WHERE account_code='1300'
UNION ALL SELECT '1320','暫記應收','Suspense Receivable','ASSET',id,1 FROM gl_accounts WHERE account_code='1300'
UNION ALL SELECT '2110','投資者本金應付','Investor Principal Payable','LIABILITY',id,1 FROM gl_accounts WHERE account_code='2100'
UNION ALL SELECT '2120','投資者原合約收益應付','Investor Contractual Return Payable','LIABILITY',id,1 FROM gl_accounts WHERE account_code='2100'
UNION ALL SELECT '2130','Investor Recycling Bonus 應付','Investor Recycling Bonus Payable','LIABILITY',id,1 FROM gl_accounts WHERE account_code='2100'
UNION ALL SELECT '2210','應計費用','Accrued Expenses','LIABILITY',id,1 FROM gl_accounts WHERE account_code='2200'
UNION ALL SELECT '2220','暫記應付','Suspense Payable','LIABILITY',id,1 FROM gl_accounts WHERE account_code='2200'
UNION ALL SELECT '4110','Advance Profit 收入','Advance Profit Income','INCOME',id,1 FROM gl_accounts WHERE account_code='4100'
UNION ALL SELECT '4120','CAPEX Funding 收入','CAPEX Funding Income','INCOME',id,1 FROM gl_accounts WHERE account_code='4100'
UNION ALL SELECT '4130','Revenue Management 收入','Revenue Management Income','INCOME',id,1 FROM gl_accounts WHERE account_code='4100'
UNION ALL SELECT '4140','Flexible Forward Rental 收入','Flexible Forward Rental Income','INCOME',id,1 FROM gl_accounts WHERE account_code='4100'
UNION ALL SELECT '4210','Pool 二次利息收入','Pool Interest Income','INCOME',id,1 FROM gl_accounts WHERE account_code='4200'
UNION ALL SELECT '4220','Pool 二次 Fee 收入','Pool Fee Income','INCOME',id,1 FROM gl_accounts WHERE account_code='4200'
UNION ALL SELECT '5110','投資者原合約收益費用','Investor Contractual Return Expense','EXPENSE',id,1 FROM gl_accounts WHERE account_code='5100'
UNION ALL SELECT '5120','外部資金方利息費用','Funder Interest Expense','EXPENSE',id,1 FROM gl_accounts WHERE account_code='5100'
UNION ALL SELECT '5210','Investor Recycling Bonus 費用','Investor Recycling Bonus Expense','EXPENSE',id,1 FROM gl_accounts WHERE account_code='5200'
UNION ALL SELECT '5310','銀行費用','Bank Charges','EXPENSE',id,1 FROM gl_accounts WHERE account_code='5300'
UNION ALL SELECT '5320','法律與文件費用','Legal / Documentation','EXPENSE',id,1 FROM gl_accounts WHERE account_code='5300'
UNION ALL SELECT '5330','回收成本','Collection Cost','EXPENSE',id,1 FROM gl_accounts WHERE account_code='5300'
ON DUPLICATE KEY UPDATE
name_zh=VALUES(name_zh),name_en=VALUES(name_en),account_type=VALUES(account_type),parent_id=VALUES(parent_id),active_flag=1;

-- ============================================================
-- 9. Core Accounting Event Mapping Seed
-- ============================================================

INSERT INTO accounting_event_mappings
(mapping_code,source_module,source_type,event_type,debit_gl_account_id,credit_gl_account_id,
 debit_account_role,credit_account_role,amount_source,description_zh,description_en,active_flag)
SELECT 'MAP_INVESTOR_CASH_IN','INVESTOR','INVESTOR_CASH_RECEIPT','PRINCIPAL',
       NULL,(SELECT id FROM gl_accounts WHERE account_code='2110'),
       'BANK_ACCOUNT',NULL,'PRINCIPAL',
       '投資者實際入金：借銀行，貸投資者本金應付',
       'Investor cash-in: debit bank, credit investor principal payable',1
UNION ALL
SELECT 'MAP_DEAL_DISBURSEMENT','DEAL','DEAL_DISBURSEMENT','PRINCIPAL',
       (SELECT id FROM gl_accounts WHERE account_code='1210'),NULL,
       NULL,'BANK_ACCOUNT','PRINCIPAL',
       'Deal 實際撥款：借 Deal 本金應收，貸銀行',
       'Deal disbursement: debit deal principal receivable, credit bank',1
UNION ALL
SELECT 'MAP_DEAL_COLLECTION_PRINCIPAL','COLLECTION','DEAL_COLLECTION','PRINCIPAL',
       NULL,(SELECT id FROM gl_accounts WHERE account_code='1210'),
       'BANK_ACCOUNT',NULL,'PRINCIPAL',
       'Deal 本金回收：借銀行，貸 Deal 本金應收',
       'Deal principal recovery: debit bank, credit deal principal receivable',1
UNION ALL
SELECT 'MAP_DEAL_COLLECTION_INTEREST','COLLECTION','DEAL_COLLECTION','INTEREST',
       NULL,NULL,'BANK_ACCOUNT','PRODUCT_INCOME','INTEREST',
       'Deal 利息或產品收益回收：借銀行，貸對應產品收入',
       'Deal interest/product income collection: debit bank, credit product income',1
UNION ALL
SELECT 'MAP_DEAL_COLLECTION_FEE','COLLECTION','DEAL_COLLECTION','FEE',
       NULL,NULL,'BANK_ACCOUNT','PRODUCT_INCOME','FEE',
       'Deal Fee 回收：借銀行，貸對應產品收入',
       'Deal fee collection: debit bank, credit product income',1
UNION ALL
SELECT 'MAP_INVESTOR_RETURN_ACCRUAL','INVESTOR','INVESTOR_RETURN_SCHEDULE','RETURN_ACCRUAL',
       (SELECT id FROM gl_accounts WHERE account_code='5110'),
       (SELECT id FROM gl_accounts WHERE account_code='2120'),
       NULL,NULL,'TOTAL',
       '投資者原合約收益應計：借原合約收益費用，貸原合約收益應付',
       'Investor contractual return accrual: debit return expense, credit return payable',1
UNION ALL
SELECT 'MAP_INVESTOR_PRINCIPAL_REPAYMENT','INVESTOR','INVESTOR_PRINCIPAL_REPAYMENT','PRINCIPAL',
       (SELECT id FROM gl_accounts WHERE account_code='2110'),NULL,
       NULL,'BANK_ACCOUNT','PRINCIPAL',
       '歸還投資者本金：借投資者本金應付，貸銀行',
       'Investor principal repayment: debit investor principal payable, credit bank',1
UNION ALL
SELECT 'MAP_POOL_LOAN_DEPLOYMENT','POOL','POOL_LOAN','PRINCIPAL',
       (SELECT id FROM gl_accounts WHERE account_code='1220'),NULL,
       NULL,'POOL_BANK','PRINCIPAL',
       'Pool 再借貸：借 Pool Loan 本金應收，貸 Pool 銀行',
       'Pool redeployment: debit pool loan principal receivable, credit pool bank',1
UNION ALL
SELECT 'MAP_POOL_COLLECTION_PRINCIPAL','POOL','POOL_LOAN_COLLECTION','PRINCIPAL',
       NULL,(SELECT id FROM gl_accounts WHERE account_code='1220'),
       'POOL_BANK',NULL,'PRINCIPAL',
       'Pool Loan 本金回收：借 Pool 銀行，貸 Pool Loan 本金應收',
       'Pool principal recovery: debit pool bank, credit pool loan principal receivable',1
UNION ALL
SELECT 'MAP_POOL_COLLECTION_INTEREST','POOL','POOL_LOAN_COLLECTION','INTEREST',
       NULL,(SELECT id FROM gl_accounts WHERE account_code='4210'),
       'POOL_BANK',NULL,'INTEREST',
       'Pool 二次利息回收：借 Pool 銀行，貸 Pool 二次利息收入',
       'Pool secondary interest: debit pool bank, credit pool interest income',1
UNION ALL
SELECT 'MAP_POOL_COLLECTION_FEE','POOL','POOL_LOAN_COLLECTION','FEE',
       NULL,(SELECT id FROM gl_accounts WHERE account_code='4220'),
       'POOL_BANK',NULL,'FEE',
       'Pool 二次 Fee 回收：借 Pool 銀行，貸 Pool 二次 Fee 收入',
       'Pool secondary fee: debit pool bank, credit pool fee income',1
UNION ALL
SELECT 'MAP_POOL_BONUS_APPROVAL','BONUS','POOL_INVESTOR_BONUS_ALLOCATION','BONUS_APPROVAL',
       (SELECT id FROM gl_accounts WHERE account_code='5210'),
       (SELECT id FROM gl_accounts WHERE account_code='2130'),
       NULL,NULL,'BONUS',
       '批准 Investor Recycling Bonus：借 Bonus 費用，貸 Bonus 應付',
       'Approve investor recycling bonus: debit bonus expense, credit bonus payable',1
UNION ALL
SELECT 'MAP_POOL_BONUS_PAYMENT','BONUS','POOL_INVESTOR_BONUS_PAYMENT','BONUS_PAYMENT',
       (SELECT id FROM gl_accounts WHERE account_code='2130'),NULL,
       NULL,'BANK_ACCOUNT','BONUS',
       '支付 Investor Recycling Bonus：借 Bonus 應付，貸銀行',
       'Pay investor recycling bonus: debit bonus payable, credit bank',1
ON DUPLICATE KEY UPDATE
source_module=VALUES(source_module),
debit_gl_account_id=VALUES(debit_gl_account_id),
credit_gl_account_id=VALUES(credit_gl_account_id),
debit_account_role=VALUES(debit_account_role),
credit_account_role=VALUES(credit_account_role),
amount_source=VALUES(amount_source),
description_zh=VALUES(description_zh),
description_en=VALUES(description_en),
active_flag=1;

-- ============================================================
-- 10. Accounting 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
('ACCOUNTING_CHART_VIEW','ACCOUNTING','FUNCTION','查看會計科目表','View Chart of Accounts','允許查看 iBilik Capital Chart of Accounts','Allows viewing the iBilik Capital chart of accounts',1,500,NOW(6)),
('ACCOUNTING_JOURNAL_VIEW','ACCOUNTING','FUNCTION','查看會計傳票','View Journals','允許查看 Journal Entry 與借貸明細','Allows viewing journal entries and debit/credit lines',1,510,NOW(6)),
('ACCOUNTING_JOURNAL_POST','ACCOUNTING','ACTION','正式過帳會計傳票','Post Journals','允許將平衡且位於開放期間的傳票正式過帳','Allows posting balanced journals in an open reporting period',1,520,NOW(6)),
('ACCOUNTING_POSTING_RUN','ACCOUNTING','ACTION','執行自動會計過帳','Run Accounting Posting','允許掃描業務事件並自動建立正式會計傳票','Allows scanning business events and automatically creating posted journals',1,530,NOW(6)),
('ACCOUNTING_PERIOD_MANAGE','ACCOUNTING','ACTION','管理會計期間','Manage Reporting Periods','允許 OPEN、SOFT_CLOSED、CLOSED 會計期間管理','Allows management of OPEN, SOFT_CLOSED and CLOSED reporting periods',1,540,NOW(6)),
('ACCOUNTING_RECONCILIATION_VIEW','ACCOUNTING','REPORT','查看會計對帳','View Accounting Reconciliation','允許查看 Business/Capital Ledger 與 General Ledger 對帳結果','Allows viewing reconciliation between business/capital ledgers and general ledger',1,550,NOW(6)),
('ACCOUNTING_RECONCILIATION_RUN','ACCOUNTING','ACTION','執行會計對帳','Run Accounting Reconciliation','允許執行 Bank、Investor、Receivable 與 Recycling Profit 自動對帳','Allows running bank, investor, receivable and recycling-profit reconciliations',1,560,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);

-- ============================================================
-- 11. Verification
-- ============================================================

SELECT 'VERIFY ACCOUNTING FOUNDATION TABLES' AS verify_section;

SELECT TABLE_NAME,TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA=@db_name
AND TABLE_NAME IN (
'accounting_event_mappings',
'accounting_posting_runs',
'accounting_source_postings',
'accounting_reconciliation_runs',
'accounting_reconciliation_issues'
)
ORDER BY TABLE_NAME;

SELECT 'VERIFY BANK GL MAPPING' AS verify_section;

SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA=@db_name
AND TABLE_NAME='bank_accounts'
AND COLUMN_NAME='gl_account_id';

SELECT 'VERIFY CHART OF ACCOUNTS' AS verify_section;

SELECT account_code,name_zh,name_en,account_type,parent_id
FROM gl_accounts
WHERE account_code IN (
'1110','1120','1130','1140',
'1210','1220','1230','1240',
'2110','2120','2130',
'4110','4120','4130','4140',
'4210','4220','5110','5210'
)
ORDER BY account_code;

SELECT 'VERIFY ACCOUNTING EVENT MAPPINGS' AS verify_section;

SELECT mapping_code,source_type,event_type,amount_source,active_flag
FROM accounting_event_mappings
ORDER BY source_module,mapping_code;

SELECT 'VERIFY ACCOUNTING PERMISSIONS' AS verify_section;

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

SELECT 'iBilik Capital Database v2.5 Accounting Foundation migration completed.' AS migration_status;

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