<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

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

    /**
     * @param array{min_impressions:int,clicks_drop_pct:float,impr_drop_pct:float,position_worsen:float} $cfg
     * @return array{total:int,rows:list<array{
     *   query:string,
     *   cur_clicks:int,cur_impressions:int,cur_ctr:float,cur_position:float,
     *   prev_clicks:int,prev_impressions:int,prev_ctr:float,prev_position:float,
     *   d_clicks:float,d_impr:float,d_pos:float,
     *   flags:list<string>
     * }>}
     */
    public function listForRange(
        int $propertyId,
        string $curStart,
        string $curEnd,
        string $prevStart,
        string $prevEnd,
        string $device,
        string $country,
        array $cfg,
        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;

        $minImpr = max(0, (int) ($cfg['min_impressions'] ?? 0));
        $thrClicks = max(0.0, (float) ($cfg['clicks_drop_pct'] ?? 0.0));
        $thrImpr = max(0.0, (float) ($cfg['impr_drop_pct'] ?? 0.0));
        $thrPos = max(0.0, (float) ($cfg['position_worsen'] ?? 0.0));

        $cur = "
            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 property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ?
            GROUP BY q
        ";
        $prev = "
            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 property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ?
            GROUP BY q
        ";

        $q = "
            SELECT
                c.q AS q,
                c.clicks AS cur_clicks,
                c.impressions AS cur_impressions,
                c.ctr AS cur_ctr,
                c.position AS cur_position,
                COALESCE(p.clicks, 0) AS prev_clicks,
                COALESCE(p.impressions, 0) AS prev_impressions,
                COALESCE(p.ctr, 0) AS prev_ctr,
                COALESCE(p.position, 0) AS prev_position,
                CASE WHEN COALESCE(p.clicks, 0) > 0 THEN ((c.clicks - p.clicks) / p.clicks) ELSE 0 END AS d_clicks,
                CASE WHEN COALESCE(p.impressions, 0) > 0 THEN ((c.impressions - p.impressions) / p.impressions) ELSE 0 END AS d_impr,
                (c.position - COALESCE(p.position, c.position)) AS d_pos
            FROM ( {$cur} ) c
            LEFT JOIN ( {$prev} ) p ON p.q = c.q
            WHERE c.impressions >= {$minImpr}
              AND (
                (COALESCE(p.clicks, 0) > 0 AND ((c.clicks - p.clicks) / p.clicks) <= -" . $thrClicks . ")
                OR
                (COALESCE(p.impressions, 0) > 0 AND ((c.impressions - p.impressions) / p.impressions) <= -" . $thrImpr . ")
                OR
                ((c.position - COALESCE(p.position, c.position)) >= " . $thrPos . ")
              )
        ";

        $sort = in_array($sort, ['d_clicks', 'd_impr', 'd_pos', 'cur_clicks', 'cur_impressions', 'cur_ctr', 'cur_position', 'query'], true) ? $sort : 'd_clicks';
        $dir = strtoupper($dir) === 'ASC' ? 'ASC' : 'DESC';
        $orderExpr = match ($sort) {
            'query' => "q {$dir}",
            'd_impr' => "d_impr {$dir}",
            'd_pos' => "d_pos {$dir}",
            'cur_clicks' => "cur_clicks {$dir}",
            'cur_impressions' => "cur_impressions {$dir}",
            'cur_ctr' => "cur_ctr {$dir}",
            'cur_position' => "cur_position {$dir}",
            default => "d_clicks {$dir}",
        };

        $countStmt = $this->pdo->prepare("SELECT COUNT(1) FROM ( {$q} ) x");
        $countStmt->execute([$propertyId, $curStart, $curEnd, $device, $country, $propertyId, $prevStart, $prevEnd, $device, $country]);
        $total = (int) ($countStmt->fetchColumn() ?: 0);

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

        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) continue;
            $dc = (float) ($r['d_clicks'] ?? 0);
            $di = (float) ($r['d_impr'] ?? 0);
            $dp = (float) ($r['d_pos'] ?? 0);
            $flags = [];
            if ((int) ($r['prev_clicks'] ?? 0) > 0 && $dc <= -$thrClicks) $flags[] = 'clicks';
            if ((int) ($r['prev_impressions'] ?? 0) > 0 && $di <= -$thrImpr) $flags[] = 'impressions';
            if ($dp >= $thrPos) $flags[] = 'position';

            $out[] = [
                'query' => (string) ($r['q'] ?? ''),
                'cur_clicks' => (int) ($r['cur_clicks'] ?? 0),
                'cur_impressions' => (int) ($r['cur_impressions'] ?? 0),
                'cur_ctr' => (float) ($r['cur_ctr'] ?? 0),
                'cur_position' => (float) ($r['cur_position'] ?? 0),
                'prev_clicks' => (int) ($r['prev_clicks'] ?? 0),
                'prev_impressions' => (int) ($r['prev_impressions'] ?? 0),
                'prev_ctr' => (float) ($r['prev_ctr'] ?? 0),
                'prev_position' => (float) ($r['prev_position'] ?? 0),
                'd_clicks' => $dc,
                'd_impr' => $di,
                'd_pos' => $dp,
                'flags' => $flags,
            ];
        }

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

