<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

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

    /**
     * @return array{clicks:int,impressions:int,ctr:float,position:float}
     */
    public function totalsForRange(int $propertyId, string $startDate, string $endDate): array
    {
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT
                COALESCE(SUM(clicks), 0) AS clicks,
                COALESCE(SUM(impressions), 0) AS impressions,
                COALESCE(SUM(position * impressions), 0) AS pos_weighted
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ?"
        );
        $stmt->execute([$propertyId, $startDate, $endDate]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        $clicks = (int) ($row['clicks'] ?? 0);
        $impr = (int) ($row['impressions'] ?? 0);
        $posWeighted = (float) ($row['pos_weighted'] ?? 0);

        return [
            'clicks' => $clicks,
            'impressions' => $impr,
            'ctr' => $impr > 0 ? ($clicks / $impr) : 0.0,
            'position' => $impr > 0 ? ($posWeighted / $impr) : 0.0,
        ];
    }

    /**
     * Device slice from `gsc_daily_property_device` (synced without query row-cap skew).
     *
     * @return array{clicks:int,impressions:int,ctr:float,position:float}
     */
    public function totalsForRangeFromPropertyDevice(int $propertyId, string $startDate, string $endDate, string $deviceSlug): array
    {
        $d = strtolower(trim($deviceSlug));
        if ($d === '' || $d === 'all') {
            return $this->totalsForRange($propertyId, $startDate, $endDate);
        }
        $t = '`' . str_replace('`', '``', $this->tablePrefix . 'gsc_daily_property_device') . '`';
        $stmt = $this->pdo->prepare(
            "SELECT
                COALESCE(SUM(clicks), 0) AS clicks,
                COALESCE(SUM(impressions), 0) AS impressions,
                COALESCE(SUM(position * impressions), 0) AS pos_weighted
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ? AND device = ?"
        );
        $stmt->execute([$propertyId, $startDate, $endDate, $d]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        $clicks = (int) ($row['clicks'] ?? 0);
        $impr = (int) ($row['impressions'] ?? 0);
        $posWeighted = (float) ($row['pos_weighted'] ?? 0);

        return [
            'clicks' => $clicks,
            'impressions' => $impr,
            'ctr' => $impr > 0 ? ($clicks / $impr) : 0.0,
            'position' => $impr > 0 ? ($posWeighted / $impr) : 0.0,
        ];
    }

    /**
     * Country slice from `gsc_daily_property_country`.
     *
     * @return array{clicks:int,impressions:int,ctr:float,position:float}
     */
    public function totalsForRangeFromPropertyCountry(int $propertyId, string $startDate, string $endDate, string $countryCode): array
    {
        $c = strtolower(trim($countryCode));
        if ($c === '' || $c === 'all') {
            return $this->totalsForRange($propertyId, $startDate, $endDate);
        }
        $t = '`' . str_replace('`', '``', $this->tablePrefix . 'gsc_daily_property_country') . '`';
        $stmt = $this->pdo->prepare(
            "SELECT
                COALESCE(SUM(clicks), 0) AS clicks,
                COALESCE(SUM(impressions), 0) AS impressions,
                COALESCE(SUM(position * impressions), 0) AS pos_weighted
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ? AND country = ?"
        );
        $stmt->execute([$propertyId, $startDate, $endDate, $c]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        $clicks = (int) ($row['clicks'] ?? 0);
        $impr = (int) ($row['impressions'] ?? 0);
        $posWeighted = (float) ($row['pos_weighted'] ?? 0);

        return [
            'clicks' => $clicks,
            'impressions' => $impr,
            'ctr' => $impr > 0 ? ($clicks / $impr) : 0.0,
            'position' => $impr > 0 ? ($posWeighted / $impr) : 0.0,
        ];
    }

    /**
     * Legacy: query facts for device/country. Prefer {@see totalsForRangeFromPropertyDevice} / {@see totalsForRangeFromPropertyCountry} or live API for combined filters.
     *
     * @return array{clicks:int,impressions:int,ctr:float,position:float}
     */
    public function totalsForRangeFromQueryFacts(int $propertyId, string $startDate, string $endDate, string $device, string $country): array
    {
        if ($device === 'all' && $country === 'all') {
            return $this->totalsForRange($propertyId, $startDate, $endDate);
        }
        if ($device !== 'all' && $country === 'all') {
            return $this->totalsForRangeFromPropertyDevice($propertyId, $startDate, $endDate, $device);
        }
        if ($device === 'all' && $country !== 'all') {
            return $this->totalsForRangeFromPropertyCountry($propertyId, $startDate, $endDate, $country);
        }
        // device+country: cannot derive from a single pre-agg table; caller should use GSC API (see GscKpiViewService).
        return [
            'clicks' => 0,
            'impressions' => 0,
            'ctr' => 0.0,
            'position' => 0.0,
        ];
    }

    /**
     * Inclusive Y-m-d list from $start to $end (empty if invalid).
     *
     * @return list<string>
     */
    private function enumerateDatesInclusive(string $start, string $end): array
    {
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $start) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $end) || $start > $end) {
            return [];
        }
        $out = [];
        for ($d = $start; $d <= $end; $d = date('Y-m-d', strtotime($d . ' +1 day'))) {
            $out[] = $d;
        }
        return $out;
    }

    /**
     * @return list<array{date:string,clicks:int,impressions:int,ctr:float,position:float}>
     */
    public function dailySeriesForRange(int $propertyId, string $startDate, string $endDate): array
    {
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT
                date,
                COALESCE(clicks, 0) AS clicks,
                COALESCE(impressions, 0) AS impressions,
                COALESCE(ctr, 0) AS ctr,
                COALESCE(position, 0) AS position
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ?
             ORDER BY date ASC"
        );
        $stmt->execute([$propertyId, $startDate, $endDate]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $byDate = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $d = (string) ($r['date'] ?? '');
            if ($d === '' || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $d)) {
                continue;
            }
            $byDate[$d] = [
                'date' => $d,
                'clicks' => (int) ($r['clicks'] ?? 0),
                'impressions' => (int) ($r['impressions'] ?? 0),
                'ctr' => (float) ($r['ctr'] ?? 0),
                'position' => (float) ($r['position'] ?? 0),
            ];
        }
        $out = [];
        foreach ($this->enumerateDatesInclusive($startDate, $endDate) as $d) {
            if (isset($byDate[$d])) {
                $out[] = $byDate[$d];
            } else {
                $out[] = [
                    'date' => $d,
                    'clicks' => 0,
                    'impressions' => 0,
                    'ctr' => 0.0,
                    'position' => 0.0,
                ];
            }
        }
        return $out;
    }

    /**
     * @return list<array{date:string,clicks:int,impressions:int,ctr:float,position:float}>
     */
    public function dailySeriesForRangeFromPropertyDevice(
        int $propertyId,
        string $startDate,
        string $endDate,
        string $deviceSlug
    ): array {
        $d = strtolower(trim($deviceSlug));
        if ($d === '' || $d === 'all') {
            return $this->dailySeriesForRange($propertyId, $startDate, $endDate);
        }
        $t = '`' . str_replace('`', '``', $this->tablePrefix . 'gsc_daily_property_device') . '`';
        $stmt = $this->pdo->prepare(
            "SELECT
                date,
                COALESCE(clicks, 0) AS clicks,
                COALESCE(impressions, 0) AS impressions,
                COALESCE(ctr, 0) AS ctr,
                COALESCE(position, 0) AS position
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ? AND device = ?
             ORDER BY date ASC"
        );
        $stmt->execute([$propertyId, $startDate, $endDate, $d]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return $this->fillDailySeriesGaps($rows, $startDate, $endDate);
    }

    /**
     * @return list<array{date:string,clicks:int,impressions:int,ctr:float,position:float}>
     */
    public function dailySeriesForRangeFromPropertyCountry(
        int $propertyId,
        string $startDate,
        string $endDate,
        string $countryCode
    ): array {
        $c = strtolower(trim($countryCode));
        if ($c === '' || $c === 'all') {
            return $this->dailySeriesForRange($propertyId, $startDate, $endDate);
        }
        $t = '`' . str_replace('`', '``', $this->tablePrefix . 'gsc_daily_property_country') . '`';
        $stmt = $this->pdo->prepare(
            "SELECT
                date,
                COALESCE(clicks, 0) AS clicks,
                COALESCE(impressions, 0) AS impressions,
                COALESCE(ctr, 0) AS ctr,
                COALESCE(position, 0) AS position
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ? AND country = ?
             ORDER BY date ASC"
        );
        $stmt->execute([$propertyId, $startDate, $endDate, $c]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return $this->fillDailySeriesGaps($rows, $startDate, $endDate);
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return list<array{date:string,clicks:int,impressions:int,ctr:float,position:float}>
     */
    private function fillDailySeriesGaps(array $rows, string $startDate, string $endDate): array
    {
        $byDate = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $d = (string) ($r['date'] ?? '');
            if ($d === '' || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $d)) {
                continue;
            }
            $byDate[$d] = [
                'date' => $d,
                'clicks' => (int) ($r['clicks'] ?? 0),
                'impressions' => (int) ($r['impressions'] ?? 0),
                'ctr' => (float) ($r['ctr'] ?? 0),
                'position' => (float) ($r['position'] ?? 0),
            ];
        }
        $out = [];
        foreach ($this->enumerateDatesInclusive($startDate, $endDate) as $d) {
            if (isset($byDate[$d])) {
                $out[] = $byDate[$d];
            } else {
                $out[] = [
                    'date' => $d,
                    'clicks' => 0,
                    'impressions' => 0,
                    'ctr' => 0.0,
                    'position' => 0.0,
                ];
            }
        }
        return $out;
    }

    /**
     * Legacy: query-fact series; for device or country only, uses property device/country tables.
     *
     * @return list<array{date:string,clicks:int,impressions:int,ctr:float,position:float}>
     */
    public function dailySeriesForRangeFromQueryFacts(int $propertyId, string $startDate, string $endDate, string $device, string $country): array
    {
        if ($device === 'all' && $country === 'all') {
            return $this->dailySeriesForRange($propertyId, $startDate, $endDate);
        }
        if ($device !== 'all' && $country === 'all') {
            return $this->dailySeriesForRangeFromPropertyDevice($propertyId, $startDate, $endDate, $device);
        }
        if ($device === 'all' && $country !== 'all') {
            return $this->dailySeriesForRangeFromPropertyCountry($propertyId, $startDate, $endDate, $country);
        }
        $out = [];
        foreach ($this->enumerateDatesInclusive($startDate, $endDate) as $d) {
            $out[] = [
                'date' => $d,
                'clicks' => 0,
                'impressions' => 0,
                'ctr' => 0.0,
                'position' => 0.0,
            ];
        }
        return $out;
    }
}

