1 files changed, 471 insertions, 0 deletions
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;
+  }
+}