-- ============================================
-- DEMO ACCOUNT FOR APP STORE REVIEW
-- ============================================
-- Creates a pre-verified demo account for Apple's App Review team.
--
-- IMPORTANT: 
-- - Run this on PRODUCTION database before App Store submission
-- - Generate a secure password and update the encrypted_password hash
-- - Update the APP_STORE_CHECKLIST.md with the actual password
--
-- Usage:
--   1. Generate password hash: Use Supabase Auth API or dashboard
--   2. Replace '$2a$10$...' below with actual bcrypt hash
--   3. Run: supabase db push --include-seed (or run directly in SQL editor)
--
-- Demo Account Credentials:
--   Email: review@dirt.app
--   Password: [Set before submission - see APP_STORE_CHECKLIST.md]

-- Fixed UUID for demo account (deterministic, idempotent)
DO $$
DECLARE
  demo_user_id UUID := 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee';
  demo_email TEXT := 'review@dirt.app';
  -- IMPORTANT: Replace this hash with actual bcrypt hash of your chosen password
  -- Generate via: SELECT crypt('YourSecurePassword123!', gen_salt('bf'));
  -- Or use Supabase Auth API to create the user, then export the hash
  demo_password_hash TEXT := '$2a$10$REPLACE_WITH_ACTUAL_BCRYPT_HASH_BEFORE_SUBMISSION';
BEGIN
  -- ============================================
  -- AUTH USER (login-capable)
  -- ============================================
  INSERT INTO auth.users (
    id,
    instance_id,
    aud,
    role,
    email,
    encrypted_password,
    email_confirmed_at,
    confirmation_sent_at,
    raw_app_meta_data,
    raw_user_meta_data,
    is_super_admin,
    created_at,
    updated_at,
    phone,
    phone_confirmed_at,
    confirmation_token,
    recovery_token,
    email_change_token_new,
    email_change
  )
  VALUES (
    demo_user_id,
    '00000000-0000-0000-0000-000000000000',
    'authenticated',
    'authenticated',
    demo_email,
    demo_password_hash,
    NOW() - INTERVAL '30 days',  -- Email verified
    NOW() - INTERVAL '30 days',
    jsonb_build_object(
      'provider', 'email',
      'providers', ARRAY['email']
    ),
    jsonb_build_object(
      'username', 'app_reviewer',
      'full_name', 'App Review'
    ),
    FALSE,
    NOW() - INTERVAL '30 days',
    NOW(),
    NULL,
    NULL,
    '',
    '',
    '',
    ''
  )
  ON CONFLICT (id) DO UPDATE SET
    email = EXCLUDED.email,
    encrypted_password = EXCLUDED.encrypted_password,
    email_confirmed_at = EXCLUDED.email_confirmed_at,
    raw_app_meta_data = EXCLUDED.raw_app_meta_data,
    raw_user_meta_data = EXCLUDED.raw_user_meta_data,
    updated_at = NOW();

  -- Auth identity (required for email login)
  INSERT INTO auth.identities (
    id,
    user_id,
    identity_data,
    provider,
    provider_id,
    last_sign_in_at,
    created_at,
    updated_at
  )
  VALUES (
    demo_user_id,
    demo_user_id,
    jsonb_build_object(
      'sub', demo_user_id::TEXT,
      'email', demo_email,
      'email_verified', true
    ),
    'email',
    demo_email,
    NOW(),
    NOW() - INTERVAL '30 days',
    NOW()
  )
  ON CONFLICT (provider, provider_id) DO UPDATE SET
    identity_data = EXCLUDED.identity_data,
    updated_at = NOW();

  -- ============================================
  -- APP USER (public.users)
  -- ============================================
  INSERT INTO public.users (
    id,
    email,
    username,
    gender,
    date_of_birth,
    city,
    state,
    country,
    -- Verification flags (all verified for demo)
    email_verified,
    phone_verified,
    id_verified,
    id_verification_status,
    id_verified_at,
    -- Access (active, not waitlisted)
    access_status,
    activated_at,
    -- Subscription
    subscription_tier,
    -- Privacy
    google_indexing_enabled,
    -- Not banned
    is_banned,
    -- Timestamps
    created_at,
    updated_at,
    last_active_at
  )
  VALUES (
    demo_user_id,
    demo_email,
    'app_reviewer',
    'prefer_not_to_say',
    '1990-01-01',
    'Cupertino',
    'CA',
    'US',
    -- All verification bypassed
    TRUE,   -- email_verified
    FALSE,  -- phone_verified (not required)
    TRUE,   -- id_verified (bypassed for demo)
    'verified',
    NOW() - INTERVAL '7 days',
    -- Active access (not on waitlist)
    'active',
    NOW() - INTERVAL '7 days',
    -- Free tier
    'free',
    -- Privacy
    FALSE,  -- Don't index demo content
    -- Not banned
    FALSE,
    -- Timestamps
    NOW() - INTERVAL '30 days',
    NOW(),
    NOW()
  )
  ON CONFLICT (id) DO UPDATE SET
    email = EXCLUDED.email,
    email_verified = EXCLUDED.email_verified,
    id_verified = EXCLUDED.id_verified,
    id_verification_status = EXCLUDED.id_verification_status,
    id_verified_at = EXCLUDED.id_verified_at,
    access_status = EXCLUDED.access_status,
    activated_at = EXCLUDED.activated_at,
    is_banned = FALSE,
    updated_at = NOW(),
    last_active_at = NOW();

  -- ============================================
  -- ENSURE NO WAITLIST ENTRY
  -- ============================================
  -- Delete any waitlist entry to ensure active status
  DELETE FROM waitlist_entries WHERE user_id = demo_user_id;

  RAISE NOTICE 'Demo account created/updated: %', demo_email;
  RAISE NOTICE 'User ID: %', demo_user_id;
  RAISE NOTICE '';
  RAISE NOTICE 'IMPORTANT: Update encrypted_password with actual bcrypt hash before submission!';
END $$;

-- ============================================
-- SAMPLE REVIEWS FOR DEMO FEED
-- ============================================
-- Ensure there's content visible when reviewer logs in.
-- These reviews are authored by the existing seed users, not the demo account.

-- Add flag column to existing reviews if missing (for red/green flag feature)
UPDATE public.reviews 
SET flag = 'red'
WHERE id = '10000000-0000-0000-0000-000000000001' 
  AND flag IS NULL;

UPDATE public.reviews 
SET flag = 'green'
WHERE id = '10000000-0000-0000-0000-000000000002' 
  AND flag IS NULL;

UPDATE public.reviews 
SET flag = 'red'
WHERE id = '10000000-0000-0000-0000-000000000003' 
  AND flag IS NULL;

UPDATE public.reviews 
SET flag = 'green'
WHERE id = '10000000-0000-0000-0000-000000000004' 
  AND flag IS NULL;

UPDATE public.reviews 
SET flag = 'green'
WHERE id = '10000000-0000-0000-0000-000000000005' 
  AND flag IS NULL;

UPDATE public.reviews 
SET flag = 'green'
WHERE id = '10000000-0000-0000-0000-000000000006' 
  AND flag IS NULL;

-- ============================================
-- VERIFICATION QUERY
-- ============================================
-- Run this to verify demo account is correctly configured:
/*
SELECT 
  u.id,
  u.email,
  u.email_verified,
  u.id_verified,
  u.id_verification_status,
  u.access_status,
  u.is_banned,
  a.email_confirmed_at IS NOT NULL as auth_email_confirmed,
  a.encrypted_password != '' as has_password
FROM public.users u
JOIN auth.users a ON a.id = u.id
WHERE u.email = 'review@dirt.app';
*/

