-- Eventsa Patch 11: canonical workflow values + database guards
-- Prerequisite: P2 review workflow migration already applied.
BEGIN;

UPDATE public.article
SET state = CASE
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('pending','waiting','wait') THEN 'pending'
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('inprogress','progress','reviewing') THEN 'inProgress'
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('returned','return','revision','needsrevision') THEN 'returned'
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('approved','approve','accepted','accept') THEN 'approved'
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('rejected','reject','declined') THEN 'rejected'
  ELSE 'pending'
END,
judgestate = CASE
  WHEN lower(regexp_replace(COALESCE(judgestate, ''), '[[:space:]_-]+', '', 'g')) IN ('accepted','accept','approved') THEN 'accepted'
  WHEN lower(regexp_replace(COALESCE(judgestate, ''), '[[:space:]_-]+', '', 'g')) IN ('rejected','reject','declined') THEN 'rejected'
  ELSE 'pending'
END,
"reviewLevel" = GREATEST(1, COALESCE("reviewLevel", 1)),
version = GREATEST(1, COALESCE(version, 1));

UPDATE public.article_user
SET state = CASE
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('pending','waiting','wait') THEN 'pending'
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('inprogress','progress','reviewing') THEN 'inProgress'
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('returned','return','revision','needsrevision') THEN 'returned'
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('approved','approve','accepted','accept') THEN 'approved'
  WHEN lower(regexp_replace(COALESCE(state, ''), '[[:space:]_-]+', '', 'g')) IN ('rejected','reject','declined') THEN 'rejected'
  ELSE 'pending'
END,
judgestate = CASE
  WHEN lower(regexp_replace(COALESCE(judgestate, ''), '[[:space:]_-]+', '', 'g')) IN ('accepted','accept','approved') THEN 'accepted'
  WHEN lower(regexp_replace(COALESCE(judgestate, ''), '[[:space:]_-]+', '', 'g')) IN ('rejected','reject','declined') THEN 'rejected'
  ELSE 'pending'
END,
"reviewStatus" = CASE
  WHEN lower(regexp_replace(COALESCE("reviewStatus", ''), '[[:space:]_-]+', '', 'g')) IN ('assigned','pending','waiting') THEN 'assigned'
  WHEN lower(regexp_replace(COALESCE("reviewStatus", ''), '[[:space:]_-]+', '', 'g')) IN ('accepted','accept') THEN 'accepted'
  WHEN lower(regexp_replace(COALESCE("reviewStatus", ''), '[[:space:]_-]+', '', 'g')) IN ('inprogress','reviewing') THEN 'inProgress'
  WHEN lower(regexp_replace(COALESCE("reviewStatus", ''), '[[:space:]_-]+', '', 'g')) IN ('submitted','complete','completed','approved') THEN 'submitted'
  WHEN lower(regexp_replace(COALESCE("reviewStatus", ''), '[[:space:]_-]+', '', 'g')) IN ('declined','rejected','reject') THEN 'declined'
  WHEN lower(regexp_replace(COALESCE("reviewStatus", ''), '[[:space:]_-]+', '', 'g')) IN ('returned','return','revision') THEN 'returned'
  ELSE 'assigned'
END,
level = GREATEST(1, COALESCE(level, 1)),
"assignedAt" = COALESCE("assignedAt", created, CURRENT_TIMESTAMP);

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

ALTER TABLE public.article
  ALTER COLUMN state SET DEFAULT 'pending',
  ALTER COLUMN state SET NOT NULL,
  ALTER COLUMN judgestate SET DEFAULT 'pending',
  ALTER COLUMN judgestate SET NOT NULL,
  ALTER COLUMN "reviewLevel" SET NOT NULL,
  ALTER COLUMN version SET NOT NULL;

ALTER TABLE public.article_user
  ALTER COLUMN state SET DEFAULT 'pending',
  ALTER COLUMN state SET NOT NULL,
  ALTER COLUMN judgestate SET DEFAULT 'pending',
  ALTER COLUMN judgestate SET NOT NULL,
  ALTER COLUMN "reviewStatus" SET DEFAULT 'assigned',
  ALTER COLUMN "reviewStatus" SET NOT NULL,
  ALTER COLUMN level SET NOT NULL;

ALTER TABLE public.article DROP CONSTRAINT IF EXISTS "CHK_article_state";
ALTER TABLE public.article DROP CONSTRAINT IF EXISTS "CHK_article_judgestate";
ALTER TABLE public.article DROP CONSTRAINT IF EXISTS "CHK_article_review_level";
ALTER TABLE public.article DROP CONSTRAINT IF EXISTS "CHK_article_version_positive";
ALTER TABLE public.article_user DROP CONSTRAINT IF EXISTS "CHK_article_user_state";
ALTER TABLE public.article_user DROP CONSTRAINT IF EXISTS "CHK_article_user_judgestate";
ALTER TABLE public.article_user DROP CONSTRAINT IF EXISTS "CHK_article_user_review_status";
ALTER TABLE public.article_user DROP CONSTRAINT IF EXISTS "CHK_article_user_level";

ALTER TABLE public.article
  ADD CONSTRAINT "CHK_article_state"
    CHECK (state IN ('pending','inProgress','returned','approved','rejected')),
  ADD CONSTRAINT "CHK_article_judgestate"
    CHECK (judgestate IN ('pending','accepted','rejected')),
  ADD CONSTRAINT "CHK_article_review_level"
    CHECK ("reviewLevel" >= 1),
  ADD CONSTRAINT "CHK_article_version_positive"
    CHECK (version >= 1);

ALTER TABLE public.article_user
  ADD CONSTRAINT "CHK_article_user_state"
    CHECK (state IN ('pending','inProgress','returned','approved','rejected')),
  ADD CONSTRAINT "CHK_article_user_judgestate"
    CHECK (judgestate IN ('pending','accepted','rejected')),
  ADD CONSTRAINT "CHK_article_user_review_status"
    CHECK ("reviewStatus" IN ('assigned','accepted','inProgress','submitted','declined','returned')),
  ADD CONSTRAINT "CHK_article_user_level"
    CHECK (level >= 1);

COMMIT;