<?php
/**
 * Database Manager API - Full Control
 * ⚠️ WARNING: This API provides full database access. Use with caution!
 * 
 * Actions:
 * - show-tables: List all tables
 * - describe: Show table structure
 * - show-indexes: Show table indexes
 * - table-stats: Get table statistics
 * - show-relations: Show foreign key relations
 * - db-size: Get database size
 * - create-table: Create new table
 * - drop-table: Drop table
 * - alter-table: Modify table structure
 * - create-index: Create index
 * - drop-index: Drop index
 * - execute-sql: Execute raw SQL
 * - import-sql: Import SQL content
 * - bulk-insert: Bulk insert data
 * - truncate-table: Empty table
 */

require_once 'db.php';

// Admin password for dangerous operations
define('DB_ADMIN_PASSWORD', 'Zagel@DB2024!'); // ⚠️ CHANGE THIS IN PRODUCTION!

// Audit log function
function logAudit($action, $details, $success = true) {
    global $pdo;
    
    try {
        // Create audit table if not exists
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS db_audit_log (
                id INT AUTO_INCREMENT PRIMARY KEY,
                action VARCHAR(100) NOT NULL,
                details TEXT,
                ip_address VARCHAR(45),
                user_agent TEXT,
                success TINYINT(1) DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        
        $stmt = $pdo->prepare("
            INSERT INTO db_audit_log (action, details, ip_address, user_agent, success)
            VALUES (?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            $action,
            is_string($details) ? $details : json_encode($details, JSON_UNESCAPED_UNICODE),
            $_SERVER['REMOTE_ADDR'] ?? 'unknown',
            $_SERVER['HTTP_USER_AGENT'] ?? 'unknown',
            $success ? 1 : 0
        ]);
    } catch (Exception $e) {
        // Silently fail audit logging
    }
}

// Verify admin password for dangerous operations
function verifyAdminPassword($password) {
    return $password === DB_ADMIN_PASSWORD;
}

// Check if operation is dangerous
function isDangerousOperation($action) {
    $dangerous = [
        'execute-sql', 'import-sql', 'drop-table', 'truncate-table', 'alter-table', 'create-table', 'drop-index',
        'rename-table', 'copy-table', 'drop-foreign-key', 'create-view', 'drop-view', 'global-search', 'full-backup'
    ];
    return in_array($action, $dangerous);
}

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

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

try {
    switch ($action) {
        // ========================================
        // PASSWORD VERIFICATION
        // ========================================
        
        case 'verify-password':
            $password = $input['password'] ?? '';
            if (verifyAdminPassword($password)) {
                logAudit('verify-password', 'Successful login');
                jsonResponse(true, 'كلمة المرور صحيحة');
            } else {
                logAudit('verify-password', 'Failed login attempt', false);
                jsonResponse(false, 'كلمة المرور الإدارية غير صحيحة', null, 401);
            }
            break;
        
        // ========================================
        // INFORMATION OPERATIONS
        // ========================================
        
        case 'show-tables':
            $stmt = $pdo->query("SHOW TABLES");
            $tables = [];
            while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
                $tables[] = $row[0];
            }
            logAudit('show-tables', 'Listed ' . count($tables) . ' tables');
            jsonResponse(true, 'تم جلب قائمة الجداول', ['tables' => $tables, 'count' => count($tables)]);
            break;
            
        case 'describe':
            $table = sanitize($_GET['table'] ?? '');
            if (empty($table)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول', null, 400);
            }
            
            // Get columns
            $stmt = $pdo->prepare("DESCRIBE `$table`");
            $stmt->execute();
            $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Get CREATE TABLE statement
            $stmt = $pdo->prepare("SHOW CREATE TABLE `$table`");
            $stmt->execute();
            $createTable = $stmt->fetch(PDO::FETCH_ASSOC);
            
            logAudit('describe', "Described table: $table");
            jsonResponse(true, "بنية الجدول $table", [
                'table' => $table,
                'columns' => $columns,
                'create_statement' => $createTable['Create Table'] ?? ''
            ]);
            break;
            
        case 'show-indexes':
            $table = sanitize($_GET['table'] ?? '');
            if (empty($table)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول', null, 400);
            }
            
            $stmt = $pdo->prepare("SHOW INDEX FROM `$table`");
            $stmt->execute();
            $indexes = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            logAudit('show-indexes', "Showed indexes for: $table");
            jsonResponse(true, "فهارس الجدول $table", ['indexes' => $indexes]);
            break;
            
        case 'table-stats':
            $table = sanitize($_GET['table'] ?? '');
            if (empty($table)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول', null, 400);
            }
            
            // Row count
            $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM `$table`");
            $stmt->execute();
            $rowCount = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
            
            // Table size
            $stmt = $pdo->prepare("
                SELECT 
                    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
                    ROUND((data_length / 1024 / 1024), 2) AS data_mb,
                    ROUND((index_length / 1024 / 1024), 2) AS index_mb,
                    table_rows,
                    auto_increment,
                    create_time,
                    update_time
                FROM information_schema.TABLES 
                WHERE table_schema = DATABASE()
                AND table_name = ?
            ");
            $stmt->execute([$table]);
            $stats = $stmt->fetch(PDO::FETCH_ASSOC);
            
            logAudit('table-stats', "Stats for: $table");
            jsonResponse(true, "إحصائيات الجدول $table", [
                'table' => $table,
                'row_count' => $rowCount,
                'size_mb' => $stats['size_mb'] ?? 0,
                'data_mb' => $stats['data_mb'] ?? 0,
                'index_mb' => $stats['index_mb'] ?? 0,
                'auto_increment' => $stats['auto_increment'],
                'created_at' => $stats['create_time'],
                'updated_at' => $stats['update_time']
            ]);
            break;
            
        case 'show-relations':
            $table = sanitize($_GET['table'] ?? '');
            
            $sql = "
                SELECT 
                    TABLE_NAME as 'table',
                    COLUMN_NAME as 'column',
                    REFERENCED_TABLE_NAME as 'referenced_table',
                    REFERENCED_COLUMN_NAME as 'referenced_column',
                    CONSTRAINT_NAME as 'constraint_name'
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE()
                AND REFERENCED_TABLE_NAME IS NOT NULL
            ";
            
            if (!empty($table)) {
                $sql .= " AND (TABLE_NAME = ? OR REFERENCED_TABLE_NAME = ?)";
                $stmt = $pdo->prepare($sql);
                $stmt->execute([$table, $table]);
            } else {
                $stmt = $pdo->query($sql);
            }
            
            $relations = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            logAudit('show-relations', "Relations for: " . ($table ?: 'all tables'));
            jsonResponse(true, 'العلاقات بين الجداول', ['relations' => $relations]);
            break;
            
        case 'db-size':
            $stmt = $pdo->query("
                SELECT 
                    table_schema AS 'database',
                    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb,
                    COUNT(*) as table_count
                FROM information_schema.TABLES 
                WHERE table_schema = DATABASE()
                GROUP BY table_schema
            ");
            $dbInfo = $stmt->fetch(PDO::FETCH_ASSOC);
            
            // Get table sizes
            $stmt = $pdo->query("
                SELECT 
                    table_name,
                    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
                    table_rows
                FROM information_schema.TABLES 
                WHERE table_schema = DATABASE()
                ORDER BY (data_length + index_length) DESC
            ");
            $tables = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            logAudit('db-size', 'Database size requested');
            jsonResponse(true, 'حجم قاعدة البيانات', [
                'database' => $dbInfo['database'],
                'total_size_mb' => $dbInfo['size_mb'],
                'table_count' => $dbInfo['table_count'],
                'tables' => $tables
            ]);
            break;
            
        // ========================================
        // DDL OPERATIONS (Require Password)
        // ========================================
        
        case 'create-table':
            $tableName = sanitize($input['table_name'] ?? '');
            $columns = $input['columns'] ?? [];
            
            if (empty($tableName) || empty($columns)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول والأعمدة', null, 400);
            }
            
            $columnDefs = [];
            $primaryKey = null;
            
            foreach ($columns as $col) {
                $def = "`{$col['name']}` {$col['type']}";
                if (!empty($col['not_null'])) $def .= " NOT NULL";
                if (isset($col['default'])) $def .= " DEFAULT " . ($col['default'] === 'NULL' ? 'NULL' : "'{$col['default']}'");
                if (!empty($col['auto_increment'])) $def .= " AUTO_INCREMENT";
                if (!empty($col['primary'])) $primaryKey = $col['name'];
                $columnDefs[] = $def;
            }
            
            if ($primaryKey) {
                $columnDefs[] = "PRIMARY KEY (`$primaryKey`)";
            }
            
            $sql = "CREATE TABLE `$tableName` (\n" . implode(",\n", $columnDefs) . "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            
            $pdo->exec($sql);
            
            logAudit('create-table', ['table' => $tableName, 'sql' => $sql]);
            jsonResponse(true, "تم إنشاء الجدول $tableName بنجاح", ['sql' => $sql]);
            break;
            
        case 'drop-table':
            $table = sanitize($input['table'] ?? '');
            $confirm = $input['confirm'] ?? false;
            
            if (empty($table)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول', null, 400);
            }
            
            if (!$confirm) {
                jsonResponse(false, 'يرجى تأكيد حذف الجدول بإرسال confirm: true', null, 400);
            }
            
            $pdo->exec("DROP TABLE `$table`");
            
            logAudit('drop-table', "Dropped table: $table");
            jsonResponse(true, "تم حذف الجدول $table بنجاح");
            break;
            
        case 'alter-table':
            $table = sanitize($input['table'] ?? '');
            $operation = $input['operation'] ?? '';
            
            if (empty($table) || empty($operation)) {
                jsonResponse(false, 'يرجى تحديد الجدول والعملية', null, 400);
            }
            
            $sql = "ALTER TABLE `$table` ";
            
            switch ($operation) {
                case 'add-column':
                    $column = $input['column'] ?? '';
                    $type = $input['type'] ?? '';
                    $after = $input['after'] ?? '';
                    
                    $sql .= "ADD COLUMN `$column` $type";
                    if (!empty($input['not_null'])) $sql .= " NOT NULL";
                    if (isset($input['default'])) $sql .= " DEFAULT '{$input['default']}'";
                    if (!empty($after)) $sql .= " AFTER `$after`";
                    break;
                    
                case 'drop-column':
                    $column = $input['column'] ?? '';
                    $sql .= "DROP COLUMN `$column`";
                    break;
                    
                case 'modify-column':
                    $column = $input['column'] ?? '';
                    $type = $input['type'] ?? '';
                    $sql .= "MODIFY COLUMN `$column` $type";
                    if (!empty($input['not_null'])) $sql .= " NOT NULL";
                    if (isset($input['default'])) $sql .= " DEFAULT '{$input['default']}'";
                    break;
                    
                case 'rename-column':
                    $oldName = $input['old_name'] ?? '';
                    $newName = $input['new_name'] ?? '';
                    $type = $input['type'] ?? '';
                    $sql .= "CHANGE COLUMN `$oldName` `$newName` $type";
                    break;
                    
                case 'add-index':
                    $indexName = $input['index_name'] ?? '';
                    $columns = $input['columns'] ?? [];
                    $unique = !empty($input['unique']);
                    $sql .= ($unique ? "ADD UNIQUE INDEX" : "ADD INDEX") . " `$indexName` (`" . implode("`, `", $columns) . "`)";
                    break;
                    
                case 'add-foreign-key':
                    $fkName = $input['fk_name'] ?? '';
                    $column = $input['column'] ?? '';
                    $refTable = $input['reference_table'] ?? '';
                    $refColumn = $input['reference_column'] ?? '';
                    $onDelete = $input['on_delete'] ?? 'CASCADE';
                    $onUpdate = $input['on_update'] ?? 'CASCADE';
                    $sql .= "ADD CONSTRAINT `$fkName` FOREIGN KEY (`$column`) REFERENCES `$refTable`(`$refColumn`) ON DELETE $onDelete ON UPDATE $onUpdate";
                    break;
                    
                default:
                    jsonResponse(false, 'عملية غير معروفة', null, 400);
            }
            
            $pdo->exec($sql);
            
            logAudit('alter-table', ['table' => $table, 'operation' => $operation, 'sql' => $sql]);
            jsonResponse(true, "تم تعديل الجدول $table بنجاح", ['sql' => $sql]);
            break;
            
        case 'create-index':
            $table = sanitize($input['table'] ?? '');
            $indexName = sanitize($input['index_name'] ?? '');
            $columns = $input['columns'] ?? [];
            $unique = !empty($input['unique']);
            
            if (empty($table) || empty($indexName) || empty($columns)) {
                jsonResponse(false, 'يرجى تحديد الجدول واسم الفهرس والأعمدة', null, 400);
            }
            
            $sql = "CREATE " . ($unique ? "UNIQUE " : "") . "INDEX `$indexName` ON `$table` (`" . implode("`, `", $columns) . "`)";
            $pdo->exec($sql);
            
            logAudit('create-index', ['table' => $table, 'index' => $indexName, 'sql' => $sql]);
            jsonResponse(true, "تم إنشاء الفهرس $indexName بنجاح", ['sql' => $sql]);
            break;
            
        case 'drop-index':
            $table = sanitize($input['table'] ?? '');
            $indexName = sanitize($input['index_name'] ?? '');
            
            if (empty($table) || empty($indexName)) {
                jsonResponse(false, 'يرجى تحديد الجدول واسم الفهرس', null, 400);
            }
            
            $sql = "DROP INDEX `$indexName` ON `$table`";
            $pdo->exec($sql);
            
            logAudit('drop-index', ['table' => $table, 'index' => $indexName]);
            jsonResponse(true, "تم حذف الفهرس $indexName بنجاح");
            break;
            
        case 'truncate-table':
            $table = sanitize($input['table'] ?? '');
            $confirm = $input['confirm'] ?? false;
            
            if (empty($table)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول', null, 400);
            }
            
            if (!$confirm) {
                jsonResponse(false, 'يرجى تأكيد إفراغ الجدول بإرسال confirm: true', null, 400);
            }
            
            $pdo->exec("SET FOREIGN_KEY_CHECKS = 0");
            $pdo->exec("TRUNCATE TABLE `$table`");
            $pdo->exec("SET FOREIGN_KEY_CHECKS = 1");
            
            logAudit('truncate-table', "Truncated table: $table");
            jsonResponse(true, "تم إفراغ الجدول $table بنجاح");
            break;
            
        // ========================================
        // RAW SQL OPERATIONS (Require Password)
        // ========================================
        
        case 'execute-sql':
            $sql = $input['sql'] ?? '';
            
            if (empty($sql)) {
                jsonResponse(false, 'يرجى تحديد استعلام SQL', null, 400);
            }
            
            // Detect query type
            $sqlTrimmed = strtoupper(trim($sql));
            $isSelect = strpos($sqlTrimmed, 'SELECT') === 0;
            $isShow = strpos($sqlTrimmed, 'SHOW') === 0;
            $isDescribe = strpos($sqlTrimmed, 'DESCRIBE') === 0 || strpos($sqlTrimmed, 'DESC ') === 0;
            
            if ($isSelect || $isShow || $isDescribe) {
                $stmt = $pdo->query($sql);
                $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
                logAudit('execute-sql', ['type' => 'SELECT', 'sql' => substr($sql, 0, 500)]);
                jsonResponse(true, 'تم تنفيذ الاستعلام', [
                    'type' => 'query',
                    'rows' => $results,
                    'count' => count($results)
                ]);
            } else {
                $affected = $pdo->exec($sql);
                logAudit('execute-sql', ['type' => 'EXECUTE', 'sql' => substr($sql, 0, 500), 'affected' => $affected]);
                jsonResponse(true, 'تم تنفيذ الاستعلام', [
                    'type' => 'execute',
                    'affected_rows' => $affected
                ]);
            }
            break;
            
        case 'import-sql':
            $sqlContent = $input['sql_content'] ?? '';
            
            if (empty($sqlContent)) {
                jsonResponse(false, 'يرجى تحديد محتوى SQL', null, 400);
            }
            
            // Split by semicolons and execute each statement
            $statements = array_filter(array_map('trim', explode(';', $sqlContent)));
            $executed = 0;
            $errors = [];
            
            $pdo->beginTransaction();
            
            try {
                foreach ($statements as $stmt) {
                    if (!empty($stmt)) {
                        try {
                            $pdo->exec($stmt);
                            $executed++;
                        } catch (Exception $e) {
                            $errors[] = [
                                'statement' => substr($stmt, 0, 100) . '...',
                                'error' => $e->getMessage()
                            ];
                        }
                    }
                }
                $pdo->commit();
                
                logAudit('import-sql', ['executed' => $executed, 'errors' => count($errors)]);
                jsonResponse(true, "تم تنفيذ $executed استعلام", [
                    'executed' => $executed,
                    'total' => count($statements),
                    'errors' => $errors
                ]);
            } catch (Exception $e) {
                $pdo->rollBack();
                throw $e;
            }
            break;
            
        case 'bulk-insert':
            $table = sanitize($input['table'] ?? '');
            $data = $input['data'] ?? [];
            
            if (empty($table) || empty($data)) {
                jsonResponse(false, 'يرجى تحديد الجدول والبيانات', null, 400);
            }
            
            $columns = array_keys($data[0]);
            $placeholders = '(' . implode(', ', array_fill(0, count($columns), '?')) . ')';
            $sql = "INSERT INTO `$table` (`" . implode('`, `', $columns) . "`) VALUES $placeholders";
            
            $stmt = $pdo->prepare($sql);
            $inserted = 0;
            
            $pdo->beginTransaction();
            
            try {
                foreach ($data as $row) {
                    $values = array_values($row);
                    $stmt->execute($values);
                    $inserted++;
                }
                $pdo->commit();
                
                logAudit('bulk-insert', ['table' => $table, 'inserted' => $inserted]);
                jsonResponse(true, "تم إدخال $inserted صف بنجاح", ['inserted' => $inserted]);
            } catch (Exception $e) {
                $pdo->rollBack();
                throw $e;
            }
            break;
            
        // ========================================
        // AUDIT LOG
        // ========================================
        
        case 'audit-log':
            $limit = intval($_GET['limit'] ?? 50);
            $offset = intval($_GET['offset'] ?? 0);
            
            $stmt = $pdo->prepare("
                SELECT * FROM db_audit_log 
                ORDER BY created_at DESC 
                LIMIT ? OFFSET ?
            ");
            $stmt->execute([$limit, $offset]);
            $logs = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            $stmt = $pdo->query("SELECT COUNT(*) as total FROM db_audit_log");
            $total = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
            
            jsonResponse(true, 'سجل العمليات', [
                'logs' => $logs,
                'total' => $total,
                'limit' => $limit,
                'offset' => $offset
            ]);
            break;
            
        // ========================================
        // NEW: ADVANCED TABLE OPERATIONS
        // ========================================
        
        case 'rename-table':
            $oldName = sanitize($input['old_name'] ?? '');
            $newName = sanitize($input['new_name'] ?? '');
            
            if (empty($oldName) || empty($newName)) {
                jsonResponse(false, 'يرجى تحديد اسم الجدول القديم والجديد', null, 400);
            }
            
            $sql = "RENAME TABLE `$oldName` TO `$newName`";
            $pdo->exec($sql);
            
            logAudit('rename-table', ['from' => $oldName, 'to' => $newName]);
            jsonResponse(true, "تم تغيير اسم الجدول من $oldName إلى $newName", ['sql' => $sql]);
            break;
            
        case 'copy-table':
            $source = sanitize($input['source'] ?? '');
            $destination = sanitize($input['destination'] ?? '');
            $withData = !empty($input['with_data']);
            
            if (empty($source) || empty($destination)) {
                jsonResponse(false, 'يرجى تحديد الجدول المصدر والوجهة', null, 400);
            }
            
            // Create table structure
            $pdo->exec("CREATE TABLE `$destination` LIKE `$source`");
            
            $rowsCopied = 0;
            if ($withData) {
                $rowsCopied = $pdo->exec("INSERT INTO `$destination` SELECT * FROM `$source`");
            }
            
            logAudit('copy-table', ['source' => $source, 'destination' => $destination, 'with_data' => $withData, 'rows' => $rowsCopied]);
            jsonResponse(true, "تم نسخ الجدول $source إلى $destination" . ($withData ? " مع $rowsCopied صف" : " (البنية فقط)"), [
                'source' => $source,
                'destination' => $destination,
                'rows_copied' => $rowsCopied
            ]);
            break;
            
        case 'drop-foreign-key':
            $table = sanitize($input['table'] ?? '');
            $constraintName = sanitize($input['constraint_name'] ?? '');
            
            if (empty($table) || empty($constraintName)) {
                jsonResponse(false, 'يرجى تحديد الجدول واسم القيد', null, 400);
            }
            
            $sql = "ALTER TABLE `$table` DROP FOREIGN KEY `$constraintName`";
            $pdo->exec($sql);
            
            logAudit('drop-foreign-key', ['table' => $table, 'constraint' => $constraintName]);
            jsonResponse(true, "تم حذف المفتاح الأجنبي $constraintName من $table", ['sql' => $sql]);
            break;
            
        // ========================================
        // NEW: VIEW OPERATIONS
        // ========================================
        
        case 'show-views':
            $stmt = $pdo->query("SHOW FULL TABLES WHERE Table_type = 'VIEW'");
            $views = [];
            while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
                $views[] = $row[0];
            }
            
            logAudit('show-views', 'Listed ' . count($views) . ' views');
            jsonResponse(true, 'قائمة العروض', ['views' => $views, 'count' => count($views)]);
            break;
            
        case 'create-view':
            $viewName = sanitize($input['view_name'] ?? '');
            $selectQuery = $input['select_query'] ?? '';
            
            if (empty($viewName) || empty($selectQuery)) {
                jsonResponse(false, 'يرجى تحديد اسم العرض واستعلام SELECT', null, 400);
            }
            
            $sql = "CREATE VIEW `$viewName` AS $selectQuery";
            $pdo->exec($sql);
            
            logAudit('create-view', ['view' => $viewName, 'sql' => $sql]);
            jsonResponse(true, "تم إنشاء العرض $viewName بنجاح", ['sql' => $sql]);
            break;
            
        case 'drop-view':
            $viewName = sanitize($input['view_name'] ?? '');
            $confirm = $input['confirm'] ?? false;
            
            if (empty($viewName)) {
                jsonResponse(false, 'يرجى تحديد اسم العرض', null, 400);
            }
            
            if (!$confirm) {
                jsonResponse(false, 'يرجى تأكيد حذف العرض بإرسال confirm: true', null, 400);
            }
            
            $pdo->exec("DROP VIEW `$viewName`");
            
            logAudit('drop-view', "Dropped view: $viewName");
            jsonResponse(true, "تم حذف العرض $viewName بنجاح");
            break;
            
        // ========================================
        // NEW: GLOBAL SEARCH
        // ========================================
        
        case 'global-search':
            $searchTerm = $input['term'] ?? '';
            $searchTables = $input['tables'] ?? [];
            $limit = intval($input['limit'] ?? 10);
            
            if (empty($searchTerm)) {
                jsonResponse(false, 'يرجى تحديد مصطلح البحث', null, 400);
            }
            
            // If no tables specified, search all tables
            if (empty($searchTables)) {
                $stmt = $pdo->query("SHOW TABLES");
                while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
                    $searchTables[] = $row[0];
                }
            }
            
            $results = [];
            foreach ($searchTables as $table) {
                try {
                    // Get columns for this table
                    $stmt = $pdo->prepare("DESCRIBE `$table`");
                    $stmt->execute();
                    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    
                    // Build search conditions for text columns
                    $conditions = [];
                    foreach ($columns as $col) {
                        $type = strtolower($col['Type']);
                        if (strpos($type, 'char') !== false || strpos($type, 'text') !== false || strpos($type, 'varchar') !== false) {
                            $conditions[] = "`{$col['Field']}` LIKE ?";
                        }
                    }
                    
                    if (!empty($conditions)) {
                        $sql = "SELECT * FROM `$table` WHERE " . implode(' OR ', $conditions) . " LIMIT $limit";
                        $stmt = $pdo->prepare($sql);
                        $params = array_fill(0, count($conditions), "%$searchTerm%");
                        $stmt->execute($params);
                        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
                        
                        if (!empty($rows)) {
                            $results[$table] = $rows;
                        }
                    }
                } catch (Exception $e) {
                    // Skip tables that fail
                }
            }
            
            logAudit('global-search', ['term' => $searchTerm, 'tables' => count($searchTables), 'results' => count($results)]);
            jsonResponse(true, 'نتائج البحث', [
                'term' => $searchTerm,
                'results' => $results,
                'tables_searched' => count($searchTables)
            ]);
            break;
            
        // ========================================
        // NEW: BACKUP OPERATIONS
        // ========================================
        
        case 'full-backup':
            $tables = [];
            $stmt = $pdo->query("SHOW TABLES");
            while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
                $tables[] = $row[0];
            }
            
            $backup = "-- Database Backup\n-- Generated: " . date('Y-m-d H:i:s') . "\n\n";
            $backup .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
            
            foreach ($tables as $table) {
                // Get CREATE TABLE
                $stmt = $pdo->prepare("SHOW CREATE TABLE `$table`");
                $stmt->execute();
                $createTable = $stmt->fetch(PDO::FETCH_ASSOC);
                
                $backup .= "-- Table: $table\n";
                $backup .= "DROP TABLE IF EXISTS `$table`;\n";
                $backup .= $createTable['Create Table'] . ";\n\n";
                
                // Get data
                $stmt = $pdo->query("SELECT * FROM `$table`");
                $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
                
                if (!empty($rows)) {
                    $columns = array_keys($rows[0]);
                    foreach ($rows as $row) {
                        $values = array_map(function($val) use ($pdo) {
                            if ($val === null) return 'NULL';
                            return $pdo->quote($val);
                        }, array_values($row));
                        $backup .= "INSERT INTO `$table` (`" . implode("`, `", $columns) . "`) VALUES (" . implode(", ", $values) . ");\n";
                    }
                    $backup .= "\n";
                }
            }
            
            $backup .= "SET FOREIGN_KEY_CHECKS = 1;\n";
            
            logAudit('full-backup', ['tables' => count($tables)]);
            jsonResponse(true, 'تم إنشاء النسخة الاحتياطية', [
                'sql' => $backup,
                'tables' => count($tables),
                'size_bytes' => strlen($backup)
            ]);
            break;
            
        case 'fix-upload-urls':
            // تصحيح روابط الملفات المرفوعة في جميع الجداول
            $oldPath = $input['old_path'] ?? '/faw/uploads/';
            $newPath = $input['new_path'] ?? '/faw/uploads/';
            
            $tables = [
                ['table' => 'expenses', 'field' => 'receipt_url'],
                ['table' => 'products', 'field' => 'image_url'],
                ['table' => 'sellers', 'field' => 'seller_logo_url'],
                ['table' => 'sellers', 'field' => 'fawtra_logo_url'],
                ['table' => 'invoices', 'field' => 'pdf_file'],
            ];
            
            $results = [];
            $totalUpdated = 0;
            
            foreach ($tables as $config) {
                try {
                    // التحقق من وجود العمود في الجدول
                    $checkStmt = $pdo->prepare("SHOW COLUMNS FROM `{$config['table']}` LIKE ?");
                    $checkStmt->execute([$config['field']]);
                    
                    if ($checkStmt->rowCount() > 0) {
                        $stmt = $pdo->prepare("
                            UPDATE `{$config['table']}` 
                            SET `{$config['field']}` = REPLACE(`{$config['field']}`, ?, ?)
                            WHERE `{$config['field']}` LIKE ?
                        ");
                        $stmt->execute([$oldPath, $newPath, "%{$oldPath}%"]);
                        $updated = $stmt->rowCount();
                        $results[] = [
                            'table' => $config['table'],
                            'field' => $config['field'],
                            'updated' => $updated
                        ];
                        $totalUpdated += $updated;
                    } else {
                        $results[] = [
                            'table' => $config['table'],
                            'field' => $config['field'],
                            'updated' => 0,
                            'note' => 'العمود غير موجود'
                        ];
                    }
                } catch (PDOException $e) {
                    $results[] = [
                        'table' => $config['table'],
                        'field' => $config['field'],
                        'error' => $e->getMessage()
                    ];
                }
            }
            
            logAudit('fix-upload-urls', [
                'old_path' => $oldPath,
                'new_path' => $newPath,
                'results' => $results,
                'total_updated' => $totalUpdated
            ]);
            
            jsonResponse(true, "تم تصحيح {$totalUpdated} رابط", [
                'old_path' => $oldPath,
                'new_path' => $newPath,
                'results' => $results,
                'total_updated' => $totalUpdated
            ]);
            break;
            
        default:
            jsonResponse(false, 'Action غير معروف. الـ Actions المتاحة: show-tables, describe, show-indexes, table-stats, show-relations, db-size, create-table, drop-table, alter-table, create-index, drop-index, execute-sql, import-sql, bulk-insert, truncate-table, audit-log, fix-upload-urls', null, 400);
    }
    
} catch (PDOException $e) {
    logAudit($action, 'Error: ' . $e->getMessage(), false);
    jsonResponse(false, 'خطأ في قاعدة البيانات: ' . $e->getMessage(), null, 500);
} catch (Exception $e) {
    logAudit($action, 'Error: ' . $e->getMessage(), false);
    jsonResponse(false, 'خطأ: ' . $e->getMessage(), null, 500);
}
