import { Router, Request, Response } from "express";
import { pool } from "../db/client";

export const stockCompetitorRouter = Router();

// GET /api/stock/competitor-by-tech
// Returns publicly-available competitor stock levels aggregated by technology (form_factor+speed_gbps).
// Used by Warehouse Stock Intelligence to benchmark Flexoptix demand against competitor availability.
stockCompetitorRouter.get("/competitor-by-tech", async (_req: Request, res: Response) => {
  try {
    const result = await pool.query(`
      SELECT
        t.form_factor,
        t.speed_gbps::numeric                                         AS speed_gbps,
        sv.name                                                       AS vendor_name,
        COUNT(DISTINCT so.transceiver_id)::int                       AS skus,
        COALESCE(SUM(so.warehouse_de_qty),0)::int                    AS de_stock,
        COALESCE(SUM(so.warehouse_global_qty),0)::int                AS global_stock,
        COALESCE(SUM(so.quantity_available),0)::int                  AS qty_available,
        MAX(so.time)::date                                            AS last_seen
      FROM stock_observations so
      JOIN transceivers  t  ON t.id  = so.transceiver_id
      JOIN vendors       sv ON sv.id = so.source_vendor_id
      WHERE so.time > NOW() - INTERVAL '14 days'
        AND sv.name IN ('FS.COM','ATGBICS','FiberMall','NADDOD','Prolabs','Blue Optics','Skylane Optics')
      GROUP BY t.form_factor, t.speed_gbps, sv.name
      ORDER BY sv.name, global_stock DESC NULLS LAST
    `);

    // Pivot: { "1G SFP": { "FS.COM": { de:..., global:... }, ... }, ... }
    const byTech: Record<string, Record<string, { de: number; global: number; skus: number; last_seen: string }>> = {};
    const vendors = new Set<string>();

    for (const row of result.rows) {
      const n = parseFloat(row.speed_gbps);
      const spd = n >= 1000
        ? ((n / 1000 * 10) % 10 === 0 ? Math.round(n / 1000).toString() : (n / 1000).toFixed(1)) + "T"
        : ((n * 10) % 10 === 0 ? Math.round(n).toString() : String(n)) + "G";
      const key = spd + " " + (row.form_factor || "?");
      if (!byTech[key]) byTech[key] = {};
      byTech[key][row.vendor_name] = {
        de:       parseInt(row.de_stock)     || 0,
        global:   parseInt(row.global_stock) || parseInt(row.qty_available) || 0,
        skus:     parseInt(row.skus)         || 0,
        last_seen: row.last_seen,
      };
      vendors.add(row.vendor_name);
    }

    res.json({
      success: true,
      vendors: [...vendors].sort(),
      by_tech: byTech,
    });
  } catch (err) {
    res.status(500).json({ success: false, error: String(err) });
  }
});
