/**
 * ROI Calculator — /api/roi
 *
 * Calculates total cost of ownership and switching savings for transceiver decisions.
 *
 * POST /api/roi/calculate
 *   Input:
 *     {
 *       ports: number,              — Number of ports to equip
 *       current_price_per_port: number,
 *       target_form_factor?: string,
 *       target_speed_gbps?: number,
 *       years?: number,             — TCO horizon (default 3)
 *       switch_cost?: number,       — One-time switching cost (labor, downtime est.)
 *       currency?: string
 *     }
 *   Output:
 *     - Current total cost (ports × price)
 *     - Market min/avg for target spec
 *     - 1/2/3 year TCO at current vs market-min price
 *     - Savings over TCO horizon
 *     - Break-even months
 *     - Top 5 cheapest vendors for the target spec
 */
import { Router, Request, Response } from "express";
import { pool } from "../db/client";

export const roiRouter = Router();

roiRouter.post("/calculate", async (req: Request, res: Response) => {
  const {
    ports,
    current_price_per_port,
    target_form_factor,
    target_speed_gbps,
    years = 3,
    switch_cost = 0,
    currency = "USD",
  } = req.body as Record<string, any>;

  if (!ports || isNaN(parseInt(ports)) || parseInt(ports) <= 0) {
    return res.status(400).json({ success: false, error: "ports must be a positive integer" });
  }
  if (!current_price_per_port || isNaN(parseFloat(current_price_per_port))) {
    return res.status(400).json({ success: false, error: "current_price_per_port required" });
  }
  if (!target_form_factor && !target_speed_gbps) {
    return res.status(400).json({ success: false, error: "Provide target_form_factor and/or target_speed_gbps" });
  }

  const portCount     = parseInt(ports);
  const currentPrice  = parseFloat(current_price_per_port);
  const switchingCost = parseFloat(switch_cost) || 0;
  const tcoYears      = Math.min(Math.max(parseInt(years) || 3, 1), 10);
  const curr          = String(currency).toUpperCase();

  try {
    // Find market prices for target spec
    const marketResult = await pool.query(`
      WITH latest AS (
        SELECT DISTINCT ON (po.transceiver_id, po.source_vendor_id)
          po.transceiver_id, po.source_vendor_id, po.price, po.currency, po.stock_level, po.url, po.time
        FROM price_observations po
        WHERE po.price > 0
          AND COALESCE(po.is_anomalous, false) = false
          AND po.currency = $1
        ORDER BY po.transceiver_id, po.source_vendor_id, po.time DESC
      )
      SELECT
        v.name                                  AS vendor_name,
        v.website,
        t.standard_name,
        t.form_factor,
        t.speed_gbps::text,
        ROUND(MIN(l.price)::numeric, 2)         AS min_price,
        ROUND(AVG(l.price)::numeric, 2)         AS avg_price,
        COUNT(DISTINCT l.source_vendor_id)::int AS vendor_count,
        l.price                                 AS vendor_price,
        l.stock_level,
        l.url
      FROM latest l
      JOIN transceivers t ON t.id = l.transceiver_id
      JOIN vendors v      ON v.id = l.source_vendor_id
      WHERE ($2::text IS NULL OR t.form_factor = $2)
        AND ($3::numeric IS NULL OR t.speed_gbps = $3)
      GROUP BY v.name, v.website, t.standard_name, t.form_factor, t.speed_gbps,
               l.price, l.stock_level, l.url
      ORDER BY l.price ASC
      LIMIT 50
    `, [curr, target_form_factor || null, target_speed_gbps ? parseFloat(target_speed_gbps) : null]);

    const allPrices = marketResult.rows.map(r => parseFloat(r.vendor_price)).filter(p => p > 0);
    const marketMin = allPrices.length ? Math.min(...allPrices) : null;
    const marketAvg = allPrices.length ? Math.round(allPrices.reduce((a,b) => a+b) / allPrices.length * 100) / 100 : null;

    // Top 5 cheapest vendors (distinct vendors)
    const vendorPrices = new Map<string, { vendor_name: string; price: number; stock_level: string; url: string; standard_name: string }>();
    for (const r of marketResult.rows) {
      if (!vendorPrices.has(r.vendor_name)) {
        vendorPrices.set(r.vendor_name, {
          vendor_name: r.vendor_name,
          price: parseFloat(r.vendor_price),
          stock_level: r.stock_level,
          url: r.url,
          standard_name: r.standard_name,
        });
      }
    }
    const top5Vendors = [...vendorPrices.values()].slice(0, 5);

    // TCO calculations
    const currentTotal   = Math.round(portCount * currentPrice * 100) / 100;
    const marketMinTotal = marketMin !== null ? Math.round(portCount * marketMin * 100) / 100 : null;
    const marketAvgTotal = marketAvg !== null ? Math.round(portCount * marketAvg * 100) / 100 : null;

    // Annual OpEx: assume 15% of hardware cost for maintenance/replacement (industry standard)
    const annualOpExFactor = 0.15;
    const currentTCO  = Math.round((currentTotal + (currentTotal * annualOpExFactor * tcoYears)) * 100) / 100;
    const targetTCOMin = marketMinTotal !== null
      ? Math.round((marketMinTotal + switchingCost + (marketMinTotal * annualOpExFactor * tcoYears)) * 100) / 100
      : null;
    const targetTCOAvg = marketAvgTotal !== null
      ? Math.round((marketAvgTotal + switchingCost + (marketAvgTotal * annualOpExFactor * tcoYears)) * 100) / 100
      : null;

    const savingsMin = targetTCOMin !== null ? Math.round((currentTCO - targetTCOMin) * 100) / 100 : null;
    const savingsAvg = targetTCOAvg !== null ? Math.round((currentTCO - targetTCOAvg) * 100) / 100 : null;

    // Break-even: months until switching cost is recovered from per-unit savings
    const monthlyHardwareSavingsMin = marketMin !== null
      ? Math.round(portCount * (currentPrice - marketMin) / 12 * 100) / 100
      : null;
    const breakEvenMonths = monthlyHardwareSavingsMin && monthlyHardwareSavingsMin > 0 && switchingCost > 0
      ? Math.ceil(switchingCost / monthlyHardwareSavingsMin)
      : 0;

    return res.json({
      success: true,
      input: {
        ports: portCount, current_price_per_port: currentPrice,
        target_form_factor: target_form_factor || null,
        target_speed_gbps: target_speed_gbps ? parseFloat(target_speed_gbps) : null,
        tco_years: tcoYears, switch_cost: switchingCost, currency: curr,
      },
      current: {
        total_hardware:  currentTotal,
        tco_estimate:    currentTCO,
        price_per_port:  currentPrice,
      },
      market: {
        min_price:      marketMin,
        avg_price:      marketAvg,
        vendor_count:   allPrices.length,
        min_total_hardware: marketMinTotal,
        avg_total_hardware: marketAvgTotal,
      },
      tco_comparison: {
        current:        currentTCO,
        target_min:     targetTCOMin,
        target_avg:     targetTCOAvg,
        savings_vs_min: savingsMin,
        savings_vs_avg: savingsAvg,
        savings_pct_min: savingsMin && currentTCO > 0
          ? Math.round(savingsMin / currentTCO * 1000) / 10
          : null,
      },
      switching: {
        cost:             switchingCost,
        break_even_months: breakEvenMonths || null,
        monthly_savings:   monthlyHardwareSavingsMin,
      },
      top_vendors: top5Vendors,
      currency: curr,
    });
  } catch (err) {
    return res.status(500).json({ success: false, error: String(err) });
  }
});
