-- Seed test data for deterministic testing
-- This migration is only applied in test environments
-- DO NOT run in production
--
-- Safety: All test data uses isolated identifiers:
--   - Email domain: @test.dirt.local (not a real domain)
--   - Review slugs: test-* prefix
--   - Deterministic UUIDs that won't conflict with production
--
-- This migration is idempotent and safe to run multiple times.

-- Clear existing test data (idempotent)
DELETE FROM reviews WHERE slug LIKE 'test-%';
DELETE FROM responses WHERE review_id IN (SELECT id FROM reviews WHERE slug LIKE 'test-%');
DELETE FROM users WHERE email LIKE '%@test.dirt.local';
DELETE FROM auth.users WHERE email LIKE '%@test.dirt.local';

-- Create auth users first (required for foreign key constraint)
-- Note: This requires service role permissions to insert into auth.users
-- Using extensions.crypt for password hashing
DO $$
BEGIN
  -- Test user 1
  INSERT INTO auth.users (
    id, instance_id, email, encrypted_password, email_confirmed_at,
    created_at, updated_at, raw_app_meta_data, raw_user_meta_data,
    is_super_admin, role
  )
  VALUES (
    '00000000-0000-0000-0000-000000000001'::uuid,
    '00000000-0000-0000-0000-000000000000'::uuid,
    'test-user-1@test.dirt.local',
    extensions.crypt('test-password-1', extensions.gen_salt('bf')),
    NOW(),
    NOW(),
    NOW(),
    '{"provider":"email","providers":["email"]}'::jsonb,
    '{}'::jsonb,
    false,
    'authenticated'
  )
  ON CONFLICT (id) DO NOTHING;
  
  -- Test user 2
  INSERT INTO auth.users (
    id, instance_id, email, encrypted_password, email_confirmed_at,
    created_at, updated_at, raw_app_meta_data, raw_user_meta_data,
    is_super_admin, role
  )
  VALUES (
    '00000000-0000-0000-0000-000000000002'::uuid,
    '00000000-0000-0000-0000-000000000000'::uuid,
    'test-user-2@test.dirt.local',
    extensions.crypt('test-password-2', extensions.gen_salt('bf')),
    NOW(),
    NOW(),
    NOW(),
    '{"provider":"email","providers":["email"]}'::jsonb,
    '{}'::jsonb,
    false,
    'authenticated'
  )
  ON CONFLICT (id) DO NOTHING;
  
  -- Test user 3
  INSERT INTO auth.users (
    id, instance_id, email, encrypted_password, email_confirmed_at,
    created_at, updated_at, raw_app_meta_data, raw_user_meta_data,
    is_super_admin, role
  )
  VALUES (
    '00000000-0000-0000-0000-000000000003'::uuid,
    '00000000-0000-0000-0000-000000000000'::uuid,
    'test-user-3@test.dirt.local',
    extensions.crypt('test-password-3', extensions.gen_salt('bf')),
    NOW(),
    NOW(),
    NOW(),
    '{"provider":"email","providers":["email"]}'::jsonb,
    '{}'::jsonb,
    false,
    'authenticated'
  )
  ON CONFLICT (id) DO NOTHING;
  
  -- Test user 4
  INSERT INTO auth.users (
    id, instance_id, email, encrypted_password, email_confirmed_at,
    created_at, updated_at, raw_app_meta_data, raw_user_meta_data,
    is_super_admin, role
  )
  VALUES (
    '00000000-0000-0000-0000-000000000004'::uuid,
    '00000000-0000-0000-0000-000000000000'::uuid,
    'test-user-4@test.dirt.local',
    extensions.crypt('test-password-4', extensions.gen_salt('bf')),
    NOW(),
    NOW(),
    NOW(),
    '{"provider":"email","providers":["email"]}'::jsonb,
    '{}'::jsonb,
    false,
    'authenticated'
  )
  ON CONFLICT (id) DO NOTHING;
END $$;

-- Test Users (deterministic IDs for reliable references)
INSERT INTO users (id, email, username, gender, date_of_birth, city, state, country, id_verified, created_at, updated_at)
VALUES
  -- Test user 1: Man, 25 years old, ID verified
  ('00000000-0000-0000-0000-000000000001', 'test-user-1@test.dirt.local', 'testuser1', 'man', 
   (CURRENT_DATE - INTERVAL '25 years'), 'San Francisco', 'CA', 'US', true, NOW(), NOW()),
  
  -- Test user 2: Woman, 28 years old, ID verified
  ('00000000-0000-0000-0000-000000000002', 'test-user-2@test.dirt.local', 'testuser2', 'woman', 
   (CURRENT_DATE - INTERVAL '28 years'), 'New York', 'NY', 'US', true, NOW(), NOW()),
  
  -- Test user 3: Man, 30 years old, NOT ID verified (for waitlist testing)
  ('00000000-0000-0000-0000-000000000003', 'test-user-3@test.dirt.local', 'testuser3', 'man', 
   (CURRENT_DATE - INTERVAL '30 years'), 'Los Angeles', 'CA', 'US', false, NOW(), NOW()),
  
  -- Test user 4: Woman, 22 years old, ID verified
  ('00000000-0000-0000-0000-000000000004', 'test-user-4@test.dirt.local', 'testuser4', 'woman', 
   (CURRENT_DATE - INTERVAL '22 years'), 'Chicago', 'IL', 'US', true, NOW(), NOW())
ON CONFLICT (id) DO UPDATE SET
  email = EXCLUDED.email,
  username = EXCLUDED.username,
  gender = EXCLUDED.gender,
  date_of_birth = EXCLUDED.date_of_birth,
  city = EXCLUDED.city,
  state = EXCLUDED.state,
  country = EXCLUDED.country,
  id_verified = EXCLUDED.id_verified,
  updated_at = NOW();

-- Test Reviews (approved, with known slugs)
INSERT INTO reviews (
  id, 
  author_id, 
  subject_first_name,
  subject_last_name,
  subject_age,
  subject_gender, 
  slug, 
  rating, 
  content, 
  flag, 
  status, 
  is_indexable, 
  published_at, 
  created_at, 
  updated_at
)
VALUES
  -- Review 1: High-rated review for a man (slug: test-high-rated-man)
  -- User 1 (man) reviews a man - but wait, this violates opposite-gender rule!
  -- Let me fix: User 2 (woman) should review a man
  ('10000000-0000-0000-0000-000000000001', 
   '00000000-0000-0000-0000-000000000002',  -- User 2 (woman) is author
   'John',  -- First name
   'D',  -- Last initial
   28,  -- Age
   'man',  -- Subject gender (opposite of author)
   'test-high-rated-man', 
   5, 
   'Excellent experience with attentive, professional hosts; highly recommend across the board.', 
   'green', 
   'approved', 
   true, 
   NOW() - INTERVAL '5 days', 
   NOW() - INTERVAL '5 days', 
   NOW() - INTERVAL '5 days'),
  
  -- Review 2: Medium-rated review for a woman (slug: test-medium-rated-woman)
  -- User 1 (man) reviews a woman
  ('10000000-0000-0000-0000-000000000002', 
   '00000000-0000-0000-0000-000000000001',  -- User 1 (man) is author
   'Jane',  -- First name
   'S',  -- Last initial
   25,  -- Age
   'woman',  -- Subject gender (opposite of author)
   'test-medium-rated-woman', 
   3, 
   'Average experience overall; the booking process and response time could improve.', 
   'green', 
   'approved', 
   true, 
   NOW() - INTERVAL '3 days', 
   NOW() - INTERVAL '3 days', 
   NOW() - INTERVAL '3 days'),
  
  -- Review 3: Low-rated review for a man (slug: test-low-rated-man)
  -- User 2 (woman) reviews a man
  ('10000000-0000-0000-0000-000000000003', 
   '00000000-0000-0000-0000-000000000002',  -- User 2 (woman) is author
   'Bob',  -- First name
   'J',  -- Last initial
   30,  -- Age
   'man',  -- Subject gender (opposite of author)
   'test-low-rated-man', 
   1, 
   'Poor experience at every stage; communication lacked clarity and the service disappointed.', 
   'red', 
   'approved', 
   true, 
   NOW() - INTERVAL '1 day', 
   NOW() - INTERVAL '1 day', 
   NOW() - INTERVAL '1 day'),
  
  -- Review 4: High-rated review for a woman (slug: test-high-rated-woman)
  -- User 1 (man) reviews a woman
  ('10000000-0000-0000-0000-000000000004', 
   '00000000-0000-0000-0000-000000000001',  -- User 1 (man) is author
   'Alice',  -- First name
   'W',  -- Last initial
   27,  -- Age
   'woman',  -- Subject gender (opposite of author)
   'test-high-rated-woman', 
   5, 
   'Outstanding experience that exceeded expectations from start to finish.', 
   'green', 
   'approved', 
   true, 
   NOW() - INTERVAL '7 days', 
   NOW() - INTERVAL '7 days', 
   NOW() - INTERVAL '7 days'),
  
  -- Review 5: Pending review (for moderation testing)
  -- User 2 (woman) reviews a man
  ('10000000-0000-0000-0000-000000000005', 
   '00000000-0000-0000-0000-000000000002',  -- User 2 (woman) is author
   'Charlie',  -- First name
   'B',  -- Last initial
   32,  -- Age
   'man',  -- Subject gender (opposite of author)
   'test-pending-review', 
   4, 
   'Pending review content for testing moderation that outlines what moderation should expect.', 
   'green', 
  'pending_review', 
   false, 
   NULL, 
   NOW(), 
   NOW()),
  
  -- Review 6: Rejected review (for moderation testing)
  -- User 1 (man) reviews a woman
  ('10000000-0000-0000-0000-000000000006', 
   '00000000-0000-0000-0000-000000000001',  -- User 1 (man) is author
   'Diana',  -- First name
   'P',  -- Last initial
   24,  -- Age
   'woman',  -- Subject gender (opposite of author)
   'test-rejected-review', 
   2, 
   'Rejected review content describing why the experience was unacceptable in detail.', 
   'red', 
   'rejected', 
   false, 
   NULL, 
   NOW() - INTERVAL '2 days', 
   NOW() - INTERVAL '2 days')
ON CONFLICT (id) DO UPDATE SET
  author_id = EXCLUDED.author_id,
  subject_first_name = EXCLUDED.subject_first_name,
  subject_last_name = EXCLUDED.subject_last_name,
  subject_age = EXCLUDED.subject_age,
  subject_gender = EXCLUDED.subject_gender,
  slug = EXCLUDED.slug,
  rating = EXCLUDED.rating,
  content = EXCLUDED.content,
  flag = EXCLUDED.flag,
  status = EXCLUDED.status,
  is_indexable = EXCLUDED.is_indexable,
  published_at = EXCLUDED.published_at,
  updated_at = NOW();

-- Test Responses (optional, for review responses)
INSERT INTO responses (
  id,
  review_id,
  responder_id,
  content,
  status,
  published_at,
  created_at,
  updated_at
)
VALUES
  -- Response 1: Approved response to high-rated man review
  -- Review 1 is about a man, so responder should be a man (user 1)
  ('20000000-0000-0000-0000-000000000001',
   '10000000-0000-0000-0000-000000000001',
   '00000000-0000-0000-0000-000000000001',  -- User 1 (man) responds to review about a man
   'Thank you for the review!',
   'approved',
   NOW() - INTERVAL '4 days',
   NOW() - INTERVAL '4 days',
   NOW() - INTERVAL '4 days'),
  
  -- Response 2: Pending response (for moderation testing)
  -- Review 2 is about a woman, so responder should be a woman (user 2)
  ('20000000-0000-0000-0000-000000000002',
   '10000000-0000-0000-0000-000000000002',
   '00000000-0000-0000-0000-000000000002',  -- User 2 (woman) responds to review about a woman
   'Pending response content.',
   'pending_review',
   NULL,
   NOW(),
   NOW())
ON CONFLICT (id) DO UPDATE SET
  review_id = EXCLUDED.review_id,
  responder_id = EXCLUDED.responder_id,
  content = EXCLUDED.content,
  status = EXCLUDED.status,
  published_at = EXCLUDED.published_at,
  updated_at = NOW();

-- Create indexes if they don't exist (for performance)
CREATE INDEX IF NOT EXISTS idx_reviews_test_slug ON reviews(slug) WHERE slug LIKE 'test-%';
CREATE INDEX IF NOT EXISTS idx_users_test_email ON users(email) WHERE email LIKE '%@test.dirt.local';
