/**
 * Services d'administration
 * Statistiques globales et audit
 */
import type { Pool, PoolConnection } from "mysql2/promise";

export type WalletResource =
  | "points"
  | "hints"
  | "undos"
  | "replays"
  | "bonus_points";

export interface WalletChange {
  resource: WalletResource;
  delta: number;
}

export interface AuditEntry {
  userId?: number | null;
  action: string;
  meta?: Record<string, unknown>;
  ip?: string | null;
  userAgent?: string | null;
  installationId?: string | null;
}

export interface AdminOverviewResult {
  users: number;
  wallets: number;
  progressRows: number;
  activeSessions: number;
  auditTotal: number;
  auditLast24h: number;
  loginsLast24h: number;
  progressTotals: Record<string, number>;
  topWallets: Array<{
    id: number;
    email: string;
    displayName: string | null;
    points: number;
    hints: number;
    undos: number;
    replays: number;
  }>;
  recentUsers: Array<{
    id: number;
    email: string;
    displayName: string | null;
    createdAt: Date | string | null;
    isAdmin: boolean;
  }>;
  recentAudits: Array<{
    id: number;
    action: string;
    createdAt: Date | string | null;
    ip: string | null;
    email: string | null;
  }>;
}

/**
 * Enregistre une action dans le journal d'audit.
 * Accepte Pool ou PoolConnection (pour rester dans une transaction).
 */
export const recordAudit = async (
  db: Pool | PoolConnection,
  entry: AuditEntry,
): Promise<void> => {
  try {
    await db.execute(
      "INSERT INTO audit_log (user_id, action, meta, ip, user_agent, installation_id) VALUES (?, ?, ?, ?, ?, ?)",
      [
        entry.userId ?? null,
        entry.action,
        entry.meta ? JSON.stringify(entry.meta) : null,
        entry.ip ?? null,
        entry.userAgent ?? null,
        entry.installationId ?? null,
      ],
    );
  } catch {
    // keep main flow uninterrupted
  }
};

/**
 * Enregistre un ou plusieurs mouvements wallet dans le ledger wallet_history.
 * À appeler APRÈS le UPDATE wallets pour que balance_after reflète le solde courant.
 * Accepte Pool ou PoolConnection (pour rester dans une transaction).
 */
export const recordWalletChange = async (
  db: Pool | PoolConnection,
  userId: number,
  changes: WalletChange[],
  source: string,
  meta?: Record<string, unknown>,
): Promise<void> => {
  try {
    const [rows] = await db.execute(
      "SELECT points, hints, undos, replays, bonus_points FROM wallets WHERE user_id = ?",
      [userId],
    );
    const wallet = (rows as Record<string, number>[])[0];
    if (!wallet) return;

    for (const { resource, delta } of changes) {
      const balanceAfter = wallet[resource] ?? 0;
      const balanceBefore = balanceAfter - delta;
      await db.execute(
        "INSERT INTO wallet_history (user_id, resource, delta, balance_before, balance_after, source, meta) VALUES (?, ?, ?, ?, ?, ?, ?)",
        [
          userId,
          resource,
          delta,
          balanceBefore,
          balanceAfter,
          source,
          meta ? JSON.stringify(meta) : null,
        ],
      );
    }
  } catch {
    // non-blocking — ne doit jamais casser le flux principal
  }
};

/**
 * Récupère une vue d'ensemble complète pour l'admin
 */
export const fetchAdminOverview = async (
  db: Pool,
): Promise<AdminOverviewResult> => {
  const [userRows] = await db.query("SELECT COUNT(*) as total FROM users");
  const [walletRows] = await db.query("SELECT COUNT(*) as total FROM wallets");
  const [progressRows] = await db.query(
    "SELECT COUNT(*) as total FROM progress",
  );
  const [sessionRows] = await db.query(
    "SELECT COUNT(*) as total FROM refresh_tokens WHERE revoked_at IS NULL AND expires_at > NOW()",
  );
  const [auditRows] = await db.query("SELECT COUNT(*) as total FROM audit_log");
  const [auditRecentRows] = await db.query(
    "SELECT COUNT(*) as total FROM audit_log WHERE created_at >= NOW() - INTERVAL 24 HOUR",
  );
  const [loginRecentRows] = await db.query(
    "SELECT COUNT(*) as total FROM audit_log WHERE action IN ('auth.login','auth.register') AND created_at >= NOW() - INTERVAL 24 HOUR",
  );
  const [difficultyRows] = await db.query(
    "SELECT difficulty, SUM(completed) as total FROM progress GROUP BY difficulty",
  );
  const [topWalletRows] = await db.query(
    "SELECT u.id, u.email, u.display_name as displayName, w.points, w.hints, w.undos, w.replays FROM wallets w JOIN users u ON w.user_id = u.id ORDER BY w.points DESC LIMIT 6",
  );
  const [recentUserRows] = await db.query(
    "SELECT id, email, display_name as displayName, created_at as createdAt, is_admin as isAdmin FROM users ORDER BY created_at DESC LIMIT 8",
  );
  const [recentAuditRows] = await db.query(
    "SELECT a.id, a.action, a.created_at as createdAt, a.ip, u.email FROM audit_log a LEFT JOIN users u ON a.user_id = u.id ORDER BY a.created_at DESC LIMIT 12",
  );

  const getFirst = (rows: unknown) =>
    Array.isArray(rows) && rows.length > 0 ? rows[0] : undefined;
  const toNumber = (row: unknown) => {
    if (row && typeof row === "object" && "total" in row) {
      const value = (row as { total?: number | string }).total;
      return Number(value) || 0;
    }
    return 0;
  };

  const progressTotals: Record<string, number> = {
    easy: 0,
    medium: 0,
    hard: 0,
    expert: 0,
  };
  if (Array.isArray(difficultyRows)) {
    difficultyRows.forEach((row) => {
      const item = row as { difficulty?: string; total?: number | string };
      if (item.difficulty && item.difficulty in progressTotals) {
        progressTotals[item.difficulty] = Number(item.total) || 0;
      }
    });
  }

  const toWallet = (row: unknown) => {
    const item = row as {
      id?: number;
      email?: string;
      displayName?: string | null;
      points?: number | string;
      hints?: number | string;
      undos?: number | string;
      replays?: number | string;
    };
    return {
      id: Number(item.id) || 0,
      email: item.email ?? "",
      displayName: item.displayName ?? null,
      points: Number(item.points) || 0,
      hints: Number(item.hints) || 0,
      undos: Number(item.undos) || 0,
      replays: Number(item.replays) || 0,
    };
  };

  const toUser = (row: unknown) => {
    const item = row as {
      id?: number;
      email?: string;
      displayName?: string | null;
      createdAt?: Date | string;
      isAdmin?: number | boolean;
    };
    return {
      id: Number(item.id) || 0,
      email: item.email ?? "",
      displayName: item.displayName ?? null,
      createdAt: item.createdAt ?? null,
      isAdmin: Boolean(item.isAdmin),
    };
  };

  const toAudit = (row: unknown) => {
    const item = row as {
      id?: number;
      action?: string;
      createdAt?: Date | string;
      ip?: string | null;
      email?: string | null;
    };
    return {
      id: Number(item.id) || 0,
      action: item.action ?? "",
      createdAt: item.createdAt ?? null,
      ip: item.ip ?? null,
      email: item.email ?? null,
    };
  };

  return {
    users: toNumber(getFirst(userRows)),
    wallets: toNumber(getFirst(walletRows)),
    progressRows: toNumber(getFirst(progressRows)),
    activeSessions: toNumber(getFirst(sessionRows)),
    auditTotal: toNumber(getFirst(auditRows)),
    auditLast24h: toNumber(getFirst(auditRecentRows)),
    loginsLast24h: toNumber(getFirst(loginRecentRows)),
    progressTotals,
    topWallets: Array.isArray(topWalletRows) ? topWalletRows.map(toWallet) : [],
    recentUsers: Array.isArray(recentUserRows)
      ? recentUserRows.map(toUser)
      : [],
    recentAudits: Array.isArray(recentAuditRows)
      ? recentAuditRows.map(toAudit)
      : [],
  };
};