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

// Ordered queue for "sequential shipment generation": same open-order universe
// as openso-list.get.ts (dashboard "Offene Aufträge"), restricted to FULFILLABLE
// orders only, ordered by dateordered ASC (oldest first).
//
// Query params:
//   warehouseIds — comma-separated m_warehouse_ids; absent/empty = all warehouses
//   partnerId    — optional c_bpartner_id filter
//   singleQty1   — 'true' keeps only orders with EXACTLY ONE item-type line
//                  (qtyordered > 0) and that line's qtyordered = 1
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 warehouseIds = String(query?.warehouseIds ?? '')
    .split(',')
    .map((s: string) => parseInt(s.trim(), 10))
    .filter((n: number) => Number.isFinite(n))
  const singleQty1 = String(query?.singleQty1 ?? '').toLowerCase() === 'true'
  const partnerIdNum = query?.partnerId ? parseInt(String(query.partnerId), 10) : NaN
  const partnerId: number | null = Number.isFinite(partnerIdNum) ? partnerIdNum : null

  // Limited roles (FrontendMenu == 'c') only see their own organization — mirrors openso-list.
  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 params: any[] = [orgFilter]
    let whClause = ''
    if (warehouseIds.length > 0) {
      params.push(warehouseIds)
      whClause = `AND o.m_warehouse_id = ANY($${params.length}::int[])`
    }
    let bpClause = ''
    if (partnerId !== null) {
      params.push(partnerId)
      bpClause = `AND o.c_bpartner_id = $${params.length}`
    }
    // Exactly one item-type line with a real quantity (> 0 skips zeroed/cancelled
    // lines so they don't disqualify the order), and that line's qty is 1.
    const singleQty1Clause = singleQty1 ? `
        AND (
          SELECT COUNT(*) = 1 AND MAX(ol.qtyordered) = 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 > 0
        )` : ''

    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
      )
      SELECT o.c_order_id     AS id,
             o.documentno,
             o.dateordered,
             o.datepromised,
             o.c_bpartner_id  AS partner_id,
             COALESCE(bp.name, 'Unknown')  AS partner_name,
             o.m_warehouse_id AS warehouse_id,
             COALESCE(wh.name, 'Unknown')  AS warehouse_name,
             o.ad_org_id      AS org_id,
             COALESCE(org.name, 'Unknown') AS org_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
      LEFT JOIN m_warehouse wh  ON wh.m_warehouse_id = o.m_warehouse_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)
        ${whClause}
        ${bpClause}
        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')
        )
        -- FULFILLABLE only (inverted is_unfulfillable from openso-list):
        -- no product whose SUMMED open demand on this order exceeds the
        -- on-hand stock in the order's warehouse (per-line comparison missed
        -- demand split across several lines of the same product)
        AND 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 = o.m_warehouse_id
              WHERE ol.c_order_id = o.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)
        )
        ${singleQty1Clause}
      ORDER BY o.dateordered ASC NULLS LAST, o.c_order_id ASC
    `

    const result = await pool.query(sql, params)
    const records = result.rows.map((r: any) => ({
      id: Number(r.id),
      documentNo: r.documentno,
      dateOrdered: r.dateordered,
      datePromised: r.datepromised,
      partnerId: r.partner_id != null ? Number(r.partner_id) : null,
      partnerName: r.partner_name || '',
      warehouseId: r.warehouse_id != null ? Number(r.warehouse_id) : null,
      warehouseName: r.warehouse_name || '',
      orgId: r.org_id != null ? Number(r.org_id) : null,
      orgName: r.org_name || ''
    }))
    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 {
      await refreshTokenHelper(event)
      return await handleFunc(event)
    } catch (error: any) {
      console.error('Fatal error in openso-generation-queue:', error)
      throw error
    }
  }
})