-- Create campaigns table
CREATE TABLE public.campaigns (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  subject TEXT NOT NULL,
  html_body TEXT NOT NULL,
  text_body TEXT,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'paused', 'completed', 'cancelled')),
  segment_tags UUID[] DEFAULT '{}',
  segment_status TEXT[],
  total_recipients INTEGER DEFAULT 0,
  sent_count INTEGER DEFAULT 0,
  failed_count INTEGER DEFAULT 0,
  skipped_count INTEGER DEFAULT 0,
  batch_size INTEGER DEFAULT 50,
  last_batch_at TIMESTAMP WITH TIME ZONE,
  scheduled_at TIMESTAMP WITH TIME ZONE,
  completed_at TIMESTAMP WITH TIME ZONE,
  created_by UUID NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Create campaign_recipients table (tracks each recipient per campaign)
CREATE TABLE public.campaign_recipients (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  campaign_id UUID NOT NULL REFERENCES public.campaigns(id) ON DELETE CASCADE,
  contact_id UUID NOT NULL REFERENCES public.contacts(id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed', 'skipped')),
  sent_at TIMESTAMP WITH TIME ZONE,
  error_message TEXT,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  UNIQUE(campaign_id, contact_id)
);

-- Create email_logs table (detailed log of all emails sent)
CREATE TABLE public.email_logs (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  campaign_id UUID REFERENCES public.campaigns(id) ON DELETE SET NULL,
  contact_id UUID REFERENCES public.contacts(id) ON DELETE SET NULL,
  recipient_email TEXT NOT NULL,
  subject TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'sent' CHECK (status IN ('sent', 'failed', 'bounced', 'complained')),
  resend_id TEXT,
  error_message TEXT,
  sent_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Create suppression_list table (unsubscribes, bounces, complaints)
CREATE TABLE public.suppression_list (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  reason TEXT NOT NULL CHECK (reason IN ('unsubscribe', 'bounce', 'complaint', 'manual')),
  source_campaign_id UUID REFERENCES public.campaigns(id) ON DELETE SET NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Enable RLS on all tables
ALTER TABLE public.campaigns ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.campaign_recipients ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.email_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.suppression_list ENABLE ROW LEVEL SECURITY;

-- RLS policies for campaigns
CREATE POLICY "Authenticated users can view campaigns" ON public.campaigns
  FOR SELECT USING (true);

CREATE POLICY "Authenticated users can create campaigns" ON public.campaigns
  FOR INSERT WITH CHECK (auth.uid() = created_by);

CREATE POLICY "Authenticated users can update campaigns" ON public.campaigns
  FOR UPDATE USING (true);

CREATE POLICY "Authenticated users can delete campaigns" ON public.campaigns
  FOR DELETE USING (true);

-- RLS policies for campaign_recipients
CREATE POLICY "Authenticated users can view campaign_recipients" ON public.campaign_recipients
  FOR SELECT USING (true);

CREATE POLICY "Authenticated users can create campaign_recipients" ON public.campaign_recipients
  FOR INSERT WITH CHECK (true);

CREATE POLICY "Authenticated users can update campaign_recipients" ON public.campaign_recipients
  FOR UPDATE USING (true);

CREATE POLICY "Authenticated users can delete campaign_recipients" ON public.campaign_recipients
  FOR DELETE USING (true);

-- RLS policies for email_logs
CREATE POLICY "Authenticated users can view email_logs" ON public.email_logs
  FOR SELECT USING (true);

CREATE POLICY "Authenticated users can create email_logs" ON public.email_logs
  FOR INSERT WITH CHECK (true);

-- RLS policies for suppression_list
CREATE POLICY "Authenticated users can view suppression_list" ON public.suppression_list
  FOR SELECT USING (true);

CREATE POLICY "Authenticated users can manage suppression_list" ON public.suppression_list
  FOR ALL USING (true);

-- Create indexes for performance
CREATE INDEX idx_campaign_recipients_campaign_status ON public.campaign_recipients(campaign_id, status);
CREATE INDEX idx_campaign_recipients_contact ON public.campaign_recipients(contact_id);
CREATE INDEX idx_email_logs_campaign ON public.email_logs(campaign_id);
CREATE INDEX idx_email_logs_contact ON public.email_logs(contact_id);
CREATE INDEX idx_suppression_list_email ON public.suppression_list(email);
CREATE INDEX idx_campaigns_status ON public.campaigns(status);

-- Add updated_at trigger for campaigns
CREATE TRIGGER update_campaigns_updated_at
  BEFORE UPDATE ON public.campaigns
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();