<?php
/**
 * new_api_booking.php
 *
 * Receives BookingRQ webhook pushes from Nightsbridge (BridgeConnect API)
 * and creates/updates/CANCELS the corresponding booking in this system.
 * On a cancellation, the order is set to status 5, every rooming row is
 * released (same behaviour as a manual cancellation in reservation_list.php),
 * and the reopened availability is pushed back to Nightsbridge immediately.
 * New bookings likewise trigger an immediate availability push for the
 * affected room type(s) - see nightsbridge_inventory_push.php.
 *
 * Nightsbridge POSTs a JSON payload to this URL every time a booking is made
 * on your Nightsbridge listing, and expects EXACTLY this response body back:
 *   {"success": true}
 * Anything else is treated as an error, and Nightsbridge will keep resending
 * the SAME booking until it gets that response - so this endpoint must be
 * idempotent (safe to receive the same booking twice) and must always
 * return a clean success response once the booking is durably saved, even
 * if some secondary step (e.g. folio creation) has a hiccup.
 *
 * ---------------------------------------------------------------------
 * STILL NEEDED BEFORE THIS GOES LIVE (marked TODO below):
 *   1. NIGHTSBRIDGE_LOGIN_KEY - set below from Nightsbridge's credentials
 *      email (confirmed: 1e72557c95f81609735fc87ff80a1f6d).
 *   2. Populate nightsbridge_room_type_map with the confirmed mapping:
 *        Roomtype ID 1 -> Pool View
 *        Roomtype ID 2 -> Garden View
 *        Roomtype ID 3 -> Garden View Family Room
 *      (see nightsbridge_room_type_map_batonka.sql)
 *      Nightsbridge only sends the room TYPE, never a specific physical
 *      room - each incoming booking is created with the correct room type
 *      but no room assigned yet, exactly like any other new reservation
 *      waiting to be allocated through your normal calendar/room-picker
 *      tools. This is intentional, not a gap to fill in later.
 *   3. Confirm the exact column names in your own orders/guests/
 *      order_rooming tables where noted below - I've used the same names
 *      established elsewhere in this system, but please double check.
 * ---------------------------------------------------------------------
 */

declare(strict_types=1);
error_reporting(E_ALL);
ini_set('display_errors', '0'); // this is a webhook endpoint - never leak errors into the HTTP response

$bootstrapCandidates = [
    __DIR__ . '/../includes/bootstrap.php',     // /rooms/api/ -> /rooms/includes/ (confirmed actual layout)
    __DIR__ . '/../../includes/bootstrap.php',  // in case this ever moves a level deeper
    __DIR__ . '/bootstrap.php',                  // in case it's copied alongside this file
];
$bootstrapFound = false;
foreach ($bootstrapCandidates as $bootstrapPath) {
    if (is_file($bootstrapPath)) {
        require_once $bootstrapPath;
        $bootstrapFound = true;
        break;
    }
}
if (!$bootstrapFound) {
    http_response_code(500);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(['success' => false, 'error' => ['code' => 599, 'message' => 'Could not find boot.php on this server.']]);
    exit;
}

const NIGHTSBRIDGE_LOGIN_KEY = '266119649f911114c0bf80c2c3456c8c';

// Availability push library (the OUTBOUND direction) - included as a library
// so we can push updated availability for the affected room type(s) the
// moment a booking is created or cancelled here, per Nightsbridge's
// real-time recommendation. Guarded: if the file isn't deployed alongside
// this one the webhook still works normally, and the nightly full flush
// cron covers availability.
if (is_file(__DIR__ . '/nightsbridge_inventory_push.php')) {
    if (!defined('NB_INVENTORY_PUSH_LIB')) {
        define('NB_INVENTORY_PUSH_LIB', true);
    }
    require_once __DIR__ . '/nightsbridge_inventory_push.php';
}

/* =========================================================================
   Always respond in Nightsbridge's exact expected format, and always exit
   after responding - never let any other output leak into the response body.
   ========================================================================= */
function respondSuccess(): void
{
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(['success' => true]);
    exit;
}

function respondError(string $message, int $code = 100): void
{
    // Still HTTP 200 - Nightsbridge distinguishes success/failure by the
    // "success" field in the body, not the HTTP status code, per the spec.
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(['success' => false, 'error' => ['code' => $code, 'message' => $message]]);
    exit;
}

/* =========================================================================
   Schema: booking log (audit trail for every push received) + room type map.
   Auto-created on first run.
   ========================================================================= */
function ensureNightsbridgeSchema(PDO $pdo): void
{
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS nightsbridge_booking_log (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            nightsbridge_booking_id BIGINT UNSIGNED NOT NULL,
            bbid BIGINT UNSIGNED NULL,
            raw_payload MEDIUMTEXT NULL,
            local_order_id INT UNSIGNED NULL,
            action ENUM('inserted','updated','rejected','error','cancelled') NOT NULL,
            note TEXT NULL,
            received_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_nb_booking_id(nightsbridge_booking_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    // Existing installs created the enum without 'cancelled' - extend it once.
    try {
        $stmt = $pdo->prepare("SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'nightsbridge_booking_log' AND COLUMN_NAME = 'action'");
        $stmt->execute();
        $colType = (string)$stmt->fetchColumn();
        if ($colType !== '' && strpos($colType, 'cancelled') === false) {
            $pdo->exec("ALTER TABLE nightsbridge_booking_log MODIFY action ENUM('inserted','updated','rejected','error','cancelled') NOT NULL");
        }
    } catch (Throwable $e) {}

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS nightsbridge_room_type_map (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            nightsbridge_roomtypeid INT UNSIGNED NOT NULL,
            room_type_id INT UNSIGNED NOT NULL,
            property_id INT UNSIGNED NULL,
            UNIQUE KEY uniq_nb_roomtype(nightsbridge_roomtypeid, property_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");

    // TODO: this column is how we recognise "have we already imported this
    // booking" on re-delivery - matches Nightsbridge's own bookingid, since
    // (unlike the Semper migration) we can't assume it lines up with our own
    // orders.id numbering.
    if (!ro_column_exists($pdo, 'orders', 'channel_booking_id')) {
        try { $pdo->exec("ALTER TABLE orders ADD COLUMN channel_booking_id BIGINT UNSIGNED NULL AFTER id"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE orders ADD COLUMN booking_source VARCHAR(30) NULL AFTER channel_booking_id"); } catch (Throwable $e) {}
    }

    // The unique index is the LAST line of defence against duplicate imports,
    // so it must be checked independently of the column check above - if the
    // columns were added on an earlier run but the index ALTER failed once,
    // the old gating meant it was never attempted again.
    // NOTE: this ALTER will fail (silently) for as long as duplicate
    // (booking_source, channel_booking_id) rows already exist in orders -
    // clean those up first (see the dedupe SQL provided with this fix).
    if (!ro_index_exists($pdo, 'orders', 'uniq_channel_booking')) {
        try { $pdo->exec("ALTER TABLE orders ADD UNIQUE INDEX uniq_channel_booking (booking_source, channel_booking_id)"); } catch (Throwable $e) {}
    }
}

function ro_column_exists(PDO $pdo, string $table, string $column): bool
{
    static $cache = [];
    $key = $table . '.' . $column;
    if (array_key_exists($key, $cache)) return $cache[$key];
    try {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c");
        $stmt->execute([':t' => $table, ':c' => $column]);
        $cache[$key] = ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable $e) {
        $cache[$key] = false;
    }
    return $cache[$key];
}

function ro_index_exists(PDO $pdo, string $table, string $indexName): bool
{
    try {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND INDEX_NAME = :i");
        $stmt->execute([':t' => $table, ':i' => $indexName]);
        return ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable $e) {
        return false;
    }
}

function logBooking(PDO $pdo, int $nbBookingId, ?int $bbid, string $rawPayload, ?int $localOrderId, string $action, string $note = ''): void
{
    $pdo->prepare("
        INSERT INTO nightsbridge_booking_log (nightsbridge_booking_id, bbid, raw_payload, local_order_id, action, note)
        VALUES (:nid, :bbid, :raw, :oid, :action, :note)
    ")->execute([
        ':nid' => $nbBookingId,
        ':bbid' => $bbid,
        ':raw' => $rawPayload,
        ':oid' => $localOrderId,
        ':action' => $action,
        ':note' => $note,
    ]);
}

function mapNightsbridgeRoomType(PDO $pdo, int $nbRoomTypeId): ?int
{
    $stmt = $pdo->prepare("SELECT room_type_id FROM nightsbridge_room_type_map WHERE nightsbridge_roomtypeid = :id LIMIT 1");
    $stmt->execute([':id' => $nbRoomTypeId]);
    $id = $stmt->fetchColumn();
    return $id !== false ? (int)$id : null;
}

/* =========================================================================
   Guest upsert - matches on email first (if present), else creates a new
   guest record. Reused for both the booking-holder ("madeby") and each
   room's own occupant ("guest").
   ========================================================================= */
function upsertGuest(PDO $pdo, array $person): int
{
    $firstName = trim((string)($person['firstname'] ?? ''));
    $lastName = trim((string)($person['surname'] ?? ''));
    $email = trim((string)($person['email'] ?? ''));
    $phone = trim((string)($person['phoneno'] ?? ''));

   /* if ($email !== '') {
        $stmt = $pdo->prepare("SELECT id FROM guests WHERE email = :email LIMIT 1");
        $stmt->execute([':email' => $email]);
        $existingId = $stmt->fetchColumn();
        if ($existingId !== false) {
            $pdo->prepare("UPDATE guests SET firstname = :fn, lastname = :ln, phone = :ph WHERE id = :id")
                ->execute([':fn' => $firstName, ':ln' => $lastName, ':ph' => $phone, ':id' => (int)$existingId]);
            return (int)$existingId;
        }
    } elseif ($firstName !== '' || $lastName !== '') {
        // Nightsbridge frequently sends NO email (or a channel-masked one) -
        // without this branch every re-delivery of the same booking created a
        // brand new guest row. Fall back to matching on name (+ phone when
        // present) so the same person resolves to the same guest record.
        $sql = "SELECT id FROM guests WHERE firstname = :fn AND lastname = :ln";
        $params = [':fn' => $firstName, ':ln' => $lastName];
        if ($phone !== '') {
            $sql .= " AND phone = :ph";
            $params[':ph'] = $phone;
        }
        $stmt = $pdo->prepare($sql . " ORDER BY id LIMIT 1");
        $stmt->execute($params);
        $existingId = $stmt->fetchColumn();
        if ($existingId !== false) {
            return (int)$existingId;
        }
    }*/

    $pdo->prepare("INSERT INTO guests (firstname, lastname, email, phone, created_at) VALUES (:fn, :ln, :email, :ph, NOW())")
        ->execute([':fn' => $firstName, ':ln' => $lastName, ':email' => $email !== '' ? $email : null, ':ph' => $phone]);
    return (int)$pdo->lastInsertId();
}

/* =========================================================================
   Cancellation handling + real-time availability push helpers.
   ========================================================================= */

/*
   Detect whether an incoming BookingRQ is a CANCELLATION. Nightsbridge's
   payload can flag this in a few places depending on the message variant,
   so detection is deliberately tolerant. The raw payload of every delivery
   is stored in nightsbridge_booking_log, so if their cancellation test
   arrives and is NOT picked up by this, check the logged raw_payload to see
   which field they actually use and tighten this to match.
   TODO: confirm the exact cancellation flag/value with Nightsbridge.
*/
function nb_booking_is_cancellation(array $payload, array $booking): bool
{
    $messageName = strtolower((string)($payload['messagename'] ?? ''));
    if (strpos($messageName, 'cancel') !== false) {
        return true;
    }

    foreach (['status', 'bookingstatus', 'bookingstatusid', 'statuscode'] as $key) {
        if (!isset($booking[$key])) continue;
        $value = strtolower(trim((string)$booking[$key]));
        if ($value === 'c' || $value === 'cx' || strpos($value, 'cancel') !== false) {
            return true;
        }
    }

    foreach (['canceldate', 'cancelleddate', 'cancellationdate', 'datecancelled'] as $key) {
        if (!empty($booking[$key])) {
            return true;
        }
    }

    return !empty($booking['cancelled']);
}

/*
   Release every order_rooming row on a cancelled order so the rooms stop
   holding inventory - same behaviour as rl_release_cancelled_inventory in
   reservation_list.php (released_status=1, check_out pulled back to
   check_in, checked_out_status=1), so channel cancellations and manual
   cancellations free rooms identically everywhere availability is counted.
*/
function nb_release_order_rooming(PDO $pdo, int $orderId): void
{
    foreach ([
        'released_status' => "ALTER TABLE `order_rooming` ADD COLUMN `released_status` TINYINT(1) NOT NULL DEFAULT 0",
        'released_at'     => "ALTER TABLE `order_rooming` ADD COLUMN `released_at` DATETIME NULL",
        'release_reason'  => "ALTER TABLE `order_rooming` ADD COLUMN `release_reason` VARCHAR(255) NULL",
    ] as $column => $ddl) {
        if (!ro_column_exists($pdo, 'order_rooming', $column)) {
            try { $pdo->exec($ddl); } catch (Throwable $e) {}
        }
    }

    // Capture any physically assigned rooms BEFORE releasing, so we can set
    // them back to available afterwards if nobody else currently holds them.
    $roomIds = [];
    try {
        $stmt = $pdo->prepare("SELECT DISTINCT room_id FROM order_rooming WHERE order_id = :oid AND room_id IS NOT NULL AND room_id > 0");
        $stmt->execute([':oid' => $orderId]);
        foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $rid) {
            $rid = (int)$rid;
            if ($rid > 0) $roomIds[$rid] = $rid;
        }
    } catch (Throwable $e) {}

    $set = [];
    if (ro_column_exists($pdo, 'order_rooming', 'released_status')) $set[] = 'released_status = 1';
    if (ro_column_exists($pdo, 'order_rooming', 'released_at')) $set[] = 'released_at = NOW()';
    if (ro_column_exists($pdo, 'order_rooming', 'release_reason')) $set[] = "release_reason = 'Cancelled via Nightsbridge'";
    if (ro_column_exists($pdo, 'order_rooming', 'checked_in_status')) $set[] = 'checked_in_status = 0';
    if (ro_column_exists($pdo, 'order_rooming', 'checked_out_status')) $set[] = 'checked_out_status = 1';
    if (ro_column_exists($pdo, 'order_rooming', 'check_out')) $set[] = 'check_out = check_in';
    if (ro_column_exists($pdo, 'order_rooming', 'updated_at')) $set[] = 'updated_at = NOW()';
    if ($set) {
        $pdo->prepare('UPDATE order_rooming SET ' . implode(', ', $set) . ' WHERE order_id = :oid')->execute([':oid' => $orderId]);
    }

    // Free the physical rooms if no OTHER active reservation is currently
    // in-house on them - same rule as reservation_list.php.
    if ($roomIds && ro_column_exists($pdo, 'rooms', 'status')) {
        $releasedClause = ro_column_exists($pdo, 'order_rooming', 'released_status') ? "AND COALESCE(rmg.released_status,0) = 0" : "";
        foreach ($roomIds as $roomId) {
            try {
                $stmt = $pdo->prepare("
                    SELECT COUNT(*) FROM order_rooming rmg
                    INNER JOIN orders o ON o.id = rmg.order_id
                    WHERE rmg.room_id = :room_id AND rmg.order_id <> :oid
                      AND DATE(o.check_in) <= CURDATE() AND DATE(o.check_out) > CURDATE()
                      AND COALESCE(rmg.checked_out_status,0) = 0 {$releasedClause}
                      AND COALESCE(o.status, 1) NOT IN (0, 5)
                ");
                $stmt->execute([':room_id' => $roomId, ':oid' => $orderId]);
                if ((int)$stmt->fetchColumn() === 0) {
                    $pdo->prepare("UPDATE rooms SET status = 'available' WHERE id = :id")->execute([':id' => $roomId]);
                }
            } catch (Throwable $e) {}
        }
    }
}

/*
   Local room type ids held by an order - used to target the real-time
   availability push at only the room type(s) the booking actually affects.
*/
function nb_order_room_type_ids(PDO $pdo, int $orderId): array
{
    try {
        $stmt = $pdo->prepare("SELECT DISTINCT room_type_id FROM order_rooming WHERE order_id = :oid AND room_type_id IS NOT NULL AND room_type_id > 0");
        $stmt->execute([':oid' => $orderId]);
        return array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));
    } catch (Throwable $e) {
        return [];
    }
}

/*
   Real-time availability push for the affected room type(s). Called AFTER
   the booking transaction has committed. Failures here are logged but NEVER
   affect the webhook response - the booking is already durably saved, and
   the nightly full inventory flush corrects any missed delta.
*/
function nb_trigger_availability_push(PDO $pdo, int $nbBookingId, ?int $bbid, array $roomTypeIds, string $source): void
{
    if (!function_exists('nb_run_inventory_push')) {
        // nightsbridge_inventory_push.php isn't deployed alongside this file -
        // the nightly cron flush will keep availability in sync instead.
        return;
    }
    try {
        $ids = array_values(array_unique(array_filter(array_map('intval', $roomTypeIds))));
        nb_run_inventory_push($pdo, $ids ?: null, $source, false);
    } catch (Throwable $e) {
        try {
            logBooking($pdo, $nbBookingId, $bbid, '', null, 'error',
                'Real-time availability push failed (booking itself saved OK, nightly flush will correct): ' . $e->getMessage());
        } catch (Throwable $ignore) {}
    }
}

/*
   Auto-allocate a physical room for a channel booking.

   Nightsbridge only sells a room TYPE, never a specific room, so bookings
   used to land with room_id = 0 and pile up in the calendar's Temporary
   Holding Area waiting for a human. This picks the first genuinely free
   room of that type for the stay dates and assigns it straight away.

   "Available" uses exactly the same rule as room_is_available_for_rooming()
   in move_booking.php - a room is taken when another ACTIVE booking's
   rooming row overlaps the dates (rmg.check_in < :co AND rmg.check_out > :ci,
   the standard half-open interval so a same-day turnover is NOT a clash) -
   so the webhook can never allocate a room the calendar considers occupied.

   $alreadyTaken carries the room ids allocated earlier in THIS webhook call.
   A 3-room Nightsbridge booking hits this function three times inside one
   uncommitted transaction; without it, the availability query (which cannot
   see the rows we are about to insert) would hand out the same room 3 times.

   Returns null when nothing is free. The strict pre-check rejects the entire
   booking before any order is inserted, so oversold bookings no longer fall
   back to room_id = 0 or the Temporary Holding Area.
*/
function nb_find_available_room(
    PDO $pdo,
    int $propertyId,
    int $roomTypeId,
    string $checkIn,
    string $checkOut,
    array $alreadyTaken = []
): ?array {
    if ($roomTypeId <= 0) {
        return null;
    }

    $roomStatusFilter = '';

    if (ro_column_exists($pdo, 'rooms', 'status')) {
        $roomStatusFilter .= "
            AND LOWER(TRIM(COALESCE(r.status, ''))) NOT IN (
                'out_of_order',
                'maintenance',
                'inactive',
                'blocked'
            )
        ";
    }

    if (ro_column_exists($pdo, 'rooms', 'is_active')) {
        $roomStatusFilter .= "
            AND COALESCE(r.is_active, 1) = 1
        ";
    }

    $releasedClause = '';

    if (ro_column_exists($pdo, 'order_rooming', 'released_status')) {
        $releasedClause .= "
            AND COALESCE(rmg.released_status, 0) = 0
        ";
    }

    $checkedOutClause = '';

    if (ro_column_exists($pdo, 'order_rooming', 'checked_out_status')) {
        $checkedOutClause .= "
            AND COALESCE(rmg.checked_out_status, 0) = 0
        ";
    }

    $takenClause = '';

    $params = [
        ':pid'  => $propertyId,
        ':rtid' => $roomTypeId,
        ':ci'   => $checkIn,
        ':co'   => $checkOut,
    ];

    if ($alreadyTaken) {
        $placeholders = [];

        foreach (array_values($alreadyTaken) as $index => $roomId) {
            $placeholder = ':taken' . ($index + 1);
            $placeholders[] = $placeholder;
            $params[$placeholder] = (int)$roomId;
        }

        $takenClause = "
            AND r.id NOT IN (" . implode(',', $placeholders) . ")
        ";
    }

    $sql = "
        SELECT
            r.id,
            r.room_no
        FROM rooms r
        WHERE r.property_id = :pid
          AND r.room_type_id = :rtid

          {$roomStatusFilter}
          {$takenClause}

          AND NOT EXISTS (
              SELECT 1
              FROM order_rooming rmg
              INNER JOIN orders o
                  ON o.id = rmg.order_id
              WHERE rmg.room_id = r.id

                AND rmg.check_in < :co
                AND rmg.check_out > :ci

                {$releasedClause}
                {$checkedOutClause}

                AND COALESCE(o.status, 1) NOT IN (0, 5)
          )

        ORDER BY
            CAST(r.room_no AS UNSIGNED) ASC,
            r.room_no ASC,
            r.id ASC

        LIMIT 1
    ";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ?: null;

    } catch (Throwable $e) {
        error_log(
            'Nightsbridge room allocation failed'
            . ' | property_id=' . $propertyId
            . ' | room_type_id=' . $roomTypeId
            . ' | check_in=' . $checkIn
            . ' | check_out=' . $checkOut
            . ' | SQL error=' . $e->getMessage()
        );

        throw $e;
    }
}

/* =========================================================================
   MAIN
   ========================================================================= */

if (($_SERVER['REQUEST_METHOD'] ?? 'GET') !== 'POST') {
    respondError('This endpoint only accepts POST requests.', 400);
}

global $pdo;
ensureNightsbridgeSchema($pdo);

// Accept either a raw JSON body, or a form-encoded "data" parameter
// (the spec mentions both styles for the outbound direction - being
// tolerant of either here costs nothing and avoids a silent failure
// if Nightsbridge's actual delivery differs slightly from the docs).
$rawBody = file_get_contents('php://input');
if (trim((string)$rawBody) === '' && isset($_POST['data'])) {
    $rawBody = $_POST['data'];
}

$payload = json_decode((string)$rawBody, true);
if (!is_array($payload)) {
    // TODO: if Nightsbridge's actual XML mode is used instead of JSON for
    // your account, this needs an XML parser branch instead - let me know
    // and I'll add it.
    respondError('Could not parse the incoming payload as JSON.', 101);
}

$loginKey = (string)($payload['loginkey'] ?? '');
if (!hash_equals(NIGHTSBRIDGE_LOGIN_KEY, $loginKey)) {
    respondError('Invalid credentials.', 102);
}

$booking = $payload['booking'] ?? null;
if (!is_array($booking) || empty($booking['bookingid'])) {
    respondError('Missing booking data.', 103);
}

$nbBookingId = (int)$booking['bookingid'];
$bbid = isset($payload['bbid']) ? (int)$payload['bbid'] : null;

// Serialize deliveries of the SAME booking. Nightsbridge retries can overlap
// with a still-running earlier delivery; without this, two concurrent requests
// both pass the SELECT idempotency check below (neither insert is committed
// yet) and BOTH insert - which is exactly how the duplicate reservations and
// guests were being created. The named lock makes the second delivery wait
// until the first has committed, so it then sees the existing order and takes
// the update path instead.
$lockName = 'nb_booking_' . $nbBookingId;
$lockStmt = $pdo->prepare("SELECT GET_LOCK(:name, 15)");
$lockStmt->execute([':name' => $lockName]);
if ((int)$lockStmt->fetchColumn() !== 1) {
    // Another delivery of this same booking is still being processed - tell
    // Nightsbridge to retry; by then the first one will have committed and
    // the retry will cleanly hit the update path.
    respondError('Booking is already being processed, please retry.', 105);
}

try {
    $pdo->beginTransaction();

    // Idempotency check: has this exact Nightsbridge booking already been imported?
    $existingStmt = $pdo->prepare("SELECT id FROM orders WHERE booking_source = 'nightsbridge' AND channel_booking_id = :nid LIMIT 1");
    $existingStmt->execute([':nid' => $nbBookingId]);
    $existingOrderId = $existingStmt->fetchColumn();

    /* ---------------------------------------------------------------------
       CANCELLATION - handled before anything else (no guest upsert needed).
       Sets orders.status = 5 (cancelled), releases every rooming row so the
       rooms stop holding inventory, then pushes the reopened availability to
       Nightsbridge immediately so their test can confirm it opens back up.
       --------------------------------------------------------------------- */
    if (nb_booking_is_cancellation($payload, $booking)) {
        if (!$existingOrderId) {
            // Cancellation for a booking we never imported - nothing to do
            // locally, but acknowledge it so Nightsbridge stops resending.
            $pdo->commit();
            logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, null, 'cancelled',
                'Cancellation received for a booking that was never imported - acknowledged, nothing to release.');
            respondSuccess();
        }

        $orderId = (int)$existingOrderId;
        $affectedRoomTypeIds = nb_order_room_type_ids($pdo, $orderId);

        $set = ['status = 5'];
        if (ro_column_exists($pdo, 'orders', 'cancellation_at')) $set[] = 'cancellation_at = NOW()';
        if (ro_column_exists($pdo, 'orders', 'cancellation_reason')) $set[] = "cancellation_reason = 'Cancelled via Nightsbridge'";
        if (ro_column_exists($pdo, 'orders', 'cancellation_inventory_released')) $set[] = 'cancellation_inventory_released = 1';
        if (ro_column_exists($pdo, 'orders', 'updated_at')) $set[] = 'updated_at = NOW()';
        $pdo->prepare('UPDATE orders SET ' . implode(', ', $set) . ' WHERE id = :id')->execute([':id' => $orderId]);

        nb_release_order_rooming($pdo, $orderId);

        logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, $orderId, 'cancelled',
            'Order set to status 5 and all rooming rows released.');

        $pdo->commit();

        // Push the reopened availability for the affected room type(s) NOW,
        // after commit - Nightsbridge sees the rooms free again immediately.
        nb_trigger_availability_push($pdo, $nbBookingId, $bbid, $affectedRoomTypeIds, 'webhook_cancel');
        respondSuccess();
    }

$madeBy = $booking['madeby'] ?? [];

$primaryGuest = $madeBy;

$firstRoomLine = (array)($booking['roomtypes'][0] ?? []);

if (!empty($firstRoomLine['guest']) && is_array($firstRoomLine['guest'])) {
    $primaryGuest = $firstRoomLine['guest'];
}

$bookingHolderGuestId = upsertGuest($pdo, $primaryGuest);

    $arrivalDate = (string)($booking['arrivaldate'] ?? '');
    $departureDate = (string)($booking['departuredate'] ?? '');
    $totalAmount = (float)($booking['totalamount'] ?? 0);
    $deposit = (float)($booking['deposit'] ?? 0);
    $notes = trim((string)($booking['notes'] ?? ''));
    $sourceName = trim((string)(($booking['source']['sourcename'] ?? '')));

    if ($existingOrderId) {
        $orderId = (int)$existingOrderId;
        $pushSource = 'webhook_update';
        $affectedRoomTypeIds = nb_order_room_type_ids($pdo, $orderId);
        // Only update Semper/Nightsbridge-owned fields - never touch room
        // assignment, folios, or anything staff manage locally, same
        // ownership rule used for the Semper sync.
        $pdo->prepare("
            UPDATE orders
            SET check_in = :ci, check_out = :co, totalamount = :total, guest_id = :gid, updated_at = NOW()
            WHERE id = :id
        ")->execute([
            ':ci' => $arrivalDate, ':co' => $departureDate, ':total' => $totalAmount,
            ':gid' => $bookingHolderGuestId, ':id' => $orderId,
        ]);
        logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, $orderId, 'updated');

        /*
           A date change does NOT move the guest's already-assigned room -
           that stays a human decision. But the new dates may now clash with
           another booking in that room, so detect it and log loudly rather
           than leaving a silent double-booking on the calendar.
        */
        try {
            $releasedClause = ro_column_exists($pdo, 'order_rooming', 'released_status')
                ? "AND COALESCE(rmg2.released_status, 0) = 0" : "";
            $clashStmt = $pdo->prepare("
                SELECT rmg1.room_id, COUNT(*) AS clashes
                FROM order_rooming rmg1
                INNER JOIN order_rooming rmg2
                    ON rmg2.room_id = rmg1.room_id
                   AND rmg2.order_id <> rmg1.order_id
                   AND rmg2.check_in < :co
                   AND rmg2.check_out > :ci
                INNER JOIN orders o2 ON o2.id = rmg2.order_id
                WHERE rmg1.order_id = :oid
                  AND rmg1.room_id > 0
                  AND COALESCE(o2.status, 1) NOT IN (0, 5)
                  {$releasedClause}
                GROUP BY rmg1.room_id
            ");
            $clashStmt->execute([':oid' => $orderId, ':ci' => $arrivalDate, ':co' => $departureDate]);
            $clashes = $clashStmt->fetchAll(PDO::FETCH_ASSOC);
            if ($clashes) {
                $roomIds = [];
                foreach ($clashes as $c) $roomIds[] = (int)$c['room_id'];
                logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, $orderId, 'error',
                    'DATE CHANGE CLASH: the new dates ' . $arrivalDate . ' to ' . $departureDate
                    . ' overlap another booking in already-assigned room id(s) ' . implode(', ', $roomIds)
                    . '. The rooming rows were NOT moved - reassign the room manually on the calendar.');
            }
        } catch (Throwable $e) { /* detection only - never block the update */ }
    } else {
        $pushSource = 'webhook_insert';
        $affectedRoomTypeIds = [];
        // Nightsbridge sends room type per room-line (in $booking['roomtypes']),
        // but the orders table itself also has its own NOT NULL room_type_id
        // column (separate from the per-room mapping written to
        // order_rooming below) - resolve it here from the FIRST room in the
        // booking so the orders insert has a value to satisfy that column.
        $firstRoomLine = (array)($booking['roomtypes'][0] ?? []);
        $primaryNbRoomTypeId = (int)($firstRoomLine['roomtypeid'] ?? 0);
        $primaryRoomTypeId = mapNightsbridgeRoomType($pdo, $primaryNbRoomTypeId);

        if ($primaryRoomTypeId === null) {
            $pdo->rollBack();
            logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, null, 'rejected',
                "No room type mapping found for Nightsbridge roomtypeid {$primaryNbRoomTypeId} - booking rejected before insert.");
            respondError('No room type mapping found for this booking.', 104);
        }

        /*
         * STRICT AVAILABILITY PRE-CHECK
         *
         * Every requested room must have a valid mapping and an available
         * physical room before the orders row is inserted. If one room cannot
         * be allocated, the entire transaction is rolled back and nothing from
         * this Nightsbridge delivery remains in the database.
         */
        $roomLines = array_values((array)($booking['roomtypes'] ?? []));
        if (!$roomLines) {
            $pdo->rollBack();
            logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, null, 'rejected',
                'Booking rejected because Nightsbridge supplied no room lines. Nothing was inserted.');
            respondError('Booking contains no rooms.', 106);
        }

        $preallocatedRooms = [];
        $roomsTakenThisBooking = [];

        foreach ($roomLines as $roomIndex => $roomLine) {
            $nbRoomTypeId = (int)($roomLine['roomtypeid'] ?? 0);
            $localRoomTypeId = mapNightsbridgeRoomType($pdo, $nbRoomTypeId);

            if ($localRoomTypeId === null) {
                $pdo->rollBack();
                logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, null, 'rejected',
                    "Booking rejected: no room type mapping for Nightsbridge roomtypeid {$nbRoomTypeId}. Nothing was inserted.");
                respondError('No room type mapping found for one or more rooms.', 104);
            }

            $freeRoom = nb_find_available_room(
                $pdo,
                2,
                $localRoomTypeId,
                $arrivalDate,
                $departureDate,
                $roomsTakenThisBooking
            );

            if (!$freeRoom) {
                $pdo->rollBack();
                logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, null, 'rejected',
                    "Booking rejected: no free room of room_type_id {$localRoomTypeId} for {$arrivalDate} to {$departureDate}. Nothing was inserted.");
                respondError('No availability for the requested room type and dates.', 106);
            }

            $assignedRoomId = (int)$freeRoom['id'];
            $roomsTakenThisBooking[] = $assignedRoomId;

            $preallocatedRooms[$roomIndex] = [
                'local_room_type_id' => $localRoomTypeId,
                'room_id'            => $assignedRoomId,
                'room_no'            => $freeRoom['room_no'],
            ];
        }

        // Number of rooms sold = number of room-lines in the booking. This
        // feeds orders.totalrooms; without it the booking shows 0 rooms even
        // though we create one order_rooming row per line below.
        $roomLineCount = max(1, count((array)($booking['roomtypes'] ?? [])));

        try {
            $pdo->prepare("
                INSERT INTO orders
                    (channel_booking_id, booking_source, guest_id, check_in, check_out, totalamount, status, reservation_notes, created_at,property_id,adults,room_type_id,currency,currency_unit,totalrooms)
                VALUES
                    (:nid, 'nightsbridge', :gid, :ci, :co, :total,2, :notes, NOW(),2,2,:rtid,'USD',1,:trooms)
            ")->execute([
                ':nid' => $nbBookingId, ':gid' => $bookingHolderGuestId, ':ci' => $arrivalDate,
                ':co' => $departureDate, ':total' => $totalAmount,
                ':notes' => trim($notes . ($sourceName !== '' ? " (via {$sourceName})" : '')),
                ':rtid' => $primaryRoomTypeId,
                ':trooms' => $roomLineCount,
            ]);
            $orderId = (int)$pdo->lastInsertId();
        } catch (PDOException $dupe) {
            // Duplicate-key on uniq_channel_booking: another delivery beat us
            // to the insert. Don't create anything - resolve the existing
            // order and log it, exactly as the update path would.
            if ((string)$dupe->getCode() !== '23000') {
                throw $dupe;
            }
            $pdo->rollBack();
            $existingStmt->execute([':nid' => $nbBookingId]);
            $orderId = (int)$existingStmt->fetchColumn();
            logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, $orderId ?: null, 'updated',
                'Duplicate delivery caught by uniq_channel_booking - no new order created.');
            respondSuccess();
        }

        // One order_rooming row per room. All rooms were successfully
        // preallocated before the order was inserted, so no accepted booking
        // can fall through to room_id = 0 or Temporary Holding.
        foreach ($roomLines as $roomIndex => $roomLine) {
            $allocation = $preallocatedRooms[$roomIndex];
            $localRoomTypeId = (int)$allocation['local_room_type_id'];
            $assignedRoomId = (int)$allocation['room_id'];
            $assignedRoomNo = $allocation['room_no'];
            $affectedRoomTypeIds[] = $localRoomTypeId;

            // The room's actual occupant, which can differ from the person
            // who made the whole booking.
            $roomGuestId = $bookingHolderGuestId;
            if (!empty($roomLine['guest'])) {
                $roomGuestId = upsertGuest($pdo, $roomLine['guest']);
            }

            $pdo->prepare("
                INSERT INTO order_rooming
                    (order_id, room_type_id, adults, children, check_in, check_out, folio_holder_guest_id,property_id,room_no,room_id)
                VALUES
                    (:oid, :rtid, :adults, :children, :ci, :co, :gid,2,:rno,:rid)
            ")->execute([
                ':oid' => $orderId,
                ':rtid' => $localRoomTypeId,
                ':adults' => (int)($roomLine['adults'] ?? 1),
                ':children' => (int)($roomLine['child1'] ?? 0) + (int)($roomLine['child2'] ?? 0),
                ':ci' => $arrivalDate,
                ':co' => $departureDate,
                ':gid' => $roomGuestId,
                ':rno' => $assignedRoomNo,
                ':rid' => $assignedRoomId,
            ]);

            // Pre-fill the pre-checkin guest name for this room, if your
            // order_rooming_guests table is in use - reuse whichever
            // column-detection helper you already have for that table
            // (see planner_build_group_rooming_list / calendar.php) rather
            // than duplicating it here.
        }

        // Set orders.totalrooms to the ACTUAL number of order_rooming rows
        // created for this booking, so the count is correct even if any line
        // was skipped. This is the authoritative room count.
        $cntStmt = $pdo->prepare("SELECT COUNT(*) FROM order_rooming WHERE order_id = :oid");
        $cntStmt->execute([':oid' => $orderId]);
        $actualRooms = max(1, (int)$cntStmt->fetchColumn());
        $pdo->prepare("UPDATE orders SET totalrooms = :n WHERE id = :oid")
            ->execute([':n' => $actualRooms, ':oid' => $orderId]);

        // TODO: post the deposit (if any) as an initial payment/folio
        // transaction, once the master folio exists for this order - reuse
        // your existing folio-resolution helper rather than duplicating it
        // here.

        logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, $orderId, 'inserted');
    }

    $pdo->commit();

    // Real-time availability push for the room type(s) this booking holds -
    // done AFTER commit so a slow/failed push can never roll back or delay
    // acknowledging the booking itself.
    nb_trigger_availability_push($pdo, $nbBookingId, $bbid, $affectedRoomTypeIds, $pushSource);
    respondSuccess();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    try {
        logBooking($pdo, $nbBookingId, $bbid, (string)$rawBody, null, 'error', $e->getMessage());
    } catch (Throwable $ignore) {
        // even the log insert failed - nothing more we can safely do here
    }
    respondError('Internal error processing booking: ' . $e->getMessage(), 500);
}
