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

// Average fulfillment handling time (a.k.a. "order-to-ship" / "click-to-ship"
// time) for customer outbound shipments: the span from order import
// (c_order.created) until the package leaves (m_inout.shipping_date), counting
// ONLY business time — Monday–Friday, 09:00–16:00. Weekends and out-of-hours
// time are excluded. Limited to shipments shipped in the last 10 days so the
// query stays bounded.
//
// Business time is summed per shipment by walking each calendar day in the
// [created, shipped] span and, for weekdays only, measuring the overlap of the
// active interval with that day's 09:00–16:00 opening window.
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 = `
      WITH shp AS (
        SELECT
          ino.m_inout_id,
          ord.created        AS order_created,
          ino.shipping_date  AS ship_date
        FROM m_inout ino
        JOIN c_order ord ON ord.c_order_id = ino.c_order_id
        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)
          AND ino.shipping_date IS NOT NULL
          AND ord.created IS NOT NULL
          AND ino.shipping_date >= ord.created
          AND ino.shipping_date >= (CURRENT_DATE - 9)
      ),
      per_shipment AS (
        SELECT
          s.m_inout_id,
          SUM(
            GREATEST(0, EXTRACT(EPOCH FROM (
              LEAST(d.day + interval '16 hour', s.ship_date)
              - GREATEST(d.day + interval '9 hour', s.order_created)
            )))
          ) AS business_seconds
        FROM shp s
        CROSS JOIN LATERAL generate_series(s.order_created::date, s.ship_date::date, interval '1 day') AS d(day)
        WHERE EXTRACT(DOW FROM d.day) BETWEEN 1 AND 5   -- Mon..Fri only
        GROUP BY s.m_inout_id
      )
      SELECT
        COUNT(*)              AS shipment_count,
        AVG(business_seconds) AS avg_business_seconds
      FROM per_shipment
    `

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

    return {
      shipmentCount: parseInt(row.shipment_count || 0),
      avgMinutes: Math.round(avgSeconds / 60),
      windowDays: 10
    }
  } 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 {
      await refreshTokenHelper(event)
      return await handleFunc(event)
    } catch(error: any) {
      console.error('Fatal error in fulfillment-speed:', error)
      throw error
    }
  }
})