/**
 * Vendor Reliability Scores — Redesigned (v2)
 *
 * Scoring methodology (100 pts total):
 *   30 pts — Data Freshness:   How recently were prices scraped?
 *   25 pts — SKU Coverage:     How many unique transceivers covered in 60d?
 *   25 pts — Price Consistency: Price anomaly rate (low anomalies = reliable)
 *   20 pts — Stock Accuracy:   Does vendor report stock status? How often in_stock?
 *
 * Routes:
 *   GET /api/vendor-reliability         — All vendor scores
 *   GET /api/vendor-reliability/:id     — Single vendor detail + breakdown
 */
import { Router, Request, Response } from "express";
import { pool } from "../db/client";

export const vendorReliabilityRouter = Router();

async function computeReliability() {
  const result = await pool.query(`
    WITH
    -- Freshness + volume (30 pts)
    freshness AS (
      SELECT
        po.source_vendor_id                                             AS vendor_id,
        MAX(po.time)                                                    AS last_seen,
        EXTRACT(EPOCH FROM (NOW() - MAX(po.time))) / 86400.0           AS days_since_last,
        COUNT(*) FILTER (WHERE po.time >= NOW() - INTERVAL '30 days')  AS obs_30d,
        COUNT(*) FILTER (WHERE po.time >= NOW() - INTERVAL '7 days')   AS obs_7d
      FROM price_observations po
      WHERE po.time >= NOW() - INTERVAL '90 days'
      GROUP BY po.source_vendor_id
    ),
    -- SKU coverage breadth (25 pts)
    coverage AS (
      SELECT
        po.source_vendor_id                                             AS vendor_id,
        COUNT(DISTINCT po.transceiver_id)                              AS skus_60d,
        COUNT(DISTINCT po.transceiver_id)
          FILTER (WHERE po.time >= NOW() - INTERVAL '7 days')          AS skus_7d
      FROM price_observations po
      WHERE po.time >= NOW() - INTERVAL '60 days'
        AND po.price > 0
      GROUP BY po.source_vendor_id
    ),
    -- Price consistency: anomaly rate (25 pts)
    consistency AS (
      SELECT
        po.source_vendor_id                                             AS vendor_id,
        COUNT(*) FILTER (WHERE po.time >= NOW() - INTERVAL '30 days')  AS total_30d,
        COUNT(*) FILTER (
          WHERE po.time >= NOW() - INTERVAL '30 days'
            AND COALESCE(po.is_anomalous, false) = true
        )                                                               AS anomalies_30d,
        -- Price variance: std dev / mean (coefficient of variation, lower = more stable)
        CASE WHEN AVG(po.price) > 0 THEN
          ROUND((STDDEV(po.price) / AVG(po.price) * 100)::numeric, 1)
        END                                                             AS price_cv_pct
      FROM price_observations po
      WHERE po.time >= NOW() - INTERVAL '30 days'
        AND po.price > 0
      GROUP BY po.source_vendor_id
    ),
    -- Stock accuracy: does vendor report stock? reliability of in_stock flag (20 pts)
    stock_acc AS (
      SELECT
        so.source_vendor_id                                             AS vendor_id,
        COUNT(*)::int                                                   AS stock_obs,
        COUNT(*) FILTER (WHERE so.in_stock = true)::int                AS in_stock_count,
        COUNT(*) FILTER (WHERE so.in_stock = false)::int               AS out_of_stock_count
      FROM stock_observations so
      WHERE so.time >= NOW() - INTERVAL '30 days'
      GROUP BY so.source_vendor_id
    )
    SELECT
      v.id::text       AS vendor_id,
      v.name           AS vendor_name,
      v.type,
      v.website,
      -- Raw metrics
      f.last_seen,
      f.days_since_last,
      f.obs_30d,
      f.obs_7d,
      c.skus_60d,
      c.skus_7d,
      co.total_30d,
      co.anomalies_30d,
      co.price_cv_pct,
      s.stock_obs,
      s.in_stock_count,
      s.out_of_stock_count
    FROM freshness f
    JOIN vendors v    ON v.id = f.vendor_id
    LEFT JOIN coverage    c  ON c.vendor_id  = f.vendor_id
    LEFT JOIN consistency co ON co.vendor_id = f.vendor_id
    LEFT JOIN stock_acc   s  ON s.vendor_id  = f.vendor_id
    ORDER BY f.last_seen DESC
  `);

  return result.rows.map(row => {
    // ── Freshness score (30 pts) ──────────────────────────────────────────
    const days = parseFloat(row.days_since_last || "999");
    const freshnessScore =
      days <= 1  ? 30 :
      days <= 3  ? 27 :
      days <= 7  ? 22 :
      days <= 14 ? 15 :
      days <= 30 ? 8  : 0;

    // ── Coverage score (25 pts) ───────────────────────────────────────────
    const skus60d = parseInt(row.skus_60d || "0");
    const MAX_SKUS = 1000;
    const coverageScore = Math.min(Math.round((skus60d / MAX_SKUS) * 25), 25);

    // ── Consistency score (25 pts) — low anomaly rate = good ─────────────
    const total30d = parseInt(row.total_30d || "0");
    const anomalies30d = parseInt(row.anomalies_30d || "0");
    const anomalyRate = total30d > 0 ? anomalies30d / total30d : 0;
    const consistencyScore =
      total30d === 0 ? 0 :
      anomalyRate <= 0.01 ? 25 :
      anomalyRate <= 0.03 ? 20 :
      anomalyRate <= 0.05 ? 15 :
      anomalyRate <= 0.10 ? 10 : 5;

    // ── Stock accuracy score (20 pts) ─────────────────────────────────────
    const stockObs = parseInt(row.stock_obs || "0");
    const stockScore = Math.min(Math.round((stockObs / 100) * 20), 20);

    const totalScore = freshnessScore + coverageScore + consistencyScore + stockScore;
    const grade =
      totalScore >= 85 ? "A" :
      totalScore >= 70 ? "B" :
      totalScore >= 50 ? "C" :
      totalScore >= 30 ? "D" : "F";

    return {
      vendor_id:           row.vendor_id,
      vendor_name:         row.vendor_name,
      type:                row.type,
      website:             row.website,
      reliability_score:   totalScore,
      grade,
      breakdown: {
        freshness:    { score: freshnessScore,    max: 30, days_since_last: Math.round(days * 10) / 10 },
        coverage:     { score: coverageScore,     max: 25, skus_60d: skus60d, skus_7d: parseInt(row.skus_7d || "0") },
        consistency:  { score: consistencyScore,  max: 25, anomaly_rate_pct: Math.round(anomalyRate * 1000) / 10, obs_30d: total30d },
        stock_accuracy: { score: stockScore,      max: 20, stock_obs_30d: stockObs, in_stock: parseInt(row.in_stock_count || "0") },
      },
      last_seen:     row.last_seen,
      obs_30d:       parseInt(row.obs_30d || "0"),
    };
  });
}

// ── GET /api/vendor-reliability ────────────────────────────────────────────
vendorReliabilityRouter.get("/", async (_req: Request, res: Response) => {
  try {
    const vendors = await computeReliability();
    vendors.sort((a, b) => b.reliability_score - a.reliability_score);
    res.json({ success: true, vendors, scored_at: new Date().toISOString() });
  } catch (err) {
    res.status(500).json({ success: false, error: String(err) });
  }
});

// ── GET /api/vendor-reliability/:id — Single vendor deep-dive ──────────────
vendorReliabilityRouter.get("/:id", async (req: Request, res: Response) => {
  try {
    const all = await computeReliability();
    const vendor = all.find(v => v.vendor_id === req.params.id);
    if (!vendor) return res.status(404).json({ success: false, error: "Vendor not found" });

    // Price history for sparkline
    const priceHistory = await pool.query(`
      SELECT DATE_TRUNC('week', time)::date AS week,
             ROUND(AVG(price)::numeric, 2)  AS avg_price,
             COUNT(*)::int                  AS observations
      FROM price_observations
      WHERE source_vendor_id = $1::uuid
        AND time >= NOW() - INTERVAL '90 days'
        AND price > 0
      GROUP BY DATE_TRUNC('week', time)
      ORDER BY week ASC
    `, [req.params.id]).catch(() => ({ rows: [] }));

    res.json({ success: true, vendor, price_history: priceHistory.rows });
  } catch (err) {
    res.status(500).json({ success: false, error: String(err) });
  }
});
