-- 20260405_email_tracking.sql
-- Create isolated tables for tracking email sends, opens, and clicks

-- UUID Extension is generally available via uuid-ossp, but just in case:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Table: email_sends
CREATE TABLE email_sends (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    recipient_email TEXT NOT NULL,
    subject TEXT,
    original_campaign_id TEXT, -- Optional linkage
    sent_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Table: email_opens
CREATE TABLE email_opens (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email_send_id UUID REFERENCES email_sends(id) ON DELETE CASCADE,
    opened_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    ip_address TEXT,
    user_agent TEXT
);

-- Table: email_clicks
CREATE TABLE email_clicks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email_send_id UUID REFERENCES email_sends(id) ON DELETE CASCADE,
    url TEXT NOT NULL,
    clicked_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    ip_address TEXT,
    user_agent TEXT
);

-- Enable RLS
ALTER TABLE email_sends ENABLE ROW LEVEL SECURITY;
ALTER TABLE email_opens ENABLE ROW LEVEL SECURITY;
ALTER TABLE email_clicks ENABLE ROW LEVEL SECURITY;

-- Explicit permissive policies for tracking
-- Allow anon and authenticated to INSERT into email_opens
CREATE POLICY "Allow anyone to insert into email_opens" ON email_opens
    FOR INSERT TO anon, authenticated
    WITH CHECK (true);

CREATE POLICY "Allow anyone to select from email_opens" ON email_opens
    FOR SELECT TO anon, authenticated
    USING (true);

-- Allow anon and authenticated to INSERT into email_clicks
CREATE POLICY "Allow anyone to insert into email_clicks" ON email_clicks
    FOR INSERT TO anon, authenticated
    WITH CHECK (true);

CREATE POLICY "Allow anyone to select from email_clicks" ON email_clicks
    FOR SELECT TO anon, authenticated
    USING (true);

-- Allow authenticated users to insert/select email_sends
-- Webhooks might need anon if sent by edge function, but we'll grant to both per requirements
CREATE POLICY "Allow anyone to insert into email_sends" ON email_sends
    FOR INSERT TO anon, authenticated
    WITH CHECK (true);

CREATE POLICY "Allow anyone to select from email_sends" ON email_sends
    FOR SELECT TO anon, authenticated
    USING (true);

-- Optional: Allow anyone to update email_sends (to avoid issues)
CREATE POLICY "Allow anyone to update email_sends" ON email_sends
    FOR UPDATE TO anon, authenticated
    USING (true)
    WITH CHECK (true);

-- Views for Statistics
CREATE OR REPLACE VIEW email_tracking_stats AS
SELECT 
    s.id AS email_send_id,
    s.recipient_email,
    s.subject,
    s.sent_at,
    (SELECT COUNT(*) FROM email_opens o WHERE o.email_send_id = s.id) AS open_count,
    (SELECT COUNT(*) FROM email_clicks c WHERE c.email_send_id = s.id) AS click_count
FROM email_sends s;
