<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

final class GscDashboardWidgetsRepository
{
    public function __construct(
        private PDO $pdo,
        private string $tablePrefix
    ) {}

    private function qTable(): string
    {
        return '`' . str_replace('`', '``', $this->tablePrefix . 'gsc_daily_query') . '`';
    }

    private function propDeviceTable(): string
    {
        return '`' . str_replace('`', '``', $this->tablePrefix . 'gsc_daily_property_device') . '`';
    }

    private function propCountryTable(): string
    {
        return '`' . str_replace('`', '``', $this->tablePrefix . 'gsc_daily_property_country') . '`';
    }

    /**
     * @return array<string,int> device => clicks
     */
    public function clicksByDevice(int $propertyId, string $startDate, string $endDate): array
    {
        $t = $this->propDeviceTable();
        $stmt = $this->pdo->prepare(
            "SELECT device, SUM(clicks) AS clicks
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ?
             GROUP BY device"
        );
        $stmt->execute([$propertyId, $startDate, $endDate]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) continue;
            $dev = strtolower((string) ($r['device'] ?? ''));
            if ($dev === '') continue;
            $out[$dev] = (int) ($r['clicks'] ?? 0);
        }
        return $out;
    }

    /**
     * @return list<array{country:string,clicks:int,share:float}>
     */
    public function topCountriesByClicks(int $propertyId, string $startDate, string $endDate, int $limit = 5): array
    {
        $limit = max(1, min(20, $limit));
        $t = $this->propCountryTable();
        $totalStmt = $this->pdo->prepare(
            "SELECT SUM(clicks) AS clicks
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ?"
        );
        $totalStmt->execute([$propertyId, $startDate, $endDate]);
        $totalClicks = (int) ($totalStmt->fetchColumn() ?: 0);

        $stmt = $this->pdo->prepare(
            "SELECT country, SUM(clicks) AS clicks
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ?
             GROUP BY country
             ORDER BY clicks DESC
             LIMIT {$limit}"
        );
        $stmt->execute([$propertyId, $startDate, $endDate]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) continue;
            $c = strtolower((string) ($r['country'] ?? ''));
            if ($c === '') continue;
            $clicks = (int) ($r['clicks'] ?? 0);
            $share = $totalClicks > 0 ? ($clicks / $totalClicks) : 0.0;
            $out[] = ['country' => $c, 'clicks' => $clicks, 'share' => $share];
        }
        return $out;
    }

    /**
     * Keyword distribution by average position buckets.
     *
     * @return array{top3:int,top10:int,top50:int,top100:int,total:int}
     */
    public function keywordDistribution(int $propertyId, string $startDate, string $endDate): array
    {
        $t = $this->qTable();
        // Use query-only rows (device=all,country=all) to avoid double counting.
        $stmt = $this->pdo->prepare(
            "SELECT
                `query` AS q,
                CASE WHEN SUM(impressions) > 0 THEN (SUM(position * impressions) / SUM(impressions)) ELSE 0 END AS pos
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ? AND device = 'all' AND country = 'all'
             GROUP BY q"
        );
        $stmt->execute([$propertyId, $startDate, $endDate]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $top3 = 0; $top10 = 0; $top50 = 0; $top100 = 0; $total = 0;
        foreach ($rows as $r) {
            if (!is_array($r)) continue;
            $q = (string) ($r['q'] ?? '');
            if ($q === '') continue;
            $pos = (float) ($r['pos'] ?? 0);
            $total++;
            if ($pos <= 3) $top3++;
            if ($pos <= 10) $top10++;
            if ($pos <= 50) $top50++;
            if ($pos <= 100) $top100++;
        }
        return ['top3' => $top3, 'top10' => $top10, 'top50' => $top50, 'top100' => $top100, 'total' => $total];
    }

    /**
     * Ranking-trend widgets (volatility, trend direction, moved up/down).
     *
     * @return array{
     *   volatility_score:int,
     *   trend:string,
     *   moved_up:int,
     *   moved_down:int
     * }
     */
    public function rankingTrend(
        int $propertyId,
        string $curStart,
        string $curEnd,
        string $prevStart,
        string $prevEnd,
        string $device,
        string $country,
        int $minImpressions = 10
    ): array {
        $minImpressions = max(0, $minImpressions);
        $t = $this->qTable();

        $fetch = function (string $start, string $end) use ($t, $propertyId, $device, $country): array {
            $stmt = $this->pdo->prepare(
                "SELECT
                    `query` AS q,
                    SUM(impressions) AS impr,
                    CASE WHEN SUM(impressions) > 0 THEN (SUM(position * impressions) / SUM(impressions)) ELSE 0 END AS pos
                 FROM {$t}
                 WHERE property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ?
                 GROUP BY q"
            );
            $stmt->execute([$propertyId, $start, $end, $device, $country]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            $out = [];
            foreach ($rows as $r) {
                if (!is_array($r)) continue;
                $q = (string) ($r['q'] ?? '');
                if ($q === '') continue;
                $out[$q] = [
                    'impr' => (int) ($r['impr'] ?? 0),
                    'pos' => (float) ($r['pos'] ?? 0),
                ];
            }
            return $out;
        };

        $cur = $fetch($curStart, $curEnd);
        $prev = $fetch($prevStart, $prevEnd);

        $eps = 0.1;
        $movedUp = 0;
        $movedDown = 0;

        $wSum = 0.0;
        $vSum = 0.0;
        $trendScore = 0.0;

        foreach ($cur as $q => $c) {
            if (!isset($prev[$q])) continue;
            $cImpr = (int) ($c['impr'] ?? 0);
            $pImpr = (int) ($prev[$q]['impr'] ?? 0);
            if ($cImpr < $minImpressions || $pImpr < $minImpressions) {
                continue;
            }
            $cPos = (float) ($c['pos'] ?? 0);
            $pPos = (float) ($prev[$q]['pos'] ?? 0);
            $delta = $cPos - $pPos; // negative = improved
            $abs = abs($delta);

            // Volatility: impression-weighted absolute movement.
            $w = (float) $cImpr;
            $wSum += $w;
            $vSum += $abs * $w;

            // Moved up/down counts.
            if ($delta < -$eps) $movedUp++;
            if ($delta > $eps) $movedDown++;

            // Trend direction score with ranking-tier weighting (current period).
            $tierW = 0.5;
            if ($cPos > 0 && $cPos <= 3) $tierW = 4.0;
            elseif ($cPos <= 10) $tierW = 3.0;
            elseif ($cPos <= 50) $tierW = 2.0;
            elseif ($cPos <= 100) $tierW = 1.0;
            $sign = $delta < 0 ? 1.0 : ($delta > 0 ? -1.0 : 0.0);
            $trendScore += $sign * $w * $tierW;
        }

        $vol = $wSum > 0 ? ($vSum / $wSum) : 0.0;
        $volScore = (int) max(0, min(100, round(($vol / 10.0) * 100.0))); // cap at 10 positions => 100

        $trend = 'neutral';
        if ($trendScore > 0.0001) $trend = 'positive';
        if ($trendScore < -0.0001) $trend = 'negative';

        return [
            'volatility_score' => $volScore,
            'trend' => $trend,
            'moved_up' => $movedUp,
            'moved_down' => $movedDown,
        ];
    }
}

