-- Ensure mobile numbers are unique for mobile login
-- This script checks for duplicates and adds a unique constraint

-- Step 1: Find duplicate mobile numbers
SELECT 
  mobile_number, 
  COUNT(*) as count,
  STRING_AGG(member_id || ' (Client: ' || COALESCE(client_id::text, 'NULL') || ')', ', ') as users
FROM login 
WHERE mobile_number != '0' AND mobile_number IS NOT NULL
GROUP BY mobile_number 
HAVING COUNT(*) > 1;

-- Step 2: If duplicates found, you need to update them manually
-- Example: Update duplicate mobile numbers by appending client_id
-- UPDATE login 
-- SET mobile_number = mobile_number || '_' || client_id
-- WHERE mobile_number IN (
--   SELECT mobile_number FROM login 
--   WHERE mobile_number != '0'
--   GROUP BY mobile_number HAVING COUNT(*) > 1
-- );

-- Step 3: Add unique constraint (run only if no duplicates)
-- Note: This excludes '0' and NULL values from the unique constraint
CREATE UNIQUE INDEX IF NOT EXISTS idx_login_mobile_unique 
ON login(mobile_number) 
WHERE mobile_number != '0' AND mobile_number IS NOT NULL;

-- Step 4: Verify the constraint was created
SELECT 
  indexname, 
  indexdef 
FROM pg_indexes 
WHERE tablename = 'login' 
  AND indexname = 'idx_login_mobile_unique';

-- Step 5: Test - try to insert duplicate (should fail)
-- INSERT INTO login (member_id, mobile_number, password, role_id, name, status)
-- VALUES ('TEST', '9876543210', 'hash', 6, 'Test', 'Y');
-- This should fail if 9876543210 already exists



