<?php
declare(strict_types=1);

require_once __DIR__ . '/includes/iscms_layout.php';

$conn = iscms_init();
iscms_require_roles(['SUPER_ADMIN', 'ADMIN']);

$exportable = [
    'users' => 'Users (no password column)',
    'organizations' => 'Organisations',
    'departments' => 'Departments',
    'roles' => 'Roles',
    'permissions' => 'Permissions',
    'role_permissions' => 'Role permissions',
    'standard_categories' => 'Standard categories',
    'standards' => 'Standards',
    'product_categories' => 'Product categories',
    'products' => 'Products',
    'product_standards' => 'Product standards',
    'fee_schedules' => 'Fee schedules',
    'certificate_types' => 'Certificate types',
    'applications' => 'Applications',
    'laboratories' => 'Laboratories',
    'internal_messages' => 'Internal messages',
];

if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'export_zip') {
    if (!iscms_csrf_validate($_POST['csrf_token'] ?? null)) {
        http_response_code(403);
        header('Content-Type: text/plain; charset=UTF-8');
        echo 'Invalid security token.';
        exit;
    }

    $selected = $_POST['tables'] ?? [];
    if (!is_array($selected)) {
        $selected = [];
    }
    $selected = array_values(array_intersect(array_keys($exportable), $selected));
    if ($selected === []) {
        http_response_code(400);
        header('Content-Type: text/plain; charset=UTF-8');
        echo 'Select at least one table.';
        exit;
    }

    $maxRows = 10000;
    $useZip = class_exists('ZipArchive');

    if (!$useZip) {
        $t = $selected[0];
        if (!iscms_table_exists($conn, $t)) {
            http_response_code(400);
            echo 'Table not found: ' . $t;
            exit;
        }
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $t . '_' . date('Y-m-d') . '.csv"');
        iscms_stream_table_csv($conn, $t, $maxRows);
        exit;
    }

    $tmp = tempnam(sys_get_temp_dir(), 'ssbs_exp');
    if ($tmp === false) {
        http_response_code(500);
        echo 'Could not create temp file.';
        exit;
    }
    $zip = new ZipArchive();
    if ($zip->open($tmp, ZipArchive::OVERWRITE) !== true) {
        unlink($tmp);
        http_response_code(500);
        echo 'Could not create archive.';
        exit;
    }

    foreach ($selected as $t) {
        if (!iscms_table_exists($conn, $t)) {
            continue;
        }
        $csv = iscms_table_csv_string($conn, $t, $maxRows);
        if ($csv !== null) {
            $zip->addFromString($t . '.csv', $csv);
        }
    }
    $zip->close();

    header('Content-Type: application/zip');
    header('Content-Disposition: attachment; filename="ssbs_iscms_export_' . date('Y-m-d_His') . '.zip"');
    header('Content-Length: ' . (string) filesize($tmp));
    readfile($tmp);
    unlink($tmp);
    exit;
}

/**
 * @return string|null UTF-8 CSV including header row
 */
function iscms_table_csv_string(mysqli $conn, string $table, int $maxRows): ?string
{
    $fh = fopen('php://temp', 'r+');
    if ($fh === false) {
        return null;
    }
    iscms_stream_table_csv_to_handle($conn, $table, $maxRows, $fh);
    rewind($fh);
    $s = stream_get_contents($fh);
    fclose($fh);
    return $s !== false ? $s : null;
}

function iscms_stream_table_csv(mysqli $conn, string $table, int $maxRows): void
{
    $out = fopen('php://output', 'w');
    if ($out !== false) {
        iscms_stream_table_csv_to_handle($conn, $table, $maxRows, $out);
        fclose($out);
    }
}

function iscms_stream_table_csv_to_handle(mysqli $conn, string $table, int $maxRows, $handle): void
{
    $t = $conn->real_escape_string($table);
    $res = $conn->query('SELECT * FROM `' . $t . '` LIMIT ' . (int) $maxRows);
    if (!$res) {
        fputcsv($handle, ['error']);
        fputcsv($handle, ['query_failed']);
        return;
    }
    if ($res->num_rows === 0) {
        fputcsv($handle, ['info']);
        fputcsv($handle, ['no_rows']);
        return;
    }
    $first = $res->fetch_assoc();
    if ($first === null) {
        return;
    }
    if ($table === 'users') {
        unset($first['password_hash']);
    }
    fputcsv($handle, array_keys($first));
    fputcsv($handle, array_map('iscms_csv_cell', $first));
    while ($row = $res->fetch_assoc()) {
        if ($table === 'users') {
            unset($row['password_hash']);
        }
        fputcsv($handle, array_map('iscms_csv_cell', $row));
    }
}

/** @param mixed $v */
function iscms_csv_cell($v): string
{
    if ($v === null) {
        return '';
    }
    if (is_bool($v)) {
        return $v ? '1' : '0';
    }
    return (string) $v;
}

$pageTitle = 'Database export';
$activeNav = 'settings';
$csrf = iscms_csrf_token();

iscms_render_head($pageTitle);
iscms_render_sidebar($activeNav);
iscms_render_topbar($pageTitle);
?>
    <div class="page-header">
        <h1>Database export</h1>
        <p>Download selected register tables as CSV (up to 10,000 rows each). Not a full binary backup.</p>
    </div>

    <div class="section-card mb-4">
        <div class="section-header">
            <h2><i class="ri-links-line"></i> Related</h2>
        </div>
        <div class="quick-actions">
            <a class="qa-btn" href="backup_restore.php"><i class="ri-database-2-line"></i> Backup and restore</a>
            <a class="qa-btn" href="audit_logs.php"><i class="ri-file-code-line"></i> Audit logs</a>
        </div>
    </div>

    <div class="section-card">
        <div class="section-header">
            <h2><i class="ri-file-excel-2-line"></i> Export</h2>
        </div>
        <div class="p-3" style="font-size:.9rem">
            <?php if (!class_exists('ZipArchive')): ?>
            <div class="alert alert-warning py-2 small">PHP <code class="iscms-mono">ZipArchive</code> is not available. Only the first selected table will download as a single CSV.</div>
            <?php endif; ?>
            <p class="text-muted small mb-3">The <code class="iscms-mono">users</code> export omits <code class="iscms-mono">password_hash</code>. Tables not yet created in your database are skipped in the ZIP.</p>
            <form method="post" action="database_export.php">
                <input type="hidden" name="csrf_token" value="<?= iscms_h($csrf) ?>">
                <input type="hidden" name="action" value="export_zip">
                <div class="row g-2 mb-3" style="max-width:36rem">
                    <?php foreach ($exportable as $tbl => $label): ?>
                    <div class="col-md-6">
                        <div class="form-check">
                            <input class="form-check-input" type="checkbox" name="tables[]" value="<?= iscms_h($tbl) ?>" id="t_<?= iscms_h($tbl) ?>"
                                <?= !iscms_table_exists($conn, $tbl) ? 'disabled' : '' ?>>
                            <label class="form-check-label small" for="t_<?= iscms_h($tbl) ?>"><?= iscms_h($label) ?>
                                <?php if (!iscms_table_exists($conn, $tbl)): ?><span class="text-muted">(n/a)</span><?php endif; ?>
                            </label>
                        </div>
                    </div>
                    <?php endforeach; ?>
                </div>
                <button type="submit" class="btn btn-sm btn-primary"><i class="ri-download-2-line me-1"></i>Download export</button>
            </form>
        </div>
    </div>
<?php
iscms_render_footer();
