/**
 * P&L aggregation service — financial-statements (wave-13).
 *
 * Accrual-basis Profit & Loss: revenue recognized when invoiced (TAX_ISSUED
 * status and equivalent), expenses on their expense_date.
 *
 * All monetary values in ILS (invoice totals use face-value `total` column).
 *
 * Design decision: credit notes (source='credit_note') are excluded from
 * gross_revenue and deducted explicitly via credit_notes line — prevents
 * double-counting their negative totals.
 */
import { sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'

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

export interface ProfitLossReport {
  period: { from: string; to: string } // YYYY-MM-DD
  revenue: {
    gross: number
    credit_notes: number
    bad_debts: number
    net: number
  }
  expenses: {
    total: number
    by_category: Record<string, number>
  }
  contractor_payouts: number
  gross_profit: number
  net_profit: number
  gross_margin_pct: number
  comparison?: Omit<ProfitLossReport, 'comparison'>
}

interface PLArgs {
  from: string
  to: string
  compare?: boolean
}

// ── Implementation ─────────────────────────────────────────────────────────────

async function computePL(
  db: Db | DbTx,
  tenantId: string,
  from: string,
  to: string,
): Promise<Omit<ProfitLossReport, 'comparison'>> {
  // ── Revenue ────────────────────────────────────────────────────────────────

  // Gross revenue: recognized invoices excluding credit notes
  const grossResult = await db.execute(sql`
    SELECT COALESCE(SUM(total), 0)::numeric AS gross
    FROM invoices
    WHERE tenant_id = ${tenantId}::uuid
      AND status IN ('TAX_ISSUED', 'PARTIALLY_PAID', 'PAID')
      AND source <> 'credit_note'
      AND tax_issue_date BETWEEN ${from}::date AND ${to}::date
  `)
  const gross = Number((grossResult[0] as { gross: string }).gross ?? 0)

  // Credit notes: absolute value of negative-total credit-note invoices
  const creditResult = await db.execute(sql`
    SELECT COALESCE(ABS(SUM(total)), 0)::numeric AS credit_notes
    FROM invoices
    WHERE tenant_id = ${tenantId}::uuid
      AND source = 'credit_note'
      AND status IN ('TAX_ISSUED', 'PARTIALLY_PAID', 'PAID')
      AND tax_issue_date BETWEEN ${from}::date AND ${to}::date
  `)
  const creditNotes = Number(
    (creditResult[0] as { credit_notes: string }).credit_notes ?? 0,
  )

  // Bad debts: invoices written off in period
  const badDebtResult = await db.execute(sql`
    SELECT COALESCE(SUM(total), 0)::numeric AS bad_debts
    FROM invoices
    WHERE tenant_id = ${tenantId}::uuid
      AND bad_debt_at IS NOT NULL
      AND bad_debt_at::date BETWEEN ${from}::date AND ${to}::date
  `)
  const badDebts = Number(
    (badDebtResult[0] as { bad_debts: string }).bad_debts ?? 0,
  )

  const netRevenue = gross - creditNotes - badDebts

  // ── Expenses by category ───────────────────────────────────────────────────

  const expResult = await db.execute(sql`
    SELECT
      COALESCE(expense_category, 'Other') AS category,
      SUM(amount * business_percent::numeric / 100)::numeric AS total
    FROM expenses
    WHERE tenant_id = ${tenantId}::uuid
      AND status = 'COMPLETED'
      AND expense_date BETWEEN ${from}::date AND ${to}::date
    GROUP BY COALESCE(expense_category, 'Other')
  `)

  const byCategory: Record<string, number> = {}
  let totalExpenses = 0
  for (const row of expResult as unknown as Array<{ category: string; total: string }>) {
    const amt = Number(row.total ?? 0)
    byCategory[row.category] = amt
    totalExpenses += amt
  }

  // ── Contractor payouts ─────────────────────────────────────────────────────

  const payoutResult = await db.execute(sql`
    SELECT COALESCE(SUM(net_amount), 0)::numeric AS contractor_payouts
    FROM payout_bills
    WHERE tenant_id = ${tenantId}::uuid
      AND status = 'PAID'
      AND paid_at::date BETWEEN ${from}::date AND ${to}::date
  `)
  const contractorPayouts = Number(
    (payoutResult[0] as { contractor_payouts: string }).contractor_payouts ?? 0,
  )

  // ── Derived totals ─────────────────────────────────────────────────────────

  const grossProfit = netRevenue - totalExpenses
  const netProfit = grossProfit - contractorPayouts
  const grossMarginPct =
    netRevenue === 0
      ? 0
      : Math.round((grossProfit / netRevenue) * 100 * 10) / 10

  return {
    period: { from, to },
    revenue: {
      gross,
      credit_notes: creditNotes,
      bad_debts: badDebts,
      net: netRevenue,
    },
    expenses: {
      total: totalExpenses,
      by_category: byCategory,
    },
    contractor_payouts: contractorPayouts,
    gross_profit: grossProfit,
    net_profit: netProfit,
    gross_margin_pct: grossMarginPct,
  }
}

export async function getProfitLoss(
  db: Db | DbTx,
  tenantId: string,
  args: PLArgs,
): Promise<ProfitLossReport> {
  const { from, to, compare } = args

  const current = await computePL(db, tenantId, from, to)

  if (!compare) {
    return current
  }

  // Comparison: immediately preceding period of equal duration
  const fromDate = new Date(from)
  const toDate = new Date(to)
  const durationMs = toDate.getTime() - fromDate.getTime()
  const prevTo = new Date(fromDate.getTime() - 24 * 60 * 60 * 1000) // from - 1 day
  const prevFrom = new Date(prevTo.getTime() - durationMs)

  const prevFromStr = prevFrom.toISOString().slice(0, 10)
  const prevToStr = prevTo.toISOString().slice(0, 10)

  const comparison = await computePL(db, tenantId, prevFromStr, prevToStr)

  return { ...current, comparison }
}