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

/**
 * Open (unpaid) sales invoices of fulfillment customers for the dashboard card.
 *
 * "Open" mirrors the /sales/invoices list + ffn-unpaid-count logic:
 *  - DocStatus = 'CO' only — Closed (CL) invoices had their open amount written
 *    off and Drafts (DR) are not yet real receivables, so neither is open.
 *  - IsPaid = 'N'
 *  - openAmt = |GrandTotal| − |sum of allocation lines whose payment is CO|
 *    must be > 0 (IsPaid may lag behind allocations).
 * Partner scope: c_bpartner.isfulfillmentcustomer = 'Y' only.
 */
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'
    })
  }

  // Cross-partner financial data — admin/staff dashboard only. Limited
  // (merchant) roles get an empty result even if they hit the URL directly.
  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 {}
  if (menuType === 'c') {
    return { records: [], count: 0, totalOpenAmt: 0 }
  }

  const pool = new Pool(dbConfig)

  try {
    // Same invoice population as /sales/invoices (isSOTrx OR doctype 1000006),
    // restricted to fulfillment customers and to potentially-open documents.
    const sql = `
      SELECT
        i.c_invoice_id          AS id,
        i.documentno,
        i.dateinvoiced,
        i.grandtotal,
        i.c_bpartner_id         AS partner_id,
        i.ad_org_id             AS org_id,
        i.c_currency_id         AS currency_id,
        cur.iso_code            AS currency,
        dt.docbasetype,
        bp.name                 AS partner,
        org.name                AS org_name,
        COALESCE(pt.netdays, 0) AS netdays,
        COALESCE(alloc.paid, 0) AS paidamt
      FROM c_invoice i
      JOIN c_bpartner bp ON bp.c_bpartner_id = i.c_bpartner_id
                        AND bp.isfulfillmentcustomer = 'Y'
      LEFT JOIN ad_org org ON org.ad_org_id = i.ad_org_id
      LEFT JOIN c_currency cur ON cur.c_currency_id = i.c_currency_id
      LEFT JOIN c_doctype dt ON dt.c_doctype_id = COALESCE(NULLIF(i.c_doctype_id, 0), i.c_doctypetarget_id)
      LEFT JOIN c_paymentterm pt ON pt.c_paymentterm_id = i.c_paymentterm_id
      LEFT JOIN (
        SELECT al.c_invoice_id, SUM(al.amount) AS paid
        FROM c_allocationline al
        JOIN c_payment p ON p.c_payment_id = al.c_payment_id
                        AND p.docstatus = 'CO'
        GROUP BY al.c_invoice_id
      ) alloc ON alloc.c_invoice_id = i.c_invoice_id
      WHERE (i.issotrx = 'Y' OR i.c_doctypetarget_id = 1000006)
        AND i.docstatus    = 'CO'
        AND i.ispaid       = 'N'
        AND i.isactive     = 'Y'
        AND i.ad_client_id = 1000000
      ORDER BY i.dateinvoiced ASC, i.c_invoice_id ASC
    `

    const result = await pool.query(sql)

    const records = result.rows
      .map((r: any) => {
        const grandTotal = parseFloat(r.grandtotal || 0)
        // Raw allocation sum is signed (reversals cancel originals); the net
        // paid amount is its absolute value — same as TotalPaidAmt on /sales/invoices.
        const paidAmt = Math.abs(parseFloat(r.paidamt || 0))
        const openAmt = Math.max(0, Math.abs(grandTotal) - paidAmt)
        return {
          id: Number(r.id),
          documentNo: r.documentno,
          dateInvoiced: r.dateinvoiced,
          partner: r.partner || '',
          partnerId: Number(r.partner_id) || 0,
          orgName: r.org_name || '',
          organizationId: Number(r.org_id) || 0,
          currencyId: Number(r.currency_id) || 0,
          currency: r.currency || '',
          // 'ARI' / 'ARC' — the quick-payment modal flips sign + doc type for credit memos.
          docBaseType: r.docbasetype || '',
          netDays: parseInt(r.netdays || 0),
          grandTotal,
          paidAmt,
          openAmt
        }
      })
      // Guard against float noise on fully-allocated invoices still flagged IsPaid='N'.
      .filter((r: any) => r.openAmt > 0.005)

    const totalOpenAmt = records.reduce((sum: number, r: any) => sum + r.openAmt, 0)

    return { records, count: records.length, totalOpenAmt }
  } catch (err: any) {
    console.error('PostgreSQL query error (open-fulfillment-invoices):', 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 {
      let authToken: any = await refreshTokenHelper(event)
      return await handleFunc(event)
    } catch(error: any) {
      console.error('Fatal error in open-fulfillment-invoices:', error)
      throw error
    }
  }
})