<?php

function url(string $path = ''): string
{
    return app_base_path() . ($path === '/' ? '/' : '/' . ltrim($path, '/'));
}

function redirect(string $path): void
{
    header('Location: ' . url($path));
    exit;
}

function current_path(): string
{
    $path = parse_url($_SERVER['REQUEST_URI'] ?? '/', PHP_URL_PATH) ?: '/';
    $basePath = app_base_path();

    if ($basePath !== '' && strpos($path, $basePath) === 0) {
        $path = substr($path, strlen($basePath));
    }

    return '/' . trim($path, '/');
}

function app_base_path(): string
{
    $requestPath = parse_url($_SERVER['REQUEST_URI'] ?? '/', PHP_URL_PATH) ?: '/';
    $scriptDir = str_replace('\\', '/', dirname($_SERVER['SCRIPT_NAME'] ?? ''));

    if ($scriptDir === '/' || $scriptDir === '.') {
        return '';
    }

    $scriptDir = rtrim($scriptDir, '/');

    if (strpos($requestPath, $scriptDir) === 0) {
        return $scriptDir;
    }

    return '';
}

function active_route(string $path): string
{
    $current = current_path();
    $path = '/' . trim($path, '/');

    if ($path === '/dashboard') {
        return $current === '/dashboard' ? 'active' : '';
    }

    return strpos($current, $path) === 0 ? 'active' : '';
}

function e(?string $value): string
{
    return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
}

function csrf_token(): string
{
    if (empty($_SESSION['_token'])) {
        $_SESSION['_token'] = bin2hex(random_bytes(32));
    }

    return $_SESSION['_token'];
}

function verify_csrf(): void
{
    $token = $_POST['_token'] ?? '';

    if (!is_string($token) || !hash_equals($_SESSION['_token'] ?? '', $token)) {
        http_response_code(403);
        exit('Session expiree. Retournez au formulaire et reessayez.');
    }
}

function valid_date(?string $date): bool
{
    if (!$date) {
        return false;
    }

    $parsed = DateTimeImmutable::createFromFormat('Y-m-d', $date);

    return $parsed instanceof DateTimeImmutable && $parsed->format('Y-m-d') === $date;
}

function valid_time(?string $time): bool
{
    $time = normalize_time_value($time);

    return $time === '' || preg_match('/^([01]\d|2[0-3]):[0-5]\d$/', $time) === 1;
}

function normalize_time_value(?string $time): string
{
    $time = trim((string) $time);

    if (preg_match('/^([01]\d|2[0-3]):([0-5]\d)(?::[0-5]\d)?$/', $time, $matches) === 1) {
        return $matches[1] . ':' . $matches[2];
    }

    return $time;
}

function flash(string $key, ?string $message = null): ?string
{
    if ($message !== null) {
        $_SESSION['flash'][$key] = $message;
        return null;
    }

    $value = $_SESSION['flash'][$key] ?? null;
    unset($_SESSION['flash'][$key]);

    return $value;
}

function permissions_list(): array
{
    return [
        'properties.view' => 'Voir les biens',
        'properties.create' => 'Ajouter des biens',
        'properties.edit' => 'Modifier des biens',
        'properties.delete' => 'Supprimer des biens',
        'reservations.view' => 'Voir les reservations',
        'reservations.create' => 'Creer des reservations',
        'reservations.edit' => 'Modifier des reservations',
        'reservations.delete' => 'Supprimer des reservations',
        'clients.view' => 'Voir les clients',
        'clients.create' => 'Ajouter des clients',
        'clients.edit' => 'Modifier des clients',
        'clients.delete' => 'Supprimer des clients',
        'payments.view' => 'Voir les paiements',
        'payments.create' => 'Ajouter des paiements',
        'payments.edit' => 'Modifier des paiements',
        'payments.delete' => 'Supprimer des paiements',
        'expenses.view' => 'Voir les depenses',
        'expenses.create' => 'Ajouter des depenses',
        'expenses.edit' => 'Modifier des depenses',
        'expenses.delete' => 'Supprimer des depenses',
        'long_rentals.view' => 'Voir location longue duree',
        'long_rentals.create' => 'Ajouter location longue duree',
        'long_rentals.edit' => 'Modifier location longue duree',
        'long_rentals.delete' => 'Supprimer location longue duree',
        'statistics.view' => 'Voir les statistiques',
        'users.view' => 'Voir les utilisateurs',
        'users.create' => 'Ajouter des utilisateurs',
        'users.edit' => 'Modifier des utilisateurs',
        'users.delete' => 'Supprimer des utilisateurs',
    ];
}

function selected_permissions(array $source): array
{
    $allowed = array_keys(permissions_list());

    return array_values(array_intersect($allowed, $source));
}

function property_types(): array
{
    return [
        'apartment' => 'Appartement',
        'studio' => 'Studio',
        'house' => 'Maison',
    ];
}

function property_statuses(): array
{
    return [
        'available' => 'Disponible',
        'occupied' => 'Occupe',
        'maintenance' => 'Maintenance',
    ];
}

function save_property_photos(int $propertyId): void
{
    if (empty($_FILES['photos']) || !is_array($_FILES['photos']['name'])) {
        return;
    }

    $uploadDir = dirname(__DIR__) . '/public/uploads/properties';

    if (!is_dir($uploadDir)) {
        mkdir($uploadDir, 0775, true);
    }

    $allowedTypes = ['image/jpeg' => 'jpg', 'image/png' => 'png', 'image/webp' => 'webp'];
    $fileCount = count($_FILES['photos']['name']);

    for ($i = 0; $i < $fileCount; $i++) {
        if ($_FILES['photos']['error'][$i] !== UPLOAD_ERR_OK) {
            continue;
        }

        $tmpPath = $_FILES['photos']['tmp_name'][$i];
        $mimeType = mime_content_type($tmpPath);

        if (!isset($allowedTypes[$mimeType])) {
            continue;
        }

        $filename = $propertyId . '-' . bin2hex(random_bytes(8)) . '.' . $allowedTypes[$mimeType];
        $targetPath = $uploadDir . '/' . $filename;

        if (move_uploaded_file($tmpPath, $targetPath)) {
            $statement = Database::connection()->prepare(
                'INSERT INTO property_photos (property_id, photo_path, is_main) VALUES (?, ?, ?)'
            );
            $statement->execute([
                $propertyId,
                'uploads/properties/' . $filename,
                property_has_photos($propertyId) ? 0 : 1,
            ]);
        }
    }
}

function property_has_photos(int $propertyId): bool
{
    $statement = Database::connection()->prepare('SELECT COUNT(*) FROM property_photos WHERE property_id = ?');
    $statement->execute([$propertyId]);

    return (int) $statement->fetchColumn() > 0;
}

function property_form_data(array $source): array
{
    return [
        'title' => trim((string) ($source['title'] ?? '')),
        'type' => array_key_exists($source['type'] ?? '', property_types()) ? (string) $source['type'] : 'apartment',
        'description' => trim((string) ($source['description'] ?? '')),
        'city' => trim((string) ($source['city'] ?? '')),
        'district' => trim((string) ($source['district'] ?? '')),
        'address' => trim((string) ($source['address'] ?? '')),
        'price_per_night' => max(0, (float) ($source['price_per_night'] ?? 0)),
        'status' => array_key_exists($source['status'] ?? '', property_statuses()) ? (string) $source['status'] : 'available',
        'bedrooms' => max(0, (int) ($source['bedrooms'] ?? 0)),
        'bathrooms' => max(0, (int) ($source['bathrooms'] ?? 0)),
        'max_guests' => max(1, (int) ($source['max_guests'] ?? 1)),
    ];
}

function validate_property(array $data): array
{
    $errors = [];

    if ($data['title'] === '') {
        $errors[] = 'Le titre est obligatoire.';
    }

    if ($data['city'] === '') {
        $errors[] = 'La ville est obligatoire.';
    }

    if ($data['price_per_night'] < 0) {
        $errors[] = 'Le prix doit etre positif.';
    }

    if ($data['max_guests'] < 1) {
        $errors[] = 'La capacite doit etre au minimum 1.';
    }

    return $errors;
}

function client_form_data(array $source): array
{
    return [
        'first_name' => trim((string) ($source['first_name'] ?? '')),
        'last_name' => trim((string) ($source['last_name'] ?? '')),
        'phone' => trim((string) ($source['phone'] ?? '')),
        'email' => trim((string) ($source['email'] ?? '')),
        'identity_number' => trim((string) ($source['identity_number'] ?? '')),
        'address' => trim((string) ($source['address'] ?? '')),
        'notes' => trim((string) ($source['notes'] ?? '')),
    ];
}

function validate_client(array $data): array
{
    $errors = [];

    if ($data['first_name'] === '') {
        $errors[] = 'Le prenom est obligatoire.';
    }

    if ($data['last_name'] === '') {
        $errors[] = 'Le nom est obligatoire.';
    }

    if ($data['email'] !== '' && !filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
        $errors[] = 'Email invalide.';
    }

    return $errors;
}

function reservation_statuses(): array
{
    return [
        'pending' => 'En attente',
        'confirmed' => 'Confirmee',
        'cancelled' => 'Annulee',
        'completed' => 'Terminee',
    ];
}

function reservation_form_data(array $source): array
{
    return [
        'property_id' => max(0, (int) ($source['property_id'] ?? 0)),
        'client_id' => max(0, (int) ($source['client_id'] ?? 0)),
        'agent_id' => max(0, (int) ($source['agent_id'] ?? 0)),
        'start_date' => trim((string) ($source['start_date'] ?? '')),
        'end_date' => trim((string) ($source['end_date'] ?? '')),
        'checkin_time' => normalize_time_value($source['checkin_time'] ?? ''),
        'checkout_time' => normalize_time_value($source['checkout_time'] ?? ''),
        'status' => array_key_exists($source['status'] ?? '', reservation_statuses()) ? (string) $source['status'] : 'pending',
        'source' => array_key_exists($source['source'] ?? '', reservation_sources()) ? (string) $source['source'] : 'private',
        'discount_amount' => max(0, (float) ($source['discount_amount'] ?? 0)),
        'notes' => trim((string) ($source['notes'] ?? '')),
    ];
}

function reservation_nights(string $startDate, string $endDate): int
{
    if (!valid_date($startDate) || !valid_date($endDate)) {
        return 0;
    }

    try {
        $start = new DateTimeImmutable($startDate);
        $end = new DateTimeImmutable($endDate);
    } catch (Throwable $e) {
        return 0;
    }

    if ($end <= $start) {
        return 0;
    }

    return max(0, (int) $start->diff($end)->format('%a'));
}

function validate_reservation(array $data, int $ignoreId = 0): array
{
    $errors = [];

    if ($data['property_id'] <= 0) {
        $errors[] = 'Le bien est obligatoire.';
    }

    if ($data['client_id'] <= 0) {
        $errors[] = 'Le client est obligatoire.';
    }

    if (!valid_date($data['start_date']) || !valid_date($data['end_date'])) {
        $errors[] = 'Les dates sont invalides.';
    }

    if (reservation_nights($data['start_date'], $data['end_date']) < 1) {
        $errors[] = 'La date de depart doit etre apres la date arrivee.';
    }

    if (!valid_time($data['checkin_time']) || !valid_time($data['checkout_time'])) {
        $errors[] = 'Les heures doivent etre au format HH:MM.';
    }

    if (!$errors && reservation_has_overlap($data['property_id'], $data['start_date'], $data['end_date'], $ignoreId)) {
        $errors[] = 'Ce bien est deja reserve sur cette periode.';
    }

    return $errors;
}

function reservation_has_overlap(int $propertyId, string $startDate, string $endDate, int $ignoreId = 0): bool
{
    $sql = 'SELECT COUNT(*) FROM reservations
        WHERE property_id = ?
        AND status IN ("pending", "confirmed")
        AND start_date < ?
        AND end_date > ?';
    $params = [$propertyId, $endDate, $startDate];

    if ($ignoreId > 0) {
        $sql .= ' AND id != ?';
        $params[] = $ignoreId;
    }

    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);

    return (int) $statement->fetchColumn() > 0;
}

function reservation_total_amount(int $propertyId, string $startDate, string $endDate): float
{
    $statement = Database::connection()->prepare('SELECT price_per_night FROM properties WHERE id = ? LIMIT 1');
    $statement->execute([$propertyId]);
    $price = (float) $statement->fetchColumn();

    return $price * reservation_nights($startDate, $endDate);
}

function calendar_range(string $view, string $date): array
{
    try {
        $current = new DateTimeImmutable($date);
    } catch (Throwable $e) {
        $current = new DateTimeImmutable('today');
    }

    if ($view === 'day') {
        return [$current, $current];
    }

    if ($view === 'week') {
        return [
            $current->modify('monday this week'),
            $current->modify('sunday this week'),
        ];
    }

    return [
        $current->modify('first day of this month'),
        $current->modify('last day of this month'),
    ];
}

function calendar_navigation_date(string $view, string $date, string $direction): string
{
    try {
        $current = new DateTimeImmutable($date);
    } catch (Throwable $e) {
        $current = new DateTimeImmutable('today');
    }

    if ($view === 'day') {
        $modifier = $direction === 'next' ? '+1 day' : '-1 day';
    } elseif ($view === 'week') {
        $modifier = $direction === 'next' ? '+1 week' : '-1 week';
    } else {
        $modifier = $direction === 'next' ? '+1 month' : '-1 month';
    }

    return $current->modify($modifier)->format('Y-m-d');
}

function calendar_days(DateTimeImmutable $start, DateTimeImmutable $end): array
{
    $days = [];
    $cursor = $start;

    while ($cursor <= $end) {
        $days[] = $cursor;
        $cursor = $cursor->modify('+1 day');
    }

    return $days;
}

function payment_statuses(): array
{
    return [
        'pending' => 'Non paye',
        'partial' => 'Partiellement paye',
        'paid' => 'Paye',
        'cancelled' => 'Annule',
    ];
}

function payment_methods(): array
{
    return [
        'cash' => 'Especes',
        'bank_transfer' => 'Virement bancaire',
        'card' => 'Carte',
        'mobile_money' => 'Mobile money',
        'other' => 'Autre',
    ];
}

function payment_form_data(array $source): array
{
    return [
        'reservation_id' => max(0, (int) ($source['reservation_id'] ?? 0)),
        'amount' => max(0, (float) ($source['amount'] ?? 0)),
        'payment_method' => array_key_exists($source['payment_method'] ?? '', payment_methods()) ? (string) $source['payment_method'] : 'cash',
        'status' => array_key_exists($source['status'] ?? '', payment_statuses()) ? (string) $source['status'] : 'pending',
        'payment_date' => trim((string) ($source['payment_date'] ?? '')),
        'notes' => trim((string) ($source['notes'] ?? '')),
    ];
}

function validate_payment(array $data, int $ignoreId = 0): array
{
    $errors = [];

    if ($data['reservation_id'] <= 0) {
        $errors[] = 'La reservation est obligatoire.';
    }

    if ($data['amount'] <= 0) {
        $errors[] = 'Le montant doit etre superieur a zero.';
    }

    if ($data['payment_date'] !== '' && !valid_date($data['payment_date'])) {
        $errors[] = 'La date de paiement est invalide.';
    }

    if (!$errors && $data['status'] !== 'cancelled') {
        $statement = Database::connection()->prepare('SELECT total_amount, final_amount FROM reservations WHERE id = ? LIMIT 1');
        $statement->execute([$data['reservation_id']]);
        $reservation = $statement->fetch();

        if (!$reservation) {
            $errors[] = 'Reservation introuvable.';
        } else {
            $sql = 'SELECT COALESCE(SUM(amount), 0) FROM payments WHERE reservation_id = ? AND status != "cancelled"';
            $params = [$data['reservation_id']];

            if ($ignoreId > 0) {
                $sql .= ' AND id != ?';
                $params[] = $ignoreId;
            }

            $sumStatement = Database::connection()->prepare($sql);
            $sumStatement->execute($params);
            $alreadyPaid = (float) $sumStatement->fetchColumn();
            $reservationTotal = (float) (($reservation['final_amount'] ?? 0) ?: ($reservation['total_amount'] ?? 0));

            if (($alreadyPaid + $data['amount']) > ($reservationTotal + 0.001)) {
                $errors[] = 'Le total des paiements depasse le montant de la reservation.';
            }
        }
    }

    return $errors;
}

function reservation_sources(): array
{
    return [
        'airbnb' => 'Airbnb',
        'booking' => 'Booking',
        'private' => 'Reservation privee',
        'website' => 'Site web',
        'other' => 'Autre',
    ];
}

function payment_status_from_amounts(float $paid, float $remaining): string
{
    if ($remaining <= 0.001) {
        return 'paid';
    }

    return $paid > 0 ? 'partial' : 'pending';
}

function refresh_reservation_financials(int $reservationId): void
{
    $statement = Database::connection()->prepare('SELECT final_amount, total_amount FROM reservations WHERE id = ? LIMIT 1');
    $statement->execute([$reservationId]);
    $reservation = $statement->fetch();

    if (!$reservation) {
        return;
    }

    $paidStatement = Database::connection()->prepare('SELECT COALESCE(SUM(amount), 0) FROM payments WHERE reservation_id = ? AND status != "cancelled"');
    $paidStatement->execute([$reservationId]);
    $paid = (float) $paidStatement->fetchColumn();
    $final = (float) ($reservation['final_amount'] ?: $reservation['total_amount']);
    $remaining = max(0, $final - $paid);
    $status = payment_status_from_amounts($paid, $remaining);

    $update = Database::connection()->prepare('UPDATE reservations SET amount_paid = ?, amount_remaining = ?, payment_status = ? WHERE id = ?');
    $update->execute([$paid, $remaining, $status, $reservationId]);
}

function expense_categories(): array
{
    return [
        'salaries' => 'Salaires',
        'electricity' => 'Electricite',
        'water' => 'Eau',
        'internet' => 'Internet',
        'advertising' => 'Publicite',
        'cleaning' => 'Menage',
        'maintenance' => 'Reparations/Maintenance',
        'other' => 'Autres charges',
    ];
}

function expense_form_data(array $source): array
{
    return [
        'expense_date' => trim((string) ($source['expense_date'] ?? date('Y-m-d'))),
        'category' => array_key_exists($source['category'] ?? '', expense_categories()) ? (string) $source['category'] : 'other',
        'amount' => max(0, (float) ($source['amount'] ?? 0)),
        'description' => trim((string) ($source['description'] ?? '')),
        'property_id' => max(0, (int) ($source['property_id'] ?? 0)),
    ];
}

function validate_expense(array $data): array
{
    $errors = [];

    if (!valid_date($data['expense_date'])) {
        $errors[] = 'La date est invalide.';
    }

    if ($data['amount'] <= 0) {
        $errors[] = 'Le montant doit etre superieur a zero.';
    }

    return $errors;
}

function long_contract_statuses(): array
{
    return [
        'active' => 'Actif',
        'finished' => 'Termine',
        'terminated' => 'Resilie',
    ];
}

function long_payment_statuses(): array
{
    return [
        'unpaid' => 'Non paye',
        'partial' => 'Partiel',
        'paid' => 'Paye',
    ];
}

function month_value(?string $value): string
{
    $value = trim((string) $value);
    return preg_match('/^\d{4}-\d{2}$/', $value) === 1 ? $value : date('Y-m');
}
function period_dates(array $source): array
{
    $from = trim((string) ($source['from'] ?? ''));
    $to = trim((string) ($source['to'] ?? ''));

    if ($from === '') {
        $from = date('Y-m-01');
    }

    if ($to === '') {
        $to = date('Y-m-t');
    }

    if (!valid_date($from)) {
        $from = date('Y-m-01');
    }

    if (!valid_date($to)) {
        $to = date('Y-m-t');
    }

    if ($from > $to) {
        [$from, $to] = [$to, $from];
    }

    return [$from, $to];
}

function days_between_inclusive(string $from, string $to): int
{
    try {
        $start = new DateTimeImmutable($from);
        $end = new DateTimeImmutable($to);
    } catch (Throwable $e) {
        return 1;
    }

    return max(1, ((int) $start->diff($end)->format('%a')) + 1);
}

function paginate_items(array $items, int $perPage = 15, string $pageParam = 'page'): array
{
    $totalItems = count($items);
    $perPage = max(1, $perPage);
    $totalPages = max(1, (int) ceil($totalItems / $perPage));
    $currentPage = max(1, min($totalPages, (int) ($_GET[$pageParam] ?? 1)));
    $offset = ($currentPage - 1) * $perPage;

    return [
        array_slice($items, $offset, $perPage),
        [
            'current_page' => $currentPage,
            'total_pages' => $totalPages,
            'total_items' => $totalItems,
            'per_page' => $perPage,
            'page_param' => $pageParam,
        ],
    ];
}

function pagination_url(int $page, array $pagination): string
{
    $query = $_GET;
    $query[$pagination['page_param'] ?? 'page'] = $page;
    $query = array_filter($query, static fn ($value): bool => $value !== '' && $value !== null);

    return url(current_path()) . ($query ? '?' . http_build_query($query) : '');
}

function notification_types(): array
{
    return [
        'new_reservation' => 'Nouvelle reservation',
        'arrival_today' => 'Arrivee aujourd hui',
        'arrival_tomorrow' => 'Arrivee demain',
        'arrival_upcoming' => 'Arrivee prevue',
        'departure_today' => 'Depart aujourd hui',
        'departure_tomorrow' => 'Depart demain',
        'departure_upcoming' => 'Depart prevu',
        'pending_payment' => 'Paiement en attente',
        'overdue_payment' => 'Paiement en retard',
        'remaining_balance' => 'Solde restant a payer',
        'important_alert' => 'Alerte importante',
    ];
}

function notification_settings(): array
{
    try {
        $settings = Database::connection()
            ->query('SELECT * FROM notification_settings WHERE id = 1')
            ->fetch();
    } catch (Throwable $e) {
        $settings = null;
    }

    return $settings ?: [
        'enabled' => 1,
        'arrival_days_before' => 1,
        'alert_new_reservation' => 1,
        'alert_arrival' => 1,
        'alert_departure' => 1,
        'alert_overdue_payment' => 1,
        'alert_remaining_balance' => 1,
        'email_enabled' => 0,
        'email_recipients' => '',
        'email_from_name' => 'Locative',
        'email_from_address' => '',
    ];
}

function notification_email_recipients(array $settings): array
{
    $recipients = [];
    $rawRecipients = preg_split('/[,;\r\n]+/', (string) ($settings['email_recipients'] ?? '')) ?: [];

    foreach ($rawRecipients as $recipient) {
        $recipient = trim($recipient);
        if ($recipient !== '' && filter_var($recipient, FILTER_VALIDATE_EMAIL)) {
            $recipients[$recipient] = true;
        }
    }

    if (!$recipients) {
        try {
            $rows = Database::connection()
                ->query('SELECT email FROM users WHERE role = "admin" AND status = "active" AND email <> ""')
                ->fetchAll();
            foreach ($rows as $row) {
                if (filter_var($row['email'], FILTER_VALIDATE_EMAIL)) {
                    $recipients[$row['email']] = true;
                }
            }
        } catch (Throwable $e) {
            return [];
        }
    }

    return array_keys($recipients);
}

function notification_from_address(array $settings): string
{
    $from = trim((string) ($settings['email_from_address'] ?? ''));
    if ($from !== '' && filter_var($from, FILTER_VALIDATE_EMAIL)) {
        return $from;
    }

    try {
        $companyEmail = (string) Database::connection()
            ->query('SELECT setting_value FROM app_settings WHERE setting_key = "company_email" LIMIT 1')
            ->fetchColumn();
        if (filter_var($companyEmail, FILTER_VALIDATE_EMAIL)) {
            return $companyEmail;
        }
    } catch (Throwable $e) {
        return '';
    }

    return '';
}

function send_notification_email(string $title, string $message): bool
{
    $settings = notification_settings();
    if ((int) ($settings['enabled'] ?? 1) !== 1 || (int) ($settings['email_enabled'] ?? 0) !== 1) {
        return false;
    }

    $recipients = notification_email_recipients($settings);
    if (!$recipients) {
        return false;
    }

    $from = notification_from_address($settings);
    $fromName = trim((string) ($settings['email_from_name'] ?? 'Locative')) ?: 'Locative';
    $subject = 'Locative - ' . $title;
    $body = $title . "\n\n" . $message . "\n\n" . 'Date: ' . date('Y-m-d H:i');
    $headers = [
        'MIME-Version: 1.0',
        'Content-Type: text/plain; charset=UTF-8',
    ];

    if ($from !== '') {
        $headers[] = 'From: ' . str_replace(["\r", "\n"], '', $fromName) . ' <' . $from . '>';
        $headers[] = 'Reply-To: ' . $from;
    }

    $sent = false;
    foreach ($recipients as $recipient) {
        $sent = @mail($recipient, $subject, wordwrap($body, 78), implode("\r\n", $headers)) || $sent;
    }

    return $sent;
}

function ensure_notification(string $type, string $title, string $message, int $relatedId, string $relatedType): void
{
    $statement = Database::connection()->prepare(
        'SELECT COUNT(*) FROM notifications WHERE type = ? AND related_id = ? AND related_type = ?'
    );
    $statement->execute([$type, $relatedId, $relatedType]);

    if ((int) $statement->fetchColumn() > 0) {
        return;
    }

    $insert = Database::connection()->prepare(
        'INSERT INTO notifications (user_id, type, title, message, is_read, related_id, related_type)
        VALUES (NULL, ?, ?, ?, 0, ?, ?)'
    );
    $insert->execute([$type, $title, $message, $relatedId, $relatedType]);
    send_notification_email($title, $message);
}

function sync_notifications(): void
{
    $settings = notification_settings();

    if ((int) ($settings['enabled'] ?? 1) !== 1) {
        return;
    }

    $today = date('Y-m-d');
    $tomorrow = date('Y-m-d', strtotime('+1 day'));
    $arrivalDaysBefore = max(0, min(365, (int) ($settings['arrival_days_before'] ?? 1)));
    $arrivalLimit = date('Y-m-d', strtotime('+' . $arrivalDaysBefore . ' days'));

    $reservations = Database::connection()
        ->query('SELECT r.*, p.title AS property_title, c.first_name, c.last_name
            FROM reservations r
            JOIN properties p ON p.id = r.property_id
            JOIN clients c ON c.id = r.client_id
            WHERE r.status IN ("pending", "confirmed")
            ORDER BY r.id DESC')
        ->fetchAll();

    foreach ($reservations as $reservation) {
        $clientName = $reservation['first_name'] . ' ' . $reservation['last_name'];

        if ((int) ($settings['alert_new_reservation'] ?? 1) === 1) {
            ensure_notification(
                'new_reservation',
                'Nouvelle reservation',
                $reservation['property_title'] . ' - ' . $clientName,
                (int) $reservation['id'],
                'reservation'
            );
        }

        if ((int) ($settings['alert_arrival'] ?? 1) === 1) {
            if ($reservation['start_date'] === $today) {
                ensure_notification('arrival_today', 'Arrivee aujourd hui', $clientName . ' arrive a ' . $reservation['property_title'], (int) $reservation['id'], 'reservation');
            }

            if ($arrivalDaysBefore >= 1 && $reservation['start_date'] === $tomorrow) {
                ensure_notification('arrival_tomorrow', 'Arrivee demain', $clientName . ' arrive demain a ' . $reservation['property_title'], (int) $reservation['id'], 'reservation');
            }

            if ($arrivalDaysBefore > 1 && $reservation['start_date'] > $tomorrow && $reservation['start_date'] <= $arrivalLimit) {
                ensure_notification('arrival_upcoming', 'Arrivee prevue', $clientName . ' arrive le ' . $reservation['start_date'] . ' a ' . $reservation['property_title'], (int) $reservation['id'], 'reservation');
            }
        }

        if ((int) ($settings['alert_departure'] ?? 1) === 1) {
            if ($reservation['end_date'] === $today) {
                ensure_notification('departure_today', 'Depart aujourd hui', $clientName . ' quitte ' . $reservation['property_title'], (int) $reservation['id'], 'reservation');
            }

            if ($reservation['end_date'] === $tomorrow) {
                ensure_notification('departure_tomorrow', 'Depart demain', $clientName . ' quitte demain ' . $reservation['property_title'], (int) $reservation['id'], 'reservation');
            }
        }

        if ((int) ($settings['alert_remaining_balance'] ?? 1) === 1 && (float) ($reservation['amount_remaining'] ?? 0) > 0) {
            ensure_notification('remaining_balance', 'Solde restant a payer', $clientName . ' - ' . $reservation['property_title'] . ' - reste ' . number_format((float) $reservation['amount_remaining'], 2, ',', ' '), (int) $reservation['id'], 'reservation');
        }

        if ((int) ($settings['alert_overdue_payment'] ?? 1) === 1 && $reservation['end_date'] < $today && (float) ($reservation['amount_remaining'] ?? 0) > 0) {
            ensure_notification('overdue_payment', 'Paiement en retard', $clientName . ' - ' . $reservation['property_title'] . ' - depart le ' . $reservation['end_date'], (int) $reservation['id'], 'reservation');
        }
    }

    if ((int) ($settings['alert_overdue_payment'] ?? 1) === 1) {
        $payments = Database::connection()
            ->query('SELECT pay.*, p.title AS property_title, c.first_name, c.last_name
                FROM payments pay
                JOIN properties p ON p.id = pay.property_id
                JOIN clients c ON c.id = pay.client_id
                WHERE pay.status IN ("pending", "partial")
                ORDER BY pay.id DESC')
            ->fetchAll();

        foreach ($payments as $payment) {
            $type = $payment['payment_date'] && $payment['payment_date'] < $today ? 'overdue_payment' : 'pending_payment';
            ensure_notification(
                $type,
                $type === 'overdue_payment' ? 'Paiement en retard' : 'Paiement en attente',
                $payment['first_name'] . ' ' . $payment['last_name'] . ' - ' . $payment['property_title'] . ' - ' . number_format((float) $payment['amount'], 2, ',', ' '),
                (int) $payment['id'],
                'payment'
            );
        }
    }
}

function sync_long_term_unpaid_months(): int
{
    $contracts = Database::connection()
        ->query('SELECT id, start_date, end_date, monthly_rent FROM long_term_contracts WHERE status = "active" ORDER BY id')
        ->fetchAll();
    $created = 0;
    $insert = Database::connection()->prepare(
        'INSERT IGNORE INTO long_term_payments (contract_id, paid_month, due_amount, amount_paid, amount_remaining, status)
        VALUES (?, ?, ?, 0, ?, "unpaid")'
    );

    foreach ($contracts as $contract) {
        try {
            $cursor = new DateTimeImmutable(substr($contract['start_date'], 0, 7) . '-01');
            $end = new DateTimeImmutable(date('Y-m-01'));

            if (!empty($contract['end_date']) && $contract['end_date'] < $end->format('Y-m-d')) {
                $end = new DateTimeImmutable(substr($contract['end_date'], 0, 7) . '-01');
            }
        } catch (Throwable $e) {
            continue;
        }

        while ($cursor <= $end) {
            $month = $cursor->format('Y-m');
            $insert->execute([(int) $contract['id'], $month, (float) $contract['monthly_rent'], (float) $contract['monthly_rent']]);
            $created += $insert->rowCount();
            $cursor = $cursor->modify('+1 month');
        }
    }

    return $created;
}

function log_activity(string $action, string $module, string $description): void
{
    $statement = Database::connection()->prepare(
        'INSERT INTO activity_logs (user_id, action, module, description) VALUES (?, ?, ?, ?)'
    );
    $statement->execute([
        $_SESSION['user_id'] ?? null,
        $action,
        $module,
        $description,
    ]);
}

function export_definitions(): array
{
    return [
        'properties' => [
            'label' => 'Biens',
            'filename' => 'biens.csv',
            'permission' => 'properties.view',
            'headers' => ['ID', 'Titre', 'Type', 'Ville', 'Quartier', 'Prix nuit', 'Statut', 'Capacite'],
            'sql' => 'SELECT id, title, type, city, district, price_per_night, status, max_guests FROM properties ORDER BY id DESC',
        ],
        'clients' => [
            'label' => 'Clients',
            'filename' => 'clients.csv',
            'permission' => 'clients.view',
            'headers' => ['ID', 'Prenom', 'Nom', 'Telephone', 'Email', 'Identite', 'Adresse'],
            'sql' => 'SELECT id, first_name, last_name, phone, email, identity_number, address FROM clients ORDER BY id DESC',
        ],
        'reservations' => [
            'label' => 'Reservations',
            'filename' => 'reservations.csv',
            'permission' => 'reservations.view',
            'headers' => ['ID', 'Bien', 'Client', 'Arrivee', 'Depart', 'Statut', 'Montant'],
            'sql' => 'SELECT r.id, p.title, CONCAT(c.first_name, " ", c.last_name), r.start_date, r.end_date, r.status, r.total_amount
                FROM reservations r
                JOIN properties p ON p.id = r.property_id
                JOIN clients c ON c.id = r.client_id
                ORDER BY r.id DESC',
        ],
        'payments' => [
            'label' => 'Paiements',
            'filename' => 'paiements.csv',
            'permission' => 'payments.view',
            'headers' => ['ID', 'Reservation', 'Client', 'Bien', 'Montant', 'Mode', 'Statut', 'Date'],
            'sql' => 'SELECT pay.id, pay.reservation_id, CONCAT(c.first_name, " ", c.last_name), p.title, pay.amount, pay.payment_method, pay.status, pay.payment_date
                FROM payments pay
                JOIN clients c ON c.id = pay.client_id
                JOIN properties p ON p.id = pay.property_id
                ORDER BY pay.id DESC',
        ],
    ];
}

function stream_csv(string $filename, array $headers, array $rows): void
{
    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');

    $output = fopen('php://output', 'w');
    fwrite($output, "\xEF\xBB\xBF");
    fputcsv($output, $headers, ';');

    foreach ($rows as $row) {
        fputcsv($output, array_values($row), ';');
    }

    fclose($output);
    exit;
}

function backup_tables(): array
{
    return [
        'app_settings',
        'users',
        'properties',
        'property_photos',
        'clients',
        'reservations',
        'payments',
        'notifications',
        'activity_logs',
    ];
}

function default_settings(): array
{
    return [
        'company_name' => 'Locative',
        'company_phone' => '',
        'company_email' => '',
        'company_address' => '',
        'company_city' => '',
        'booking_sync_token' => '',
    ];
}

function app_settings(): array
{
    $settings = default_settings();

    try {
        $rows = Database::connection()->query('SELECT setting_key, setting_value FROM app_settings')->fetchAll();

        foreach ($rows as $row) {
            if (array_key_exists($row['setting_key'], $settings)) {
                $settings[$row['setting_key']] = (string) $row['setting_value'];
            }
        }
    } catch (Throwable $e) {
        return $settings;
    }

    return $settings;
}

function save_app_settings(array $settings): void
{
    $allowed = default_settings();
    $statement = Database::connection()->prepare(
        'INSERT INTO app_settings (setting_key, setting_value) VALUES (?, ?)
        ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)'
    );

    foreach ($allowed as $key => $default) {
        $statement->execute([$key, trim((string) ($settings[$key] ?? $default))]);
    }
}

function ensure_booking_sync_schema(): void
{
    $connection = Database::connection();
    $connection->exec(
        'CREATE TABLE IF NOT EXISTS booking_sync_feeds (
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            property_id INT UNSIGNED NOT NULL,
            source ENUM("booking", "airbnb") NOT NULL DEFAULT "booking",
            feed_url TEXT NOT NULL,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            last_synced_at DATETIME NULL,
            last_status ENUM("never", "success", "error") NOT NULL DEFAULT "never",
            last_message TEXT NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_booking_sync_property_source (property_id, source),
            CONSTRAINT fk_booking_sync_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $feedColumns = [];
    $feedRows = $connection->query('SHOW COLUMNS FROM booking_sync_feeds')->fetchAll();
    foreach ($feedRows as $row) {
        $feedColumns[] = $row['Field'];
    }

    if (!in_array('source', $feedColumns, true)) {
        $connection->exec('ALTER TABLE booking_sync_feeds ADD COLUMN source ENUM("booking", "airbnb") NOT NULL DEFAULT "booking" AFTER property_id');
    }

    $columns = [];
    $rows = $connection->query('SHOW COLUMNS FROM reservations')->fetchAll();
    foreach ($rows as $row) {
        $columns[] = $row['Field'];
    }

    if (!in_array('external_source', $columns, true)) {
        $connection->exec('ALTER TABLE reservations ADD COLUMN external_source VARCHAR(40) NULL AFTER source');
    }

    if (!in_array('external_uid', $columns, true)) {
        $connection->exec('ALTER TABLE reservations ADD COLUMN external_uid VARCHAR(190) NULL AFTER external_source');
    }

    $indexes = [];
    $feedIndexes = [];
    $feedIndexRows = $connection->query('SHOW INDEX FROM booking_sync_feeds')->fetchAll();
    foreach ($feedIndexRows as $row) {
        $feedIndexes[] = $row['Key_name'];
    }

    if (in_array('uq_booking_sync_property', $feedIndexes, true)) {
        $connection->exec('ALTER TABLE booking_sync_feeds DROP INDEX uq_booking_sync_property');
    }

    if (!in_array('uq_booking_sync_property_source', $feedIndexes, true)) {
        $connection->exec('ALTER TABLE booking_sync_feeds ADD UNIQUE KEY uq_booking_sync_property_source (property_id, source)');
    }

    $indexRows = $connection->query('SHOW INDEX FROM reservations')->fetchAll();
    foreach ($indexRows as $row) {
        $indexes[] = $row['Key_name'];
    }

    if (!in_array('idx_reservations_external', $indexes, true)) {
        $connection->exec('CREATE INDEX idx_reservations_external ON reservations (external_source, external_uid)');
    }
}

function booking_sync_token(): string
{
    ensure_booking_sync_schema();
    $settings = app_settings();
    $token = trim((string) ($settings['booking_sync_token'] ?? ''));

    if ($token !== '') {
        return $token;
    }

    $token = bin2hex(random_bytes(24));
    $statement = Database::connection()->prepare(
        'INSERT INTO app_settings (setting_key, setting_value) VALUES (?, ?)
        ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)'
    );
    $statement->execute(['booking_sync_token', $token]);

    return $token;
}

function booking_sync_feeds(): array
{
    ensure_booking_sync_schema();

    $rows = Database::connection()
        ->query('SELECT p.id AS property_id, p.title, f.source, f.feed_url, f.is_active, f.last_synced_at, f.last_status, f.last_message
            FROM properties p
            LEFT JOIN booking_sync_feeds f ON f.property_id = p.id
            ORDER BY p.title')
        ->fetchAll();
    $feeds = [];

    foreach ($rows as $row) {
        $propertyId = (int) $row['property_id'];
        if (!isset($feeds[$propertyId])) {
            $feeds[$propertyId] = [
                'property_id' => $propertyId,
                'title' => $row['title'],
                'sources' => [],
            ];
        }

        if (!empty($row['source'])) {
            $feeds[$propertyId]['sources'][$row['source']] = [
                'feed_url' => $row['feed_url'] ?? '',
                'is_active' => (int) ($row['is_active'] ?? 1),
                'last_synced_at' => $row['last_synced_at'] ?? '',
                'last_status' => $row['last_status'] ?? 'never',
                'last_message' => $row['last_message'] ?? '',
            ];
        }
    }

    return array_values($feeds);
}

function save_booking_sync_feeds(array $source): void
{
    ensure_booking_sync_schema();
    $connection = Database::connection();
    $properties = $connection->query('SELECT id FROM properties')->fetchAll();
    $upsert = $connection->prepare(
        'INSERT INTO booking_sync_feeds (property_id, source, feed_url, is_active)
        VALUES (?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE feed_url = VALUES(feed_url), is_active = VALUES(is_active)'
    );
    $delete = $connection->prepare('DELETE FROM booking_sync_feeds WHERE property_id = ? AND source = ?');

    foreach ($properties as $property) {
        $propertyId = (int) $property['id'];
        foreach (channel_sync_sources() as $sourceKey => $sourceLabel) {
            $feedUrl = trim((string) ($source['feed_url'][$sourceKey][$propertyId] ?? ''));
            $isActive = isset($source['is_active'][$sourceKey][$propertyId]) ? 1 : 0;

            if ($feedUrl === '') {
                $delete->execute([$propertyId, $sourceKey]);
                continue;
            }

            $upsert->execute([$propertyId, $sourceKey, $feedUrl, $isActive]);
        }
    }
}

function channel_sync_sources(): array
{
    return [
        'booking' => 'Booking.com',
        'airbnb' => 'Airbnb',
    ];
}

function parse_ical_events(string $content): array
{
    $content = str_replace(["\r\n", "\r"], "\n", $content);
    $rawLines = explode("\n", $content);
    $lines = [];

    foreach ($rawLines as $line) {
        if (($line[0] ?? '') === ' ' || ($line[0] ?? '') === "\t") {
            $lastIndex = count($lines) - 1;
            if ($lastIndex >= 0) {
                $lines[$lastIndex] .= substr($line, 1);
            }
            continue;
        }

        $lines[] = $line;
    }

    $events = [];
    $event = null;

    foreach ($lines as $line) {
        $line = trim($line);
        if ($line === 'BEGIN:VEVENT') {
            $event = [];
            continue;
        }

        if ($line === 'END:VEVENT') {
            if (is_array($event)) {
                $events[] = $event;
            }
            $event = null;
            continue;
        }

        if (!is_array($event) || strpos($line, ':') === false) {
            continue;
        }

        [$name, $value] = explode(':', $line, 2);
        $name = strtoupper(strtok($name, ';') ?: $name);
        $event[$name] = ical_text_value($value);
    }

    return $events;
}

function ical_text_value(string $value): string
{
    return str_replace(['\\n', '\\,', '\\;', '\\\\'], ["\n", ',', ';', '\\'], trim($value));
}

function ical_date_value(?string $value): string
{
    $value = trim((string) $value);

    if (preg_match('/^(\d{4})(\d{2})(\d{2})/', $value, $matches) !== 1) {
        return '';
    }

    return $matches[1] . '-' . $matches[2] . '-' . $matches[3];
}

function channel_guest_names(array $event, string $source): array
{
    $summary = trim((string) ($event['SUMMARY'] ?? ''));
    $summary = preg_replace('/\s+/', ' ', $summary) ?: '';
    $summary = trim(str_ireplace(['Booking.com', 'Airbnb', 'Reservation', 'Reserved', 'Booked'], '', $summary), " -:#\t\n\r\0\x0B");
    $defaultFirstName = $source === 'airbnb' ? 'Airbnb' : 'Booking';

    if ($summary === '' || stripos($summary, 'not available') !== false || stripos($summary, 'closed') !== false) {
        return [$defaultFirstName, 'Guest'];
    }

    $parts = preg_split('/\s+/', $summary) ?: [];
    $firstName = array_shift($parts) ?: $defaultFirstName;
    $lastName = trim(implode(' ', $parts)) ?: 'Guest';

    return [$firstName, $lastName];
}

function find_or_create_channel_client(array $event, string $source): int
{
    [$firstName, $lastName] = channel_guest_names($event, $source);
    $sourceLabel = channel_sync_sources()[$source] ?? ucfirst($source);
    $notes = 'Client cree automatiquement depuis ' . $sourceLabel . ' iCal.';
    $statement = Database::connection()->prepare(
        'SELECT id FROM clients WHERE first_name = ? AND last_name = ? AND notes = ? LIMIT 1'
    );
    $statement->execute([$firstName, $lastName, $notes]);
    $clientId = (int) $statement->fetchColumn();

    if ($clientId > 0) {
        return $clientId;
    }

    $insert = Database::connection()->prepare(
        'INSERT INTO clients (first_name, last_name, notes) VALUES (?, ?, ?)'
    );
    $insert->execute([$firstName, $lastName, $notes]);

    return (int) Database::connection()->lastInsertId();
}

function sync_booking_reservations(?int $onlyPropertyId = null): array
{
    ensure_booking_sync_schema();
    $connection = Database::connection();
    $sql = 'SELECT f.*, p.title, p.price_per_night
        FROM booking_sync_feeds f
        JOIN properties p ON p.id = f.property_id
        WHERE f.is_active = 1 AND f.feed_url != ""';
    $params = [];

    if ($onlyPropertyId !== null && $onlyPropertyId > 0) {
        $sql .= ' AND f.property_id = ?';
        $params[] = $onlyPropertyId;
    }

    $statement = $connection->prepare($sql);
    $statement->execute($params);
    $feeds = $statement->fetchAll();
    $summary = ['feeds' => count($feeds), 'created' => 0, 'updated' => 0, 'skipped' => 0, 'errors' => []];

    foreach ($feeds as $feed) {
        $propertyId = (int) $feed['property_id'];
        $source = array_key_exists($feed['source'] ?? '', channel_sync_sources()) ? (string) $feed['source'] : 'booking';
        $sourceLabel = channel_sync_sources()[$source];
        $message = '';

        try {
            $content = fetch_channel_feed((string) $feed['feed_url'], $source);
            $events = parse_ical_events($content);
            $created = 0;
            $updated = 0;
            $skipped = 0;

            foreach ($events as $event) {
                $uid = trim((string) ($event['UID'] ?? ''));
                $startDate = ical_date_value($event['DTSTART'] ?? '');
                $endDate = ical_date_value($event['DTEND'] ?? '');

                if ($uid === '' || !valid_date($startDate) || !valid_date($endDate) || reservation_nights($startDate, $endDate) < 1) {
                    $skipped++;
                    continue;
                }

                $existing = $connection->prepare('SELECT id FROM reservations WHERE property_id = ? AND external_source = ? AND external_uid = ? LIMIT 1');
                $existing->execute([$propertyId, $source, $uid]);
                $reservationId = (int) $existing->fetchColumn();
                $totalAmount = reservation_total_amount($propertyId, $startDate, $endDate);
                $notes = channel_event_notes($event, $feed['title'], $source);

                if ($reservationId > 0) {
                    $update = $connection->prepare(
                        'UPDATE reservations SET property_id = ?, start_date = ?, end_date = ?, status = "confirmed",
                        source = ?, total_amount = ?, gross_amount = ?, final_amount = ?, amount_remaining = ?, notes = ? WHERE id = ?'
                    );
                    $update->execute([$propertyId, $startDate, $endDate, $source, $totalAmount, $totalAmount, $totalAmount, $totalAmount, $notes, $reservationId]);
                    refresh_reservation_financials($reservationId);
                    $updated++;
                    continue;
                }

                if (reservation_has_overlap($propertyId, $startDate, $endDate)) {
                    $skipped++;
                    continue;
                }

                $clientId = find_or_create_channel_client($event, $source);
                $insert = $connection->prepare(
                    'INSERT INTO reservations (property_id, client_id, user_id, agent_id, start_date, end_date, status, source, external_source, external_uid, total_amount, gross_amount, discount_amount, final_amount, amount_remaining, payment_status, notes)
                    VALUES (?, ?, ?, ?, ?, ?, "confirmed", ?, ?, ?, ?, ?, 0, ?, ?, "pending", ?)'
                );
                $insert->execute([
                    $propertyId,
                    $clientId,
                    $_SESSION['user_id'] ?? null,
                    $_SESSION['user_id'] ?? null,
                    $startDate,
                    $endDate,
                    $source,
                    $source,
                    $uid,
                    $totalAmount,
                    $totalAmount,
                    $totalAmount,
                    $totalAmount,
                    $notes,
                ]);
                $reservationId = (int) $connection->lastInsertId();
                ensure_notification('new_reservation', 'Nouvelle reservation ' . $sourceLabel, $feed['title'] . ' - ' . $startDate, $reservationId, 'reservation');
                $created++;
            }

            $summary['created'] += $created;
            $summary['updated'] += $updated;
            $summary['skipped'] += $skipped;
            $message = 'Creees: ' . $created . ', mises a jour: ' . $updated . ', ignorees: ' . $skipped;
            update_booking_feed_status($propertyId, $source, 'success', $message);
        } catch (Throwable $e) {
            $summary['errors'][] = $sourceLabel . ' - ' . $feed['title'] . ': ' . $e->getMessage();
            update_booking_feed_status($propertyId, $source, 'error', $e->getMessage());
        }
    }

    return $summary;
}

function fetch_channel_feed(string $url, string $source): string
{
    if (!filter_var($url, FILTER_VALIDATE_URL)) {
        throw new RuntimeException('URL iCal invalide.');
    }

    $context = stream_context_create([
        'http' => [
            'timeout' => 20,
            'follow_location' => 1,
            'user_agent' => 'Locative Channel Sync',
        ],
        'ssl' => [
            'verify_peer' => true,
            'verify_peer_name' => true,
        ],
    ]);
    $content = @file_get_contents($url, false, $context);

    if (!is_string($content) || trim($content) === '') {
        $sourceLabel = channel_sync_sources()[$source] ?? ucfirst($source);
        throw new RuntimeException('Impossible de lire le calendrier ' . $sourceLabel . '.');
    }

    return $content;
}

function channel_event_notes(array $event, string $propertyTitle, string $source): string
{
    $sourceLabel = channel_sync_sources()[$source] ?? ucfirst($source);
    $parts = [
        'Import automatique ' . $sourceLabel,
        'Bien: ' . $propertyTitle,
    ];

    foreach (['UID', 'SUMMARY', 'DESCRIPTION'] as $key) {
        if (!empty($event[$key])) {
            $parts[] = $key . ': ' . trim((string) $event[$key]);
        }
    }

    return implode("\n", $parts);
}

function update_booking_feed_status(int $propertyId, string $source, string $status, string $message): void
{
    $statement = Database::connection()->prepare(
        'UPDATE booking_sync_feeds SET last_synced_at = NOW(), last_status = ?, last_message = ? WHERE property_id = ? AND source = ?'
    );
    $statement->execute([$status, substr($message, 0, 1000), $propertyId, $source]);
}

function sync_property_statuses(): array
{
    $today = date('Y-m-d');
    $properties = Database::connection()
        ->query('SELECT id, title, status FROM properties WHERE status != "maintenance"')
        ->fetchAll();
    $updated = ['occupied' => 0, 'available' => 0];

    foreach ($properties as $property) {
        $statement = Database::connection()->prepare(
            'SELECT COUNT(*) FROM reservations
            WHERE property_id = ?
            AND status IN ("pending", "confirmed")
            AND start_date <= ?
            AND end_date > ?'
        );
        $statement->execute([(int) $property['id'], $today, $today]);
        $shouldBeOccupied = (int) $statement->fetchColumn() > 0;
        $newStatus = $shouldBeOccupied ? 'occupied' : 'available';

        if ($property['status'] !== $newStatus) {
            $update = Database::connection()->prepare('UPDATE properties SET status = ? WHERE id = ?');
            $update->execute([$newStatus, (int) $property['id']]);
            $updated[$newStatus]++;
        }
    }

    return $updated;
}

function sql_quote_value($value): string
{
    if ($value === null) {
        return 'NULL';
    }

    return Database::connection()->quote((string) $value);
}

function stream_database_backup(): void
{
    $filename = 'locative_backup_' . date('Ymd_His') . '.sql';

    header('Content-Type: application/sql; charset=UTF-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');

    echo "-- Locative database backup\n";
    echo "-- Generated at " . date('Y-m-d H:i:s') . "\n\n";
    echo "SET FOREIGN_KEY_CHECKS=0;\n\n";

    foreach (backup_tables() as $table) {
        echo "TRUNCATE TABLE `$table`;\n";

        $rows = Database::connection()->query("SELECT * FROM `$table`")->fetchAll();

        foreach ($rows as $row) {
            $columns = array_map(static fn (string $column): string => "`$column`", array_keys($row));
            $values = array_map('sql_quote_value', array_values($row));

            echo 'INSERT INTO `' . $table . '` (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $values) . ");\n";
        }

        echo "\n";
    }

    echo "SET FOREIGN_KEY_CHECKS=1;\n";
    exit;
}
