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 get products without weight
    const sql = `
      SELECT DISTINCT
        p.m_product_id AS id,
        p.name,
        p.sku,
        p.weight,
        p.ad_org_id AS "orgId",
        o.name AS "orgName"
      FROM m_product p
      INNER JOIN m_storage s ON s.m_product_id = p.m_product_id
      INNER JOIN ad_org o ON o.ad_org_id = p.ad_org_id
      WHERE p.isactive = 'Y'
        AND p.isbom = 'N'
        AND s.qtyonhand > 0
        AND p.ad_client_id = 1000000
        AND (p.weight IS NULL OR p.weight::numeric = 0)
        AND bp.isfulfillmentcustomer = 'Y'
      ORDER BY p.name
    `

    const result = await pool.query(sql)

    return {
      records: result.rows
    }
  } 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 missing-weight:', error)
      throw error
    }
  }
})