/**
 * Tổng kết tuần (yêu cầu mục 3).
 *
 * Mọi con số ở đây đều suy ra từ lộ trình đã đăng ký và báo cáo ngày đã ghi —
 * không có ô nào cho nhập tay. Nếu cho nhập, bản tổng kết sẽ luôn đẹp và mất hết
 * giá trị đối chiếu.
 */
import "server-only";
import { and, asc, eq, gte, lte, sql } from "drizzle-orm";
import { db } from "@/db";
import { weeklyPlans, planItems, dailyReports, checkIns, customers } from "@/db/schema";
import { weekDays } from "./period";
import { vnDate } from "./sql-dates";

export type WeeklySummary = {
  weekStart: string;
  days: string[];
  objective: string | null;
  summary: string | null;
  nextWeekObjective: string | null;
  submittedAt: Date | null;
  totalItems: number;
  doneItems: number;
  missedItems: number;
  plannedItems: number;
  /** % việc trong lộ trình đã hoàn thành. */
  completionPct: number;
  /** Các việc chưa đạt, kèm lý do NVKD đã ghi. */
  missed: { id: string; planDate: string; content: string; customerName: string | null; reason: string | null }[];
  /** Tồn đọng gom từ các báo cáo ngày trong tuần. */
  pending: { reportDate: string; text: string }[];
  /** Đề xuất, kiến nghị gom từ báo cáo ngày. */
  proposals: { reportDate: string; text: string }[];
  reportCount: number;
  visitCount: number;
  outOfRangeCount: number;
  /** % báo cáo ngày tự đánh giá là đi đúng lộ trình. */
  routeCompliancePct: number | null;
};

export async function buildWeeklySummary(
  tenantId: string,
  userId: string,
  weekStart: string,
): Promise<WeeklySummary> {
  const days = weekDays(weekStart);

  const [planRow] = await db
    .select({
      objective: weeklyPlans.objective,
      summary: weeklyPlans.summary,
      nextWeekObjective: weeklyPlans.nextWeekObjective,
      submittedAt: weeklyPlans.submittedAt,
    })
    .from(weeklyPlans)
    .where(
      and(
        eq(weeklyPlans.tenantId, tenantId),
        eq(weeklyPlans.userId, userId),
        eq(weeklyPlans.weekStart, weekStart),
      ),
    )
    .limit(1);

  const items = await db
    .select({
      id: planItems.id,
      planDate: planItems.planDate,
      content: planItems.content,
      status: planItems.status,
      missedReason: planItems.missedReason,
      customerName: customers.orgName,
    })
    .from(planItems)
    .innerJoin(weeklyPlans, eq(planItems.planId, weeklyPlans.id))
    .leftJoin(customers, eq(planItems.customerId, customers.id))
    .where(
      and(
        eq(planItems.tenantId, tenantId),
        eq(weeklyPlans.userId, userId),
        eq(weeklyPlans.weekStart, weekStart),
      ),
    )
    .orderBy(asc(planItems.planDate), asc(planItems.sortOrder));

  const reports = await db
    .select({
      reportDate: dailyReports.reportDate,
      pending: dailyReports.pending,
      proposals: dailyReports.proposals,
      routeCompliant: dailyReports.routeCompliant,
    })
    .from(dailyReports)
    .where(
      and(
        eq(dailyReports.tenantId, tenantId),
        eq(dailyReports.userId, userId),
        gte(dailyReports.reportDate, days[0]),
        lte(dailyReports.reportDate, days[6]),
      ),
    )
    .orderBy(asc(dailyReports.reportDate));

  const [visits] = await 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, userId),
        gte(vnDate(checkIns.checkedAt), days[0]),
        lte(vnDate(checkIns.checkedAt), days[6]),
      ),
    );

  const doneItems = items.filter((item) => item.status === "DONE").length;
  const missedItems = items.filter((item) => item.status === "MISSED").length;
  const plannedItems = items.filter((item) => item.status === "PLANNED").length;

  const routeCompliant = reports.filter((report) => report.routeCompliant).length;

  return {
    weekStart,
    days,
    objective: planRow?.objective ?? null,
    summary: planRow?.summary ?? null,
    nextWeekObjective: planRow?.nextWeekObjective ?? null,
    submittedAt: planRow?.submittedAt ?? null,
    totalItems: items.length,
    doneItems,
    missedItems,
    plannedItems,
    completionPct: items.length > 0 ? Math.round((doneItems / items.length) * 100) : 0,
    missed: items
      .filter((item) => item.status === "MISSED")
      .map((item) => ({
        id: item.id,
        planDate: item.planDate,
        content: item.content,
        customerName: item.customerName,
        reason: item.missedReason,
      })),
    pending: reports
      .filter((report) => report.pending?.trim())
      .map((report) => ({ reportDate: report.reportDate, text: report.pending!.trim() })),
    proposals: reports
      .filter((report) => report.proposals?.trim())
      .map((report) => ({ reportDate: report.reportDate, text: report.proposals!.trim() })),
    reportCount: reports.length,
    visitCount: Number(visits?.total ?? 0),
    outOfRangeCount: Number(visits?.outOfRange ?? 0),
    routeCompliancePct:
      reports.length > 0 ? Math.round((routeCompliant / reports.length) * 100) : null,
  };
}