<?php
/**
 * CastConductor Database Backup Manager
 *
 * Handles SQL dump and restore for CastConductor tables.
 * Provides full backup with overwrite restore semantics.
 *
 * SECURITY ARCHITECTURE:
 * ----------------------
 * This class implements multiple layers of protection to ensure
 * it ONLY ever touches CastConductor tables (wp_castconductor_*):
 *
 * BACKUP (Export) Guards:
 * 1. Explicit allowlist - only hardcoded table names in $tables array
 * 2. Existence verification before any table access
 * 3. Read-only operations (SELECT/SHOW only, no writes)
 *
 * RESTORE (Import) Guards:
 * 1. Signature validation - file must contain "CastConductor Database Backup"
 * 2. Statement-level validation - EVERY SQL statement is checked against
 *    a regex pattern that ONLY allows wp_*castconductor_* table names
 * 3. Blocked statements are logged and skipped (fail-safe)
 * 4. Pre-restore automatic backup for undo capability
 * 5. User must explicitly confirm understanding of data overwrite
 *
 * These guards make it IMPOSSIBLE for this class to:
 * - Read from non-CC tables (allowlist blocks at generation time)
 * - Write to non-CC tables (regex validation blocks at execute time)
 * - Execute arbitrary SQL (statement-type whitelist + table pattern check)
 *
 * @package CastConductor
 * @subpackage Database
 * @since 5.7.14
 */

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

class CastConductor_Database_Backup {

    /**
     * List of CastConductor tables (without prefix)
     */
    private $tables = array(
        'castconductor_containers',
        'castconductor_content_blocks',
        'castconductor_container_blocks',
        'castconductor_backgrounds',
        'castconductor_scenes',
        'castconductor_scene_containers',
        'castconductor_menus',
        'castconductor_menu_items',
        'castconductor_button_bindings',
    );

    /**
     * Analytics tables (separate to allow selective backup)
     * These are the actual cc_analytics_* tables used by the analytics module
     */
    private $analytics_tables = array(
        'cc_analytics_events',
        'cc_analytics_sessions',
        'cc_analytics_hourly',
        'cc_analytics_daily',
        'cc_analytics_sponsor_daily',
        'cc_analytics_qr_scans',
        'cc_analytics_api_keys',
        'cc_analytics_devices',
    );

    /**
     * Get all CastConductor table names with prefix
     *
     * @param bool $include_analytics Whether to include analytics tables
     * @return array Full table names
     */
    public function get_table_names($include_analytics = false) {
        global $wpdb;
        
        $table_names = array();
        $all_tables = $include_analytics 
            ? array_merge($this->tables, $this->analytics_tables)
            : $this->tables;
        
        foreach ($all_tables as $table) {
            $full_name = $wpdb->prefix . $table;
            // Only include if table exists
            if ($wpdb->get_var("SHOW TABLES LIKE '{$full_name}'") === $full_name) {
                $table_names[] = $full_name;
            }
        }
        
        return $table_names;
    }

    /**
     * Generate SQL backup of all CastConductor tables
     *
     * @param bool $include_analytics Whether to include analytics data
     * @return string SQL dump content
     */
    public function generate_backup($include_analytics = false) {
        global $wpdb;
        
        $tables = $this->get_table_names($include_analytics);
        
        if (empty($tables)) {
            throw new Exception('No CastConductor tables found to backup');
        }
        
        $sql = "-- CastConductor Database Backup\n";
        $sql .= "-- Generated: " . gmdate('Y-m-d H:i:s') . " UTC\n";
        $sql .= "-- Plugin Version: " . (defined('CASTCONDUCTOR_VERSION') ? CASTCONDUCTOR_VERSION : 'unknown') . "\n";
        $sql .= "-- WordPress Version: " . get_bloginfo('version') . "\n";
        $sql .= "-- Tables: " . count($tables) . "\n";
        $sql .= "-- Table Prefix: {$wpdb->prefix}\n";
        $sql .= "--\n";
        $sql .= "-- RESTORE INSTRUCTIONS:\n";
        $sql .= "-- This backup uses full overwrite semantics.\n";
        $sql .= "-- Existing data in these tables will be replaced.\n";
        $sql .= "--\n\n";
        
        $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
        
        foreach ($tables as $table) {
            $sql .= $this->dump_table($table);
        }
        
        $sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";
        $sql .= "\n-- End of CastConductor Backup\n";
        
        return $sql;
    }

    /**
     * Dump a single table to SQL
     *
     * @param string $table Table name with prefix
     * @return string SQL for the table
     */
    private function dump_table($table) {
        global $wpdb;
        
        $sql = "-- --------------------------------------------------------\n";
        $sql .= "-- Table: {$table}\n";
        $sql .= "-- --------------------------------------------------------\n\n";
        
        // Get CREATE TABLE statement
        $create_result = $wpdb->get_row("SHOW CREATE TABLE `{$table}`", ARRAY_N);
        if ($create_result) {
            $create_sql = $create_result[1];
            
            // Use DROP TABLE IF EXISTS for clean restore
            $sql .= "DROP TABLE IF EXISTS `{$table}`;\n\n";
            $sql .= $create_sql . ";\n\n";
        }
        
        // Get row count
        $count = $wpdb->get_var("SELECT COUNT(*) FROM `{$table}`");
        $sql .= "-- Rows: {$count}\n\n";
        
        if ($count > 0) {
            // Fetch all rows
            $rows = $wpdb->get_results("SELECT * FROM `{$table}`", ARRAY_A);
            
            if (!empty($rows)) {
                // Get column names
                $columns = array_keys($rows[0]);
                $column_list = '`' . implode('`, `', $columns) . '`';
                
                // Generate INSERT statements (batched for efficiency)
                $batch_size = 100;
                $values_batch = array();
                
                foreach ($rows as $i => $row) {
                    $values = array();
                    foreach ($row as $value) {
                        if ($value === null) {
                            $values[] = 'NULL';
                        } else {
                            $values[] = "'" . $wpdb->_real_escape($value) . "'";
                        }
                    }
                    $values_batch[] = '(' . implode(', ', $values) . ')';
                    
                    // Write batch or final
                    if (count($values_batch) >= $batch_size || $i === count($rows) - 1) {
                        $sql .= "INSERT INTO `{$table}` ({$column_list}) VALUES\n";
                        $sql .= implode(",\n", $values_batch) . ";\n\n";
                        $values_batch = array();
                    }
                }
            }
        }
        
        return $sql;
    }

    /**
     * Download backup as SQL file
     *
     * @param bool $include_analytics Whether to include analytics
     */
    public function download_backup($include_analytics = false) {
        $sql = $this->generate_backup($include_analytics);
        
        $filename = 'castconductor-backup-' . gmdate('Y-m-d-His') . '.sql';
        
        // Set headers for download
        header('Content-Type: application/sql');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . strlen($sql));
        header('Cache-Control: no-cache, must-revalidate');
        header('Expires: 0');
        
        echo $sql;
        exit;
    }

    /**
     * Create automatic pre-restore backup
     *
     * @return string|false Path to backup file or false on failure
     */
    public function create_pre_restore_backup() {
        $upload_dir = wp_upload_dir();
        $backup_dir = $upload_dir['basedir'] . '/castconductor-backups';
        
        // Create backup directory if needed
        if (!file_exists($backup_dir)) {
            wp_mkdir_p($backup_dir);
            // Add .htaccess to protect backups
            file_put_contents($backup_dir . '/.htaccess', "Deny from all\n");
        }
        
        $filename = 'pre-restore-' . gmdate('Y-m-d-His') . '.sql';
        $filepath = $backup_dir . '/' . $filename;
        
        try {
            $sql = $this->generate_backup(true); // Include analytics in pre-restore
            file_put_contents($filepath, $sql);
            
            // Cleanup old pre-restore backups (keep last 5)
            $this->cleanup_old_backups($backup_dir, 5);
            
            return $filepath;
        } catch (Exception $e) {
            error_log('CastConductor: Pre-restore backup failed: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * Cleanup old backup files
     *
     * @param string $dir Backup directory
     * @param int $keep Number of backups to keep
     */
    private function cleanup_old_backups($dir, $keep = 5) {
        $files = glob($dir . '/pre-restore-*.sql');
        if (count($files) > $keep) {
            // Sort by modification time (oldest first)
            usort($files, function($a, $b) {
                return filemtime($a) - filemtime($b);
            });
            
            // Remove oldest files
            $to_delete = array_slice($files, 0, count($files) - $keep);
            foreach ($to_delete as $file) {
                unlink($file);
            }
        }
    }

    /**
     * Restore from SQL backup file
     *
     * @param string $filepath Path to SQL backup file
     * @param bool $create_pre_backup Whether to create pre-restore backup
     * @return array Result with status and messages
     */
    public function restore_from_file($filepath, $create_pre_backup = true) {
        global $wpdb;
        
        $result = array(
            'success' => false,
            'pre_backup_path' => null,
            'tables_restored' => 0,
            'rows_inserted' => 0,
            'errors' => array(),
            'warnings' => array(),
        );
        
        // Validate file exists
        if (!file_exists($filepath)) {
            $result['errors'][] = 'Backup file not found';
            return $result;
        }
        
        // Read SQL content
        $sql_content = file_get_contents($filepath);
        if (empty($sql_content)) {
            $result['errors'][] = 'Backup file is empty';
            return $result;
        }
        
        // Validate it's a CastConductor backup
        if (strpos($sql_content, 'CastConductor Database Backup') === false) {
            $result['errors'][] = 'Invalid backup file: Not a CastConductor backup';
            return $result;
        }
        
        // Create pre-restore backup
        if ($create_pre_backup) {
            $pre_backup = $this->create_pre_restore_backup();
            if ($pre_backup) {
                $result['pre_backup_path'] = $pre_backup;
            } else {
                $result['warnings'][] = 'Could not create pre-restore backup';
            }
        }
        
        // Check if backup uses different table prefix
        preg_match('/-- Table Prefix: (.+)/', $sql_content, $prefix_match);
        $backup_prefix = isset($prefix_match[1]) ? trim($prefix_match[1]) : $wpdb->prefix;
        
        if ($backup_prefix !== $wpdb->prefix) {
            // Replace table prefix in SQL
            $sql_content = str_replace(
                "`{$backup_prefix}castconductor_",
                "`{$wpdb->prefix}castconductor_",
                $sql_content
            );
            $result['warnings'][] = "Table prefix changed from '{$backup_prefix}' to '{$wpdb->prefix}'";
        }
        
        // Execute SQL statements
        try {
            // Split into individual statements
            $statements = $this->split_sql_statements($sql_content);
            
            // Build allowed table pattern for this site
            $allowed_pattern = '`' . $wpdb->prefix . 'castconductor_';
            
            // Disable foreign key checks is already in the SQL, but ensure it
            $wpdb->query('SET FOREIGN_KEY_CHECKS = 0');
            
            foreach ($statements as $statement) {
                $statement = trim($statement);
                
                // Skip empty statements
                if (empty($statement)) {
                    continue;
                }
                
                // Skip comment-only statements (lines that start with --)
                // But handle multi-line where comments precede actual SQL
                $lines = explode("\n", $statement);
                $sql_lines = array_filter($lines, function($line) {
                    $line = trim($line);
                    return !empty($line) && strpos($line, '--') !== 0;
                });
                
                if (empty($sql_lines)) {
                    continue; // Only comments, skip
                }
                
                // Reconstruct statement from non-comment lines
                $clean_statement = implode("\n", $sql_lines);
                
                // Skip SET statements (safe)
                if (stripos($clean_statement, 'SET ') === 0) {
                    $wpdb->query($clean_statement);
                    continue;
                }
                
                // CRITICAL SAFETY CHECK: Verify statement only affects CastConductor tables
                // This prevents any malicious SQL from touching other tables
                if (preg_match('/^(DROP TABLE IF EXISTS|DROP TABLE|CREATE TABLE|INSERT INTO|UPDATE|DELETE FROM|TRUNCATE)/i', $clean_statement)) {
                    // Extract table name from statement - look for backtick-wrapped table name
                    // Pattern matches: `wp_castconductor_*` anywhere in statement
                    if (!preg_match('/`' . preg_quote($wpdb->prefix, '/') . 'castconductor_[a-z_]+`/i', $clean_statement)) {
                        $result['errors'][] = 'SECURITY: Blocked statement targeting non-CastConductor table';
                        error_log('CastConductor Backup SECURITY: Blocked potentially malicious SQL: ' . substr($clean_statement, 0, 100));
                        continue; // Skip this statement entirely
                    }
                }
                
                // Execute statement
                $query_result = $wpdb->query($clean_statement);
                
                if ($query_result === false) {
                    $result['errors'][] = 'SQL Error: ' . $wpdb->last_error;
                } else {
                    // Track statistics
                    if (stripos($clean_statement, 'DROP TABLE') !== false) {
                        // Table dropped
                    } elseif (stripos($clean_statement, 'CREATE TABLE') !== false) {
                        $result['tables_restored']++;
                    } elseif (stripos($clean_statement, 'INSERT INTO') !== false) {
                        $result['rows_inserted'] += $wpdb->rows_affected;
                    }
                }
            }
            
            // Re-enable foreign key checks
            $wpdb->query('SET FOREIGN_KEY_CHECKS = 1');
            
            $result['success'] = empty($result['errors']);
            
        } catch (Exception $e) {
            $result['errors'][] = 'Restore failed: ' . $e->getMessage();
            // Try to re-enable foreign key checks
            $wpdb->query('SET FOREIGN_KEY_CHECKS = 1');
        }
        
        return $result;
    }

    /**
     * Split SQL content into individual statements
     *
     * @param string $sql SQL content
     * @return array Array of statements
     */
    private function split_sql_statements($sql) {
        $statements = array();
        $current = '';
        $in_string = false;
        $string_char = '';
        $length = strlen($sql);
        
        for ($i = 0; $i < $length; $i++) {
            $char = $sql[$i];
            
            // Handle string literals
            if (($char === "'" || $char === '"') && ($i === 0 || $sql[$i-1] !== '\\')) {
                if (!$in_string) {
                    $in_string = true;
                    $string_char = $char;
                } elseif ($char === $string_char) {
                    $in_string = false;
                }
            }
            
            // Check for statement terminator
            if ($char === ';' && !$in_string) {
                $statements[] = $current;
                $current = '';
                continue;
            }
            
            $current .= $char;
        }
        
        // Add any remaining content
        if (trim($current)) {
            $statements[] = $current;
        }
        
        return $statements;
    }

    /**
     * Get backup statistics (table counts, sizes)
     *
     * @return array Statistics for each table
     */
    public function get_backup_stats() {
        global $wpdb;
        
        $stats = array(
            'tables' => array(),
            'total_rows' => 0,
            'estimated_size' => 0,
        );
        
        $tables = $this->get_table_names(true);
        
        foreach ($tables as $table) {
            $count = $wpdb->get_var("SELECT COUNT(*) FROM `{$table}`");
            $size_result = $wpdb->get_row(
                "SELECT 
                    data_length + index_length AS size 
                FROM information_schema.TABLES 
                WHERE table_schema = DATABASE() 
                AND table_name = '{$table}'"
            );
            
            $size = $size_result ? (int)$size_result->size : 0;
            
            // Clean table name (remove prefix)
            $clean_name = str_replace($wpdb->prefix, '', $table);
            
            $stats['tables'][$clean_name] = array(
                'rows' => (int)$count,
                'size' => $size,
            );
            
            $stats['total_rows'] += (int)$count;
            $stats['estimated_size'] += $size;
        }
        
        return $stats;
    }

    /**
     * List available pre-restore backups
     *
     * @return array List of backup files with metadata
     */
    public function list_pre_restore_backups() {
        $upload_dir = wp_upload_dir();
        $backup_dir = $upload_dir['basedir'] . '/castconductor-backups';
        
        if (!file_exists($backup_dir)) {
            return array();
        }
        
        $backups = array();
        $files = glob($backup_dir . '/pre-restore-*.sql');
        
        foreach ($files as $file) {
            $backups[] = array(
                'filename' => basename($file),
                'path' => $file,
                'size' => filesize($file),
                'date' => filemtime($file),
                'date_formatted' => date('Y-m-d H:i:s', filemtime($file)),
            );
        }
        
        // Sort by date descending (newest first)
        usort($backups, function($a, $b) {
            return $b['date'] - $a['date'];
        });
        
        return $backups;
    }
}
