-- 1. Room Categories & Base Inventory
CREATE TABLE hotel_rooms (
    room_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_code VARCHAR(20) NOT NULL UNIQUE,       -- e.g. 'DLX-MNT-101'
    room_type VARCHAR(100) NOT NULL,             -- 'Mountain View Deluxe Suite'
    total_units INT UNSIGNED NOT NULL DEFAULT 1,
    max_adults TINYINT UNSIGNED NOT NULL DEFAULT 2,
    max_children TINYINT UNSIGNED NOT NULL DEFAULT 1,
    base_price_npr DECIMAL(10,2) NOT NULL,       -- Domestic standard rate
    base_price_usd DECIMAL(8,2) NOT NULL,        -- International standard rate
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 2. Dynamic Seasonal & Festival Rate Adjustments
CREATE TABLE seasonal_rate_rules (
    rule_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_id INT UNSIGNED NOT NULL,
    season_name VARCHAR(100) NOT NULL,           -- e.g. 'Autumn Trekking Peak'
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    price_multiplier DECIMAL(4,2) NOT NULL DEFAULT 1.25, -- +25% during peak
    min_stay_nights TINYINT UNSIGNED NOT NULL DEFAULT 1,
    FOREIGN KEY (room_id) REFERENCES hotel_rooms(room_id) ON DELETE CASCADE,
    INDEX idx_date_range (room_id, start_date, end_date)
) ENGINE=InnoDB;

-- 3. Reservations (Direct + OTA Imports)
CREATE TABLE reservations (
    booking_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    booking_reference VARCHAR(32) NOT NULL UNIQUE, -- e.g. 'RES-2026-9841'
    room_id INT UNSIGNED NOT NULL,
    guest_name VARCHAR(150) NOT NULL,
    guest_email VARCHAR(150) NOT NULL,
    guest_phone VARCHAR(50) NOT NULL,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    adults TINYINT UNSIGNED NOT NULL DEFAULT 1,
    total_amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'NPR',  -- 'NPR' or 'USD'
    booking_source ENUM('DIRECT', 'BOOKING_COM', 'AGODA', 'AIRBNB') NOT NULL DEFAULT 'DIRECT',
    payment_status ENUM('PENDING', 'PAID', 'REFUNDED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    payment_gateway VARCHAR(50) NULL,            -- 'FONEPAY', 'ESEWA', 'STRIPE', 'HBL'
    gateway_transaction_id VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (room_id) REFERENCES hotel_rooms(room_id),
    INDEX idx_dates (room_id, check_in_date, check_out_date, payment_status)
) ENGINE=InnoDB;

-- 4. External Channel iCal Feeds
CREATE TABLE ical_channel_feeds (
    feed_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_id INT UNSIGNED NOT NULL,
    channel_name VARCHAR(50) NOT NULL,           -- 'Booking.com', 'Airbnb'
    import_ical_url TEXT NOT NULL,
    export_token VARCHAR(64) NOT NULL UNIQUE,
    last_sync_time TIMESTAMP NULL,
    sync_status ENUM('SUCCESS', 'FAILED') DEFAULT 'SUCCESS',
    FOREIGN KEY (room_id) REFERENCES hotel_rooms(room_id) ON DELETE CASCADE
) ENGINE=InnoDB;

<?php
declare(strict_types=1);

namespace SafalHospitality\Engine;

use PDO;
use Exception;

class ReservationEngine {
    public function __construct(private PDO $pdo) {}

    /**
     * Atomically validates room availability and creates a locked pending reservation.
     * Prevents race conditions using SELECT ... FOR UPDATE.
     */
    public function bookRoom(
        int $roomId,
        string $checkIn,
        string $checkOut,
        array $guestData
    ): string {
        $this->pdo->beginTransaction();

        try {
            // 1. Lock room record to serialize concurrent booking attempts
            $stmt = $this->pdo->prepare(
                "SELECT total_units FROM hotel_rooms WHERE room_id = :id AND is_active = 1 FOR UPDATE"
            );
            $stmt->execute(['id' => $roomId]);
            $room = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$room) {
                throw new Exception("Selected room category does not exist or is inactive.");
            }

            // 2. Count overlapping active reservations
            $conflictStmt = $this->pdo->prepare(
                "SELECT COUNT(*) FROM reservations 
                 WHERE room_id = :id 
                   AND payment_status IN ('PAID', 'PENDING')
                   AND NOT (check_out_date <= :in OR check_in_date >= :out)"
            );
            $conflictStmt->execute([
                'id'  => $roomId,
                'in'  => $checkIn,
                'out' => $checkOut
            ]);
            $bookedUnits = (int) $conflictStmt->fetchColumn();

            if ($bookedUnits >= (int) $room['total_units']) {
                throw new Exception("No rooms available in this category for the selected dates.");
            }

            // 3. Generate cryptographically secure booking reference
            $bookingRef = 'RES-' . date('Y') . '-' . bin2hex(random_bytes(4));

            // 4. Calculate seasonal rate
            $totalAmount = $this->calculateTotalRate($roomId, $checkIn, $checkOut);

            // 5. Insert pending reservation
            $ins = $this->pdo->prepare(
                "INSERT INTO reservations (
                    booking_reference, room_id, guest_name, guest_email, 
                    guest_phone, check_in_date, check_out_date, total_amount, 
                    currency, booking_source, payment_status
                 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'NPR', 'DIRECT', 'PENDING')"
            );
            $ins->execute([
                $bookingRef,
                $roomId,
                $guestData['name'],
                $guestData['email'],
                $guestData['phone'],
                $checkIn,
                $checkOut,
                $totalAmount
            ]);

            $this->pdo->commit();
            return $bookingRef;

        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

<?php
declare(strict_types=1);

namespace SafalHospitality\Sync;

use PDO;

class ICalFeedGenerator {
    public function generateExportFeed(PDO $pdo, int $roomId, string $token): string {
        // Authenticate secret token
        $stmt = $pdo->prepare("SELECT feed_id FROM ical_channel_feeds WHERE room_id = ? AND export_token = ?");
        $stmt->execute([$roomId, $token]);
        if (!$stmt->fetch()) {
            http_response_code(403);
            exit("Access Denied");
        }

        // Fetch all confirmed active reservations
        $resStmt = $pdo->prepare(
            "SELECT booking_reference, check_in_date, check_out_date 
             FROM reservations 
             WHERE room_id = ? AND payment_status = 'PAID' AND check_out_date >= CURDATE()"
        );
        $resStmt->execute([$roomId]);
        $bookings = $resStmt->fetchAll(PDO::FETCH_ASSOC);

        $ics = "BEGIN:VCALENDAR\r\n";
        $ics .= "VERSION:2.0\r\n";
        $ics .= "PRODID:-//Safal Bhurtel Direct Engine//Hospitality Sync//NP\r\n";
        $ics .= "CALSCALE:GREGORIAN\r\n";
        $ics .= "METHOD:PUBLISH\r\n";

        foreach ($bookings as $b) {
            $dtStart = str_replace('-', '', $b['check_in_date']);
            $dtEnd = str_replace('-', '', $b['check_out_date']);
            $uid = $b['booking_reference'] . '@safalresort.com';

            $ics .= "BEGIN:VEVENT\r\n";
            $ics .= "UID:{$uid}\r\n";
            $ics .= "DTSTAMP:" . gmdate('Ymd\THis\Z') . "\r\n";
            $ics .= "DTSTART;VALUE=DATE:{$dtStart}\r\n";
            $ics .= "DTEND;VALUE=DATE:{$dtEnd}\r\n";
            $ics .= "SUMMARY:Reserved (Direct Booking Engine)\r\n";
            $ics .= "STATUS:CONFIRMED\r\n";
            $ics .= "END:VEVENT\r\n";
        }

        $ics .= "END:VCALENDAR\r\n";
        return $ics;
    }
}