// Auteur : Simon-Pierre Boucher — contact@spboucher.ai
/**
 * Administration de la base de coûts (serveur) : authentification par jeton,
 * état des connecteurs, anomalies, mappings, lancement en arrière-plan.
 */
import { NextResponse } from "next/server";
import type Database from "better-sqlite3";
import { getCostDb } from "../db";
import { clientKey, rateLimit } from "../cache";
import { CONNECTORS, IMPORT_ONLY, isRunnable, type RunnableKey } from "./registry";
import { isDue, runConnector } from "./run";
import type { RunStats } from "./types";

export function adminAuth(req: Request): NextResponse | null {
  const token = process.env.COST_ADMIN_TOKEN;
  if (!token) return NextResponse.json({ error: "COST_ADMIN_TOKEN non configuré" }, { status: 503 });
  if (!rateLimit(`admin:${clientKey(req)}`, 60, 60_000)) return NextResponse.json({ error: "Trop de requêtes" }, { status: 429 });
  const h = req.headers.get("authorization") ?? "";
  const bearer = h.toLowerCase().startsWith("bearer ") ? h.slice(7).trim() : null;
  const q = new URL(req.url).searchParams.get("token");
  if (bearer !== token && q !== token) return NextResponse.json({ error: "non autorisé" }, { status: 401 });
  return null;
}

type Row = Record<string, unknown>;

export function connectorsStatus(d: Database.Database = getCostDb()) {
  const sources = d.prepare(`SELECT s.key, s.name, s.source_type type, s.license_status license, s.is_active active, s.priority, s.refresh_frequency frequency, s.last_successful_sync lastSync, s.last_error lastError,
      (SELECT COUNT(*) FROM cost_item_prices p WHERE p.source_id=s.id) prices,
      (SELECT COUNT(*) FROM cost_item_prices p WHERE p.source_id=s.id AND p.observation_date >= date('now','-7 days')) pricesWeek,
      (SELECT COUNT(DISTINCT cost_item_id) FROM cost_item_prices p WHERE p.source_id=s.id AND p.observation_date >= date('now','-120 days')) itemsRecent,
      (SELECT COUNT(*) FROM labour_rates l WHERE l.source_id=s.id) labour,
      (SELECT COUNT(*) FROM cost_raw_observations o WHERE o.source_id=s.id) raw,
      (SELECT COUNT(*) FROM cost_raw_observations o WHERE o.source_id=s.id AND o.status='rejected') rejected,
      (SELECT COUNT(*) FROM cost_item_sources c WHERE c.source_id=s.id AND c.active=1) mappedItems,
      (SELECT COUNT(*) FROM product_mappings m WHERE m.source_id=s.id AND m.status='pending') pendingMappings
    FROM cost_sources s ORDER BY s.priority, s.name`).all() as Row[];
  const runs = d.prepare("SELECT id, connector, status, started_at startedAt, finished_at finishedAt, pages, observations, accepted, rejected, unchanged, errors, duration_ms durationMs FROM connector_runs ORDER BY started_at DESC LIMIT 40").all() as Row[];
  const indices = d.prepare("SELECT index_code code, geography, building_type buildingType, division, MAX(period) last, COUNT(*) n, MAX(retrieved_at) retrievedAt FROM construction_cost_indices GROUP BY index_code ORDER BY index_code").all() as Row[];
  const running = [...RUNNING.entries()].map(([k, v]) => ({ connector: k, runId: v.runId, startedAt: v.startedAt }));
  return {
    sources: sources.map((s) => ({ ...s, runnable: isRunnable(String(s.key)), importOnly: IMPORT_ONLY.includes(s.key as never), due: isRunnable(String(s.key)) ? isDue(s.key as RunnableKey, d) : false, refreshDays: isRunnable(String(s.key)) ? CONNECTORS[s.key as RunnableKey].config.refreshDays : null })),
    runs, indices, running,
    metrics: {
      syncSuccessRate: successRate(d), observationsPerSource: Object.fromEntries(sources.map((s) => [String(s.key), Number(s.prices) + Number(s.labour)])),
      ageOfLatestPriceDays: ageDays((d.prepare("SELECT MAX(observation_date) m FROM cost_item_prices WHERE price_kind IN ('observed','official')").get() as { m: string | null }).m),
      mappingFailureRate: mappingFailureRate(d), outlierRate: outlierRate(d),
      aiUsage: d.prepare("SELECT purpose, COUNT(*) n, SUM(input_tokens) input, SUM(output_tokens) output, ROUND(SUM(estimated_cost_usd),4) usd FROM ai_usage GROUP BY purpose").all(),
    },
  };
}

function successRate(d: Database.Database): number | null {
  const r = d.prepare("SELECT SUM(CASE WHEN status IN ('ok','partial') THEN 1 ELSE 0 END) ok, COUNT(*) n FROM connector_runs WHERE started_at >= datetime('now','-30 days') AND status != 'running'").get() as { ok: number | null; n: number };
  return r.n ? Math.round((100 * (r.ok ?? 0)) / r.n) : null;
}
function mappingFailureRate(d: Database.Database): number | null {
  const r = d.prepare("SELECT SUM(CASE WHEN status IN ('pending','rejected') THEN 1 ELSE 0 END) bad, COUNT(*) n FROM product_mappings").get() as { bad: number | null; n: number };
  return r.n ? Math.round((100 * (r.bad ?? 0)) / r.n) : null;
}
function outlierRate(d: Database.Database): number | null {
  const r = d.prepare("SELECT (SELECT COUNT(*) FROM cost_raw_observations WHERE status='rejected' AND reject_reason LIKE 'aberration%') a, (SELECT COUNT(*) FROM cost_item_prices) n").get() as { a: number; n: number };
  return r.n ? Math.round((1000 * r.a) / (r.n + r.a)) / 10 : null;
}
function ageDays(iso: string | null): number | null {
  return iso ? Math.round((Date.now() - new Date(iso).getTime()) / 86400000) : null;
}

export function anomalies(d: Database.Database = getCostDb()) {
  const outliers = d.prepare(`SELECT o.id, s.name source, o.source_url url, o.raw_title title, o.raw_price price, o.raw_unit unit, o.retrieved_at retrievedAt, o.reject_reason reason FROM cost_raw_observations o JOIN cost_sources s ON s.id=o.source_id WHERE o.status='rejected' ORDER BY o.retrieved_at DESC LIMIT 100`).all();
  const pending = d.prepare(`SELECT m.id, s.name source, m.product_url url, m.product_title title, m.proposed_item_code itemCode, m.method, m.confidence, m.rationale, m.pack_qty packQty, m.created_at createdAt FROM product_mappings m JOIN cost_sources s ON s.id=m.source_id WHERE m.status='pending' ORDER BY m.created_at DESC LIMIT 200`).all();
  const stale = d.prepare(`SELECT s.key, s.name, s.last_successful_sync lastSync FROM cost_sources s WHERE s.is_active=1 AND s.source_type IN ('retail','labour','index') AND (s.last_successful_sync IS NULL OR s.last_successful_sync < datetime('now','-14 days'))`).all();
  // écarts > 30 % entre deux observations consécutives d'un même (article, source)
  const jumps = d.prepare(`SELECT i.canonical_code code, s.name source, p1.observation_date d1, p1.total_cost v1, p2.observation_date d2, p2.total_cost v2, ROUND(100.0*(p2.total_cost/p1.total_cost-1),1) pct
    FROM cost_item_prices p2 JOIN cost_item_prices p1 ON p1.cost_item_id=p2.cost_item_id AND p1.source_id=p2.source_id AND p1.observation_date = (SELECT MAX(observation_date) FROM cost_item_prices x WHERE x.cost_item_id=p2.cost_item_id AND x.source_id=p2.source_id AND x.observation_date < p2.observation_date)
    JOIN cost_items i ON i.id=p2.cost_item_id JOIN cost_sources s ON s.id=p2.source_id
    WHERE ABS(p2.total_cost/p1.total_cost-1) > 0.3 ORDER BY p2.observation_date DESC LIMIT 100`).all();
  const noPrice = d.prepare(`SELECT i.canonical_code code, i.name_fr name, i.reference_price ref FROM cost_items i WHERE i.active=1 AND i.retail_query IS NOT NULL AND NOT EXISTS (SELECT 1 FROM cost_item_prices p WHERE p.cost_item_id=i.id AND p.observation_date >= date('now','-120 days')) ORDER BY i.canonical_code`).all();
  return { outliers, pendingMappings: pending, staleSources: stale, priceJumps: jumps, itemsWithoutObservation: noPrice };
}

/* ------------------------------------------------ lancement en arrière-plan */

const RUNNING = new Map<RunnableKey, { runId: number; startedAt: string; promise: Promise<RunStats> }>();

/** Démarre un connecteur sans bloquer la requête ; renvoie tout de suite l'identifiant du run. */
export function launchConnector(key: RunnableKey, force: boolean, onlyItems?: string[]): { started: boolean; runId: number | null; message: string } {
  if (RUNNING.has(key)) return { started: false, runId: RUNNING.get(key)!.runId, message: "déjà en cours" };
  const d = getCostDb();
  const before = (d.prepare("SELECT COALESCE(MAX(id),0) m FROM connector_runs").get() as { m: number }).m;
  const promise = runConnector(key, { force, onlyItems, db: d }).finally(() => RUNNING.delete(key));
  RUNNING.set(key, { runId: before + 1, startedAt: new Date().toISOString(), promise });
  return { started: true, runId: before + 1, message: "démarré en arrière-plan" };
}