mini-prog/backend/migrations/0008_production_skeleton.sql

BEGIN;

CREATE TABLE places (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  place_public_id TEXT NOT NULL UNIQUE,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  region TEXT,
  cover_url TEXT,
  description TEXT,
  center_point_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX places_status_idx ON places(status);

CREATE TRIGGER places_set_updated_at
BEFORE UPDATE ON places
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE map_assets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  map_asset_public_id TEXT NOT NULL UNIQUE,
  place_id UUID NOT NULL REFERENCES places(id) ON DELETE CASCADE,
  legacy_map_id UUID REFERENCES maps(id) ON DELETE SET NULL,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  map_type TEXT NOT NULL DEFAULT 'standard',
  cover_url TEXT,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  current_tile_release_id UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX map_assets_place_id_idx ON map_assets(place_id);
CREATE INDEX map_assets_legacy_map_id_idx ON map_assets(legacy_map_id);
CREATE INDEX map_assets_status_idx ON map_assets(status);

CREATE TRIGGER map_assets_set_updated_at
BEFORE UPDATE ON map_assets
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE tile_releases (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tile_release_public_id TEXT NOT NULL UNIQUE,
  map_asset_id UUID NOT NULL REFERENCES map_assets(id) ON DELETE CASCADE,
  legacy_map_version_id UUID REFERENCES map_versions(id) ON DELETE SET NULL,
  version_code TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'published', 'retired', 'archived')),
  tile_base_url TEXT NOT NULL,
  meta_url TEXT NOT NULL,
  published_asset_root TEXT,
  metadata_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (map_asset_id, version_code)
);

CREATE INDEX tile_releases_map_asset_id_idx ON tile_releases(map_asset_id);
CREATE INDEX tile_releases_legacy_map_version_id_idx ON tile_releases(legacy_map_version_id);
CREATE INDEX tile_releases_status_idx ON tile_releases(status);

CREATE TRIGGER tile_releases_set_updated_at
BEFORE UPDATE ON tile_releases
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

ALTER TABLE map_assets
ADD CONSTRAINT map_assets_current_tile_release_fk
FOREIGN KEY (current_tile_release_id) REFERENCES tile_releases(id) ON DELETE SET NULL;

CREATE TABLE course_sources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_source_public_id TEXT NOT NULL UNIQUE,
  legacy_playfield_version_id UUID REFERENCES playfield_versions(id) ON DELETE SET NULL,
  source_type TEXT NOT NULL CHECK (source_type IN ('kml', 'geojson', 'control_set', 'json')),
  file_url TEXT NOT NULL,
  checksum TEXT,
  parser_version TEXT,
  import_status TEXT NOT NULL DEFAULT 'imported' CHECK (import_status IN ('draft', 'imported', 'parsed', 'failed', 'archived')),
  metadata_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  imported_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX course_sources_legacy_playfield_version_id_idx ON course_sources(legacy_playfield_version_id);
CREATE INDEX course_sources_source_type_idx ON course_sources(source_type);
CREATE INDEX course_sources_import_status_idx ON course_sources(import_status);

CREATE TRIGGER course_sources_set_updated_at
BEFORE UPDATE ON course_sources
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE course_sets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_set_public_id TEXT NOT NULL UNIQUE,
  place_id UUID NOT NULL REFERENCES places(id) ON DELETE RESTRICT,
  map_asset_id UUID NOT NULL REFERENCES map_assets(id) ON DELETE RESTRICT,
  code TEXT NOT NULL UNIQUE,
  mode TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  current_variant_id UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX course_sets_place_id_idx ON course_sets(place_id);
CREATE INDEX course_sets_map_asset_id_idx ON course_sets(map_asset_id);
CREATE INDEX course_sets_status_idx ON course_sets(status);

CREATE TRIGGER course_sets_set_updated_at
BEFORE UPDATE ON course_sets
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE course_variants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_variant_public_id TEXT NOT NULL UNIQUE,
  course_set_id UUID NOT NULL REFERENCES course_sets(id) ON DELETE CASCADE,
  source_id UUID REFERENCES course_sources(id) ON DELETE SET NULL,
  name TEXT NOT NULL,
  route_code TEXT,
  mode TEXT NOT NULL,
  control_count INTEGER,
  difficulty TEXT,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  is_default BOOLEAN NOT NULL DEFAULT FALSE,
  config_patch_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  metadata_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX course_variants_course_set_id_idx ON course_variants(course_set_id);
CREATE INDEX course_variants_source_id_idx ON course_variants(source_id);
CREATE INDEX course_variants_status_idx ON course_variants(status);
CREATE INDEX course_variants_route_code_idx ON course_variants(route_code);

CREATE TRIGGER course_variants_set_updated_at
BEFORE UPDATE ON course_variants
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

ALTER TABLE course_sets
ADD CONSTRAINT course_sets_current_variant_fk
FOREIGN KEY (current_variant_id) REFERENCES course_variants(id) ON DELETE SET NULL;

CREATE TABLE map_runtime_bindings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  runtime_binding_public_id TEXT NOT NULL UNIQUE,
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  place_id UUID NOT NULL REFERENCES places(id) ON DELETE RESTRICT,
  map_asset_id UUID NOT NULL REFERENCES map_assets(id) ON DELETE RESTRICT,
  tile_release_id UUID NOT NULL REFERENCES tile_releases(id) ON DELETE RESTRICT,
  course_set_id UUID NOT NULL REFERENCES course_sets(id) ON DELETE RESTRICT,
  course_variant_id UUID NOT NULL REFERENCES course_variants(id) ON DELETE RESTRICT,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX map_runtime_bindings_event_id_idx ON map_runtime_bindings(event_id);
CREATE INDEX map_runtime_bindings_place_id_idx ON map_runtime_bindings(place_id);
CREATE INDEX map_runtime_bindings_map_asset_id_idx ON map_runtime_bindings(map_asset_id);
CREATE INDEX map_runtime_bindings_tile_release_id_idx ON map_runtime_bindings(tile_release_id);
CREATE INDEX map_runtime_bindings_course_set_id_idx ON map_runtime_bindings(course_set_id);
CREATE INDEX map_runtime_bindings_course_variant_id_idx ON map_runtime_bindings(course_variant_id);
CREATE INDEX map_runtime_bindings_status_idx ON map_runtime_bindings(status);

CREATE TRIGGER map_runtime_bindings_set_updated_at
BEFORE UPDATE ON map_runtime_bindings
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

ALTER TABLE event_releases
ADD COLUMN runtime_binding_id UUID REFERENCES map_runtime_bindings(id) ON DELETE SET NULL;

CREATE INDEX event_releases_runtime_binding_id_idx ON event_releases(runtime_binding_id);

COMMIT;