<?php
/**
 * Total Processing Gateway Logger - Database Table Management
 * 
 * Handles creation and management of the gateway logs database table
 *
 * @package TotalProcessing
 * @subpackage Gateway_Logger
 */

if (!defined('ABSPATH')) {
    exit;
}

class TP_Gateway_Logger_Table {
    
    /**
     * Table name (without prefix)
     */
    const TABLE_NAME = 'totalprocessing_gateway_logs';
    
    /**
     * Current database version
     */
    const DB_VERSION = '1.0.0';
    
    /**
     * Database version option name
     */
    const VERSION_OPTION = 'tp_gateway_logs_db_version';
    
    /**
     * Create or update the logs table
     */
    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,
            gateway_id VARCHAR(50) NOT NULL,
            log_level VARCHAR(20) NOT NULL,
            message TEXT NOT NULL,
            log_data LONGTEXT DEFAULT NULL,
            order_id BIGINT(20) UNSIGNED DEFAULT NULL,
            transaction_id VARCHAR(100) DEFAULT NULL,
            checkout_id VARCHAR(100) DEFAULT NULL,
            user_id BIGINT(20) UNSIGNED DEFAULT NULL,
            session_id VARCHAR(100) DEFAULT NULL,
            ip_address VARCHAR(45) DEFAULT NULL,
            cart_hash VARCHAR(32) DEFAULT NULL,
            request_url VARCHAR(255) DEFAULT NULL,
            stack_trace TEXT DEFAULT NULL,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            INDEX idx_gateway_date (gateway_id, created_at),
            INDEX idx_order_date (order_id, created_at),
            INDEX idx_level (log_level),
            INDEX idx_cleanup (gateway_id, created_at)
        ) {$charset_collate};";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Update version
        update_option(self::VERSION_OPTION, self::DB_VERSION);
        
        return true;
    }
    
    /**
     * Check if table exists
     */
    public static function table_exists() {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        return $wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name;
    }
    
    /**
     * Get table name with prefix
     */
    public static function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_NAME;
    }
    
    /**
     * Drop the table (for uninstall)
     */
    public static function drop_table() {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        $wpdb->query("DROP TABLE IF EXISTS {$table_name}");
        delete_option(self::VERSION_OPTION);
    }
    
    /**
     * Get table statistics
     */
    public static function get_table_stats() {
        global $wpdb;
        $table_name = $wpdb->prefix . self::TABLE_NAME;
        
        if (!self::table_exists()) {
            return [
                'total_logs' => 0,
                'size_mb' => 0,
                'oldest_log' => null,
                'newest_log' => null
            ];
        }
        
        $stats = [
            'total_logs' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}"),
            'oldest_log' => $wpdb->get_var("SELECT created_at FROM {$table_name} ORDER BY created_at ASC LIMIT 1"),
            'newest_log' => $wpdb->get_var("SELECT created_at FROM {$table_name} ORDER BY created_at DESC LIMIT 1")
        ];
        
        // Get table size in MB
        $size_query = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
            FROM information_schema.TABLES 
            WHERE table_schema = %s 
            AND table_name = %s",
            DB_NAME,
            $table_name
        ));
        
        $stats['size_mb'] = $size_query ? (float) $size_query->size_mb : 0;
        
        return $stats;
    }
    
    /**
     * Get statistics by gateway
     */
    public static function get_gateway_stats($gateway_id) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        if (!self::table_exists()) {
            return [
                'total' => 0,
                'errors' => 0,
                'warnings' => 0,
                'info' => 0,
                'success' => 0
            ];
        }
        
        $stats = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN log_level = 'error' THEN 1 ELSE 0 END) as errors,
                SUM(CASE WHEN log_level = 'warning' THEN 1 ELSE 0 END) as warnings,
                SUM(CASE WHEN log_level = 'info' THEN 1 ELSE 0 END) as info,
                SUM(CASE WHEN log_level = 'success' THEN 1 ELSE 0 END) as success
            FROM {$table_name}
            WHERE gateway_id = %s",
            $gateway_id
        ), ARRAY_A);
        
        return $stats ?: [
            'total' => 0,
            'errors' => 0,
            'warnings' => 0,
            'info' => 0,
            'success' => 0
        ];
    }
}
