<?php
/**
 * GeoIP Lookup - Local IP to Country Resolution
 *
 * Uses IP2Location LITE database for standalone country lookup without API dependency.
 * Database: ~500K IP ranges, ~50MB storage, <5ms lookup with caching.
 *
 * @package VMPFence
 * @since 2.2.0
 */

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

class VMPFence_GeoIP_Lookup {
    
    /**
     * Database table name
     *
     * @var string
     */
    private static $table_name = 'vmpfence_geoip';

    /**
     * Lookup country code for an IP address
     *
     * @param string $ip IP address to lookup
     * @return string|false Country code (e.g., 'US', 'CN') or false if not found
     */
    public static function lookup($ip) {
        global $wpdb;

        // Validate IP address
        if (!filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
            return false;
        }

        // Check cache first (24 hour transient)
        $cache_key = 'vmpfence_geoip_' . md5($ip);
        $cached = get_transient($cache_key);
        
        if ($cached !== false) {
            return $cached;
        }

        // Convert IP to long integer for range comparison
        // Note: ip2long() returns negative values for IPs >= 128.0.0.0
        // Convert to unsigned integer for database storage
        $ip_long = ip2long($ip);
        if ($ip_long === false) {
            return false;
        }
        
        // Convert to unsigned integer to handle full IPv4 range
        // PHP ip2long() returns signed int, but database uses unsigned BIGINT
        if ($ip_long < 0) {
            $ip_long = sprintf('%u', $ip_long);
        } else {
            // Ensure it's treated as string for consistency in prepared statement
            $ip_long = (string) $ip_long;
        }

        // Query database for IP range match
        $table = $wpdb->prefix . self::$table_name;
        
        // Verify table exists before querying
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
            return false;
        }
        
        // Use prepared statement for security (esc_sql for table name)
        $query = $wpdb->prepare(
            "SELECT country_code FROM " . esc_sql($table) . " 
             WHERE ip_from <= %d AND ip_to >= %d 
             LIMIT 1",
            $ip_long,
            $ip_long
        );

        $country_code = $wpdb->get_var($query);

        if ($country_code) {
            // Cache for 24 hours
            set_transient($cache_key, $country_code, 24 * HOUR_IN_SECONDS);
            return strtoupper($country_code);
        }

        // Cache negative result for 1 hour (shorter than positive)
        set_transient($cache_key, false, HOUR_IN_SECONDS);
        return false;
    }

    /**
     * Create GeoIP database table
     *
     * @return bool True on success, false on failure
     */
    public static function create_table() {
        global $wpdb;

        $table_name = $wpdb->prefix . self::$table_name;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            ip_from bigint(20) unsigned NOT NULL,
            ip_to bigint(20) unsigned NOT NULL,
            country_code char(2) NOT NULL,
            country_name varchar(64) NOT NULL,
            PRIMARY KEY (id),
            KEY ip_from (ip_from),
            KEY ip_to (ip_to),
            KEY ip_range (ip_from, ip_to),
            KEY country_code (country_code)
        ) {$charset_collate};";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        // Verify table was created
        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") != $table_name) {
            return false;
        }

        return true;
    }

    /**
     * Import IP2Location LITE database from CSV
     *
     * Downloads and imports the free IP2Location LITE database.
     * CSV Format: "ip_from","ip_to","country_code","country_name"
     *
     * @param string $csv_url Optional URL to CSV file (uses default IP2Location LITE if not provided)
     * @return array Status array with 'success', 'message', 'imported_count'
     */
    public static function import_database($csv_url = null) {
        global $wpdb;
        
        // Load WordPress file functions (required for unzip_file and download_url)
        if (!function_exists('download_url')) {
            require_once(ABSPATH . 'wp-admin/includes/file.php');
        }

        // Default to IP2Location LITE DB1 (free, no registration)
        if (!$csv_url) {
            $csv_url = 'https://download.ip2location.com/lite/IP2LOCATION-LITE-DB1.CSV.ZIP';
        }

        $table_name = $wpdb->prefix . self::$table_name;

        // Download ZIP file to temp location
        $zip_file = download_url($csv_url, 300); // 5 minute timeout
        
        if (is_wp_error($zip_file)) {
            return array(
                'success' => false,
                'message' => 'Download failed: ' . $zip_file->get_error_message(),
                'imported_count' => 0
            );
        }

        // Create temp directory for extraction
        $extract_dir = wp_tempnam('geoip-extract');
        @unlink($extract_dir); // Remove the temp file
        wp_mkdir_p($extract_dir); // Create as directory

        // Extract ZIP using WordPress unzip_file (works without ZipArchive extension)
        WP_Filesystem();
        $unzip_result = unzip_file($zip_file, $extract_dir);
        
        // Clean up ZIP file
        @unlink($zip_file);
        
        if (is_wp_error($unzip_result)) {
            @vmpfence_remove_directory($extract_dir);
            return array(
                'success' => false,
                'message' => 'Failed to extract ZIP: ' . $unzip_result->get_error_message(),
                'imported_count' => 0
            );
        }

        // Find CSV file in extracted directory
        $csv_file = null;
        $files = @scandir($extract_dir);
        if ($files) {
            foreach ($files as $file) {
                if (stripos($file, '.csv') !== false) {
                    $csv_file = $extract_dir . '/' . $file;
                    break;
                }
            }
        }

        if (!$csv_file || !file_exists($csv_file)) {
            @vmpfence_remove_directory($extract_dir);
            return array(
                'success' => false,
                'message' => 'CSV file not found in ZIP',
                'imported_count' => 0
            );
        }

        // Clear existing data (escape table name for security)
        $truncate_result = $wpdb->query("TRUNCATE TABLE " . esc_sql($table_name));
        
        if ($truncate_result === false) {
            @unlink($csv_file);
            return array(
                'success' => false,
                'message' => 'Failed to clear existing GeoIP data',
                'imported_count' => 0
            );
        }

        // Import CSV with batch processing (prevent memory issues)
        $handle = fopen($csv_file, 'r');
        if (!$handle) {
            @unlink($csv_file);
            return array(
                'success' => false,
                'message' => 'Failed to open CSV file',
                'imported_count' => 0
            );
        }

        $batch_size = 1000;
        $batch = array();
        $imported_count = 0;
        $line_number = 0;

        while (($data = fgetcsv($handle)) !== false) {
            $line_number++;

            // Skip malformed lines
            if (count($data) < 4) {
                continue;
            }

            // Parse CSV row: "ip_from","ip_to","country_code","country_name"
            $ip_from = trim($data[0], '"');
            $ip_to = trim($data[1], '"');
            $country_code = trim($data[2], '"');
            $country_name = trim($data[3], '"');

            // Validate data
            if (!is_numeric($ip_from) || !is_numeric($ip_to) || strlen($country_code) != 2) {
                continue;
            }

            // Add to batch
            $batch[] = $wpdb->prepare(
                "(%d, %d, %s, %s)",
                $ip_from,
                $ip_to,
                strtoupper($country_code),
                $country_name
            );

            // Insert batch when full
            if (count($batch) >= $batch_size) {
                $values = implode(',', $batch);
                $wpdb->query(
                    "INSERT INTO " . esc_sql($table_name) . " (ip_from, ip_to, country_code, country_name) 
                     VALUES {$values}"
                );
                $imported_count += count($batch);
                $batch = array();

                // Prevent timeout on large imports
                if ($line_number % 5000 == 0) {
                    set_time_limit(300); // Extend time limit
                }
            }
        }

        // Insert remaining batch
        if (!empty($batch)) {
            $values = implode(',', $batch);
            $wpdb->query(
                "INSERT INTO " . esc_sql($table_name) . " (ip_from, ip_to, country_code, country_name) 
                 VALUES {$values}"
            );
            $imported_count += count($batch);
        }

        fclose($handle);
        
        // Clean up extracted files and directory
        @vmpfence_remove_directory($extract_dir);

        // Optimize table after import
        $wpdb->query("OPTIMIZE TABLE " . esc_sql($table_name));

        return array(
            'success' => true,
            'message' => sprintf('Successfully imported %d IP ranges', $imported_count),
            'imported_count' => $imported_count
        );
    }

    /**
     * Get database statistics
     *
     * @return array Statistics including row count, table size, last update
     */
    public static function get_stats() {
        global $wpdb;

        $table_name = $wpdb->prefix . self::$table_name;

        // Check if table exists
        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") != $table_name) {
            return array(
                'exists' => false,
                'row_count' => 0,
                'table_size' => 0,
                'last_update' => null,
                'country_count' => 0
            );
        }

        // Get row count (escape table name)
        $row_count = $wpdb->get_var("SELECT COUNT(*) FROM " . esc_sql($table_name));

        // Get table size
        $table_status = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT data_length + index_length as size, update_time 
                 FROM information_schema.TABLES 
                 WHERE table_schema = %s AND table_name = %s",
                DB_NAME,
                $table_name
            )
        );

        $table_size = $table_status ? $table_status->size : 0;
        $last_update = $table_status ? $table_status->update_time : null;

        // Get unique country count (escape table name)
        $country_count = $wpdb->get_var("SELECT COUNT(DISTINCT country_code) FROM " . esc_sql($table_name));

        return array(
            'exists' => true,
            'row_count' => (int) $row_count,
            'table_size' => (int) $table_size,
            'table_size_mb' => round($table_size / 1048576, 2), // Convert to MB
            'last_update' => $last_update,
            'country_count' => (int) $country_count,
            'is_empty' => ($row_count == 0)
        );
    }

    /**
     * Schedule automatic database updates
     *
     * Sets up a monthly cron job to update the GeoIP database
     *
     * @return void
     */
    public static function schedule_updates() {
        if (!wp_next_scheduled('vmpfence_geoip_update')) {
            wp_schedule_event(time(), 'monthly', 'vmpfence_geoip_update');
        }
    }

    /**
     * Clear all GeoIP caches
     *
     * Removes all transient caches for IP lookups
     *
     * @return int Number of caches cleared
     */
    public static function clear_cache() {
        global $wpdb;

        // Clear all GeoIP transients (use prepared statement for LIKE patterns)
        $deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$wpdb->options} 
                 WHERE option_name LIKE %s 
                 OR option_name LIKE %s",
                $wpdb->esc_like('_transient_vmpfence_geoip_') . '%',
                $wpdb->esc_like('_transient_timeout_vmpfence_geoip_') . '%'
            )
        );

        return $deleted;
    }
}
