<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

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

    /**
     * @param null|list<string> $onlyPages When non-null, restrict to this exact URL set (after trim/dedupe). Empty array yields no rows.
     * @return array{total:int,rows:list<array{page:string,clicks:int,impressions:int,ctr:float,position:float}>}
     */
    public function listForRange(
        int $propertyId,
        string $startDate,
        string $endDate,
        string $search,
        string $device,
        string $country,
        string $sort,
        string $dir,
        int $page,
        int $perPage,
        ?array $onlyPages = null
    ): array {
        $t = $this->table();
        $page = max(1, $page);
        $perPage = max(5, min(100, $perPage));
        $offset = ($page - 1) * $perPage;

        $search = trim($search);
        $where = " WHERE property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ?";
        $argsBase = [$propertyId, $startDate, $endDate, $device, $country];
        if ($search !== '') {
            $where .= " AND page LIKE ?";
            $argsBase[] = '%' . $search . '%';
        }

        if ($onlyPages !== null) {
            $ps = [];
            foreach ($onlyPages as $p) {
                $p = trim((string) $p);
                if ($p !== '') {
                    $ps[$p] = true;
                }
            }
            $onlyList = array_keys($ps);
            if ($onlyList === []) {
                return ['total' => 0, 'rows' => []];
            }
            $ph = implode(',', array_fill(0, count($onlyList), '?'));
            $where .= " AND page IN ({$ph})";
            $argsBase = array_merge($argsBase, $onlyList);
        }

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

        $countStmt = $this->pdo->prepare(
            "SELECT COUNT(DISTINCT page) AS c
             FROM {$t}
             {$where}"
        );
        $countStmt->execute($argsBase);
        $total = (int) ($countStmt->fetchColumn() ?: 0);

        $stmt = $this->pdo->prepare(
            "SELECT
                page AS p,
                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}
             GROUP BY p
             ORDER BY {$orderExpr}
             LIMIT {$perPage} OFFSET {$offset}"
        );
        $stmt->execute($argsBase);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $out[] = [
                'page' => (string) ($r['p'] ?? ''),
                'clicks' => (int) ($r['clicks'] ?? 0),
                'impressions' => (int) ($r['impressions'] ?? 0),
                'ctr' => (float) ($r['ctr'] ?? 0),
                'position' => (float) ($r['position'] ?? 0),
            ];
        }

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

    /**
     * @param list<string> $pages
     * @return array<string,array{clicks:int,impressions:int,ctr:float,position:float}>
     */
    public function totalsForPages(int $propertyId, string $startDate, string $endDate, string $device, string $country, array $pages): array
    {
        $pages = array_values(array_filter(array_map('trim', $pages), static fn ($p) => $p !== ''));
        if ($pages === []) {
            return [];
        }

        $t = $this->table();
        $placeholders = implode(',', array_fill(0, count($pages), '?'));
        $args = array_merge([$propertyId, $startDate, $endDate, $device, $country], $pages);

        $stmt = $this->pdo->prepare(
            "SELECT
                page AS p,
                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 = ? AND page IN ({$placeholders})
             GROUP BY p"
        );
        $stmt->execute($args);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $p = (string) ($r['p'] ?? '');
            if ($p === '') {
                continue;
            }
            $clicks = (int) ($r['clicks'] ?? 0);
            $impr = (int) ($r['impressions'] ?? 0);
            $out[$p] = [
                'clicks' => $clicks,
                'impressions' => $impr,
                'ctr' => (float) ($r['ctr'] ?? ($impr > 0 ? ($clicks / $impr) : 0)),
                'position' => (float) ($r['position'] ?? 0),
            ];
        }
        return $out;
    }
}

