CREATE TABLE "absence_day" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"date" date NOT NULL,
	"reason" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "blocker" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"description" text,
	"area_id" uuid,
	"project_id" uuid,
	"owner_user_id" uuid,
	"opened_by" uuid NOT NULL,
	"opened_at" timestamp with time zone DEFAULT now() NOT NULL,
	"resolved_by" uuid,
	"resolved_at" timestamp with time zone,
	"resolution_text" text
);
--> statement-breakpoint
CREATE TABLE "day_entry" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"date" date NOT NULL,
	"status" text NOT NULL,
	"checked_in_at" timestamp with time zone,
	"reflected_at" timestamp with time zone,
	"reflection_text" text,
	"ampel" text,
	"trend" text,
	"url" text,
	"blocker_id" uuid,
	"edit_locked_at" timestamp with time zone,
	"published_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "todo" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"day_entry_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"date" date NOT NULL,
	"title" text NOT NULL,
	"order" integer DEFAULT 0 NOT NULL,
	"bezug_kind" text,
	"bezug_id" uuid,
	"status" text DEFAULT 'open' NOT NULL,
	"drop_reason" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"completed_at" timestamp with time zone,
	"dropped_at" timestamp with time zone
);
--> statement-breakpoint
ALTER TABLE "absence_day" ADD CONSTRAINT "absence_day_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "blocker" ADD CONSTRAINT "blocker_owner_user_id_user_id_fk" FOREIGN KEY ("owner_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "blocker" ADD CONSTRAINT "blocker_opened_by_user_id_fk" FOREIGN KEY ("opened_by") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "blocker" ADD CONSTRAINT "blocker_resolved_by_user_id_fk" FOREIGN KEY ("resolved_by") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "day_entry" ADD CONSTRAINT "day_entry_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "todo" ADD CONSTRAINT "todo_day_entry_id_day_entry_id_fk" FOREIGN KEY ("day_entry_id") REFERENCES "public"."day_entry"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "todo" ADD CONSTRAINT "todo_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "uniq_absence_user_date" ON "absence_day" USING btree ("user_id","date");--> statement-breakpoint
CREATE INDEX "idx_blocker_area" ON "blocker" USING btree ("area_id");--> statement-breakpoint
CREATE INDEX "idx_blocker_project" ON "blocker" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "idx_blocker_opened_at" ON "blocker" USING btree ("opened_at");--> statement-breakpoint
CREATE UNIQUE INDEX "uniq_day_entry_user_date" ON "day_entry" USING btree ("user_id","date");--> statement-breakpoint
CREATE INDEX "idx_day_entry_date" ON "day_entry" USING btree ("date");--> statement-breakpoint
CREATE INDEX "idx_day_entry_published_at" ON "day_entry" USING btree ("published_at");--> statement-breakpoint
CREATE INDEX "idx_todo_day_entry_id" ON "todo" USING btree ("day_entry_id");--> statement-breakpoint
CREATE INDEX "idx_todo_user_date" ON "todo" USING btree ("user_id","date");