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

const handleFunc = async (event: any) => {
  // Get PostgreSQL credentials from environment
  const config = useRuntimeConfig()
  const dbConfig = {
    host: config.pgHost,
    port: parseInt(config.pgPort || '5432'),
    database: config.pgDatabase,
    user: config.pgUser,
    password: config.pgPassword,
  }

  // Validate required credentials
  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)

  try {
    // Direct SQL query to count orders without shipments
    const sql = `SELECT COUNT(*) AS orders_without_shipment
                 FROM c_order o
                 WHERE o.issotrx = 'Y'
                   AND o.docstatus IN ('CO','CL')
                   AND o.ad_client_id = 1000000
                   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')
                   )`

    const result = await pool.query(sql)

    return {
      count: parseInt(result.rows[0]?.orders_without_shipment || 0)
    }
  } 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:', error)
      throw error
    }
  }
})