-- Migration: Add unique index to prevent duplicate emails per project
-- Date: 2025-02-09
-- Description: This migration adds a unique composite index on (email, project_id)
--              in the peoples table to prevent duplicate registrations

USE survey_manager;

-- First, remove any duplicate entries that might exist
-- Keep only the first (oldest) entry for each email+project_id combination
DELETE p1 FROM peoples p1
INNER JOIN peoples p2
WHERE p1.id > p2.id
AND p1.email = p2.email
AND p1.project_id = p2.project_id;

-- Add unique index to enforce constraint at database level
-- This will prevent duplicate email registrations for the same project
ALTER TABLE peoples
ADD UNIQUE INDEX idx_unique_email_project (email, project_id);

-- Verify the index was created
SHOW INDEX FROM peoples WHERE Key_name = 'idx_unique_email_project';
