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

// Auto-propose allocations for ONE PO↔SO pair (the confirm dialog that opens
// when an inbound truck is dropped onto an outbound truck on the board).
//
// Pairs the two orders' item lines by product (lines ranked per product on both
// sides and joined rank-to-rank), returns open quantities, what is already
// allocated elsewhere, and the existing allocation of THIS pair (for upsert).
// The proposed qty itself is computed client-side:
//   min(poOpen - poAllocatedOther, soOpen - soAllocatedOther), floored at 0.
//
// Query params: poOrderId, soOrderId (both required)
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 poOrderId = parseInt(String(query?.poOrderId || ''))
  const soOrderId = parseInt(String(query?.soOrderId || ''))
  if (!Number.isFinite(poOrderId) || !Number.isFinite(soOrderId)) {
    throw createError({ statusCode: 400, message: 'poOrderId and soOrderId are required' })
  }

  const pool = new Pool(dbConfig)

  try {
    // Unqualified + adempiere-schema check: iDempiere tables live in the
    // `adempiere` schema (resolved via search_path), NOT in `public`.
    const reg = await pool.query(
      `SELECT COALESCE(to_regclass('cust_crossdockalloc'), to_regclass('adempiere.cust_crossdockalloc')) AS t`
    )
    if (!reg.rows?.[0]?.t) {
      return { rows: [], linkTableMissing: true }
    }

    const sql = `
      WITH po_lines AS (
        SELECT ol.c_orderline_id, ol.m_product_id, ol.line,
               (ol.qtyordered - COALESCE(ol.qtydelivered, 0)) AS open_qty,
               ROW_NUMBER() OVER (PARTITION BY ol.m_product_id ORDER BY ol.line, ol.c_orderline_id) AS rn
        FROM c_orderline ol
        JOIN m_product p ON p.m_product_id = ol.m_product_id AND p.producttype = 'I'
        WHERE ol.c_order_id = $1
      ),
      so_lines AS (
        SELECT ol.c_orderline_id, ol.m_product_id, ol.line,
               (ol.qtyordered - COALESCE(ol.qtydelivered, 0)) AS open_qty,
               ROW_NUMBER() OVER (PARTITION BY ol.m_product_id ORDER BY ol.line, ol.c_orderline_id) AS rn
        FROM c_orderline ol
        JOIN m_product p ON p.m_product_id = ol.m_product_id AND p.producttype = 'I'
        WHERE ol.c_order_id = $2
      ),
      alloc_po AS (
        SELECT po_orderline_id, SUM(qtyallocated) AS qty
        FROM cust_crossdockalloc WHERE isactive = 'Y' GROUP BY po_orderline_id
      ),
      alloc_so AS (
        SELECT so_orderline_id, SUM(qtyallocated) AS qty
        FROM cust_crossdockalloc WHERE isactive = 'Y' GROUP BY so_orderline_id
      ),
      pair_alloc AS (
        SELECT po_orderline_id, so_orderline_id,
               MIN(cust_crossdockalloc_id) AS id,
               SUM(qtyallocated) AS qty
        FROM cust_crossdockalloc WHERE isactive = 'Y'
        GROUP BY po_orderline_id, so_orderline_id
      )
      SELECT p.name  AS product_name,
             p.value AS sku,
             pl.m_product_id,
             pl.c_orderline_id AS po_orderline_id,
             sl.c_orderline_id AS so_orderline_id,
             pl.open_qty AS po_open_qty,
             sl.open_qty AS so_open_qty,
             COALESCE(ap.qty, 0)  AS po_allocated_total,
             COALESCE(aso.qty, 0) AS so_allocated_total,
             pa.id AS existing_id,
             COALESCE(pa.qty, 0) AS existing_qty
      FROM po_lines pl
      JOIN so_lines sl ON sl.m_product_id = pl.m_product_id AND sl.rn = pl.rn
      LEFT JOIN m_product p    ON p.m_product_id = pl.m_product_id
      LEFT JOIN alloc_po ap    ON ap.po_orderline_id = pl.c_orderline_id
      LEFT JOIN alloc_so aso   ON aso.so_orderline_id = sl.c_orderline_id
      LEFT JOIN pair_alloc pa  ON pa.po_orderline_id = pl.c_orderline_id
                              AND pa.so_orderline_id = sl.c_orderline_id
      ORDER BY p.name, pl.line
    `
    const result = await pool.query(sql, [poOrderId, soOrderId])

    const rows = result.rows.map((r: any) => {
      const poOpen = parseFloat(r.po_open_qty || 0)
      const soOpen = parseFloat(r.so_open_qty || 0)
      const existingQty = parseFloat(r.existing_qty || 0)
      // "allocated elsewhere" excludes what THIS pair already holds.
      const poAllocatedOther = Math.max(0, parseFloat(r.po_allocated_total || 0) - existingQty)
      const soAllocatedOther = Math.max(0, parseFloat(r.so_allocated_total || 0) - existingQty)
      return {
        productId: Number(r.m_product_id),
        productName: r.product_name || '',
        sku: r.sku || '',
        poOrderLineId: Number(r.po_orderline_id),
        soOrderLineId: Number(r.so_orderline_id),
        poOpenQty: poOpen,
        soOpenQty: soOpen,
        poAllocatedOther,
        soAllocatedOther,
        existingId: r.existing_id ? Number(r.existing_id) : null,
        existingQty
      }
    })

    return { rows, linkTableMissing: false }
  } catch (err: any) {
    console.error('PostgreSQL query error (crossdock match-proposal):', 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 crossdock match-proposal:', error)
      throw error
    }
  }
})