mini-prog/backend/migrations/0006_resource_objects.sql

BEGIN;

CREATE TABLE maps (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  map_public_id TEXT NOT NULL UNIQUE,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  description TEXT,
  current_version_id UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX maps_status_idx ON maps(status);

CREATE TRIGGER maps_set_updated_at
BEFORE UPDATE ON maps
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE map_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  version_public_id TEXT NOT NULL UNIQUE,
  map_id UUID NOT NULL REFERENCES maps(id) ON DELETE CASCADE,
  version_code TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'archived')),
  mapmeta_url TEXT NOT NULL,
  tiles_root_url TEXT NOT NULL,
  published_asset_root TEXT,
  bounds_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(),
  UNIQUE (map_id, version_code)
);

CREATE INDEX map_versions_map_id_idx ON map_versions(map_id);
CREATE INDEX map_versions_status_idx ON map_versions(status);

CREATE TRIGGER map_versions_set_updated_at
BEFORE UPDATE ON map_versions
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

ALTER TABLE maps
ADD CONSTRAINT maps_current_version_fk
FOREIGN KEY (current_version_id) REFERENCES map_versions(id) ON DELETE SET NULL;

CREATE TABLE playfields (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  playfield_public_id TEXT NOT NULL UNIQUE,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  kind TEXT NOT NULL DEFAULT 'course',
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  description TEXT,
  current_version_id UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX playfields_status_idx ON playfields(status);

CREATE TRIGGER playfields_set_updated_at
BEFORE UPDATE ON playfields
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE playfield_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  version_public_id TEXT NOT NULL UNIQUE,
  playfield_id UUID NOT NULL REFERENCES playfields(id) ON DELETE CASCADE,
  version_code TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'archived')),
  source_type TEXT NOT NULL CHECK (source_type IN ('kml', 'geojson', 'control_set', 'json')),
  source_url TEXT NOT NULL,
  published_asset_root TEXT,
  control_count INTEGER,
  bounds_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(),
  UNIQUE (playfield_id, version_code)
);

CREATE INDEX playfield_versions_playfield_id_idx ON playfield_versions(playfield_id);
CREATE INDEX playfield_versions_status_idx ON playfield_versions(status);

CREATE TRIGGER playfield_versions_set_updated_at
BEFORE UPDATE ON playfield_versions
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

ALTER TABLE playfields
ADD CONSTRAINT playfields_current_version_fk
FOREIGN KEY (current_version_id) REFERENCES playfield_versions(id) ON DELETE SET NULL;

CREATE TABLE resource_packs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_pack_public_id TEXT NOT NULL UNIQUE,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  description TEXT,
  current_version_id UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX resource_packs_status_idx ON resource_packs(status);

CREATE TRIGGER resource_packs_set_updated_at
BEFORE UPDATE ON resource_packs
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE resource_pack_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  version_public_id TEXT NOT NULL UNIQUE,
  resource_pack_id UUID NOT NULL REFERENCES resource_packs(id) ON DELETE CASCADE,
  version_code TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'archived')),
  content_entry_url TEXT,
  audio_root_url TEXT,
  theme_profile_code TEXT,
  published_asset_root TEXT,
  metadata_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (resource_pack_id, version_code)
);

CREATE INDEX resource_pack_versions_pack_id_idx ON resource_pack_versions(resource_pack_id);
CREATE INDEX resource_pack_versions_status_idx ON resource_pack_versions(status);

CREATE TRIGGER resource_pack_versions_set_updated_at
BEFORE UPDATE ON resource_pack_versions
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

ALTER TABLE resource_packs
ADD CONSTRAINT resource_packs_current_version_fk
FOREIGN KEY (current_version_id) REFERENCES resource_pack_versions(id) ON DELETE SET NULL;

COMMIT;