CREATE TYPE "public"."ai_knowledge_scope" AS ENUM('platform', 'organization');--> statement-breakpoint
CREATE TYPE "public"."ai_knowledge_status" AS ENUM('enabled', 'disabled');--> statement-breakpoint
CREATE TYPE "public"."elder_ai_analysis_status" AS ENUM('completed', 'failed');--> statement-breakpoint
CREATE TABLE "ai_knowledge_chunks" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"entry_id" uuid NOT NULL,
	"organization_id" uuid,
	"scope" "ai_knowledge_scope" NOT NULL,
	"chunk_index" integer NOT NULL,
	"content" text NOT NULL,
	"embedding" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"source_title" text NOT NULL,
	"source_category" text DEFAULT '' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "ai_knowledge_entries" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"scope" "ai_knowledge_scope" NOT NULL,
	"organization_id" uuid,
	"title" text NOT NULL,
	"category" text DEFAULT '' NOT NULL,
	"tags" text DEFAULT '' NOT NULL,
	"body" text NOT NULL,
	"status" "ai_knowledge_status" DEFAULT 'enabled' NOT NULL,
	"created_by_account_id" uuid,
	"updated_by_account_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "elder_ai_analyses" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"organization_id" uuid NOT NULL,
	"elder_id" uuid NOT NULL,
	"actor_account_id" uuid,
	"status" "elder_ai_analysis_status" NOT NULL,
	"data_scopes" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"result_json" jsonb,
	"citations_json" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"model_summary_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"error_category" text DEFAULT '' NOT NULL,
	"error_reason" text DEFAULT '' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "ai_knowledge_chunks" ADD CONSTRAINT "ai_knowledge_chunks_entry_id_ai_knowledge_entries_id_fk" FOREIGN KEY ("entry_id") REFERENCES "public"."ai_knowledge_entries"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ai_knowledge_chunks" ADD CONSTRAINT "ai_knowledge_chunks_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ai_knowledge_entries" ADD CONSTRAINT "ai_knowledge_entries_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ai_knowledge_entries" ADD CONSTRAINT "ai_knowledge_entries_created_by_account_id_accounts_id_fk" FOREIGN KEY ("created_by_account_id") REFERENCES "public"."accounts"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ai_knowledge_entries" ADD CONSTRAINT "ai_knowledge_entries_updated_by_account_id_accounts_id_fk" FOREIGN KEY ("updated_by_account_id") REFERENCES "public"."accounts"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "elder_ai_analyses" ADD CONSTRAINT "elder_ai_analyses_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "elder_ai_analyses" ADD CONSTRAINT "elder_ai_analyses_elder_id_elders_id_fk" FOREIGN KEY ("elder_id") REFERENCES "public"."elders"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "elder_ai_analyses" ADD CONSTRAINT "elder_ai_analyses_actor_account_id_accounts_id_fk" FOREIGN KEY ("actor_account_id") REFERENCES "public"."accounts"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "ai_knowledge_chunks_entry_lookup" ON "ai_knowledge_chunks" USING btree ("entry_id");--> statement-breakpoint
CREATE INDEX "ai_knowledge_chunks_scope_lookup" ON "ai_knowledge_chunks" USING btree ("scope","organization_id");--> statement-breakpoint
CREATE INDEX "ai_knowledge_entries_scope_lookup" ON "ai_knowledge_entries" USING btree ("scope","status");--> statement-breakpoint
CREATE INDEX "ai_knowledge_entries_organization_lookup" ON "ai_knowledge_entries" USING btree ("organization_id","status");--> statement-breakpoint
CREATE INDEX "elder_ai_analyses_elder_lookup" ON "elder_ai_analyses" USING btree ("organization_id","elder_id","created_at");--> statement-breakpoint
CREATE INDEX "elder_ai_analyses_status_lookup" ON "elder_ai_analyses" USING btree ("organization_id","status");