<?php
declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

    /**
     * @return array<string,float> bucket => expected_ctr (0..1)
     */
    public function curveForRange(
        int $propertyId,
        string $rangeStartYmd,
        string $rangeEndYmd,
        string $device,
        string $country
    ): array {
        $tbl = $this->tablePrefix . 'gsc_daily_query';

        $sql = "
            SELECT bucket, (SUM(clicks) / NULLIF(SUM(impressions), 0)) AS ctr
            FROM (
                SELECT
                    CASE
                        WHEN position <= 1.5 THEN '1'
                        WHEN position <= 2.5 THEN '2'
                        WHEN position <= 3.5 THEN '3'
                        WHEN position <= 5.5 THEN '4-5'
                        WHEN position <= 10.5 THEN '6-10'
                        WHEN position <= 20.5 THEN '11-20'
                        WHEN position <= 50.5 THEN '21-50'
                        ELSE '51-100'
                    END AS bucket,
                    clicks,
                    impressions
                FROM {$tbl}
                WHERE property_id = :pid
                  AND date BETWEEN :start AND :end
                  AND device = :device
                  AND country = :country
                  AND impressions > 0
            ) t
            GROUP BY bucket
        ";

        $st = $this->pdo->prepare($sql);
        $st->execute([
            ':pid' => $propertyId,
            ':start' => $rangeStartYmd,
            ':end' => $rangeEndYmd,
            ':device' => $device,
            ':country' => $country,
        ]);

        $out = [];
        while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
            if (!is_array($row)) {
                continue;
            }
            $bucket = (string) ($row['bucket'] ?? '');
            if ($bucket === '') {
                continue;
            }
            $ctr = (float) ($row['ctr'] ?? 0.0);
            $out[$bucket] = max(0.0, min(1.0, $ctr));
        }

        // Ensure stable bucket order + fallback defaults if missing (light-touch, avoids division by zero).
        $defaults = [
            '1' => 0.25,
            '2' => 0.15,
            '3' => 0.10,
            '4-5' => 0.07,
            '6-10' => 0.04,
            '11-20' => 0.02,
            '21-50' => 0.01,
            '51-100' => 0.005,
        ];
        foreach ($defaults as $b => $d) {
            if (!array_key_exists($b, $out)) {
                $out[$b] = $d;
            }
        }

        return $out;
    }
}

