/**
 * Danh sách khách hàng tiềm năng (yêu cầu mục 5) — dữ liệu ban đầu từ Marketing, chưa chuyển đổi thành khách.
 *
 * Khách tiềm năng = khách ở giai đoạn "Sẽ" (LEAD) HOẶC khách đã được chốt từ tiềm năng (`lead_status = CONVERTED`, đã chuyển sang
 * "Đang"/"Đại lý"). Nhờ vậy lọc "Đã chốt" vẫn thấy các khách đã chuyển đổi. Phạm vi dữ liệu dùng chung `scopeFilter` với danh sách khách:
 * Ban giám đốc tất cả, quản lý khách của nhân viên trong nhánh, NVKD khách của mình. Lưu ý: khách chưa phân công (owner rỗng) chỉ
 * Ban giám đốc thấy — nên có thao tác giao hàng loạt (`assignLeadsAction`).
 */
import { and, asc, desc, eq, ilike, inArray, isNull, ne, or, sql, type SQL } from "drizzle-orm";
import { db } from "@/db";
import { channels, customerNotes, customers, territories, users } from "@/db/schema";
import type { SessionUser } from "./auth";
import { scopeFilter } from "./rbac";
import { isRealDate } from "./period";
import { LEAD_STATUSES, isLeadReport, stripReportTag } from "./lead-status";

export const LEAD_PAGE_SIZE = 50;

export type LeadQuery = {
  q: string;
  status: "" | (typeof LEAD_STATUSES)[number]["key"];
  territory: string;
  /** "" = tất cả, "none" = chưa phân công, hoặc uuid người phụ trách. */
  owner: string;
  channel: string;
  source: string;
  from: string;
  to: string;
  order: "moi" | "cu";
  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 parseLeadQuery(get: (key: string) => string | undefined | null): LeadQuery {
  const status = get("tt") ?? "";
  const page = Number(get("trang"));
  const owner = get("nv") ?? "";
  return {
    q: (get("q") ?? "").trim().slice(0, 100),
    status: (LEAD_STATUSES.some((s) => s.key === status) ? status : "") as LeadQuery["status"],
    territory: UUID.test(get("kv") ?? "") ? (get("kv") as string) : "",
    owner: owner === "none" ? "none" : UUID.test(owner) ? owner : "",
    channel: UUID.test(get("loai") ?? "") ? (get("loai") as string) : "",
    source: (get("nguon") ?? "").trim().slice(0, 100),
    from: isRealDate(get("tu")) ? (get("tu") as string) : "",
    to: isRealDate(get("den")) ? (get("den") as string) : "",
    order: get("xep") === "cu" ? "cu" : "moi",
    page: Number.isInteger(page) && page >= 1 && page <= 10_000 ? page : 1,
  };
}

export type LeadRow = {
  id: string;
  code: string;
  createdAt: Date;
  orgName: string;
  contactName: string | null;
  contactPosition: string | null;
  phone: string | null;
  ward: string | null;
  territoryName: string | null;
  channelName: string | null;
  leadNeed: string | null;
  leadSource: string | null;
  leadStatus: string;
  stage: "LEAD" | "PROSPECT" | "DEALER";
  ownerId: string | null;
  ownerName: string | null;
  /** Báo cáo gần nhất của khách tiềm năng (nội dung, không kèm nhãn trạng thái). */
  lastReport: { text: string; at: Date } | null;
};

/** Điều kiện chung nhận diện khách tiềm năng, kèm phạm vi vai trò. */
async function baseConditions(session: SessionUser): Promise<SQL[]> {
  const scope = await scopeFilter(session, {
    tenantId: customers.tenantId,
    ownerId: customers.ownerId,
    territoryId: customers.territoryId,
  });
  const conditions: SQL[] = [eq(customers.isActive, true), or(eq(customers.stage, "LEAD"), eq(customers.leadStatus, "CONVERTED"))!];
  if (scope) conditions.unshift(scope);
  return conditions;
}

function filterConditions(query: LeadQuery, includeStatus: boolean): SQL[] {
  const conditions: SQL[] = [];
  if (query.q) {
    const term = `%${query.q}%`;
    conditions.push(
      or(
        ilike(customers.orgName, term),
        ilike(customers.contactName, term),
        ilike(customers.phone, term),
        ilike(customers.ward, term),
        ilike(customers.leadNeed, term),
        ilike(customers.code, term),
      )!,
    );
  }
  if (includeStatus && query.status) {
    conditions.push(
      query.status === "CONVERTED"
        ? eq(customers.leadStatus, "CONVERTED")
        : and(eq(customers.stage, "LEAD"), eq(customers.leadStatus, query.status))!,
    );
  }
  if (query.territory) conditions.push(eq(customers.territoryId, query.territory));
  if (query.channel) conditions.push(eq(customers.channelId, query.channel));
  if (query.owner === "none") conditions.push(isNull(customers.ownerId));
  else if (query.owner) conditions.push(eq(customers.ownerId, query.owner));
  if (query.source) conditions.push(eq(customers.leadSource, query.source));
  // Ngày thêm theo giờ Việt Nam (UTC+7): so sánh trên giờ đã đổi múi.
  if (query.from) conditions.push(sql`(${customers.createdAt} at time zone 'Asia/Ho_Chi_Minh')::date >= ${query.from}`);
  if (query.to) conditions.push(sql`(${customers.createdAt} at time zone 'Asia/Ho_Chi_Minh')::date <= ${query.to}`);
  return conditions;
}

export async function loadLeadList(session: SessionUser, query: LeadQuery, options: { all?: boolean } = {}) {
  const base = await baseConditions(session);
  const where = and(...base, ...filterConditions(query, true));

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

  const rowsQuery = db
    .select({
      id: customers.id,
      code: customers.code,
      createdAt: customers.createdAt,
      orgName: customers.orgName,
      contactName: customers.contactName,
      contactPosition: customers.contactPosition,
      phone: customers.phone,
      ward: customers.ward,
      territoryName: territories.name,
      channelName: channels.name,
      leadNeed: customers.leadNeed,
      leadSource: customers.leadSource,
      leadStatus: customers.leadStatus,
      stage: customers.stage,
      ownerId: customers.ownerId,
      ownerName: users.fullName,
    })
    .from(customers)
    .leftJoin(territories, eq(customers.territoryId, territories.id))
    .leftJoin(channels, eq(customers.channelId, channels.id))
    .leftJoin(users, eq(customers.ownerId, users.id))
    .where(where)
    .orderBy(query.order === "cu" ? asc(customers.createdAt) : desc(customers.createdAt), customers.code);
  const rawRows = options.all ? await rowsQuery : await rowsQuery.limit(LEAD_PAGE_SIZE).offset((page - 1) * LEAD_PAGE_SIZE);

  // Báo cáo gần nhất của từng khách: các dòng "Thông tin trao đổi" có nhãn trạng thái ở đầu.
  const lastReports = new Map<string, { text: string; at: Date }>();
  if (rawRows.length > 0) {
    const notes = await db
      .select({ customerId: customerNotes.customerId, content: customerNotes.content, at: customerNotes.recordedAt })
      .from(customerNotes)
      .where(
        and(
          eq(customerNotes.tenantId, session.tenantId),
          inArray(customerNotes.customerId, rawRows.map((r) => r.id)),
          sql`${customerNotes.content} like '[%]%'`,
        ),
      )
      .orderBy(desc(customerNotes.recordedAt));
    for (const note of notes) {
      if (!lastReports.has(note.customerId) && isLeadReport(note.content)) {
        lastReports.set(note.customerId, { text: stripReportTag(note.content), at: note.at });
      }
    }
  }

  const rows: LeadRow[] = rawRows.map((row) => ({ ...row, lastReport: lastReports.get(row.id) ?? null }));

  // Số khách theo từng trạng thái (giữ các bộ lọc khác, bỏ bộ lọc trạng thái) để hiển thị trên các nút lọc nhanh.
  const countRows = await db
    .select({
      converted: sql<number>`(count(*) filter (where ${customers.leadStatus} = 'CONVERTED'))::int`,
      notContacted: sql<number>`(count(*) filter (where ${customers.stage} = 'LEAD' and ${customers.leadStatus} = 'NOT_CONTACTED'))::int`,
      contacted: sql<number>`(count(*) filter (where ${customers.stage} = 'LEAD' and ${customers.leadStatus} = 'CONTACTED'))::int`,
      cancelled: sql<number>`(count(*) filter (where ${customers.stage} = 'LEAD' and ${customers.leadStatus} = 'CANCELLED'))::int`,
      all: sql<number>`count(*)::int`,
    })
    .from(customers)
    .where(and(...base, ...filterConditions(query, false)));
  const counts = {
    "": Number(countRows[0]?.all ?? 0),
    NOT_CONTACTED: Number(countRows[0]?.notContacted ?? 0),
    CONTACTED: Number(countRows[0]?.contacted ?? 0),
    CANCELLED: Number(countRows[0]?.cancelled ?? 0),
    CONVERTED: Number(countRows[0]?.converted ?? 0),
  } as Record<string, number>;

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

/** Lựa chọn cho bộ lọc: nguồn khách đang có, loại, khu vực, người phụ trách — chỉ trong phạm vi người xem. */
export async function leadFilterOptions(session: SessionUser) {
  const base = await baseConditions(session);
  const where = and(...base);
  const [sourceRows, channelRows, territoryRows, ownerRows] = await Promise.all([
    db.selectDistinct({ value: customers.leadSource }).from(customers).where(and(where, ne(customers.leadSource, ""))).orderBy(customers.leadSource),
    db.selectDistinct({ id: channels.id, name: channels.name }).from(customers).innerJoin(channels, eq(customers.channelId, channels.id)).where(where).orderBy(channels.name),
    db.selectDistinct({ id: territories.id, name: territories.name }).from(customers).innerJoin(territories, eq(customers.territoryId, territories.id)).where(where).orderBy(territories.name),
    session.role === "SALES"
      ? Promise.resolve([] as { id: string; name: string }[])
      : db.selectDistinct({ id: users.id, name: users.fullName }).from(customers).innerJoin(users, eq(customers.ownerId, users.id)).where(where).orderBy(users.fullName),
  ]);
  return {
    sources: sourceRows.map((r) => r.value).filter((v): v is string => !!v),
    channels: channelRows,
    territories: territoryRows,
    owners: ownerRows,
  };
}