<?php
/**
 * FLOWB0T NEXUS - Import Processor
 * Complete import functionality for pinfeeds, feed_data, user_myhashtag, and crawler_seen_links
 * Preserves ALL functionality from existing code (mass_crawler_bing.php, ps20.php)
 *
 * @package Flowb0t\Engine\Processors
 * @version 1.0.0
 */

namespace Flowb0t\Engine\Processors;

use Flowb0t\Core\Database;
use Flowb0t\Core\Logger;

class ImportProcessor {
    private Database $db;
    private Logger $logger;
    private ?int $jobId = null;

    // Table names (existing tables)
    const TABLE_PINFEEDS = 'pinfeeds';
    const TABLE_FEED_DATA = 'feed_data';
    const TABLE_USERS = 'user_myhashtag';
    const TABLE_SEEN_LINKS = 'crawler_seen_links';

    // Statistics
    private array $stats = [
        'total_processed' => 0,
        'imported_pinfeeds' => 0,
        'imported_feed_data' => 0,
        'users_created' => 0,
        'duplicates_skipped' => 0,
        'errors' => 0
    ];

    // Author names for random generation (from data/authors.php)
    private array $authorNames = [];

    // Common words to filter from tags (English + Portuguese + Spanish)
    private array $commonWords = [
        // English
        "a","an","the","and","or","but","in","at","on","with","to","–","for","is","of","that","it",
        "by","from","as","are","was","be","has","have","will","this","which","its","about","up","more",
        "who","also","they","out","he","she","you","their","we","her","his","them","been","these","would",
        "some","can","like","there","if","all","my","what","so","then","into","just","over","do","than",
        "when","other","how","our","any","new","me","after","most","made","only","time","where","year",
        "years","make","does","could","were","your","good","well",
        // Portuguese
        "que","uma","com","para","das","como","dos","não","mais","está","tem","seu","sua","são","foi",
        "por","ele","ela","nos","quando","muito","pode","após","sobre","entre","até","sem","mas","isso",
        // Spanish
        "los","las","una","del","que","con","por","para","como","más","pero","sus","ese","esta"
    ];

    /**
     * Constructor
     */
    public function __construct(?int $jobId = null) {
        $this->db = Database::getInstance();
        $this->logger = new Logger($jobId ? "job_{$jobId}" : 'import');
        $this->jobId = $jobId;
        $this->loadAuthorNames();
    }

    /**
     * Load author names from data file
     */
    private function loadAuthorNames(): void {
        $authorsFile = NEXUS_ROOT . '/data/authors.php';
        if (file_exists($authorsFile)) {
            include $authorsFile;
            if (isset($AUTHOR_NAMES)) {
                $this->authorNames = $AUTHOR_NAMES;
            }
        }

        // Fallback names if file not loaded
        if (empty($this->authorNames)) {
            $this->authorNames = [
                "Carlos Dias","Mariana Silva","João Santos","Ana Monteiro",
                "Pedro Correia","Maria Almeida","Sofia Nunes","Rodrigo Azevedo",
                "Luciana Araújo","Felipe Santana","MoonlightKnight","SunshineQueen",
                "RedDragon","BluePhoenix","ShadowHunter","StarGazer","ThunderStriker"
            ];
        }
    }

    /**
     * Import a crawled result to all relevant tables
     *
     * @param array $result Crawl result with url, html, metadata
     * @param string|null $searchTerm The search term that found this URL
     * @return array Import result with status and IDs
     */
    public function import(array $result, ?string $searchTerm = null): array {
        $this->stats['total_processed']++;

        try {
            // Parse HTML if not already done
            $dom = $this->getDomDocument($result['html'] ?? '');
            if (!$dom) {
                throw new \Exception('Could not parse HTML content');
            }

            $xpath = new \DOMXPath($dom);
            $url = $result['url'];

            // Extract base URL
            $parsedUrl = parse_url($url);
            $baseURL = ($parsedUrl['scheme'] ?? 'https') . '://' . ($parsedUrl['host'] ?? '');

            // Extract all content
            $canonical = $this->getCanonicalUrl($xpath, $url);
            $title = $this->getTitle($xpath);
            $description = $this->getDescription($xpath);
            $thumbnail = $this->getThumbnail($xpath, $baseURL);
            $favicon = $this->getFavicon($xpath, $baseURL);
            $embedCode = $this->extractEmbedCode($canonical);
            $sourceHost = parse_url($canonical, PHP_URL_HOST) ?: parse_url($url, PHP_URL_HOST);
            $tags = $this->extractTagsFromTitle($title);

            // Generate random author and get/create user
            $author = $this->generateRandomAuthor();
            $userId = $this->getOrCreateUserId($author);

            // Get or create feed_data entry for this domain
            $domainInfo = $this->getOrCreateFeedData($sourceHost, $url);

            // Begin transaction for pinfeeds import
            $pdo = $this->db->getConnection();
            $pdo->beginTransaction();

            try {
                // Import to pinfeeds
                $pinfeedsId = $this->importToPinfeeds([
                    'title' => $title,
                    'description' => $description,
                    'thumbnail' => $thumbnail,
                    'link' => $canonical,
                    'source_website' => $sourceHost,
                    'author' => $author,
                    'favicon' => $favicon,
                    'tags' => implode(',', $tags),
                    'embed_code' => $embedCode,
                    'source_domain' => $sourceHost,
                    'user_id' => $userId,
                    'source_domain_id' => $domainInfo['id'],
                    'main_category_id' => $domainInfo['main_category_id'] ?? 0
                ]);

                $pdo->commit();

                $this->stats['imported_pinfeeds']++;
                $this->logger->info('IMPORT', "Imported to pinfeeds: {$title}", [
                    'url' => $canonical,
                    'pinfeeds_id' => $pinfeedsId
                ]);

                return [
                    'success' => true,
                    'pinfeeds_id' => $pinfeedsId,
                    'user_id' => $userId,
                    'domain_id' => $domainInfo['id'],
                    'title' => $title,
                    'author' => $author
                ];

            } catch (\Exception $e) {
                $pdo->rollBack();
                throw $e;
            }

        } catch (\Exception $e) {
            $this->stats['errors']++;
            $this->logger->error('IMPORT', "Import failed: {$e->getMessage()}", [
                'url' => $result['url'] ?? 'unknown'
            ]);

            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }

    /**
     * Batch import multiple results
     *
     * @param array $results Array of crawl results
     * @param string|null $searchTerm The search term
     * @return array Summary of import results
     */
    public function importBatch(array $results, ?string $searchTerm = null): array {
        $imported = [];
        $failed = [];

        foreach ($results as $result) {
            $importResult = $this->import($result, $searchTerm);
            if ($importResult['success']) {
                $imported[] = $importResult;
            } else {
                $failed[] = $importResult;
            }
        }

        return [
            'total' => count($results),
            'imported' => count($imported),
            'failed' => count($failed),
            'results' => $imported,
            'errors' => $failed,
            'stats' => $this->stats
        ];
    }

    /**
     * Import directly to pinfeeds table
     * Exact structure from existing code
     *
     * @param array $data Content data
     * @return int Inserted ID
     */
    public function importToPinfeeds(array $data): int {
        $pdo = $this->db->getConnection();

        $stmt = $pdo->prepare("
            INSERT INTO " . self::TABLE_PINFEEDS . " (
                title, description, thumbnail,
                pubDate, link, updated,
                source_website, author, favicon,
                tags, embed_code, source_domain,
                user_id, source_domain_id, main_category_id,
                title_cat_id, description_cat_id, tag_cat_id
            ) VALUES (?, ?, ?, NOW(), ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");

        $stmt->execute([
            $data['title'] ?? 'No title',
            $data['description'] ?? 'No description',
            $data['thumbnail'] ?? 'fallback_image.jpg',
            $data['link'],
            $data['source_website'] ?? '',
            $data['author'] ?? 'Anonymous',
            $data['favicon'] ?? 'default_favicon.ico',
            $data['tags'] ?? '',
            $data['embed_code'] ?? '',
            $data['source_domain'] ?? '',
            $data['user_id'] ?? 1,
            $data['source_domain_id'] ?? 0,
            $data['main_category_id'] ?? 0,
            $data['title_cat_id'] ?? 0,
            $data['description_cat_id'] ?? 0,
            $data['tag_cat_id'] ?? 0
        ]);

        return (int) $pdo->lastInsertId();
    }

    /**
     * Get or create user in user_myhashtag table
     * Exact implementation from existing code
     *
     * @param string $author Author name
     * @return int User ID
     */
    public function getOrCreateUserId(string $author): int {
        $pdo = $this->db->getConnection();

        // Check if user exists
        $stmt = $pdo->prepare("SELECT ID FROM " . self::TABLE_USERS . " WHERE username = ? LIMIT 1");
        $stmt->execute([$author]);
        $existing = $stmt->fetchColumn();

        if ($existing) {
            return (int) $existing;
        }

        // Create new user with full profile (exact structure from existing code)
        $authorParts = explode(' ', $author);
        $firstName = ucfirst($authorParts[0] ?? 'Unknown');
        $lastName = ucfirst($authorParts[1] ?? 'User');

        $userData = [
            'username' => $author,
            'email' => strtolower(preg_replace('/\s+/', '', $author)) . '@digupdog.com',
            'senha' => password_hash('Raimundinho1', PASSWORD_DEFAULT),
            'first_name' => $firstName,
            'last_name' => $lastName,
            'address' => '123 Random Street',
            'phone_number' => '+1' . rand(1000000000, 9999999999),
            'birthdate' => date('Y-m-d', strtotime('-' . rand(18, 50) . ' years')),
            'profile_picture' => 'https://example.com/default_profile.jpg',
            'bio' => "Bio gerada automaticamente para {$firstName} {$lastName}",
            'gender' => (rand(0, 1) === 0) ? 'male' : 'female',
            'status' => 'active',
            'user_role' => 'user',
            'privacy_settings' => json_encode(['visibility' => 'public']),
            'social_links' => json_encode(['facebook' => '', 'twitter' => '']),
            'preferences' => json_encode(['theme' => 'dark', 'notifications' => true])
        ];

        $stmt = $pdo->prepare("
            INSERT INTO " . self::TABLE_USERS . "
            (username, email, senha, first_name, last_name, address, phone_number, created_at, birthdate,
             profile_picture, bio, gender, status, user_role, privacy_settings, social_links, preferences)
            VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");

        $stmt->execute([
            $userData['username'],
            $userData['email'],
            $userData['senha'],
            $userData['first_name'],
            $userData['last_name'],
            $userData['address'],
            $userData['phone_number'],
            $userData['birthdate'],
            $userData['profile_picture'],
            $userData['bio'],
            $userData['gender'],
            $userData['status'],
            $userData['user_role'],
            $userData['privacy_settings'],
            $userData['social_links'],
            $userData['preferences']
        ]);

        $userId = (int) $pdo->lastInsertId();
        $this->stats['users_created']++;

        $this->logger->info('IMPORT', "Created user: {$author}", ['user_id' => $userId]);

        return $userId;
    }

    /**
     * Get or create feed_data entry for domain
     *
     * @param string $domain Domain name
     * @param string $url Original URL
     * @return array Domain info with id and main_category_id
     */
    public function getOrCreateFeedData(string $domain, string $url): array {
        $pdo = $this->db->getConnection();
        $sourceDomainBase = 'https://' . $domain;

        // Check if domain exists
        $stmt = $pdo->prepare("SELECT id, main_category_id FROM " . self::TABLE_FEED_DATA . " WHERE website_base LIKE ? LIMIT 1");
        $stmt->execute(['%' . $sourceDomainBase . '%']);
        $domainRow = $stmt->fetch();

        if ($domainRow) {
            return [
                'id' => (int) $domainRow['id'],
                'main_category_id' => (int) ($domainRow['main_category_id'] ?? 0)
            ];
        }

        // Create new feed_data entry
        $stmt = $pdo->prepare("
            INSERT INTO " . self::TABLE_FEED_DATA . " (
                website_base, website_feed, main_category_id,
                subcategory_id, subsubcategory_id,
                subcategory_name, subsubcategory_name
            ) VALUES (?, ?, 0, NULL, NULL, NULL, NULL)
        ");
        $stmt->execute([$sourceDomainBase, $url]);

        $domainId = (int) $pdo->lastInsertId();
        $this->stats['imported_feed_data']++;

        $this->logger->info('IMPORT', "Created feed_data for domain: {$domain}", ['domain_id' => $domainId]);

        return [
            'id' => $domainId,
            'main_category_id' => 0
        ];
    }

    /**
     * Check and mark link as seen for deduplication
     *
     * @param string $processId Process/Job ID
     * @param string $url URL to check
     * @param int $depth Current depth
     * @return bool True if already seen, false if new
     */
    public function checkAndMarkSeenLink(string $processId, string $url, int $depth = 0): bool {
        $pdo = $this->db->getConnection();

        $stmt = $pdo->prepare("
            INSERT INTO " . self::TABLE_SEEN_LINKS . " (process_id, link, depth)
            VALUES (:proc, :l, :d)
        ");

        try {
            $stmt->execute([
                ':proc' => $processId,
                ':l' => $url,
                ':d' => $depth
            ]);
            return false; // New link
        } catch (\Exception $e) {
            $msg = $e->getMessage();
            if (stripos($msg, 'Duplicate') !== false || stripos($msg, '1062') !== false) {
                $this->stats['duplicates_skipped']++;
                return true; // Already seen
            }
            throw $e;
        }
    }

    /**
     * Clear seen links for a process
     *
     * @param string $processId Process ID
     */
    public function clearSeenLinks(string $processId): void {
        $pdo = $this->db->getConnection();
        $stmt = $pdo->prepare("DELETE FROM " . self::TABLE_SEEN_LINKS . " WHERE process_id = ?");
        $stmt->execute([$processId]);
    }

    /**
     * Check if URL already exists in pinfeeds
     *
     * @param string $url URL to check
     * @return bool True if exists
     */
    public function urlExistsInPinfeeds(string $url): bool {
        $pdo = $this->db->getConnection();
        $stmt = $pdo->prepare("SELECT 1 FROM " . self::TABLE_PINFEEDS . " WHERE link = ? LIMIT 1");
        $stmt->execute([$url]);
        return (bool) $stmt->fetchColumn();
    }

    /**
     * Generate random author name
     *
     * @return string Full author name
     */
    public function generateRandomAuthor(): string {
        if (count($this->authorNames) < 2) {
            return 'Anonymous User';
        }

        $firstName = $this->authorNames[array_rand($this->authorNames)];
        $lastName = $this->authorNames[array_rand($this->authorNames)];

        return $firstName . ' ' . $lastName;
    }

    // ============================================
    // HTML EXTRACTION FUNCTIONS
    // ============================================

    /**
     * Get DOMDocument from HTML
     */
    private function getDomDocument(string $html): ?\DOMDocument {
        if (empty($html)) {
            return null;
        }

        libxml_use_internal_errors(true);
        $dom = new \DOMDocument();
        $dom->loadHTML('<?xml encoding="UTF-8">' . $html, LIBXML_NOWARNING | LIBXML_NOERROR);
        libxml_clear_errors();

        return $dom;
    }

    /**
     * Extract title from page
     */
    public function getTitle(\DOMXPath $xpath): string {
        $paths = [
            '//meta[@property="og:title"]/@content',
            '//title',
            '//h1'
        ];

        foreach ($paths as $p) {
            $nodes = $xpath->query($p);
            if ($nodes && $nodes->length > 0) {
                $val = trim($nodes->item(0)->nodeValue);
                if ($val) return mb_substr($val, 0, 500);
            }
        }

        return 'No title';
    }

    /**
     * Extract description from page
     */
    public function getDescription(\DOMXPath $xpath): string {
        $paths = [
            '//meta[@property="og:description"]/@content',
            '//meta[@name="description"]/@content',
            '//p'
        ];

        foreach ($paths as $p) {
            $nodes = $xpath->query($p);
            if ($nodes && $nodes->length > 0) {
                $val = trim($nodes->item(0)->nodeValue);
                if ($val) return mb_substr($val, 0, 2000);
            }
        }

        return 'No description';
    }

    /**
     * Extract thumbnail from page
     */
    public function getThumbnail(\DOMXPath $xpath, string $baseURL): string {
        $queries = [
            '//meta[@property="og:image:secure_url"]/@content',
            '//meta[@property="og:image:url"]/@content',
            '//meta[@property="og:image"]/@content',
            '//meta[@name="twitter:image:src"]/@content',
            '//meta[@name="twitter:image"]/@content',
            '//link[@rel="apple-touch-icon"]/@href',
            '//link[@rel="apple-touch-icon-precomposed"]/@href',
            '//img[contains(@class, "post-thumbnail")]/@src',
            '//figure/img/@src',
            '//div[@class="post-thumbnail"]/img/@src',
            '//img[contains(@class, "wp-post-image")]/@src',
            '//video/@poster',
            '//div[contains(@class, "video-thumbnail")]/img/@src',
            '//img/@src'
        ];

        foreach ($queries as $q) {
            $nodes = $xpath->query($q);
            if ($nodes && $nodes->length > 0) {
                $url = trim($nodes->item(0)->nodeValue);
                // Add scheme if missing
                if (parse_url($url, PHP_URL_SCHEME) === null) {
                    $url = rtrim($baseURL, '/') . '/' . ltrim($url, '/');
                }
                if (filter_var($url, FILTER_VALIDATE_URL)) {
                    return $url;
                }
            }
        }

        return 'fallback_image.jpg';
    }

    /**
     * Extract favicon from page
     */
    public function getFavicon(\DOMXPath $xpath, string $baseURL): string {
        $queries = [
            '//link[@rel="icon"]/@href',
            '//link[@rel="shortcut icon"]/@href'
        ];

        foreach ($queries as $q) {
            $nodes = $xpath->query($q);
            if ($nodes && $nodes->length > 0 && $nodes->item(0)->nodeValue) {
                $val = trim($nodes->item(0)->nodeValue);
                if (parse_url($val, PHP_URL_SCHEME) === null) {
                    $val = rtrim($baseURL, '/') . '/' . ltrim($val, '/');
                }
                if (filter_var($val, FILTER_VALIDATE_URL)) {
                    return $val;
                }
            }
        }

        return 'default_favicon.ico';
    }

    /**
     * Get canonical URL
     */
    public function getCanonicalUrl(\DOMXPath $xpath, string $originalUrl): string {
        $nodes = $xpath->query('//link[@rel="canonical"]/@href');
        if ($nodes && $nodes->length > 0) {
            $canon = trim($nodes->item(0)->nodeValue);
            if (filter_var($canon, FILTER_VALIDATE_URL)) {
                return $canon;
            }
        }
        return $originalUrl;
    }

    /**
     * Extract embed code for video platforms
     */
    public function extractEmbedCode(string $url): string {
        $domain = parse_url($url, PHP_URL_HOST);
        $domain = strtolower($domain ?? '');

        // YouTube
        if (strpos($domain, 'youtube.com') !== false || strpos($domain, 'youtu.be') !== false) {
            $videoId = $this->getYouTubeVideoId($url);
            if ($videoId) {
                return "<iframe src=\"https://www.youtube.com/embed/{$videoId}\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen></iframe>";
            }
        }

        // Vimeo
        if (strpos($domain, 'vimeo.com') !== false) {
            $videoId = $this->getVimeoVideoId($url);
            if ($videoId) {
                return "<iframe src=\"https://player.vimeo.com/video/{$videoId}\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen></iframe>";
            }
        }

        // Dailymotion
        if (strpos($domain, 'dailymotion.com') !== false) {
            $videoId = $this->getDailymotionVideoId($url);
            if ($videoId) {
                return "<iframe src=\"https://www.dailymotion.com/embed/video/{$videoId}\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen></iframe>";
            }
        }

        // TikTok
        if (strpos($domain, 'tiktok.com') !== false) {
            $videoId = $this->getTikTokVideoId($url);
            if ($videoId) {
                return "<blockquote class=\"tiktok-embed\" data-video-id=\"{$videoId}\"></blockquote>";
            }
        }

        // Instagram
        if (strpos($domain, 'instagram.com') !== false) {
            return "<blockquote class=\"instagram-media\" data-instgrm-permalink=\"{$url}\"></blockquote>";
        }

        // Twitter/X
        if (strpos($domain, 'twitter.com') !== false || strpos($domain, 'x.com') !== false) {
            return "<blockquote class=\"twitter-tweet\"><a href=\"{$url}\"></a></blockquote>";
        }

        return '';
    }

    /**
     * Extract YouTube video ID
     */
    private function getYouTubeVideoId(string $url): ?string {
        // Standard youtube.com/watch?v=VIDEO_ID
        parse_str(parse_url($url, PHP_URL_QUERY) ?? '', $query);
        if (!empty($query['v'])) {
            return $query['v'];
        }

        // Short youtu.be/VIDEO_ID
        if (strpos($url, 'youtu.be') !== false) {
            $path = parse_url($url, PHP_URL_PATH);
            return trim($path, '/');
        }

        // Shorts youtube.com/shorts/VIDEO_ID
        if (strpos($url, '/shorts/') !== false) {
            $parts = explode('/shorts/', $url);
            return explode('?', $parts[1] ?? '')[0];
        }

        return null;
    }

    /**
     * Extract Vimeo video ID
     */
    private function getVimeoVideoId(string $url): ?string {
        $parts = explode('/', parse_url($url, PHP_URL_PATH) ?? '');
        foreach ($parts as $part) {
            if (is_numeric($part)) {
                return $part;
            }
        }
        return null;
    }

    /**
     * Extract Dailymotion video ID
     */
    private function getDailymotionVideoId(string $url): ?string {
        $path = parse_url($url, PHP_URL_PATH) ?? '';
        if (preg_match('/\/video\/([a-zA-Z0-9]+)/', $path, $matches)) {
            return $matches[1];
        }
        return null;
    }

    /**
     * Extract TikTok video ID
     */
    private function getTikTokVideoId(string $url): ?string {
        $path = parse_url($url, PHP_URL_PATH) ?? '';
        $parts = explode('/', trim($path, '/'));
        return end($parts) ?: null;
    }

    /**
     * Extract tags from title
     */
    public function extractTagsFromTitle(string $title): array {
        // Remove punctuation
        $title = preg_replace("/[.,\/#!\$%\^&\*;:{}=\-_~()\[\]\"']/", "", $title);
        $title = mb_strtolower($title);
        $words = explode(" ", $title);

        // Filter common words and short words
        $keywords = array_filter($words, function($w) {
            return !in_array($w, $this->commonWords) && mb_strlen($w) > 2;
        });

        // Return max 10 tags
        return array_slice(array_values($keywords), 0, 10);
    }

    // ============================================
    // STATISTICS AND UTILITIES
    // ============================================

    /**
     * Get import statistics
     */
    public function getStats(): array {
        return $this->stats;
    }

    /**
     * Reset statistics
     */
    public function resetStats(): void {
        $this->stats = [
            'total_processed' => 0,
            'imported_pinfeeds' => 0,
            'imported_feed_data' => 0,
            'users_created' => 0,
            'duplicates_skipped' => 0,
            'errors' => 0
        ];
    }

    /**
     * Get count of records in pinfeeds
     */
    public function getPinfeedsCount(): int {
        $pdo = $this->db->getConnection();
        $stmt = $pdo->query("SELECT COUNT(*) FROM " . self::TABLE_PINFEEDS);
        return (int) $stmt->fetchColumn();
    }

    /**
     * Get count of users
     */
    public function getUsersCount(): int {
        $pdo = $this->db->getConnection();
        $stmt = $pdo->query("SELECT COUNT(*) FROM " . self::TABLE_USERS);
        return (int) $stmt->fetchColumn();
    }

    /**
     * Get count of domains in feed_data
     */
    public function getDomainsCount(): int {
        $pdo = $this->db->getConnection();
        $stmt = $pdo->query("SELECT COUNT(*) FROM " . self::TABLE_FEED_DATA);
        return (int) $stmt->fetchColumn();
    }

    /**
     * Get recent imports from pinfeeds
     *
     * @param int $limit Number of records
     * @return array Recent pinfeeds records
     */
    public function getRecentImports(int $limit = 10): array {
        $pdo = $this->db->getConnection();
        $stmt = $pdo->prepare("
            SELECT id, title, link, author, source_domain, pubDate
            FROM " . self::TABLE_PINFEEDS . "
            ORDER BY id DESC
            LIMIT ?
        ");
        $stmt->execute([$limit]);
        return $stmt->fetchAll();
    }

    /**
     * Get top domains by count
     *
     * @param int $limit Number of domains
     * @return array Top domains with counts
     */
    public function getTopDomains(int $limit = 10): array {
        $pdo = $this->db->getConnection();
        $stmt = $pdo->prepare("
            SELECT source_domain, COUNT(*) as count
            FROM " . self::TABLE_PINFEEDS . "
            GROUP BY source_domain
            ORDER BY count DESC
            LIMIT ?
        ");
        $stmt->execute([$limit]);
        return $stmt->fetchAll();
    }
}
