<?php
/**
 * Live Traffic Query Builder
 *
 * @package VMPFence
 * @subpackage Tools
 * @since 2.1.0
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Class VMPFence_Live_Traffic_Query
 *
 * Query builder for live traffic with advanced filtering and grouping
 *
 * @since 2.1.0
 */
class VMPFence_Live_Traffic_Query {

    /**
     * Filter collection
     * @var VMPFence_Live_Traffic_Filter_Collection|null
     */
    private $filters = null;

    /**
     * Group by object
     * @var VMPFence_Live_Traffic_GroupBy|null
     */
    private $groupBy = null;

    /**
     * Start date (timestamp)
     * @var float|null
     */
    private $startDate = null;

    /**
     * End date (timestamp)
     * @var float|null
     */
    private $endDate = null;

    /**
     * Result limit
     * @var int
     */
    private $limit = 50;

    /**
     * Result offset
     * @var int
     */
    private $offset = 0;

    /**
     * Table name
     * @var string
     */
    private $tableName;

    /**
     * Valid query parameters
     * @var array
     */
    private $validParams = array(
        'ip',
        'url',
        'referer',
        'ua',
        'statuscode',
        'action',
        'userid',
        'username',
        'type',
        'isgoogle',
        'jsrun',
        'actiondescription',
    );

    /**
     * Constructor
     *
     * @since 2.1.0
     * @param array $args Optional query arguments
     */
    public function __construct($args = array()) {
        global $wpdb;
        $this->tableName = $wpdb->prefix . 'vmpfence_hits';

        // Initialize filter collection
        $this->filters = new VMPFence_Live_Traffic_Filter_Collection();

        // Parse arguments
        if (!empty($args)) {
            if (isset($args['limit'])) {
                $this->setLimit($args['limit']);
            }
            if (isset($args['offset'])) {
                $this->setOffset($args['offset']);
            }
            if (isset($args['startDate'])) {
                $this->setStartDate($args['startDate']);
            }
            if (isset($args['endDate'])) {
                $this->setEndDate($args['endDate']);
            }
        }
    }

    /**
     * Set filters collection
     *
     * @since 2.1.0
     * @param VMPFence_Live_Traffic_Filter_Collection $filters Filter collection
     * @return void
     */
    public function setFilters($filters) {
        $this->filters = $filters;
    }

    /**
     * Set group by
     *
     * @since 2.1.0
     * @param VMPFence_Live_Traffic_GroupBy|null $groupBy Group by object
     * @return void
     */
    public function setGroupBy($groupBy) {
        $this->groupBy = $groupBy;
    }

    /**
     * Set start date
     *
     * @since 2.1.0
     * @param float $timestamp Start date timestamp
     * @return void
     */
    public function setStartDate($timestamp) {
        $this->startDate = floatval($timestamp);
    }

    /**
     * Set end date
     *
     * @since 2.1.0
     * @param float $timestamp End date timestamp
     * @return void
     */
    public function setEndDate($timestamp) {
        $this->endDate = floatval($timestamp);
    }

    /**
     * Set result limit
     *
     * @since 2.1.0
     * @param int $limit Result limit
     * @return void
     */
    public function setLimit($limit) {
        $this->limit = max(1, intval($limit));
    }

    /**
     * Set result offset
     *
     * @since 2.1.0
     * @param int $offset Result offset
     * @return void
     */
    public function setOffset($offset) {
        $this->offset = max(0, intval($offset));
    }

    /**
     * Check if parameter is valid
     *
     * @since 2.1.0
     * @param string $param Parameter name
     * @return bool
     */
    public function isValidParam($param) {
        return in_array(strtolower($param), $this->validParams);
    }

    /**
     * Get column name from parameter
     *
     * @since 2.1.0
     * @param string $param Parameter name
     * @return string Column name
     */
    public function getColumnName($param) {
        $param = strtolower($param);
        
        $mapping = array(
            'ip' => 'IP',
            'url' => 'URL',
            'referer' => 'referer',
            'ua' => 'UA',
            'statuscode' => 'statusCode',
            'action' => 'action',
            'userid' => 'userID',
            'username' => 'user_login',
            'type' => 'jsRun',
            'isgoogle' => 'isGoogle',
            'jsrun' => 'jsRun',
            'actiondescription' => 'actionDescription',
        );

        return isset($mapping[$param]) ? $mapping[$param] : $param;
    }

    /**
     * Build SQL query
     *
     * @since 2.1.0
     * @return string SQL query
     */
    public function buildQuery() {
        global $wpdb;

        // Build SELECT clause
        if ($this->groupBy) {
            $groupColumn = $this->groupBy->getParam();
            $selectClause = "SELECT {$groupColumn}, COUNT(*) as hitCount, MAX(h.ctime) as lastHit";
        } else {
            $selectClause = "SELECT h.*, u.user_login, u.display_name, l.fail as loginFail, l.username as loginUsername";
        }

        // Build FROM clause with JOINs
        $fromClause = "FROM {$this->tableName} h";
        $fromClause .= " LEFT JOIN {$wpdb->users} u ON h.userID = u.ID";
        $fromClause .= " LEFT JOIN {$wpdb->prefix}vmpfence_logins l ON h.id = l.hitID";

        // Build WHERE clause
        $whereConditions = array();

        // Add filter conditions
        if ($this->filters && $this->filters->count() > 0) {
            $filterSQL = $this->filters->toSQL();
            if (!empty($filterSQL)) {
                $whereConditions[] = $filterSQL;
            }
        }

        // Add date range
        if ($this->startDate !== null) {
            $whereConditions[] = $wpdb->prepare("h.ctime >= %f", $this->startDate);
        }
        if ($this->endDate !== null) {
            $whereConditions[] = $wpdb->prepare("h.ctime <= %f", $this->endDate);
        }

        // Exclude internal actions
        $whereConditions[] = "(h.action IS NULL OR h.action NOT IN ('logged:waf', 'scan:detectproxy'))";

        $whereClause = '';
        if (!empty($whereConditions)) {
            $whereClause = 'WHERE ' . implode(' AND ', $whereConditions);
        }

        // Build GROUP BY clause
        $groupByClause = '';
        if ($this->groupBy) {
            $groupByClause = 'GROUP BY ' . $this->groupBy->toSQL();
        }

        // Build ORDER BY clause
        if ($this->groupBy) {
            $orderByClause = 'ORDER BY hitCount DESC, lastHit DESC';
        } else {
            $orderByClause = 'ORDER BY h.ctime DESC';
        }

        // Build LIMIT/OFFSET clause
        $limitClause = $wpdb->prepare("LIMIT %d OFFSET %d", $this->limit, $this->offset);

        // Combine all clauses
        $query = "{$selectClause} {$fromClause} {$whereClause} {$groupByClause} {$orderByClause} {$limitClause}";

        return $query;
    }

    /**
     * Execute query and return results
     *
     * @since 2.1.0
     * @return array Query results
     */
    public function execute() {
        global $wpdb;

        // Build and execute query
        $query = $this->buildQuery();
        $results = $wpdb->get_results($query, ARRAY_A);

        if (empty($results)) {
            return array();
        }

        // Post-process results
        foreach ($results as &$row) {
            // Convert binary IP to string
            if (isset($row['IP']) && !empty($row['IP'])) {
                $row['IP'] = VMPFence_Request_Model::binaryToIP($row['IP']);
            }

            // Format timestamps
            if (isset($row['ctime'])) {
                $row['ctime_formatted'] = date('Y-m-d H:i:s', (int)$row['ctime']);
                // Add formatted_time for frontend compatibility
                $row['formatted_time'] = date('m/d/Y g:i:s A', (int)$row['ctime']);
            }
            if (isset($row['attackLogTime'])) {
                $row['attackLogTime_formatted'] = date('Y-m-d H:i:s', (int)$row['attackLogTime']);
                // Use attackLogTime if ctime not available
                if (!isset($row['formatted_time'])) {
                    $row['formatted_time'] = date('m/d/Y g:i:s A', (int)$row['attackLogTime']);
                }
            }
            if (isset($row['lastHit'])) {
                $row['lastHit_formatted'] = date('Y-m-d H:i:s', (int)$row['lastHit']);
            }
            
            // Get hostname
            if (isset($row['IP'])) {
                $row['hostname'] = VMPFence_Live_Traffic_Model::get_hostname($row['IP']);
            }
            
            // Determine traffic type
            $row['type'] = $this->getTrafficType($row);
            
            // Get response code
            $row['response'] = !empty($row['statusCode']) ? $row['statusCode'] : 200;

            // Get country info from GeoIP lookup
            $country_info = VMPFence_Live_Traffic_Model::get_country_info(isset($row['IP']) ? $row['IP'] : '');
            $row['countryCode'] = $country_info['code'];
            $row['countryName'] = $country_info['name'];
            $row['countryFlag'] = $country_info['flag'];
        }

        return $results;
    }
    
    /**
     * Get traffic type based on row data
     *
     * @since 2.1.0
     * @param array $row Traffic row data
     * @return string Traffic type
     */
    private function getTrafficType($row) {
        if (!empty($row['action']) && $row['action'] !== '') {
            return 'blocked';
        }
        
        if (isset($row['isGoogle']) && $row['isGoogle'] == 1) {
            return 'bot';
        }
        
        if (isset($row['jsRun']) && $row['jsRun'] == 1) {
            return 'human';
        }
        
        return 'unspecified';
    }

    /**
     * Get total count (without limit/offset)
     *
     * @since 2.1.0
     * @return int Total count
     */
    public function getTotalCount() {
        global $wpdb;

        // Build count query (similar to buildQuery but with COUNT(*))
        $countClause = "SELECT COUNT(DISTINCT h.id) as total";
        
        $fromClause = "FROM {$this->tableName} h";
        $fromClause .= " LEFT JOIN {$wpdb->users} u ON h.userID = u.ID";
        $fromClause .= " LEFT JOIN {$wpdb->prefix}vmpfence_logins l ON h.id = l.hitID";

        $whereConditions = array();

        if ($this->filters && $this->filters->count() > 0) {
            $filterSQL = $this->filters->toSQL();
            if (!empty($filterSQL)) {
                $whereConditions[] = $filterSQL;
            }
        }

        if ($this->startDate !== null) {
            $whereConditions[] = $wpdb->prepare("h.ctime >= %f", $this->startDate);
        }
        if ($this->endDate !== null) {
            $whereConditions[] = $wpdb->prepare("h.ctime <= %f", $this->endDate);
        }

        $whereConditions[] = "(h.action IS NULL OR h.action NOT IN ('logged:waf', 'scan:detectproxy'))";

        $whereClause = '';
        if (!empty($whereConditions)) {
            $whereClause = 'WHERE ' . implode(' AND ', $whereConditions);
        }

        $query = "{$countClause} {$fromClause} {$whereClause}";
        $result = $wpdb->get_var($query);

        return intval($result);
    }
}
