/**
 * Executive-report aggregation helpers.
 *
 * The Executive dashboard summarises uptime / latency over 30-day, 60-day and
 * 12-month windows. Computing that by loading raw Heartbeat rows into Node and
 * reducing in JS pegged the web process (~1M rows, every 30s, per viewer — see
 * PR perf cache). These helpers push the math into MySQL and combine the two
 * storage tiers:
 *
 *   - `Heartbeat`        — raw per-check rows, retained ~7 days (RollupService).
 *   - `HeartbeatHourly`  — per-hour rollup (successCount / totalChecks /
 *                          avgLatency) for everything older than that.
 *
 * The two tiers are TIME-DISJOINT: RollupService DELETEs raw rows once they're
 * rolled into hourly, so summing an aggregate from BOTH over the same window
 * never double-counts. During the transition (rollup not yet run) hourly is
 * empty and raw holds everything — the sum still holds.
 *
 * Percentiles can't come from the rollup (it stores avg/min/max, not
 * percentiles), so they're computed from a bounded recent sample of raw rows —
 * which is both cheap and the most operationally relevant window.
 */
import { prisma } from '@/lib/prisma';
import { rawBoundary } from './retention';

export interface Agg {
    total: number;     // total checks
    up: number;        // successful checks
    latSum: number;    // sum of latencies (ms) — for a weighted mean
    latCount: number;  // count of samples contributing to latSum
}

export const EMPTY_AGG: Agg = { total: 0, up: 0, latSum: 0, latCount: 0 };

/** Coerce a possibly-bigint / possibly-null SQL scalar to a number. */
const num = (v: unknown): number => (v == null ? 0 : Number(v));

export function addAgg(a: Agg, b: Agg): Agg {
    return { total: a.total + b.total, up: a.up + b.up, latSum: a.latSum + b.latSum, latCount: a.latCount + b.latCount };
}

/** Uptime % for an aggregate; an empty window is treated as 100% (no data = no known downtime). */
export function uptimePct(a: Agg): number {
    return a.total > 0 ? (a.up / a.total) * 100 : 100;
}

/** Weighted mean latency (ms) for an aggregate. */
export function avgLatency(a: Agg): number {
    return a.latCount > 0 ? a.latSum / a.latCount : 0;
}

/** Nearest-rank percentile of a pre-sorted ascending array. */
export function percentile(sortedAsc: number[], p: number): number {
    if (sortedAsc.length === 0) return 0;
    const idx = Math.ceil((p / 100) * sortedAsc.length) - 1;
    return sortedAsc[Math.max(0, Math.min(sortedAsc.length - 1, idx))];
}

type RawRow = { total: bigint | number; up: bigint | number | null; latsum: bigint | number | null; latcount: bigint | number | null };
type HourlyRow = { total: bigint | number | null; up: bigint | number | null; latsum: bigint | number | null; latcount: bigint | number | null };

/**
 * Raw Heartbeat is retained only ~RAW_RETENTION_DAYS (RollupService /
 * /api/cron/cleanup); older data lives ONLY in HeartbeatHourly. CRUCIALLY the
 * two tiers OVERLAP in time for recent hours — the per-check upsert
 * (upsertHeartbeatHourly) writes the current hour into HeartbeatHourly while
 * the raw rows still exist — so summing both over the same window double-counts
 * (and historically skewed uptime, since the real-time rows lacked successCount).
 * We therefore split at the boundary: read RAW for the recent side, HOURLY for
 * the older side. RAW_RETENTION_DAYS / rawBoundary() live in ./retention (the
 * single source of truth, shared with /api/cron/cleanup and analytics-aggregation).
 */
const EMPTY_RAW: RawRow[] = [];
const EMPTY_HOURLY: HourlyRow[] = [];

/** Combined raw+hourly aggregate over [start, end), split at the retention boundary (no overlap). */
export async function rangeAgg(start: Date, end: Date): Promise<Agg> {
    const boundary = rawBoundary();
    const rawStart = start > boundary ? start : boundary; // max(start, boundary)
    const hourlyEnd = end < boundary ? end : boundary;    // min(end, boundary)
    const [raw, hourly] = await Promise.all([
        end > rawStart
            ? prisma.$queryRaw<RawRow[]>`
                SELECT COUNT(*) AS total,
                       SUM(status = 'up') AS up,
                       SUM(CASE WHEN status = 'up' THEN responseTimeMs END) AS latsum,
                       SUM(status = 'up' AND responseTimeMs IS NOT NULL) AS latcount
                FROM Heartbeat
                WHERE createdAt >= ${rawStart} AND createdAt < ${end}`
            : Promise.resolve(EMPTY_RAW),
        start < hourlyEnd
            ? prisma.$queryRaw<HourlyRow[]>`
                SELECT SUM(totalChecks) AS total,
                       SUM(successCount) AS up,
                       SUM(avgLatency * successCount) AS latsum,
                       SUM(successCount) AS latcount
                FROM HeartbeatHourly
                WHERE timestamp >= ${start} AND timestamp < ${hourlyEnd}`
            : Promise.resolve(EMPTY_HOURLY),
    ]);
    const r = raw[0] ?? ({} as RawRow);
    const h = hourly[0] ?? ({} as HourlyRow);
    return {
        total: num(r.total) + num(h.total),
        up: num(r.up) + num(h.up),
        latSum: num(r.latsum) + num(h.latsum),
        latCount: num(r.latcount) + num(h.latcount),
    };
}

/** Combined raw+hourly aggregate bucketed by calendar month ('YYYY-MM'), since `start` (boundary-split). */
export async function monthlyAgg(start: Date): Promise<Map<string, Agg>> {
    const boundary = rawBoundary();
    const rawStart = start > boundary ? start : boundary; // max(start, boundary)
    const [raw, hourly] = await Promise.all([
        prisma.$queryRaw<(RawRow & { ym: string })[]>`
            SELECT DATE_FORMAT(createdAt, '%Y-%m') AS ym,
                   COUNT(*) AS total,
                   SUM(status = 'up') AS up,
                   SUM(CASE WHEN status = 'up' THEN responseTimeMs END) AS latsum,
                   SUM(status = 'up' AND responseTimeMs IS NOT NULL) AS latcount
            FROM Heartbeat
            WHERE createdAt >= ${rawStart}
            GROUP BY ym`,
        start < boundary
            ? prisma.$queryRaw<(HourlyRow & { ym: string })[]>`
                SELECT DATE_FORMAT(timestamp, '%Y-%m') AS ym,
                       SUM(totalChecks) AS total,
                       SUM(successCount) AS up,
                       SUM(avgLatency * successCount) AS latsum,
                       SUM(successCount) AS latcount
                FROM HeartbeatHourly
                WHERE timestamp >= ${start} AND timestamp < ${boundary}
                GROUP BY ym`
            : Promise.resolve([] as (HourlyRow & { ym: string })[]),
    ]);
    const map = new Map<string, Agg>();
    for (const row of [...raw, ...hourly]) {
        const cur = map.get(row.ym) ?? { ...EMPTY_AGG };
        map.set(row.ym, addAgg(cur, { total: num(row.total), up: num(row.up), latSum: num(row.latsum), latCount: num(row.latcount) }));
    }
    return map;
}

/** Combined raw+hourly uptime aggregate bucketed by monitor region, since `start` (boundary-split). */
export async function regionAgg(start: Date): Promise<Map<string, Agg>> {
    const boundary = rawBoundary();
    const rawStart = start > boundary ? start : boundary; // max(start, boundary)
    const [raw, hourly] = await Promise.all([
        prisma.$queryRaw<{ region: string | null; total: bigint | number; up: bigint | number | null }[]>`
            SELECT m.region AS region, COUNT(*) AS total, SUM(h.status = 'up') AS up
            FROM Heartbeat h JOIN Monitor m ON m.id = h.monitorId
            WHERE h.createdAt >= ${rawStart} AND m.deletedAt IS NULL
            GROUP BY m.region`,
        start < boundary
            ? prisma.$queryRaw<{ region: string | null; total: bigint | number | null; up: bigint | number | null }[]>`
                SELECT m.region AS region, SUM(hh.totalChecks) AS total, SUM(hh.successCount) AS up
                FROM HeartbeatHourly hh JOIN Monitor m ON m.id = hh.monitorId
                WHERE hh.timestamp >= ${start} AND hh.timestamp < ${boundary} AND m.deletedAt IS NULL
                GROUP BY m.region`
            : Promise.resolve([] as { region: string | null; total: bigint | number | null; up: bigint | number | null }[]),
    ]);
    const map = new Map<string, Agg>();
    for (const row of [...raw, ...hourly]) {
        const region = row.region || 'Global';
        const cur = map.get(region) ?? { ...EMPTY_AGG };
        map.set(region, addAgg(cur, { total: num(row.total), up: num(row.up), latSum: 0, latCount: 0 }));
    }
    return map;
}

/**
 * Bounded ascending sample of recent raw response times for percentile calc.
 * Capped so the cost is constant regardless of table size. `sinceMs` defaults
 * to the last 24h — the operationally relevant latency distribution.
 */
export async function recentResponseSample(since: Date): Promise<number[]> {
    const rows = await prisma.$queryRaw<{ r: bigint | number }[]>`
        SELECT responseTimeMs AS r
        FROM Heartbeat
        WHERE createdAt >= ${since} AND responseTimeMs IS NOT NULL
        ORDER BY createdAt DESC
        LIMIT 50000`;
    return rows.map((x) => num(x.r)).sort((a, b) => a - b);
}