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

export const researchRobotRouter = Router();

// Known job queues are validated against pgboss.queue before any action.
async function isKnownQueue(name: string): Promise<boolean> {
  const r = await pool.query("SELECT 1 FROM pgboss.queue WHERE name = $1", [name]);
  return (r.rowCount ?? 0) > 0;
}

type Recommendation = {
  job: string;
  severity: "critical" | "warning" | "info";
  title: string;
  detail: string;
  cause: string;
  // Which actions the UI should offer for this job
  actions: Array<"dispatch" | "pause" | "resume" | "token_help">;
  failed_count?: number;
  last_error?: string;
};

// Classify a job error message into a human cause + recommended actions.
function classifyError(jobName: string, errorText: string): Omit<Recommendation, "job" | "failed_count" | "last_error"> {
  const e = (errorText || "").toLowerCase();

  if (/http 401|http 403|unauthorized|forbidden|invalid.*token|api.*key/.test(e)) {
    return {
      severity: "critical",
      title: `${jobName}: Authentifizierung fehlgeschlagen`,
      detail: "Der Job erreicht die Quelle, wird aber abgewiesen (401/403). Meist ein fehlender oder abgelaufener API-Token. Ohne gültigen Token reiht der Robot den Job endlos neu ein und er schlägt jedes Mal fehl.",
      cause: "auth",
      actions: ["token_help", "pause"],
    };
  }
  if (/timeout|etimedout|econnrefused|enotfound|network|socket hang up|fetch failed/.test(e)) {
    return {
      severity: "warning",
      title: `${jobName}: Quelle nicht erreichbar`,
      detail: "Netzwerk-/Verbindungsfehler beim Abruf. Oft temporär (Ziel-Server kurz down, Rate-Limit). Manuell erneut auslösen oder pausieren, falls die Quelle dauerhaft weg ist.",
      cause: "network",
      actions: ["dispatch", "pause"],
    };
  }
  if (/no handler|not registered|unknown job|kein handler/.test(e)) {
    return {
      severity: "critical",
      title: `${jobName}: Kein Handler registriert`,
      detail: "Der Job ist im Schedule, aber es gibt keinen Code-Handler dafür. Er kann nie laufen — entweder Handler nachrüsten oder den Job aus dem Schedule entfernen.",
      cause: "no_handler",
      actions: ["pause"],
    };
  }
  return {
    severity: "warning",
    title: `${jobName}: Wiederholt fehlgeschlagen`,
    detail: "Der Job schlägt regelmäßig fehl. Letzte Fehlermeldung siehe unten. Manuell auslösen zum erneuten Versuch, oder pausieren falls überflüssig.",
    cause: "other",
    actions: ["dispatch", "pause"],
  };
}

// GET /api/research-robot — letzte Robot-Laeufe + abgeleitete Handlungsempfehlungen
researchRobotRouter.get("/", async (_req: Request, res: Response) => {
  try {
    const r = await pool.query(
      "SELECT run_at, freshness, decision, dispatched, model FROM research_robot_runs ORDER BY run_at DESC LIMIT 20"
    );

    // Build recommendations from LIVE pgboss state (more accurate than the stored summary).
    const recommendations: Recommendation[] = [];
    try {
      const failing = await pool.query<{ name: string; failed: string; last_error: string | null }>(`
        SELECT j.name,
               COUNT(*) FILTER (WHERE j.state = 'failed') AS failed,
               (SELECT jj.output::text FROM pgboss.job jj
                  WHERE jj.name = j.name AND jj.state = 'failed' AND jj.output IS NOT NULL
                  ORDER BY jj.created_on DESC LIMIT 1) AS last_error
        FROM pgboss.job j
        WHERE j.created_on > NOW() - INTERVAL '3 days'
        GROUP BY j.name
        HAVING COUNT(*) FILTER (WHERE j.state = 'failed') >= 2
           AND COUNT(*) FILTER (WHERE j.state = 'completed') = 0
        ORDER BY failed DESC
        LIMIT 20
      `);

      // Which jobs are currently scheduled (so we can offer pause vs resume correctly)
      const scheduled = await pool.query<{ name: string }>("SELECT name FROM pgboss.schedule");
      const scheduledSet = new Set(scheduled.rows.map((s) => s.name));

      for (const row of failing.rows) {
        let errMsg = "";
        try {
          const parsed = JSON.parse(row.last_error || "{}") as { stack?: string; message?: string };
          errMsg = (parsed.stack || parsed.message || row.last_error || "").slice(0, 300);
        } catch {
          errMsg = (row.last_error || "").slice(0, 300);
        }
        const cls = classifyError(row.name, errMsg);
        // If the job isn't scheduled, swap pause→resume in the offered actions
        const actions = cls.actions.map((a) =>
          a === "pause" && !scheduledSet.has(row.name) ? "resume" : a
        ) as Recommendation["actions"];
        recommendations.push({
          job: row.name,
          severity: cls.severity,
          title: cls.title,
          detail: cls.detail,
          cause: cls.cause,
          actions: [...new Set(actions)],
          failed_count: parseInt(row.failed, 10),
          last_error: errMsg.split("\n")[0].slice(0, 160),
        });
      }
    } catch { /* pgboss introspection best-effort */ }

    if (recommendations.length === 0) {
      recommendations.push({
        job: "",
        severity: "info",
        title: "Alle Scraper-Jobs gesund",
        detail: "Keine dauerhaft fehlschlagenden Jobs erkannt. Keine Aktion nötig.",
        cause: "healthy",
        actions: [],
      });
    }

    res.json({ success: true, runs: r.rows, recommendations });
  } catch (e) {
    res.json({ success: true, runs: [], recommendations: [] });
  }
});

// POST /api/research-robot/action — { action, job }
// dispatch → Job sofort in die Queue stellen; pause → aus Schedule entfernen (Backup);
// resume → aus Backup wiederherstellen.
researchRobotRouter.post("/action", async (req: Request, res: Response) => {
  const action = String((req.body?.action ?? "")).trim();
  const job = String((req.body?.job ?? "")).trim();

  if (!["dispatch", "pause", "resume"].includes(action)) {
    res.status(400).json({ success: false, error: "Unbekannte Aktion." });
    return;
  }
  if (!job || !(await isKnownQueue(job))) {
    res.status(400).json({ success: false, error: "Unbekannter oder ungültiger Job." });
    return;
  }

  try {
    if (action === "dispatch") {
      // Enqueue one job run immediately (pg-boss v10 standard policy, mirrors a scheduled send)
      await pool.query(
        `INSERT INTO pgboss.job (id, name, data, priority, retry_limit, retry_delay, expire_in, keep_until, policy, start_after)
         VALUES (gen_random_uuid(), $1, '{}'::jsonb, 0, 2, 0, INTERVAL '15 minutes', NOW() + INTERVAL '14 days', 'standard', NOW())`,
        [job]
      );
      res.json({ success: true, action, job, message: `Job '${job}' wurde in die Queue gestellt und läuft beim nächsten Worker-Tick.` });
      return;
    }

    // Ensure backup table for paused schedules exists
    await pool.query(`
      CREATE TABLE IF NOT EXISTS research_robot_paused_schedules (
        name TEXT PRIMARY KEY,
        cron TEXT, timezone TEXT, data JSONB, options JSONB,
        paused_at TIMESTAMPTZ DEFAULT NOW()
      )`);

    if (action === "pause") {
      const sched = await pool.query("SELECT name, cron, timezone, data, options FROM pgboss.schedule WHERE name = $1", [job]);
      if (sched.rowCount === 0) {
        res.json({ success: true, action, job, message: `Job '${job}' war nicht im Schedule — bereits pausiert.` });
        return;
      }
      const s = sched.rows[0];
      await pool.query(
        `INSERT INTO research_robot_paused_schedules (name, cron, timezone, data, options, paused_at)
         VALUES ($1,$2,$3,$4,$5,NOW())
         ON CONFLICT (name) DO UPDATE SET cron=$2, timezone=$3, data=$4, options=$5, paused_at=NOW()`,
        [s.name, s.cron, s.timezone, s.data, s.options]
      );
      await pool.query("DELETE FROM pgboss.schedule WHERE name = $1", [job]);
      res.json({ success: true, action, job, message: `Job '${job}' pausiert (aus Schedule entfernt). Der Robot reiht ihn nicht mehr neu ein. Reversibel über 'Fortsetzen'.` });
      return;
    }

    if (action === "resume") {
      const bak = await pool.query("SELECT name, cron, timezone, data, options FROM research_robot_paused_schedules WHERE name = $1", [job]);
      if (bak.rowCount === 0) {
        res.status(404).json({ success: false, error: `Kein pausierter Schedule-Eintrag für '${job}' gefunden.` });
        return;
      }
      const s = bak.rows[0];
      await pool.query(
        `INSERT INTO pgboss.schedule (name, cron, timezone, data, options, created_on, updated_on)
         VALUES ($1,$2,$3,$4,$5,NOW(),NOW())
         ON CONFLICT (name) DO UPDATE SET cron=$2, timezone=$3, data=$4, options=$5, updated_on=NOW()`,
        [s.name, s.cron, s.timezone, s.data, s.options]
      );
      await pool.query("DELETE FROM research_robot_paused_schedules WHERE name = $1", [job]);
      res.json({ success: true, action, job, message: `Job '${job}' wieder aktiviert (zurück im Schedule).` });
      return;
    }
  } catch (e) {
    res.status(500).json({ success: false, error: (e as Error).message });
  }
});
