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'
    })
  }

  const pool = new Pool(dbConfig)
  const query = getQuery(event)
  const orgFilter = (query.org as string) || ''

  // 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 orgIdFilter: number | null = (menuType === 'c' && Number.isFinite(orgIdNum)) ? orgIdNum : null

  try {
    const params: any[] = [orgIdFilter]
    let orgClause = ''
    if (orgFilter) {
      params.push(orgFilter)
      orgClause = `AND COALESCE(org.name, 'Unknown') = $${params.length}`
    }

    const sql = `
      SELECT
        io.m_inout_id        AS id,
        io.documentno        AS document_no,
        io.docstatus         AS doc_status,
        io.movementdate      AS movement_date,
        io.created           AS created,
        COALESCE(org.name, 'Unknown') AS org_name,
        bp.name              AS partner_name,
        wh.name              AS warehouse_name,
        ord.documentno       AS order_no,
        ord.c_order_id       AS order_id,
        loc.name             AS location_name,
        shp.name             AS shipper_name,
        io.poreference       AS po_reference,
        (SELECT COALESCE(SUM(l.qtyentered), 0) FROM m_inoutline l WHERE l.m_inout_id = io.m_inout_id) AS total_qty
      FROM m_inout io
      LEFT JOIN ad_org               org ON org.ad_org_id = io.ad_org_id
      LEFT JOIN c_bpartner           bp  ON bp.c_bpartner_id = io.c_bpartner_id
      LEFT JOIN m_warehouse          wh  ON wh.m_warehouse_id = io.m_warehouse_id
      LEFT JOIN c_order              ord ON ord.c_order_id = io.c_order_id
      LEFT JOIN c_bpartner_location  loc ON loc.c_bpartner_location_id = io.c_bpartner_location_id
      LEFT JOIN m_shipper            shp ON shp.m_shipper_id = io.m_shipper_id
      WHERE io.ad_client_id   = 1000000
        AND io.isactive       = 'Y'
        AND io.issotrx        = 'Y'
        AND io.iscommissioned = 'N'
        AND io.docstatus      = 'CO'
        AND io.movementtype   = 'C-'
        AND io.c_doctype_id   = 1000011
        AND ord.isfulfillmentorder = 'Y'
        AND ($1::int IS NULL OR io.ad_org_id = $1)
        ${orgClause}
      ORDER BY io.movementdate DESC NULLS LAST, io.m_inout_id DESC
      LIMIT 300
    `

    const result = await pool.query(sql, params)

    const records = result.rows.map((r: any) => ({
      id: Number(r.id),
      documentNo: r.document_no || '',
      docStatus: r.doc_status || '',
      movementDate: r.movement_date,
      created: r.created,
      orgName: r.org_name || '',
      partnerName: r.partner_name || '',
      warehouseName: r.warehouse_name || '',
      orderNo: r.order_no || '',
      orderId: r.order_id ? Number(r.order_id) : null,
      locationName: r.location_name || '',
      shipperName: r.shipper_name || '',
      poReference: r.po_reference || '',
      totalQty: Number(r.total_qty || 0)
    }))

    return { records, org: orgFilter || null }
  } 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 open-to-commission-list:', error)
      throw error
    }
  }
})