<?php
define('ADMIN_ACCESS', true);
require_once 'config.php';
require_once '../includes/database.php';
require_once '../includes/site-manager.php';
require_once '../includes/multi-tenant-auth.php';
require_once '../includes/integrated-admin-auth.php';
require_once '../includes/permission-middleware.php';

// Check integrated authentication
$integratedAuth = new IntegratedAdminAuth();
if (!isset($_SESSION['integrated_admin_session_id'])) {
    header('Location: login.php');
    exit;
}

$adminData = $integratedAuth->validateSession($_SESSION['integrated_admin_session_id']);
if (!$adminData) {
    session_destroy();
    header('Location: login.php');
    exit;
}

// Update session data  
$_SESSION['admin_data'] = $adminData;

$db = new Database();
$pdo = $db->getConnection();
$action = $_GET['action'] ?? 'status';

// Handle actions
$message = '';
$error = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if ($_POST['action'] === 'backup') {
        $backupResult = performDatabaseBackup();
        if ($backupResult['success']) {
            $message = $backupResult['message'];
        } else {
            $error = $backupResult['message'];
        }
    }
}

// Function to perform database backup
function performDatabaseBackup() {
    global $pdo;
    
    try {
        $dbname = 'linkslot88_autoprediksikjmngpfjr941';
        $backupDir = '../backup/';
        
        // Create backup directory if not exists
        if (!is_dir($backupDir)) {
            mkdir($backupDir, 0755, true);
        }
        
        $filename = $backupDir . 'backup_' . date('Y-m-d_H-i-s') . '.sql';
        
        // Use mysqldump command
        $mysqldumpPath = 'D:\\xampp\\mysql\\bin\\mysqldump.exe';
        $command = "\"{$mysqldumpPath}\" -u root {$dbname} > \"{$filename}\"";
        
        exec($command, $output, $returnCode);
        
        if ($returnCode === 0 && file_exists($filename)) {
            $fileSize = formatBytes(filesize($filename));
            return [
                'success' => true,
                'message' => "Backup berhasil dibuat: " . basename($filename) . " ({$fileSize})"
            ];
        } else {
            return [
                'success' => false,
                'message' => "Backup gagal! Return code: {$returnCode}"
            ];
        }
    } catch (Exception $e) {
        return [
            'success' => false,
            'message' => "Error: " . $e->getMessage()
        ];
    }
}

// Function to format bytes
function formatBytes($bytes, $precision = 2) {
    $units = array('B', 'KB', 'MB', 'GB', 'TB');
    
    for ($i = 0; $bytes > 1024; $i++) {
        $bytes /= 1024;
    }
    
    return round($bytes, $precision) . ' ' . $units[$i];
}

// Get database statistics
$dbStats = [];
try {
    // Database size
    $stmt = $pdo->prepare("
        SELECT 
            table_schema as database_name,
            ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
        FROM information_schema.tables 
        WHERE table_schema = 'AUTOPREDIKSI'
        GROUP BY table_schema
    ");
    $stmt->execute();
    $sizeResult = $stmt->fetch(PDO::FETCH_ASSOC);
    $dbStats['size'] = $sizeResult ? $sizeResult['size_mb'] . ' MB' : 'Unknown';
    
    // Table count
    $stmt = $pdo->prepare("
        SELECT COUNT(*) as table_count 
        FROM information_schema.tables 
        WHERE table_schema = 'AUTOPREDIKSI'
    ");
    $stmt->execute();
    $dbStats['tables'] = $stmt->fetchColumn();
    
    // Connection status
    $stmt = $pdo->prepare("SELECT 1");
    $stmt->execute();
    $dbStats['connection'] = 'Connected';
    
    // Server version
    $stmt = $pdo->prepare("SELECT VERSION() as version");
    $stmt->execute();
    $versionResult = $stmt->fetch(PDO::FETCH_ASSOC);
    $dbStats['version'] = $versionResult['version'];
    
    // Get table information
    $stmt = $pdo->prepare("
        SELECT 
            table_name,
            table_rows,
            ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
            engine
        FROM information_schema.tables 
        WHERE table_schema = 'AUTOPREDIKSI'
        ORDER BY (data_length + index_length) DESC
    ");
    $stmt->execute();
    $tables = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
} catch (Exception $e) {
    $dbStats = [
        'connection' => 'Error: ' . $e->getMessage(),
        'size' => 'Unknown',
        'tables' => 'Unknown',
        'version' => 'Unknown'
    ];
    $tables = [];
}

// Get backup files
$backupFiles = [];
if (is_dir('../backup/')) {
    $files = glob('../backup/backup_*.sql');
    foreach ($files as $file) {
        $backupFiles[] = [
            'name' => basename($file),
            'size' => formatBytes(filesize($file)),
            'date' => date('Y-m-d H:i:s', filemtime($file)),
            'path' => $file
        ];
    }
    // Sort by date descending
    usort($backupFiles, function($a, $b) {
        return strcmp($b['date'], $a['date']);
    });
}

$pageTitle = 'Database Management';
include 'admin-sidebar-template.php';
?>

<!-- Database Status Navigation -->
<div class="row mb-4">
    <div class="col-12">
        <div class="card">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-center">
                    <h5 class="card-title mb-0">
                        <i class="fas fa-database me-2"></i>Database Management
                    </h5>
                    <a href="dashboard.php" class="btn btn-outline-primary">
                        <i class="fas fa-arrow-left me-1"></i> Kembali ke Dashboard
                    </a>
                </div>
            </div>
        </div>
    </div>
</div>
<!-- Navigation Tabs -->
<div class="row mb-4">
    <div class="col-12">
        <div class="card">
            <div class="card-body py-2">
                <ul class="nav nav-pills nav-fill">
                    <li class="nav-item">
                        <a class="nav-link <?php echo $action === 'status' ? 'active' : ''; ?>" href="database.php">
                            <i class="fas fa-server me-2"></i>Database Status
                        </a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link <?php echo $action === 'backup' ? 'active' : ''; ?>" href="database.php?action=backup">
                            <i class="fas fa-download me-2"></i>Backup Data
                        </a>
                    </li>
                </ul>
            </div>
        </div>
    </div>
</div>
    
<!-- Messages -->
<?php if ($message): ?>
<div class="row mb-3">
    <div class="col-12">
        <div class="alert alert-success alert-dismissible fade show" role="alert">
            <i class="fas fa-check-circle me-2"></i><?php echo htmlspecialchars($message); ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    </div>
</div>
<?php endif; ?>

<?php if ($error): ?>
<div class="row mb-3">
    <div class="col-12">
        <div class="alert alert-danger alert-dismissible fade show" role="alert">
            <i class="fas fa-exclamation-triangle me-2"></i><?php echo htmlspecialchars($error); ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    </div>
</div>
<?php endif; ?>
    
<?php if ($action === 'status'): ?>
<!-- Database Status -->
<div class="row mb-4">
    <div class="col-12">
        <div class="card">
            <div class="card-header">
                <h5 class="card-title mb-0">
                    <i class="fas fa-server me-2"></i>Database Status
                </h5>
            </div>
            <div class="card-body">
                <div class="row g-3">
                    <div class="col-md-3">
                        <div class="card bg-light h-100">
                            <div class="card-body text-center">
                                <div class="h4 mb-1">
                                    <span class="badge <?php echo $dbStats['connection'] === 'Connected' ? 'bg-success' : 'bg-danger'; ?>">
                                        <?php echo $dbStats['connection']; ?>
                                    </span>
                                </div>
                                <small class="text-muted">Connection Status</small>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-md-3">
                        <div class="card bg-light h-100">
                            <div class="card-body text-center">
                                <div class="h4 mb-1 text-primary"><?php echo $dbStats['size']; ?></div>
                                <small class="text-muted">Database Size</small>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-md-3">
                        <div class="card bg-light h-100">
                            <div class="card-body text-center">
                                <div class="h4 mb-1 text-info"><?php echo $dbStats['tables']; ?></div>
                                <small class="text-muted">Total Tables</small>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-md-3">
                        <div class="card bg-light h-100">
                            <div class="card-body text-center">
                                <div class="h4 mb-1 text-warning"><?php echo $dbStats['version']; ?></div>
                                <small class="text-muted">MySQL Version</small>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>
        
<!-- Table Information -->
<div class="row">
    <div class="col-12">
        <div class="card">
            <div class="card-header">
                <h5 class="card-title mb-0">
                    <i class="fas fa-table me-2"></i>Table Information
                </h5>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-striped table-hover">
                        <thead class="table-dark">
                            <tr>
                                <th>Table Name</th>
                            <th>Rows</th>
                            <th>Size</th>
                            <th>Engine</th>
                        </tr>
                    </thead>
                    <tbody>
                            <?php foreach ($tables as $table): ?>
                            <tr>
                                <td><code><?php echo htmlspecialchars($table['table_name']); ?></code></td>
                                <td><?php echo number_format($table['table_rows']); ?></td>
                                <td><span class="badge bg-info"><?php echo $table['size_mb']; ?> MB</span></td>
                                <td><?php echo htmlspecialchars($table['engine']); ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>
        
<?php elseif ($action === 'backup'): ?>
<!-- Backup Management -->
<div class="row mb-4">
    <div class="col-12">
        <div class="card">
            <div class="card-header">
                <h5 class="card-title mb-0">
                    <i class="fas fa-download me-2"></i>Create Database Backup
                </h5>
            </div>
            <div class="card-body">
            
                <div class="alert alert-info">
                    <i class="fas fa-info-circle me-2"></i>
                    Backup akan menyimpan seluruh data database dalam format SQL yang dapat di-restore kapan saja.
                </div>
                
                <form method="POST" class="text-center">
                    <input type="hidden" name="action" value="backup">
                    <button type="submit" class="btn btn-success btn-lg">
                        <i class="fas fa-download me-2"></i>Create Backup Now
                    </button>
                </form>
            </div>
        </div>
    </div>
</div>
        
<!-- Existing Backups -->
<div class="row">
    <div class="col-12">
        <div class="card">
            <div class="card-header">
                <h5 class="card-title mb-0">
                    <i class="fas fa-history me-2"></i>Backup History
                </h5>
            </div>
            <div class="card-body">
                <?php if (empty($backupFiles)): ?>
                    <div class="alert alert-warning text-center">
                        <i class="fas fa-exclamation-triangle me-2"></i>
                        Belum ada backup yang dibuat.
                    </div>
                <?php else: ?>
                    <div class="table-responsive">
                        <table class="table table-striped table-hover">
                            <thead class="table-dark">
                                <tr>
                                    <th>Filename</th>
                                    <th>Size</th>
                                    <th>Created Date</th>
                                    <th>Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($backupFiles as $backup): ?>
                                    <tr>
                                        <td><code><?php echo htmlspecialchars($backup['name']); ?></code></td>
                                        <td><span class="badge bg-info"><?php echo $backup['size']; ?></span></td>
                                        <td><?php echo $backup['date']; ?></td>
                                        <td>
                                            <a href="<?php echo $backup['path']; ?>" download class="btn btn-sm btn-outline-primary">
                                                <i class="fas fa-download me-1"></i>Download
                                            </a>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>
<?php endif; ?>

<?php include 'admin-sidebar-footer.php'; ?>

<?php include 'admin-sidebar-footer.php'; ?>