CREATE TABLE "download_files" (
  "id" uuid NOT NULL DEFAULT gen_random_uuid(),
  "app_key" TEXT NOT NULL,
  "context_key" TEXT NOT NULL DEFAULT 'general',
  "title" TEXT NOT NULL,
  "description" TEXT,
  "file_name" TEXT NOT NULL,
  "source_url" TEXT NOT NULL,
  "content_type" TEXT,
  "file_size_bytes" BIGINT,
  "uploaded_by_app_key" TEXT,
  "uploaded_by_auth_id" uuid,
  "sort_order" INTEGER NOT NULL DEFAULT 0,
  "is_active" BOOLEAN NOT NULL DEFAULT true,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP(3) NOT NULL,
  CONSTRAINT "download_files_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "download_events" (
  "id" uuid NOT NULL DEFAULT gen_random_uuid(),
  "file_id" uuid NOT NULL,
  "auth_user_id" uuid,
  "app_key" TEXT NOT NULL,
  "user_agent" TEXT,
  "ip_address" TEXT,
  "downloaded_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "download_events_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "download_files_app_key_context_key_is_active_sort_order_idx" ON "download_files"("app_key", "context_key", "is_active", "sort_order");
CREATE INDEX "download_files_uploaded_by_app_key_idx" ON "download_files"("uploaded_by_app_key");
CREATE INDEX "download_events_file_id_downloaded_at_idx" ON "download_events"("file_id", "downloaded_at");
CREATE INDEX "download_events_auth_user_id_downloaded_at_idx" ON "download_events"("auth_user_id", "downloaded_at");
CREATE INDEX "download_events_app_key_downloaded_at_idx" ON "download_events"("app_key", "downloaded_at");

ALTER TABLE "download_events"
  ADD CONSTRAINT "download_events_file_id_fkey"
  FOREIGN KEY ("file_id") REFERENCES "download_files"("id") ON DELETE CASCADE ON UPDATE CASCADE;

COMMENT ON TABLE "download_files" IS '스튜디오 앱들이 공통 다운로드 API로 제공하는 파일 메타데이터';
COMMENT ON COLUMN "download_files"."app_key" IS '파일이 노출될 앱 구분 키';
COMMENT ON COLUMN "download_files"."context_key" IS '앱 내부 화면 또는 영역 구분 키';
COMMENT ON COLUMN "download_files"."title" IS '사용자에게 표시할 파일 제목';
COMMENT ON COLUMN "download_files"."description" IS '파일 설명';
COMMENT ON COLUMN "download_files"."file_name" IS '다운로드 시 사용할 파일명';
COMMENT ON COLUMN "download_files"."source_url" IS '실제 원본 파일 URL';
COMMENT ON COLUMN "download_files"."content_type" IS '다운로드 응답에 사용할 MIME 타입';
COMMENT ON COLUMN "download_files"."file_size_bytes" IS '파일 크기 바이트';
COMMENT ON COLUMN "download_files"."uploaded_by_app_key" IS '파일을 등록한 앱 구분 키';
COMMENT ON COLUMN "download_files"."uploaded_by_auth_id" IS '파일을 등록한 중앙 auth 사용자 ID';
COMMENT ON COLUMN "download_files"."sort_order" IS '목록 표시 순서';
COMMENT ON COLUMN "download_files"."is_active" IS '다운로드 노출 여부';

COMMENT ON TABLE "download_events" IS '공통 다운로드 API를 통한 파일 다운로드 이력';
COMMENT ON COLUMN "download_events"."file_id" IS '다운로드한 파일 ID';
COMMENT ON COLUMN "download_events"."auth_user_id" IS '다운로드한 중앙 auth 사용자 ID';
COMMENT ON COLUMN "download_events"."app_key" IS '다운로드가 발생한 앱 구분 키';
COMMENT ON COLUMN "download_events"."user_agent" IS '요청 User-Agent';
COMMENT ON COLUMN "download_events"."ip_address" IS '요청 IP 주소';
COMMENT ON COLUMN "download_events"."downloaded_at" IS '다운로드 발생 시각';