BEGIN;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM "JourneySet" WHERE "wordCardId" IS NOT NULL) AND (
    (SELECT count(*) FROM "WordCard") <> 164 OR
    (SELECT count(DISTINCT "displayOrder") FROM "WordCard") <> 164 OR
    (SELECT min("displayOrder") FROM "WordCard") <> 1 OR
    (SELECT max("displayOrder") FROM "WordCard") <> 164
  ) THEN
    RAISE EXCEPTION 'cannot migrate selected words: legacy word catalog is not the canonical 1..164 deck';
  END IF;
  IF EXISTS (SELECT 1 FROM "JourneySet" WHERE "imageCardId" IS NOT NULL) AND (
    (SELECT count(*) FROM "ImageCard") <> 164 OR
    (SELECT count(DISTINCT "displayOrder") FROM "ImageCard") <> 164 OR
    (SELECT min("displayOrder") FROM "ImageCard") <> 1 OR
    (SELECT max("displayOrder") FROM "ImageCard") <> 164
  ) THEN
    RAISE EXCEPTION 'cannot migrate selected images: legacy image catalog is not the canonical 1..164 deck';
  END IF;
END;
$$;

ALTER TABLE "Journey" ADD COLUMN "themeKey" VARCHAR(80);
ALTER TABLE "Journey" ADD COLUMN "catalogVersion" VARCHAR(40) NOT NULL DEFAULT 'dha-2026-v1';
ALTER TABLE "Journey" ALTER COLUMN "catalogVersion" DROP DEFAULT;

UPDATE "Journey" AS journey
SET
  "themeKey" = COALESCE(
    CASE theme."name"
      WHEN 'Relacionamentos' THEN 'relacionamentos'
      WHEN 'Trabalho' THEN 'trabalho'
      WHEN 'Mudanças' THEN 'mudancas'
      WHEN 'Autoconhecimento' THEN 'autoconhecimento'
      WHEN 'Outro' THEN 'outro'
      ELSE NULL
    END,
    'personalizado'
  ),
  "customTheme" = CASE
    WHEN theme."name" IN ('Relacionamentos', 'Trabalho', 'Mudanças', 'Autoconhecimento', 'Outro') THEN journey."customTheme"
    ELSE COALESCE(journey."customTheme", theme."name")
  END
FROM "Theme" AS theme
WHERE journey."themeId" = theme."id";

UPDATE "Journey"
SET "themeKey" = 'personalizado',
    "customTheme" = COALESCE("customTheme", 'Tema anterior')
WHERE "themeKey" IS NULL;

ALTER TABLE "Journey" ALTER COLUMN "themeKey" SET NOT NULL;

ALTER TABLE "JourneySet" ADD COLUMN "wordKey" VARCHAR(40);
ALTER TABLE "JourneySet" ADD COLUMN "imageKey" VARCHAR(40);

UPDATE "JourneySet" AS journey_set
SET "wordKey" = 'palavra-' || lpad(word_card."displayOrder"::text, 3, '0')
FROM "WordCard" AS word_card
WHERE journey_set."wordCardId" = word_card."id";

UPDATE "JourneySet" AS journey_set
SET "imageKey" = 'imagem-' || lpad(image_card."displayOrder"::text, 3, '0')
FROM "ImageCard" AS image_card
WHERE journey_set."imageCardId" = image_card."id";

DROP TRIGGER IF EXISTS "JourneySet_cards_immutable" ON "JourneySet";
DROP FUNCTION IF EXISTS prevent_card_replacement();
ALTER TABLE "JourneySet" DROP CONSTRAINT IF EXISTS "JourneySet_word_before_image_check";
ALTER TABLE "Journey" DROP CONSTRAINT IF EXISTS "Journey_themeId_fkey";
ALTER TABLE "JourneySet" DROP CONSTRAINT IF EXISTS "JourneySet_wordCardId_fkey";
ALTER TABLE "JourneySet" DROP CONSTRAINT IF EXISTS "JourneySet_imageCardId_fkey";
ALTER TABLE "JourneySet" DROP CONSTRAINT IF EXISTS "JourneySet_journeyId_wordCardId_key";
ALTER TABLE "JourneySet" DROP CONSTRAINT IF EXISTS "JourneySet_journeyId_imageCardId_key";

ALTER TABLE "Journey" DROP COLUMN "themeId";
ALTER TABLE "JourneySet" DROP COLUMN "wordCardId";
ALTER TABLE "JourneySet" DROP COLUMN "imageCardId";

DROP TABLE "Theme";
DROP TABLE "WordCard";
DROP TABLE "ImageCard";

CREATE UNIQUE INDEX "JourneySet_journeyId_wordKey_key" ON "JourneySet"("journeyId", "wordKey");
CREATE UNIQUE INDEX "JourneySet_journeyId_imageKey_key" ON "JourneySet"("journeyId", "imageKey");
ALTER TABLE "JourneySet" ADD CONSTRAINT "JourneySet_word_before_image_check"
CHECK ("imageKey" IS NULL OR ("wordKey" IS NOT NULL AND "wordDrawnAt" IS NOT NULL AND "imageDrawnAt" >= "wordDrawnAt"));
ALTER TABLE "JourneySet" ADD CONSTRAINT "JourneySet_word_draw_state_check"
CHECK (("wordKey" IS NULL) = ("wordDrawnAt" IS NULL));
ALTER TABLE "JourneySet" ADD CONSTRAINT "JourneySet_image_draw_state_check"
CHECK (("imageKey" IS NULL) = ("imageDrawnAt" IS NULL));
ALTER TABLE "JourneySet" ADD CONSTRAINT "JourneySet_word_key_format_check"
CHECK ("wordKey" IS NULL OR "wordKey" ~ '^palavra-(00[1-9]|0[1-9][0-9]|1[0-5][0-9]|16[0-4])$');
ALTER TABLE "JourneySet" ADD CONSTRAINT "JourneySet_image_key_format_check"
CHECK ("imageKey" IS NULL OR "imageKey" ~ '^imagem-(00[1-9]|0[1-9][0-9]|1[0-5][0-9]|16[0-4])$');

CREATE FUNCTION prevent_card_replacement() RETURNS trigger AS $$
BEGIN
  IF OLD."wordKey" IS NOT NULL AND NEW."wordKey" IS DISTINCT FROM OLD."wordKey" THEN RAISE EXCEPTION 'word card is immutable'; END IF;
  IF OLD."imageKey" IS NOT NULL AND NEW."imageKey" IS DISTINCT FROM OLD."imageKey" THEN RAISE EXCEPTION 'image card is immutable'; END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER "JourneySet_cards_immutable" BEFORE UPDATE ON "JourneySet" FOR EACH ROW EXECUTE FUNCTION prevent_card_replacement();

COMMIT;