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

/**
 * BI → Product Performance (admin-only page, direct Postgres like the other pg routes).
 *
 * Sold qty = completed customer shipments (m_inout C-, dated by shipping_date, falling
 * back to movementdate), returns = completed customer returns (C+, movementdate).
 * Revenue = shipped qty × c_orderline.priceactual (net) of the linked order line.
 *
 * Query params
 *   orgId       organisation (required; limited roles are forced to their own org)
 *   mode        'window' (default) | 'monthly'
 *   days        window length in days: 3 | 7 | 14 | 30 | 90 | 180 | 365 (window mode)
 *   months      number of monthly buckets, default 12, max 24 (monthly mode)
 *   productIds  comma-separated M_Product_IDs → only these products (manual scope)
 *   limit       top-N products by sold qty when productIds is empty (default 50, max 300)
 */
const CLIENT_ID = 1000000
const ALLOWED_DAYS = [3, 7, 14, 30, 90, 180, 365]

const toIso = (d: Date) => {
  const y = d.getFullYear()
  const m = String(d.getMonth() + 1).padStart(2, '0')
  const day = String(d.getDate()).padStart(2, '0')
  return `${y}-${m}-${day}`
}
const addDays = (d: Date, n: number) => { const x = new Date(d); x.setDate(x.getDate() + n); return x }
const startOfMonth = (d: Date) => new Date(d.getFullYear(), d.getMonth(), 1)
const addMonths = (d: Date, n: number) => new Date(d.getFullYear(), d.getMonth() + n, 1)
const num = (v: any) => { const n = Number(v); return Number.isFinite(n) ? n : 0 }

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

  const query = getQuery(event)
  const mode = String(query?.mode || 'window') === 'monthly' ? 'monthly' : 'window'
  let days = parseInt(String(query?.days || '30'))
  if (!ALLOWED_DAYS.includes(days)) days = 30
  let months = parseInt(String(query?.months || '12'))
  if (!Number.isFinite(months) || months < 3) months = 12
  if (months > 24) months = 24
  let limit = parseInt(String(query?.limit || '50'))
  if (!Number.isFinite(limit) || limit < 5) limit = 50
  if (limit > 300) limit = 300
  const productIds = String(query?.productIds || '')
    .split(',').map((s) => parseInt(s.trim())).filter((n) => Number.isFinite(n) && n > 0)

  // Limited roles (FrontendMenu == 'c') only ever see their own organisation.
  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 ownOrgCookie = getCookie(event, 'logship_organization_id')
  const ownOrgId = ownOrgCookie ? parseInt(ownOrgCookie) : NaN
  const requestedOrg = query?.orgId != null && String(query.orgId) !== '' ? parseInt(String(query.orgId)) : NaN
  let orgId: number | null = Number.isFinite(requestedOrg) ? requestedOrg : (Number.isFinite(ownOrgId) ? ownOrgId : null)
  if (menuType === 'c') orgId = Number.isFinite(ownOrgId) ? ownOrgId : -1
  if (!orgId) throw createError({ statusCode: 400, message: 'orgId is required' })

  const pool = new Pool(dbConfig)
  try {
    // ---------------------------------------------------------------- shared pieces
    // Customer shipments / returns of the org, one row per line, with the business date.
    const movesCte = `
      moves AS (
        SELECT ino.m_inout_id,
               ino.movementtype,
               iol.m_product_id                                   AS pid,
               ABS(iol.movementqty)                               AS qty,
               ABS(iol.movementqty) * COALESCE(ol.priceactual, 0) AS revenue,
               CASE WHEN ino.movementtype = 'C-'
                    THEN COALESCE(ino.shipping_date, ino.movementdate)::date
                    ELSE ino.movementdate::date END               AS d
        FROM m_inout ino
        JOIN m_inoutline iol ON iol.m_inout_id = ino.m_inout_id
        LEFT JOIN c_orderline ol ON ol.c_orderline_id = iol.c_orderline_id
        WHERE ino.issotrx      = 'Y'
          AND ino.isactive     = 'Y'
          AND ino.docstatus    = 'CO'
          AND ino.movementtype IN ('C-', 'C+')
          AND ino.ad_client_id = ${CLIENT_ID}
          AND ino.ad_org_id    = $1
          AND iol.isactive     = 'Y'
          AND iol.m_product_id IS NOT NULL
          AND iol.movementqty  <> 0
      )`
    const productSelect = `
        p.m_product_id AS id,
        p.name,
        p.value,
        p.sku,
        p.upc,
        p.isbom,
        p.isjtlbom,
        p.isactive,
        p.imageurl,
        pc.name AS category`
    const productJoin = `
      JOIN m_product p ON p.m_product_id = t.pid
      LEFT JOIN m_product_category pc ON pc.m_product_category_id = p.m_product_category_id`

    if (mode === 'window') {
      const today = new Date()
      const from = addDays(today, -(days - 1))       // inclusive window of `days` days ending today
      const prevFrom = addDays(from, -days)          // previous window of equal length, right before
      const prevTo = addDays(from, -1)
      const params: any[] = [orgId, toIso(from), toIso(today), toIso(prevFrom)]
      const scopeSql = productIds.length ? `AND t.pid = ANY($5::int[])` : ''
      if (productIds.length) params.push(productIds)
      else params.push(limit)
      const limitSql = productIds.length ? '' : `LIMIT $5`

      const sql = `
      WITH ${movesCte},
      agg AS (
        SELECT pid,
               SUM(qty)      FILTER (WHERE movementtype = 'C-' AND d >= $2::date AND d <= $3::date) AS qty_sold,
               COUNT(DISTINCT m_inout_id) FILTER (WHERE movementtype = 'C-' AND d >= $2::date AND d <= $3::date) AS shipments,
               SUM(revenue)  FILTER (WHERE movementtype = 'C-' AND d >= $2::date AND d <= $3::date) AS revenue,
               SUM(qty)      FILTER (WHERE movementtype = 'C+' AND d >= $2::date AND d <= $3::date) AS qty_returned,
               SUM(qty)      FILTER (WHERE movementtype = 'C-' AND d >= $4::date AND d <  $2::date) AS qty_sold_prev,
               SUM(revenue)  FILTER (WHERE movementtype = 'C-' AND d >= $4::date AND d <  $2::date) AS revenue_prev,
               SUM(qty)      FILTER (WHERE movementtype = 'C+' AND d >= $4::date AND d <  $2::date) AS qty_returned_prev,
               MAX(d)        FILTER (WHERE movementtype = 'C-') AS last_sold
        FROM moves
        WHERE d >= $4::date AND d <= $3::date
        GROUP BY pid
      ),
      stock AS (
        SELECT s.m_product_id AS pid, SUM(s.qtyonhand) AS qty_on_hand
        FROM m_storage s
        JOIN m_locator l ON l.m_locator_id = s.m_locator_id
        WHERE l.ad_org_id = $1
        GROUP BY s.m_product_id
      )
      SELECT ${productSelect},
             COALESCE(t.qty_sold, 0)          AS qty_sold,
             COALESCE(t.shipments, 0)         AS shipments,
             COALESCE(t.revenue, 0)           AS revenue,
             COALESCE(t.qty_returned, 0)      AS qty_returned,
             COALESCE(t.qty_sold_prev, 0)     AS qty_sold_prev,
             COALESCE(t.revenue_prev, 0)      AS revenue_prev,
             COALESCE(t.qty_returned_prev, 0) AS qty_returned_prev,
             t.last_sold,
             COALESCE(st.qty_on_hand, 0)      AS qty_on_hand
      FROM agg t
      ${productJoin}
      LEFT JOIN stock st ON st.pid = t.pid
      WHERE (t.qty_sold > 0 OR t.qty_returned > 0 OR t.qty_sold_prev > 0 ${productIds.length ? 'OR TRUE' : ''})
      ${scopeSql}
      ORDER BY COALESCE(t.qty_sold, 0) DESC, COALESCE(t.qty_returned, 0) DESC, p.name ASC
      ${limitSql}`

      // Org-level daily series (all products, not just the listed ones) for the trend chart
      const dailySql = `
      WITH ${movesCte}
      SELECT d,
             SUM(qty) FILTER (WHERE movementtype = 'C-') AS qty_sold,
             SUM(qty) FILTER (WHERE movementtype = 'C+') AS qty_returned,
             COUNT(DISTINCT m_inout_id) FILTER (WHERE movementtype = 'C-') AS shipments,
             SUM(revenue) FILTER (WHERE movementtype = 'C-') AS revenue
      FROM moves
      WHERE d >= $2::date AND d <= $3::date
      GROUP BY d
      ORDER BY d`

      // Org totals for the window + previous window (KPI cards) — all products
      const totalsSql = `
      WITH ${movesCte}
      SELECT
        SUM(qty)     FILTER (WHERE movementtype = 'C-' AND d >= $2::date) AS qty_sold,
        SUM(revenue) FILTER (WHERE movementtype = 'C-' AND d >= $2::date) AS revenue,
        SUM(qty)     FILTER (WHERE movementtype = 'C+' AND d >= $2::date) AS qty_returned,
        COUNT(DISTINCT m_inout_id) FILTER (WHERE movementtype = 'C-' AND d >= $2::date) AS shipments,
        COUNT(DISTINCT pid)        FILTER (WHERE movementtype = 'C-' AND d >= $2::date) AS products_sold,
        SUM(qty)     FILTER (WHERE movementtype = 'C-' AND d < $2::date) AS qty_sold_prev,
        SUM(revenue) FILTER (WHERE movementtype = 'C-' AND d < $2::date) AS revenue_prev,
        SUM(qty)     FILTER (WHERE movementtype = 'C+' AND d < $2::date) AS qty_returned_prev,
        COUNT(DISTINCT m_inout_id) FILTER (WHERE movementtype = 'C-' AND d < $2::date) AS shipments_prev,
        COUNT(DISTINCT pid)        FILTER (WHERE movementtype = 'C-' AND d < $2::date) AS products_sold_prev
      FROM moves
      WHERE d >= $4::date AND d <= $3::date`

      const baseParams = [orgId, toIso(from), toIso(today), toIso(prevFrom)]
      const [rowsRes, dailyRes, totalsRes] = await Promise.all([
        pool.query(sql, params),
        pool.query(dailySql, baseParams.slice(0, 3)),
        pool.query(totalsSql, baseParams)
      ])

      const products = (rowsRes.rows || []).map((r: any) => {
        const sold = num(r.qty_sold), returned = num(r.qty_returned), onHand = num(r.qty_on_hand)
        const perDay = sold / days
        return {
          id: r.id,
          name: r.name,
          value: r.value,
          sku: r.sku,
          upc: r.upc,
          isBom: r.isbom === 'Y' || r.isjtlbom === 'Y',
          isActive: r.isactive === 'Y',
          imageUrl: r.imageurl || null,
          category: r.category || '',
          qtySold: sold,
          qtySoldPrev: num(r.qty_sold_prev),
          shipments: num(r.shipments),
          revenue: Math.round(num(r.revenue) * 100) / 100,
          revenuePrev: Math.round(num(r.revenue_prev) * 100) / 100,
          qtyReturned: returned,
          qtyReturnedPrev: num(r.qty_returned_prev),
          returnRate: sold > 0 ? Math.round((returned / sold) * 1000) / 10 : (returned > 0 ? 100 : 0),
          qtyOnHand: onHand,
          perDay: Math.round(perDay * 100) / 100,
          coverageDays: perDay > 0 ? Math.round(onHand / perDay) : null,
          lastSold: r.last_sold ? toIso(new Date(r.last_sold)) : null
        }
      })

      const t = totalsRes.rows?.[0] || {}
      const daily = (dailyRes.rows || []).map((r: any) => ({
        day: toIso(new Date(r.d)),
        qtySold: num(r.qty_sold), qtyReturned: num(r.qty_returned), shipments: num(r.shipments), revenue: Math.round(num(r.revenue) * 100) / 100
      }))
      // fill missing days with zeros so the chart has a continuous axis
      const byDay: Record<string, any> = Object.fromEntries(daily.map((x) => [x.day, x]))
      const dailyFilled: any[] = []
      for (let i = 0; i < days; i++) {
        const day = toIso(addDays(from, i))
        dailyFilled.push(byDay[day] || { day, qtySold: 0, qtyReturned: 0, shipments: 0, revenue: 0 })
      }

      return {
        mode, orgId, days,
        from: toIso(from), to: toIso(today), prevFrom: toIso(prevFrom), prevTo: toIso(prevTo),
        scope: productIds.length ? 'manual' : 'top',
        limit,
        totals: {
          qtySold: num(t.qty_sold), qtySoldPrev: num(t.qty_sold_prev),
          revenue: Math.round(num(t.revenue) * 100) / 100, revenuePrev: Math.round(num(t.revenue_prev) * 100) / 100,
          qtyReturned: num(t.qty_returned), qtyReturnedPrev: num(t.qty_returned_prev),
          shipments: num(t.shipments), shipmentsPrev: num(t.shipments_prev),
          productsSold: num(t.products_sold), productsSoldPrev: num(t.products_sold_prev),
          returnRate: num(t.qty_sold) > 0 ? Math.round((num(t.qty_returned) / num(t.qty_sold)) * 1000) / 10 : 0,
          returnRatePrev: num(t.qty_sold_prev) > 0 ? Math.round((num(t.qty_returned_prev) / num(t.qty_sold_prev)) * 1000) / 10 : 0
        },
        daily: dailyFilled,
        products
      }
    }

    // ---------------------------------------------------------------- monthly matrix
    const today = new Date()
    const firstBucket = addMonths(startOfMonth(today), -(months - 1))
    const params: any[] = [orgId, toIso(firstBucket), toIso(addDays(today, 1))]
    const scopeSql = productIds.length ? `WHERE pid = ANY($4::int[])` : ''
    if (productIds.length) params.push(productIds)
    else params.push(limit)
    const topSql = productIds.length
      ? `SELECT DISTINCT pid FROM moves ${scopeSql}`
      : `SELECT pid FROM moves WHERE movementtype = 'C-' GROUP BY pid ORDER BY SUM(qty) DESC LIMIT $4`

    const sql = `
      WITH ${movesCte.replace('AND iol.movementqty  <> 0', `AND iol.movementqty  <> 0
          AND (CASE WHEN ino.movementtype = 'C-' THEN COALESCE(ino.shipping_date, ino.movementdate) ELSE ino.movementdate END) >= $2::date
          AND (CASE WHEN ino.movementtype = 'C-' THEN COALESCE(ino.shipping_date, ino.movementdate) ELSE ino.movementdate END) <  $3::date`)},
      scope AS (${topSql}),
      agg AS (
        SELECT m.pid,
               date_trunc('month', m.d)::date AS month,
               SUM(m.qty)     FILTER (WHERE m.movementtype = 'C-') AS qty_sold,
               SUM(m.revenue) FILTER (WHERE m.movementtype = 'C-') AS revenue,
               SUM(m.qty)     FILTER (WHERE m.movementtype = 'C+') AS qty_returned
        FROM moves m
        JOIN scope s ON s.pid = m.pid
        GROUP BY m.pid, date_trunc('month', m.d)
      ),
      stock AS (
        SELECT s.m_product_id AS pid, SUM(s.qtyonhand) AS qty_on_hand
        FROM m_storage s
        JOIN m_locator l ON l.m_locator_id = s.m_locator_id
        WHERE l.ad_org_id = $1
        GROUP BY s.m_product_id
      )
      SELECT ${productSelect}, t.month, COALESCE(t.qty_sold, 0) AS qty_sold, COALESCE(t.revenue, 0) AS revenue,
             COALESCE(t.qty_returned, 0) AS qty_returned, COALESCE(st.qty_on_hand, 0) AS qty_on_hand
      FROM agg t
      ${productJoin}
      LEFT JOIN stock st ON st.pid = t.pid
      ORDER BY p.name, t.month`

    const monthlyTotalsSql = `
      WITH ${movesCte}
      SELECT date_trunc('month', d)::date AS month,
             SUM(qty)     FILTER (WHERE movementtype = 'C-') AS qty_sold,
             SUM(revenue) FILTER (WHERE movementtype = 'C-') AS revenue,
             SUM(qty)     FILTER (WHERE movementtype = 'C+') AS qty_returned,
             COUNT(DISTINCT m_inout_id) FILTER (WHERE movementtype = 'C-') AS shipments
      FROM moves
      WHERE d >= $2::date AND d < $3::date
      GROUP BY 1 ORDER BY 1`

    const [rowsRes, totalsRes] = await Promise.all([
      pool.query(sql, params),
      pool.query(monthlyTotalsSql, [orgId, toIso(firstBucket), toIso(addDays(today, 1))])
    ])

    const monthKeys: string[] = []
    for (let i = 0; i < months; i++) monthKeys.push(toIso(addMonths(firstBucket, i)).slice(0, 7))

    const byProduct: Record<string, any> = {}
    for (const r of (rowsRes.rows || [])) {
      const key = String(r.id)
      if (!byProduct[key]) {
        byProduct[key] = {
          id: r.id, name: r.name, value: r.value, sku: r.sku, upc: r.upc,
          isBom: r.isbom === 'Y' || r.isjtlbom === 'Y', isActive: r.isactive === 'Y', imageUrl: r.imageurl || null,
          category: r.category || '', qtyOnHand: num(r.qty_on_hand),
          months: Object.fromEntries(monthKeys.map((k) => [k, { qtySold: 0, qtyReturned: 0, revenue: 0 }])),
          qtySold: 0, qtyReturned: 0, revenue: 0
        }
      }
      const mk = toIso(new Date(r.month)).slice(0, 7)
      const cell = byProduct[key].months[mk]
      if (cell) {
        cell.qtySold += num(r.qty_sold); cell.qtyReturned += num(r.qty_returned); cell.revenue += num(r.revenue)
      }
      byProduct[key].qtySold += num(r.qty_sold)
      byProduct[key].qtyReturned += num(r.qty_returned)
      byProduct[key].revenue += num(r.revenue)
    }
    const products = Object.values(byProduct).map((p: any) => {
      const series = monthKeys.map((k) => p.months[k].qtySold)
      const last3 = series.slice(-3).reduce((a, b) => a + b, 0)
      const prev3 = series.slice(-6, -3).reduce((a, b) => a + b, 0)
      return {
        ...p,
        revenue: Math.round(p.revenue * 100) / 100,
        returnRate: p.qtySold > 0 ? Math.round((p.qtyReturned / p.qtySold) * 1000) / 10 : (p.qtyReturned > 0 ? 100 : 0),
        avgPerMonth: Math.round((p.qtySold / months) * 10) / 10,
        trend: prev3 > 0 ? Math.round(((last3 - prev3) / prev3) * 1000) / 10 : (last3 > 0 ? 100 : 0),
        last3, prev3,
        coverageMonths: p.qtySold > 0 ? Math.round((p.qtyOnHand / (p.qtySold / months)) * 10) / 10 : null
      }
    }).sort((a: any, b: any) => b.qtySold - a.qtySold || a.name.localeCompare(b.name))

    const totalsByMonth = Object.fromEntries(monthKeys.map((k) => [k, { qtySold: 0, qtyReturned: 0, revenue: 0, shipments: 0 }]))
    for (const r of (totalsRes.rows || [])) {
      const mk = toIso(new Date(r.month)).slice(0, 7)
      if (totalsByMonth[mk]) totalsByMonth[mk] = { qtySold: num(r.qty_sold), qtyReturned: num(r.qty_returned), revenue: Math.round(num(r.revenue) * 100) / 100, shipments: num(r.shipments) }
    }

    return {
      mode, orgId, months, monthKeys,
      from: toIso(firstBucket), to: toIso(today),
      scope: productIds.length ? 'manual' : 'top',
      limit,
      totalsByMonth,
      products
    }
  } catch (err: any) {
    console.error('[bi/product-performance] 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) {
    if (err?.statusCode === 400 || err?.statusCode === 500) throw err
    try {
      await refreshTokenHelper(event)
      return await handleFunc(event)
    } catch (error: any) {
      console.error('[bi/product-performance] fatal:', error)
      throw createError({ statusCode: 500, message: error?.message || 'Product performance failed' })
    }
  }
})