<?php

function appSqlValue(mysqli $conn, $value): string
{
    if ($value === null) {
        return 'NULL';
    }

    return "'" . $conn->real_escape_string((string) $value) . "'";
}

function currentAppDatabaseName(mysqli $conn): string
{
    $result = $conn->query('SELECT DATABASE() AS db_name');
    $row = $result ? $result->fetch_assoc() : null;
    $dbName = trim((string) ($row['db_name'] ?? ''));

    if ($dbName === '') {
        throw new RuntimeException('Could not determine the current store database name.');
    }

    return $dbName;
}

function buildAppDatabaseBackup(mysqli $conn): array
{
    $databaseName = currentAppDatabaseName($conn);
    $tables = [];
    $result = $conn->query('SHOW TABLES');
    if ($result) {
        while ($row = $result->fetch_array(MYSQLI_NUM)) {
            $tables[] = $row[0];
        }
        $result->free();
    }

    $dump = "-- HouseTab Pro database backup\n";
    $dump .= "-- Database: `" . $databaseName . "`\n";
    $dump .= "-- Created: " . date('Y-m-d H:i:s') . "\n\n";
    $dump .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

    foreach ($tables as $table) {
        $escapedTable = str_replace('`', '``', $table);
        $createResult = $conn->query("SHOW CREATE TABLE `{$escapedTable}`");
        if (!$createResult) {
            throw new RuntimeException('Could not read schema for table ' . $table . '.');
        }

        $createRow = $createResult->fetch_assoc();
        $createResult->free();
        $createSql = (string) ($createRow['Create Table'] ?? '');

        $dump .= "DROP TABLE IF EXISTS `{$escapedTable}`;\n";
        $dump .= $createSql . ";\n\n";

        $rowsResult = $conn->query("SELECT * FROM `{$escapedTable}`");
        if (!$rowsResult) {
            throw new RuntimeException('Could not read rows for table ' . $table . '.');
        }

        while ($row = $rowsResult->fetch_assoc()) {
            $columns = array_map(
                static fn($column) => '`' . str_replace('`', '``', $column) . '`',
                array_keys($row)
            );
            $values = array_map(
                static fn($value) => appSqlValue($conn, $value),
                array_values($row)
            );

            $dump .= "INSERT INTO `{$escapedTable}` (" . implode(', ', $columns) . ') VALUES (' . implode(', ', $values) . ");\n";
        }

        $rowsResult->free();
        $dump .= "\n";
    }

    $dump .= "SET FOREIGN_KEY_CHECKS=1;\n";

    $safeDb = preg_replace('/[^A-Za-z0-9_-]+/', '_', $databaseName);
    $filename = ($safeDb !== '' ? $safeDb : 'housetab_store') . '_backup_' . date('Ymd_His') . '.sql';

    return [
        'database_name' => $databaseName,
        'filename' => $filename,
        'content' => $dump,
    ];
}

function appRunSqlBatch(mysqli $conn, string $sql): void
{
    if (!$conn->multi_query($sql)) {
        throw new RuntimeException('Could not import backup: ' . $conn->error);
    }

    do {
        if ($result = $conn->store_result()) {
            $result->free();
        }
    } while ($conn->more_results() && $conn->next_result());

    if ($conn->errno) {
        throw new RuntimeException('Could not finish backup import: ' . $conn->error);
    }
}

function appRestoreSqlFromUpload(array $file): array
{
    $error = (int) ($file['error'] ?? UPLOAD_ERR_NO_FILE);
    if ($error !== UPLOAD_ERR_OK) {
        if ($error === UPLOAD_ERR_NO_FILE) {
            throw new RuntimeException('Please choose a backup file to restore.');
        }
        throw new RuntimeException('The backup upload could not be processed.');
    }

    $tmpName = (string) ($file['tmp_name'] ?? '');
    if ($tmpName === '' || !is_uploaded_file($tmpName)) {
        throw new RuntimeException('The uploaded backup file is not valid.');
    }

    $originalName = trim((string) ($file['name'] ?? 'backup.sql'));
    $content = file_get_contents($tmpName);
    if ($content === false) {
        throw new RuntimeException('Could not read the uploaded backup file.');
    }

    $lowerName = strtolower($originalName);
    if (substr($lowerName, -3) === '.gz') {
        if (!function_exists('gzdecode')) {
            throw new RuntimeException('This server cannot read .gz backup files right now.');
        }

        $decoded = gzdecode($content);
        if ($decoded === false) {
            throw new RuntimeException('The uploaded .gz backup file could not be decoded.');
        }
        $content = $decoded;
    }

    $sql = trim((string) $content);
    if ($sql === '') {
        throw new RuntimeException('The uploaded backup file is empty.');
    }

    if (stripos($sql, 'CREATE TABLE') === false && stripos($sql, 'INSERT INTO') === false) {
        throw new RuntimeException('That file does not look like a HouseTab Pro SQL backup.');
    }

    return [
        'original_name' => $originalName !== '' ? $originalName : 'backup.sql',
        'sql' => $sql,
    ];
}

function restoreCurrentAppDatabase(mysqli $conn, string $restoreSql): array
{
    $preRestoreBackup = buildAppDatabaseBackup($conn);

    try {
        appRunSqlBatch($conn, $restoreSql);
    } catch (Throwable $e) {
        try {
            appRunSqlBatch($conn, (string) ($preRestoreBackup['content'] ?? ''));
        } catch (Throwable $restoreError) {
            throw new RuntimeException(
                'Restore failed, and the automatic safety restore also failed. Original error: '
                . $e->getMessage()
                . ' Recovery error: '
                . $restoreError->getMessage()
            );
        }

        throw new RuntimeException('Restore failed and your previous data snapshot was put back. ' . $e->getMessage());
    }

    return $preRestoreBackup;
}