-- Make member_id column unique across the entire system
-- This prevents duplicate member_ids and solves mobile login conflicts

-- Step 1: Check for existing duplicate member_ids
SELECT 
  member_id, 
  COUNT(*) as count,
  STRING_AGG(
    'ID:' || login_id::text || 
    ' Client:' || COALESCE(client_id::text, 'NULL') || 
    ' Name:' || name, 
    ' | '
  ) as users
FROM login 
GROUP BY member_id 
HAVING COUNT(*) > 1
ORDER BY count DESC;

-- Step 2: If duplicates exist, you MUST rename them before adding unique constraint
-- Example: Rename duplicates by appending login_id
-- UPDATE login 
-- SET member_id = member_id || '_' || login_id::text
-- WHERE member_id IN (
--   SELECT member_id FROM login GROUP BY member_id HAVING COUNT(*) > 1
-- );

-- Step 3: Add UNIQUE constraint on member_id
-- WARNING: This will fail if duplicates exist. Fix duplicates first (Step 2)
ALTER TABLE login ADD CONSTRAINT login_member_id_unique UNIQUE (member_id);

-- Step 4: Verify the constraint was added
SELECT 
  con.conname as constraint_name,
  con.contype as constraint_type,
  att.attname as column_name
FROM pg_constraint con
JOIN pg_attribute att ON att.attnum = ANY(con.conkey)
WHERE con.conrelid = 'login'::regclass
  AND con.conname = 'login_member_id_unique';

-- Step 5: Test the constraint (should fail if member_id already exists)
-- INSERT INTO login (member_id, password, role_id, name, status)
-- VALUES ('TEST_DUPLICATE', 'hash', 6, 'Test', 'Y');
-- 
-- INSERT INTO login (member_id, password, role_id, name, status)
-- VALUES ('TEST_DUPLICATE', 'hash', 6, 'Test2', 'Y');
-- ↑ This should fail with: ERROR: duplicate key value violates unique constraint

-- Success message
SELECT 'member_id is now UNIQUE across the entire system!' as status;



