<?php
/**
 * XCHANGE 2025: DATABASE BACKUP UTILITY
 * 
 * Creates a backup of the database before running migrations
 * 
 * Usage: php create_backup.php [--output-dir=path] [--compress]
 * 
 * Options:
 *   --output-dir=path    Directory to save backup (default: ./backups/)
 *   --compress          Compress backup with gzip
 *   --tables-only       Backup only specific tables (products, registrations, etc.)
 */

require_once 'db_config.php';

class DatabaseBackup {
    private $pdo;
    private $outputDir = './backups/';
    private $compress = false;
    private $tablesOnly = false;
    
    // Critical tables for delegate migration
    private $criticalTables = [
        'products',
        'product_categories', 
        'product_pricing',
        'registrations',
        'registration_products',
        'delegates',
        'admin_activity_log'
    ];
    
    public function __construct() {
        $this->pdo = getDBConnection();
        
        // Parse command line arguments
        global $argv;
        if (isset($argv)) {
            foreach ($argv as $arg) {
                if (strpos($arg, '--output-dir=') === 0) {
                    $this->outputDir = substr($arg, 13);
                } elseif ($arg === '--compress') {
                    $this->compress = true;
                } elseif ($arg === '--tables-only') {
                    $this->tablesOnly = true;
                }
            }
        }
        
        // Ensure output directory exists
        if (!is_dir($this->outputDir)) {
            mkdir($this->outputDir, 0755, true);
        }
    }
    
    public function setTablesOnly($tablesOnly) {
        $this->tablesOnly = $tablesOnly;
    }
    
    public function setCompress($compress) {
        $this->compress = $compress;
    }
    
    public function createBackup() {
        try {
            $timestamp = date('Y_m_d_H_i_s');
            $dbName = $this->getDatabaseName();
            
            $backupType = $this->tablesOnly ? 'tables' : 'full';
            $filename = "xchange_backup_{$backupType}_{$timestamp}.sql";
            
            if ($this->compress) {
                $filename .= '.gz';
            }
            
            $filepath = $this->outputDir . $filename;
            
            echo "Creating database backup...\n";
            echo "Database: {$dbName}\n";
            echo "Type: " . ($this->tablesOnly ? 'Critical tables only' : 'Full database') . "\n";
            echo "Output: {$filepath}\n";
            echo "Compress: " . ($this->compress ? 'Yes' : 'No') . "\n";
            echo "\n";
            
            if ($this->tablesOnly) {
                $this->backupCriticalTables($filepath, $dbName);
            } else {
                $this->backupFullDatabase($filepath, $dbName);
            }
            
            $fileSize = $this->formatBytes(filesize($filepath));
            echo "✅ Backup created successfully!\n";
            echo "File: {$filepath}\n";
            echo "Size: {$fileSize}\n";
            
            // Verify backup
            $this->verifyBackup($filepath);
            
            return $filepath;
            
        } catch (Exception $e) {
            echo "❌ Backup failed: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    private function getDatabaseName() {
        $stmt = $this->pdo->prepare("SELECT DATABASE() as db_name");
        $stmt->execute();
        return $stmt->fetch()['db_name'];
    }
    
    private function backupFullDatabase($filepath, $dbName) {
        $command = "mysqldump";
        $command .= " -h" . DB_HOST;
        $command .= " -u" . DB_USER;
        if (DB_PASS) {
            $command .= " -p" . DB_PASS;
        }
        $command .= " --single-transaction --routines --triggers";
        $command .= " " . $dbName;
        
        if ($this->compress) {
            $command .= " | gzip > " . escapeshellarg($filepath);
        } else {
            $command .= " > " . escapeshellarg($filepath);
        }
        
        echo "Running: mysqldump (password hidden)\n";
        $output = [];
        $returnCode = 0;
        exec($command, $output, $returnCode);
        
        if ($returnCode !== 0) {
            throw new Exception("mysqldump failed with code {$returnCode}: " . implode("\n", $output));
        }
    }
    
    private function backupCriticalTables($filepath, $dbName) {
        echo "Backing up critical tables for delegate migration...\n";
        
        $handle = $this->compress ? gzopen($filepath, 'w') : fopen($filepath, 'w');
        
        if (!$handle) {
            throw new Exception("Failed to open backup file for writing");
        }
        
        $this->writeToFile($handle, "-- XChange Database Backup (Critical Tables)\n");
        $this->writeToFile($handle, "-- Created: " . date('Y-m-d H:i:s') . "\n");
        $this->writeToFile($handle, "-- Database: {$dbName}\n\n");
        
        $this->writeToFile($handle, "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n");
        $this->writeToFile($handle, "START TRANSACTION;\n");
        $this->writeToFile($handle, "SET time_zone = \"+00:00\";\n\n");
        
        foreach ($this->criticalTables as $table) {
            if ($this->tableExists($table)) {
                echo "  Backing up table: {$table}\n";
                $this->backupTable($handle, $table);
            } else {
                echo "  ⚠️  Table {$table} does not exist, skipping\n";
            }
        }
        
        $this->writeToFile($handle, "\nCOMMIT;\n");
        
        if ($this->compress) {
            gzclose($handle);
        } else {
            fclose($handle);
        }
    }
    
    private function tableExists($tableName) {
        $stmt = $this->pdo->query("SHOW TABLES LIKE '$tableName'");
        return $stmt->fetch() !== false;
    }
    
    private function backupTable($handle, $tableName) {
        // Get table structure
        $stmt = $this->pdo->query("SHOW CREATE TABLE `$tableName`");
        $createTable = $stmt->fetch();
        
        $this->writeToFile($handle, "-- Table structure for table `{$tableName}`\n");
        $this->writeToFile($handle, "DROP TABLE IF EXISTS `{$tableName}`;\n");
        $this->writeToFile($handle, $createTable['Create Table'] . ";\n\n");
        
        // Get table data
        $stmt = $this->pdo->query("SELECT COUNT(*) as count FROM `$tableName`");
        $rowCount = $stmt->fetch()['count'];
        
        if ($rowCount > 0) {
            $this->writeToFile($handle, "-- Dumping data for table `{$tableName}` ({$rowCount} rows)\n");
            
            $stmt = $this->pdo->query("SELECT * FROM `$tableName`");
            
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $columns = array_keys($row);
                $values = array_map(function($value) {
                    return $value === null ? 'NULL' : $this->pdo->quote($value);
                }, array_values($row));
                
                $insertSql = "INSERT INTO `{$tableName}` (`" . implode('`, `', $columns) . "`) VALUES (" . implode(', ', $values) . ");\n";
                $this->writeToFile($handle, $insertSql);
            }
        } else {
            $this->writeToFile($handle, "-- No data for table `{$tableName}`\n");
        }
        
        $this->writeToFile($handle, "\n");
    }
    
    private function writeToFile($handle, $content) {
        if ($this->compress) {
            gzwrite($handle, $content);
        } else {
            fwrite($handle, $content);
        }
    }
    
    private function verifyBackup($filepath) {
        echo "\nVerifying backup...\n";
        
        if (!file_exists($filepath)) {
            throw new Exception("Backup file not found: {$filepath}");
        }
        
        $fileSize = filesize($filepath);
        if ($fileSize < 1000) {
            throw new Exception("Backup file too small ({$fileSize} bytes) - likely empty or corrupt");
        }
        
        // Check if file contains expected content
        if ($this->compress) {
            $handle = gzopen($filepath, 'r');
            $contentStr = gzread($handle, 2000);
            gzclose($handle);
        } else {
            $contentStr = file_get_contents($filepath, false, null, 0, 2000);
        }
        
        if (strpos($contentStr, 'CREATE TABLE') === false && strpos($contentStr, 'DROP TABLE') === false) {
            throw new Exception("Backup file appears to be corrupt - no SQL statements found");
        }
        
        echo "✅ Backup verification passed\n";
    }
    
    private function formatBytes($size, $precision = 2) {
        $units = array('B', 'KB', 'MB', 'GB', 'TB');
        
        for ($i = 0; $size > 1024 && $i < count($units) - 1; $i++) {
            $size /= 1024;
        }
        
        return round($size, $precision) . ' ' . $units[$i];
    }
}

// Run backup if called directly
if (basename(__FILE__) == basename($_SERVER["SCRIPT_NAME"])) {
    echo "=== XCHANGE DATABASE BACKUP UTILITY ===\n\n";
    
    $backup = new DatabaseBackup();
    $result = $backup->createBackup();
    
    if ($result) {
        echo "\n🎉 Backup completed successfully!\n";
        echo "You can now safely run the migration.\n";
        exit(0);
    } else {
        echo "\n❌ Backup failed!\n";
        exit(1);
    }
}
?>
