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 {
    // Counts use `shipping_date` (the real "package left" timestamp set when
    // the shipment is commissioned). `movementdate` was unreliable here because
    // it's the iDempiere business-movement date, not the actual ship date.
    // fri_to_mon = the most recent Friday→Monday window. "Last Monday" is the
    // most recent Monday on or before today; the Friday is that Monday − 3 days.
    const sql = `
      WITH bounds AS (
        SELECT
          CURRENT_DATE AS today,
          (CURRENT_DATE - ((EXTRACT(DOW FROM CURRENT_DATE)::int + 6) % 7))::date AS last_monday,
          ((CURRENT_DATE - ((EXTRACT(DOW FROM CURRENT_DATE)::int + 6) % 7)) - 3)::date AS prev_friday
      )
      SELECT
        COUNT(*) FILTER (WHERE ino.shipping_date::date = b.today)                                AS today,
        COUNT(*) FILTER (WHERE ino.shipping_date::date = b.today - 1)                            AS yesterday,
        COUNT(*) FILTER (WHERE ino.shipping_date::date >= b.today - 2)                           AS last_3_days,
        COUNT(*) FILTER (WHERE ino.shipping_date::date BETWEEN b.prev_friday AND b.last_monday)  AS fri_to_mon,
        COUNT(*) FILTER (WHERE ino.shipping_date::date >= b.today - 6)                           AS last_7_days,
        COUNT(*) FILTER (WHERE ino.shipping_date::date >= b.today - 29)                          AS last_30_days,
        b.prev_friday::text  AS fri_to_mon_from,
        b.last_monday::text  AS fri_to_mon_to
      FROM m_inout ino
      CROSS JOIN bounds b
      WHERE ino.issotrx       = 'Y'
        AND ino.isactive      = 'Y'
        AND ino.docstatus     = 'CO'
        AND ino.c_doctype_id  = 1000011
        AND ino.movementtype  = 'C-'
        AND ino.ad_client_id  = 1000000
        AND ($1::int IS NULL OR ino.ad_org_id = $1)
      GROUP BY b.prev_friday, b.last_monday
    `

    const result = await pool.query(sql, [orgFilter])
    const row: any = result.rows[0] || {}

    return {
      today:       parseInt(row.today       || 0),
      yesterday:   parseInt(row.yesterday   || 0),
      last3Days:   parseInt(row.last_3_days || 0),
      friToMon:    parseInt(row.fri_to_mon  || 0),
      last7Days:   parseInt(row.last_7_days || 0),
      last30Days:  parseInt(row.last_30_days || 0),
      friToMonFrom: row.fri_to_mon_from || null,
      friToMonTo:   row.fri_to_mon_to   || 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 shipped-stats:', error)
      throw error
    }
  }
})