-- Drop and recreate pass_random_unique_code table with proper structure
-- This supports multiple randomUniqueCode fields per pass

-- Step 1: Drop the existing table (WARNING: This will delete all existing data)
DROP TABLE IF EXISTS `pass_random_unique_code`;

-- Step 2: Create the new table with field_uid column
CREATE TABLE `pass_random_unique_code` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `template_uid` VARCHAR(255) NOT NULL,
  `code` VARCHAR(255) NOT NULL,
  `pass_uid` VARCHAR(255) NOT NULL,
  `field_uid` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  -- Unique constraint on (field_uid, code) ensures codes are unique per field
  -- This allows the same code to exist in different fields, but not twice in the same field
  UNIQUE KEY `idx_field_uid_code_unique` (`field_uid`, `code`),
  -- Index for faster lookups by template_uid
  KEY `idx_template_uid` (`template_uid`),
  -- Index for faster lookups by pass_uid
  KEY `idx_pass_uid` (`pass_uid`),
  -- Index for faster lookups by field_uid
  KEY `idx_field_uid` (`field_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Note:
-- - field_uid can be NULL for backward compatibility (though new records should always have it)
-- - The unique constraint on (field_uid, code) ensures:
--   * Same code cannot exist twice for the same field_uid
--   * Same code CAN exist in different fields (different field_uid values)
--   * NULL field_uid values are allowed (for backward compatibility)
-- - Multiple codes per template are now allowed (different field_uid values)

