중첩 관계

// SubsetQuery: LEFT JOIN으로 처리
{
  "subsets": {
    "P": [
      "id",
      "username",
      "employee.salary",           // ← OneToOne
      "employee.department.name"   // ← BelongsTo
    ]
  }
}

// SQL: LEFT JOIN employees, LEFT JOIN departments
// 결과: 1행당 1개의 employee

// LoaderQuery: 별도 쿼리로 처리
{
  loaders: [
    {
      as: "posts",
      refId: "user_id",
      qb: (qb, fromIds) => 
        qb.table("posts")
          .whereIn("user_id", fromIds)
    }
  ]
}

// SQL: SELECT * FROM posts WHERE user_id IN (1, 2, 3)
// 결과: 1명당 N개의 posts 배열

// src/application/user/user.subset-loaders.ts

import type { SubsetLoaderMap } from "sonamu";

export const UserSubsetLoaders: SubsetLoaderMap<"User"> = {
  // Subset A에 posts 로더 추가
  A: [
    {
      as: "posts",              // 결과 필드명
      refId: "user_id",         // 참조 필드 (posts.user_id)
      qb: (qb, fromIds) => 
        qb.table("posts")
          .whereIn("user_id", fromIds)
          .select({
            id: "id",
            title: "title",
            created_at: "created_at",
          })
          .orderBy("created_at", "desc"),
    },
  ],
};

// Department Entity
export const DepartmentSubsetLoaders: SubsetLoaderMap<"Department"> = {
  P: [
    {
      as: "employees",
      refId: "department_id",
      qb: (qb, fromIds) => 
        qb.table("employees")
          .whereIn("department_id", fromIds)
          .join("users", "employees.user_id", "users.id")
          .select({
            id: "employees.id",
            employee_number: "employees.employee_number",
            username: "users.username",
            salary: "employees.salary",
          })
          .orderBy("employees.employee_number", "asc"),
    },
  ],
};

const dept = await DepartmentModel.findById(1, ["P"]);

// 타입:
// {
//   id: number;
//   name: string;
//   employees: Array<{
//     id: number;
//     employee_number: string;
//     username: string;
//     salary: string;
//   }>;
// }

console.log(`${dept.name} 부서 직원 수: ${dept.employees.length}`);
dept.employees.forEach(emp => {
  console.log(`- ${emp.username} (${emp.employee_number})`);
});

// Project Entity
export const ProjectSubsetLoaders: SubsetLoaderMap<"Project"> = {
  P: [
    {
      as: "members",
      refId: "project_id",
      qb: (qb, fromIds) => 
        qb.table("projects__employees")
          .whereIn("project_id", fromIds)
          .join("employees", "projects__employees.employee_id", "employees.id")
          .join("users", "employees.user_id", "users.id")
          .select({
            id: "employees.id",
            username: "users.username",
            employee_number: "employees.employee_number",
            role: "projects__employees.role",  // 중간 테이블 필드
          }),
    },
  ],
};

const project = await ProjectModel.findById(1, ["P"]);

// 타입:
// {
//   id: number;
//   name: string;
//   members: Array<{
//     id: number;
//     username: string;
//     employee_number: string;
//     role: string;  // 중간 테이블의 role
//   }>;
// }

console.log(`프로젝트: ${project.name}`);
console.log(`참여자 ${project.members.length}명:`);
project.members.forEach(member => {
  console.log(`- ${member.username} (${member.role})`);
});

// Department Entity
export const DepartmentSubsetLoaders: SubsetLoaderMap<"Department"> = {
  P: [
    {
      as: "employees",
      refId: "department_id",
      qb: (qb, fromIds) => 
        qb.table("employees")
          .whereIn("department_id", fromIds)
          .join("users", "employees.user_id", "users.id")
          .select({
            id: "employees.id",
            username: "users.username",
            user_id: "users.id",  // ← 다음 Loader를 위한 refId
          }),
      
      // 중첩 Loader: 직원 → 프로젝트들
      loaders: [
        {
          as: "projects",
          refId: "employee_id",
          qb: (qb, fromIds) => 
            qb.table("projects__employees")
              .whereIn("employee_id", fromIds)
              .join("projects", "projects__employees.project_id", "projects.id")
              .select({
                id: "projects.id",
                name: "projects.name",
                status: "projects.status",
              }),
        },
      ],
    },
  ],
};

{
  id: 1,
  name: "Engineering",
  employees: [
    {
      id: 1,
      username: "john",
      projects: [
        { id: 1, name: "Project A", status: "in_progress" },
        { id: 2, name: "Project B", status: "completed" }
      ]
    },
    {
      id: 2,
      username: "jane",
      projects: [
        { id: 3, name: "Project C", status: "in_progress" }
      ]
    }
  ]
}

// ❌ N+1 문제 발생
const users = await UserModel.findMany({ subsetKey: "L" }); // 1 query

for (const user of users) {
  const posts = await PostModel.findMany({
    where: [["user_id", user.id]],
    subsetKey: "L",
  }); // N queries (users 수만큼)
  
  console.log(`${user.username}: ${posts.length} posts`);
}

// 총 쿼리: 1 + N개

// ✅ Batch Loading으로 해결
const users = await UserModel.findMany({
  subsetKey: "A",  // A Subset에 posts Loader 포함
});

// 쿼리 1: SELECT * FROM users
// 쿼리 2: SELECT * FROM posts WHERE user_id IN (1, 2, 3, ...)

users.forEach(user => {
  console.log(`${user.username}: ${user.posts.length} posts`);
});

// 총 쿼리: 2개 (users 수와 무관)

async getDepartmentWithEmployees(deptId: number) {
  const dept = await DepartmentModel.findById(deptId, ["P"]);
  
  if (!dept) {
    throw new NotFoundException("Department not found");
  }
  
  return {
    id: dept.id,
    name: dept.name,
    employeeCount: dept.employees.length,
    employees: dept.employees.map(emp => ({
      id: emp.id,
      name: emp.username,
      number: emp.employee_number,
      salary: emp.salary,
    })),
  };
}

async getProjectDashboard(projectId: number) {
  const project = await ProjectModel.findById(projectId, ["Dashboard"]);
  
  if (!project) {
    throw new NotFoundException("Project not found");
  }
  
  return {
    id: project.id,
    name: project.name,
    status: project.status,
    
    // Members from LoaderQuery
    members: project.members.map(m => ({
      id: m.id,
      name: m.username,
      role: m.role,
    })),
    
    // Tasks from LoaderQuery
    tasks: project.tasks.map(t => ({
      id: t.id,
      title: t.title,
      status: t.status,
      assignee: t.assignee_name,
    })),
    
    stats: {
      memberCount: project.members.length,
      taskCount: project.tasks.length,
      completedTasks: project.tasks.filter(t => t.status === "completed").length,
    },
  };
}

// User Subset with posts and comments
const user = await UserModel.findById(userId, ["Activity"]);

const summary = {
  user: {
    id: user.id,
    username: user.username,
    email: user.email,
  },
  
  activity: {
    totalPosts: user.posts.length,
    recentPosts: user.posts.slice(0, 5).map(p => ({
      id: p.id,
      title: p.title,
      createdAt: p.created_at,
    })),
    
    totalComments: user.comments.length,
    recentComments: user.comments.slice(0, 5).map(c => ({
      id: c.id,
      content: c.content,
      postTitle: c.post_title,
      createdAt: c.created_at,
    })),
  },
};

// ❌ 나쁨: 모든 필드 로딩
{
  as: "employees",
  refId: "department_id",
  qb: (qb, fromIds) => 
    qb.table("employees")
      .whereIn("department_id", fromIds)
      .selectAll(),  // 모든 필드 (password 등 포함)
}

// ✅ 좋음: 필요한 필드만
{
  as: "employees",
  refId: "department_id",
  qb: (qb, fromIds) => 
    qb.table("employees")
      .whereIn("department_id", fromIds)
      .select({
        id: "id",
        username: "username",
        employee_number: "employee_number",
      }),
}

// 최근 5개 게시글만
{
  as: "recentPosts",
  refId: "user_id",
  qb: (qb, fromIds) => 
    qb.table("posts")
      .whereIn("user_id", fromIds)
      .orderBy("created_at", "desc")
      .limit(5),
}

// 완료된 프로젝트만
{
  as: "completedProjects",
  refId: "employee_id",
  qb: (qb, fromIds) =>
    qb.table("projects__employees")
      .whereIn("employee_id", fromIds)
      .join("projects", "projects__employees.project_id", "projects.id")
      .where("projects.status", "completed")
      .select({
        id: "projects.id",
        name: "projects.name",
      }),
}

class PostModelClass extends BaseModelClass {
  @api({ httpMethod: "GET", clients: ["axios", "tanstack-query"] })
  async findMany<T extends PostSubsetKey>(
    subset: T,
    params: PostListParams
  ): Promise<ListResult<PostListParams, PostSubsetMapping[T]>> {
    const context = Sonamu.getContext();

    // 사용자 권한에 따라 Subset 동적 결정
    let effectiveSubset: PostSubsetKey;

    if (context.user?.role === "admin") {
      // 관리자: 모든 정보 포함 (작성자 상세, 통계 등)
      effectiveSubset = "AdminList" as T;
    } else if (context.user?.role === "normal") {
      // 일반 사용자: 기본 정보만
      effectiveSubset = "List" as T;
    } else {
      // 게스트: 공개 정보만
      effectiveSubset = "PublicList" as T;
    }

    const { qb, onSubset } = this.getSubsetQueries(effectiveSubset);

    // 권한별 추가 필터링
    if (context.user?.role !== "admin") {
      // 비공개 게시글 제외
      qb.where("posts.is_public", true);
    }

    return this.executeSubsetQuery({
      subset: effectiveSubset,
      qb,
      params,
    });
  }
}

// Entity 정의: Comment Subset
export const commentSubsetQueries = {
  // 대댓글 포함 (3단계: Post → Comment → Reply)
  WithReplies: {
    _one: {
      id: "comments.id",
      content: "comments.content",
      created_at: "comments.created_at",
      // 작성자 (2단계)
      author__id: "users.id",
      author__username: "users.username",
    },
    _many: {
      // 대댓글 (3단계)
      replies: {
        as: "replies",
        refId: "parent_id",
        qb: (qb, fromIds) =>
          qb.table("comments")
            .whereIn("parent_id", fromIds)
            .where("deleted_at", null)
            .leftJoin("users", "comments.user_id", "users.id")
            .select({
              id: "comments.id",
              content: "comments.content",
              created_at: "comments.created_at",
              // 대댓글 작성자
              author__id: "users.id",
              author__username: "users.username",
              author__profile__avatar: "profiles.avatar_url",
            })
            .leftJoin("profiles", "users.id", "profiles.user_id")
            .orderBy("comments.created_at", "asc"),
      },
    },
  },
} as const;

// Model 사용
class PostModelClass extends BaseModelClass {
  @api({ httpMethod: "GET" })
  async findByIdWithComments(id: number): Promise<Post> {
    // Post → Comment → Reply → Author까지 한 번에 로드
    const post = await this.findById("DetailWithComments", id);

    // 타입 안전하게 접근
    post.comments?.forEach((comment) => {
      console.log(comment.author?.username); // 댓글 작성자

      comment.replies?.forEach((reply) => {
        console.log(reply.author?.username); // 대댓글 작성자
        console.log(reply.author?.profile?.avatar); // 프로필 이미지
      });
    });

    return post;
  }
}

class UserModelClass extends BaseModelClass {
  @api({ httpMethod: "GET" })
  async findManyWithOptionalPosts<T extends UserSubsetKey>(
    subset: T,
    params: UserListParams,
    includePosts: boolean = false
  ): Promise<ListResult<UserListParams, UserSubsetMapping[T]>> {
    const { qb, onSubset } = this.getSubsetQueries(subset);

    // 기본 쿼리 구성
    qb.where("users.deleted_at", null);

    // 조건부 중첩 데이터 로딩
    if (includePosts) {
      onSubset({
        List: () => {
          // List Subset에 게시글 동적 추가
          qb.onExecute(async (rows) => {
            const userIds = rows.map((row) => row.id);

            // 게시글 별도 조회
            const posts = await this.getPuri("r")
              .table("posts")
              .whereIn("user_id", userIds)
              .where("is_public", true)
              .select({
                id: "id",
                user_id: "user_id",
                title: "title",
                created_at: "created_at",
              })
              .orderBy("created_at", "desc");

            // 사용자별로 게시글 그룹화
            const postsByUser = posts.reduce((acc, post) => {
              if (!acc[post.user_id]) {
                acc[post.user_id] = [];
              }
              acc[post.user_id].push(post);
              return acc;
            }, {} as Record<number, typeof posts>);

            // 결과에 게시글 추가
            return rows.map((row) => ({
              ...row,
              posts: postsByUser[row.id] || [],
            }));
          });
        },
      });
    }

    return this.executeSubsetQuery({ subset, qb, params });
  }
}

// ❌ 나쁜 예: N+1 문제 발생
class PostModelClass extends BaseModelClass {
  async getPostsWithAuthorsBad(): Promise<PostWithAuthor[]> {
    const posts = await this.getPuri("r")
      .table("posts")
      .select({ id: "id", title: "title", user_id: "user_id" });

    // 각 게시글마다 작성자 조회 → N+1 문제!
    const results = [];
    for (const post of posts) {
      const author = await UserModel.findById("Simple", post.user_id);
      results.push({ ...post, author });
    }

    return results;
  }
}

// ✅ 좋은 예: Subset으로 한 번에 로드
class PostModelClass extends BaseModelClass {
  @api({ httpMethod: "GET" })
  async getPostsWithAuthorsGood(): Promise<PostSubsetMapping["WithAuthor"][]> {
    // Subset 정의에 author 관계 포함
    const { rows } = await this.findMany("WithAuthor", { num: 100 });

    // 2개의 쿼리로 완료:
    // 1. SELECT * FROM posts ...
    // 2. SELECT * FROM users WHERE id IN (...)

    return rows;
  }
}

// Subset 정의
export const postSubsetQueries = {
  WithAuthor: {
    _one: {
      id: "posts.id",
      title: "posts.title",
      content: "posts.content",
      // 작성자 정보
      author__id: "users.id",
      author__username: "users.username",
      author__email: "users.email",
    },
  },
} as const;

// Entity: 조직도 (상사-부하 관계)
export const employeeSubsetQueries = {
  // 직속 부하 포함
  WithDirectReports: {
    _one: {
      id: "employees.id",
      username: "employees.username",
      position: "employees.position",
      manager_id: "employees.manager_id",
    },
    _many: {
      // 직속 부하들 (1단계만)
      directReports: {
        as: "directReports",
        refId: "manager_id",
        qb: (qb, fromIds) =>
          qb.table("employees")
            .whereIn("manager_id", fromIds)
            .where("deleted_at", null)
            .select({
              id: "id",
              username: "username",
              position: "position",
            })
            .orderBy("position", "asc"),
      },
    },
  },
} as const;

// Model: 전체 조직도 트리 구성
class EmployeeModelClass extends BaseModelClass {
  @api({ httpMethod: "GET" })
  async getOrganizationTree(
    rootId: number,
    maxDepth: number = 3
  ): Promise<EmployeeTreeNode> {
    // 재귀적으로 조직도 구성
    return this.buildTreeRecursive(rootId, 0, maxDepth);
  }

  private async buildTreeRecursive(
    employeeId: number,
    currentDepth: number,
    maxDepth: number
  ): Promise<EmployeeTreeNode> {
    // 최대 깊이 제한
    if (currentDepth >= maxDepth) {
      const employee = await this.findById("Simple", employeeId);
      return { ...employee, children: [] };
    }

    // 직속 부하 포함 조회
    const employee = await this.findById("WithDirectReports", employeeId);

    // 각 부하에 대해 재귀 호출
    const children = await Promise.all(
      (employee.directReports || []).map((report) =>
        this.buildTreeRecursive(report.id, currentDepth + 1, maxDepth)
      )
    );

    return {
      ...employee,
      children,
      depth: currentDepth,
    };
  }

  @api({ httpMethod: "GET" })
  async getOrganizationTreeOptimized(
    rootId: number
  ): Promise<EmployeeTreeNode> {
    // 최적화: 한 번에 모든 직원 조회 후 메모리에서 트리 구성
    const allEmployees = await this.findMany("WithDirectReports", {
      num: 10000,
    });

    // Map으로 빠른 조회
    const employeeMap = new Map(
      allEmployees.rows.map((emp) => [emp.id, emp])
    );

    // 트리 구성
    return this.buildTreeFromMap(rootId, employeeMap);
  }

  private buildTreeFromMap(
    employeeId: number,
    employeeMap: Map<number, Employee>
  ): EmployeeTreeNode {
    const employee = employeeMap.get(employeeId);
    if (!employee) {
      throw new NotFoundError(`직원 ${employeeId}를 찾을 수 없습니다`);
    }

    // 직속 부하 찾기
    const children = Array.from(employeeMap.values())
      .filter((emp) => emp.manager_id === employeeId)
      .map((emp) => this.buildTreeFromMap(emp.id, employeeMap));

    return {
      ...employee,
      children,
    };
  }
}

interface EmployeeTreeNode extends Employee {
  children: EmployeeTreeNode[];
  depth?: number;
}

interface ProductSearchParams {
  category?: string;
  minPrice?: number;
  maxPrice?: number;
  tags?: string[];
  inStock?: boolean;
  sortBy?: "price" | "popularity" | "recent";
}

class ProductModelClass extends BaseModelClass {
  @api({ httpMethod: "POST" })
  async search<T extends ProductSubsetKey>(
    subset: T,
    searchParams: ProductSearchParams,
    listParams: ProductListParams
  ): Promise<ListResult<ProductListParams, ProductSubsetMapping[T]>> {
    const { qb, onSubset } = this.getSubsetQueries(subset);

    // 동적 필터 적용
    if (searchParams.category) {
      qb.where("products.category", searchParams.category);
    }

    if (searchParams.minPrice !== undefined) {
      qb.where("products.price", ">=", searchParams.minPrice);
    }

    if (searchParams.maxPrice !== undefined) {
      qb.where("products.price", "<=", searchParams.maxPrice);
    }

    if (searchParams.tags && searchParams.tags.length > 0) {
      // PostgreSQL array contains
      qb.whereRaw("products.tags @> ?", [JSON.stringify(searchParams.tags)]);
    }

    if (searchParams.inStock === true) {
      qb.where("products.stock", ">", 0);
    }

    // 동적 정렬
    switch (searchParams.sortBy) {
      case "price":
        qb.orderBy("products.price", "asc");
        break;
      case "popularity":
        qb.orderBy("products.view_count", "desc");
        break;
      case "recent":
        qb.orderBy("products.created_at", "desc");
        break;
      default:
        qb.orderBy("products.id", "desc");
    }

    // Subset별 추가 로직
    onSubset({
      ListWithReviews: () => {
        // 리뷰 포함 시 평균 평점 계산
        qb.select({
          avg_rating: qb.raw(
            "(SELECT AVG(rating) FROM reviews WHERE product_id = products.id)"
          ),
        });
      },
    });

    return this.executeSubsetQuery({ subset, qb, params: listParams });
  }
}