/**
 * Hồ sơ và báo cáo chi tiết của một nhân viên (yêu cầu mục Nhân viên: "bấm vào ra thông tin nhân viên, có cả báo cáo chi tiết").
 *
 * Chỉ người xem được dữ liệu của nhân viên đó (chính họ, cấp trên trong nhánh phòng ban, Ban giám đốc) mới gọi được — kiểm ở
 * `canViewStaffData`. Mọi truy vấn đều lọc theo tenant và theo đúng `userId` của nhân viên.
 */
import { and, desc, eq, gte, inArray, lte, sql } from "drizzle-orm";
import { db } from "@/db";
import {
  alerts,
  checkIns,
  customKpiEntries,
  customKpis,
  customerClassifications,
  customers,
  dailyReports,
  events,
  kpiMonthly,
  orderPayments,
  orders,
  planItems,
  users,
  weeklyPlans,
} from "@/db/schema";
import type { SessionUser } from "./auth";
import { visibleUserIds, departmentSubtree } from "./rbac";
import { lastCompletedMonth, monthKey, today } from "./period";
import { vnDate } from "./sql-dates";

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

/** Chính mình, hoặc nhân viên nằm trong phạm vi (nhánh phòng ban) của người xem; Ban giám đốc xem tất cả. */
export async function canViewStaffData(session: SessionUser, targetId: string): Promise<boolean> {
  if (session.userId === targetId) return true;
  const visible = await visibleUserIds(session);
  return visible === null || visible.includes(targetId);
}

/**
 * Cảnh báo ĐANG HIỆU LỰC của nhân viên: chỉ kỳ đã đóng sổ gần nhất mà job đêm quét. Cảnh báo các kỳ cũ không bao giờ tự đóng
 * (người xử lý phải bấm) nên nếu lấy hết thì nhân viên đã cải thiện vẫn bị gắn cờ mãi.
 */
export function currentStaffAlertCondition() {
  return and(eq(alerts.entityType, "user"), eq(alerts.status, "OPEN"), eq(alerts.periodKey, lastCompletedMonth()));
}

export async function staffAlerts(tenantId: string, userIds: string[]) {
  if (userIds.length === 0) return new Map<string, { id: string; title: string; message: string; severity: "INFO" | "WARNING" | "CRITICAL"; createdAt: Date }[]>();
  const rows = await db
    .select({ id: alerts.id, userId: alerts.entityId, title: alerts.title, message: alerts.message, severity: alerts.severity, createdAt: alerts.createdAt })
    .from(alerts)
    .where(and(eq(alerts.tenantId, tenantId), currentStaffAlertCondition(), inArray(alerts.entityId, userIds)));
  const map = new Map<string, { id: string; title: string; message: string; severity: "INFO" | "WARNING" | "CRITICAL"; createdAt: Date }[]>();
  for (const row of rows) {
    if (!row.userId) continue;
    map.set(row.userId, [...(map.get(row.userId) ?? []), { id: row.id, title: row.title, message: row.message, severity: row.severity, createdAt: row.createdAt }]);
  }
  return map;
}

export async function staffReport(session: SessionUser, targetId: string, range: DateRange, monthPeriod: string, target: { role: string; departmentId: string | null }) {
  const tenantId = session.tenantId;

  const orderWhere = and(eq(orders.tenantId, tenantId), eq(orders.salesUserId, targetId), gte(orders.orderDate, range.start), lte(orders.orderDate, range.end));
  const asOfMonth = monthKey(today());

  const [
    alertMap,
    [ord],
    [newCust],
    [custTotal],
    [receivable],
    [allPaid],
    [visit],
    [reports],
    [kpi],
    customKpiRows,
    planRows,
    dailyRows,
    orderRows,
    topCustomerRows,
    stageRows,
    [inactive],
    checkInRows,
  ] = await Promise.all([
    staffAlerts(tenantId, [targetId]),
    db
      .select({
        revenue: sql<number>`coalesce(sum(${orders.totalAmount}) filter (where ${orders.status} = 'CONFIRMED'), 0)::bigint`,
        orderCount: sql<number>`(count(*) filter (where ${orders.status} = 'CONFIRMED'))::int`,
        pending: sql<number>`(count(*) filter (where ${orders.status} = 'DRAFT'))::int`,
      })
      .from(orders)
      .where(orderWhere),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(customers)
      .where(
        and(
          eq(customers.tenantId, tenantId),
          eq(customers.ownerId, targetId),
          sql`${vnDate(customers.createdAt)} >= ${range.start}::date`,
          sql`${vnDate(customers.createdAt)} <= ${range.end}::date`,
        ),
      ),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(customers)
      .where(and(eq(customers.tenantId, tenantId), eq(customers.ownerId, targetId), eq(customers.isActive, true))),
    // Công nợ hiện tại: phải thu (tiền hàng + phụ thu phí) của các đơn đã duyệt trừ số đã thu (toàn thời gian).
    db
      .select({ total: sql<number>`coalesce(sum(${orders.totalAmount} + ${orders.surchargeAmount}), 0)::bigint` })
      .from(orders)
      .where(and(eq(orders.tenantId, tenantId), eq(orders.salesUserId, targetId), eq(orders.status, "CONFIRMED"))),
    db
      .select({ total: sql<number>`coalesce(sum(${orderPayments.amount}), 0)::bigint` })
      .from(orderPayments)
      .innerJoin(orders, eq(orderPayments.orderId, orders.id))
      .where(and(eq(orders.tenantId, tenantId), eq(orders.salesUserId, targetId), eq(orders.status, "CONFIRMED"))),
    db
      .select({
        total: sql<number>`count(*)::int`,
        outOfRange: sql<number>`(count(*) filter (where ${checkIns.outOfRange}))::int`,
      })
      .from(checkIns)
      .where(
        and(
          eq(checkIns.tenantId, tenantId),
          eq(checkIns.userId, targetId),
          sql`${vnDate(checkIns.checkedAt)} >= ${range.start}::date`,
          sql`${vnDate(checkIns.checkedAt)} <= ${range.end}::date`,
        ),
      ),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(dailyReports)
      .where(and(eq(dailyReports.tenantId, tenantId), eq(dailyReports.userId, targetId), gte(dailyReports.reportDate, range.start), lte(dailyReports.reportDate, range.end))),
    db
      .select({
        pct: kpiMonthly.achievementPct,
        target: kpiMonthly.revenueTarget,
        compliance: kpiMonthly.planCompliancePct,
      })
      .from(kpiMonthly)
      .where(and(eq(kpiMonthly.tenantId, tenantId), eq(kpiMonthly.userId, targetId), eq(kpiMonthly.periodKey, monthPeriod)))
      .limit(1),
    db
      .select({ name: customKpis.name, unit: customKpis.unit, target: customKpiEntries.targetValue, actual: customKpiEntries.actualValue })
      .from(customKpiEntries)
      .innerJoin(customKpis, eq(customKpiEntries.kpiId, customKpis.id))
      .where(and(eq(customKpiEntries.tenantId, tenantId), eq(customKpiEntries.userId, targetId), eq(customKpiEntries.periodKey, monthPeriod))),
    db
      .select({
        planned: sql<number>`count(*)::int`,
        done: sql<number>`(count(*) filter (where ${planItems.status} = 'DONE'))::int`,
        missed: sql<number>`(count(*) filter (where ${planItems.status} = 'MISSED'))::int`,
      })
      .from(planItems)
      .innerJoin(weeklyPlans, eq(planItems.planId, weeklyPlans.id))
      .where(and(eq(planItems.tenantId, tenantId), eq(weeklyPlans.userId, targetId), gte(planItems.planDate, range.start), lte(planItems.planDate, range.end))),
    db
      .select({
        date: dailyReports.reportDate,
        completionPct: dailyReports.completionPct,
        routeCompliant: dailyReports.routeCompliant,
        content: dailyReports.content,
        results: dailyReports.results,
        proposals: dailyReports.proposals,
        pending: dailyReports.pending,
        nextSteps: dailyReports.nextSteps,
      })
      .from(dailyReports)
      .where(and(eq(dailyReports.tenantId, tenantId), eq(dailyReports.userId, targetId), gte(dailyReports.reportDate, range.start), lte(dailyReports.reportDate, range.end)))
      .orderBy(desc(dailyReports.reportDate))
      .limit(20),
    db
      .select({
        id: orders.id,
        code: orders.code,
        orderDate: orders.orderDate,
        status: orders.status,
        needsRevision: orders.needsRevision,
        totalAmount: orders.totalAmount,
        customerName: customers.orgName,
      })
      .from(orders)
      .innerJoin(customers, eq(orders.customerId, customers.id))
      .where(orderWhere)
      .orderBy(desc(orders.orderDate), desc(orders.code))
      .limit(10),
    db
      .select({
        id: customers.id,
        name: customers.orgName,
        revenue: sql<number>`coalesce(sum(${orders.totalAmount}) filter (where ${orders.status} = 'CONFIRMED'), 0)::bigint`,
        orderCount: sql<number>`(count(*) filter (where ${orders.status} = 'CONFIRMED'))::int`,
      })
      .from(orders)
      .innerJoin(customers, eq(orders.customerId, customers.id))
      .where(orderWhere)
      .groupBy(customers.id, customers.orgName)
      .orderBy(desc(sql`sum(${orders.totalAmount}) filter (where ${orders.status} = 'CONFIRMED')`))
      .limit(5),
    db
      .select({ stage: customers.stage, count: sql<number>`count(*)::int` })
      .from(customers)
      .where(and(eq(customers.tenantId, tenantId), eq(customers.ownerId, targetId), eq(customers.isActive, true)))
      .groupBy(customers.stage),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(customerClassifications)
      .innerJoin(customers, eq(customerClassifications.customerId, customers.id))
      .where(
        and(
          eq(customerClassifications.tenantId, tenantId),
          eq(customerClassifications.periodKey, asOfMonth),
          eq(customers.ownerId, targetId),
          eq(customers.isActive, true),
          gte(customerClassifications.monthsInactive, 2),
        ),
      ),
    db
      .select({ at: checkIns.checkedAt, note: checkIns.note, outOfRange: checkIns.outOfRange, customerName: customers.orgName })
      .from(checkIns)
      .leftJoin(customers, eq(checkIns.customerId, customers.id))
      .where(
        and(
          eq(checkIns.tenantId, tenantId),
          eq(checkIns.userId, targetId),
          sql`${vnDate(checkIns.checkedAt)} >= ${range.start}::date`,
          sql`${vnDate(checkIns.checkedAt)} <= ${range.end}::date`,
        ),
      )
      .orderBy(desc(checkIns.checkedAt))
      .limit(8),
  ]);

  const stageCount = (stage: string) => Number(stageRows.find((row) => row.stage === stage)?.count ?? 0);
  const plan = planRows[0] ?? { planned: 0, done: 0, missed: 0 };

  // Quản lý / Giám đốc khu vực: báo cáo còn có bảng nhân sự trực thuộc và doanh thu từng người trong kỳ.
  let team: { id: string; name: string; role: string; jobTitle: string | null; revenue: number; orderCount: number; classCount: number; attendedCount: number }[] = [];
  if (target.role !== "SALES" && target.departmentId) {
    const subtree = await departmentSubtree(tenantId, [target.departmentId]);
    const members = await db
      .select({ id: users.id, name: users.fullName, role: users.role, jobTitle: users.jobTitle })
      .from(users)
      .where(and(eq(users.tenantId, tenantId), eq(users.isActive, true), inArray(users.departmentId, subtree.length > 0 ? subtree : ["00000000-0000-0000-0000-000000000000"])));
    const memberIds = members.map((m) => m.id).filter((id) => id !== targetId);
    if (memberIds.length > 0) {
      const revenueRows = await db
        .select({
          userId: orders.salesUserId,
          revenue: sql<number>`coalesce(sum(${orders.totalAmount}) filter (where ${orders.status} = 'CONFIRMED'), 0)::bigint`,
          orderCount: sql<number>`(count(*) filter (where ${orders.status} = 'CONFIRMED'))::int`,
        })
        .from(orders)
        .where(and(eq(orders.tenantId, tenantId), inArray(orders.salesUserId, memberIds), gte(orders.orderDate, range.start), lte(orders.orderDate, range.end)))
        .groupBy(orders.salesUserId);
      const byUser = new Map(revenueRows.map((row) => [row.userId, row]));
      // Lớp/chuyên đề từng người đã phụ trách trong kỳ — dành cho nhóm chuyên môn (không bán hàng nên doanh thu luôn 0).
      const classRows = await db
        .select({
          ownerId: events.ownerId,
          classCount: sql<number>`count(*)::int`,
          attended: sql<number>`coalesce(sum(${events.attendedCount}), 0)::int`,
        })
        .from(events)
        .where(and(eq(events.tenantId, tenantId), inArray(events.ownerId, memberIds), gte(events.eventDate, range.start), lte(events.eventDate, range.end)))
        .groupBy(events.ownerId);
      const classByUser = new Map(classRows.map((row) => [row.ownerId, row]));
      team = members
        .filter((m) => m.id !== targetId)
        .map((m) => ({
          id: m.id,
          name: m.name,
          role: m.role,
          jobTitle: m.jobTitle,
          revenue: Number(byUser.get(m.id)?.revenue ?? 0),
          orderCount: Number(byUser.get(m.id)?.orderCount ?? 0),
          classCount: Number(classByUser.get(m.id)?.classCount ?? 0),
          attendedCount: Number(classByUser.get(m.id)?.attended ?? 0),
        }))
        .sort((a, b) => b.revenue - a.revenue || b.classCount - a.classCount || a.name.localeCompare(b.name, "vi"));
    }
  }

  return {
    alerts: alertMap.get(targetId) ?? [],
    sales: {
      revenue: Number(ord?.revenue ?? 0),
      orderCount: Number(ord?.orderCount ?? 0),
      pendingOrders: Number(ord?.pending ?? 0),
      newCustomers: Number(newCust?.count ?? 0),
      customerCount: Number(custTotal?.count ?? 0),
      debt: Math.max(0, Number(receivable?.total ?? 0) - Number(allPaid?.total ?? 0)),
      visits: Number(visit?.total ?? 0),
      outOfRange: Number(visit?.outOfRange ?? 0),
      reports: Number(reports?.count ?? 0),
      kpiPct: kpi ? Number(kpi.pct) : null,
      kpiTarget: kpi ? Number(kpi.target) : 0,
      planCompliancePct: kpi ? Number(kpi.compliance) : null,
      inactiveDealers: Number(inactive?.count ?? 0),
    },
    customKpis: customKpiRows.map((row) => ({
      name: row.name,
      unit: row.unit,
      target: Number(row.target),
      actual: Number(row.actual),
      pct: Number(row.target) > 0 ? Math.round((Number(row.actual) / Number(row.target)) * 100) : null,
    })),
    plan: { planned: Number(plan.planned), done: Number(plan.done), missed: Number(plan.missed) },
    dailyReports: dailyRows,
    orders: orderRows.map((row) => ({ ...row, totalAmount: Number(row.totalAmount) })),
    topCustomers: topCustomerRows.map((row) => ({ ...row, revenue: Number(row.revenue), orderCount: Number(row.orderCount) })),
    stages: { DEALER: stageCount("DEALER"), PROSPECT: stageCount("PROSPECT"), LEAD: stageCount("LEAD") },
    checkIns: checkInRows,
    team,
  };
}