<?php

if (!defined('ABSPATH')) {
    exit;
}

/**
 * Background aggregate SQL and legacy diagnostics. Request callers use the
 * published-only StatsRepository facade; its cache never invokes these scans.
 */
class ABJ_404_Solution_StatsReadRepository {

    /** @var int Max age for cached stats-periodic aggregates. */
    const PERIODIC_STATS_CACHE_TTL_SECONDS = 300;
    /** @var int Minimum interval before recalculating expensive stats aggregates. */
    const PERIODIC_STATS_REFRESH_COOLDOWN_SECONDS = 30;

    /** @var ABJ_404_Solution_DatabaseQueryInterface */
    private $dbCore;
    /** @var ABJ_404_Solution_LogsRepositoryInterface */
    private $logsRepo;
    /** @var ABJ_404_Solution_Logging */
    private $logger;
    /** @var ABJ_404_Solution_StatsRefreshLock */
    private $refreshLock;
    /** @var ABJ_404_Solution_PeriodicStatsRepository */
    private $periodicStatsRepository;

    /**
     * @param ABJ_404_Solution_DatabaseQueryInterface $dbCore
     * @param ABJ_404_Solution_LogsRepositoryInterface $logsRepo
     * @param ABJ_404_Solution_Logging $logging
     * @param ABJ_404_Solution_StatsRefreshLock $refreshLock
     */
    public function __construct(
        ABJ_404_Solution_DatabaseQueryInterface $dbCore,
        ABJ_404_Solution_LogsRepositoryInterface $logsRepo,
        $logging,
        ABJ_404_Solution_StatsRefreshLock $refreshLock
    ) {
        $this->dbCore = $dbCore;
        $this->logsRepo = $logsRepo;
        $this->logger = $logging;
        $this->refreshLock = $refreshLock;
        $this->periodicStatsRepository = new ABJ_404_Solution_PeriodicStatsRepository($dbCore, $logging);
    }

    /**
     * @param string $query
     * @param array<int|string, mixed> $valueParams
     * @return int
     */
    public function getStatsCount($query, array $valueParams) {
        if ($query == '') {
            return 0;
        }

        $result = $this->dbCore->queryAndGetResults($query, array('query_params' => $valueParams));

        if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) {
            return 0;
        }

        $rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
        if (empty($rows)) {
            $this->logger->debugMessage("getStatsCount returned no results for query: " . esc_html($query));
            return 0;
        }

        $first = $rows[0];
        if (is_array($first)) {
            $value = reset($first);
        } else {
            $value = $first;
        }
        return $this->toInt($value, 0);
    }

    /**
     * @param int $sinceTimestamp
     * @param string $notFoundDest
     * @return array{disp404:int,distinct404:int,visitors404:int,refer404:int,redirected:int,distinctredirected:int,distinctvisitors:int,distinctrefer:int}
     */
    public function getPeriodicStatsSummary($sinceTimestamp, $notFoundDest = '404') {
        return $this->periodicStatsRepository->getSummary($sinceTimestamp, $notFoundDest);
    }

    /**
     * @param string $notFoundDest
     * @return array{today:array<string,int>,month:array<string,int>,year:array<string,int>,all:array<string,int>}
     */
    public function getPeriodicStatsSummariesCached($notFoundDest = '404') {
        $now = abj_clock()->now();
        $thresholds = $this->computePeriodicThresholds($now);

        $emptyPayload = $this->emptyPeriodicPayload();
        $cacheKey = 'abj404_stats_periodic_v1_' . $this->currentBlogId() . '_' . md5(
            $notFoundDest . '|' . $thresholds['today'] . '|' . $thresholds['month'] . '|' . $thresholds['year']
        );
        $cached = function_exists('get_transient') ? get_transient($cacheKey) : null;

        $isCachedValid = (is_array($cached) && isset($cached['periods']) && is_array($cached['periods']));
        $cachedPeriods = $isCachedValid ? $this->normalizePeriodicPayload($cached['periods']) : $emptyPayload;
        $currentMaxLogId = -1;
        try {
            $currentMaxLogId = $this->toInt($this->logsRepo->getMaxLogId(), -1);
        } catch (Throwable $unused) { // allow-silent-catch: cache-key derivation; -1 means "no cached entry, recompute" which is the correct degraded behavior
            $currentMaxLogId = -1;
        }

        if ($isCachedValid) {
            $refreshedAt = $this->toInt($cached['refreshed_at'] ?? 0, 0);
            $ageSeconds = max(0, abj_clock()->now() - $refreshedAt);
            $cachedMaxLogId = $this->toInt($cached['max_log_id'] ?? -1, -1);
            if ($currentMaxLogId >= 0 && $cachedMaxLogId === $currentMaxLogId) {
                return $cachedPeriods;
            }
            if ($ageSeconds < self::PERIODIC_STATS_REFRESH_COOLDOWN_SECONDS) {
                return $cachedPeriods;
            }
        }

        $lockKey = 'stats-periodic:' . $cacheKey;
        $lockAcquired = $this->refreshLock->acquire($lockKey);
        if (!$lockAcquired && $isCachedValid) {
            return $cachedPeriods;
        }

        try {
            $result = $this->periodicStatsRepository->readSummariesWithRecovery($thresholds, $notFoundDest);

            if (function_exists('set_transient')) {
                set_transient(
                    $cacheKey,
                    array(
                        'refreshed_at' => abj_clock()->now(),
                        'max_log_id' => $currentMaxLogId,
                        'periods' => $result,
                    ),
                    self::PERIODIC_STATS_CACHE_TTL_SECONDS
                );
            }

            return $result;
        } finally {
            if ($lockAcquired) {
                $this->refreshLock->release($lockKey);
            }
        }
    }

    /** @return int */
    public function getEarliestLogTimestamp() {
        // allow-unbounded-select: MIN(timestamp) aggregate; returns a single row
        $query = 'SELECT min(timestamp) as timestamp FROM {wp_abj404_logsv2}';

        $result = $this->dbCore->queryAndGetResults($query);

        if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) {
            return -1;
        }

        $rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
        if (empty($rows)) {
            return -1;
        }

        $first = $rows[0];
        $value = is_array($first) ? reset($first) : $first;
        if ($value === null || $value === false || $value === '') {
            return -1;
        }
        return $this->toInt($value, -1);
    }

    /**
     * Count redirect rows grouped into match-confidence bands for the stats
     * page Match Confidence card.
     *
     * A NULL score is the "manual" band (no automated scoring took place);
     * scored rows fall into high/medium/low per
     * {@see ABJ_404_Solution_ScoreThresholds}. Disabled rows and rows with
     * status 0 are excluded. Routed through queryAndGetResults() so the
     * 5x SUM(CASE...) aggregate inherits the centralized 60s SELECT timeout
     * (the redirects table can be very large on busy sites).
     *
     * This is the single owner of the confidence-band SQL and thresholds:
     * the view layer asks for the counts and only formats them.
     *
     * @return array{high:int,medium:int,low:int,manual:int,avg:float|null,total:int}|null
     *   Band counts plus the rounded average score (avg is null when no scored
     *   rows exist), or null when the query timed out, errored, or returned no
     *   aggregate row so the caller can skip rendering the card.
     */
    public function getConfidenceBandCounts() {
        $redirectsTable = $this->dbCore->doTableNameReplacements('{wp_abj404_redirects}');

        $high = ABJ_404_Solution_ScoreThresholds::HIGH;
        $medium = ABJ_404_Solution_ScoreThresholds::MEDIUM;
        $sql = "SELECT
               SUM(CASE WHEN score IS NULL THEN 1 ELSE 0 END) AS manual_count,
               SUM(CASE WHEN score >= {$high} THEN 1 ELSE 0 END) AS high_count,
               SUM(CASE WHEN score >= {$medium} AND score < {$high} THEN 1 ELSE 0 END) AS medium_count,
               SUM(CASE WHEN score IS NOT NULL AND score < {$medium} THEN 1 ELSE 0 END) AS low_count,
               AVG(score) AS avg_score
             FROM `{$redirectsTable}`
             WHERE disabled = %d AND status != %d";

        $result = $this->dbCore->queryAndGetResults($sql, array('query_params' => array(0, 0)));
        if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) {
            return null;
        }
        $rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
        if (empty($rows) || !is_array($rows[0] ?? null)) {
            return null;
        }
        return $this->confidenceBandCountsFromRow($rows[0]);
    }

    /** @return array<string, mixed> */
    public function buildStatsDashboardSnapshotData() {
        $redirectsTable = $this->dbCore->doTableNameReplacements("{wp_abj404_redirects}");
        $aggregate = $this->readDashboardRedirectAggregate($redirectsTable);
        $thresholds = $this->computePeriodicThresholds(abj_clock()->now());
        $aggregate['periods'] = $this->periodicStatsRepository->readSummariesWithRecovery($thresholds, '404');
        return $aggregate;
    }

    /** @return array{redirects:array<string,int>,captured:array<string,int>,confidence:array{high:int,medium:int,low:int,manual:int,avg:float|null,total:int}} */
    public function readDashboardRedirectAggregate(string $redirectsTable, int $timeout = 4): array {
        $high = ABJ_404_Solution_ScoreThresholds::HIGH;
        $medium = ABJ_404_Solution_ScoreThresholds::MEDIUM;
        $sql = "SELECT
                SUM(CASE WHEN disabled = 0 AND code = 301 AND status = %d THEN 1 ELSE 0 END) AS auto301_count,
                SUM(CASE WHEN disabled = 0 AND code = 302 AND status = %d THEN 1 ELSE 0 END) AS auto302_count,
                SUM(CASE WHEN disabled = 0 AND code = 301 AND status = %d THEN 1 ELSE 0 END) AS manual301_count,
                SUM(CASE WHEN disabled = 0 AND code = 302 AND status = %d THEN 1 ELSE 0 END) AS manual302_count,
                SUM(CASE WHEN disabled = 1 AND (status = %d OR status = %d) THEN 1 ELSE 0 END) AS trashed_redirects_count,
                SUM(CASE WHEN disabled = 0 AND status = %d THEN 1 ELSE 0 END) AS captured_count,
                SUM(CASE WHEN disabled = 0 AND (status = %d OR status = %d) THEN 1 ELSE 0 END) AS ignored_count,
                SUM(CASE WHEN disabled = 1 AND (status = %d OR status = %d OR status = %d) THEN 1 ELSE 0 END) AS trashed_captured_count,
                SUM(CASE WHEN disabled = 0 AND status != %d AND score IS NULL THEN 1 ELSE 0 END) AS manual_count,
                SUM(CASE WHEN disabled = 0 AND status != %d AND score >= {$high} THEN 1 ELSE 0 END) AS high_count,
                SUM(CASE WHEN disabled = 0 AND status != %d AND score >= {$medium} AND score < {$high} THEN 1 ELSE 0 END) AS medium_count,
                SUM(CASE WHEN disabled = 0 AND status != %d AND score IS NOT NULL AND score < {$medium} THEN 1 ELSE 0 END) AS low_count,
                AVG(CASE WHEN disabled = 0 AND status != %d THEN score ELSE NULL END) AS avg_score FROM {$redirectsTable}";
        $result = $this->dbCore->queryAndGetResults($sql, array('timeout' => $timeout, 'query_params' => array(
            ABJ404_STATUS_AUTO, ABJ404_STATUS_AUTO, ABJ404_STATUS_MANUAL, ABJ404_STATUS_MANUAL,
            ABJ404_STATUS_AUTO, ABJ404_STATUS_MANUAL, ABJ404_STATUS_CAPTURED, ABJ404_STATUS_IGNORED,
            ABJ404_STATUS_LATER, ABJ404_STATUS_CAPTURED, ABJ404_STATUS_IGNORED, ABJ404_STATUS_LATER, 0, 0, 0, 0, 0,
        )));
        if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) {
            throw new RuntimeException('Dashboard redirect aggregate failed: '
                . (is_string($result['last_error'] ?? null) ? $result['last_error'] : 'timeout'), 503);
        }
        $rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
        if (empty($rows) || !is_array($rows[0] ?? null)) {
            throw new UnexpectedValueException('Dashboard redirect aggregate returned no row.', 502);
        }
        $row = $rows[0];
        return array('redirects' => array(
            'auto301' => $this->toInt($row['auto301_count'] ?? 0, 0),
            'auto302' => $this->toInt($row['auto302_count'] ?? 0, 0),
            'manual301' => $this->toInt($row['manual301_count'] ?? 0, 0),
            'manual302' => $this->toInt($row['manual302_count'] ?? 0, 0),
            'trashed' => $this->toInt($row['trashed_redirects_count'] ?? 0, 0),
        ), 'captured' => array(
            'captured' => $this->toInt($row['captured_count'] ?? 0, 0),
            'ignored' => $this->toInt($row['ignored_count'] ?? 0, 0),
            'trashed' => $this->toInt($row['trashed_captured_count'] ?? 0, 0),
        ),
            'confidence' => $this->confidenceBandCountsFromRow($row),
        );
    }

    /**
     * @param array<mixed,mixed> $row
     * @return array{high:int,medium:int,low:int,manual:int,avg:float|null,total:int}
     */
    private function confidenceBandCountsFromRow(array $row): array {
        $highCount = $this->toInt($row['high_count'] ?? 0, 0);
        $mediumCount = $this->toInt($row['medium_count'] ?? 0, 0);
        $lowCount = $this->toInt($row['low_count'] ?? 0, 0);
        $manualCount = $this->toInt($row['manual_count'] ?? 0, 0);
        $avgRaw = $row['avg_score'] ?? null;
        $avgScore = is_numeric($avgRaw) ? round((float)$avgRaw, 1) : null;

        return array(
            'high' => $highCount,
            'medium' => $mediumCount,
            'low' => $lowCount,
            'manual' => $manualCount,
            'avg' => $avgScore,
            'total' => $highCount + $mediumCount + $lowCount + $manualCount,
        );
    }

    /**
     * 'today'/'month'/'year' period-start boundaries, anchored to the WP
     * site's configured timezone (SiteTimezone) rather than PHP's implicit
     * default timezone -- matching the convention established by
     * RedirectScheduleTimezone and CronScheduler::scheduleDailyInWindowIfMissing().
     * getPeriodicStatsSummary() compares these thresholds against `timestamp`,
     * a true-UTC DB column, so deriving them with date()/mktime() (which read
     * and reconstruct calendar fields using PHP's default timezone) silently
     * picks the wrong local day/month/year start whenever the host's PHP
     * default timezone differs from the site's configured timezone.
     *
     * @param int $now Unix epoch (true UTC) to anchor the boundaries to.
     * @return array{today:int,month:int,year:int,all:int}
     */
    public function computePeriodicThresholds(int $now): array {
        $siteTimezone = ABJ_404_Solution_SiteTimezone::resolve();
        try {
            $siteNow = (new DateTimeImmutable('@' . $now))->setTimezone($siteTimezone);
            $today = (new DateTimeImmutable($siteNow->format('Y-m-d') . ' 00:00:00', $siteTimezone))->getTimestamp();
            $firstOfMonth = (new DateTimeImmutable($siteNow->format('Y-m') . '-01 00:00:00', $siteTimezone))->getTimestamp();
            $firstOfYear = (new DateTimeImmutable($siteNow->format('Y') . '-01-01 00:00:00', $siteTimezone))->getTimestamp();
        } catch (Exception $e) {
            // $siteNow->format() output is always a well-formed date string,
            // so this is unreachable in practice; degrade to UTC-anchored
            // boundaries rather than let a periodic stats read fail entirely.
            $this->logger->warn('StatsReadRepository: failed to compute site-timezone-anchored ' .
                'periodic thresholds, falling back to UTC: ' . $e->getMessage());
            $today = gmmktime(0, 0, 0, (int)gmdate('m', $now), (int)gmdate('d', $now), (int)gmdate('Y', $now));
            $firstOfMonth = gmmktime(0, 0, 0, (int)gmdate('m', $now), 1, (int)gmdate('Y', $now));
            $firstOfYear = gmmktime(0, 0, 0, 1, 1, (int)gmdate('Y', $now));
        }

        return array(
            'today' => (int)$today,
            'month' => (int)$firstOfMonth,
            'year' => (int)$firstOfYear,
            'all' => 0,
        );
    }

    /** @return array{disp404:int,distinct404:int,visitors404:int,refer404:int,redirected:int,distinctredirected:int,distinctvisitors:int,distinctrefer:int} */
    private function zeroPeriodicStats(): array {
        return array(
            'disp404' => 0,
            'distinct404' => 0,
            'visitors404' => 0,
            'refer404' => 0,
            'redirected' => 0,
            'distinctredirected' => 0,
            'distinctvisitors' => 0,
            'distinctrefer' => 0,
        );
    }

    /** @return array{today:array<string,int>,month:array<string,int>,year:array<string,int>,all:array<string,int>} */
    private function emptyPeriodicPayload(): array {
        $zero = $this->zeroPeriodicStats();
        return array(
            'today' => $zero,
            'month' => $zero,
            'year' => $zero,
            'all' => $zero,
        );
    }

    /**
     * @param mixed $periods
     * @return array{today:array<string,int>,month:array<string,int>,year:array<string,int>,all:array<string,int>}
     */
    private function normalizePeriodicPayload($periods): array {
        $payload = $this->emptyPeriodicPayload();
        if (!is_array($periods)) {
            return $payload;
        }
        foreach (array('today', 'month', 'year', 'all') as $periodKey) {
            if (isset($periods[$periodKey]) && is_array($periods[$periodKey])) {
                $payload[$periodKey] = $this->normalizePeriodicStats($periods[$periodKey]);
            }
        }
        return $payload;
    }

    /**
     * @param array<int|string, mixed> $stats
     * @return array{disp404:int,distinct404:int,visitors404:int,refer404:int,redirected:int,distinctredirected:int,distinctvisitors:int,distinctrefer:int}
     */
    private function normalizePeriodicStats(array $stats): array {
        $zero = $this->zeroPeriodicStats();
        foreach ($zero as $key => $unused) {
            $zero[$key] = array_key_exists($key, $stats) ? $this->toInt($stats[$key], 0) : 0;
        }
        return $zero;
    }

    /** @return int */
    private function currentBlogId(): int {
        return function_exists('get_current_blog_id') ? max(1, absint(get_current_blog_id())) : 1;
    }

    /** @param mixed $value @param int $default @return int */
    private function toInt($value, int $default): int {
        if ($value === null) {
            return $default;
        }
        if (is_scalar($value)) {
            return intval($value);
        }
        return $default;
    }
}