<?php
declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

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

    /**
     * @return array{total:int,rows:list<array{query:string,clicks:int,impressions:int,ctr:float,position:float,terms:int}>}
     */
    public function listForRange(
        int $propertyId,
        string $startDate,
        string $endDate,
        int $minTerms,
        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;
        $minTerms = max(1, min(10, $minTerms));

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

        // Terms count: number of spaces + 1, after light normalization.
        // IMPORTANT: cannot reference the SELECT alias (q) inside the same SELECT on MariaDB.
        $termsExpr = "(CHAR_LENGTH(TRIM(REPLACE(REPLACE(`query`, '\\t', ' '), '  ', ' '))) - CHAR_LENGTH(REPLACE(TRIM(REPLACE(REPLACE(`query`, '\\t', ' '), '  ', ' ')), ' ', '')) + 1)";

        $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,
                {$termsExpr} AS terms
            FROM {$t}
            WHERE property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ?
            GROUP BY q
            HAVING terms >= ?
        ";

        $countStmt = $this->pdo->prepare("SELECT COUNT(1) FROM ( {$agg} ) x");
        $countStmt->execute([$propertyId, $startDate, $endDate, $device, $country, $minTerms]);
        $total = (int) ($countStmt->fetchColumn() ?: 0);

        // LIMIT/OFFSET as ints (avoid MariaDB quoting edge cases).
        $stmt = $this->pdo->prepare(
            "SELECT * FROM ( {$agg} ) x
             ORDER BY {$orderExpr}
             LIMIT ? OFFSET ?"
        );
        $stmt->bindValue(1, $propertyId, PDO::PARAM_INT);
        $stmt->bindValue(2, $startDate);
        $stmt->bindValue(3, $endDate);
        $stmt->bindValue(4, $device);
        $stmt->bindValue(5, $country);
        $stmt->bindValue(6, $minTerms, PDO::PARAM_INT);
        $stmt->bindValue(7, $perPage, PDO::PARAM_INT);
        $stmt->bindValue(8, $offset, PDO::PARAM_INT);
        $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),
                'terms' => (int) ($r['terms'] ?? 0),
            ];
        }

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

