/**
 * Danh sách khách hàng — bộ lọc, chỉ số theo kỳ và xếp hạng (yêu cầu mục 5).
 *
 * Màn hình `/khach-hang` và file Excel xuất ra dùng chung hàm này nên luôn khớp nhau, kể cả phạm vi
 * dữ liệu theo vai trò (`scopeFilter`): Ban giám đốc thấy tất cả, GĐ khu vực/Quản lý thấy khách của nhân
 * viên trong nhánh phòng ban mình, NVKD chỉ thấy khách mình phụ trách.
 *
 * Mọi số liệu "bình quân tháng" chia cho số tháng ĐÃ BẮT ĐẦU trong kỳ (quý này mới qua 2 tháng thì chia 2, không chia 3).
 * Chuỗi người dùng chỉ được dùng để chọn trong danh sách cố định (SORTS/RANGES/...) hoặc là uuid đã kiểm tra —
 * không bao giờ đưa thẳng vào câu SQL.
 */
import { and, eq, ilike, inArray, or, sql, type SQL } from "drizzle-orm";
import { db } from "@/db";
import { alerts, customers, customerClassifications, channels, departments, orders, orderPayments, territories, users } from "@/db/schema";
import type { SessionUser } from "./auth";
import { departmentPaths, departmentSubtree, scopeFilter } from "./rbac";
import { customerMetrics, customerPeriodStats } from "./reports";
import { INACTIVE_THRESHOLDS } from "./alerts";
import { LEAD_LABEL } from "./lead-status";
import { lastCompletedMonth, lastMonths, monthKey, monthsInPeriod, periodRange, quarterOfMonth, shiftMonth, today } from "./period";

export const STAGES = ["DEALER", "PROSPECT", "LEAD"] as const;
export { LEAD_LABEL };

/** Tiêu chí xếp hạng. `range` = có phụ thuộc "Kỳ tính" hay không. */
export const SORTS = [
  { key: "hang", label: "Doanh thu bình quân 12 tháng (theo hạng — mặc định)", range: false },
  { key: "ds", label: "Doanh số bình quân tháng", range: true },
  { key: "dt", label: "Doanh thu bình quân tháng", range: true },
  { key: "nobq", label: "Công nợ bình quân tháng", range: true },
  { key: "no", label: "Công nợ hiện tại", range: false },
  { key: "don", label: "Số lần lên đơn", range: true },
  { key: "sp", label: "Số sản phẩm đang ứng dụng", range: false },
  { key: "gan", label: "Ngày lên đơn gần nhất", range: false },
] as const;
export type SortKey = (typeof SORTS)[number]["key"];

export const RANGES = [
  { key: "thang", label: "Tháng này" },
  { key: "thang-truoc", label: "Tháng trước" },
  { key: "quy", label: "Quý này" },
  { key: "quy-truoc", label: "Quý trước" },
  { key: "nam", label: "Năm nay" },
  { key: "nam-truoc", label: "Năm trước" },
  { key: "12t", label: "12 tháng gần nhất" },
] as const;
export type RangeKey = (typeof RANGES)[number]["key"];

/** Ngưỡng "trên N sản phẩm" — cùng mốc với hạng SKU (trên 30 / 20 / 10 / 5). */
export const SKU_THRESHOLDS = [5, 10, 20, 30] as const;

export const ALERT_FILTERS = [
  { key: "co", label: "Có cảnh báo" },
  { key: "no", label: "Nợ quá hạn" },
  { key: "ngung", label: "Lâu chưa lấy hàng" },
  { key: "giam", label: "Doanh thu giảm" },
] as const;
export type AlertFilter = "" | (typeof ALERT_FILTERS)[number]["key"];

export type CustomerListQuery = {
  q: string;
  stage: "" | (typeof STAGES)[number];
  tier: "" | "A" | "B" | "C" | "D" | "E";
  lead: string;
  /** Chỉ lấy khách có SỐ SP ứng dụng LỚN HƠN mức này (0 = không lọc). */
  minSku: number;
  sort: SortKey;
  dir: "giam" | "tang";
  range: RangeKey;
  owner: string;
  /** Phòng ban / team của NVKD phụ trách (gồm các team con). */
  dept: string;
  territory: string;
  alert: AlertFilter;
  page: number;
};

const UUID = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

/** Đọc tham số lọc từ URL — giá trị lạ đều rơi về mặc định, không bao giờ lỗi. */
export function parseCustomerListQuery(get: (key: string) => string | undefined | null): CustomerListQuery {
  const pick = <T extends string>(value: string | undefined | null, allowed: readonly T[]): T | "" =>
    value && (allowed as readonly string[]).includes(value) ? (value as T) : "";
  const sku = Number(get("sp"));
  const page = Number(get("trang"));
  return {
    q: (get("q") ?? "").trim().slice(0, 100),
    stage: pick(get("stage"), STAGES),
    tier: pick(get("tier"), ["A", "B", "C", "D", "E"] as const),
    lead: pick(get("lead"), Object.keys(LEAD_LABEL)),
    minSku: (SKU_THRESHOLDS as readonly number[]).includes(sku) ? sku : 0,
    sort: pick(get("sap"), SORTS.map((s) => s.key)) || "hang",
    dir: get("huong") === "tang" ? "tang" : "giam",
    range: pick(get("ky"), RANGES.map((r) => r.key)) || "thang",
    owner: UUID.test(get("nv") ?? "") ? (get("nv") as string) : "",
    dept: UUID.test(get("pb") ?? "") ? (get("pb") as string) : "",
    territory: UUID.test(get("kv") ?? "") ? (get("kv") as string) : "",
    alert: pick(get("cb"), ALERT_FILTERS.map((a) => a.key)),
    page: Number.isInteger(page) && page >= 1 && page <= 10_000 ? page : 1,
  };
}

/* -------------------------------------------------------------------------- */
/* Kỳ tính                                                                    */
/* -------------------------------------------------------------------------- */

export type ResolvedRange = {
  key: RangeKey;
  label: string;
  start: string;
  end: string;
  /** Các tháng đã bắt đầu trong kỳ (không tính tháng tương lai) — mẫu số của số bình quân. */
  months: string[];
  /** Ngày chốt công nợ cuối mỗi tháng (tháng đang chạy: chốt tới hôm nay). */
  monthEnds: string[];
};

export function resolveRange(key: RangeKey, asOf = today()): ResolvedRange {
  const thisMonth = monthKey(asOf);
  const thisYear = Number(asOf.slice(0, 4));
  let months: string[];
  switch (key) {
    case "thang-truoc":
      months = [shiftMonth(thisMonth, 1)];
      break;
    case "quy":
      months = monthsInPeriod(quarterOfMonth(thisMonth));
      break;
    case "quy-truoc":
      months = monthsInPeriod(quarterOfMonth(shiftMonth(thisMonth, 3)));
      break;
    case "nam":
      months = monthsInPeriod(String(thisYear));
      break;
    case "nam-truoc":
      months = monthsInPeriod(String(thisYear - 1));
      break;
    case "12t":
      months = lastMonths(thisMonth, 12);
      break;
    default:
      months = [thisMonth];
  }
  months = months.filter((m) => m <= thisMonth);
  const first = months[0];
  const last = months[months.length - 1];
  return {
    key,
    label: RANGES.find((r) => r.key === key)?.label ?? key,
    start: periodRange(first).start,
    end: periodRange(last).end,
    months,
    monthEnds: months.map((m) => {
      const end = periodRange(m).end;
      return end < asOf ? end : asOf;
    }),
  };
}

/* -------------------------------------------------------------------------- */
/* Cảnh báo                                                                   */
/* -------------------------------------------------------------------------- */

export type CustomerWarning = { kind: "no" | "ngung" | "giam"; severity: "INFO" | "WARNING" | "CRITICAL"; label: string };

export type CustomerListRow = {
  id: string;
  code: string;
  orgName: string;
  contactName: string | null;
  contactPosition: string | null;
  phone: string | null;
  stage: (typeof STAGES)[number];
  isKey: boolean;
  leadStatus: string;
  leadNeed: string | null;
  leadSource: string | null;
  ward: string | null;
  territoryName: string | null;
  channelName: string | null;
  ownerId: string | null;
  ownerName: string | null;
  revenueTier: "A" | "B" | "C" | "D" | "E" | null;
  skuTier: "A" | "B" | "C" | "D" | "E" | null;
  /** Doanh thu bình quân tháng trong 12 tháng gần nhất (phân hạng, tính lại mỗi đêm). */
  avgRevenue: number | null;
  skuCount: number | null;
  lastOrderDate: string | null;
  lastOrderValue: number;
  outstanding: number;
  overdue: number;
  salesAvg: number;
  revenueAvg: number;
  debtAvg: number;
  orderCount: number;
  warnings: CustomerWarning[];
};

const ID_LIST = (ids: string[]) => sql.join(ids.map((id) => sql`${id}`), sql`, `);

/**
 * Công nợ bình quân tháng: trung bình của công nợ tại CUỐI mỗi tháng trong kỳ (tháng đang chạy chốt tới hôm nay).
 * Công nợ tại ngày X = tổng phải thu (tiền hàng + phụ thu phí) của đơn đã duyệt lập tới ngày X − tiền đã thu tới ngày X của chính các đơn đó.
 */
export async function averageMonthlyDebt(tenantId: string, ids: string[], monthEnds: string[]): Promise<Map<string, number>> {
  const result = new Map<string, number>();
  if (ids.length === 0 || monthEnds.length === 0) return result;

  const orderCols: Record<string, SQL<number>> = {};
  const payCols: Record<string, SQL<number>> = {};
  monthEnds.forEach((end, i) => {
    orderCols[`m${i}`] = sql<number>`coalesce(sum(${orders.totalAmount} + ${orders.surchargeAmount}) filter (where ${orders.orderDate} <= ${end}), 0)::bigint`;
    payCols[`m${i}`] = sql<number>`coalesce(sum(${orderPayments.amount}) filter (where ${orders.orderDate} <= ${end} and ${orderPayments.paidOn} <= ${end}), 0)::bigint`;
  });

  const [billed, paid] = await Promise.all([
    db
      .select({ customerId: orders.customerId, ...orderCols })
      .from(orders)
      .where(and(eq(orders.tenantId, tenantId), eq(orders.status, "CONFIRMED"), inArray(orders.customerId, ids)))
      .groupBy(orders.customerId),
    db
      .select({ customerId: orders.customerId, ...payCols })
      .from(orderPayments)
      .innerJoin(orders, eq(orderPayments.orderId, orders.id))
      .where(and(eq(orders.tenantId, tenantId), eq(orders.status, "CONFIRMED"), inArray(orders.customerId, ids)))
      .groupBy(orders.customerId),
  ]);

  const paidByCustomer = new Map<string, Record<string, unknown>>();
  for (const row of paid) paidByCustomer.set(row.customerId, row as Record<string, unknown>);

  for (const row of billed as Record<string, unknown>[]) {
    const customerId = String(row.customerId);
    const paidRow = paidByCustomer.get(customerId);
    let total = 0;
    for (let i = 0; i < monthEnds.length; i++) {
      total += Math.max(Number(row[`m${i}`] ?? 0) - Number(paidRow?.[`m${i}`] ?? 0), 0);
    }
    result.set(customerId, total / monthEnds.length);
  }
  return result;
}

/** Phần chưa thu của các đơn đã duyệt đã QUÁ hạn thanh toán (theo từng đơn, không bù trừ giữa các đơn). */
export async function overdueDebt(tenantId: string, ids: string[], asOf: string): Promise<Map<string, number>> {
  const result = new Map<string, number>();
  if (ids.length === 0) return result;
  const rows = await db.execute(sql`
    select o.customer_id, sum(greatest(o.total_amount + o.surcharge_amount - coalesce((
      select sum(p.amount) from order_payments p where p.order_id = o.id
    ), 0), 0))::bigint as amount
    from orders o
    where o.tenant_id = ${tenantId}
      and o.status = 'CONFIRMED'
      and o.due_date is not null
      and o.due_date < ${asOf}
      and o.customer_id in (${ID_LIST(ids)})
    group by o.customer_id
  `);
  for (const row of rows.rows as { customer_id: string; amount: string | number }[]) {
    const amount = Number(row.amount);
    if (amount > 0) result.set(row.customer_id, amount);
  }
  return result;
}

/** Cảnh báo giảm doanh thu của kỳ đã đóng sổ gần nhất (cùng kỳ mà job hằng đêm quét). */
async function revenueDropAlerts(tenantId: string, ids: string[]): Promise<Map<string, { severity: CustomerWarning["severity"]; label: string }>> {
  const result = new Map<string, { severity: CustomerWarning["severity"]; label: string }>();
  if (ids.length === 0) return result;
  const rows = await db
    .select({ entityId: alerts.entityId, severity: alerts.severity, metrics: alerts.metrics })
    .from(alerts)
    .where(
      and(
        eq(alerts.tenantId, tenantId),
        eq(alerts.entityType, "customer"),
        eq(alerts.type, "DEALER_REVENUE_DROP"),
        eq(alerts.status, "OPEN"),
        eq(alerts.periodKey, lastCompletedMonth()),
        inArray(alerts.entityId, ids),
      ),
    );
  for (const row of rows) {
    if (!row.entityId) continue;
    const m = (row.metrics ?? {}) as { dropPct?: number; comparedTo?: string };
    result.set(row.entityId, {
      severity: row.severity,
      label: `Doanh thu giảm ${m.dropPct ?? "?"}%${m.comparedTo ? ` so với ${m.comparedTo}` : ""}`,
    });
  }
  return result;
}

/* -------------------------------------------------------------------------- */
/* Truy vấn chính                                                             */
/* -------------------------------------------------------------------------- */

export const MAX_MATCHED = 5000;

export type CustomerList = {
  rows: CustomerListRow[];
  /** Có bị cắt ở MAX_MATCHED khách hay không — khi đó xếp hạng chỉ trên phần đã lấy. */
  capped: boolean;
  range: ResolvedRange;
  scope: SQL | undefined;
};

export async function loadCustomerList(session: SessionUser, query: CustomerListQuery): Promise<CustomerList> {
  const asOf = today();
  const period = monthKey(asOf);
  const range = resolveRange(query.range, asOf);

  const scope = await scopeFilter(session, {
    tenantId: customers.tenantId,
    ownerId: customers.ownerId,
    territoryId: customers.territoryId,
  });

  const filters: (SQL | undefined)[] = [scope, eq(customers.isActive, true)];
  if (query.q) {
    const term = `%${query.q}%`;
    filters.push(
      or(
        ilike(customers.orgName, term),
        ilike(customers.code, term),
        ilike(customers.phone, term),
        ilike(customers.contactName, term),
        ilike(customers.ward, term),
      ),
    );
  }
  if (query.stage) filters.push(eq(customers.stage, query.stage));
  if (query.minSku > 0) filters.push(sql`${customerClassifications.skuCount} > ${query.minSku}`);
  if (query.lead) filters.push(eq(customers.leadStatus, query.lead));
  if (query.tier) filters.push(eq(customerClassifications.revenueTier, query.tier));
  // Bộ lọc người phụ trách / khu vực chỉ thu hẹp thêm — luôn nằm TRONG phạm vi vai trò ở trên.
  if (query.owner) filters.push(eq(customers.ownerId, query.owner));
  if (query.territory) filters.push(eq(customers.territoryId, query.territory));
  // Phòng ban / team: khách của các NVKD thuộc phòng đó (và team con).
  if (query.dept) {
    const deptIds = await departmentSubtree(session.tenantId, [query.dept]);
    filters.push(
      deptIds.length > 0
        ? inArray(customers.ownerId, db.select({ id: users.id }).from(users).where(and(eq(users.tenantId, session.tenantId), inArray(users.departmentId, deptIds))))
        : sql`false`,
    );
  }

  const base = await db
    .select({
      id: customers.id,
      code: customers.code,
      orgName: customers.orgName,
      contactName: customers.contactName,
      contactPosition: customers.contactPosition,
      phone: customers.phone,
      stage: customers.stage,
      isKey: customers.isKey,
      leadStatus: customers.leadStatus,
      leadNeed: customers.leadNeed,
      leadSource: customers.leadSource,
      ward: customers.ward,
      territoryName: territories.name,
      channelName: channels.name,
      ownerId: customers.ownerId,
      ownerName: users.fullName,
      revenueTier: customerClassifications.revenueTier,
      skuTier: customerClassifications.skuTier,
      avgRevenue: customerClassifications.avgMonthlyRevenue,
      skuCount: customerClassifications.skuCount,
      monthsInactive: customerClassifications.monthsInactive,
    })
    .from(customers)
    .leftJoin(territories, eq(customers.territoryId, territories.id))
    .leftJoin(channels, eq(customers.channelId, channels.id))
    .leftJoin(users, eq(customers.ownerId, users.id))
    .leftJoin(
      customerClassifications,
      and(eq(customerClassifications.customerId, customers.id), eq(customerClassifications.periodKey, period)),
    )
    .where(and(...filters))
    .orderBy(sql`${customerClassifications.avgMonthlyRevenue} desc nulls last`, customers.orgName)
    .limit(MAX_MATCHED + 1);

  const capped = base.length > MAX_MATCHED;
  if (capped) base.length = MAX_MATCHED;
  const ids = base.map((row) => row.id);

  const [metrics, periodStats, debtAvg, overdue, drops] = await Promise.all([
    customerMetrics(session, ids),
    customerPeriodStats(session, ids, { start: range.start, end: range.end }),
    averageMonthlyDebt(session.tenantId, ids, range.monthEnds),
    overdueDebt(session.tenantId, ids, asOf),
    revenueDropAlerts(session.tenantId, ids),
  ]);

  const monthCount = Math.max(range.months.length, 1);
  let rows: CustomerListRow[] = base.map((row) => {
    const m = metrics.get(row.id);
    const p = periodStats.get(row.id);
    const overdueAmount = overdue.get(row.id) ?? 0;
    const warnings: CustomerWarning[] = [];
    if (overdueAmount > 0) warnings.push({ kind: "no", severity: "WARNING", label: "Nợ quá hạn" });
    const inactive = row.monthsInactive ?? 0;
    const threshold = INACTIVE_THRESHOLDS.find((t) => inactive >= t.months);
    if (threshold) warnings.push({ kind: "ngung", severity: threshold.severity, label: `${inactive} tháng chưa lấy hàng` });
    const drop = drops.get(row.id);
    if (drop) warnings.push({ kind: "giam", severity: drop.severity, label: drop.label });

    return {
      id: row.id,
      code: row.code,
      orgName: row.orgName,
      contactName: row.contactName,
      contactPosition: row.contactPosition,
      phone: row.phone,
      stage: row.stage,
      isKey: row.isKey,
      leadStatus: row.leadStatus,
      leadNeed: row.leadNeed,
      leadSource: row.leadSource,
      ward: row.ward,
      territoryName: row.territoryName,
      channelName: row.channelName,
      ownerId: row.ownerId,
      ownerName: row.ownerName,
      revenueTier: row.revenueTier,
      skuTier: row.skuTier,
      avgRevenue: row.avgRevenue == null ? null : Number(row.avgRevenue),
      skuCount: row.skuCount,
      lastOrderDate: m?.lastOrderDate ?? null,
      lastOrderValue: m?.lastOrderValue ?? 0,
      outstanding: m?.outstanding ?? 0,
      overdue: overdueAmount,
      salesAvg: (p?.sales ?? 0) / monthCount,
      revenueAvg: (p?.revenue ?? 0) / monthCount,
      debtAvg: debtAvg.get(row.id) ?? 0,
      orderCount: p?.orderCount ?? 0,
      warnings,
    };
  });

  if (query.alert) {
    rows = rows.filter((row) => (query.alert === "co" ? row.warnings.length > 0 : row.warnings.some((w) => w.kind === query.alert)));
  }

  rows.sort(compareBy(query.sort, query.dir));
  return { rows, capped, range, scope };
}

/** Giá trị dùng để xếp hạng; `null` = chưa có số liệu, luôn xếp cuối dù tăng hay giảm dần. */
export function sortValue(row: CustomerListRow, sort: SortKey): number | null {
  switch (sort) {
    case "ds":
      return row.salesAvg;
    case "dt":
      return row.revenueAvg;
    case "nobq":
      return row.debtAvg;
    case "no":
      return row.outstanding;
    case "don":
      return row.orderCount;
    case "sp":
      return row.skuCount;
    case "gan":
      return row.lastOrderDate ? Date.parse(`${row.lastOrderDate}T00:00:00Z`) : null;
    default:
      return row.avgRevenue;
  }
}

function compareBy(sort: SortKey, dir: "giam" | "tang") {
  const sign = dir === "tang" ? 1 : -1;
  return (a: CustomerListRow, b: CustomerListRow): number => {
    const va = sortValue(a, sort);
    const vb = sortValue(b, sort);
    if (va == null && vb == null) return a.orgName.localeCompare(b.orgName, "vi");
    if (va == null) return 1;
    if (vb == null) return -1;
    return (va - vb) * sign || a.orgName.localeCompare(b.orgName, "vi");
  };
}

/** Khu vực và người phụ trách để chọn trong bộ lọc — chỉ những gì nằm trong phạm vi người xem. */
export async function customerFilterOptions(session: SessionUser) {
  if (session.role === "SALES") return { territories: [], owners: [], departments: [] as { id: string; name: string }[] };
  const scope = await scopeFilter(session, {
    tenantId: customers.tenantId,
    ownerId: customers.ownerId,
    territoryId: customers.territoryId,
  });
  const [territoryRows, ownerRows, departmentRows, paths] = await Promise.all([
    db
      .selectDistinct({ id: territories.id, name: territories.name })
      .from(customers)
      .innerJoin(territories, eq(customers.territoryId, territories.id))
      .where(and(scope, eq(customers.isActive, true)))
      .orderBy(territories.name),
    db
      .selectDistinct({ id: users.id, name: users.fullName })
      .from(customers)
      .innerJoin(users, eq(customers.ownerId, users.id))
      .where(and(scope, eq(customers.isActive, true)))
      .orderBy(users.fullName),
    db
      .selectDistinct({ id: departments.id, name: departments.name })
      .from(customers)
      .innerJoin(users, eq(customers.ownerId, users.id))
      .innerJoin(departments, eq(users.departmentId, departments.id))
      .where(and(scope, eq(customers.isActive, true)))
      .orderBy(departments.name),
    departmentPaths(session.tenantId),
  ]);
  return { territories: territoryRows, owners: ownerRows, departments: departmentRows.map((d) => ({ id: d.id, name: paths.get(d.id) ?? d.name })) };
}

/** "Phường X - Tỉnh Y" theo yêu cầu; thiếu phần nào bỏ phần đó. */
export function areaLabel(row: Pick<CustomerListRow, "ward" | "territoryName">): string {
  return [row.ward, row.territoryName].filter(Boolean).join(" - ") || "—";
}