<?php
declare(strict_types=1);

if (PHP_SAPI !== 'cli') {
    http_response_code(404);
    exit('Not found');
}

$config = require __DIR__ . '/config.php';

function backup_db(): PDO {
    global $config;
    $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8mb4', $config['db_host'], $config['db_name']);
    return new PDO($dsn, $config['db_user'], $config['db_pass'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
}

function clean_header(string $value): string {
    return trim(preg_replace('/[\r\n]+/', ' ', $value));
}

function sql_value(mixed $value): string {
    if ($value === null) return 'NULL';
    if (is_int($value) || is_float($value)) return (string)$value;
    return "'" . str_replace(["\\", "'"], ["\\\\", "\\'"], (string)$value) . "'";
}

function write_sql_dump(PDO $pdo, string $path, array $tables): void {
    $out = fopen($path, 'wb');
    fwrite($out, "-- Grange Station backup\n-- Created " . date('c') . "\n\nSET FOREIGN_KEY_CHECKS=0;\n\n");
    foreach ($tables as $table) {
        try {
            $create = $pdo->query("SHOW CREATE TABLE `{$table}`")->fetch();
        } catch (Throwable $error) {
            fwrite($out, "-- Skipped missing table `{$table}`\n\n");
            continue;
        }
        if (!$create) continue;
        fwrite($out, "DROP TABLE IF EXISTS `{$table}`;\n");
        fwrite($out, ($create['Create Table'] ?? array_values($create)[1]) . ";\n\n");

        $rows = $pdo->query("SELECT * FROM `{$table}`")->fetchAll();
        foreach ($rows as $row) {
            $columns = array_map(fn($column) => "`{$column}`", array_keys($row));
            $values = array_map('sql_value', array_values($row));
            fwrite($out, "INSERT INTO `{$table}` (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $values) . ");\n");
        }
        fwrite($out, "\n");
    }
    fwrite($out, "SET FOREIGN_KEY_CHECKS=1;\n");
    fclose($out);
}

function write_csv(PDO $pdo, string $path, string $sql): void {
    $rows = $pdo->query($sql)->fetchAll();
    $out = fopen($path, 'wb');
    if (!$rows) {
        fclose($out);
        return;
    }
    fputcsv($out, array_keys($rows[0]));
    foreach ($rows as $row) fputcsv($out, $row);
    fclose($out);
}

function smtp_read($socket): string {
    $response = '';
    while (($line = fgets($socket, 515)) !== false) {
        $response .= $line;
        if (isset($line[3]) && $line[3] === ' ') break;
    }
    return $response;
}

function smtp_command($socket, string $command, array $expected): void {
    fwrite($socket, $command . "\r\n");
    $response = smtp_read($socket);
    $code = (int)substr($response, 0, 3);
    if (!in_array($code, $expected, true)) {
        throw new RuntimeException('SMTP command failed: ' . trim($response));
    }
}

function send_backup_email(string $subject, string $body, string $attachmentPath): bool {
    global $config;
    if (empty($config['smtp_host']) || empty($config['smtp_user']) || empty($config['smtp_pass'])) return false;

    $host = (string)$config['smtp_host'];
    $port = (int)($config['smtp_port'] ?? 465);
    $secure = (string)($config['smtp_secure'] ?? 'ssl');
    $target = $secure === 'ssl' ? "ssl://{$host}:{$port}" : "tcp://{$host}:{$port}";
    $socket = @stream_socket_client($target, $errno, $errstr, 20, STREAM_CLIENT_CONNECT);
    if (!$socket) throw new RuntimeException("SMTP connection failed: {$errstr}");
    stream_set_timeout($socket, 20);

    smtp_read($socket);
    smtp_command($socket, 'EHLO grangestation.com', [250]);
    if ($secure === 'tls') {
        smtp_command($socket, 'STARTTLS', [220]);
        if (!stream_socket_enable_crypto($socket, true, STREAM_CRYPTO_METHOD_TLS_CLIENT)) {
            throw new RuntimeException('SMTP TLS failed.');
        }
        smtp_command($socket, 'EHLO grangestation.com', [250]);
    }
    smtp_command($socket, 'AUTH LOGIN', [334]);
    smtp_command($socket, base64_encode((string)$config['smtp_user']), [334]);
    smtp_command($socket, base64_encode((string)$config['smtp_pass']), [235]);

    $to = clean_header((string)($config['mail_to'] ?? 'events@grangestation.com'));
    $from = clean_header((string)($config['mail_from'] ?? $config['smtp_user']));
    $boundary = 'grange-backup-' . bin2hex(random_bytes(8));
    $filename = basename($attachmentPath);
    $mime = str_ends_with($filename, '.zip') ? 'application/zip' : 'application/gzip';
    $attachment = chunk_split(base64_encode(file_get_contents($attachmentPath) ?: ''));

    smtp_command($socket, "MAIL FROM:<{$from}>", [250]);
    smtp_command($socket, "RCPT TO:<{$to}>", [250, 251]);
    smtp_command($socket, 'DATA', [354]);
    $message = implode("\r\n", [
        "From: Grange Station <{$from}>",
        "To: {$to}",
        "Subject: " . clean_header($subject),
        "MIME-Version: 1.0",
        "Content-Type: multipart/mixed; boundary=\"{$boundary}\"",
        "",
        "--{$boundary}",
        "Content-Type: text/plain; charset=UTF-8",
        "",
        $body,
        "",
        "--{$boundary}",
        "Content-Type: {$mime}; name=\"{$filename}\"",
        "Content-Transfer-Encoding: base64",
        "Content-Disposition: attachment; filename=\"{$filename}\"",
        "",
        $attachment,
        "--{$boundary}--",
    ]);
    fwrite($socket, $message . "\r\n.\r\n");
    $response = smtp_read($socket);
    smtp_command($socket, 'QUIT', [221]);
    fclose($socket);
    return ((int)substr($response, 0, 3)) === 250;
}

function zip_directory(string $sourceDir, string $zipPath): void {
    if (!class_exists('ZipArchive')) {
        throw new RuntimeException('ZipArchive is not installed.');
    }
    $zip = new ZipArchive();
    if ($zip->open($zipPath, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
        throw new RuntimeException('Could not create zip file.');
    }
    $files = new RecursiveIteratorIterator(
        new RecursiveDirectoryIterator($sourceDir, FilesystemIterator::SKIP_DOTS),
        RecursiveIteratorIterator::LEAVES_ONLY
    );
    foreach ($files as $file) {
        if (!$file->isFile()) continue;
        $zip->addFile($file->getPathname(), substr($file->getPathname(), strlen($sourceDir) + 1));
    }
    $zip->close();
}

function archive_directory(string $sourceDir, string $archivePath): string {
    try {
        zip_directory($sourceDir, $archivePath);
        return $archivePath;
    } catch (Throwable $zipError) {
        $tarPath = preg_replace('/\.zip$/', '.tar', $archivePath);
        $gzPath = $tarPath . '.gz';
        if (file_exists($tarPath)) unlink($tarPath);
        if (file_exists($gzPath)) unlink($gzPath);
        $tar = new PharData($tarPath);
        $tar->buildFromDirectory($sourceDir);
        $tar->compress(Phar::GZ);
        unset($tar);
        if (file_exists($tarPath)) unlink($tarPath);
        return $gzPath;
    }
}

$pdo = backup_db();
$backupRoot = dirname(__DIR__) . '/backups';
if (!is_dir($backupRoot)) mkdir($backupRoot, 0750, true);

$stamp = date('Y-m-d_His');
$workDir = "{$backupRoot}/grange-station-backup-{$stamp}";
mkdir($workDir, 0750, true);

$tables = [
    'booking_requests',
    'booking_request_addons',
    'confirmed_bookings',
    'availability_blocks',
    'contact_messages',
    'site_settings',
    'rental_addons',
    'vendor_categories',
    'preferred_vendors',
    'admin_users',
];

write_sql_dump($pdo, "{$workDir}/database.sql", $tables);
write_csv($pdo, "{$workDir}/booking_requests.csv", "
    SELECT r.id, r.public_id, r.status, r.request_type, r.contact_name, r.contact_email, r.contact_phone,
           r.requested_date, TIME_FORMAT(r.requested_start, '%H:%i') AS requested_start,
           TIME_FORMAT(r.requested_end, '%H:%i') AS requested_end, r.guest_count, r.event_type,
           r.space_preference, r.vendor_help, r.extra_hours, r.price_adjustment, r.amount_owed,
           GROUP_CONCAT(a.addon_name ORDER BY a.id SEPARATOR '; ') AS addons,
           r.message, r.created_at, r.updated_at
    FROM booking_requests r
    LEFT JOIN booking_request_addons a ON a.request_id = r.id
    GROUP BY r.id
    ORDER BY r.created_at DESC
");
write_csv($pdo, "{$workDir}/confirmed_bookings.csv", "
    SELECT id, request_id, title, event_date, TIME_FORMAT(start_time, '%H:%i') AS start_time,
           TIME_FORMAT(end_time, '%H:%i') AS end_time, guest_count, extra_hours, price_adjustment,
           amount_owed, status, private_notes, created_at, updated_at
    FROM confirmed_bookings
    ORDER BY event_date, start_time
");
write_csv($pdo, "{$workDir}/availability_blocks.csv", "
    SELECT id, title, block_date, TIME_FORMAT(start_time, '%H:%i') AS start_time,
           TIME_FORMAT(end_time, '%H:%i') AS end_time, reason, is_public, created_at, updated_at
    FROM availability_blocks
    ORDER BY block_date, start_time
");
write_csv($pdo, "{$workDir}/contact_messages.csv", "
    SELECT id, contact_name, contact_email, contact_phone, reason, message, created_at
    FROM contact_messages
    ORDER BY created_at DESC
");

$zipPath = archive_directory($workDir, "{$backupRoot}/grange-station-backup-{$stamp}.zip");

$sizeMb = round(filesize($zipPath) / 1024 / 1024, 2);
$body = "Grange Station backup created {$stamp}.\n\nAttached: " . basename($zipPath) . "\nSize: {$sizeMb} MB\nServer path: {$zipPath}\n";
$emailed = filesize($zipPath) <= 8 * 1024 * 1024 && send_backup_email('Grange Station booking backup ' . date('Y-m-d'), $body, $zipPath);

echo "Backup created: {$zipPath}\n";
echo "Email sent: " . ($emailed ? 'yes' : 'no') . "\n";