`db/migrations/008_pd_film_images.sql`

35 lines
-- Public-domain film/TV images, aggregated from Wikimedia Commons,
-- Internet Archive, Library of Congress, and NARA.
--
-- Each image is a separate row with provenance + a `source` enum.
-- A nullable production_id link lets us attach images to film_production
-- rows without requiring a match (some images are stand-alone PD posters
-- with no permit-side production record).
--
-- Cache-first model: fetcher script populates this table; the FilmsGrid
-- component reads from here only. No live API calls in the request path.

CREATE TABLE IF NOT EXISTS pd_film_image (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source          text NOT NULL CHECK (source IN ('wikimedia','archive_org','loc','nara')),
  source_id       text NOT NULL,                -- file name / item id / digital id at source
  title           text NOT NULL,
  caption         text,
  image_url       text NOT NULL,                -- direct hot-link URL (we do not re-host)
  thumb_url       text,                         -- smaller preview if source provides one
  width           int,
  height          int,
  year            int,                          -- production year if known
  kind            text,                         -- 'poster' | 'still' | 'lobby_card' | 'screenshot' | 'newsreel'
  rights          text NOT NULL DEFAULT 'public-domain',  -- always PD by definition for this table
  rights_url      text,                         -- link to the rights statement at source
  source_url      text NOT NULL,                -- back to the source page (provenance display)
  production_id   uuid REFERENCES film_production(id) ON DELETE SET NULL,
  fetched_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source, source_id)
);
CREATE INDEX IF NOT EXISTS idx_pdfilm_year       ON pd_film_image (year);
CREATE INDEX IF NOT EXISTS idx_pdfilm_kind       ON pd_film_image (kind);
CREATE INDEX IF NOT EXISTS idx_pdfilm_source     ON pd_film_image (source);
CREATE INDEX IF NOT EXISTS idx_pdfilm_production ON pd_film_image (production_id);