-- ============================================================
-- iBilik Capital Database v1.8c - Full Corrected Migration
-- Target: MySQL 5.7.44
-- PHP Runtime: 8.0.30
-- Purpose:
--   Safe completion of Calculation Runner database upgrade
-- Notes:
--   1) calculation_outputs.label_en was already added in v1.3
--      therefore this migration WILL NOT add it again.
--   2) All ALTER operations check information_schema first.
--   3) Re-running this script is safe for the objects covered here.
--   4) 所有新增資料表與欄位均保留中文 COMMENT。
-- ============================================================

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

-- ------------------------------------------------------------
-- 1. calculation_runs.created_by_super_admin_id
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'calculation_runs'
          AND COLUMN_NAME = 'created_by_super_admin_id'
    ),
    'SELECT ''SKIP: calculation_runs.created_by_super_admin_id already exists'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN created_by_super_admin_id BIGINT UNSIGNED NULL COMMENT ''建立此計算執行的超級管理員主鍵；一般使用者執行時可為空'' AFTER created_by_user_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 2. calculation_runs.engine_code
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'calculation_runs'
          AND COLUMN_NAME = 'engine_code'
    ),
    'SELECT ''SKIP: calculation_runs.engine_code already exists'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN engine_code VARCHAR(80) NULL COMMENT ''本次計算所使用的公式引擎代碼，例如 ADVANCE_PROFIT、CAPEX_FUNDING'' AFTER formula_version_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 3. calculation_runs.engine_version
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'calculation_runs'
          AND COLUMN_NAME = 'engine_version'
    ),
    'SELECT ''SKIP: calculation_runs.engine_version already exists'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN engine_version VARCHAR(30) NULL COMMENT ''本次計算實際使用的 PHP Formula Engine 版本'' AFTER engine_code'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 4. calculation_runs.execution_ms
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'calculation_runs'
          AND COLUMN_NAME = 'execution_ms'
    ),
    'SELECT ''SKIP: calculation_runs.execution_ms already exists'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN execution_ms INT UNSIGNED NULL COMMENT ''本次公式引擎執行耗時，單位毫秒'' AFTER result_checksum'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 5. calculation_runs.error_message
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'calculation_runs'
          AND COLUMN_NAME = 'error_message'
    ),
    'SELECT ''SKIP: calculation_runs.error_message already exists'' AS migration_message',
    'ALTER TABLE calculation_runs ADD COLUMN error_message VARCHAR(1000) NULL COMMENT ''計算失敗時的安全錯誤摘要；不得保存密碼、MID 或其他敏感憑證'' AFTER execution_ms'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 6. calculation_runs index for super admin
-- ------------------------------------------------------------
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_run_super_admin'
    ),
    'SELECT ''SKIP: idx_calc_run_super_admin already exists'' AS migration_message',
    'ALTER TABLE calculation_runs ADD KEY idx_calc_run_super_admin (created_by_super_admin_id)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 7. calculation_runs FK to super admin
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_SCHEMA = @db_name
          AND TABLE_NAME = 'calculation_runs'
          AND CONSTRAINT_NAME = 'fk_calc_run_super_admin'
          AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    ),
    'SELECT ''SKIP: fk_calc_run_super_admin already exists'' AS migration_message',
    'ALTER TABLE calculation_runs ADD CONSTRAINT fk_calc_run_super_admin FOREIGN KEY (created_by_super_admin_id) REFERENCES sys_super_admins(id)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 8. IMPORTANT
-- calculation_outputs.label_en already exists from v1.3.
-- This migration intentionally does nothing to that column.
-- ------------------------------------------------------------
SELECT 'SKIP: calculation_outputs.label_en already exists from bilingual migration v1.3' AS migration_message;

-- ------------------------------------------------------------
-- 9. calculation_snapshots.snapshot_schema_version
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'calculation_snapshots'
          AND COLUMN_NAME = 'snapshot_schema_version'
    ),
    'SELECT ''SKIP: calculation_snapshots.snapshot_schema_version already exists'' AS migration_message',
    'ALTER TABLE calculation_snapshots ADD COLUMN snapshot_schema_version VARCHAR(20) NOT NULL DEFAULT ''1.0'' COMMENT ''快照 JSON 結構版本，確保未來程式仍可正確解析舊資料'' AFTER calculation_run_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 10. waterfall_schedules.formula_version_id
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'waterfall_schedules'
          AND COLUMN_NAME = 'formula_version_id'
    ),
    'SELECT ''SKIP: waterfall_schedules.formula_version_id already exists'' AS migration_message',
    'ALTER TABLE waterfall_schedules ADD COLUMN formula_version_id BIGINT UNSIGNED NULL COMMENT ''產生此 Waterfall 的公式版本主鍵，用於追溯計算邏輯來源'' AFTER calculation_run_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 11. waterfall_schedules.engine_code
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'waterfall_schedules'
          AND COLUMN_NAME = 'engine_code'
    ),
    'SELECT ''SKIP: waterfall_schedules.engine_code already exists'' AS migration_message',
    'ALTER TABLE waterfall_schedules ADD COLUMN engine_code VARCHAR(80) NULL COMMENT ''產生此 Waterfall 的公式引擎代碼'' AFTER formula_version_id'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 12. waterfall formula index
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'waterfall_schedules'
          AND INDEX_NAME = 'idx_waterfall_formula'
    ),
    'SELECT ''SKIP: idx_waterfall_formula already exists'' AS migration_message',
    'ALTER TABLE waterfall_schedules ADD KEY idx_waterfall_formula (formula_version_id)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 13. waterfall formula FK
-- ------------------------------------------------------------
SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_SCHEMA = @db_name
          AND TABLE_NAME = 'waterfall_schedules'
          AND CONSTRAINT_NAME = 'fk_waterfall_formula'
          AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    ),
    'SELECT ''SKIP: fk_waterfall_formula already exists'' AS migration_message',
    'ALTER TABLE waterfall_schedules ADD CONSTRAINT fk_waterfall_formula FOREIGN KEY (formula_version_id) REFERENCES formula_versions(id)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 14. Golden Reference Validation table
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS calculation_golden_validations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
        COMMENT 'Golden Reference 驗證結果主鍵',

    calculation_run_id BIGINT UNSIGNED NOT NULL
        COMMENT '關聯本次系統公式計算執行記錄',

    golden_test_case_id BIGINT UNSIGNED NOT NULL
        COMMENT '關聯 Excel Golden Reference 測試案例主鍵',

    validation_status VARCHAR(20) NOT NULL DEFAULT 'PENDING'
        COMMENT '驗證狀態：PENDING、PASSED、FAILED、ERROR',

    compared_output_count INT UNSIGNED NOT NULL DEFAULT 0
        COMMENT '本次參與 Golden Reference 比對的輸出指標總數',

    passed_output_count INT UNSIGNED NOT NULL DEFAULT 0
        COMMENT '本次通過允許誤差的輸出指標數量',

    failed_output_count INT UNSIGNED NOT NULL DEFAULT 0
        COMMENT '本次未通過允許誤差的輸出指標數量',

    tolerance DECIMAL(18,8) NOT NULL DEFAULT 0.00010000
        COMMENT 'Golden Reference 數值比對允許的誤差範圍',

    validation_result_json LONGTEXT NULL
        COMMENT '各輸出指標的 Golden Reference 驗證結果 JSON，記錄期望值、實際值與誤差',

    error_message VARCHAR(1000) NULL
        COMMENT 'Golden Reference 驗證失敗時的安全錯誤摘要；不得保存登入憑證',

    validated_at DATETIME(6) NULL
        COMMENT '完成 Golden Reference 驗證的時間',

    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
        COMMENT 'Golden Reference 驗證記錄建立時間',

    PRIMARY KEY (id),

    UNIQUE KEY uq_cgv_run_case (
        calculation_run_id,
        golden_test_case_id
    ),

    KEY idx_cgv_status (
        validation_status,
        created_at
    ),

    KEY idx_cgv_golden_case (
        golden_test_case_id
    ),

    CONSTRAINT fk_cgv_calculation_run
        FOREIGN KEY (calculation_run_id)
        REFERENCES calculation_runs(id),

    CONSTRAINT fk_cgv_golden_case
        FOREIGN KEY (golden_test_case_id)
        REFERENCES formula_golden_test_cases(id)

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COMMENT='系統公式計算結果與四張 Excel Golden Reference 的自動對帳及驗證記錄';

-- ------------------------------------------------------------
-- 15. Permission Seed
-- ------------------------------------------------------------
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_CALCULATE',
    'FORMULA',
    'ACTION',
    '執行金融公式計算',
    'Run Formula Calculation',
    '允許使用者執行金融產品公式及 Waterfall 計算',
    'Allows the user to execute financial product formula and waterfall calculations',
    1,
    200,
    NOW(6)
),
(
    'FORMULA_RUN_VIEW',
    'FORMULA',
    'FUNCTION',
    '查看公式計算記錄',
    'View Calculation Runs',
    '允許查看歷史計算、輸入、輸出及 Waterfall 結果',
    'Allows viewing historical calculation runs, inputs, outputs and waterfall results',
    1,
    210,
    NOW(6)
),
(
    'FORMULA_GOLDEN_VALIDATE',
    'FORMULA',
    'ACTION',
    '執行 Excel Golden Reference 驗證',
    'Run Golden Reference Validation',
    '允許將系統計算結果與 Excel Golden Reference 自動進行對帳驗證',
    'Allows system calculation results to be automatically validated against Excel Golden Reference',
    1,
    220,
    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);

-- ============================================================
-- 16. FINAL VERIFICATION
-- ============================================================

SELECT 'VERIFY calculation_runs' AS verify_section;

SELECT
    COLUMN_NAME AS column_name,
    COLUMN_TYPE AS column_type,
    COLUMN_COMMENT AS chinese_comment
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
AND TABLE_NAME = 'calculation_runs'
AND COLUMN_NAME IN (
    'engine_code',
    'engine_version',
    'execution_ms',
    'error_message',
    'created_by_super_admin_id'
)
ORDER BY ORDINAL_POSITION;

SELECT 'VERIFY calculation_snapshots' AS verify_section;

SELECT
    COLUMN_NAME AS column_name,
    COLUMN_TYPE AS column_type,
    COLUMN_COMMENT AS chinese_comment
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
AND TABLE_NAME = 'calculation_snapshots'
AND COLUMN_NAME = 'snapshot_schema_version';

SELECT 'VERIFY waterfall_schedules' AS verify_section;

SELECT
    COLUMN_NAME AS column_name,
    COLUMN_TYPE AS column_type,
    COLUMN_COMMENT AS chinese_comment
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
AND TABLE_NAME = 'waterfall_schedules'
AND COLUMN_NAME IN (
    'formula_version_id',
    'engine_code'
)
ORDER BY ORDINAL_POSITION;

SELECT 'VERIFY golden validation table' AS verify_section;

SELECT
    TABLE_NAME AS table_name,
    TABLE_COMMENT AS chinese_table_comment
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @db_name
AND TABLE_NAME = 'calculation_golden_validations';

SELECT 'VERIFY formula permissions' AS verify_section;

SELECT
    permission_code AS permission_code,
    name_zh AS name_zh,
    name_en AS name_en
FROM permission_catalog
WHERE permission_code IN (
    'FORMULA_CALCULATE',
    'FORMULA_RUN_VIEW',
    'FORMULA_GOLDEN_VALIDATE'
)
ORDER BY permission_code;

SELECT 'iBilik Capital Database v1.8c migration completed.' AS migration_status;

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