<?php

declare(strict_types=1);

namespace PpPhp\Core\Build;

use PpPhp\Core\DB;
use PpPhp\Core\PlatformSettings;

/**
 * Build metering, audit, and quota — the abuse guard for the build authority.
 *
 * Every server-side build is attributed to the licence instance that asked for it,
 * so a leaked or misbehaving install cannot silently hammer the generator. Three jobs:
 *   • RECORD each build against per-instance rollups — an HOURLY bucket (for the hour
 *     window) and a DAILY bucket (for day/week/month + totals) — cheap visibility the
 *     owner can act on.
 *   • RESOLVE the effective limits for the caller — per-licence override → the licence's
 *     PLAN limits → the global defaults, independently for each window.
 *   • ENFORCE limits across FOUR windows at once (hour / day / week / month). A build is
 *     allowed only if EVERY configured window still has headroom.
 *
 * Server-only (stripped from client bundles). FAIL-OPEN by design: any storage/lookup
 * error returns "allowed" and recording is best-effort, so a metering glitch can never
 * block a legitimate customer's build — a window only ever bites when it is both
 * configured (>0) AND the counter can be read.
 *
 * Limit value convention (uniform at every level):
 *   • null / absent  → INHERIT from the next-lower-priority level
 *   • 0              → UNLIMITED (explicit)
 *   • N > 0          → cap of N builds in that window
 * Global defaults are the base (never null); 0 there means unlimited.
 */
final class BuildMeter
{
    /** The four windows a limit can apply to, tightest first. */
    public const WINDOWS = ['hour', 'day', 'week', 'month'];

    // Global default limits (one per window). 0 = unlimited.
    private const SETTING_LIMIT_HOUR  = 'platform.build_limit_hour';
    private const SETTING_LIMIT_DAY   = 'platform.build_limit_day';
    private const SETTING_LIMIT_WEEK  = 'platform.build_limit_week';
    private const SETTING_LIMIT_MONTH = 'platform.build_limit_month';

    // Legacy single-window settings — still honoured as a fallback for the day window.
    private const SETTING_CAP    = 'platform.build_daily_cap';   // legacy (daily)
    private const SETTING_LIMIT  = 'platform.build_limit';       // legacy single limit
    private const SETTING_PERIOD = 'platform.build_period';      // legacy day|week|month

    private const SETTING_WATERMARK  = 'platform.watermark_builds';
    private const SETTING_DELTA      = 'platform.build_delta';
    private const SETTING_SLIM       = 'platform.build_slim';
    private const SETTING_CACHE_DAYS = 'platform.build_cache_days';
    private const SETTING_CACHE_MAX  = 'platform.build_cache_max';

    /** Legacy window list (day/week/month) — kept for callers that predate 'hour'. */
    public static function periods(): array { return ['day', 'week', 'month']; }

    private static function boolSetting(string $key, bool $default): bool
    {
        try {
            $v = PlatformSettings::get($key)['value'] ?? null;
            return $v === null ? $default : (bool) $v;
        } catch (\Throwable $e) { return $default; }
    }

    private static function intSetting(string $key, int $default, int $min = 1): int
    {
        try {
            $v = (int) (PlatformSettings::get($key)['value'] ?? 0);
            return $v >= $min ? $v : $default;
        } catch (\Throwable $e) { return $default; }
    }

    /** Ship only-changed files on rebuild (near-instant iterate loop). Default ON. */
    public static function deltaBuilds(): bool { return self::boolSetting(self::SETTING_DELTA, true); }
    public static function setDeltaBuilds(bool $on): bool { try { return PlatformSettings::set(self::SETTING_DELTA, ['value' => $on ? 1 : 0]); } catch (\Throwable $e) { return false; } }

    /** Omit static vendor assets the client already ships (>90% smaller). Default ON. */
    public static function assetSlimming(): bool { return self::boolSetting(self::SETTING_SLIM, true); }
    public static function setAssetSlimming(bool $on): bool { try { return PlatformSettings::set(self::SETTING_SLIM, ['value' => $on ? 1 : 0]); } catch (\Throwable $e) { return false; } }

    /** How long a cached build ZIP is kept (days). Default 14. */
    public static function cacheDays(): int { return self::intSetting(self::SETTING_CACHE_DAYS, 14); }
    public static function setCacheDays(int $d): bool { try { return PlatformSettings::set(self::SETTING_CACHE_DAYS, ['value' => max(1, min(365, $d))]); } catch (\Throwable $e) { return false; } }

    /** Max cached builds kept on the server before the oldest are pruned. Default 400. */
    public static function cacheMax(): int { return self::intSetting(self::SETTING_CACHE_MAX, 400); }
    public static function setCacheMax(int $n): bool { try { return PlatformSettings::set(self::SETTING_CACHE_MAX, ['value' => max(10, min(100000, $n))]); } catch (\Throwable $e) { return false; } }

    /** Whether server-side builds embed the per-licence watermark (default ON). */
    public static function watermarkEnabled(): bool
    {
        try {
            $v = PlatformSettings::get(self::SETTING_WATERMARK)['value'] ?? null;
            return $v === null ? true : (bool) $v;      // absent = on
        } catch (\Throwable $e) {
            return true;
        }
    }

    public static function setWatermark(bool $on): bool
    {
        try { return PlatformSettings::set(self::SETTING_WATERMARK, ['value' => $on ? 1 : 0]); }
        catch (\Throwable $e) { return false; }
    }

    // ── Schema ────────────────────────────────────────────────────────────

    private static bool $schemaReady = false;

    private static function ensureSchema(): void
    {
        if (self::$schemaReady) return;
        self::$schemaReady = true;
        try {
            DB::Init();
            DB::SetConnection();
            // Daily rollup — day/week/month windows, totals, and per-instance history.
            DB::Exec("CREATE TABLE IF NOT EXISTS `pp_build_usage` (
                `instance_id` VARCHAR(64) NOT NULL,
                `day` DATE NOT NULL,
                `builds` INT NOT NULL DEFAULT 0,
                `bytes` BIGINT NOT NULL DEFAULT 0,
                `last_at` DATETIME NULL,
                PRIMARY KEY (`instance_id`, `day`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            // Hourly rollup — the 'hour' window only. Small + auto-pruned (see record()).
            DB::Exec("CREATE TABLE IF NOT EXISTS `pp_build_usage_hourly` (
                `instance_id` VARCHAR(64) NOT NULL,
                `hour` DATETIME NOT NULL,
                `builds` INT NOT NULL DEFAULT 0,
                PRIMARY KEY (`instance_id`, `hour`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            // Append-only per-build EVENT log — the durable analytical record behind
            // the analytics/monitoring dashboard (throughput, cache-hit rate, build
            // size + duration, full-vs-delta, load-by-hour). One row per build; NOT
            // pruned by default (kept for analysis). Indexed for time-range + per-
            // install queries.
            DB::Exec("CREATE TABLE IF NOT EXISTS `pp_build_events` (
                `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `instance_id` VARCHAR(64) NOT NULL,
                `kind` VARCHAR(10) NOT NULL DEFAULT 'full',
                `cached` TINYINT NOT NULL DEFAULT 0,
                `ok` TINYINT NOT NULL DEFAULT 1,
                `bytes` BIGINT NOT NULL DEFAULT 0,
                `files` INT NOT NULL DEFAULT 0,
                `duration_ms` INT NULL,
                `created_at` DATETIME NOT NULL,
                PRIMARY KEY (`id`),
                KEY `idx_created` (`created_at`),
                KEY `idx_inst_created` (`instance_id`, `created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        } catch (\Throwable $e) {
            // A missing table just means metering is unavailable — never fatal.
        }
    }

    // ── Global default limits (per window) ─────────────────────────────────

    /**
     * The owner-set GLOBAL default limits, one per window. Always concrete ints
     * (0 = unlimited) — this is the base level, never "inherit". Honours the legacy
     * single build_limit/build_period for the matching window if the new per-window
     * key is unset, so existing installs keep their configured cap.
     *
     * @return array{hour:int,day:int,week:int,month:int}
     */
    public static function globalLimits(): array
    {
        $read = static function (string $key): ?int {
            try {
                $raw = PlatformSettings::get($key)['value'] ?? null;
                return $raw === null || $raw === '' ? null : max(0, (int) $raw);
            } catch (\Throwable $e) { return null; }
        };
        $out = [
            'hour'  => $read(self::SETTING_LIMIT_HOUR),
            'day'   => $read(self::SETTING_LIMIT_DAY),
            'week'  => $read(self::SETTING_LIMIT_WEEK),
            'month' => $read(self::SETTING_LIMIT_MONTH),
        ];
        // Legacy fallback: a pre-existing single limit maps onto its period's window.
        $legacyLimit = $read(self::SETTING_LIMIT) ?? $read(self::SETTING_CAP);
        if ($legacyLimit !== null && $legacyLimit > 0) {
            $legacyWindow = self::legacyPeriod();
            if ($out[$legacyWindow] === null) $out[$legacyWindow] = $legacyLimit;
        }
        foreach (self::WINDOWS as $w) { if ($out[$w] === null) $out[$w] = 0; }   // base = 0 (unlimited)
        return $out;
    }

    /** Persist the global default limits. Accepts a partial {window: int|null} map. */
    public static function setGlobalLimits(array $limits): bool
    {
        $map = [
            'hour'  => self::SETTING_LIMIT_HOUR,
            'day'   => self::SETTING_LIMIT_DAY,
            'week'  => self::SETTING_LIMIT_WEEK,
            'month' => self::SETTING_LIMIT_MONTH,
        ];
        $ok = true;
        foreach ($map as $w => $key) {
            if (!array_key_exists($w, $limits)) continue;
            $v = $limits[$w];
            $v = ($v === null || $v === '') ? 0 : max(0, (int) $v);
            try { $ok = PlatformSettings::set($key, ['value' => $v]) && $ok; }
            catch (\Throwable $e) { $ok = false; }
        }
        return $ok;
    }

    /** The legacy single-window period, day|week|month (never 'hour'). */
    private static function legacyPeriod(): string
    {
        try {
            $p = strtolower(trim((string) (PlatformSettings::get(self::SETTING_PERIOD)['value'] ?? 'day')));
            return in_array($p, ['day', 'week', 'month'], true) ? $p : 'day';
        } catch (\Throwable $e) {
            return 'day';
        }
    }

    // ── Effective limits for a specific instance ───────────────────────────

    /**
     * Resolve the limits that actually apply to one instance, per window, by
     * precedence: per-licence override → the licence's PLAN limits → global default.
     * A window is null at a level ⇒ inherit the next level; a set value (0=unlimited,
     * N=cap) stops the search. Fail-open: any lookup failure falls back to the global
     * defaults so enforcement never breaks on a licensing hiccup.
     *
     * @return array{hour:int,day:int,week:int,month:int}
     */
    public static function effectiveLimits(string $instanceId): array
    {
        $global = self::globalLimits();
        if ($instanceId === '') return $global;

        $plan = [];
        $override = [];
        try {
            if (class_exists(\PpPhp\Core\Licensing\LicenseServer::class)
                && class_exists(\PpPhp\Core\Licensing\LicenseKeys::class)) {
                $srv  = new \PpPhp\Core\Licensing\LicenseServer(new \PpPhp\Core\Licensing\LicenseKeys());
                $inst = $srv->instance($instanceId);
                if (is_array($inst) && ($inst['license_id'] ?? null) !== null) {
                    $lic = $srv->license((int) $inst['license_id']);
                    if (is_array($lic)) {
                        if (class_exists(\PpPhp\Core\Licensing\PlanRegistry::class)) {
                            $plan = \PpPhp\Core\Licensing\PlanRegistry::buildLimitsFor((string) ($lic['plan'] ?? ''));
                        }
                        $override = self::decodeLimits($lic['build_limits_override'] ?? null);
                    }
                }
            }
        } catch (\Throwable $e) {
            return $global;   // fail-open
        }

        $out = [];
        foreach (self::WINDOWS as $w) {
            if (array_key_exists($w, $override) && $override[$w] !== null) { $out[$w] = max(0, (int) $override[$w]); continue; }
            if (array_key_exists($w, $plan) && $plan[$w] !== null)         { $out[$w] = max(0, (int) $plan[$w]);     continue; }
            $out[$w] = (int) ($global[$w] ?? 0);
        }
        return $out;
    }

    /**
     * Normalise a stored/JSON limits map to {window: int|null}. Accepts a JSON string
     * or an array; unknown/blank values become null (inherit).
     *
     * @return array<string,int|null>
     */
    public static function decodeLimits(mixed $raw): array
    {
        if (is_string($raw)) { $raw = $raw === '' ? [] : (json_decode($raw, true) ?: []); }
        if (!is_array($raw)) $raw = [];
        $out = [];
        foreach (self::WINDOWS as $w) {
            if (!array_key_exists($w, $raw)) { $out[$w] = null; continue; }
            $v = $raw[$w];
            $out[$w] = ($v === null || $v === '') ? null : max(0, (int) $v);
        }
        return $out;
    }

    /** Encode a {window: int|null} map to a compact JSON string for storage. */
    public static function encodeLimits(array $limits): string
    {
        $clean = self::decodeLimits($limits);
        // Drop pure-inherit maps to '' so an untouched override stores nothing.
        $hasValue = false;
        foreach ($clean as $v) { if ($v !== null) { $hasValue = true; break; } }
        return $hasValue ? (string) json_encode($clean) : '';
    }

    // ── Usage counters ─────────────────────────────────────────────────────

    /** SQL predicate for "the daily `day` column falls in the current window". */
    private static function dayWindowWhere(string $window): string
    {
        return match ($window) {
            'week'  => '`day` >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)',   // since Monday
            'month' => '`day` >= DATE_FORMAT(CURDATE(), \'%Y-%m-01\')',                   // since the 1st
            default => '`day` = CURDATE()',
        };
    }

    /** Builds this instance has run in the given window (hour|day|week|month). */
    public static function usedInWindow(string $instanceId, string $window): int
    {
        if ($instanceId === '') return 0;
        $window = in_array($window, self::WINDOWS, true) ? $window : 'day';
        self::ensureSchema();
        try {
            if ($window === 'hour') {
                $rows = DB::FetchAll(
                    'SELECT COALESCE(SUM(`builds`),0) AS n FROM `pp_build_usage_hourly`
                      WHERE `instance_id` = ? AND `hour` = DATE_FORMAT(NOW(), \'%Y-%m-%d %H:00:00\')',
                    [$instanceId]
                );
            } else {
                $rows = DB::FetchAll(
                    'SELECT COALESCE(SUM(`builds`),0) AS n FROM `pp_build_usage`
                      WHERE `instance_id` = ? AND ' . self::dayWindowWhere($window),
                    [$instanceId]
                );
            }
            return (int) ($rows[0]['n'] ?? 0);
        } catch (\Throwable $e) {
            return 0;
        }
    }

    /** Legacy alias (day|week|month). */
    public static function usedInPeriod(string $instanceId, ?string $period = null): int
    {
        return self::usedInWindow($instanceId, $period ?? self::legacyPeriod());
    }

    /** Back-compat: builds today. */
    public static function usedToday(string $instanceId): int
    {
        return self::usedInWindow($instanceId, 'day');
    }

    /**
     * A compact usage snapshot for one instance — for the console Installations list.
     *
     * @return array{hour:int,day:int,week:int,month:int,total:int,last_at:?string}
     */
    public static function usage(string $instanceId): array
    {
        $blank = ['hour' => 0, 'day' => 0, 'week' => 0, 'month' => 0, 'total' => 0, 'last_at' => null];
        if ($instanceId === '') return $blank;
        self::ensureSchema();
        try {
            $agg = DB::FetchAll(
                'SELECT
                    COALESCE(SUM(CASE WHEN `day` = CURDATE() THEN `builds` END),0) AS d,
                    COALESCE(SUM(CASE WHEN `day` >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) THEN `builds` END),0) AS w,
                    COALESCE(SUM(CASE WHEN `day` >= DATE_FORMAT(CURDATE(), \'%Y-%m-01\') THEN `builds` END),0) AS m,
                    COALESCE(SUM(`builds`),0) AS t,
                    MAX(`last_at`) AS last_at
                 FROM `pp_build_usage` WHERE `instance_id` = ?',
                [$instanceId]
            );
            $r = $agg[0] ?? [];
            return [
                'hour'    => self::usedInWindow($instanceId, 'hour'),
                'day'     => (int) ($r['d'] ?? 0),
                'week'    => (int) ($r['w'] ?? 0),
                'month'   => (int) ($r['m'] ?? 0),
                'total'   => (int) ($r['t'] ?? 0),
                'last_at' => $r['last_at'] ?? null,
            ];
        } catch (\Throwable $e) {
            return $blank;
        }
    }

    /**
     * Bulk usage for many instances at once — one query for the whole Installations
     * table instead of N. Hourly counts are merged in from the hourly table.
     *
     * @param  list<string> $instanceIds
     * @return array<string,array{hour:int,day:int,week:int,month:int,total:int,last_at:?string}>
     */
    public static function usageFor(array $instanceIds): array
    {
        $ids = array_values(array_unique(array_filter(array_map('strval', $instanceIds), 'strlen')));
        $out = [];
        foreach ($ids as $id) { $out[$id] = ['hour' => 0, 'day' => 0, 'week' => 0, 'month' => 0, 'total' => 0, 'last_at' => null]; }
        if ($ids === []) return $out;
        self::ensureSchema();
        try {
            $in = implode(',', array_fill(0, count($ids), '?'));
            $rows = DB::FetchAll(
                'SELECT `instance_id`,
                    COALESCE(SUM(CASE WHEN `day` = CURDATE() THEN `builds` END),0) AS d,
                    COALESCE(SUM(CASE WHEN `day` >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) THEN `builds` END),0) AS w,
                    COALESCE(SUM(CASE WHEN `day` >= DATE_FORMAT(CURDATE(), \'%Y-%m-01\') THEN `builds` END),0) AS m,
                    COALESCE(SUM(`builds`),0) AS t,
                    MAX(`last_at`) AS last_at
                 FROM `pp_build_usage` WHERE `instance_id` IN (' . $in . ') GROUP BY `instance_id`',
                $ids
            );
            foreach ($rows as $r) {
                $id = (string) $r['instance_id'];
                if (!isset($out[$id])) continue;
                $out[$id]['day']     = (int) $r['d'];
                $out[$id]['week']    = (int) $r['w'];
                $out[$id]['month']   = (int) $r['m'];
                $out[$id]['total']   = (int) $r['t'];
                $out[$id]['last_at'] = $r['last_at'] ?? null;
            }
            $hrows = DB::FetchAll(
                'SELECT `instance_id`, COALESCE(SUM(`builds`),0) AS h FROM `pp_build_usage_hourly`
                  WHERE `hour` = DATE_FORMAT(NOW(), \'%Y-%m-%d %H:00:00\') AND `instance_id` IN (' . $in . ')
                  GROUP BY `instance_id`',
                $ids
            );
            foreach ($hrows as $r) {
                $id = (string) $r['instance_id'];
                if (isset($out[$id])) $out[$id]['hour'] = (int) $r['h'];
            }
        } catch (\Throwable $e) {
            // fail-soft — return zeroed rows
        }
        return $out;
    }

    /**
     * Estate-wide build totals for the analytics dashboard: builds this hour / today
     * / this week / this month / all-time, and how many distinct installs have ever
     * built. Fail-soft to zeros.
     *
     * @return array{hour:int,day:int,week:int,month:int,total:int,installs:int}
     */
    public static function globalTotals(): array
    {
        $out = ['hour' => 0, 'day' => 0, 'week' => 0, 'month' => 0, 'total' => 0, 'installs' => 0];
        self::ensureSchema();
        try {
            $r = DB::FetchAll(
                'SELECT
                    COALESCE(SUM(CASE WHEN `day` = CURDATE() THEN `builds` END),0) AS d,
                    COALESCE(SUM(CASE WHEN `day` >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) THEN `builds` END),0) AS w,
                    COALESCE(SUM(CASE WHEN `day` >= DATE_FORMAT(CURDATE(), \'%Y-%m-01\') THEN `builds` END),0) AS m,
                    COALESCE(SUM(`builds`),0) AS t,
                    COUNT(DISTINCT `instance_id`) AS n
                 FROM `pp_build_usage`'
            )[0] ?? [];
            $out['day']      = (int) ($r['d'] ?? 0);
            $out['week']     = (int) ($r['w'] ?? 0);
            $out['month']    = (int) ($r['m'] ?? 0);
            $out['total']    = (int) ($r['t'] ?? 0);
            $out['installs'] = (int) ($r['n'] ?? 0);
            $h = DB::FetchAll(
                'SELECT COALESCE(SUM(`builds`),0) AS h FROM `pp_build_usage_hourly`
                  WHERE `hour` = DATE_FORMAT(NOW(), \'%Y-%m-%d %H:00:00\')'
            )[0] ?? [];
            $out['hour'] = (int) ($h['h'] ?? 0);
        } catch (\Throwable $e) { /* fail-soft */ }
        return $out;
    }

    /**
     * Daily build totals for the last N days (oldest→newest), gaps filled with 0 —
     * for the analytics trend chart. Dates are yyyy-mm-dd.
     *
     * @return list<array{day:string,builds:int}>
     */
    public static function dailyTrend(int $days = 14): array
    {
        $days = max(1, min(120, $days));
        self::ensureSchema();
        $byDay = [];
        try {
            $rows = DB::FetchAll(
                'SELECT `day`, COALESCE(SUM(`builds`),0) AS n FROM `pp_build_usage`
                  WHERE `day` >= DATE_SUB(CURDATE(), INTERVAL ? DAY) GROUP BY `day`',
                [$days - 1]
            );
            foreach ($rows as $r) { $byDay[(string) $r['day']] = (int) $r['n']; }
        } catch (\Throwable $e) { /* fail-soft — a flat line */ }
        $out = [];
        $ts = time();
        for ($i = $days - 1; $i >= 0; $i--) {
            $d = date('Y-m-d', $ts - $i * 86400);
            $out[] = ['day' => $d, 'builds' => $byDay[$d] ?? 0];
        }
        return $out;
    }

    // ── Analytics (dashboard + monitoring) ─────────────────────────────────

    /** Supported analytics ranges → [days back, bucket granularity]. */
    private static function rangeSpec(string $range): array
    {
        return match ($range) {
            '24h'  => [1,  'hour'],
            '7d'   => [7,  'day'],
            '90d'  => [90, 'day'],
            default => [30, 'day'],   // 30d
        };
    }

    /** The whole analytics payload for one range — for the dashboard + its JSON API. */
    public static function analytics(string $range = '30d'): array
    {
        self::ensureSchema();
        return [
            'range'   => in_array($range, ['24h', '7d', '30d', '90d'], true) ? $range : '30d',
            'series'  => self::volumeSeries($range),
            'rates'   => self::eventRates($range, false),
            'prev'    => self::eventRates($range, true),
            'heatmap' => self::eventHeatmap(30),
        ];
    }

    /**
     * Build volume per bucket over a range, gaps filled with 0. Powered by the
     * COUNTER rollups so it shows existing history immediately (24h → hourly,
     * otherwise daily).
     *
     * @return list<array{t:string,label:string,builds:int}>
     */
    public static function volumeSeries(string $range = '30d'): array
    {
        [$days, $gran] = self::rangeSpec($range);
        self::ensureSchema();
        $out = [];
        try {
            if ($gran === 'hour') {
                $rows = DB::FetchAll(
                    "SELECT DATE_FORMAT(`hour`,'%Y-%m-%d %H') AS b, COALESCE(SUM(`builds`),0) n
                       FROM `pp_build_usage_hourly` WHERE `hour` >= DATE_SUB(NOW(), INTERVAL 23 HOUR)
                      GROUP BY b"
                );
                $by = [];
                foreach ($rows as $r) { $by[(string) $r['b']] = (int) $r['n']; }
                $ts = time();
                for ($i = 23; $i >= 0; $i--) {
                    $k = date('Y-m-d H', $ts - $i * 3600);
                    $out[] = ['t' => $k . ':00', 'label' => date('H:00', $ts - $i * 3600), 'builds' => $by[$k] ?? 0];
                }
            } else {
                $rows = DB::FetchAll(
                    "SELECT `day` AS b, COALESCE(SUM(`builds`),0) n FROM `pp_build_usage`
                      WHERE `day` >= DATE_SUB(CURDATE(), INTERVAL ? DAY) GROUP BY `day`",
                    [$days - 1]
                );
                $by = [];
                foreach ($rows as $r) { $by[(string) $r['b']] = (int) $r['n']; }
                $ts = time();
                for ($i = $days - 1; $i >= 0; $i--) {
                    $d = date('Y-m-d', $ts - $i * 86400);
                    $out[] = ['t' => $d, 'label' => date('j M', $ts - $i * 86400), 'builds' => $by[$d] ?? 0];
                }
            }
        } catch (\Throwable $e) { /* fail-soft */ }
        return $out;
    }

    /** Hours in a range window — for the events lookback + the previous period. */
    private static function rangeHours(string $range): int
    {
        return match ($range) { '24h' => 24, '7d' => 168, '90d' => 2160, default => 720 };
    }

    /**
     * Event-derived rates over a range (or the PREVIOUS equal window when $prev),
     * for the KPI tiles + their deltas. From the durable event log, so these accrue
     * from the moment event logging shipped.
     *
     * @return array{builds:int,cached:int,errors:int,full:int,delta:int,bytesTotal:int,bytesAvg:int,durAvg:?int,installs:int,cacheHitPct:int}
     */
    public static function eventRates(string $range = '30d', bool $prev = false): array
    {
        $h = self::rangeHours($range);
        self::ensureSchema();
        $blank = ['builds' => 0, 'cached' => 0, 'errors' => 0, 'full' => 0, 'delta' => 0,
                  'bytesTotal' => 0, 'bytesAvg' => 0, 'durAvg' => null, 'installs' => 0, 'cacheHitPct' => 0];
        try {
            $where = $prev
                ? '`created_at` >= DATE_SUB(NOW(), INTERVAL ? HOUR) AND `created_at` < DATE_SUB(NOW(), INTERVAL ? HOUR)'
                : '`created_at` >= DATE_SUB(NOW(), INTERVAL ? HOUR)';
            $args  = $prev ? [$h * 2, $h] : [$h];
            $r = DB::FetchAll(
                "SELECT COUNT(*) builds,
                        COALESCE(SUM(`cached`),0) cached,
                        COALESCE(SUM(`ok`=0),0) errors,
                        COALESCE(SUM(`kind`='full'),0) fulls,
                        COALESCE(SUM(`kind`='delta'),0) deltas,
                        COALESCE(SUM(`bytes`),0) bytesTotal,
                        COALESCE(AVG(`bytes`),0) bytesAvg,
                        AVG(`duration_ms`) durAvg,
                        COUNT(DISTINCT `instance_id`) installs
                   FROM `pp_build_events` WHERE {$where}",
                $args
            )[0] ?? [];
            $builds = (int) ($r['builds'] ?? 0);
            return [
                'builds'      => $builds,
                'cached'      => (int) ($r['cached'] ?? 0),
                'errors'      => (int) ($r['errors'] ?? 0),
                'full'        => (int) ($r['fulls'] ?? 0),
                'delta'       => (int) ($r['deltas'] ?? 0),
                'bytesTotal'  => (int) ($r['bytesTotal'] ?? 0),
                'bytesAvg'    => (int) round((float) ($r['bytesAvg'] ?? 0)),
                'durAvg'      => $r['durAvg'] !== null ? (int) round((float) $r['durAvg']) : null,
                'installs'    => (int) ($r['installs'] ?? 0),
                'cacheHitPct' => $builds > 0 ? (int) round(((int) ($r['cached'] ?? 0)) / $builds * 100) : 0,
            ];
        } catch (\Throwable $e) {
            return $blank;
        }
    }

    /**
     * Builds by weekday (0=Mon…6=Sun) × hour-of-day (0-23) over the last N days —
     * the load-pattern heatmap for capacity/monitoring. Returns a 7×24 matrix plus
     * the peak cell value for scaling.
     *
     * @return array{grid:array<int,array<int,int>>,max:int}
     */
    public static function eventHeatmap(int $days = 30): array
    {
        $days = max(1, min(120, $days));
        self::ensureSchema();
        $grid = [];
        for ($d = 0; $d < 7; $d++) $grid[$d] = array_fill(0, 24, 0);
        $max = 0;
        try {
            $rows = DB::FetchAll(
                "SELECT WEEKDAY(`created_at`) wd, HOUR(`created_at`) hr, COUNT(*) n
                   FROM `pp_build_events` WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL ? DAY)
                  GROUP BY wd, hr",
                [$days]
            );
            foreach ($rows as $r) {
                $wd = (int) $r['wd']; $hr = (int) $r['hr']; $n = (int) $r['n'];
                if ($wd < 0 || $wd > 6 || $hr < 0 || $hr > 23) continue;
                $grid[$wd][$hr] = $n;
                if ($n > $max) $max = $n;
            }
        } catch (\Throwable $e) { /* fail-soft — empty grid */ }
        return ['grid' => $grid, 'max' => $max];
    }

    // ── Enforcement ────────────────────────────────────────────────────────

    /**
     * May this instance build right now? Resolves its effective limits (override →
     * plan → global) and checks EVERY window; the tightest exceeded window wins.
     * Fail-open: allowed on any error and whenever no window is configured.
     *
     * @return array{ok:bool,window:string,limit:int,used:int,message:string,limits:array<string,int>,usage:array<string,int>}
     */
    public static function allow(string $instanceId): array
    {
        $limits = self::effectiveLimits($instanceId);
        $usage  = ['hour' => 0, 'day' => 0, 'week' => 0, 'month' => 0];
        $ok = ['ok' => true, 'window' => '', 'limit' => 0, 'used' => 0, 'message' => '', 'limits' => $limits, 'usage' => $usage];
        if ($instanceId === '') return $ok;

        foreach (self::WINDOWS as $w) {
            $cap = (int) ($limits[$w] ?? 0);
            if ($cap <= 0) continue;                          // 0 = unlimited for this window
            $used = self::usedInWindow($instanceId, $w);
            $usage[$w] = $used;
            $ok['usage'] = $usage;
            if ($used >= $cap) {
                return [
                    'ok'      => false,
                    'window'  => $w,
                    'limit'   => $cap,
                    'used'    => $used,
                    'limits'  => $limits,
                    'usage'   => $usage,
                    'message' => 'This installation has reached its build limit (' . $cap . ' per ' . $w
                        . '). It has built ' . $used . ' ' . self::windowLabel($w) . '; the count resets '
                        . self::resetLabel($w) . ', or contact support to raise it.',
                ];
            }
        }
        return $ok;
    }

    /** "this hour" / "today" / "this week" / "this month". */
    private static function windowLabel(string $window): string
    {
        return ['hour' => 'this hour', 'day' => 'today', 'week' => 'this week', 'month' => 'this month'][$window] ?? 'this period';
    }

    /** Human "resets …" phrase for a window. */
    private static function resetLabel(string $window): string
    {
        return [
            'hour'  => 'at the top of the next hour',
            'day'   => 'at midnight',
            'week'  => 'next Monday',
            'month' => 'on the 1st of next month',
        ][$window] ?? 'at the start of the next period';
    }

    /**
     * Record one completed build: the hourly + daily COUNTER rollups (fast, for
     * enforcement + quick totals) AND an append-only EVENT row (durable, for the
     * analytics/monitoring dashboard).
     *
     * @param array{kind?:string,files?:int,durationMs?:int,ok?:bool} $meta
     *        kind = 'full'|'delta'; durationMs = server build time; ok = succeeded.
     */
    public static function record(string $instanceId, int $bytes, bool $cached, array $meta = []): void
    {
        if ($instanceId === '') return;
        self::ensureSchema();
        try {
            // Daily rollup (day/week/month + totals). Atomic, race-free upsert.
            DB::Query(
                'INSERT INTO `pp_build_usage` (`instance_id`, `day`, `builds`, `bytes`, `last_at`)
                 VALUES (?, CURDATE(), 1, ?, NOW())
                 ON DUPLICATE KEY UPDATE `builds` = `builds` + 1,
                                         `bytes`  = `bytes` + VALUES(`bytes`),
                                         `last_at` = NOW()',
                [$instanceId, max(0, $bytes)]
            );
            // Hourly rollup (the hour window).
            DB::Query(
                'INSERT INTO `pp_build_usage_hourly` (`instance_id`, `hour`, `builds`)
                 VALUES (?, DATE_FORMAT(NOW(), \'%Y-%m-%d %H:00:00\'), 1)
                 ON DUPLICATE KEY UPDATE `builds` = `builds` + 1',
                [$instanceId]
            );
            // Durable per-build event (analytics + performance monitoring).
            DB::Query(
                'INSERT INTO `pp_build_events`
                    (`instance_id`, `kind`, `cached`, `ok`, `bytes`, `files`, `duration_ms`, `created_at`)
                 VALUES (?,?,?,?,?,?,?,NOW())',
                [
                    $instanceId,
                    (($meta['kind'] ?? '') === 'delta') ? 'delta' : 'full',
                    $cached ? 1 : 0,
                    (($meta['ok'] ?? true) ? 1 : 0),
                    max(0, $bytes),
                    max(0, (int) ($meta['files'] ?? 0)),
                    isset($meta['durationMs']) ? max(0, (int) $meta['durationMs']) : null,
                ]
            );
            // Keep the hourly table tiny — only the last ~3 days matter for the hour
            // window; prune opportunistically (~1% of writes) so it never grows.
            if (function_exists('random_int') && random_int(1, 100) === 1) {
                DB::Query('DELETE FROM `pp_build_usage_hourly` WHERE `hour` < DATE_SUB(NOW(), INTERVAL 3 DAY)');
            }
        } catch (\Throwable $e) {
            // Recording is telemetry, not correctness — swallow.
        }
    }

    // ── Back-compat shims (single-window callers / older UI) ───────────────

    /** Legacy: the effective DAY limit from the global defaults. */
    public static function limit(): int { return (int) (self::globalLimits()['day'] ?? 0); }

    /** Legacy: the configured single-window period. */
    public static function period(): string { return self::legacyPeriod(); }

    /** Legacy alias. */
    public static function cap(): int { return self::limit(); }

    public static function setLimit(int $limit): bool
    {
        // Route the legacy single limit onto the current legacy period's window.
        return self::setGlobalLimits([self::legacyPeriod() => max(0, $limit)]);
    }

    public static function setPeriod(string $period): bool
    {
        $p = in_array($period, ['day', 'week', 'month'], true) ? $period : 'day';
        try { return PlatformSettings::set(self::SETTING_PERIOD, ['value' => $p]); }
        catch (\Throwable $e) { return false; }
    }

    public static function setCap(int $cap): bool { return self::setLimit($cap); }
}
