-- ============================================================
-- iBilik Capital Database v2.0
-- Phase 5 v12 - Parameter Metadata, Scenario, Sensitivity,
-- Waterfall Field Mapping
-- Target: MySQL 5.7.44
-- 所有新增資料表及欄位均使用中文 COMMENT
-- 可重複執行：ALTER 欄位會先檢查是否已存在
-- ============================================================
SET NAMES utf8mb4;
SET @db_name := DATABASE();

-- ------------------------------------------------------------
-- A. formula_parameters UI / validation metadata
-- ------------------------------------------------------------
SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='formula_parameters' AND COLUMN_NAME='group_code'),
'SELECT ''SKIP formula_parameters.group_code'' AS migration_message',
'ALTER TABLE formula_parameters ADD COLUMN group_code VARCHAR(60) NULL COMMENT ''參數在計算工作區的顯示分組代碼，例如 CORE、HOTEL、FUNDER、FEES、MODEL'' AFTER label_en');
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='formula_parameters' AND COLUMN_NAME='help_zh'),
'SELECT ''SKIP formula_parameters.help_zh'' AS migration_message',
'ALTER TABLE formula_parameters ADD COLUMN help_zh VARCHAR(500) NULL COMMENT ''參數中文操作說明、計算用途或人工修改注意事項'' AFTER group_code');
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='formula_parameters' AND COLUMN_NAME='help_en'),
'SELECT ''SKIP formula_parameters.help_en'' AS migration_message',
'ALTER TABLE formula_parameters ADD COLUMN help_en VARCHAR(500) NULL COMMENT ''參數英文操作說明；英文介面使用，空白時回退中文'' AFTER help_zh');
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='formula_parameters' AND COLUMN_NAME='step_value'),
'SELECT ''SKIP formula_parameters.step_value'' AS migration_message',
'ALTER TABLE formula_parameters ADD COLUMN step_value DECIMAL(19,8) NULL COMMENT ''前端數字欄位建議每次調整的步進值；例如 0.01、1000、1'' AFTER max_value');
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='formula_parameters' AND COLUMN_NAME='option_set_code'),
'SELECT ''SKIP formula_parameters.option_set_code'' AS migration_message',
'ALTER TABLE formula_parameters ADD COLUMN option_set_code VARCHAR(80) NULL COMMENT ''當 unit 或 data_type 為 OPTION 時關聯的選項集合代碼'' AFTER step_value');
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='formula_parameters' AND COLUMN_NAME='sensitivity_flag'),
'SELECT ''SKIP formula_parameters.sensitivity_flag'' AS migration_message',
'ALTER TABLE formula_parameters ADD COLUMN sensitivity_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''是否允許此參數作為敏感度分析變數；0否1是'' AFTER editable_flag');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- B. Parameter option lists
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS formula_parameter_options (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '公式參數選項主鍵',
 option_set_code VARCHAR(80) NOT NULL COMMENT '選項集合代碼；由 formula_parameters.option_set_code 關聯',
 option_value VARCHAR(120) NOT NULL COMMENT '實際送入計算引擎的選項值',
 label_zh VARCHAR(180) NOT NULL COMMENT '選項中文名稱',
 label_en VARCHAR(180) NULL COMMENT '選項英文名稱；空白時回退中文',
 sort_order INT NOT NULL DEFAULT 0 COMMENT '選項顯示順序',
 active_flag TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否啟用此選項；0否1是',
 created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間',
 PRIMARY KEY(id),
 UNIQUE KEY uq_fpo_set_value(option_set_code,option_value),
 KEY idx_fpo_set_active(option_set_code,active_flag,sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='公式工作區下拉選單及固定選項定義；避免選項文字硬寫在 PHP 頁面';

-- ------------------------------------------------------------
-- C. Scenario master + scenario values
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS formula_scenarios (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '公式情境主鍵',
 scenario_code VARCHAR(60) NOT NULL COMMENT '情境唯一編號，例如 SCN-20260816-0001',
 deal_id BIGINT UNSIGNED NOT NULL COMMENT '此情境所屬 Deal 主鍵',
 formula_version_id BIGINT UNSIGNED NOT NULL COMMENT '此情境鎖定的公式版本主鍵',
 scenario_name_zh VARCHAR(180) NOT NULL COMMENT '情境中文名稱，例如 Base Case、保守情境、積極情境',
 scenario_name_en VARCHAR(180) NULL COMMENT '情境英文名稱；英文介面使用',
 scenario_type VARCHAR(30) NOT NULL DEFAULT 'CUSTOM' COMMENT '情境類型：BASE、CONSERVATIVE、UPSIDE、DOWNSIDE、CUSTOM',
 base_calculation_run_id BIGINT UNSIGNED NULL COMMENT '若情境由既有 Calculation Run 複製而來，保存基準執行主鍵',
 status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' COMMENT '情境狀態：ACTIVE、ARCHIVED',
 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 '建立時間',
 updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '最後更新時間',
 PRIMARY KEY(id),
 UNIQUE KEY uq_formula_scenario_code(scenario_code),
 KEY idx_formula_scenario_deal(deal_id,formula_version_id,status),
 KEY idx_formula_scenario_base(base_calculation_run_id),
 CONSTRAINT fk_formula_scenario_deal FOREIGN KEY(deal_id) REFERENCES deals(id),
 CONSTRAINT fk_formula_scenario_version FOREIGN KEY(formula_version_id) REFERENCES formula_versions(id),
 CONSTRAINT fk_formula_scenario_base_run FOREIGN KEY(base_calculation_run_id) REFERENCES calculation_runs(id),
 CONSTRAINT fk_formula_scenario_user FOREIGN KEY(created_by_user_id) REFERENCES sys_users(id),
 CONSTRAINT fk_formula_scenario_super FOREIGN KEY(created_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='金融公式情境主檔；同一 Deal 可建立 Base、保守、積極及自訂方案做 A/B 比較';

CREATE TABLE IF NOT EXISTS formula_scenario_values (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '公式情境參數值主鍵',
 scenario_id BIGINT UNSIGNED NOT NULL COMMENT '所屬公式情境主鍵',
 parameter_code VARCHAR(80) NOT NULL COMMENT '公式參數代碼',
 value_text VARCHAR(255) NULL COMMENT '情境保存的參數值；依 formula_parameters.data_type 解析',
 source_type VARCHAR(20) NOT NULL DEFAULT 'USER' COMMENT '值來源：DEFAULT、BASE_RUN、USER',
 manually_overridden TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否相對基準值由人工修改；0否1是',
 prior_value VARCHAR(255) NULL COMMENT '人工修改前的基準值，方便 A/B 差異追蹤',
 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_formula_scenario_parameter(scenario_id,parameter_code),
 CONSTRAINT fk_formula_scenario_value_scenario FOREIGN KEY(scenario_id) REFERENCES formula_scenarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='每個金融計算情境的參數值；保留基準值與人工覆寫差異';

-- ------------------------------------------------------------
-- D. A/B Run comparison
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS calculation_run_comparisons (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '計算執行比較主鍵',
 comparison_code VARCHAR(60) NOT NULL COMMENT 'A/B 比較唯一編號',
 deal_id BIGINT UNSIGNED NOT NULL COMMENT '比較所屬 Deal 主鍵',
 base_run_id BIGINT UNSIGNED NOT NULL COMMENT 'A 方案或基準 Calculation Run',
 compare_run_id BIGINT UNSIGNED NOT NULL COMMENT 'B 方案或比較 Calculation Run',
 comparison_name_zh VARCHAR(180) NULL COMMENT '比較中文名稱',
 comparison_name_en VARCHAR(180) NULL COMMENT '比較英文名稱',
 comparison_json LONGTEXT NULL COMMENT '輸入差異、KPI 差異與 Waterfall 摘要比較 JSON',
 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_calc_comparison_code(comparison_code),
 KEY idx_calc_comparison_deal(deal_id,created_at),
 CONSTRAINT fk_calc_comparison_deal FOREIGN KEY(deal_id) REFERENCES deals(id),
 CONSTRAINT fk_calc_comparison_base FOREIGN KEY(base_run_id) REFERENCES calculation_runs(id),
 CONSTRAINT fk_calc_comparison_compare FOREIGN KEY(compare_run_id) REFERENCES calculation_runs(id),
 CONSTRAINT fk_calc_comparison_user FOREIGN KEY(created_by_user_id) REFERENCES sys_users(id),
 CONSTRAINT fk_calc_comparison_super FOREIGN KEY(created_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='同一 Deal 不同 Calculation Run 的 A/B 比較結果；用於比較參數、KPI、Waterfall 與利潤差異';

-- ------------------------------------------------------------
-- E. Sensitivity analysis
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS formula_sensitivity_runs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '敏感度分析主鍵',
 sensitivity_code VARCHAR(60) NOT NULL COMMENT '敏感度分析唯一編號',
 deal_id BIGINT UNSIGNED NOT NULL COMMENT '所屬 Deal 主鍵',
 formula_version_id BIGINT UNSIGNED NOT NULL COMMENT '使用的公式版本主鍵',
 base_run_id BIGINT UNSIGNED NULL COMMENT '敏感度分析使用的基準 Calculation Run',
 parameter_code VARCHAR(80) NOT NULL COMMENT '被調整的敏感度參數代碼',
 output_code VARCHAR(80) NOT NULL COMMENT '主要觀察的輸出 KPI 代碼',
 start_value DECIMAL(19,8) NOT NULL COMMENT '敏感度參數起始值',
 end_value DECIMAL(19,8) NOT NULL COMMENT '敏感度參數結束值',
 step_value DECIMAL(19,8) NOT NULL COMMENT '每次敏感度試算的增減步進值',
 status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '敏感度分析狀態：PENDING、COMPLETED、FAILED',
 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_sensitivity_code(sensitivity_code),
 KEY idx_sensitivity_deal(deal_id,formula_version_id,status),
 CONSTRAINT fk_sensitivity_deal FOREIGN KEY(deal_id) REFERENCES deals(id),
 CONSTRAINT fk_sensitivity_formula FOREIGN KEY(formula_version_id) REFERENCES formula_versions(id),
 CONSTRAINT fk_sensitivity_base FOREIGN KEY(base_run_id) REFERENCES calculation_runs(id),
 CONSTRAINT fk_sensitivity_user FOREIGN KEY(created_by_user_id) REFERENCES sys_users(id),
 CONSTRAINT fk_sensitivity_super FOREIGN KEY(created_by_super_admin_id) REFERENCES sys_super_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='單一公式參數在指定範圍內反覆試算的敏感度分析主檔';

CREATE TABLE IF NOT EXISTS formula_sensitivity_points (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '敏感度分析數據點主鍵',
 sensitivity_run_id BIGINT UNSIGNED NOT NULL COMMENT '所屬敏感度分析主鍵',
 sequence_no INT UNSIGNED NOT NULL COMMENT '敏感度試算順序',
 parameter_value DECIMAL(19,8) NOT NULL COMMENT '此數據點使用的敏感度參數值',
 output_value DECIMAL(19,8) NULL COMMENT '此數據點主要觀察 KPI 的計算結果',
 calculation_run_id BIGINT UNSIGNED NULL COMMENT '如保存完整計算執行，關聯 Calculation Run 主鍵',
 result_json LONGTEXT NULL COMMENT '此敏感度數據點的其他 KPI 結果 JSON',
 created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間',
 PRIMARY KEY(id),
 UNIQUE KEY uq_sensitivity_point(sensitivity_run_id,sequence_no),
 KEY idx_sensitivity_point_value(sensitivity_run_id,parameter_value),
 CONSTRAINT fk_sensitivity_point_run FOREIGN KEY(sensitivity_run_id) REFERENCES formula_sensitivity_runs(id) ON DELETE CASCADE,
 CONSTRAINT fk_sensitivity_point_calc FOREIGN KEY(calculation_run_id) REFERENCES calculation_runs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='敏感度分析每一個參數試算點的 KPI 結果；可用於後續圖表及決策比較';

-- ------------------------------------------------------------
-- F. Waterfall field metadata / product-specific mapping
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS waterfall_field_definitions (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Waterfall 顯示欄位定義主鍵',
 product_id BIGINT UNSIGNED NOT NULL COMMENT '所屬金融產品主鍵',
 field_code VARCHAR(80) NOT NULL COMMENT 'Waterfall 欄位代碼',
 source_location VARCHAR(30) NOT NULL DEFAULT 'CORE' COMMENT '欄位資料位置：CORE 表示固定欄位，DIMENSION 表示 dimensions_json 產品專屬欄位',
 source_key VARCHAR(100) NOT NULL COMMENT '固定資料庫欄位名稱或 dimensions_json Key',
 label_zh VARCHAR(180) NOT NULL COMMENT '欄位中文名稱',
 label_en VARCHAR(180) NULL COMMENT '欄位英文名稱；英文介面使用',
 unit VARCHAR(30) NULL COMMENT '欄位單位，例如 MYR、PERCENT、ROOM、DAY',
 format_code VARCHAR(30) NULL COMMENT '前端格式代碼，例如 MONEY、PERCENT、NUMBER、TEXT',
 sort_order INT NOT NULL DEFAULT 0 COMMENT 'Waterfall 表格顯示順序',
 visible_flag TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否預設顯示此欄位；0否1是',
 created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立時間',
 PRIMARY KEY(id),
 UNIQUE KEY uq_waterfall_field(product_id,field_code),
 KEY idx_waterfall_field_product(product_id,visible_flag,sort_order),
 CONSTRAINT fk_waterfall_field_product FOREIGN KEY(product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='各金融產品 Waterfall 表格欄位 Metadata；共用欄位與產品專屬 dimensions_json 欄位均由此配置';

-- ------------------------------------------------------------
-- G. Metadata seed / existing 44 parameters
-- ------------------------------------------------------------
UPDATE formula_parameters SET group_code='OWNER', step_value=1000, sensitivity_flag=1 WHERE formula_version_id=1 AND parameter_code='FORECAST_MONTHLY_OWNER_PROFIT';
UPDATE formula_parameters SET group_code='ADVANCE', step_value=1, sensitivity_flag=1 WHERE formula_version_id=1 AND parameter_code IN ('ADVANCE_MONTHS','HOTEL_TENURE_OVERRIDE','FUNDER_TENURE_OVERRIDE');
UPDATE formula_parameters SET group_code='ADVANCE', step_value=1000 WHERE formula_version_id=1 AND parameter_code='GROSS_ADVANCE_OVERRIDE';
UPDATE formula_parameters SET group_code='FEES', step_value=0.001, sensitivity_flag=1 WHERE formula_version_id=1 AND parameter_code IN ('OWNER_SERVICE_FEE_RATE','FUNDER_PROCESSING_FEE_RATE');
UPDATE formula_parameters SET group_code='HOTEL', step_value=0.001, sensitivity_flag=1 WHERE formula_version_id=1 AND parameter_code='HOTEL_INTEREST_RATE';
UPDATE formula_parameters SET group_code='FUNDER', step_value=0.001, sensitivity_flag=1 WHERE formula_version_id=1 AND parameter_code IN ('FUNDER_INTEREST_RATE','FUNDER_FINANCING_RATIO');
UPDATE formula_parameters SET group_code='OPTIONS', option_set_code='SERVICE_FEE_BASIS' WHERE formula_version_id=1 AND parameter_code='SERVICE_FEE_BASIS';
UPDATE formula_parameters SET group_code='OPTIONS', option_set_code='HOTEL_INTEREST_METHOD' WHERE formula_version_id=1 AND parameter_code='HOTEL_INTEREST_METHOD';

UPDATE formula_parameters SET group_code='HOTEL', sensitivity_flag=1 WHERE formula_version_id=2 AND parameter_code IN ('ROOMS','ADR','OCCUPANCY_RATE','OPERATING_COST_RATE','ANNUAL_REVENUE_GROWTH');
UPDATE formula_parameters SET group_code='CAPEX', sensitivity_flag=1 WHERE formula_version_id=2 AND parameter_code='CAPEX_REQUIRED';
UPDATE formula_parameters SET group_code='MODEL' WHERE formula_version_id=2 AND parameter_code IN ('DAYS_PER_MONTH','MODEL_HORIZON');
UPDATE formula_parameters SET group_code='FEES', sensitivity_flag=1 WHERE formula_version_id=2 AND parameter_code IN ('MANAGEMENT_FEE_RATE','PROCESSING_FEE_RATE');
UPDATE formula_parameters SET group_code='WATERFALL', sensitivity_flag=1 WHERE formula_version_id=2 AND parameter_code IN ('MONTHLY_RETURN_RATE','MONTHLY_PRINCIPAL_RATE','MAX_TOPLINE_SHARE','ALTERNATIVE_MONTHLY_RENTAL');
UPDATE formula_parameters SET group_code='FUNDER', sensitivity_flag=1 WHERE formula_version_id=2 AND parameter_code='FUNDER_RATE';
UPDATE formula_parameters SET group_code='OPTIONS', option_set_code='MANAGEMENT_FEE_BASIS' WHERE formula_version_id=2 AND parameter_code='MANAGEMENT_FEE_BASIS';

UPDATE formula_parameters SET group_code='HOTEL', sensitivity_flag=1 WHERE formula_version_id=3 AND parameter_code IN ('HOTELS','ROOMS_PER_HOTEL','HOTEL_TERM','HOTEL_FLAT_RATE');
UPDATE formula_parameters SET group_code='PROGRAM', sensitivity_flag=1 WHERE formula_version_id=3 AND parameter_code IN ('SIGNUP_BONUS_PER_ROOM','CAPEX_PER_ROOM');
UPDATE formula_parameters SET group_code='FUNDER', sensitivity_flag=1 WHERE formula_version_id=3 AND parameter_code IN ('FUNDER_TERM','FUNDER_RATE','FUNDER_RATIO');

UPDATE formula_parameters SET group_code='RENTAL', sensitivity_flag=1 WHERE formula_version_id=4 AND parameter_code IN ('MONTHLY_GUARANTEED_RENTAL','ADVANCE_RENTAL_PERIOD','OWNER_DISCOUNT_RATE');
UPDATE formula_parameters SET group_code='FUNDER', sensitivity_flag=1 WHERE formula_version_id=4 AND parameter_code IN ('FUNDER_INTEREST_RATE','FUNDER_PROCESSING_FEE_RATE');
UPDATE formula_parameters SET group_code='FEES', sensitivity_flag=1 WHERE formula_version_id=4 AND parameter_code IN ('OWNER_SERVICE_FEE_RATE','AGENT_COMMISSION_RATE');

INSERT INTO formula_parameter_options(option_set_code,option_value,label_zh,label_en,sort_order,active_flag)
VALUES
('SERVICE_FEE_BASIS','1','以預支總額計算','Based on gross advance amount',10,1),
('SERVICE_FEE_BASIS','2','以實際支付酒店現金計算','Based on net cash paid to hotelier',20,1),
('HOTEL_INTEREST_METHOD','1','平息／固定本金基礎','Flat / fixed principal basis',10,1),
('HOTEL_INTEREST_METHOD','2','遞減本金基礎','Reducing principal basis',20,1),
('MANAGEMENT_FEE_BASIS','Gross Revenue','以總營收計算','Based on gross revenue',10,1),
('MANAGEMENT_FEE_BASIS','Operating Surplus','以營運盈餘計算','Based on operating surplus',20,1)
ON DUPLICATE KEY UPDATE label_zh=VALUES(label_zh),label_en=VALUES(label_en),sort_order=VALUES(sort_order),active_flag=1;

-- ------------------------------------------------------------
-- H. Waterfall core field seed for all products
-- ------------------------------------------------------------
INSERT INTO waterfall_field_definitions(product_id,field_code,source_location,source_key,label_zh,label_en,unit,format_code,sort_order,visible_flag)
SELECT p.id,x.field_code,'CORE',x.source_key,x.label_zh,x.label_en,x.unit,x.format_code,x.sort_order,1
FROM products p
JOIN (
 SELECT 'PERIOD_NO' field_code,'period_no' source_key,'期數' label_zh,'Period' label_en,NULL unit,'NUMBER' format_code,10 sort_order
 UNION ALL SELECT 'PERIOD_DATE','period_date','日期','Date',NULL,'DATE',20
 UNION ALL SELECT 'OPENING_PRINCIPAL','opening_principal','期初本金','Opening Principal','MYR','MONEY',30
 UNION ALL SELECT 'GROSS_INFLOW','gross_inflow','總流入','Gross Inflow','MYR','MONEY',40
 UNION ALL SELECT 'PRINCIPAL_DUE','principal_due','本金回收','Principal Due','MYR','MONEY',50
 UNION ALL SELECT 'INTEREST_DUE','interest_due','收益／利息','Interest / Return','MYR','MONEY',60
 UNION ALL SELECT 'FEE_DUE','fee_due','服務及管理費','Fees','MYR','MONEY',70
 UNION ALL SELECT 'FUNDING_COST_DUE','funding_cost_due','資金成本','Funding Cost','MYR','MONEY',80
 UNION ALL SELECT 'OWNER_CASH','owner_cash','業主剩餘現金','Owner Cash','MYR','MONEY',90
 UNION ALL SELECT 'CLOSING_PRINCIPAL','closing_principal','期末本金','Closing Principal','MYR','MONEY',100
) x
WHERE p.product_code IN ('ADVANCE_PROFIT','CAPEX_FUNDING','REVENUE_MANAGEMENT','FLEXIBLE_FORWARD_RENTAL')
ON DUPLICATE KEY UPDATE label_zh=VALUES(label_zh),label_en=VALUES(label_en),unit=VALUES(unit),format_code=VALUES(format_code),sort_order=VALUES(sort_order),visible_flag=1;

-- ------------------------------------------------------------
-- I. 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
('FORMULA_SCENARIO_MANAGE','FORMULA','ACTION','管理公式情境','Manage Formula Scenarios','建立 Base、保守、積極及自訂金融計算情境','Create and manage base, conservative, upside and custom financial calculation scenarios',1,250,NOW(6)),
('FORMULA_RUN_COMPARE','FORMULA','FUNCTION','比較計算方案','Compare Calculation Runs','比較同一 Deal 兩個 Calculation Run 的參數、KPI 與 Waterfall 差異','Compare parameters, KPIs and waterfall differences between two calculation runs for the same deal',1,260,NOW(6)),
('FORMULA_SENSITIVITY_RUN','FORMULA','ACTION','執行敏感度分析','Run Sensitivity Analysis','允許對可分析參數進行區間試算並觀察 KPI 變化','Allows range-based sensitivity analysis on eligible parameters and KPIs',1,270,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.0 Phase 5 v12 migration completed.' AS migration_status;
