import "server-only";
import { query } from "../connection";

// Observabilité crashs mobile (mf_ask #119 / #118 côté backend). La table
// `mobile_crash_reports` est alimentée par l'app mobile via
// missioflow-app/public/api/mobile/crash_report.php. Le panel la lit en direct
// (read-only, user `superadmin_ro`) — pas d'endpoint dédié, comme les autres
// listings (cf. AGENTS.md "Découpe lectures / mutations").

export type MobileCrashKind = "render" | "fatal" | "rejection";

export type MobileCrashRow = {
  id: number;
  tenant_id: number | null;
  tenant_name: string | null; // dérivé via jointure tenants
  tenant_slug: string | null;
  user_id: number | null;
  user_name: string | null; // dérivé via jointure techniciens
  kind: MobileCrashKind;
  message: string;
  stack: string | null;
  component_stack: string | null;
  is_fatal: number; // tinyint 0/1
  app_version: string | null;
  platform: string | null;
  instance_url: string | null;
  client_timestamp: Date | null;
  fingerprint: string;
  created_at: Date;
};

// Filtres exposés à la vue. Tous optionnels — on ne pose une clause que pour
// les champs réellement fournis. Toutes les valeurs passent par des `?`
// placeholders (cf. mitigation SQL stricte AGENTS.md), jamais d'interpolation.
export type MobileCrashFilters = {
  kind?: MobileCrashKind;
  tenantId?: number;
  appVersion?: string;
  instanceUrl?: string;
  from?: string; // 'YYYY-MM-DD' (inclus, sur created_at)
  to?: string; // 'YYYY-MM-DD' (inclus jusqu'à 23:59:59, sur created_at)
};

// Construit le WHERE partagé entre le listing et l'agrégat. Retourne le
// fragment SQL (sans le mot-clé WHERE) et les params dans l'ordre.
function buildWhere(f: MobileCrashFilters): { clause: string; params: unknown[] } {
  const conds: string[] = [];
  const params: unknown[] = [];

  if (f.kind) {
    conds.push("c.kind = ?");
    params.push(f.kind);
  }
  if (typeof f.tenantId === "number" && Number.isFinite(f.tenantId)) {
    conds.push("c.tenant_id = ?");
    params.push(f.tenantId);
  }
  if (f.appVersion) {
    conds.push("c.app_version = ?");
    params.push(f.appVersion);
  }
  if (f.instanceUrl) {
    conds.push("c.instance_url = ?");
    params.push(f.instanceUrl);
  }
  if (f.from) {
    conds.push("c.created_at >= ?");
    params.push(`${f.from} 00:00:00`);
  }
  if (f.to) {
    conds.push("c.created_at <= ?");
    params.push(`${f.to} 23:59:59`);
  }

  return {
    clause: conds.length > 0 ? `WHERE ${conds.join(" AND ")}` : "",
    params,
  };
}

export async function listMobileCrashes(
  filters: MobileCrashFilters = {},
  limit = 200,
): Promise<MobileCrashRow[]> {
  const { clause, params } = buildWhere(filters);
  return query<MobileCrashRow>(
    `SELECT
       c.id, c.tenant_id, c.user_id, c.kind, c.message, c.stack,
       c.component_stack, c.is_fatal, c.app_version, c.platform,
       c.instance_url, c.client_timestamp, c.fingerprint, c.created_at,
       tn.name AS tenant_name,
       tn.slug AS tenant_slug,
       CONCAT_WS(' ', t.prenom, t.nom) AS user_name
     FROM mobile_crash_reports c
     LEFT JOIN tenants tn ON tn.id = c.tenant_id
     LEFT JOIN techniciens t ON t.id = c.user_id
     ${clause}
     ORDER BY c.created_at DESC, c.id DESC
     LIMIT ?`,
    [...params, limit],
  );
}

export type MobileCrashSummary = {
  total: number;
  fatal: number;
  render: number;
  rejection: number;
};

// Compteurs sur l'ENSEMBLE du périmètre filtré (indépendant du LIMIT du
// listing) — sinon les cartes mentent dès qu'on dépasse la page.
export async function getMobileCrashSummary(
  filters: MobileCrashFilters = {},
): Promise<MobileCrashSummary> {
  const { clause, params } = buildWhere(filters);
  const rows = await query<{
    total: number;
    fatal: number;
    render: number;
    rejection: number;
  }>(
    `SELECT
       COUNT(*) AS total,
       SUM(c.is_fatal = 1) AS fatal,
       SUM(c.kind = 'render') AS render,
       SUM(c.kind = 'rejection') AS rejection
     FROM mobile_crash_reports c
     ${clause}`,
    params,
  );
  const r = rows[0];
  return {
    total: Number(r?.total ?? 0),
    fatal: Number(r?.fatal ?? 0),
    render: Number(r?.render ?? 0),
    rejection: Number(r?.rejection ?? 0),
  };
}

export type MobileCrashFilterOptions = {
  versions: string[];
  instances: string[];
  tenants: { id: number; name: string }[];
};

// Alimente les <select> de filtre. On ne liste que les valeurs réellement
// présentes dans la table de crashs (pas tous les tenants du SaaS) — un filtre
// vide n'a pas de sens.
export async function getMobileCrashFilterOptions(): Promise<MobileCrashFilterOptions> {
  const [versions, instances, tenants] = await Promise.all([
    query<{ app_version: string }>(
      `SELECT DISTINCT app_version FROM mobile_crash_reports
       WHERE app_version IS NOT NULL AND app_version <> ''
       ORDER BY app_version DESC`,
    ),
    query<{ instance_url: string }>(
      `SELECT DISTINCT instance_url FROM mobile_crash_reports
       WHERE instance_url IS NOT NULL AND instance_url <> ''
       ORDER BY instance_url ASC`,
    ),
    query<{ id: number; name: string }>(
      `SELECT DISTINCT tn.id, tn.name
       FROM mobile_crash_reports c
       JOIN tenants tn ON tn.id = c.tenant_id
       ORDER BY tn.name ASC`,
    ),
  ]);
  return {
    versions: versions.map((v) => v.app_version),
    instances: instances.map((i) => i.instance_url),
    tenants: tenants.map((t) => ({ id: t.id, name: t.name })),
  };
}