-- WAIA Connect → Supabase: the two tables the Edge Function writes to.
-- Put this file in supabase/migrations/<timestamp>_connect_whatsapp.sql and run `supabase db push`
-- (or paste it in the SQL editor of your project).

-- One row per WhatsApp message (incoming, and the ones you send from the phone = echoes).
create table if not exists public.whatsapp_messages (
  id               bigint generated always as identity primary key,
  wamid            text        not null,          -- the WhatsApp message id
  event_id         text,                          -- Connect's evt_… (informative)
  connection_id    text,                          -- Connect's conn_… (the number it came in on)
  direction        text        not null check (direction in ('in', 'out')),
  contact          text,                          -- the other party's phone number
  contact_name     text,
  type             text        not null,          -- text, image, audio, …
  text             text,                          -- only for text messages
  sent_at          timestamptz,                   -- when WhatsApp says it was sent
  received_at      timestamptz not null default now(),
  replied_at       timestamptz,
  reply_message_id text                           -- Connect's msg_… of the auto-reply
);

-- 🔑 The reason nothing is processed twice: a Connect retry or a Meta re-delivery carries the
-- same wamid, and this index rejects the second row (the function then skips the reply).
create unique index if not exists whatsapp_messages_wamid_key on public.whatsapp_messages (wamid);
create index if not exists whatsapp_messages_contact_idx on public.whatsapp_messages (contact, received_at desc);

-- What went wrong: rejected replies, failed sends reported by Connect, config problems.
create table if not exists public.whatsapp_errors (
  id         bigint generated always as identity primary key,
  at         timestamptz not null default now(),
  kind       text        not null,   -- send_failed, delivery_failed, reply_rejected, config, handler…
  message_id text,                   -- Connect's msg_… for a failed send
  wamid      text,
  event_id   text,
  code       text,                   -- e.g. WINDOW_24H_EXPIRED, API_KEY_INVALID
  detail     text
);
-- One row per failed message (a status can be re-delivered).
create unique index if not exists whatsapp_errors_failed_key on public.whatsapp_errors (kind, message_id);

-- RLS ON, and no policies: the public (anon) key cannot read or write these tables.
-- The Edge Function uses the project's secret key (or the legacy service_role key), which bypasses RLS.
alter table public.whatsapp_messages enable row level security;
alter table public.whatsapp_errors   enable row level security;