(대표님) archiver 추가, 데이터룸구현

2 files changed, 987 insertions, 0 deletions

diff --git a/lib/services/fileService.ts b/lib/services/fileService.ts
new file mode 100644
index 00000000..56966a86
--- /dev/null
+++ b/lib/services/fileService.ts
@@ -0,0 +1,516 @@
+// lib/services/fileService.ts
+import db from "@/db/db";
+import {
+  fileItems,
+  filePermissions,
+  fileShares,
+  fileActivityLogs,
+  projects,
+  type FileItem,
+  type NewFileItem,
+  type FilePermission,
+} from "@/db/schema/fileSystem";
+import { users } from "@/db/schema/users";
+import { eq, and, or, isNull, lte, gte, sql, inArray } from "drizzle-orm";
+import crypto from "crypto";
+
+export interface FileAccessContext {
+  userId: number;
+  userDomain: string;
+  userEmail: string;
+  ipAddress?: string;
+  userAgent?: string;
+}
+
+export class FileService {
+  // 사용자가 내부 사용자인지 확인
+  private isInternalUser(domain: string): boolean {
+    // partners가 아닌 경우 내부 사용자로 간주
+    return domain !== "partners";
+  }
+
+  // 파일 접근 권한 확인
+  async checkFileAccess(
+    fileId: string,
+    context: FileAccessContext,
+    requiredAction: "view" | "download" | "edit" | "delete" | "share"
+  ): Promise<boolean> {
+    // 내부 사용자는 모든 권한 보유
+    if (this.isInternalUser(context.userDomain)) {
+      return true;
+    }
+
+    // 파일 정보 조회
+    const file = await db.query.fileItems.findFirst({
+      where: eq(fileItems.id, fileId),
+    });
+
+    if (!file) return false;
+
+    // 외부 사용자 권한 체크
+    // 1. 파일 카테고리별 기본 권한 체크
+    switch (file.category) {
+      case "public":
+        // public 파일은 열람과 다운로드 가능
+        if (requiredAction === "view" || requiredAction === "download") {
+          return true;
+        }
+        break;
+      case "restricted":
+        // restricted 파일은 열람만 가능
+        if (requiredAction === "view") {
+          return true;
+        }
+        break;
+      case "confidential":
+      case "internal":
+        // 기본적으로 접근 불가
+        break;
+    }
+
+    // 2. 개별 권한 설정 체크
+    const permission = await db.query.filePermissions.findFirst({
+      where: and(
+        eq(filePermissions.fileItemId, fileId),
+        or(
+          eq(filePermissions.userId, context.userId),
+          eq(filePermissions.userDomain, context.userDomain)
+        ),
+        or(
+          isNull(filePermissions.validFrom),
+          lte(filePermissions.validFrom, new Date())
+        ),
+        or(
+          isNull(filePermissions.validUntil),
+          gte(filePermissions.validUntil, new Date())
+        )
+      ),
+    });
+
+    if (permission) {
+      switch (requiredAction) {
+        case "view": return permission.canView;
+        case "download": return permission.canDownload;
+        case "edit": return permission.canEdit;
+        case "delete": return permission.canDelete;
+        case "share": return permission.canShare;
+      }
+    }
+
+    return false;
+  }
+
+ // 파일 목록 조회 (트리 뷰 지원)
+async getFileList(
+  projectId: string,
+  parentId: string | null,
+  context: FileAccessContext,
+  options?: { 
+    includeAll?: boolean;  // 전체 파일 가져오기 옵션
+  }
+) {
+  const isInternal = this.isInternalUser(context.userDomain);
+
+  // 기본 쿼리 빌드
+  let baseConditions = [eq(fileItems.projectId, projectId)];
+  
+  // includeAll이 false이거나 명시되지 않은 경우에만 parentId 조건 추가
+  if (!options?.includeAll) {
+    baseConditions.push(
+      parentId ? eq(fileItems.parentId, parentId) : isNull(fileItems.parentId)
+    );
+  }
+
+  let query = db
+    .select({
+      file: fileItems,
+      canView: sql<boolean>`true`,
+      canDownload: sql<boolean>`${isInternal}`,
+      canEdit: sql<boolean>`${isInternal}`,
+      canDelete: sql<boolean>`${isInternal}`,
+    })
+    .from(fileItems)
+    .where(and(...baseConditions));
+
+  if (!isInternal) {
+    // 외부 사용자는 접근 가능한 파일만 표시
+    let externalConditions = [eq(fileItems.projectId, projectId)];
+    
+    if (!options?.includeAll) {
+      externalConditions.push(
+        parentId ? eq(fileItems.parentId, parentId) : isNull(fileItems.parentId)
+      );
+    }
+
+    query = db
+      .select({
+        file: fileItems,
+        canView: sql<boolean>`
+          CASE 
+            WHEN ${fileItems.category} IN ('public', 'restricted') THEN true
+            WHEN ${filePermissions.canView} = true THEN true
+            ELSE false
+          END
+        `,
+        canDownload: sql<boolean>`
+          CASE 
+            WHEN ${fileItems.category} = 'public' THEN true
+            WHEN ${filePermissions.canDownload} = true THEN true
+            ELSE false
+          END
+        `,
+        canEdit: sql<boolean>`COALESCE(${filePermissions.canEdit}, false)`,
+        canDelete: sql<boolean>`COALESCE(${filePermissions.canDelete}, false)`,
+      })
+      .from(fileItems)
+      .leftJoin(
+        filePermissions,
+        and(
+          eq(filePermissions.fileItemId, fileItems.id),
+          or(
+            eq(filePermissions.userId, context.userId),
+            eq(filePermissions.userDomain, context.userDomain)
+          )
+        )
+      )
+      .where(
+        and(
+          ...externalConditions,
+          or(
+            inArray(fileItems.category, ["public", "restricted"]),
+            eq(filePermissions.canView, true)
+          )
+        )
+      );
+  }
+
+  const results = await query;
+  
+  // 활동 로그 기록 (전체 목록 조회시에는 로그 생략)
+  if (!options?.includeAll) {
+    for (const result of results) {
+      await this.logActivity(result.file.id, projectId, "view", context);
+    }
+  }
+
+  return results.map(r => ({
+    ...r.file,
+    permissions: {
+      canView: r.canView,
+      canDownload: r.canDownload,
+      canEdit: r.canEdit,
+      canDelete: r.canDelete,
+    },
+  }));
+}
+
+
+  // 파일/폴더 생성
+  async createFileItem(
+    data: NewFileItem,
+    context: FileAccessContext
+  ): Promise<FileItem> {
+    // 내부 사용자만 파일 생성 가능
+    if (!this.isInternalUser(context.userDomain)) {
+      throw new Error("권한이 없습니다");
+    }
+
+    // 경로 계산
+    let path = "/";
+    let depth = 0;
+
+    if (data.parentId) {
+      const parent = await db.query.fileItems.findFirst({
+        where: eq(fileItems.id, data.parentId),
+      });
+      if (parent) {
+        path = `${parent.path}${parent.name}/`;
+        depth = parent.depth + 1;
+      }
+    }
+
+    const [newFile] = await db
+      .insert(fileItems)
+      .values({
+        ...data,
+        path,
+        depth,
+        createdBy: context.userId,
+        updatedBy: context.userId,
+      })
+      .returning();
+
+    await this.logActivity(newFile.id, newFile.projectId, "upload", context);
+
+    return newFile;
+  }
+
+  // 파일 다운로드
+  async downloadFile(
+    fileId: string,
+    context: FileAccessContext
+  ): Promise<FileItem | null> {
+    const hasAccess = await this.checkFileAccess(fileId, context, "download");
+    
+    if (!hasAccess) {
+      throw new Error("다운로드 권한이 없습니다");
+    }
+
+    const file = await db.query.fileItems.findFirst({
+      where: eq(fileItems.id, fileId),
+    });
+
+    if (!file) return null;
+
+    // 다운로드 카운트 증가
+    await db
+      .update(fileItems)
+      .set({
+        downloadCount: sql`${fileItems.downloadCount} + 1`,
+      })
+      .where(eq(fileItems.id, fileId));
+
+    // 활동 로그 기록
+    await this.logActivity(fileId, file.projectId, "download", context);
+
+    return file;
+  }
+
+  // 파일 공유 링크 생성
+  async createShareLink(
+    fileId: string,
+    options: {
+      accessLevel?: "view_only" | "view_download";
+      password?: string;
+      expiresAt?: Date;
+      maxDownloads?: number;
+      sharedWithEmail?: string;
+    },
+    context: FileAccessContext
+  ): Promise<string> {
+    const hasAccess = await this.checkFileAccess(fileId, context, "share");
+    
+    if (!hasAccess) {
+      throw new Error("공유 권한이 없습니다");
+    }
+
+    const shareToken = crypto.randomBytes(32).toString("hex");
+
+    const [share] = await db
+      .insert(fileShares)
+      .values({
+        fileItemId: fileId,
+        shareToken,
+        accessLevel: options.accessLevel || "view_only",
+        password: options.password,
+        expiresAt: options.expiresAt,
+        maxDownloads: options.maxDownloads,
+        sharedWithEmail: options.sharedWithEmail,
+        createdBy: context.userId,
+      })
+      .returning();
+
+    const file = await db.query.fileItems.findFirst({
+      where: eq(fileItems.id, fileId),
+    });
+
+    if (file) {
+      await this.logActivity(fileId, file.projectId, "share", context, {
+        shareId: share.id,
+        sharedWithEmail: options.sharedWithEmail,
+      });
+    }
+
+    return shareToken;
+  }
+
+  // 공유 링크로 파일 접근
+  async accessFileByShareToken(
+    shareToken: string,
+    password?: string
+  ): Promise<{ file: FileItem; accessLevel: string } | null> {
+    const share = await db.query.fileShares.findFirst({
+      where: eq(fileShares.shareToken, shareToken),
+      with: {
+        fileItem: true,
+      },
+    });
+
+    if (!share || !share.fileItem) return null;
+
+    // 유효성 검사
+    if (share.expiresAt && share.expiresAt < new Date()) {
+      throw new Error("공유 링크가 만료되었습니다");
+    }
+
+    if (share.password && share.password !== password) {
+      throw new Error("비밀번호가 일치하지 않습니다");
+    }
+
+    if (
+      share.maxDownloads &&
+      share.currentDownloads >= share.maxDownloads
+    ) {
+      throw new Error("최대 다운로드 횟수를 초과했습니다");
+    }
+
+    // 접근 기록 업데이트
+    await db
+      .update(fileShares)
+      .set({
+        lastAccessedAt: new Date(),
+      })
+      .where(eq(fileShares.id, share.id));
+
+    // 조회수 증가
+    await db
+      .update(fileItems)
+      .set({
+        viewCount: sql`${fileItems.viewCount} + 1`,
+      })
+      .where(eq(fileItems.id, share.fileItemId));
+
+    return {
+      file: share.fileItem,
+      accessLevel: share.accessLevel,
+    };
+  }
+
+  // 파일 권한 부여
+  async grantPermission(
+    fileId: string,
+    targetUserId: number | null,
+    targetDomain: string | null,
+    permissions: {
+      canView?: boolean;
+      canDownload?: boolean;
+      canEdit?: boolean;
+      canDelete?: boolean;
+      canShare?: boolean;
+    },
+    context: FileAccessContext
+  ): Promise<void> {
+    // 내부 사용자만 권한 부여 가능
+    if (!this.isInternalUser(context.userDomain)) {
+      throw new Error("권한 부여 권한이 없습니다");
+    }
+
+    await db
+      .insert(filePermissions)
+      .values({
+        fileItemId: fileId,
+        userId: targetUserId,
+        userDomain: targetDomain,
+        ...permissions,
+        grantedBy: context.userId,
+      })
+      .onConflictDoUpdate({
+        target: [filePermissions.fileItemId, filePermissions.userId],
+        set: {
+          ...permissions,
+          updatedAt: new Date(),
+        },
+      });
+  }
+
+  // 활동 로그 기록
+  private async logActivity(
+    fileItemId: string,
+    projectId: string,
+    action: string,
+    context: FileAccessContext,
+    details: any = {}
+  ): Promise<void> {
+    await db.insert(fileActivityLogs).values({
+      fileItemId,
+      projectId,
+      action,
+      actionDetails: details,
+      userId: context.userId,
+      userEmail: context.userEmail,
+      userDomain: context.userDomain,
+      ipAddress: context.ipAddress,
+      userAgent: context.userAgent,
+    });
+  }
+
+  // 파일 이동
+  async moveFile(
+    fileId: string,
+    newParentId: string | null,
+    context: FileAccessContext
+  ): Promise<void> {
+    const hasAccess = await this.checkFileAccess(fileId, context, "edit");
+    
+    if (!hasAccess) {
+      throw new Error("이동 권한이 없습니다");
+    }
+
+    // 새 경로 계산
+    let newPath = "/";
+    let newDepth = 0;
+
+    if (newParentId) {
+      const newParent = await db.query.fileItems.findFirst({
+        where: eq(fileItems.id, newParentId),
+      });
+      if (newParent) {
+        newPath = `${newParent.path}${newParent.name}/`;
+        newDepth = newParent.depth + 1;
+      }
+    }
+
+    await db
+      .update(fileItems)
+      .set({
+        parentId: newParentId,
+        path: newPath,
+        depth: newDepth,
+        updatedBy: context.userId,
+        updatedAt: new Date(),
+      })
+      .where(eq(fileItems.id, fileId));
+
+    // 하위 항목들의 경로도 재귀적으로 업데이트 필요 (생략)
+  }
+
+  // 파일 삭제
+  async deleteFile(
+    fileId: string,
+    context: FileAccessContext
+  ): Promise<void> {
+    const hasAccess = await this.checkFileAccess(fileId, context, "delete");
+    
+    if (!hasAccess) {
+      throw new Error("삭제 권한이 없습니다");
+    }
+
+    const file = await db.query.fileItems.findFirst({
+      where: eq(fileItems.id, fileId),
+    });
+
+    if (file) {
+      await this.logActivity(fileId, file.projectId, "delete", context);
+    }
+
+    await db.delete(fileItems).where(eq(fileItems.id, fileId));
+  }
+
+  // 프로젝트별 스토리지 사용량 계산
+  async getProjectStorageUsage(projectId: string): Promise<{
+    totalSize: number;
+    fileCount: number;
+    folderCount: number;
+  }> {
+    const result = await db
+      .select({
+        totalSize: sql<number>`COALESCE(SUM(${fileItems.size}), 0)`,
+        fileCount: sql<number>`COUNT(CASE WHEN ${fileItems.type} = 'file' THEN 1 END)`,
+        folderCount: sql<number>`COUNT(CASE WHEN ${fileItems.type} = 'folder' THEN 1 END)`,
+      })
+      .from(fileItems)
+      .where(eq(fileItems.projectId, projectId));
+
+    return result[0] || { totalSize: 0, fileCount: 0, folderCount: 0 };
+  }
+}
\ No newline at end of file
diff --git a/lib/services/projectService.ts b/lib/services/projectService.ts
new file mode 100644
index 00000000..55ddcf0e
--- /dev/null
+++ b/lib/services/projectService.ts
@@ -0,0 +1,471 @@
+// lib/services/projectService.ts
+import db from "@/db/db";
+import {
+  fileSystemProjects,
+  fileItems,
+  projectMembers,
+  fileActivityLogs,
+  type FileSystemProject,
+  type NewFileSystemProject,
+} from "@/db/schema/fileSystem";
+import { users } from "@/db/schema/users";
+import { eq, and, or, inArray, gte, sql, not } from "drizzle-orm";
+
+// 프로젝트 멤버 역할 타입
+export type ProjectRole = "owner" | "admin" | "editor" | "viewer";
+
+export class ProjectService {
+  // 프로젝트 생성 (생성자가 자동으로 owner가 됨)
+  async createProject(
+    data: {
+      name: string;
+      description?: string;
+      isPublic?: boolean;
+    },
+    userId: number
+  ): Promise<FileSystemProject> {
+    const [project] = await db.transaction(async (tx) => {
+      // 1. 프로젝트 생성
+      const [newProject] = await tx
+        .insert(fileSystemProjects)
+        .values({
+          ...data,
+          ownerId: userId,
+        })
+        .returning();
+
+      // 2. 생성자를 owner로 프로젝트 멤버에 추가
+      await tx.insert(projectMembers).values({
+        projectId: newProject.id,
+        userId: userId,
+        role: "owner",
+        addedBy: userId,
+      });
+
+      return [newProject];
+    });
+
+    return project;
+  }
+
+  // 프로젝트 Owner 확인
+  async isProjectOwner(projectId: string, userId: number): Promise<boolean> {
+    const project = await db.query.fileSystemProjects.findFirst({
+      where: and(
+        eq(fileSystemProjects.id, projectId),
+        eq(fileSystemProjects.ownerId, userId)
+      ),
+    });
+
+    return !!project;
+  }
+
+  // 프로젝트 접근 권한 확인
+  async checkProjectAccess(
+    projectId: string,
+    userId: number,
+    requiredRole?: ProjectRole
+  ): Promise<{
+    hasAccess: boolean;
+    role?: ProjectRole;
+    isOwner: boolean;
+  }> {
+    // 1. Owner 확인
+    const project = await db.query.fileSystemProjects.findFirst({
+      where: eq(fileSystemProjects.id, projectId),
+    });
+
+    if (!project) {
+      return { hasAccess: false, isOwner: false };
+    }
+
+    const isOwner = project.ownerId === userId;
+    
+    // Owner는 모든 권한 보유
+    if (isOwner) {
+      return { hasAccess: true, role: "owner", isOwner: true };
+    }
+
+    // 2. 프로젝트 멤버 확인
+    const member = await db.query.projectMembers.findFirst({
+      where: and(
+        eq(projectMembers.projectId, projectId),
+        eq(projectMembers.userId, userId)
+      ),
+    });
+
+    if (!member) {
+      // 공개 프로젝트인 경우 viewer 권한
+      if (project.isPublic) {
+        return { 
+          hasAccess: !requiredRole || requiredRole === "viewer",
+          role: "viewer",
+          isOwner: false 
+        };
+      }
+      return { hasAccess: false, isOwner: false };
+    }
+
+    // 3. 역할 계층 확인
+    const roleHierarchy: Record<ProjectRole, number> = {
+      owner: 4,
+      admin: 3,
+      editor: 2,
+      viewer: 1,
+    };
+
+    const hasRequiredRole = !requiredRole || 
+      roleHierarchy[member.role] >= roleHierarchy[requiredRole];
+
+    return {
+      hasAccess: hasRequiredRole,
+      role: member.role as ProjectRole,
+      isOwner: false,
+    };
+  }
+
+  // 프로젝트 멤버 추가 (Owner만 가능)
+  async addProjectMember(
+    projectId: string,
+    newMemberId: number,
+    role: ProjectRole,
+    addedByUserId: number
+  ): Promise<void> {
+    // Owner 권한 확인
+    const isOwner = await this.isProjectOwner(projectId, addedByUserId);
+    
+    if (!isOwner) {
+      throw new Error("프로젝트 소유자만 멤버를 추가할 수 있습니다");
+    }
+
+    // Owner 역할은 양도를 통해서만 가능
+    if (role === "owner") {
+      throw new Error("Owner 역할은 직접 할당할 수 없습니다. transferOwnership을 사용하세요.");
+    }
+
+    await db.insert(projectMembers).values({
+      projectId,
+      userId: newMemberId,
+      role,
+      addedBy: addedByUserId,
+    });
+  }
+
+  // 프로젝트 소유권 이전 (Owner만 가능)
+  async transferOwnership(
+    projectId: string,
+    currentOwnerId: number,
+    newOwnerId: number
+  ): Promise<void> {
+    await db.transaction(async (tx) => {
+      // 1. 현재 Owner 확인
+      const project = await tx.query.fileSystemProjects.findFirst({
+        where: and(
+          eq(fileSystemProjects.id, projectId),
+          eq(fileSystemProjects.ownerId, currentOwnerId)
+        ),
+      });
+
+      if (!project) {
+        throw new Error("프로젝트 소유자만 소유권을 이전할 수 있습니다");
+      }
+
+      // 2. 프로젝트 owner 업데이트
+      await tx
+        .update(fileSystemProjects)
+        .set({ ownerId: newOwnerId })
+        .where(eq(fileSystemProjects.id, projectId));
+
+      // 3. 프로젝트 멤버 역할 업데이트
+      // 이전 owner를 admin으로 변경
+      await tx
+        .update(projectMembers)
+        .set({ role: "admin" })
+        .where(
+          and(
+            eq(projectMembers.projectId, projectId),
+            eq(projectMembers.userId, currentOwnerId)
+          )
+        );
+
+      // 새 owner를 owner 역할로 설정 (없으면 추가)
+      await tx
+        .insert(projectMembers)
+        .values({
+          projectId,
+          userId: newOwnerId,
+          role: "owner",
+          addedBy: currentOwnerId,
+        })
+        .onConflictDoUpdate({
+          target: [projectMembers.projectId, projectMembers.userId],
+          set: { role: "owner", updatedAt: new Date() },
+        });
+    });
+  }
+
+  // 프로젝트 삭제 (Owner만 가능)
+  async deleteProject(projectId: string, userId: number): Promise<void> {
+    const isOwner = await this.isProjectOwner(projectId, userId);
+    
+    if (!isOwner) {
+      throw new Error("프로젝트 소유자만 프로젝트를 삭제할 수 있습니다");
+    }
+
+    // 프로젝트 삭제 (cascade로 관련 파일, 멤버 등도 삭제됨)
+    await db.delete(fileSystemProjects).where(eq(fileSystemProjects.id, projectId));
+  }
+
+  // 프로젝트 설정 변경 (Owner와 Admin만 가능)
+  async updateProjectSettings(
+    projectId: string,
+    userId: number,
+    settings: {
+      name?: string;
+      description?: string;
+      isPublic?: boolean;
+      externalAccessEnabled?: boolean;
+    }
+  ): Promise<void> {
+    const access = await this.checkProjectAccess(projectId, userId, "admin");
+    
+    if (!access.hasAccess) {
+      throw new Error("프로젝트 설정을 변경할 권한이 없습니다");
+    }
+
+    await db
+      .update(fileSystemProjects)
+      .set({
+        ...settings,
+        updatedAt: new Date(),
+      })
+      .where(eq(fileSystemProjects.id, projectId));
+  }
+
+  // 사용자의 프로젝트 목록 조회
+  async getUserProjects(userId: number): Promise<{
+    owned: FileSystemProject[];
+    member: Array<FileSystemProject & { role: ProjectRole }>;
+    public: FileSystemProject[];
+  }> {
+    // 1. 소유한 프로젝트
+    const ownedProjects = await db.query.fileSystemProjects.findMany({
+      where: eq(fileSystemProjects.ownerId, userId),
+      orderBy: (fileSystemProjects, { desc }) => [desc(fileSystemProjects.createdAt)],
+    });
+
+    // 2. 멤버로 참여한 프로젝트
+    const memberProjects = await db
+      .select({
+        project: fileSystemProjects,
+        role: projectMembers.role,
+      })
+      .from(projectMembers)
+      .innerJoin(fileSystemProjects, eq(fileSystemProjects.id, projectMembers.projectId))
+      .where(
+        and(
+          eq(projectMembers.userId, userId),
+          // Owner가 아닌 경우만 (중복 방지) - not 사용
+          not(eq(fileSystemProjects.ownerId, userId))
+        )
+      );
+
+    // 3. 공개 프로젝트 (참여하지 않은)
+    const memberProjectIds = memberProjects.map(mp => mp.project.id);
+    const ownedProjectIds = ownedProjects.map(p => p.id);
+    const allUserProjectIds = [...memberProjectIds, ...ownedProjectIds];
+    
+    let publicProjects;
+    if (allUserProjectIds.length > 0) {
+      publicProjects = await db.query.fileSystemProjects.findMany({
+        where: and(
+          eq(fileSystemProjects.isPublic, true),
+          not(eq(fileSystemProjects.ownerId, userId)),
+          not(inArray(fileSystemProjects.id, allUserProjectIds))
+        ),
+        orderBy: (fileSystemProjects, { desc }) => [desc(fileSystemProjects.createdAt)],
+      });
+    } else {
+      // 사용자가 참여한 프로젝트가 없는 경우
+      publicProjects = await db.query.fileSystemProjects.findMany({
+        where: and(
+          eq(fileSystemProjects.isPublic, true),
+          not(eq(fileSystemProjects.ownerId, userId))
+        ),
+        orderBy: (fileSystemProjects, { desc }) => [desc(fileSystemProjects.createdAt)],
+      });
+    }
+
+    return {
+      owned: ownedProjects,
+      member: memberProjects.map(mp => ({
+        ...mp.project,
+        role: mp.role as ProjectRole,
+      })),
+      public: publicProjects,
+    };
+  }
+
+  // 프로젝트 통계 (Owner용)
+  async getProjectStats(projectId: string, userId: number) {
+    const isOwner = await this.isProjectOwner(projectId, userId);
+    
+    if (!isOwner) {
+      throw new Error("프로젝트 통계는 소유자만 볼 수 있습니다");
+    }
+
+    // 파일 통계
+    const fileStats = await db
+      .select({
+        totalFiles: sql<number>`COUNT(*)`,
+        totalSize: sql<number>`COALESCE(SUM(size), 0)`,
+        publicFiles: sql<number>`COUNT(CASE WHEN category = 'public' THEN 1 END)`,
+        restrictedFiles: sql<number>`COUNT(CASE WHEN category = 'restricted' THEN 1 END)`,
+        confidentialFiles: sql<number>`COUNT(CASE WHEN category = 'confidential' THEN 1 END)`,
+      })
+      .from(fileItems)
+      .where(eq(fileItems.projectId, projectId));
+
+    // 멤버 통계
+    const memberStats = await db
+      .select({
+        totalMembers: sql<number>`COUNT(*)`,
+        admins: sql<number>`COUNT(CASE WHEN role = 'admin' THEN 1 END)`,
+        editors: sql<number>`COUNT(CASE WHEN role = 'editor' THEN 1 END)`,
+        viewers: sql<number>`COUNT(CASE WHEN role = 'viewer' THEN 1 END)`,
+      })
+      .from(projectMembers)
+      .where(eq(projectMembers.projectId, projectId));
+
+    // 활동 통계 (최근 30일)
+    const activityStats = await db
+      .select({
+        totalViews: sql<number>`COUNT(CASE WHEN action = 'view' THEN 1 END)`,
+        totalDownloads: sql<number>`COUNT(CASE WHEN action = 'download' THEN 1 END)`,
+        totalUploads: sql<number>`COUNT(CASE WHEN action = 'upload' THEN 1 END)`,
+        uniqueUsers: sql<number>`COUNT(DISTINCT user_id)`,
+      })
+      .from(fileActivityLogs)
+      .where(
+        and(
+          eq(fileActivityLogs.projectId, projectId),
+          gte(fileActivityLogs.createdAt, new Date(Date.now() - 30 * 24 * 60 * 60 * 1000))
+        )
+      );
+
+    return {
+      files: fileStats[0],
+      members: memberStats[0],
+      activity: activityStats[0],
+    };
+  }
+
+  // 개별 프로젝트 정보 조회
+  async getProject(projectId: string): Promise<FileSystemProject | null> {
+    const project = await db.query.fileSystemProjects.findFirst({
+      where: eq(fileSystemProjects.id, projectId),
+      with: {
+        owner: true,
+      },
+    });
+
+    return project || null;
+  }
+
+  // 프로젝트 보관
+  async archiveProject(projectId: string, userId: number): Promise<void> {
+    const isOwner = await this.isProjectOwner(projectId, userId);
+    
+    if (!isOwner) {
+      throw new Error("프로젝트 소유자만 보관할 수 있습니다");
+    }
+
+    // 프로젝트를 보관 상태로 변경
+    await db
+      .update(fileSystemProjects)
+      .set({
+        metadata: sql`jsonb_set(metadata, '{archived}', 'true')`,
+        updatedAt: new Date(),
+      })
+      .where(eq(fileSystemProjects.id, projectId));
+  }
+
+  // 멤버 역할 업데이트
+  async updateMemberRole(
+    projectId: string,
+    memberId: string,
+    newRole: ProjectRole
+  ): Promise<void> {
+    // Owner 역할은 transferOwnership를 통해서만 가능
+    if (newRole === 'owner') {
+      throw new Error("Owner 역할은 소유권 이전을 통해서만 가능합니다");
+    }
+
+    await db
+      .update(projectMembers)
+      .set({
+        role: newRole,
+        updatedAt: new Date(),
+      })
+      .where(
+        and(
+          eq(projectMembers.projectId, projectId),
+          eq(projectMembers.id, memberId)
+        )
+      );
+  }
+
+  // 프로젝트 멤버 제거
+  async removeMember(projectId: string, memberId: string): Promise<void> {
+    // Owner는 제거할 수 없음
+    const member = await db.query.projectMembers.findFirst({
+      where: and(
+        eq(projectMembers.projectId, projectId),
+        eq(projectMembers.id, memberId)
+      ),
+    });
+
+    if (member?.role === 'owner') {
+      throw new Error("Owner는 제거할 수 없습니다");
+    }
+
+    await db
+      .delete(projectMembers)
+      .where(
+        and(
+          eq(projectMembers.projectId, projectId),
+          eq(projectMembers.id, memberId)
+        )
+      );
+  }
+
+  // 프로젝트 멤버 목록 조회
+  async getProjectMembers(projectId: string): Promise<any[]> {
+    const members = await db
+      .select({
+        id: projectMembers.id,
+        userId: projectMembers.userId,
+        role: projectMembers.role,
+        addedAt: projectMembers.createdAt,
+        user: {
+          name: users.name,
+          email: users.email,
+          imageUrl: users.imageUrl,
+          domain: users.domain,
+        },
+      })
+      .from(projectMembers)
+      .innerJoin(users, eq(users.id, projectMembers.userId))
+      .where(eq(projectMembers.projectId, projectId))
+      .orderBy(
+        sql`CASE 
+          WHEN ${projectMembers.role} = 'owner' THEN 1
+          WHEN ${projectMembers.role} = 'admin' THEN 2
+          WHEN ${projectMembers.role} = 'editor' THEN 3
+          ELSE 4
+        END`
+      );
+
+    return members;
+  }
+}