import { db } from "@/db";
import { clients, projects, payments, deliverables, time_entries } from "@/db/schema";
import { eq, and, isNotNull, isNull, desc, sum, count } from "drizzle-orm";

export type KpiStats = {
  clientiAttivi: number;
  revenueTotale: string;       // sum of projects.accepted_total (non-archived)
  pagamentiInSospeso: string;  // sum of payments.amount where status = 'da_saldare' or 'inviata'
  progettiInCorso: number;     // projects not archived
};

export type ActivityItem = {
  type: "nuovo_cliente" | "nuovo_progetto" | "deliverable_approvato" | "timer_stoppato";
  label: string;
  detail: string;
  timestamp: Date;
};

export type DashboardStats = {
  kpi: KpiStats;
  activity: ActivityItem[];
};

export async function getDashboardStats(): Promise<DashboardStats> {
  // ── KPIs ─────────────────────────────────────────────────────────────────────
  const [clientiAttiviRow] = await db
    .select({ count: count() })
    .from(clients)
    .where(eq(clients.archived, false));

  const [revenueRow] = await db
    .select({ total: sum(projects.accepted_total) })
    .from(projects)
    .where(eq(projects.archived, false));

  const [pagamentiDaSaldareRow] = await db
    .select({ total: sum(payments.amount) })
    .from(payments)
    .where(eq(payments.status, "da_saldare"));

  const [pagamentiInviataRow] = await db
    .select({ total: sum(payments.amount) })
    .from(payments)
    .where(eq(payments.status, "inviata"));

  const [progettiRow] = await db
    .select({ count: count() })
    .from(projects)
    .where(eq(projects.archived, false));

  const pagamentiSospeso =
    (parseFloat(pagamentiDaSaldareRow?.total ?? "0") || 0) +
    (parseFloat(pagamentiInviataRow?.total ?? "0") || 0);

  const kpi: KpiStats = {
    clientiAttivi: clientiAttiviRow?.count ?? 0,
    revenueTotale: revenueRow?.total ?? "0",
    pagamentiInSospeso: pagamentiSospeso.toFixed(2),
    progettiInCorso: progettiRow?.count ?? 0,
  };

  // ── Activity feed ─────────────────────────────────────────────────────────────
  // Fetch recent events from multiple tables, merge and sort
  const [recentClients, recentProjects, recentApprovals, recentTimers] = await Promise.all([
    db
      .select({ id: clients.id, name: clients.name, created_at: clients.created_at })
      .from(clients)
      .orderBy(desc(clients.created_at))
      .limit(5),

    db
      .select({ id: projects.id, name: projects.name, created_at: projects.created_at })
      .from(projects)
      .orderBy(desc(projects.created_at))
      .limit(5),

    db
      .select({ id: deliverables.id, title: deliverables.title, approved_at: deliverables.approved_at })
      .from(deliverables)
      .where(isNotNull(deliverables.approved_at))
      .orderBy(desc(deliverables.approved_at))
      .limit(5),

    db
      .select({
        id: time_entries.id,
        ended_at: time_entries.ended_at,
        duration_seconds: time_entries.duration_seconds,
      })
      .from(time_entries)
      .where(
        and(
          isNotNull(time_entries.ended_at),
          isNotNull(time_entries.duration_seconds)
        )
      )
      .orderBy(desc(time_entries.ended_at))
      .limit(5),
  ]);

  const activity: ActivityItem[] = [
    ...recentClients.map((c) => ({
      type: "nuovo_cliente" as const,
      label: "Nuovo cliente",
      detail: c.name,
      timestamp: c.created_at,
    })),
    ...recentProjects.map((p) => ({
      type: "nuovo_progetto" as const,
      label: "Nuovo progetto",
      detail: p.name,
      timestamp: p.created_at,
    })),
    ...recentApprovals
      .filter((d): d is typeof d & { approved_at: Date } => d.approved_at !== null)
      .map((d) => ({
        type: "deliverable_approvato" as const,
        label: "Deliverable approvato",
        detail: d.title,
        timestamp: d.approved_at,
      })),
    ...recentTimers
      .filter(
        (t): t is typeof t & { ended_at: Date; duration_seconds: number } =>
          t.ended_at !== null && t.duration_seconds !== null
      )
      .map((t) => ({
        type: "timer_stoppato" as const,
        label: "Timer stoppato",
        detail: `${Math.round(t.duration_seconds / 60)} min`,
        timestamp: t.ended_at,
      })),
  ]
    .sort((a, b) => b.timestamp.getTime() - a.timestamp.getTime())
    .slice(0, 10);

  return { kpi, activity };
}
