User Management and Cleanup Guide

# Step 1: Dry-run to identify test users (no changes)
npm run db:cleanup:test-users -- --dry-run --env production

# Step 2: Preview what would be deleted
npm run db:cleanup:test-users -- --env production

# Step 3: Actually delete (requires --confirm flag)
npm run db:cleanup:test-users -- --env production --confirm

-- Identify test users (dry-run first)
SELECT id, email, created_at 
FROM auth.users
WHERE email LIKE '%test%' 
   OR email LIKE '%example%'
   OR email LIKE 'e2e-%'
   OR email LIKE 'rls-%'
   OR email LIKE '%debug%'
   OR email LIKE '%staging%'
ORDER BY created_at DESC;

-- Delete test users (CAREFUL - this is permanent!)
-- Only run after verifying the SELECT query above
DELETE FROM auth.users
WHERE email LIKE '%test%' 
   OR email LIKE '%example%'
   OR email LIKE 'e2e-%'
   OR email LIKE 'rls-%'
   OR email LIKE '%debug%'
   OR email LIKE '%staging%';

-- Mark test users as archived via metadata
UPDATE auth.users
SET raw_user_meta_data = raw_user_meta_data || '{"archived": true}'::jsonb
WHERE email LIKE '%test%' 
   OR email LIKE '%example%'
   OR email LIKE 'e2e-%'
   OR email LIKE 'rls-%'
   OR email LIKE '%debug%'
   OR email LIKE '%staging%';

-- Query to filter out archived users
SELECT * FROM auth.users 
WHERE (raw_user_meta_data->>'archived')::boolean IS NOT TRUE;

-- Create tracking table
CREATE TABLE IF NOT EXISTS pf_user_archive_status (
  user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  archived BOOLEAN DEFAULT false,
  archived_at TIMESTAMPTZ,
  archived_by UUID REFERENCES auth.users(id),
  reason TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Enable RLS
ALTER TABLE pf_user_archive_status ENABLE ROW LEVEL SECURITY;

-- Allow platform admins to manage
CREATE POLICY "Platform admins manage archive status"
ON pf_user_archive_status
FOR ALL
USING (pf_has_system_role(auth.uid(), 'platform_admin'));

-- Archive test users
INSERT INTO pf_user_archive_status (user_id, archived, archived_at)
SELECT id, true, now()
FROM auth.users
WHERE email LIKE '%test%' 
   OR email LIKE '%example%'
   OR email LIKE 'e2e-%'
   OR email LIKE 'rls-%'
   OR email LIKE '%debug%'
   OR email LIKE '%staging%'
ON CONFLICT (user_id) DO UPDATE SET archived = true, archived_at = now();

-- Query non-archived users by joining
SELECT u.* FROM auth.users u
LEFT JOIN pf_user_archive_status a ON a.user_id = u.id
WHERE a.archived IS NOT TRUE OR a.user_id IS NULL;

-- Delete users created in last 7 days (if all are test users)
DELETE FROM auth.users
WHERE created_at > NOW() - INTERVAL '7 days'
  AND (email LIKE '%test%' OR email LIKE '%example%' OR email LIKE 'e2e-%' OR email LIKE 'rls-%');

# Clean up test users older than 30 days
# Run as scheduled job or manual cleanup

-- User types breakdown
SELECT 
  CASE 
    WHEN email LIKE 'e2e-%' THEN 'E2E Tests'
    WHEN email LIKE 'rls-%' THEN 'RLS Tests'
    WHEN email LIKE '%debug%' OR email LIKE '%test%' THEN 'Debug/Test'
    WHEN email LIKE '%example%' OR email LIKE '%staging%' THEN 'Seed Data'
    ELSE 'Production/Real'
  END as user_type,
  COUNT(*) as count
FROM auth.users
GROUP BY user_type
ORDER BY count DESC;

-- Users created in last 7 days
SELECT 
  DATE(created_at) as creation_date,
  COUNT(*) as users_created
FROM auth.users
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY DATE(created_at)
ORDER BY creation_date DESC;

-- All test users
SELECT id, email, created_at 
FROM auth.users
WHERE email LIKE '%test%' 
   OR email LIKE '%example%'
   OR email LIKE 'e2e-%'
   OR email LIKE 'rls-%'
   OR email LIKE '%debug%'
   OR email LIKE '%staging%'
ORDER BY created_at DESC;