-- 0050 — Partitionnement mensuel RANGE(date) des snapshots GSC (Phase 0 ADR).
-- Manuscrite : drizzle-kit ne sait pas émettre PARTITION BY. Le schéma logique
-- (colonnes, PK, FK) est inchangé — domain-schema.ts et le snapshot meta
-- restent la vérité déclarative. Pattern copy+swap du précédent 0045 (keywords).
-- Dev/test sont vides mais la PROD ne l'est pas : la copie est obligatoire.
-- Noms de contraintes PK/FK repris à l'identique de 0017_reflective_firelord.sql.

-- ============ gsc_query_snapshots ============
CREATE TABLE "gsc_query_snapshots_p" (LIKE "gsc_query_snapshots" INCLUDING DEFAULTS)
  PARTITION BY RANGE ("date");
--> statement-breakpoint
-- PK anonyme ici : le nom historique est encore porté par l'index PK de
-- l'ancienne table (namespace relations partagé) — renommé après le DROP.
ALTER TABLE "gsc_query_snapshots_p" ADD PRIMARY KEY ("site_id", "date", "query");
--> statement-breakpoint
DO $$
DECLARE m date;
BEGIN
  FOR m IN SELECT generate_series('2026-01-01'::date, '2028-12-01'::date, interval '1 month')
  LOOP
    EXECUTE format(
      'CREATE TABLE %I PARTITION OF "gsc_query_snapshots_p" FOR VALUES FROM (%L) TO (%L)',
      'gsc_query_snapshots_' || to_char(m, 'YYYY_MM'), m, m + interval '1 month'
    );
  END LOOP;
END $$;
--> statement-breakpoint
CREATE TABLE "gsc_query_snapshots_default" PARTITION OF "gsc_query_snapshots_p" DEFAULT;
--> statement-breakpoint
INSERT INTO "gsc_query_snapshots_p" SELECT * FROM "gsc_query_snapshots";
--> statement-breakpoint
DROP TABLE "gsc_query_snapshots" CASCADE;
--> statement-breakpoint
ALTER TABLE "gsc_query_snapshots_p" RENAME TO "gsc_query_snapshots";
--> statement-breakpoint
-- Renomme l'index PK (et donc la contrainte) au nom exact de 0017.
ALTER INDEX "gsc_query_snapshots_p_pkey" RENAME TO "gsc_query_snapshots_site_id_date_query_pk";
--> statement-breakpoint
ALTER TABLE "gsc_query_snapshots"
  ADD CONSTRAINT "gsc_query_snapshots_site_id_sites_id_fk"
  FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade ON UPDATE no action;
--> statement-breakpoint
CREATE INDEX "gsc_query_snap_date_brin" ON "gsc_query_snapshots" USING brin ("date");
--> statement-breakpoint

-- ============ gsc_page_snapshots ============ (symétrique, dim = page)
CREATE TABLE "gsc_page_snapshots_p" (LIKE "gsc_page_snapshots" INCLUDING DEFAULTS)
  PARTITION BY RANGE ("date");
--> statement-breakpoint
ALTER TABLE "gsc_page_snapshots_p" ADD PRIMARY KEY ("site_id", "date", "page");
--> statement-breakpoint
DO $$
DECLARE m date;
BEGIN
  FOR m IN SELECT generate_series('2026-01-01'::date, '2028-12-01'::date, interval '1 month')
  LOOP
    EXECUTE format(
      'CREATE TABLE %I PARTITION OF "gsc_page_snapshots_p" FOR VALUES FROM (%L) TO (%L)',
      'gsc_page_snapshots_' || to_char(m, 'YYYY_MM'), m, m + interval '1 month'
    );
  END LOOP;
END $$;
--> statement-breakpoint
CREATE TABLE "gsc_page_snapshots_default" PARTITION OF "gsc_page_snapshots_p" DEFAULT;
--> statement-breakpoint
INSERT INTO "gsc_page_snapshots_p" SELECT * FROM "gsc_page_snapshots";
--> statement-breakpoint
DROP TABLE "gsc_page_snapshots" CASCADE;
--> statement-breakpoint
ALTER TABLE "gsc_page_snapshots_p" RENAME TO "gsc_page_snapshots";
--> statement-breakpoint
ALTER INDEX "gsc_page_snapshots_p_pkey" RENAME TO "gsc_page_snapshots_site_id_date_page_pk";
--> statement-breakpoint
ALTER TABLE "gsc_page_snapshots"
  ADD CONSTRAINT "gsc_page_snapshots_site_id_sites_id_fk"
  FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade ON UPDATE no action;
--> statement-breakpoint
CREATE INDEX "gsc_page_snap_date_brin" ON "gsc_page_snapshots" USING brin ("date");