-- Dé-partitionnement de keywords (audit db-audit-2026-07-07 §5, option b).
-- La prod n'a jamais exécuté 0045 : elle rejouera 0045 (partitionne) puis 0057
-- (dé-partitionne) — linéaire et idempotent. PK(id) seule : id = `${siteId}-${slug}`,
-- globalement unique par construction. FK satellites simplifiées vers keywords(id) ;
-- les colonnes site_id satellites restent (tenancy/RLS futur) avec FK sites + index (P11).
CREATE TABLE "keywords_flat" (LIKE "keywords" INCLUDING DEFAULTS);--> statement-breakpoint
ALTER TABLE "keywords_flat" ADD PRIMARY KEY ("id");--> statement-breakpoint
INSERT INTO "keywords_flat" SELECT * FROM "keywords";--> statement-breakpoint
DROP TABLE "keywords" CASCADE;--> statement-breakpoint
ALTER TABLE "keywords_flat" RENAME TO "keywords";--> statement-breakpoint
CREATE INDEX "keywords_site_term_idx" ON "keywords" USING btree ("site_id","term");--> statement-breakpoint
CREATE INDEX "keywords_stages_gin" ON "keywords" USING gin ("stages");--> statement-breakpoint
ALTER TABLE "keywords" ADD CONSTRAINT "keywords_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "keyword_targets" ADD CONSTRAINT "keyword_targets_keyword_id_keywords_id_fk" FOREIGN KEY ("keyword_id") REFERENCES "public"."keywords"("id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "keyword_targets" ADD CONSTRAINT "keyword_targets_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade;--> statement-breakpoint
CREATE INDEX "keyword_targets_site_idx" ON "keyword_targets" USING btree ("site_id");--> statement-breakpoint
ALTER TABLE "content_keywords" ADD CONSTRAINT "content_keywords_keyword_id_keywords_id_fk" FOREIGN KEY ("keyword_id") REFERENCES "public"."keywords"("id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "content_keywords" ADD CONSTRAINT "content_keywords_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade;--> statement-breakpoint
CREATE INDEX "content_keywords_site_idx" ON "content_keywords" USING btree ("site_id");--> statement-breakpoint
ALTER TABLE "content_items" ADD CONSTRAINT "content_items_primary_keyword_id_keywords_id_fk" FOREIGN KEY ("primary_keyword_id") REFERENCES "public"."keywords"("id") ON DELETE set null;--> statement-breakpoint
ALTER TABLE "keyword_shortlist" ADD CONSTRAINT "keyword_shortlist_keyword_id_keywords_id_fk" FOREIGN KEY ("keyword_id") REFERENCES "public"."keywords"("id") ON DELETE cascade;