import { Pool } from 'pg'
import { analyzeDimensions } from '../../../../app/utils/productDimensions'

/**
 * Data-quality check for the volume-billed merchants (c_bpartner.isAccountingWarehouseVolume = 'Y'):
 * products WITH stock whose dimensions are missing or implausible (cm / m / mixed units).
 * These products are billed with 0 m³ (missing) or a wrong m³ by the warehouse-rent cron
 * (../logship-scripts … rent_volume/calculate_volume.py — dimensions are always mm).
 * Direct Postgres like server/api/products/missing-dimensions.get.ts (needs the PG tunnel on dev).
 */
const handleFunc = async () => {
  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 (PG_HOST, PG_DATABASE, PG_USER, PG_PASSWORD)' })
  }

  const pool = new Pool(dbConfig)
  try {
    const sql = `
      WITH onhand AS (
        SELECT l.m_warehouse_id, s.m_product_id, SUM(s.qtyonhand) AS qty
        FROM m_storage s
        JOIN m_locator l ON l.m_locator_id = s.m_locator_id
        GROUP BY l.m_warehouse_id, s.m_product_id
        HAVING SUM(s.qtyonhand) > 0
      )
      SELECT p.m_product_id AS id, p.value AS sku, p.name, p.length, p.width, p.height,
             oh.qty, o.ad_org_id AS "orgId", o.name AS "orgName", bp.volumeqm3price AS "ratePerM3"
      FROM onhand oh
      JOIN m_product p ON p.m_product_id = oh.m_product_id
      JOIN ad_org o ON o.ad_org_id = p.ad_org_id
      JOIN c_bpartner bp ON bp.c_bpartner_id = o.c_bpartner_id
      WHERE p.isactive = 'Y'
        AND bp.isaccountingwarehousevolume = 'Y'
        AND bp.accoutingwarehouserentstarts IS NOT NULL
        AND COALESCE(bp.volumeqm3price, 0) > 0
      ORDER BY o.name, oh.qty DESC
    `
    const result = await pool.query(sql)
    const records = result.rows
      .map((r: any) => {
        const check = analyzeDimensions(r)
        // a stocked product with nothing entered is "missing" here (form-level 'empty' = not billed)
        const status = check.status === 'empty' ? 'missing' : check.status
        return {
          ...r,
          qty: Number(r.qty),
          status,
          message: status === 'missing' ? 'No dimensions → stored with 0 m³, not billed for storage.' : check.message,
          volumeM3: check.volumeM3,
          suggestion: check.suggestion,
        }
      })
      .filter((r: any) => r.status !== 'ok')
    return { records, checkedProducts: result.rowCount }
  } catch (err: any) {
    console.error('dimension-check query error:', err)
    throw createError({ statusCode: 500, message: `Database error: ${err.message}` })
  } finally {
    await pool.end()
  }
}

export default defineEventHandler(async () => handleFunc())