-- User contact mapping rule migration
-- Goal:
-- 1) Remove global unique email constraint on users
-- 2) Enforce unique email inside each family
-- 3) Add lookup indexes for email/phone

USE ggn_famlynk;

-- Drop legacy global unique index on email if present.
SET @legacy_email_unique := (
  SELECT index_name
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'users'
    AND non_unique = 0
    AND index_name IN ('uq_users_email', 'email')
  LIMIT 1
);
SET @drop_legacy_sql := IF(
  @legacy_email_unique IS NULL,
  'SELECT 1',
  CONCAT('ALTER TABLE users DROP INDEX ', @legacy_email_unique)
);
PREPARE stmt1 FROM @drop_legacy_sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

-- Add family-level unique email index.
SET @has_family_email := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'users'
    AND index_name = 'uq_users_family_email'
);
SET @add_family_email_sql := IF(
  @has_family_email > 0,
  'SELECT 1',
  'ALTER TABLE users ADD UNIQUE INDEX uq_users_family_email (family_id, email)'
);
PREPARE stmt2 FROM @add_family_email_sql;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

-- Add lookup indexes for email and phone.
SET @has_email_idx := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'users'
    AND index_name = 'idx_users_email'
);
SET @add_email_idx_sql := IF(
  @has_email_idx > 0,
  'SELECT 1',
  'ALTER TABLE users ADD INDEX idx_users_email (email)'
);
PREPARE stmt3 FROM @add_email_idx_sql;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;

SET @has_phone_idx := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'users'
    AND index_name = 'idx_users_phone'
);
SET @add_phone_idx_sql := IF(
  @has_phone_idx > 0,
  'SELECT 1',
  'ALTER TABLE users ADD INDEX idx_users_phone (phone)'
);
PREPARE stmt4 FROM @add_phone_idx_sql;
EXECUTE stmt4;
DEALLOCATE PREPARE stmt4;