CREATE TYPE "JourneyStatus" AS ENUM ('EM_PREPARACAO', 'EM_TIRAGEM', 'CARTAS_CONCLUIDAS', 'PAUSADA', 'CANCELADA', 'EXPIRADA');
CREATE TYPE "Movement" AS ENUM ('CIRCUNSTANCIA_PERCEBIDA', 'HISTORIA', 'CONDICIONAMENTOS', 'CONSCIENCIA', 'ESCOLHA_CONSCIENTE');
CREATE TYPE "ConsentType" AS ENUM ('INFORMED', 'PRIVACY', 'SENSITIVE_DATA');
CREATE TYPE "AiOperationType" AS ENUM ('QUESTIONS', 'ANALYSIS', 'AYA_ROUND', 'REPORT_REVISION');
CREATE TYPE "AiOperationStatus" AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'QUOTA_BLOCKED', 'SAFETY_BLOCKED', 'INVALID_OUTPUT');

CREATE TABLE "Journey" ("id" UUID PRIMARY KEY, "publicId" VARCHAR(64) NOT NULL UNIQUE, "status" "JourneyStatus" NOT NULL DEFAULT 'EM_PREPARACAO', "currentStep" INTEGER NOT NULL DEFAULT 0, "themeId" UUID, "customTheme" VARCHAR(120), "circumstanceText" VARCHAR(5000) NOT NULL, "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updatedAt" TIMESTAMP(3) NOT NULL, "completedAt" TIMESTAMP(3), CONSTRAINT "Journey_currentStep_check" CHECK ("currentStep" BETWEEN 0 AND 5));
CREATE TABLE "JourneyContact" ("id" UUID PRIMARY KEY, "journeyId" UUID NOT NULL UNIQUE, "name" VARCHAR(120) NOT NULL, "email" VARCHAR(254) NOT NULL, "emailNormalized" VARCHAR(254) NOT NULL, "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updatedAt" TIMESTAMP(3) NOT NULL);
CREATE TABLE "Consent" ("id" UUID PRIMARY KEY, "journeyId" UUID NOT NULL, "consentType" "ConsentType" NOT NULL, "consentVersion" VARCHAR(32) NOT NULL, "accepted" BOOLEAN NOT NULL, "acceptedAt" TIMESTAMP(3) NOT NULL, "ipHash" VARCHAR(64), "userAgentHash" VARCHAR(64), UNIQUE ("journeyId", "consentType"));
CREATE TABLE "Theme" ("id" UUID PRIMARY KEY, "name" VARCHAR(80) NOT NULL UNIQUE, "description" VARCHAR(300) NOT NULL, "active" BOOLEAN NOT NULL DEFAULT true, "displayOrder" INTEGER NOT NULL);
CREATE TABLE "WordCard" ("id" UUID PRIMARY KEY, "word" VARCHAR(80) NOT NULL UNIQUE, "active" BOOLEAN NOT NULL DEFAULT true, "displayOrder" INTEGER NOT NULL, "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updatedAt" TIMESTAMP(3) NOT NULL);
CREATE TABLE "ImageCard" ("id" UUID PRIMARY KEY, "internalName" VARCHAR(120) NOT NULL UNIQUE, "objectiveDescription" VARCHAR(500) NOT NULL, "storageKey" VARCHAR(300) NOT NULL, "alternativeText" VARCHAR(300) NOT NULL, "active" BOOLEAN NOT NULL DEFAULT true, "displayOrder" INTEGER NOT NULL, "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updatedAt" TIMESTAMP(3) NOT NULL);
CREATE TABLE "JourneySet" ("id" UUID PRIMARY KEY, "journeyId" UUID NOT NULL, "position" INTEGER NOT NULL, "movement" "Movement" NOT NULL, "wordCardId" UUID, "imageCardId" UUID, "initialImpression" VARCHAR(1000), "wordDrawnAt" TIMESTAMP(3), "imageDrawnAt" TIMESTAMP(3), "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updatedAt" TIMESTAMP(3) NOT NULL, CONSTRAINT "JourneySet_position_check" CHECK ("position" BETWEEN 1 AND 5), CONSTRAINT "JourneySet_word_before_image_check" CHECK ("imageCardId" IS NULL OR ("wordCardId" IS NOT NULL AND "wordDrawnAt" IS NOT NULL AND "imageDrawnAt" >= "wordDrawnAt")), UNIQUE ("journeyId", "position"), UNIQUE ("journeyId", "wordCardId"), UNIQUE ("journeyId", "imageCardId"));
CREATE INDEX "JourneySet_journeyId_idx" ON "JourneySet"("journeyId");
CREATE TABLE "PublicAccessSession" ("id" UUID PRIMARY KEY, "journeyId" UUID NOT NULL, "tokenHash" VARCHAR(64) NOT NULL UNIQUE, "expiresAt" TIMESTAMP(3) NOT NULL, "revokedAt" TIMESTAMP(3), "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "lastSeenAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE INDEX "PublicAccessSession_journeyId_idx" ON "PublicAccessSession"("journeyId");
CREATE TABLE "AuditLog" ("id" UUID PRIMARY KEY, "journeyId" UUID, "action" VARCHAR(80) NOT NULL, "entityType" VARCHAR(80) NOT NULL, "entityId" VARCHAR(80) NOT NULL, "metadata" JSONB, "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE "AiOperation" ("id" UUID PRIMARY KEY, "journeyId" UUID NOT NULL, "type" "AiOperationType" NOT NULL, "idempotencyKey" VARCHAR(160) NOT NULL UNIQUE, "inputHash" VARCHAR(64) NOT NULL, "promptVersion" VARCHAR(32) NOT NULL, "model" VARCHAR(120) NOT NULL, "status" "AiOperationStatus" NOT NULL DEFAULT 'PENDING', "requestCount" INTEGER NOT NULL DEFAULT 0, "promptTokens" INTEGER, "outputTokens" INTEGER, "totalTokens" INTEGER, "latencyMs" INTEGER, "providerErrorCode" VARCHAR(80), "resultJson" JSONB, "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "completedAt" TIMESTAMP(3));
CREATE TABLE "AiDailyQuota" ("id" UUID PRIMARY KEY, "provider" VARCHAR(40) NOT NULL, "model" VARCHAR(120) NOT NULL, "quotaDatePacific" DATE NOT NULL, "operationalLimit" INTEGER NOT NULL, "reservedCount" INTEGER NOT NULL DEFAULT 0, "sentCount" INTEGER NOT NULL DEFAULT 0, "failedCount" INTEGER NOT NULL DEFAULT 0, "updatedAt" TIMESTAMP(3) NOT NULL, UNIQUE ("provider", "model", "quotaDatePacific"));

ALTER TABLE "Journey" ADD CONSTRAINT "Journey_themeId_fkey" FOREIGN KEY ("themeId") REFERENCES "Theme"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "JourneyContact" ADD CONSTRAINT "JourneyContact_journeyId_fkey" FOREIGN KEY ("journeyId") REFERENCES "Journey"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "Consent" ADD CONSTRAINT "Consent_journeyId_fkey" FOREIGN KEY ("journeyId") REFERENCES "Journey"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "JourneySet" ADD CONSTRAINT "JourneySet_journeyId_fkey" FOREIGN KEY ("journeyId") REFERENCES "Journey"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "JourneySet" ADD CONSTRAINT "JourneySet_wordCardId_fkey" FOREIGN KEY ("wordCardId") REFERENCES "WordCard"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "JourneySet" ADD CONSTRAINT "JourneySet_imageCardId_fkey" FOREIGN KEY ("imageCardId") REFERENCES "ImageCard"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "PublicAccessSession" ADD CONSTRAINT "PublicAccessSession_journeyId_fkey" FOREIGN KEY ("journeyId") REFERENCES "Journey"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "AuditLog" ADD CONSTRAINT "AuditLog_journeyId_fkey" FOREIGN KEY ("journeyId") REFERENCES "Journey"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "AiOperation" ADD CONSTRAINT "AiOperation_journeyId_fkey" FOREIGN KEY ("journeyId") REFERENCES "Journey"("id") ON DELETE CASCADE ON UPDATE CASCADE;

CREATE FUNCTION prevent_card_replacement() RETURNS trigger AS $$
BEGIN
  IF OLD."wordCardId" IS NOT NULL AND NEW."wordCardId" IS DISTINCT FROM OLD."wordCardId" THEN RAISE EXCEPTION 'word card is immutable'; END IF;
  IF OLD."imageCardId" IS NOT NULL AND NEW."imageCardId" IS DISTINCT FROM OLD."imageCardId" 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();