-- V20: 去除活动名称唯一约束 — 删除 t_biz_contest 上包含 contest_name 的唯一索引（保留主键）
-- 背景: 允许同一租户/系统内创建同名活动，不再强制活动名称全局或联合唯一

DROP PROCEDURE IF EXISTS tmp_drop_biz_contest_name_uniques;

DELIMITER //
CREATE PROCEDURE tmp_drop_biz_contest_name_uniques()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE ix VARCHAR(128);
  DECLARE cur CURSOR FOR
    SELECT DISTINCT s.INDEX_NAME
    FROM information_schema.STATISTICS s
    WHERE s.TABLE_SCHEMA = DATABASE()
      AND s.TABLE_NAME = 't_biz_contest'
      AND s.NON_UNIQUE = 0
      AND s.INDEX_NAME <> 'PRIMARY'
      AND EXISTS (
        SELECT 1 FROM information_schema.STATISTICS s2
        WHERE s2.TABLE_SCHEMA = DATABASE()
          AND s2.TABLE_NAME = 't_biz_contest'
          AND s2.INDEX_NAME = s.INDEX_NAME
          AND s2.COLUMN_NAME = 'contest_name'
      );
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  ix_loop: LOOP
    FETCH cur INTO ix;
    IF done THEN
      LEAVE ix_loop;
    END IF;
    SET @dsql = CONCAT('ALTER TABLE t_biz_contest DROP INDEX `', REPLACE(ix, '`', '``'), '`');
    PREPARE ps FROM @dsql;
    EXECUTE ps;
    DEALLOCATE PREPARE ps;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

CALL tmp_drop_biz_contest_name_uniques();
DROP PROCEDURE IF EXISTS tmp_drop_biz_contest_name_uniques;
