gupi-osint-board/migrations/002_widgetize_levels.sql

CREATE TABLE osint.levels (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  subtitle TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE osint.widgets (
  id TEXT PRIMARY KEY,
  level_id TEXT NOT NULL REFERENCES osint.levels(id) ON DELETE CASCADE,
  widget_type TEXT NOT NULL CHECK (widget_type IN ('document', 'evidence', 'note', 'event')),
  title TEXT NOT NULL,
  content TEXT NOT NULL DEFAULT '',
  config JSONB NOT NULL DEFAULT '{}'::jsonb,
  source_widget_id TEXT REFERENCES osint.widgets(id) ON DELETE SET NULL,
  source_region_key TEXT,
  event_date DATE,
  x DOUBLE PRECISION,
  y DOUBLE PRECISION,
  width DOUBLE PRECISION,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (level_id, id)
);

CREATE INDEX widgets_level_type_idx ON osint.widgets(level_id, widget_type, sort_order);

CREATE TABLE osint.widget_regions (
  id TEXT PRIMARY KEY,
  document_widget_id TEXT NOT NULL REFERENCES osint.widgets(id) ON DELETE CASCADE,
  region_key TEXT NOT NULL,
  label TEXT NOT NULL,
  excerpt TEXT NOT NULL,
  event_date DATE,
  config JSONB NOT NULL DEFAULT '{}'::jsonb,
  sort_order INTEGER NOT NULL DEFAULT 0,
  UNIQUE (document_widget_id, region_key)
);

CREATE TABLE osint.level_connections (
  id TEXT PRIMARY KEY,
  level_id TEXT NOT NULL REFERENCES osint.levels(id) ON DELETE CASCADE,
  from_widget_id TEXT NOT NULL REFERENCES osint.widgets(id) ON DELETE CASCADE,
  to_widget_id TEXT NOT NULL REFERENCES osint.widgets(id) ON DELETE CASCADE,
  config JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE osint.playthroughs (
  id TEXT PRIMARY KEY,
  level_id TEXT NOT NULL REFERENCES osint.levels(id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'completed', 'abandoned')),
  viewport JSONB NOT NULL DEFAULT '{"x":0,"y":28,"zoom":0.7}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE osint.playthrough_widget_state (
  playthrough_id TEXT NOT NULL REFERENCES osint.playthroughs(id) ON DELETE CASCADE,
  widget_id TEXT NOT NULL REFERENCES osint.widgets(id) ON DELETE CASCADE,
  x DOUBLE PRECISION NOT NULL,
  y DOUBLE PRECISION NOT NULL,
  width DOUBLE PRECISION NOT NULL,
  hidden BOOLEAN NOT NULL DEFAULT FALSE,
  annotation TEXT,
  PRIMARY KEY (playthrough_id, widget_id)
);

CREATE TABLE osint.playthrough_widgets (
  id TEXT PRIMARY KEY,
  playthrough_id TEXT NOT NULL REFERENCES osint.playthroughs(id) ON DELETE CASCADE,
  widget_type TEXT NOT NULL CHECK (widget_type IN ('evidence', 'note', 'event')),
  title TEXT NOT NULL,
  content TEXT NOT NULL DEFAULT '',
  source_widget_id TEXT REFERENCES osint.widgets(id) ON DELETE SET NULL,
  source_region_key TEXT,
  event_date DATE,
  x DOUBLE PRECISION NOT NULL,
  y DOUBLE PRECISION NOT NULL,
  width DOUBLE PRECISION NOT NULL,
  config JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE osint.playthrough_connections (
  id TEXT PRIMARY KEY,
  playthrough_id TEXT NOT NULL REFERENCES osint.playthroughs(id) ON DELETE CASCADE,
  from_widget_id TEXT NOT NULL,
  to_widget_id TEXT NOT NULL,
  config JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- Preserve levels saved by the pre-widget architecture, if any exist.
INSERT INTO osint.levels (id, title, subtitle, status, created_at, updated_at)
SELECT id, state_json->>'title', COALESCE(state_json->>'subtitle', ''), 'draft', created_at, updated_at
FROM osint.cases;

INSERT INTO osint.widgets (id, level_id, widget_type, title, content, config, event_date, sort_order)
SELECT doc->>'id', c.id, 'document', doc->>'title', '',
       jsonb_build_object('kind', doc->>'kind', 'date', doc->>'date', 'body', COALESCE(doc->'body', '[]'::jsonb)),
       NULLIF(doc->>'date', '')::date, ordinality::integer
FROM osint.cases c
CROSS JOIN LATERAL jsonb_array_elements(c.state_json->'documents') WITH ORDINALITY AS item(doc, ordinality);

INSERT INTO osint.widget_regions (id, document_widget_id, region_key, label, excerpt, event_date, sort_order)
SELECT (doc->>'id') || ':' || (region->>'id'), doc->>'id', region->>'id', region->>'label', region->>'excerpt',
       NULLIF(region->>'date', '')::date, region_ordinality::integer
FROM osint.cases c
CROSS JOIN LATERAL jsonb_array_elements(c.state_json->'documents') AS docs(doc)
CROSS JOIN LATERAL jsonb_array_elements(doc->'regions') WITH ORDINALITY AS regions(region, region_ordinality);

INSERT INTO osint.widgets (id, level_id, widget_type, title, content, source_widget_id, source_region_key, event_date, x, y, width, sort_order)
SELECT ev->>'id', c.id, ev->>'type', ev->>'title', ev->>'content', NULLIF(ev->>'sourceDocumentId', ''),
       NULLIF(ev->>'sourceRegionId', ''), NULLIF(ev->>'eventDate', '')::date,
       (ev->>'x')::double precision, (ev->>'y')::double precision, (ev->>'width')::double precision, ordinality::integer
FROM osint.cases c
CROSS JOIN LATERAL jsonb_array_elements(c.state_json->'evidence') WITH ORDINALITY AS item(ev, ordinality);

INSERT INTO osint.level_connections (id, level_id, from_widget_id, to_widget_id)
SELECT conn->>'id', c.id, conn->>'fromEvidenceId', conn->>'toEvidenceId'
FROM osint.cases c
CROSS JOIN LATERAL jsonb_array_elements(c.state_json->'connections') AS connections(conn);

INSERT INTO osint.playthroughs (id, level_id, viewport)
SELECT 'default:' || id, id, COALESCE(state_json->'viewport', '{"x":0,"y":28,"zoom":0.7}'::jsonb)
FROM osint.cases;

DROP TABLE osint.cases;