-- iBilik Capital Database v1.5 - Investor + Capital Core
-- 目標環境：MySQL 5.7.44 / utf8mb4
-- 先決條件：已完成 v1.0 - v1.4
-- 規則：所有新增資料表與欄位皆使用中文 COMMENT。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE `investors`
  ADD COLUMN `email` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '投資者主要電子郵件' AFTER `registration_no`,
  ADD COLUMN `phone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '投資者主要聯絡電話' AFTER `email`,
  ADD COLUMN `country_code` char(2) COLLATE utf8mb4_unicode_ci DEFAULT 'MY' COMMENT '投資者國家或地區 ISO 2碼代碼' AFTER `phone`,
  ADD COLUMN `address_line1` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '投資者地址第一行' AFTER `country_code`,
  ADD COLUMN `address_line2` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '投資者地址第二行' AFTER `address_line1`,
  ADD COLUMN `postcode` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '投資者郵遞區號' AFTER `address_line2`,
  ADD COLUMN `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '投資者城市' AFTER `postcode`,
  ADD COLUMN `state` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '投資者州屬或省份' AFTER `city`,
  ADD COLUMN `notes` text COLLATE utf8mb4_unicode_ci COMMENT '投資者內部備註；不得存放密碼或高敏感登入憑證' AFTER `status`,
  ADD COLUMN `created_by_user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '建立投資者的一般使用者；由超級管理員直接建立時可為空' AFTER `notes`;

ALTER TABLE `investors`
  ADD KEY `idx_investor_status_name` (`status`,`name`),
  ADD KEY `idx_investor_kyc` (`kyc_status`,`status`),
  ADD KEY `fk_investor_creator` (`created_by_user_id`),
  ADD CONSTRAINT `fk_investor_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`);

CREATE TABLE `investor_contacts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '投資者聯絡人主鍵',
  `investor_id` bigint(20) UNSIGNED NOT NULL COMMENT '所屬投資者主鍵',
  `name` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '聯絡人姓名',
  `position` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '聯絡人職位或與投資者關係',
  `email` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '聯絡人電子郵件',
  `phone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '聯絡人電話',
  `is_primary` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否為主要聯絡人；1是0否',
  `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_investor_contact` (`investor_id`,`is_primary`),
  CONSTRAINT `fk_investor_contact_investor` FOREIGN KEY (`investor_id`) REFERENCES `investors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='投資者聯絡人資料表；支援個人、公司及多聯絡窗口管理';

CREATE TABLE `investor_commitments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '投資者資金承諾主鍵',
  `commitment_code` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '投資者資金承諾唯一編號',
  `investor_id` bigint(20) UNSIGNED NOT NULL COMMENT '作出資金承諾的投資者',
  `committed_amount` decimal(19,2) NOT NULL COMMENT '投資者承諾提供但未必已實際入金的本金金額',
  `funded_amount` decimal(19,2) NOT NULL DEFAULT '0.00' COMMENT '此承諾目前已實際入金並轉成 Subscription 的累計本金',
  `currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'MYR' COMMENT '資金承諾幣別',
  `committed_date` date NOT NULL COMMENT '投資者作出承諾的日期',
  `expiry_date` date DEFAULT NULL COMMENT '尚未入金部分的承諾失效日期',
  `expected_maturity_date` date DEFAULT NULL COMMENT '預計形成投資後的本金到期日；正式責任以 Subscription maturity_date 為準',
  `annual_return_rate` decimal(12,8) NOT NULL DEFAULT '0.00000000' COMMENT '承諾階段預計年化回報率；正式條件以 Subscription 為準',
  `payment_frequency` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '預計回報支付頻率 MONTHLY、QUARTERLY、MATURITY 等',
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'OPEN' COMMENT '承諾狀態 OPEN、PARTIALLY_FUNDED、FUNDED、EXPIRED、CANCELLED',
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT '資金承諾內部備註',
  `created_by_user_id` bigint(20) UNSIGNED DEFAULT 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_investor_commitment_code` (`commitment_code`),
  KEY `idx_investor_commitment_status` (`investor_id`,`status`,`expiry_date`),
  KEY `fk_investor_commitment_creator` (`created_by_user_id`),
  CONSTRAINT `fk_investor_commitment_investor` FOREIGN KEY (`investor_id`) REFERENCES `investors` (`id`),
  CONSTRAINT `fk_investor_commitment_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='投資者尚未或尚未完全實際入金的資金承諾；與真正形成負債的 Subscription 分開管理';

ALTER TABLE `investor_subscriptions`
  ADD COLUMN `commitment_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '若此筆實際投資由先前資金承諾轉入，記錄對應 investor_commitments 主鍵' AFTER `investor_id`,
  ADD COLUMN `agreement_no` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '投資協議、合約或內部協議參考號' AFTER `subscription_code`,
  ADD COLUMN `return_method` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'CONTRACT' COMMENT '投資回報計算方式；CONTRACT 表示由正式合約或產品公式決定，禁止在程式中自行假設' AFTER `annual_return_rate`,
  ADD COLUMN `day_count_basis` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '日數計算基準，例如 ACT_365、ACT_360；空白表示依產品或合約規則' AFTER `return_method`,
  ADD COLUMN `notes` text COLLATE utf8mb4_unicode_ci COMMENT '此投資資金批次內部備註' AFTER `status`,
  ADD COLUMN `created_by_user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '建立此投資資金批次的一般使用者；超級管理員建立時可為空' AFTER `notes`;

ALTER TABLE `investor_subscriptions`
  ADD KEY `fk_subscription_commitment` (`commitment_id`),
  ADD KEY `fk_subscription_creator` (`created_by_user_id`),
  ADD KEY `idx_subscription_maturity_status` (`maturity_date`,`status`),
  ADD CONSTRAINT `fk_subscription_commitment` FOREIGN KEY (`commitment_id`) REFERENCES `investor_commitments` (`id`),
  ADD CONSTRAINT `fk_subscription_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`);

CREATE TABLE `investor_cash_receipts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '投資者實際入金主鍵',
  `receipt_code` varchar(70) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '投資者實際入金唯一收款編號',
  `subscription_id` bigint(20) UNSIGNED NOT NULL COMMENT '此筆現金形成的投資資金批次',
  `amount` decimal(19,2) NOT NULL COMMENT '實際收到並確認入帳的投資本金',
  `receipt_date` date NOT NULL COMMENT 'Capital 公司實際收到資金日期',
  `bank_account_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '實際收款銀行帳戶；可在 Treasury 銀行帳戶建立後指定',
  `reference` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '銀行流水號、轉帳參考或收款憑證編號',
  `created_by_user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '登錄此筆實際入金的一般使用者；超級管理員登錄時可為空',
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_investor_cash_receipt_code` (`receipt_code`),
  KEY `idx_investor_cash_receipt_subscription` (`subscription_id`,`receipt_date`),
  KEY `fk_investor_cash_receipt_bank` (`bank_account_id`),
  KEY `fk_investor_cash_receipt_creator` (`created_by_user_id`),
  CONSTRAINT `fk_investor_cash_receipt_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `investor_subscriptions` (`id`),
  CONSTRAINT `fk_investor_cash_receipt_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`),
  CONSTRAINT `fk_investor_cash_receipt_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `sys_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='投資者實際進入 Capital 的現金本金紀錄；承諾不等於入金，只有此表確認後才形成真正可部署資金';

-- Phase 4 權限
INSERT INTO `permission_catalog`
(`permission_code`,`module_code`,`permission_type`,`name_zh`,`name_en`,`description_zh`,`description_en`,`active_flag`,`sort_order`)
VALUES
('INVESTOR_COMMITMENT_MANAGE','INVESTOR','ACTION','維護投資者資金承諾','Manage Investor Commitments','允許建立及維護投資者尚未完全入金的資金承諾','Allows creating and maintaining investor commitments before full funding',1,320),
('INVESTOR_SUBSCRIPTION_POST','INVESTOR','ACTION','登錄投資者實際入金','Post Investor Funding','允許把已確認實際收到的投資本金建立成 Subscription 與 Capital 負債','Allows confirmed investor cash funding to create a Subscription and Capital liability',1,330),
('CAPITAL_POSITION_VIEW','CAPITAL','FUNCTION','查看 Capital 資金部位','View Capital Position','允許查看投資者本金負債、到期結構及 Capital 資金摘要','Allows viewing investor principal liabilities, maturity profile and Capital position summary',1,340)
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;

SET FOREIGN_KEY_CHECKS = 1;

-- 驗證
SELECT COUNT(*) AS investor_contact_table_exists FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='investor_contacts';
SELECT COUNT(*) AS investor_commitment_table_exists FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='investor_commitments';
SELECT COUNT(*) AS investor_cash_receipt_table_exists FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='investor_cash_receipts';
SELECT permission_code,name_zh,name_en FROM permission_catalog WHERE permission_code IN ('INVESTOR_COMMITMENT_MANAGE','INVESTOR_SUBSCRIPTION_POST','CAPITAL_POSITION_VIEW') ORDER BY permission_code;
