<?php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();
require_once __DIR__ . '/quotation_lib.php';

$id = (int)($_GET['id'] ?? 0);

function qc_fail(string $message, int $quoteId = 0): void
{
    require_once __DIR__ . '/../../includes/header.php';
    ?>
    <div class="container-fluid py-3">
        <div class="alert alert-danger shadow-sm">
            <h5 class="mb-2">Conversion declined</h5>
            <div><?php echo q_h($message); ?></div>
        </div>
        <div class="d-flex gap-2">
            <?php if ($quoteId > 0): ?>
                <a href="quotation_form.php?id=<?php echo (int)$quoteId; ?>" class="btn btn-primary">Back to Quote</a>
                <a href="quotation_pdf.php?id=<?php echo (int)$quoteId; ?>" target="_blank" class="btn btn-outline-secondary">View PDF</a>
            <?php endif; ?>
            <a href="quotation_list.php" class="btn btn-light">Quotation List</a>
        </div>
    </div>
    <?php
    require_once __DIR__ . '/../../includes/footer.php';
    exit;
}


function qc_column_exists(PDO $pdo, string $table, string $column): bool
{
    static $cache = [];
    $key = strtolower($table . '.' . $column);

    if (array_key_exists($key, $cache)) {
        return $cache[$key];
    }

    try {
        $stmt = $pdo->prepare("\n            SELECT COUNT(*)\n            FROM INFORMATION_SCHEMA.COLUMNS\n            WHERE TABLE_SCHEMA = DATABASE()\n              AND TABLE_NAME = :table_name\n              AND COLUMN_NAME = :column_name\n        ");
        $stmt->execute([
            ':table_name' => $table,
            ':column_name' => $column,
        ]);
        $cache[$key] = ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable $e) {
        $cache[$key] = false;
    }

    return $cache[$key];
}

function qc_apply_optional_update(PDO $pdo, string $table, int $id, array $values): void
{
    if ($id <= 0) {
        return;
    }

    $sets = [];
    $params = [':id' => $id];

    foreach ($values as $column => $value) {
        if (!qc_column_exists($pdo, $table, $column)) {
            continue;
        }
        $param = ':v_' . preg_replace('/[^a-zA-Z0-9_]/', '_', $column);
        $sets[] = "`{$column}` = {$param}";
        $params[$param] = $value;
    }

    if (!$sets) {
        return;
    }

    $stmt = $pdo->prepare("UPDATE `{$table}` SET " . implode(', ', $sets) . " WHERE id = :id");
    $stmt->execute($params);
}

function qc_apply_optional_update_by_order_date(PDO $pdo, string $table, int $orderId, string $date, array $values): void
{
    if ($orderId <= 0 || trim($date) === '') {
        return;
    }

    $sets = [];
    $params = [
        ':order_id' => $orderId,
        ':date' => $date,
    ];

    foreach ($values as $column => $value) {
        if (!qc_column_exists($pdo, $table, $column)) {
            continue;
        }
        $param = ':v_' . preg_replace('/[^a-zA-Z0-9_]/', '_', $column);
        $sets[] = "`{$column}` = {$param}";
        $params[$param] = $value;
    }

    if (!$sets) {
        return;
    }

    $stmt = $pdo->prepare("UPDATE `{$table}` SET " . implode(', ', $sets) . " WHERE order_id = :order_id AND `date` = :date");
    $stmt->execute($params);
}

function qc_ensure_nightly_override_columns(PDO $pdo): void
{
    $alters = [
        'original_total'     => "ALTER TABLE rel_orders_prices ADD COLUMN original_total DECIMAL(12,2) NULL",
        'override_total'     => "ALTER TABLE rel_orders_prices ADD COLUMN override_total DECIMAL(12,2) NULL",
        'override_reason'    => "ALTER TABLE rel_orders_prices ADD COLUMN override_reason VARCHAR(255) NULL",
        'rate_overridden'    => "ALTER TABLE rel_orders_prices ADD COLUMN rate_overridden TINYINT(1) NOT NULL DEFAULT 0",
        'override_type'      => "ALTER TABLE rel_orders_prices ADD COLUMN override_type VARCHAR(30) NULL",
        'override_value'     => "ALTER TABLE rel_orders_prices ADD COLUMN override_value DECIMAL(12,2) NULL",
        'extra_bed_override' => "ALTER TABLE rel_orders_prices ADD COLUMN extra_bed_override DECIMAL(12,2) NULL",
        'override_by'        => "ALTER TABLE rel_orders_prices ADD COLUMN override_by INT NULL",
        'override_at'        => "ALTER TABLE rel_orders_prices ADD COLUMN override_at DATETIME NULL",
    ];

    foreach ($alters as $column => $sql) {
        if (!qc_column_exists($pdo, 'rel_orders_prices', $column)) {
            try {
                $pdo->exec($sql);
            } catch (Throwable $e) {
                // Ignore permission/duplicate-column errors. Conversion will still continue.
            }
        }
    }
}

function qc_room_type_title(PDO $pdo, int $roomTypeId): string
{
    if ($roomTypeId <= 0) {
        return 'Room Type';
    }

    try {
        $st = $pdo->prepare("SELECT title FROM room_types WHERE id = :id LIMIT 1");
        $st->execute([':id' => $roomTypeId]);
        $title = trim((string)$st->fetchColumn());
        return $title !== '' ? $title : ('Room Type #' . $roomTypeId);
    } catch (Throwable $e) {
        return 'Room Type #' . $roomTypeId;
    }
}


function qc_available_room_ids_for_type(PDO $pdo, int $propertyId, int $roomTypeId, string $checkIn, string $checkOut, int $excludeOrderId = 0): array
{
    if ($propertyId <= 0 || $roomTypeId <= 0 || trim($checkIn) === '' || trim($checkOut) === '') {
        return [];
    }

    $activeOrderSql = "COALESCE(o.status, 1) <> 0";

    $sql = "
        SELECT r.id
        FROM rooms r
        WHERE r.property_id = :property_id
          AND r.room_type_id = :room_type_id
          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 < :check_out
                AND rmg.check_out > :check_in
                AND {$activeOrderSql}
    ";

    $params = [
        ':property_id' => $propertyId,
        ':room_type_id' => $roomTypeId,
        ':check_in' => $checkIn,
        ':check_out' => $checkOut,
    ];

    if ($excludeOrderId > 0) {
        $sql .= " AND rmg.order_id <> :exclude_order_id ";
        $params[':exclude_order_id'] = $excludeOrderId;
    }

    $sql .= "
          )
        ORDER BY r.room_no ASC, r.id ASC
        FOR UPDATE
    ";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN) ?: []);
    } catch (Throwable $e) {
        /* Some MySQL/MariaDB versions do not allow FOR UPDATE with this query shape.
           Retry without FOR UPDATE rather than forcing conversion to holding. */
        $sql = str_replace("\n        FOR UPDATE", "", $sql);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN) ?: []);
    }
}

function qc_allocate_rooms_for_slots(PDO $pdo, int $propertyId, string $checkIn, string $checkOut, array $slots, int $orderId = 0): array
{
    $availableByType = [];
    $allocated = [];

    foreach ($slots as $idx => $slot) {
        $roomTypeId = (int)($slot['room_type_id'] ?? 0);
        if ($roomTypeId <= 0) {
            $allocated[$idx] = 0;
            continue;
        }

        if (!array_key_exists($roomTypeId, $availableByType)) {
            $availableByType[$roomTypeId] = qc_available_room_ids_for_type($pdo, $propertyId, $roomTypeId, $checkIn, $checkOut, $orderId);
        }

        $allocated[$idx] = (int)(array_shift($availableByType[$roomTypeId]) ?: 0);
    }

    return $allocated;
}

function qc_check_quote_availability(PDO $pdo, array $quote, array $roomRows): array
{
    $propertyId = (int)($quote['property_id'] ?? 0);
    $checkIn = (string)($quote['check_in'] ?? '');
    $checkOut = (string)($quote['check_out'] ?? '');

    if ($propertyId <= 0) {
        return ['ok' => false, 'message' => 'Quote has no property selected.'];
    }

    if (!strtotime($checkIn) || !strtotime($checkOut) || strtotime($checkOut) <= strtotime($checkIn)) {
        return ['ok' => false, 'message' => 'Quote has invalid check-in/check-out dates.'];
    }

    $requiredByType = [];

    foreach ($roomRows as $row) {
        $roomTypeId = (int)($row['room_type_id'] ?? 0);
        $qtyRooms = (int)($row['qty_rooms'] ?? 0);

        if ($roomTypeId <= 0) {
            return ['ok' => false, 'message' => 'One or more quotation room rows has no room type selected.'];
        }

        if ($qtyRooms <= 0) {
            return ['ok' => false, 'message' => 'One or more quotation room rows has invalid room quantity.'];
        }

        if (!isset($requiredByType[$roomTypeId])) {
            $requiredByType[$roomTypeId] = 0;
        }

        $requiredByType[$roomTypeId] += $qtyRooms;
    }

    $shortages = [];

    foreach ($requiredByType as $roomTypeId => $requiredRooms) {
        $availability = q_count_available_rooms_by_type($pdo, $propertyId, (int)$roomTypeId, $checkIn, $checkOut);

        $availableRooms = 0;
        if (is_array($availability)) {
            $availableRooms = (int)($availability['available_rooms'] ?? $availability['available'] ?? 0);
        }

        if ($availableRooms < $requiredRooms) {
            $shortages[] = [
                'room_type_id' => (int)$roomTypeId,
                'room_type' => qc_room_type_title($pdo, (int)$roomTypeId),
                'required' => (int)$requiredRooms,
                'available' => (int)$availableRooms,
            ];
        }
    }

    if ($shortages) {
        $parts = [];
        foreach ($shortages as $s) {
            $parts[] = $s['room_type'] . ': required ' . $s['required'] . ', available ' . $s['available'];
        }

        return [
            'ok' => false,
            'message' => 'Not enough room availability for this quotation. ' . implode('; ', $parts) . '. Conversion has been stopped.',
            'shortages' => $shortages,
        ];
    }

    return ['ok' => true, 'message' => 'Available'];
}

if ($id <= 0) {
    qc_fail('Invalid quote.');
}

$st = $pdo->prepare('SELECT * FROM reservation_quotes WHERE id = :id LIMIT 1');
$st->execute([':id' => $id]);
$q = $st->fetch(PDO::FETCH_ASSOC);

if (!$q) {
    qc_fail('Quote not found.', $id);
}

if (($q['status'] ?? '') === 'converted' && (int)($q['converted_order_id'] ?? 0) > 0) {
    header('Location: ../reservations/reservation_form.php?id=' . (int)$q['converted_order_id']);
    exit;
}

$rooms = $pdo->prepare('
    SELECT *
    FROM reservation_quote_rooms
    WHERE quote_id = :id
    ORDER BY row_no, id
');
$rooms->execute([':id' => $id]);
$roomRows = $rooms->fetchAll(PDO::FETCH_ASSOC) ?: [];

if (!$roomRows) {
    qc_fail('Quote has no room rows.', $id);
}

/*
    IMPORTANT:
    Do availability validation BEFORE opening transaction and before creating orders.
    This prevents half-converted quotations and overbooked room types.
*/
$availabilityCheck = qc_check_quote_availability($pdo, $q, $roomRows);
if (empty($availabilityCheck['ok'])) {
    qc_fail((string)$availabilityCheck['message'], $id);
}

try {
    $pdo->beginTransaction();

    /*
        Re-check availability inside the transaction as a safety guard.
        This reduces the risk of two users converting competing quotes at the same time.
    */
    $availabilityCheck = qc_check_quote_availability($pdo, $q, $roomRows);
    if (empty($availabilityCheck['ok'])) {
        throw new RuntimeException((string)$availabilityCheck['message']);
    }

    $guestId = (int)($q['guest_id'] ?? 0);
    if ($guestId <= 0) {
        $guestId = q_find_or_create_guest($pdo, (string)$q['guest_name']);
    }

    $summary = q_summarize_room_rows($roomRows);
    $orderNo = q_next_order_no($pdo);
    $userId = q_current_user_id();

    $ins = $pdo->prepare("
        INSERT INTO orders (
            property_id,
            order_no,
            guest_id,
            agent_id,
            contact_id,
            originating_agent,
            voucher_no,
            occassion_id,
            nationality_id,
            reservation_notes,
            internal_memo,
            reminder_info,
            check_in,
            check_out,
            adults,
            kids,
            room_type_id,
            totalrooms,
            room_id,
            ordered_on,
            created_by,
            updated_on,
            updated_by,
            updated_at,
            status,
            payment_status,
            currency,
            currency_unit,
            is_new
        ) VALUES (
            :property_id,
            :order_no,
            :guest_id,
            :agent_id,
            :contact_id,
            :originating_agent,
            :voucher_no,
            :occassion_id,
            :nationality_id,
            :reservation_notes,
            :internal_memo,
            NULL,
            :check_in,
            :check_out,
            :adults,
            :kids,
            :room_type_id,
            :totalrooms,
            0,
            NOW(),
            :created_by,
            NOW(),
            :updated_by,
            NOW(),
            1,
            0,
            :currency,
            :currency_unit,
            0
        )
    ");

    $currencyUnit = 1.0000;
    if (isset($q['currency_roe']) && (float)$q['currency_roe'] > 0) {
        $currencyUnit = (float)$q['currency_roe'];
    } elseif (isset($q['roe']) && (float)$q['roe'] > 0) {
        $currencyUnit = (float)$q['roe'];
    }

    $ins->execute([
        ':property_id'       => (int)$q['property_id'],
        ':order_no'          => $orderNo,
        ':guest_id'          => $guestId,
        ':agent_id'          => (int)$q['agent_id'] > 0 ? (int)$q['agent_id'] : null,
        ':contact_id'        => (int)$q['contact_id'] > 0 ? (int)$q['contact_id'] : null,
        ':originating_agent' => (int)$q['originating_agent'] > 0 ? (int)$q['originating_agent'] : null,
        ':voucher_no'        => $q['voucher_no'] ?: null,
        ':occassion_id'      => (int)$q['occassion_id'] > 0 ? (int)$q['occassion_id'] : null,
        ':nationality_id'    => (int)$q['nationality_id'] > 0 ? (int)$q['nationality_id'] : null,
        ':reservation_notes' => $q['reservation_notes'] ?: null,
        ':internal_memo'     => 'Converted from quotation ' . ($q['quote_no'] ?? $id) . (($q['internal_memo'] ?? '') ? "\n" . $q['internal_memo'] : ''),
        ':check_in'          => $q['check_in'],
        ':check_out'         => $q['check_out'],
        ':adults'            => (int)$summary['adults'],
        ':kids'              => (int)$summary['kids'],
        ':room_type_id'      => (int)($roomRows[0]['room_type_id'] ?? 0),
        ':totalrooms'        => (int)$summary['totalrooms'],
        ':created_by'        => $userId > 0 ? $userId : null,
        ':updated_by'        => $userId > 0 ? $userId : null,
        ':currency'          => $q['currency'] ?: 'USD',
        ':currency_unit'     => $currencyUnit,
    ]);

    $orderId = (int)$pdo->lastInsertId();


    /*
        Converted quotations already have their quoted nightly rows inserted below.
        Mark the booking as converted/price-locked where your schema supports it,
        so reservation_form.php does not treat it as a brand-new reservation and
        recalculate normal rack/nightly pricing on first open.
    */
    qc_apply_optional_update($pdo, 'orders', $orderId, [
        'is_new' => 0,
        'from_quote' => 1,
        'is_quote_converted' => 1,
        'quote_id' => $id,
        'source_quote_id' => $id,
        'price_locked' => 1,
        'lock_rates' => 1,
        'manual_rate' => 1,
        'rates_locked' => 1,
        'pricing_source' => 'quotation',
        'rate_source' => 'quotation',
    ]);

    $slots = q_expand_room_rows($roomRows);

    /*
        Do not send converted quotations into the temporary holding area.
        The old conversion inserted every order_rooming row with room_id = 0.
        Even when the room type had availability, the planner/reservation screen
        treated room_id = 0 as unallocated/holding.

        Allocate actual physical rooms by room type before inserting rooming rows.
        If a physical room cannot be found after the availability re-check, stop
        conversion instead of silently creating a holding-area booking.
    */
    $allocatedRoomIds = qc_allocate_rooms_for_slots(
        $pdo,
        (int)$q['property_id'],
        (string)$q['check_in'],
        (string)$q['check_out'],
        $slots,
        $orderId
    );

    $rins = $pdo->prepare("
        INSERT INTO order_rooming (
            order_id,
            property_id,
            room_no,
            room_type_id,
            room_id,
            adults,
            children,
            check_in,
            check_out
        ) VALUES (
            :order_id,
            :property_id,
            :room_no,
            :room_type_id,
            :room_id,
            :adults,
            :children,
            :check_in,
            :check_out
        )
    " );

    $firstAllocatedRoomId = 0;

    foreach ($slots as $idx => $s) {
        $allocatedRoomId = (int)($allocatedRoomIds[$idx] ?? 0);

        if ($allocatedRoomId <= 0) {
            throw new RuntimeException(
                'Availability exists by room type, but no physical room could be allocated for ' .
                qc_room_type_title($pdo, (int)$s['room_type_id']) .
                '. Please check room setup/status and existing room allocations.'
            );
        }

        if ($firstAllocatedRoomId <= 0) {
            $firstAllocatedRoomId = $allocatedRoomId;
        }

        $rins->execute([
            ':order_id'     => $orderId,
            ':property_id'  => (int)$q['property_id'],
            ':room_no'      => $idx + 1,
            ':room_type_id' => (int)$s['room_type_id'],
            ':room_id'      => $allocatedRoomId,
            ':adults'       => (int)$s['adults'],
            ':children'     => (int)$s['children'],
            ':check_in'     => $q['check_in'],
            ':check_out'    => $q['check_out'],
        ]);
    }

    if ($firstAllocatedRoomId > 0) {
        qc_apply_optional_update($pdo, 'orders', $orderId, [
            'room_id' => $firstAllocatedRoomId,
        ]);
    }

    $prices = $pdo->prepare("
        SELECT
            stay_date,
            SUM(total) AS total,
            MAX(rate_plan_id) AS rate_plan_id,
            MAX(rate_plan_title) AS rate_plan_title,
            MAX(rate_plan_currency) AS rate_plan_currency,
            MAX(rate_plan_season_label) AS rate_plan_season_label,
            MAX(rate_plan_season_color) AS rate_plan_season_color,
            SUM(adult_total) AS adult_total,
            SUM(child_total) AS child_total,
            MAX(sharing_rate) AS sharing_rate,
            MAX(single_rate) AS single_rate,
            MAX(single_supplement) AS single_supplement,
            MAX(child_rate_percent) AS child_rate_percent
        FROM reservation_quote_prices
        WHERE quote_id = :id
        GROUP BY stay_date
        ORDER BY stay_date
    ");
    $prices->execute([':id' => $id]);

    qc_ensure_nightly_override_columns($pdo);

    $pins = $pdo->prepare("
        INSERT INTO rel_orders_prices (
            order_id,
            property_id,
            date,
            price,
            additional_person,
            additional_person_price,
            total,
            room_id,
            rate_plan_id,
            rate_plan_title,
            rate_plan_currency,
            rate_plan_season_label,
            rate_plan_season_color,
            adults_count,
            kids_count,
            sharing_rate,
            single_rate,
            single_supplement,
            child_rate_percent,
            adult_total,
            child_total,
            applied_rate_type
        ) VALUES (
            :order_id,
            :property_id,
            :date,
            :price,
            0,
            :additional_person_price,
            :total,
            0,
            :rate_plan_id,
            :rate_plan_title,
            :rate_plan_currency,
            :rate_plan_season_label,
            :rate_plan_season_color,
            :adults_count,
            :kids_count,
            :sharing_rate,
            :single_rate,
            :single_supplement,
            :child_rate_percent,
            :adult_total,
            :child_total,
            'quote_converted'
        )
    ");

    /*
        Preserve quotation discounts when converting to a reservation.

        Older logic copied reservation_quote_prices totals directly. If the quote
        had a header/overall discount, reservation_quotes.totalamount was the
        discounted amount, but reservation_quote_prices still represented normal
        pricing. That made the booking reopen in Reservations at normal price.

        Fix:
        - Load all quote price rows first.
        - Compare their raw total to the quote header totalamount.
        - If the quote header total is lower/higher, distribute the factor across
          nightly rel_orders_prices rows.
        - Store the converted rows as quote_converted so the reservation keeps the
          quoted manual/discounted rate instead of recalculating normal pricing.
    */
    $priceRows = $prices->fetchAll(PDO::FETCH_ASSOC) ?: [];

    $rawGrand = 0.00;
    foreach ($priceRows as $p) {
        $rawGrand += (float)($p['total'] ?? 0);
    }

    $quoteGrand = 0.00;
    foreach (['totalamount', 'total_amount', 'grand_total', 'quote_total', 'net_total'] as $totalCol) {
        if (isset($q[$totalCol]) && (float)$q[$totalCol] > 0) {
            $quoteGrand = (float)$q[$totalCol];
            break;
        }
    }

    $conversionFactor = 1.000000;
    if ($quoteGrand > 0 && $rawGrand > 0 && abs($quoteGrand - $rawGrand) > 0.01) {
        $conversionFactor = $quoteGrand / $rawGrand;
    }

    $grand = 0.00;
    $nights = 0;
    $runningRoundedTotal = 0.00;
    $priceRowCount = count($priceRows);

    foreach ($priceRows as $rowIndex => $p) {
        $nights++;

        $isLastPriceRow = ($rowIndex === $priceRowCount - 1);
        $rawTotal = (float)($p['total'] ?? 0);
        $convertedTotal = round($rawTotal * $conversionFactor, 2);

        /*
           Keep the final row adjusted for rounding so the booking total equals
           the quote total exactly when a discounted quote total exists.
        */
        if ($quoteGrand > 0 && $rawGrand > 0 && $isLastPriceRow) {
            $convertedTotal = round($quoteGrand - $runningRoundedTotal, 2);
        }

        $runningRoundedTotal += $convertedTotal;
        $grand += $convertedTotal;

        $convertedSharingRate      = round((float)($p['sharing_rate'] ?? 0) * $conversionFactor, 4);
        $convertedSingleRate       = round((float)($p['single_rate'] ?? 0) * $conversionFactor, 4);
        $convertedSingleSupplement = round((float)($p['single_supplement'] ?? 0) * $conversionFactor, 4);
        $convertedAdultTotal       = round((float)($p['adult_total'] ?? 0) * $conversionFactor, 2);
        $convertedChildTotal       = round((float)($p['child_total'] ?? 0) * $conversionFactor, 2);

        $pins->execute([
            ':order_id'                 => $orderId,
            ':property_id'              => (int)$q['property_id'],
            ':date'                     => $p['stay_date'],
            ':price'                    => $convertedSharingRate,
            ':additional_person_price'  => $convertedChildTotal,
            ':total'                    => $convertedTotal,
            ':rate_plan_id'             => (int)$p['rate_plan_id'],
            ':rate_plan_title'          => $p['rate_plan_title'] ?: 'Quote converted rate',
            ':rate_plan_currency'       => $p['rate_plan_currency'] ?: ($q['currency'] ?: 'USD'),
            ':rate_plan_season_label'   => $p['rate_plan_season_label'],
            ':rate_plan_season_color'   => $p['rate_plan_season_color'],
            ':adults_count'             => (int)$summary['adults'],
            ':kids_count'               => (int)$summary['kids'],
            ':sharing_rate'             => $convertedSharingRate,
            ':single_rate'              => $convertedSingleRate,
            ':single_supplement'        => $convertedSingleSupplement,
            ':child_rate_percent'       => (float)$p['child_rate_percent'],
            ':adult_total'              => $convertedAdultTotal,
            ':child_total'              => $convertedChildTotal,
        ]);


        /*
           The reservation screen applies saved overrides by order_id + date.
           Some installs do not have an id column on rel_orders_prices, so do
           not rely on lastInsertId(). Write the official override columns by
           date immediately after inserting the nightly row.
        */
        $overrideValues = [
            'rate_overridden'    => 1,
            'override_type'      => 'total',
            'override_value'     => $convertedTotal,
            'override_total'     => $convertedTotal,
            'original_total'     => $rawTotal,
            'override_reason'    => 'Converted from discounted quotation ' . ($q['quote_no'] ?? $id),
            'extra_bed_override' => null,
            'override_by'        => $userId > 0 ? $userId : null,
            'override_at'        => date('Y-m-d H:i:s'),
            'is_manual'          => 1,
            'manual_rate'        => 1,
            'is_override'        => 1,
            'override_price'     => 1,
            'price_locked'       => 1,
            'lock_rate'          => 1,
            'rate_locked'        => 1,
            'source_quote_id'    => $id,
            'quote_id'           => $id,
            'pricing_source'     => 'quotation',
            'rate_source'        => 'quotation',
            'discount_factor'    => $conversionFactor,
            'quoted_total'       => $convertedTotal,
        ];

        qc_apply_optional_update_by_order_date($pdo, 'rel_orders_prices', $orderId, (string)$p['stay_date'], $overrideValues);

        $priceRowId = (int)$pdo->lastInsertId();
        if ($priceRowId > 0) {
            qc_apply_optional_update($pdo, 'rel_orders_prices', $priceRowId, $overrideValues);
        }
    }

    if ($grand <= 0 && $quoteGrand > 0) {
        $grand = $quoteGrand;
    }

    $pdo->prepare("
        UPDATE orders
        SET nights = :n,
            base_price = :b,
            amount = :a,
            totalamount = :t
        WHERE id = :id
    ")->execute([
        ':n'  => $nights,
        ':b'  => round($grand, 2),
        ':a'  => round($grand, 2),
        ':t'  => round($grand, 2),
        ':id' => $orderId,
    ]);

    $pdo->prepare("
        UPDATE reservation_quotes
        SET status = 'converted',
            converted_order_id = :order_id,
            converted_at = NOW(),
            updated_at = NOW()
        WHERE id = :id
    ")->execute([
        ':order_id' => $orderId,
        ':id'       => $id,
    ]);

    $pdo->commit();

    header('Location: ../reservations/reservation_form.php?id=' . $orderId);
    exit;
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    qc_fail('Conversion failed: ' . $e->getMessage(), $id);
}
