CREATE TABLE "atproto_session" (
	"sub" text PRIMARY KEY NOT NULL,
	"value_ciphertext" "bytea" NOT NULL,
	"value_nonce" "bytea" NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "atproto_state" (
	"key" text PRIMARY KEY NOT NULL,
	"value_ciphertext" "bytea" NOT NULL,
	"value_nonce" "bytea" NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "installation" (
	"id" bigint PRIMARY KEY NOT NULL,
	"account_login" text NOT NULL,
	"account_id" bigint NOT NULL,
	"account_type" text NOT NULL,
	"suspended_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "installation_account_type_chk" CHECK ("installation"."account_type" in ('User','Organization'))
);
--> statement-breakpoint
CREATE TABLE "job" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"kind" text NOT NULL,
	"payload" jsonb NOT NULL,
	"attempts" integer DEFAULT 0 NOT NULL,
	"run_after" timestamp with time zone DEFAULT now() NOT NULL,
	"locked_by" text,
	"locked_until" timestamp with time zone,
	"status" text DEFAULT 'queued' NOT NULL,
	"last_error" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone,
	CONSTRAINT "job_status_chk" CHECK ("job"."status" in ('queued','running','done','failed'))
);
--> statement-breakpoint
CREATE TABLE "repo_mapping" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"installation_id" bigint NOT NULL,
	"github_repo_id" bigint NOT NULL,
	"github_full_name" text NOT NULL,
	"tangled_repo_did" text,
	"tangled_full_name" text,
	"knot" text,
	"primary_side" text DEFAULT 'github' NOT NULL,
	"last_synced_refs" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"status" text DEFAULT 'pending' NOT NULL,
	"last_error" text,
	"disabled_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone,
	CONSTRAINT "repo_mapping_installation_repo_unq" UNIQUE("installation_id","github_repo_id"),
	CONSTRAINT "repo_mapping_primary_side_chk" CHECK ("repo_mapping"."primary_side" in ('github','tangled')),
	CONSTRAINT "repo_mapping_status_chk" CHECK ("repo_mapping"."status" in ('pending','enrolling','active','error'))
);
--> statement-breakpoint
CREATE TABLE "ssh_key" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"installation_id" bigint NOT NULL,
	"did" text NOT NULL,
	"public_key" text NOT NULL,
	"private_key_ciphertext" "bytea" NOT NULL,
	"private_key_nonce" "bytea" NOT NULL,
	"tangled_key_rkey" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"rotated_at" timestamp with time zone,
	CONSTRAINT "ssh_key_installation_did_unq" UNIQUE("installation_id","did")
);
--> statement-breakpoint
CREATE TABLE "user_identity" (
	"did" text PRIMARY KEY NOT NULL,
	"handle" text,
	"installation_id" bigint,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "webhook_event" (
	"delivery_id" uuid PRIMARY KEY NOT NULL,
	"source" text NOT NULL,
	"event" text NOT NULL,
	"received_at" timestamp with time zone DEFAULT now() NOT NULL,
	"processed_at" timestamp with time zone,
	CONSTRAINT "webhook_event_source_chk" CHECK ("webhook_event"."source" in ('github','tangled'))
);
--> statement-breakpoint
ALTER TABLE "repo_mapping" ADD CONSTRAINT "repo_mapping_installation_id_installation_id_fk" FOREIGN KEY ("installation_id") REFERENCES "public"."installation"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ssh_key" ADD CONSTRAINT "ssh_key_installation_id_installation_id_fk" FOREIGN KEY ("installation_id") REFERENCES "public"."installation"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "user_identity" ADD CONSTRAINT "user_identity_installation_id_installation_id_fk" FOREIGN KEY ("installation_id") REFERENCES "public"."installation"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "job_status_run_after_idx" ON "job" USING btree ("status","run_after");--> statement-breakpoint
CREATE INDEX "repo_mapping_installation_idx" ON "repo_mapping" USING btree ("installation_id");--> statement-breakpoint
CREATE INDEX "ssh_key_installation_idx" ON "ssh_key" USING btree ("installation_id");--> statement-breakpoint
CREATE INDEX "user_identity_installation_idx" ON "user_identity" USING btree ("installation_id");