/**
 * Tầng truy vấn báo cáo dùng chung cho M4 (báo cáo ngày/tuần/tháng) và
 * M5 (phân tích). Ba tầng báo cáo dùng chung bộ chỉ số, chỉ khác khoảng ngày —
 * nên tất cả nhận vào `{ start, end }` thay vì tự quyết định kỳ.
 */
import "server-only";
import { and, eq, gte, lte, or, sql, desc, inArray, type SQL } from "drizzle-orm";
import { db } from "@/db";
import {
  orders,
  orderLines,
  customers,
  products,
  users,
  territories,
  channels,
  targets,
  programs,
  events,
  kpiMonthly,
  customerClassifications,
  dealerInactiveNotes,
  departments,
  orderPayments,
} from "@/db/schema";
import type { SessionUser } from "./auth";
import {
  visibleTerritoryIds,
  visibleUserIds,
  visibleDepartmentIds,
  assignedTerritoryIds,
  scopeFilter,
  departmentSubtree,
  expandTerritoryDescendants,
} from "./rbac";
import { periodRange, monthProgress, monthsInPeriod } from "./period";
import { isSpecialist, specialistDepartmentIds } from "./specialist";
import { vnDate } from "./sql-dates";

export type DateRange = { start: string; end: string };

/** UUID không bao giờ tồn tại — dùng khi danh sách lọc rỗng để điều kiện `IN` luôn sai. */
const NULL_ID = "00000000-0000-0000-0000-000000000000";

/** Bộ chỉ số lõi — giống nhau ở cả ba tầng báo cáo ngày/tuần/tháng. */
export type CoreMetrics = {
  revenue: number;
  promoValue: number;
  orderCount: number;
  skuCount: number;
  dealerCount: number;
  newCustomerCount: number;
};

/**
 * Điều kiện giới hạn đơn hàng theo vai trò người xem.
 *
 * Quản lý thấy đơn của mọi nhân viên trong nhánh phòng ban mình phụ trách. Nếu
 * chưa được gán phòng ban thì lùi về phạm vi khu vực như trước, để dữ liệu đang
 * chạy không đứt trong lúc chuyển đổi cơ cấu.
 */
/**
 * `confirmed`: đơn đã duyệt (doanh thu). `active`: đơn đã lập chưa huỷ, gồm cả chờ duyệt (doanh số).
 */
export type OrderScopeMode = "confirmed" | "active";

async function baseOrderScope(session: SessionUser, mode: OrderScopeMode = "confirmed"): Promise<SQL | undefined> {
  const base = and(
    eq(orders.tenantId, session.tenantId),
    mode === "active" ? sql`${orders.status} <> 'CANCELLED'` : eq(orders.status, "CONFIRMED"),
  );
  if (session.role === "DIRECTOR") return base;

  if (session.role === "SALES") {
    return and(base, eq(orders.salesUserId, session.userId));
  }

  const userIds = await visibleUserIds(session);
  if (userIds) {
    return and(
      base,
      userIds.length > 0 ? inArray(orders.salesUserId, userIds) : sql`false`,
    );
  }

  const territoryIds = await visibleTerritoryIds(session);
  if (!territoryIds || territoryIds.length === 0) {
    return and(base, sql`false`);
  }
  return and(base, inArray(orders.territoryId, territoryIds));
}

/**
 * Phạm vi đơn hàng của người xem, thu hẹp thêm theo bộ chọn khu vực / phòng ban / nhân viên (nếu có).
 * Điều kiện thu hẹp luôn được AND thêm vào phạm vi quyền gốc nên không thể mở rộng quyền xem:
 * chọn một khu vực ngoài phạm vi của mình chỉ cho ra kết quả rỗng.
 */
export async function orderScope(session: SessionUser, mode: OrderScopeMode = "confirmed"): Promise<SQL | undefined> {
  const base = await baseOrderScope(session, mode);
  const focus = session.focus;
  if (!focus) return base;
  if (focus.type === "user") return and(base, eq(orders.salesUserId, focus.id));
  if (focus.type === "dept") {
    const ids = await departmentSubtree(session.tenantId, [focus.id]);
    return and(base, ids.length > 0 ? inArray(orders.departmentId, ids) : sql`false`);
  }
  const ids = await expandTerritoryDescendants(session.tenantId, [focus.id]);
  return and(base, ids.length > 0 ? inArray(orders.territoryId, ids) : sql`false`);
}

/** Phạm vi khách hàng theo vai trò (giống danh sách khách) + bộ chọn thu hẹp — dùng cho các số liệu tính trên khách. */
export async function customerScope(session: SessionUser): Promise<SQL | undefined> {
  // Khách đã ngưng (xoá mềm) không tính vào bất kỳ số liệu nào — danh sách khách hàng cũng ẩn họ.
  const base = and(
    await scopeFilter(session, {
      tenantId: customers.tenantId,
      ownerId: customers.ownerId,
      territoryId: customers.territoryId,
    }),
    eq(customers.isActive, true),
  );
  const focus = session.focus;
  if (!focus) return base;
  if (focus.type === "user") return and(base, eq(customers.ownerId, focus.id));
  if (focus.type === "dept") {
    const deptIds = await departmentSubtree(session.tenantId, [focus.id]);
    const members =
      deptIds.length > 0
        ? await db
            .select({ id: users.id })
            .from(users)
            .where(and(eq(users.tenantId, session.tenantId), inArray(users.departmentId, deptIds)))
        : [];
    return and(base, members.length > 0 ? inArray(customers.ownerId, members.map((m) => m.id)) : sql`false`);
  }
  const ids = await expandTerritoryDescendants(session.tenantId, [focus.id]);
  return and(base, ids.length > 0 ? inArray(customers.territoryId, ids) : sql`false`);
}

/**
 * Danh sách nhân viên người xem được thấy trong báo cáo, đã thu hẹp theo bộ chọn phạm vi (khu vực / phòng ban / nhân viên).
 * `null` = không giới hạn (Ban giám đốc, không chọn phạm vi). Bảng theo NHÂN VIÊN (KPI, xếp hạng, định biên…) phải đi qua đây —
 * chỉ lọc theo tenant thì mọi vai trò đều thấy KPI của cả công ty.
 */
export async function scopedUserIds(session: SessionUser): Promise<string[] | null> {
  const base = await visibleUserIds(session);
  const focus = session.focus;
  if (!focus) return base;

  let focused: string[];
  if (focus.type === "user") {
    focused = [focus.id];
  } else if (focus.type === "dept") {
    const deptIds = await departmentSubtree(session.tenantId, [focus.id]);
    const rows =
      deptIds.length > 0
        ? await db.select({ id: users.id }).from(users).where(and(eq(users.tenantId, session.tenantId), inArray(users.departmentId, deptIds)))
        : [];
    focused = rows.map((r) => r.id);
  } else {
    const area = await expandTerritoryDescendants(session.tenantId, [focus.id]);
    const rows =
      area.length > 0
        ? await db
            .select({ id: users.id })
            .from(users)
            .where(
              and(
                eq(users.tenantId, session.tenantId),
                or(inArray(users.territoryId, area), sql`${users.id} in (select user_id from user_territories where territory_id in (${sql.join(area.map((id) => sql`${id}`), sql`, `)}))`),
              ),
            )
        : [];
    focused = rows.map((r) => r.id);
  }
  return base ? focused.filter((id) => base.includes(id)) : focused;
}

export async function coreMetrics(session: SessionUser, range: DateRange): Promise<CoreMetrics> {
  const scope = await orderScope(session);
  const dateFilter = and(gte(orders.orderDate, range.start), lte(orders.orderDate, range.end));

  const [totals] = await db
    .select({
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      promoValue: sql<number>`coalesce(sum(${orders.promoValue}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
      dealerCount: sql<number>`count(distinct ${orders.customerId})::int`,
    })
    .from(orders)
    .where(and(scope, dateFilter));

  const [skus] = await db
    .select({ skuCount: sql<number>`count(distinct ${orderLines.productId})::int` })
    .from(orderLines)
    .innerJoin(orders, eq(orderLines.orderId, orders.id))
    .where(and(scope, dateFilter, sql`${orderLines.qtySold} > 0`));

  const [newCustomers] = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(customers)
    .where(
      and(
        eq(customers.tenantId, session.tenantId),
        await customerScope(session),
        gte(vnDate(customers.createdAt), range.start),
        lte(vnDate(customers.createdAt), range.end),
      ),
    );

  return {
    revenue: Number(totals?.revenue ?? 0),
    promoValue: Number(totals?.promoValue ?? 0),
    orderCount: Number(totals?.orderCount ?? 0),
    skuCount: Number(skus?.skuCount ?? 0),
    dealerCount: Number(totals?.dealerCount ?? 0),
    newCustomerCount: Number(newCustomers?.count ?? 0),
  };
}

/** Doanh thu theo khu vực — dùng cho bảng báo cáo và biểu đồ tỷ trọng thị trường. */
export async function revenueByTerritory(session: SessionUser, range: DateRange) {
  const scope = await orderScope(session);
  return db
    .select({
      territoryId: orders.territoryId,
      territoryName: sql<string>`coalesce(${territories.name}, 'Chưa phân khu vực')`,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
      customerCount: sql<number>`count(distinct ${orders.customerId})::int`,
    })
    .from(orders)
    .leftJoin(territories, eq(orders.territoryId, territories.id))
    .where(and(scope, gte(orders.orderDate, range.start), lte(orders.orderDate, range.end)))
    .groupBy(orders.territoryId, territories.name)
    .orderBy(desc(sql`sum(${orders.totalAmount})`));
}

/** Doanh thu theo NVKD — nền cho định biên doanh thu/NVKD ở M5. */
export async function revenueBySalesUser(session: SessionUser, range: DateRange) {
  const scope = await orderScope(session);
  return db
    .select({
      userId: orders.salesUserId,
      fullName: sql<string>`coalesce(${users.fullName}, 'Không xác định')`,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
      customerCount: sql<number>`count(distinct ${orders.customerId})::int`,
    })
    .from(orders)
    .leftJoin(users, eq(orders.salesUserId, users.id))
    .where(and(scope, gte(orders.orderDate, range.start), lte(orders.orderDate, range.end)))
    .groupBy(orders.salesUserId, users.fullName)
    .orderBy(desc(sql`sum(${orders.totalAmount})`));
}

/** Doanh thu theo sản phẩm — tỷ trọng đóng góp từng SP (M5). */
export async function revenueByProduct(session: SessionUser, range: DateRange, limit = 50) {
  const scope = await orderScope(session);
  return db
    .select({
      productId: orderLines.productId,
      code: products.code,
      name: products.name,
      role: products.role,
      qtySold: sql<number>`coalesce(sum(${orderLines.qtySold}), 0)::int`,
      qtyPromo: sql<number>`coalesce(sum(${orderLines.qtyPromo}), 0)::int`,
      revenue: sql<number>`coalesce(sum(${orderLines.amount}), 0)::bigint`,
      customerCount: sql<number>`count(distinct ${orders.customerId})::int`,
    })
    .from(orderLines)
    .innerJoin(orders, eq(orderLines.orderId, orders.id))
    .innerJoin(products, eq(orderLines.productId, products.id))
    .where(and(scope, gte(orders.orderDate, range.start), lte(orders.orderDate, range.end)))
    .groupBy(orderLines.productId, products.code, products.name, products.role)
    .orderBy(desc(sql`sum(${orderLines.amount})`))
    .limit(limit);
}

/**
 * Hàng khuyến mãi đã dùng theo từng NVKD, chi tiết theo mặt hàng — khách yêu cầu bổ
 * sung khi trả lời câu hỏi "ngân sách khuyến mãi": không có ngân sách, khuyến mãi luôn
 * quy ra hàng (không hoàn tiền mặt), nên cái cần theo dõi là NVKD nào đã cho đi bao
 * nhiêu hàng KM, mặt hàng gì, trong kỳ đang xem.
 */
export async function promoGoodsByStaff(session: SessionUser, range: DateRange) {
  const scope = await orderScope(session);
  return db
    .select({
      salesUserId: orders.salesUserId,
      salesUserName: sql<string>`coalesce(${users.fullName}, 'Không xác định')`,
      productId: orderLines.productId,
      code: products.code,
      name: products.name,
      qtyPromo: sql<number>`coalesce(sum(${orderLines.qtyPromo}), 0)::int`,
      promoValue: sql<number>`coalesce(sum(${orderLines.qtyPromo} * ${orderLines.unitPrice}), 0)::bigint`,
    })
    .from(orderLines)
    .innerJoin(orders, eq(orderLines.orderId, orders.id))
    .innerJoin(products, eq(orderLines.productId, products.id))
    .leftJoin(users, eq(orders.salesUserId, users.id))
    .where(and(scope, gte(orders.orderDate, range.start), lte(orders.orderDate, range.end)))
    .groupBy(orders.salesUserId, users.fullName, orderLines.productId, products.code, products.name)
    .having(sql`coalesce(sum(${orderLines.qtyPromo}), 0) > 0`)
    .orderBy(desc(sql`sum(${orderLines.qtyPromo} * ${orderLines.unitPrice})`));
}

/** Doanh thu theo từng ngày trong khoảng — dữ liệu cho biểu đồ đường. */
export async function revenueByDay(session: SessionUser, range: DateRange) {
  const scope = await orderScope(session);
  return db
    .select({
      day: sql<string>`${orders.orderDate}::text`,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
    })
    .from(orders)
    .where(and(scope, gte(orders.orderDate, range.start), lte(orders.orderDate, range.end)))
    .groupBy(orders.orderDate)
    .orderBy(orders.orderDate);
}

/** Doanh thu theo tháng trong một năm — biểu đồ xu hướng. */
export async function revenueByMonth(session: SessionUser, year: string) {
  const scope = await orderScope(session);
  const { start, end } = periodRange(year);
  return db
    .select({
      month: sql<string>`to_char(${orders.orderDate}, 'YYYY-MM')`,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
    })
    .from(orders)
    .where(and(scope, gte(orders.orderDate, start), lte(orders.orderDate, end)))
    .groupBy(sql`to_char(${orders.orderDate}, 'YYYY-MM')`)
    .orderBy(sql`to_char(${orders.orderDate}, 'YYYY-MM')`);
}

/**
 * Đại lý có / không lấy hàng trong kỳ, so với toàn bộ đại lý đang quản lý
 * (yêu cầu cuối mục 4).
 */
export async function dealerActivity(
  session: SessionUser,
  range: DateRange,
  /** Kỳ tháng để lấy phân hạng và lý do không lấy hàng; mặc định theo ngày cuối kỳ. */
  periodKey = range.end.slice(0, 7),
) {
  const scope = await orderScope(session);

  const purchased = await db
    .select({
      customerId: orders.customerId,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
    })
    .from(orders)
    .where(and(scope, gte(orders.orderDate, range.start), lte(orders.orderDate, range.end)))
    .groupBy(orders.customerId);

  const purchasedMap = new Map(purchased.map((r) => [r.customerId, r]));

  /* Số SP và phân loại ĐL lấy từ ảnh chụp phân hạng của kỳ — yêu cầu mục 4 đòi
     bảng này phải có "chi tiết doanh thu tháng, số SP & phân loại ĐL". */
  const allDealers = await db
    .select({
      id: customers.id,
      code: customers.code,
      orgName: customers.orgName,
      territoryId: customers.territoryId,
      territoryName: territories.name,
      ownerId: customers.ownerId,
      ownerName: users.fullName,
      isKey: customers.isKey,
      revenueTier: customerClassifications.revenueTier,
      skuCount: customerClassifications.skuCount,
      monthsInactive: customerClassifications.monthsInactive,
    })
    .from(customers)
    .leftJoin(territories, eq(customers.territoryId, territories.id))
    .leftJoin(users, eq(customers.ownerId, users.id))
    .leftJoin(
      customerClassifications,
      and(
        eq(customerClassifications.customerId, customers.id),
        eq(customerClassifications.periodKey, periodKey),
      ),
    )
    .where(
      and(
        eq(customers.tenantId, session.tenantId),
        eq(customers.stage, "DEALER"),
        eq(customers.isActive, true),
        await customerScope(session),
      ),
    );

  const notes = await db
    .select({ customerId: dealerInactiveNotes.customerId, reason: dealerInactiveNotes.reason })
    .from(dealerInactiveNotes)
    .where(
      and(
        eq(dealerInactiveNotes.tenantId, session.tenantId),
        eq(dealerInactiveNotes.periodKey, periodKey),
      ),
    );

  const reasonMap = new Map(notes.map((n) => [n.customerId, n.reason]));

  const active = [];
  const inactive = [];
  for (const dealer of allDealers) {
    const row = purchasedMap.get(dealer.id);
    const base = {
      ...dealer,
      skuCount: dealer.skuCount ?? 0,
      monthsInactive: dealer.monthsInactive ?? 0,
      reason: reasonMap.get(dealer.id) ?? null,
    };
    if (row) {
      active.push({ ...base, revenue: Number(row.revenue), orderCount: Number(row.orderCount) });
    } else {
      inactive.push({ ...base, revenue: 0, orderCount: 0 });
    }
  }
  return { total: allDealers.length, active, inactive, periodKey };
}

/**
 * Doanh thu theo phòng ban (yêu cầu Mediworld mục 6, 7).
 *
 * Gộp theo `orders.departmentId` — cột được sao chép tại thời điểm chốt đơn, nên
 * nhân viên chuyển phòng ban không làm đổi số liệu lịch sử của phòng ban cũ.
 */
export async function revenueByDepartment(session: SessionUser, range: DateRange) {
  const scope = await orderScope(session);
  return db
    .select({
      departmentId: orders.departmentId,
      departmentName: sql<string>`coalesce(${departments.name}, 'Chưa phân phòng ban')`,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
      customerCount: sql<number>`count(distinct ${orders.customerId})::int`,
      staffCount: sql<number>`count(distinct ${orders.salesUserId})::int`,
    })
    .from(orders)
    .leftJoin(departments, eq(orders.departmentId, departments.id))
    .where(and(scope, gte(orders.orderDate, range.start), lte(orders.orderDate, range.end)))
    .groupBy(orders.departmentId, departments.name)
    .orderBy(desc(sql`sum(${orders.totalAmount})`));
}

/**
 * Bảng xếp hạng doanh số nhân viên (yêu cầu Mediworld mục 1).
 *
 * Không cần chặn thêm gì để "mỗi miền không thấy của nhau" — `orderScope` đã giới
 * hạn theo nhánh phòng ban của người xem, nên quản lý miền Bắc chỉ thấy người
 * miền Bắc trong bảng này.
 */
export type LeaderboardSort = "doanhthu" | "doanhso" | "no" | "moi";

export async function salesLeaderboard(session: SessionUser, range: DateRange, sortBy: LeaderboardSort = "doanhthu") {
  const scope = await orderScope(session, "confirmed");
  const activeScope = await orderScope(session, "active");
  const dateFilter = and(gte(orders.orderDate, range.start), lte(orders.orderDate, range.end));

  const rows = await db
    .select({
      userId: orders.salesUserId,
      fullName: sql<string>`coalesce(${users.fullName}, 'Không xác định')`,
      departmentName: sql<string>`coalesce(${departments.name}, '—')`,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
      customerCount: sql<number>`count(distinct ${orders.customerId})::int`,
    })
    .from(orders)
    .leftJoin(users, eq(orders.salesUserId, users.id))
    .leftJoin(departments, eq(orders.departmentId, departments.id))
    .where(and(scope, dateFilter))
    .groupBy(orders.salesUserId, users.fullName, departments.name)
    .orderBy(desc(sql`sum(${orders.totalAmount})`));

  /* Bảng xếp hạng phải có MỌI NVKD trong phạm vi, kể cả người chưa có đơn đã duyệt trong kỳ (đang có đơn chờ duyệt, hoặc chỉ mở khách mới):
     nếu chỉ lấy người có doanh thu thì xếp theo "Doanh số" hay "Mở mới khách" sẽ bỏ sót đúng những người đó. */
  const visible = await scopedUserIds(session);
  const cmDepartments = await specialistDepartmentIds(session.tenantId);
  const staffRows = await db
    .select({
      id: users.id,
      fullName: users.fullName,
      jobTitle: users.jobTitle,
      departmentId: users.departmentId,
      departmentName: sql<string>`coalesce(${departments.name}, '—')`,
    })
    .from(users)
    .leftJoin(departments, eq(users.departmentId, departments.id))
    .where(
      and(
        eq(users.tenantId, session.tenantId),
        eq(users.isActive, true),
        eq(users.role, "SALES"),
        visible ? inArray(users.id, visible.length > 0 ? visible : [NULL_ID]) : undefined,
      ),
    );
  const inRows = new Set(rows.map((row) => row.userId));
  const zeroRows = staffRows
    .filter((s) => !inRows.has(s.id) && !isSpecialist(s, cmDepartments))
    .map((s) => ({ userId: s.id, fullName: s.fullName, departmentName: s.departmentName, revenue: 0, orderCount: 0, customerCount: 0 }));
  const allRows = [...rows, ...zeroRows];

  const userIds = allRows.map((row) => row.userId).filter((id): id is string => !!id);

  /* Doanh số, công nợ và khách mở mới đều đi qua ĐÚNG phạm vi của các số tổng (`orderScope` / `customerScope`, gồm cả bộ chọn thu hẹp),
     nên cộng cột của bảng khớp với thẻ tổng ở Báo cáo. Trước đây chúng chỉ lọc theo danh sách người nên lệch khi đang thu hẹp theo khu vực/phòng. */
  const [salesRows, paidRows, surchargeRows, newRows, people] = await Promise.all([
    db
      .select({ userId: orders.salesUserId, sales: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint` })
      .from(orders)
      .where(and(activeScope, dateFilter))
      .groupBy(orders.salesUserId),
    db
      .select({ userId: orders.salesUserId, paid: sql<number>`coalesce(sum(${orderPayments.amount}), 0)::bigint` })
      .from(orderPayments)
      .innerJoin(orders, eq(orderPayments.orderId, orders.id))
      .where(and(scope, dateFilter))
      .groupBy(orders.salesUserId),
    db
      .select({ userId: orders.salesUserId, surcharge: sql<number>`coalesce(sum(${orders.surchargeAmount}), 0)::bigint` })
      .from(orders)
      .where(and(scope, dateFilter))
      .groupBy(orders.salesUserId),
    db
      .select({ userId: customers.ownerId, count: sql<number>`count(*)::int` })
      .from(customers)
      .where(
        and(
          eq(customers.tenantId, session.tenantId),
          await customerScope(session),
          sql`${vnDate(customers.createdAt)} >= ${range.start}::date`,
          sql`${vnDate(customers.createdAt)} <= ${range.end}::date`,
        ),
      )
      .groupBy(customers.ownerId),
    // Thông tin hiển thị của từng người (ảnh, chức vụ, khu vực) để danh sách giống danh sách nhân viên.
    userIds.length > 0
      ? db
          .select({ id: users.id, avatar: users.avatarDataUrl, jobTitle: users.jobTitle, territoryName: territories.name })
          .from(users)
          .leftJoin(territories, eq(users.territoryId, territories.id))
          .where(and(eq(users.tenantId, session.tenantId), inArray(users.id, userIds)))
      : Promise.resolve([]),
  ]);
  const salesMap = new Map(salesRows.map((r) => [r.userId, Number(r.sales)]));
  const paidMap = new Map(paidRows.map((r) => [r.userId, Number(r.paid)]));
  const surchargeMap = new Map(surchargeRows.map((r) => [r.userId, Number(r.surcharge)]));
  const newMap = new Map(newRows.map((r) => [r.userId, Number(r.count)]));
  const peopleMap = new Map(people.map((p) => [p.id, p]));

  const total = allRows.reduce((sum, row) => sum + Number(row.revenue), 0);

  const enriched = allRows.map((row) => ({
    userId: row.userId,
    fullName: row.fullName,
    departmentName: row.departmentName,
    jobTitle: peopleMap.get(row.userId ?? "")?.jobTitle ?? null,
    territoryName: peopleMap.get(row.userId ?? "")?.territoryName ?? null,
    avatar: peopleMap.get(row.userId ?? "")?.avatar ?? null,
    revenue: Number(row.revenue),
    sales: salesMap.get(row.userId) ?? 0,
    // Công nợ = tiền hàng + phụ thu phí − đã thu (cùng công thức "Phải thu" của đơn hàng).
    debt: Math.max(0, Number(row.revenue) + (surchargeMap.get(row.userId) ?? 0) - (paidMap.get(row.userId) ?? 0)),
    newCustomers: newMap.get(row.userId) ?? 0,
    orderCount: Number(row.orderCount),
    customerCount: Number(row.customerCount),
    sharePct: total > 0 ? (Number(row.revenue) / total) * 100 : 0,
  }));

  const key = { doanhthu: "revenue", doanhso: "sales", no: "debt", moi: "newCustomers" }[sortBy] as
    | "revenue"
    | "sales"
    | "debt"
    | "newCustomers";
  // Cao → thấp theo tiêu chí đang chọn; bằng nhau thì người doanh thu cao hơn đứng trước, rồi theo tên.
  enriched.sort((a, b) => b[key] - a[key] || b.revenue - a.revenue || a.fullName.localeCompare(b.fullName, "vi"));

  // Đồng hạng: cùng giá trị thì cùng thứ hạng (1, 2, 2, 4…) — không ai bị xếp thấp hơn người bằng điểm với mình.
  let rank = 0;
  let previous: number | null = null;
  return enriched.map((row, index) => {
    if (previous === null || row[key] !== previous) rank = index + 1;
    previous = row[key];
    return { rank, ...row };
  });
}

/* -------------------------------------------------------------------------- */
/* Công nợ                                                                    */
/* -------------------------------------------------------------------------- */

export type DebtSummary = {
  revenue: number;
  /** Phụ thu phí giao hàng của các đơn đã duyệt — khách phải trả nhưng không tính vào doanh thu sản phẩm. */
  surcharge: number;
  collected: number;
  outstanding: number;
  overdue: number;
  collectionRate: number | null;
};

/**
 * Tổng hợp công nợ trong kỳ.
 *
 *   Doanh thu    = tổng tiền hàng các đơn đã duyệt
 *   Phải thu     = doanh thu + phụ thu phí (cùng cách tính với "Phải thu" ở trang đơn hàng)
 *   Đã thu       = tổng các lần thanh toán ghi nhận cho những đơn đó
 *   Còn phải thu = phải thu − đã thu
 *   Quá hạn      = phần còn phải thu của các đơn đã qua hạn thanh toán
 *
 * Cố ý không dựng hệ thống định khoản: khách chỉ cần biết đơn nào còn nợ và nợ
 * bao lâu. Muốn đối chiếu sổ sách đầy đủ thì đồng bộ với phần mềm kế toán.
 */
export async function debtSummary(
  session: SessionUser,
  range: DateRange,
  asOf: string,
): Promise<DebtSummary> {
  const scope = await orderScope(session);
  const dateFilter = and(gte(orders.orderDate, range.start), lte(orders.orderDate, range.end));

  const [totals] = await db
    .select({
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      surcharge: sql<number>`coalesce(sum(${orders.surchargeAmount}), 0)::bigint`,
    })
    .from(orders)
    .where(and(scope, dateFilter));

  const [collected] = await db
    .select({ amount: sql<number>`coalesce(sum(${orderPayments.amount}), 0)::bigint` })
    .from(orderPayments)
    .innerJoin(orders, eq(orderPayments.orderId, orders.id))
    .where(and(scope, dateFilter));

  /* Quá hạn tính trên từng đơn: phần chưa thu của những đơn có hạn thanh toán đã
     trôi qua. Phải gộp thanh toán theo đơn trước rồi mới trừ — cộng dồn toàn bộ
     rồi trừ một lần sẽ cho ra số âm ở đơn trả thừa và làm sai tổng.
     Dùng JOIN + GROUP BY thay cho truy vấn con tương quan: khi câu lệnh chỉ có một bảng, Drizzle
     ghi `"id"` không kèm tên bảng và Postgres hiểu là cột của bảng con → bỏ qua mọi khoản đã thu. */
  const [overdue] = await db
    .select({
      amount: sql<number>`coalesce(sum(greatest(remaining, 0)), 0)::bigint`,
    })
    .from(
      db
        .select({
          remaining: sql<number>`(${orders.totalAmount} + ${orders.surchargeAmount}) - coalesce(sum(${orderPayments.amount}), 0)`.as("remaining"),
        })
        .from(orders)
        .leftJoin(orderPayments, eq(orderPayments.orderId, orders.id))
        .where(
          and(
            scope,
            dateFilter,
            sql`${orders.dueDate} is not null`,
            sql`${orders.dueDate} < ${asOf}`,
          ),
        )
        .groupBy(orders.id)
        .as("overdue_orders"),
    );

  const revenue = Number(totals?.revenue ?? 0);
  const surcharge = Number(totals?.surcharge ?? 0);
  const payable = revenue + surcharge;
  const collectedAmount = Number(collected?.amount ?? 0);

  return {
    revenue,
    surcharge,
    collected: collectedAmount,
    outstanding: payable - collectedAmount,
    overdue: Number(overdue?.amount ?? 0),
    collectionRate: payable > 0 ? (collectedAmount / payable) * 100 : null,
  };
}

/**
 * Chỉ số theo từng khách hàng dùng cho danh sách khách hàng:
 * tổng doanh thu, số đơn, ngày và giá trị đơn gần nhất, công nợ còn lại.
 */
export type CustomerMetrics = {
  totalRevenue: number;
  orderCount: number;
  lastOrderDate: string | null;
  lastOrderValue: number;
  outstanding: number;
};

/**
 * Doanh số / doanh thu / số đơn của từng khách trong một khoảng ngày — dùng để xếp hạng
 * khách hàng theo tuần/tháng/quý/năm. Doanh số = đơn đã lập chưa huỷ (kể cả chờ duyệt),
 * doanh thu = đơn đã duyệt.
 */
export type CustomerPeriodStats = { sales: number; revenue: number; orderCount: number };

export async function customerPeriodStats(
  session: SessionUser,
  customerIds: string[],
  range: { start: string; end: string },
): Promise<Map<string, CustomerPeriodStats>> {
  const result = new Map<string, CustomerPeriodStats>();
  if (customerIds.length === 0) return result;

  const rows = await db
    .select({
      customerId: orders.customerId,
      sales: sql<number>`coalesce(sum(${orders.totalAmount}) filter (where ${orders.status} <> 'CANCELLED'), 0)::bigint`,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}) filter (where ${orders.status} = 'CONFIRMED'), 0)::bigint`,
      orderCount: sql<number>`(count(*) filter (where ${orders.status} <> 'CANCELLED'))::int`,
    })
    .from(orders)
    .where(
      and(
        eq(orders.tenantId, session.tenantId),
        inArray(orders.customerId, customerIds),
        gte(orders.orderDate, range.start),
        lte(orders.orderDate, range.end),
      ),
    )
    .groupBy(orders.customerId);

  for (const row of rows) {
    result.set(row.customerId, {
      sales: Number(row.sales),
      revenue: Number(row.revenue),
      orderCount: Number(row.orderCount),
    });
  }
  return result;
}

export async function customerMetrics(
  session: SessionUser,
  customerIds: string[],
): Promise<Map<string, CustomerMetrics>> {
  const result = new Map<string, CustomerMetrics>();
  if (customerIds.length === 0) return result;

  const rows = await db
    .select({
      customerId: orders.customerId,
      totalRevenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      // Phải thu = tiền hàng + phụ thu phí (khớp "Phải thu" của từng đơn); doanh thu vẫn chỉ tính tiền hàng.
      totalPayable: sql<number>`coalesce(sum(${orders.totalAmount} + ${orders.surchargeAmount}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
      lastOrderDate: sql<string>`max(${orders.orderDate})::text`,
    })
    .from(orders)
    .where(
      and(
        eq(orders.tenantId, session.tenantId),
        eq(orders.status, "CONFIRMED"),
        inArray(orders.customerId, customerIds),
      ),
    )
    .groupBy(orders.customerId);

  /* Giá trị đơn gần nhất phải lấy riêng: `max(orderDate)` cho ra ngày, không cho
     ra số tiền của đúng đơn đó. Dùng DISTINCT ON để lấy đơn mới nhất mỗi khách. */
  const latest = await db.execute(sql`
    select distinct on (customer_id) customer_id, total_amount
    from orders
    where tenant_id = ${session.tenantId}
      and status = 'CONFIRMED'
      and customer_id in (${sql.join(customerIds.map((id) => sql`${id}`), sql`, `)})
    order by customer_id, order_date desc, created_at desc
  `);

  const latestMap = new Map<string, number>();
  for (const row of latest.rows as { customer_id: string; total_amount: string | number }[]) {
    latestMap.set(row.customer_id, Number(row.total_amount));
  }

  const paid = await db
    .select({
      customerId: orders.customerId,
      amount: sql<number>`coalesce(sum(${orderPayments.amount}), 0)::bigint`,
    })
    .from(orderPayments)
    .innerJoin(orders, eq(orderPayments.orderId, orders.id))
    // Chỉ tính tiền đã thu của đơn ĐÃ DUYỆT: đặt cọc ở đơn còn chờ duyệt / đã huỷ không được trừ vào nợ của đơn khác.
    .where(and(eq(orders.tenantId, session.tenantId), eq(orders.status, "CONFIRMED"), inArray(orders.customerId, customerIds)))
    .groupBy(orders.customerId);

  const paidMap = new Map(paid.map((row) => [row.customerId, Number(row.amount)]));

  for (const row of rows) {
    const totalRevenue = Number(row.totalRevenue);
    result.set(row.customerId, {
      totalRevenue,
      orderCount: Number(row.orderCount),
      lastOrderDate: row.lastOrderDate ?? null,
      lastOrderValue: latestMap.get(row.customerId) ?? 0,
      outstanding: Number(row.totalPayable) - (paidMap.get(row.customerId) ?? 0),
    });
  }

  return result;
}

/**
 * Thực hiện so với kế hoạch theo từng khách hàng (yêu cầu mục 5:
 * "Doanh thu thực hiện so với kế hoạch: từng KV, khách hàng, NVKD").
 *
 * Chỉ liệt kê những khách đã được giao chỉ tiêu — giao chỉ tiêu tới từng đại lý là
 * việc có chọn lọc, không phải làm cho toàn bộ danh sách.
 */
export async function customerTargetProgress(session: SessionUser, periodKey: string) {
  const monthKeys = monthsInPeriod(periodKey);
  const range = periodRange(periodKey);

  const targetRows = await db
    .select({
      customerId: targets.scopeId,
      revenueTarget: sql<number>`coalesce(sum(${targets.revenueTarget}), 0)::bigint`,
    })
    .from(targets)
    .where(
      and(
        eq(targets.tenantId, session.tenantId),
        eq(targets.scopeType, "CUSTOMER"),
        inArray(targets.periodKey, monthKeys),
      ),
    )
    .groupBy(targets.scopeId);

  const withId = targetRows.filter((row) => row.customerId);
  if (withId.length === 0) return [];

  const customerIds = withId.map((row) => row.customerId!);
  const scope = await orderScope(session);

  const actuals = await db
    .select({
      customerId: orders.customerId,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      orderCount: sql<number>`count(*)::int`,
    })
    .from(orders)
    .where(
      and(
        scope,
        gte(orders.orderDate, range.start),
        lte(orders.orderDate, range.end),
        inArray(orders.customerId, customerIds),
      ),
    )
    .groupBy(orders.customerId);

  const actualMap = new Map(actuals.map((row) => [row.customerId, row]));

  const info = await db
    .select({
      id: customers.id,
      code: customers.code,
      orgName: customers.orgName,
      ownerName: users.fullName,
      territoryName: territories.name,
    })
    .from(customers)
    .leftJoin(users, eq(customers.ownerId, users.id))
    .leftJoin(territories, eq(customers.territoryId, territories.id))
    // Chỉ khách trong phạm vi người xem — không lộ đại lý và chỉ tiêu của người/khu vực khác.
    .where(and(eq(customers.tenantId, session.tenantId), inArray(customers.id, customerIds), await customerScope(session)));

  const infoMap = new Map(info.map((row) => [row.id, row]));

  return withId
    .map((row) => {
      const detail = infoMap.get(row.customerId!);
      const actual = actualMap.get(row.customerId!);
      const revenue = Number(actual?.revenue ?? 0);
      const revenueTarget = Number(row.revenueTarget);
      return {
        customerId: row.customerId!,
        code: detail?.code ?? "—",
        orgName: detail?.orgName ?? "Không xác định",
        ownerName: detail?.ownerName ?? null,
        territoryName: detail?.territoryName ?? null,
        revenue,
        revenueTarget,
        orderCount: Number(actual?.orderCount ?? 0),
        achievementPct: revenueTarget > 0 ? (revenue / revenueTarget) * 100 : null,
      };
    })
    .filter((row) => infoMap.has(row.customerId))
    .sort((a, b) => (b.achievementPct ?? 0) - (a.achievementPct ?? 0));
}

/**
 * Chỉ tiêu áp dụng cho người xem trong một kỳ; null nghĩa là chưa giao chỉ tiêu.
 *
 * Chỉ tiêu chỉ được giao theo tháng. Với kỳ quý hoặc năm, hàm này **cộng dồn chỉ
 * tiêu của các tháng nằm trong kỳ** thay vì đòi người dùng giao thêm một bộ chỉ
 * tiêu riêng cho quý — giao hai nơi thì sớm muộn cũng lệch nhau.
 */
export async function targetFor(
  session: SessionUser,
  periodKey: string,
): Promise<{ revenueTarget: number; orderTarget: number } | null> {
  const monthKeys = monthsInPeriod(periodKey);

  // Đang thu hẹp vào một khu vực / phòng ban / nhân viên: đối chiếu với chỉ tiêu giao đúng cho đối tượng đó.
  if (session.focus) {
    // Chỉ tiêu không đi qua bộ lọc phạm vi đơn hàng, nên phải tự chặn: đối tượng ngoài quyền của người xem thì không có chỉ tiêu để thấy.
    if (session.role !== "DIRECTOR") {
      const allowed =
        session.focus.type === "user"
          ? await visibleUserIds(session)
          : session.focus.type === "dept"
            ? await visibleDepartmentIds(session)
            : await visibleTerritoryIds(session);
      if (allowed && !allowed.includes(session.focus.id)) return null;
    }
    const scopeType = { user: "USER", dept: "DEPARTMENT", terr: "TERRITORY" }[session.focus.type] as
      | "USER"
      | "DEPARTMENT"
      | "TERRITORY";
    const rows = await db
      .select({ revenueTarget: targets.revenueTarget, orderTarget: targets.orderTarget })
      .from(targets)
      .where(
        and(
          eq(targets.tenantId, session.tenantId),
          inArray(targets.periodKey, monthKeys),
          eq(targets.scopeType, scopeType),
          eq(targets.scopeId, session.focus.id),
        ),
      );
    if (rows.length === 0) return null;
    return {
      revenueTarget: rows.reduce((sum, row) => sum + Number(row.revenueTarget), 0),
      orderTarget: rows.reduce((sum, row) => sum + Number(row.orderTarget), 0),
    };
  }

  /* Quản lý vùng đối chiếu với chỉ tiêu giao theo khu vực mình phụ trách, không
     phải chỉ tiêu toàn công ty — nếu không, đội nào cũng trông như đang chậm
     tiến độ dù đã vượt chỉ tiêu thật của chính mình. */
  if (session.role === "MANAGER" || session.role === "GDKV") {
    const territoryIds = await assignedTerritoryIds(session);
    if (territoryIds.length === 0) return null;

    const rows = await db
      .select({ revenueTarget: targets.revenueTarget, orderTarget: targets.orderTarget })
      .from(targets)
      .where(
        and(
          eq(targets.tenantId, session.tenantId),
          inArray(targets.periodKey, monthKeys),
          eq(targets.scopeType, "TERRITORY"),
          inArray(targets.scopeId, territoryIds),
        ),
      );

    if (rows.length === 0) return null;
    return {
      revenueTarget: rows.reduce((sum, row) => sum + Number(row.revenueTarget), 0),
      orderTarget: rows.reduce((sum, row) => sum + Number(row.orderTarget), 0),
    };
  }

  const scopeType = session.role === "SALES" ? "USER" : "COMPANY";
  const scopeId = session.role === "SALES" ? session.userId : null;

  const rows = await db
    .select({ revenueTarget: targets.revenueTarget, orderTarget: targets.orderTarget })
    .from(targets)
    .where(
      and(
        eq(targets.tenantId, session.tenantId),
        inArray(targets.periodKey, monthKeys),
        eq(targets.scopeType, scopeType),
        scopeId ? eq(targets.scopeId, scopeId) : sql`${targets.scopeId} is null`,
      ),
    );

  if (rows.length === 0) return null;
  return {
    revenueTarget: rows.reduce((sum, row) => sum + Number(row.revenueTarget), 0),
    orderTarget: rows.reduce((sum, row) => sum + Number(row.orderTarget), 0),
  };
}

/**
 * Các lớp/chuyên đề một nhân viên cụ thể đã phụ trách trong kỳ — dùng cho hồ sơ
 * nhân viên (yêu cầu Nhân viên & Chuyên môn: xem số lớp đã dạy theo kỳ, kèm ghi
 * chú báo cáo sau mỗi lớp thay vì trôi mất trong nhóm Zalo).
 *
 * Trả `null` nếu người xem không có quyền xem nhân viên này (không cùng nhánh
 * phòng ban và không phải chính họ) — trang gọi hàm này tự quyết định hiển thị
 * gì khi nhận `null`.
 */
export async function eventsByOwner(session: SessionUser, targetUserId: string, range: DateRange) {
  if (session.userId !== targetUserId) {
    const visibleIds = await visibleUserIds(session);
    if (visibleIds !== null && !visibleIds.includes(targetUserId)) return null;
  }

  const rows = await db
    .select({
      id: events.id,
      name: events.name,
      kind: events.kind,
      eventDate: events.eventDate,
      invitedCount: events.invitedCount,
      attendedCount: events.attendedCount,
      resultOrderCount: events.resultOrderCount,
      resultRevenue: events.resultRevenue,
      note: events.note,
      territoryName: territories.name,
    })
    .from(events)
    .leftJoin(territories, eq(events.territoryId, territories.id))
    .where(
      and(
        eq(events.tenantId, session.tenantId),
        eq(events.ownerId, targetUserId),
        gte(events.eventDate, range.start),
        lte(events.eventDate, range.end),
      ),
    )
    .orderBy(desc(events.eventDate));

  return rows.map((row) => ({
    ...row,
    resultRevenue: Number(row.resultRevenue),
    attendanceRate: row.invitedCount > 0 ? (row.attendedCount / row.invitedCount) * 100 : null,
  }));
}

/**
 * KPI của đội ngũ trong một kỳ bất kỳ.
 *
 * Bảng `kpi_monthly` lưu theo tháng, nên với kỳ quý/năm phải cộng dồn các tháng
 * rồi tính lại tỷ lệ hoàn thành trên tổng — không được lấy trung bình cộng của
 * các tỷ lệ tháng, vì các tháng có chỉ tiêu khác nhau thì trung bình đó sai.
 */
export async function kpiForPeriod(session: SessionUser, periodKey: string) {
  const monthKeys = monthsInPeriod(periodKey);
  const userIds = await scopedUserIds(session);

  const rows = await db
    .select({
      userId: kpiMonthly.userId,
      fullName: users.fullName,
      revenue: sql<number>`coalesce(sum(${kpiMonthly.revenue}), 0)::bigint`,
      revenueTarget: sql<number>`coalesce(sum(${kpiMonthly.revenueTarget}), 0)::bigint`,
      orderCount: sql<number>`coalesce(sum(${kpiMonthly.orderCount}), 0)::int`,
      visitCount: sql<number>`coalesce(sum(${kpiMonthly.visitCount}), 0)::int`,
      newCustomerCount: sql<number>`coalesce(sum(${kpiMonthly.newCustomerCount}), 0)::int`,
      activeDealerCount: sql<number>`coalesce(max(${kpiMonthly.activeDealerCount}), 0)::int`,
      planCompliancePct: sql<number>`coalesce(round(avg(${kpiMonthly.planCompliancePct})), 0)::int`,
    })
    .from(kpiMonthly)
    .innerJoin(users, eq(kpiMonthly.userId, users.id))
    .where(
      and(
        eq(kpiMonthly.tenantId, session.tenantId),
        inArray(kpiMonthly.periodKey, monthKeys),
        // Chỉ nhân viên trong phạm vi người xem — trước đây thiếu điều kiện này nên mọi vai trò đều thấy KPI cả công ty.
        userIds ? inArray(kpiMonthly.userId, userIds.length > 0 ? userIds : [NULL_ID]) : undefined,
      ),
    )
    .groupBy(kpiMonthly.userId, users.fullName)
    .orderBy(desc(sql`sum(${kpiMonthly.revenue})`));

  return rows.map((row) => {
    const revenue = Number(row.revenue);
    const revenueTarget = Number(row.revenueTarget);
    return {
      userId: row.userId,
      fullName: row.fullName,
      revenue,
      revenueTarget,
      achievementPct: revenueTarget > 0 ? Math.round((revenue / revenueTarget) * 100) : 0,
      orderCount: Number(row.orderCount),
      visitCount: Number(row.visitCount),
      planCompliancePct: Number(row.planCompliancePct),
      newCustomerCount: Number(row.newCustomerCount),
      activeDealerCount: Number(row.activeDealerCount),
    };
  });
}

/**
 * Danh sách khách hàng mở mới trong kỳ, kèm trị giá đơn và số sản phẩm đã lấy
 * (yêu cầu mục 4: "thông tin, kênh, trị giá đơn hàng, số SP").
 */
export async function newCustomerDetails(session: SessionUser, range: DateRange) {
  const rows = await db
    .select({
      id: customers.id,
      code: customers.code,
      orgName: customers.orgName,
      contactName: customers.contactName,
      phone: customers.phone,
      stage: customers.stage,
      createdAt: customers.createdAt,
      channelName: channels.name,
      territoryName: territories.name,
      ownerName: users.fullName,
    })
    .from(customers)
    .leftJoin(channels, eq(customers.channelId, channels.id))
    .leftJoin(territories, eq(customers.territoryId, territories.id))
    .leftJoin(users, eq(customers.ownerId, users.id))
    .where(
      and(
        eq(customers.tenantId, session.tenantId),
        await customerScope(session),
        gte(vnDate(customers.createdAt), range.start),
        lte(vnDate(customers.createdAt), range.end),
      ),
    )
    .orderBy(desc(customers.createdAt));

  if (rows.length === 0) return [];

  // Trị giá đơn và số SP lấy bằng truy vấn gộp riêng rồi ghép trong JS.
  const stats = await db
    .select({
      customerId: orders.customerId,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}), 0)::bigint`,
      orderCount: sql<number>`count(distinct ${orders.id})::int`,
      skuCount: sql<number>`count(distinct ${orderLines.productId})::int`,
    })
    .from(orders)
    .innerJoin(orderLines, eq(orderLines.orderId, orders.id))
    .where(
      and(
        eq(orders.tenantId, session.tenantId),
        eq(orders.status, "CONFIRMED"),
        inArray(
          orders.customerId,
          rows.map((r) => r.id),
        ),
      ),
    )
    .groupBy(orders.customerId);

  const statMap = new Map(stats.map((s) => [s.customerId, s]));

  return rows.map((row) => {
    const stat = statMap.get(row.id);
    return {
      ...row,
      revenue: Number(stat?.revenue ?? 0),
      orderCount: Number(stat?.orderCount ?? 0),
      skuCount: Number(stat?.skuCount ?? 0),
    };
  });
}

/**
 * Tiến độ tháng: đã đạt bao nhiêu so với chỉ tiêu, và đang nhanh hay chậm so
 * với số ngày đã trôi qua. `pace` > 100 nghĩa là đang vượt tiến độ thời gian.
 */
export async function monthProgressSummary(session: SessionUser, periodKey: string) {
  const range = periodRange(periodKey);
  const [metrics, target] = await Promise.all([
    coreMetrics(session, range),
    targetFor(session, periodKey),
  ]);
  const { elapsed, total } = monthProgress(periodKey);
  const timePct = total > 0 ? (elapsed / total) * 100 : 0;
  const achievementPct =
    target && target.revenueTarget > 0 ? (metrics.revenue / target.revenueTarget) * 100 : null;

  return {
    metrics,
    target,
    elapsedDays: elapsed,
    totalDays: total,
    timePct,
    achievementPct,
    pace: achievementPct != null && timePct > 0 ? (achievementPct / timePct) * 100 : null,
  };
}

/**
 * Tiến độ từng chương trình bán hàng (SP mới, SP trọng tâm) trong kỳ.
 *
 * `programId` được gắn tự động lên dòng hàng khi lên đơn, dựa trên danh sách sản
 * phẩm của chương trình — nên chỉ cần tổng hợp theo cột đó là ra tiến độ.
 */
export async function programProgress(session: SessionUser, range: DateRange) {
  const scope = await orderScope(session);

  const actuals = await db
    .select({
      programId: orderLines.programId,
      qtySold: sql<number>`coalesce(sum(${orderLines.qtySold}), 0)::int`,
      revenue: sql<number>`coalesce(sum(${orderLines.amount}), 0)::bigint`,
      customerCount: sql<number>`count(distinct ${orders.customerId})::int`,
      skuCount: sql<number>`count(distinct ${orderLines.productId})::int`,
    })
    .from(orderLines)
    .innerJoin(orders, eq(orderLines.orderId, orders.id))
    .where(
      and(
        scope,
        gte(orders.orderDate, range.start),
        lte(orders.orderDate, range.end),
        sql`${orderLines.programId} is not null`,
      ),
    )
    .groupBy(orderLines.programId);

  const actualMap = new Map(actuals.filter((r) => r.programId).map((r) => [r.programId!, r]));

  const programRows = await db
    .select({
      id: programs.id,
      name: programs.name,
      type: programs.type,
      revenueTarget: programs.revenueTarget,
      qtyTarget: programs.qtyTarget,
    })
    .from(programs)
    .where(and(eq(programs.tenantId, session.tenantId), eq(programs.isActive, true)))
    .orderBy(programs.name);

  return programRows.map((program) => {
    const actual = actualMap.get(program.id);
    const revenue = Number(actual?.revenue ?? 0);
    const qtySold = Number(actual?.qtySold ?? 0);
    const revenueTarget = Number(program.revenueTarget ?? 0);
    const qtyTarget = Number(program.qtyTarget ?? 0);

    return {
      id: program.id,
      name: program.name,
      type: program.type,
      revenue,
      qtySold,
      customerCount: Number(actual?.customerCount ?? 0),
      skuCount: Number(actual?.skuCount ?? 0),
      revenueTarget,
      qtyTarget,
      revenuePct: revenueTarget > 0 ? (revenue / revenueTarget) * 100 : null,
      qtyPct: qtyTarget > 0 ? (qtySold / qtyTarget) * 100 : null,
    };
  });
}

/** Phân bố đại lý theo hạng doanh thu trong một kỳ (M5). */
export async function dealerTierDistribution(session: SessionUser, periodKey: string) {
  const rows = await db
    .select({
      tier: customerClassifications.revenueTier,
      count: sql<number>`count(*)::int`,
      revenue: sql<number>`coalesce(sum(${customerClassifications.avgMonthlyRevenue}), 0)::bigint`,
    })
    .from(customerClassifications)
    .innerJoin(customers, eq(customerClassifications.customerId, customers.id))
    .where(
      and(
        eq(customerClassifications.tenantId, session.tenantId),
        eq(customerClassifications.periodKey, periodKey),
        await customerScope(session),
      ),
    )
    .groupBy(customerClassifications.revenueTier)
    .orderBy(customerClassifications.revenueTier);

  return rows.map((r) => ({
    tier: r.tier ?? "E",
    count: Number(r.count),
    revenue: Number(r.revenue),
  }));
}