-- =============================================================================
-- Migration : Remove UNIQUE constraints from `emailaccounts`.`name` & `emailaccounts`.`email_account`
-- Database  : MySQL / MariaDB
-- Table     : emailaccounts
-- Date      : 2026-02-23
--
-- SAFETY APPROACH  (same as remove_unique_emails_name_domain.sql)
--   • Discovers constraint names dynamically via information_schema
--   • Safe to run even if constraints were already removed
--   • Confirms result with a final SHOW INDEX query
-- =============================================================================

-- ── 0. Verify database context ────────────────────────────────────────────────
SELECT DATABASE() AS current_database;

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

-- =============================================================================
-- Dynamic drop via stored procedure (no hard-coded constraint names)
-- =============================================================================
DROP PROCEDURE IF EXISTS _drop_unique_if_exists_ea;

DELIMITER $$

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

    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_ea('emailaccounts', 'name');

-- ── 3. Drop UNIQUE on `email_account` ────────────────────────────────────────
CALL _drop_unique_if_exists_ea('emailaccounts', 'email_account');

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

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

-- Expected: empty result set

-- =============================================================================
-- ALTERNATIVE (if stored procedures are disabled)
--   Check the index names from step 1 above, then run:
--
--   ALTER TABLE `emailaccounts` DROP INDEX `name`;
--   ALTER TABLE `emailaccounts` DROP INDEX `email_account`;
-- =============================================================================