-- Supabase / PostgreSQL Schema for AgileAdapt Web Factory

-- 1. Create the `tenants` table to store agent, deploy, and business data
CREATE TABLE public.tenants (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    business_name TEXT NOT NULL,
    subdomain TEXT UNIQUE NOT NULL, -- The netlify deploy alias
    telnyx_assistant_id TEXT, -- The JIT created voice assistant
    html_payload TEXT, -- The raw generated HTML string
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Create the `leads` table for the website's contact form submissions
CREATE TABLE public.leads (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    first_name TEXT NOT NULL,
    last_name TEXT,
    phone_number TEXT,
    email TEXT,
    service_requested TEXT,
    message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_leads_tenant_id ON public.leads(tenant_id);
CREATE INDEX idx_tenants_subdomain ON public.tenants(subdomain);

-------------------------------------------------------------------------------
-- ROW LEVEL SECURITY (RLS) POLICIES
-------------------------------------------------------------------------------

-- Enable RLS
ALTER TABLE public.tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.leads ENABLE ROW LEVEL SECURITY;

-- Tenants Policy (Only viewable from backend / admin)
CREATE POLICY "Tenants are viewable by admins only"
    ON public.tenants
    FOR SELECT
    USING (auth.role() = 'service_role');

CREATE POLICY "Tenants are insertable by backend only"
    ON public.tenants
    FOR INSERT
    WITH CHECK (auth.role() = 'service_role');

-- Leads Policy #1: The public generated website can only INSERT leads for its specific tenant_id.
-- This prevents spam or cross-tenant data pollution.
CREATE POLICY "Anyone can insert leads anonymously"
    ON public.leads
    FOR INSERT
    TO anon
    WITH CHECK (true); -- Note: In a production Supabase setup, we will use JWT claims to strictly enforce the specific tenant_id

-- Leads Policy #2: Leads are only viewable by the backend/admin
CREATE POLICY "Leads are viewable by admins only"
    ON public.leads
    FOR SELECT
    USING (auth.role() = 'service_role');
