`db/schema.sql`

163 lines
-- Unofficial 90210 Guide — canonical Postgres schema
-- Architecture-agnostic: consumed by the shared backend regardless of repo layout.
-- Rights-first design: the assets table stores LICENSE metadata, not just files,
-- so no image renders without a recorded, cleared right to use it.

create extension if not exists "uuid-ossp";

-- ── Core show / season / episode ───────────────────────────────────────────
create table if not exists shows (
  id                uuid primary key default uuid_generate_v4(),
  canonical_title   text not null,
  display_title     text not null,
  franchise_title   text,
  description_original text not null,      -- ORIGINAL, never a copied synopsis
  official_source_url  text,
  disclaimer_text   text not null,          -- app-level "unofficial" disclaimer
  created_at        timestamptz not null default now()
);

create table if not exists seasons (
  id                uuid primary key default uuid_generate_v4(),
  show_id           uuid not null references shows(id) on delete cascade,
  season_number     int  not null,
  title             text,
  episode_count     int,
  source_last_verified_at timestamptz,
  unique (show_id, season_number)
);

create table if not exists episodes (
  id                uuid primary key default uuid_generate_v4(),
  show_id           uuid not null references shows(id) on delete cascade,
  season_id         uuid references seasons(id) on delete set null,
  season_number     int  not null,
  episode_number    int  not null,
  production_code   text,
  title             text not null,
  air_date          date,
  writer            text,
  director          text,
  summary_short_original text,              -- spoiler-safe, ORIGINAL
  summary_full_original  text,              -- expanded, ORIGINAL
  recap_status      text default 'needs-recap', -- needs-recap | ai-draft | reviewed
  spoiler_rating    int default 0,
  source_url        text,
  source_type       text,
  external_ids      jsonb default '{}',     -- tvmaze/wikidata ids etc.
  last_verified_at  timestamptz,
  created_at        timestamptz not null default now(),
  unique (show_id, season_number, episode_number)
);

-- ── Cast / characters / credits ────────────────────────────────────────────
create table if not exists cast_people (
  id                uuid primary key default uuid_generate_v4(),
  name              text not null,
  biography_original text,                  -- ORIGINAL bio
  bio_status        text default 'needs-bio', -- needs-bio | ai-draft | reviewed
  official_site_url text,
  verified_social_links jsonb default '[]',
  headshot_asset_id uuid,                    -- FK added after assets exists
  publicity_risk_level text default 'editorial-only',
  last_verified_at  timestamptz,
  created_at        timestamptz not null default now()
);

create table if not exists characters (
  id                uuid primary key default uuid_generate_v4(),
  name              text not null,
  description_original text,
  show_id           uuid references shows(id) on delete cascade
);

create table if not exists credits (
  id                uuid primary key default uuid_generate_v4(),
  episode_id        uuid references episodes(id) on delete cascade,
  person_id         uuid references cast_people(id) on delete cascade,
  character_id      uuid references characters(id) on delete set null,
  credit_type       text,
  billing_order     int
);

-- ── Per-episode guest / recurring cast (factual, from TVmaze) ──────────────
create table if not exists episode_guest_cast (
  id                serial primary key,
  episode_id        uuid not null references episodes(id) on delete cascade,
  person_name       text not null,
  character_name    text,
  ord               int default 0
);
create index if not exists idx_egc_ep on episode_guest_cast(episode_id);

-- ── News snippets (short, attributed, linked out) ──────────────────────────
create table if not exists news_items (
  id                uuid primary key default uuid_generate_v4(),
  headline          text not null,
  summary_original  text,                   -- one-sentence ORIGINAL summary (written at review; null while snippet_status='indexed')
  canonical_url     text not null,
  publisher_name    text,
  published_at      timestamptz,
  topic_tags        text[] default '{}',
  person_ids        uuid[] default '{}',
  show_ids          uuid[] default '{}',
  snippet_status    text default 'pending',
  source_query      text,                   -- the Google-News query that surfaced this item
  editor_reviewed_at timestamptz,
  created_at        timestamptz not null default now(),
  unique (canonical_url)                     -- ingest-news.mjs upserts on conflict (canonical_url)
);

-- ── Assets = the RIGHTS LEDGER (the legally critical table) ─────────────────
create table if not exists assets (
  id                uuid primary key default uuid_generate_v4(),
  type              text not null,          -- image | thumbnail | ...
  file_url          text,                   -- null while PENDING a cleared license
  thumbnail_url     text,
  license_type      text not null,          -- public-domain | cc-by | cc-by-sa | licensed | PENDING
  license_url       text,
  attribution_text  text,
  usage_scope       text,                   -- editorial | marketing | icon ...
  source_url        text,
  rights_notes      text,
  approved_for_marketing boolean default false,   -- publicity-rights guard
  license_purchase_status text default 'none',      -- none | drafted-for-approval | purchased
  created_at        timestamptz not null default now()
);

-- headshot FK now that assets exists
do $$ begin
  alter table cast_people
    add constraint cast_people_headshot_fk
    foreign key (headshot_asset_id) references assets(id) on delete set null;
exception when duplicate_object then null; end $$;

-- ── UGC + DMCA (moderation-first, per Apple/Play UGC rules) ─────────────────
create table if not exists ugc_posts (
  id                uuid primary key default uuid_generate_v4(),
  user_id           uuid,
  entity_type       text,                   -- episode | cast | news
  entity_id         uuid,
  content           text,
  spoiler_flag      boolean default false,
  status            text default 'pending', -- pending | published | removed
  report_count      int default 0,
  created_at        timestamptz not null default now()
);

create table if not exists dmca_cases (
  id                uuid primary key default uuid_generate_v4(),
  complainant       text,
  material_url      text,
  notice_received_at timestamptz,
  status            text default 'open',
  resolution        text,
  counter_notice_deadline timestamptz
);

-- Fast lookups
create index if not exists idx_episodes_show_season on episodes(show_id, season_number, episode_number);
create index if not exists idx_news_published on news_items(published_at desc);
create index if not exists idx_assets_license on assets(license_type);
create index if not exists idx_assets_pending on assets(license_purchase_status) where license_purchase_status <> 'purchased';