<?php

declare(strict_types=1);

namespace App\Models;

use App\Database;
use PDO;

final class Booking
{
    private PDO $db;

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

<<<<<<< HEAD
=======
    private function normalizePlate(string $value): string
    {
        return preg_replace('/[^A-Z0-9]/', '', strtoupper($value)) ?? '';
    }

>>>>>>> origin/main
    public function search(array $filters, int $page, int $limit): array
    {
        $where = ['1=1'];
        $params = [];

        if (!empty($filters['q'])) {
            $where[] = "(
<<<<<<< HEAD
                c.first_name LIKE :q OR c.last_name LIKE :q OR CONCAT(c.first_name, ' ', c.last_name) LIKE :q OR
                c.email LIKE :q OR c.phone LIKE :q OR b.booking_number LIKE :q OR s.name LIKE :q OR cr.title LIKE :q OR
                REPLACE(REPLACE(REPLACE(UPPER(b.car_license_plate), '-', ''), ' ', ''), '.', '') LIKE :q_plate
            )";
            $params['q'] = '%' . $filters['q'] . '%';
            $params['q_plate'] = '%' . normalizePlate((string) $filters['q']) . '%';
        }
        if (!empty($filters['date'])) {
            $where[] = ':booking_date BETWEEN DATE(b.start_date) AND DATE(b.end_date)';
            $params['booking_date'] = $filters['date'];
        }
=======
                c.first_name LIKE :q OR
                c.last_name LIKE :q OR
                CONCAT_WS(' ', c.first_name, c.last_name) LIKE :q OR
                c.email LIKE :q OR
                c.phone LIKE :q OR
                b.phone_override LIKE :q OR
                b.booking_no LIKE :q OR
                b.serial LIKE :q OR
                s.name LIKE :q OR
                cr.title LIKE :q OR
                cr.cruise_code LIKE :q OR
                REPLACE(REPLACE(REPLACE(UPPER(b.car_license_plate), '-', ''), ' ', ''), '.', '') LIKE :q_plate
            )";

            $params['q'] = '%' . $filters['q'] . '%';
            $params['q_plate'] = '%' . $this->normalizePlate((string) $filters['q']) . '%';
        }

        if (!empty($filters['date'])) {
            $where[] = ':booking_date BETWEEN b.parking_from AND b.parking_to';
            $params['booking_date'] = $filters['date'];
        }

>>>>>>> origin/main
        if (!empty($filters['status'])) {
            $where[] = 'b.status = :status';
            $params['status'] = $filters['status'];
        }
<<<<<<< HEAD
        if (!empty($filters['parking_type'])) {
            $where[] = 'p.type = :parking_type';
            $params['parking_type'] = $filters['parking_type'];
        }
=======

        if (!empty($filters['parking_type'])) {
            $where[] = 'b.parking_type = :parking_type';
            $params['parking_type'] = $filters['parking_type'];
        }

>>>>>>> origin/main
        if (!empty($filters['ship_id'])) {
            $where[] = 's.id = :ship_id';
            $params['ship_id'] = (int) $filters['ship_id'];
        }

        $offset = ($page - 1) * $limit;
<<<<<<< HEAD
        $sql = "SELECT b.*, c.first_name, c.last_name, c.email, c.phone, cr.title AS cruise_title, cr.code AS cruise_code,
                       s.name AS ship_name, p.name AS parking_product, p.type AS parking_type,
                       i.total_amount AS invoice_total, i.status AS invoice_status
=======

        $sql = "SELECT
                    b.*,
                    c.first_name,
                    c.last_name,
                    c.company,
                    c.email,
                    c.phone,
                    cr.title AS cruise_title,
                    cr.cruise_code,
                    cr.starts_at AS cruise_start_date,
                    cr.ends_at AS cruise_end_date,
                    s.name AS ship_name,
                    p.title AS parking_product,
                    p.type AS product_type,
                    i.total_amount AS invoice_total,
                    i.status AS invoice_status,
                    i.payment_status
>>>>>>> origin/main
                FROM bookings b
                LEFT JOIN customers c ON c.id = b.customer_id
                LEFT JOIN cruises cr ON cr.id = b.cruise_id
                LEFT JOIN ships s ON s.id = cr.ship_id
                LEFT JOIN products p ON p.id = b.parking_product_id
                LEFT JOIN invoices i ON i.booking_id = b.id
                WHERE " . implode(' AND ', $where) . "
<<<<<<< HEAD
                ORDER BY b.start_date DESC, b.id DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue(':' . $k, $v);
        }
=======
                ORDER BY b.parking_from DESC, b.id DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);

        foreach ($params as $k => $v) {
            $stmt->bindValue(':' . $k, $v);
        }

>>>>>>> origin/main
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

<<<<<<< HEAD
        $countSql = "SELECT COUNT(*) FROM bookings b
=======
        $countSql = "SELECT COUNT(*)
                    FROM bookings b
>>>>>>> origin/main
                    LEFT JOIN customers c ON c.id = b.customer_id
                    LEFT JOIN cruises cr ON cr.id = b.cruise_id
                    LEFT JOIN ships s ON s.id = cr.ship_id
                    LEFT JOIN products p ON p.id = b.parking_product_id
<<<<<<< HEAD
                    WHERE " . implode(' AND ', $where);
        $countStmt = $this->db->prepare($countSql);
        foreach ($params as $k => $v) {
            $countStmt->bindValue(':' . $k, $v);
        }
=======
                    LEFT JOIN invoices i ON i.booking_id = b.id
                    WHERE " . implode(' AND ', $where);

        $countStmt = $this->db->prepare($countSql);

        foreach ($params as $k => $v) {
            $countStmt->bindValue(':' . $k, $v);
        }

>>>>>>> origin/main
        $countStmt->execute();

        return [
            'rows' => $stmt->fetchAll(),
            'total' => (int) $countStmt->fetchColumn(),
            'page' => $page,
            'limit' => $limit,
        ];
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM bookings WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
<<<<<<< HEAD
        $row = $stmt->fetch();
=======

        $row = $stmt->fetch();

>>>>>>> origin/main
        return $row ?: null;
    }

    public function update(int $id, array $data): bool
    {
<<<<<<< HEAD
        $allowed = ['car_license_plate', 'guests_count', 'checkin_at', 'checkout_at', 'status', 'note', 'specials', 'parking_product_id'];
=======
        $allowed = [
            'car_license_plate',
            'guests',
            'checked_in_at',
            'checked_out_at',
            'status',
            'note',
            'specials',
            'parking_product_id',
            'parking_type',
            'valet',
            'phone_override',
            'serial',
            'cruise_checkin',
            'parking_from',
            'parking_to',
        ];

>>>>>>> origin/main
        $set = [];
        $params = ['id' => $id];

        foreach ($allowed as $field) {
            if (array_key_exists($field, $data)) {
                $set[] = "$field = :$field";
                $params[$field] = $data[$field];
            }
        }

        if (!$set) {
            return false;
        }

        $sql = 'UPDATE bookings SET ' . implode(', ', $set) . ', updated_at = NOW() WHERE id = :id';
<<<<<<< HEAD
        $stmt = $this->db->prepare($sql);
=======

        $stmt = $this->db->prepare($sql);

>>>>>>> origin/main
        return $stmt->execute($params);
    }

    public function checkin(int $id): bool
    {
<<<<<<< HEAD
        $stmt = $this->db->prepare("UPDATE bookings SET status = 'checked_in', checkin_at = NOW(), updated_at = NOW() WHERE id = :id");
=======
        $stmt = $this->db->prepare("
            UPDATE bookings
            SET status = 'checked_in',
                checked_in_at = NOW(),
                updated_at = NOW()
            WHERE id = :id
        ");

>>>>>>> origin/main
        return $stmt->execute(['id' => $id]);
    }

    public function checkout(int $id): bool
    {
<<<<<<< HEAD
        $stmt = $this->db->prepare("UPDATE bookings SET status = 'checked_out', checkout_at = NOW(), updated_at = NOW() WHERE id = :id");
=======
        $stmt = $this->db->prepare("
            UPDATE bookings
            SET status = 'checked_out',
                checked_out_at = NOW(),
                updated_at = NOW()
            WHERE id = :id
        ");

>>>>>>> origin/main
        return $stmt->execute(['id' => $id]);
    }

    public function setStatus(int $id, string $status): bool
    {
<<<<<<< HEAD
        $stmt = $this->db->prepare('UPDATE bookings SET status = :status, updated_at = NOW() WHERE id = :id');
        return $stmt->execute(['id' => $id, 'status' => $status]);
=======
        $stmt = $this->db->prepare('
            UPDATE bookings
            SET status = :status,
                updated_at = NOW()
            WHERE id = :id
        ');

        return $stmt->execute([
            'id' => $id,
            'status' => $status,
        ]);
>>>>>>> origin/main
    }

    public function addNote(int $id, string $note): bool
    {
<<<<<<< HEAD
        $stmt = $this->db->prepare('UPDATE bookings SET note = :note, updated_at = NOW() WHERE id = :id');
        return $stmt->execute(['id' => $id, 'note' => $note]);
=======
        $stmt = $this->db->prepare('
            UPDATE bookings
            SET note = :note,
                updated_at = NOW()
            WHERE id = :id
        ');

        return $stmt->execute([
            'id' => $id,
            'note' => $note,
        ]);
>>>>>>> origin/main
    }

    public function getTodayStats(): array
    {
        $sql = "SELECT
            SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) AS bookings_today,
<<<<<<< HEAD
            SUM(CASE WHEN DATE(checkin_at) = CURDATE() THEN 1 ELSE 0 END) AS checkins_today,
            SUM(CASE WHEN DATE(checkout_at) = CURDATE() THEN 1 ELSE 0 END) AS checkouts_today,
            SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) AS open_bookings
            FROM bookings";
        $stats = $this->db->query($sql)->fetch() ?: [];

        $capacity = $this->db->query("SELECT p.type, COUNT(*) AS booked
            FROM bookings b
            LEFT JOIN products p ON p.id = b.parking_product_id
            WHERE CURDATE() BETWEEN DATE(b.start_date) AND DATE(b.end_date)
            GROUP BY p.type")->fetchAll();

        $nextCruises = $this->db->query("SELECT cr.*, s.name AS ship_name
            FROM cruises cr
            LEFT JOIN ships s ON s.id = cr.ship_id
            WHERE DATE(cr.start_date) >= CURDATE()
            ORDER BY cr.start_date ASC
            LIMIT 5")->fetchAll();

        $recentBookingsStmt = $this->db->query("SELECT DATE(b.created_at) AS booking_day, b.id, b.booking_number,
                CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, '')) AS customer_name,
                b.car_license_plate, b.status
            FROM bookings b
            LEFT JOIN customers c ON c.id = b.customer_id
            WHERE b.created_at IS NOT NULL
              AND DATE(b.created_at) >= DATE_SUB(CURDATE(), INTERVAL 13 DAY)
            ORDER BY booking_day DESC, b.id DESC");
        $recentRows = $recentBookingsStmt->fetchAll();
        $recentGrouped = [];
        foreach ($recentRows as $row) {
            $day = (string) $row['booking_day'];
            $recentGrouped[$day] ??= [];
            $recentGrouped[$day][] = $row;
        }

        return ['stats' => $stats, 'capacity' => $capacity, 'nextCruises' => $nextCruises, 'recentBookingsGrouped' => $recentGrouped];
=======
            SUM(CASE WHEN DATE(checked_in_at) = CURDATE() THEN 1 ELSE 0 END) AS checkins_today,
            SUM(CASE WHEN DATE(checked_out_at) = CURDATE() THEN 1 ELSE 0 END) AS checkouts_today,
            SUM(CASE WHEN status = 'reserved' THEN 1 ELSE 0 END) AS open_bookings,
            SUM(CASE WHEN CURDATE() BETWEEN parking_from AND parking_to THEN 1 ELSE 0 END) AS current_bookings,
            SUM(CASE WHEN CURDATE() BETWEEN parking_from AND parking_to AND status = 'reserved' THEN 1 ELSE 0 END) AS expected_today
            FROM bookings";

        $stats = $this->db->query($sql)->fetch() ?: [];

        $capacity = $this->db->query("
            SELECT
                b.parking_type,
                b.valet,
                COUNT(*) AS booked
            FROM bookings b
            WHERE CURDATE() BETWEEN b.parking_from AND b.parking_to
              AND b.status NOT IN ('cancelled', 'deleted')
            GROUP BY b.parking_type, b.valet
            ORDER BY b.parking_type, b.valet
        ")->fetchAll();

        $nextCruises = $this->db->query("
            SELECT
                cr.*,
                s.name AS ship_name
            FROM cruises cr
            LEFT JOIN ships s ON s.id = cr.ship_id
            WHERE DATE(cr.starts_at) >= CURDATE()
              AND cr.active = 1
            ORDER BY cr.starts_at ASC
            LIMIT 5
        ")->fetchAll();

        return [
            'stats' => $stats,
            'capacity' => $capacity,
            'nextCruises' => $nextCruises,
        ];
>>>>>>> origin/main
    }

    public function getWithRelations(int $id): ?array
    {
<<<<<<< HEAD
        $stmt = $this->db->prepare("SELECT b.*, c.*, cr.title AS cruise_title, cr.start_date AS cruise_start_date,
                cr.end_date AS cruise_end_date, s.name AS ship_name, p.name AS parking_name, p.type AS parking_type
=======
        $stmt = $this->db->prepare("
            SELECT
                b.*,

                c.id AS customer_id,
                c.gender,
                c.first_name,
                c.last_name,
                c.company,
                c.street,
                c.zip_code,
                c.city,
                c.country,
                c.phone,
                c.email,
                c.newsletter,
                c.source,
                c.note AS customer_note,

                cr.title AS cruise_title,
                cr.cruise_code,
                cr.starts_at AS cruise_start_date,
                cr.ends_at AS cruise_end_date,

                s.name AS ship_name,
                s.brand AS ship_brand,
                s.logo AS ship_logo,

                p.title AS parking_name,
                p.type AS product_type

>>>>>>> origin/main
            FROM bookings b
            LEFT JOIN customers c ON c.id = b.customer_id
            LEFT JOIN cruises cr ON cr.id = b.cruise_id
            LEFT JOIN ships s ON s.id = cr.ship_id
            LEFT JOIN products p ON p.id = b.parking_product_id
            WHERE b.id = :id
<<<<<<< HEAD
            LIMIT 1");
=======
            LIMIT 1
        ");

>>>>>>> origin/main
        $stmt->execute(['id' => $id]);
        $booking = $stmt->fetch();

        if (!$booking) {
            return null;
        }

<<<<<<< HEAD
        $booking['items'] = $this->db->prepare('SELECT * FROM booking_items WHERE booking_id = :id');
        $booking['items']->execute(['id' => $id]);
        $booking['items'] = $booking['items']->fetchAll();

        $invStmt = $this->db->prepare('SELECT * FROM invoices WHERE booking_id = :id LIMIT 1');
        $invStmt->execute(['id' => $id]);
        $invoice = $invStmt->fetch() ?: null;
        $booking['invoice'] = $invoice;

        $booking['payments'] = [];
        if ($invoice) {
            $payStmt = $this->db->prepare('SELECT * FROM payments WHERE invoice_id = :id ORDER BY paid_at DESC');
=======
        $itemsStmt = $this->db->prepare('
            SELECT *
            FROM booking_items
            WHERE booking_id = :id
            ORDER BY id ASC
        ');
        $itemsStmt->execute(['id' => $id]);
        $booking['items'] = $itemsStmt->fetchAll();

        $invStmt = $this->db->prepare('
            SELECT *
            FROM invoices
            WHERE booking_id = :id
            LIMIT 1
        ');
        $invStmt->execute(['id' => $id]);
        $invoice = $invStmt->fetch() ?: null;

        $booking['invoice'] = $invoice;
        $booking['payments'] = [];

        if ($invoice) {
            $payStmt = $this->db->prepare('
                SELECT *
                FROM payments
                WHERE invoice_id = :id
                ORDER BY payment_date DESC, created_at DESC
            ');
>>>>>>> origin/main
            $payStmt->execute(['id' => $invoice['id']]);
            $booking['payments'] = $payStmt->fetchAll();
        }

<<<<<<< HEAD
        $msgStmt = $this->db->prepare('SELECT * FROM messages WHERE booking_id = :id ORDER BY created_at DESC');
=======
        $msgStmt = $this->db->prepare('
            SELECT *
            FROM messages
            WHERE booking_id = :id
            ORDER BY created_at DESC
        ');
>>>>>>> origin/main
        $msgStmt->execute(['id' => $id]);
        $booking['messages'] = $msgStmt->fetchAll();

        return $booking;
    }
<<<<<<< HEAD
}
=======
}
>>>>>>> origin/main