<?php
declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

    /**
     * @param array<string,float> $ctrCurve bucket => expected ctr (0..1)
     * @return array{total:int,rows:list<array<string,mixed>>}
     */
    public function listIqqi(
        int $propertyId,
        string $rangeStartYmd,
        string $rangeEndYmd,
        string $device,
        string $country,
        int $minImpressions,
        float $posMin,
        float $posMax,
        array $ctrCurve,
        int $page,
        int $perPage,
        string $sort,
        string $dir
    ): array {
        return $this->listQueryBased(
            $propertyId,
            $rangeStartYmd,
            $rangeEndYmd,
            $device,
            $country,
            [
                'minImpr' => $minImpressions,
                'posMin' => $posMin,
                'posMax' => $posMax,
                'maxClicks' => null,
                'type' => 'iqqi',
            ],
            $ctrCurve,
            $page,
            $perPage,
            $sort,
            $dir
        );
    }

    /**
     * @param array<string,float> $ctrCurve
     * @return array{total:int,rows:list<array<string,mixed>>}
     */
    public function listCtrOpportunity(
        int $propertyId,
        string $rangeStartYmd,
        string $rangeEndYmd,
        string $device,
        string $country,
        int $minImpressions,
        array $ctrCurve,
        int $page,
        int $perPage,
        string $sort,
        string $dir
    ): array {
        return $this->listQueryBased(
            $propertyId,
            $rangeStartYmd,
            $rangeEndYmd,
            $device,
            $country,
            [
                'minImpr' => $minImpressions,
                'posMin' => null,
                'posMax' => null,
                'maxClicks' => null,
                'type' => 'ctr_opportunity',
            ],
            $ctrCurve,
            $page,
            $perPage,
            $sort,
            $dir
        );
    }

    /**
     * @param array<string,float> $ctrCurve
     * @return array{total:int,rows:list<array<string,mixed>>}
     */
    public function listTopicalGaps(
        int $propertyId,
        string $rangeStartYmd,
        string $rangeEndYmd,
        string $device,
        string $country,
        int $minImpressions,
        float $posMin,
        int $maxClicks,
        array $ctrCurve,
        int $page,
        int $perPage,
        string $sort,
        string $dir
    ): array {
        return $this->listQueryBased(
            $propertyId,
            $rangeStartYmd,
            $rangeEndYmd,
            $device,
            $country,
            [
                'minImpr' => $minImpressions,
                'posMin' => $posMin,
                'posMax' => null,
                'maxClicks' => $maxClicks,
                'type' => 'topical_gaps',
            ],
            $ctrCurve,
            $page,
            $perPage,
            $sort,
            $dir
        );
    }

    /**
     * @param array{minImpr:int,posMin:?float,posMax:?float,maxClicks:?int,type:string} $filter
     * @param array<string,float> $ctrCurve
     * @return array{total:int,rows:list<array<string,mixed>>}
     */
    private function listQueryBased(
        int $propertyId,
        string $rangeStartYmd,
        string $rangeEndYmd,
        string $device,
        string $country,
        array $filter,
        array $ctrCurve,
        int $page,
        int $perPage,
        string $sort,
        string $dir
    ): array {
        $tbl = $this->tablePrefix . 'gsc_daily_query';
        $offset = max(0, ($page - 1) * $perPage);

        $allowedSort = ['query', 'score', 'impressions', 'clicks', 'ctr', 'position', 'ctr_expected', 'ctr_gap'];
        $sort = in_array($sort, $allowedSort, true) ? $sort : 'score';
        $dir = strtoupper($dir) === 'ASC' ? 'ASC' : 'DESC';

        $curveExpr = $this->expectedCtrCaseExpr($ctrCurve);
        $bucketExpr = $this->bucketCaseExpr();

        $posMinSql = $filter['posMin'] === null ? '' : ' AND position BETWEEN :posMin AND :posMax ';
        $posMinParams = $filter['posMin'] === null ? [] : [':posMin' => $filter['posMin'], ':posMax' => (float) ($filter['posMax'] ?? $filter['posMin'])];

        $posOnlyMinSql = ($filter['posMin'] !== null && $filter['posMax'] === null) ? ' AND position >= :posMinOnly ' : '';
        $posOnlyMinParams = ($filter['posMin'] !== null && $filter['posMax'] === null) ? [':posMinOnly' => $filter['posMin']] : [];

        $maxClicksSql = $filter['maxClicks'] === null ? '' : ' AND clicks <= :maxClicks ';
        $maxClicksParams = $filter['maxClicks'] === null ? [] : [':maxClicks' => $filter['maxClicks']];

        $baseSql = "
            SELECT
                query,
                SUM(clicks) AS clicks,
                SUM(impressions) AS impressions,
                (SUM(clicks) / NULLIF(SUM(impressions), 0)) AS ctr,
                (SUM(position * impressions) / NULLIF(SUM(impressions), 0)) AS position,
                {$bucketExpr} AS bucket,
                {$curveExpr} AS ctr_expected,
                GREATEST(({$curveExpr} - (SUM(clicks) / NULLIF(SUM(impressions), 0))), 0) AS ctr_gap,
                0.0 AS score
            FROM {$tbl}
            WHERE property_id = :pid
              AND date BETWEEN :start AND :end
              AND device = :device
              AND country = :country
              AND impressions > 0
            GROUP BY query
            HAVING SUM(impressions) >= :minImpr
        ";

        $havingExtra = '';
        if ($filter['type'] === 'iqqi') {
            $havingExtra .= " AND position BETWEEN :posMin AND :posMax ";
            $havingExtra .= " AND ctr_gap > 0 ";
        } elseif ($filter['type'] === 'ctr_opportunity') {
            $havingExtra .= " AND ctr_gap > 0 ";
        } elseif ($filter['type'] === 'topical_gaps') {
            $havingExtra .= " AND position >= :posMinOnly ";
            $havingExtra .= " AND clicks <= :maxClicks ";
        }

        $scoredSql = "
            SELECT
                query,
                clicks,
                impressions,
                ctr,
                position,
                bucket,
                ctr_expected,
                ctr_gap,
                CASE
                    WHEN :type = 'iqqi' THEN (impressions * (10 - position) * ctr_gap)
                    WHEN :type = 'ctr_opportunity' THEN (impressions * ctr_gap)
                    WHEN :type = 'topical_gaps' THEN (impressions * GREATEST(position - 10, 0))
                    ELSE (impressions * ctr_gap)
                END AS score,
                CASE
                    WHEN :type = 'iqqi' THEN 'near_top,ctr_gap'
                    WHEN :type = 'ctr_opportunity' THEN 'ctr_gap'
                    WHEN :type = 'topical_gaps' THEN 'topical_gap'
                    ELSE ''
                END AS why
            FROM (
                {$baseSql}
                {$havingExtra}
            ) q
        ";

        $countSql = "SELECT COUNT(*) AS c FROM ({$scoredSql}) x";
        $countSt = $this->pdo->prepare($countSql);
        $countSt->execute(array_merge(
            [
                ':pid' => $propertyId,
                ':start' => $rangeStartYmd,
                ':end' => $rangeEndYmd,
                ':device' => $device,
                ':country' => $country,
                ':minImpr' => $filter['minImpr'],
                ':type' => $filter['type'],
            ],
            $posMinParams,
            $posOnlyMinParams,
            $maxClicksParams
        ));
        $total = (int) (($countSt->fetch(PDO::FETCH_ASSOC)['c'] ?? 0));

        $sql = $scoredSql . " ORDER BY {$sort} {$dir} LIMIT :lim OFFSET :off ";
        $st = $this->pdo->prepare($sql);
        $params = array_merge(
            [
                ':pid' => $propertyId,
                ':start' => $rangeStartYmd,
                ':end' => $rangeEndYmd,
                ':device' => $device,
                ':country' => $country,
                ':minImpr' => $filter['minImpr'],
                ':type' => $filter['type'],
            ],
            $posMinParams,
            $posOnlyMinParams,
            $maxClicksParams,
            []
        );

        // MariaDB can reject quoted LIMIT/OFFSET when emulate prepares is enabled, so bind as ints.
        foreach ($params as $k => $v) {
            $st->bindValue((string) $k, $v);
        }
        $st->bindValue(':lim', (int) $perPage, PDO::PARAM_INT);
        $st->bindValue(':off', (int) $offset, PDO::PARAM_INT);
        $st->execute();
        $rows = [];
        while ($r = $st->fetch(PDO::FETCH_ASSOC)) {
            if (!is_array($r)) continue;
            $why = array_values(array_filter(array_map('trim', explode(',', (string) ($r['why'] ?? '')))));
            $rows[] = [
                'query' => (string) ($r['query'] ?? ''),
                'clicks' => (int) ($r['clicks'] ?? 0),
                'impressions' => (int) ($r['impressions'] ?? 0),
                'ctr' => (float) ($r['ctr'] ?? 0.0),
                'position' => (float) ($r['position'] ?? 0.0),
                'bucket' => (string) ($r['bucket'] ?? ''),
                'ctr_expected' => (float) ($r['ctr_expected'] ?? 0.0),
                'ctr_gap' => (float) ($r['ctr_gap'] ?? 0.0),
                'score' => (float) ($r['score'] ?? 0.0),
                'rules' => $why,
            ];
        }

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

    /**
     * @param array<string,float> $ctrCurve
     */
    private function expectedCtrCaseExpr(array $ctrCurve): string
    {
        // Using the same bucket logic; curve values are inlined as constants.
        $v = static fn (string $b, float $d): float => (float) ($ctrCurve[$b] ?? $d);
        $b1 = $v('1', 0.25);
        $b2 = $v('2', 0.15);
        $b3 = $v('3', 0.10);
        $b45 = $v('4-5', 0.07);
        $b610 = $v('6-10', 0.04);
        $b1120 = $v('11-20', 0.02);
        $b2150 = $v('21-50', 0.01);
        $b51100 = $v('51-100', 0.005);

        return "
            CASE
                WHEN position <= 1.5 THEN {$b1}
                WHEN position <= 2.5 THEN {$b2}
                WHEN position <= 3.5 THEN {$b3}
                WHEN position <= 5.5 THEN {$b45}
                WHEN position <= 10.5 THEN {$b610}
                WHEN position <= 20.5 THEN {$b1120}
                WHEN position <= 50.5 THEN {$b2150}
                ELSE {$b51100}
            END
        ";
    }

    private function bucketCaseExpr(): string
    {
        return "
            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
        ";
    }

    /**
     * @param array<string,float> $ctrCurve
     * @return array{total:int,rows:list<array<string,mixed>>}
     */
    public function listLowCtrPages(
        int $propertyId,
        string $rangeStartYmd,
        string $rangeEndYmd,
        string $device,
        string $country,
        int $minImpressions,
        float $coeff,
        array $ctrCurve,
        int $page,
        int $perPage,
        string $sort,
        string $dir
    ): array {
        $tbl = $this->tablePrefix . 'gsc_daily_page';
        $offset = max(0, ($page - 1) * $perPage);

        $allowedSort = ['page', 'score', 'impressions', 'clicks', 'ctr', 'position', 'ctr_expected', 'ctr_gap'];
        $sort = in_array($sort, $allowedSort, true) ? $sort : 'score';
        $dir = strtoupper($dir) === 'ASC' ? 'ASC' : 'DESC';

        $curveExpr = $this->expectedCtrCaseExpr($ctrCurve);
        $bucketExpr = $this->bucketCaseExpr();

        $baseSql = "
            SELECT
                page,
                SUM(clicks) AS clicks,
                SUM(impressions) AS impressions,
                (SUM(clicks) / NULLIF(SUM(impressions), 0)) AS ctr,
                (SUM(position * impressions) / NULLIF(SUM(impressions), 0)) AS position,
                {$bucketExpr} AS bucket,
                {$curveExpr} AS ctr_expected,
                GREATEST(({$curveExpr} - (SUM(clicks) / NULLIF(SUM(impressions), 0))), 0) AS ctr_gap,
                (SUM(impressions) * GREATEST(({$curveExpr} - (SUM(clicks) / NULLIF(SUM(impressions), 0))), 0)) AS score
            FROM {$tbl}
            WHERE property_id = :pid
              AND date BETWEEN :start AND :end
              AND device = :device
              AND country = :country
              AND impressions > 0
            GROUP BY page
            HAVING SUM(impressions) >= :minImpr
               AND ctr < (ctr_expected * :coeff)
               AND ctr_gap > 0
        ";

        $countSql = "SELECT COUNT(*) AS c FROM ({$baseSql}) x";
        $countSt = $this->pdo->prepare($countSql);
        $countSt->execute([
            ':pid' => $propertyId,
            ':start' => $rangeStartYmd,
            ':end' => $rangeEndYmd,
            ':device' => $device,
            ':country' => $country,
            ':minImpr' => $minImpressions,
            ':coeff' => $coeff,
        ]);
        $total = (int) (($countSt->fetch(PDO::FETCH_ASSOC)['c'] ?? 0));

        $sql = $baseSql . " ORDER BY {$sort} {$dir} LIMIT :lim OFFSET :off ";
        $st = $this->pdo->prepare($sql);
        $st->bindValue(':pid', $propertyId, PDO::PARAM_INT);
        $st->bindValue(':start', $rangeStartYmd);
        $st->bindValue(':end', $rangeEndYmd);
        $st->bindValue(':device', $device);
        $st->bindValue(':country', $country);
        $st->bindValue(':minImpr', $minImpressions, PDO::PARAM_INT);
        $st->bindValue(':coeff', $coeff);
        $st->bindValue(':lim', (int) $perPage, PDO::PARAM_INT);
        $st->bindValue(':off', (int) $offset, PDO::PARAM_INT);
        $st->execute();

        $rows = [];
        while ($r = $st->fetch(PDO::FETCH_ASSOC)) {
            if (!is_array($r)) continue;
            $rows[] = [
                'page' => (string) ($r['page'] ?? ''),
                'clicks' => (int) ($r['clicks'] ?? 0),
                'impressions' => (int) ($r['impressions'] ?? 0),
                'ctr' => (float) ($r['ctr'] ?? 0.0),
                'position' => (float) ($r['position'] ?? 0.0),
                'bucket' => (string) ($r['bucket'] ?? ''),
                'ctr_expected' => (float) ($r['ctr_expected'] ?? 0.0),
                'ctr_gap' => (float) ($r['ctr_gap'] ?? 0.0),
                'score' => (float) ($r['score'] ?? 0.0),
                'rules' => ['low_ctr_page'],
            ];
        }

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

