<?php

namespace auctionforge\inc\model;

use auctionforge\inc\InstanceDB;

class LotAnalytics
{
    const TABLE_LOT_ANALYTICS = "af_lot_analytics";
    const TABLE_LOT_FAVORITES = "af_lot_favorites";
    const TABLE_LOT_SHARES = "af_lot_shares";

    public static function getAnalyticsTableName()
    {
        $db = InstanceDB::getInstanceDB();
        return $db->prefix . self::TABLE_LOT_ANALYTICS;
    }

    public static function getFavoritesTableName()
    {
        $db = InstanceDB::getInstanceDB();
        return $db->prefix . self::TABLE_LOT_FAVORITES;
    }

    public static function getSharesTableName()
    {
        $db = InstanceDB::getInstanceDB();
        return $db->prefix . self::TABLE_LOT_SHARES;
    }

    /**
     * Increment view count for a lot. Called server-side when a lot page is viewed.
     */
    public static function recordView($postId, $termId = 0, $visitorHash = '')
    {
        if (empty($postId)) return false;

        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();

        $sql = $db->prepare(
            "INSERT INTO `{$table}` (post_id, term_id, view_count, unique_visitors, favorite_count, avg_time_on_page, first_viewed_at, last_viewed_at, unique_hashes)
             VALUES (%d, %d, 1, 1, 0, 0, NOW(), NOW(), %s)
             ON DUPLICATE KEY UPDATE
                view_count = view_count + 1,
                last_viewed_at = NOW(),
                term_id = IF(VALUES(term_id) > 0, VALUES(term_id), term_id)",
            $postId,
            $termId,
            $visitorHash
        );

        $result = $db->query($sql);

        // Update unique visitors using the hash
        if (!empty($visitorHash)) {
            $row = $db->get_row($db->prepare(
                "SELECT unique_hashes FROM `{$table}` WHERE post_id = %d", $postId
            ));
            if ($row && !empty($row->unique_hashes)) {
                $hashes = explode(',', $row->unique_hashes);
                if (!in_array($visitorHash, $hashes)) {
                    $hashes[] = $visitorHash;
                    // Keep only last 1000 hashes to limit storage
                    if (count($hashes) > 1000) {
                        $hashes = array_slice($hashes, -1000);
                    }
                    $db->update(
                        $table,
                        array(
                            'unique_visitors' => count($hashes),
                            'unique_hashes' => implode(',', $hashes)
                        ),
                        array('post_id' => $postId),
                        array('%d', '%s'),
                        array('%d')
                    );
                }
            }
        }

        return $result;
    }


    /**
     * Increment bid count for a lot.
     */
    public static function recordBid($postId, $termId = 0)
    {
        if (empty($postId)) return false;

        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();

        $sql = $db->prepare(
            "INSERT INTO `{$table}` (post_id, term_id, view_count, unique_visitors, favorite_count, bid_count, first_viewed_at, last_viewed_at)
             VALUES (%d, %d, 0, 0, 0, 1, NOW(), NOW())
             ON DUPLICATE KEY UPDATE
                bid_count = bid_count + 1,
                last_viewed_at = NOW()",
            $postId,
            $termId
        );

        return $db->query($sql);
    }

    /**
     * Get bid count for a specific lot
     */
    public static function getBidCount($postId)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();

        $count = $db->get_var($db->prepare(
            "SELECT bid_count FROM `{$table}` WHERE post_id = %d", $postId
        ));

        return $count ? intval($count) : 0;
    }

    /**
     * Get effective bid count with fallback for BidSpirit-originated bids.
     * If tracked bid_count is 0 but there is a leading bid, infer a minimum count:
     *   - leadingBid == startPrice => at least 1 bid
     *   - leadingBid > startPrice  => at least 2 bids
     * If tracked bid_count >= 2, always use the tracked value.
     */
    public static function getEffectiveBidCount($postId)
    {
        $trackedCount = self::getBidCount($postId);

        // Look up leadingBid and startPrice from lot metadata
        $leadingBid = floatval(LotMetaData::getMetaField('leadingBid', $postId));
        $startPrice = floatval(LotMetaData::getMetaField('startPrice', $postId));

        // If leading bid exceeds start price, there must be at least 2 bids
        if ($leadingBid > 0 && $leadingBid > $startPrice && $startPrice > 0) {
            return max($trackedCount, 2);
        }

        // If leading bid equals start price, there is at least 1 bid
        if ($leadingBid > 0) {
            return max($trackedCount, 1);
        }

        // If we have 2+ tracked bids, trust the tracked count
        return $trackedCount;
    }
    /**
     * Get view count for a specific lot
     */
    public static function getViewCount($postId)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();

        $count = $db->get_var($db->prepare(
            "SELECT view_count FROM `{$table}` WHERE post_id = %d", $postId
        ));

        return $count ? intval($count) : 0;
    }

    /**
     * Get analytics for a specific lot
     */
    public static function getLotAnalytics($postId)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();

        return $db->get_row($db->prepare(
            "SELECT * FROM `{$table}` WHERE post_id = %d", $postId
        ));
    }

    /**
     * Get most viewed lots
     */
    public static function getMostViewed($limit = 10, $termId = 0, $period = '')
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();
        $metaTable = LotMetaData::getTableName();

        $where = "WHERE 1=1";
        $params = array();

        if ($termId > 0) {
            $where .= " AND a.term_id = %d";
            $params[] = $termId;
        }

        if (!empty($period)) {
            $where .= " AND a.last_viewed_at >= %s";
            $params[] = date('Y-m-d H:i:s', strtotime("-{$period}"));
        }

        $params[] = $limit;

        $sql = "SELECT a.*, m.name as lot_name, m.itemIndex, m.startPrice, m.soldBid,
                       m.leadingBid, m.estimatedPrice, m.imagesListStr, m.imagesBase, m.imagesCloned,
                       p.post_title, p.post_name,
                       t.name as auction_name
                FROM `{$table}` a
                INNER JOIN {$db->posts} p ON a.post_id = p.ID AND p.post_status = 'publish'
                LEFT JOIN `{$metaTable}` m ON a.post_id = m.postId
                LEFT JOIN {$db->terms} t ON a.term_id = t.term_id
                {$where}
                ORDER BY a.view_count DESC
                LIMIT %d";

        return $db->get_results($db->prepare($sql, $params));
    }

    /**
     * Get most favorited lots
     */
    public static function getMostFavorited($limit = 10, $termId = 0)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();
        $metaTable = LotMetaData::getTableName();

        $where = "WHERE a.favorite_count > 0";
        $params = array();

        if ($termId > 0) {
            $where .= " AND a.term_id = %d";
            $params[] = $termId;
        }

        $params[] = $limit;

        $sql = "SELECT a.*, m.name as lot_name, m.itemIndex, m.startPrice, m.soldBid,
                       m.leadingBid, m.estimatedPrice, m.imagesListStr, m.imagesBase, m.imagesCloned,
                       p.post_title, p.post_name,
                       t.name as auction_name
                FROM `{$table}` a
                INNER JOIN {$db->posts} p ON a.post_id = p.ID AND p.post_status = 'publish'
                LEFT JOIN `{$metaTable}` m ON a.post_id = m.postId
                LEFT JOIN {$db->terms} t ON a.term_id = t.term_id
                {$where}
                ORDER BY a.favorite_count DESC
                LIMIT %d";

        return $db->get_results($db->prepare($sql, $params));
    }

    /**
     * Get trending lots (most views in recent period)
     */
    public static function getTrending($limit = 10, $days = 7, $termId = 0)
    {
        $db = InstanceDB::getInstanceDB();
        $pvTable = $db->prefix . 'af_stats_pageviews';
        $metaTable = LotMetaData::getTableName();

        $since = date('Y-m-d H:i:s', strtotime("-{$days} days"));

        $termFilter = '';
        $params = array($since);
        if ($termId > 0) {
            $termFilter = ' AND m.termId = %d';
            $params[] = $termId;
        }
        $params[] = $limit;

        $sql = $db->prepare(
            "SELECT m.postId as post_id, m.termId as term_id, COUNT(*) as recent_views,
                    COUNT(DISTINCT pv.visitor_hash) as recent_unique,
                    m.name as lot_name, m.itemIndex, m.startPrice, m.soldBid,
                    m.leadingBid, m.estimatedPrice, m.imagesListStr, m.imagesBase, m.imagesCloned,
                    p.post_title, p.post_name,
                    t.name as auction_name
             FROM `{$pvTable}` pv
             INNER JOIN {$db->posts} p ON p.post_name = SUBSTRING_INDEX(TRIM(TRAILING '/' FROM pv.url_path), '/', -1)
                AND p.post_type = 'lot' AND p.post_status = 'publish'
             LEFT JOIN `{$metaTable}` m ON p.ID = m.postId
             LEFT JOIN {$db->terms} t ON m.termId = t.term_id
             WHERE pv.created_at >= %s
               AND pv.url_path LIKE '%%/lot-%%'
               {$termFilter}
             GROUP BY m.postId
             ORDER BY recent_views DESC
             LIMIT %d",
            $params
        );

        return $db->get_results($sql);
    }

    /**
     * Record a favorite action (add or remove)
     */
    public static function recordFavorite($userEmail, $postId, $termId = 0, $isFavorite = true)
    {
        if (empty($userEmail) || empty($postId)) return false;

        $db = InstanceDB::getInstanceDB();
        $favTable = self::getFavoritesTableName();
        $analyticsTable = self::getAnalyticsTableName();

        if ($isFavorite) {
            // Add favorite
            $db->query($db->prepare(
                "INSERT IGNORE INTO `{$favTable}` (user_email, post_id, term_id, created_at)
                 VALUES (%s, %d, %d, NOW())",
                $userEmail, $postId, $termId
            ));
        } else {
            // Remove favorite
            $db->delete($favTable, array(
                'user_email' => $userEmail,
                'post_id' => $postId,
            ), array('%s', '%d'));
        }

        // Update favorite count in analytics table
        $count = $db->get_var($db->prepare(
            "SELECT COUNT(*) FROM `{$favTable}` WHERE post_id = %d", $postId
        ));

        $db->query($db->prepare(
            "INSERT INTO `{$analyticsTable}` (post_id, term_id, view_count, unique_visitors, favorite_count, avg_time_on_page, first_viewed_at, last_viewed_at, unique_hashes)
             VALUES (%d, %d, 0, 0, %d, 0, NOW(), NOW(), '')
             ON DUPLICATE KEY UPDATE favorite_count = %d, term_id = IF(VALUES(term_id) > 0, VALUES(term_id), term_id)",
            $postId, $termId, $count, $count
        ));

        return true;
    }

    /**
     * Check if a user has favorited a lot
     */
    public static function isFavorited($userEmail, $postId)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getFavoritesTableName();

        return (bool)$db->get_var($db->prepare(
            "SELECT COUNT(*) FROM `{$table}` WHERE user_email = %s AND post_id = %d",
            $userEmail, $postId
        ));
    }

    /**
     * Get favorite count for a lot
     */
    public static function getFavoriteCount($postId)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getFavoritesTableName();

        $count = $db->get_var($db->prepare(
            "SELECT COUNT(*) FROM `{$table}` WHERE post_id = %d", $postId
        ));

        return $count ? intval($count) : 0;
    }

    /**
     * Get auction-level statistics
     */
    public static function getAuctionStats($termId)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();

        return $db->get_row($db->prepare(
            "SELECT SUM(view_count) as total_views,
                    SUM(unique_visitors) as total_unique,
                    SUM(favorite_count) as total_favorites,
                    COUNT(*) as total_lots,
                    MAX(view_count) as max_views,
                    AVG(view_count) as avg_views
             FROM `{$table}` WHERE term_id = %d",
            $termId
        ));
    }

    /**
     * Get all auction stats for comparison
     */
    public static function getAllAuctionStats($limit = 20)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();
        $pvTable = $db->prefix . 'af_stats_pageviews';
        $metaTable = LotMetaData::getTableName();

        $results = $db->get_results($db->prepare(
            "SELECT a.term_id,
                    t.name as auction_name,
                    SUM(a.view_count) as total_views,
                    SUM(a.favorite_count) as total_favorites,
                    COUNT(*) as lot_count,
                    AVG(a.view_count) as avg_views_per_lot
             FROM `{$table}` a
             INNER JOIN {$db->terms} t ON a.term_id = t.term_id
             WHERE a.term_id > 0
             GROUP BY a.term_id
             ORDER BY total_views DESC
             LIMIT %d",
            $limit
        ));

        // Enrich with auction date and state from term meta
        $now = current_time('Y-m-d H:i');
        foreach ($results as &$row) {
            $auctionDate = get_term_meta($row->term_id, 'auction_date', true);
            $auctionState = get_term_meta($row->term_id, 'auction_state', true);
            $row->auction_date = $auctionDate ?: '';
            $row->auction_state = $auctionState ?: '';
            if (!empty($auctionDate)) {
                $row->is_past = ($auctionDate < $now || strtoupper($auctionState) === 'ENDED') ? 1 : 0;
            } else {
                $row->is_past = (strtoupper($auctionState) === 'ENDED') ? 1 : 0;
            }
        }
        unset($row);

        return $results;
    }

    /**
     * Backfill analytics from existing pageview data
     */
    public static function backfillFromPageviews()
    {
        $db = InstanceDB::getInstanceDB();
        $pvTable = $db->prefix . 'af_stats_pageviews';
        $analyticsTable = self::getAnalyticsTableName();
        $metaTable = LotMetaData::getTableName();

        // Get lot pageviews grouped by post
        $results = $db->get_results(
            "SELECT p.ID as post_id, m.termId as term_id,
                    COUNT(*) as views, COUNT(DISTINCT pv.visitor_hash) as uniques,
                    MIN(pv.created_at) as first_view, MAX(pv.created_at) as last_view,
                    ROUND(AVG(pv.time_on_page)) as avg_time
             FROM `{$pvTable}` pv
             INNER JOIN {$db->posts} p ON p.post_name = SUBSTRING_INDEX(TRIM(TRAILING '/' FROM pv.url_path), '/', -1)
                AND p.post_type = 'lot' AND p.post_status = 'publish'
             LEFT JOIN `{$metaTable}` m ON p.ID = m.postId
             WHERE pv.url_path LIKE '%/lot-%'
             GROUP BY p.ID"
        );

        $count = 0;
        foreach ($results as $row) {
            $db->query($db->prepare(
                "INSERT INTO `{$analyticsTable}` (post_id, term_id, view_count, unique_visitors, favorite_count, avg_time_on_page, first_viewed_at, last_viewed_at, unique_hashes)
                 VALUES (%d, %d, %d, %d, 0, %d, %s, %s, '')
                 ON DUPLICATE KEY UPDATE
                    view_count = GREATEST(view_count, VALUES(view_count)),
                    unique_visitors = GREATEST(unique_visitors, VALUES(unique_visitors)),
                    avg_time_on_page = VALUES(avg_time_on_page),
                    first_viewed_at = LEAST(first_viewed_at, VALUES(first_viewed_at)),
                    last_viewed_at = GREATEST(last_viewed_at, VALUES(last_viewed_at))",
                $row->post_id, $row->term_id ?: 0, $row->views, $row->uniques,
                $row->avg_time ?: 0, $row->first_view, $row->last_view
            ));
            $count++;
        }

        return $count;
    }

    /**
     * Record a click-through source (catalogue or external)
     */
    public static function recordClickSource($postId, $termId = 0, $fromCatalogue = true)
    {
        if (empty($postId)) return false;
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();
        $col = $fromCatalogue ? 'catalogue_clicks' : 'external_clicks';

        return $db->query($db->prepare(
            "INSERT INTO `{$table}` (post_id, term_id, {$col}, view_count)
             VALUES (%d, %d, 1, 0)
             ON DUPLICATE KEY UPDATE {$col} = {$col} + 1",
            $postId, $termId
        ));
    }

    /**
     * Record a share event
     */
    public static function recordShare($postId, $termId = 0, $channel = 'unknown', $visitorHash = '')
    {
        if (empty($postId)) return false;
        $db = InstanceDB::getInstanceDB();

        // Increment share_count on analytics table
        $table = self::getAnalyticsTableName();
        $db->query($db->prepare(
            "INSERT INTO `{$table}` (post_id, term_id, share_count, view_count)
             VALUES (%d, %d, 1, 0)
             ON DUPLICATE KEY UPDATE share_count = share_count + 1",
            $postId, $termId
        ));

        // Record individual share event
        $sharesTable = self::getSharesTableName();
        return $db->insert($sharesTable, array(
            'post_id' => $postId,
            'term_id' => $termId,
            'channel' => sanitize_text_field($channel),
            'visitor_hash' => $visitorHash,
            'created_at' => current_time('mysql'),
        ));
    }

    /**
     * Get conversion stats: views vs bids/sold for lots
     */
    public static function getConversionStats($termId = 0, $limit = 20)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();
        $metaTable = LotMetaData::getTableName();

        $termFilter = '';
        $params = array();
        if ($termId > 0) {
            $termFilter = 'AND a.term_id = %d';
            $params[] = $termId;
        }
        $params[] = $limit;

        return $db->get_results($db->prepare(
            "SELECT a.post_id, a.view_count, a.unique_visitors, a.favorite_count,
                    a.catalogue_clicks, a.external_clicks, a.share_count,
                    m.name as lot_name, m.itemIndex, m.startPrice, m.soldBid, m.leadingBid,
                    p.post_title,
                    t.name as auction_name,
                    CASE
                        WHEN m.soldBid > 0 THEN 'sold'
                        WHEN m.leadingBid > 0 THEN 'has_bids'
                        ELSE 'no_bids'
                    END as bid_status,
                    CASE
                        WHEN a.view_count > 0 AND m.soldBid > 0 THEN ROUND(100.0 / a.view_count, 1)
                        ELSE 0
                    END as conversion_rate
             FROM `{$table}` a
             INNER JOIN {$db->posts} p ON a.post_id = p.ID
             LEFT JOIN `{$metaTable}` m ON a.post_id = m.postId
             LEFT JOIN {$db->terms} t ON a.term_id = t.term_id
             WHERE a.view_count > 0 {$termFilter}
             ORDER BY a.view_count DESC
             LIMIT %d",
            $params
        ));
    }

    /**
     * Get peak viewing times (hourly distribution)
     */
    public static function getPeakViewingTimes($termId = 0, $days = 30, $from = null, $to = null)
    {
        $db = InstanceDB::getInstanceDB();
        $pvTable = $db->prefix . 'af_stats_pageviews';
        $metaTable = LotMetaData::getTableName();

        // Honour an explicit date range when supplied (matches the dashboard's
        // global range picker); otherwise fall back to a trailing N-day window.
        if (!empty($from) && !empty($to)) {
            $dateClause = 'pv.created_at BETWEEN %s AND %s';
            $params = array($from, $to);
        } else {
            $dateClause = 'pv.created_at >= %s';
            $params = array(date('Y-m-d H:i:s', strtotime("-{$days} days")));
        }
        $termFilter = '';
        if ($termId > 0) {
            $termFilter = 'AND m.termId = %d';
            $params[] = $termId;
        }

        return $db->get_results($db->prepare(
            "SELECT HOUR(pv.created_at) as hour_of_day,
                    DAYOFWEEK(pv.created_at) as day_of_week,
                    COUNT(*) as views
             FROM `{$pvTable}` pv
             INNER JOIN {$db->posts} p ON p.post_name = SUBSTRING_INDEX(TRIM(TRAILING '/' FROM pv.url_path), '/', -1)
                AND p.post_type = 'lot' AND p.post_status = 'publish'
             LEFT JOIN `{$metaTable}` m ON p.ID = m.postId
             WHERE {$dateClause}
               AND pv.url_path LIKE '%%/lot-%%'
               {$termFilter}
             GROUP BY hour_of_day, day_of_week
             ORDER BY hour_of_day, day_of_week",
            $params
        ));
    }

    /**
     * Get pre-sale vs live viewing comparison
     */
    public static function getPreSaleVsLive($termId = 0)
    {
        $db = InstanceDB::getInstanceDB();
        $pvTable = $db->prefix . 'af_stats_pageviews';
        $metaTable = LotMetaData::getTableName();

        $termFilter = '';
        $params = array();
        if ($termId > 0) {
            $termFilter = 'AND m.termId = %d';
            $params[] = $termId;
        }

        // Get auction dates from term meta
        $auctions = $db->get_results(
            "SELECT DISTINCT a.term_id, t.name as auction_name
             FROM `" . self::getAnalyticsTableName() . "` a
             INNER JOIN {$db->terms} t ON a.term_id = t.term_id
             WHERE a.term_id > 0" . ($termId > 0 ? " AND a.term_id = {$termId}" : "")
        );

        $results = array();
        foreach ($auctions as $auction) {
            // Try auction_date first (simpler), fall back to auction_days
            $earliestDay = get_term_meta($auction->term_id, 'auction_date', true);
            if (!empty($earliestDay)) {
                // auction_date may include time like "2025-12-14 16:00", take just the date
                $earliestDay = substr($earliestDay, 0, 10);
            } else {
                $auctionDays = get_term_meta($auction->term_id, 'auction_days', true);
                if (empty($auctionDays) || !is_array($auctionDays)) continue;
                $earliestDay = null;
                foreach ($auctionDays as $day) {
                    $d = is_object($day) ? (isset($day->date) ? $day->date : null) : (isset($day['date']) ? $day['date'] : null);
                    if ($d && (!$earliestDay || $d < $earliestDay)) $earliestDay = $d;
                }
            }
            if (!$earliestDay) continue;

            $preSale = $db->get_var($db->prepare(
                "SELECT COUNT(*) FROM `{$pvTable}` pv
                 INNER JOIN {$db->posts} p ON p.post_name = SUBSTRING_INDEX(TRIM(TRAILING '/' FROM pv.url_path), '/', -1)
                    AND p.post_type = 'lot'
                 INNER JOIN `{$metaTable}` m ON p.ID = m.postId AND m.termId = %d
                 WHERE pv.url_path LIKE '%%/lot-%%' AND pv.created_at < %s",
                $auction->term_id, $earliestDay
            ));

            $live = $db->get_var($db->prepare(
                "SELECT COUNT(*) FROM `{$pvTable}` pv
                 INNER JOIN {$db->posts} p ON p.post_name = SUBSTRING_INDEX(TRIM(TRAILING '/' FROM pv.url_path), '/', -1)
                    AND p.post_type = 'lot'
                 INNER JOIN `{$metaTable}` m ON p.ID = m.postId AND m.termId = %d
                 WHERE pv.url_path LIKE '%%/lot-%%' AND pv.created_at >= %s",
                $auction->term_id, $earliestDay
            ));

            $results[] = (object)array(
                'term_id' => $auction->term_id,
                'auction_name' => $auction->auction_name,
                'auction_date' => $earliestDay,
                'pre_sale_views' => intval($preSale),
                'live_views' => intval($live),
                'total_views' => intval($preSale) + intval($live),
            );
        }

        return $results;
    }

    /**
     * Get category-level analytics
     */
    public static function getCategoryStats($termId = 0, $limit = 20)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();
        $metaTable = LotMetaData::getTableName();

        $termFilter = '';
        $params = array();
        if ($termId > 0) {
            $termFilter = 'AND a.term_id = %d';
            $params[] = $termId;
        }
        $params[] = $limit;

        return $db->get_results($db->prepare(
            "SELECT m.categoryId,
                    SUM(a.view_count) as total_views,
                    SUM(a.unique_visitors) as total_unique,
                    SUM(a.favorite_count) as total_favorites,
                    SUM(a.share_count) as total_shares,
                    COUNT(*) as lot_count,
                    AVG(a.view_count) as avg_views,
                    SUM(CASE WHEN m.soldBid > 0 THEN 1 ELSE 0 END) as sold_count
             FROM `{$table}` a
             LEFT JOIN `{$metaTable}` m ON a.post_id = m.postId
             WHERE m.categoryId IS NOT NULL AND m.categoryId != ''
               {$termFilter}
             GROUP BY m.categoryId
             ORDER BY total_views DESC
             LIMIT %d",
            $params
        ));
    }

    /**
     * Get share analytics by channel
     */
    public static function getShareStats($termId = 0, $limit = 20)
    {
        $db = InstanceDB::getInstanceDB();
        $sharesTable = self::getSharesTableName();
        $metaTable = LotMetaData::getTableName();

        $termFilter = '';
        $params = array();
        if ($termId > 0) {
            $termFilter = 'AND s.term_id = %d';
            $params[] = $termId;
        }
        $params[] = $limit;

        $channels = $db->get_results($db->prepare(
            "SELECT s.channel, COUNT(*) as share_count, COUNT(DISTINCT s.post_id) as lot_count
             FROM `{$sharesTable}` s
             WHERE 1=1 {$termFilter}
             GROUP BY s.channel
             ORDER BY share_count DESC
             LIMIT %d",
            $params
        ));

        // Also get top shared lots
        $params2 = array();
        $termFilter2 = '';
        if ($termId > 0) {
            $termFilter2 = 'AND s.term_id = %d';
            $params2[] = $termId;
        }
        $params2[] = 10;

        $topLots = $db->get_results($db->prepare(
            "SELECT s.post_id, m.name as lot_name, p.post_title, COUNT(*) as share_count,
                    GROUP_CONCAT(DISTINCT s.channel) as channels
             FROM `{$sharesTable}` s
             INNER JOIN {$db->posts} p ON s.post_id = p.ID
             LEFT JOIN `{$metaTable}` m ON s.post_id = m.postId
             WHERE 1=1 {$termFilter2}
             GROUP BY s.post_id
             ORDER BY share_count DESC
             LIMIT %d",
            $params2
        ));

        return (object)array('channels' => $channels, 'top_lots' => $topLots);
    }

    /**
     * Get search-to-view funnel data
     */
    public static function getSearchFunnel($termId = 0, $limit = 20)
    {
        $db = InstanceDB::getInstanceDB();
        $pvTable = $db->prefix . 'af_stats_pageviews';
        $searchTable = $db->prefix . 'af_stats_searches';
        $metaTable = LotMetaData::getTableName();

        // Get searches that led to lot views (same session)
        $termFilter = '';
        $params = array();
        if ($termId > 0) {
            $termFilter = 'AND m.termId = %d';
            $params[] = $termId;
        }
        $params[] = $limit;

        return $db->get_results($db->prepare(
            "SELECT s.search_query as query, COUNT(DISTINCT pv.id) as lot_views,
                    COUNT(DISTINCT s.id) as search_count,
                    COUNT(DISTINCT m.postId) as unique_lots_viewed
             FROM `{$searchTable}` s
             INNER JOIN `{$pvTable}` pv ON pv.session_id = s.session_id
                AND pv.url_path LIKE '%%/lot-%%'
                AND pv.created_at > s.created_at
             INNER JOIN {$db->posts} p ON p.post_name = SUBSTRING_INDEX(TRIM(TRAILING '/' FROM pv.url_path), '/', -1)
                AND p.post_type = 'lot'
             LEFT JOIN `{$metaTable}` m ON p.ID = m.postId
             WHERE s.search_query != ''
               {$termFilter}
             GROUP BY s.search_query
             ORDER BY lot_views DESC
             LIMIT %d",
            $params
        ));
    }

    /**
     * Get lots eligible for "Hot Lot" badge
     */
    public static function getHotLots($threshold = 5, $termId = 0)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();

        $termFilter = '';
        $params = array($threshold);
        if ($termId > 0) {
            $termFilter = 'AND term_id = %d';
            $params[] = $termId;
        }

        return $db->get_col($db->prepare(
            "SELECT post_id FROM `{$table}` WHERE view_count >= %d {$termFilter}",
            $params
        ));
    }

    /**
     * Export analytics data as array (for CSV)
     */
    public static function getExportData($termId = 0)
    {
        $db = InstanceDB::getInstanceDB();
        $table = self::getAnalyticsTableName();
        $metaTable = LotMetaData::getTableName();

        $termFilter = '';
        $params = array();
        if ($termId > 0) {
            $termFilter = 'AND a.term_id = %d';
            $params[] = $termId;
        }

        $sql = "SELECT m.itemIndex as lot_number, COALESCE(m.name, p.post_title) as lot_name,
                       t.name as auction_name,
                       a.view_count, a.unique_visitors, a.catalogue_clicks, a.external_clicks,
                       a.favorite_count, a.share_count,
                       m.startPrice as start_price, m.soldBid as sold_price, m.estimatedPrice as estimated_price,
                       CASE WHEN m.soldBid > 0 THEN 'Sold' WHEN m.leadingBid > 0 THEN 'Has Bids' ELSE 'No Bids' END as status,
                       a.first_viewed_at, a.last_viewed_at
                FROM `{$table}` a
                INNER JOIN {$db->posts} p ON a.post_id = p.ID
                LEFT JOIN `{$metaTable}` m ON a.post_id = m.postId
                LEFT JOIN {$db->terms} t ON a.term_id = t.term_id
                WHERE 1=1 {$termFilter}
                ORDER BY a.view_count DESC";

        if (!empty($params)) {
            return $db->get_results($db->prepare($sql, $params), ARRAY_A);
        }
        return $db->get_results($sql, ARRAY_A);
    }
}
