-- Persist ReviewPanel JSON bundles (templateData + officialBieuMau + full trees)
-- Apply on existing DBs:
--   psql "$INITIATIVE_DATABASE_URL" -f migrations/003_review_documents.sql

CREATE TABLE IF NOT EXISTS application_review_documents (
  id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  initiative_id      UUID NOT NULL REFERENCES initiatives(id) ON DELETE CASCADE,
  case_id            TEXT NOT NULL,
  document_version   INTEGER NOT NULL DEFAULT 1,
  official_bieu_mau  JSONB NOT NULL DEFAULT '{}'::jsonb,
  template_data      JSONB,
  full_bundle        JSONB,
  created_by         UUID REFERENCES users(id),
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (initiative_id, document_version)
);

CREATE INDEX IF NOT EXISTS idx_review_docs_initiative_time
  ON application_review_documents (initiative_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_review_docs_case_time
  ON application_review_documents (case_id, created_at DESC);