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

export const priceHistoryRouter = Router();

// GET /api/price-history/:transceiverIdOrSlug?days=90&vendor=all
// Returns time-bucketed price history for a transceiver (for charts)
priceHistoryRouter.get("/:id", async (req: Request, res: Response) => {
  const { id } = req.params;
  const days = Math.min(parseInt((req.query.days as string) || "90"), 365);
  const vendorFilter = req.query.vendor as string | undefined;

  try {
    // Resolve slug or UUID to transceiver
    const tx = await pool.query(
      `SELECT t.id, t.part_number, t.standard_name, v.name as vendor_name
       FROM transceivers t LEFT JOIN vendors v ON v.id = t.vendor_id
       WHERE t.id::text = $1 OR t.slug = $1 LIMIT 1`,
      [id]
    );
    if (!tx.rows[0]) {
      res.status(404).json({ success: false, error: "Transceiver not found" });
      return;
    }
    const txId = tx.rows[0].id;

    // Daily min/max/avg price per source vendor — bucket by day
    const conditions = [`po.transceiver_id = $1`, `po.time >= NOW() - INTERVAL '${days} days'`, `po.price > 0`, `po.is_anomalous IS NOT TRUE`];
    const values: unknown[] = [txId];
    let idx = 2;

    if (vendorFilter && vendorFilter !== "all") {
      conditions.push(`sv.name ILIKE $${idx}`);
      values.push(`%${vendorFilter}%`);
      idx++;
    }

    const where = `WHERE ${conditions.join(" AND ")}`;

    const seriesQuery = `
      SELECT
        DATE_TRUNC('day', po.time) AS day,
        sv.name AS source_vendor,
        sv.id AS source_vendor_id,
        MIN(po.price) AS price_min,
        MAX(po.price) AS price_max,
        AVG(po.price)::numeric(12,2) AS price_avg,
        po.currency,
        COUNT(*) AS observations
      FROM price_observations po
      LEFT JOIN vendors sv ON sv.id = po.source_vendor_id
      ${where}
      GROUP BY DATE_TRUNC('day', po.time), sv.name, sv.id, po.currency
      ORDER BY day ASC, source_vendor
    `;

    // Current best price (lowest verified non-anomalous)
    const currentQuery = `
      SELECT
        sv.name AS source_vendor,
        MIN(po.price) AS best_price,
        po.currency,
        MAX(po.time) AS last_seen
      FROM price_observations po
      LEFT JOIN vendors sv ON sv.id = po.source_vendor_id
      WHERE po.transceiver_id = $1
        AND po.time >= NOW() - INTERVAL '7 days'
        AND po.price > 0
        AND po.is_anomalous IS NOT TRUE
      GROUP BY sv.name, po.currency
      ORDER BY best_price ASC
      LIMIT 10
    `;

    const [series, current] = await Promise.all([
      pool.query(seriesQuery, values),
      pool.query(currentQuery, [txId]),
    ]);

    res.json({
      success: true,
      transceiver: tx.rows[0],
      days,
      series: series.rows,
      current_prices: current.rows,
    });
  } catch (err) {
    res.status(500).json({ success: false, error: (err as Error).message });
  }
});
