thrilltrack-explorer/supabase/migrations/20251028173232_6a6d4761-ed51-47ff-b6ff-3531177ad7de.sql

-- Add email threading support to contact submissions
ALTER TABLE contact_submissions 
ADD COLUMN IF NOT EXISTS thread_id TEXT UNIQUE,
ADD COLUMN IF NOT EXISTS last_admin_response_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS response_count INTEGER DEFAULT 0;

-- Generate thread_id for existing submissions
UPDATE contact_submissions 
SET thread_id = 'thread_' || id::text 
WHERE thread_id IS NULL;

-- Make thread_id NOT NULL after backfill
ALTER TABLE contact_submissions 
ALTER COLUMN thread_id SET NOT NULL;

-- Create index
CREATE INDEX IF NOT EXISTS idx_contact_submissions_thread_id 
ON contact_submissions(thread_id);

-- Create contact_email_threads table
CREATE TABLE IF NOT EXISTS contact_email_threads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  submission_id UUID NOT NULL REFERENCES contact_submissions(id) ON DELETE CASCADE,
  
  -- Email metadata
  message_id TEXT UNIQUE NOT NULL,
  in_reply_to TEXT,
  reference_chain TEXT[],
  
  -- Email content
  from_email TEXT NOT NULL,
  to_email TEXT NOT NULL,
  subject TEXT NOT NULL,
  body_text TEXT NOT NULL,
  body_html TEXT,
  
  -- Direction & sender tracking
  direction TEXT NOT NULL CHECK (direction IN ('inbound', 'outbound')),
  sent_by UUID REFERENCES auth.users(id),
  
  -- Metadata
  metadata JSONB DEFAULT '{}'::jsonb,
  
  CONSTRAINT valid_direction CHECK (
    (direction = 'outbound' AND sent_by IS NOT NULL) OR
    (direction = 'inbound' AND sent_by IS NULL)
  )
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_email_threads_submission ON contact_email_threads(submission_id);
CREATE INDEX IF NOT EXISTS idx_email_threads_message_id ON contact_email_threads(message_id);
CREATE INDEX IF NOT EXISTS idx_email_threads_created ON contact_email_threads(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_email_threads_direction ON contact_email_threads(direction);

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

-- Admin-only read policy
CREATE POLICY "Admins can view all email threads"
  ON contact_email_threads FOR SELECT
  TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

-- Admin-only write policy
CREATE POLICY "Admins can insert email threads"
  ON contact_email_threads FOR INSERT
  TO authenticated
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

-- Grant select to authenticated users (RLS will filter)
GRANT SELECT ON contact_email_threads TO authenticated;
GRANT INSERT ON contact_email_threads TO authenticated;

-- Add comment for documentation
COMMENT ON TABLE contact_email_threads IS 
  'Stores email thread history for contact form submissions. Admin-only access.';