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

// Per-warehouse (and per-org) open sales-order counts using the EXACT same
// universe as the dashboard "Open Orders" card (server/api/orders/openso-count.get.ts):
// completed/closed fulfilment sales orders that still have an open ITEM-type line
// and no non-voided shipment yet. The only difference vs openso-count is the
// GROUP BY: here we also break the count down by M_Warehouse_ID so the
// generate-shipments pages can show a per-warehouse / per-merchant badge that
// matches the dashboard.
//
// Every row additionally carries `fulfillable` — the subset of those orders whose
// summed open demand per product is covered by the on-hand stock of the order's
// warehouse (the inverse of unfulfillable-count.get.ts, i.e. the dashboard's
// "fulfillable = open − unfulfillable"). This is the number of orders a generate
// run on that warehouse can actually turn into shipments.
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'
    })
  }

  // Limited roles (FrontendMenu == 'c') only see their own organization. Admins
  // see all orgs (NULL filter is a no-op in the SQL below). Mirrors openso-count.
  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 {
    const sql = `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
                 ),
                 open_orders AS (
                   SELECT o.c_order_id, o.m_warehouse_id, o.ad_org_id
                   FROM c_order o
                   WHERE o.issotrx = 'Y'
                     AND o.docstatus IN ('CO','CL')
                     AND o.ad_client_id = 1000000
                     AND o.isfulfillmentorder = 'Y'
                     AND ($1::int IS NULL OR o.ad_org_id = $1)
                     AND EXISTS (
                           SELECT 1
                           FROM c_orderline ol
                           JOIN m_product p ON p.m_product_id = ol.m_product_id
                           WHERE ol.c_order_id = o.c_order_id
                             AND p.producttype = 'I'
                             AND (ol.qtyordered - COALESCE(ol.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')
                     )
                 )
                 SELECT oo.m_warehouse_id AS warehouse_id,
                        COALESCE(wh.name, 'Unknown') AS warehouse_name,
                        oo.ad_org_id AS org_id,
                        COALESCE(org.name, 'Unknown') AS org_name,
                        COUNT(*) AS count_per_warehouse,
                        -- Fulfillable = no product whose SUMMED open demand on the
                        -- order exceeds the on-hand stock in the order's warehouse
                        -- (same rule as unfulfillable-count / openso-list).
                        COUNT(*) FILTER (WHERE NOT EXISTS (
                          SELECT 1
                          FROM c_orderline ol
                          JOIN m_product p ON p.m_product_id = ol.m_product_id
                          LEFT JOIN stock st
                            ON st.m_product_id   = ol.m_product_id
                           AND st.m_warehouse_id = oo.m_warehouse_id
                          WHERE ol.c_order_id = oo.c_order_id
                            AND p.producttype = 'I'
                            AND (ol.qtyordered - COALESCE(ol.qtydelivered, 0)) > 0
                          GROUP BY ol.m_product_id
                          HAVING SUM(ol.qtyordered - COALESCE(ol.qtydelivered, 0)) > COALESCE(MAX(st.qtyonhand), 0)
                        )) AS fulfillable_per_warehouse
                 FROM open_orders oo
                 LEFT JOIN ad_org org ON org.ad_org_id = oo.ad_org_id
                 LEFT JOIN m_warehouse wh ON wh.m_warehouse_id = oo.m_warehouse_id
                 GROUP BY oo.m_warehouse_id, wh.name, oo.ad_org_id, org.name
                 ORDER BY count_per_warehouse DESC`

    const result = await pool.query(sql, [orgFilter])

    const byWarehouse = result.rows.map((r: any) => ({
      warehouseId: r.warehouse_id != null ? parseInt(r.warehouse_id) : null,
      warehouseName: r.warehouse_name,
      orgId: r.org_id != null ? parseInt(r.org_id) : null,
      orgName: r.org_name,
      count: parseInt(r.count_per_warehouse || 0),
      fulfillable: parseInt(r.fulfillable_per_warehouse || 0)
    }))

    // Per-warehouse maps keyed by id (string keys for safe client-side lookup).
    const counts: Record<string, number> = {}
    const fulfillableCounts: Record<string, number> = {}
    for (const r of byWarehouse) {
      if (r.warehouseId != null) {
        counts[String(r.warehouseId)] = (counts[String(r.warehouseId)] || 0) + r.count
        fulfillableCounts[String(r.warehouseId)] = (fulfillableCounts[String(r.warehouseId)] || 0) + r.fulfillable
      }
    }

    // Roll up per org as well, for any org-level summary.
    const byOrgMap: Record<string, { orgId: number | null, org: string, count: number, fulfillable: number }> = {}
    for (const r of byWarehouse) {
      const key = String(r.orgId ?? r.orgName)
      if (!byOrgMap[key]) byOrgMap[key] = { orgId: r.orgId, org: r.orgName, count: 0, fulfillable: 0 }
      byOrgMap[key].count += r.count
      byOrgMap[key].fulfillable += r.fulfillable
    }
    const byOrg = Object.values(byOrgMap).sort((a, b) => b.count - a.count)

    const count = byWarehouse.reduce((acc, r) => acc + r.count, 0)
    const fulfillable = byWarehouse.reduce((acc, r) => acc + r.fulfillable, 0)

    return { count, fulfillable, counts, fulfillableCounts, byWarehouse, byOrg }
  } catch (err: any) {
    console.error('PostgreSQL query error:', 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, authToken)
    } catch(error: any) {
      console.error('Fatal error in openso-count-by-warehouse:', error)
      throw error
    }
  }
})