CREATE TABLE "checklist_items" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"checklist_id" uuid NOT NULL,
	"text" text NOT NULL,
	"item_type" text DEFAULT 'checkbox' NOT NULL,
	"unit" text,
	"checked" boolean DEFAULT false NOT NULL,
	"value" text,
	"sort_order" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "checklist_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"description" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "component_documents" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"component_id" uuid NOT NULL,
	"file_path" text NOT NULL,
	"original_filename" text NOT NULL,
	"file_type" text,
	"description" text,
	"uploaded_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "component_images" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"component_id" uuid NOT NULL,
	"file_path" text NOT NULL,
	"thumbnail_path" text,
	"caption" text,
	"uploaded_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "component_instances" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"component_id" uuid NOT NULL,
	"instance_number" integer DEFAULT 1 NOT NULL,
	"serial_number" text,
	"condition" text DEFAULT 'Working' NOT NULL,
	"firmware_version" text,
	"notes" text,
	"current_device_id" uuid,
	"location_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "instances_condition_check" CHECK ("component_instances"."condition" IN ('Working', 'Faulty', 'Unknown', 'Refurbished'))
);
--> statement-breakpoint
CREATE TABLE "components" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"component_type" text NOT NULL,
	"brand" text,
	"part_number" text,
	"specs" text,
	"notes" text,
	"default_condition" text DEFAULT 'Working' NOT NULL,
	"default_firmware_version" text,
	"default_location_id" uuid,
	"disabled" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "computer_details" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"device_id" uuid NOT NULL,
	"os_version" text,
	"firmware_version" text,
	"installed_software" text,
	CONSTRAINT "computer_details_device_id_unique" UNIQUE("device_id")
);
--> statement-breakpoint
CREATE TABLE "device_checklists" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"device_id" uuid NOT NULL,
	"title" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "device_documents" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"device_id" uuid NOT NULL,
	"file_path" text NOT NULL,
	"original_filename" text NOT NULL,
	"file_type" text,
	"description" text,
	"uploaded_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "device_images" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"device_id" uuid NOT NULL,
	"file_path" text NOT NULL,
	"thumbnail_path" text,
	"caption" text,
	"sort_order" integer DEFAULT 0,
	"uploaded_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "device_log" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"device_id" uuid NOT NULL,
	"type" text NOT NULL,
	"description" text NOT NULL,
	"condition_after" text,
	"performed_by" text,
	"performed_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "device_log_type_check" CHECK ("device_log"."type" IN ('repair', 'inspection', 'cleaning', 'modification', 'diagnostic', 'recap', 'other'))
);
--> statement-breakpoint
CREATE TABLE "device_tags" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"device_id" uuid NOT NULL,
	"tag_uid" text,
	"payload_url" text NOT NULL,
	"written_by" text,
	"written_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "devices" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"category" text NOT NULL,
	"brand" text,
	"model" text,
	"serial_number" text,
	"year" integer,
	"condition" text DEFAULT 'Waiting to be Tested' NOT NULL,
	"voltage" text,
	"frequency" text,
	"origin" text,
	"fault_description" text,
	"repair_notes" text,
	"location_id" uuid,
	"initial_condition" text,
	"general_notes" text,
	"disabled" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "devices_category_check" CHECK ("devices"."category" IN ('Computer', 'Audio Equipment', 'Peripheral', 'Other')),
	CONSTRAINT "devices_condition_check" CHECK ("devices"."condition" IN ('Working', 'In Repair', 'Waiting for Repair', 'Waiting to be Tested', 'Unrepairable'))
);
--> statement-breakpoint
CREATE TABLE "feature_requests" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"description" text,
	"status" text DEFAULT 'open' NOT NULL,
	"votes" integer DEFAULT 0 NOT NULL,
	"created_by" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "installation_log" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"instance_id" uuid NOT NULL,
	"device_id" uuid NOT NULL,
	"action" text NOT NULL,
	"performed_by" text,
	"notes" text,
	"performed_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "install_log_action_check" CHECK ("installation_log"."action" IN ('installed', 'removed', 'swapped'))
);
--> statement-breakpoint
CREATE TABLE "locations" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" text NOT NULL,
	"description" text,
	"parent_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 "parts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" text,
	"category" text NOT NULL,
	"mpn" text,
	"value" text,
	"voltage" text,
	"tolerance" text,
	"power" text,
	"mounting" text,
	"package" text,
	"quantity" integer DEFAULT 0 NOT NULL,
	"unit" text DEFAULT 'pcs' NOT NULL,
	"location_id" uuid,
	"notes" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "sessions" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text NOT NULL,
	"expires_at" timestamp with time zone NOT NULL
);
--> statement-breakpoint
CREATE TABLE "template_items" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"template_id" uuid NOT NULL,
	"text" text NOT NULL,
	"item_type" text DEFAULT 'checkbox' NOT NULL,
	"unit" text,
	"sort_order" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "todos" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"description" text,
	"status" text DEFAULT 'todo' NOT NULL,
	"priority" integer DEFAULT 2 NOT NULL,
	"device_id" uuid,
	"due_date" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "todos_status_check" CHECK ("todos"."status" IN ('todo', 'in_progress', 'done')),
	CONSTRAINT "todos_priority_check" CHECK ("todos"."priority" IN (0, 1, 2, 3))
);
--> statement-breakpoint
CREATE TABLE "users" (
	"id" text PRIMARY KEY NOT NULL,
	"email" text NOT NULL,
	"display_name" text,
	"password_hash" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "users_email_unique" UNIQUE("email")
);
--> statement-breakpoint
CREATE TABLE "wiki_categories" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" text NOT NULL,
	"slug" text NOT NULL,
	"description" text,
	"sort_order" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "wiki_categories_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "wiki_page_tags" (
	"page_id" uuid NOT NULL,
	"tag_id" uuid NOT NULL
);
--> statement-breakpoint
CREATE TABLE "wiki_pages" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"slug" text NOT NULL,
	"content" text NOT NULL,
	"category_id" uuid,
	"created_by" text,
	"updated_by" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "wiki_pages_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "wiki_tags" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" text NOT NULL,
	CONSTRAINT "wiki_tags_name_unique" UNIQUE("name")
);
--> statement-breakpoint
ALTER TABLE "checklist_items" ADD CONSTRAINT "checklist_items_checklist_id_device_checklists_id_fk" FOREIGN KEY ("checklist_id") REFERENCES "public"."device_checklists"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "component_documents" ADD CONSTRAINT "component_documents_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "component_images" ADD CONSTRAINT "component_images_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "component_instances" ADD CONSTRAINT "component_instances_component_id_components_id_fk" FOREIGN KEY ("component_id") REFERENCES "public"."components"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "component_instances" ADD CONSTRAINT "component_instances_current_device_id_devices_id_fk" FOREIGN KEY ("current_device_id") REFERENCES "public"."devices"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "component_instances" ADD CONSTRAINT "component_instances_location_id_locations_id_fk" FOREIGN KEY ("location_id") REFERENCES "public"."locations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "components" ADD CONSTRAINT "components_default_location_id_locations_id_fk" FOREIGN KEY ("default_location_id") REFERENCES "public"."locations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "computer_details" ADD CONSTRAINT "computer_details_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "device_checklists" ADD CONSTRAINT "device_checklists_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "device_documents" ADD CONSTRAINT "device_documents_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "device_images" ADD CONSTRAINT "device_images_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "device_log" ADD CONSTRAINT "device_log_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "device_tags" ADD CONSTRAINT "device_tags_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "devices" ADD CONSTRAINT "devices_location_id_locations_id_fk" FOREIGN KEY ("location_id") REFERENCES "public"."locations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "installation_log" ADD CONSTRAINT "installation_log_instance_id_component_instances_id_fk" FOREIGN KEY ("instance_id") REFERENCES "public"."component_instances"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "installation_log" ADD CONSTRAINT "installation_log_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "locations" ADD CONSTRAINT "locations_parent_id_locations_id_fk" FOREIGN KEY ("parent_id") REFERENCES "public"."locations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "parts" ADD CONSTRAINT "parts_location_id_locations_id_fk" FOREIGN KEY ("location_id") REFERENCES "public"."locations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "template_items" ADD CONSTRAINT "template_items_template_id_checklist_templates_id_fk" FOREIGN KEY ("template_id") REFERENCES "public"."checklist_templates"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "todos" ADD CONSTRAINT "todos_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "wiki_page_tags" ADD CONSTRAINT "wiki_page_tags_page_id_wiki_pages_id_fk" FOREIGN KEY ("page_id") REFERENCES "public"."wiki_pages"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "wiki_page_tags" ADD CONSTRAINT "wiki_page_tags_tag_id_wiki_tags_id_fk" FOREIGN KEY ("tag_id") REFERENCES "public"."wiki_tags"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "wiki_pages" ADD CONSTRAINT "wiki_pages_category_id_wiki_categories_id_fk" FOREIGN KEY ("category_id") REFERENCES "public"."wiki_categories"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "checklist_items_checklist_idx" ON "checklist_items" USING btree ("checklist_id");--> statement-breakpoint
CREATE INDEX "component_documents_component_idx" ON "component_documents" USING btree ("component_id");--> statement-breakpoint
CREATE INDEX "component_images_component_idx" ON "component_images" USING btree ("component_id");--> statement-breakpoint
CREATE INDEX "instances_component_idx" ON "component_instances" USING btree ("component_id");--> statement-breakpoint
CREATE INDEX "instances_device_idx" ON "component_instances" USING btree ("current_device_id");--> statement-breakpoint
CREATE INDEX "instances_location_idx" ON "component_instances" USING btree ("location_id");--> statement-breakpoint
CREATE INDEX "components_type_idx" ON "components" USING btree ("component_type");--> statement-breakpoint
CREATE INDEX "device_checklists_device_idx" ON "device_checklists" USING btree ("device_id");--> statement-breakpoint
CREATE INDEX "device_documents_device_idx" ON "device_documents" USING btree ("device_id");--> statement-breakpoint
CREATE INDEX "device_images_device_idx" ON "device_images" USING btree ("device_id");--> statement-breakpoint
CREATE INDEX "device_log_device_idx" ON "device_log" USING btree ("device_id");--> statement-breakpoint
CREATE INDEX "device_log_date_idx" ON "device_log" USING btree ("performed_at");--> statement-breakpoint
CREATE INDEX "device_tags_device_idx" ON "device_tags" USING btree ("device_id");--> statement-breakpoint
CREATE INDEX "device_tags_written_at_idx" ON "device_tags" USING btree ("written_at");--> statement-breakpoint
CREATE INDEX "devices_category_idx" ON "devices" USING btree ("category");--> statement-breakpoint
CREATE INDEX "devices_condition_idx" ON "devices" USING btree ("condition");--> statement-breakpoint
CREATE INDEX "devices_location_idx" ON "devices" USING btree ("location_id");--> statement-breakpoint
CREATE INDEX "install_log_instance_idx" ON "installation_log" USING btree ("instance_id");--> statement-breakpoint
CREATE INDEX "install_log_device_idx" ON "installation_log" USING btree ("device_id");--> statement-breakpoint
CREATE INDEX "install_log_date_idx" ON "installation_log" USING btree ("performed_at");--> statement-breakpoint
CREATE INDEX "parts_category_idx" ON "parts" USING btree ("category");--> statement-breakpoint
CREATE INDEX "parts_location_idx" ON "parts" USING btree ("location_id");--> statement-breakpoint
CREATE INDEX "template_items_template_idx" ON "template_items" USING btree ("template_id");--> statement-breakpoint
CREATE INDEX "todos_status_idx" ON "todos" USING btree ("status");--> statement-breakpoint
CREATE INDEX "todos_priority_idx" ON "todos" USING btree ("priority");--> statement-breakpoint
CREATE INDEX "todos_device_idx" ON "todos" USING btree ("device_id");--> statement-breakpoint
CREATE INDEX "wiki_page_tags_page_idx" ON "wiki_page_tags" USING btree ("page_id");--> statement-breakpoint
CREATE INDEX "wiki_page_tags_tag_idx" ON "wiki_page_tags" USING btree ("tag_id");--> statement-breakpoint
CREATE INDEX "wiki_pages_category_idx" ON "wiki_pages" USING btree ("category_id");--> statement-breakpoint
CREATE INDEX "wiki_pages_slug_idx" ON "wiki_pages" USING btree ("slug");