<?php
namespace Crawl\Repositories;

use Crawl\Lib\Database;

class CrawlPageRepository
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function upsert(int $runId, array $row): void
    {
        $sql = 'INSERT INTO crawl_pages (
                    crawl_run_id, url, normalized_url, status_code, content_type, title, title_length,
                    meta_description, meta_description_length, h1, h2_list, h3_list, canonical_url,
                    meta_robots, x_robots_tag, word_count, response_time, depth, content_hash,
                    near_duplicate_hash, json_ld_schema, redirect_url, redirect_chain_length,
                    content_length, transferred_bytes, sentence_count, flesch_reading_ease,
                    text_ratio, last_modified, redirect_type, http_version, created_at
                ) VALUES (
                    :crawl_run_id, :url, :normalized_url, :status_code, :content_type, :title, :title_length,
                    :meta_description, :meta_description_length, :h1, :h2_list, :h3_list, :canonical_url,
                    :meta_robots, :x_robots_tag, :word_count, :response_time, :depth, :content_hash,
                    :near_duplicate_hash, :json_ld_schema, :redirect_url, :redirect_chain_length,
                    :content_length, :transferred_bytes, :sentence_count, :flesch_reading_ease,
                    :text_ratio, :last_modified, :redirect_type, :http_version, NOW()
                )
                ON DUPLICATE KEY UPDATE
                    status_code = VALUES(status_code),
                    content_type = VALUES(content_type),
                    title = VALUES(title),
                    title_length = VALUES(title_length),
                    meta_description = VALUES(meta_description),
                    meta_description_length = VALUES(meta_description_length),
                    h1 = VALUES(h1),
                    h2_list = VALUES(h2_list),
                    h3_list = VALUES(h3_list),
                    canonical_url = VALUES(canonical_url),
                    meta_robots = VALUES(meta_robots),
                    x_robots_tag = VALUES(x_robots_tag),
                    word_count = VALUES(word_count),
                    response_time = VALUES(response_time),
                    depth = VALUES(depth),
                    content_hash = VALUES(content_hash),
                    near_duplicate_hash = VALUES(near_duplicate_hash),
                    json_ld_schema = VALUES(json_ld_schema),
                    redirect_url = VALUES(redirect_url),
                    redirect_chain_length = VALUES(redirect_chain_length),
                    content_length = VALUES(content_length),
                    transferred_bytes = VALUES(transferred_bytes),
                    sentence_count = VALUES(sentence_count),
                    flesch_reading_ease = VALUES(flesch_reading_ease),
                    text_ratio = VALUES(text_ratio),
                    last_modified = VALUES(last_modified),
                    redirect_type = VALUES(redirect_type),
                    http_version = VALUES(http_version)';

        $params = array_merge(['crawl_run_id' => $runId], $row);
        $this->db->query($sql, $params);
    }

    public function getByRunAndUrl(int $runId, string $url): ?array
    {
        return $this->db->fetch('SELECT * FROM crawl_pages WHERE crawl_run_id = ? AND url = ? LIMIT 1', [$runId, $url]);
    }

    public function paginated(int $runId, array $filters, int $limit, int $offset, string $sortBy, string $sortDir): array
    {
        $allowedSort = ['url', 'status_code', 'title_length', 'meta_description_length', 'word_count', 'depth', 'response_time', 'created_at'];
        if (!in_array($sortBy, $allowedSort, true)) {
            $sortBy = 'url';
        }
        $sortDir = strtoupper($sortDir) === 'DESC' ? 'DESC' : 'ASC';

        $where = ['crawl_run_id = :run_id'];
        $params = ['run_id' => $runId];

        if (!empty($filters['search'])) {
            $where[] = '(url LIKE :search OR title LIKE :search OR meta_description LIKE :search)';
            $params['search'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['status_code'])) {
            $where[] = 'status_code = :status_code';
            $params['status_code'] = (int)$filters['status_code'];
        }
        if (!empty($filters['content_type'])) {
            $where[] = 'content_type LIKE :content_type';
            $params['content_type'] = '%' . $filters['content_type'] . '%';
        }
        if (isset($filters['depth_min']) && $filters['depth_min'] !== '') {
            $where[] = 'depth >= :depth_min';
            $params['depth_min'] = (int)$filters['depth_min'];
        }
        if (isset($filters['depth_max']) && $filters['depth_max'] !== '') {
            $where[] = 'depth <= :depth_max';
            $params['depth_max'] = (int)$filters['depth_max'];
        }
        if (isset($filters['word_count_min']) && $filters['word_count_min'] !== '') {
            $where[] = 'word_count >= :word_count_min';
            $params['word_count_min'] = (int)$filters['word_count_min'];
        }
        if (isset($filters['word_count_max']) && $filters['word_count_max'] !== '') {
            $where[] = 'word_count <= :word_count_max';
            $params['word_count_max'] = (int)$filters['word_count_max'];
        }
        if (!empty($filters['indexability'])) {
            if ($filters['indexability'] === 'noindex') {
                $where[] = '(meta_robots LIKE "%noindex%" OR x_robots_tag LIKE "%noindex%")';
            }
            if ($filters['indexability'] === 'indexable') {
                $where[] = '(IFNULL(meta_robots, "") NOT LIKE "%noindex%" AND IFNULL(x_robots_tag, "") NOT LIKE "%noindex%")';
            }
        }
        if (!empty($filters['status_code_min'])) {
            $where[] = 'status_code >= :status_code_min';
            $params['status_code_min'] = (int)$filters['status_code_min'];
        }
        if (!empty($filters['status_code_max'])) {
            $where[] = 'status_code <= :status_code_max';
            $params['status_code_max'] = (int)$filters['status_code_max'];
        }
        if (!empty($filters['type_filter'])) {
            if ($filters['type_filter'] === 'html') {
                $where[] = "content_type LIKE '%html%'";
            } elseif ($filters['type_filter'] === 'image') {
                $where[] = "content_type LIKE '%image%'";
            } elseif ($filters['type_filter'] === 'other') {
                $where[] = "(content_type NOT LIKE '%html%' AND content_type NOT LIKE '%image%')";
            }
        }
        if (!empty($filters['hide_noise'])) {
            $where[] = "(url NOT LIKE '%/page/%' AND url NOT LIKE '%/tag/%' AND url NOT LIKE '%/author/%' AND url NOT LIKE '%/category/%' AND url NOT LIKE '%?s=%' AND url NOT LIKE '%/feed/%' AND url NOT LIKE '%/amp%')";
        }

        $whereSql = implode(' AND ', $where);

        $totalRow = $this->db->fetch("SELECT COUNT(*) AS cnt FROM crawl_pages WHERE {$whereSql}", $params);
        $rows = $this->db->fetchAll(
            "SELECT * FROM crawl_pages WHERE {$whereSql} ORDER BY {$sortBy} {$sortDir} LIMIT {$limit} OFFSET {$offset}",
            $params
        );

        return [
            'total' => (int)($totalRow['cnt'] ?? 0),
            'rows' => $rows,
        ];
    }

    public function statusDistribution(int $runId): array
    {
        return $this->db->fetchAll('SELECT status_code, COUNT(*) AS cnt FROM crawl_pages WHERE crawl_run_id = ? GROUP BY status_code ORDER BY status_code', [$runId]);
    }

    public function depthDistribution(int $runId): array
    {
        return $this->db->fetchAll('SELECT depth, COUNT(*) AS cnt FROM crawl_pages WHERE crawl_run_id = ? GROUP BY depth ORDER BY depth', [$runId]);
    }

    public function contentTypeDistribution(int $runId): array
    {
        return $this->db->fetchAll('SELECT content_type, COUNT(*) AS cnt FROM crawl_pages WHERE crawl_run_id = ? GROUP BY content_type ORDER BY cnt DESC', [$runId]);
    }

    public function allByRun(int $runId): array
    {
        return $this->db->fetchAll('SELECT * FROM crawl_pages WHERE crawl_run_id = ? ORDER BY url ASC', [$runId]);
    }

    // ── New analytical queries ────────────────────────────────────────────

    /**
     * Full 46-column audit rows with inlink/outlink counts joined from crawl_links.
     * Returns up to $limit rows (no offset — caller loads a limited set once, filters client-side).
     */
    public function fullAuditRows(int $runId, array $filters, int $limit): array
    {
        // Build WHERE clauses on crawl_pages (same filter logic as paginated())
        $where        = ['p.crawl_run_id = ?'];
        $filterParams = [];  // params that belong to the main WHERE clause

        if (!empty($filters['search'])) {
            $where[]  = '(p.url LIKE ? OR p.title LIKE ? OR p.meta_description LIKE ?)';
            $s = '%' . $filters['search'] . '%';
            $filterParams[] = $s; $filterParams[] = $s; $filterParams[] = $s;
        }
        if (!empty($filters['status_code_min'])) {
            $where[]        = 'p.status_code >= ?';
            $filterParams[] = (int)$filters['status_code_min'];
        }
        if (!empty($filters['status_code_max'])) {
            $where[]        = 'p.status_code <= ?';
            $filterParams[] = (int)$filters['status_code_max'];
        }
        if (!empty($filters['type_filter'])) {
            if ($filters['type_filter'] === 'html')      { $where[] = "p.content_type LIKE '%html%'"; }
            elseif ($filters['type_filter'] === 'image') { $where[] = "p.content_type LIKE '%image%'"; }
            elseif ($filters['type_filter'] === 'other') { $where[] = "(p.content_type NOT LIKE '%html%' AND p.content_type NOT LIKE '%image%')"; }
        }
        if (!empty($filters['indexability'])) {
            if ($filters['indexability'] === 'noindex') {
                $where[] = '(p.meta_robots LIKE "%noindex%" OR p.x_robots_tag LIKE "%noindex%")';
            } elseif ($filters['indexability'] === 'indexable') {
                $where[] = '(IFNULL(p.meta_robots,"") NOT LIKE "%noindex%" AND IFNULL(p.x_robots_tag,"") NOT LIKE "%noindex%")';
            }
        }
        if (!empty($filters['hide_noise'])) {
            $where[] = "(p.url NOT LIKE '%/page/%' AND p.url NOT LIKE '%/tag/%' AND p.url NOT LIKE '%/author/%' AND p.url NOT LIKE '%/category/%' AND p.url NOT LIKE '%?s=%' AND p.url NOT LIKE '%/feed/%' AND p.url NOT LIKE '%/amp%')";
        }

        // 3 subquery params + 1 main WHERE runId + filter params + 1 LIMIT
        $params = [$runId, $runId, $runId, $runId, ...$filterParams, $limit];

        $whereSql = implode(' AND ', $where);

        $sql = "
            SELECT p.*,
                COALESCE(il.inlink_count,    0) AS inlinks,
                COALESCE(il.uniq_inlinks,    0) AS unique_inlinks,
                COALESCE(ol.outlink_count,   0) AS outlinks,
                COALESCE(ol.uniq_outlinks,   0) AS unique_outlinks,
                COALESCE(el.ext_count,       0) AS external_outlinks,
                COALESCE(el.uniq_ext,        0) AS unique_external_outlinks
            FROM crawl_pages p
            LEFT JOIN (
                SELECT to_url,
                    COUNT(*)              AS inlink_count,
                    COUNT(DISTINCT from_url) AS uniq_inlinks
                FROM crawl_links WHERE crawl_run_id = ? AND is_internal = 1
                GROUP BY to_url
            ) il ON il.to_url = p.url
            LEFT JOIN (
                SELECT from_url,
                    COUNT(*)              AS outlink_count,
                    COUNT(DISTINCT to_url)   AS uniq_outlinks
                FROM crawl_links WHERE crawl_run_id = ? AND is_internal = 1
                GROUP BY from_url
            ) ol ON ol.from_url = p.url
            LEFT JOIN (
                SELECT from_url,
                    COUNT(*)              AS ext_count,
                    COUNT(DISTINCT to_url)   AS uniq_ext
                FROM crawl_links WHERE crawl_run_id = ? AND is_internal = 0
                GROUP BY from_url
            ) el ON el.from_url = p.url
            WHERE {$whereSql}
            ORDER BY p.url ASC
            LIMIT ?";

        $total = (int)($this->db->fetch(
            "SELECT COUNT(*) AS c FROM crawl_pages WHERE crawl_run_id = ?",
            [$runId]
        )['c'] ?? 0);

        return [
            'total' => $total,
            'rows'  => $this->db->fetchAll($sql, $params),
        ];
    }

    public function duplicateGroups(int $runId): array
    {
        return $this->db->fetchAll(
            "SELECT content_hash, COUNT(*) AS cnt, GROUP_CONCAT(url ORDER BY url SEPARATOR '|||') AS urls
             FROM crawl_pages
             WHERE crawl_run_id = ? AND content_hash IS NOT NULL AND content_hash != ''
             GROUP BY content_hash HAVING cnt > 1
             ORDER BY cnt DESC
             LIMIT 100",
            [$runId]
        );
    }

    public function canonicalIssues(int $runId): array
    {
        return $this->db->fetchAll(
            "SELECT url, canonical_url, status_code
             FROM crawl_pages
             WHERE crawl_run_id = ?
               AND status_code = 200
               AND canonical_url IS NOT NULL
               AND canonical_url != ''
               AND TRIM(TRAILING '/' FROM canonical_url) != TRIM(TRAILING '/' FROM url)
             ORDER BY url ASC
             LIMIT 500",
            [$runId]
        );
    }

    public function headingAudit(int $runId, int $limit, int $offset): array
    {
        return $this->db->fetchAll(
            "SELECT url, h1, h2_list, h3_list, title, title_length
             FROM crawl_pages
             WHERE crawl_run_id = ? AND status_code = 200
             ORDER BY url ASC
             LIMIT {$limit} OFFSET {$offset}",
            [$runId]
        );
    }

    public function crawlBudgetStats(int $runId): array
    {
        $total = (int)($this->db->fetch("SELECT COUNT(*) AS c FROM crawl_pages WHERE crawl_run_id = ?", [$runId])['c'] ?? 0);
        if ($total === 0) return ['total' => 0, 'categories' => []];

        $noindex = (int)($this->db->fetch(
            "SELECT COUNT(*) AS c FROM crawl_pages WHERE crawl_run_id = ? AND (meta_robots LIKE '%noindex%' OR x_robots_tag LIKE '%noindex%')",
            [$runId]
        )['c'] ?? 0);

        $redirects = (int)($this->db->fetch(
            "SELECT COUNT(*) AS c FROM crawl_pages WHERE crawl_run_id = ? AND status_code >= 300 AND status_code < 400",
            [$runId]
        )['c'] ?? 0);

        $thin = (int)($this->db->fetch(
            "SELECT COUNT(*) AS c FROM crawl_pages WHERE crawl_run_id = ? AND word_count > 0 AND word_count < 300 AND status_code = 200",
            [$runId]
        )['c'] ?? 0);

        $errors = (int)($this->db->fetch(
            "SELECT COUNT(*) AS c FROM crawl_pages WHERE crawl_run_id = ? AND status_code >= 400",
            [$runId]
        )['c'] ?? 0);

        // Noise pages via pattern matching
        $noise = (int)($this->db->fetch(
            "SELECT COUNT(*) AS c FROM crawl_pages WHERE crawl_run_id = ?
             AND (url LIKE '%/page/%' OR url LIKE '%/tag/%' OR url LIKE '%/author/%'
                  OR url LIKE '%/category/%' OR url LIKE '%?s=%' OR url LIKE '%/feed/%' OR url LIKE '%/amp%')",
            [$runId]
        )['c'] ?? 0);

        $pct = fn(int $n): float => $total > 0 ? round($n / $total * 100, 1) : 0;

        return [
            'total'      => $total,
            'categories' => [
                ['label' => 'Indexable (200)',    'count' => max(0, $total - $noindex - $redirects - $errors), 'pct' => $pct(max(0, $total - $noindex - $redirects - $errors)), 'color' => 'success'],
                ['label' => 'Noindex',            'count' => $noindex,   'pct' => $pct($noindex),   'color' => 'warning'],
                ['label' => 'Redirects (3xx)',    'count' => $redirects, 'pct' => $pct($redirects), 'color' => 'info'],
                ['label' => 'Errors (4xx/5xx)',   'count' => $errors,    'pct' => $pct($errors),    'color' => 'danger'],
                ['label' => 'Thin Content',       'count' => $thin,      'pct' => $pct($thin),      'color' => 'secondary'],
                ['label' => 'Noise URLs',         'count' => $noise,     'pct' => $pct($noise),     'color' => 'secondary'],
            ],
        ];
    }

    public function technicalPages(int $runId, int $limit, int $offset): array
    {
        $where  = "crawl_run_id = :run_id AND (status_code >= 300 OR response_time > 3.0 OR meta_robots LIKE '%noindex%' OR x_robots_tag LIKE '%noindex%')";
        $params = ['run_id' => $runId];

        $total = $this->db->fetch("SELECT COUNT(*) AS cnt FROM crawl_pages WHERE {$where}", $params);
        $rows  = $this->db->fetchAll(
            "SELECT url, status_code, canonical_url, meta_robots, x_robots_tag, response_time
             FROM crawl_pages WHERE {$where}
             ORDER BY status_code DESC, response_time DESC
             LIMIT {$limit} OFFSET {$offset}",
            $params
        );

        return ['total' => (int)($total['cnt'] ?? 0), 'rows' => $rows];
    }
}
