-- Meta CAPI plumbing for the audience-builder pipeline.
--
-- Two changes:
--
-- 1. customers.fbp / customers.fbc — Meta Pixel cookie values captured
--    browser-side at checkout time and threaded through Stripe's
--    client_reference_id (compact-encoded by js/utm-capture.js,
--    decoded by netlify/functions/stripe-webhook.js). Stored on the
--    customer row so the trial-end Subscribe event (fired days later
--    from invoice.payment_succeeded) can re-attach them to the CAPI
--    payload for high match quality.
--
-- 2. capi_events_sent — idempotency log for outbound CAPI events.
--    Same shape as public.onboarding_emails_sent: PRIMARY KEY blocks
--    duplicates across Stripe webhook redeliveries, monthly renewals,
--    and any future retry path. The webhook INSERTs before posting to
--    Graph API and DELETEs the row on a hard CAPI failure so the next
--    Stripe redelivery retries.
--
-- Apply by pasting into Supabase SQL Editor. Idempotent (uses
-- if-not-exists on both objects).
--
-- Service role only. No RLS policies — customers never read this
-- table; the admin dashboard can join through service role if a
-- future debugging view wants to surface "what's been fired to
-- Meta for X."

alter table public.customers
  add column if not exists fbp text,
  add column if not exists fbc text;

comment on column public.customers.fbp is
  'Meta Pixel _fbp cookie captured at checkout (format fb.1.<ts>.<rand>). '
  'Threaded through Stripe client_reference_id by js/utm-capture.js, '
  'decoded by stripe-webhook.js. Used as raw user_data on outbound '
  'CAPI Lead/Subscribe events for higher match quality.';

comment on column public.customers.fbc is
  'Meta Pixel _fbc cookie captured at checkout (format fb.1.<ts>.<fbclid>). '
  'Same plumbing as fbp.';

create table if not exists public.capi_events_sent (
  customer_id uuid not null
    references public.customers(id) on delete cascade,
  event_name  text not null
    check (event_name in ('Lead', 'Subscribe')),
  sent_at     timestamptz not null default now(),
  primary key (customer_id, event_name)
);

comment on table public.capi_events_sent is
  'Idempotency log for outbound Meta Conversions API events. One row '
  'per (customer, event_name). Inserted by stripe-webhook.js before '
  'POSTing to graph.facebook.com/<pixel>/events. PRIMARY KEY blocks '
  'duplicate Lead/Subscribe events across Stripe webhook redeliveries '
  'and monthly subscription renewals.';

-- Service role needs SELECT (eligibility query, debugging),
-- INSERT (record send), DELETE (rollback on CAPI failure).
-- Per the project's "Supabase grants don't survive DROP+CREATE" memory,
-- grants stay explicit.
grant select, insert, delete on public.capi_events_sent to service_role;
