/**
 * Widget data resolver — reports-analytics custom dashboards (wave C).
 *
 * Tenant-scoped aggregations for the 12 dashboard widget types.
 * Reuses existing query helpers where siblings already compute the same metrics.
 */
import { and, eq, gte, isNull, lte, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { invoices } from '../schema/invoices'
import { invoicePayments } from '../schema/invoice-payments'
import { leads } from '../schema/marketing'
import { proposals } from '../schema/proposals'
import { tickets, ticketCategories } from '../schema/support'
import {
  getRevenueByPeriod,
  getExpensesByCategory,
  getTopCustomers,
} from './analytics'
import {
  getTimeReportByProject,
  getTimeReportByPerson,
  getTimeReportTotals,
  secondsToHours,
} from './time-reports'
import type { WidgetType } from './dashboards'

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

export interface WidgetDateRange {
  from: string
  to: string
}

export interface WidgetFilters {
  projectId?: string
  customerId?: string
  userId?: string
  categoryId?: string
}

export interface WidgetQueryEnv {
  CF_ANALYTICS_READ_TOKEN?: string
  CF_ACCOUNT_ID?: string
  CF_ANALYTICS_DATASET?: string
}

export interface WidgetRow {
  label: string
  value: number | string
  [key: string]: unknown
}

export interface WidgetDataPayload {
  widget: WidgetType
  from: string
  to: string
  labels: string[]
  values: (number | string)[]
  rows: WidgetRow[]
  meta?: Record<string, unknown>
}

const FUNNEL_EVENTS = [
  'catalog_view',
  'lead_captured',
  'proposal_accepted',
  'invoice_paid',
] as const

const AGING_BUCKETS = ['0-30', '30-60', '60-90', '90+'] as const

function safeUuid(value: string): string | null {
  return /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(value)
    ? value
    : null
}

// ── Individual widget resolvers ─────────────────────────────────────────────

export async function resolveRevenueKpi(
  db: Db,
  tenantId: string,
  range: WidgetDateRange,
): Promise<WidgetDataPayload> {
  const [totalRow] = await db
    .select({ total: sql<string>`COALESCE(SUM(${invoices.amountPaid}), 0)::text` })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.status} IN ('PAID','PARTIALLY_PAID')`,
        gte(invoices.issueDate, range.from),
        lte(invoices.issueDate, range.to),
      ),
    )

  const sparkline = await getRevenueByPeriod(db, tenantId, 'day', range.from, range.to)
  const labels = sparkline.map((r) => r.period)
  const values = sparkline.map((r) => r.collected)
  const rows: WidgetRow[] = sparkline.map((r) => ({
    label: r.period,
    value: r.collected,
    collected: r.collected,
    invoiced: r.invoiced,
    outstanding: r.outstanding,
  }))

  return {
    widget: 'revenue_kpi',
    from: range.from,
    to: range.to,
    labels,
    values,
    rows,
    meta: { totalPaid: totalRow?.total ?? '0' },
  }
}

export async function resolveInvoiceStatusPipeline(
  db: Db,
  tenantId: string,
  range: WidgetDateRange,
): Promise<WidgetDataPayload> {
  const raw = await db.execute(sql`
    SELECT
      i.status AS label,
      COUNT(*)::int AS count,
      COALESCE(SUM(i.total), 0)::text AS total
    FROM invoices i
    WHERE i.tenant_id = ${tenantId}
      AND i.issue_date >= ${range.from}::date
      AND i.issue_date <= ${range.to}::date
      AND i.status NOT IN ('VOID')
    GROUP BY i.status
    ORDER BY count DESC
  `)

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

  return {
    widget: 'invoice_status_pipeline',
    from: range.from,
    to: range.to,
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.count),
    rows,
  }
}

export async function resolveOpenInvoicesAging(
  db: Db,
  tenantId: string,
): Promise<WidgetDataPayload> {
  const raw = await db.execute(sql`
    SELECT
      CASE
        WHEN i.due_date IS NULL OR i.due_date >= CURRENT_DATE THEN '0-30'
        WHEN (CURRENT_DATE - i.due_date) <= 30 THEN '0-30'
        WHEN (CURRENT_DATE - i.due_date) <= 60 THEN '30-60'
        WHEN (CURRENT_DATE - i.due_date) <= 90 THEN '60-90'
        ELSE '90+'
      END AS bucket,
      COUNT(*)::int AS count,
      COALESCE(SUM(i.total - i.amount_paid), 0)::text AS amount
    FROM invoices i
    WHERE i.tenant_id = ${tenantId}
      AND i.status IN ('SENT','APPROVED','TAX_ISSUED','PARTIALLY_PAID')
      AND (i.total - i.amount_paid) > 0
    GROUP BY 1
  `)

  const byBucket = new Map<string, { count: number; amount: string }>()
  for (const bucket of AGING_BUCKETS) {
    byBucket.set(bucket, { count: 0, amount: '0' })
  }
  for (const r of raw as Array<Record<string, unknown>>) {
    const bucket = String(r.bucket)
    byBucket.set(bucket, {
      count: Number(r.count ?? 0),
      amount: String(r.amount ?? '0'),
    })
  }

  const rows: WidgetRow[] = AGING_BUCKETS.map((label) => {
    const b = byBucket.get(label)!
    return { label, value: b.count, count: b.count, amount: b.amount }
  })

  return {
    widget: 'open_invoices_aging',
    from: '',
    to: '',
    labels: [...AGING_BUCKETS],
    values: rows.map((r) => r.count as number),
    rows,
  }
}

export async function resolveTimeByProject(
  db: Db,
  tenantId: string,
  range: WidgetDateRange,
  filters: WidgetFilters,
): Promise<WidgetDataPayload> {
  const data = await getTimeReportByProject(db, {
    tenantId,
    from: range.from,
    to: range.to,
    projectId: filters.projectId,
  })

  const rows: WidgetRow[] = data.map((r) => ({
    label: r.projectName,
    value: secondsToHours(r.totalSeconds),
    projectId: r.projectId,
    totalHours: secondsToHours(r.totalSeconds),
    billableHours: secondsToHours(r.billableSeconds),
    entryCount: r.entryCount,
  }))

  return {
    widget: 'time_by_project',
    from: range.from,
    to: range.to,
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.value as number),
    rows,
  }
}

export async function resolveTimeByTeamMember(
  db: Db,
  tenantId: string,
  range: WidgetDateRange,
  filters: WidgetFilters,
): Promise<WidgetDataPayload> {
  const data = await getTimeReportByPerson(db, {
    tenantId,
    from: range.from,
    to: range.to,
    userId: filters.userId,
  })

  const rows: WidgetRow[] = data.map((r) => ({
    label: r.userName ?? r.userEmail ?? 'Unknown',
    value: secondsToHours(r.totalSeconds),
    userId: r.userId,
    totalHours: secondsToHours(r.totalSeconds),
    billableHours: secondsToHours(r.billableSeconds),
    entryCount: r.entryCount,
  }))

  return {
    widget: 'time_by_team_member',
    from: range.from,
    to: range.to,
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.value as number),
    rows,
  }
}

export async function resolveBillableVsNonbillable(
  db: Db,
  tenantId: string,
  range: WidgetDateRange,
  filters: WidgetFilters,
): Promise<WidgetDataPayload> {
  const totals = await getTimeReportTotals(db, {
    tenantId,
    from: range.from,
    to: range.to,
    userId: filters.userId,
    projectId: filters.projectId,
  })

  const billableHours = secondsToHours(totals.billableSeconds)
  const nonBillableHours = secondsToHours(totals.totalSeconds - totals.billableSeconds)
  const rows: WidgetRow[] = [
    { label: 'Billable', value: billableHours, hours: billableHours },
    { label: 'Non-billable', value: nonBillableHours, hours: nonBillableHours },
  ]

  return {
    widget: 'billable_vs_nonbillable',
    from: range.from,
    to: range.to,
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.value as number),
    rows,
    meta: { totalHours: secondsToHours(totals.totalSeconds), entryCount: totals.entryCount },
  }
}

export async function resolveCustomerRevenue(
  db: Db,
  tenantId: string,
  range: WidgetDateRange,
): Promise<WidgetDataPayload> {
  const data = await getTopCustomers(db, tenantId, 20, range.from, range.to)
  const rows: WidgetRow[] = data.map((r) => ({
    label: r.name,
    value: r.totalInvoiced,
    customerId: r.customerId,
    totalInvoiced: r.totalInvoiced,
    paid: r.paid,
  }))

  return {
    widget: 'customer_revenue',
    from: range.from,
    to: range.to,
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.totalInvoiced as string),
    rows,
  }
}

export async function resolveExpenseBreakdown(
  db: Db,
  tenantId: string,
  range: WidgetDateRange,
): Promise<WidgetDataPayload> {
  const data = await getExpensesByCategory(db, tenantId, range.from, range.to)
  const rows: WidgetRow[] = data.map((r) => ({
    label: r.category,
    value: r.total,
    total: r.total,
    count: r.count,
  }))

  return {
    widget: 'expense_breakdown',
    from: range.from,
    to: range.to,
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.total as string),
    rows,
  }
}

async function runAeCount(
  env: WidgetQueryEnv,
  tenantId: string,
  range: WidgetDateRange,
  sqlBody: string,
): Promise<number> {
  if (!env.CF_ANALYTICS_READ_TOKEN || !env.CF_ACCOUNT_ID) return 0

  const url = `https://api.cloudflare.com/client/v4/accounts/${env.CF_ACCOUNT_ID}/analytics_engine/sql`
  const res = await fetch(url, {
    method: 'POST',
    headers: {
      Authorization: `Bearer ${env.CF_ANALYTICS_READ_TOKEN}`,
      'Content-Type': 'text/plain',
    },
    body: sqlBody,
  })

  if (!res.ok) return 0
  const body = (await res.json()) as { data?: Array<{ count: number }>; rows?: Array<{ count: number }> }
  const rows = body.data ?? body.rows ?? []
  return Number(rows[0]?.count ?? 0)
}

export async function resolveLeadsFunnel(
  db: Db,
  env: WidgetQueryEnv,
  tenantId: string,
  range: WidgetDateRange,
): Promise<WidgetDataPayload & { unavailable?: boolean; reason?: string }> {
  if (!env.CF_ANALYTICS_READ_TOKEN) {
    return {
      widget: 'leads_funnel',
      from: range.from,
      to: range.to,
      labels: [],
      values: [],
      rows: [],
      unavailable: true,
      reason: 'analytics_token_unconfigured',
    }
  }

  const dataset = env.CF_ANALYTICS_DATASET ?? 'zync_funnel'
  const safeTenant = safeUuid(tenantId)
  if (!safeTenant) {
    return {
      widget: 'leads_funnel',
      from: range.from,
      to: range.to,
      labels: [],
      values: [],
      rows: [],
      unavailable: true,
      reason: 'invalid_tenant',
    }
  }

  const tsFrom = `toDateTime('${range.from} 00:00:00')`
  const tsTo = `toDateTime('${range.to} 00:00:00') + INTERVAL '1' DAY`

  // catalog_view emitter indexes by tenantId (asymmetric vs lead_captured which indexes by event name)
  // — reader matches the writer per-event; do not force symmetry.
  const catalogCount = await runAeCount(
    env,
    tenantId,
    range,
    `SELECT count() AS count FROM ${dataset}
     WHERE index1 = '${safeTenant}' AND blob1 = '${safeTenant}'
       AND timestamp >= ${tsFrom} AND timestamp < ${tsTo}`,
  )

  const leadCapturedCount = await runAeCount(
    env,
    tenantId,
    range,
    `SELECT count() AS count FROM ${dataset}
     WHERE index1 = 'lead_captured' AND blob1 = '${safeTenant}'
       AND timestamp >= ${tsFrom} AND timestamp < ${tsTo}`,
  )

  const [proposalAcceptedRow] = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(proposals)
    .where(
      and(
        eq(proposals.tenantId, tenantId),
        eq(proposals.status, 'accepted'),
        gte(proposals.updatedAt, new Date(`${range.from}T00:00:00Z`)),
        lte(proposals.updatedAt, new Date(`${range.to}T23:59:59Z`)),
      ),
    )

  const [invoicePaidRow] = await db
    .select({ count: sql<number>`count(DISTINCT ${invoicePayments.invoiceId})::int` })
    .from(invoicePayments)
    .where(
      and(
        eq(invoicePayments.tenantId, tenantId),
        gte(invoicePayments.paidAt, new Date(`${range.from}T00:00:00Z`)),
        lte(invoicePayments.paidAt, new Date(`${range.to}T23:59:59Z`)),
      ),
    )

  const counts: Record<(typeof FUNNEL_EVENTS)[number], number> = {
    catalog_view: catalogCount,
    lead_captured: leadCapturedCount,
    proposal_accepted: proposalAcceptedRow?.count ?? 0,
    invoice_paid: invoicePaidRow?.count ?? 0,
  }

  let prev: number | null = null
  const rows: WidgetRow[] = FUNNEL_EVENTS.map((event) => {
    const count = counts[event]
    const pctOfPrev =
      prev === null ? 100 : prev > 0 ? Math.round((count / prev) * 1000) / 10 : 0
    prev = count
    return { label: event, value: count, count, pctOfPrev }
  })

  return {
    widget: 'leads_funnel',
    from: range.from,
    to: range.to,
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.count as number),
    rows,
  }
}

export async function resolveLeadPipelineByStage(
  db: Db,
  tenantId: string,
): Promise<WidgetDataPayload> {
  const raw = await db
    .select({ stage: leads.stage, count: sql<number>`count(*)::int` })
    .from(leads)
    .where(and(eq(leads.tenantId, tenantId), isNull(leads.archivedAt)))
    .groupBy(leads.stage)
    .orderBy(leads.stage)

  const rows: WidgetRow[] = raw.map((r) => ({
    label: r.stage,
    value: r.count,
    count: r.count,
  }))

  return {
    widget: 'lead_pipeline_by_stage',
    from: '',
    to: '',
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.count as number),
    rows,
  }
}

export async function resolveTicketResolutionTime(
  db: Db,
  tenantId: string,
  range: WidgetDateRange,
): Promise<WidgetDataPayload> {
  const raw = await db.execute(sql`
    SELECT
      CASE
        WHEN days_open <= 1 THEN '0-1 days'
        WHEN days_open <= 3 THEN '2-3 days'
        WHEN days_open <= 7 THEN '4-7 days'
        WHEN days_open <= 14 THEN '8-14 days'
        ELSE '15+ days'
      END AS bucket,
      COUNT(*)::int AS count,
      ROUND(AVG(days_open)::numeric, 1)::float AS avg_days
    FROM (
      SELECT
        EXTRACT(EPOCH FROM (COALESCE(t.resolved_at, t.closed_at, NOW()) - t.created_at)) / 86400.0 AS days_open
      FROM tickets t
      WHERE t.tenant_id = ${tenantId}
        AND t.deleted_at IS NULL
        AND t.status IN ('resolved','closed')
        AND t.created_at >= ${range.from}::timestamptz
        AND t.created_at < (${range.to}::date + interval '1 day')::timestamptz
    ) sub
    GROUP BY 1
    ORDER BY MIN(days_open)
  `)

  const rows = (raw as Array<Record<string, unknown>>).map((r) => ({
    label: String(r.bucket),
    value: Number(r.count ?? 0),
    count: Number(r.count ?? 0),
    avgDays: Number(r.avg_days ?? 0),
  }))

  const [avgRow] = await db.execute(sql`
    SELECT ROUND(AVG(
      EXTRACT(EPOCH FROM (COALESCE(t.resolved_at, t.closed_at) - t.created_at)) / 86400.0
    )::numeric, 1)::float AS avg_days
    FROM tickets t
    WHERE t.tenant_id = ${tenantId}
      AND t.deleted_at IS NULL
      AND t.status IN ('resolved','closed')
      AND t.created_at >= ${range.from}::timestamptz
      AND t.created_at < (${range.to}::date + interval '1 day')::timestamptz
  `)

  const avgDays = Number((avgRow as Record<string, unknown>)?.avg_days ?? 0)

  return {
    widget: 'ticket_resolution_time',
    from: range.from,
    to: range.to,
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.count),
    rows,
    meta: { avgDaysOpen: avgDays },
  }
}

export async function resolveTicketVolumeByCategory(
  db: Db,
  tenantId: string,
  range: WidgetDateRange,
  filters: WidgetFilters = {},
): Promise<WidgetDataPayload> {
  const raw = await db
    .select({
      label: sql<string>`COALESCE(${ticketCategories.name}, 'Uncategorized')`,
      count: sql<number>`count(*)::int`,
      categoryId: tickets.categoryId,
    })
    .from(tickets)
    .leftJoin(ticketCategories, eq(tickets.categoryId, ticketCategories.id))
    .where(
      and(
        eq(tickets.tenantId, tenantId),
        isNull(tickets.deletedAt),
        gte(tickets.createdAt, new Date(`${range.from}T00:00:00Z`)),
        lte(tickets.createdAt, new Date(`${range.to}T23:59:59Z`)),
        filters.categoryId ? eq(tickets.categoryId, filters.categoryId) : undefined,
      ),
    )
    .groupBy(ticketCategories.name, tickets.categoryId)
    .orderBy(sql`count(*) DESC`)

  const rows: WidgetRow[] = raw.map((r) => ({
    label: r.label,
    value: r.count,
    count: r.count,
    categoryId: r.categoryId,
  }))

  return {
    widget: 'ticket_volume_by_category',
    from: range.from,
    to: range.to,
    labels: rows.map((r) => r.label),
    values: rows.map((r) => r.count as number),
    rows,
  }
}

export async function resolveWidgetData(
  db: Db,
  env: WidgetQueryEnv,
  tenantId: string,
  widget: WidgetType,
  range: WidgetDateRange,
  filters: WidgetFilters = {},
): Promise<WidgetDataPayload | (WidgetDataPayload & { unavailable?: boolean; reason?: string })> {
  switch (widget) {
    case 'revenue_kpi':
      return resolveRevenueKpi(db, tenantId, range)
    case 'invoice_status_pipeline':
      return resolveInvoiceStatusPipeline(db, tenantId, range)
    case 'open_invoices_aging':
      return resolveOpenInvoicesAging(db, tenantId)
    case 'time_by_project':
      return resolveTimeByProject(db, tenantId, range, filters)
    case 'time_by_team_member':
      return resolveTimeByTeamMember(db, tenantId, range, filters)
    case 'billable_vs_nonbillable':
      return resolveBillableVsNonbillable(db, tenantId, range, filters)
    case 'customer_revenue':
      return resolveCustomerRevenue(db, tenantId, range)
    case 'expense_breakdown':
      return resolveExpenseBreakdown(db, tenantId, range)
    case 'leads_funnel':
      return resolveLeadsFunnel(db, env, tenantId, range)
    case 'lead_pipeline_by_stage':
      return resolveLeadPipelineByStage(db, tenantId)
    case 'ticket_resolution_time':
      return resolveTicketResolutionTime(db, tenantId, range)
    case 'ticket_volume_by_category':
      return resolveTicketVolumeByCategory(db, tenantId, range, filters)
    default: {
      const _exhaustive: never = widget
      throw new Error(`Unknown widget type: ${_exhaustive}`)
    }
  }
}