<?php

declare(strict_types=1);

session_start();

require_once dirname(__DIR__) . '/app/Database.php';
require_once dirname(__DIR__) . '/app/helpers.php';
require_once dirname(__DIR__) . '/app/Auth.php';

$path = parse_url($_SERVER['REQUEST_URI'] ?? '/', PHP_URL_PATH) ?: '/';
$basePath = app_base_path();
if ($basePath !== '' && strpos($path, $basePath) === 0) {
    $path = substr($path, strlen($basePath));
}
$route = '/' . trim($path, '/');
$route = $route === '//' ? '/' : $route;
$method = $_SERVER['REQUEST_METHOD'] ?? 'GET';
$readMethod = in_array($method, ['GET', 'HEAD'], true);

if ($route === '/login' && $readMethod) {
    if (Auth::check()) {
        redirect('/dashboard');
    }

    $pageTitle = 'Connexion - Locative';
    $error = $_SESSION['login_error'] ?? null;
    unset($_SESSION['login_error']);

    require dirname(__DIR__) . '/resources/views/auth/login.php';
    exit;
}

if ($route === '/login' && $method === 'POST') {
    verify_csrf();

    $email = trim((string) ($_POST['email'] ?? ''));
    $password = (string) ($_POST['password'] ?? '');

    if (Auth::attempt($email, $password)) {
        redirect('/dashboard');
    }

    $_SESSION['login_error'] = 'Email ou mot de passe incorrect.';
    redirect('/login');
}

if ($route === '/logout') {
    Auth::logout();
    redirect('/login');
}

if ($route === '/' || $route === '') {
    if (Auth::check()) {
        redirect('/dashboard');
    }

    redirect('/login');
}

if ($route === '/dashboard') {
    Auth::requireLogin();
    sync_notifications();

    $pageTitle = 'Dashboard - Locative';
    $user = Auth::user();
    $connection = Database::connection();
    $today = new DateTimeImmutable('today');
    $period = (string) ($_GET['period'] ?? 'month');
    $allowedPeriods = ['day', 'week', 'month', 'year', 'custom'];
    if (!in_array($period, $allowedPeriods, true)) {
        $period = 'month';
    }

    if ($period === 'day') {
        $from = $today->format('Y-m-d');
        $to = $from;
    } elseif ($period === 'week') {
        $from = $today->modify('monday this week')->format('Y-m-d');
        $to = $today->modify('sunday this week')->format('Y-m-d');
    } elseif ($period === 'year') {
        $from = $today->format('Y-01-01');
        $to = $today->format('Y-12-31');
    } elseif ($period === 'custom') {
        [$from, $to] = period_dates($_GET);
    } else {
        $from = $today->format('Y-m-01');
        $to = $today->format('Y-m-t');
    }

    $periodLabels = [
        'day' => 'Aujourd hui',
        'week' => 'Cette semaine',
        'month' => 'Ce mois',
        'year' => 'Cette annee',
        'custom' => 'Periode personnalisee',
    ];
    $periodLabel = $periodLabels[$period] ?? $periodLabels['month'];

    $shortRevenueStatement = $connection->prepare('SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status != "cancelled" AND payment_date BETWEEN ? AND ?');
    $shortRevenueStatement->execute([$from, $to]);
    $shortRevenue = (float) $shortRevenueStatement->fetchColumn();

    $longRevenueStatement = $connection->prepare('SELECT COALESCE(SUM(amount_paid), 0) FROM long_term_payments WHERE COALESCE(payment_date, CONCAT(paid_month, "-01")) BETWEEN ? AND ?');
    $longRevenueStatement->execute([$from, $to]);
    $longRevenue = (float) $longRevenueStatement->fetchColumn();

    $expensesStatement = $connection->prepare('SELECT COALESCE(SUM(amount), 0) FROM expenses WHERE expense_date BETWEEN ? AND ?');
    $expensesStatement->execute([$from, $to]);
    $expensesTotal = (float) $expensesStatement->fetchColumn();

    $pendingShortStatement = $connection->prepare('SELECT COALESCE(SUM(amount_remaining), 0) FROM reservations WHERE payment_status != "paid" AND start_date <= ? AND end_date >= ?');
    $pendingShortStatement->execute([$to, $from]);
    $pendingShort = (float) $pendingShortStatement->fetchColumn();

    $pendingLongStatement = $connection->prepare('SELECT COALESCE(SUM(amount_remaining), 0) FROM long_term_payments WHERE status != "paid" AND CONCAT(paid_month, "-01") BETWEEN ? AND ?');
    $pendingLongStatement->execute([$from, $to]);
    $pendingLong = (float) $pendingLongStatement->fetchColumn();

    $propertyCount = max(1, (int) Database::connection()->query('SELECT COUNT(*) FROM properties')->fetchColumn());
    $periodDays = days_between_inclusive($from, $to);
    $occupiedNights = 0;
    $nightsStatement = $connection->prepare('SELECT start_date, end_date FROM reservations WHERE status IN ("pending", "confirmed", "completed") AND start_date <= ? AND end_date >= ?');
    $nightsStatement->execute([$to, $from]);
    $nightsRows = $nightsStatement->fetchAll();
    foreach ($nightsRows as $row) {
        $occupiedNights += reservation_nights(max($row['start_date'], $from), min($row['end_date'], $to));
    }

    $shortReservationsStatement = $connection->prepare('SELECT COUNT(*) FROM reservations WHERE start_date BETWEEN ? AND ?');
    $shortReservationsStatement->execute([$from, $to]);

    $pendingPaymentsStatement = $connection->prepare('SELECT COUNT(*) FROM reservations WHERE payment_status != "paid" AND start_date <= ? AND end_date >= ?');
    $pendingPaymentsStatement->execute([$to, $from]);

    $stats = [
        'properties' => (int) $connection->query('SELECT COUNT(*) FROM properties')->fetchColumn(),
        'reservations' => (int) $connection->query('SELECT COUNT(*) FROM reservations')->fetchColumn(),
        'clients' => (int) $connection->query('SELECT COUNT(*) FROM clients')->fetchColumn(),
        'pendingPayments' => (int) $pendingPaymentsStatement->fetchColumn(),
        'monthRevenue' => $shortRevenue,
        'arrivalsToday' => (int) $connection->query('SELECT COUNT(*) FROM reservations WHERE start_date = CURDATE() AND status IN ("pending", "confirmed")')->fetchColumn(),
        'departuresToday' => (int) $connection->query('SELECT COUNT(*) FROM reservations WHERE end_date = CURDATE() AND status IN ("pending", "confirmed")')->fetchColumn(),
        'shortRevenue' => $shortRevenue,
        'shortReservations' => (int) $shortReservationsStatement->fetchColumn(),
        'occupancyRate' => round(($occupiedNights / max(1, $propertyCount * $periodDays)) * 100, 2),
        'longRevenue' => $longRevenue,
        'activeTenants' => (int) $connection->query('SELECT COUNT(DISTINCT tenant_id) FROM long_term_contracts WHERE status = "active"')->fetchColumn(),
        'pendingRent' => $pendingLong,
        'remainingToCollect' => $pendingShort + $pendingLong,
        'totalRevenue' => $shortRevenue + $longRevenue,
        'expensesTotal' => $expensesTotal,
        'netProfit' => ($shortRevenue + $longRevenue) - $expensesTotal,
    ];

    $recentReservations = $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
            ORDER BY r.id DESC LIMIT 5')
        ->fetchAll();

    $chartStatement = $connection->prepare('SELECT period_key, SUM(revenue) AS revenue, SUM(expenses) AS expenses, SUM(revenue - expenses) AS profit FROM (
        SELECT DATE_FORMAT(payment_date, "%Y-%m-%d") AS period_key, SUM(amount) AS revenue, 0 AS expenses FROM payments WHERE status != "cancelled" AND payment_date BETWEEN ? AND ? GROUP BY DATE_FORMAT(payment_date, "%Y-%m-%d")
        UNION ALL
        SELECT DATE_FORMAT(COALESCE(payment_date, CONCAT(paid_month, "-01")), "%Y-%m-%d") AS period_key, SUM(amount_paid) AS revenue, 0 AS expenses FROM long_term_payments WHERE COALESCE(payment_date, CONCAT(paid_month, "-01")) BETWEEN ? AND ? GROUP BY DATE_FORMAT(COALESCE(payment_date, CONCAT(paid_month, "-01")), "%Y-%m-%d")
        UNION ALL
        SELECT DATE_FORMAT(expense_date, "%Y-%m-%d") AS period_key, 0 AS revenue, SUM(amount) AS expenses FROM expenses WHERE expense_date BETWEEN ? AND ? GROUP BY DATE_FORMAT(expense_date, "%Y-%m-%d")
    ) totals GROUP BY period_key ORDER BY period_key ASC');
    $chartStatement->execute([$from, $to, $from, $to, $from, $to]);
    $chartRows = $chartStatement->fetchAll();

    $propertyRevenueStatement = $connection->prepare('SELECT p.title, COALESCE(SUM(pay.amount), 0) AS revenue FROM properties p LEFT JOIN payments pay ON pay.property_id = p.id AND pay.status != "cancelled" AND pay.payment_date BETWEEN ? AND ? GROUP BY p.id, p.title ORDER BY revenue DESC LIMIT 6');
    $propertyRevenueStatement->execute([$from, $to]);
    $propertyRevenueRows = $propertyRevenueStatement->fetchAll();

    $upcomingContracts = $connection->query('SELECT c.end_date, t.full_name AS tenant_name, p.title AS property_title
        FROM long_term_contracts c
        JOIN long_term_tenants t ON t.id = c.tenant_id
        JOIN properties p ON p.id = c.property_id
        WHERE c.status = "active" AND c.end_date IS NOT NULL AND c.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
        ORDER BY c.end_date ASC LIMIT 5')->fetchAll();

    $overduePayments = $connection->query('SELECT lp.paid_month, lp.amount_remaining, t.full_name AS tenant_name, p.title AS property_title
        FROM long_term_payments lp
        JOIN long_term_contracts c ON c.id = lp.contract_id
        JOIN long_term_tenants t ON t.id = c.tenant_id
        JOIN properties p ON p.id = c.property_id
        WHERE lp.status != "paid" AND CONCAT(lp.paid_month, "-01") < DATE_FORMAT(CURDATE(), "%Y-%m-01")
        ORDER BY lp.paid_month ASC LIMIT 5')->fetchAll();

    $unreadNotifications = (int) $connection
        ->query('SELECT COUNT(*) FROM notifications WHERE is_read = 0')
        ->fetchColumn();

    require dirname(__DIR__) . '/resources/views/dashboard/index.php';
    exit;
}

if ($route === '/profile' && $readMethod) {
    Auth::requireLogin();

    $pageTitle = 'Profil - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/profile/index.php';
    exit;
}

if ($route === '/profile/update' && $method === 'POST') {
    Auth::requireLogin();
    verify_csrf();

    $user = Auth::user();
    $name = trim((string) ($_POST['name'] ?? ''));
    $email = trim((string) ($_POST['email'] ?? ''));
    $currentPassword = (string) ($_POST['current_password'] ?? '');
    $newPassword = (string) ($_POST['new_password'] ?? '');
    $newPasswordConfirmation = (string) ($_POST['new_password_confirmation'] ?? '');
    $errors = [];

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

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

    if ($newPassword !== '' || $newPasswordConfirmation !== '') {
        $statement = Database::connection()->prepare('SELECT password FROM users WHERE id = ? LIMIT 1');
        $statement->execute([$user['id']]);
        $passwordHash = (string) $statement->fetchColumn();

        if (!password_verify($currentPassword, $passwordHash)) {
            $errors[] = 'Le mot de passe actuel est incorrect.';
        }

        if (strlen($newPassword) < 6) {
            $errors[] = 'Le nouveau mot de passe doit contenir au moins 6 caracteres.';
        }

        if ($newPassword !== $newPasswordConfirmation) {
            $errors[] = 'La confirmation du nouveau mot de passe ne correspond pas.';
        }
    }

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/profile');
    }

    try {
        if ($newPassword !== '') {
            $statement = Database::connection()->prepare('UPDATE users SET name = ?, email = ?, password = ? WHERE id = ?');
            $statement->execute([$name, $email, password_hash($newPassword, PASSWORD_DEFAULT), $user['id']]);
        } else {
            $statement = Database::connection()->prepare('UPDATE users SET name = ?, email = ? WHERE id = ?');
            $statement->execute([$name, $email, $user['id']]);
        }

        log_activity('update', 'profile', 'Profil modifie: ' . $email);
        flash('notice', 'Profil modifie avec succes.');
    } catch (Throwable $e) {
        $_SESSION['form_errors'] = ['Cet email existe deja.'];
        redirect('/profile');
    }

    redirect('/profile');
}

if ($route === '/exports' && $readMethod) {
    Auth::requireLogin();

    $pageTitle = 'Exports - Locative';
    $user = Auth::user();
    $exports = array_filter(export_definitions(), static fn (array $definition): bool => Auth::can($definition['permission']));

    require dirname(__DIR__) . '/resources/views/exports/index.php';
    exit;
}

if ($route === '/exports/download' && $readMethod) {
    Auth::requireLogin();

    $type = (string) ($_GET['type'] ?? '');
    $exports = export_definitions();

    if (!isset($exports[$type])) {
        http_response_code(404);
        $pageTitle = 'Export introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $definition = $exports[$type];
    Auth::requirePermission($definition['permission']);

    $rows = Database::connection()->query($definition['sql'])->fetchAll(PDO::FETCH_NUM);
    log_activity('export', 'exports', 'Export CSV: ' . $type);
    stream_csv($definition['filename'], $definition['headers'], $rows);
}

if ($route === '/backup' && $readMethod) {
    Auth::requirePermission('users.view');

    $pageTitle = 'Backup - Locative';
    $user = Auth::user();

    require dirname(__DIR__) . '/resources/views/backup/index.php';
    exit;
}

if ($route === '/backup/download' && $readMethod) {
    Auth::requirePermission('users.view');

    log_activity('export', 'backup', 'Backup SQL telecharge');
    stream_database_backup();
}

if ($route === '/settings' && $readMethod) {
    Auth::requirePermission('users.view');

    $pageTitle = 'Parametres - Locative';
    $user = Auth::user();
    $settings = app_settings();
    $settings['booking_sync_token'] = booking_sync_token();
    $bookingFeeds = booking_sync_feeds();
    $bookingCronUrl = url('/booking-sync/cron?token=' . $settings['booking_sync_token']);
    $channelSources = channel_sync_sources();
    $adminAccount = $user;
    $notice = flash('notice');
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/settings/index.php';
    exit;
}

if ($route === '/settings/update' && $method === 'POST') {
    Auth::requirePermission('users.view');
    verify_csrf();

    $settings = [
        'company_name' => trim((string) ($_POST['company_name'] ?? '')),
        'company_phone' => trim((string) ($_POST['company_phone'] ?? '')),
        'company_email' => trim((string) ($_POST['company_email'] ?? '')),
        'company_address' => trim((string) ($_POST['company_address'] ?? '')),
        'company_city' => trim((string) ($_POST['company_city'] ?? '')),
        'booking_sync_token' => trim((string) ($_POST['booking_sync_token'] ?? '')),
    ];
    $adminData = [
        'name' => trim((string) ($_POST['admin_name'] ?? '')),
        'email' => trim((string) ($_POST['admin_email'] ?? '')),
        'current_password' => (string) ($_POST['admin_current_password'] ?? ''),
        'new_password' => (string) ($_POST['admin_new_password'] ?? ''),
        'new_password_confirmation' => (string) ($_POST['admin_new_password_confirmation'] ?? ''),
    ];
    $errors = [];

    if ($settings['company_name'] === '') {
        $errors[] = 'Le nom de la societe est obligatoire.';
    }

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

    if ($adminData['name'] === '') {
        $errors[] = 'Le nom administrateur est obligatoire.';
    }

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

    if ($adminData['new_password'] !== '' || $adminData['new_password_confirmation'] !== '') {
        $passwordStatement = Database::connection()->prepare('SELECT password FROM users WHERE id = ? LIMIT 1');
        $passwordStatement->execute([$_SESSION['user_id'] ?? 0]);
        $passwordHash = (string) $passwordStatement->fetchColumn();

        if (!password_verify($adminData['current_password'], $passwordHash)) {
            $errors[] = 'Le mot de passe actuel administrateur est incorrect.';
        }

        if (strlen($adminData['new_password']) < 6) {
            $errors[] = 'Le nouveau mot de passe administrateur doit contenir au moins 6 caracteres.';
        }

        if ($adminData['new_password'] !== $adminData['new_password_confirmation']) {
            $errors[] = 'La confirmation du nouveau mot de passe administrateur ne correspond pas.';
        }
    }

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/settings');
    }

    try {
        save_app_settings($settings);

        if ($adminData['new_password'] !== '') {
            $adminStatement = Database::connection()->prepare('UPDATE users SET name = ?, email = ?, password = ? WHERE id = ?');
            $adminStatement->execute([
                $adminData['name'],
                $adminData['email'],
                password_hash($adminData['new_password'], PASSWORD_DEFAULT),
                $_SESSION['user_id'] ?? 0,
            ]);
        } else {
            $adminStatement = Database::connection()->prepare('UPDATE users SET name = ?, email = ? WHERE id = ?');
            $adminStatement->execute([
                $adminData['name'],
                $adminData['email'],
                $_SESSION['user_id'] ?? 0,
            ]);
        }
    } catch (Throwable $e) {
        $_SESSION['form_errors'] = ['Cet email administrateur existe deja.'];
        redirect('/settings');
    }

    log_activity('update', 'settings', 'Parametres application et administrateur modifies');
    flash('notice', 'Parametres enregistres avec succes.');
    redirect('/settings');
}

if ($route === '/booking-sync/settings' && $method === 'POST') {
    Auth::requirePermission('users.view');
    verify_csrf();

    try {
        save_booking_sync_feeds($_POST);
        flash('notice', 'Parametres Booking/Airbnb enregistres.');
    } catch (Throwable $e) {
        $_SESSION['form_errors'] = ['Parametres Booking/Airbnb impossibles: ' . $e->getMessage()];
    }

    redirect('/settings');
}

if ($route === '/booking-sync/run' && $method === 'POST') {
    Auth::requirePermission('reservations.create');
    verify_csrf();

    $summary = sync_booking_reservations();
    $message = 'Sync Booking/Airbnb termine: ' . (int) $summary['created'] . ' creee(s), ' . (int) $summary['updated'] . ' mise(s) a jour, ' . (int) $summary['skipped'] . ' ignoree(s).';
    if (!empty($summary['errors'])) {
        $message .= ' Erreurs: ' . implode(' | ', $summary['errors']);
    }

    flash('notice', $message);
    log_activity('sync', 'channels', $message);
    redirect('/settings');
}

if ($route === '/booking-sync/cron' && $readMethod) {
    $token = trim((string) ($_GET['token'] ?? ''));
    $expectedToken = booking_sync_token();

    if ($token === '' || !hash_equals($expectedToken, $token)) {
        http_response_code(403);
        header('Content-Type: application/json; charset=UTF-8');
        echo json_encode(['ok' => false, 'error' => 'Token invalide']);
        exit;
    }

    $summary = sync_booking_reservations();
    header('Content-Type: application/json; charset=UTF-8');
    echo json_encode(['ok' => empty($summary['errors']), 'summary' => $summary], JSON_UNESCAPED_UNICODE);
    exit;
}

if ($route === '/settings/reset-data' && $method === 'POST') {
    Auth::requirePermission('users.view');
    verify_csrf();

    $user = Auth::user();
    if (($user['role'] ?? '') !== 'admin') {
        http_response_code(403);
        $pageTitle = 'Acces refuse - Locative';
        require dirname(__DIR__) . '/resources/views/errors/403.php';
        exit;
    }

    if (trim((string) ($_POST['reset_confirmation'] ?? '')) !== 'RESET') {
        $_SESSION['form_errors'] = ['Tapez RESET pour confirmer la suppression des donnees.'];
        redirect('/settings');
    }

    $tables = [
        'long_term_payments',
        'long_term_contracts',
        'long_term_tenants',
        'expenses',
        'payments',
        'reservations',
        'booking_sync_feeds',
        'property_photos',
        'properties',
        'clients',
        'notifications',
        'activity_logs',
    ];

    $connection = Database::connection();
    try {
        $connection->exec('SET FOREIGN_KEY_CHECKS = 0');
        foreach ($tables as $table) {
            try {
                $connection->exec('TRUNCATE TABLE ' . $table);
            } catch (Throwable $e) {
                try {
                    $connection->exec('DELETE FROM ' . $table);
                } catch (Throwable $ignored) {
                }
            }
        }
        $connection->exec('SET FOREIGN_KEY_CHECKS = 1');
    } catch (Throwable $e) {
        $connection->exec('SET FOREIGN_KEY_CHECKS = 1');
        $_SESSION['form_errors'] = ['Reset data impossible: ' . $e->getMessage()];
        redirect('/settings');
    }

    log_activity('reset', 'settings', 'Reset data execute par administrateur');
    flash('notice', 'Les donnees ont ete reinitialisees avec succes.');
    redirect('/settings');
}

if ($route === '/maintenance/property-statuses' && $readMethod) {
    Auth::requirePermission('properties.edit');

    $pageTitle = 'Synchronisation statuts - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $properties = Database::connection()
        ->query('SELECT id, title, type, city, status FROM properties ORDER BY status, title')
        ->fetchAll();

    require dirname(__DIR__) . '/resources/views/maintenance/property_statuses.php';
    exit;
}

if ($route === '/maintenance/property-statuses/sync' && $method === 'POST') {
    Auth::requirePermission('properties.edit');
    verify_csrf();

    $updated = sync_property_statuses();
    log_activity('update', 'properties', 'Synchronisation statuts biens');
    flash('notice', 'Synchronisation terminee: ' . $updated['occupied'] . ' occupe(s), ' . $updated['available'] . ' disponible(s).');
    redirect('/maintenance/property-statuses');
}

if ($route === '/statistics' && $readMethod) {
    Auth::requirePermission('statistics.view');

    [$from, $to] = period_dates($_GET);
    $pageTitle = 'Statistiques - Locative';
    $user = Auth::user();
    $days = days_between_inclusive($from, $to);
    $propertyCount = (int) Database::connection()->query('SELECT COUNT(*) FROM properties')->fetchColumn();
    $capacityNights = max(1, $propertyCount * $days);

    $paidStatement = Database::connection()->prepare(
        'SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status = "paid" AND payment_date BETWEEN ? AND ?'
    );
    $paidStatement->execute([$from, $to]);

    $pendingStatement = Database::connection()->prepare(
        'SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status = "pending" AND (payment_date BETWEEN ? AND ? OR payment_date IS NULL)'
    );
    $pendingStatement->execute([$from, $to]);

    $reservationStatement = Database::connection()->prepare(
        'SELECT COUNT(*) FROM reservations WHERE status IN ("pending", "confirmed", "completed") AND start_date <= ? AND end_date >= ?'
    );
    $reservationStatement->execute([$to, $from]);

    $nightsStatement = Database::connection()->prepare(
        'SELECT start_date, end_date FROM reservations
        WHERE status IN ("pending", "confirmed", "completed") AND start_date <= ? AND end_date >= ?'
    );
    $nightsStatement->execute([$to, $from]);
    $occupiedNights = 0;

    foreach ($nightsStatement->fetchAll() as $row) {
        $start = max($row['start_date'], $from);
        $end = min($row['end_date'], $to);
        $occupiedNights += reservation_nights($start, $end);
    }

    $summary = [
        'paidRevenue' => (float) $paidStatement->fetchColumn(),
        'pendingRevenue' => (float) $pendingStatement->fetchColumn(),
        'reservations' => (int) $reservationStatement->fetchColumn(),
        'occupancyRate' => round(($occupiedNights / $capacityNights) * 100, 2),
    ];

    $propertyRevenueStatement = Database::connection()->prepare(
        'SELECT p.title, COALESCE(SUM(pay.amount), 0) AS revenue
        FROM properties p
        LEFT JOIN payments pay ON pay.property_id = p.id AND pay.status = "paid" AND pay.payment_date BETWEEN ? AND ?
        GROUP BY p.id, p.title
        ORDER BY revenue DESC, p.title'
    );
    $propertyRevenueStatement->execute([$from, $to]);
    $propertyRevenues = $propertyRevenueStatement->fetchAll();

    $dailyRevenueStatement = Database::connection()->prepare(
        'SELECT payment_date, COALESCE(SUM(amount), 0) AS revenue
        FROM payments
        WHERE status = "paid" AND payment_date BETWEEN ? AND ?
        GROUP BY payment_date
        ORDER BY payment_date'
    );
    $dailyRevenueStatement->execute([$from, $to]);
    $dailyRevenues = $dailyRevenueStatement->fetchAll();

    $weeklyRevenueStatement = Database::connection()->prepare(
        'SELECT YEARWEEK(payment_date, 3) AS period_key, COALESCE(SUM(amount), 0) AS revenue
        FROM payments
        WHERE status = "paid" AND payment_date BETWEEN ? AND ?
        GROUP BY YEARWEEK(payment_date, 3)
        ORDER BY period_key'
    );
    $weeklyRevenueStatement->execute([$from, $to]);
    $weeklyRevenues = $weeklyRevenueStatement->fetchAll();

    $monthlyRevenueStatement = Database::connection()->prepare(
        'SELECT DATE_FORMAT(payment_date, "%Y-%m") AS period_key, COALESCE(SUM(amount), 0) AS revenue
        FROM payments
        WHERE status = "paid" AND payment_date BETWEEN ? AND ?
        GROUP BY DATE_FORMAT(payment_date, "%Y-%m")
        ORDER BY period_key'
    );
    $monthlyRevenueStatement->execute([$from, $to]);
    $monthlyRevenues = $monthlyRevenueStatement->fetchAll();

    require dirname(__DIR__) . '/resources/views/statistics/index.php';
    exit;
}

if ($route === '/reports' && $readMethod) {
    Auth::requirePermission('statistics.view');

    [$from, $to] = period_dates($_GET);
    $pageTitle = 'Rapport detaille - Locative';
    $user = Auth::user();

    $summaryStatement = Database::connection()->prepare(
        'SELECT
            (SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status = "paid" AND payment_date BETWEEN ? AND ?) AS paid_revenue,
            (SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status = "pending" AND (payment_date BETWEEN ? AND ? OR payment_date IS NULL)) AS pending_revenue,
            (SELECT COUNT(*) FROM reservations WHERE status IN ("pending", "confirmed", "completed") AND start_date <= ? AND end_date >= ?) AS reservations_count,
            (SELECT COUNT(*) FROM clients) AS clients_count,
            (SELECT COUNT(*) FROM properties) AS properties_count'
    );
    $summaryStatement->execute([$from, $to, $from, $to, $to, $from]);
    $summary = $summaryStatement->fetch();

    $reservationStatement = Database::connection()->prepare(
        '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.start_date <= ? AND r.end_date >= ?
        ORDER BY r.start_date DESC'
    );
    $reservationStatement->execute([$to, $from]);
    $reservations = $reservationStatement->fetchAll();

    $paymentStatement = Database::connection()->prepare(
        '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.payment_date BETWEEN ? AND ? OR pay.payment_date IS NULL
        ORDER BY pay.payment_date DESC, pay.id DESC'
    );
    $paymentStatement->execute([$from, $to]);
    $payments = $paymentStatement->fetchAll();

    require dirname(__DIR__) . '/resources/views/reports/index.php';
    exit;
}

if ($route === '/notifications' && $readMethod) {
    Auth::requireLogin();
    sync_notifications();

    $pageTitle = 'Notifications - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $notifications = Database::connection()
        ->query('SELECT * FROM notifications ORDER BY is_read ASC, id DESC')
        ->fetchAll();
    [$notifications, $pagination] = paginate_items($notifications, 15);

    require dirname(__DIR__) . '/resources/views/notifications/index.php';
    exit;
}

if ($route === '/notifications/read' && $method === 'POST') {
    Auth::requireLogin();
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);
    $statement = Database::connection()->prepare('UPDATE notifications SET is_read = 1 WHERE id = ?');
    $statement->execute([$id]);
    flash('notice', 'Notification marquee comme lue.');
    redirect('/notifications');
}

if ($route === '/notifications/read-all' && $method === 'POST') {
    Auth::requireLogin();
    verify_csrf();

    Database::connection()->query('UPDATE notifications SET is_read = 1 WHERE is_read = 0');
    flash('notice', 'Toutes les notifications sont marquees comme lues.');
    redirect('/notifications');
}

if ($route === '/notifications/create-alert' && $readMethod) {
    Auth::requirePermission('users.view');

    $pageTitle = 'Nouvelle alerte - Locative';
    $user = Auth::user();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/notifications/create_alert.php';
    exit;
}

if ($route === '/notifications/store-alert' && $method === 'POST') {
    Auth::requirePermission('users.view');
    verify_csrf();

    $title = trim((string) ($_POST['title'] ?? ''));
    $message = trim((string) ($_POST['message'] ?? ''));
    $errors = [];

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

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

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/notifications/create-alert');
    }

    $statement = Database::connection()->prepare(
        'INSERT INTO notifications (user_id, type, title, message, is_read, related_id, related_type)
        VALUES (?, "important_alert", ?, ?, 0, NULL, "alert")'
    );
    $statement->execute([$_SESSION['user_id'] ?? null, $title, $message]);
    send_notification_email($title, $message);
    log_activity('create', 'notifications', 'Alerte importante ajoutee: ' . $title);
    flash('notice', 'Alerte importante ajoutee.');
    redirect('/notifications');
}

if ($route === '/activity-logs' && $readMethod) {
    Auth::requirePermission('users.view');

    $pageTitle = 'Historique - Locative';
    $user = Auth::user();
    $module = trim((string) ($_GET['module'] ?? ''));
    $conditions = [];
    $params = [];

    if ($module !== '') {
        $conditions[] = 'al.module = ?';
        $params[] = $module;
    }

    $sql = 'SELECT al.*, u.name AS user_name
        FROM activity_logs al
        LEFT JOIN users u ON u.id = al.user_id';

    if ($conditions) {
        $sql .= ' WHERE ' . implode(' AND ', $conditions);
    }

    $sql .= ' ORDER BY al.id DESC';
    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $logs = $statement->fetchAll();
    [$logs, $pagination] = paginate_items($logs, 20);

    $modules = Database::connection()
        ->query('SELECT DISTINCT module FROM activity_logs ORDER BY module')
        ->fetchAll();

    require dirname(__DIR__) . '/resources/views/activity_logs/index.php';
    exit;
}

if ($route === '/operations/arrivals-departures' && $readMethod) {
    Auth::requirePermission('reservations.view');

    $date = trim((string) ($_GET['date'] ?? date('Y-m-d')));
    if (!valid_date($date)) {
        $date = date('Y-m-d');
    }

    $arrivalStatement = Database::connection()->prepare(
        'SELECT r.*, p.title AS property_title, c.first_name, c.last_name, c.phone
        FROM reservations r
        JOIN properties p ON p.id = r.property_id
        JOIN clients c ON c.id = r.client_id
        WHERE r.start_date = ? AND r.status IN ("pending", "confirmed")
        ORDER BY r.checkin_time, r.id'
    );
    $arrivalStatement->execute([$date]);
    $arrivals = $arrivalStatement->fetchAll();

    $departureStatement = Database::connection()->prepare(
        'SELECT r.*, p.title AS property_title, c.first_name, c.last_name, c.phone
        FROM reservations r
        JOIN properties p ON p.id = r.property_id
        JOIN clients c ON c.id = r.client_id
        WHERE r.end_date = ? AND r.status IN ("pending", "confirmed")
        ORDER BY r.checkout_time, r.id'
    );
    $departureStatement->execute([$date]);
    $departures = $departureStatement->fetchAll();

    $pageTitle = 'Arrivees et departs - Locative';
    $user = Auth::user();
    $previousDate = (new DateTimeImmutable($date))->modify('-1 day')->format('Y-m-d');
    $nextDate = (new DateTimeImmutable($date))->modify('+1 day')->format('Y-m-d');

    require dirname(__DIR__) . '/resources/views/operations/arrivals_departures.php';
    exit;
}

if ($route === '/properties' && $readMethod) {
    Auth::requirePermission('properties.view');

    $pageTitle = 'Biens - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $city = trim((string) ($_GET['city'] ?? ''));
    $district = trim((string) ($_GET['district'] ?? ''));
    $address = trim((string) ($_GET['address'] ?? ''));
    $status = (string) ($_GET['status'] ?? '');
    $type = (string) ($_GET['type'] ?? '');
    $conditions = [];
    $params = [];

    if ($city !== '') {
        $conditions[] = 'p.city LIKE ?';
        $params[] = '%' . $city . '%';
    }

    if ($district !== '') {
        $conditions[] = 'p.district LIKE ?';
        $params[] = '%' . $district . '%';
    }

    if ($address !== '') {
        $conditions[] = 'p.address LIKE ?';
        $params[] = '%' . $address . '%';
    }

    if (array_key_exists($status, property_statuses())) {
        $conditions[] = 'p.status = ?';
        $params[] = $status;
    }

    if (array_key_exists($type, property_types())) {
        $conditions[] = 'p.type = ?';
        $params[] = $type;
    }

    $sql = 'SELECT p.*, pp.photo_path FROM properties p
        LEFT JOIN property_photos pp ON pp.property_id = p.id AND pp.is_main = 1';

    if ($conditions) {
        $sql .= ' WHERE ' . implode(' AND ', $conditions);
    }

    $sql .= ' ORDER BY p.id DESC';
    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $properties = $statement->fetchAll();
    [$properties, $pagination] = paginate_items($properties, 12);

    require dirname(__DIR__) . '/resources/views/properties/index.php';
    exit;
}

if ($route === '/properties/create' && $readMethod) {
    Auth::requirePermission('properties.create');

    $pageTitle = 'Ajouter bien - Locative';
    $user = Auth::user();
    $property = [
        'title' => '',
        'type' => 'apartment',
        'description' => '',
        'city' => '',
        'district' => '',
        'address' => '',
        'price_per_night' => '0',
        'status' => 'available',
        'bedrooms' => '0',
        'bathrooms' => '0',
        'max_guests' => '1',
    ];
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/properties/form.php';
    exit;
}

if ($route === '/properties/store' && $method === 'POST') {
    Auth::requirePermission('properties.create');
    verify_csrf();

    $data = property_form_data($_POST);
    $errors = validate_property($data);

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/properties/create');
    }

    $statement = Database::connection()->prepare(
        'INSERT INTO properties (title, type, description, city, district, address, price_per_night, status, bedrooms, bathrooms, max_guests, created_by)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
    );
    $statement->execute([
        $data['title'],
        $data['type'],
        $data['description'],
        $data['city'],
        $data['district'],
        $data['address'],
        $data['price_per_night'],
        $data['status'],
        $data['bedrooms'],
        $data['bathrooms'],
        $data['max_guests'],
        $_SESSION['user_id'] ?? null,
    ]);

    save_property_photos((int) Database::connection()->lastInsertId());
    flash('notice', 'Bien ajoute avec succes.');
    log_activity('create', 'properties', 'Bien ajoute: ' . $data['title']);
    redirect('/properties');
}

if ($route === '/properties/show' && $readMethod) {
    Auth::requirePermission('properties.view');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT * FROM properties WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $property = $statement->fetch();

    if (!$property) {
        http_response_code(404);
        $pageTitle = 'Bien introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $photosStatement = Database::connection()->prepare('SELECT * FROM property_photos WHERE property_id = ? ORDER BY is_main DESC, id DESC');
    $photosStatement->execute([$id]);
    $photos = $photosStatement->fetchAll();
    $pageTitle = $property['title'] . ' - Locative';
    $user = Auth::user();

    require dirname(__DIR__) . '/resources/views/properties/show.php';
    exit;
}

if ($route === '/properties/edit' && $readMethod) {
    Auth::requirePermission('properties.edit');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT * FROM properties WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $property = $statement->fetch();

    if (!$property) {
        http_response_code(404);
        $pageTitle = 'Bien introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $pageTitle = 'Modifier bien - Locative';
    $user = Auth::user();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/properties/form.php';
    exit;
}

if ($route === '/properties/update' && $method === 'POST') {
    Auth::requirePermission('properties.edit');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);
    $data = property_form_data($_POST);
    $errors = validate_property($data);

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/properties/edit?id=' . $id);
    }

    $statement = Database::connection()->prepare(
        'UPDATE properties SET title = ?, type = ?, description = ?, city = ?, district = ?, address = ?,
        price_per_night = ?, status = ?, bedrooms = ?, bathrooms = ?, max_guests = ? WHERE id = ?'
    );
    $statement->execute([
        $data['title'],
        $data['type'],
        $data['description'],
        $data['city'],
        $data['district'],
        $data['address'],
        $data['price_per_night'],
        $data['status'],
        $data['bedrooms'],
        $data['bathrooms'],
        $data['max_guests'],
        $id,
    ]);

    save_property_photos($id);
    flash('notice', 'Bien modifie avec succes.');
    log_activity('update', 'properties', 'Bien modifie #' . $id . ': ' . $data['title']);
    redirect('/properties');
}

if ($route === '/properties/delete' && $method === 'POST') {
    Auth::requirePermission('properties.delete');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);

    try {
        $statement = Database::connection()->prepare('DELETE FROM properties WHERE id = ?');
        $statement->execute([$id]);
        flash('notice', 'Bien supprime avec succes.');
        log_activity('delete', 'properties', 'Bien supprime #' . $id);
    } catch (Throwable $e) {
        flash('notice', 'Impossible de supprimer ce bien car il est lie a des reservations ou paiements.');
    }

    redirect('/properties');
}

if ($route === '/properties/photo/delete' && $method === 'POST') {
    Auth::requirePermission('properties.edit');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT * FROM property_photos WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $photo = $statement->fetch();

    if ($photo) {
        $filePath = dirname(__DIR__) . '/public/' . $photo['photo_path'];
        if (is_file($filePath)) {
            unlink($filePath);
        }

        $delete = Database::connection()->prepare('DELETE FROM property_photos WHERE id = ?');
        $delete->execute([$id]);
        flash('notice', 'Photo supprimee.');
        log_activity('delete', 'properties', 'Photo supprimee #' . $id);
        redirect('/properties/show?id=' . $photo['property_id']);
    }

    redirect('/properties');
}

if ($route === '/reservations' && $readMethod) {
    Auth::requirePermission('reservations.view');

    $pageTitle = 'Reservations - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $status = (string) ($_GET['status'] ?? '');
    $from = trim((string) ($_GET['from'] ?? ''));
    $to = trim((string) ($_GET['to'] ?? ''));
    $city = trim((string) ($_GET['city'] ?? ''));
    $district = trim((string) ($_GET['district'] ?? ''));
    $conditions = [];
    $params = [];

    if ($city !== '') {
        $conditions[] = 'p.city LIKE ?';
        $params[] = '%' . $city . '%';
    }

    if ($district !== '') {
        $conditions[] = 'p.district LIKE ?';
        $params[] = '%' . $district . '%';
    }

    if (array_key_exists($status, reservation_statuses())) {
        $conditions[] = 'r.status = ?';
        $params[] = $status;
    }

    if ($from !== '') {
        $conditions[] = 'r.end_date >= ?';
        $params[] = $from;
    }

    if ($to !== '') {
        $conditions[] = 'r.start_date <= ?';
        $params[] = $to;
    }

    $sql = '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';

    if ($conditions) {
        $sql .= ' WHERE ' . implode(' AND ', $conditions);
    }

    $sql .= ' ORDER BY r.start_date DESC, r.id DESC';
    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $reservations = $statement->fetchAll();
    [$reservations, $pagination] = paginate_items($reservations, 15);

    require dirname(__DIR__) . '/resources/views/reservations/index.php';
    exit;
}

if ($route === '/reservations/calendar' && $readMethod) {
    Auth::requirePermission('reservations.view');

    $view = in_array($_GET['view'] ?? '', ['day', 'week', 'month'], true) ? (string) $_GET['view'] : 'month';
    $date = trim((string) ($_GET['date'] ?? date('Y-m-d')));
    [$rangeStart, $rangeEnd] = calendar_range($view, $date);
    $calendarStart = $view === 'month' ? $rangeStart->modify('monday this week') : $rangeStart;
    $calendarEnd = $view === 'month' ? $rangeEnd->modify('sunday this week') : $rangeEnd;
    $days = calendar_days($calendarStart, $calendarEnd);

    $statement = Database::connection()->prepare(
        '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.start_date <= ? AND r.end_date >= ?
        ORDER BY r.start_date, r.id'
    );
    $statement->execute([$calendarEnd->format('Y-m-d'), $calendarStart->format('Y-m-d')]);
    $reservations = $statement->fetchAll();
    $reservationsByDay = [];

    foreach ($days as $day) {
        $dayKey = $day->format('Y-m-d');
        $reservationsByDay[$dayKey] = [];

        foreach ($reservations as $reservation) {
            if ($reservation['start_date'] <= $dayKey && $reservation['end_date'] >= $dayKey) {
                $reservationsByDay[$dayKey][] = $reservation;
            }
        }
    }

    $pageTitle = 'Planning - Locative';
    $user = Auth::user();
    $previousDate = calendar_navigation_date($view, $date, 'previous');
    $nextDate = calendar_navigation_date($view, $date, 'next');

    require dirname(__DIR__) . '/resources/views/reservations/calendar.php';
    exit;
}

if ($route === '/reservations/create' && $readMethod) {
    Auth::requirePermission('reservations.create');

    $pageTitle = 'Ajouter reservation - Locative';
    $user = Auth::user();
    $reservation = [
        'property_id' => (int) ($_GET['property_id'] ?? 0),
        'client_id' => (int) ($_GET['client_id'] ?? 0),
        'agent_id' => (int) ($_SESSION['user_id'] ?? 0),
        'source' => 'private',
        'discount_amount' => 0,
        'start_date' => '',
        'end_date' => '',
        'checkin_time' => '15:00',
        'checkout_time' => '11:00',
        'status' => 'pending',
        'notes' => '',
    ];
    $properties = Database::connection()->query('SELECT id, title, price_per_night FROM properties ORDER BY title')->fetchAll();
    $clients = Database::connection()->query('SELECT id, first_name, last_name FROM clients ORDER BY last_name, first_name')->fetchAll();
    $agents = Database::connection()->query('SELECT id, name FROM users WHERE status = "active" ORDER BY name')->fetchAll();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/reservations/form.php';
    exit;
}

if ($route === '/reservations/store' && $method === 'POST') {
    Auth::requirePermission('reservations.create');
    verify_csrf();

    $data = reservation_form_data($_POST);
    $errors = validate_reservation($data);

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/reservations/create');
    }

    $totalAmount = reservation_total_amount($data['property_id'], $data['start_date'], $data['end_date']);
    $finalAmount = max(0, $totalAmount - $data['discount_amount']);
    $statement = Database::connection()->prepare(
        'INSERT INTO reservations (property_id, client_id, user_id, agent_id, start_date, end_date, checkin_time, checkout_time, status, source, total_amount, gross_amount, discount_amount, final_amount, amount_remaining, payment_status, notes)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
    );
    $statement->execute([
        $data['property_id'],
        $data['client_id'],
        $_SESSION['user_id'] ?? null,
        $data['agent_id'] ?: null,
        $data['start_date'],
        $data['end_date'],
        $data['checkin_time'] ?: null,
        $data['checkout_time'] ?: null,
        $data['status'],
        $data['source'],
        $totalAmount,
        $totalAmount,
        $data['discount_amount'],
        $finalAmount,
        $finalAmount,
        'pending',
        $data['notes'],
    ]);
    $reservationId = (int) Database::connection()->lastInsertId();
    $propertyStatement = Database::connection()->prepare('SELECT title FROM properties WHERE id = ? LIMIT 1');
    $propertyStatement->execute([$data['property_id']]);
    $propertyTitle = (string) $propertyStatement->fetchColumn();
    $clientStatement = Database::connection()->prepare('SELECT first_name, last_name FROM clients WHERE id = ? LIMIT 1');
    $clientStatement->execute([$data['client_id']]);
    $client = $clientStatement->fetch() ?: ['first_name' => '', 'last_name' => ''];
    ensure_notification(
        'new_reservation',
        'Nouvelle reservation',
        trim($propertyTitle . ' - ' . $client['first_name'] . ' ' . $client['last_name']),
        $reservationId,
        'reservation'
    );

    flash('notice', 'Reservation ajoutee avec succes.');
    log_activity('create', 'reservations', 'Reservation ajoutee #' . $reservationId);
    redirect('/reservations');
}

if ($route === '/reservations/show' && $readMethod) {
    Auth::requirePermission('reservations.view');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare(
        'SELECT r.*, p.title AS property_title, p.city, p.district, c.first_name, c.last_name, c.phone, c.email
        FROM reservations r
        JOIN properties p ON p.id = r.property_id
        JOIN clients c ON c.id = r.client_id
        WHERE r.id = ? LIMIT 1'
    );
    $statement->execute([$id]);
    $reservation = $statement->fetch();

    if (!$reservation) {
        http_response_code(404);
        $pageTitle = 'Reservation introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $pageTitle = 'Reservation #' . $reservation['id'] . ' - Locative';
    $user = Auth::user();

    require dirname(__DIR__) . '/resources/views/reservations/show.php';
    exit;
}

if ($route === '/reservations/edit' && $readMethod) {
    Auth::requirePermission('reservations.edit');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT * FROM reservations WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $reservation = $statement->fetch();

    if (!$reservation) {
        http_response_code(404);
        $pageTitle = 'Reservation introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $pageTitle = 'Modifier reservation - Locative';
    $user = Auth::user();
    $properties = Database::connection()->query('SELECT id, title, price_per_night FROM properties ORDER BY title')->fetchAll();
    $clients = Database::connection()->query('SELECT id, first_name, last_name FROM clients ORDER BY last_name, first_name')->fetchAll();
    $agents = Database::connection()->query('SELECT id, name FROM users WHERE status = "active" ORDER BY name')->fetchAll();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/reservations/form.php';
    exit;
}

if ($route === '/reservations/update' && $method === 'POST') {
    Auth::requirePermission('reservations.edit');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);
    $data = reservation_form_data($_POST);
    $errors = validate_reservation($data, $id);

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/reservations/edit?id=' . $id);
    }

    $totalAmount = reservation_total_amount($data['property_id'], $data['start_date'], $data['end_date']);
    $finalAmount = max(0, $totalAmount - $data['discount_amount']);
    $statement = Database::connection()->prepare(
        'UPDATE reservations SET property_id = ?, client_id = ?, agent_id = ?, start_date = ?, end_date = ?, checkin_time = ?,
        checkout_time = ?, status = ?, source = ?, total_amount = ?, gross_amount = ?, discount_amount = ?, final_amount = ?, notes = ? WHERE id = ?'
    );
    $statement->execute([
        $data['property_id'],
        $data['client_id'],
        $data['agent_id'] ?: null,
        $data['start_date'],
        $data['end_date'],
        $data['checkin_time'] ?: null,
        $data['checkout_time'] ?: null,
        $data['status'],
        $data['source'],
        $totalAmount,
        $totalAmount,
        $data['discount_amount'],
        $finalAmount,
        $data['notes'],
        $id,
    ]);

    refresh_reservation_financials($id);
    flash('notice', 'Reservation modifiee avec succes.');
    log_activity('update', 'reservations', 'Reservation modifiee #' . $id);
    redirect('/reservations');
}

if ($route === '/reservations/delete' && $method === 'POST') {
    Auth::requirePermission('reservations.delete');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);

    try {
        $statement = Database::connection()->prepare('DELETE FROM reservations WHERE id = ?');
        $statement->execute([$id]);
        flash('notice', 'Reservation supprimee avec succes.');
        log_activity('delete', 'reservations', 'Reservation supprimee #' . $id);
    } catch (Throwable $e) {
        flash('notice', 'Impossible de supprimer cette reservation car elle est liee a des paiements.');
    }

    redirect('/reservations');
}

if ($route === '/payments' && $readMethod) {
    Auth::requirePermission('payments.view');

    $pageTitle = 'Paiements - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $status = (string) ($_GET['status'] ?? '');
    $from = trim((string) ($_GET['from'] ?? ''));
    $to = trim((string) ($_GET['to'] ?? ''));
    $conditions = [];
    $params = [];

    if (array_key_exists($status, payment_statuses())) {
        $conditions[] = 'pay.status = ?';
        $params[] = $status;
    }

    if ($from !== '') {
        $conditions[] = 'pay.payment_date >= ?';
        $params[] = $from;
    }

    if ($to !== '') {
        $conditions[] = 'pay.payment_date <= ?';
        $params[] = $to;
    }

    $sql = 'SELECT pay.*, r.start_date, r.end_date, p.title AS property_title, c.first_name, c.last_name
        FROM payments pay
        JOIN reservations r ON r.id = pay.reservation_id
        JOIN properties p ON p.id = pay.property_id
        JOIN clients c ON c.id = pay.client_id';

    if ($conditions) {
        $sql .= ' WHERE ' . implode(' AND ', $conditions);
    }

    $sql .= ' ORDER BY pay.id DESC';
    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $payments = $statement->fetchAll();
    [$payments, $pagination] = paginate_items($payments, 15);

    $summary = [
        'paid' => (float) Database::connection()->query('SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status = "paid"')->fetchColumn(),
        'pending' => (float) Database::connection()->query('SELECT COALESCE(SUM(amount), 0) FROM payments WHERE status = "pending"')->fetchColumn(),
    ];

    require dirname(__DIR__) . '/resources/views/payments/index.php';
    exit;
}

if ($route === '/payments/create' && $readMethod) {
    Auth::requirePermission('payments.create');

    $pageTitle = 'Ajouter paiement - Locative';
    $user = Auth::user();
    $payment = [
        'reservation_id' => (int) ($_GET['reservation_id'] ?? 0),
        'amount' => '',
        'payment_method' => 'cash',
        'status' => 'pending',
        'payment_date' => date('Y-m-d'),
        'notes' => '',
    ];
    $reservations = Database::connection()
        ->query('SELECT r.id, r.start_date, r.end_date, r.total_amount, 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
            ORDER BY r.id DESC')
        ->fetchAll();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/payments/form.php';
    exit;
}

if ($route === '/payments/store' && $method === 'POST') {
    Auth::requirePermission('payments.create');
    verify_csrf();

    $data = payment_form_data($_POST);
    $errors = validate_payment($data);

    $reservationStatement = Database::connection()->prepare('SELECT property_id, client_id FROM reservations WHERE id = ? LIMIT 1');
    $reservationStatement->execute([$data['reservation_id']]);
    $reservation = $reservationStatement->fetch();

    if (!$reservation) {
        $errors[] = 'Reservation introuvable.';
    }

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/payments/create');
    }

    $statement = Database::connection()->prepare(
        'INSERT INTO payments (reservation_id, client_id, property_id, amount, payment_method, status, payment_date, notes)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
    );
    $statement->execute([
        $data['reservation_id'],
        $reservation['client_id'],
        $reservation['property_id'],
        $data['amount'],
        $data['payment_method'],
        $data['status'],
        $data['payment_date'] ?: null,
        $data['notes'],
    ]);

    refresh_reservation_financials($data['reservation_id']);
    flash('notice', 'Paiement ajoute avec succes.');
    log_activity('create', 'payments', 'Paiement ajoute #' . Database::connection()->lastInsertId());
    redirect('/payments');
}

if ($route === '/payments/show' && $readMethod) {
    Auth::requirePermission('payments.view');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare(
        'SELECT pay.*, r.start_date, r.end_date, r.total_amount, p.title AS property_title, c.first_name, c.last_name, c.phone, c.email
        FROM payments pay
        JOIN reservations r ON r.id = pay.reservation_id
        JOIN properties p ON p.id = pay.property_id
        JOIN clients c ON c.id = pay.client_id
        WHERE pay.id = ? LIMIT 1'
    );
    $statement->execute([$id]);
    $payment = $statement->fetch();

    if (!$payment) {
        http_response_code(404);
        $pageTitle = 'Paiement introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $pageTitle = 'Paiement #' . $payment['id'] . ' - Locative';
    $user = Auth::user();

    require dirname(__DIR__) . '/resources/views/payments/show.php';
    exit;
}

if ($route === '/payments/receipt' && $readMethod) {
    Auth::requirePermission('payments.view');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare(
        'SELECT pay.*, r.start_date, r.end_date, r.total_amount, p.title AS property_title, p.city, p.district,
            c.first_name, c.last_name, c.phone, c.email, c.identity_number
        FROM payments pay
        JOIN reservations r ON r.id = pay.reservation_id
        JOIN properties p ON p.id = pay.property_id
        JOIN clients c ON c.id = pay.client_id
        WHERE pay.id = ? LIMIT 1'
    );
    $statement->execute([$id]);
    $payment = $statement->fetch();

    if (!$payment) {
        http_response_code(404);
        $pageTitle = 'Recu introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $pageTitle = 'Recu paiement #' . $payment['id'] . ' - Locative';
    $user = Auth::user();
    $settings = app_settings();

    require dirname(__DIR__) . '/resources/views/payments/receipt.php';
    exit;
}

if ($route === '/payments/edit' && $readMethod) {
    Auth::requirePermission('payments.edit');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT * FROM payments WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $payment = $statement->fetch();

    if (!$payment) {
        http_response_code(404);
        $pageTitle = 'Paiement introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $pageTitle = 'Modifier paiement - Locative';
    $user = Auth::user();
    $reservations = Database::connection()
        ->query('SELECT r.id, r.start_date, r.end_date, r.total_amount, 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
            ORDER BY r.id DESC')
        ->fetchAll();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/payments/form.php';
    exit;
}

if ($route === '/payments/update' && $method === 'POST') {
    Auth::requirePermission('payments.edit');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);
    $oldStatement = Database::connection()->prepare('SELECT reservation_id FROM payments WHERE id = ? LIMIT 1');
    $oldStatement->execute([$id]);
    $oldReservationId = (int) $oldStatement->fetchColumn();

    $data = payment_form_data($_POST);
    $errors = validate_payment($data, $id);

    $reservationStatement = Database::connection()->prepare('SELECT property_id, client_id FROM reservations WHERE id = ? LIMIT 1');
    $reservationStatement->execute([$data['reservation_id']]);
    $reservation = $reservationStatement->fetch();

    if (!$reservation) {
        $errors[] = 'Reservation introuvable.';
    }

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/payments/edit?id=' . $id);
    }

    $statement = Database::connection()->prepare(
        'UPDATE payments SET reservation_id = ?, client_id = ?, property_id = ?, amount = ?, payment_method = ?,
        status = ?, payment_date = ?, notes = ? WHERE id = ?'
    );
    $statement->execute([
        $data['reservation_id'],
        $reservation['client_id'],
        $reservation['property_id'],
        $data['amount'],
        $data['payment_method'],
        $data['status'],
        $data['payment_date'] ?: null,
        $data['notes'],
        $id,
    ]);

    if ($oldReservationId > 0) {
        refresh_reservation_financials($oldReservationId);
    }
    refresh_reservation_financials($data['reservation_id']);
    flash('notice', 'Paiement modifie avec succes.');
    log_activity('update', 'payments', 'Paiement modifie #' . $id);
    redirect('/payments');
}

if ($route === '/payments/delete' && $method === 'POST') {
    Auth::requirePermission('payments.delete');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);
    $oldStatement = Database::connection()->prepare('SELECT reservation_id FROM payments WHERE id = ? LIMIT 1');
    $oldStatement->execute([$id]);
    $oldReservationId = (int) $oldStatement->fetchColumn();
    $statement = Database::connection()->prepare('DELETE FROM payments WHERE id = ?');
    $statement->execute([$id]);
    if ($oldReservationId > 0) {
        refresh_reservation_financials($oldReservationId);
    }
    flash('notice', 'Paiement supprime avec succes.');
    log_activity('delete', 'payments', 'Paiement supprime #' . $id);
    redirect('/payments');
}

if ($route === '/clients' && $readMethod) {
    Auth::requirePermission('clients.view');

    $pageTitle = 'Clients - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $search = trim((string) ($_GET['search'] ?? ''));
    $conditions = [];
    $params = [];

    if ($search !== '') {
        $conditions[] = '(first_name LIKE ? OR last_name LIKE ? OR phone LIKE ? OR email LIKE ? OR identity_number LIKE ?)';
        $term = '%' . $search . '%';
        $params = [$term, $term, $term, $term, $term];
    }

    $sql = 'SELECT * FROM clients';
    if ($conditions) {
        $sql .= ' WHERE ' . implode(' AND ', $conditions);
    }
    $sql .= ' ORDER BY id DESC';

    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $clients = $statement->fetchAll();
    [$clients, $pagination] = paginate_items($clients, 15);

    require dirname(__DIR__) . '/resources/views/clients/index.php';
    exit;
}

if ($route === '/clients/create' && $readMethod) {
    Auth::requirePermission('clients.create');

    $pageTitle = 'Ajouter client - Locative';
    $user = Auth::user();
    $client = [
        'first_name' => '',
        'last_name' => '',
        'phone' => '',
        'email' => '',
        'identity_number' => '',
        'address' => '',
        'notes' => '',
    ];
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/clients/form.php';
    exit;
}

if ($route === '/clients/store' && $method === 'POST') {
    Auth::requirePermission('clients.create');
    verify_csrf();

    $data = client_form_data($_POST);
    $errors = validate_client($data);

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/clients/create');
    }

    $statement = Database::connection()->prepare(
        'INSERT INTO clients (first_name, last_name, phone, email, identity_number, address, notes)
        VALUES (?, ?, ?, ?, ?, ?, ?)'
    );
    $statement->execute([
        $data['first_name'],
        $data['last_name'],
        $data['phone'],
        $data['email'],
        $data['identity_number'],
        $data['address'],
        $data['notes'],
    ]);

    flash('notice', 'Client ajoute avec succes.');
    log_activity('create', 'clients', 'Client ajoute: ' . $data['first_name'] . ' ' . $data['last_name']);
    redirect('/clients');
}

if ($route === '/clients/show' && $readMethod) {
    Auth::requirePermission('clients.view');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT * FROM clients WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $client = $statement->fetch();

    if (!$client) {
        http_response_code(404);
        $pageTitle = 'Client introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $historyStatement = Database::connection()->prepare(
        'SELECT r.*, p.title AS property_title FROM reservations r
        LEFT JOIN properties p ON p.id = r.property_id
        WHERE r.client_id = ? ORDER BY r.start_date DESC'
    );
    $historyStatement->execute([$id]);
    $reservations = $historyStatement->fetchAll();

    $pageTitle = $client['first_name'] . ' ' . $client['last_name'] . ' - Locative';
    $user = Auth::user();

    require dirname(__DIR__) . '/resources/views/clients/show.php';
    exit;
}

if ($route === '/clients/edit' && $readMethod) {
    Auth::requirePermission('clients.edit');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT * FROM clients WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $client = $statement->fetch();

    if (!$client) {
        http_response_code(404);
        $pageTitle = 'Client introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $pageTitle = 'Modifier client - Locative';
    $user = Auth::user();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/clients/form.php';
    exit;
}

if ($route === '/clients/update' && $method === 'POST') {
    Auth::requirePermission('clients.edit');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);
    $data = client_form_data($_POST);
    $errors = validate_client($data);

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/clients/edit?id=' . $id);
    }

    $statement = Database::connection()->prepare(
        'UPDATE clients SET first_name = ?, last_name = ?, phone = ?, email = ?, identity_number = ?, address = ?, notes = ? WHERE id = ?'
    );
    $statement->execute([
        $data['first_name'],
        $data['last_name'],
        $data['phone'],
        $data['email'],
        $data['identity_number'],
        $data['address'],
        $data['notes'],
        $id,
    ]);

    flash('notice', 'Client modifie avec succes.');
    log_activity('update', 'clients', 'Client modifie #' . $id . ': ' . $data['first_name'] . ' ' . $data['last_name']);
    redirect('/clients');
}

if ($route === '/clients/delete' && $method === 'POST') {
    Auth::requirePermission('clients.delete');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);

    try {
        $statement = Database::connection()->prepare('DELETE FROM clients WHERE id = ?');
        $statement->execute([$id]);
        flash('notice', 'Client supprime avec succes.');
        log_activity('delete', 'clients', 'Client supprime #' . $id);
    } catch (Throwable $e) {
        flash('notice', 'Impossible de supprimer ce client car il est lie a des reservations ou paiements.');
    }

    redirect('/clients');
}

if ($route === '/expenses' && $readMethod) {
    Auth::requirePermission('expenses.view');

    $pageTitle = 'Depenses - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    [$from, $to] = period_dates($_GET);
    $category = (string) ($_GET['category'] ?? '');
    $propertyId = (int) ($_GET['property_id'] ?? 0);
    $conditions = ['e.expense_date BETWEEN ? AND ?'];
    $params = [$from, $to];

    if (array_key_exists($category, expense_categories())) {
        $conditions[] = 'e.category = ?';
        $params[] = $category;
    }

    if ($propertyId > 0) {
        $conditions[] = 'e.property_id = ?';
        $params[] = $propertyId;
    }

    $sql = 'SELECT e.*, p.title AS property_title FROM expenses e LEFT JOIN properties p ON p.id = e.property_id WHERE ' . implode(' AND ', $conditions) . ' ORDER BY e.expense_date DESC, e.id DESC';
    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $expenses = $statement->fetchAll();

    $total = array_sum(array_map(static fn ($row) => (float) $row['amount'], $expenses));
    [$expenses, $pagination] = paginate_items($expenses, 15);
    $properties = Database::connection()->query('SELECT id, title FROM properties ORDER BY title')->fetchAll();

    $dailyStatement = Database::connection()->prepare('SELECT expense_date AS label, SUM(amount) AS total FROM expenses WHERE expense_date BETWEEN ? AND ? GROUP BY expense_date ORDER BY expense_date');
    $dailyStatement->execute([$from, $to]);
    $dailyExpenses = $dailyStatement->fetchAll();

    $monthlyStatement = Database::connection()->prepare('SELECT DATE_FORMAT(expense_date, "%Y-%m") AS label, SUM(amount) AS total FROM expenses WHERE expense_date BETWEEN ? AND ? GROUP BY DATE_FORMAT(expense_date, "%Y-%m") ORDER BY label');
    $monthlyStatement->execute([$from, $to]);
    $monthlyExpenses = $monthlyStatement->fetchAll();

    $yearlyStatement = Database::connection()->prepare('SELECT YEAR(expense_date) AS label, SUM(amount) AS total FROM expenses WHERE expense_date BETWEEN ? AND ? GROUP BY YEAR(expense_date) ORDER BY label');
    $yearlyStatement->execute([$from, $to]);
    $yearlyExpenses = $yearlyStatement->fetchAll();

    $propertyStatement = Database::connection()->prepare('SELECT COALESCE(p.title, "Sans bien") AS label, SUM(e.amount) AS total FROM expenses e LEFT JOIN properties p ON p.id = e.property_id WHERE e.expense_date BETWEEN ? AND ? GROUP BY e.property_id, p.title ORDER BY total DESC');
    $propertyStatement->execute([$from, $to]);
    $propertyExpenses = $propertyStatement->fetchAll();

    require dirname(__DIR__) . '/resources/views/expenses/index.php';
    exit;
}

if ($route === '/expenses/create' && $readMethod) {
    Auth::requirePermission('expenses.create');
    $pageTitle = 'Ajouter depense - Locative';
    $user = Auth::user();
    $expense = ['expense_date' => date('Y-m-d'), 'category' => 'other', 'amount' => '', 'description' => '', 'property_id' => 0];
    $properties = Database::connection()->query('SELECT id, title FROM properties ORDER BY title')->fetchAll();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);
    require dirname(__DIR__) . '/resources/views/expenses/form.php';
    exit;
}

if ($route === '/expenses/store' && $method === 'POST') {
    Auth::requirePermission('expenses.create');
    verify_csrf();
    $data = expense_form_data($_POST);
    $errors = validate_expense($data);
    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/expenses/create');
    }
    $statement = Database::connection()->prepare('INSERT INTO expenses (expense_date, category, amount, description, property_id, created_by) VALUES (?, ?, ?, ?, ?, ?)');
    $statement->execute([$data['expense_date'], $data['category'], $data['amount'], $data['description'], $data['property_id'] ?: null, $_SESSION['user_id'] ?? null]);
    flash('notice', 'Depense ajoutee avec succes.');
    log_activity('create', 'expenses', 'Depense ajoutee #' . Database::connection()->lastInsertId());
    redirect('/expenses');
}

if ($route === '/expenses/edit' && $readMethod) {
    Auth::requirePermission('expenses.edit');
    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT * FROM expenses WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $expense = $statement->fetch();
    if (!$expense) {
        http_response_code(404);
        $pageTitle = 'Depense introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }
    $pageTitle = 'Modifier depense - Locative';
    $user = Auth::user();
    $properties = Database::connection()->query('SELECT id, title FROM properties ORDER BY title')->fetchAll();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);
    require dirname(__DIR__) . '/resources/views/expenses/form.php';
    exit;
}

if ($route === '/expenses/update' && $method === 'POST') {
    Auth::requirePermission('expenses.edit');
    verify_csrf();
    $id = (int) ($_POST['id'] ?? 0);
    $data = expense_form_data($_POST);
    $errors = validate_expense($data);
    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/expenses/edit?id=' . $id);
    }
    $statement = Database::connection()->prepare('UPDATE expenses SET expense_date = ?, category = ?, amount = ?, description = ?, property_id = ? WHERE id = ?');
    $statement->execute([$data['expense_date'], $data['category'], $data['amount'], $data['description'], $data['property_id'] ?: null, $id]);
    flash('notice', 'Depense modifiee avec succes.');
    log_activity('update', 'expenses', 'Depense modifiee #' . $id);
    redirect('/expenses');
}

if ($route === '/expenses/delete' && $method === 'POST') {
    Auth::requirePermission('expenses.delete');
    verify_csrf();
    $id = (int) ($_POST['id'] ?? 0);
    $statement = Database::connection()->prepare('DELETE FROM expenses WHERE id = ?');
    $statement->execute([$id]);
    flash('notice', 'Depense supprimee avec succes.');
    log_activity('delete', 'expenses', 'Depense supprimee #' . $id);
    redirect('/expenses');
}

if ($route === '/long-rentals' && $readMethod) {
    Auth::requirePermission('long_rentals.view');
    sync_long_term_unpaid_months();

    $pageTitle = 'Gestion Locative Longue Duree - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $summary = [
        'properties' => (int) Database::connection()->query('SELECT COUNT(DISTINCT property_id) FROM long_term_contracts')->fetchColumn(),
        'activeTenants' => (int) Database::connection()->query('SELECT COUNT(DISTINCT tenant_id) FROM long_term_contracts WHERE status = "active"')->fetchColumn(),
        'activeContracts' => (int) Database::connection()->query('SELECT COUNT(*) FROM long_term_contracts WHERE status = "active"')->fetchColumn(),
        'paid' => (float) Database::connection()->query('SELECT COALESCE(SUM(amount_paid), 0) FROM long_term_payments')->fetchColumn(),
        'remaining' => (float) Database::connection()->query('SELECT COALESCE(SUM(amount_remaining), 0) FROM long_term_payments WHERE status != "paid"')->fetchColumn(),
        'unpaidMonths' => (int) Database::connection()->query('SELECT COUNT(*) FROM long_term_payments WHERE status = "unpaid"')->fetchColumn(),
        'partialMonths' => (int) Database::connection()->query('SELECT COUNT(*) FROM long_term_payments WHERE status = "partial"')->fetchColumn(),
    ];
    require dirname(__DIR__) . '/resources/views/long_rentals/index.php';
    exit;
}

if ($route === '/long-rentals/properties' && $readMethod) {
    Auth::requirePermission('long_rentals.view');
    sync_long_term_unpaid_months();

    $pageTitle = 'Biens longue duree - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $status = (string) ($_GET['status'] ?? '');
    $search = trim((string) ($_GET['search'] ?? ''));
    $conditions = [];
    $params = [];
    if ($search !== '') {
        $conditions[] = '(p.title LIKE ? OR p.city LIKE ? OR p.address LIKE ? OR t.full_name LIKE ?)';
        $term = '%' . $search . '%';
        $params = [$term, $term, $term, $term];
    }
    $where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
    $having = '';
    if ($status === 'occupied') {
        $having = 'HAVING has_active_contract = 1';
    } elseif ($status === 'available') {
        $having = 'HAVING has_active_contract = 0';
    }
    $sql = 'SELECT p.id, p.title, p.city, p.district, p.address, COUNT(c.id) AS contracts_count, MAX(CASE WHEN c.status = "active" THEN 1 ELSE 0 END) AS has_active_contract, GROUP_CONCAT(DISTINCT t.full_name ORDER BY t.full_name SEPARATOR ", ") AS tenant_names, COALESCE(SUM(CASE WHEN c.status = "active" THEN c.monthly_rent ELSE 0 END), 0) AS active_monthly_rent, MIN(CASE WHEN c.status = "active" THEN c.start_date ELSE NULL END) AS active_start_date, MAX(CASE WHEN c.status = "active" THEN c.end_date ELSE NULL END) AS active_end_date FROM properties p JOIN long_term_contracts c ON c.property_id = p.id JOIN long_term_tenants t ON t.id = c.tenant_id ' . $where . ' GROUP BY p.id, p.title, p.city, p.district, p.address ' . $having . ' ORDER BY p.title';
    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $properties = $statement->fetchAll();
    [$properties, $pagination] = paginate_items($properties, 15);
    require dirname(__DIR__) . '/resources/views/long_rentals/properties_index.php';
    exit;
}

if ($route === '/long-rentals/situations' && $readMethod) {
    Auth::requirePermission('long_rentals.view');
    sync_long_term_unpaid_months();

    $pageTitle = 'Situation des locataires - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $search = trim((string) ($_GET['search'] ?? ''));
    $conditions = [];
    $params = [];
    if ($search !== '') {
        $conditions[] = 't.full_name LIKE ? OR t.phone LIKE ? OR t.identity_number LIKE ?';
        $term = '%' . $search . '%';
        $params = [$term, $term, $term];
    }
    $where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
    $sql = 'SELECT t.*, COUNT(lp.id) AS total_months, COALESCE(SUM(lp.status = "paid"), 0) AS paid_months, COALESCE(SUM(lp.status = "unpaid"), 0) AS unpaid_months, COALESCE(SUM(lp.status = "partial"), 0) AS partial_months, COALESCE(SUM(lp.amount_paid), 0) AS total_paid, COALESCE(SUM(lp.amount_remaining), 0) AS total_remaining, MAX(lp.payment_date) AS last_payment_date FROM long_term_tenants t LEFT JOIN long_term_contracts c ON c.tenant_id = t.id LEFT JOIN long_term_payments lp ON lp.contract_id = c.id ' . $where . ' GROUP BY t.id ORDER BY total_remaining DESC, unpaid_months DESC, partial_months DESC, t.full_name';
    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $situations = $statement->fetchAll();
    [$situations, $pagination] = paginate_items($situations, 15);
    require dirname(__DIR__) . '/resources/views/long_rentals/situations_index.php';
    exit;
}

if ($route === '/long-rentals/tenants' && $readMethod) {
    Auth::requirePermission('long_rentals.view');
    $pageTitle = 'Locataires longue duree - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $search = trim((string) ($_GET['search'] ?? ''));
    $sql = 'SELECT * FROM long_term_tenants';
    $params = [];
    if ($search !== '') {
        $sql .= ' WHERE full_name LIKE ? OR phone LIKE ? OR email LIKE ? OR identity_number LIKE ?';
        $term = '%' . $search . '%';
        $params = [$term, $term, $term, $term];
    }
    $sql .= ' ORDER BY id DESC';
    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $tenants = $statement->fetchAll();
    [$tenants, $pagination] = paginate_items($tenants, 15);
    require dirname(__DIR__) . '/resources/views/long_rentals/tenants_index.php';
    exit;
}

if ($route === '/long-rentals/tenants/show' && $readMethod) {
    Auth::requirePermission('long_rentals.view');
    sync_long_term_unpaid_months();

    $tenantId = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT * FROM long_term_tenants WHERE id = ? LIMIT 1');
    $statement->execute([$tenantId]);
    $tenant = $statement->fetch();
    if (!$tenant) {
        http_response_code(404);
        $pageTitle = 'Locataire introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $summaryStatement = Database::connection()->prepare('SELECT COUNT(lp.id) AS total_months, COALESCE(SUM(lp.status = "paid"), 0) AS paid_months, COALESCE(SUM(lp.status = "unpaid"), 0) AS unpaid_months, COALESCE(SUM(lp.status = "partial"), 0) AS partial_months, COALESCE(SUM(lp.amount_paid), 0) AS total_paid, COALESCE(SUM(lp.amount_remaining), 0) AS total_remaining, MAX(lp.payment_date) AS last_payment_date FROM long_term_contracts c LEFT JOIN long_term_payments lp ON lp.contract_id = c.id WHERE c.tenant_id = ?');
    $summaryStatement->execute([$tenantId]);
    $summary = $summaryStatement->fetch() ?: ['total_months' => 0, 'paid_months' => 0, 'unpaid_months' => 0, 'partial_months' => 0, 'total_paid' => 0, 'total_remaining' => 0, 'last_payment_date' => null];

    $contractsStatement = Database::connection()->prepare('SELECT c.*, p.title AS property_title, p.city AS property_city FROM long_term_contracts c JOIN properties p ON p.id = c.property_id WHERE c.tenant_id = ? ORDER BY c.start_date DESC, c.id DESC');
    $contractsStatement->execute([$tenantId]);
    $contracts = $contractsStatement->fetchAll();

    $paymentsStatement = Database::connection()->prepare('SELECT lp.*, c.id AS contract_number, p.title AS property_title FROM long_term_payments lp JOIN long_term_contracts c ON c.id = lp.contract_id JOIN properties p ON p.id = c.property_id WHERE c.tenant_id = ? ORDER BY lp.paid_month DESC, lp.id DESC');
    $paymentsStatement->execute([$tenantId]);
    $payments = $paymentsStatement->fetchAll();

    $pageTitle = 'Situation locataire - Locative';
    $user = Auth::user();
    require dirname(__DIR__) . '/resources/views/long_rentals/tenant_show.php';
    exit;
}

if (in_array($route, ['/long-rentals/tenants/create', '/long-rentals/tenants/edit'], true) && $readMethod) {
    Auth::requirePermission($route === '/long-rentals/tenants/create' ? 'long_rentals.create' : 'long_rentals.edit');
    $tenant = ['full_name' => '', 'phone' => '', 'email' => '', 'identity_number' => '', 'address' => ''];
    if ($route === '/long-rentals/tenants/edit') {
        $statement = Database::connection()->prepare('SELECT * FROM long_term_tenants WHERE id = ? LIMIT 1');
        $statement->execute([(int) ($_GET['id'] ?? 0)]);
        $tenant = $statement->fetch();
        if (!$tenant) {
            http_response_code(404);
            $pageTitle = 'Locataire introuvable - Locative';
            require dirname(__DIR__) . '/resources/views/errors/404.php';
            exit;
        }
    }
    $pageTitle = 'Locataire longue duree - Locative';
    $user = Auth::user();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);
    require dirname(__DIR__) . '/resources/views/long_rentals/tenant_form.php';
    exit;
}

if (in_array($route, ['/long-rentals/tenants/store', '/long-rentals/tenants/update'], true) && $method === 'POST') {
    Auth::requirePermission($route === '/long-rentals/tenants/store' ? 'long_rentals.create' : 'long_rentals.edit');
    verify_csrf();
    $id = (int) ($_POST['id'] ?? 0);
    $data = [
        'full_name' => trim((string) ($_POST['full_name'] ?? '')),
        'phone' => trim((string) ($_POST['phone'] ?? '')),
        'email' => trim((string) ($_POST['email'] ?? '')),
        'identity_number' => trim((string) ($_POST['identity_number'] ?? '')),
        'address' => trim((string) ($_POST['address'] ?? '')),
    ];
    $errors = [];
    if ($data['full_name'] === '') {
        $errors[] = 'Le nom complet est obligatoire.';
    }
    if ($data['email'] !== '' && !filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
        $errors[] = 'Email invalide.';
    }
    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect($route === '/long-rentals/tenants/store' ? '/long-rentals/tenants/create' : '/long-rentals/tenants/edit?id=' . $id);
    }
    if ($route === '/long-rentals/tenants/store') {
        $statement = Database::connection()->prepare('INSERT INTO long_term_tenants (full_name, phone, email, identity_number, address) VALUES (?, ?, ?, ?, ?)');
        $statement->execute([$data['full_name'], $data['phone'], $data['email'], $data['identity_number'], $data['address']]);
        flash('notice', 'Locataire ajoute avec succes.');
    } else {
        $statement = Database::connection()->prepare('UPDATE long_term_tenants SET full_name = ?, phone = ?, email = ?, identity_number = ?, address = ? WHERE id = ?');
        $statement->execute([$data['full_name'], $data['phone'], $data['email'], $data['identity_number'], $data['address'], $id]);
        flash('notice', 'Locataire modifie avec succes.');
    }
    redirect('/long-rentals/tenants');
}

if ($route === '/long-rentals/tenants/delete' && $method === 'POST') {
    Auth::requirePermission('long_rentals.delete');
    verify_csrf();
    try {
        $statement = Database::connection()->prepare('DELETE FROM long_term_tenants WHERE id = ?');
        $statement->execute([(int) ($_POST['id'] ?? 0)]);
        flash('notice', 'Locataire supprime avec succes.');
    } catch (Throwable $e) {
        flash('notice', 'Impossible de supprimer ce locataire car il est lie a un contrat.');
    }
    redirect('/long-rentals/tenants');
}

if ($route === '/long-rentals/contracts' && $readMethod) {
    Auth::requirePermission('long_rentals.view');
    sync_long_term_unpaid_months();

    $pageTitle = 'Contrats longue duree - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    [$from, $to] = period_dates($_GET);
    $status = (string) ($_GET['status'] ?? '');
    $conditions = ['c.start_date <= ? AND (c.end_date IS NULL OR c.end_date >= ?)'];
    $params = [$to, $from];
    if (array_key_exists($status, long_contract_statuses())) {
        $conditions[] = 'c.status = ?';
        $params[] = $status;
    }
    $sql = 'SELECT c.*, t.full_name AS tenant_name, p.title AS property_title FROM long_term_contracts c JOIN long_term_tenants t ON t.id = c.tenant_id JOIN properties p ON p.id = c.property_id WHERE ' . implode(' AND ', $conditions) . ' ORDER BY c.id DESC';
    $statement = Database::connection()->prepare($sql);
    $statement->execute($params);
    $contracts = $statement->fetchAll();
    [$contracts, $pagination] = paginate_items($contracts, 15);
    $summary = [
        'activeTenants' => (int) Database::connection()->query('SELECT COUNT(DISTINCT tenant_id) FROM long_term_contracts WHERE status = "active"')->fetchColumn(),
        'monthlyRent' => (float) Database::connection()->query('SELECT COALESCE(SUM(monthly_rent), 0) FROM long_term_contracts WHERE status = "active"')->fetchColumn(),
        'pendingRent' => (float) Database::connection()->query('SELECT COALESCE(SUM(amount_remaining), 0) FROM long_term_payments WHERE status != "paid"')->fetchColumn(),
    ];
    require dirname(__DIR__) . '/resources/views/long_rentals/contracts_index.php';
    exit;
}

if (in_array($route, ['/long-rentals/contracts/create', '/long-rentals/contracts/edit'], true) && $readMethod) {
    Auth::requirePermission($route === '/long-rentals/contracts/create' ? 'long_rentals.create' : 'long_rentals.edit');
    $contract = ['tenant_id' => 0, 'property_id' => 0, 'start_date' => date('Y-m-d'), 'end_date' => '', 'monthly_rent' => '', 'deposit_amount' => '', 'observations' => '', 'status' => 'active'];
    if ($route === '/long-rentals/contracts/edit') {
        $statement = Database::connection()->prepare('SELECT * FROM long_term_contracts WHERE id = ? LIMIT 1');
        $statement->execute([(int) ($_GET['id'] ?? 0)]);
        $contract = $statement->fetch();
        if (!$contract) {
            http_response_code(404);
            $pageTitle = 'Contrat introuvable - Locative';
            require dirname(__DIR__) . '/resources/views/errors/404.php';
            exit;
        }
    }
    $pageTitle = 'Contrat longue duree - Locative';
    $user = Auth::user();
    $tenants = Database::connection()->query('SELECT id, full_name FROM long_term_tenants ORDER BY full_name')->fetchAll();
    $properties = Database::connection()->query('SELECT id, title FROM properties ORDER BY title')->fetchAll();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);
    require dirname(__DIR__) . '/resources/views/long_rentals/contract_form.php';
    exit;
}

if (in_array($route, ['/long-rentals/contracts/store', '/long-rentals/contracts/update'], true) && $method === 'POST') {
    Auth::requirePermission($route === '/long-rentals/contracts/store' ? 'long_rentals.create' : 'long_rentals.edit');
    verify_csrf();
    $id = (int) ($_POST['id'] ?? 0);
    $data = [
        'tenant_id' => max(0, (int) ($_POST['tenant_id'] ?? 0)),
        'property_id' => max(0, (int) ($_POST['property_id'] ?? 0)),
        'start_date' => trim((string) ($_POST['start_date'] ?? '')),
        'end_date' => trim((string) ($_POST['end_date'] ?? '')),
        'monthly_rent' => max(0, (float) ($_POST['monthly_rent'] ?? 0)),
        'deposit_amount' => max(0, (float) ($_POST['deposit_amount'] ?? 0)),
        'observations' => trim((string) ($_POST['observations'] ?? '')),
        'status' => array_key_exists($_POST['status'] ?? '', long_contract_statuses()) ? (string) $_POST['status'] : 'active',
    ];
    $errors = [];
    if ($data['tenant_id'] <= 0) { $errors[] = 'Le locataire est obligatoire.'; }
    if ($data['property_id'] <= 0) { $errors[] = 'Le bien est obligatoire.'; }
    if (!valid_date($data['start_date'])) { $errors[] = 'La date debut est invalide.'; }
    if ($data['end_date'] !== '' && !valid_date($data['end_date'])) { $errors[] = 'La date fin est invalide.'; }
    if ($data['monthly_rent'] <= 0) { $errors[] = 'Le loyer mensuel doit etre superieur a zero.'; }
    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect($route === '/long-rentals/contracts/store' ? '/long-rentals/contracts/create' : '/long-rentals/contracts/edit?id=' . $id);
    }
    if ($route === '/long-rentals/contracts/store') {
        $statement = Database::connection()->prepare('INSERT INTO long_term_contracts (tenant_id, property_id, start_date, end_date, monthly_rent, deposit_amount, observations, status, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)');
        $statement->execute([$data['tenant_id'], $data['property_id'], $data['start_date'], $data['end_date'] ?: null, $data['monthly_rent'], $data['deposit_amount'], $data['observations'], $data['status'], $_SESSION['user_id'] ?? null]);
        flash('notice', 'Contrat ajoute avec succes.');
    } else {
        $statement = Database::connection()->prepare('UPDATE long_term_contracts SET tenant_id = ?, property_id = ?, start_date = ?, end_date = ?, monthly_rent = ?, deposit_amount = ?, observations = ?, status = ? WHERE id = ?');
        $statement->execute([$data['tenant_id'], $data['property_id'], $data['start_date'], $data['end_date'] ?: null, $data['monthly_rent'], $data['deposit_amount'], $data['observations'], $data['status'], $id]);
        flash('notice', 'Contrat modifie avec succes.');
    }
    redirect('/long-rentals/contracts');
}

if ($route === '/long-rentals/contracts/delete' && $method === 'POST') {
    Auth::requirePermission('long_rentals.delete');
    verify_csrf();
    try {
        $statement = Database::connection()->prepare('DELETE FROM long_term_contracts WHERE id = ?');
        $statement->execute([(int) ($_POST['id'] ?? 0)]);
        flash('notice', 'Contrat supprime avec succes.');
    } catch (Throwable $e) {
        flash('notice', 'Impossible de supprimer ce contrat car il est lie a des paiements.');
    }
    redirect('/long-rentals/contracts');
}

if ($route === '/long-rentals/payments' && $readMethod) {
    Auth::requirePermission('long_rentals.view');
    sync_long_term_unpaid_months();

    $pageTitle = 'Paiements longue duree - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    [$from, $to] = period_dates(['from' => ($_GET['from'] ?? date('Y-m-01')), 'to' => ($_GET['to'] ?? date('Y-m-t'))]);
    $sql = 'SELECT lp.*, c.monthly_rent, t.full_name AS tenant_name, p.title AS property_title FROM long_term_payments lp JOIN long_term_contracts c ON c.id = lp.contract_id JOIN long_term_tenants t ON t.id = c.tenant_id JOIN properties p ON p.id = c.property_id WHERE CONCAT(lp.paid_month, "-01") BETWEEN ? AND ? ORDER BY lp.paid_month DESC, lp.id DESC';
    $statement = Database::connection()->prepare($sql);
    $statement->execute([$from, $to]);
    $payments = $statement->fetchAll();
    $summary = [
        'paid' => (float) array_sum(array_map(static fn ($row) => (float) $row['amount_paid'], $payments)),
        'remaining' => (float) array_sum(array_map(static fn ($row) => (float) $row['amount_remaining'], $payments)),
    ];
    [$payments, $pagination] = paginate_items($payments, 15);
    require dirname(__DIR__) . '/resources/views/long_rentals/payments_index.php';
    exit;
}

if (in_array($route, ['/long-rentals/payments/create', '/long-rentals/payments/edit'], true) && $readMethod) {
    Auth::requirePermission($route === '/long-rentals/payments/create' ? 'long_rentals.create' : 'long_rentals.edit');
    $payment = ['contract_id' => (int) ($_GET['contract_id'] ?? 0), 'paid_month' => date('Y-m'), 'due_amount' => '', 'amount_paid' => '', 'payment_date' => date('Y-m-d'), 'notes' => ''];
    if ($route === '/long-rentals/payments/edit') {
        $statement = Database::connection()->prepare('SELECT * FROM long_term_payments WHERE id = ? LIMIT 1');
        $statement->execute([(int) ($_GET['id'] ?? 0)]);
        $payment = $statement->fetch();
        if (!$payment) {
            http_response_code(404);
            $pageTitle = 'Paiement introuvable - Locative';
            require dirname(__DIR__) . '/resources/views/errors/404.php';
            exit;
        }
    }
    $pageTitle = 'Paiement longue duree - Locative';
    $user = Auth::user();
    $contracts = Database::connection()->query('SELECT c.id, c.monthly_rent, t.full_name AS tenant_name, p.title AS property_title FROM long_term_contracts c JOIN long_term_tenants t ON t.id = c.tenant_id JOIN properties p ON p.id = c.property_id ORDER BY c.id DESC')->fetchAll();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);
    require dirname(__DIR__) . '/resources/views/long_rentals/payment_form.php';
    exit;
}

if (in_array($route, ['/long-rentals/payments/store', '/long-rentals/payments/update'], true) && $method === 'POST') {
    Auth::requirePermission($route === '/long-rentals/payments/store' ? 'long_rentals.create' : 'long_rentals.edit');
    verify_csrf();
    $id = (int) ($_POST['id'] ?? 0);
    $contractId = max(0, (int) ($_POST['contract_id'] ?? 0));
    $paidMonth = month_value($_POST['paid_month'] ?? '');
    $dueAmount = max(0, (float) ($_POST['due_amount'] ?? 0));
    $amountPaid = max(0, (float) ($_POST['amount_paid'] ?? 0));
    $remaining = max(0, $dueAmount - $amountPaid);
    $status = $remaining <= 0.001 ? 'paid' : ($amountPaid > 0 ? 'partial' : 'unpaid');
    $paymentDate = trim((string) ($_POST['payment_date'] ?? ''));
    $notes = trim((string) ($_POST['notes'] ?? ''));
    $errors = [];
    if ($contractId <= 0) { $errors[] = 'Le contrat est obligatoire.'; }
    if ($dueAmount <= 0) { $errors[] = 'Le montant du loyer est obligatoire.'; }
    if ($paymentDate !== '' && !valid_date($paymentDate)) { $errors[] = 'La date paiement est invalide.'; }
    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect($route === '/long-rentals/payments/store' ? '/long-rentals/payments/create' : '/long-rentals/payments/edit?id=' . $id);
    }
    try {
        if ($route === '/long-rentals/payments/store') {
            $statement = Database::connection()->prepare(
                'INSERT INTO long_term_payments (contract_id, paid_month, due_amount, amount_paid, amount_remaining, payment_date, status, notes)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE
                    due_amount = VALUES(due_amount),
                    amount_paid = VALUES(amount_paid),
                    amount_remaining = VALUES(amount_remaining),
                    payment_date = VALUES(payment_date),
                    status = VALUES(status),
                    notes = VALUES(notes)'
            );
            $statement->execute([$contractId, $paidMonth, $dueAmount, $amountPaid, $remaining, $paymentDate ?: null, $status, $notes]);
            flash('notice', 'Paiement longue duree enregistre avec succes.');
        } else {
            $statement = Database::connection()->prepare('UPDATE long_term_payments SET contract_id = ?, paid_month = ?, due_amount = ?, amount_paid = ?, amount_remaining = ?, payment_date = ?, status = ?, notes = ? WHERE id = ?');
            $statement->execute([$contractId, $paidMonth, $dueAmount, $amountPaid, $remaining, $paymentDate ?: null, $status, $notes, $id]);
            flash('notice', 'Paiement longue duree modifie avec succes.');
        }
    } catch (Throwable $e) {
        $_SESSION['form_errors'] = ['Ce mois existe deja pour ce contrat.'];
        redirect($route === '/long-rentals/payments/store' ? '/long-rentals/payments/create' : '/long-rentals/payments/edit?id=' . $id);
    }
    redirect('/long-rentals/payments');
}

if ($route === '/long-rentals/payments/delete' && $method === 'POST') {
    Auth::requirePermission('long_rentals.delete');
    verify_csrf();
    $statement = Database::connection()->prepare('DELETE FROM long_term_payments WHERE id = ?');
    $statement->execute([(int) ($_POST['id'] ?? 0)]);
    flash('notice', 'Paiement longue duree supprime avec succes.');
    redirect('/long-rentals/payments');
}

if ($route === '/notification-settings' && $readMethod) {
    Auth::requirePermission('users.view');
    $pageTitle = 'Parametres notifications - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $settings = notification_settings();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);
    require dirname(__DIR__) . '/resources/views/notifications/settings.php';
    exit;
}

if ($route === '/notification-settings/update' && $method === 'POST') {
    Auth::requirePermission('users.view');
    verify_csrf();
    $days = max(0, min(365, (int) ($_POST['arrival_days_before'] ?? 1)));
    $emailRecipients = trim((string) ($_POST['email_recipients'] ?? ''));
    $emailFromName = trim((string) ($_POST['email_from_name'] ?? 'Locative'));
    $emailFromAddress = trim((string) ($_POST['email_from_address'] ?? ''));
    $emailErrors = [];
    foreach (preg_split('/[,;\r\n]+/', $emailRecipients) ?: [] as $recipient) {
        $recipient = trim($recipient);
        if ($recipient !== '' && !filter_var($recipient, FILTER_VALIDATE_EMAIL)) {
            $emailErrors[] = 'Email destinataire invalide: ' . $recipient;
        }
    }
    if ($emailFromAddress !== '' && !filter_var($emailFromAddress, FILTER_VALIDATE_EMAIL)) {
        $emailErrors[] = 'Email expediteur invalide.';
    }
    if ($emailErrors) {
        $_SESSION['form_errors'] = $emailErrors;
        redirect('/notification-settings');
    }
    $statement = Database::connection()->prepare('REPLACE INTO notification_settings (id, enabled, arrival_days_before, alert_new_reservation, alert_arrival, alert_departure, alert_overdue_payment, alert_remaining_balance, email_enabled, email_recipients, email_from_name, email_from_address) VALUES (1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
    $statement->execute([
        isset($_POST['enabled']) ? 1 : 0,
        $days,
        isset($_POST['alert_new_reservation']) ? 1 : 0,
        isset($_POST['alert_arrival']) ? 1 : 0,
        isset($_POST['alert_departure']) ? 1 : 0,
        isset($_POST['alert_overdue_payment']) ? 1 : 0,
        isset($_POST['alert_remaining_balance']) ? 1 : 0,
        isset($_POST['email_enabled']) ? 1 : 0,
        $emailRecipients,
        $emailFromName,
        $emailFromAddress,
    ]);
    flash('notice', 'Parametres notifications enregistres.');
    redirect('/notification-settings');
}

if ($route === '/advanced-reports' && $readMethod) {
    Auth::requirePermission('statistics.view');
    [$from, $to] = period_dates($_GET);
    $pageTitle = 'Rapports avances - Locative';
    $user = Auth::user();
    $days = days_between_inclusive($from, $to);

    $profitStatement = Database::connection()->prepare('SELECT p.title, p.city,
        COALESCE((SELECT SUM(amount) FROM payments pay WHERE pay.property_id = p.id AND pay.status != "cancelled" AND pay.payment_date BETWEEN ? AND ?), 0) AS short_revenue,
        COALESCE((SELECT SUM(lp.amount_paid) FROM long_term_payments lp JOIN long_term_contracts c ON c.id = lp.contract_id WHERE c.property_id = p.id AND CONCAT(lp.paid_month, "-01") BETWEEN ? AND ?), 0) AS long_revenue,
        COALESCE((SELECT SUM(e.amount) FROM expenses e WHERE e.property_id = p.id AND e.expense_date BETWEEN ? AND ?), 0) AS expenses
        FROM properties p ORDER BY (short_revenue + long_revenue - expenses) DESC');
    $profitStatement->execute([$from, $to, $from, $to, $from, $to]);
    $profitability = $profitStatement->fetchAll();

    $sourceStatement = Database::connection()->prepare('SELECT source AS label, COUNT(*) AS total, COALESCE(SUM(final_amount), 0) AS revenue FROM reservations WHERE start_date <= ? AND end_date >= ? GROUP BY source ORDER BY revenue DESC');
    $sourceStatement->execute([$to, $from]);
    $sources = $sourceStatement->fetchAll();

    $agentStatement = Database::connection()->prepare('SELECT COALESCE(u.name, "Sans agent") AS label, COUNT(r.id) AS total, COALESCE(SUM(r.final_amount), 0) AS revenue FROM reservations r LEFT JOIN users u ON u.id = r.agent_id WHERE r.start_date <= ? AND r.end_date >= ? GROUP BY r.agent_id, u.name ORDER BY revenue DESC');
    $agentStatement->execute([$to, $from]);
    $agents = $agentStatement->fetchAll();

    $cityStatement = Database::connection()->prepare('SELECT city AS label,
        COALESCE((SELECT SUM(pay.amount) FROM payments pay JOIN properties pp ON pp.id = pay.property_id WHERE pp.city = p.city AND pay.status != "cancelled" AND pay.payment_date BETWEEN ? AND ?), 0) AS short_revenue,
        COALESCE((SELECT SUM(lp.amount_paid) FROM long_term_payments lp JOIN long_term_contracts c ON c.id = lp.contract_id JOIN properties pp2 ON pp2.id = c.property_id WHERE pp2.city = p.city AND CONCAT(lp.paid_month, "-01") BETWEEN ? AND ?), 0) AS long_revenue
        FROM properties p
        GROUP BY p.city
        ORDER BY (short_revenue + long_revenue) DESC');
    $cityStatement->execute([$from, $to, $from, $to]);
    $cities = $cityStatement->fetchAll();

    $tenantStatement = Database::connection()->prepare('SELECT t.full_name AS label, COALESCE(SUM(lp.amount_paid), 0) AS revenue, COALESCE(SUM(lp.amount_remaining), 0) AS remaining
        FROM long_term_tenants t
        LEFT JOIN long_term_contracts c ON c.tenant_id = t.id
        LEFT JOIN long_term_payments lp ON lp.contract_id = c.id AND CONCAT(lp.paid_month, "-01") BETWEEN ? AND ?
        GROUP BY t.id, t.full_name
        ORDER BY revenue DESC, remaining DESC');
    $tenantStatement->execute([$from, $to]);
    $tenants = $tenantStatement->fetchAll();

    $occupancyRows = Database::connection()
        ->query('SELECT id, title FROM properties ORDER BY title')
        ->fetchAll();
    $occupancyByProperty = [];
    $occupiedTotal = 0;

    $nightsStatement = Database::connection()->prepare('SELECT start_date, end_date FROM reservations WHERE property_id = ? AND status IN ("pending", "confirmed", "completed") AND start_date <= ? AND end_date >= ?');

    foreach ($occupancyRows as $property) {
        $nightsStatement->execute([(int) $property['id'], $to, $from]);
        $occupiedNights = 0;

        foreach ($nightsStatement->fetchAll() as $row) {
            $occupiedNights += reservation_nights(max($row['start_date'], $from), min($row['end_date'], $to));
        }

        $occupiedTotal += $occupiedNights;
        $occupancyByProperty[] = [
            'title' => $property['title'],
            'occupied_nights' => $occupiedNights,
            'occupancy_rate' => round(($occupiedNights / max(1, $days)) * 100, 2),
        ];
    }

    usort($occupancyByProperty, static fn (array $a, array $b): int => $b['occupancy_rate'] <=> $a['occupancy_rate']);
    $globalOccupancyRate = round(($occupiedTotal / max(1, count($occupancyRows) * $days)) * 100, 2);

    require dirname(__DIR__) . '/resources/views/reports/advanced.php';
    exit;
}
if ($route === '/users' && $readMethod) {
    Auth::requirePermission('users.view');

    $pageTitle = 'Utilisateurs - Locative';
    $user = Auth::user();
    $notice = flash('notice');
    $users = Database::connection()
        ->query('SELECT id, name, email, role, status, created_at FROM users ORDER BY id DESC')
        ->fetchAll();
    [$users, $pagination] = paginate_items($users, 15);

    require dirname(__DIR__) . '/resources/views/users/index.php';
    exit;
}

if ($route === '/users/create' && $readMethod) {
    Auth::requirePermission('users.create');

    $pageTitle = 'Ajouter utilisateur - Locative';
    $user = Auth::user();
    $permissions = permissions_list();
    $formUser = [
        'name' => '',
        'email' => '',
        'role' => 'manager',
        'status' => 'active',
        'permissions' => [],
    ];
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/users/form.php';
    exit;
}

if ($route === '/users/store' && $method === 'POST') {
    Auth::requirePermission('users.create');
    verify_csrf();

    $name = trim((string) ($_POST['name'] ?? ''));
    $email = trim((string) ($_POST['email'] ?? ''));
    $password = (string) ($_POST['password'] ?? '');
    $role = in_array($_POST['role'] ?? '', ['admin', 'manager'], true) ? $_POST['role'] : 'manager';
    $status = in_array($_POST['status'] ?? '', ['active', 'inactive'], true) ? $_POST['status'] : 'active';
    $permissions = selected_permissions((array) ($_POST['permissions'] ?? []));
    $errors = [];

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

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

    if (strlen($password) < 6) {
        $errors[] = 'Le mot de passe doit contenir au moins 6 caracteres.';
    }

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/users/create');
    }

    try {
        $statement = Database::connection()->prepare(
            'INSERT INTO users (name, email, password, role, permissions, status) VALUES (?, ?, ?, ?, ?, ?)'
        );
        $statement->execute([
            $name,
            $email,
            password_hash($password, PASSWORD_DEFAULT),
            $role,
            json_encode($permissions, JSON_THROW_ON_ERROR),
            $status,
        ]);
        flash('notice', 'Utilisateur ajoute avec succes.');
        log_activity('create', 'users', 'Utilisateur ajoute: ' . $email);
    } catch (Throwable $e) {
        $_SESSION['form_errors'] = ['Cet email existe deja.'];
        redirect('/users/create');
    }

    redirect('/users');
}

if ($route === '/users/edit' && $readMethod) {
    Auth::requirePermission('users.edit');

    $id = (int) ($_GET['id'] ?? 0);
    $statement = Database::connection()->prepare('SELECT id, name, email, role, permissions, status FROM users WHERE id = ? LIMIT 1');
    $statement->execute([$id]);
    $formUser = $statement->fetch();

    if (!$formUser) {
        http_response_code(404);
        $pageTitle = 'Utilisateur introuvable - Locative';
        require dirname(__DIR__) . '/resources/views/errors/404.php';
        exit;
    }

    $formUser['permissions'] = json_decode((string) ($formUser['permissions'] ?? '[]'), true) ?: [];
    $pageTitle = 'Modifier utilisateur - Locative';
    $user = Auth::user();
    $permissions = permissions_list();
    $errors = $_SESSION['form_errors'] ?? [];
    unset($_SESSION['form_errors']);

    require dirname(__DIR__) . '/resources/views/users/form.php';
    exit;
}

if ($route === '/users/update' && $method === 'POST') {
    Auth::requirePermission('users.edit');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);
    $name = trim((string) ($_POST['name'] ?? ''));
    $email = trim((string) ($_POST['email'] ?? ''));
    $password = (string) ($_POST['password'] ?? '');
    $role = in_array($_POST['role'] ?? '', ['admin', 'manager'], true) ? $_POST['role'] : 'manager';
    $status = in_array($_POST['status'] ?? '', ['active', 'inactive'], true) ? $_POST['status'] : 'active';
    $permissions = selected_permissions((array) ($_POST['permissions'] ?? []));
    $errors = [];

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

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

    if ($password !== '' && strlen($password) < 6) {
        $errors[] = 'Le mot de passe doit contenir au moins 6 caracteres.';
    }

    if ($id === (int) ($_SESSION['user_id'] ?? 0) && $status === 'inactive') {
        $errors[] = 'Vous ne pouvez pas desactiver votre propre compte.';
    }

    $currentStatement = Database::connection()->prepare('SELECT role, status FROM users WHERE id = ? LIMIT 1');
    $currentStatement->execute([$id]);
    $currentUser = $currentStatement->fetch();

    if ($currentUser && $currentUser['role'] === 'admin' && ($role !== 'admin' || $status !== 'active')) {
        $adminCount = (int) Database::connection()
            ->query('SELECT COUNT(*) FROM users WHERE role = "admin" AND status = "active"')
            ->fetchColumn();

        if ($adminCount <= 1) {
            $errors[] = 'Vous devez garder au moins un administrateur actif.';
        }
    }

    if ($errors) {
        $_SESSION['form_errors'] = $errors;
        redirect('/users/edit?id=' . $id);
    }

    try {
        if ($password !== '') {
            $statement = Database::connection()->prepare(
                'UPDATE users SET name = ?, email = ?, password = ?, role = ?, permissions = ?, status = ? WHERE id = ?'
            );
            $statement->execute([$name, $email, password_hash($password, PASSWORD_DEFAULT), $role, json_encode($permissions, JSON_THROW_ON_ERROR), $status, $id]);
        } else {
            $statement = Database::connection()->prepare(
                'UPDATE users SET name = ?, email = ?, role = ?, permissions = ?, status = ? WHERE id = ?'
            );
            $statement->execute([$name, $email, $role, json_encode($permissions, JSON_THROW_ON_ERROR), $status, $id]);
        }
        flash('notice', 'Utilisateur modifie avec succes.');
        log_activity('update', 'users', 'Utilisateur modifie: ' . $email);
    } catch (Throwable $e) {
        $_SESSION['form_errors'] = ['Cet email existe deja.'];
        redirect('/users/edit?id=' . $id);
    }

    redirect('/users');
}

if ($route === '/users/delete' && $method === 'POST') {
    Auth::requirePermission('users.delete');
    verify_csrf();

    $id = (int) ($_POST['id'] ?? 0);

    if ($id === (int) ($_SESSION['user_id'] ?? 0)) {
        flash('notice', 'Vous ne pouvez pas supprimer votre propre compte.');
        redirect('/users');
    }

    $currentStatement = Database::connection()->prepare('SELECT role, status FROM users WHERE id = ? LIMIT 1');
    $currentStatement->execute([$id]);
    $currentUser = $currentStatement->fetch();

    if ($currentUser && $currentUser['role'] === 'admin' && $currentUser['status'] === 'active') {
        $adminCount = (int) Database::connection()
            ->query('SELECT COUNT(*) FROM users WHERE role = "admin" AND status = "active"')
            ->fetchColumn();

        if ($adminCount <= 1) {
            flash('notice', 'Vous devez garder au moins un administrateur actif.');
            redirect('/users');
        }
    }

    $statement = Database::connection()->prepare('DELETE FROM users WHERE id = ?');
    $statement->execute([$id]);
    flash('notice', 'Utilisateur supprime avec succes.');
    log_activity('delete', 'users', 'Utilisateur supprime #' . $id);
    redirect('/users');
}

http_response_code(404);
$pageTitle = 'Page introuvable - Locative';
require dirname(__DIR__) . '/resources/views/errors/404.php';
