<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Backup extends MY_Controller {
    public function __construct(){
        parent::__construct();
        $this->load_global();
        
        if(!special_access()){
            show_error("Access Denied", 403, $heading = "Unauthorized Access!!");exit();    
        }
    }
    
    public function index(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        try {
            $data = $this->data; // My_Controller constructor data accessed here
            $data['page_title'] = $this->lang->line('database_backup');
            
            // Get list of existing backups
            $data['backups'] = $this->get_backup_files();
            
            $this->load->view('backup', $data);
        } catch (Exception $e) {
            log_message('error', 'Backup controller error: ' . $e->getMessage());
            show_error('An error occurred while loading the backup page. Please check the error logs.');
        }
    }
    
    // Simple test method
    public function test(){
        echo "Backup controller is working!";
    }
    
    // Test restore method
    public function test_restore(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        try {
            $result = $this->db->query("SELECT 1 as test");
            if($result){
                echo "Database connection successful<br>";
            } else {
                echo "Database connection failed: " . $this->db->error()['message'] . "<br>";
                return;
            }
            
            $result = $this->db->query("SET FOREIGN_KEY_CHECKS = 0");
            if($result){
                echo "Foreign key checks disabled successfully<br>";
            } else {
                echo "Failed to disable foreign key checks: " . $this->db->error()['message'] . "<br>";
            }
            
            $result = $this->db->query("SET FOREIGN_KEY_CHECKS = 1");
            if($result){
                echo "Foreign key checks enabled successfully<br>";
            } else {
                echo "Failed to enable foreign key checks: " . $this->db->error()['message'] . "<br>";
            }
            
            $result = $this->db->query("SHOW TABLES");
            if($result){
                echo "Database has " . $result->num_rows() . " tables<br>";
            } else {
                echo "Failed to get tables: " . $this->db->error()['message'] . "<br>";
            }
            
            echo "All tests completed successfully!";
            
        } catch (Exception $e) {
            echo "Database test failed: " . $e->getMessage();
        }
    }
    
    public function create_backup(){
        if(demo_app()){
            $this->_backup_response("Restricted in Demo");
            return;
        }
        
        @ini_set('memory_limit', '512M');
        @ini_set('max_execution_time', '300');
        @ini_set('display_errors', 0);
        
        while (ob_get_level()) { ob_end_clean(); }
        ob_start();
        
        $backup_name = $this->input->post('backup_name');
        if(empty($backup_name)){
            $backup_name = 'backup_' . date('Y-m-d_H-i-s');
        }
        $backup_name = preg_replace('/[^a-zA-Z0-9_\-]/', '_', $backup_name);
        
        $backup_dir = FCPATH . 'dbbackup' . DIRECTORY_SEPARATOR;
        if (!is_dir($backup_dir)) {
            if (!@mkdir($backup_dir, 0755, true)) {
                log_message('error', 'Backup: Could not create directory: ' . $backup_dir);
                ob_end_clean();
                $this->_backup_response("Backup folder could not be created.");
                return;
            }
        }
        if (!is_writable($backup_dir)) {
            log_message('error', 'Backup: Directory not writable: ' . $backup_dir);
            ob_end_clean();
            $this->_backup_response("Backup folder is not writable.");
            return;
        }
        
        try {
            $backup = $this->_build_backup_sql();
        } catch (Throwable $e) {
            log_message('error', 'Backup: exception: ' . $e->getMessage());
            ob_end_clean();
            $this->_backup_response("Database backup failed: " . $e->getMessage());
            return;
        }
        
        $out = ob_get_clean();
        if ($out !== '' && $out !== false) {
            log_message('error', 'Backup: Unexpected output during backup: ' . substr($out, 0, 500));
        }
        
        if ($backup === FALSE || $backup === '') {
            log_message('error', 'Backup: backup produced no data.');
            $this->_backup_response("Database backup produced no data.");
            return;
        }
        
        $this->load->helper('file');
        if (class_exists('ZipArchive')) {
            $zip = new ZipArchive();
            $zip_path = $backup_dir . $backup_name . '.zip';
            if ($zip->open($zip_path, ZipArchive::CREATE | ZipArchive::OVERWRITE) === TRUE) {
                $zip->addFromString($backup_name . '.sql', $backup);
                $zip->close();
                $written = file_exists($zip_path) && filesize($zip_path) > 0;
                $filename = $zip_path;
            } else {
                $filename = $backup_dir . $backup_name . '.sql';
                $written = write_file($filename, $backup);
            }
        } else {
            $filename = $backup_dir . $backup_name . '.sql';
            $written = write_file($filename, $backup);
        }
        
        if ($written) {
            $this->_backup_response("success", true);
        } else {
            log_message('error', 'Backup: write_file failed for: ' . $filename);
            $this->_backup_response("Could not write file to disk. Check folder permissions.");
        }
    }
    
    /**
     * Build SQL dump with table names always backtick-quoted (fixes tables named e.g. "123"
     * where CI escape_identifiers returns them unquoted and causes SQL syntax error).
     */
    private function _build_backup_sql(){
        $newline = "\n";
        $output = "SET foreign_key_checks = 0;".$newline;
        
        $tables = $this->db->list_tables();
        if (!is_array($tables)) {
            return FALSE;
        }
        
        foreach ($tables as $table) {
            $table = (string) $table;
            $quoted = '`'.str_replace('`', '``', $table).'`';
            
            $query = $this->db->query('SHOW CREATE TABLE '.$quoted);
            if ($query === FALSE || $query->num_rows() === 0) {
                continue;
            }
            
            $row = $query->row_array();
            $vals = array_values($row);
            $create = isset($row['Create Table']) ? $row['Create Table'] : (isset($vals[1]) ? $vals[1] : '');
            $output .= '# TABLE STRUCTURE FOR: '.$table.$newline.'#'.$newline.$newline;
            $output .= 'DROP TABLE IF EXISTS '.$quoted.';'.$newline.$newline;
            $output .= $create.';'.$newline.$newline;
            
            $query = $this->db->query('SELECT * FROM '.$quoted);
            if ($query === FALSE || $query->num_rows() === 0) {
                continue;
            }
            
            $fields = $query->list_fields();
            $field_list = array();
            foreach ($fields as $f) {
                $field_list[] = '`'.str_replace('`', '``', $f).'`';
            }
            $field_str = implode(', ', $field_list);
            
            foreach ($query->result_array() as $row) {
                $vals = array();
                foreach ($row as $v) {
                    $vals[] = ($v === NULL) ? 'NULL' : $this->db->escape($v);
                }
                $output .= 'INSERT INTO '.$quoted.' ('.$field_str.') VALUES ('.implode(', ', $vals).');'.$newline;
            }
            $output .= $newline;
        }
        
        $output .= "SET foreign_key_checks = 1;".$newline;
        return $output;
    }
    
    private function _backup_response($message, $success = false){
        if (ob_get_level()) { ob_end_clean(); }
        header('Content-Type: text/plain; charset=utf-8');
        header('X-Content-Type-Options: nosniff');
        echo $success ? $message : "Failed to create backup: " . $message;
    }
    
    public function upload_backup(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        // Set upload configuration
        $config['upload_path'] = 'dbbackup/';
        $config['allowed_types'] = 'zip|sql';
        $config['max_size'] = 200 * 1024; // 200MB in KB
        $config['file_name'] = 'uploaded_' . date('Y-m-d_H-i-s') . '_' . uniqid();
        
        // Ensure upload directory exists
        if(!is_dir($config['upload_path'])){
            mkdir($config['upload_path'], 0755, true);
        }
        
        $this->load->library('upload', $config);
        
        if(!$this->upload->do_upload('backup_file')){
            $error = $this->upload->display_errors();
            log_message('error', 'Upload failed: ' . $error);
            echo "Upload failed: " . $error;
            return;
        }
        
        $upload_data = $this->upload->data();
        $uploaded_file = $upload_data['full_path'];
        $original_name = $upload_data['orig_name'];
        
        log_message('info', 'File uploaded successfully: ' . $uploaded_file);
        
        // Debug: Analyze the uploaded file first
        $this->debug_uploaded_file($uploaded_file);
        
        // Validate the uploaded file
        if(!$this->validate_backup_file($uploaded_file)){
            unlink($uploaded_file); // Delete invalid file
            echo "Invalid backup file format. Please ensure the file is a valid database backup. Check the logs for details.";
            return;
        }
        
        // Create a backup copy with original name for display
        $display_name = 'uploaded_' . pathinfo($original_name, PATHINFO_FILENAME) . '_' . date('Y-m-d_H-i-s') . '.' . pathinfo($original_name, PATHINFO_EXTENSION);
        $display_path = 'dbbackup/' . $display_name;
        
        if(copy($uploaded_file, $display_path)){
            unlink($uploaded_file); // Remove temporary file
            $uploaded_file = $display_path;
        }
        
        // Now restore the uploaded backup
        $result = $this->restore_from_file($uploaded_file);
        
        if($result === true){
            echo "success";
        } else {
            // If restore failed, delete the uploaded file
            if(file_exists($uploaded_file)){
                unlink($uploaded_file);
            }
            echo "Failed to restore database: " . $result;
        }
    }
    
    private function validate_backup_file($filepath){
        if(!file_exists($filepath)){
            log_message('error', 'Backup file does not exist: ' . $filepath);
            return false;
        }
        
        $file_size = filesize($filepath);
        if($file_size == 0){
            log_message('error', 'Backup file is empty: ' . $filepath);
            return false;
        }
        
        $extension = strtolower(pathinfo($filepath, PATHINFO_EXTENSION));
        log_message('info', 'Validating backup file: ' . $filepath . ' (extension: ' . $extension . ', size: ' . $file_size . ')');
        
        if($extension == 'zip'){
            if(!class_exists('ZipArchive')){
                log_message('error', 'ZipArchive class not available for validation');
                return false;
            }
            
            $zip = new ZipArchive;
            if($zip->open($filepath) !== TRUE){
                log_message('error', 'Failed to open ZIP file for validation: ' . $filepath);
                return false;
            }
            
            log_message('info', 'ZIP file opened successfully, checking contents...');
            
            // Check if zip contains SQL content (more flexible approach)
            $has_sql_content = false;
            $has_sql_file = false;
            
            for($i = 0; $i < $zip->numFiles; $i++){
                $filename = $zip->getNameIndex($i);
                log_message('info', 'ZIP contains file: ' . $filename);
                
                // Check for .sql extension
                if(pathinfo($filename, PATHINFO_EXTENSION) == 'sql'){
                    $has_sql_file = true;
                    log_message('info', 'Found SQL file in ZIP: ' . $filename);
                }
                
                // Try to read content and check for SQL keywords
                $content = $zip->getFromIndex($i);
                if($content && (
                    strpos($content, 'CREATE TABLE') !== false || 
                    strpos($content, 'INSERT INTO') !== false ||
                    strpos($content, 'DROP TABLE') !== false ||
                    strpos($content, 'CREATE DATABASE') !== false
                )){
                    $has_sql_content = true;
                    log_message('info', 'Found SQL content in ZIP file: ' . $filename);
                }
            }
            
            $zip->close();
            
            // Accept if either has SQL file extension OR SQL content
            $is_valid = $has_sql_file || $has_sql_content;
            log_message('info', 'ZIP validation result - Has SQL file: ' . ($has_sql_file ? 'yes' : 'no') . ', Has SQL content: ' . ($has_sql_content ? 'yes' : 'no') . ', Valid: ' . ($is_valid ? 'yes' : 'no'));
            
            return $is_valid;
            
        } elseif($extension == 'sql'){
            // Check if file contains SQL content
            $content = file_get_contents($filepath, false, null, 0, 1024); // Read first 1KB
            $has_sql = (strpos($content, 'CREATE TABLE') !== false || 
                       strpos($content, 'INSERT INTO') !== false ||
                       strpos($content, 'DROP TABLE') !== false ||
                       strpos($content, 'CREATE DATABASE') !== false);
            
            log_message('info', 'SQL file validation result: ' . ($has_sql ? 'valid' : 'invalid'));
            return $has_sql;
        }
        
        log_message('error', 'Unsupported file extension: ' . $extension);
        return false;
    }
    
    private function debug_uploaded_file($filepath){
        log_message('info', '=== DEBUGGING UPLOADED FILE ===');
        log_message('info', 'File path: ' . $filepath);
        log_message('info', 'File exists: ' . (file_exists($filepath) ? 'yes' : 'no'));
        
        if(file_exists($filepath)){
            log_message('info', 'File size: ' . filesize($filepath) . ' bytes');
            log_message('info', 'File extension: ' . pathinfo($filepath, PATHINFO_EXTENSION));
            
            $extension = strtolower(pathinfo($filepath, PATHINFO_EXTENSION));
            
            if($extension == 'zip'){
                if(class_exists('ZipArchive')){
                    $zip = new ZipArchive;
                    if($zip->open($filepath) === TRUE){
                        log_message('info', 'ZIP file opened successfully');
                        log_message('info', 'Number of files in ZIP: ' . $zip->numFiles);
                        
                        for($i = 0; $i < $zip->numFiles; $i++){
                            $filename = $zip->getNameIndex($i);
                            log_message('info', 'File ' . $i . ': ' . $filename);
                            
                            // Try to get file size
                            $stat = $zip->statIndex($i);
                            if($stat){
                                log_message('info', '  Size: ' . $stat['size'] . ' bytes');
                            }
                            
                            // Try to read first 200 characters
                            $content = $zip->getFromIndex($i, 200);
                            if($content){
                                log_message('info', '  Content preview: ' . substr($content, 0, 200));
                            }
                        }
                        $zip->close();
                    } else {
                        log_message('error', 'Failed to open ZIP file');
                    }
                } else {
                    log_message('error', 'ZipArchive class not available');
                }
            } else {
                // For non-ZIP files, read first 500 characters
                $content = file_get_contents($filepath, false, null, 0, 500);
                log_message('info', 'Content preview: ' . substr($content, 0, 500));
            }
        }
        log_message('info', '=== END DEBUG ===');
    }
    
    // Public method to test file validation
    public function test_upload_validation(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        $filename = $this->input->get('file');
        if(empty($filename)){
            echo "Please provide a filename parameter: ?file=your_backup_file.zip";
            return;
        }
        
        $filepath = 'dbbackup/' . $filename;
        if(!file_exists($filepath)){
            echo "File not found: " . $filepath;
            return;
        }
        
        echo "<h2>File Validation Test</h2>";
        echo "<p><strong>File:</strong> " . $filepath . "</p>";
        echo "<p><strong>Size:</strong> " . filesize($filepath) . " bytes</p>";
        echo "<p><strong>Extension:</strong> " . pathinfo($filepath, PATHINFO_EXTENSION) . "</p>";
        
        $this->debug_uploaded_file($filepath);
        
        $is_valid = $this->validate_backup_file($filepath);
        echo "<p><strong>Validation Result:</strong> " . ($is_valid ? "VALID" : "INVALID") . "</p>";
        
        if($is_valid){
            echo "<p style='color: green;'>✅ File is valid and can be restored!</p>";
        } else {
            echo "<p style='color: red;'>❌ File is invalid. Check the logs for details.</p>";
        }
    }
    
    private function restore_from_file($filepath){
        try {
            error_reporting(E_ALL & ~E_DEPRECATED & ~E_STRICT);
            ini_set('display_errors', 1);
            ini_set('memory_limit', '512M');
            ini_set('max_execution_time', 600);
            ini_set('max_input_time', 600);
            
            $filename = basename($filepath);
            log_message('info', 'Starting restore from uploaded file: ' . $filepath);
            
            if(!file_exists($filepath)){
                log_message('error', 'Uploaded file not found: ' . $filepath);
                return "Backup file not found";
            }
            
            $file_size = filesize($filepath);
            log_message('info', 'Uploaded file size: ' . $file_size . ' bytes');
            
            if($file_size > 200 * 1024 * 1024) {
                return "Backup file is too large (max 200MB)";
            }
            
            $backup_content = '';
            $handle = fopen($filepath, 'r');
            if($handle === false) {
                log_message('error', 'Failed to open uploaded file: ' . $filepath);
                return "Failed to open backup file";
            }
            
            while(!feof($handle)) {
                $backup_content .= fread($handle, 8192);
            }
            fclose($handle);
            
            if(empty($backup_content)) {
                log_message('error', 'Uploaded file is empty: ' . $filepath);
                return "Backup file is empty";
            }
            
            log_message('info', 'Backup content size: ' . strlen($backup_content) . ' bytes');
            
            // Handle zip files
            if(pathinfo($filename, PATHINFO_EXTENSION) == 'zip'){
                log_message('info', 'Processing uploaded zip file');
                
                if(!class_exists('ZipArchive')) {
                    log_message('error', 'ZipArchive class not available');
                    return "ZipArchive not available on this server";
                }
                
                $zip = new ZipArchive;
                if ($zip->open($filepath) === TRUE) {
                    $backup_content = $zip->getFromIndex(0);
                    $zip->close();
                    
                    if($backup_content === false){
                        log_message('error', 'Failed to extract SQL from uploaded zip file');
                        return "Failed to extract SQL from zip file";
                    }
                    log_message('info', 'Extracted SQL size: ' . strlen($backup_content) . ' bytes');
                } else {
                    log_message('error', 'Failed to open uploaded zip file: ' . $filepath);
                    return "Failed to open backup file";
                }
            }
            
            // Test database connection
            $test_result = $this->db->query("SELECT 1 as test");
            if(!$test_result) {
                $db_error = $this->db->error();
                log_message('error', 'Database connection failed: ' . $db_error['message']);
                return "Database connection failed: " . $db_error['message'];
            }
            
            // Execute restore process (reuse existing restore logic)
            $this->db->query('SET FOREIGN_KEY_CHECKS = 0');
            $this->db->query('SET SQL_MODE = ""');
            $this->db->query('SET AUTOCOMMIT = 0');
            
            $sql_queries = array();
            $lines = explode("\n", $backup_content);
            $current_query = '';
            $in_string = false;
            $string_char = '';
            $in_comment = false;
            
            foreach($lines as $line_num => $line) {
                $line = rtrim($line);
                
                if(empty($line)) {
                    continue;
                }
                
                if(strpos($line, '/*') !== false) {
                    $in_comment = true;
                }
                if(strpos($line, '*/') !== false) {
                    $in_comment = false;
                    continue;
                }
                if($in_comment) {
                    continue;
                }
                
                if(strpos($line, '--') === 0) {
                    continue;
                }
                
                for($i = 0; $i < strlen($line); $i++) {
                    $char = $line[$i];
                    
                    if(($char == "'" || $char == '"') && ($i == 0 || $line[$i-1] != '\\')) {
                        if(!$in_string) {
                            $in_string = true;
                            $string_char = $char;
                        } elseif($char == $string_char) {
                            $in_string = false;
                        }
                    }
                    
                    $current_query .= $char;
                    
                    if($char == ';' && !$in_string) {
                        $current_query = trim($current_query);
                        if(!empty($current_query)) {
                            $sql_queries[] = $current_query;
                        }
                        $current_query = '';
                    }
                }
                
                if($line_num < count($lines) - 1) {
                    $current_query .= "\n";
                }
            }
            
            $current_query = trim($current_query);
            if(!empty($current_query)) {
                $sql_queries[] = $current_query;
            }
            
            log_message('info', 'Total SQL queries to execute: ' . count($sql_queries));
            
            $executed_queries = 0;
            $failed_queries = 0;
            $skipped_queries = 0;
            
            foreach($sql_queries as $index => $query){
                $query = trim($query);
                if(empty($query)) {
                    continue;
                }
                
                if(preg_match('/^(USE|SET|LOCK|UNLOCK|START|COMMIT|ROLLBACK)/i', $query)) {
                    $skipped_queries++;
                    continue;
                }
                
                if(preg_match('/^--/', $query) || preg_match('/^\/\*.*\*\/$/', $query)) {
                    $skipped_queries++;
                    continue;
                }
                
                try {
                    $result = $this->db->query($query);
                    if($result === false){
                        $db_error = $this->db->error();
                        $error_message = $db_error['message'];
                        
                        if(preg_match('/already exists|doesn\'t exist|duplicate|table.*exists|column.*exists|index.*exists/i', $error_message)) {
                            $skipped_queries++;
                        } else {
                            $failed_queries++;
                            log_message('error', 'Query failed at index ' . $index . ': ' . $error_message);
                        }
                    } else {
                        $executed_queries++;
                    }
                    
                } catch (Exception $e) {
                    $error_message = $e->getMessage();
                    
                    if(preg_match('/already exists|doesn\'t exist|duplicate|table.*exists|column.*exists|index.*exists/i', $error_message)) {
                        $skipped_queries++;
                    } else {
                        $failed_queries++;
                        log_message('error', 'Exception in query at index ' . $index . ': ' . $error_message);
                    }
                }
            }
            
            $this->db->query('SET FOREIGN_KEY_CHECKS = 1');
            $this->db->query('COMMIT');
            $this->db->query('SET AUTOCOMMIT = 1');
            
            log_message('info', 'Restore completed - Executed: ' . $executed_queries . ', Failed: ' . $failed_queries . ', Skipped: ' . $skipped_queries);
            
            if($executed_queries > 0){
                log_message('info', 'Database restore from uploaded file completed successfully');
                return true;
            } else {
                log_message('error', 'No queries were executed successfully from uploaded file');
                return "Failed to restore database: No queries were executed successfully";
            }
            
        } catch (Exception $e) {
            $this->db->query('SET FOREIGN_KEY_CHECKS = 1');
            $this->db->query('COMMIT');
            $this->db->query('SET AUTOCOMMIT = 1');
            log_message('error', 'Restore exception from uploaded file: ' . $e->getMessage());
            return "Failed to restore database: " . $e->getMessage();
        }
    }
    
    public function download_backup(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        $filename = $this->input->post('filename');
        $filepath = 'dbbackup/' . $filename;
        
        if(file_exists($filepath)){
            $this->load->helper('download');
            force_download($filepath, file_get_contents($filepath));
        } else {
            echo "File not found";
        }
    }
    
    public function delete_backup(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        $filename = $this->input->post('filename');
        $filepath = 'dbbackup/' . $filename;
        
        if(file_exists($filepath) && unlink($filepath)){
            echo "success";
        } else {
            echo "Failed to delete file";
        }
    }
    
    public function restore_backup(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        error_reporting(E_ALL & ~E_DEPRECATED & ~E_STRICT);
        ini_set('display_errors', 1);
        
        ini_set('memory_limit', '512M');
        ini_set('max_execution_time', 600);
        ini_set('max_input_time', 600);
        
        try {
            $filename = $this->input->post('filename');
            if(empty($filename)) {
                echo "No filename provided";
                return;
            }
            
            $filepath = 'dbbackup/' . $filename;
            
            log_message('info', 'Restore attempt for file: ' . $filepath);
            
            if(!file_exists($filepath)){
                log_message('error', 'Backup file not found: ' . $filepath);
                echo "Backup file not found";
                return;
            }
            
            $file_size = filesize($filepath);
            log_message('info', 'Backup file size: ' . $file_size . ' bytes');
            
            if($file_size > 200 * 1024 * 1024) {
                echo "Backup file is too large (max 200MB)";
                return;
            }
            
            $backup_content = '';
            $handle = fopen($filepath, 'r');
            if($handle === false) {
                log_message('error', 'Failed to open backup file: ' . $filepath);
                echo "Failed to open backup file";
                return;
            }
            
            while(!feof($handle)) {
                $backup_content .= fread($handle, 8192);
            }
            fclose($handle);
            
            if(empty($backup_content)) {
                log_message('error', 'Backup file is empty: ' . $filepath);
                echo "Backup file is empty";
                return;
            }
            
            log_message('info', 'Backup content size: ' . strlen($backup_content) . ' bytes');
            
            if(pathinfo($filename, PATHINFO_EXTENSION) == 'zip'){
                log_message('info', 'Processing zip file');
                
                if(!class_exists('ZipArchive')) {
                    log_message('error', 'ZipArchive class not available');
                    echo "ZipArchive not available on this server. Please install the zip extension or contact your hosting provider.";
                    return;
                }
                
                $zip = new ZipArchive;
                if ($zip->open($filepath) === TRUE) {
                    $backup_content = $zip->getFromIndex(0);
                    $zip->close();
                    
                    if($backup_content === false){
                        log_message('error', 'Failed to extract SQL from zip file');
                        echo "Failed to extract SQL from zip file";
                        return;
                    }
                    log_message('info', 'Extracted SQL size: ' . strlen($backup_content) . ' bytes');
                } else {
                    log_message('error', 'Failed to open zip file: ' . $filepath);
                    echo "Failed to open backup file";
                    return;
                }
            } else if(pathinfo($filename, PATHINFO_EXTENSION) == 'sql') {
                log_message('info', 'Processing SQL file directly');
            } else {
                log_message('error', 'Unsupported file format: ' . pathinfo($filename, PATHINFO_EXTENSION));
                echo "Unsupported file format. Only .zip and .sql files are supported.";
                return;
            }
            
            $test_result = $this->db->query("SELECT 1 as test");
            if(!$test_result) {
                $db_error = $this->db->error();
                log_message('error', 'Database connection failed: ' . $db_error['message']);
                echo "Database connection failed: " . $db_error['message'];
                return;
            }
            
            $this->db->query('SET FOREIGN_KEY_CHECKS = 0');
            $this->db->query('SET SQL_MODE = ""');
            $this->db->query('SET AUTOCOMMIT = 0');
            
            $sql_queries = array();
            $lines = explode("\n", $backup_content);
            $current_query = '';
            $in_string = false;
            $string_char = '';
            $in_comment = false;
            
            foreach($lines as $line_num => $line) {
                $line = rtrim($line);
                
                if(empty($line)) {
                    continue;
                }
                
                if(strpos($line, '/*') !== false) {
                    $in_comment = true;
                }
                if(strpos($line, '*/') !== false) {
                    $in_comment = false;
                    continue;
                }
                if($in_comment) {
                    continue;
                }
                
                if(strpos($line, '--') === 0) {
                    continue;
                }
                
                for($i = 0; $i < strlen($line); $i++) {
                    $char = $line[$i];
                    
                    if(($char == "'" || $char == '"') && ($i == 0 || $line[$i-1] != '\\')) {
                        if(!$in_string) {
                            $in_string = true;
                            $string_char = $char;
                        } elseif($char == $string_char) {
                            $in_string = false;
                        }
                    }
                    
                    $current_query .= $char;
                    
                    if($char == ';' && !$in_string) {
                        $current_query = trim($current_query);
                        if(!empty($current_query)) {
                            $sql_queries[] = $current_query;
                        }
                        $current_query = '';
                    }
                }
                
                if($line_num < count($lines) - 1) {
                    $current_query .= "\n";
                }
            }
            
            $current_query = trim($current_query);
            if(!empty($current_query)) {
                $sql_queries[] = $current_query;
            }
            
            log_message('info', 'Total SQL queries to execute: ' . count($sql_queries));
            
            $executed_queries = 0;
            $failed_queries = 0;
            $skipped_queries = 0;
            
            foreach($sql_queries as $index => $query){
                $query = trim($query);
                if(empty($query)) {
                    continue;
                }
                
                if(preg_match('/^(USE|SET|LOCK|UNLOCK|START|COMMIT|ROLLBACK)/i', $query)) {
                    $skipped_queries++;
                    continue;
                }
                
                if(preg_match('/^--/', $query) || preg_match('/^\/\*.*\*\/$/', $query)) {
                    $skipped_queries++;
                    continue;
                }
                
                try {
                    $result = $this->db->query($query);
                    if($result === false){
                        $db_error = $this->db->error();
                        $error_message = $db_error['message'];
                        
                        if(preg_match('/already exists|doesn\'t exist|duplicate|table.*exists|column.*exists|index.*exists/i', $error_message)) {
                            $skipped_queries++;
                        } else {
                            $failed_queries++;
                            log_message('error', 'Query failed at index ' . $index . ': ' . $error_message);
                            log_message('error', 'Failed query: ' . substr($query, 0, 200) . '...');
                        }
                    } else {
                        $executed_queries++;
                        
                        if($executed_queries % 100 == 0) {
                            log_message('info', 'Executed ' . $executed_queries . ' queries successfully');
                        }
                    }
                    
                } catch (Exception $e) {
                    $error_message = $e->getMessage();
                    
                    if(preg_match('/already exists|doesn\'t exist|duplicate|table.*exists|column.*exists|index.*exists/i', $error_message)) {
                        $skipped_queries++;
                    } else {
                        $failed_queries++;
                        log_message('error', 'Exception in query at index ' . $index . ': ' . $error_message);
                        log_message('error', 'Failed query: ' . substr($query, 0, 200) . '...');
                    }
                }
            }
            
            $this->db->query('SET FOREIGN_KEY_CHECKS = 1');
            $this->db->query('COMMIT');
            $this->db->query('SET AUTOCOMMIT = 1');
            
            log_message('info', 'Executed queries: ' . $executed_queries . ', Failed: ' . $failed_queries . ', Skipped: ' . $skipped_queries);
            
            if($executed_queries > 0){
                log_message('info', 'Database restore completed successfully');
                $message = "success";
                if($failed_queries > 0 || $skipped_queries > 0) {
                    $message .= "|" . $executed_queries . " queries executed successfully";
                    if($failed_queries > 0) {
                        $message .= ", " . $failed_queries . " queries failed";
                    }
                    if($skipped_queries > 0) {
                        $message .= ", " . $skipped_queries . " queries skipped";
                    }
                }
                echo $message;
            } else {
                log_message('error', 'No queries were executed successfully');
                echo "Failed to restore database: No queries were executed successfully. Check the backup file format.";
            }
            
        } catch (Exception $e) {
            $this->db->query('SET FOREIGN_KEY_CHECKS = 1');
            $this->db->query('COMMIT');
            $this->db->query('SET AUTOCOMMIT = 1');
            log_message('error', 'Restore exception: ' . $e->getMessage());
            echo "Failed to restore database: " . $e->getMessage();
        }
    }
    
    // Analyze backup file content
    public function analyze_backup(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        $filename = $this->input->get('filename');
        if(empty($filename)) {
            echo "No filename provided";
            return;
        }
        
        $filepath = 'dbbackup/' . $filename;
        
        if(!file_exists($filepath)){
            echo "Backup file not found";
            return;
        }
        
        echo "<h2>Backup File Analysis: " . $filename . "</h2>";
        
        echo "<p><strong>File Size:</strong> " . filesize($filepath) . " bytes</p>";
        echo "<p><strong>File Type:</strong> " . pathinfo($filename, PATHINFO_EXTENSION) . "</p>";
        
        $content = file_get_contents($filepath);
        if(empty($content)) {
            echo "<p><strong>Error:</strong> File is empty</p>";
            return;
        }
        
        if(pathinfo($filename, PATHINFO_EXTENSION) == 'zip'){
            if(!class_exists('ZipArchive')) {
                echo "<p><strong>Error:</strong> ZipArchive not available</p>";
                return;
            }
            
            $zip = new ZipArchive;
            if ($zip->open($filepath) === TRUE) {
                $content = $zip->getFromIndex(0);
                $zip->close();
                echo "<p><strong>Extracted SQL Size:</strong> " . strlen($content) . " bytes</p>";
            } else {
                echo "<p><strong>Error:</strong> Failed to open zip file</p>";
                return;
            }
        }
        
        $queries = explode(';', $content);
        $valid_queries = 0;
        $ddl_queries = 0;
        $dml_queries = 0;
        
        echo "<h3>SQL Analysis:</h3>";
        echo "<p><strong>Total SQL statements found:</strong> " . count($queries) . "</p>";
        
        foreach($queries as $index => $query) {
            $query = trim($query);
            if(!empty($query)) {
                $valid_queries++;
                
                if(preg_match('/^(CREATE|DROP|ALTER)/i', $query)) {
                    $ddl_queries++;
                } elseif(preg_match('/^(INSERT|UPDATE|DELETE)/i', $query)) {
                    $dml_queries++;
                }
            }
        }
        
        echo "<p><strong>Valid queries:</strong> " . $valid_queries . "</p>";
        echo "<p><strong>DDL queries (CREATE/DROP/ALTER):</strong> " . $ddl_queries . "</p>";
        echo "<p><strong>DML queries (INSERT/UPDATE/DELETE):</strong> " . $dml_queries . "</p>";
        
        echo "<h3>Sample Queries:</h3>";
        $sample_count = 0;
        foreach($queries as $index => $query) {
            $query = trim($query);
            if(!empty($query) && $sample_count < 5) {
                echo "<p><strong>Query " . ($index + 1) . ":</strong> " . substr($query, 0, 100) . "...</p>";
                $sample_count++;
            }
        }
        
        if($valid_queries == 0) {
            echo "<p><strong style='color: red;'>Warning: No valid SQL queries found in backup file!</strong></p>";
        }
    }
    
    // Diagnostic method to check server capabilities
    public function diagnose(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        echo "<h2>Server Diagnostics</h2>";
        
        echo "<p><strong>PHP Version:</strong> " . phpversion() . "</p>";
        
        echo "<p><strong>Memory Limit:</strong> " . ini_get('memory_limit') . "</p>";
        
        echo "<p><strong>Max Execution Time:</strong> " . ini_get('max_execution_time') . " seconds</p>";
        
        echo "<p><strong>Upload Max Filesize:</strong> " . ini_get('upload_max_filesize') . "</p>";
        echo "<p><strong>Post Max Size:</strong> " . ini_get('post_max_size') . "</p>";
        
        echo "<p><strong>ZipArchive Available:</strong> " . (class_exists('ZipArchive') ? 'Yes' : 'No') . "</p>";
        
        try {
            $result = $this->db->query("SELECT 1 as test");
            if($result) {
                echo "<p><strong>Database Connection:</strong> Success</p>";
                
                $result = $this->db->query("SET FOREIGN_KEY_CHECKS = 0");
                if($result) {
                    echo "<p><strong>Foreign Key Operations:</strong> Success</p>";
                } else {
                    echo "<p><strong>Foreign Key Operations:</strong> Failed</p>";
                }
                $this->db->query("SET FOREIGN_KEY_CHECKS = 1");
                
            } else {
                echo "<p><strong>Database Connection:</strong> Failed</p>";
            }
        } catch (Exception $e) {
            echo "<p><strong>Database Connection:</strong> Error - " . $e->getMessage() . "</p>";
        }
        
        $backup_dir = 'dbbackup/';
        if(is_dir($backup_dir)) {
            echo "<p><strong>Backup Directory:</strong> Exists</p>";
            if(is_writable($backup_dir)) {
                echo "<p><strong>Backup Directory Writable:</strong> Yes</p>";
            } else {
                echo "<p><strong>Backup Directory Writable:</strong> No</p>";
            }
        } else {
            echo "<p><strong>Backup Directory:</strong> Does not exist</p>";
            if(mkdir($backup_dir, 0755, true)) {
                echo "<p><strong>Backup Directory Created:</strong> Success</p>";
            } else {
                echo "<p><strong>Backup Directory Created:</strong> Failed</p>";
            }
        }
        
        echo "<p><strong>Error Reporting:</strong> " . error_reporting() . "</p>";
        echo "<p><strong>Display Errors:</strong> " . (ini_get('display_errors') ? 'On' : 'Off') . "</p>";
        
        log_message('info', 'Diagnostic test completed');
        echo "<p><strong>Log Writing:</strong> Test completed (check application/logs/)</p>";
        
        echo "<h3>Recommendations:</h3>";
        echo "<ul>";
        if(!class_exists('ZipArchive')) {
            echo "<li>Install ZipArchive extension for better backup handling</li>";
        }
        if(ini_get('memory_limit') < '256M') {
            echo "<li>Consider increasing memory_limit to 256M or higher</li>";
        }
        if(ini_get('max_execution_time') < 300) {
            echo "<li>Consider increasing max_execution_time to 300 seconds or higher</li>";
        }
        echo "</ul>";
    }
    
    // Create a test backup with minimal data
    public function create_test_backup(){
        if(demo_app()){
            echo "Restricted in Demo";exit();
        }
        
        $backup_name = 'test_backup_' . date('Y-m-d_H-i-s');
        
        $this->load->dbutil();
        
        $sql_content = "-- Test Backup File\n";
        $sql_content .= "-- Created: " . date('Y-m-d H:i:s') . "\n\n";
        
        $tables = $this->db->list_tables();
        
        foreach($tables as $table) {
            if(strpos($table, 'ci_') === 0 || strpos($table, 'information_schema') === 0) {
                continue;
            }
            
            $sql_content .= "-- Table structure for table `$table`\n";
            $sql_content .= "DROP TABLE IF EXISTS `$table`;\n";
            
            $result = $this->db->query("SHOW CREATE TABLE `$table`");
            if($result && $result->num_rows() > 0) {
                $row = $result->row_array();
                $sql_content .= $row['Create Table'] . ";\n\n";
            }
            
            $result = $this->db->query("SELECT * FROM `$table` LIMIT 10");
            if($result && $result->num_rows() > 0) {
                $sql_content .= "-- Data for table `$table`\n";
                foreach($result->result_array() as $row) {
                    $values = array();
                    foreach($row as $value) {
                        if($value === null) {
                            $values[] = 'NULL';
                        } else {
                            $values[] = "'" . $this->db->escape_str($value) . "'";
                        }
                    }
                    $sql_content .= "INSERT INTO `$table` VALUES (" . implode(', ', $values) . ");\n";
                }
                $sql_content .= "\n";
            }
        }
        
        $this->load->helper('file');
        $filename = 'dbbackup/' . $backup_name . '.sql';
        
        if(write_file($filename, $sql_content)){
            echo "Test backup created successfully: " . $backup_name . ".sql";
        } else {
            echo "Failed to create test backup file";
        }
    }
    
    private function get_backup_files(){
        $backup_dir = 'dbbackup/';
        $files = array();
        
        if(!is_dir($backup_dir)){
            mkdir($backup_dir, 0755, true);
        }
        
        if(is_dir($backup_dir)){
            $file_list = scandir($backup_dir);
            foreach($file_list as $file){
                if($file != '.' && $file != '..' && 
                   (pathinfo($file, PATHINFO_EXTENSION) == 'zip' || 
                    pathinfo($file, PATHINFO_EXTENSION) == 'sql')){
                    $filepath = $backup_dir . $file;
                    if(file_exists($filepath)){
                        $files[] = array(
                            'name' => $file,
                            'size' => $this->format_bytes(filesize($filepath)),
                            'date' => date('Y-m-d H:i:s', filemtime($filepath))
                        );
                    }
                }
            }
        }
        
        usort($files, function($a, $b) {
            return strtotime($b['date']) - strtotime($a['date']);
        });
        
        return $files;
    }
    
    private function format_bytes($bytes, $precision = 2) {
        $units = array('B', 'KB', 'MB', 'GB', 'TB');
        
        for ($i = 0; $bytes > 1024 && $i < count($units) - 1; $i++) {
            $bytes /= 1024;
        }
        
        return round($bytes, $precision) . ' ' . $units[$i];
    }
} 