`db/migrations/015_walk_of_fame.sql`

28 lines
-- Hollywood Walk of Fame stars — sidewalk inscriptions, not residences.
-- Distinct from entity_place_association (residences/landmarks); these are
-- terrazzo+brass stars at known coordinates on Hollywood Blvd / Vine St.
--
-- Each star: who, category (5 disciplines), address block, lat/lng, dedicated date.
-- Source: Hollywood Chamber of Commerce official list + Wikipedia cross-ref.

CREATE TABLE IF NOT EXISTS walk_of_fame (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  honoree_name    text NOT NULL,
  honoree_slug    text NOT NULL UNIQUE,
  category        text NOT NULL CHECK (category IN ('motion_pictures', 'television', 'recording', 'radio', 'theater', 'sports', 'special')),
  address         text,                      -- e.g. "6774 Hollywood Blvd"
  block           text,                      -- e.g. "6700 W Hollywood Blvd"
  lat             numeric(10,7),
  lng             numeric(10,7),
  dedicated_date  date,                      -- when the star was unveiled
  entity_id       uuid REFERENCES entity(id) ON DELETE SET NULL,  -- link to entity record if exists
  source          text DEFAULT 'Hollywood Chamber of Commerce',
  source_url      text,
  notes           text,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_wof_category   ON walk_of_fame (category);
CREATE INDEX IF NOT EXISTS idx_wof_lat_lng    ON walk_of_fame (lat, lng);
CREATE INDEX IF NOT EXISTS idx_wof_name_lower ON walk_of_fame (lower(honoree_name));
CREATE INDEX IF NOT EXISTS idx_wof_entity     ON walk_of_fame (entity_id);