create_praveen_super_admin.sql

-- ============================================
-- CREATE PRAVEEN KUMAR AS PHIXON SUPER ADMIN
-- Phixon Internal Staff Management System
-- ============================================

/*
  INSTRUCTIONS:

  Step 1: Create user in Supabase Auth Dashboard first
  -------------------------------------------------------
  Email:    praveen.katkom@phixon.co.in
  Password: Phixon#2024@SuperAdmin!PK
  ✅ Auto Confirm User: YES

  Step 2: Copy the User ID (UUID) from Supabase
  -------------------------------------------------------
  After creating the user, you'll see a UUID like:
  a1b2c3d4-e5f6-7890-abcd-ef1234567890

  Step 3: Replace YOUR_AUTH_USER_ID below and run this script
  -------------------------------------------------------
*/

-- Create Praveen Kumar as Super Admin in Phixon Staff System
DO $$
DECLARE
  v_auth_user_id uuid := 'YOUR_AUTH_USER_ID'; -- ⚠️ REPLACE THIS WITH ACTUAL UUID
  v_exec_dept_id uuid;
  v_staff_id uuid;
BEGIN
  -- Validate auth user ID is provided
  IF v_auth_user_id = 'YOUR_AUTH_USER_ID' THEN
    RAISE EXCEPTION 'Please replace YOUR_AUTH_USER_ID with actual UUID from Supabase Auth';
  END IF;

  -- Get Executive Leadership department ID
  SELECT id INTO v_exec_dept_id
  FROM phixon_departments
  WHERE department_code = 'EXEC';

  IF v_exec_dept_id IS NULL THEN
    RAISE EXCEPTION 'Executive department not found. Please run migrations first.';
  END IF;

  -- Check if staff already exists
  IF EXISTS (SELECT 1 FROM phixon_staff WHERE email = 'praveen.katkom@phixon.co.in') THEN
    RAISE NOTICE 'Staff account already exists for praveen.katkom@phixon.co.in';

    -- Update existing account
    UPDATE phixon_staff
    SET
      auth_user_id = v_auth_user_id,
      employee_id = 'PHIX-0001',
      full_name = 'Katkom Praveen Kumar',
      designation = 'Director & Co-Founder',
      department_id = v_exec_dept_id,
      phone = '+91 7799899957',
      employee_type = 'super_admin',
      permissions = jsonb_build_object(
        'full_access', true,
        'can_manage_staff', true,
        'can_manage_departments', true,
        'can_view_all_logs', true,
        'can_manage_healthcare_users', true,
        'can_manage_system_settings', true,
        'can_view_analytics', true,
        'can_export_data', true
      ),
      is_active = true,
      updated_at = NOW()
    WHERE email = 'praveen.katkom@phixon.co.in'
    RETURNING id INTO v_staff_id;

    RAISE NOTICE 'Updated existing staff account. Staff ID: %', v_staff_id;
  ELSE
    -- Create new staff profile
    INSERT INTO phixon_staff (
      auth_user_id,
      employee_id,
      email,
      full_name,
      designation,
      department_id,
      phone,
      employee_type,
      permissions,
      is_active,
      joined_date
    ) VALUES (
      v_auth_user_id,
      'PHIX-0001',
      'praveen.katkom@phixon.co.in',
      'Katkom Praveen Kumar',
      'Director & Co-Founder',
      v_exec_dept_id,
      '+91 7799899957',
      'super_admin',
      jsonb_build_object(
        'full_access', true,
        'can_manage_staff', true,
        'can_manage_departments', true,
        'can_view_all_logs', true,
        'can_manage_healthcare_users', true,
        'can_manage_system_settings', true,
        'can_view_analytics', true,
        'can_export_data', true
      ),
      true,
      CURRENT_DATE
    ) RETURNING id INTO v_staff_id;

    RAISE NOTICE 'Created new staff account. Staff ID: %', v_staff_id;
  END IF;

  -- Update department head
  UPDATE phixon_departments
  SET head_staff_id = v_staff_id
  WHERE department_code = 'EXEC';

  -- Log the creation
  INSERT INTO phixon_staff_activity_logs (
    staff_id,
    activity_type,
    module,
    action_details
  ) VALUES (
    v_staff_id,
    'account_created',
    'system',
    jsonb_build_object(
      'message', 'Super admin account created/updated',
      'created_by', 'system_setup',
      'timestamp', NOW()
    )
  );

  RAISE NOTICE '✅ Successfully created/updated super admin account for Praveen Kumar';
  RAISE NOTICE '📧 Email: praveen.katkom@phixon.co.in';
  RAISE NOTICE '🔑 Password: Phixon#2024@SuperAdmin!PK';
  RAISE NOTICE '⚠️  IMPORTANT: Change password after first login!';
END $$;

-- ============================================
-- VERIFICATION: Check the account was created
-- ============================================

SELECT
  ps.id,
  ps.employee_id,
  ps.email,
  ps.full_name,
  ps.designation,
  ps.employee_type,
  ps.is_active,
  ps.phone,
  pd.department_name,
  pd.department_code,
  au.email as auth_email,
  au.created_at as auth_created_at,
  au.email_confirmed_at,
  ps.created_at as staff_created_at
FROM phixon_staff ps
LEFT JOIN phixon_departments pd ON ps.department_id = pd.id
LEFT JOIN auth.users au ON ps.auth_user_id = au.id
WHERE ps.email = 'praveen.katkom@phixon.co.in';

-- ============================================
-- OPTIONAL: Create Additional Staff Members
-- ============================================

-- Director 2: Joru Shwetha
-- First create user in Supabase Auth, then run:
/*
DO $$
DECLARE
  v_auth_user_id uuid := 'DIRECTOR_2_AUTH_USER_ID'; -- Replace with actual UUID
  v_exec_dept_id uuid;
BEGIN
  SELECT id INTO v_exec_dept_id FROM phixon_departments WHERE department_code = 'EXEC';

  INSERT INTO phixon_staff (
    auth_user_id,
    email,
    full_name,
    designation,
    department_id,
    phone,
    employee_type,
    permissions,
    is_active
  ) VALUES (
    v_auth_user_id,
    'shwetha.joru@phixon.co.in',
    'Joru Shwetha',
    'Director & Co-Founder',
    v_exec_dept_id,
    '+91 8297585757',
    'super_admin',
    jsonb_build_object('full_access', true, 'can_manage_staff', true),
    true
  );

  RAISE NOTICE 'Created staff account for Joru Shwetha';
END $$;
*/

-- ============================================
-- VIEW ALL PHIXON STAFF
-- ============================================

SELECT
  ps.employee_id,
  ps.full_name,
  ps.email,
  ps.designation,
  ps.employee_type,
  pd.department_name,
  ps.is_active,
  ps.joined_date
FROM phixon_staff ps
LEFT JOIN phixon_departments pd ON ps.department_id = pd.id
ORDER BY ps.created_at ASC;