-- Marriott Box Tagger — request log
-- One row per file the tagger sent to Gemini (success or error).
-- Skipped-as-already-tagged files do not produce rows.

-- pg_trgm powers the fuzzy `similarity()` call in /api/events. Idempotent.
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE IF NOT EXISTS tagging_events (
    id                          BIGSERIAL PRIMARY KEY,
    run_id                      UUID        NOT NULL,
    created_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
    file_id                     TEXT        NOT NULL,
    file_name                   TEXT        NOT NULL,
    folder_path                 TEXT,
    media_type                  TEXT        NOT NULL CHECK (media_type IN ('image','video')),
    gemini_model                TEXT        NOT NULL,
    prompt                      TEXT,
    raw_response                JSONB,
    description                 TEXT,
    scenes                      JSONB,
    validated_metadata          JSONB,
    metadata_write_success      BOOLEAN,
    description_write_success   BOOLEAN,
    scene_comment_write_success BOOLEAN,
    status                      TEXT        NOT NULL,
    error_message               TEXT,
    duration_ms                 INTEGER
);

CREATE INDEX IF NOT EXISTS tagging_events_run_id_idx  ON tagging_events (run_id);
CREATE INDEX IF NOT EXISTS tagging_events_file_id_idx ON tagging_events (file_id);
CREATE INDEX IF NOT EXISTS tagging_events_created_idx ON tagging_events (created_at DESC);

-- Per-file state: thumbnails + consolidated search blob. One row per Box file_id,
-- upserted by the tagger and backfill flows. Independent of the append-only
-- tagging_events log (which can have many rows per file_id over time).
CREATE TABLE IF NOT EXISTS file_assets (
    file_id                TEXT        PRIMARY KEY,
    thumbnail_bytes        BYTEA,
    thumbnail_content_type TEXT,
    thumbnail_size         INTEGER,
    search_terms           TEXT,
    updated_at             TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS file_assets_updated_idx ON file_assets (updated_at DESC);