breakpilot-core/pitch-deck/lib/finanzplan/adapter.ts

/**
 * Adapter: Maps Finanzplan DB data → existing FMResult interface
 * used by FinancialsSlide, FinancialChart, AnnualPLTable
 */

import { Pool } from 'pg'
import { FMResult, FMComputeResponse } from '../types'
import { MonthlyValues, MONTHS, monthToDate, emptyMonthly, annualSums } from './types'

export async function finanzplanToFMResults(pool: Pool, scenarioId?: string): Promise<FMComputeResponse> {
  // Get scenario
  let sid = scenarioId
  if (!sid) {
    const { rows } = await pool.query('SELECT id FROM fp_scenarios WHERE is_default = true LIMIT 1')
    if (rows.length === 0) throw new Error('No default scenario')
    sid = rows[0].id
  }

  // Load computed data
  const [personalRes, liquidRes, betriebRes, umsatzRes, materialRes, investRes, kundenRes] = await Promise.all([
    pool.query("SELECT * FROM fp_personalkosten WHERE scenario_id = $1 ORDER BY sort_order", [sid]),
    pool.query("SELECT * FROM fp_liquiditaet WHERE scenario_id = $1 ORDER BY sort_order", [sid]),
    pool.query("SELECT * FROM fp_betriebliche_aufwendungen WHERE scenario_id = $1 ORDER BY sort_order", [sid]),
    pool.query("SELECT * FROM fp_umsatzerloese WHERE scenario_id = $1 AND section = 'revenue' AND row_label = 'GESAMTUMSATZ' LIMIT 1", [sid]),
    pool.query("SELECT * FROM fp_materialaufwand WHERE scenario_id = $1 AND row_label = 'SUMME' LIMIT 1", [sid]),
    pool.query("SELECT * FROM fp_investitionen WHERE scenario_id = $1 ORDER BY sort_order", [sid]),
    pool.query("SELECT * FROM fp_kunden_summary WHERE scenario_id = $1 AND row_label = 'Bestandskunden gesamt' LIMIT 1", [sid]),
  ])

  const personal = personalRes.rows
  const liquid = liquidRes.rows
  const betrieb = betriebRes.rows
  const customersByMonth = (kundenRes.rows[0]?.values as MonthlyValues) || emptyMonthly()

  // Helper to sum a field across personnel
  function sumPersonalField(field: string): MonthlyValues {
    const r = emptyMonthly()
    for (const p of personal) {
      const vals = p[field] || {}
      for (let m = 1; m <= MONTHS; m++) r[`m${m}`] += vals[`m${m}`] || 0
    }
    return r
  }

  const totalPersonal = sumPersonalField('values_total')
  const totalBrutto = sumPersonalField('values_brutto')
  const revenue = umsatzRes.rows[0]?.values || emptyMonthly()
  const material = materialRes.rows[0]?.values || emptyMonthly()

  // Betriebliche sonstige (without personal + abschreibungen)
  const sonstRow = betrieb.find((r: any) => r.row_label?.includes('Summe sonstige'))
  const sonstige = sonstRow?.values || emptyMonthly()

  // AfA
  const afaRow = betrieb.find((r: any) => r.row_label === 'Abschreibungen')
  const afa = afaRow?.values || emptyMonthly()

  // Liquidität endstand — match by row_type to handle both 'LIQUIDITÄT' and 'LIQUIDITAET' labels
  const liqEndRow = liquid.find((r: any) => r.row_type === 'kontostand' && r.row_label?.includes('LIQUIDIT'))
  const cashBalance = liqEndRow?.values || emptyMonthly()

  // Headcount
  const headcount = emptyMonthly()
  for (let m = 1; m <= MONTHS; m++) {
    headcount[`m${m}`] = personal.filter((p: any) => (p.values_total?.[`m${m}`] || 0) > 0).length
  }

  // Marketing (from betriebliche)
  const marketingRow = betrieb.find((r: any) => r.row_label?.includes('Werbe') || r.row_label?.includes('marketing'))
  const marketing = marketingRow?.values || emptyMonthly()

  // Build 60 monthly FMResult entries
  const results: FMResult[] = []
  let cumulativeRevenue = 0
  let prevCustomers = 0

  for (let m = 1; m <= MONTHS; m++) {
    const { year, month } = monthToDate(m)
    const rev = revenue[`m${m}`] || 0
    const mat = material[`m${m}`] || 0
    const pers = totalPersonal[`m${m}`] || 0
    const infra = sonstige[`m${m}`] || 0
    const mktg = marketing[`m${m}`] || 0
    const totalCosts = mat + pers + infra
    const grossMargin = rev > 0 ? ((rev - mat) / rev) * 100 : 0
    cumulativeRevenue += rev

    const hc = headcount[`m${m}`] || 0
    const cash = cashBalance[`m${m}`] || 0
    const burnRate = totalCosts > rev ? totalCosts - rev : 0
    const runway = burnRate > 0 ? Math.round(Math.max(0, cash) / burnRate) : 999

    results.push({
      month: m,
      year,
      month_in_year: month,
      new_customers: Math.max((customersByMonth[`m${m}`] || 0) - prevCustomers, 0),
      churned_customers: 0,
      total_customers: Math.round(customersByMonth[`m${m}`] || 0),
      mrr_eur: Math.round(rev / 1),  // monthly
      arr_eur: Math.round(rev * 12),
      revenue_eur: Math.round(rev),
      cogs_eur: Math.round(mat),
      personnel_eur: Math.round(pers),
      infra_eur: Math.round(infra),
      marketing_eur: Math.round(mktg),
      total_costs_eur: Math.round(totalCosts),
      employees_count: hc,
      gross_margin_pct: Math.round(grossMargin * 10) / 10,
      burn_rate_eur: Math.round(burnRate),
      runway_months: runway,
      cac_eur: 0,
      ltv_eur: 0,
      ltv_cac_ratio: 0,
      cash_balance_eur: Math.round(cash),
      cumulative_revenue_eur: Math.round(cumulativeRevenue),
    })
    prevCustomers = customersByMonth[`m${m}`] || 0
  }

  // Summary
  const lastMonth = results[MONTHS - 1]
  const breakEvenMonth = results.findIndex(r => r.revenue_eur > r.total_costs_eur)

  return {
    scenario_id: sid as string,
    results,
    summary: {
      final_arr: lastMonth?.arr_eur || 0,
      final_customers: lastMonth?.total_customers || 0,
      break_even_month: breakEvenMonth >= 0 ? breakEvenMonth + 1 : null,
      final_runway: lastMonth?.runway_months || 0,
      final_ltv_cac: 0,
      peak_burn: Math.max(...results.map(r => r.burn_rate_eur)),
      total_funding_needed: Math.abs(Math.min(...results.map(r => r.cash_balance_eur), 0)),
    },
  }
}