조인

const results = await db.table("employees").join("users", "employees.user_id", "users.id").select({
  employeeId: "employees.id",
  employeeNumber: "employees.employee_number",
  userName: "users.username",
  userEmail: "users.email",
});

// SQL:
// SELECT ...
// FROM employees
// INNER JOIN users ON employees.user_id = users.id

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id")
  .join("departments", "employees.department_id", "departments.id")
  .join("companies", "departments.company_id", "companies.id")
  .select({
    employeeName: "users.username",
    departmentName: "departments.name",
    companyName: "companies.name",
  });

// employees → users (필수)
// employees → departments (필수)
// departments → companies (필수)

const results = await db
  .table("employees")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    employeeId: "employees.id",
    employeeNumber: "employees.employee_number",
    departmentName: "departments.name", // null 가능
  });

// SQL:
// SELECT ...
// FROM employees
// LEFT JOIN departments ON employees.department_id = departments.id

const results = await db
  .table("employees")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .leftJoin("companies", "departments.company_id", "companies.id")
  .select({
    employeeId: "employees.id",
    employeeNumber: "employees.employee_number",
    departmentName: "departments.name", // null 가능
    companyName: "companies.name", // null 가능
  });

// employees (필수)
// → departments (선택)
// → companies (선택)

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id") // 필수: 모든 직원은 사용자 있음
  .leftJoin("departments", "employees.department_id", "departments.id") // 선택: 부서 없을 수 있음
  .select({
    employeeName: "users.username", // 항상 존재
    departmentName: "departments.name", // null 가능
  });

const results = await db
  .table({ child: "departments" })
  .leftJoin({ parent: "departments" }, "child.parent_id", "parent.id")
  .select({
    childId: "child.id",
    childName: "child.name",
    parentId: "parent.id",
    parentName: "parent.name", // null 가능 (최상위 부서)
  });

// departments (자식) LEFT JOIN departments (부모)
// child.parent_id = parent.id

const results = await db
  .table({ user: "users" })
  .leftJoin({ referrer: "users" }, "user.referrer_id", "referrer.id")
  .select({
    userId: "user.id",
    userName: "user.username",
    referrerId: "referrer.id",
    referrerName: "referrer.username", // null 가능
  })
  .where("user.role", "normal");

// 일반 사용자 + 추천인 정보

const results = await db
  .table("projects")
  .leftJoin("projects__employees", "projects.id", "projects__employees.project_id")
  .leftJoin("employees", "projects__employees.employee_id", "employees.id")
  .leftJoin("users", "employees.user_id", "users.id")
  .select({
    projectId: "projects.id",
    projectName: "projects.name",
    projectStatus: "projects.status",
    employeeId: "employees.id",
    employeeName: "users.username",
  })
  .where("projects.status", "in_progress");

// projects ↔ projects__employees ↔ employees ↔ users

const results = await db
  .table("employees")
  .leftJoin("projects__employees", "employees.id", "projects__employees.employee_id")
  .leftJoin("projects", "projects__employees.project_id", "projects.id")
  .leftJoin("users", "employees.user_id", "users.id")
  .select({
    employeeId: "employees.id",
    employeeName: "users.username",
    projectCount: Puri.count("projects.id"),
  })
  .groupBy("employees.id", "users.username")
  .having("projectCount", ">", 0);

// 직원별 참여 프로젝트 수

// 부서별 직원 수 서브쿼리
const empStats = db
  .table("employees")
  .select({
    department_id: "department_id",
    emp_count: Puri.count("id"),
    avg_salary: Puri.avg("salary"),
  })
  .groupBy("department_id");

// 부서 + 통계 조인
const results = await db
  .table("departments")
  .leftJoin({ stats: empStats }, "departments.id", "stats.department_id")
  .select({
    deptId: "departments.id",
    deptName: "departments.name",
    empCount: "stats.emp_count",
    avgSalary: "stats.avg_salary",
  });

// 사용자별 최신 로그인 서브쿼리
const latestLogins = db
  .table("login_logs")
  .select({
    user_id: "user_id",
    latest_login: Puri.max("logged_in_at"),
  })
  .groupBy("user_id");

// 사용자 + 최신 로그인 조인
const results = await db
  .table("users")
  .leftJoin({ latest: latestLogins }, "users.id", "latest.user_id")
  .select({
    userId: "users.id",
    userName: "users.username",
    latestLogin: "latest.latest_login",
  });

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    employeeId: "employees.id",
    userName: "users.username",
    deptName: "departments.name",
  })
  .where("users.is_active", true) // users 필터
  .where("employees.salary", ">=", 50000) // employees 필터
  .where("departments.name", "like", "%개발%"); // departments 필터

const results = await db
  .table("departments")
  .leftJoin("employees", "departments.id", "employees.department_id")
  .select({
    deptId: "departments.id",
    deptName: "departments.name",
    empCount: Puri.count("employees.id"),
  })
  .groupBy("departments.id", "departments.name")
  .having("empCount", ">", 5)
  .orderBy("empCount", "desc");

async getUserProfile(userId: number) {
  const profile = await this.getPuri("r")
    .table("users")
    .join("employees", "users.id", "employees.user_id")
    .leftJoin("departments", "employees.department_id", "departments.id")
    .leftJoin("companies", "departments.company_id", "companies.id")
    .select({
      userId: "users.id",
      username: "users.username",
      email: "users.email",
      employeeNumber: "employees.employee_number",
      departmentName: "departments.name",
      companyName: "companies.name",
      salary: "employees.salary",
    })
    .where("users.id", userId)
    .first();

  if (!profile) {
    throw new Error("User not found");
  }

  return profile;
}

async getProjectDetails(projectId: number) {
  const project = await this.getPuri("r")
    .table("projects")
    .select({
      id: "projects.id",
      name: "projects.name",
      status: "projects.status",
      description: "projects.description",
    })
    .where("projects.id", projectId)
    .first();

  if (!project) {
    throw new Error("Project not found");
  }

  // 참여자 목록
  const members = await this.getPuri("r")
    .table("projects__employees")
    .join("employees", "projects__employees.employee_id", "employees.id")
    .join("users", "employees.user_id", "users.id")
    .leftJoin("departments", "employees.department_id", "departments.id")
    .select({
      employeeId: "employees.id",
      employeeName: "users.username",
      employeeNumber: "employees.employee_number",
      departmentName: "departments.name",
      role: "projects__employees.role",
    })
    .where("projects__employees.project_id", projectId);

  return {
    ...project,
    members,
  };
}

async getDepartmentHierarchy(companyId: number) {
  const departments = await this.getPuri("r")
    .table({ dept: "departments" })
    .leftJoin(
      { parent: "departments" },
      "dept.parent_id",
      "parent.id"
    )
    .leftJoin("companies", "dept.company_id", "companies.id")
    .select({
      id: "dept.id",
      name: "dept.name",
      parentId: "parent.id",
      parentName: "parent.name",
      companyName: "companies.name",
      level: Puri.rawNumber(`
        CASE
          WHEN dept.parent_id IS NULL THEN 0
          WHEN parent.parent_id IS NULL THEN 1
          ELSE 2
        END
      `),
    })
    .where("dept.company_id", companyId)
    .orderBy("level", "asc")
    .orderBy("dept.name", "asc");

  return departments;
}

const results = await db
  .table("employees")
  .join("users", "employees.user_id", "users.id")
  .leftJoin("departments", "employees.department_id", "departments.id")
  .select({
    employeeId: "employees.id", // number
    userName: "users.username", // string
    departmentName: "departments.name", // string | null (LEFT JOIN)
  });

// 타입이 자동으로 추론됨
const first = results[0];
first.employeeId; // number
first.userName; // string
first.departmentName; // string | null

-- 조인 키에 인덱스 생성
CREATE INDEX idx_employees_user_id ON employees(user_id);
CREATE INDEX idx_employees_department_id ON employees(department_id);

// ❌ 나쁨: 모든 컬럼 조회
await db.table("employees").join("users", "employees.user_id", "users.id").selectAll();

// ✅ 좋음: 필요한 컬럼만 선택
await db.table("employees").join("users", "employees.user_id", "users.id").select({
  id: "employees.id",
  name: "users.username",
});

// 작은 테이블 → 큰 테이블 순서
await db
  .table("departments")        // 10개
  .join("employees", ...)      // 100개
  .join("projects__employees", ...); // 1000개