<?php
declare(strict_types=1);

if (session_status() !== PHP_SESSION_ACTIVE) {
    session_start();
}

require_once __DIR__ . '/../db/config.php';

function app_boot(): void
{
    static $booted = false;
    if ($booted) {
        return;
    }

    ensure_schema();
    seed_offers();
    $booted = true;
}

function ensure_schema(): void
{
    $pdo = db();

    $statements = [
        <<<'SQL'
CREATE TABLE IF NOT EXISTS rides (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    pickup_label VARCHAR(255) NOT NULL,
    pickup_lat DECIMAL(10, 7) NULL,
    pickup_lng DECIMAL(10, 7) NULL,
    destination_label VARCHAR(255) NOT NULL,
    destination_lat DECIMAL(10, 7) NULL,
    destination_lng DECIMAL(10, 7) NULL,
    scheduled_for DATETIME NULL,
    status ENUM('pending', 'confirmed', 'completed', 'cancelled') NOT NULL DEFAULT 'confirmed',
    eta_minutes INT NULL,
    source_channel ENUM('app', 'hotel', 'reception', 'web') NOT NULL DEFAULT 'web',
    context_zone VARCHAR(120) NULL,
    locale VARCHAR(20) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rides_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SQL,
        <<<'SQL'
CREATE TABLE IF NOT EXISTS offers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(190) NOT NULL UNIQUE,
    category ENUM('restaurant', 'experience', 'activity', 'product', 'service') NOT NULL,
    excerpt VARCHAR(255) NULL,
    description TEXT NULL,
    location_label VARCHAR(255) NULL,
    lat DECIMAL(10, 7) NULL,
    lng DECIMAL(10, 7) NULL,
    price_from DECIMAL(10, 2) NULL,
    duration_minutes INT NULL,
    image_url VARCHAR(500) NULL,
    status ENUM('draft', 'published', 'paused') NOT NULL DEFAULT 'published',
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    priority_score INT NOT NULL DEFAULT 0,
    available_now TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_offers_status_priority (status, priority_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SQL,
        <<<'SQL'
CREATE TABLE IF NOT EXISTS bookings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    ride_id INT UNSIGNED NULL,
    offer_id INT UNSIGNED NOT NULL,
    customer_name VARCHAR(255) NULL,
    customer_email VARCHAR(255) NULL,
    customer_phone VARCHAR(80) NULL,
    party_size INT NOT NULL DEFAULT 1,
    booking_for DATETIME NULL,
    status ENUM('started', 'confirmed', 'cancelled') NOT NULL DEFAULT 'confirmed',
    amount DECIMAL(10, 2) NULL,
    commission_amount DECIMAL(10, 2) NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_bookings_offer_created (offer_id, created_at),
    INDEX idx_bookings_ride_created (ride_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SQL,
        <<<'SQL'
CREATE TABLE IF NOT EXISTS events (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type ENUM('request_created', 'recommendation_viewed', 'recommendation_clicked', 'booking_started', 'booking_completed') NOT NULL,
    ride_id INT UNSIGNED NULL,
    offer_id INT UNSIGNED NULL,
    booking_id INT UNSIGNED NULL,
    session_id VARCHAR(120) NULL,
    meta JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_events_type_created (event_type, created_at),
    INDEX idx_events_ride_created (ride_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SQL,
    ];

    foreach ($statements as $sql) {
        $pdo->exec($sql);
    }
}

function seed_offers(): void
{
    $pdo = db();
    $existing = (int) $pdo->query('SELECT COUNT(*) FROM offers')->fetchColumn();
    if ($existing >= 10) {
        return;
    }

    $offers = [
        [
            'title' => 'Lilium Bistro Marina',
            'slug' => 'lilium-bistro-marina',
            'category' => 'restaurant',
            'excerpt' => 'Cena junto a la marina con cocina canaria contemporanea.',
            'description' => 'Mesa frente al paseo maritimo, carta corta y servicio agil para antes o despues del trayecto. Ideal para cerrar la tarde con cocina local refinada.',
            'location_label' => 'Marina Lanzarote',
            'price_from' => 32.00,
            'duration_minutes' => 90,
            'image_url' => 'https://images.pexels.com/photos/262978/pexels-photo-262978.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 1,
            'priority_score' => 97,
        ],
        [
            'title' => 'Tegala Terraza Reserva',
            'slug' => 'tegala-terraza-reserva',
            'category' => 'restaurant',
            'excerpt' => 'Restaurante elegante para una cena tranquila con vistas.',
            'description' => 'Propuesta de autor con menu flexible, buena para parejas y grupos pequenos. La reserva puede confirmarse sin prepago en esta demo.',
            'location_label' => 'Puerto del Carmen',
            'price_from' => 45.00,
            'duration_minutes' => 110,
            'image_url' => 'https://images.pexels.com/photos/67468/pexels-photo-67468.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 1,
            'priority_score' => 90,
        ],
        [
            'title' => 'Brisa Market Tapas',
            'slug' => 'brisa-market-tapas',
            'category' => 'restaurant',
            'excerpt' => 'Tapas locales, vino volcanico y servicio rapido.',
            'description' => 'Parada perfecta si buscas algo informal pero bien curado. Carta pensada para viajeros con poco tiempo y ganas de probar producto local.',
            'location_label' => 'Arrecife Centro',
            'price_from' => 18.00,
            'duration_minutes' => 60,
            'image_url' => 'https://images.pexels.com/photos/1640774/pexels-photo-1640774.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 0,
            'priority_score' => 78,
        ],
        [
            'title' => 'Costa Norte Breakfast Club',
            'slug' => 'costa-norte-breakfast-club',
            'category' => 'restaurant',
            'excerpt' => 'Desayunos premium cerca de hoteles y apartamentos.',
            'description' => 'Cafe de especialidad, bowls y opciones ligeras para arrancar el dia. Muy util para llegadas tempranas o salidas al aeropuerto.',
            'location_label' => 'Costa Teguise',
            'price_from' => 14.00,
            'duration_minutes' => 45,
            'image_url' => 'https://images.pexels.com/photos/376464/pexels-photo-376464.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 0,
            'priority_score' => 74,
        ],
        [
            'title' => 'Sunset Catamaran Escape',
            'slug' => 'sunset-catamaran-escape',
            'category' => 'experience',
            'excerpt' => 'Salida al atardecer con bebida incluida desde la marina.',
            'description' => 'Experiencia premium de dos horas pensada para turistas que ya tienen el traslado resuelto y quieren aprovechar el hueco perfecto del dia.',
            'location_label' => 'Puerto Calero Marina',
            'price_from' => 69.00,
            'duration_minutes' => 120,
            'image_url' => 'https://images.pexels.com/photos/1430676/pexels-photo-1430676.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 1,
            'priority_score' => 96,
        ],
        [
            'title' => 'Volcanic Winery Tasting',
            'slug' => 'volcanic-winery-tasting',
            'category' => 'experience',
            'excerpt' => 'Cata guiada en La Geria con opcion de traslado.',
            'description' => 'Recorrido breve y elegante por bodega local con degustacion seleccionada. Encaja muy bien para parejas o pequenos grupos.',
            'location_label' => 'La Geria',
            'price_from' => 36.00,
            'duration_minutes' => 75,
            'image_url' => 'https://images.pexels.com/photos/1407858/pexels-photo-1407858.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 1,
            'priority_score' => 88,
        ],
        [
            'title' => 'Cesar Manrique Studio Visit',
            'slug' => 'cesar-manrique-studio-visit',
            'category' => 'experience',
            'excerpt' => 'Visita cultural compacta para encajar entre trayectos.',
            'description' => 'Entrada y recorrido sugerido por una de las visitas iconicas de la isla. Pensado para viajeros que buscan algo curado y facil de reservar.',
            'location_label' => 'Tahiche',
            'price_from' => 22.00,
            'duration_minutes' => 70,
            'image_url' => 'https://images.pexels.com/photos/161154/stained-glass-spiral-circle-pattern-161154.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 0,
            'priority_score' => 80,
        ],
        [
            'title' => 'North Coast Scenic Route',
            'slug' => 'north-coast-scenic-route',
            'category' => 'experience',
            'excerpt' => 'Ruta panoramica de media tarde con paradas cortas.',
            'description' => 'Un circuito ligero con vistas potentes, paradas fotografiables y ritmo tranquilo. Muy recomendable si el destino final esta en el norte.',
            'location_label' => 'Haria y Mirador del Rio',
            'price_from' => 54.00,
            'duration_minutes' => 150,
            'image_url' => 'https://images.pexels.com/photos/417074/pexels-photo-417074.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 0,
            'priority_score' => 77,
        ],
        [
            'title' => 'Surf Starter Session',
            'slug' => 'surf-starter-session',
            'category' => 'activity',
            'excerpt' => 'Clase corta para principiantes cerca de la playa.',
            'description' => 'Sesion guiada con equipo incluido y bloque horario facil de reservar. Encaja especialmente bien si te mueves hacia la costa.',
            'location_label' => 'Famara',
            'price_from' => 40.00,
            'duration_minutes' => 90,
            'image_url' => 'https://images.pexels.com/photos/416676/pexels-photo-416676.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 0,
            'priority_score' => 83,
        ],
        [
            'title' => 'Timanfaya Express Walk',
            'slug' => 'timanfaya-express-walk',
            'category' => 'activity',
            'excerpt' => 'Ventana corta para ver paisaje volcanico sin dedicar medio dia.',
            'description' => 'Actividad orientada a visitantes con agenda compacta. Incluye una propuesta muy clara de horario y punto de encuentro.',
            'location_label' => 'Timanfaya',
            'price_from' => 28.00,
            'duration_minutes' => 80,
            'image_url' => 'https://images.pexels.com/photos/1553945/pexels-photo-1553945.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 0,
            'priority_score' => 81,
        ],
        [
            'title' => 'Beach E-bike Circuit',
            'slug' => 'beach-e-bike-circuit',
            'category' => 'activity',
            'excerpt' => 'Recorrido suave por costa y paseo con bicicleta electrica.',
            'description' => 'Una propuesta ligera, con baja friccion para turistas que quieren hacer algo util entre check-in, playa y cena.',
            'location_label' => 'Playa Blanca',
            'price_from' => 29.00,
            'duration_minutes' => 100,
            'image_url' => 'https://images.pexels.com/photos/100582/pexels-photo-100582.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 0,
            'priority_score' => 76,
        ],
        [
            'title' => 'Airport Fast Track Assist',
            'slug' => 'airport-fast-track-assist',
            'category' => 'service',
            'excerpt' => 'Soporte rapido para salidas y llegadas con equipaje.',
            'description' => 'Servicio pensado para familias o viajeros premium que quieren resolver rapido colas, maletas y coordinacion con su traslado.',
            'location_label' => 'Aeropuerto ACE',
            'price_from' => 25.00,
            'duration_minutes' => 30,
            'image_url' => 'https://images.pexels.com/photos/1008155/pexels-photo-1008155.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 1,
            'priority_score' => 89,
        ],
        [
            'title' => 'Island Essentials Welcome Pack',
            'slug' => 'island-essentials-welcome-pack',
            'category' => 'product',
            'excerpt' => 'Pack listo con agua, snacks y esenciales de llegada.',
            'description' => 'Producto sencillo para anadir valor inmediato a la llegada. Facil de entender y facil de confirmar dentro del mismo flujo.',
            'location_label' => 'Entrega en hotel o recepcion',
            'price_from' => 19.00,
            'duration_minutes' => 15,
            'image_url' => 'https://images.pexels.com/photos/2101187/pexels-photo-2101187.jpeg?auto=compress&cs=tinysrgb&w=1200',
            'is_featured' => 0,
            'priority_score' => 72,
        ],
    ];

    $sql = 'INSERT INTO offers (uuid, title, slug, category, excerpt, description, location_label, price_from, duration_minutes, image_url, status, is_featured, priority_score, available_now) VALUES (:uuid, :title, :slug, :category, :excerpt, :description, :location_label, :price_from, :duration_minutes, :image_url, :status, :is_featured, :priority_score, :available_now)';
    $stmt = $pdo->prepare($sql);

    foreach ($offers as $offer) {
        $existsStmt = $pdo->prepare('SELECT id FROM offers WHERE slug = :slug LIMIT 1');
        $existsStmt->execute(['slug' => $offer['slug']]);
        if ($existsStmt->fetch()) {
            continue;
        }

        $stmt->execute([
            'uuid' => uuid_v4(),
            'title' => $offer['title'],
            'slug' => $offer['slug'],
            'category' => $offer['category'],
            'excerpt' => $offer['excerpt'],
            'description' => $offer['description'],
            'location_label' => $offer['location_label'],
            'price_from' => $offer['price_from'],
            'duration_minutes' => $offer['duration_minutes'],
            'image_url' => $offer['image_url'],
            'status' => 'published',
            'is_featured' => $offer['is_featured'],
            'priority_score' => $offer['priority_score'],
            'available_now' => 1,
        ]);
    }
}

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

function uuid_v4(): string
{
    $bytes = random_bytes(16);
    $bytes[6] = chr((ord($bytes[6]) & 0x0f) | 0x40);
    $bytes[8] = chr((ord($bytes[8]) & 0x3f) | 0x80);
    return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($bytes), 4));
}

function project_name(): string
{
    $name = trim((string) ($_SERVER['PROJECT_NAME'] ?? 'TaxiLanz'));
    return $name !== '' ? $name : 'TaxiLanz';
}

function redirect_to(string $path): never
{
    header('Location: ' . $path, true, 303);
    exit;
}

function set_flash(string $tone, string $title, string $message): void
{
    $_SESSION['flash'] = [
        'tone' => $tone,
        'title' => $title,
        'message' => $message,
    ];
}

function pull_flash(): ?array
{
    if (!isset($_SESSION['flash'])) {
        return null;
    }
    $flash = $_SESSION['flash'];
    unset($_SESSION['flash']);
    return is_array($flash) ? $flash : null;
}

function remember_form(string $key, array $payload): void
{
    $_SESSION['forms'][$key] = $payload;
}

function old_form(string $key): array
{
    $data = $_SESSION['forms'][$key] ?? [];
    unset($_SESSION['forms'][$key]);
    return is_array($data) ? $data : [];
}

function locale_code(): string
{
    $lang = (string) ($_SERVER['HTTP_ACCEPT_LANGUAGE'] ?? 'es-ES');
    return substr($lang, 0, 5);
}

function infer_context_zone(string $pickup, string $destination): string
{
    $haystack = strtolower($pickup . ' ' . $destination);
    $map = [
        'aeropuerto' => 'airport',
        'airport' => 'airport',
        'marina' => 'marina',
        'puerto' => 'harbor',
        'harbour' => 'harbor',
        'hotel' => 'hotel',
        'resort' => 'hotel',
        'playa' => 'beach',
        'beach' => 'beach',
        'famara' => 'north-coast',
        'timanfaya' => 'volcanic-park',
        'geria' => 'wine-route',
    ];
    foreach ($map as $needle => $zone) {
        if (str_contains($haystack, $needle)) {
            return $zone;
        }
    }
    return 'general';
}

function create_ride(array $payload): array
{
    $pdo = db();
    $uuid = uuid_v4();
    $eta = random_int(4, 11);
    $scheduledFor = $payload['scheduled_for'] !== '' ? $payload['scheduled_for'] : null;
    $zone = infer_context_zone((string) $payload['pickup_label'], (string) $payload['destination_label']);

    $stmt = $pdo->prepare(
        'INSERT INTO rides (uuid, pickup_label, destination_label, scheduled_for, status, eta_minutes, source_channel, context_zone, locale) VALUES (:uuid, :pickup_label, :destination_label, :scheduled_for, :status, :eta_minutes, :source_channel, :context_zone, :locale)'
    );
    $stmt->execute([
        'uuid' => $uuid,
        'pickup_label' => $payload['pickup_label'],
        'destination_label' => $payload['destination_label'],
        'scheduled_for' => $scheduledFor,
        'status' => 'confirmed',
        'eta_minutes' => $eta,
        'source_channel' => 'web',
        'context_zone' => $zone,
        'locale' => locale_code(),
    ]);

    $ride = find_ride_by_uuid($uuid);
    if (!$ride) {
        throw new RuntimeException('Ride was created but could not be loaded.');
    }

    log_event('request_created', [
        'ride_id' => (int) $ride['id'],
        'meta' => [
            'pickup' => $ride['pickup_label'],
            'destination' => $ride['destination_label'],
            'scheduled_for' => $ride['scheduled_for'],
        ],
    ]);

    return $ride;
}

function create_booking(array $payload): array
{
    $pdo = db();
    $uuid = uuid_v4();
    $offer = find_offer_by_id((int) $payload['offer_id']);
    if (!$offer) {
        throw new RuntimeException('Offer not found for booking.');
    }

    $rideId = null;
    if (!empty($payload['ride_uuid'])) {
        $ride = find_ride_by_uuid((string) $payload['ride_uuid']);
        $rideId = $ride ? (int) $ride['id'] : null;
    }

    $partySize = max(1, min(12, (int) $payload['party_size']));
    $baseAmount = isset($offer['price_from']) ? (float) $offer['price_from'] : 0.0;
    $amount = in_array($offer['category'], ['restaurant', 'activity'], true) ? $baseAmount * $partySize : $baseAmount;
    $commission = $amount > 0 ? round($amount * 0.12, 2) : null;
    $bookingFor = $payload['booking_for'] !== '' ? $payload['booking_for'] : null;

    log_event('booking_started', [
        'ride_id' => $rideId,
        'offer_id' => (int) $offer['id'],
        'meta' => [
            'offer_slug' => $offer['slug'],
            'party_size' => $partySize,
        ],
    ]);

    $stmt = $pdo->prepare(
        'INSERT INTO bookings (uuid, ride_id, offer_id, customer_name, customer_email, customer_phone, party_size, booking_for, status, amount, commission_amount, notes) VALUES (:uuid, :ride_id, :offer_id, :customer_name, :customer_email, :customer_phone, :party_size, :booking_for, :status, :amount, :commission_amount, :notes)'
    );
    $stmt->execute([
        'uuid' => $uuid,
        'ride_id' => $rideId,
        'offer_id' => (int) $offer['id'],
        'customer_name' => $payload['customer_name'] ?: null,
        'customer_email' => $payload['customer_email'] ?: null,
        'customer_phone' => $payload['customer_phone'] ?: null,
        'party_size' => $partySize,
        'booking_for' => $bookingFor,
        'status' => 'confirmed',
        'amount' => $amount > 0 ? $amount : null,
        'commission_amount' => $commission,
        'notes' => $payload['notes'] ?: null,
    ]);

    $booking = find_booking_by_uuid($uuid);
    if (!$booking) {
        throw new RuntimeException('Booking was created but could not be loaded.');
    }

    log_event('booking_completed', [
        'ride_id' => $rideId,
        'offer_id' => (int) $offer['id'],
        'booking_id' => (int) $booking['id'],
        'meta' => [
            'booking_uuid' => $booking['uuid'],
            'amount' => $booking['amount'],
            'commission' => $booking['commission_amount'],
        ],
    ]);

    return $booking;
}

function log_event(string $eventType, array $payload = []): void
{
    $pdo = db();
    $stmt = $pdo->prepare(
        'INSERT INTO events (event_type, ride_id, offer_id, booking_id, session_id, meta) VALUES (:event_type, :ride_id, :offer_id, :booking_id, :session_id, :meta)'
    );

    $meta = $payload['meta'] ?? null;
    $stmt->execute([
        'event_type' => $eventType,
        'ride_id' => $payload['ride_id'] ?? null,
        'offer_id' => $payload['offer_id'] ?? null,
        'booking_id' => $payload['booking_id'] ?? null,
        'session_id' => session_id() ?: null,
        'meta' => $meta ? json_encode($meta, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
    ]);
}

function featured_offers(int $limit = 6): array
{
    $stmt = db()->prepare('SELECT * FROM offers WHERE status = :status ORDER BY is_featured DESC, priority_score DESC, created_at DESC LIMIT ' . max(1, $limit));
    $stmt->execute(['status' => 'published']);
    return $stmt->fetchAll() ?: [];
}

function find_offer_by_slug(string $slug): ?array
{
    $stmt = db()->prepare('SELECT * FROM offers WHERE slug = :slug LIMIT 1');
    $stmt->execute(['slug' => $slug]);
    $offer = $stmt->fetch();
    return $offer ?: null;
}

function find_offer_by_id(int $id): ?array
{
    $stmt = db()->prepare('SELECT * FROM offers WHERE id = :id LIMIT 1');
    $stmt->execute(['id' => $id]);
    $offer = $stmt->fetch();
    return $offer ?: null;
}

function find_ride_by_uuid(string $uuid): ?array
{
    $stmt = db()->prepare('SELECT * FROM rides WHERE uuid = :uuid LIMIT 1');
    $stmt->execute(['uuid' => $uuid]);
    $ride = $stmt->fetch();
    return $ride ?: null;
}

function find_booking_by_uuid(string $uuid): ?array
{
    $stmt = db()->prepare(
        'SELECT b.*, o.title AS offer_title, o.slug AS offer_slug, o.category AS offer_category, r.uuid AS ride_uuid FROM bookings b JOIN offers o ON o.id = b.offer_id LEFT JOIN rides r ON r.id = b.ride_id WHERE b.uuid = :uuid LIMIT 1'
    );
    $stmt->execute(['uuid' => $uuid]);
    $booking = $stmt->fetch();
    return $booking ?: null;
}

function recent_rides(int $limit = 8): array
{
    $stmt = db()->prepare('SELECT * FROM rides ORDER BY created_at DESC LIMIT ' . max(1, $limit));
    $stmt->execute();
    return $stmt->fetchAll() ?: [];
}

function recent_bookings(int $limit = 8): array
{
    $stmt = db()->prepare(
        'SELECT b.*, o.title AS offer_title, o.slug AS offer_slug FROM bookings b JOIN offers o ON o.id = b.offer_id ORDER BY b.created_at DESC LIMIT ' . max(1, $limit)
    );
    $stmt->execute();
    return $stmt->fetchAll() ?: [];
}

function event_summary(): array
{
    $rows = db()->query('SELECT event_type, COUNT(*) AS total FROM events GROUP BY event_type')->fetchAll() ?: [];
    $summary = [];
    foreach ($rows as $row) {
        $summary[$row['event_type']] = (int) $row['total'];
    }
    return $summary;
}

function recommendations_for_ride(array $ride, int $limit = 3): array
{
    $offers = featured_offers(12);
    $routeContext = strtolower(trim(($ride['pickup_label'] ?? '') . ' ' . ($ride['destination_label'] ?? '') . ' ' . ($ride['context_zone'] ?? '')));
    $scored = [];

    foreach ($offers as $offer) {
        $score = (int) ($offer['priority_score'] ?? 0);
        $location = strtolower((string) ($offer['location_label'] ?? ''));
        $category = (string) $offer['category'];

        if ((int) $offer['is_featured'] === 1) {
            $score += 16;
        }
        if ((int) $offer['available_now'] === 1) {
            $score += 8;
        }

        $rules = [
            'airport' => ['service' => 24, 'product' => 15, 'restaurant' => 6],
            'hotel' => ['restaurant' => 18, 'service' => 10, 'experience' => 8],
            'beach' => ['activity' => 22, 'experience' => 14],
            'marina' => ['experience' => 20, 'restaurant' => 12],
            'harbor' => ['experience' => 18, 'restaurant' => 10],
            'wine-route' => ['experience' => 18, 'restaurant' => 8],
            'volcanic-park' => ['activity' => 16, 'experience' => 12],
            'general' => ['experience' => 6, 'restaurant' => 5],
        ];

        $zone = (string) ($ride['context_zone'] ?? 'general');
        if (isset($rules[$zone][$category])) {
            $score += $rules[$zone][$category];
        }

        if ($location !== '' && str_contains($routeContext, strtolower($location))) {
            $score += 10;
        }
        if (str_contains($routeContext, 'playa') || str_contains($routeContext, 'beach')) {
            if ($category === 'activity') {
                $score += 6;
            }
        }

        $offer['recommendation_score'] = $score;
        $scored[] = $offer;
    }

    usort($scored, static function (array $a, array $b): int {
        return ($b['recommendation_score'] <=> $a['recommendation_score'])
            ?: (($b['priority_score'] ?? 0) <=> ($a['priority_score'] ?? 0));
    });

    return array_slice($scored, 0, $limit);
}

function log_recommendation_views(array $ride, array $offers): void
{
    $sessionKey = 'seen_recommendations_' . $ride['uuid'];
    $seen = $_SESSION[$sessionKey] ?? [];
    if (!is_array($seen)) {
        $seen = [];
    }

    foreach ($offers as $offer) {
        $offerId = (int) $offer['id'];
        if (in_array($offerId, $seen, true)) {
            continue;
        }
        log_event('recommendation_viewed', [
            'ride_id' => (int) $ride['id'],
            'offer_id' => $offerId,
            'meta' => [
                'offer_slug' => $offer['slug'],
                'score' => $offer['recommendation_score'] ?? null,
            ],
        ]);
        $seen[] = $offerId;
    }

    $_SESSION[$sessionKey] = $seen;
}

function related_offers(array $offer, int $limit = 3): array
{
    $stmt = db()->prepare('SELECT * FROM offers WHERE status = :status AND slug <> :slug ORDER BY (category = :category) DESC, is_featured DESC, priority_score DESC LIMIT ' . max(1, $limit));
    $stmt->execute([
        'status' => 'published',
        'slug' => $offer['slug'],
        'category' => $offer['category'],
    ]);
    return $stmt->fetchAll() ?: [];
}

function format_datetime(?string $value): string
{
    if (!$value) {
        return 'Ahora mismo';
    }
    try {
        return (new DateTime($value))->format('d/m/Y · H:i');
    } catch (Throwable $e) {
        return $value;
    }
}

function format_currency(mixed $value): string
{
    if ($value === null || $value === '') {
        return 'A consultar';
    }
    return number_format((float) $value, 2, ',', '.') . ' €';
}

function category_label(string $value): string
{
    return match ($value) {
        'restaurant' => 'Restaurante',
        'experience' => 'Experiencia',
        'activity' => 'Actividad',
        'product' => 'Producto',
        'service' => 'Servicio',
        default => ucfirst($value),
    };
}

function status_label(string $value): string
{
    return match ($value) {
        'pending' => 'Pendiente',
        'confirmed' => 'Confirmado',
        'completed' => 'Completado',
        'cancelled' => 'Cancelado',
        'started' => 'Iniciado',
        default => ucfirst($value),
    };
}

function excerpt_for_offer(array $offer): string
{
    $excerpt = trim((string) ($offer['excerpt'] ?? ''));
    if ($excerpt !== '') {
        return $excerpt;
    }
    $description = trim((string) ($offer['description'] ?? ''));
    if ($description === '') {
        return 'Seleccion cuidadosamente preparada para completar el trayecto.';
    }
    return substr($description, 0, 120) . (strlen($description) > 120 ? '…' : '');
}

function page_url(string $path): string
{
    return $path;
}

function render_page_start(string $pageTitle, string $pageDescription, string $activeNav = 'home'): void
{
    $projectName = project_name();
    $metaDescription = $pageDescription !== '' ? $pageDescription : ((string) ($_SERVER['PROJECT_DESCRIPTION'] ?? '')); 
    if ($metaDescription === '') {
        $metaDescription = 'TaxiLanz confirma taxis y convierte la espera en recomendaciones y reservas utiles.';
    }
    $projectImageUrl = (string) ($_SERVER['PROJECT_IMAGE_URL'] ?? '');
    $pageTitleFull = trim($pageTitle . ' | ' . $projectName);
    $cssVersion = is_file(__DIR__ . '/../assets/css/custom.css') ? (string) filemtime(__DIR__ . '/../assets/css/custom.css') : (string) time();
    $jsVersion = is_file(__DIR__ . '/../assets/js/main.js') ? (string) filemtime(__DIR__ . '/../assets/js/main.js') : (string) time();
    $flash = pull_flash();

    echo '<!doctype html>';
    echo '<html lang="es">';
    echo '<head>';
    echo '  <meta charset="utf-8">';
    echo '  <meta name="viewport" content="width=device-width, initial-scale=1">';
    echo '  <title>' . h($pageTitleFull) . '</title>';
    echo '  <meta name="description" content="' . h($metaDescription) . '">';
    echo '  <meta property="og:title" content="' . h($pageTitleFull) . '">';
    echo '  <meta property="og:description" content="' . h($metaDescription) . '">';
    echo '  <meta property="twitter:title" content="' . h($pageTitleFull) . '">';
    echo '  <meta property="twitter:description" content="' . h($metaDescription) . '">';
    if ($projectImageUrl !== '') {
        echo '  <meta property="og:image" content="' . h($projectImageUrl) . '">';
        echo '  <meta property="twitter:image" content="' . h($projectImageUrl) . '">';
    }
    echo '  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">';
    echo '  <link rel="stylesheet" href="/assets/css/custom.css?v=' . h($cssVersion) . '">';
    echo '</head>';
    echo '<body>';
    echo '<header class="app-header">';
    echo '  <div class="container-fluid app-shell px-3 px-lg-4">';
    echo '    <nav class="navbar navbar-expand-lg navbar-light py-3 px-0">';
    echo '      <a class="navbar-brand brand-mark" href="/">TaxiLanz</a>';
    echo '      <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#mainNav" aria-controls="mainNav" aria-expanded="false" aria-label="Mostrar navegacion">';
    echo '        <span class="navbar-toggler-icon"></span>';
    echo '      </button>';
    echo '      <div class="collapse navbar-collapse" id="mainNav">';
    echo '        <ul class="navbar-nav ms-auto align-items-lg-center gap-lg-2">';
    echo '          <li class="nav-item"><a class="nav-link ' . ($activeNav === 'home' ? 'active' : '') . '" href="/">Solicitar taxi</a></li>';
    echo '          <li class="nav-item"><a class="nav-link ' . ($activeNav === 'operations' ? 'active' : '') . '" href="/operations/">Operaciones</a></li>';
    echo '          <li class="nav-item"><a class="nav-link ' . ($activeNav === 'health' ? 'active' : '') . '" href="/healthz/">Health</a></li>';
    echo '        </ul>';
    echo '      </div>';
    echo '    </nav>';
    echo '  </div>';
    echo '</header>';
    echo '<main class="pb-5">';
    echo '  <div class="container-fluid app-shell px-3 px-lg-4">';

    if ($flash) {
        echo '<div class="toast-container position-fixed top-0 end-0 p-3">';
        echo '  <div class="toast app-toast border-0 show" role="status" aria-live="polite" aria-atomic="true">';
        echo '      <div class="toast-header">';
        echo '          <strong class="me-auto">' . h($flash['title']) . '</strong>';
        echo '          <button type="button" class="btn-close" data-bs-dismiss="toast" aria-label="Cerrar"></button>';
        echo '      </div>';
        echo '      <div class="toast-body">' . h($flash['message']) . '</div>';
        echo '  </div>';
        echo '</div>';
    }

    echo '    <div class="app-statusbar d-flex flex-wrap align-items-center justify-content-between gap-2 mb-4">';
    echo '      <div class="status-chip">Conserjeria de movilidad para Lanzarote</div>';
    echo '      <div class="small text-secondary">UX demo · bootstrap 5 · flujo real</div>';
    echo '    </div>';
}

function render_page_end(): void
{
    $jsVersion = is_file(__DIR__ . '/../assets/js/main.js') ? (string) filemtime(__DIR__ . '/../assets/js/main.js') : (string) time();

    echo '  </div>';
    echo '</main>';
    echo '<footer class="app-footer border-top">';
    echo '  <div class="container-fluid app-shell px-3 px-lg-4 py-3 d-flex flex-column flex-lg-row justify-content-between gap-2 text-secondary small">';
    echo '    <span>TaxiLanz MVP · solicitud, recomendacion y reserva en una sola experiencia.</span>';
    echo '    <span>Preparado para ampliar con tracking fino y dashboard operativo.</span>';
    echo '  </div>';
    echo '</footer>';
    echo '<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>';
    echo '<script src="/assets/js/main.js?v=' . h($jsVersion) . '"></script>';
    echo '</body>';
    echo '</html>';
}