CREATE TYPE "storage_folder_access" AS ENUM ('view', 'edit', 'admin');

CREATE TABLE "storage_folder" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "parent_id" uuid,
  "name" varchar(255) NOT NULL,
  "path" text NOT NULL DEFAULT '/',
  "created_by" uuid REFERENCES "user"("id"),
  "is_public" boolean NOT NULL DEFAULT true,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE "storage_folder_permission" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "folder_id" uuid NOT NULL REFERENCES "storage_folder"("id") ON DELETE CASCADE,
  "role_id" uuid REFERENCES "role"("id"),
  "user_id" uuid REFERENCES "user"("id"),
  "access_level" "storage_folder_access" NOT NULL DEFAULT 'view',
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE "storage_file" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "folder_id" uuid NOT NULL REFERENCES "storage_folder"("id") ON DELETE CASCADE,
  "filename" varchar(255) NOT NULL,
  "path" varchar(1000) NOT NULL,
  "content_type" varchar(100) NOT NULL,
  "size_bytes" integer NOT NULL,
  "uploaded_by" uuid REFERENCES "user"("id"),
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);
