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

// List the actual orders behind the "Offene Aufträge" dashboard widget.
// Same WHERE-clause universe as openso-count + unfulfillable-count, and tags
// each row with `is_unfulfillable` so the modal can split / filter.
//
// Query params:
//   org    — filter to a specific organisation by name (optional)
//   status — 'fillable' | 'unfillable' | 'all'  (defaults to 'all')
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 org = (query?.org as string) || null
  const status = ((query?.status as string) || 'all').toLowerCase()

  // 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 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.documentno,
               o.dateordered,
               o.datepromised,
               o.grandtotal,
               o.m_warehouse_id,
               o.ad_org_id,
               COALESCE(org.name, 'Unknown') AS org_name,
               bp.name AS partner_name
        FROM c_order o
        LEFT JOIN ad_org    org ON org.ad_org_id    = o.ad_org_id
        LEFT JOIN c_bpartner bp ON bp.c_bpartner_id = o.c_bpartner_id
        WHERE o.issotrx = 'Y'
          AND o.docstatus IN ('CO','CL')
          AND o.ad_client_id = 1000000
          AND o.isfulfillmentorder = 'Y'
          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.c_order_id   AS id,
        oo.documentno,
        oo.dateordered,
        oo.datepromised,
        oo.grandtotal,
        oo.org_name,
        oo.partner_name,
        -- Unfulfillable when the order's SUMMED open demand for any product
        -- exceeds warehouse stock (per-line comparison missed demand split
        -- across several lines of the same product).
        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 is_unfulfillable
      FROM open_orders oo
      WHERE ($1::int IS NULL OR oo.ad_org_id = $1)
        ${org ? 'AND oo.org_name = $2' : ''}
      ORDER BY oo.dateordered DESC NULLS LAST, oo.c_order_id DESC
    `

    const params: any[] = [orgFilter]
    if (org) params.push(org)

    const result = await pool.query(sql, params)
    let records = result.rows.map((r: any) => ({
      id: Number(r.id),
      documentNo: r.documentno,
      dateOrdered: r.dateordered,
      datePromised: r.datepromised,
      grandTotal: parseFloat(r.grandtotal || 0),
      orgName: r.org_name || '',
      partnerName: r.partner_name || '',
      isUnfulfillable: !!r.is_unfulfillable
    }))

    if (status === 'fillable')   records = records.filter(r => !r.isUnfulfillable)
    if (status === 'unfillable') records = records.filter(r =>  r.isUnfulfillable)

    return { records, count: records.length }
  } 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 {
      const authToken: any = await refreshTokenHelper(event)
      return await handleFunc(event)
    } catch (error: any) {
      console.error('Fatal error in openso-list:', error)
      throw error
    }
  }
})