/**
 * Price Matrix — Transceiver × Vendor Grid
 *
 * Routes:
 *   GET /api/price-matrix   — Price matrix for selected (or top) transceivers
 *   Query params:
 *     ids   — comma-separated transceiver IDs (max 20); omit for auto top-10
 *     limit — how many top transceivers to return when ids is omitted (default 10)
 */
import { Router, Request, Response } from "express";
import { pool } from "../db/client";

export const priceMatrixRouter = Router();

const MAX_IDS   = 20;
const MAX_LIMIT = 50;

// ─── GET /api/price-matrix ────────────────────────────────────────────────────
priceMatrixRouter.get("/", async (req: Request, res: Response) => {
  try {
    let transceiverIds: number[];

    if (typeof req.query.ids === "string" && req.query.ids.trim().length > 0) {
      const parsed = req.query.ids
        .split(",")
        .map((s) => parseInt(s.trim(), 10))
        .filter((n) => Number.isFinite(n) && n > 0)
        .slice(0, MAX_IDS);

      if (parsed.length === 0) {
        res.status(400).json({ success: false, error: "No valid transceiver IDs provided" });
        return;
      }
      transceiverIds = parsed;
    } else {
      // Auto-select top N by observation count in last 30 days
      const rawLimit = typeof req.query.limit === "string" ? parseInt(req.query.limit, 10) : 10;
      const topLimit = Number.isFinite(rawLimit) && rawLimit > 0 ? Math.min(rawLimit, MAX_LIMIT) : 10;

      const topResult = await pool.query<{ transceiver_id: number }>(
        `SELECT transceiver_id
         FROM price_observations
         WHERE time > NOW() - INTERVAL '30 days'
         GROUP BY transceiver_id
         ORDER BY COUNT(*) DESC
         LIMIT $1`,
        [topLimit]
      );

      transceiverIds = topResult.rows.map((r) => r.transceiver_id);

      if (transceiverIds.length === 0) {
        res.json({ success: true, transceivers: [], vendors: [], matrix: {}, best_prices: {} });
        return;
      }
    }

    const placeholders = transceiverIds.map((_, i) => `$${i + 1}`).join(", ");

    const [txResult, priceResult] = await Promise.all([
      pool.query<{
        id: number;
        model_name: string;
        part_number: string;
        form_factor: string;
        speed_gbps: number;
      }>(
        `SELECT id, COALESCE(standard_name, part_number, '') AS model_name, part_number, form_factor, speed_gbps
         FROM transceivers
         WHERE id IN (${placeholders})
         ORDER BY id`,
        transceiverIds
      ),

      pool.query<{
        transceiver_id: number;
        vendor_id: number;
        vendor_name: string;
        price: string;
      }>(
        `SELECT DISTINCT ON (po.transceiver_id, po.source_vendor_id)
           po.transceiver_id,
           po.source_vendor_id  AS vendor_id,
           v.name               AS vendor_name,
           po.price
         FROM price_observations po
         JOIN vendors v ON v.id = po.source_vendor_id
         WHERE po.transceiver_id IN (${placeholders})
         ORDER BY po.transceiver_id, po.source_vendor_id, po.time DESC`,
        transceiverIds
      ),
    ]);

    // Build vendor list (deduplicated, stable order)
    const vendorMap = new Map<number, string>();
    for (const row of priceResult.rows) {
      if (!vendorMap.has(row.vendor_id)) {
        vendorMap.set(row.vendor_id, row.vendor_name);
      }
    }
    const vendors = Array.from(vendorMap.entries()).map(([vendor_id, vendor_name]) => ({
      vendor_id,
      vendor_name,
    }));

    // Build matrix and best_prices
    const matrix: Record<string, Record<string, number>> = {};
    const bestPrices: Record<string, number> = {};

    for (const row of priceResult.rows) {
      const txKey  = String(row.transceiver_id);
      const vKey   = String(row.vendor_id);
      const price  = parseFloat(row.price);

      if (!Number.isFinite(price)) continue;

      if (!matrix[txKey]) matrix[txKey] = {};
      matrix[txKey][vKey] = price;

      if (bestPrices[txKey] === undefined || price < bestPrices[txKey]) {
        bestPrices[txKey] = price;
      }
    }

    res.json({
      success:     true,
      transceivers: txResult.rows,
      vendors,
      matrix,
      best_prices: bestPrices,
    });
  } catch (err) {
    console.error("GET /api/price-matrix error:", err);
    res.status(500).json({ success: false, error: String(err) });
  }
});
