/**
 * Danh sách đơn hàng (yêu cầu mục Bán hàng): ngày tạo, mã đơn, mã KH, tên cơ sở, người tạo đơn, trạng thái
 * (Chờ duyệt / Điều chỉnh / Đã duyệt / Huỷ), doanh số, doanh thu, thanh toán, còn lại.
 *
 * Màn hình `/don-hang` và file Excel dùng chung hàm này nên khớp nhau. Phạm vi luôn qua `scopeFilter` (theo người bán của đơn, nhánh
 * phòng ban) — bộ lọc chỉ thu hẹp thêm. Định nghĩa số tiền:
 *   Doanh số  = tiền hàng của đơn chưa huỷ (kể cả chờ duyệt);  Doanh thu = tiền hàng của đơn đã duyệt;
 *   Phải thu  = tiền hàng + phụ thu phí (đơn đã duyệt);         Còn lại   = phải thu − đã thu.
 */
import { and, desc, eq, gte, ilike, inArray, lte, or, sql, type SQL } from "drizzle-orm";
import { alias } from "drizzle-orm/pg-core";
import { db } from "@/db";
import { customers, orderPayments, orders, territories, users } from "@/db/schema";
import type { SessionUser } from "./auth";
import { scopeFilter } from "./rbac";
import { isRealDate, monthKey, periodRange, today } from "./period";

export const ORDER_PAGE_SIZE = 50;

export const ORDER_STATUS_FILTERS = [
  { key: "cho-duyet", label: "Chờ duyệt", tone: "gold" },
  { key: "dieu-chinh", label: "Điều chỉnh", tone: "brand" },
  { key: "duyet", label: "Đã duyệt", tone: "green" },
  { key: "huy", label: "Huỷ", tone: "red" },
] as const;
export type OrderStatusFilter = (typeof ORDER_STATUS_FILTERS)[number]["key"];

export type OrderListQuery = {
  from: string;
  to: string;
  status: "" | OrderStatusFilter;
  vat: boolean;
  q: string;
  /** Người tạo đơn (uuid) — trống = tất cả. */
  creator: string;
  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;

export function parseOrderListQuery(get: (key: string) => string | undefined | null): OrderListQuery {
  const defaults = periodRange(monthKey(today()));
  const status = get("tt") ?? "";
  const page = Number(get("trang"));
  const tu = get("tu");
  const den = get("den");
  return {
    from: isRealDate(tu) ? tu : defaults.start,
    to: isRealDate(den) ? den : defaults.end,
    status: (ORDER_STATUS_FILTERS.some((s) => s.key === status) ? status : "") as OrderListQuery["status"],
    vat: get("vat") === "1",
    q: (get("q") ?? "").trim().slice(0, 100),
    creator: UUID.test(get("nguoi") ?? "") ? (get("nguoi") as string) : "",
    page: Number.isInteger(page) && page >= 1 && page <= 10_000 ? page : 1,
  };
}

/** Điều kiện trạng thái hiển thị: "Điều chỉnh" là đơn chờ duyệt đã bị người duyệt trả về. */
function statusCondition(status: OrderStatusFilter): SQL {
  switch (status) {
    case "cho-duyet":
      return and(eq(orders.status, "DRAFT"), eq(orders.needsRevision, false))!;
    case "dieu-chinh":
      return and(eq(orders.status, "DRAFT"), eq(orders.needsRevision, true))!;
    case "duyet":
      return eq(orders.status, "CONFIRMED");
    default:
      return eq(orders.status, "CANCELLED");
  }
}

export async function orderListConditions(session: SessionUser, query: OrderListQuery, includeStatus: boolean): Promise<SQL[]> {
  const conditions: SQL[] = [eq(orders.tenantId, session.tenantId), gte(orders.orderDate, query.from), lte(orders.orderDate, query.to)];
  const scope = await scopeFilter(session, { tenantId: orders.tenantId, ownerId: orders.salesUserId, territoryId: orders.territoryId });
  if (scope) conditions.push(scope);
  if (includeStatus && query.status) conditions.push(statusCondition(query.status));
  if (query.vat) conditions.push(eq(orders.vatRequired, true));
  if (query.creator) conditions.push(eq(orders.createdBy, query.creator));
  if (query.q) {
    const term = `%${query.q}%`;
    conditions.push(or(ilike(orders.code, term), ilike(customers.code, term), ilike(customers.orgName, term), ilike(customers.contactName, term))!);
  }
  return conditions;
}

export type OrderListRow = {
  id: string;
  code: string;
  orderDate: string;
  status: "DRAFT" | "CONFIRMED" | "CANCELLED";
  needsRevision: boolean;
  vatRequired: boolean;
  totalAmount: number;
  promoValue: number;
  surchargeAmount: number;
  dueDate: string | null;
  createdByName: string | null;
  salesName: string | null;
  customerCode: string;
  customerName: string;
  territoryName: string | null;
  lineCount: number;
  paid: number;
  /** Phải thu chưa thu (chỉ đơn đã duyệt có nghĩa). */
  remaining: number;
};

export async function loadOrderList(session: SessionUser, query: OrderListQuery, options: { all?: boolean } = {}) {
  const creator = alias(users, "creator");
  const sales = alias(users, "sales");
  const withoutStatus = await orderListConditions(session, query, false);
  const where = and(...(await orderListConditions(session, query, true)));

  const base = db.select({ total: sql<number>`count(*)::int` }).from(orders).innerJoin(customers, eq(orders.customerId, customers.id));
  const [{ total }] = await base.where(where);
  const totalPages = Math.max(1, Math.ceil(total / ORDER_PAGE_SIZE));
  const page = Math.min(query.page, totalPages);

  const rowsQuery = db
    .select({
      id: orders.id,
      code: orders.code,
      orderDate: orders.orderDate,
      status: orders.status,
      needsRevision: orders.needsRevision,
      vatRequired: orders.vatRequired,
      totalAmount: orders.totalAmount,
      promoValue: orders.promoValue,
      surchargeAmount: orders.surchargeAmount,
      dueDate: orders.dueDate,
      createdByName: creator.fullName,
      salesName: sales.fullName,
      customerCode: customers.code,
      customerName: customers.orgName,
      territoryName: territories.name,
    })
    .from(orders)
    .innerJoin(customers, eq(orders.customerId, customers.id))
    .leftJoin(sales, eq(orders.salesUserId, sales.id))
    .leftJoin(creator, eq(orders.createdBy, creator.id))
    .leftJoin(territories, eq(orders.territoryId, territories.id))
    .where(where)
    .orderBy(desc(orders.orderDate), desc(orders.code));
  const rawRows = options.all ? await rowsQuery : await rowsQuery.limit(ORDER_PAGE_SIZE).offset((page - 1) * ORDER_PAGE_SIZE);

  const ids = rawRows.map((row) => row.id);
  const [paidRows, lineRows] =
    ids.length > 0
      ? await Promise.all([
          db
            .select({ orderId: orderPayments.orderId, paid: sql<number>`coalesce(sum(${orderPayments.amount}), 0)::bigint` })
            .from(orderPayments)
            .where(inArray(orderPayments.orderId, ids))
            .groupBy(orderPayments.orderId),
          db.execute(sql`select order_id, count(*)::int as n from order_lines where order_id in (${sql.join(ids.map((id) => sql`${id}`), sql`, `)}) group by order_id`),
        ])
      : [[], { rows: [] as { order_id: string; n: number }[] }];
  const paidMap = new Map(paidRows.map((row) => [row.orderId, Number(row.paid)]));
  const lineMap = new Map((lineRows.rows as { order_id: string; n: number }[]).map((row) => [row.order_id, Number(row.n)]));

  const rows: OrderListRow[] = rawRows.map((row) => {
    const paid = paidMap.get(row.id) ?? 0;
    return {
      ...row,
      totalAmount: Number(row.totalAmount),
      promoValue: Number(row.promoValue),
      surchargeAmount: Number(row.surchargeAmount),
      lineCount: lineMap.get(row.id) ?? 0,
      paid,
      remaining: row.status === "CONFIRMED" ? Math.max(0, Number(row.totalAmount) + Number(row.surchargeAmount) - paid) : 0,
    };
  });

  // Số đơn theo từng trạng thái (giữ các bộ lọc khác, bỏ lọc trạng thái) cho các nút lọc nhanh.
  const [countRow] = await db
    .select({
      all: sql<number>`count(*)::int`,
      draft: sql<number>`(count(*) filter (where ${orders.status} = 'DRAFT' and not ${orders.needsRevision}))::int`,
      revision: sql<number>`(count(*) filter (where ${orders.status} = 'DRAFT' and ${orders.needsRevision}))::int`,
      confirmed: sql<number>`(count(*) filter (where ${orders.status} = 'CONFIRMED'))::int`,
      cancelled: sql<number>`(count(*) filter (where ${orders.status} = 'CANCELLED'))::int`,
      revenue: sql<number>`coalesce(sum(${orders.totalAmount}) filter (where ${orders.status} = 'CONFIRMED'), 0)::bigint`,
      promo: sql<number>`coalesce(sum(${orders.promoValue}) filter (where ${orders.status} = 'CONFIRMED'), 0)::bigint`,
      customers: sql<number>`count(distinct ${orders.customerId}) filter (where ${orders.status} = 'CONFIRMED')::int`,
    })
    .from(orders)
    .innerJoin(customers, eq(orders.customerId, customers.id))
    .where(and(...withoutStatus));
  const counts: Record<string, number> = {
    "": Number(countRow?.all ?? 0),
    "cho-duyet": Number(countRow?.draft ?? 0),
    "dieu-chinh": Number(countRow?.revision ?? 0),
    duyet: Number(countRow?.confirmed ?? 0),
    huy: Number(countRow?.cancelled ?? 0),
  };
  const stats = {
    revenue: Number(countRow?.revenue ?? 0),
    promo: Number(countRow?.promo ?? 0),
    confirmedCount: Number(countRow?.confirmed ?? 0),
    customerCount: Number(countRow?.customers ?? 0),
  };

  return { rows, total, page, totalPages, counts, stats };
}

/** Người tạo đơn có mặt trong các đơn thuộc phạm vi của người xem — cho ô lọc "Người tạo". */
export async function orderCreatorOptions(session: SessionUser) {
  const creator = alias(users, "creator");
  const scope = await scopeFilter(session, { tenantId: orders.tenantId, ownerId: orders.salesUserId, territoryId: orders.territoryId });
  return db
    .selectDistinct({ id: creator.id, name: creator.fullName })
    .from(orders)
    .innerJoin(creator, eq(orders.createdBy, creator.id))
    .where(scope)
    .orderBy(creator.fullName);
}