CREATE TYPE "public"."status" AS ENUM('no iniciada', 'en proceso', 'completada', 'revisada', 'cancelada', 'reabierta');--> statement-breakpoint
CREATE TYPE "public"."eventType" AS ENUM('crear', 'modificar', 'eliminar');--> statement-breakpoint
CREATE TYPE "public"."type" AS ENUM('apartamento', 'casa', 'otro');--> statement-breakpoint
CREATE TYPE "public"."typeCotization" AS ENUM('pa', 'metro', 'otro');--> statement-breakpoint
CREATE TYPE "public"."priority" AS ENUM('baja', 'media', 'alta');--> statement-breakpoint
CREATE TYPE "public"."userRols" AS ENUM('admin', 'moderator', 'user');--> statement-breakpoint
CREATE TABLE "areas" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"type" varchar(250) NOT NULL,
	"length" real NOT NULL,
	"width" real NOT NULL,
	"quantity" integer DEFAULT 1 NOT NULL,
	"project_id" uuid NOT NULL,
	"project_name" varchar(250) NOT NULL,
	"floorId" uuid NOT NULL,
	"status" "status" DEFAULT 'no iniciada' NOT NULL
);
--> statement-breakpoint
CREATE TABLE "eventsLog" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"eventType" "eventType" NOT NULL,
	"userId" varchar(255),
	"newValues" text NOT NULL,
	"modifiedItem" text NOT NULL,
	"tableName" varchar(80) NOT NULL,
	"createdAt" timestamp DEFAULT now()
);
--> statement-breakpoint
CREATE TABLE "floor" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" varchar(255) NOT NULL,
	"project_name" varchar(255) NOT NULL,
	"project_id" uuid,
	CONSTRAINT "floor_name_project_id_unique" UNIQUE("name","project_id")
);
--> statement-breakpoint
CREATE TABLE "materials" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" varchar(250) NOT NULL,
	"price" integer NOT NULL,
	"brand" varchar(255),
	CONSTRAINT "materials_name_unique" UNIQUE("name")
);
--> statement-breakpoint
CREATE TABLE "project" (
	"id" uuid PRIMARY KEY NOT NULL,
	"name" varchar(250) NOT NULL,
	"residence" "type" NOT NULL,
	"costPerMeter" bigint DEFAULT 0 NOT NULL,
	"totalCostPerMeter" bigint DEFAULT 0,
	"totalCostMaterials" bigint DEFAULT 0,
	"laborCost" bigint DEFAULT 0 NOT NULL,
	"startDate" timestamp DEFAULT now() NOT NULL,
	"typeCotization" "typeCotization" NOT NULL,
	"estimadedEndDate" timestamp DEFAULT now() NOT NULL,
	"status" "status" DEFAULT 'no iniciada' NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now()
);
--> statement-breakpoint
CREATE TABLE "task" (
	"id" uuid PRIMARY KEY NOT NULL,
	"comments" varchar(255) NOT NULL,
	"status" "status" DEFAULT 'no iniciada' NOT NULL,
	"user_id" varchar(255),
	"start_date" date NOT NULL,
	"expire_date" date,
	"started_time" timestamp,
	"ended_time" timestamp,
	"project_id" uuid NOT NULL,
	"area_id" uuid NOT NULL,
	"area_name" varchar(255),
	"floor_name" varchar(255),
	"project_name" varchar(255),
	"priority" "priority" NOT NULL,
	"created_At" timestamp DEFAULT now(),
	"updated_At" timestamp DEFAULT now()
);
--> statement-breakpoint
CREATE TABLE "user" (
	"id" varchar(255) PRIMARY KEY NOT NULL,
	"firstName" varchar(255) NOT NULL,
	"lastName" varchar(255),
	"email" varchar(255),
	"createdAt" timestamp DEFAULT now() NOT NULL,
	"updatedAt" timestamp DEFAULT now() NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"rol" "userRols" DEFAULT 'moderator' NOT NULL,
	"is_deleted" boolean DEFAULT false NOT NULL,
	CONSTRAINT "user_email_unique" UNIQUE("email")
);
--> statement-breakpoint
CREATE TABLE "projectMaterials" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"project_name" varchar(255) NOT NULL,
	"project_id" uuid NOT NULL,
	"material_id" uuid NOT NULL,
	"required_quantity" integer NOT NULL,
	"used_quantity" integer DEFAULT 0 NOT NULL,
	"available_quantity" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "projectMaterials_material_id_project_id_unique" UNIQUE("material_id","project_id")
);
--> statement-breakpoint
CREATE TABLE "tool" (
	"id" uuid PRIMARY KEY NOT NULL,
	"name" varchar(255) NOT NULL,
	"quantity" integer DEFAULT 1 NOT NULL,
	"createdAt" timestamp DEFAULT now() NOT NULL,
	"updatedAt" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "userTool" (
	"id" uuid PRIMARY KEY NOT NULL,
	"toolId" uuid NOT NULL,
	"userId" varchar(255) NOT NULL,
	"tool_name" varchar(255) NOT NULL,
	"quantity" integer DEFAULT 1 NOT NULL,
	"createdAt" timestamp DEFAULT now() NOT NULL,
	"updatedAt" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "userTool_toolId_userId_unique" UNIQUE("toolId","userId")
);
--> statement-breakpoint
ALTER TABLE "areas" ADD CONSTRAINT "areas_project_id_project_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."project"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "areas" ADD CONSTRAINT "areas_floorId_floor_id_fk" FOREIGN KEY ("floorId") REFERENCES "public"."floor"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
ALTER TABLE "eventsLog" ADD CONSTRAINT "eventsLog_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
ALTER TABLE "floor" ADD CONSTRAINT "floor_project_id_project_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."project"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
ALTER TABLE "task" ADD CONSTRAINT "task_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
ALTER TABLE "task" ADD CONSTRAINT "task_project_id_project_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."project"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
ALTER TABLE "task" ADD CONSTRAINT "task_area_id_areas_id_fk" FOREIGN KEY ("area_id") REFERENCES "public"."areas"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
ALTER TABLE "projectMaterials" ADD CONSTRAINT "projectMaterials_project_id_project_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."project"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
ALTER TABLE "projectMaterials" ADD CONSTRAINT "projectMaterials_material_id_materials_id_fk" FOREIGN KEY ("material_id") REFERENCES "public"."materials"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
ALTER TABLE "userTool" ADD CONSTRAINT "userTool_toolId_tool_id_fk" FOREIGN KEY ("toolId") REFERENCES "public"."tool"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
ALTER TABLE "userTool" ADD CONSTRAINT "userTool_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "areas_floor" ON "areas" USING btree ("floorId");--> statement-breakpoint
CREATE INDEX "floor_project" ON "floor" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "project_name" ON "project" USING btree ("name");--> statement-breakpoint
CREATE INDEX "startDate" ON "project" USING btree ("startDate");--> statement-breakpoint
CREATE INDEX "project_status" ON "project" USING btree ("status");--> statement-breakpoint
CREATE INDEX "task_project" ON "task" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "task_area" ON "task" USING btree ("area_id");--> statement-breakpoint
CREATE INDEX "task_startDate" ON "task" USING btree ("start_date");--> statement-breakpoint
CREATE INDEX "task_endDate" ON "task" USING btree ("expire_date");--> statement-breakpoint
CREATE INDEX "task_status" ON "task" USING btree ("status");--> statement-breakpoint
CREATE INDEX "task_user" ON "task" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "projectMaterials_project" ON "projectMaterials" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "material_id" ON "projectMaterials" USING btree ("material_id");--> statement-breakpoint
CREATE INDEX "userTool_tool" ON "userTool" USING btree ("toolId");--> statement-breakpoint
CREATE INDEX "userTool_user" ON "userTool" USING btree ("userId");