/**
 * Cash Flow aggregation service — financial-statements (wave-13).
 *
 * Cash-basis: actual money movement (invoice_payments.paid_at,
 * expenses.expense_date, payout_bills.paid_at).
 *
 * Receivables are point-in-time (not period-bounded): the current snapshot
 * of outstanding invoices regardless of query period.
 *
 * All monetary values in ILS.
 */
import { sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'

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

export interface CashFlowReport {
  period: { from: string; to: string }
  received_from_customers: number
  expenses_paid: number
  contractor_payouts: number
  net_operating: number
  receivables: {
    tax_issued: number
    partially_paid_balance: number
  }
}

interface CFArgs {
  from: string
  to: string
}

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

export async function getCashFlow(
  db: Db | DbTx,
  tenantId: string,
  args: CFArgs,
): Promise<CashFlowReport> {
  const { from, to } = args

  // ── Cash received from customers ──────────────────────────────────────────

  const receivedResult = await db.execute(sql`
    SELECT COALESCE(SUM(amount), 0)::numeric AS received
    FROM invoice_payments
    WHERE tenant_id = ${tenantId}::uuid
      AND paid_at::date BETWEEN ${from}::date AND ${to}::date
  `)
  const received = Number(
    (receivedResult[0] as { received: string }).received ?? 0,
  )

  // ── Expenses paid ─────────────────────────────────────────────────────────

  const expResult = await db.execute(sql`
    SELECT COALESCE(SUM(amount * business_percent::numeric / 100), 0)::numeric AS expenses_paid
    FROM expenses
    WHERE tenant_id = ${tenantId}::uuid
      AND status = 'COMPLETED'
      AND expense_date BETWEEN ${from}::date AND ${to}::date
  `)
  const expensesPaid = Number(
    (expResult[0] as { expenses_paid: string }).expenses_paid ?? 0,
  )

  // ── Contractor payouts (cash) ─────────────────────────────────────────────

  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,
  )

  const netOperating = received - expensesPaid - contractorPayouts

  // ── Receivables (point-in-time — no period filter) ────────────────────────

  const taxIssuedResult = await db.execute(sql`
    SELECT COALESCE(SUM(total), 0)::numeric AS tax_issued
    FROM invoices
    WHERE tenant_id = ${tenantId}::uuid
      AND status = 'TAX_ISSUED'
  `)
  const taxIssued = Number(
    (taxIssuedResult[0] as { tax_issued: string }).tax_issued ?? 0,
  )

  const partialResult = await db.execute(sql`
    SELECT COALESCE(
      SUM(total - COALESCE(amount_paid, 0)),
      0
    )::numeric AS partially_paid_balance
    FROM invoices
    WHERE tenant_id = ${tenantId}::uuid
      AND status = 'PARTIALLY_PAID'
  `)
  const partiallyPaidBalance = Number(
    (partialResult[0] as { partially_paid_balance: string })
      .partially_paid_balance ?? 0,
  )

  return {
    period: { from, to },
    received_from_customers: received,
    expenses_paid: expensesPaid,
    contractor_payouts: contractorPayouts,
    net_operating: netOperating,
    receivables: {
      tax_issued: taxIssued,
      partially_paid_balance: partiallyPaidBalance,
    },
  }
}