RAPPORT-HOST/server/routes/admin.js

// RAPPORT-HOST — Betreiber-Bereich (/api/admin). Separates Admin-Login
// (ADMIN_PASSWORD), getrennt von Kundenkonten. Übersicht aller Kunden, Abos,
// Instanzen + Sperren/Reaktivieren.
import { Router } from "express";
import { one, query } from "../db.js";
import { requireAdmin, signAdminToken } from "../auth.js";
import { getPlan } from "../plans.js";
import { env, provisioningMock } from "../env.js";

export const adminRouter = Router();

// — Separates Admin-Login (Passwort aus ADMIN_PASSWORD) — kein requireAdmin davor.
adminRouter.post("/login", (req, res) => {
  if (!env.adminPassword) return res.status(503).json({ error: "Admin-Bereich nicht konfiguriert." });
  if ((req.body?.password || "") !== env.adminPassword) {
    return res.status(401).json({ error: "Passwort falsch." });
  }
  res.json({ token: signAdminToken() });
});

// Alle folgenden Routen verlangen einen gültigen Operator-Token.
adminRouter.use(requireAdmin);

// — Kennzahlen fürs Dashboard —
adminRouter.get("/stats", async (_req, res) => {
  const accounts = await one("select count(*)::int n from accounts");
  const newAccounts = await one("select count(*)::int n from accounts where created_at > now() - interval '30 days'");
  const activeSubs = await one("select count(*)::int n from subscriptions where status = 'active'");
  const instances = await one("select count(*)::int n from instances");
  const activeInst = await one("select count(*)::int n from instances where status = 'active'");
  const suspendedInst = await one("select count(*)::int n from instances where status = 'suspended'");
  // Zahlungsprobleme: Abos in past_due (fehlgeschlagene Zahlung) — braucht Aufmerksamkeit.
  const pastDue = await one("select count(*)::int n from subscriptions where status = 'past_due'");

  // MRR + Verteilung nach Plan (nur aktive Abos).
  const { rows: subs } = await query("select plan from subscriptions where status = 'active'");
  let mrr = 0;
  const byPlan = {};
  for (const s of subs) {
    const p = getPlan(s.plan);
    mrr += p?.priceChf || 0;
    byPlan[s.plan] = byPlan[s.plan] || { count: 0, revenue: 0, name: p?.name || s.plan };
    byPlan[s.plan].count += 1;
    byPlan[s.plan].revenue += p?.priceChf || 0;
  }

  res.json({
    accounts: accounts.n,
    newAccounts30d: newAccounts.n,
    activeSubscriptions: activeSubs.n,
    instances: instances.n,
    activeInstances: activeInst.n,
    suspendedInstances: suspendedInst.n,
    pastDueSubscriptions: pastDue.n,
    mrrChf: mrr,
    arrChf: mrr * 12,
    byPlan, // { solo: {count,revenue,name}, ... }
  });
});

// — Alle Kunden mit Abo + Instanzen —
adminRouter.get("/accounts", async (_req, res) => {
  const { rows } = await query(`
    select a.id, a.email, a.company, a.created_at,
           s.plan, s.status as sub_status, s.current_period_end,
           coalesce(i.cnt, 0)::int as instance_count
      from accounts a
      left join lateral (
        select plan, status, current_period_end
          from subscriptions where account_id = a.id
          order by created_at desc limit 1
      ) s on true
      left join (
        select account_id, count(*) cnt from instances group by account_id
      ) i on i.account_id = a.id
      order by a.created_at desc
  `);
  res.json({ accounts: rows });
});

// — Voll-Detail eines Kontos: Profil + Abo-Historie + Instanzen —
adminRouter.get("/accounts/:id", async (req, res) => {
  const account = await one(
    `select id, email, company, contact_name, street, zip, city, country, phone, created_at
       from accounts where id = $1`,
    [req.params.id]
  );
  if (!account) return res.status(404).json({ error: "Konto nicht gefunden." });

  const { rows: subscriptions } = await query(
    `select plan, status, current_period_end, stripe_subscription_id, created_at
       from subscriptions where account_id = $1 order by created_at desc`,
    [req.params.id]
  );
  const { rows: instances } = await query(
    "select id, studio_slug, label, instance_url, status, created_at from instances where account_id = $1 order by created_at",
    [req.params.id]
  );
  // Plan-Preis an die Abos hängen (fürs UI).
  for (const s of subscriptions) s.priceChf = getPlan(s.plan)?.priceChf ?? null;

  res.json({ account, subscriptions, instances });
});

// — CSV-Export für die Buchhaltung —
// Eine Zeile pro Kunde mit aktuellem Abo + Plan-Preis. Semikolon-getrennt
// (Excel-CH-freundlich), UTF-8 BOM, damit Umlaute korrekt erscheinen.
function csvCell(v) {
  const s = v == null ? "" : String(v);
  return /[";\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
}
adminRouter.get("/export/accounts.csv", async (_req, res) => {
  const { rows } = await query(`
    select a.email, a.company, a.contact_name, a.street, a.zip, a.city, a.country, a.created_at,
           s.plan, s.status as sub_status, s.current_period_end,
           coalesce(i.cnt, 0)::int as instance_count
      from accounts a
      left join lateral (
        select plan, status, current_period_end from subscriptions
        where account_id = a.id order by created_at desc limit 1
      ) s on true
      left join (select account_id, count(*) cnt from instances group by account_id) i
        on i.account_id = a.id
      order by a.created_at
  `);
  const header = ["Email","Firma","Ansprechperson","Strasse","PLZ","Ort","Land",
    "Kunde_seit","Plan","Abo_Status","Periode_bis","Preis_CHF_mtl","Instanzen"];
  const lines = [header.join(";")];
  for (const r of rows) {
    const price = getPlan(r.plan)?.priceChf ?? "";
    lines.push([
      r.email, r.company, r.contact_name, r.street, r.zip, r.city, r.country,
      r.created_at ? new Date(r.created_at).toISOString().slice(0, 10) : "",
      r.plan || "", r.sub_status || "", r.current_period_end ? new Date(r.current_period_end).toISOString().slice(0, 10) : "",
      r.sub_status === "active" ? price : "", r.instance_count,
    ].map(csvCell).join(";"));
  }
  const csv = "" + lines.join("\r\n");
  res.setHeader("Content-Type", "text/csv; charset=utf-8");
  res.setHeader("Content-Disposition", `attachment; filename="rapport-kunden-${new Date().toISOString().slice(0,10)}.csv"`);
  res.send(csv);
});

// — Instanzen eines Kontos (Kurzform) —
adminRouter.get("/accounts/:id/instances", async (req, res) => {
  const { rows } = await query(
    "select id, studio_slug, label, instance_url, status, created_at from instances where account_id = $1 order by created_at",
    [req.params.id]
  );
  res.json({ instances: rows });
});

// — Health-Check aller aktiven Instanzen —
// Pingt jede Instanz-URL (HEAD, kurzes Timeout) und meldet up/down. Im
// MOCK-Modus (provisioningMock) sind die URLs synthetisch → Status "unknown"
// statt fake "up", damit das Cockpit ehrlich bleibt.
adminRouter.get("/health", async (_req, res) => {
  const { rows } = await query(
    "select id, studio_slug, label, instance_url, status from instances where status = 'active'"
  );

  if (provisioningMock) {
    return res.json({
      mock: true,
      checked: 0,
      instances: rows.map((i) => ({ ...i, health: "unknown" })),
    });
  }

  async function ping(url) {
    const ctrl = new AbortController();
    const t = setTimeout(() => ctrl.abort(), 4000);
    try {
      const r = await fetch(url, { method: "HEAD", signal: ctrl.signal, redirect: "manual" });
      return r.status < 500 ? "up" : "down";
    } catch {
      return "down";
    } finally {
      clearTimeout(t);
    }
  }

  const checked = await Promise.all(
    rows.map(async (i) => ({ ...i, health: await ping(i.instance_url) }))
  );
  res.json({
    mock: false,
    checked: checked.length,
    down: checked.filter((i) => i.health === "down").length,
    instances: checked,
  });
});

// — Instanz sperren / reaktivieren —
adminRouter.post("/instances/:id/:action", async (req, res) => {
  const { id, action } = req.params;
  const map = { suspend: "suspended", reactivate: "active" };
  const status = map[action];
  if (!status) return res.status(400).json({ error: "Unbekannte Aktion." });
  const row = await one("update instances set status = $1 where id = $2 returning id, status", [status, id]);
  if (!row) return res.status(404).json({ error: "Instanz nicht gefunden." });
  res.json({ instance: row });
});