dogfather-universe/server-internal/routes/supporter-admin.js

/* =====================================================================
   routes/supporter-admin.js — Administrationsbereich für das Supporter-
   Abo (Dogfather_VanVan_Supporter_Abo.odt Abschnitt 27). 1:1 portiert aus
   cloudflare-worker/src/routes/supporter-admin.js.

   Nutzt die BESTEHENDE interne Team-Session (auth.js/requireSession),
   NICHT die öffentliche Supporter-Session — nur berechtigte
   Administrator:innen (Owner oder Rolle mit SUPPORTER_*-Rechten) dürfen
   hierauf zugreifen.
   ===================================================================== */
import { db } from "../db.js";
import { generateId, nowIso } from "../lib/crypto.js";
import { hasPermission } from "../lib/permissions.js";
import { json } from "../lib/http.js";
import { logAction } from "../lib/audit.js";
import { computeProgress, timelineForCurrentCycle } from "../lib/supporter-cycle.js";

function can(session, key) {
  if (!session) return false;
  if (session.isOwner) return true;
  return hasPermission(session.permissions, session.overrides, key);
}

function forbidden(res) {
  return json(res, { ok: false, error: "Keine Berechtigung." }, 403);
}

/** Abonnentenübersicht mit Filtern (Abschnitt 27). */
export async function listSupporters(req, res, session) {
  if (!can(session, "SUPPORTER_VIEW")) return forbidden(res);
  const body = req.body || {};
  const filter = body.filter || "alle";

  const rows = db
    .prepare(
      `SELECT u.id, u.display_name, u.tiktok_username, u.email, u.email_verified, u.created_at,
              s.id as sub_id, s.paypal_subscription_id, s.status as sub_status, s.started_at,
              s.next_payment_at, s.paid_period_end_at, s.cancelled_at, s.total_paid_months
       FROM supporter_users u
       LEFT JOIN supporter_subscriptions s ON s.supporter_id = u.id
       WHERE u.status = 'active'
       ORDER BY u.created_at DESC`
    )
    .all();

  let list = rows;
  const FILTER_MAP = {
    aktiv: (r) => r.sub_status === "active",
    gekuendigt: (r) => r.sub_status === "cancelled",
    zahlung_fehlgeschlagen: (r) => r.sub_status === "suspended",
    monat_6: (r) => r.total_paid_months >= 6,
    monat_12: (r) => r.total_paid_months >= 12,
    monat_18: (r) => r.total_paid_months >= 18,
    monat_24: (r) => r.total_paid_months >= 24,
    monat_30: (r) => r.total_paid_months >= 30,
  };
  if (FILTER_MAP[filter]) list = list.filter(FILTER_MAP[filter]);

  // Fortschritt (Zyklus/Meilenstein) rein rechnerisch aus total_paid_months
  // ergänzen (Nutzer-Wunsch 04.08.2026: "wo die Leute mit den Abos dran
  // sind ... alles einsehen können") — dieselbe Logik wie im Supporter-
  // Bereich selbst (lib/supporter-cycle.js), damit nichts auseinanderläuft.
  list = list.map((r) => ({ ...r, progress: computeProgress(r.total_paid_months || 0) }));

  return json(res, { ok: true, supporters: list });
}

/**
 * Übersicht über ALLE Supporter hinweg (Nutzer-Wunsch 04.08.2026: "ein
 * festes Fenster, wo man drauf klicken muss um zu sehen wer was hat, wie
 * weit ist" — statt sich erst durch jeden einzelnen Supporter klicken zu
 * müssen). Liefert die komplette Prämien-Warteschlange (für den Versand)
 * und die letzten Zahlungen über alle Supporter, jeweils mit Name/E-Mail
 * dazu — bewusst EIN kombinierter Aufruf statt vieler Einzelabfragen.
 */
export async function getAdminOverview(req, res, session) {
  if (!can(session, "SUPPORTER_VIEW")) return forbidden(res);

  const premiums = db
    .prepare(
      `SELECT p.*, c.name as collection_name, u.display_name, u.email, u.tiktok_username
       FROM supporter_premiums p
       JOIN supporter_collections c ON c.id = p.collection_id
       JOIN supporter_users u ON u.id = p.supporter_id
       ORDER BY
         CASE p.redeem_status
           WHEN 'wird_geprueft' THEN 0
           WHEN 'wird_vorbereitet' THEN 1
           WHEN 'versandbereit' THEN 2
           WHEN 'problem' THEN 3
           WHEN 'ersatz_wird_vorbereitet' THEN 4
           WHEN 'verfuegbar' THEN 5
           WHEN 'versendet' THEN 6
           WHEN 'zugestellt' THEN 7
           WHEN 'erhalten' THEN 8
           ELSE 9
         END,
         p.unlocked_at DESC`
    )
    .all();

  const payments = db
    .prepare(
      `SELECT pay.*, u.display_name, u.email
       FROM supporter_payments pay
       JOIN supporter_users u ON u.id = pay.supporter_id
       ORDER BY pay.paid_at DESC LIMIT 100`
    )
    .all();

  return json(res, { ok: true, premiums, payments });
}

/** Details + Prämien eines einzelnen Supporters. */
export async function getSupporterDetail(req, res, session) {
  if (!can(session, "SUPPORTER_VIEW")) return forbidden(res);
  const body = req.body || {};
  const supporter = db.prepare(`SELECT * FROM supporter_users WHERE id = ?`).get(body.supporterId);
  if (!supporter) return json(res, { ok: false, error: "Nicht gefunden." }, 404);

  const sub = db
    .prepare(`SELECT * FROM supporter_subscriptions WHERE supporter_id = ? ORDER BY created_at DESC LIMIT 1`)
    .get(supporter.id);
  const premiums = db
    .prepare(
      `SELECT p.*, c.name as collection_name FROM supporter_premiums p
       JOIN supporter_collections c ON c.id = p.collection_id
       WHERE p.supporter_id = ? ORDER BY p.cycle_number, p.milestone_month`
    )
    .all(supporter.id);
  const payments = db.prepare(`SELECT * FROM supporter_payments WHERE supporter_id = ? ORDER BY paid_at DESC LIMIT 50`).all(supporter.id);

  const totalPaidMonths = sub?.total_paid_months || 0;
  const progress = computeProgress(totalPaidMonths);
  const timeline = timelineForCurrentCycle(totalPaidMonths);

  return json(res, {
    ok: true, supporter, subscription: sub, premiums, payments,
    progress, timeline,
  });
}

/** Prämie/Versand manuell bearbeiten (Abschnitt 27 "Prämienverwaltung"). */
export async function updatePremium(req, res, session) {
  if (!can(session, "SUPPORTER_MANAGE_PREMIUMS")) return forbidden(res);
  const body = req.body || {};
  const premium = db.prepare(`SELECT * FROM supporter_premiums WHERE id = ?`).get(body.premiumId);
  if (!premium) return json(res, { ok: false, error: "Prämie nicht gefunden." }, 404);

  const ALLOWED_STATUS = [
    "verfuegbar", "eingeloest", "wird_geprueft", "wird_vorbereitet",
    "versandbereit", "versendet", "zugestellt", "erhalten", "problem", "ersatz_wird_vorbereitet",
  ];
  const nextStatus = ALLOWED_STATUS.includes(body.redeemStatus) ? body.redeemStatus : premium.redeem_status;
  const trackingNumber = body.trackingNumber !== undefined ? body.trackingNumber : premium.tracking_number;
  const shippedAt = nextStatus === "versendet" && !premium.shipped_at ? nowIso() : premium.shipped_at;
  const deliveredAt = nextStatus === "zugestellt" && !premium.delivered_at ? nowIso() : premium.delivered_at;

  db.prepare(
    `UPDATE supporter_premiums SET redeem_status = ?, tracking_number = ?, shipped_at = ?, delivered_at = ?, admin_note = ? WHERE id = ?`
  ).run(nextStatus, trackingNumber || null, shippedAt, deliveredAt, body.adminNote ?? premium.admin_note, premium.id);

  logAction(session.actor, "supporter.premium_updated", premium.id, {
    from: premium.redeem_status, to: nextStatus,
  });
  return json(res, { ok: true });
}

/** Kollektionen anlegen/bearbeiten (Abschnitt 27 "Kollektionen verwalten"). */
export async function listCollections(req, res, session) {
  if (!can(session, "SUPPORTER_VIEW")) return forbidden(res);
  const rows = db.prepare(`SELECT * FROM supporter_collections ORDER BY collection_number ASC`).all();
  return json(res, { ok: true, collections: rows });
}

export async function upsertCollection(req, res, session) {
  if (!can(session, "SUPPORTER_MANAGE_COLLECTIONS")) return forbidden(res);
  const body = req.body || {};
  const num = Number(body.collectionNumber);
  if (!Number.isFinite(num) || num < 1) return json(res, { ok: false, error: "Ungültige Kollektionsnummer." }, 400);

  const existing = db.prepare(`SELECT id FROM supporter_collections WHERE collection_number = ?`).get(num);
  const id = existing?.id || generateId();
  const fields = {
    name: body.name || `Kollektion ${String(num).padStart(2, "0")}`,
    theme: body.theme || null,
    description: body.description || null,
    cover_image: body.coverImage || null,
    small_teddy_design: body.smallTeddyDesign || null,
    medium_teddy_design: body.mediumTeddyDesign || null,
    large_teddy_design: body.largeTeddyDesign || null,
    mug_design: body.mugDesign || null,
    card_design: body.cardDesign || null,
    hoodie_design: body.hoodieDesign || null,
    available_hoodie_sizes: JSON.stringify(body.availableHoodieSizes || ["S", "M", "L", "XL", "XXL"]),
    status: body.status === "published" ? "published" : "draft",
  };

  if (existing) {
    db.prepare(
      `UPDATE supporter_collections SET name=?, theme=?, description=?, cover_image=?, small_teddy_design=?,
         medium_teddy_design=?, large_teddy_design=?, mug_design=?, card_design=?, hoodie_design=?,
         available_hoodie_sizes=?, status=? WHERE id=?`
    ).run(
      fields.name, fields.theme, fields.description, fields.cover_image, fields.small_teddy_design,
      fields.medium_teddy_design, fields.large_teddy_design, fields.mug_design, fields.card_design,
      fields.hoodie_design, fields.available_hoodie_sizes, fields.status, id
    );
  } else {
    db.prepare(
      `INSERT INTO supporter_collections
         (id, collection_number, name, theme, description, cover_image, small_teddy_design, medium_teddy_design,
          large_teddy_design, mug_design, card_design, hoodie_design, available_hoodie_sizes, status, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
    ).run(
      id, num, fields.name, fields.theme, fields.description, fields.cover_image, fields.small_teddy_design,
      fields.medium_teddy_design, fields.large_teddy_design, fields.mug_design, fields.card_design,
      fields.hoodie_design, fields.available_hoodie_sizes, fields.status, nowIso()
    );
  }

  logAction(session.actor, "supporter.collection_saved", id, { collectionNumber: num });
  return json(res, { ok: true, id });
}