-- Hash-partition keywords by site_id (16 partitions) + composite FKs. Local only.
CREATE TABLE "keywords_p" (LIKE "keywords" INCLUDING DEFAULTS) PARTITION BY HASH ("site_id");--> statement-breakpoint
ALTER TABLE "keywords_p" ADD PRIMARY KEY ("id", "site_id");--> statement-breakpoint
CREATE TABLE "keywords_p_0" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 0);--> statement-breakpoint
CREATE TABLE "keywords_p_1" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 1);--> statement-breakpoint
CREATE TABLE "keywords_p_2" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 2);--> statement-breakpoint
CREATE TABLE "keywords_p_3" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 3);--> statement-breakpoint
CREATE TABLE "keywords_p_4" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 4);--> statement-breakpoint
CREATE TABLE "keywords_p_5" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 5);--> statement-breakpoint
CREATE TABLE "keywords_p_6" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 6);--> statement-breakpoint
CREATE TABLE "keywords_p_7" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 7);--> statement-breakpoint
CREATE TABLE "keywords_p_8" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 8);--> statement-breakpoint
CREATE TABLE "keywords_p_9" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 9);--> statement-breakpoint
CREATE TABLE "keywords_p_10" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 10);--> statement-breakpoint
CREATE TABLE "keywords_p_11" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 11);--> statement-breakpoint
CREATE TABLE "keywords_p_12" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 12);--> statement-breakpoint
CREATE TABLE "keywords_p_13" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 13);--> statement-breakpoint
CREATE TABLE "keywords_p_14" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 14);--> statement-breakpoint
CREATE TABLE "keywords_p_15" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 15);--> statement-breakpoint
INSERT INTO "keywords_p" SELECT * FROM "keywords";--> statement-breakpoint
ALTER TABLE "keyword_targets" ADD COLUMN "site_id" text;--> statement-breakpoint
UPDATE "keyword_targets" t SET "site_id" = k."site_id" FROM "keywords" k WHERE t."keyword_id" = k."id";--> statement-breakpoint
ALTER TABLE "keyword_targets" ALTER COLUMN "site_id" SET NOT NULL;--> statement-breakpoint
ALTER TABLE "content_keywords" ADD COLUMN "site_id" text;--> statement-breakpoint
UPDATE "content_keywords" t SET "site_id" = k."site_id" FROM "keywords" k WHERE t."keyword_id" = k."id";--> statement-breakpoint
ALTER TABLE "content_keywords" ALTER COLUMN "site_id" SET NOT NULL;--> statement-breakpoint
DROP TABLE "keywords" CASCADE;--> statement-breakpoint
ALTER TABLE "keywords_p" RENAME TO "keywords";--> statement-breakpoint
CREATE INDEX "keywords_site_idx" ON "keywords" USING btree ("site_id");--> 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 "keyword_targets" ADD CONSTRAINT "keyword_targets_keyword_id_site_id_keywords_id_site_id_fk" FOREIGN KEY ("keyword_id","site_id") REFERENCES "public"."keywords"("id","site_id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "content_keywords" ADD CONSTRAINT "content_keywords_keyword_id_site_id_keywords_id_site_id_fk" FOREIGN KEY ("keyword_id","site_id") REFERENCES "public"."keywords"("id","site_id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "content_items" ADD CONSTRAINT "content_items_primary_keyword_id_site_id_keywords_id_site_id_fk" FOREIGN KEY ("primary_keyword_id","site_id") REFERENCES "public"."keywords"("id","site_id") ON DELETE set null;--> statement-breakpoint
ALTER TABLE "keyword_shortlist" ADD CONSTRAINT "keyword_shortlist_keyword_id_site_id_keywords_id_site_id_fk" FOREIGN KEY ("keyword_id","site_id") REFERENCES "public"."keywords"("id","site_id") ON DELETE cascade;