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

// Return-rate per article = returned qty ÷ total shipped qty, aggregated across
// all completed customer movements. Mirrors shipped-stats.get.ts: same pg Pool,
// same tenant (ad_client_id = 1000000) and the same limited-role org scoping.
// Selection is the top-5 by RATE (returned ÷ shipped) descending, restricted to
// articles that actually have a shipped baseline so the rate is meaningful.
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 {
    // movementtype 'C+' = customer return (receipt), 'C-' = customer shipment
    // (delivery). ABS() guards against either sign convention. Both legs are
    // completed, active, SO-trx movements for the tenant.
    const sql = `
      WITH returned AS (
        SELECT iol.m_product_id AS pid, SUM(ABS(iol.movementqty)) AS qty_returned
        FROM m_inout ino
        JOIN m_inoutline iol ON iol.m_inout_id = ino.m_inout_id
        WHERE ino.issotrx      = 'Y'
          AND ino.isactive     = 'Y'
          AND ino.docstatus    = 'CO'
          AND ino.movementtype = 'C+'
          AND ino.ad_client_id = 1000000
          AND ($1::int IS NULL OR ino.ad_org_id = $1)
          AND iol.isactive     = 'Y'
          AND iol.m_product_id IS NOT NULL
        GROUP BY iol.m_product_id
      ),
      shipped AS (
        SELECT iol.m_product_id AS pid, SUM(ABS(iol.movementqty)) AS qty_shipped
        FROM m_inout ino
        JOIN m_inoutline iol ON iol.m_inout_id = ino.m_inout_id
        WHERE ino.issotrx      = 'Y'
          AND ino.isactive     = 'Y'
          AND ino.docstatus    = 'CO'
          AND ino.movementtype = 'C-'
          AND ino.ad_client_id = 1000000
          AND ($1::int IS NULL OR ino.ad_org_id = $1)
          AND iol.isactive     = 'Y'
          AND iol.m_product_id IS NOT NULL
        GROUP BY iol.m_product_id
      )
      SELECT r.pid AS id,
             p.name  AS name,
             p.value AS sku,
             r.qty_returned             AS qty_returned,
             COALESCE(s.qty_shipped, 0) AS qty_shipped
      FROM returned r
      JOIN m_product p ON p.m_product_id = r.pid
      LEFT JOIN shipped s ON s.pid = r.pid
      WHERE COALESCE(s.qty_shipped, 0) > 0
      ORDER BY (r.qty_returned::numeric / s.qty_shipped) DESC
      LIMIT 5
    `

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

    return {
      records: (result.rows || []).map((row: any) => ({
        id: row.id,
        name: row.name || '',
        sku: row.sku || '',
        qtyReturned: Number(row.qty_returned || 0),
        qtyShipped: Number(row.qty_shipped || 0)
      }))
    }
  } 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 return-rate:', error)
      return { records: [] }
    }
  }
})