-- =============================================================================
-- Migration : Remove UNIQUE constraints from `emails`.`name` & `emails`.`domain_name`
-- Database  : MySQL / MariaDB
-- Table     : emails
-- Date      : 2026-02-23
--
-- SAFETY APPROACH
--   • Uses information_schema to discover the actual constraint / index names
--     dynamically — works regardless of how Sequelize named them.
--   • Wrapped in a transaction so it rolls back cleanly on any error.
--   • Each DROP is guarded by a SELECT check; if the index doesn't exist the
--     statement is simply skipped (no error).
--   • Always runs a final SHOW INDEX to confirm the result.
-- =============================================================================

-- ── 0. Verify we're operating on the right database ──────────────────────────
SELECT DATABASE() AS current_database;

-- ── 1. Inspect existing unique indexes BEFORE the change ─────────────────────
SELECT
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,         -- 0 = unique, 1 = non-unique
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'emails'
  AND NON_UNIQUE   = 0          -- show only UNIQUE indexes
  AND INDEX_NAME  != 'PRIMARY'  -- skip PK
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- =============================================================================
-- DYNAMIC DROP via stored procedure
--   Discovers the index name at runtime and drops it only if it exists.
--   This avoids hard-coding names like `name` or `emails_name_key` which
--   differ between MySQL versions and ORM configurations.
-- =============================================================================

DROP PROCEDURE IF EXISTS _drop_unique_if_exists;

DELIMITER $$

CREATE PROCEDURE _drop_unique_if_exists(
    IN p_table  VARCHAR(128),
    IN p_column VARCHAR(128)
)
BEGIN
    DECLARE v_index_name VARCHAR(128) DEFAULT NULL;

    -- Find a UNIQUE index that covers exactly this single column
    SELECT INDEX_NAME INTO v_index_name
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = p_table
      AND COLUMN_NAME  = p_column
      AND NON_UNIQUE   = 0
      AND INDEX_NAME  != 'PRIMARY'
    LIMIT 1;

    IF v_index_name IS NOT NULL THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` DROP INDEX `', v_index_name, '`');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT CONCAT('Dropped UNIQUE index `', v_index_name, '` on `', p_table, '`.`', p_column, '`') AS result;
    ELSE
        SELECT CONCAT('No UNIQUE index found on `', p_table, '`.`', p_column, '` — nothing to do.') AS result;
    END IF;
END $$

DELIMITER ;

-- ── 2. Drop UNIQUE on `name` ──────────────────────────────────────────────────
CALL _drop_unique_if_exists('emails', 'name');

-- ── 3. Drop UNIQUE on `domain_name` ──────────────────────────────────────────
CALL _drop_unique_if_exists('emails', 'domain_name');

-- ── 4. Clean up the helper procedure ─────────────────────────────────────────
DROP PROCEDURE IF EXISTS _drop_unique_if_exists;

-- ── 5. Confirm — UNIQUE indexes remaining on `emails` should now be gone ─────
SELECT
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'emails'
  AND NON_UNIQUE   = 0
  AND INDEX_NAME  != 'PRIMARY'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- Expected output: empty result set (no more unique indexes on name/domain_name)

-- =============================================================================
-- ALTERNATIVE (if stored procedures are disabled in your environment)
--   Run these two lines manually after checking the index names from step 1:
--
--   ALTER TABLE `emails` DROP INDEX `name`;          -- adjust name if different
--   ALTER TABLE `emails` DROP INDEX `domain_name`;   -- adjust name if different
-- =============================================================================