<?php
/**
 * CargoHub Free Database Class
 * 
 * Handles database operations for the FREE version
 */

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

class CargoHub_Free_Database {
    
    /**
     * Table name for tracking data
     */
    private $table_name;
    
    /**
     * Cache group name
     */
    private $cache_group = 'cargohub_free_tracking';
    
    /**
     * Cache expiration time (in seconds)
     */
    private $cache_expiration = 0; // will be set from constant in constructor
    
    /**
     * Constructor
     */
    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'cargohub_free_tracking';
        // Set cache expiration from constant or fallback
        $this->cache_expiration = defined('CARGOHUB_FREE_CACHE_TIMEOUT') ? CARGOHUB_FREE_CACHE_TIMEOUT : 600;
        
        // Check if table exists and create if needed
        $this->ensure_table_exists();
    }
    
    /**
     * Ensure table exists, create if it doesn't
     */
    public function ensure_table_exists() {
        global $wpdb;
        
        // Fast check using option to avoid running SHOW TABLES on every single request
        if (get_option('cargohub_free_table_installed') === CARGOHUB_FREE_VERSION) {
            return;
        }
        
        // Check if table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name)) === $this->table_name;
        
        if (!$table_exists) {
            if (class_exists('CargoHub_Free_Logger')) {
                CargoHub_Free_Logger::warning('CargoHub tracking table does not exist, creating now...', array(
                    'table_name' => $this->table_name
                ));
            }
            $this->create_tables();
        } else {
            update_option('cargohub_free_table_installed', CARGOHUB_FREE_VERSION);
        }
    }
    
    /**
     * Create database tables
     */
    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE {$this->table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            carrier varchar(50) NOT NULL,
            tracking_number varchar(100) NOT NULL,
            status varchar(20) DEFAULT 'active',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY order_id (order_id),
            KEY carrier (carrier),
            KEY tracking_number (tracking_number),
            KEY status (status),
            KEY created_at (created_at)
        ) $charset_collate;";
        
        if (!function_exists('dbDelta')) {
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        }
        
        $result = dbDelta($sql);
        
        // Add composite indexes (MySQL 5.6 compatible): check existence first
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SHOW INDEX requires direct query; table name is validated and prefixed
        $existing_indexes = $wpdb->get_results("SHOW INDEX FROM {$this->table_name}");
        $existing_index_names = array();
        if (is_array($existing_indexes)) {
            foreach ($existing_indexes as $idx) {
                if (isset($idx->Key_name)) {
                    $existing_index_names[$idx->Key_name] = true;
                }
            }
        }
        if (empty($existing_index_names['idx_order_status'])) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange -- index creation at install time
            $wpdb->query("CREATE INDEX idx_order_status ON {$this->table_name} (order_id, status)");
        }
        if (empty($existing_index_names['idx_created_status'])) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange -- index creation at install time
            $wpdb->query("CREATE INDEX idx_created_status ON {$this->table_name} (created_at, status)");
        }
        if (empty($existing_index_names['idx_tracking_carrier'])) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange -- index creation at install time
            $wpdb->query("CREATE INDEX idx_tracking_carrier ON {$this->table_name} (tracking_number, carrier)");
        }
        
        // Check if table was created successfully
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name)) != $this->table_name) {
            $error_message = 'Failed to create database table - ' . $wpdb->last_error;
            
            if (class_exists('CargoHub_Free_Logger')) {
                CargoHub_Free_Logger::critical($error_message, array(
                    'table_name' => $this->table_name,
                    'last_error' => $wpdb->last_error
                ));
            }
        }
    }
    
    /**
     * Insert tracking data
     * 
     * @param int $order_id
     * @param string $carrier
     * @param string $tracking_number
     * @return int|false Insert ID on success, false on failure
     */
    public function insert_tracking($order_id, $carrier, $tracking_number) {
        global $wpdb;
        
        // Validate input
        $order_id = absint($order_id);
        $carrier = sanitize_text_field($carrier);
        $tracking_number = sanitize_text_field($tracking_number);
        
        if (empty($order_id) || empty($carrier) || empty($tracking_number)) {
            return false;
        }
        
        // Check if tracking already exists for this order
        $existing = $this->get_tracking_by_order_id($order_id);
        if ($existing) {
            // Update existing record
            return $this->update_tracking($existing->id, $carrier, $tracking_number);
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->insert(
            $this->table_name,
            array(
                'order_id' => $order_id,
                'carrier' => $carrier,
                'tracking_number' => $tracking_number,
                'status' => 'active',
                'created_at' => current_time('mysql'),
                'updated_at' => current_time('mysql')
            ),
            array(
                '%d',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s'
            )
        );
        
        // Clear cache for this order
        if ($result !== false) {
            $this->clear_cache_for_order($order_id);
        }
        
        return $result !== false ? $wpdb->insert_id : false;
    }
    
    /**
     * Update tracking data
     * 
     * @param int $id
     * @param string $carrier
     * @param string $tracking_number
     * @return bool
     */
    public function update_tracking($id, $carrier, $tracking_number) {
        global $wpdb;
        
        $id = absint($id);
        $carrier = sanitize_text_field($carrier);
        $tracking_number = sanitize_text_field($tracking_number);
        
        if (empty($id) || empty($carrier) || empty($tracking_number)) {
            return false;
        }
        
        // Get order_id before update for cache clearing
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is validated and prefixed
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $tracking_record = $wpdb->get_row($wpdb->prepare(
            "SELECT order_id FROM {$this->table_name} WHERE id = %d",
            $id
        ));
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            $this->table_name,
            array(
                'carrier' => $carrier,
                'tracking_number' => $tracking_number,
                'updated_at' => current_time('mysql')
            ),
            array('id' => $id),
            array('%s', '%s', '%s'),
            array('%d')
        );
        
        // Clear cache for this order
        if ($result !== false && $tracking_record) {
            $this->clear_cache_for_order($tracking_record->order_id);
        }
        
        return $result !== false;
    }
    
    /**
     * Get tracking data by order ID
     * 
     * @param int $order_id
     * @return object|null
     */
    public function get_tracking_by_order_id($order_id) {
        $cache_key = "tracking_order_{$order_id}";
        $cached_result = wp_cache_get($cache_key, $this->cache_group);
        
        if ($cached_result !== false) {
            return $cached_result;
        }
        
        global $wpdb;
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is validated and prefixed
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $result = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$this->table_name} WHERE order_id = %d AND status = 'active' ORDER BY created_at DESC LIMIT 1",
            absint($order_id)
        ));
        
        // Cache the result
        wp_cache_set($cache_key, $result, $this->cache_group, $this->cache_expiration);
        
        return $result;
    }
    
    /**
     * Delete tracking data
     * 
     * @param int $order_id
     * @return bool
     */
    public function delete_tracking($order_id) {
        global $wpdb;
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            $this->table_name,
            array('status' => 'deleted'),
            array('order_id' => absint($order_id)),
            array('%s'),
            array('%d')
        );
        
        // Clear cache
        $this->clear_cache_for_order($order_id);
        
        return $result !== false;
    }
    
    /**
     * Clear cache for specific order
     * 
     * @param int $order_id
     */
    private function clear_cache_for_order($order_id) {
        $cache_key = "tracking_order_{$order_id}";
        wp_cache_delete($cache_key, $this->cache_group);
    }
    
    /**
     * Get tracking statistics (for admin dashboard)
     * 
     * @return array
     */
    public function get_tracking_stats() {
        global $wpdb;
        
        $cache_key = 'tracking_stats';
        $cached_result = wp_cache_get($cache_key, $this->cache_group);
        
        if ($cached_result !== false) {
            return $cached_result;
        }
        
        $stats = array();
        
        // Total active trackings
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is validated and prefixed
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $stats['total'] = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->table_name} WHERE status = %s",
            'active'
        ));
        
        // This month's trackings
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is validated and prefixed
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $stats['this_month'] = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->table_name} WHERE status = %s AND created_at >= %s",
            'active',
            gmdate('Y-m-01 00:00:00')
        ));
        
        // Cache for 1 hour
        wp_cache_set($cache_key, $stats, $this->cache_group, 3600);
        
        return $stats;
    }
    
    /**
     * Get tracking data for an order (wrapper for admin compatibility)
     *
     * @param int $order_id
     * @return array|null
     */
    public function get_tracking_data($order_id) {
        $tracking_record = $this->get_tracking_by_order_id($order_id);
        if ($tracking_record) {
            return array(
                'carrier' => $tracking_record->carrier,
                'tracking_number' => $tracking_record->tracking_number,
                'shipped_date' => $tracking_record->created_at,
            );
        }
        return array(); // Return empty array instead of null to prevent errors
    }

    /**
     * Save tracking data (wrapper for admin compatibility)
     *
     * @param array $tracking_data
     * @return bool
     */
    public function save_tracking_data($tracking_data) {
        try {
            // Validate required fields
            if (!isset($tracking_data['order_id']) || !isset($tracking_data['carrier'])) {
                if (class_exists('CargoHub_Free_Logger')) {
                    CargoHub_Free_Logger::warning('Missing required fields in save_tracking_data', $tracking_data);
                }
                return false;
            }
            
            // Allow empty tracking number (will create pending record)
            $tracking_number = isset($tracking_data['tracking_number']) ? $tracking_data['tracking_number'] : '';
            
            if (empty($tracking_number)) {
                // If no tracking number, just return true (don't save empty tracking)
                return true;
            }
            
            $result = $this->insert_tracking(
                $tracking_data['order_id'], 
                $tracking_data['carrier'], 
                $tracking_number
            );
            
            if ($result === false && class_exists('CargoHub_Free_Logger')) {
                CargoHub_Free_Logger::error('Failed to save tracking data', array(
                    'order_id' => $tracking_data['order_id'],
                    'carrier' => $tracking_data['carrier'],
                    'tracking_number' => $tracking_number
                ));
            }
            
            return $result !== false;
            
        } catch (Exception $e) {
            if (class_exists('CargoHub_Free_Logger')) {
                CargoHub_Free_Logger::critical('Exception in save_tracking_data: ' . $e->getMessage(), array(
                    'tracking_data' => $tracking_data,
                    'trace' => $e->getTraceAsString()
                ));
            }
            // Swallow DB exception logging for production
            return false;
        }
    }

    /**
     * Delete tracking data for an order (wrapper for admin compatibility)
     *
     * @param int $order_id
     * @return bool
     */
    public function delete_tracking_data($order_id) {
        return $this->delete_tracking($order_id);
    }
}
