-- ============================================================
-- iBilik Capital Database v2.2
-- Master Source v1.0.1 Hardening
-- Target: MySQL 5.7.44
-- 所有新增欄位均使用中文 COMMENT
-- ============================================================
SET NAMES utf8mb4;
SET @db_name := DATABASE();

-- 1. Calculation Run confirmation attribution
SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='calculation_runs' AND COLUMN_NAME='confirmed_by_user_id'),
  'SELECT ''SKIP confirmed_by_user_id'' AS migration_message',
  'ALTER TABLE calculation_runs ADD COLUMN confirmed_by_user_id BIGINT UNSIGNED NULL COMMENT ''確認此計算結果的一般使用者主鍵；Super Admin 確認時為空'' AFTER confirmed_at'
);
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='confirmed_by_super_admin_id'),
  'SELECT ''SKIP confirmed_by_super_admin_id'' AS migration_message',
  'ALTER TABLE calculation_runs ADD COLUMN confirmed_by_super_admin_id BIGINT UNSIGNED NULL COMMENT ''確認此計算結果的超級管理員主鍵；一般使用者確認時為空'' AFTER confirmed_by_user_id'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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_confirm_user'),
  'SELECT ''SKIP idx_calc_confirm_user'' AS migration_message',
  'ALTER TABLE calculation_runs ADD KEY idx_calc_confirm_user (confirmed_by_user_id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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_confirm_super'),
  'SELECT ''SKIP idx_calc_confirm_super'' AS migration_message',
  'ALTER TABLE calculation_runs ADD KEY idx_calc_confirm_super (confirmed_by_super_admin_id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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_confirm_user' AND CONSTRAINT_TYPE='FOREIGN KEY'),
  'SELECT ''SKIP fk_calc_confirm_user'' AS migration_message',
  'ALTER TABLE calculation_runs ADD CONSTRAINT fk_calc_confirm_user FOREIGN KEY (confirmed_by_user_id) REFERENCES sys_users(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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_confirm_super' AND CONSTRAINT_TYPE='FOREIGN KEY'),
  'SELECT ''SKIP fk_calc_confirm_super'' AS migration_message',
  'ALTER TABLE calculation_runs ADD CONSTRAINT fk_calc_confirm_super FOREIGN KEY (confirmed_by_super_admin_id) REFERENCES sys_super_admins(id)'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. Enforce one run number per Deal, but only if historical data contains no duplicates.
SET @duplicate_run_no := (
  SELECT COUNT(*) FROM (
    SELECT deal_id,run_no FROM calculation_runs GROUP BY deal_id,run_no HAVING COUNT(*)>1
  ) x
);
SET @sql := IF(
  EXISTS(SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='calculation_runs' AND INDEX_NAME='uq_calc_deal_run_no'),
  'SELECT ''SKIP uq_calc_deal_run_no already exists'' AS migration_message',
  IF(@duplicate_run_no=0,
     'ALTER TABLE calculation_runs ADD UNIQUE KEY uq_calc_deal_run_no (deal_id,run_no)',
     'SELECT ''WARNING: duplicate calculation run numbers exist; unique key not added'' AS migration_message')
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Verification
SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='calculation_runs'
AND COLUMN_NAME IN ('confirmed_by_user_id','confirmed_by_super_admin_id')
ORDER BY ORDINAL_POSITION;

SELECT INDEX_NAME,NON_UNIQUE,COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME='calculation_runs'
AND INDEX_NAME IN ('idx_calc_confirm_user','idx_calc_confirm_super','uq_calc_deal_run_no')
ORDER BY INDEX_NAME,SEQ_IN_INDEX;

SELECT 'iBilik Capital Database v2.2 Master Source hardening completed.' AS migration_status;
