<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

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

    /**
     * @param null|list<string> $onlyQueries When non-null, restrict to this exact query set (after trim/dedupe). Empty array yields no rows.
     * @return array{total:int,rows:list<array{query:string,clicks:int,impressions:int,ctr:float,position:float}>}
     */
    public function listForRange(
        int $propertyId,
        string $startDate,
        string $endDate,
        string $search,
        string $device,
        string $country,
        string $sort,
        string $dir,
        int $page,
        int $perPage,
        ?array $onlyQueries = null
    ): array {
        $t = $this->table();
        $page = max(1, $page);
        $perPage = max(5, min(100, $perPage));
        $offset = ($page - 1) * $perPage;

        $search = trim($search);
        $where = " WHERE property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ?";
        $argsBase = [$propertyId, $startDate, $endDate, $device, $country];
        if ($search !== '') {
            $where .= " AND `query` LIKE ?";
            $argsBase[] = '%' . $search . '%';
        }

        if ($onlyQueries !== null) {
            $qs = [];
            foreach ($onlyQueries as $q) {
                $q = trim((string) $q);
                if ($q !== '') {
                    $qs[$q] = true;
                }
            }
            $onlyList = array_keys($qs);
            if ($onlyList === []) {
                return ['total' => 0, 'rows' => []];
            }
            $ph = implode(',', array_fill(0, count($onlyList), '?'));
            $where .= " AND `query` IN ({$ph})";
            $argsBase = array_merge($argsBase, $onlyList);
        }

        $sort = in_array($sort, ['clicks', 'impressions', 'ctr', 'position', 'query'], true) ? $sort : 'clicks';
        $dir = strtoupper($dir) === 'ASC' ? 'ASC' : 'DESC';
        $orderExpr = match ($sort) {
            'query' => "q {$dir}",
            'impressions' => "impressions {$dir}",
            'ctr' => "ctr {$dir}",
            'position' => "position {$dir}",
            default => "clicks {$dir}",
        };

        $countStmt = $this->pdo->prepare(
            "SELECT COUNT(DISTINCT `query`) AS c
             FROM {$t}
             {$where}"
        );
        $countStmt->execute($argsBase);
        $total = (int) ($countStmt->fetchColumn() ?: 0);

        $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}
             GROUP BY q
             ORDER BY {$orderExpr}
             LIMIT {$perPage} OFFSET {$offset}"
        );
        $stmt->execute($argsBase);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $out[] = [
                'query' => (string) ($r['q'] ?? ''),
                'clicks' => (int) ($r['clicks'] ?? 0),
                'impressions' => (int) ($r['impressions'] ?? 0),
                'ctr' => (float) ($r['ctr'] ?? 0),
                'position' => (float) ($r['position'] ?? 0),
            ];
        }

        return ['total' => $total, 'rows' => $out];
    }

    /**
     * @param list<string> $queries
     * @return array<string,array{clicks:int,impressions:int,ctr:float,position:float}>
     */
    public function totalsForQueries(int $propertyId, string $startDate, string $endDate, string $device, string $country, array $queries): array
    {
        $queries = array_values(array_filter(array_map('trim', $queries), static fn ($q) => $q !== ''));
        if ($queries === []) {
            return [];
        }

        $t = $this->table();
        $placeholders = implode(',', array_fill(0, count($queries), '?'));
        // Totals for previous period are computed for the same (device,country) slice in the caller.
        $args = array_merge([$propertyId, $startDate, $endDate, $device, $country], $queries);

        $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 device = ? AND country = ? AND `query` IN ({$placeholders})
             GROUP BY q"
        );
        $stmt->execute($args);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $q = (string) ($r['q'] ?? '');
            if ($q === '') {
                continue;
            }
            $clicks = (int) ($r['clicks'] ?? 0);
            $impr = (int) ($r['impressions'] ?? 0);
            $out[$q] = [
                'clicks' => $clicks,
                'impressions' => $impr,
                'ctr' => (float) ($r['ctr'] ?? ($impr > 0 ? ($clicks / $impr) : 0)),
                'position' => (float) ($r['position'] ?? 0),
            ];
        }
        return $out;
    }
}

