mini-prog/backend/migrations/0009_event_ops_phase2.sql

BEGIN;

CREATE TABLE event_presentations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  presentation_public_id TEXT NOT NULL UNIQUE,
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  presentation_type TEXT NOT NULL CHECK (presentation_type IN ('card', 'detail', 'h5', 'result', 'generic')),
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  is_default BOOLEAN NOT NULL DEFAULT FALSE,
  schema_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (event_id, code)
);

CREATE INDEX event_presentations_event_id_idx ON event_presentations(event_id);
CREATE INDEX event_presentations_status_idx ON event_presentations(status);

CREATE TRIGGER event_presentations_set_updated_at
BEFORE UPDATE ON event_presentations
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE content_bundles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content_bundle_public_id TEXT NOT NULL UNIQUE,
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'disabled', 'archived')),
  is_default BOOLEAN NOT NULL DEFAULT FALSE,
  entry_url TEXT,
  asset_root_url TEXT,
  metadata_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (event_id, code)
);

CREATE INDEX content_bundles_event_id_idx ON content_bundles(event_id);
CREATE INDEX content_bundles_status_idx ON content_bundles(status);

CREATE TRIGGER content_bundles_set_updated_at
BEFORE UPDATE ON content_bundles
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

ALTER TABLE event_releases
ADD COLUMN presentation_id UUID REFERENCES event_presentations(id) ON DELETE SET NULL,
ADD COLUMN content_bundle_id UUID REFERENCES content_bundles(id) ON DELETE SET NULL;

CREATE INDEX event_releases_presentation_id_idx ON event_releases(presentation_id);
CREATE INDEX event_releases_content_bundle_id_idx ON event_releases(content_bundle_id);

COMMIT;