import { Pool } from 'pg'
import refreshTokenHelper from '../../../utils/refreshTokenHelper'

/**
 * OPOS-Liste (Offene-Posten-Liste) — DATEV-style open items list.
 *
 * One row per open item (offener Posten): every completed invoice / credit memo
 * whose open amount is not zero, optionally plus unallocated payments
 * ("Zahlungen ohne Rechnungsbezug"). Direct Postgres like the other
 * `openso-*` / `open-fulfillment-invoices` routes, because the open amount is
 * computed by iDempiere's own SQL functions (`invoiceOpen`, `invoiceOpenToDate`,
 * `paymentTermDueDate`, `paymentAvailable`) which the REST API can't reach
 * (the RV_OpenItem view has no PK and is rejected by the REST layer).
 *
 * Query params:
 *   type            'ar' (Debitoren) | 'ap' (Kreditoren) | 'all'   (default 'all')
 *   orgId           restrict to one organisation (default: all orgs; limited roles are forced to their own org)
 *   asOf            OP-Stichtag YYYY-MM-DD — the list as it looked on that date
 *                   (invoices booked up to that date, allocations up to that date). Default: today.
 *   includePayments 'true' to add unallocated payments as (negative) open items.
 *
 * Sign convention (document perspective, like iDempiere's invoiceOpen):
 *   invoice  → positive open amount, credit memo / unallocated payment → negative.
 *   `sh` carries the DATEV Soll/Haben marker: Debitor invoice = S, Debitor credit/payment = H,
 *   Kreditor invoice = H, Kreditor credit/payment = S.
 *
 * AR/AP split follows the app's own convention (see server/api/invoices/so.get.ts):
 * `IsSOTrx='Y' OR C_DocTypeTarget_ID = 1000006` is AR — doctype 1000006 ("AP CreditMemo")
 * is used for customer credit notes and is economically a Debitoren credit. The exception
 * only applies to partners flagged IsCustomer: a genuine VENDOR credit memo (e.g. an Amazon
 * "Steuergutschrift" fee refund, partner vendor-only) is a Kreditoren credit and stays AP.
 */

const CLIENT_ID = 1000000
const AR_SPECIAL_DOCTYPE = 1000006

const toIsoDate = (v: any): string | null => {
  if (!v) return null
  if (v instanceof Date) {
    return `${v.getFullYear()}-${String(v.getMonth() + 1).padStart(2, '0')}-${String(v.getDate()).padStart(2, '0')}`
  }
  const m = String(v).match(/^(\d{4})-(\d{2})-(\d{2})/)
  return m ? `${m[1]}-${m[2]}-${m[3]}` : null
}

const num = (v: any): number => {
  const n = typeof v === 'number' ? v : parseFloat(v)
  return Number.isFinite(n) ? n : 0
}

const handleFunc = async (event: any) => {
  const config = useRuntimeConfig()
  const dbConfig = {
    host: config.pgHost,
    port: parseInt(config.pgPort || '5432'),
    database: config.pgDatabase,
    user: config.pgUser,
    password: config.pgPassword,
  }

  if (!dbConfig.host || !dbConfig.database || !dbConfig.user || !dbConfig.password) {
    throw createError({
      statusCode: 500,
      message: 'PostgreSQL credentials not configured. Please set PG_HOST, PG_DATABASE, PG_USER, PG_PASSWORD in .env'
    })
  }

  const query = getQuery(event)
  const type = String(query?.type || 'all').toLowerCase()
  const includePayments = String(query?.includePayments || '') === 'true'
  const asOfParam = toIsoDate(query?.asOf)
  const today = toIsoDate(new Date()) as string
  // A Stichtag in the future is meaningless — clamp to today (= "current" mode).
  const asOf: string | null = asOfParam && asOfParam < today ? asOfParam : null
  const referenceDate = asOf || today

  // Limited roles (FrontendMenu == 'c') only ever see their own organisation.
  const roleCookie = getCookie(event, 'logship_role')
  let menuType: string = 'Menu'
  try {
    const role = roleCookie ? JSON.parse(roleCookie) : null
    menuType = role?.FrontendMenu?.id || role?.FrontendMenu || 'Menu'
  } catch {}
  const ownOrgCookie = getCookie(event, 'logship_organization_id')
  const ownOrgId = ownOrgCookie ? parseInt(ownOrgCookie) : NaN
  const requestedOrg = query?.orgId != null && String(query.orgId) !== '' ? parseInt(String(query.orgId)) : NaN
  let orgFilter: number | null = Number.isFinite(requestedOrg) ? requestedOrg : null
  if (menuType === 'c') orgFilter = Number.isFinite(ownOrgId) ? ownOrgId : -1

  const sideFilter = type === 'ar' ? 'AR' : type === 'ap' ? 'AP' : null

  const pool = new Pool(dbConfig)

  try {
    // ---- Invoices / credit memos ------------------------------------------------
    // $1 org (int|null), $2 asOf (date|null), $3 side ('AR'|'AP'|null), $4 reference date
    const invoiceSql = `
      WITH inv AS (
        SELECT
          i.c_invoice_id,
          i.documentno,
          i.dateinvoiced::date              AS dateinvoiced,
          i.dateacct::date                  AS dateacct,
          i.issotrx,
          i.docstatus,
          i.ispaid,
          i.isindispute,
          i.poreference,
          i.description,
          i.c_order_id,
          i.grandtotal,
          i.c_currency_id,
          i.c_bpartner_id,
          i.ad_org_id,
          i.c_paymentterm_id,
          i.c_payment_id,
          dt.docbasetype,
          dt.name                           AS doctype_name,
          CASE WHEN dt.docbasetype IN ('ARC','APC') THEN -1 ELSE 1 END AS multiplier,
          CASE WHEN i.issotrx = 'Y'
                 OR (i.c_doctypetarget_id = ${AR_SPECIAL_DOCTYPE}
                     AND EXISTS (SELECT 1 FROM c_bpartner cbp WHERE cbp.c_bpartner_id = i.c_bpartner_id AND cbp.iscustomer = 'Y'))
               THEN 'AR' ELSE 'AP' END AS side,
          paymenttermduedate(i.c_paymentterm_id, i.dateinvoiced)::date AS duedate,
          CASE WHEN $2::date IS NULL
               THEN invoiceopen(i.c_invoice_id, 0)
               ELSE invoiceopentodate(i.c_invoice_id, 0, $2::date)
          END                               AS openamt_alloc
        FROM c_invoice i
        JOIN c_doctype dt ON dt.c_doctype_id = COALESCE(NULLIF(i.c_doctype_id, 0), i.c_doctypetarget_id)
        WHERE i.ad_client_id = ${CLIENT_ID}
          AND i.isactive  = 'Y'
          AND i.docstatus = 'CO'
          AND ($1::int  IS NULL OR i.ad_org_id = $1)
          AND ($2::date IS NULL OR i.dateacct::date <= $2::date)
          AND (
            i.ispaid = 'N'
            OR ($2::date IS NOT NULL AND EXISTS (
                 SELECT 1
                 FROM c_allocationline al
                 JOIN c_allocationhdr ah ON ah.c_allocationhdr_id = al.c_allocationhdr_id
                 WHERE al.c_invoice_id = i.c_invoice_id
                   AND ah.isactive = 'Y'
                   AND ah.dateacct::date > $2::date
            ))
          )
      )
      SELECT
        inv.*,
        bp.value                            AS partner_value,
        bp.name                             AS partner_name,
        bp.iscustomer,
        bp.isvendor,
        org.name                            AS org_name,
        cur.iso_code                        AS currency,
        pt.name                             AS paymentterm,
        COALESCE(pt.netdays, 0)             AS netdays,
        COALESCE(pt.discountdays, 0)        AS discountdays,
        COALESCE(pt.discount, 0)            AS discount,
        o.documentno                        AS order_documentno,
        al.n                                AS alloc_count,
        al.last_date                        AS last_payment_date,
        COALESCE(al.discount_sum, 0)        AS alloc_discount,
        COALESCE(al.writeoff_sum, 0)        AS alloc_writeoff,
        lp.payamt                           AS linked_payamt,
        lp.documentno                       AS linked_payment_documentno,
        lp.datetrx                          AS linked_payment_date,
        COALESCE(dun.n, 0)                  AS dunning_count,
        dun.last_date                       AS last_dunning_date,
        dun.level_name                      AS dunning_level,
        ($4::date - inv.duedate)            AS days_due
      FROM inv
      JOIN c_bpartner bp     ON bp.c_bpartner_id   = inv.c_bpartner_id
      LEFT JOIN ad_org org   ON org.ad_org_id      = inv.ad_org_id
      LEFT JOIN c_currency cur ON cur.c_currency_id = inv.c_currency_id
      LEFT JOIN c_paymentterm pt ON pt.c_paymentterm_id = inv.c_paymentterm_id
      LEFT JOIN c_order o    ON o.c_order_id       = inv.c_order_id
      LEFT JOIN LATERAL (
        SELECT COUNT(*)                                        AS n,
               MAX(ah.dateacct)::date                          AS last_date,
               SUM(al.discountamt)                             AS discount_sum,
               SUM(al.writeoffamt)                             AS writeoff_sum
        FROM c_allocationline al
        JOIN c_allocationhdr ah ON ah.c_allocationhdr_id = al.c_allocationhdr_id
        WHERE al.c_invoice_id = inv.c_invoice_id
          AND ah.isactive = 'Y'
          AND ($2::date IS NULL OR ah.dateacct::date <= $2::date)
      ) al ON TRUE
      LEFT JOIN LATERAL (
        -- App convention: an invoice may carry a direct C_Payment_ID link without an
        -- allocation (see /sales/invoices "TotalPaidAmt || C_Payment_ID.PayAmt").
        SELECT p.payamt, p.documentno, p.datetrx::date AS datetrx
        FROM c_payment p
        WHERE p.c_payment_id = inv.c_payment_id
          AND p.docstatus IN ('CO','CL')
          AND ($2::date IS NULL OR p.dateacct::date <= $2::date)
      ) lp ON TRUE
      LEFT JOIN LATERAL (
        SELECT COUNT(*)                 AS n,
               MAX(dr.dunningdate)::date AS last_date,
               (ARRAY_AGG(dl.name ORDER BY dr.dunningdate DESC))[1] AS level_name
        FROM c_dunningrunline drl
        JOIN c_dunningrunentry dre ON dre.c_dunningrunentry_id = drl.c_dunningrunentry_id
        JOIN c_dunningrun dr       ON dr.c_dunningrun_id       = dre.c_dunningrun_id
        LEFT JOIN c_dunninglevel dl ON dl.c_dunninglevel_id     = dr.c_dunninglevel_id
        WHERE drl.c_invoice_id = inv.c_invoice_id
          AND drl.processed = 'Y'
          AND ($2::date IS NULL OR dr.dunningdate::date <= $2::date)
      ) dun ON TRUE
      WHERE ($3::text IS NULL OR inv.side = $3::text)
      ORDER BY inv.side, bp.value, bp.name, inv.duedate, inv.dateinvoiced, inv.c_invoice_id
    `

    const params = [orgFilter, asOf, sideFilter, referenceDate]
    const invRes = await pool.query(invoiceSql, params)

    const records: any[] = []
    for (const r of invRes.rows) {
      const multiplier = num(r.multiplier) || 1
      const grandTotal = num(r.grandtotal)
      const amount = grandTotal * multiplier                   // signed document amount
      let openAmt = num(r.openamt_alloc)
      let paidVia: 'allocation' | 'link' | null = num(r.alloc_count) > 0 ? 'allocation' : null
      // Direct-link fallback only when nothing is allocated at all (same rule as /sales/invoices).
      if (num(r.alloc_count) === 0 && r.linked_payamt != null && multiplier === 1) {
        openAmt = openAmt - num(r.linked_payamt)
        paidVia = 'link'
      }
      openAmt = Math.round(openAmt * 100) / 100
      if (Math.abs(openAmt) < 0.005) continue
      const paidAmt = Math.round((amount - openAmt) * 100) / 100
      // Allocations that net to zero (allocation + its reversal) are not a payment.
      if (paidVia === 'allocation' && paidAmt === 0) paidVia = null

      const side: 'AR' | 'AP' = r.side === 'AP' ? 'AP' : 'AR'
      const isCredit = multiplier < 0
      const discountPct = num(r.discount)
      const discountDays = num(r.discountdays)
      const dateInvoiced = toIsoDate(r.dateinvoiced)
      let discountDate: string | null = null
      if (discountPct > 0 && dateInvoiced) {
        const d = new Date(dateInvoiced + 'T00:00:00')
        d.setDate(d.getDate() + discountDays)
        discountDate = toIsoDate(d)
      }
      const daysDue = r.days_due == null ? 0 : parseInt(String(r.days_due))

      records.push({
        kind: 'invoice',
        id: Number(r.c_invoice_id),
        documentNo: r.documentno,
        side,
        docBaseType: r.docbasetype || '',
        docTypeName: r.doctype_name || '',
        isCredit,
        // DATEV Soll/Haben marker, from the sign of the open amount:
        // Debitor: positive = S (Forderung), negative = H; Kreditor: positive = H (Verbindlichkeit), negative = S.
        sh: (side === 'AR') === (openAmt > 0) ? 'S' : 'H',
        dateDoc: dateInvoiced,
        dateAcct: toIsoDate(r.dateacct),
        dueDate: toIsoDate(r.duedate),
        daysDue,
        isOverdue: daysDue > 0,
        partnerId: Number(r.c_bpartner_id) || 0,
        partnerValue: r.partner_value || '',
        partnerName: r.partner_name || '',
        isCustomer: r.iscustomer === 'Y',
        isVendor: r.isvendor === 'Y',
        orgId: Number(r.ad_org_id) || 0,
        orgName: r.org_name || '',
        currency: r.currency || 'EUR',
        amount: Math.round(amount * 100) / 100,
        paidAmt,
        allocDiscount: num(r.alloc_discount),
        allocWriteOff: num(r.alloc_writeoff),
        openAmt,
        paidVia,
        paymentTerm: r.paymentterm || '',
        netDays: num(r.netdays),
        discountDays,
        discountPct,
        discountDate,
        discountAmt: discountPct > 0 ? Math.round(grandTotal * discountPct) / 100 : 0,
        discountStillPossible: !!(discountDate && discountDate >= referenceDate),
        dunningCount: num(r.dunning_count),
        dunningLevel: r.dunning_level || '',
        lastDunningDate: toIsoDate(r.last_dunning_date),
        lastPaymentDate: toIsoDate(r.last_payment_date) || (paidVia === 'link' ? toIsoDate(r.linked_payment_date) : null),
        linkedPaymentDocumentNo: paidVia === 'link' ? (r.linked_payment_documentno || '') : '',
        description: r.description || '',
        poReference: r.poreference || '',
        orderId: r.c_order_id ? Number(r.c_order_id) : null,
        orderDocumentNo: r.order_documentno || '',
        isInDispute: r.isindispute === 'Y',
        isPaidFlag: r.ispaid === 'Y'
      })
    }

    // ---- Unallocated payments (Zahlungen ohne Rechnungsbezug) --------------------
    if (includePayments) {
      const paymentSql = `
        WITH pay AS (
          SELECT
            p.c_payment_id, p.documentno, p.datetrx::date AS datetrx, p.dateacct::date AS dateacct,
            p.payamt, p.isreceipt, p.c_bpartner_id, p.c_currency_id, p.ad_org_id, p.description,
            p.c_invoice_id, p.c_order_id, p.tendertype, p.checkno,
            CASE WHEN p.isreceipt = 'Y' THEN 'AR' ELSE 'AP' END AS side,
            CASE WHEN $2::date IS NULL
                 THEN paymentavailable(p.c_payment_id)
                 ELSE p.payamt - COALESCE((
                        SELECT SUM(al.amount) * (CASE WHEN p.isreceipt = 'Y' THEN 1 ELSE -1 END)
                        FROM c_allocationline al
                        JOIN c_allocationhdr ah ON ah.c_allocationhdr_id = al.c_allocationhdr_id
                        WHERE al.c_payment_id = p.c_payment_id
                          AND ah.isactive = 'Y'
                          AND ah.dateacct::date <= $2::date
                      ), 0)
            END AS available
          FROM c_payment p
          WHERE p.ad_client_id = ${CLIENT_ID}
            AND p.isactive  = 'Y'
            AND p.docstatus IN ('CO','CL')
            AND p.c_charge_id IS NULL
            AND ($1::int  IS NULL OR p.ad_org_id = $1)
            AND ($2::date IS NULL OR p.dateacct::date <= $2::date)
            AND (
              p.isallocated = 'N'
              OR ($2::date IS NOT NULL AND EXISTS (
                   SELECT 1
                   FROM c_allocationline al
                   JOIN c_allocationhdr ah ON ah.c_allocationhdr_id = al.c_allocationhdr_id
                   WHERE al.c_payment_id = p.c_payment_id
                     AND ah.isactive = 'Y'
                     AND ah.dateacct::date > $2::date
              ))
            )
            -- payments "used" through the direct invoice link are not open
            AND NOT EXISTS (
              SELECT 1 FROM c_invoice x
              WHERE x.c_payment_id = p.c_payment_id AND x.docstatus = 'CO' AND x.isactive = 'Y'
            )
        )
        SELECT pay.*,
               bp.value AS partner_value, bp.name AS partner_name, bp.iscustomer, bp.isvendor,
               org.name AS org_name, cur.iso_code AS currency,
               inv.documentno AS invoice_documentno,
               o.documentno AS order_documentno
        FROM pay
        JOIN c_bpartner bp      ON bp.c_bpartner_id = pay.c_bpartner_id
        LEFT JOIN ad_org org    ON org.ad_org_id = pay.ad_org_id
        LEFT JOIN c_currency cur ON cur.c_currency_id = pay.c_currency_id
        LEFT JOIN c_invoice inv ON inv.c_invoice_id = pay.c_invoice_id
        LEFT JOIN c_order o     ON o.c_order_id = pay.c_order_id
        WHERE ($3::text IS NULL OR pay.side = $3::text)
        ORDER BY pay.side, bp.value, bp.name, pay.datetrx, pay.c_payment_id
      `
      const payRes = await pool.query(paymentSql, [orgFilter, asOf, sideFilter])
      for (const r of payRes.rows) {
        const available = Math.round(num(r.available) * 100) / 100
        if (Math.abs(available) < 0.005) continue
        const side: 'AR' | 'AP' = r.side === 'AP' ? 'AP' : 'AR'
        const dateTrx = toIsoDate(r.datetrx)
        const daysDue = dateTrx ? Math.round((new Date(referenceDate + 'T00:00:00').getTime() - new Date(dateTrx + 'T00:00:00').getTime()) / 86400000) : 0
        const openAmt = -available   // a received payment is a credit (H) on the debtor account
        records.push({
          kind: 'payment',
          id: Number(r.c_payment_id),
          documentNo: r.documentno,
          side,
          docBaseType: side === 'AR' ? 'ARR' : 'APP',
          docTypeName: side === 'AR' ? 'Zahlungseingang' : 'Zahlungsausgang',
          isCredit: true,
          sh: (side === 'AR') === (openAmt > 0) ? 'S' : 'H',
          dateDoc: dateTrx,
          dateAcct: toIsoDate(r.dateacct),
          dueDate: dateTrx,
          daysDue,
          isOverdue: false,
          partnerId: Number(r.c_bpartner_id) || 0,
          partnerValue: r.partner_value || '',
          partnerName: r.partner_name || '',
          isCustomer: r.iscustomer === 'Y',
          isVendor: r.isvendor === 'Y',
          orgId: Number(r.ad_org_id) || 0,
          orgName: r.org_name || '',
          currency: r.currency || 'EUR',
          amount: -Math.round(num(r.payamt) * 100) / 100,
          paidAmt: -Math.round((num(r.payamt) - available) * 100) / 100,
          allocDiscount: 0,
          allocWriteOff: 0,
          openAmt,
          paidVia: null,
          paymentTerm: '',
          netDays: 0,
          discountDays: 0,
          discountPct: 0,
          discountDate: null,
          discountAmt: 0,
          discountStillPossible: false,
          dunningCount: 0,
          dunningLevel: '',
          lastDunningDate: null,
          lastPaymentDate: dateTrx,
          linkedPaymentDocumentNo: '',
          description: r.description || '',
          poReference: r.checkno || '',
          orderId: r.c_order_id ? Number(r.c_order_id) : null,
          orderDocumentNo: r.order_documentno || '',
          invoiceId: r.c_invoice_id ? Number(r.c_invoice_id) : null,
          invoiceDocumentNo: r.invoice_documentno || '',
          isInDispute: false,
          isPaidFlag: false
        })
      }
    }

    const sum = (rows: any[], f: (r: any) => number) => Math.round(rows.reduce((s, r) => s + f(r), 0) * 100) / 100
    const ar = records.filter(r => r.side === 'AR')
    const ap = records.filter(r => r.side === 'AP')

    return {
      records,
      count: records.length,
      asOf: referenceDate,
      isHistorical: !!asOf,
      orgId: orgFilter,
      type,
      includePayments,
      summary: {
        arOpen: sum(ar, r => r.openAmt),
        arOverdue: sum(ar.filter(r => r.kind === 'invoice' && r.daysDue > 0 && r.openAmt > 0), r => r.openAmt),
        arCount: ar.length,
        apOpen: sum(ap, r => r.openAmt),
        apOverdue: sum(ap.filter(r => r.kind === 'invoice' && r.daysDue > 0 && r.openAmt > 0), r => r.openAmt),
        apCount: ap.length
      }
    }
  } catch (err: any) {
    console.error('PostgreSQL query error (accounting/opos):', err)
    throw createError({ statusCode: 500, message: `Database error: ${err.message}` })
  } finally {
    await pool.end()
  }
}

export default defineEventHandler(async (event) => {
  try {
    return await handleFunc(event)
  } catch (err: any) {
    try {
      await refreshTokenHelper(event)
      return await handleFunc(event)
    } catch (error: any) {
      console.error('Fatal error in accounting/opos:', error)
      throw error
    }
  }
})