/**
 * Boundary-split analytics aggregation — the retention-aware counterpart to
 * executive-metrics, but SCOPED to a caller-supplied monitor set (the analytics
 * surfaces apply per-user region/permission filtering before aggregating).
 *
 * THE BUG THIS FIXES: the heatmap, /api/reports/downtime and the analytics
 * KPI/trend/insights services (src/lib/server/analytics.ts) all queried raw
 * `Heartbeat` directly over windows up to 90 days. Raw is retained only
 * RAW_RETENTION_DAYS (RollupService / /api/cron/cleanup); older rows are DELETEd
 * after being rolled into `HeartbeatHourly`. So every day older than the
 * boundary rendered blank / 0% once cleanup runs. (It "worked" only because
 * cleanup wasn't scheduled yet and raw had grown unbounded — the same ~1M-row
 * table that pegged the CPU.)
 *
 * FIX: split every retention-spanning query at rawBoundary() — read RAW for the
 * recent side, HOURLY for the older side — exactly like executive-metrics. The
 * two tiers are time-disjoint after rollup, so the union is the full window with
 * no double-count.
 *
 * Pure helpers (mergeDailyBuckets / fillDailySeries) carry all the combine +
 * gap-fill logic and are unit-tested; the $queryRaw wrappers just feed them.
 */
import { prisma } from '@/lib/prisma';
import { Prisma } from '@prisma/client';
import { rawBoundary } from './retention';
import { type Agg, EMPTY_AGG, addAgg, uptimePct, avgLatency } from './executive-metrics';

/** A per-day aggregate carrying the calendar day it belongs to ('YYYY-MM-DD', UTC). */
export type DailyRow = { day: string } & Agg;

/** One emitted heatmap/calendar cell. `hasData:false` means no checks ran that day. */
export interface DailyCell {
    date: string;
    uptimePercent: number | null;
    totalChecks: number;
    upChecks: number;
    downChecks: number;
    avgLatency: number;
    hasData: boolean;
}

const num = (v: unknown): number => (v == null ? 0 : Number(v));

/**
 * Merge raw-tier + hourly-tier per-day rows into one map keyed by 'YYYY-MM-DD'.
 * Sums fields for any day present in both tiers (shouldn't happen given the
 * boundary split, but stays correct if a row straddles it).
 */
export function mergeDailyBuckets(raw: DailyRow[], hourly: DailyRow[]): Map<string, Agg> {
    const map = new Map<string, Agg>();
    for (const row of [...raw, ...hourly]) {
        const cur = map.get(row.day) ?? { ...EMPTY_AGG };
        map.set(row.day, addAgg(cur, { total: row.total, up: row.up, latSum: row.latSum, latCount: row.latCount }));
    }
    return map;
}

/**
 * Project a day→Agg map onto a contiguous calendar series of `days + 1` cells
 * starting at `start` (inclusive of both endpoints — matches the heatmap's
 * "last N days including today" grid). Days with no Agg become hasData:false
 * cells (uptimePercent null) so the UI can paint them as "no data" instead of a
 * misleading 0% (red) outage.
 */
export function fillDailySeries(map: Map<string, Agg>, start: Date, days: number): DailyCell[] {
    const cells: DailyCell[] = [];
    const base = new Date(start);
    for (let d = 0; d <= days; d++) {
        const date = new Date(base);
        date.setUTCDate(base.getUTCDate() + d);
        const dateStr = date.toISOString().split('T')[0];
        const agg = map.get(dateStr);
        if (agg && agg.total > 0) {
            cells.push({
                date: dateStr,
                uptimePercent: parseFloat(uptimePct(agg).toFixed(1)),
                totalChecks: agg.total,
                upChecks: agg.up,
                downChecks: agg.total - agg.up,
                avgLatency: Math.round(avgLatency(agg)),
                hasData: true,
            });
        } else {
            cells.push({
                date: dateStr,
                uptimePercent: null,
                totalChecks: 0,
                upChecks: 0,
                downChecks: 0,
                avgLatency: 0,
                hasData: false,
            });
        }
    }
    return cells;
}

// ---------------------------------------------------------------------------
// DB-backed boundary-split aggregators. Each reads RAW for [max(start,boundary),
// end) and HOURLY for [start, boundary), then combines. `monitorIds` scopes to
// the caller's permitted monitors; an empty array means "no access" → empty.
// ---------------------------------------------------------------------------

type RawDailyRow = { day: string; total: bigint | number; up: bigint | number | null; latsum: bigint | number | null; latcount: bigint | number | null };

function toDailyRows(rows: RawDailyRow[]): DailyRow[] {
    return rows.map((r) => ({ day: r.day, total: num(r.total), up: num(r.up), latSum: num(r.latsum), latCount: num(r.latcount) }));
}

/**
 * Per-calendar-day (UTC) aggregate over [start, now], boundary-split across raw
 * + hourly, scoped to `monitorIds`. Powers the 90-day heatmap.
 */
export async function dailyAgg(start: Date, monitorIds: number[]): Promise<Map<string, Agg>> {
    if (monitorIds.length === 0) return new Map();
    const boundary = rawBoundary();
    const rawStart = start > boundary ? start : boundary; // max(start, boundary)
    const ids = Prisma.join(monitorIds);

    const [raw, hourly] = await Promise.all([
        prisma.$queryRaw<RawDailyRow[]>`
            SELECT DATE_FORMAT(createdAt, '%Y-%m-%d') AS day,
                   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 monitorId IN (${ids}) AND createdAt >= ${rawStart}
            GROUP BY day`,
        start < boundary
            ? prisma.$queryRaw<RawDailyRow[]>`
                SELECT DATE_FORMAT(timestamp, '%Y-%m-%d') AS day,
                       SUM(totalChecks) AS total,
                       SUM(successCount) AS up,
                       SUM(avgLatency * successCount) AS latsum,
                       SUM(successCount) AS latcount
                FROM HeartbeatHourly
                WHERE monitorId IN (${ids}) AND timestamp >= ${start} AND timestamp < ${boundary}
                GROUP BY day`
            : Promise.resolve([] as RawDailyRow[]),
    ]);

    return mergeDailyBuckets(toDailyRows(raw), toDailyRows(hourly));
}

/**
 * Combined raw+hourly aggregate over [start, now], scoped to `monitorIds`,
 * boundary-split. Powers the analytics KPI cards (uptime / avg latency /
 * downtime-checks). Latency is averaged over UP rows only on both tiers.
 */
export async function rangeAggScoped(start: Date, monitorIds: number[]): Promise<Agg> {
    if (monitorIds.length === 0) return { ...EMPTY_AGG };
    const boundary = rawBoundary();
    const rawStart = start > boundary ? start : boundary;
    const ids = Prisma.join(monitorIds);

    const [raw, hourly] = await Promise.all([
        prisma.$queryRaw<RawDailyRow[]>`
            SELECT '' AS day,
                   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 monitorId IN (${ids}) AND createdAt >= ${rawStart}`,
        start < boundary
            ? prisma.$queryRaw<RawDailyRow[]>`
                SELECT '' AS day,
                       SUM(totalChecks) AS total,
                       SUM(successCount) AS up,
                       SUM(avgLatency * successCount) AS latsum,
                       SUM(successCount) AS latcount
                FROM HeartbeatHourly
                WHERE monitorId IN (${ids}) AND timestamp >= ${start} AND timestamp < ${boundary}`
            : Promise.resolve([] as RawDailyRow[]),
    ]);
    const r = raw[0];
    const h = hourly[0];
    return addAgg(
        r ? { total: num(r.total), up: num(r.up), latSum: num(r.latsum), latCount: num(r.latcount) } : { ...EMPTY_AGG },
        h ? { total: num(h.total), up: num(h.up), latSum: num(h.latsum), latCount: num(h.latcount) } : { ...EMPTY_AGG },
    );
}

/**
 * Time-bucketed aggregate for the analytics trend chart, scoped to `monitorIds`.
 * `granularity` selects the SQL DATE_FORMAT: hourly for the 24h view, daily
 * otherwise. Boundary-split (raw recent / hourly older). Returns rows ordered by
 * bucket. tls/connect sub-metrics exist only in raw; older daily buckets sourced
 * from the hourly rollup carry tls=connect=0 (degraded but not wrong — the
 * rollup keeps no handshake breakdown). 24h granularity never reaches hourly.
 */
export interface TrendBucket extends Agg {
    bucket: string;
    tlsSum: number;
    connectSum: number;
}

type RawTrendRow = {
    bucket: string;
    total: bigint | number;
    up: bigint | number | null;
    latsum: bigint | number | null;
    latcount: bigint | number | null;
    tlssum: bigint | number | null;
    connectsum: bigint | number | null;
};

export async function trendAgg(start: Date, monitorIds: number[], granularity: '24h' | 'daily'): Promise<TrendBucket[]> {
    if (monitorIds.length === 0) return [];
    const boundary = rawBoundary();
    const rawStart = start > boundary ? start : boundary;
    const ids = Prisma.join(monitorIds);
    const fmt = granularity === '24h' ? '%Y-%m-%d %H:00:00' : '%Y-%m-%d';

    const [raw, hourly] = await Promise.all([
        prisma.$queryRaw<RawTrendRow[]>`
            SELECT DATE_FORMAT(createdAt, ${fmt}) AS bucket,
                   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,
                   SUM(CASE WHEN status = 'up' THEN tlsTimeMs END) AS tlssum,
                   SUM(CASE WHEN status = 'up' THEN connectTimeMs END) AS connectsum
            FROM Heartbeat
            WHERE monitorId IN (${ids}) AND createdAt >= ${rawStart}
            GROUP BY bucket`,
        start < boundary && granularity === 'daily'
            ? prisma.$queryRaw<RawTrendRow[]>`
                SELECT DATE_FORMAT(timestamp, '%Y-%m-%d') AS bucket,
                       SUM(totalChecks) AS total,
                       SUM(successCount) AS up,
                       SUM(avgLatency * successCount) AS latsum,
                       SUM(successCount) AS latcount,
                       0 AS tlssum,
                       0 AS connectsum
                FROM HeartbeatHourly
                WHERE monitorId IN (${ids}) AND timestamp >= ${start} AND timestamp < ${boundary}
                GROUP BY bucket`
            : Promise.resolve([] as RawTrendRow[]),
    ]);

    const map = new Map<string, TrendBucket>();
    for (const r of [...raw, ...hourly]) {
        const cur = map.get(r.bucket) ?? { bucket: r.bucket, total: 0, up: 0, latSum: 0, latCount: 0, tlsSum: 0, connectSum: 0 };
        cur.total += num(r.total);
        cur.up += num(r.up);
        cur.latSum += num(r.latsum);
        cur.latCount += num(r.latcount);
        cur.tlsSum += num(r.tlssum);
        cur.connectSum += num(r.connectsum);
        map.set(r.bucket, cur);
    }
    return [...map.values()].sort((a, b) => a.bucket.localeCompare(b.bucket));
}

/**
 * Failed-check count per monitor over [start, now], scoped + boundary-split.
 * Powers "worst performers". Raw counts non-'up' rows; hourly uses failureCount.
 */
export async function failureCountByMonitor(start: Date, monitorIds: number[]): Promise<Map<number, number>> {
    if (monitorIds.length === 0) return new Map();
    const boundary = rawBoundary();
    const rawStart = start > boundary ? start : boundary;
    const ids = Prisma.join(monitorIds);

    const [raw, hourly] = await Promise.all([
        prisma.$queryRaw<{ monitorId: number; down: bigint | number | null }[]>`
            SELECT monitorId, SUM(status <> 'up') AS down
            FROM Heartbeat
            WHERE monitorId IN (${ids}) AND createdAt >= ${rawStart}
            GROUP BY monitorId`,
        start < boundary
            ? prisma.$queryRaw<{ monitorId: number; down: bigint | number | null }[]>`
                SELECT monitorId, SUM(failureCount) AS down
                FROM HeartbeatHourly
                WHERE monitorId IN (${ids}) AND timestamp >= ${start} AND timestamp < ${boundary}
                GROUP BY monitorId`
            : Promise.resolve([] as { monitorId: number; down: bigint | number | null }[]),
    ]);

    const map = new Map<number, number>();
    for (const row of [...raw, ...hourly]) {
        const id = Number(row.monitorId);
        map.set(id, (map.get(id) ?? 0) + num(row.down));
    }
    return map;
}

/** Average a TrendBucket's response time over its UP samples (0 when none). */
export function bucketAvg(sum: number, count: number): number {
    return count > 0 ? Math.round(sum / count) : 0;
}

// Re-export the shared shapes so callers need only import from here.
export type { Agg };
export { uptimePct, avgLatency, addAgg, EMPTY_AGG };