-- Fix unique constraints on pass_random_unique_code table
-- This script removes the unique constraint on template_uid and adds a composite unique constraint on (field_uid, code)

-- Step 1: Show current constraints (for reference)
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'pass_random_unique_code'
AND CONSTRAINT_NAME != 'PRIMARY';

-- Step 2: Drop unique constraint/index on template_uid
-- Try different possible constraint names
DROP INDEX IF EXISTS `template_uid` ON `pass_random_unique_code`;
DROP INDEX IF EXISTS `idx_template_uid` ON `pass_random_unique_code`;
DROP INDEX IF EXISTS `uk_template_uid` ON `pass_random_unique_code`;

-- Step 3: Add field_uid column if it doesn't exist
SET @col_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'pass_random_unique_code' 
    AND COLUMN_NAME = 'field_uid'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `pass_random_unique_code` ADD COLUMN `field_uid` VARCHAR(255) NULL AFTER `pass_uid`',
    'SELECT "Column field_uid already exists" AS message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 4: Drop any existing composite indexes
DROP INDEX IF EXISTS `idx_field_uid_code` ON `pass_random_unique_code`;
DROP INDEX IF EXISTS `idx_field_uid_code_unique` ON `pass_random_unique_code`;

-- Step 5: Create new unique index on (field_uid, code)
-- This ensures codes are unique per field, but allows same code in different fields
CREATE UNIQUE INDEX `idx_field_uid_code_unique` ON `pass_random_unique_code` (`field_uid`, `code`);

-- Verification: Show updated constraints
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'pass_random_unique_code'
AND CONSTRAINT_NAME != 'PRIMARY';

