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

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

  const pool = new Pool(dbConfig)

  try {
    const sql = `
      SELECT
        o.c_order_id    AS id,
        o.documentno,
        o.dateordered,
        o.datepromised,
        o.grandtotal,
        bp.name         AS partner,
        org.name        AS org_name,
        COALESCE(ol.line_count, 0) AS line_count,
        COALESCE(ol.total_qty, 0)  AS total_qty
      FROM c_order o
      LEFT JOIN c_bpartner bp ON bp.c_bpartner_id = o.c_bpartner_id
      LEFT JOIN ad_org org    ON org.ad_org_id    = o.ad_org_id
      LEFT JOIN (
        SELECT c_order_id, COUNT(*) AS line_count, SUM(qtyordered) AS total_qty
        FROM c_orderline
        GROUP BY c_order_id
      ) ol ON ol.c_order_id = o.c_order_id
      WHERE o.issotrx     = 'N'
        AND o.docstatus   = 'CO'
        AND o.ad_client_id = 1000000
        AND o.datepromised IS NOT NULL
        AND ($1::int IS NULL OR o.ad_org_id = $1)
      ORDER BY o.datepromised ASC
      LIMIT 50
    `

    const result = await pool.query(sql, [orgFilter])

    const records = result.rows.map((r: any) => ({
      id: Number(r.id),
      documentNo: r.documentno,
      dateOrdered: r.dateordered,
      datePromised: r.datepromised,
      grandTotal: parseFloat(r.grandtotal || 0),
      lineCount: parseInt(r.line_count || 0),
      totalQty: parseFloat(r.total_qty || 0),
      partner: r.partner || '',
      orgName: r.org_name || ''
    }))

    return { records }
  } 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 po-upcoming:', error)
      throw error
    }
  }
})