<?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";

    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;
    }

    /**
     * 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;
    }

    /**
     * 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)
    {
        $db = InstanceDB::getInstanceDB();
        $pvTable = $db->prefix . 'af_stats_pageviews';
        $metaTable = LotMetaData::getTableName();

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

        $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-%%'
             GROUP BY m.postId
             ORDER BY recent_views DESC
             LIMIT %d",
            $since, $limit
        );

        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();

        return $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
             LEFT 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
        ));
    }

    /**
     * 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;
    }
}
