thrilltrack-explorer/supabase/migrations/20251028184108_2c913e6e-149b-48d8-924a-773e3c6a42d7.sql

-- Backfill existing contact submissions into email threads
-- This is a one-time migration to add initial messages to the email thread
-- New submissions are automatically added by the edge function

INSERT INTO contact_email_threads (
  submission_id,
  direction,
  from_email,
  to_email,
  subject,
  body_text,
  message_id,
  metadata,
  created_at
)
SELECT 
  cs.id as submission_id,
  'inbound' as direction,
  cs.email as from_email,
  'admin@thrillwiki.com' as to_email,
  cs.subject,
  cs.message as body_text,
  '<' || cs.ticket_number || '.' || cs.id || '@thrillwiki.com>' as message_id,
  jsonb_build_object(
    'category', cs.category,
    'name', cs.name,
    'backfilled', true
  ) as metadata,
  cs.created_at
FROM contact_submissions cs
WHERE cs.id NOT IN (
  SELECT DISTINCT submission_id 
  FROM contact_email_threads
)
AND cs.ticket_number IS NOT NULL;