<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

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

    /**
     * @return list<array{page:string,clicks:int,impressions:int,ctr:float,position:float}>
     */
    public function topPagesForQuery(
        int $propertyId,
        string $startDate,
        string $endDate,
        string $query,
        string $device = 'all',
        string $country = 'all',
        int $limit = 10
    ): array {
        $query = trim($query);
        if ($propertyId < 1 || $query === '') {
            return [];
        }
        $limit = max(1, min(50, $limit));
        $t = $this->table();

        // gsc_daily_query_page contains device/country columns after M14 migration; default rows are (all,all).
        $stmt = $this->pdo->prepare(
            "SELECT
                page AS p,
                SUM(clicks) AS clicks,
                SUM(impressions) AS impressions,
                CASE WHEN SUM(impressions) > 0 THEN (SUM(clicks) / SUM(impressions)) ELSE 0 END AS ctr,
                CASE WHEN SUM(impressions) > 0 THEN (SUM(position * impressions) / SUM(impressions)) ELSE 0 END AS position
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ? AND `query` = ? AND device = ? AND country = ?
             GROUP BY p
             ORDER BY impressions DESC
             LIMIT {$limit}"
        );
        $stmt->execute([$propertyId, $startDate, $endDate, $query, $device, $country]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) continue;
            $p = (string) ($r['p'] ?? '');
            if ($p === '') continue;
            $out[] = [
                'page' => $p,
                'clicks' => (int) ($r['clicks'] ?? 0),
                'impressions' => (int) ($r['impressions'] ?? 0),
                'ctr' => (float) ($r['ctr'] ?? 0),
                'position' => (float) ($r['position'] ?? 0),
            ];
        }
        return $out;
    }

    /**
     * @return list<array{query:string,clicks:int,impressions:int,ctr:float,position:float}>
     */
    public function topQueriesForPage(
        int $propertyId,
        string $startDate,
        string $endDate,
        string $pageUrl,
        string $device = 'all',
        string $country = 'all',
        int $limit = 25
    ): array {
        $pageUrl = trim($pageUrl);
        if ($propertyId < 1 || $pageUrl === '') {
            return [];
        }
        $limit = max(1, min(100, $limit));
        $t = $this->table();

        $stmt = $this->pdo->prepare(
            "SELECT
                `query` AS q,
                SUM(clicks) AS clicks,
                SUM(impressions) AS impressions,
                CASE WHEN SUM(impressions) > 0 THEN (SUM(clicks) / SUM(impressions)) ELSE 0 END AS ctr,
                CASE WHEN SUM(impressions) > 0 THEN (SUM(position * impressions) / SUM(impressions)) ELSE 0 END AS position
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ? AND page = ? AND device = ? AND country = ?
             GROUP BY q
             ORDER BY impressions DESC
             LIMIT {$limit}"
        );
        $stmt->execute([$propertyId, $startDate, $endDate, $pageUrl, $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[] = [
                'query' => $q,
                'clicks' => (int) ($r['clicks'] ?? 0),
                'impressions' => (int) ($r['impressions'] ?? 0),
                'ctr' => (float) ($r['ctr'] ?? 0),
                'position' => (float) ($r['position'] ?? 0),
            ];
        }
        return $out;
    }

    /**
     * @param list<array{query:string,page:string,clicks:int,impressions:int,ctr:float,position:float,device?:string,country?:string}> $rows
     */
    public function upsertMany(int $propertyId, string $siteUrl, string $date, array $rows, string $device = 'all', string $country = 'all'): int
    {
        if ($rows === []) {
            return 0;
        }
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare(
            "INSERT INTO {$t} (property_id, site_url, date, `query`, page, device, country, clicks, impressions, ctr, position, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
             ON DUPLICATE KEY UPDATE clicks = VALUES(clicks),
                                     impressions = VALUES(impressions),
                                     ctr = VALUES(ctr),
                                     position = VALUES(position),
                                     device = VALUES(device),
                                     country = VALUES(country),
                                     property_id = VALUES(property_id),
                                     updated_at = VALUES(updated_at)"
        );

        $count = 0;
        foreach ($rows as $r) {
            $q = trim((string) ($r['query'] ?? ''));
            $p = trim((string) ($r['page'] ?? ''));
            if ($q === '' || $p === '') {
                continue;
            }
            $dev = strtolower(trim((string) ($r['device'] ?? $device)));
            $ctr3 = strtolower(trim((string) ($r['country'] ?? $country)));
            if ($dev === '') $dev = 'all';
            if ($ctr3 === '') $ctr3 = 'all';
            $stmt->execute([
                $propertyId > 0 ? $propertyId : null,
                $siteUrl,
                $date,
                $q,
                $p,
                $dev,
                $ctr3,
                (int) ($r['clicks'] ?? 0),
                (int) ($r['impressions'] ?? 0),
                (float) ($r['ctr'] ?? 0),
                (float) ($r['position'] ?? 0),
                $now,
            ]);
            $count++;
        }
        return $count;
    }
}

