<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

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

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

    /**
     * Returns queries that have multiple pages in the range.
     *
     * @return array{total:int,rows:list<array{
     *   query:string,total_clicks:int,total_impressions:int,total_ctr:float,total_position:float,pages_count:int,
     *   top_pages:list<array{page:string,clicks:int,impressions:int,share_clicks:float,share_impressions:float}>,
     *   suggestion:string
     * }>}
     */
    public function listForRange(
        int $propertyId,
        string $startDate,
        string $endDate,
        string $device,
        string $country,
        int $minImpressions,
        int $minPages,
        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;
        $minImpressions = max(0, $minImpressions);
        $minPages = max(2, $minPages);

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

        $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,
                COUNT(DISTINCT page) AS pages_count
            FROM {$t}
            WHERE property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ?
            GROUP BY q
            HAVING impressions >= {$minImpressions} AND pages_count >= {$minPages}
        ";

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

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

        // For each query in this page, fetch top pages distribution.
        $queries = [];
        foreach ($rows as $r) {
            if (is_array($r) && isset($r['q'])) {
                $queries[] = (string) $r['q'];
            }
        }
        // Return all competing URLs per query (UI shows them in a collapsible panel).
        $topMap = $this->topPagesForQueries($propertyId, $startDate, $endDate, $device, $country, $queries, 50);

        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) continue;
            $q = (string) ($r['q'] ?? '');
            $clicks = (int) ($r['clicks'] ?? 0);
            $impr = (int) ($r['impressions'] ?? 0);
            $pagesCount = (int) ($r['pages_count'] ?? 0);
            $top = $topMap[$q] ?? [];

            $suggestion = $this->suggestionForTop($pagesCount, $top);

            $out[] = [
                'query' => $q,
                'total_clicks' => $clicks,
                'total_impressions' => $impr,
                'total_ctr' => (float) ($r['ctr'] ?? 0),
                'total_position' => (float) ($r['position'] ?? 0),
                'pages_count' => $pagesCount,
                'top_pages' => $top,
                'suggestion' => $suggestion,
            ];
        }

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

    /**
     * @param list<string> $queries
     * @return array<string,list<array{page:string,clicks:int,impressions:int,share_clicks:float,share_impressions:float}>>
     */
    private function topPagesForQueries(int $propertyId, string $startDate, string $endDate, string $device, string $country, array $queries, int $limit): array
    {
        $queries = array_values(array_filter(array_map('trim', $queries), static fn ($q) => $q !== ''));
        if ($queries === []) {
            return [];
        }

        $t = $this->table();
        // Cannibalization can involve many URLs; keep a sensible hard cap to protect the UI.
        $limit = max(1, min(50, $limit));
        $placeholders = implode(',', array_fill(0, count($queries), '?'));
        $args = array_merge([$propertyId, $startDate, $endDate, $device, $country], $queries);

        $stmt = $this->pdo->prepare(
            "SELECT
                `query` AS q,
                page AS p,
                SUM(clicks) AS clicks,
                SUM(impressions) AS impressions
             FROM {$t}
             WHERE property_id = ? AND date >= ? AND date <= ? AND device = ? AND country = ? AND `query` IN ({$placeholders})
             GROUP BY q, p"
        );
        $stmt->execute($args);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        // Build per-query totals and page lists, then compute shares and pick top-N by impressions.
        $byQ = [];
        $totals = [];
        foreach ($rows as $r) {
            if (!is_array($r)) continue;
            $q = (string) ($r['q'] ?? '');
            $p = (string) ($r['p'] ?? '');
            if ($q === '' || $p === '') continue;
            $c = (int) ($r['clicks'] ?? 0);
            $i = (int) ($r['impressions'] ?? 0);
            $byQ[$q][] = ['page' => $p, 'clicks' => $c, 'impressions' => $i];
            $totals[$q] = [
                'clicks' => (int) (($totals[$q]['clicks'] ?? 0) + $c),
                'impressions' => (int) (($totals[$q]['impressions'] ?? 0) + $i),
            ];
        }

        $out = [];
        foreach ($byQ as $q => $pages) {
            usort($pages, static fn ($a, $b) => ((int) $b['impressions']) <=> ((int) $a['impressions']));
            $pages = array_slice($pages, 0, $limit);
            $totC = (int) ($totals[$q]['clicks'] ?? 0);
            $totI = (int) ($totals[$q]['impressions'] ?? 0);
            $mapped = [];
            foreach ($pages as $p) {
                $pc = (int) ($p['clicks'] ?? 0);
                $pi = (int) ($p['impressions'] ?? 0);
                $mapped[] = [
                    'page' => (string) ($p['page'] ?? ''),
                    'clicks' => $pc,
                    'impressions' => $pi,
                    'share_clicks' => $totC > 0 ? ($pc / $totC) : 0.0,
                    'share_impressions' => $totI > 0 ? ($pi / $totI) : 0.0,
                ];
            }
            $out[$q] = $mapped;
        }
        return $out;
    }

    /**
     * @param list<array{page:string,clicks:int,impressions:int,share_clicks:float,share_impressions:float}> $top
     */
    private function suggestionForTop(int $pagesCount, array $top): string
    {
        $topShare = 0.0;
        if ($top !== [] && isset($top[0]) && is_array($top[0])) {
            $topShare = (float) ($top[0]['share_impressions'] ?? 0.0);
        }
        if ($pagesCount >= 4) {
            return 'many_urls';
        }
        if ($topShare < 0.55) {
            return 'split_even';
        }
        if ($topShare < 0.75) {
            return 'split_some';
        }
        return 'dominant';
    }
}

