-- ============================================================
-- iBilik Capital Database v2.1
-- Phase 5 v13 Integration Hardening
-- Target: MySQL 5.7.44
-- Purpose:
--   Clean duplicate calculation_runs Super Admin index/FK
--   left by historical incremental migrations.
-- No financial data is changed.
-- ============================================================

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

-- Keep fk_calc_run_super_admin and idx_calc_run_super_admin.
-- Remove historical duplicate fk_calc_super_admin if it exists.
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_super_admin'
          AND CONSTRAINT_TYPE='FOREIGN KEY'
    ),
    'ALTER TABLE calculation_runs DROP FOREIGN KEY fk_calc_super_admin',
    'SELECT ''SKIP: fk_calc_super_admin does not exist'' AS migration_message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Remove historical duplicate index only after duplicate FK is gone.
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_super_admin'
    ),
    'ALTER TABLE calculation_runs DROP INDEX idx_calc_super_admin',
    'SELECT ''SKIP: idx_calc_super_admin does not exist'' AS migration_message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT
  CONSTRAINT_NAME,
  CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA=@db_name
  AND TABLE_NAME='calculation_runs'
  AND CONSTRAINT_NAME IN ('fk_calc_super_admin','fk_calc_run_super_admin');

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

SELECT 'iBilik Capital Database v2.1 integration hardening completed.' AS migration_status;
