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

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).
  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 warehouse_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
      ),
      total_stock AS (
        SELECT s.m_product_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
      )
      SELECT
        p.m_product_id AS id,
        p.name,
        p.value,
        p.sku,
        p.isbom,
        p.isjtlbom,
        p.strapi_product_documentid AS strapi_product_documentid,
        COUNT(DISTINCT o.c_order_id) AS order_count,
        SUM(ol.qtyordered - COALESCE(ol.qtydelivered, 0)) AS qty_needed,
        COALESCE(MAX(ts.qtyonhand), 0) AS qty_on_hand,
        GREATEST(
          SUM(ol.qtyordered - COALESCE(ol.qtydelivered, 0)) - COALESCE(MAX(ts.qtyonhand), 0),
          0
        ) AS qty_missing,
        ARRAY_AGG(DISTINCT COALESCE(org.name, 'Unknown')) AS org_names
      FROM c_order o
      JOIN c_orderline ol ON ol.c_order_id = o.c_order_id
      JOIN m_product p ON p.m_product_id = ol.m_product_id
      LEFT JOIN ad_org org ON org.ad_org_id = o.ad_org_id
      LEFT JOIN warehouse_stock ws
        ON ws.m_product_id = ol.m_product_id
       AND ws.m_warehouse_id = o.m_warehouse_id
      LEFT JOIN total_stock ts
        ON ts.m_product_id = ol.m_product_id
      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 ol.m_product_id IS NOT NULL
        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')
        )
      GROUP BY p.m_product_id, p.name, p.value, p.sku, p.isbom, p.isjtlbom, p.strapi_product_documentid
      -- Missing = SUMMED open demand exceeds stock. A per-line comparison here
      -- (the previous version) missed products whose demand is split across
      -- several lines/orders each individually below stock — e.g. one order
      -- with 3 lines of 15+40+40 = 95 needed vs 88 on hand was NOT listed.
      -- The BOOL_OR keeps the old per-line warehouse check as a safety net for
      -- demand sitting in a warehouse that has no stock of the product.
      HAVING SUM(ol.qtyordered - COALESCE(ol.qtydelivered, 0)) > COALESCE(MAX(ts.qtyonhand), 0)
          OR BOOL_OR((ol.qtyordered - COALESCE(ol.qtydelivered, 0)) > COALESCE(ws.qtyonhand, 0))
      ORDER BY qty_missing DESC, qty_needed DESC
      LIMIT 50
    `

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

    const records = result.rows.map((r: any) => ({
      id: Number(r.id),
      name: r.name,
      value: r.value,
      sku: r.sku,
      isBom: r.isbom === 'Y',
      isJtlBom: r.isjtlbom === 'Y',
      strapiProductId: r.strapi_product_documentid || null,
      orderCount: parseInt(r.order_count || 0),
      qtyNeeded: parseFloat(r.qty_needed || 0),
      qtyOnHand: parseFloat(r.qty_on_hand || 0),
      qtyMissing: parseFloat(r.qty_missing || 0),
      orgNames: Array.isArray(r.org_names) ? r.org_names.filter(Boolean) : []
    }))

    return { records }
  } 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)
    } catch(error: any) {
      console.error('Fatal error in unfulfillable-products:', error)
      throw error
    }
  }
})