supabase-schema.sql

-- The Entourage — Supabase Database Schema
-- Run this in the Supabase SQL Editor

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ==========================================
-- Table: celebrities
-- ==========================================
CREATE TABLE IF NOT EXISTS celebrities (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  wikipedia_summary text,
  image_url text,
  category text CHECK (category IN ('music', 'film', 'sport', 'politics', 'tech', 'media')),
  is_preloaded boolean DEFAULT false,
  created_at timestamp with time zone DEFAULT now()
);

-- ==========================================
-- Table: contacts
-- ==========================================
CREATE TABLE IF NOT EXISTS contacts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  celebrity_id uuid NOT NULL REFERENCES celebrities(id) ON DELETE CASCADE,
  name text NOT NULL,
  role text NOT NULL CHECK (role IN ('Manager', 'Publicist', 'Agent', 'Assistant', 'Collaborator', 'Label', 'Production')),
  platform text CHECK (platform IN ('email', 'twitter', 'instagram', 'linkedin')),
  handle_or_email text,
  phone_number text,
  source_url text,
  reachability_score integer CHECK (reachability_score BETWEEN 1 AND 10),
  reachability_reasoning text,
  created_at timestamp with time zone DEFAULT now()
);

-- ==========================================
-- Table: outreach
-- ==========================================
CREATE TABLE IF NOT EXISTS outreach (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  contact_id uuid NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  message_warm text,
  message_direct text,
  message_bold text,
  selected_variant text,
  channel text,
  sent_at timestamp with time zone,
  status text DEFAULT 'drafted' CHECK (status IN ('drafted', 'sent', 'replied', 'failed')),
  created_at timestamp with time zone DEFAULT now()
);

-- ==========================================
-- Indexes
-- ==========================================
CREATE INDEX IF NOT EXISTS idx_contacts_celebrity_id ON contacts(celebrity_id);
CREATE INDEX IF NOT EXISTS idx_outreach_contact_id ON outreach(contact_id);
CREATE INDEX IF NOT EXISTS idx_celebrities_is_preloaded ON celebrities(is_preloaded);

-- ==========================================
-- Enable Realtime on outreach table
-- ==========================================
ALTER PUBLICATION supabase_realtime ADD TABLE outreach;

-- ==========================================
-- Row Level Security (permissive for hackathon)
-- ==========================================
ALTER TABLE celebrities ENABLE ROW LEVEL SECURITY;
ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE outreach ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow all access to celebrities" ON celebrities FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all access to contacts" ON contacts FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Allow all access to outreach" ON outreach FOR ALL USING (true) WITH CHECK (true);