-- Add Personal Intel for Michael Rocco McLoughlin (Test Data)
-- Run this in Supabase SQL Editor

-- First, find or create the lead
DO $$
DECLARE
  v_lead_id UUID;
BEGIN
  -- Try to find existing lead
  SELECT id INTO v_lead_id
  FROM leads
  WHERE (first_name ILIKE '%michael%' AND last_name ILIKE '%loughlin%')
     OR (first_name ILIKE '%michael%' AND last_name ILIKE '%rocco%')
  LIMIT 1;

  -- If no lead found, create one
  IF v_lead_id IS NULL THEN
    INSERT INTO leads (first_name, last_name, company, source, status, score)
    VALUES ('Michael', 'McLoughlin', 'Formcork Steelfixin', 'test', 'new', 75)
    RETURNING id INTO v_lead_id;
    RAISE NOTICE 'Created new lead with ID: %', v_lead_id;
  ELSE
    RAISE NOTICE 'Found existing lead with ID: %', v_lead_id;
  END IF;

  -- Insert or update lead_intel with personal data
  INSERT INTO lead_intel (
    lead_id,
    family_status,
    hobbies,
    interests,
    location_suburb,
    personality_notes,
    conversation_starters,
    personal_news
  ) VALUES (
    v_lead_id,
    -- Family Status
    'Irish expat, 44 years old',
    -- Hobbies
    ARRAY['Following F1', 'Watching football', 'Watching NRL', 'Grew up working in family pub'],
    -- Interests (includes sports teams)
    ARRAY['Liverpool FC supporter', 'South Sydney Rabbitohs fan', 'F1 Racing - Max Verstappen', 'Construction industry', 'Irish heritage'],
    -- Location
    'Sydney (from Achill Island, Ireland)',
    -- Personality Notes
    'Irish heritage, grew up in a pub on Achill Island. Passionate about sports - Liverpool FC supporter, follows Rabbitohs in NRL, big F1 fan backing Max Verstappen. Works in construction at Formcork Steelfixin.',
    -- Conversation Starters
    ARRAY[
      'Ask about Liverpool''s season - he''s a big Reds supporter',
      'Mention the F1 - Max Verstappen is his driver',
      'Ask how the Rabbitohs are going this NRL season',
      'Growing up in a pub on Achill Island - great stories there',
      'Construction chat - Formcork Steelfixin and concrete work'
    ],
    -- Personal News (JSONB)
    '{"origin": "Achill Island, Ireland", "fun_fact": "Grew up in a pub", "age": 44, "favorite_f1_driver": "Max Verstappen", "sports_teams": ["Liverpool FC", "South Sydney Rabbitohs"]}'::JSONB
  )
  ON CONFLICT (lead_id)
  DO UPDATE SET
    family_status = EXCLUDED.family_status,
    hobbies = EXCLUDED.hobbies,
    interests = EXCLUDED.interests,
    location_suburb = EXCLUDED.location_suburb,
    personality_notes = EXCLUDED.personality_notes,
    conversation_starters = EXCLUDED.conversation_starters,
    personal_news = EXCLUDED.personal_news,
    updated_at = NOW();

  RAISE NOTICE 'Personal intel added for lead ID: %', v_lead_id;
END $$;

-- Verify it was added
SELECT
  l.id,
  l.first_name,
  l.last_name,
  l.company,
  li.family_status,
  li.hobbies,
  li.interests,
  li.location_suburb,
  li.conversation_starters
FROM leads l
LEFT JOIN lead_intel li ON l.id = li.lead_id
WHERE l.first_name ILIKE '%michael%'
  AND (l.last_name ILIKE '%loughlin%' OR l.last_name ILIKE '%rocco%');
