-- EVENTSA P2: article/review workflow, history and versioning
-- Apply AFTER 2026-09-16-p0-hardening.sql. Keep POSTGRES_SYNCHRONIZE=false.
BEGIN;

ALTER TABLE public.article
  ADD COLUMN IF NOT EXISTS "reviewLevel" integer NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS "finalDecision" character varying NULL,
  ADD COLUMN IF NOT EXISTS "finalNote" text NULL,
  ADD COLUMN IF NOT EXISTS "ownerCanEdit" boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS "reviewsVisibleToAuthor" boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS version integer NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS "reviewStartedAt" timestamp without time zone NULL,
  ADD COLUMN IF NOT EXISTS "reviewCompletedAt" timestamp without time zone NULL;

ALTER TABLE public.article_user
  ADD COLUMN IF NOT EXISTS level integer NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS active boolean NOT NULL DEFAULT true,
  ADD COLUMN IF NOT EXISTS "reviewStatus" character varying NOT NULL DEFAULT 'assigned',
  ADD COLUMN IF NOT EXISTS decision character varying NULL,
  ADD COLUMN IF NOT EXISTS "finalNote" text NULL,
  ADD COLUMN IF NOT EXISTS "assignedAt" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN IF NOT EXISTS "acceptedAt" timestamp without time zone NULL,
  ADD COLUMN IF NOT EXISTS "startedAt" timestamp without time zone NULL,
  ADD COLUMN IF NOT EXISTS "submittedAt" timestamp without time zone NULL;

UPDATE public.article_user
SET
  "assignedAt" = COALESCE("assignedAt", created),
  "reviewStatus" = CASE
    WHEN judgestate = 'rejected' OR reject = true THEN 'declined'
    WHEN state = 'approved' OR form IS NOT NULL OR score IS NOT NULL THEN 'submitted'
    WHEN state = 'inProgress' THEN 'inProgress'
    WHEN judgestate = 'accepted' THEN 'accepted'
    WHEN state = 'returned' THEN 'returned'
    ELSE 'assigned'
  END,
  "submittedAt" = CASE
    WHEN state = 'approved' OR form IS NOT NULL OR score IS NOT NULL
      THEN COALESCE("submittedAt", updated, created)
    ELSE "submittedAt"
  END,
  "acceptedAt" = CASE
    WHEN judgestate = 'accepted'
      THEN COALESCE("acceptedAt", updated, created)
    ELSE "acceptedAt"
  END;

UPDATE public.article a
SET
  "reviewStartedAt" = q.first_assigned,
  "reviewLevel" = GREATEST(1, q.max_level)
FROM (
  SELECT "articleId", MIN(COALESCE("assignedAt", created)) AS first_assigned,
         MAX(COALESCE(level, 1)) AS max_level
  FROM public.article_user
  WHERE "articleId" IS NOT NULL
  GROUP BY "articleId"
) q
WHERE a.id = q."articleId";

UPDATE public.article
SET "reviewCompletedAt" = COALESCE("reviewCompletedAt", updated)
WHERE state IN ('approved', 'rejected');

CREATE TABLE IF NOT EXISTS public.article_review_history (
  id bigserial PRIMARY KEY,
  action character varying NOT NULL,
  "fromState" character varying NULL,
  "toState" character varying NULL,
  "fromJudgeState" character varying NULL,
  "toJudgeState" character varying NULL,
  note text NULL,
  score character varying NULL,
  metadata jsonb NULL,
  "articleId" bigint NOT NULL,
  "assignmentId" bigint NULL,
  "actorId" bigint NULL,
  "siteId" bigint NULL,
  created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "FK_review_history_article" FOREIGN KEY ("articleId") REFERENCES public.article(id) ON DELETE CASCADE,
  CONSTRAINT "FK_review_history_assignment" FOREIGN KEY ("assignmentId") REFERENCES public.article_user(id) ON DELETE SET NULL,
  CONSTRAINT "FK_review_history_actor" FOREIGN KEY ("actorId") REFERENCES public."user"(id) ON DELETE SET NULL,
  CONSTRAINT "FK_review_history_site" FOREIGN KEY ("siteId") REFERENCES public.site(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS "IDX_review_history_article" ON public.article_review_history ("articleId");
CREATE INDEX IF NOT EXISTS "IDX_review_history_assignment" ON public.article_review_history ("assignmentId");
CREATE INDEX IF NOT EXISTS "IDX_review_history_site" ON public.article_review_history ("siteId");
CREATE INDEX IF NOT EXISTS "IDX_article_user_review_queue"
  ON public.article_user ("judgeId", active, "reviewStatus", level);
CREATE INDEX IF NOT EXISTS "IDX_article_review_level" ON public.article ("siteId", "reviewLevel", state);

CREATE TABLE IF NOT EXISTS public.article_version (
  id bigserial PRIMARY KEY,
  version integer NOT NULL,
  snapshot jsonb NOT NULL,
  reason text NULL,
  "articleId" bigint NOT NULL,
  "createdById" bigint NULL,
  "siteId" bigint NULL,
  created timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "FK_article_version_article" FOREIGN KEY ("articleId") REFERENCES public.article(id) ON DELETE CASCADE,
  CONSTRAINT "FK_article_version_creator" FOREIGN KEY ("createdById") REFERENCES public."user"(id) ON DELETE SET NULL,
  CONSTRAINT "FK_article_version_site" FOREIGN KEY ("siteId") REFERENCES public.site(id) ON DELETE SET NULL,
  CONSTRAINT "UX_article_version_article_version" UNIQUE ("articleId", version)
);

CREATE INDEX IF NOT EXISTS "IDX_article_version_article" ON public.article_version ("articleId");
CREATE INDEX IF NOT EXISTS "IDX_article_version_site" ON public.article_version ("siteId");

-- Preserve a readable starting point for records that existed before workflow history.
INSERT INTO public.article_review_history
  (action, "fromState", "toState", "fromJudgeState", "toJudgeState", metadata, "articleId", "actorId", "siteId", created)
SELECT
  'migration-existing-article', NULL, a.state, NULL, a.judgestate,
  jsonb_build_object('migrated', true, 'reviewLevel', a."reviewLevel"),
  a.id, a."userId", a."siteId", COALESCE(a.created, CURRENT_TIMESTAMP)
FROM public.article a
WHERE NOT EXISTS (
  SELECT 1 FROM public.article_review_history h
  WHERE h."articleId" = a.id AND h.action = 'migration-existing-article'
);

INSERT INTO public.article_review_history
  (action, "toState", "toJudgeState", note, score, metadata, "articleId", "assignmentId", "actorId", "siteId", created)
SELECT
  'migration-existing-review', au.state, au.judgestate, au.note, au.score,
  jsonb_build_object('migrated', true, 'level', au.level, 'reviewStatus', au."reviewStatus"),
  au."articleId", au.id, au."judgeId", au."siteId", COALESCE(au.created, CURRENT_TIMESTAMP)
FROM public.article_user au
WHERE au."articleId" IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM public.article_review_history h
    WHERE h."assignmentId" = au.id AND h.action = 'migration-existing-review'
  );

-- Initial immutable snapshot for pre-existing articles. createdBy is the author when known.
INSERT INTO public.article_version (version, snapshot, reason, "articleId", "createdById", "siteId", created)
SELECT
  COALESCE(a.version, 1),
  jsonb_build_object(
    'id', a.id,
    'title', a.title,
    'type', a.type,
    'note', a.note,
    'state', a.state,
    'judgestate', a.judgestate,
    'form', a.form,
    'judgeform', a.judgeform,
    'reviewLevel', a."reviewLevel",
    'finalDecision', a."finalDecision",
    'finalNote', a."finalNote",
    'ownerCanEdit', a."ownerCanEdit",
    'reviewsVisibleToAuthor', a."reviewsVisibleToAuthor",
    'userId', a."userId",
    'headJudgeId', a."headJudgeId",
    'seminarId', a."seminarId",
    'workshopId', a."workshopId",
    'siteId', a."siteId"
  ),
  'migration-initial-snapshot',
  a.id,
  a."userId",
  a."siteId",
  COALESCE(a.created, CURRENT_TIMESTAMP)
FROM public.article a
ON CONFLICT ("articleId", version) DO NOTHING;

COMMIT;