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

// Cross-dock planning board data: inbound POs + outbound SOs in a date window,
// plus the persisted line-level allocations (CUST_CrossDockAlloc) aggregated
// into per-order-pair "links" with live coverage numbers.
//
// Same direct-Postgres pattern as orders/openso-list + orders/po-upcoming
// (org gate from the logship_role cookie, ad_client 1000000).
//
// Query params:
//   from, to     — ISO dates (inclusive), default today-3d … today+14d
//   warehouseId  — optional m_warehouse filter (applies to both lanes)
//
// Fail-soft: when CUST_CrossDockAlloc doesn't exist yet in iDempiere the lanes
// still load and `linkTableMissing: true` tells the UI to disable linking.
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 isoDate = (v: any, fallbackOffsetDays: number) => {
    const d = v ? new Date(String(v)) : new Date(Date.now() + fallbackOffsetDays * 86400000)
    if (isNaN(d.getTime())) throw createError({ statusCode: 400, message: 'invalid from/to date' })
    return d.toISOString().slice(0, 10)
  }
  const from = isoDate(query?.from, -3)
  const to = isoDate(query?.to, 14)
  const warehouseId = query?.warehouseId ? parseInt(String(query.warehouseId)) : null

  // Limited roles (FrontendMenu == 'c') only see their own organization.
  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 orgIdCookie = getCookie(event, 'logship_organization_id')
  const orgIdNum = orgIdCookie ? parseInt(orgIdCookie) : NaN
  const orgFilter: number | null = (menuType === 'c' && Number.isFinite(orgIdNum)) ? orgIdNum : null

  const pool = new Pool(dbConfig)

  try {
    // ---- inbound lane: open/completed purchase orders arriving in the window ----
    const inboundSql = `
      SELECT o.c_order_id AS id,
             o.documentno,
             to_char(o.dateordered,  'YYYY-MM-DD') AS dateordered,
             to_char(o.datepromised, 'YYYY-MM-DD') AS datepromised,
             o.grandtotal,
             o.ad_org_id,
             COALESCE(org.name, 'Unknown') AS org_name,
             o.m_warehouse_id,
             wh.name AS warehouse_name,
             o.c_bpartner_id,
             bp.name AS partner_name,
             COALESCE(ol.line_count, 0) AS line_count,
             COALESCE(ol.total_qty, 0)  AS total_qty,
             COALESCE(ol.open_qty, 0)   AS open_qty,
             EXISTS (
               SELECT 1 FROM m_inout io
               WHERE io.c_order_id = o.c_order_id
                 AND io.issotrx = 'N'
                 AND io.docstatus IN ('CO','CL')
             ) AS received
      FROM c_order o
      LEFT JOIN c_bpartner bp  ON bp.c_bpartner_id  = o.c_bpartner_id
      LEFT JOIN ad_org org     ON org.ad_org_id     = o.ad_org_id
      LEFT JOIN m_warehouse wh ON wh.m_warehouse_id = o.m_warehouse_id
      LEFT JOIN (
        SELECT c_order_id,
               COUNT(*) AS line_count,
               SUM(qtyordered) AS total_qty,
               SUM(qtyordered - COALESCE(qtydelivered, 0)) AS open_qty
        FROM c_orderline
        GROUP BY c_order_id
      ) ol ON ol.c_order_id = o.c_order_id
      WHERE o.issotrx = 'N'
        AND o.docstatus = 'CO'
        AND o.ad_client_id = 1000000
        AND o.datepromised >= $1::date
        AND o.datepromised < ($2::date + 1)
        AND ($3::int IS NULL OR o.ad_org_id = $3)
        AND ($4::int IS NULL OR o.m_warehouse_id = $4)
      ORDER BY o.datepromised ASC, o.c_order_id ASC
    `

    // ---- outbound lane: open (unshipped) sales orders promised in the window ----
    // Same universe as orders/openso-list (incl. stock check for is_unfulfillable).
    const outboundSql = `
      WITH stock AS (
        SELECT s.m_product_id,
               l.m_warehouse_id,
               SUM(s.qtyonhand) AS qtyonhand
        FROM m_storage s
        JOIN m_locator l ON l.m_locator_id = s.m_locator_id
        LEFT JOIN m_locatortype lt ON lt.m_locatortype_id = l.m_locatortype_id
        WHERE lt.m_locatortype_id IS NULL
           OR lt.isavailableforshipping = 'Y'
           OR lt.isavailableforreplenishment = 'Y'
           OR lt.isavailableforreservation = 'Y'
        GROUP BY s.m_product_id, l.m_warehouse_id
      )
      SELECT o.c_order_id AS id,
             o.documentno,
             to_char(o.dateordered,  'YYYY-MM-DD') AS dateordered,
             to_char(o.datepromised, 'YYYY-MM-DD') AS datepromised,
             o.grandtotal,
             o.ad_org_id,
             COALESCE(org.name, 'Unknown') AS org_name,
             o.m_warehouse_id,
             wh.name AS warehouse_name,
             o.c_bpartner_id,
             bp.name AS partner_name,
             COALESCE(ol.line_count, 0) AS line_count,
             COALESCE(ol.total_qty, 0)  AS total_qty,
             COALESCE(ol.open_qty, 0)   AS open_qty,
             EXISTS (
               SELECT 1
               FROM c_orderline ol2
               JOIN m_product p ON p.m_product_id = ol2.m_product_id
               LEFT JOIN stock st
                 ON st.m_product_id   = ol2.m_product_id
                AND st.m_warehouse_id = o.m_warehouse_id
               WHERE ol2.c_order_id = o.c_order_id
                 AND p.producttype = 'I'
                 AND (ol2.qtyordered - COALESCE(ol2.qtydelivered, 0)) > 0
                 AND (ol2.qtyordered - COALESCE(ol2.qtydelivered, 0)) > COALESCE(st.qtyonhand, 0)
             ) AS is_unfulfillable
      FROM c_order o
      LEFT JOIN c_bpartner bp  ON bp.c_bpartner_id  = o.c_bpartner_id
      LEFT JOIN ad_org org     ON org.ad_org_id     = o.ad_org_id
      LEFT JOIN m_warehouse wh ON wh.m_warehouse_id = o.m_warehouse_id
      LEFT JOIN (
        SELECT c_order_id,
               COUNT(*) AS line_count,
               SUM(qtyordered) AS total_qty,
               SUM(qtyordered - COALESCE(qtydelivered, 0)) AS open_qty
        FROM c_orderline
        GROUP BY c_order_id
      ) ol ON ol.c_order_id = o.c_order_id
      WHERE o.issotrx = 'Y'
        AND o.docstatus IN ('CO','CL')
        AND o.ad_client_id = 1000000
        AND o.isfulfillmentorder = 'Y'
        AND o.datepromised IS NOT NULL
        AND o.datepromised >= $1::date
        AND o.datepromised < ($2::date + 1)
        AND ($3::int IS NULL OR o.ad_org_id = $3)
        AND ($4::int IS NULL OR o.m_warehouse_id = $4)
        AND EXISTS (
              SELECT 1
              FROM c_orderline ol3
              JOIN m_product p ON p.m_product_id = ol3.m_product_id
              WHERE ol3.c_order_id = o.c_order_id
                AND p.producttype = 'I'
                AND (ol3.qtyordered - COALESCE(ol3.qtydelivered, 0)) > 0
        )
        AND NOT EXISTS (
              SELECT 1
              FROM m_inout io
              JOIN m_inoutline iol ON iol.m_inout_id = io.m_inout_id
              WHERE io.c_order_id = o.c_order_id
                AND io.issotrx = 'Y'
                AND io.docstatus NOT IN ('VO','RE')
        )
      ORDER BY o.datepromised ASC, o.c_order_id ASC
    `

    const params = [from, to, orgFilter, warehouseId]
    const [inboundRes, outboundRes] = await Promise.all([
      pool.query(inboundSql, params),
      pool.query(outboundSql, params)
    ])

    const mapOrder = (r: any) => ({
      id: Number(r.id),
      documentNo: r.documentno,
      dateOrdered: r.dateordered,
      datePromised: r.datepromised,
      grandTotal: parseFloat(r.grandtotal || 0),
      orgId: Number(r.ad_org_id),
      orgName: r.org_name || '',
      warehouseId: r.m_warehouse_id ? Number(r.m_warehouse_id) : null,
      warehouseName: r.warehouse_name || '',
      partnerId: r.c_bpartner_id ? Number(r.c_bpartner_id) : null,
      partnerName: r.partner_name || '',
      lineCount: parseInt(r.line_count || 0),
      totalQty: parseFloat(r.total_qty || 0),
      openQty: parseFloat(r.open_qty || 0),
    })

    const inbound = inboundRes.rows.map((r: any) => ({ ...mapOrder(r), received: !!r.received }))
    const outbound = outboundRes.rows.map((r: any) => ({ ...mapOrder(r), isUnfulfillable: !!r.is_unfulfillable }))

    // ---- links: allocations grouped per PO↔SO pair (fail-soft when table missing) ----
    let links: any[] = []
    let linkTableMissing = false
    // 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) {
      linkTableMissing = true
    } else {
      const linksSql = `
        WITH alloc AS (
          SELECT a.cust_crossdockalloc_id AS id,
                 a.qtyallocated,
                 pol.c_order_id     AS po_order_id,
                 sol.c_order_id     AS so_order_id,
                 pol.c_orderline_id AS po_line_id,
                 sol.c_orderline_id AS so_line_id,
                 sol.m_product_id,
                 (pol.qtyordered - COALESCE(pol.qtydelivered, 0)) AS po_open,
                 (sol.qtyordered - COALESCE(sol.qtydelivered, 0)) AS so_open
          FROM cust_crossdockalloc a
          JOIN c_orderline pol ON pol.c_orderline_id = a.po_orderline_id
          JOIN c_orderline sol ON sol.c_orderline_id = a.so_orderline_id
          WHERE a.isactive = 'Y'
        ),
        so_open_total AS (
          SELECT ol.c_order_id,
                 SUM(ol.qtyordered - COALESCE(ol.qtydelivered, 0)) AS open_qty
          FROM c_orderline ol
          JOIN m_product p ON p.m_product_id = ol.m_product_id AND p.producttype = 'I'
          GROUP BY ol.c_order_id
        )
        SELECT alloc.po_order_id,
               alloc.so_order_id,
               SUM(alloc.qtyallocated) AS qty_allocated,
               COALESCE(sot.open_qty, 0) AS so_open_total,
               EXISTS (
                 SELECT 1 FROM m_inout io
                 WHERE io.c_order_id = alloc.po_order_id
                   AND io.issotrx = 'N'
                   AND io.docstatus IN ('CO','CL')
               ) AS arrived,
               json_agg(json_build_object(
                 'allocId',       alloc.id,
                 'productId',     alloc.m_product_id,
                 'productName',   p.name,
                 'qtyAllocated',  alloc.qtyallocated,
                 'poOpenQty',     alloc.po_open,
                 'soOpenQty',     alloc.so_open,
                 'poOrderLineId', alloc.po_line_id,
                 'soOrderLineId', alloc.so_line_id
               ) ORDER BY p.name) AS products
        FROM alloc
        LEFT JOIN m_product p ON p.m_product_id = alloc.m_product_id
        LEFT JOIN so_open_total sot ON sot.c_order_id = alloc.so_order_id
        GROUP BY alloc.po_order_id, alloc.so_order_id, sot.open_qty
      `
      const linksRes = await pool.query(linksSql)
      links = linksRes.rows.map((r: any) => {
        const qtyAllocated = parseFloat(r.qty_allocated || 0)
        const soOpenTotal = parseFloat(r.so_open_total || 0)
        return {
          poOrderId: Number(r.po_order_id),
          soOrderId: Number(r.so_order_id),
          qtyAllocated,
          soOpenTotal,
          // SO fully delivered (open 0) with allocations still present → treat as covered.
          coveragePct: soOpenTotal > 0 ? Math.min(100, Math.round(100 * qtyAllocated / soOpenTotal)) : 100,
          arrived: !!r.arrived,
          products: r.products || []
        }
      })
    }

    return { inbound, outbound, links, linkTableMissing, from, to }
  } catch (err: any) {
    console.error('PostgreSQL query error (crossdock board):', 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 board:', error)
      throw error
    }
  }
})