-- iBilik Capital Database v1.9
-- Calculation Workspace
-- MySQL 5.7.44 compatible
-- 所有新增欄位均使用中文 COMMENT

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

-- calculation_runs: optional human-readable title
SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='calculation_runs' AND COLUMN_NAME='run_title'
    ),
    'SELECT ''SKIP calculation_runs.run_title'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN run_title VARCHAR(160) NULL COMMENT ''本次計算的人工識別名稱，方便歷史記錄搜尋，例如某酒店2026年8月CAPEX方案'' AFTER run_no'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- calculation_runs: user confirmation flag
SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='calculation_runs' AND COLUMN_NAME='confirmed_flag'
    ),
    'SELECT ''SKIP calculation_runs.confirmed_flag'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN confirmed_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''使用者是否確認此計算結果作為後續 Deal 決策參考；0否1是'' AFTER status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- calculation_runs: confirmation timestamp
SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='calculation_runs' AND COLUMN_NAME='confirmed_at'
    ),
    'SELECT ''SKIP calculation_runs.confirmed_at'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN confirmed_at DATETIME(6) NULL COMMENT ''使用者確認此計算結果的時間'' AFTER confirmed_flag'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- calculation_runs: notes bilingual
SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='calculation_runs' AND COLUMN_NAME='notes_zh'
    ),
    'SELECT ''SKIP calculation_runs.notes_zh'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN notes_zh VARCHAR(1000) NULL COMMENT ''本次計算中文備註'' AFTER error_message'
);
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='calculation_runs' AND COLUMN_NAME='notes_en'
    ),
    'SELECT ''SKIP calculation_runs.notes_en'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN notes_en VARCHAR(1000) NULL COMMENT ''本次計算英文備註；英文介面使用'' AFTER notes_zh'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- index for history views
SET @sql := IF(
    EXISTS(
        SELECT 1 FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='calculation_runs' AND INDEX_NAME='idx_calc_history'
    ),
    'SELECT ''SKIP idx_calc_history'' AS migration_message',
    'ALTER TABLE calculation_runs ADD KEY idx_calc_history (engine_code,status,created_at)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 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_WORKSPACE_VIEW','FORMULA','FUNCTION','使用公式計算工作區','Use Formula Workspace','允許進入四大金融產品公式計算工作區','Allows access to the financial formula calculation workspace',1,230,NOW(6)),
('FORMULA_RUN_CONFIRM','FORMULA','ACTION','確認計算結果','Confirm Calculation Run','允許將計算結果標記為已確認，供後續 Deal 或審批引用','Allows a calculation run to be marked as confirmed for downstream deal or approval use',1,240,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 v1.9 migration completed.' AS migration_status;
