<?php
declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

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

    /**
     * @param list<string> $terms lowercased tokens (e.g. "what")
     * @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,
        array $terms,
        string $device,
        string $country,
        string $sort,
        string $dir,
        int $page,
        int $perPage
    ): array {
        $t = $this->table();
        $page = max(1, $page);
        $perPage = max(10, min(100, $perPage));
        $offset = ($page - 1) * $perPage;

        $terms = array_values(array_unique(array_filter(array_map(static fn ($s) => strtolower(trim((string) $s)), $terms), static fn ($s) => $s !== '')));
        if ($terms === []) {
            return ['total' => 0, 'rows' => []];
        }

        $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}",
        };

        $where = " WHERE property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ? ";
        $args = [$propertyId, $startDate, $endDate, $device, $country];

        // Match question term anywhere (simple + language-agnostic).
        $likes = [];
        foreach ($terms as $term) {
            $likes[] = "LOWER(`query`) LIKE ?";
            $args[] = '%' . $term . '%';
        }
        $where .= " AND (" . implode(' OR ', $likes) . ") ";

        $agg = "
            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
        ";

        $countStmt = $this->pdo->prepare("SELECT COUNT(1) FROM ( {$agg} ) x");
        $countStmt->execute($args);
        $total = (int) ($countStmt->fetchColumn() ?: 0);

        $stmt = $this->pdo->prepare(
            "SELECT * FROM ( {$agg} ) x
             ORDER BY {$orderExpr}
             LIMIT ? OFFSET ?"
        );
        $allArgs = array_merge($args, [$perPage, $offset]);
        foreach ($allArgs as $idx => $val) {
            $param = $idx + 1;
            if (is_int($val)) {
                $stmt->bindValue($param, $val, PDO::PARAM_INT);
            } else {
                $stmt->bindValue($param, $val);
            }
        }
        $stmt->execute();

        $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];
    }
}

