CREATE TABLE "site_pages" (
	"id" text PRIMARY KEY NOT NULL,
	"site_id" text NOT NULL,
	"url" text NOT NULL,
	"status" integer,
	"title" text,
	"meta_description" text,
	"h1" text,
	"h1_count" integer DEFAULT 0 NOT NULL,
	"canonical" text,
	"robots" text,
	"lang" text,
	"word_count" integer DEFAULT 0 NOT NULL,
	"issues" text[] DEFAULT '{}' NOT NULL,
	"crawled_at" timestamp with time zone NOT NULL
);
--> statement-breakpoint
ALTER TABLE "analytics_jobs" ALTER COLUMN "source" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "analytics_jobs" ALTER COLUMN "object_key" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "analytics_jobs" ADD COLUMN "type" text DEFAULT 'csv-import' NOT NULL;--> statement-breakpoint
ALTER TABLE "keywords" ADD COLUMN "target_page_id" text;--> statement-breakpoint
ALTER TABLE "keywords" ADD COLUMN "target_page_source" text;--> statement-breakpoint
ALTER TABLE "keywords" ADD COLUMN "target_page_score" integer;--> statement-breakpoint
ALTER TABLE "site_pages" ADD CONSTRAINT "site_pages_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "site_pages_site_idx" ON "site_pages" USING btree ("site_id");--> statement-breakpoint
CREATE UNIQUE INDEX "site_pages_site_url_uidx" ON "site_pages" USING btree ("site_id","url");--> statement-breakpoint
ALTER TABLE "keywords" ADD CONSTRAINT "keywords_target_page_id_site_pages_id_fk" FOREIGN KEY ("target_page_id") REFERENCES "public"."site_pages"("id") ON DELETE set null ON UPDATE no action;