<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

final class GscOpportunitiesRepository
{
    // Rule defaults (v1). Keep in sync with documentation/opportunities-rules.md
    private const LOW_CTR_IMPRESSIONS_MIN = 1000;
    private const LOW_CTR_MAX = 0.01;
    private const NEAR_TOP_IMPRESSIONS_MIN = 500;
    private const NEAR_TOP_POS_MIN = 3.0;
    private const NEAR_TOP_POS_MAX = 10.0;

    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,score:float,rules:list<string>
     * }>}
     */
    public function listForRange(
        int $propertyId,
        string $startDate,
        string $endDate,
        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;

        // We pre-aggregate and compute score in SQL to keep pagination stable and performant.
        $lowCtrImprMin = (int) self::LOW_CTR_IMPRESSIONS_MIN;
        $lowCtrMax = (float) self::LOW_CTR_MAX;
        $nearTopImprMin = (int) self::NEAR_TOP_IMPRESSIONS_MIN;
        $nearTopPosMin = (float) self::NEAR_TOP_POS_MIN;
        $nearTopPosMax = (float) self::NEAR_TOP_POS_MAX;

        $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,
                GREATEST(
                    (SUM(impressions) * GREATEST(0, (0.02 - (CASE WHEN SUM(impressions) > 0 THEN (SUM(clicks) / SUM(impressions)) ELSE 0 END)))),
                    (SUM(impressions) * GREATEST(0, ((10 - (CASE WHEN SUM(impressions) > 0 THEN (SUM(position * impressions) / SUM(impressions)) ELSE 0 END)) / 10)))
                ) AS score
            FROM {$t}
            WHERE property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ?
            GROUP BY q
        ";

        // Filter only rows that match at least one rule.
        $having = "
            HAVING (
                (impressions >= {$lowCtrImprMin} AND ctr <= {$lowCtrMax})
                OR
                (impressions >= {$nearTopImprMin} AND position >= {$nearTopPosMin} AND position <= {$nearTopPosMax})
            )
        ";

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

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

        $stmt = $this->pdo->prepare(
            "SELECT * FROM ( {$agg} {$having} ) x
             ORDER BY {$orderExpr}
             LIMIT {$perPage} OFFSET {$offset}"
        );
        $stmt->execute([$propertyId, $startDate, $endDate, $device, $country]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $impr = (int) ($r['impressions'] ?? 0);
            $ctr = (float) ($r['ctr'] ?? 0);
            $pos = (float) ($r['position'] ?? 0);
            $rules = [];
            if ($impr >= self::LOW_CTR_IMPRESSIONS_MIN && $ctr <= self::LOW_CTR_MAX) {
                $rules[] = 'low_ctr';
            }
            if ($impr >= self::NEAR_TOP_IMPRESSIONS_MIN && $pos >= self::NEAR_TOP_POS_MIN && $pos <= self::NEAR_TOP_POS_MAX) {
                $rules[] = 'near_top';
            }

            $out[] = [
                'query' => (string) ($r['q'] ?? ''),
                'clicks' => (int) ($r['clicks'] ?? 0),
                'impressions' => $impr,
                'ctr' => $ctr,
                'position' => $pos,
                'score' => (float) ($r['score'] ?? 0),
                'rules' => $rules,
            ];
        }

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

