import { Router, Request, Response } from "express";
import { getDbStats } from "../db/queries";
import { pool } from "../db/client";
import { loadavg, totalmem, freemem, cpus } from "os";
import { execSync } from "child_process";

export const healthRouter = Router();

// GET /api/health — Health check with DB stats
healthRouter.get("/", async (_req: Request, res: Response) => {
  try {
    const start = Date.now();
    const stats = await getDbStats();
    const latencyMs = Date.now() - start;

    // Verification stats
    const verStats = await pool.query(`
      SELECT
        COUNT(*) FILTER (WHERE price_verified)  AS price_verified,
        COUNT(*) FILTER (WHERE price_status = 'public_price') AS price_public_price,
        COUNT(*) FILTER (WHERE price_status = 'no_public_price') AS price_no_public_price,
        COUNT(*) FILTER (WHERE price_status = 'ambiguous') AS price_ambiguous,
        COUNT(*) FILTER (WHERE price_status IN ('unknown', 'needs_research')) AS price_needs_research,
        COUNT(*) FILTER (WHERE image_verified)  AS image_verified,
        COUNT(*) FILTER (WHERE COALESCE(image_status, CASE WHEN image_verified THEN 'public_image' ELSE 'needs_research' END) = 'public_image') AS image_public_image,
        COUNT(*) FILTER (WHERE image_status = 'no_public_image') AS image_no_public_image,
        COUNT(*) FILTER (WHERE image_status = 'ambiguous') AS image_ambiguous,
        COUNT(*) FILTER (WHERE COALESCE(image_status, CASE WHEN image_verified THEN 'public_image' ELSE 'needs_research' END) IN ('unknown', 'needs_research')) AS image_needs_research,
        COUNT(*) FILTER (WHERE details_verified) AS details_verified,
        COUNT(*) FILTER (WHERE COALESCE(details_status, CASE WHEN details_verified THEN 'public_details' ELSE 'needs_research' END) = 'public_details') AS details_public_details,
        COUNT(*) FILTER (WHERE details_status = 'no_public_details') AS details_no_public_details,
        COUNT(*) FILTER (WHERE details_status = 'ambiguous') AS details_ambiguous,
        COUNT(*) FILTER (WHERE COALESCE(details_status, CASE WHEN details_verified THEN 'public_details' ELSE 'needs_research' END) IN ('unknown', 'needs_research')) AS details_needs_research,
        COUNT(*) FILTER (WHERE fully_verified)  AS fully_verified,
        COUNT(*) FILTER (WHERE competitor_status = 'matched') AS competitor_matched,
        COUNT(*) FILTER (WHERE competitor_status = 'no_valid_match') AS competitor_no_valid_match,
        COUNT(*) FILTER (WHERE competitor_status = 'ambiguous') AS competitor_ambiguous,
        COUNT(*) FILTER (WHERE competitor_status = 'needs_research') AS competitor_needs_research,
        COUNT(*) FILTER (
          WHERE price_status IN ('public_price', 'no_public_price', 'ambiguous')
            AND COALESCE(image_status, CASE WHEN image_verified THEN 'public_image' ELSE 'needs_research' END) IN ('public_image', 'no_public_image', 'ambiguous')
            AND COALESCE(details_status, CASE WHEN details_verified THEN 'public_details' ELSE 'needs_research' END) IN ('public_details', 'no_public_details', 'ambiguous')
            AND competitor_status IN ('matched', 'no_valid_match', 'ambiguous')
        ) AS research_resolved,
        COUNT(*)                                AS total
      FROM transceivers
      WHERE COALESCE(data_confidence, 'unknown') != 'garbage'
        AND COALESCE(product_page_url, '') NOT LIKE '%/category/%'
        AND COALESCE(category, '') NOT IN (
          'NonTransceiver',
          'Accessory',
          'Adapter / Converter',
          'Switch / Media Converter',
          'Switch / Network Infrastructure',
          'NIC / Adapter',
          'Mux / Passive Optical',
          'Product Family',
          'Loopback / Test Module'
        )
    `).catch(() => ({ rows: [{}] }));
    const v = verStats.rows[0] || {};

    // Stock observations stats
    const stockStats = await pool.query(`
      SELECT
        COUNT(*)                                                        AS total_observations,
        COUNT(DISTINCT transceiver_id)                                  AS transceivers_with_stock,
        COUNT(DISTINCT source_vendor_id)                                AS vendors_with_stock,
        SUM(warehouse_de_qty)     FILTER (WHERE warehouse_de_qty > 0)  AS total_de_qty,
        SUM(warehouse_global_qty) FILTER (WHERE warehouse_global_qty > 0) AS total_global_qty,
        MAX(time)                                                       AS last_observation_at
      FROM stock_observations
    `).catch(() => ({ rows: [{}] }));
    const s = stockStats.rows[0] || {};

    // System metrics
    const [load1, load5, load15] = loadavg();
    const totalMem = totalmem();
    const freeMem  = freemem();
    const usedMem  = totalMem - freeMem;
    const coreCount = cpus().length;

    let diskUsedPct: number | null = null;
    let diskFreeGb:  number | null = null;
    try {
      const df = execSync("df -h / 2>/dev/null | tail -1", { timeout: 2000 }).toString().trim();
      const parts = df.split(/\s+/);
      diskUsedPct = parseInt(parts[4] ?? "0", 10) || null;
      diskFreeGb  = parseFloat(parts[3] ?? "0") || null;
    } catch { /* skip on systems without df */ }

    const loadStatus = load1 > coreCount * 0.9 ? "overloaded" : load1 > coreCount * 0.6 ? "busy" : "ok";

    res.json({
      success: true,
      status: "healthy",
      version: "0.3.0",
      uptime: process.uptime(),
      system: {
        load: { "1m": +load1.toFixed(2), "5m": +load5.toFixed(2), "15m": +load15.toFixed(2) },
        load_status: loadStatus,
        cpu_cores: coreCount,
        memory: {
          total_mb:  Math.round(totalMem / 1024 / 1024),
          used_mb:   Math.round(usedMem  / 1024 / 1024),
          free_mb:   Math.round(freeMem  / 1024 / 1024),
          used_pct:  Math.round(usedMem / totalMem * 100),
        },
        disk: {
          used_pct: diskUsedPct,
          free_gb:  diskFreeGb,
        },
        process_rss_mb: Math.round(process.memoryUsage().rss / 1024 / 1024),
      },
      database: {
        connected: true,
        latency_ms: latencyMs,
        stats,
      },
      verification: {
        price_verified:      Number(v.price_verified   || 0),
        price_status: {
          public_price:    Number(v.price_public_price    || 0),
          no_public_price: Number(v.price_no_public_price || 0),
          ambiguous:       Number(v.price_ambiguous       || 0),
          needs_research:  Number(v.price_needs_research  || 0),
        },
        image_verified:      Number(v.image_verified   || 0),
        image_status: {
          public_image:    Number(v.image_public_image    || 0),
          no_public_image: Number(v.image_no_public_image || 0),
          ambiguous:       Number(v.image_ambiguous       || 0),
          needs_research:  Number(v.image_needs_research  || 0),
        },
        details_verified:    Number(v.details_verified || 0),
        details_status: {
          public_details:    Number(v.details_public_details    || 0),
          no_public_details: Number(v.details_no_public_details || 0),
          ambiguous:         Number(v.details_ambiguous         || 0),
          needs_research:    Number(v.details_needs_research    || 0),
        },
        fully_verified:      Number(v.fully_verified   || 0),
        competitor_status: {
          matched:        Number(v.competitor_matched        || 0),
          no_valid_match: Number(v.competitor_no_valid_match || 0),
          ambiguous:      Number(v.competitor_ambiguous      || 0),
          needs_research: Number(v.competitor_needs_research || 0),
        },
        research_resolved:   Number(v.research_resolved || 0),
        total:               Number(v.total            || 0),
        price_coverage_pct:  v.total ? Math.round(Number(v.price_verified) / Number(v.total) * 100) : 0,
        fully_verified_pct:  v.total ? Math.round(Number(v.fully_verified) / Number(v.total) * 100) : 0,
        research_resolved_pct: v.total ? Math.round(Number(v.research_resolved) / Number(v.total) * 100) : 0,
      },
      stock: {
        total_observations:      Number(s.total_observations      || 0),
        transceivers_with_stock: Number(s.transceivers_with_stock || 0),
        vendors_with_stock:      Number(s.vendors_with_stock      || 0),
        total_de_qty:            Number(s.total_de_qty            || 0),
        total_global_qty:        Number(s.total_global_qty        || 0),
        last_observation_at:     s.last_observation_at ?? null,
      },
    });
  } catch (err) {
    res.status(503).json({
      success: false,
      status: "unhealthy",
      database: { connected: false, error: String(err) },
    });
  }
});
