CREATE TABLE IF NOT EXISTS "profiles" (
	"uri" text GENERATED ALWAYS AS ('at://' || "profiles"."did" || '/blue.recipes.actor.profile/self') STORED,
	"cid" text NOT NULL,
	"did" text PRIMARY KEY NOT NULL,
	"ingested_at" text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	"display_name" varchar(640) NOT NULL,
	"description" varchar(2500),
	"pronouns" varchar(200),
	"website" text,
	"avatar" text,
	"banner" text,
	"created_at" text NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "recipes" (
	"uri" text GENERATED ALWAYS AS ('at://' || "recipes"."author_did" || '/blue.recipes.feed.recipe/' || "recipes"."rkey") STORED,
	"cid" text NOT NULL,
	"author_did" text NOT NULL,
	"rkey" text NOT NULL,
	"image" text,
	"title" text NOT NULL,
	"time" integer DEFAULT 0 NOT NULL,
	"serves" integer,
	"description" text,
	"ingredients" jsonb NOT NULL,
	"ingredients_count" integer GENERATED ALWAYS AS (jsonb_array_length("recipes"."ingredients")) STORED,
	"steps" jsonb NOT NULL,
	"steps_count" integer GENERATED ALWAYS AS (jsonb_array_length("recipes"."steps")) STORED,
	"created_at" text NOT NULL,
	"ingested_at" text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CONSTRAINT "recipes_author_did_rkey_pk" PRIMARY KEY("author_did","rkey")
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "recipes" ADD CONSTRAINT "recipes_author_did_profiles_did_fk" FOREIGN KEY ("author_did") REFERENCES "public"."profiles"("did") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "profiles_cid_idx" ON "profiles" USING btree ("cid");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "profiles_cat_idx" ON "profiles" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "profiles_iat_idx" ON "profiles" USING btree ("ingested_at");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "recipes_title_idx" ON "recipes" USING btree ("title");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "recipes_cid_idx" ON "recipes" USING btree ("cid");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "recipes_cat_idx" ON "recipes" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "recipes_iat_idx" ON "recipes" USING btree ("ingested_at");