-- Tier 2 Block H — day-of check-in.
--
-- One row per guest who showed up. The UNIQUE on guest_id is the
-- double-check-in guard at the DB layer; the QR validation (JWT
-- signature + expiry + event match) lives in the API.
--
-- arrival_count records how many people actually walked in for this
-- guest (1 + plus-ones who showed). walk_in marks the row as a
-- door-add — a guest who wasn't on the original list. The QR payload
-- itself isn't stored; it's a derivable JWT.

CREATE TABLE IF NOT EXISTS check_ins (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    guest_id      UUID NOT NULL UNIQUE REFERENCES guests(id) ON DELETE CASCADE,
    checked_in_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    checked_in_by UUID REFERENCES users(id) ON DELETE SET NULL,
    arrival_count INTEGER NOT NULL DEFAULT 1,
    notes         TEXT,
    walk_in       BOOLEAN NOT NULL DEFAULT FALSE
);

-- "How's the door looking?" — the live arrivals widget runs this
-- against (event_id, checked_in_at) so the index covers the ORDER BY.
CREATE INDEX IF NOT EXISTS idx_checkins_event_time
    ON check_ins(checked_in_at DESC);