-- File storage table
CREATE TABLE IF NOT EXISTS "file" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "company_id" uuid NOT NULL REFERENCES "company"("id"),
  "path" varchar(1000) NOT NULL,
  "filename" varchar(255) NOT NULL,
  "content_type" varchar(100) NOT NULL,
  "size_bytes" integer NOT NULL,
  "entity_type" varchar(100) NOT NULL,
  "entity_id" uuid NOT NULL,
  "category" varchar(100) NOT NULL,
  "uploaded_by" uuid,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX "file_company_path" ON "file" ("company_id", "path");
CREATE INDEX "file_entity" ON "file" ("company_id", "entity_type", "entity_id");

-- Update member_identifier: replace base64 columns with file references
ALTER TABLE "member_identifier" DROP COLUMN IF EXISTS "image_front";
ALTER TABLE "member_identifier" DROP COLUMN IF EXISTS "image_back";
ALTER TABLE "member_identifier" DROP COLUMN IF EXISTS "image_front_url";
ALTER TABLE "member_identifier" DROP COLUMN IF EXISTS "image_back_url";
ALTER TABLE "member_identifier" ADD COLUMN "image_front_file_id" uuid REFERENCES "file"("id");
ALTER TABLE "member_identifier" ADD COLUMN "image_back_file_id" uuid REFERENCES "file"("id");
