-- Ireland Team Users Migration
-- Rocky's brothers - callers working 6-10pm Ireland time (5-9am AEST next day)
-- Date: Jan 21, 2026

-- ============================================
-- Add Ireland team to user_xp
-- ============================================

-- Jake McLoughlin
INSERT INTO user_xp (user_id, display_name, email, role, level, total_xp, is_active)
SELECT
  COALESCE(
    (SELECT id FROM auth.users WHERE email = 'jakemcloughlin24@hotmail.com'),
    gen_random_uuid()
  ),
  'Jake',
  'jakemcloughlin24@hotmail.com',
  'caller',
  1,
  0,
  true
WHERE NOT EXISTS (
  SELECT 1 FROM user_xp WHERE email = 'jakemcloughlin24@hotmail.com'
);

-- Roy McLoughlin
INSERT INTO user_xp (user_id, display_name, email, role, level, total_xp, is_active)
SELECT
  COALESCE(
    (SELECT id FROM auth.users WHERE email = 'roymcloughlin97@gmail.com'),
    gen_random_uuid()
  ),
  'Roy',
  'roymcloughlin97@gmail.com',
  'caller',
  1,
  0,
  true
WHERE NOT EXISTS (
  SELECT 1 FROM user_xp WHERE email = 'roymcloughlin97@gmail.com'
);

-- Daniel McLoughlin
INSERT INTO user_xp (user_id, display_name, email, role, level, total_xp, is_active)
SELECT
  COALESCE(
    (SELECT id FROM auth.users WHERE email = 'danielmcloughlin@hotmail.com'),
    gen_random_uuid()
  ),
  'Daniel',
  'danielmcloughlin@hotmail.com',
  'caller',
  1,
  0,
  true
WHERE NOT EXISTS (
  SELECT 1 FROM user_xp WHERE email = 'danielmcloughlin@hotmail.com'
);

-- ============================================
-- Update user_id when they sign in for first time
-- This trigger ensures user_xp.user_id matches auth.users.id
-- ============================================

CREATE OR REPLACE FUNCTION sync_user_xp_user_id()
RETURNS TRIGGER AS $$
BEGIN
  -- When a new auth user is created, update any existing user_xp record
  -- that has their email but a different (placeholder) user_id
  UPDATE user_xp
  SET user_id = NEW.id
  WHERE email = NEW.email
    AND user_id != NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Drop if exists to avoid conflicts
DROP TRIGGER IF EXISTS sync_user_xp_on_auth_insert ON auth.users;

-- Create trigger (only runs on new user signups)
CREATE TRIGGER sync_user_xp_on_auth_insert
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION sync_user_xp_user_id();
