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 dimensions
    const sql = `
      SELECT DISTINCT
        p.m_product_id AS id,
        p.name,
        p.sku,
        p.width,
        p.length,
        p.height,
        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
      INNER JOIN c_bpartner bp ON bp.c_bpartner_id = o.c_bpartner_id
      WHERE p.isactive = 'Y'
        AND s.qtyonhand > 0
        AND p.ad_client_id = 1000000
        AND bp.iscustomer = 'Y'
        AND bp.isactive = 'Y'
        AND bp.isfulfillmentcustomer = 'Y'
        AND bp.isaccountingwarehousevolume = 'Y'
        AND (
          p.width IS NULL OR p.width::numeric = 0
          OR p.length IS NULL OR p.length::numeric = 0
          OR p.height IS NULL OR p.height::numeric = 0
        )
      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-dimensions:', error)
      throw error
    }
  }
})