/**
 * DHL "Nachbelastung" attachment for a fulfillment fee invoice — data side.
 *
 * Every "Nachbelastung DHL Paket (Zuschläge lt. DHL-Rechnung): N Sendungen"
 * position on a generated fee invoice is backed by one `cust_fulfillmentfeeline`
 * per shipment (`FulfillmentPricingRule = 'SHIPPINGFEE_NACHBELASTUNG'`), written by
 * `../logship-scripts/accounting/02-Fill-Accounting/create_fulfillment.py`. The fee
 * lines point at the generated order through `c_order_refinv_id` (set by
 * `server/api/fulfillment/batch-process-lines.post.ts`); the invoice points at the
 * order through `C_Invoice.C_Order_ID`.
 *
 * The fee line `Description` is a snapshot of the DHL invoice wording at booking
 * time ("… lt. DHL-Rechnung 1847900910: DHL PAKET (GK) bis 2 kg 4,15 · Maut- und
 * CO2-Zuschlag 0,19 … = 5,00 EUR; bereits berechnet 4,91 EUR; Nachbelastung 0,09 EUR")
 * and is the source of truth for the report. When it is missing or unparseable the
 * `m_inout.ext_freight_*` buckets are used with the same component names the
 * Python script prints.
 */
import { Pool } from 'pg'

export interface DhlPosition { label: string; amount: number }

export interface ParsedNachbelastung {
  shipmentNo: string | null
  shipDateText: string | null
  dhlInvoiceNos: string[]
  source: 'dhl' | 'calc' | 'unknown'
  positions: DhlPosition[]
  dhlTotal: number | null
  booked: number | null
  difference: number | null
  /** Text after the shipment prefix — "lt. DHL-Rechnung …; bereits berechnet …" */
  detailText: string
}

/** Component names exactly as DHL prints them (mirrors DHL_FEE_COMPONENTS in create_fulfillment.py). */
export const DHL_FEE_COMPONENTS: Array<{ column: string; label: string | null }> = [
  { column: 'ext_freight_cost', label: null },
  { column: 'ext_freight_co2', label: 'Maut- und CO2-Zuschlag' },
  { column: 'ext_freight_energy', label: 'Energiezuschlag DHL Paket (GK)' },
  { column: 'ext_freight_peak', label: 'Peak-Zuschlag DHL Paket' },
  { column: 'ext_freight_codierung', label: 'DHL Paket Codierentgelt' },
  { column: 'ext_freight_insurance', label: 'Transportversicherung' },
  { column: 'ext_freight_returnfees', label: 'Rücksendeentgelt Paket national' },
  { column: 'ext_freight_intpremium', label: 'DHL PAKET International (GK) Premium' },
  { column: 'ext_freight_others', label: 'weitere Entgelte lt. DHL-Rechnung' }
]

export const NACHBELASTUNG_PREFIX = 'Nachbelastung DHL Paket'

const round2 = (n: number) => Math.round((Number(n) || 0) * 100) / 100

/** "1.234,56" / "-0,20" → number (German decimal comma). */
const deAmount = (txt: string): number | null => {
  if (txt == null) return null
  const cleaned = String(txt).trim().replace(/\./g, '').replace(',', '.')
  const n = Number(cleaned)
  return Number.isFinite(n) ? n : null
}

/**
 * Parse the fee line description written by create_fulfillment.py.
 * Returns `null` when the text does not carry the known shape at all.
 */
export const parseNachbelastungDescription = (description: string | null | undefined): ParsedNachbelastung | null => {
  const text = String(description || '').trim()
  if (!text) return null

  const result: ParsedNachbelastung = {
    shipmentNo: null,
    shipDateText: null,
    dhlInvoiceNos: [],
    source: 'unknown',
    positions: [],
    dhlTotal: null,
    booked: null,
    difference: null,
    detailText: text
  }

  // Head: "Nachbelastung DHL Paket - Sendung 540436 vom 30.10.2026 - "
  const head = text.match(/^Nachbelastung DHL Paket\s+-\s+Sendung\s+(\S+)\s+vom\s+(\S+)\s+-\s+/)
  let rest = text
  if (head) {
    result.shipmentNo = head[1]
    result.shipDateText = head[2]
    rest = text.slice(head[0].length)
  }
  result.detailText = rest

  // Tail: "; bereits berechnet 4,91 EUR; Nachbelastung 0,09 EUR"
  const booked = rest.match(/;\s*bereits berechnet\s+(-?[\d.]+,\d{2})\s*EUR/)
  if (booked) result.booked = deAmount(booked[1])
  const diff = rest.match(/;\s*Nachbelastung\s+(-?[\d.]+,\d{2})\s*EUR/)
  if (diff) result.difference = deAmount(diff[1])
  const body = rest.split(/;\s*bereits berechnet/)[0]

  // Variant A: "lt. DHL-Rechnung 1847900910, 1542709978: <pos> · <pos> = 5,00 EUR"
  const dhl = body.match(/^lt\.\s*DHL-Rechnung\s+([^:]*):\s*(.*?)\s*=\s*(-?[\d.]+,\d{2})\s*EUR\s*$/)
  if (dhl) {
    result.source = 'dhl'
    result.dhlInvoiceNos = dhl[1].split(',').map(s => s.trim()).filter(s => s && s !== '?')
    result.dhlTotal = deAmount(dhl[3])
    for (const part of dhl[2].split(' · ')) {
      const m = part.trim().match(/^(.+?)\s+(-?[\d.]+,\d{2})$/)
      if (m) {
        const amount = deAmount(m[2])
        if (amount != null) result.positions.push({ label: m[1].trim(), amount })
      } else if (part.trim()) {
        result.positions.push({ label: part.trim(), amount: 0 })
      }
    }
    return result
  }

  // Variant B: "lt. Frachtkalkulation 4,91 EUR" (internal calculation was higher than DHL)
  const calc = body.match(/^lt\.\s*Frachtkalkulation\s+(-?[\d.]+,\d{2})\s*EUR\s*$/)
  if (calc) {
    result.source = 'calc'
    result.dhlTotal = deAmount(calc[1])
    return result
  }

  return head || booked || diff ? result : null
}

/** Build positions from the m_inout buckets (fallback when the description is missing). */
export const positionsFromBuckets = (row: any): DhlPosition[] => {
  const positions: DhlPosition[] = []
  for (const comp of DHL_FEE_COMPONENTS) {
    const amount = round2(Number(row?.[comp.column]) || 0)
    if (!amount) continue
    const label = comp.label ?? String(row?.dhl_product_description || 'DHL PAKET (GK)').trim()
    positions.push({ label, amount })
  }
  return positions
}

/**
 * DHL invoice numbers from `ext_freight_processed_files` — mirrors
 * `dhl_invoice_numbers_from_files()` in create_fulfillment.py:
 *   invoice_<YYYY-MM-DD>_<no>.xlsx            → last token
 *   <EKP>_00<no>_<YYYYMMDD>_<HHMMSS>.XLSX     → second token without leading zeros
 */
export const invoiceNosFromFiles = (files: string | null | undefined): string[] => {
  const out: string[] = []
  for (const f of String(files || '').split(',')) {
    const parts = f.trim().split('/').pop()!.replace(/\.xlsx$/i, '').split('_')
    if (parts.length < 3) continue
    let no: string
    if (parts[0].toLowerCase() === 'invoice') no = parts[parts.length - 1]
    else if (parts.length >= 4 && /^\d+$/.test(parts[0]) && /^\d+$/.test(parts[1])) no = parts[1].replace(/^0+/, '')
    else no = parts[parts.length - 1]
    if (no && !out.includes(no)) out.push(no)
  }
  return out
}

export interface DhlSurchargeRow {
  feeLineId: number
  productId: number
  productName: string
  amount: number
  detectedDate: string | null
  inoutId: number
  shipmentNo: string
  trackingNo: string | null
  shipDate: string | null
  orderNo: string | null
  orderReference: string | null
  countryCode: string | null
  dhlInvoiceNos: string[]
  source: 'dhl' | 'calc' | 'unknown'
  positions: DhlPosition[]
  dhlTotal: number | null
  booked: number | null
  parsed: boolean
}

export interface DhlSurchargeReportData {
  status: number
  message?: string
  invoice?: {
    id: number
    documentNo: string
    dateInvoiced: string | null
    periodOfPerformance: string | null
    orderId: number
    orderDocumentNo: string
    partnerId: number
    partnerName: string
    language: 'de' | 'en'
  }
  rows?: DhlSurchargeRow[]
  byProduct?: Array<{ productId: number; productName: string; count: number; total: number; invoiceLineNetAmt: number | null }>
  bySurcharge?: Array<{ label: string; count: number; total: number }>
  totals?: { count: number; dhlTotal: number; booked: number; difference: number }
  dhlInvoiceNos?: string[]
  count?: number
  total?: number
}

const pgConfig = () => {
  const config = useRuntimeConfig()
  return {
    host: config.pgHost,
    port: parseInt(config.pgPort || '5432'),
    database: config.pgDatabase,
    user: config.pgUser,
    password: config.pgPassword
  }
}

const toIso = (v: any): string | null => {
  if (!v) return null
  if (v instanceof Date) return isNaN(v.getTime()) ? null : v.toISOString()
  return String(v)
}

const FEE_LINE_WHERE = `fl.c_order_refinv_id = $1
      AND fl.fulfillmentpricingrule = 'SHIPPINGFEE_NACHBELASTUNG'
      AND fl.isactive = 'Y'`

/**
 * Load the invoice (user token → role access enforced by iDempiere) and its
 * Nachbelastung fee lines (direct Postgres, one query).
 */
export const loadDhlSurchargeReport = async (
  event: any,
  token: string,
  invoiceId: number,
  opts: { summaryOnly?: boolean } = {}
): Promise<DhlSurchargeReportData> => {
  const inv: any = await event.context.fetch(
    `models/c_invoice/${invoiceId}?$select=DocumentNo,DateInvoiced,C_Order_ID,C_BPartner_ID,AD_Org_ID,period_of_performance,IsSOTrx&$expand=C_BPartner_ID($select=Name,AD_Language)`,
    'GET',
    token,
    null
  )
  if (!inv?.id) return { status: Number(inv?.status) || 404, message: inv?.message || 'Invoice not found' }
  const orderId = Number(inv?.C_Order_ID?.id) || 0
  if (!orderId) {
    return { status: 404, message: 'Invoice has no order reference — not a generated fee invoice', count: 0, total: 0 }
  }

  const langRaw = String(inv?.C_BPartner_ID?.AD_Language?.id || inv?.C_BPartner_ID?.AD_Language || 'de_DE')
  const invoice = {
    id: Number(inv.id),
    documentNo: String(inv?.DocumentNo || inv.id),
    dateInvoiced: inv?.DateInvoiced || null,
    periodOfPerformance: inv?.period_of_performance || inv?.Period_Of_Performance || null,
    orderId,
    orderDocumentNo: String(inv?.C_Order_ID?.identifier || orderId).split('_')[0],
    partnerId: Number(inv?.C_BPartner_ID?.id) || 0,
    partnerName: String(inv?.C_BPartner_ID?.Name || inv?.C_BPartner_ID?.identifier || ''),
    language: (langRaw.toLowerCase().startsWith('en') ? 'en' : 'de') as 'de' | 'en'
  }

  const pool = new Pool(pgConfig())
  try {
    if (opts.summaryOnly) {
      const res = await pool.query(
        `SELECT count(*)::int AS count, COALESCE(SUM(fl.linetotalamt), 0)::float AS total
         FROM adempiere.cust_fulfillmentfeeline fl
         WHERE ${FEE_LINE_WHERE}`,
        [orderId]
      )
      const count = Number(res.rows?.[0]?.count) || 0
      return { status: count > 0 ? 200 : 404, invoice, count, total: round2(Number(res.rows?.[0]?.total) || 0), message: count ? undefined : 'No DHL Nachbelastung lines on this invoice' }
    }

    const res = await pool.query(
      `SELECT fl.cust_fulfillmentfeeline_id, fl.description, fl.linetotalamt::float AS linetotalamt,
              fl.m_product_id, p.name AS product_name, fl.shipping_date AS detected_date,
              mi.m_inout_id, mi.documentno AS shipment_no, mi.trackingno, mi.shipping_date, mi.movementdate,
              mi.dhl_product_description, mi.ext_freight_total::float AS ext_freight_total,
              mi.int_freight_total::float AS int_freight_total,
              mi.ext_freight_cost::float AS ext_freight_cost, mi.ext_freight_co2::float AS ext_freight_co2,
              mi.ext_freight_energy::float AS ext_freight_energy, mi.ext_freight_peak::float AS ext_freight_peak,
              mi.ext_freight_codierung::float AS ext_freight_codierung, mi.ext_freight_insurance::float AS ext_freight_insurance,
              mi.ext_freight_returnfees::float AS ext_freight_returnfees, mi.ext_freight_intpremium::float AS ext_freight_intpremium,
              mi.ext_freight_others::float AS ext_freight_others, mi.ext_freight_processed_files,
              o.documentno AS order_no, o.poreference, o.externalorderid, ctry.countrycode
       FROM adempiere.cust_fulfillmentfeeline fl
       JOIN adempiere.m_inout mi ON mi.m_inout_id = fl.m_inout_id
       LEFT JOIN adempiere.m_product p ON p.m_product_id = fl.m_product_id
       LEFT JOIN adempiere.c_order o ON o.c_order_id = mi.c_order_id
       LEFT JOIN adempiere.c_bpartner_location bpl ON bpl.c_bpartner_location_id = mi.c_bpartner_location_id
       LEFT JOIN adempiere.c_location loc ON loc.c_location_id = bpl.c_location_id
       LEFT JOIN adempiere.c_country ctry ON ctry.c_country_id = loc.c_country_id
       WHERE ${FEE_LINE_WHERE}
       ORDER BY COALESCE(mi.shipping_date, mi.movementdate), mi.m_inout_id, fl.cust_fulfillmentfeeline_id`,
      [orderId]
    )

    const rows: DhlSurchargeRow[] = res.rows.map((r: any) => {
      const amount = round2(Number(r.linetotalamt) || 0)
      const parsed = parseNachbelastungDescription(r.description)
      let positions = parsed?.positions || []
      let source: 'dhl' | 'calc' | 'unknown' = parsed?.source || 'unknown'
      let dhlTotal = parsed?.dhlTotal ?? null
      let booked = parsed?.booked ?? null
      let dhlInvoiceNos = parsed?.dhlInvoiceNos || []
      if (!parsed || (parsed.source === 'unknown' && positions.length === 0)) {
        const ext = round2(Number(r.ext_freight_total) || 0)
        const int = round2(Number(r.int_freight_total) || 0)
        if (ext >= int && ext !== 0) {
          source = 'dhl'
          positions = positionsFromBuckets(r)
          dhlTotal = ext
        } else {
          source = 'calc'
          dhlTotal = int
        }
        booked = round2((dhlTotal || 0) - amount)
        dhlInvoiceNos = invoiceNosFromFiles(r.ext_freight_processed_files)
      }
      const reference = [r.poreference, r.externalorderid].map((v: any) => String(v || '').trim()).filter(Boolean)
      return {
        feeLineId: Number(r.cust_fulfillmentfeeline_id),
        productId: Number(r.m_product_id) || 0,
        productName: String(r.product_name || ''),
        amount,
        detectedDate: toIso(r.detected_date),
        inoutId: Number(r.m_inout_id),
        shipmentNo: String(r.shipment_no || ''),
        trackingNo: r.trackingno ? String(r.trackingno) : null,
        shipDate: toIso(r.shipping_date) || toIso(r.movementdate),
        orderNo: r.order_no ? String(r.order_no) : null,
        orderReference: reference.length ? Array.from(new Set(reference)).join(' / ') : null,
        countryCode: r.countrycode ? String(r.countrycode) : null,
        dhlInvoiceNos,
        source,
        positions,
        dhlTotal,
        booked,
        parsed: !!parsed
      }
    })

    if (rows.length === 0) {
      return { status: 404, invoice, message: 'No DHL Nachbelastung lines on this invoice', count: 0, total: 0 }
    }

    // Invoice positions for reconciliation (shares the product with the regular shipping fee line,
    // so match on the description prefix).
    let invoiceLines: any[] = []
    try {
      const il: any = await event.context.fetch(
        `models/c_invoiceline?$filter=C_Invoice_ID eq ${invoiceId}&$top=200&$select=Line,M_Product_ID,Description,LineNetAmt`,
        'GET',
        token,
        null
      )
      invoiceLines = (il?.records || []).filter((l: any) => String(l?.Description || '').startsWith(NACHBELASTUNG_PREFIX))
    } catch {
      invoiceLines = []
    }

    const byProductMap = new Map<number, { productId: number; productName: string; count: number; total: number; invoiceLineNetAmt: number | null }>()
    const bySurchargeMap = new Map<string, { label: string; count: number; total: number }>()
    const totals = { count: 0, dhlTotal: 0, booked: 0, difference: 0 }
    const invoiceNos = new Set<string>()

    for (const row of rows) {
      const bp = byProductMap.get(row.productId) || { productId: row.productId, productName: row.productName, count: 0, total: 0, invoiceLineNetAmt: null }
      bp.count += 1
      bp.total = round2(bp.total + row.amount)
      byProductMap.set(row.productId, bp)

      for (const pos of row.positions) {
        const s = bySurchargeMap.get(pos.label) || { label: pos.label, count: 0, total: 0 }
        s.count += 1
        s.total = round2(s.total + pos.amount)
        bySurchargeMap.set(pos.label, s)
      }
      totals.count += 1
      totals.dhlTotal = round2(totals.dhlTotal + (row.dhlTotal || 0))
      totals.booked = round2(totals.booked + (row.booked || 0))
      totals.difference = round2(totals.difference + row.amount)
      row.dhlInvoiceNos.forEach(n => invoiceNos.add(n))
    }
    for (const bp of byProductMap.values()) {
      const il = invoiceLines.find((l: any) => Number(l?.M_Product_ID?.id) === bp.productId)
      bp.invoiceLineNetAmt = il ? round2(Number(il.LineNetAmt) || 0) : null
    }

    return {
      status: 200,
      invoice,
      rows,
      byProduct: Array.from(byProductMap.values()).sort((a, b) => b.total - a.total),
      bySurcharge: Array.from(bySurchargeMap.values()).sort((a, b) => b.total - a.total),
      totals,
      dhlInvoiceNos: Array.from(invoiceNos).sort(),
      count: rows.length,
      total: totals.difference
    }
  } finally {
    await pool.end()
  }
}