<?php
/**
 * Database Backup & Export API
 * 
 * Actions:
 * - export-table: Export single table as SQL
 * - export-database: Export entire database as SQL
 * - export-json: Export table data as JSON
 * - export-csv: Export table data as CSV
 */

require_once 'db.php';

define('DB_ADMIN_PASSWORD', 'Zagel@DB2024!');

function verifyAdminPassword($password) {
    return $password === DB_ADMIN_PASSWORD;
}

$action = $_GET['action'] ?? '';
$input = getJsonInput();

// Verify password for all operations
$password = $input['password'] ?? $_GET['password'] ?? '';
if (!verifyAdminPassword($password)) {
    jsonResponse(false, 'كلمة المرور الإدارية غير صحيحة', null, 401);
}

try {
    switch ($action) {
        case 'export-table':
            $table = sanitize($_GET['table'] ?? $input['table'] ?? '');
            $includeData = ($_GET['include_data'] ?? $input['include_data'] ?? 'true') === 'true';
            
            if (empty($table)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول', null, 400);
            }
            
            $output = "-- Export of table: $table\n";
            $output .= "-- Generated at: " . date('Y-m-d H:i:s') . "\n\n";
            
            // Get CREATE TABLE statement
            $stmt = $pdo->prepare("SHOW CREATE TABLE `$table`");
            $stmt->execute();
            $createTable = $stmt->fetch(PDO::FETCH_ASSOC);
            
            $output .= "DROP TABLE IF EXISTS `$table`;\n\n";
            $output .= $createTable['Create Table'] . ";\n\n";
            
            if ($includeData) {
                // Get all data
                $stmt = $pdo->prepare("SELECT * FROM `$table`");
                $stmt->execute();
                $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
                
                if (!empty($rows)) {
                    $columns = array_keys($rows[0]);
                    $output .= "INSERT INTO `$table` (`" . implode("`, `", $columns) . "`) VALUES\n";
                    
                    $values = [];
                    foreach ($rows as $row) {
                        $rowValues = [];
                        foreach ($row as $value) {
                            if ($value === null) {
                                $rowValues[] = 'NULL';
                            } else {
                                $rowValues[] = $pdo->quote($value);
                            }
                        }
                        $values[] = '(' . implode(', ', $rowValues) . ')';
                    }
                    $output .= implode(",\n", $values) . ";\n";
                }
            }
            
            // Return as downloadable or JSON
            if (($_GET['download'] ?? '') === 'true') {
                header('Content-Type: application/sql');
                header("Content-Disposition: attachment; filename=\"{$table}_export.sql\"");
                echo $output;
                exit;
            }
            
            jsonResponse(true, "تم تصدير الجدول $table", [
                'table' => $table,
                'sql' => $output,
                'size_bytes' => strlen($output)
            ]);
            break;
            
        case 'export-database':
            $includeTables = $input['tables'] ?? null; // null = all tables
            $includeData = ($input['include_data'] ?? true);
            
            $output = "-- Full Database Export\n";
            $output .= "-- Generated at: " . date('Y-m-d H:i:s') . "\n";
            $output .= "-- =====================================\n\n";
            
            $output .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
            
            // Get all tables
            $stmt = $pdo->query("SHOW TABLES");
            $tables = [];
            while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
                if ($includeTables === null || in_array($row[0], $includeTables)) {
                    $tables[] = $row[0];
                }
            }
            
            foreach ($tables as $table) {
                $output .= "-- =====================================\n";
                $output .= "-- Table: $table\n";
                $output .= "-- =====================================\n\n";
                
                // Get CREATE TABLE
                $stmt = $pdo->prepare("SHOW CREATE TABLE `$table`");
                $stmt->execute();
                $createTable = $stmt->fetch(PDO::FETCH_ASSOC);
                
                $output .= "DROP TABLE IF EXISTS `$table`;\n\n";
                $output .= $createTable['Create Table'] . ";\n\n";
                
                if ($includeData) {
                    $stmt = $pdo->prepare("SELECT * FROM `$table`");
                    $stmt->execute();
                    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    
                    if (!empty($rows)) {
                        $columns = array_keys($rows[0]);
                        
                        // Batch inserts (1000 rows at a time)
                        $chunks = array_chunk($rows, 1000);
                        
                        foreach ($chunks as $chunk) {
                            $output .= "INSERT INTO `$table` (`" . implode("`, `", $columns) . "`) VALUES\n";
                            
                            $values = [];
                            foreach ($chunk as $row) {
                                $rowValues = [];
                                foreach ($row as $value) {
                                    if ($value === null) {
                                        $rowValues[] = 'NULL';
                                    } else {
                                        $rowValues[] = $pdo->quote($value);
                                    }
                                }
                                $values[] = '(' . implode(', ', $rowValues) . ')';
                            }
                            $output .= implode(",\n", $values) . ";\n\n";
                        }
                    }
                }
            }
            
            $output .= "SET FOREIGN_KEY_CHECKS = 1;\n";
            
            // Return as downloadable or JSON
            if (($_GET['download'] ?? '') === 'true') {
                header('Content-Type: application/sql');
                header('Content-Disposition: attachment; filename="database_export_' . date('Y-m-d_His') . '.sql"');
                echo $output;
                exit;
            }
            
            jsonResponse(true, 'تم تصدير قاعدة البيانات', [
                'tables_count' => count($tables),
                'tables' => $tables,
                'sql' => $output,
                'size_bytes' => strlen($output)
            ]);
            break;
            
        case 'export-json':
            $table = sanitize($_GET['table'] ?? $input['table'] ?? '');
            $limit = intval($_GET['limit'] ?? $input['limit'] ?? 0);
            $offset = intval($_GET['offset'] ?? $input['offset'] ?? 0);
            
            if (empty($table)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول', null, 400);
            }
            
            $sql = "SELECT * FROM `$table`";
            if ($limit > 0) {
                $sql .= " LIMIT $limit OFFSET $offset";
            }
            
            $stmt = $pdo->query($sql);
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Get total count
            $stmt = $pdo->query("SELECT COUNT(*) as total FROM `$table`");
            $total = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
            
            if (($_GET['download'] ?? '') === 'true') {
                header('Content-Type: application/json');
                header("Content-Disposition: attachment; filename=\"{$table}_export.json\"");
                echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
                exit;
            }
            
            jsonResponse(true, "تم تصدير بيانات $table", [
                'table' => $table,
                'data' => $data,
                'count' => count($data),
                'total' => $total
            ]);
            break;
            
        case 'export-csv':
            $table = sanitize($_GET['table'] ?? $input['table'] ?? '');
            
            if (empty($table)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول', null, 400);
            }
            
            $stmt = $pdo->query("SELECT * FROM `$table`");
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            if (empty($data)) {
                jsonResponse(false, 'لا توجد بيانات للتصدير', null, 404);
            }
            
            // Generate CSV
            $output = fopen('php://temp', 'r+');
            
            // Header row
            fputcsv($output, array_keys($data[0]));
            
            // Data rows
            foreach ($data as $row) {
                fputcsv($output, $row);
            }
            
            rewind($output);
            $csv = stream_get_contents($output);
            fclose($output);
            
            if (($_GET['download'] ?? '') === 'true') {
                header('Content-Type: text/csv; charset=utf-8');
                header("Content-Disposition: attachment; filename=\"{$table}_export.csv\"");
                echo "\xEF\xBB\xBF"; // UTF-8 BOM for Excel
                echo $csv;
                exit;
            }
            
            jsonResponse(true, "تم تصدير بيانات $table كـ CSV", [
                'table' => $table,
                'csv' => $csv,
                'rows' => count($data)
            ]);
            break;
            
        default:
            jsonResponse(false, 'Action غير معروف. الـ Actions المتاحة: export-table, export-database, export-json, export-csv', null, 400);
    }
    
} catch (PDOException $e) {
    jsonResponse(false, 'خطأ في قاعدة البيانات: ' . $e->getMessage(), null, 500);
} catch (Exception $e) {
    jsonResponse(false, 'خطأ: ' . $e->getMessage(), null, 500);
}
