-- Migration: Add unique index to notification_logs for strict idempotency
-- Purpose: Prevent duplicate notifications for the same journey step at the database level

USE aayusha_backend;

-- 1. Clean up any existing duplicates first (keep only the first one)
DELETE n1 FROM notification_logs n1
INNER JOIN notification_logs n2 
WHERE n1.id > n2.id 
  AND n1.execution_id = n2.execution_id 
  AND n1.step_id = n2.step_id
  AND n1.execution_id IS NOT NULL
  AND n1.step_id IS NOT NULL;

-- 2. Add the unique index
-- Note: We only apply this to journey-based notifications (where execution_id and step_id are present)
ALTER TABLE notification_logs ADD UNIQUE KEY idx_execution_step (execution_id, step_id);
