<?php
/**
 * ComptaFlow UK by MeeTempo - Backup & Restore Service
 * Version 1.0.0
 * 
 * Export/Import accounting data
 */

namespace ComptaFlowUK\Services;

defined('ABSPATH') || exit;

class BackupService {
    
    /**
     * ComptaFlow table prefix
     */
    private static function getPrefix(): string {
        global $wpdb;
        return $wpdb->prefix . 'cfuk_';
    }
    
    /**
     * Get the list of ComptaFlow tables
     * 
     * @return array List of table names
     */
    public static function getTables(): array {
        global $wpdb;
        $prefix = self::getPrefix();
        
        return $wpdb->get_col(
            "SELECT table_name 
             FROM information_schema.tables 
             WHERE table_schema = DATABASE() 
             AND table_name LIKE '{$prefix}%'
             ORDER BY table_name"
        );
    }
    
    /**
     * Get table statistics
     * 
     * @return array Statistics per table
     */
    public static function getTablesStats(): array {
        global $wpdb;
        $tables = self::getTables();
        $stats = [];
        
        foreach ($tables as $table) {
            $count = $wpdb->get_var("SELECT COUNT(*) FROM `{$table}`");
            $stats[$table] = intval($count);
        }
        
        return $stats;
    }
    
    /**
     * Generate SQL export content
     * 
     * @param bool $include_structure Include table structure
     * @param bool $include_data Include table data
     * @return string SQL content
     */
    public static function generateSQL(bool $include_structure = true, bool $include_data = true): string {
        global $wpdb;
        $tables = self::getTables();
        
        $sql = "-- ========================================\n";
        $sql .= "-- ComptaFlow UK by MeeTempo - Backup\n";
        $sql .= "-- Date : " . gmdate('Y-m-d H:i:s') . "\n";
        $sql .= "-- Version : 1.0.0\n";
        $sql .= "-- Site : " . get_site_url() . "\n";
        $sql .= "-- ========================================\n\n";
        
        $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n";
        $sql .= "SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n";
        $sql .= "SET AUTOCOMMIT = 0;\n";
        $sql .= "START TRANSACTION;\n\n";
        
        foreach ($tables as $table) {
            $sql .= "-- ----------------------------------------\n";
            $sql .= "-- Table: {$table}\n";
            $sql .= "-- ----------------------------------------\n\n";
            
            // Table structure
            if ($include_structure) {
                $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
                
                $create = $wpdb->get_row("SHOW CREATE TABLE `{$table}`", ARRAY_N);
                if ($create && isset($create[1])) {
                    $sql .= $create[1] . ";\n\n";
                }
            }
            
            // Table data
            if ($include_data) {
                $rows = $wpdb->get_results("SELECT * FROM `{$table}`", ARRAY_A);
                
                if (!empty($rows)) {
                    $columns = array_keys($rows[0]);
                    $columns_escaped = array_map(function($col) {
                        return "`{$col}`";
                    }, $columns);
                    
                    $sql .= "INSERT INTO `{$table}` (" . implode(', ', $columns_escaped) . ") VALUES\n";
                    
                    $values_array = [];
                    foreach ($rows as $row) {
                        $values = [];
                        foreach ($row as $value) {
                            if ($value === null) {
                                $values[] = 'NULL';
                            } else {
                                $values[] = "'" . $wpdb->_real_escape($value) . "'";
                            }
                        }
                        $values_array[] = '(' . implode(', ', $values) . ')';
                    }
                    
                    $sql .= implode(",\n", $values_array) . ";\n\n";
                } else {
                    $sql .= "-- Empty table\n\n";
                }
            }
        }
        
        // WordPress options related to ComptaFlow
        $sql .= "-- ----------------------------------------\n";
        $sql .= "-- Options WordPress ComptaFlow\n";
        $sql .= "-- ----------------------------------------\n\n";
        
        $options = [
            'comptaflow_uk_company_settings',
            'comptaflow_uk_db_version',
            'comptaflow_uk_last_migration',
        ];
        
        foreach ($options as $option_name) {
            $value = get_option($option_name);
            if ($value !== false) {
                $value_serialized = maybe_serialize($value);
                $value_escaped = $wpdb->_real_escape($value_serialized);
                $sql .= "DELETE FROM `{$wpdb->options}` WHERE `option_name` = '{$option_name}';\n";
                $sql .= "INSERT INTO `{$wpdb->options}` (`option_name`, `option_value`, `autoload`) VALUES ('{$option_name}', '{$value_escaped}', 'yes');\n";
            }
        }
        
        $sql .= "\nCOMMIT;\n";
        $sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";
        
        $sql .= "\n-- ========================================\n";
        $sql .= "-- End of backup\n";
        $sql .= "-- ========================================\n";
        
        return $sql;
    }
    
    /**
     * Download the SQL backup
     * 
     * @return void
     */
    public static function download(): void {
        // Check permissions
        if (!current_user_can('manage_options')) {
            wp_die('Insufficient permissions.');
        }
        
        // Verify nonce
        $nonce = isset($_GET['_wpnonce']) ? sanitize_text_field(wp_unslash($_GET['_wpnonce'])) : '';
        if (empty($nonce) || !wp_verify_nonce($nonce, 'comptaflow_uk_backup_download')) {
            wp_die('Link expired. Please try again.');
        }
        
        // Generate SQL
        $sql = self::generateSQL(true, true);
        
        // Filename
        $filename = 'comptaflow_backup_' . gmdate('Y-m-d_H-i-s') . '.sql';
        
        // Headers for download
        header('Content-Type: application/sql');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . strlen($sql));
        header('Cache-Control: private, max-age=0, must-revalidate');
        header('Pragma: public');
        
        // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- SQL output, not HTML
        echo $sql;
        exit;
    }
    
    /**
     * Restore from an SQL file
     * 
     * @param string $file_path Path to the uploaded SQL file
     * @return array Result ['success' => bool, 'message' => string, 'queries' => int]
     */
    public static function restore(string $file_path): array {
        global $wpdb;
        
        // Check that the file exists
        if (!file_exists($file_path)) {
            return [
                'success' => false,
                'message' => 'File not found.',
                'queries' => 0,
            ];
        }
        
        // Read content using WP_Filesystem
        global $wp_filesystem;
        if (empty($wp_filesystem)) {
            require_once ABSPATH . 'wp-admin/includes/file.php';
            WP_Filesystem();
        }
        $sql_content = $wp_filesystem->get_contents($file_path);
        
        if (empty($sql_content)) {
            return [
                'success' => false,
                'message' => 'File is empty or unreadable.',
                'queries' => 0,
            ];
        }
        
        // Verify this is a ComptaFlow backup
        if (strpos($sql_content, 'ComptaFlow') === false) {
            return [
                'success' => false,
                'message' => 'This file does not appear to be a valid ComptaFlow backup.',
                'queries' => 0,
            ];
        }
        
        // Disable foreign key constraints
        $wpdb->query("SET FOREIGN_KEY_CHECKS = 0");
        
        // Split queries
        $queries = self::splitSQLQueries($sql_content);
        
        $executed = 0;
        $errors = [];
        
        foreach ($queries as $query) {
            $query = trim($query);
            
            // Remove comments at query start
            $query = preg_replace('/^(\s*--[^\n]*\n)+/s', '', $query);
            $query = trim($query);
            
            // Skip empty lines
            if (empty($query)) {
                continue;
            }
            
            // Skip START/COMMIT/AUTOCOMMIT commands (but keep SET FOREIGN_KEY_CHECKS)
            if (preg_match('/^(START|COMMIT|AUTOCOMMIT)/i', $query)) {
                continue;
            }
            
            // Skip SET commands except FOREIGN_KEY_CHECKS
            if (preg_match('/^SET/i', $query) && stripos($query, 'FOREIGN_KEY_CHECKS') === false) {
                continue;
            }
            
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL restore from trusted backup file
            $result = $wpdb->query($query);
            
            if ($result === false) {
                $errors[] = $wpdb->last_error . ' (Query: ' . substr($query, 0, 100) . '...)';
            } else {
                $executed++;
            }
        }
        
        // Re-enable constraints
        $wpdb->query("SET FOREIGN_KEY_CHECKS = 1");
        
        if (!empty($errors)) {
            return [
                'success' => false,
                'message' => 'Partial restoration. Errors: ' . implode(' | ', array_slice($errors, 0, 3)),
                'queries' => $executed,
            ];
        }
        
        return [
            'success' => true,
            'message' => 'Restoration successful!',
            'queries' => $executed,
        ];
    }
    
    /**
     * Split SQL content into individual queries
     * 
     * @param string $sql SQL content
     * @return array List of queries
     */
    private static function splitSQLQueries(string $sql): array {
        // Remove multi-line comments
        $sql = preg_replace('/\/\*.*?\*\//s', '', $sql);
        
        // Split by semicolon (handling values containing ;)
        $queries = [];
        $current_query = '';
        $in_string = false;
        $string_char = '';
        
        $length = strlen($sql);
        for ($i = 0; $i < $length; $i++) {
            $char = $sql[$i];
            
            // String handling
            if (($char === "'" || $char === '"') && ($i === 0 || $sql[$i-1] !== '\\')) {
                if (!$in_string) {
                    $in_string = true;
                    $string_char = $char;
                } elseif ($char === $string_char) {
                    $in_string = false;
                }
            }
            
            // Split on ;
            if ($char === ';' && !$in_string) {
                $current_query = trim($current_query);
                if (!empty($current_query)) {
                    $queries[] = $current_query;
                }
                $current_query = '';
            } else {
                $current_query .= $char;
            }
        }
        
        // Last query if no trailing ;
        $current_query = trim($current_query);
        if (!empty($current_query)) {
            $queries[] = $current_query;
        }
        
        return $queries;
    }
    
    /**
     * Get the download URL
     * 
     * @return string URL
     */
    public static function getDownloadUrl(): string {
        return admin_url('admin-post.php?' . http_build_query([
            'action' => 'comptaflow_uk_backup_download',
            '_wpnonce' => wp_create_nonce('comptaflow_uk_backup_download'),
        ]));
    }
    
    /**
     * Calculate estimated backup size
     * 
     * @return int Size in bytes
     */
    public static function getEstimatedSize(): int {
        global $wpdb;
        $tables = self::getTables();
        $total_size = 0;
        
        foreach ($tables as $table) {
            $size = $wpdb->get_var("
                SELECT data_length + index_length 
                FROM information_schema.tables 
                WHERE table_schema = DATABASE() 
                AND table_name = '{$table}'
            ");
            $total_size += intval($size);
        }
        
        return $total_size;
    }
    
    /**
     * Format byte size to human-readable format
     * 
     * @param int $bytes Size in bytes
     * @return string Formatted size
     */
    public static function formatSize(int $bytes): string {
        if ($bytes < 1024) {
            return $bytes . ' bytes';
        } elseif ($bytes < 1048576) {
            return round($bytes / 1024, 2) . ' KB';
        } else {
            return round($bytes / 1048576, 2) . ' MB';
        }
    }
}