/**
 * Analytics query helpers — reports-analytics (wave-10 leaf 8).
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Route files MUST NOT import raw Drizzle tables; they import from '@zync/db/queries'.
 *
 * Money values returned as strings (NUMERIC precision preserved).
 * Hours returned as numbers (decimal hours, 2dp).
 */
import { sql } from 'drizzle-orm'
import type { Db } from '../client'

// ── Types ─────────────────────────────────────────────────────────────────────

export interface RevenuePeriodRow {
  period: string
  invoiced: string
  collected: string
  outstanding: string
}

export interface ExpenseCategoryRow {
  category: string
  total: string
  count: number
}

export interface TopCustomerRow {
  customerId: string
  name: string
  totalInvoiced: string
  paid: string
}

export interface TeamProductivityRow {
  userId: string
  name: string
  totalHours: number
  billableHours: number
  revenue: string
}

export interface OverdueInvoiceRow {
  invoiceId: string
  number: string
  customer: string
  amount: string
  daysOverdue: number
}

// ── getRevenueByPeriod ─────────────────────────────────────────────────────────

export async function getRevenueByPeriod(
  db: Db,
  tenantId: string,
  groupBy: 'day' | 'week' | 'month',
  startDate: string,
  endDate: string,
): Promise<RevenuePeriodRow[]> {
  const truncFn =
    groupBy === 'day' ? 'day' : groupBy === 'week' ? 'week' : 'month'

  const rows = await db.execute(sql`
    SELECT
      date_trunc(${truncFn}, i.issue_date::timestamptz) AS period,
      COALESCE(SUM(i.total), 0)::text                   AS invoiced,
      COALESCE(SUM(i.amount_paid), 0)::text             AS collected,
      COALESCE(SUM(i.total - i.amount_paid), 0)::text   AS outstanding
    FROM invoices i
    WHERE i.tenant_id    = ${tenantId}
      AND i.status NOT IN ('DRAFT', 'VOID')
      AND i.issue_date  >= ${startDate}::date
      AND i.issue_date  <= ${endDate}::date
    GROUP BY 1
    ORDER BY 1 ASC
  `)

  return (rows as Array<Record<string, unknown>>).map((r) => ({
    period: String(r.period instanceof Date ? r.period.toISOString().slice(0, 10) : r.period),
    invoiced: String(r.invoiced ?? '0'),
    collected: String(r.collected ?? '0'),
    outstanding: String(r.outstanding ?? '0'),
  }))
}

// ── getExpensesByCategory ─────────────────────────────────────────────────────

export async function getExpensesByCategory(
  db: Db,
  tenantId: string,
  startDate: string,
  endDate: string,
): Promise<ExpenseCategoryRow[]> {
  const rows = await db.execute(sql`
    SELECT
      COALESCE(e.expense_category, 'uncategorized') AS category,
      COALESCE(SUM(e.amount), 0)::text              AS total,
      COUNT(*)::int                                 AS count
    FROM expenses e
    WHERE e.tenant_id    = ${tenantId}
      AND e.status       = 'COMPLETED'
      AND e.expense_date >= ${startDate}::date
      AND e.expense_date <= ${endDate}::date
    GROUP BY 1
    ORDER BY SUM(e.amount) DESC NULLS LAST
  `)

  return (rows as Array<Record<string, unknown>>).map((r) => ({
    category: String(r.category),
    total: String(r.total ?? '0'),
    count: Number(r.count ?? 0),
  }))
}

// ── getTopCustomers ───────────────────────────────────────────────────────────

export async function getTopCustomers(
  db: Db,
  tenantId: string,
  limit: number,
  startDate: string,
  endDate: string,
): Promise<TopCustomerRow[]> {
  const safeLimit = Math.min(Math.max(1, limit), 100)

  const rows = await db.execute(sql`
    SELECT
      c.id                                            AS customer_id,
      c.name                                          AS name,
      COALESCE(SUM(i.total), 0)::text                 AS total_invoiced,
      COALESCE(SUM(i.amount_paid), 0)::text           AS paid
    FROM customers c
    LEFT JOIN invoices i
      ON i.customer_id = c.id
     AND i.tenant_id  = ${tenantId}
     AND i.status NOT IN ('DRAFT', 'VOID')
     AND i.issue_date >= ${startDate}::date
     AND i.issue_date <= ${endDate}::date
    WHERE c.tenant_id = ${tenantId}
      AND c.status    = 'active'
    GROUP BY c.id, c.name
    ORDER BY SUM(i.total) DESC NULLS LAST
    LIMIT ${safeLimit}
  `)

  return (rows as Array<Record<string, unknown>>).map((r) => ({
    customerId: String(r.customer_id),
    name: String(r.name),
    totalInvoiced: String(r.total_invoiced ?? '0'),
    paid: String(r.paid ?? '0'),
  }))
}

// ── getTeamProductivity ───────────────────────────────────────────────────────

export async function getTeamProductivity(
  db: Db,
  tenantId: string,
  startDate: string,
  endDate: string,
): Promise<TeamProductivityRow[]> {
  const rows = await db.execute(sql`
    SELECT
      u.id                                                              AS user_id,
      COALESCE(u.name, u.email)                                        AS name,
      ROUND(COALESCE(SUM(te.duration_seconds), 0) / 3600.0, 2)       AS total_hours,
      ROUND(COALESCE(SUM(CASE WHEN te.billable THEN te.duration_seconds ELSE 0 END), 0) / 3600.0, 2) AS billable_hours,
      ROUND(COALESCE(SUM(
        CASE WHEN te.billable THEN
          (te.duration_seconds::numeric / 3600.0) *
          COALESCE(
            pm.hourly_rate,
            (p.billing_config->>'rate_per_hour')::numeric
          )
        ELSE 0 END
      ), 0), 2)::text                                                  AS revenue
    FROM users u
    INNER JOIN tenant_memberships tm
      ON tm.user_id   = u.id
     AND tm.tenant_id = ${tenantId}
     AND tm.status    = 'active'
    LEFT JOIN time_entries te
      ON te.user_id   = u.id
     AND te.tenant_id = ${tenantId}
     AND te.started_at >= ${startDate}::timestamptz
     AND te.started_at <= (${endDate}::date + interval '1 day')::timestamptz
     AND te.duration_seconds IS NOT NULL
    LEFT JOIN projects p
      ON p.id = te.project_id
     AND p.tenant_id = ${tenantId}
    LEFT JOIN project_members pm
      ON pm.project_id = te.project_id
     AND pm.user_id    = te.user_id
     AND pm.tenant_id  = ${tenantId}
    GROUP BY u.id, u.name, u.email
    ORDER BY SUM(te.duration_seconds) DESC NULLS LAST
  `)

  return (rows as Array<Record<string, unknown>>).map((r) => ({
    userId: String(r.user_id),
    name: String(r.name),
    totalHours: Number(r.total_hours ?? 0),
    billableHours: Number(r.billable_hours ?? 0),
    revenue: String(r.revenue ?? '0'),
  }))
}

// ── getOverdueReport ──────────────────────────────────────────────────────────

export async function getOverdueReport(
  db: Db,
  tenantId: string,
): Promise<OverdueInvoiceRow[]> {
  const rows = await db.execute(sql`
    SELECT
      i.id                                               AS invoice_id,
      COALESCE(i.proforma_number, i.invoice_number, 'N/A') AS number,
      COALESCE(c.name, 'Unknown')                        AS customer,
      (i.total - i.amount_paid)::text                   AS amount,
      (CURRENT_DATE - i.due_date)::int                  AS days_overdue
    FROM invoices i
    LEFT JOIN customers c ON c.id = i.customer_id
    WHERE i.tenant_id = ${tenantId}
      AND i.status    IN ('SENT', 'APPROVED', 'TAX_ISSUED', 'PARTIALLY_PAID')
      AND i.due_date  <  CURRENT_DATE
      AND i.due_date  IS NOT NULL
    ORDER BY days_overdue DESC
  `)

  return (rows as Array<Record<string, unknown>>).map((r) => ({
    invoiceId: String(r.invoice_id),
    number: String(r.number),
    customer: String(r.customer),
    amount: String(r.amount ?? '0'),
    daysOverdue: Number(r.days_overdue ?? 0),
  }))
}