<?php
/**
 * EdumacFlo Database Backup & Safety Suite
 * Streamlined, secure database backup management for administrators.
 */

// Ensure required dependencies are available
if (!isset($connection)) {
    $conn_candidates = [
        __DIR__ . '/../../public/connection.php',
        __DIR__ . '/../public/connection.php',
        'public/connection.php'
    ];
    foreach ($conn_candidates as $cpath) {
        if (file_exists($cpath)) {
            require_once($cpath);
            break;
        }
    }
}
if (!function_exists('inject_checker')) {
    $func_candidates = [
        __DIR__ . '/../../public/functions.php',
        __DIR__ . '/../public/functions.php',
        'public/functions.php'
    ];
    foreach ($func_candidates as $fpath) {
        if (file_exists($fpath)) {
            require_once($fpath);
            break;
        }
    }
}

// CSRF Protection
$csrf_candidates = [
    __DIR__ . '/../../public/csrf.php',
    __DIR__ . '/../public/csrf.php',
    'public/csrf.php'
];
foreach ($csrf_candidates as $csrf_path) {
    if (file_exists($csrf_path)) {
        require_once($csrf_path);
        break;
    }
}

if (!function_exists('csrf_input')) {
    function csrf_input() { return ''; }
}
if (!function_exists('verify_csrf_token')) {
    function verify_csrf_token() {}
}

$backup_dir = __DIR__ . '/../backups/';
if (!is_dir($backup_dir)) {
    @mkdir($backup_dir, 0755, true);
}

// -------------------------------------------------------------
// HELPER: Format Bytes
// -------------------------------------------------------------
function formatDbBytes($bytes, $precision = 2) {
    $units = ['B', 'KB', 'MB', 'GB', 'TB'];
    $bytes = max($bytes, 0);
    $pow = floor(($bytes ? log($bytes) : 0) / log(1024));
    $pow = min($pow, count($units) - 1);
    $bytes /= pow(1024, $pow);
    return round($bytes, $precision) . ' ' . $units[$pow];
}

// -------------------------------------------------------------
// ACTION: Download Live Database Dump directly to Browser (POST)
// -------------------------------------------------------------
if (isset($_POST['download_live_backup'])) {
    verify_csrf_token();
    require_once(__DIR__ . '/../backup_cron.php');
    $format = $_POST['backup_format'] ?? 'zip';
    $timestamp = date('Y-m-d_H-i-s');
    $dump_sql = dumpDatabaseSql($connection, $dbname);

    if ($format === 'zip' && class_exists('ZipArchive')) {
        $zip_file = tempnam(sys_get_temp_dir(), 'edumacflo_db_');
        $zip = new ZipArchive();
        if ($zip->open($zip_file, ZipArchive::CREATE | ZipArchive::OVERWRITE) === TRUE) {
            $zip->addFromString($dbname . '_backup_' . $timestamp . '.sql', $dump_sql);
            $zip->close();

            header('Content-Type: application/zip');
            header('Content-Disposition: attachment; filename="' . $dbname . '_backup_' . $timestamp . '.zip"');
            header('Content-Length: ' . filesize($zip_file));
            header('Pragma: no-cache');
            header('Expires: 0');
            readfile($zip_file);
            @unlink($zip_file);
            exit;
        }
    }

    // Default: Download plain SQL
    header('Content-Type: application/sql');
    header('Content-Disposition: attachment; filename="' . $dbname . '_backup_' . $timestamp . '.sql"');
    header('Pragma: no-cache');
    header('Expires: 0');
    echo $dump_sql;
    exit;
}

// -------------------------------------------------------------
// ACTION: Download Existing Saved Snapshot (POST)
// -------------------------------------------------------------
if (isset($_POST['download_snapshot'])) {
    verify_csrf_token();
    $file = basename($_POST['snapshot_file'] ?? '');
    $filepath = $backup_dir . $file;

    if (!empty($file) && file_exists($filepath) && $file !== '.htaccess' && $file !== 'index.html') {
        $ext = strtolower(pathinfo($file, PATHINFO_EXTENSION));
        $mime = ($ext === 'zip') ? 'application/zip' : 'application/sql';

        header('Content-Type: ' . $mime);
        header('Content-Disposition: attachment; filename="' . $file . '"');
        header('Content-Length: ' . filesize($filepath));
        header('Pragma: no-cache');
        header('Expires: 0');
        readfile($filepath);
        exit;
    } else {
        $_SESSION['msg'] = "Backup file not found or invalid.";
        $_SESSION['type'] = "danger";
        header("Location: db_management");
        exit;
    }
}

// -------------------------------------------------------------
// ACTION: Create Snapshot & Save to Server (POST)
// -------------------------------------------------------------
if (isset($_POST['create_server_snapshot'])) {
    verify_csrf_token();
    require_once(__DIR__ . '/../backup_cron.php');
    $res = runScheduledBackup($connection, $dbname, $backup_dir);
    if ($res['status'] === 'success') {
        $_SESSION['msg'] = "New database snapshot <b>" . htmlspecialchars($res['file']) . "</b> ({$res['size_formatted']}) created successfully.";
        $_SESSION['type'] = "success";
    } else {
        $_SESSION['msg'] = "Failed to create database snapshot.";
        $_SESSION['type'] = "danger";
    }
    header("Location: db_management");
    exit;
}

// -------------------------------------------------------------
// ACTION: Delete Saved Snapshot (POST with CSRF)
// -------------------------------------------------------------
if (isset($_POST['delete_snapshot'])) {
    verify_csrf_token();
    $file = basename($_POST['snapshot_file'] ?? '');
    $filepath = $backup_dir . $file;
    if (!empty($file) && file_exists($filepath) && $file !== '.htaccess' && $file !== 'index.html') {
        @unlink($filepath);
        $_SESSION['msg'] = "Snapshot <b>" . htmlspecialchars($file) . "</b> deleted successfully.";
        $_SESSION['type'] = "success";
    } else {
        $_SESSION['msg'] = "Snapshot file not found.";
        $_SESSION['type'] = "warning";
    }
    header("Location: db_management");
    exit;
}

// -------------------------------------------------------------
// ACTION: Emergency Database Restore (POST with CSRF)
// -------------------------------------------------------------
if (isset($_POST['restore_db'])) {
    verify_csrf_token();
    $sql_content = '';
    $source_name = '';

    if (isset($_FILES['backup_file']['tmp_name']) && !empty($_FILES['backup_file']['tmp_name'])) {
        $source_name = basename($_FILES['backup_file']['name']);
        $ext = strtolower(pathinfo($source_name, PATHINFO_EXTENSION));

        if ($ext === 'zip' && class_exists('ZipArchive')) {
            $zip = new ZipArchive();
            if ($zip->open($_FILES['backup_file']['tmp_name']) === TRUE) {
                for ($i = 0; $i < $zip->numFiles; $i++) {
                    $entry_name = $zip->getNameIndex($i);
                    if (strtolower(pathinfo($entry_name, PATHINFO_EXTENSION)) === 'sql') {
                        $sql_content = $zip->getFromIndex($i);
                        break;
                    }
                }
                $zip->close();
            }
        } elseif ($ext === 'sql') {
            $sql_content = file_get_contents($_FILES['backup_file']['tmp_name']);
        }
    }

    if (!empty($sql_content)) {
        mysqli_query($connection, "SET FOREIGN_KEY_CHECKS=0");
        mysqli_query($connection, "SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO'");

        if (mysqli_multi_query($connection, $sql_content)) {
            do {
                if ($result = mysqli_store_result($connection)) {
                    mysqli_free_result($result);
                }
            } while (mysqli_more_results($connection) && mysqli_next_result($connection));

            mysqli_query($connection, "SET FOREIGN_KEY_CHECKS=1");
            $_SESSION['msg'] = "Database successfully restored from <b>" . htmlspecialchars($source_name) . "</b>.";
            $_SESSION['type'] = "success";
        } else {
            mysqli_query($connection, "SET FOREIGN_KEY_CHECKS=1");
            $_SESSION['msg'] = "Database restoration error: " . htmlspecialchars(mysqli_error($connection));
            $_SESSION['type'] = "danger";
        }
    } else {
        $_SESSION['msg'] = "Please provide a valid .sql or .zip backup archive.";
        $_SESSION['type'] = "warning";
    }

    header("Location: db_management");
    exit;
}

// -------------------------------------------------------------
// DATA QUERIES: High-Level Database Summary (No Schema Exposure)
// -------------------------------------------------------------
$total_db_size = 0;
$total_tables = 0;

$info_query = "SELECT 
    COUNT(TABLE_NAME) as total_tables,
    COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH), 0) as total_size
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = '" . mysqli_real_escape_string($connection, $dbname) . "'";
$info_res = mysqli_query($connection, $info_query);

if ($info_res && $row = mysqli_fetch_assoc($info_res)) {
    $total_tables = (int)$row['total_tables'];
    $total_db_size = (int)$row['total_size'];
} else {
    // Fallback if information_schema query is restricted
    $fallback_res = mysqli_query($connection, "SHOW TABLE STATUS FROM `" . $dbname . "`");
    if ($fallback_res) {
        while ($t = mysqli_fetch_assoc($fallback_res)) {
            $total_tables++;
            $total_db_size += (int)($t['Data_length'] ?? 0) + (int)($t['Index_length'] ?? 0);
        }
    }
}

// Fetch all local server snapshots
$saved_snapshots = [];
$snapshot_files = glob($backup_dir . '*.*');
if ($snapshot_files) {
    foreach ($snapshot_files as $f) {
        $basename = basename($f);
        if ($basename === '.htaccess' || $basename === 'index.html') continue;
        $saved_snapshots[] = [
            'filename' => $basename,
            'size' => filesize($f),
            'size_formatted' => formatDbBytes(filesize($f)),
            'mtime' => filemtime($f),
            'date_formatted' => date('M d, Y - h:i A', filemtime($f)),
            'ext' => strtolower(pathinfo($basename, PATHINFO_EXTENSION))
        ];
    }
    // Sort newest first
    usort($saved_snapshots, function($a, $b) {
        return $b['mtime'] <=> $a['mtime'];
    });
}

$latest_backup = !empty($saved_snapshots) ? $saved_snapshots[0] : null;
?>

<div class="page-title">
    <div>
        <h1><i class="fa fa-database"></i> Database Backup & Recovery</h1>
        <p>Manage scheduled snapshots, download backups, and protect school records</p>
    </div>
</div>

<style>
    .kpi-card {
        background: #fff;
        border-radius: 8px;
        padding: 16px 18px;
        margin-bottom: 20px;
        border: 1px solid #e2e8f0;
        box-shadow: 0 1px 3px rgba(0,0,0,0.05);
        display: flex;
        align-items: center;
        transition: transform 0.15s ease, box-shadow 0.15s ease;
    }
    .kpi-card:hover {
        transform: translateY(-2px);
        box-shadow: 0 4px 12px rgba(0,0,0,0.08);
    }
    .kpi-icon {
        width: 48px;
        height: 48px;
        border-radius: 8px;
        display: flex;
        align-items: center;
        justify-content: center;
        font-size: 20px;
        margin-right: 14px;
        flex-shrink: 0;
    }
    .kpi-title {
        font-size: 11px;
        text-transform: uppercase;
        font-weight: 700;
        color: #64748b;
        margin-bottom: 2px;
        letter-spacing: 0.5px;
    }
    .kpi-value {
        font-size: 20px;
        font-weight: 800;
        color: #1e293b;
        line-height: 1.2;
    }
    .kpi-desc {
        font-size: 11px;
        color: #94a3b8;
        margin-top: 2px;
    }
    .panel-clean {
        background: #fff;
        border: 1px solid #e2e8f0;
        border-radius: 8px;
        box-shadow: 0 1px 3px rgba(0,0,0,0.04);
        margin-bottom: 25px;
    }
    .panel-clean .panel-heading {
        background: #f8fafc;
        border-bottom: 1px solid #e2e8f0;
        padding: 14px 18px;
        font-weight: 700;
        font-size: 14px;
        color: #1e293b;
        border-top-left-radius: 7px;
        border-top-right-radius: 7px;
        display: flex;
        justify-content: space-between;
        align-items: center;
    }
    .panel-clean .panel-body {
        padding: 20px;
    }
    .table-db th {
        background: #f8fafc;
        color: #475569;
        font-size: 12px;
        font-weight: 700;
        padding: 10px 14px;
        border-bottom: 2px solid #e2e8f0 !important;
    }
    .table-db td {
        font-size: 13px;
        padding: 11px 14px;
        vertical-align: middle !important;
    }
    .btn-action-group {
        display: flex;
        gap: 6px;
        justify-content: flex-end;
    }
</style>

<!-- High-Level KPI Summary -->
<div class="row">
    <div class="col-md-3 col-sm-6">
        <div class="kpi-card">
            <div class="kpi-icon" style="background:#ecfdf5; color:#059669;">
                <i class="fa fa-check-circle"></i>
            </div>
            <div>
                <div class="kpi-title">Database Status</div>
                <div class="kpi-value text-success">Healthy</div>
                <div class="kpi-desc">Connected & operational</div>
            </div>
        </div>
    </div>

    <div class="col-md-3 col-sm-6">
        <div class="kpi-card">
            <div class="kpi-icon" style="background:#eff6ff; color:#2563eb;">
                <i class="fa fa-database"></i>
            </div>
            <div>
                <div class="kpi-title">Database Size</div>
                <div class="kpi-value"><?= formatDbBytes($total_db_size) ?></div>
                <div class="kpi-desc"><?= $total_tables ?> registered tables</div>
            </div>
        </div>
    </div>

    <div class="col-md-3 col-sm-6">
        <div class="kpi-card">
            <div class="kpi-icon" style="background:#f5f3ff; color:#7c3aed;">
                <i class="fa fa-calendar-check"></i>
            </div>
            <div>
                <div class="kpi-title">Latest Snapshot</div>
                <div class="kpi-value" style="font-size:15px; font-weight:700;">
                    <?= $latest_backup ? $latest_backup['date_formatted'] : '<span class="text-muted">None Yet</span>' ?>
                </div>
                <div class="kpi-desc"><?= $latest_backup ? $latest_backup['size_formatted'] : 'Create first backup below' ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-3 col-sm-6">
        <div class="kpi-card">
            <div class="kpi-icon" style="background:#fef3c7; color:#d97706;">
                <i class="fa fa-archive"></i>
            </div>
            <div>
                <div class="kpi-title">Stored Snapshots</div>
                <div class="kpi-value"><?= count($saved_snapshots) ?></div>
                <div class="kpi-desc">Available on server</div>
            </div>
        </div>
    </div>
</div>

<!-- Main Panels -->
<div class="row">
    <!-- Left Column: Generate Backup -->
    <div class="col-md-4">
        <div class="panel-clean">
            <div class="panel-heading">
                <span><i class="fa fa-download text-primary"></i> Create Backup</span>
            </div>
            <div class="panel-body">
                <p class="text-muted" style="font-size:13px; margin-bottom:15px;">
                    Generate a full backup containing student biodata, grades, payment records, and configuration.
                </p>

                <form method="POST">
                    <?= csrf_input() ?>
                    <div class="form-group">
                        <label style="font-size:12px; font-weight:600; color:#334155;">Download Format:</label>
                        <select name="backup_format" class="form-control input-sm">
                            <option value="zip">Compressed Archive (.ZIP - Recommended)</option>
                            <option value="sql">Plain SQL Script (.SQL)</option>
                        </select>
                    </div>
                    <button type="submit" name="download_live_backup" class="btn btn-primary btn-block" style="font-weight:600; padding:9px 15px;">
                        <i class="fa fa-cloud-download-alt"></i> Download to Computer
                    </button>
                </form>

                <div style="margin:20px 0; border-top:1px solid #f1f5f9; position:relative; text-align:center;">
                    <span style="background:#fff; padding:0 10px; color:#94a3b8; font-size:11px; position:relative; top:-9px; text-transform:uppercase;">or</span>
                </div>

                <form method="POST">
                    <?= csrf_input() ?>
                    <button type="submit" name="create_server_snapshot" class="btn btn-success btn-block" style="font-weight:600; padding:9px 15px;">
                        <i class="fa fa-save"></i> Save Snapshot to Server
                    </button>
                    <p class="text-muted" style="margin-top:8px; font-size:11px; line-height:1.4;">
                        <i class="fa fa-info-circle text-info"></i> Snapshots are stored in the server's protected backup folder and automatically pruned after 30 days.
                    </p>
                </form>
            </div>
        </div>
    </div>

    <!-- Right Column: Stored Snapshots -->
    <div class="col-md-8">
        <div class="panel-clean">
            <div class="panel-heading">
                <span><i class="fa fa-server text-info"></i> Stored Server Snapshots</span>
                <span class="badge" style="background:#64748b; font-weight:600; font-size:11px;"><?= count($saved_snapshots) ?> available</span>
            </div>
            <div class="panel-body" style="padding:0;">
                <?php if (!empty($saved_snapshots)): ?>
                    <div class="table-responsive">
                        <table class="table table-hover table-db" style="margin-bottom:0;">
                            <thead>
                                <tr>
                                    <th>Snapshot File</th>
                                    <th>Created Date</th>
                                    <th>Size</th>
                                    <th style="text-align:right; width:130px;">Actions</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($saved_snapshots as $snap): ?>
                                    <tr>
                                        <td>
                                            <i class="fa fa-file-archive text-warning" style="margin-right:6px;"></i>
                                            <span style="font-weight:600; color:#1e293b;"><?= htmlspecialchars($snap['filename']) ?></span>
                                        </td>
                                        <td><span class="text-muted"><?= $snap['date_formatted'] ?></span></td>
                                        <td><span class="label label-default" style="font-size:11px;"><?= $snap['size_formatted'] ?></span></td>
                                        <td style="text-align:right;">
                                            <div class="btn-action-group">
                                                <!-- Download Form -->
                                                <form method="POST" style="display:inline-block; margin:0;">
                                                    <?= csrf_input() ?>
                                                    <input type="hidden" name="snapshot_file" value="<?= htmlspecialchars($snap['filename']) ?>">
                                                    <button type="submit" name="download_snapshot" class="btn btn-xs btn-primary" title="Download Snapshot">
                                                        <i class="fa fa-download"></i>
                                                    </button>
                                                </form>

                                                <!-- Delete Form -->
                                                <form method="POST" style="display:inline-block; margin:0;" onsubmit="return confirm('Are you sure you want to delete snapshot <?= htmlspecialchars($snap['filename']) ?>?');">
                                                    <?= csrf_input() ?>
                                                    <input type="hidden" name="snapshot_file" value="<?= htmlspecialchars($snap['filename']) ?>">
                                                    <button type="submit" name="delete_snapshot" class="btn btn-xs btn-default text-danger" title="Delete Snapshot">
                                                        <i class="fa fa-trash"></i>
                                                    </button>
                                                </form>
                                            </div>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                <?php else: ?>
                    <div class="text-center text-muted" style="padding:45px 20px;">
                        <i class="fa fa-folder-open fa-3x" style="color:#cbd5e1; margin-bottom:12px;"></i>
                        <p style="font-size:14px; margin-bottom:4px; font-weight:600; color:#64748b;">No stored snapshots yet</p>
                        <p style="font-size:12px; color:#94a3b8; max-width:400px; margin:0 auto;">
                            Click <b>"Save Snapshot to Server"</b> to create your first on-server backup.
                        </p>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<!-- Guarded Emergency Disaster Recovery -->
<div class="row">
    <div class="col-md-12">
        <div class="panel-clean" style="border-left: 4px solid #ef4444;">
            <div class="panel-heading" style="background:#fff; cursor:pointer;" data-toggle="collapse" data-target="#recoverySection" aria-expanded="false">
                <span style="color:#b91c1c;">
                    <i class="fa fa-shield-alt text-danger"></i> Emergency Disaster Recovery
                </span>
                <span class="small text-muted"><i class="fa fa-chevron-down"></i> Click to expand</span>
            </div>
            <div id="recoverySection" class="collapse">
                <div class="panel-body" style="padding:22px;">
                    <div class="alert alert-danger" style="margin-bottom:18px; border-radius:6px; font-size:13px;">
                        <strong><i class="fa fa-exclamation-triangle"></i> Caution:</strong>
                        Restoring a database will overwrite current database records with the contents of the uploaded backup archive. Only perform a restoration if instructed by senior technical personnel.
                    </div>

                    <form method="POST" enctype="multipart/form-data">
                        <?= csrf_input() ?>
                        <div class="row">
                            <div class="col-md-8">
                                <div class="form-group" style="margin-bottom:0;">
                                    <label style="font-size:12px; font-weight:600;">Select Backup Archive (.sql or .zip):</label>
                                    <input type="file" name="backup_file" class="form-control" accept=".sql,.zip" required>
                                </div>
                            </div>
                            <div class="col-md-4">
                                <label style="font-size:12px; visibility:hidden;">Action</label>
                                <button type="submit" name="restore_db" class="btn btn-danger btn-block" style="font-weight:700; padding:8px 15px;" onclick="return confirm('CRITICAL CONFIRMATION: Are you 100% sure you want to restore the database from this file? All current records will be overwritten.')">
                                    <i class="fa fa-history"></i> Restore Database
                                </button>
                            </div>
                        </div>
                    </form>
                </div>
            </div>
        </div>
    </div>
</div>
