/**
 * Revenue forecasting query helpers — revenue-forecasting (spec 116).
 */
import { and, eq, inArray } from 'drizzle-orm'
import type { Db } from '../client'
import { invoices } from '../schema/invoices'
import { leads } from '../schema/marketing'
import { recurringInvoiceTemplates } from '../schema/recurring-invoices'
import { tenantSettings } from '../schema/tenants'
import {
  DEFAULT_LEAD_STAGE_PROBABILITIES,
  type LeadStageProbabilities,
  type RevenueForecastMonth,
  type RevenueForecastMonths,
  type RevenueForecastResponse,
} from '@zync/types'

export interface ForecastWindow {
  startMonth: string
  endMonth: string
  startDate: string
  endDate: string
  months: RevenueForecastMonths
  today: string
}

interface InvoiceForecastRow {
  status: string
  total: string | number | null
  amountPaid: string | number | null
  dueDate: string | null
  issueDate: string | null
}

interface LeadForecastRow {
  stage: string
  estimatedValue: string | number | null
  reengagementAt: string | Date | null
  archivedAt: string | Date | null
}

interface TemplateForecastRow {
  status: string
  nextGenerationDate: string
  frequency: string
  frequencyDay: number | null
  endDate: string | null
  vatRate: string | number | null
  lineItems: unknown
}

interface RecurringLineItem {
  quantity?: number | string | null
  unitPrice?: number | string | null
  unit_price?: number | string | null
  discountPct?: number | string | null
  discount_pct?: number | string | null
  taxable?: boolean | null
}

const COMMITTED_STATUSES = ['SENT', 'TAX_ISSUED', 'PARTIALLY_PAID'] as const

function roundCurrency(value: number): number {
  return Math.round((value + Number.EPSILON) * 100) / 100
}

function monthKeyFromParts(year: number, monthIndex: number): string {
  return `${year}-${String(monthIndex + 1).padStart(2, '0')}`
}

function monthKeyFromDate(date: Date): string {
  return monthKeyFromParts(date.getUTCFullYear(), date.getUTCMonth())
}

function dateFromMonthKey(month: string): Date {
  return new Date(`${month}-01T00:00:00.000Z`)
}

function toIsoDate(date: Date): string {
  return date.toISOString().slice(0, 10)
}

function lastDayOfMonthUtc(year: number, monthIndex: number): number {
  return new Date(Date.UTC(year, monthIndex + 1, 0)).getUTCDate()
}

function addMonths(month: string, offset: number): string {
  const base = dateFromMonthKey(month)
  return monthKeyFromParts(base.getUTCFullYear(), base.getUTCMonth() + offset)
}

function getMonthSequence(startMonth: string, count: number): string[] {
  return Array.from({ length: count }, (_, index) => addMonths(startMonth, index))
}

function parseNumber(value: unknown): number {
  if (typeof value === 'number') return Number.isFinite(value) ? value : 0
  if (typeof value === 'string') {
    const parsed = Number.parseFloat(value)
    return Number.isFinite(parsed) ? parsed : 0
  }
  return 0
}

function isMonthInWindow(month: string, window: ForecastWindow): boolean {
  return month >= window.startMonth && month <= window.endMonth
}

function toMonthKey(value: string | Date | null | undefined): string | null {
  if (!value) return null
  if (typeof value === 'string') {
    if (/^\d{4}-\d{2}$/.test(value)) return value
    if (/^\d{4}-\d{2}-\d{2}/.test(value)) return value.slice(0, 7)
    const parsed = new Date(value)
    return Number.isNaN(parsed.getTime()) ? null : monthKeyFromDate(parsed)
  }
  return Number.isNaN(value.getTime()) ? null : monthKeyFromDate(value)
}

function clampDay(year: number, monthIndex: number, day: number): Date {
  const clampedDay = Math.max(1, Math.min(day, lastDayOfMonthUtc(year, monthIndex)))
  return new Date(Date.UTC(year, monthIndex, clampedDay))
}

function advanceRecurringDate(
  currentDate: string,
  frequency: string,
  frequencyDay: number | null,
): string {
  const current = new Date(`${currentDate}T00:00:00.000Z`)
  if (Number.isNaN(current.getTime())) return currentDate

  if (frequency === 'weekly') {
    current.setUTCDate(current.getUTCDate() + 7)
    return toIsoDate(current)
  }

  const baseDay = frequencyDay && frequencyDay > 0 ? frequencyDay : current.getUTCDate()
  const monthDelta =
    frequency === 'quarterly' ? 3 :
      frequency === 'yearly' ? 12 :
        1
  const nextYear = current.getUTCFullYear()
  const nextMonthIndex = current.getUTCMonth() + monthDelta
  return toIsoDate(clampDay(nextYear, nextMonthIndex, baseDay))
}

function normalizeStageProbabilities(
  probabilities: Partial<LeadStageProbabilities> | null | undefined,
): LeadStageProbabilities {
  return {
    NEW: parseNumber(probabilities?.NEW) || DEFAULT_LEAD_STAGE_PROBABILITIES.NEW,
    CONTACTED: parseNumber(probabilities?.CONTACTED) || DEFAULT_LEAD_STAGE_PROBABILITIES.CONTACTED,
    QUALIFIED: parseNumber(probabilities?.QUALIFIED) || DEFAULT_LEAD_STAGE_PROBABILITIES.QUALIFIED,
    PROPOSAL: parseNumber(probabilities?.PROPOSAL) || DEFAULT_LEAD_STAGE_PROBABILITIES.PROPOSAL,
  }
}

export function buildForecastWindow(now: Date, months: RevenueForecastMonths): ForecastWindow {
  const start = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), 1))
  const end = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth() + months, 0))
  return {
    startMonth: monthKeyFromDate(start),
    endMonth: monthKeyFromDate(end),
    startDate: toIsoDate(start),
    endDate: toIsoDate(end),
    months,
    today: toIsoDate(now),
  }
}

export function summarizeCommittedInvoices(
  rows: InvoiceForecastRow[],
  window: ForecastWindow,
  recoveryRate: number,
): Map<string, number> {
  const buckets = new Map<string, number>()
  for (const row of rows) {
    if (!COMMITTED_STATUSES.includes(row.status as (typeof COMMITTED_STATUSES)[number])) continue
    const bucketMonth = toMonthKey(row.dueDate ?? row.issueDate)
    if (!bucketMonth || !isMonthInWindow(bucketMonth, window)) continue
    const outstanding =
      row.status === 'PARTIALLY_PAID'
        ? Math.max(0, parseNumber(row.total) - parseNumber(row.amountPaid))
        : parseNumber(row.total)
    if (outstanding <= 0) continue
    const discounted =
      row.dueDate && row.dueDate < window.today
        ? outstanding * recoveryRate
        : outstanding
    buckets.set(bucketMonth, roundCurrency((buckets.get(bucketMonth) ?? 0) + discounted))
  }
  return buckets
}

export function summarizeProjectedLeads(
  rows: LeadForecastRow[],
  window: ForecastWindow,
  stageProbabilities: LeadStageProbabilities | null | undefined,
): Map<string, number> {
  const buckets = new Map<string, number>()
  const probabilities = normalizeStageProbabilities(stageProbabilities)
  for (const row of rows) {
    if (row.archivedAt) continue
    if (row.stage === 'WON' || row.stage === 'LOST') continue
    const estimatedValue = parseNumber(row.estimatedValue)
    if (estimatedValue <= 0) continue
    const probability = parseNumber(probabilities[row.stage as keyof LeadStageProbabilities])
    if (probability <= 0) continue
    const weightedValue = estimatedValue * (probability / 100)
    const expectedMonth = toMonthKey(row.reengagementAt)

    if (expectedMonth && isMonthInWindow(expectedMonth, window)) {
      buckets.set(expectedMonth, roundCurrency((buckets.get(expectedMonth) ?? 0) + weightedValue))
      continue
    }

    const spreadMonths = getMonthSequence(window.startMonth, Math.min(3, window.months))
    const perMonth = weightedValue / spreadMonths.length
    for (const month of spreadMonths) {
      buckets.set(month, roundCurrency((buckets.get(month) ?? 0) + perMonth))
    }
  }
  return buckets
}

function getLineItemSubtotal(item: RecurringLineItem): number {
  const quantity = parseNumber(item.quantity)
  const unitPrice = parseNumber(item.unitPrice ?? item.unit_price)
  const discountPct = parseNumber(item.discountPct ?? item.discount_pct)
  return quantity * unitPrice * (1 - discountPct / 100)
}

function getTemplateOccurrenceAmount(lineItems: unknown, vatRate: string | number | null): number {
  if (!Array.isArray(lineItems)) return 0
  const rate = parseNumber(vatRate)
  let total = 0

  for (const item of lineItems as RecurringLineItem[]) {
    const subtotal = getLineItemSubtotal(item)
    if (subtotal <= 0) continue
    const taxable = item.taxable !== false
    total += taxable ? subtotal * (1 + rate) : subtotal
  }

  return roundCurrency(total)
}

export function summarizeScheduledTemplates(
  rows: TemplateForecastRow[],
  window: ForecastWindow,
): Map<string, number> {
  const buckets = new Map<string, number>()
  for (const row of rows) {
    if (row.status !== 'active') continue
    const amount = getTemplateOccurrenceAmount(row.lineItems, row.vatRate)
    if (amount <= 0) continue

    let occurrence = row.nextGenerationDate
    while (occurrence <= window.endDate) {
      if (row.endDate && occurrence > row.endDate) break
      if (occurrence >= window.startDate) {
        const month = occurrence.slice(0, 7)
        buckets.set(month, roundCurrency((buckets.get(month) ?? 0) + amount))
      }
      const next = advanceRecurringDate(occurrence, row.frequency, row.frequencyDay)
      if (next <= occurrence) break
      occurrence = next
    }
  }
  return buckets
}

function mergeMonthlyForecast(
  months: string[],
  committed: Map<string, number>,
  scheduled: Map<string, number>,
  projected: Map<string, number>,
): RevenueForecastMonth[] {
  return months.map((month) => {
    const committedValue = roundCurrency(committed.get(month) ?? 0)
    const scheduledValue = roundCurrency(scheduled.get(month) ?? 0)
    const projectedValue = roundCurrency(projected.get(month) ?? 0)
    return {
      month,
      committed: committedValue,
      scheduled: scheduledValue,
      projected: projectedValue,
      total: roundCurrency(committedValue + scheduledValue + projectedValue),
    }
  })
}

export async function getRevenueForecast(
  db: Db,
  tenantId: string,
  months: RevenueForecastMonths,
  now = new Date(),
): Promise<RevenueForecastResponse> {
  const window = buildForecastWindow(now, months)
  const [settingsRow, invoiceRows, leadRows, templateRows] = await Promise.all([
    db
      .select({ leadStageProbabilities: tenantSettings.leadStageProbabilities })
      .from(tenantSettings)
      .where(eq(tenantSettings.tenantId, tenantId))
      .limit(1)
      .then((rows) => rows[0] ?? null),
    db
      .select({
        status: invoices.status,
        total: invoices.total,
        amountPaid: invoices.amountPaid,
        dueDate: invoices.dueDate,
        issueDate: invoices.issueDate,
      })
      .from(invoices)
      .where(
        and(
          eq(invoices.tenantId, tenantId),
          inArray(invoices.status, [...COMMITTED_STATUSES]),
        ),
      ),
    db
      .select({
        stage: leads.stage,
        estimatedValue: leads.estimatedValue,
        reengagementAt: leads.reengagementAt,
        archivedAt: leads.archivedAt,
      })
      .from(leads)
      .where(eq(leads.tenantId, tenantId)),
    db
      .select({
        status: recurringInvoiceTemplates.status,
        nextGenerationDate: recurringInvoiceTemplates.nextGenerationDate,
        frequency: recurringInvoiceTemplates.frequency,
        frequencyDay: recurringInvoiceTemplates.frequencyDay,
        endDate: recurringInvoiceTemplates.endDate,
        vatRate: recurringInvoiceTemplates.vatRate,
        lineItems: recurringInvoiceTemplates.lineItems,
      })
      .from(recurringInvoiceTemplates)
      .where(eq(recurringInvoiceTemplates.tenantId, tenantId)),
  ])

  const probabilities = normalizeStageProbabilities(settingsRow?.leadStageProbabilities)
  const committed = summarizeCommittedInvoices(invoiceRows, window, 0.8)
  const scheduled = summarizeScheduledTemplates(templateRows, window)
  const projected = summarizeProjectedLeads(leadRows, window, probabilities)
  const monthSequence = getMonthSequence(window.startMonth, window.months)
  const monthly = mergeMonthlyForecast(monthSequence, committed, scheduled, projected)

  const summary = monthly.reduce(
    (acc, row) => ({
      committed: roundCurrency(acc.committed + row.committed),
      scheduled: roundCurrency(acc.scheduled + row.scheduled),
      projected: roundCurrency(acc.projected + row.projected),
      total: roundCurrency(acc.total + row.total),
    }),
    { committed: 0, scheduled: 0, projected: 0, total: 0 },
  )

  return { summary, monthly }
}