-- Lead Assignment System Migration
-- Extends user_xp for user management, adds assignment tracking

-- ============================================
-- PART 1: Extend user_xp for User Management
-- ============================================

-- Add email column for user lookup (links to auth.users email)
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS email VARCHAR(255);

-- Add role column for permissions (admin, closer, caller)
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS role VARCHAR(20) DEFAULT 'caller';

-- Add is_active flag for soft-disable users
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;

-- Create unique index on email for fast lookup (only for non-null emails)
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_xp_email ON user_xp(email) WHERE email IS NOT NULL;

-- Create index for role-based queries
CREATE INDEX IF NOT EXISTS idx_user_xp_role ON user_xp(role);

-- ============================================
-- PART 2: Lead Assignment Tracking
-- ============================================

-- Add assigned_at timestamp to leads for stale detection
ALTER TABLE leads ADD COLUMN IF NOT EXISTS assigned_at TIMESTAMPTZ;

-- Add user_id to communications for activity tracking
ALTER TABLE communications ADD COLUMN IF NOT EXISTS user_id UUID;

-- Index for fast assignment queries (partial index for assigned leads only)
CREATE INDEX IF NOT EXISTS idx_leads_assigned_to ON leads(assigned_to)
WHERE assigned_to IS NOT NULL AND deleted_at IS NULL;

-- Index for stale assignment detection
CREATE INDEX IF NOT EXISTS idx_leads_assigned_at ON leads(assigned_at);

-- Index for user activity queries
CREATE INDEX IF NOT EXISTS idx_communications_user_id ON communications(user_id);

-- ============================================
-- PART 3: Seed Initial Users (Tony + Michael)
-- ============================================

-- Update Tony's user_xp record if exists
UPDATE user_xp
SET email = 'tonymccabe53@gmail.com', role = 'closer', is_active = true
WHERE user_id IN (
  SELECT id FROM auth.users WHERE email = 'tonymccabe53@gmail.com'
);

-- Update Michael's user_xp record if exists
UPDATE user_xp
SET email = 'admin@rateright.com.au', role = 'admin', is_active = true
WHERE user_id IN (
  SELECT id FROM auth.users WHERE email = 'admin@rateright.com.au'
);

-- Insert Tony if no user_xp record exists
INSERT INTO user_xp (user_id, display_name, email, role, level, total_xp, is_active)
SELECT
  id,
  'Tony',
  'tonymccabe53@gmail.com',
  'closer',
  1,
  0,
  true
FROM auth.users
WHERE email = 'tonymccabe53@gmail.com'
AND id NOT IN (SELECT user_id FROM user_xp WHERE user_id IS NOT NULL)
ON CONFLICT DO NOTHING;

-- Insert Michael if no user_xp record exists
INSERT INTO user_xp (user_id, display_name, email, role, level, total_xp, is_active)
SELECT
  id,
  'Michael',
  'admin@rateright.com.au',
  'admin',
  1,
  0,
  true
FROM auth.users
WHERE email = 'admin@rateright.com.au'
AND id NOT IN (SELECT user_id FROM user_xp WHERE user_id IS NOT NULL)
ON CONFLICT DO NOTHING;
