-- Phase M (t-paliad-021): email service tables.
--
-- Two tables in this migration:
--   * reminder_log — dedup for hourly deadline-reminder emails. One row per
--     (frist_id, reminder_type, day). The service refuses to re-send when a
--     row younger than 24h exists; storing the timestamp rather than a bare
--     (frist_id, type) PK lets us re-send after the dedup window without
--     garbage-collecting.
--   * invitations — append-only audit of colleague invites sent via POST
--     /api/invite. Lets us show per-user history and, later, mark a row
--     accepted_at when the invitee completes register.
--
-- Optional paliad.users.lang column lets per-user language preference override
-- the DE default when rendering reminders/invitations. Unset today (the
-- onboarding form doesn't collect it yet); the MailService falls back to 'de'
-- whenever the field is NULL.

ALTER TABLE paliad.users
    ADD COLUMN IF NOT EXISTS lang text;

-- reminder_log: one row per (frist_id, reminder_type) and day. The type
-- column takes 'overdue' | 'tomorrow' | 'weekly'. Weekly rows use the Monday
-- key date; per-Frist rows use the due date.
CREATE TABLE paliad.reminder_log (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    frist_id       uuid REFERENCES paliad.fristen(id) ON DELETE CASCADE,
    user_id        uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    reminder_type  text NOT NULL CHECK (reminder_type IN ('overdue', 'tomorrow', 'weekly')),
    sent_at        timestamptz NOT NULL DEFAULT now()
);

-- Dedup index: fast lookup of "did we send this reminder to this user for
-- this frist recently?". For weekly summaries, frist_id is NULL and the
-- uniqueness is enforced by the service (one weekly per user per Monday).
CREATE INDEX reminder_log_dedup_idx
    ON paliad.reminder_log (user_id, reminder_type, frist_id, sent_at DESC);

-- invitations: record of every /api/invite call. Rate-limited per user at
-- the handler layer (10/day), not via a DB constraint — the handler keeps
-- an in-memory counter, mirroring the AI-extraction rate limiter pattern.
CREATE TABLE paliad.invitations (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    from_user_id   uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    to_email       text NOT NULL,
    message        text NOT NULL DEFAULT '',
    sent_at        timestamptz NOT NULL DEFAULT now(),
    accepted_at    timestamptz
);

CREATE INDEX invitations_from_user_sent_idx
    ON paliad.invitations (from_user_id, sent_at DESC);

CREATE INDEX invitations_to_email_idx
    ON paliad.invitations (lower(to_email));

-- RLS: service-layer only for now (no client-facing endpoints read these
-- tables). Enabling RLS with no policies denies all direct Supabase PostgREST
-- access — the Go server bypasses RLS via its direct DB pool anyway, matching
-- every other paliad.* write-table.
ALTER TABLE paliad.reminder_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE paliad.invitations  ENABLE ROW LEVEL SECURITY;
