CREATE TYPE "public"."card_assignment_status" AS ENUM('ASSIGNED', 'RETURNED');--> statement-breakpoint CREATE TYPE "public"."device_type" AS ENUM('RFID_SCANNER', 'LOCK_SYSTEM');--> statement-breakpoint CREATE TYPE "public"."management_user_role" AS ENUM('ADMIN');--> statement-breakpoint CREATE TYPE "public"."rfid_card_status" AS ENUM('NEW', 'ENGRAVED', 'LOST', 'DISPOSED');--> statement-breakpoint CREATE TYPE "public"."system_log_type" AS ENUM('ACTION', 'INFO', 'WARNING', 'ERROR');--> statement-breakpoint CREATE TABLE "access_logs" ( "member_id" uuid NOT NULL, "device_id" uuid NOT NULL, "accessed_at" timestamp NOT NULL, "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "devices" ( "name" varchar(200) NOT NULL, "api_key" varchar(255) NOT NULL, "type" "device_type" DEFAULT 'RFID_SCANNER' NOT NULL, "last_seen_at" timestamp, "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL, CONSTRAINT "devices_api_key_unique" UNIQUE("api_key") ); --> statement-breakpoint CREATE TABLE "management_users" ( "first_name" varchar(100) NOT NULL, "last_name" varchar(100) NOT NULL, "username" varchar(50) NOT NULL, "password_hash" varchar(255) NOT NULL, "role" "management_user_role" DEFAULT 'ADMIN' NOT NULL, "enabled" boolean DEFAULT true NOT NULL, "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL, CONSTRAINT "management_users_username_unique" UNIQUE("username") ); --> statement-breakpoint CREATE TABLE "member_rfid_cards" ( "member_id" uuid NOT NULL, "card_id" uuid NOT NULL, "status" "card_assignment_status" DEFAULT 'ASSIGNED' NOT NULL, "issued_at" timestamp, "returned_at" timestamp, "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "members" ( "first_name" varchar(100) NOT NULL, "last_name" varchar(100) NOT NULL, "title" varchar(20), "birth_date" timestamp, "membership_number" varchar(50), "occupation" varchar(200), "street" varchar(200), "house_number" varchar(10), "postal_code" varchar(10), "city" varchar(100), "phone_home" varchar(20), "phone_work" varchar(20), "phone_mobile" varchar(20), "email" varchar(255), "guest_account" boolean DEFAULT false NOT NULL, "joined_at" timestamp, "free_text_function" text, "free_text_comment" text, "password_hash" varchar(255), "profile_image" varchar(500), "allow_self_service" boolean DEFAULT false NOT NULL, "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL, CONSTRAINT "members_membership_number_unique" UNIQUE("membership_number") ); --> statement-breakpoint CREATE TABLE "rfid_cards" ( "rfid_id" varchar(50) NOT NULL, "status" "rfid_card_status" DEFAULT 'NEW' NOT NULL, "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL, CONSTRAINT "rfid_cards_rfid_id_unique" UNIQUE("rfid_id") ); --> statement-breakpoint CREATE TABLE "system_logs" ( "type" "system_log_type" NOT NULL, "name" varchar(255), "user_id" uuid, "payload" text, "created_at" timestamp DEFAULT now() NOT NULL ); --> statement-breakpoint ALTER TABLE "access_logs" ADD CONSTRAINT "access_logs_member_id_members_id_fk" FOREIGN KEY ("member_id") REFERENCES "public"."members"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint ALTER TABLE "access_logs" ADD CONSTRAINT "access_logs_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint ALTER TABLE "member_rfid_cards" ADD CONSTRAINT "member_rfid_cards_member_id_members_id_fk" FOREIGN KEY ("member_id") REFERENCES "public"."members"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint ALTER TABLE "member_rfid_cards" ADD CONSTRAINT "member_rfid_cards_card_id_rfid_cards_id_fk" FOREIGN KEY ("card_id") REFERENCES "public"."rfid_cards"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint ALTER TABLE "system_logs" ADD CONSTRAINT "system_logs_user_id_management_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."management_users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint CREATE INDEX "access_logs_member_id_idx" ON "access_logs" USING btree ("member_id");--> statement-breakpoint CREATE INDEX "access_logs_device_id_idx" ON "access_logs" USING btree ("device_id");--> statement-breakpoint CREATE INDEX "access_logs_accessed_at_idx" ON "access_logs" USING btree ("accessed_at");--> statement-breakpoint CREATE UNIQUE INDEX "devices_api_key_idx" ON "devices" USING btree ("api_key");--> statement-breakpoint CREATE INDEX "devices_type_idx" ON "devices" USING btree ("type");--> statement-breakpoint CREATE INDEX "devices_name_idx" ON "devices" USING btree ("name");--> statement-breakpoint CREATE UNIQUE INDEX "management_users_username_idx" ON "management_users" USING btree ("username");--> statement-breakpoint CREATE INDEX "management_users_enabled_idx" ON "management_users" USING btree ("enabled");--> statement-breakpoint CREATE INDEX "member_rfid_cards_member_id_idx" ON "member_rfid_cards" USING btree ("member_id");--> statement-breakpoint CREATE INDEX "member_rfid_cards_card_id_idx" ON "member_rfid_cards" USING btree ("card_id");--> statement-breakpoint CREATE INDEX "member_rfid_cards_status_idx" ON "member_rfid_cards" USING btree ("status");--> statement-breakpoint CREATE INDEX "member_rfid_cards_member_card_status_idx" ON "member_rfid_cards" USING btree ("member_id","card_id","status");--> statement-breakpoint CREATE UNIQUE INDEX "members_membership_number_idx" ON "members" USING btree ("membership_number");--> statement-breakpoint CREATE INDEX "members_email_idx" ON "members" USING btree ("email");--> statement-breakpoint CREATE INDEX "members_guest_account_idx" ON "members" USING btree ("guest_account");--> statement-breakpoint CREATE INDEX "members_name_idx" ON "members" USING btree ("first_name","last_name");--> statement-breakpoint CREATE UNIQUE INDEX "rfid_cards_rfid_id_idx" ON "rfid_cards" USING btree ("rfid_id");--> statement-breakpoint CREATE INDEX "rfid_cards_status_idx" ON "rfid_cards" USING btree ("status");--> statement-breakpoint CREATE INDEX "system_logs_type_idx" ON "system_logs" USING btree ("type");--> statement-breakpoint CREATE INDEX "system_logs_user_id_idx" ON "system_logs" USING btree ("user_id");--> statement-breakpoint CREATE INDEX "system_logs_created_at_idx" ON "system_logs" USING btree ("created_at");--> statement-breakpoint CREATE INDEX "system_logs_name_idx" ON "system_logs" USING btree ("name");