-- ============================================================================
-- Legacy Page Builder Phase 5a: Archiv-Tabellen (SICHER ADDITIV – kein Datenverlust)
--
-- ⚠️  NICHT ohne Backup ausführen:
--     node scripts/backup-legacy-page-builder-tables.mjs --write
--     node scripts/audit-legacy-page-builder.mjs
--
-- Doku: packages/database/docs/20260604_legacy_page_pageblock_archive_PHASE5_SAFE.md
-- - Nur CREATE TABLE + INSERT (Kopie)
-- - Kein DROP, kein DELETE, kein ALTER an "Page" / "PageBlock"
-- - Keine FK auf ShoppingCenter (Archiv bleibt auch nach Live-DROP lesbar)
-- ============================================================================

-- 1. Archiv: ehemalige SPA-Seiten (Page)
CREATE TABLE IF NOT EXISTS "public"."LegacyPageArchive" (
  "id" TEXT NOT NULL,
  "title" TEXT NOT NULL,
  "slug" TEXT NOT NULL,
  "type" TEXT NOT NULL,
  "status" TEXT NOT NULL DEFAULT 'draft',
  "metaTitle" TEXT,
  "metaDescription" TEXT,
  "metaKeywords" TEXT,
  "ogImage" TEXT,
  "template" TEXT,
  "theme" TEXT,
  "customCSS" TEXT,
  "publishedAt" TIMESTAMP(3),
  "scheduledAt" TIMESTAMP(3),
  "createdAt" TIMESTAMP(3) NOT NULL,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  "createdBy" TEXT NOT NULL,
  "centerId" TEXT NOT NULL,
  "isHomepage" BOOLEAN NOT NULL DEFAULT false,
  "isActive" BOOLEAN NOT NULL DEFAULT true,
  "position" INTEGER,
  "archivedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "LegacyPageArchive_pkey" PRIMARY KEY ("id")
);

CREATE INDEX IF NOT EXISTS "LegacyPageArchive_centerId_status_idx"
  ON "public"."LegacyPageArchive"("centerId", "status");
CREATE INDEX IF NOT EXISTS "LegacyPageArchive_type_status_idx"
  ON "public"."LegacyPageArchive"("type", "status");
CREATE INDEX IF NOT EXISTS "LegacyPageArchive_centerId_slug_idx"
  ON "public"."LegacyPageArchive"("centerId", "slug");

-- 2. Archiv: ehemalige Page-Blöcke
CREATE TABLE IF NOT EXISTS "public"."LegacyPageBlockArchive" (
  "id" TEXT NOT NULL,
  "blockType" TEXT NOT NULL,
  "variant" TEXT,
  "position" INTEGER NOT NULL,
  "config" TEXT NOT NULL,
  "content" TEXT NOT NULL,
  "isVisible" BOOLEAN NOT NULL DEFAULT true,
  "conditions" TEXT,
  "createdAt" TIMESTAMP(3) NOT NULL,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  "pageId" TEXT NOT NULL,
  "archivedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "LegacyPageBlockArchive_pkey" PRIMARY KEY ("id")
);

CREATE INDEX IF NOT EXISTS "LegacyPageBlockArchive_pageId_position_idx"
  ON "public"."LegacyPageBlockArchive"("pageId", "position");
CREATE INDEX IF NOT EXISTS "LegacyPageBlockArchive_blockType_idx"
  ON "public"."LegacyPageBlockArchive"("blockType");

-- 3. Daten kopieren (idempotent — bestehende Archiv-Zeilen werden übersprungen)
INSERT INTO "public"."LegacyPageArchive" (
  "id", "title", "slug", "type", "status",
  "metaTitle", "metaDescription", "metaKeywords", "ogImage",
  "template", "theme", "customCSS",
  "publishedAt", "scheduledAt", "createdAt", "updatedAt",
  "createdBy", "centerId", "isHomepage", "isActive", "position",
  "archivedAt"
)
SELECT
  p."id", p."title", p."slug", p."type", p."status",
  p."metaTitle", p."metaDescription", p."metaKeywords", p."ogImage",
  p."template", p."theme", p."customCSS",
  p."publishedAt", p."scheduledAt", p."createdAt", p."updatedAt",
  p."createdBy", p."centerId", p."isHomepage", p."isActive", p."position",
  CURRENT_TIMESTAMP
FROM "public"."Page" p
WHERE NOT EXISTS (
  SELECT 1 FROM "public"."LegacyPageArchive" a WHERE a."id" = p."id"
);

INSERT INTO "public"."LegacyPageBlockArchive" (
  "id", "blockType", "variant", "position", "config", "content",
  "isVisible", "conditions", "createdAt", "updatedAt", "pageId",
  "archivedAt"
)
SELECT
  b."id", b."blockType", b."variant", b."position", b."config", b."content",
  b."isVisible", b."conditions", b."createdAt", b."updatedAt", b."pageId",
  CURRENT_TIMESTAMP
FROM "public"."PageBlock" b
WHERE NOT EXISTS (
  SELECT 1 FROM "public"."LegacyPageBlockArchive" a WHERE a."id" = b."id"
);

-- 4. Verifikation (manuell prüfen — Counts sollten übereinstimmen)
-- SELECT
--   (SELECT COUNT(*) FROM "public"."Page") AS live_pages,
--   (SELECT COUNT(*) FROM "public"."LegacyPageArchive") AS archived_pages,
--   (SELECT COUNT(*) FROM "public"."PageBlock") AS live_blocks,
--   (SELECT COUNT(*) FROM "public"."LegacyPageBlockArchive") AS archived_blocks;
