/**
 * RFQ Analyzer — POST /api/rfq/analyze
 *
 * Paste a vendor quote (list of part numbers + quantities + prices) and
 * get back: current market rates, cheapest alternative via equivalences,
 * total savings opportunity, and per-line delta.
 *
 * Request body:
 *   {
 *     lines: [
 *       { part_number: string, quantity: number, unit_price: number, currency?: string }
 *     ],
 *     currency?: "USD" | "EUR"
 *   }
 */
import { Router, Request, Response } from "express";
import { pool } from "../db/client";
import { sendCSV } from "../utils/csv";

export const rfqRouter = Router();

interface RfqLine {
  part_number: string;
  quantity: number;
  unit_price: number;
  currency?: string;
}

// POST /api/rfq/analyze
rfqRouter.post("/analyze", async (req: Request, res: Response) => {
  const { lines, currency: preferredCurrency = "USD" } = req.body as {
    lines: RfqLine[];
    currency?: string;
  };

  if (!Array.isArray(lines) || lines.length === 0) {
    return res.status(400).json({ success: false, error: "lines[] required" });
  }
  if (lines.length > 200) {
    return res.status(400).json({ success: false, error: "Max 200 lines per RFQ" });
  }

  try {
    const results = await Promise.all(
      lines.map(async (line) => {
        const pn = String(line.part_number || "").trim();
        const qty = Math.max(1, Number(line.quantity) || 1);
        const quotedPrice = Number(line.unit_price) || 0;

        if (!pn) {
          return { part_number: pn, error: "Empty part_number", resolved: false };
        }

        // 1. Resolve transceiver by part_number or standard_name
        const txResult = await pool.query(
          `SELECT id, part_number, standard_name, form_factor, speed_gbps, speed
           FROM transceivers
           WHERE part_number ILIKE $1 OR standard_name ILIKE $1
           LIMIT 1`,
          [pn]
        );

        if (txResult.rows.length === 0) {
          return { part_number: pn, quantity: qty, quoted_unit_price: quotedPrice, resolved: false, error: "Not found in catalog" };
        }

        const tx = txResult.rows[0];

        // 2. Get current market prices for this transceiver
        const marketResult = await pool.query(
          `SELECT
             v.name AS vendor_name, v.website,
             po.price, po.currency, po.stock_level, po.url, po.time AS observed_at
           FROM (
             SELECT DISTINCT ON (source_vendor_id)
               source_vendor_id, price, currency, stock_level, url, time
             FROM price_observations
             WHERE transceiver_id = $1
               AND price > 0
               AND COALESCE(is_anomalous, false) = false
             ORDER BY source_vendor_id, time DESC
           ) po
           JOIN vendors v ON v.id = po.source_vendor_id
           WHERE po.currency = $2 OR po.currency IS NULL
           ORDER BY po.price ASC
           LIMIT 10`,
          [tx.id, preferredCurrency]
        );

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

        // 3. Find cheapest equivalent via transceiver_equivalences
        const equivResult = await pool.query(
          `SELECT
             te.competitor_id, t2.part_number AS equiv_part_number,
             t2.standard_name AS equiv_standard_name,
             te.confidence, te.match_basis,
             (
               SELECT po2.price FROM price_observations po2
               WHERE po2.transceiver_id = te.competitor_id
                 AND po2.currency = $2
                 AND po2.price > 0
                 AND COALESCE(po2.is_anomalous, false) = false
               ORDER BY po2.time DESC LIMIT 1
             ) AS equiv_price,
             (
               SELECT v2.name FROM price_observations po2
               JOIN vendors v2 ON v2.id = po2.source_vendor_id
               WHERE po2.transceiver_id = te.competitor_id
                 AND po2.currency = $2 AND po2.price > 0
               ORDER BY po2.price ASC, po2.time DESC LIMIT 1
             ) AS equiv_cheapest_vendor
           FROM transceiver_equivalences te
           JOIN transceivers t2 ON t2.id = te.competitor_id
           WHERE (te.flexoptix_id = $1 OR te.competitor_id = $1)
             AND te.status = 'approved'
             AND te.confidence >= 0.7
           ORDER BY te.confidence DESC
           LIMIT 5`,
          [tx.id, preferredCurrency]
        );

        const equivalents = equivResult.rows
          .filter(e => e.equiv_price !== null)
          .map(e => ({
            part_number: e.equiv_part_number,
            standard_name: e.equiv_standard_name,
            confidence: parseFloat(e.confidence),
            match_basis: e.match_basis,
            unit_price: parseFloat(e.equiv_price),
            vendor: e.equiv_cheapest_vendor,
          }));

        const cheapestEquiv = equivalents.sort((a, b) => a.unit_price - b.unit_price)[0] || null;

        // 4. Calculate savings
        const savingsVsMarketMin = marketMin !== null && quotedPrice > 0
          ? Math.round((quotedPrice - marketMin) * qty * 100) / 100
          : null;
        const savingsVsEquiv = cheapestEquiv && quotedPrice > 0
          ? Math.round((quotedPrice - cheapestEquiv.unit_price) * qty * 100) / 100
          : null;

        return {
          part_number: pn,
          resolved: true,
          transceiver: {
            id: tx.id,
            standard_name: tx.standard_name,
            form_factor: tx.form_factor,
            speed: tx.speed,
          },
          quantity: qty,
          quoted_unit_price: quotedPrice,
          quoted_total: Math.round(quotedPrice * qty * 100) / 100,
          market: {
            min_price: marketMin,
            avg_price: marketAvg,
            vendor_count: prices.length,
            cheapest_vendor: cheapestVendor ? {
              name: cheapestVendor.vendor_name,
              price: parseFloat(cheapestVendor.price),
              stock_level: cheapestVendor.stock_level,
              url: cheapestVendor.url,
            } : null,
          },
          equivalents,
          cheapest_equivalent: cheapestEquiv,
          savings: {
            vs_market_min: savingsVsMarketMin,
            vs_equiv: savingsVsEquiv,
            best_saving: Math.max(savingsVsMarketMin || 0, savingsVsEquiv || 0) || null,
          },
          currency: preferredCurrency,
        };
      })
    );

    // Aggregate totals
    const resolved = results.filter(r => r.resolved);
    const totalQuoted = resolved.reduce((s, r) => s + (r.quoted_total as number || 0), 0);
    const totalSavingsMarket = resolved.reduce((s, r) => {
      const sv = (r.savings as any)?.vs_market_min;
      return s + (sv && sv > 0 ? sv : 0);
    }, 0);
    const totalSavingsEquiv = resolved.reduce((s, r) => {
      const sv = (r.savings as any)?.vs_equiv;
      return s + (sv && sv > 0 ? sv : 0);
    }, 0);

    res.json({
      success: true,
      currency: preferredCurrency,
      line_count: lines.length,
      resolved_count: resolved.length,
      lines: results,
      totals: {
        quoted: Math.round(totalQuoted * 100) / 100,
        potential_savings_vs_market: Math.round(totalSavingsMarket * 100) / 100,
        potential_savings_vs_equiv:  Math.round(totalSavingsEquiv  * 100) / 100,
        best_total_saving: Math.round(Math.max(totalSavingsMarket, totalSavingsEquiv) * 100) / 100,
      },
    });
  } catch (err) {
    res.status(500).json({ success: false, error: String(err) });
  }
});

// GET /api/rfq/export — Export last RFQ result as CSV (pass lines as query params)
rfqRouter.get("/export", async (req: Request, res: Response) => {
  res.status(405).json({ error: "Use POST /api/rfq/analyze with ?format=csv to export" });
});
