`db/migrations/007_film_locations.sql`

44 lines
-- Film + TV production records and the addresses that hosted them.
-- Source-of-record: FilmLA permits via City of LA Open Data Portal.
-- All data is public-record by definition (filming permits are public).

CREATE TABLE IF NOT EXISTS film_production (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          text UNIQUE NOT NULL,
  kind          text NOT NULL,                       -- 'film' | 'tv' | 'commercial' | 'music_video' | 'documentary' | 'short' | 'student'
  title         text NOT NULL,
  year          int,                                  -- year of release / first air; null for in-production
  imdb_id       text,                                 -- 'tt0166924' style
  wikidata_qid  text,                                 -- 'Q170564' style
  poster_url    text,
  blurb         text,
  source_tier   char(1) NOT NULL CHECK (source_tier IN ('A','B','C','D')) DEFAULT 'C',
  source_label  text,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_film_year ON film_production (year);
CREATE INDEX IF NOT EXISTS idx_film_kind ON film_production (kind);
CREATE INDEX IF NOT EXISTS idx_film_title ON film_production (lower(title));

-- A specific shoot / scene at a place.
CREATE TABLE IF NOT EXISTS filming_location (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  production_id   uuid NOT NULL REFERENCES film_production(id) ON DELETE CASCADE,
  listing_id      uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
  role            text,                               -- 'exterior' | 'interior' | 'establishing_shot' | 'background_plate'
  scene_note      text,                               -- "Driveway intro shot", "Pool party scene"
  shoot_date_from date,
  shoot_date_to   date,
  permit_number   text,                               -- FilmLA / city permit
  source_tier     char(1) NOT NULL CHECK (source_tier IN ('A','B','C','D')) DEFAULT 'A',
  source_label    text,
  source_url      text,
  confidence      numeric(3,2) NOT NULL DEFAULT 0.85 CHECK (confidence BETWEEN 0 AND 1),
  public_visible  boolean NOT NULL DEFAULT true,
  created_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (production_id, listing_id, permit_number)
);
CREATE INDEX IF NOT EXISTS idx_filmloc_production ON filming_location (production_id);
CREATE INDEX IF NOT EXISTS idx_filmloc_listing ON filming_location (listing_id);
CREATE INDEX IF NOT EXISTS idx_filmloc_visible ON filming_location (public_visible) WHERE public_visible;