schema.sql

-- Schema for the booking_invites table.
-- Run this once against your PostgreSQL database (Supabase SQL editor,
-- psql, etc.) before starting the service.

CREATE TABLE IF NOT EXISTS booking_invites (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    slug                 text UNIQUE NOT NULL,
    contact_id           text,
    recipient_name       text NOT NULL,
    recipient_first_name text NOT NULL,
    recipient_email      text,
    greeting             text DEFAULT 'A note for',
    context_quote        text,
    closing              text DEFAULT 'Looking forward to the conversation.',
    cal_link             text,
    duration_minutes     integer,
    og_title             text,
    og_description       text,
    meeting_type         text DEFAULT 'discovery_call',
    -- active | booked | expired | cancelled
    status               text NOT NULL DEFAULT 'active',
    -- when true, the invite is NOT marked booked after a booking (reusable link)
    reusable             boolean NOT NULL DEFAULT false,
    view_count           integer NOT NULL DEFAULT 0,
    expires_at           timestamptz,
    booked_at            timestamptz,
    created_at           timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_booking_invites_status
    ON booking_invites (status);

-- Sentinel row used to count views of the always-on generic page (/book).
-- The slug "_generic" is reserved and never served as a personalized page.
INSERT INTO booking_invites (slug, recipient_name, recipient_first_name, status, reusable)
VALUES ('_generic', 'Generic', 'Generic', 'active', true)
ON CONFLICT (slug) DO NOTHING;