<?php
/**
 * Database Backup
 * 
 * Secure database backup functionality with prepared statements
 * Addresses SQL injection vulnerabilities found in XCloner
 * 
 * @package TigerStyleLife9
 * @since 1.0.0
 */

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Database backup class
 * 
 * @since 1.0.0
 */
class TigerStyle_Life9_Database_Backup {
    
    /**
     * Security instance
     * 
     * @var TigerStyle_Life9_Security
     */
    private $security;
    
    /**
     * Database connection
     * 
     * @var wpdb
     */
    private $wpdb;
    
    /**
     * Backup progress callback
     * 
     * @var callable
     */
    private $progress_callback;
    
    /**
     * Constructor
     */
    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->security = tigerstyle_life9()->get_security();
    }
    
    /**
     * Export database to SQL file
     * 
     * @param string $output_file Output file path
     * @param array $config Export configuration
     * @return bool Success status
     */
    public function export_database($output_file, $config = []) {
        $defaults = [
            'include_tables' => [],
            'exclude_tables' => [],
            'add_drop_table' => true,
            'add_if_not_exists' => false,
            'disable_keys' => true,
            'single_transaction' => true,
            'lock_tables' => false,
            'where_conditions' => [],
            'max_query_size' => 1048576, // 1MB
            'compress_output' => false
        ];
        
        $config = array_merge($defaults, $config);
        
        // Validate output file path
        if (!$this->security->validate_path(dirname($output_file))) {
            throw new Exception('Invalid output file path');
        }
        
        try {
            $tables = $this->get_tables_to_backup($config);
            
            if (empty($tables)) {
                throw new Exception('No tables found to backup');
            }
            
            $this->log_info('Starting database backup', [
                'tables_count' => count($tables),
                'output_file' => $output_file
            ]);
            
            // Open output file
            $handle = fopen($output_file, 'w');
            if (!$handle) {
                throw new Exception('Cannot open output file for writing');
            }
            
            // Write SQL header
            $this->write_sql_header($handle, $config);
            
            // Begin transaction if configured
            if ($config['single_transaction']) {
                fwrite($handle, "START TRANSACTION;\n");
                fwrite($handle, "SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n");
                fwrite($handle, "SET AUTOCOMMIT = 0;\n\n");
            }
            
            // Disable foreign key checks temporarily
            fwrite($handle, "SET FOREIGN_KEY_CHECKS = 0;\n\n");
            
            $total_tables = count($tables);
            $processed_tables = 0;
            
            // Export each table
            foreach ($tables as $table) {
                $this->export_table($handle, $table, $config);
                
                $processed_tables++;
                if ($this->progress_callback) {
                    call_user_func($this->progress_callback, 'database', 
                        round(($processed_tables / $total_tables) * 100), $table);
                }
            }
            
            // Re-enable foreign key checks
            fwrite($handle, "\nSET FOREIGN_KEY_CHECKS = 1;\n");
            
            // Commit transaction if configured
            if ($config['single_transaction']) {
                fwrite($handle, "COMMIT;\n");
            }
            
            // Write SQL footer
            $this->write_sql_footer($handle);
            
            fclose($handle);
            
            // Compress if requested
            if ($config['compress_output']) {
                $this->compress_sql_file($output_file);
            }
            
            $this->log_info('Database backup completed successfully', [
                'file_size' => filesize($output_file),
                'tables_exported' => count($tables)
            ]);
            
            return true;
            
        } catch (Exception $e) {
            if (isset($handle) && $handle) {
                fclose($handle);
            }
            
            $this->log_error('Database backup failed', ['error' => $e->getMessage()]);
            
            // Clean up partial file
            if (file_exists($output_file)) {
                unlink($output_file);
            }
            
            throw $e;
        }
    }
    
    /**
     * Get list of tables to backup
     * 
     * @param array $config Backup configuration
     * @return array Array of table names
     */
    private function get_tables_to_backup($config) {
        $all_tables = $this->get_all_tables();
        
        // If specific tables are included, use only those
        if (!empty($config['include_tables'])) {
            $tables = array_intersect($all_tables, $config['include_tables']);
        } else {
            $tables = $all_tables;
        }
        
        // Remove excluded tables
        if (!empty($config['exclude_tables'])) {
            $tables = array_diff($tables, $config['exclude_tables']);
        }
        
        // Validate table names for security
        $sanitizer = new TigerStyle_Life9_Sanitizer();
        $valid_tables = [];
        
        foreach ($tables as $table) {
            $clean_table = $sanitizer->sanitize_table_name($table);
            if ($clean_table && $this->table_exists($clean_table)) {
                $valid_tables[] = $clean_table;
            }
        }
        
        return $valid_tables;
    }
    
    /**
     * Get all tables in database
     * 
     * @return array Array of table names
     */
    private function get_all_tables() {
        $tables = [];
        
        $results = $this->wpdb->get_results("SHOW TABLES", ARRAY_N);
        
        foreach ($results as $row) {
            if (isset($row[0])) {
                $tables[] = $row[0];
            }
        }
        
        return $tables;
    }
    
    /**
     * Check if table exists
     * 
     * @param string $table_name Table name
     * @return bool True if table exists
     */
    private function table_exists($table_name) {
        $table = $this->wpdb->get_var($this->wpdb->prepare(
            "SHOW TABLES LIKE %s",
            $table_name
        ));
        
        return $table === $table_name;
    }
    
    /**
     * Export single table
     * 
     * @param resource $handle File handle
     * @param string $table Table name
     * @param array $config Export configuration
     */
    private function export_table($handle, $table, $config) {
        $this->log_info('Exporting table: ' . $table);
        
        // Get table structure
        $create_table = $this->get_table_structure($table, $config);
        
        if ($config['add_drop_table']) {
            fwrite($handle, "DROP TABLE IF EXISTS `{$table}`;\n");
        }
        
        fwrite($handle, $create_table . "\n\n");
        
        // Get table data
        $this->export_table_data($handle, $table, $config);
        
        fwrite($handle, "\n");
    }
    
    /**
     * Get table structure (CREATE TABLE statement)
     * 
     * @param string $table Table name
     * @param array $config Export configuration
     * @return string CREATE TABLE statement
     */
    private function get_table_structure($table, $config) {
        $result = $this->wpdb->get_row($this->wpdb->prepare(
            "SHOW CREATE TABLE `%s`",
            $table
        ), ARRAY_A);
        
        if (!$result || !isset($result['Create Table'])) {
            throw new Exception("Failed to get structure for table: {$table}");
        }
        
        $create_table = $result['Create Table'];
        
        // Modify CREATE statement if needed
        if ($config['add_if_not_exists']) {
            $create_table = str_replace(
                'CREATE TABLE `' . $table . '`',
                'CREATE TABLE IF NOT EXISTS `' . $table . '`',
                $create_table
            );
        }
        
        return $create_table . ';';
    }
    
    /**
     * Export table data
     * 
     * @param resource $handle File handle
     * @param string $table Table name
     * @param array $config Export configuration
     */
    private function export_table_data($handle, $table, $config) {
        // Get column information
        $columns = $this->get_table_columns($table);
        
        if (empty($columns)) {
            return; // No columns, skip data export
        }
        
        // Build column list
        $column_list = '`' . implode('`, `', array_keys($columns)) . '`';
        
        // Add DISABLE KEYS for MyISAM tables if configured
        if ($config['disable_keys']) {
            fwrite($handle, "ALTER TABLE `{$table}` DISABLE KEYS;\n");
        }
        
        // Prepare base query
        $base_query = "SELECT {$column_list} FROM `{$table}`";
        
        // Add WHERE conditions if specified
        $where_clause = '';
        if (isset($config['where_conditions'][$table])) {
            $where_condition = $config['where_conditions'][$table];
            // Validate WHERE condition for security
            if ($this->validate_where_condition($where_condition)) {
                $where_clause = " WHERE {$where_condition}";
            }
        }
        
        $query = $base_query . $where_clause;
        
        // Get total row count for progress tracking
        $count_query = "SELECT COUNT(*) FROM `{$table}`" . $where_clause;
        $total_rows = $this->wpdb->get_var($count_query);
        
        if ($total_rows == 0) {
            return; // No data to export
        }
        
        // Export data in chunks to manage memory
        $chunk_size = 1000;
        $offset = 0;
        $current_insert = '';
        $current_size = 0;
        
        while ($offset < $total_rows) {
            $chunk_query = $query . " LIMIT {$chunk_size} OFFSET {$offset}";
            $rows = $this->wpdb->get_results($chunk_query, ARRAY_A);
            
            if (empty($rows)) {
                break;
            }
            
            foreach ($rows as $row) {
                $values = $this->prepare_row_values($row, $columns);
                $insert_line = "({$values})";
                
                // Start new INSERT statement if needed
                if (empty($current_insert)) {
                    $current_insert = "INSERT INTO `{$table}` ({$column_list}) VALUES\n";
                    $current_size = strlen($current_insert);
                }
                
                // Check if adding this row would exceed max query size
                if ($current_size + strlen($insert_line) > $config['max_query_size']) {
                    // Write current INSERT and start new one
                    fwrite($handle, rtrim($current_insert, ",\n") . ";\n");
                    $current_insert = "INSERT INTO `{$table}` ({$column_list}) VALUES\n";
                    $current_size = strlen($current_insert);
                }
                
                $current_insert .= $insert_line . ",\n";
                $current_size += strlen($insert_line) + 2;
            }
            
            $offset += $chunk_size;
        }
        
        // Write final INSERT statement
        if (!empty($current_insert)) {
            fwrite($handle, rtrim($current_insert, ",\n") . ";\n");
        }
        
        // Re-enable keys if configured
        if ($config['disable_keys']) {
            fwrite($handle, "ALTER TABLE `{$table}` ENABLE KEYS;\n");
        }
    }
    
    /**
     * Get table columns information
     * 
     * @param string $table Table name
     * @return array Column information
     */
    private function get_table_columns($table) {
        $columns = [];
        
        $results = $this->wpdb->get_results($this->wpdb->prepare(
            "SHOW COLUMNS FROM `%s`",
            $table
        ), ARRAY_A);
        
        foreach ($results as $column) {
            $columns[$column['Field']] = [
                'type' => $column['Type'],
                'null' => $column['Null'] === 'YES',
                'key' => $column['Key'],
                'default' => $column['Default'],
                'extra' => $column['Extra']
            ];
        }
        
        return $columns;
    }
    
    /**
     * Prepare row values for INSERT statement
     * 
     * @param array $row Row data
     * @param array $columns Column information
     * @return string Formatted values string
     */
    private function prepare_row_values($row, $columns) {
        $values = [];
        
        foreach ($row as $column => $value) {
            if ($value === null) {
                $values[] = 'NULL';
            } else {
                // Escape value based on column type
                $column_info = $columns[$column] ?? [];
                $escaped_value = $this->escape_value($value, $column_info);
                $values[] = $escaped_value;
            }
        }
        
        return implode(', ', $values);
    }
    
    /**
     * Escape value for SQL
     * 
     * @param mixed $value Value to escape
     * @param array $column_info Column information
     * @return string Escaped value
     */
    private function escape_value($value, $column_info) {
        // Use WordPress's built-in escaping
        if (is_numeric($value) && !empty($column_info['type'])) {
            $type = strtolower($column_info['type']);
            
            // For numeric types, don't quote if it's actually numeric
            if (strpos($type, 'int') !== false || 
                strpos($type, 'decimal') !== false || 
                strpos($type, 'float') !== false || 
                strpos($type, 'double') !== false) {
                return $value;
            }
        }
        
        // For all other types, escape and quote
        return "'" . $this->wpdb->_escape($value) . "'";
    }
    
    /**
     * Validate WHERE condition for security
     * 
     * @param string $condition WHERE condition
     * @return bool True if condition is safe
     */
    private function validate_where_condition($condition) {
        // Basic validation to prevent SQL injection
        $dangerous_keywords = [
            'DROP', 'DELETE', 'UPDATE', 'INSERT', 'ALTER', 'CREATE',
            'EXEC', 'EXECUTE', 'UNION', 'SCRIPT', '--', '/*', '*/'
        ];
        
        $upper_condition = strtoupper($condition);
        
        foreach ($dangerous_keywords as $keyword) {
            if (strpos($upper_condition, $keyword) !== false) {
                return false;
            }
        }
        
        return true;
    }
    
    /**
     * Write SQL file header
     * 
     * @param resource $handle File handle
     * @param array $config Export configuration
     */
    private function write_sql_header($handle, $config) {
        $header = "-- TigerStyle Life9 Database Backup\n";
        $header .= "-- Generated on: " . date('Y-m-d H:i:s') . "\n";
        $header .= "-- WordPress Version: " . get_bloginfo('version') . "\n";
        $header .= "-- Database: " . DB_NAME . "\n";
        $header .= "-- Host: " . DB_HOST . "\n";
        $header .= "-- PHP Version: " . PHP_VERSION . "\n";
        $header .= "-- Plugin Version: " . TIGERSTYLE_LIFE9_VERSION . "\n";
        $header .= "--\n";
        $header .= "-- WARNING: This file contains sensitive data.\n";
        $header .= "-- Do not share or store in public locations.\n";
        $header .= "--\n\n";
        
        $header .= "SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n";
        $header .= "SET time_zone = '+00:00';\n\n";
        
        fwrite($handle, $header);
    }
    
    /**
     * Write SQL file footer
     * 
     * @param resource $handle File handle
     */
    private function write_sql_footer($handle) {
        $footer = "\n-- Backup completed successfully\n";
        $footer .= "-- End of TigerStyle Life9 Database Backup\n";
        
        fwrite($handle, $footer);
    }
    
    /**
     * Compress SQL file using gzip
     * 
     * @param string $file_path SQL file path
     * @return bool Success status
     */
    private function compress_sql_file($file_path) {
        if (!function_exists('gzencode')) {
            return false;
        }
        
        $data = file_get_contents($file_path);
        if ($data === false) {
            return false;
        }
        
        $compressed = gzencode($data, 9);
        if ($compressed === false) {
            return false;
        }
        
        $compressed_file = $file_path . '.gz';
        $result = file_put_contents($compressed_file, $compressed) !== false;
        
        if ($result) {
            // Remove original file and rename compressed file
            unlink($file_path);
            rename($compressed_file, $file_path);
        }
        
        return $result;
    }
    
    /**
     * Set progress callback
     * 
     * @param callable $callback Progress callback function
     */
    public function set_progress_callback($callback) {
        $this->progress_callback = $callback;
    }
    
    /**
     * Get database size
     * 
     * @return array Database size information
     */
    public function get_database_size() {
        $query = "SELECT 
            table_schema as 'database_name',
            SUM(data_length + index_length) as 'size_bytes',
            COUNT(*) as 'table_count'
        FROM information_schema.tables 
        WHERE table_schema = %s
        GROUP BY table_schema";
        
        $result = $this->wpdb->get_row($this->wpdb->prepare($query, DB_NAME), ARRAY_A);
        
        if (!$result) {
            return [
                'database_name' => DB_NAME,
                'size_bytes' => 0,
                'table_count' => 0,
                'formatted_size' => '0 B'
            ];
        }
        
        $result['formatted_size'] = $this->format_bytes($result['size_bytes']);
        
        return $result;
    }
    
    /**
     * Get table sizes
     * 
     * @return array Array of table size information
     */
    public function get_table_sizes() {
        $query = "SELECT 
            table_name,
            table_rows,
            data_length,
            index_length,
            (data_length + index_length) as total_size
        FROM information_schema.tables 
        WHERE table_schema = %s
        ORDER BY total_size DESC";
        
        $results = $this->wpdb->get_results($this->wpdb->prepare($query, DB_NAME), ARRAY_A);
        
        foreach ($results as &$table) {
            $table['formatted_size'] = $this->format_bytes($table['total_size']);
        }
        
        return $results;
    }
    
    /**
     * Test database connection
     * 
     * @return bool True if connection is working
     */
    public function test_connection() {
        try {
            $result = $this->wpdb->get_var("SELECT 1");
            return $result === '1';
        } catch (Exception $e) {
            return false;
        }
    }
    
    /**
     * Format bytes to human readable format
     * 
     * @param int $bytes Number of bytes
     * @return string Formatted size
     */
    private function format_bytes($bytes) {
        $units = ['B', 'KB', 'MB', 'GB', 'TB'];
        
        for ($i = 0; $bytes > 1024; $i++) {
            $bytes /= 1024;
        }
        
        return round($bytes, 2) . ' ' . $units[$i];
    }
    
    /**
     * Log info message
     * 
     * @param string $message Log message
     * @param array $context Additional context
     */
    private function log_info($message, $context = []) {
        error_log("TigerStyle Life9 DB Backup [INFO]: {$message}");
    }
    
    /**
     * Log error message
     * 
     * @param string $message Log message
     * @param array $context Additional context
     */
    private function log_error($message, $context = []) {
        error_log("TigerStyle Life9 DB Backup [ERROR]: {$message}");
    }
}