-- iBilik Capital Database v1.6
-- Investor Repayment & Capital Liability
-- MySQL 5.7.44 compatible
-- 所有新增資料表與欄位均使用中文註解

CREATE TABLE IF NOT EXISTS investor_liability_schedules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '投資者負債排程主鍵',
  subscription_id BIGINT UNSIGNED NOT NULL COMMENT '關聯投資認購記錄編號',
  investor_id BIGINT UNSIGNED NOT NULL COMMENT '關聯投資者編號',
  obligation_type VARCHAR(30) NOT NULL COMMENT '應付款類型，例如 PRINCIPAL 本金或 RETURN 收益',
  due_date DATE NOT NULL COMMENT '合約預計付款到期日',
  scheduled_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '此期原定應付款金額',
  paid_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '此期累計已實際支付金額',
  outstanding_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '此期尚未支付餘額',
  schedule_status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '排程狀態：PENDING、PARTIAL、PAID、OVERDUE、CANCELLED',
  formula_run_id BIGINT UNSIGNED NULL COMMENT '未來由公式引擎產生收益排程時關聯的計算執行編號',
  notes_zh VARCHAR(500) NULL COMMENT '中文備註',
  notes_en VARCHAR(500) 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),
  KEY idx_ils_subscription (subscription_id),
  KEY idx_ils_investor_due (investor_id,due_date),
  KEY idx_ils_status_due (schedule_status,due_date),
  CONSTRAINT fk_ils_subscription FOREIGN KEY (subscription_id) REFERENCES investor_subscriptions(id),
  CONSTRAINT fk_ils_investor FOREIGN KEY (investor_id) REFERENCES investors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='投資者本金及收益應付款排程；作為 Capital 未來負債與到期壓力的權威排程資料';

CREATE TABLE IF NOT EXISTS investor_repayments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '投資者實際還款主鍵',
  repayment_code VARCHAR(40) NOT NULL COMMENT '實際還款唯一編號',
  investor_id BIGINT UNSIGNED NOT NULL COMMENT '收款投資者編號',
  subscription_id BIGINT UNSIGNED NOT NULL COMMENT '關聯原始投資認購編號',
  payment_date DATE NOT NULL COMMENT '實際付款日期',
  principal_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '本次實際償還本金',
  return_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '本次實際支付收益或利息',
  total_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '本次實際付款總額',
  payment_method VARCHAR(30) NULL COMMENT '付款方式，例如 BANK_TRANSFER',
  bank_reference VARCHAR(120) NULL COMMENT '銀行交易或付款參考號碼',
  repayment_type VARCHAR(30) NOT NULL DEFAULT 'NORMAL' COMMENT '還款類型：NORMAL、PARTIAL、EARLY、MATURITY、ADJUSTMENT',
  status VARCHAR(20) NOT NULL DEFAULT 'POSTED' COMMENT '付款狀態：DRAFT、POSTED、VOID',
  posted_by_user_id BIGINT UNSIGNED NULL COMMENT '登錄此付款的一般系統帳戶編號',
  posted_by_super_admin_id BIGINT UNSIGNED NULL COMMENT '登錄此付款的超級管理員編號',
  notes_zh VARCHAR(500) NULL COMMENT '中文備註',
  notes_en VARCHAR(500) NULL COMMENT '英文備註',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間',
  PRIMARY KEY (id),
  UNIQUE KEY uq_ir_code (repayment_code),
  KEY idx_ir_investor_date (investor_id,payment_date),
  KEY idx_ir_subscription (subscription_id),
  CONSTRAINT fk_ir_investor FOREIGN KEY (investor_id) REFERENCES investors(id),
  CONSTRAINT fk_ir_subscription FOREIGN KEY (subscription_id) REFERENCES investor_subscriptions(id),
  CONSTRAINT fk_ir_user FOREIGN KEY (posted_by_user_id) REFERENCES sys_users(id),
  CONSTRAINT fk_ir_super FOREIGN KEY (posted_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='投資者實際本金及收益付款流水；任何實際對投資者付款均須建立此不可省略的業務記錄';

CREATE TABLE IF NOT EXISTS investor_repayment_allocations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '還款分配明細主鍵',
  repayment_id BIGINT UNSIGNED NOT NULL COMMENT '關聯實際還款編號',
  liability_schedule_id BIGINT UNSIGNED NOT NULL COMMENT '被沖銷的負債排程編號',
  allocation_type VARCHAR(20) NOT NULL COMMENT '分配類型：PRINCIPAL 或 RETURN',
  allocated_amount DECIMAL(18,2) NOT NULL COMMENT '本次分配至該排程的金額',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間',
  PRIMARY KEY (id),
  UNIQUE KEY uq_ira_repayment_schedule_type (repayment_id,liability_schedule_id,allocation_type),
  KEY idx_ira_schedule (liability_schedule_id),
  CONSTRAINT fk_ira_repayment FOREIGN KEY (repayment_id) REFERENCES investor_repayments(id),
  CONSTRAINT fk_ira_schedule FOREIGN KEY (liability_schedule_id) REFERENCES investor_liability_schedules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='實際還款對投資者負債排程的分配明細，用於準確追蹤每一期本金與收益被支付多少';

CREATE TABLE IF NOT EXISTS capital_liability_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Capital 負債快照主鍵',
  snapshot_date DATE NOT NULL COMMENT '快照統計日期',
  currency_code CHAR(3) NOT NULL DEFAULT 'MYR' COMMENT '統計幣別',
  principal_outstanding DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '尚未償還投資者本金總額',
  return_outstanding DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '尚未支付投資者收益總額',
  due_0_3m DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '未來三個月內到期總額',
  due_3_6m DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '未來超過三個月至六個月內到期總額',
  due_6_12m DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '未來超過六個月至十二個月內到期總額',
  overdue_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '截至快照日已逾期未付總額',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '快照建立時間',
  PRIMARY KEY (id),
  UNIQUE KEY uq_cls_date_currency (snapshot_date,currency_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Capital 對投資者負債壓力每日或指定日期快照，用於管理未來 3、6、12 個月現金需求';

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
('INVESTOR_LIABILITY_VIEW','INVESTOR','FUNCTION','查看投資者負債','View Investor Liabilities','查看本金、收益、到期與逾期排程','View principal, return, maturity and overdue schedules',1,140,NOW(6)),
('INVESTOR_REPAYMENT_POST','INVESTOR','ACTION','登錄投資者還款','Post Investor Repayment','登錄本金或收益的實際付款','Post actual principal or return payments',1,150,NOW(6)),
('CAPITAL_LIABILITY_VIEW','CAPITAL','REPORT','查看 Capital 負債壓力','View Capital Liability','查看未來 3、6、12 個月到期與逾期資金','View 3, 6 and 12 month maturities and overdue capital',1,160,NOW(6))
ON DUPLICATE KEY UPDATE
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);
