-- BACKLOG U3: onboarding email drip flow.
--
-- One row per (customer, drip campaign) pair. Inserted by
-- netlify/functions/onboarding-drip.js BEFORE the Resend send fires.
-- The PRIMARY KEY is the idempotency guard: a daily cron that
-- accidentally runs twice cannot send the same drip to the same
-- customer twice, because the second insert raises a unique
-- violation. If the Resend send fails after the insert, the function
-- best-effort deletes the row so the next day retries.
--
-- Apply by pasting into Supabase SQL Editor. Idempotent (uses
-- create-table-if-not-exists pattern via DO block).
--
-- Service role only. No RLS policies — customers never read this
-- table; admin /admin dashboard can join through service role if
-- a future debugging view wants to surface "what's been sent to X".

create table if not exists public.onboarding_emails_sent (
  customer_id uuid not null
    references public.customers(id) on delete cascade,
  campaign    text not null
    check (campaign in ('day_1','day_2','day_3','day_4','day_5','day_6')),
  sent_at     timestamptz not null default now(),
  primary key (customer_id, campaign)
);

comment on table public.onboarding_emails_sent is
  'BACKLOG U3 drip idempotency guard. One row per (customer, campaign). '
  'Inserted before Resend send by netlify/functions/onboarding-drip.js. '
  'PRIMARY KEY blocks duplicate sends across cron retries.';

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