-- Lead capture form: persistence table.
-- Apply manually in Supabase SQL editor or via the Supabase MCP `apply_migration`
-- tool once the change is reviewed. The RLS pattern mirrors `chat_messages`:
-- the service role (n8n / webhook-proxy) is the only writer, owners read.

create table if not exists public.leads (
  id              uuid primary key default gen_random_uuid(),
  bot_id          uuid not null references public.chatbots(id) on delete cascade,
  user_id         uuid references auth.users(id) on delete set null,
  session_id      text,
  visitor_user_id text,
  form_id         text not null,
  form_version    integer not null default 1,
  -- Denormalised fields for indexing/search; full payload is in raw_data.
  name            text,
  email           text,
  phone           text,
  reason          text,
  availability    text,
  raw_data        jsonb not null,
  consent         boolean not null,
  consent_label   text,
  consent_version text not null default '1.0',
  ip_hash         text,
  user_agent      text,
  source_origin   text,
  created_at      timestamptz not null default now()
);

create index if not exists leads_bot_id_created_at_idx on public.leads (bot_id, created_at desc);
create index if not exists leads_email_idx on public.leads (lower(email));
create index if not exists leads_session_id_idx on public.leads (session_id);

alter table public.leads enable row level security;

-- Block any client-side writes; n8n / webhook-proxy use the service role.
create policy "leads_block_anon_writes_insert" on public.leads
  as restrictive for insert to anon, authenticated with check (false);
create policy "leads_block_anon_writes_update" on public.leads
  as restrictive for update to anon, authenticated using (false) with check (false);
create policy "leads_block_anon_writes_delete" on public.leads
  as restrictive for delete to anon, authenticated using (false);

-- Owners read their leads via the chatbots ownership chain.
create policy "leads_select_by_chatbot_owner" on public.leads
  for select using (
    exists (
      select 1 from public.chatbots c
      where c.id = leads.bot_id and c.user_id = auth.uid()
    )
  );

comment on table public.leads is 'Structured leads captured via the chat widget lead form.';
comment on column public.leads.raw_data is 'Full form submission payload as received from the widget.';
comment on column public.leads.consent_label is 'Verbatim consent label shown to the user at submission time (RGPD audit).';
