<?php

declare(strict_types=1);

namespace App\Models;

use App\Database;
use PDO;

final class Cruise
{
    private PDO $db;

    public function __construct()
    {
        $this->db = Database::connection();
    }

    public function list(): array
    {
        $sql = "SELECT cr.*, s.name AS ship_name,
                    SUM(CASE WHEN p.type='indoor' THEN 1 ELSE 0 END) AS booked_indoor,
                    SUM(CASE WHEN p.type='outdoor' THEN 1 ELSE 0 END) AS booked_outdoor,
                    SUM(CASE WHEN p.type='valet' THEN 1 ELSE 0 END) AS booked_valet,
                    SUM(CASE WHEN p.type='camper' THEN 1 ELSE 0 END) AS booked_camper
                FROM cruises cr
                LEFT JOIN ships s ON s.id = cr.ship_id
                LEFT JOIN bookings b ON b.cruise_id = cr.id
                LEFT JOIN products p ON p.id = b.parking_product_id
                GROUP BY cr.id
                ORDER BY cr.start_date DESC";
        return $this->db->query($sql)->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM cruises WHERE id = :id');
        $stmt->execute(['id' => $id]);
        return $stmt->fetch() ?: null;
    }

    public function getCapacityStats(int $id): array
    {
        $stmt = $this->db->prepare("SELECT p.type, COUNT(*) AS booked
                FROM bookings b
                LEFT JOIN products p ON p.id = b.parking_product_id
                WHERE b.cruise_id = :id
                GROUP BY p.type");
        $stmt->execute(['id' => $id]);
        return $stmt->fetchAll();
    }
}