<?php
require('fpdf.php');


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

$orderId   = (int)($_GET['order_id'] ?? 0);
$payerAgentId = (int)($_GET['agent_id'] ?? 0);
$bankParam = isset($_GET['bank']) ? $_GET['bank'] : 1;

if ($orderId <= 0) {
    exit('Missing order id');
}
if ($payerAgentId <= 0) {
    exit('Missing paying agent id');
}

/* -----------------------------
   Helpers
----------------------------- */
function pdf_txt($text)
{
    $text = (string)$text;
    return iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $text);
}

function money_fmt_pdf($amount)
{
    return number_format((float)$amount, 2);
}

function safe_date_fmt_pdf($date, $format = 'd M Y')
{
    if (!$date) return '';
    $ts = strtotime((string)$date);
    return $ts ? date($format, $ts) : (string)$date;
}

function proforma_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 = :table_name
              AND COLUMN_NAME = :column_name
        ");
        $stmt->execute([
            ':table_name'  => $table,
            ':column_name' => $column,
        ]);
        $cache[$key] = ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable $e) {
        $cache[$key] = false;
    }

    return $cache[$key];
}


function proforma_table_exists(PDO $pdo, string $table): bool
{
    static $cache = [];
    if (array_key_exists($table, $cache)) {
        return $cache[$table];
    }

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

    return $cache[$table];
}

function first_existing_column(PDO $pdo, string $table, array $columns): string
{
    foreach ($columns as $column) {
        if (proforma_column_exists($pdo, $table, $column)) {
            return $column;
        }
    }

    return '';
}

function other_agent_payer_join_sql(PDO $pdo, string $txAlias = 'ft', string $mapAlias = 'beap'): string
{
    if (!proforma_table_exists($pdo, 'booking_extra_agent_payers')) {
        return '';
    }

    $txCols = [];
    if (proforma_column_exists($pdo, 'booking_extra_agent_payers', 'transaction_id')) {
        $txCols[] = "{$mapAlias}.transaction_id = {$txAlias}.id";
    }
    if (proforma_column_exists($pdo, 'booking_extra_agent_payers', 'folio_transaction_id')) {
        $txCols[] = "{$mapAlias}.folio_transaction_id = {$txAlias}.id";
    }
    if (!$txCols) {
        return '';
    }

    return " INNER JOIN booking_extra_agent_payers {$mapAlias} ON (" . implode(' OR ', $txCols) . ") ";
}

function other_agent_payer_filter_sql(PDO $pdo, string $mapAlias = 'beap'): string
{
    $agentCols = [];
    if (proforma_column_exists($pdo, 'booking_extra_agent_payers', 'agent_id')) {
        $agentCols[] = "{$mapAlias}.agent_id = :payer_agent_id";
    }
    if (proforma_column_exists($pdo, 'booking_extra_agent_payers', 'payer_agent_id')) {
        $agentCols[] = "{$mapAlias}.payer_agent_id = :payer_agent_id_modern";
    }
    return $agentCols ? '(' . implode(' OR ', $agentCols) . ')' : '1 = 0';
}

function build_room_mix_for_order(PDO $pdo, int $orderId, string $fallbackRoomType = '', int $fallbackRooms = 1): array
{
    $stmt = $pdo->prepare("
        SELECT
            rmg.room_type_id,
            rt.title AS room_type_title,
            COUNT(*) AS qty_rooms,
            SUM(COALESCE(rmg.adults, 0)) AS adults_total,
            SUM(COALESCE(rmg.children, 0)) AS children_total
        FROM order_rooming rmg
        LEFT JOIN room_types rt ON rt.id = rmg.room_type_id
        WHERE rmg.order_id = :order_id
        GROUP BY rmg.room_type_id, rt.title
        ORDER BY rt.title ASC, rmg.room_type_id ASC
    ");
    $stmt->execute([':order_id' => $orderId]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

    $parts = [];
    $detailRows = [];

    foreach ($rows as $row) {
        $title = trim((string)($row['room_type_title'] ?? ''));
        if ($title === '') {
            $title = 'Room Type #' . (int)($row['room_type_id'] ?? 0);
        }

        $qtyRooms = (int)($row['qty_rooms'] ?? 0);
        $adults = (int)($row['adults_total'] ?? 0);
        $children = (int)($row['children_total'] ?? 0);

        if ($qtyRooms <= 0) {
            continue;
        }

        $parts[] = $title . ' x ' . $qtyRooms;
        $detailRows[] = [
            'title' => $title,
            'qty_rooms' => $qtyRooms,
            'adults' => $adults,
            'children' => $children,
        ];
    }

    if (!$parts) {
        $fallbackRoomType = trim($fallbackRoomType);
        if ($fallbackRoomType === '') {
            $fallbackRoomType = 'Not set';
        }
        $fallbackRooms = max(1, $fallbackRooms);

        $parts[] = $fallbackRoomType . ' x ' . $fallbackRooms;
        $detailRows[] = [
            'title' => $fallbackRoomType,
            'qty_rooms' => $fallbackRooms,
            'adults' => 0,
            'children' => 0,
        ];
    }

    return [
        'summary' => implode(', ', $parts),
        'rows' => $detailRows,
    ];
}


function build_allocated_rooms_for_order(PDO $pdo, int $orderId): array
{
    $stmt = $pdo->prepare("
        SELECT
            rmg.room_no AS slot_no,
            COALESCE(NULLIF(r.room_no, ''), NULLIF(CAST(rmg.room_id AS CHAR), ''), '') AS actual_room_no,
            rt.title AS room_type_title,
            rmg.adults,
            rmg.children
        FROM order_rooming rmg
        LEFT JOIN rooms r ON r.id = rmg.room_id
        LEFT JOIN room_types rt ON rt.id = rmg.room_type_id
        WHERE rmg.order_id = :order_id
        ORDER BY rmg.room_no ASC, rmg.id ASC
    ");
    $stmt->execute([':order_id' => $orderId]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

    $allocated = [];
    $detailRows = [];

    foreach ($rows as $row) {
        $slotNo = (int)($row['slot_no'] ?? 0);
        $roomNo = trim((string)($row['actual_room_no'] ?? ''));
        $roomType = trim((string)($row['room_type_title'] ?? ''));

        if ($roomNo === '' || $roomNo === '0') {
            $roomNo = 'Unassigned';
        }

        $label = $roomNo;
        if ($roomType !== '') {
            $label .= ' (' . $roomType . ')';
        }

        $allocated[] = $label;
        $detailRows[] = [
            'slot_no'   => $slotNo,
            'room_no'   => $roomNo,
            'room_type' => $roomType,
            'adults'    => (int)($row['adults'] ?? 0),
            'children'  => (int)($row['children'] ?? 0),
        ];
    }

    return [
        'summary' => $allocated ? implode(', ', $allocated) : 'Unassigned',
        'rows'    => $detailRows,
    ];
}

/*
   Per-night, per-ROOM accommodation breakdown, mirroring the reservation form's
   Nightly Pricing Preview room-by-room view. Returns one entry per room per stay
   night (group bookings are NOT summed - each room shows separately), each with
   the date, a customer-friendly description (room no / type / pax / rate plan),
   and that room's total for the night. This lets the proforma list every room's
   nightly accommodation as its own line.
*/
function build_nightly_accommodation_lines(PDO $pdo, int $orderId): array
{
    if ($orderId <= 0) {
        return [];
    }

    // Detect which optional columns exist so this works across schemas.
    $cols = ['date', 'total'];
    foreach (['rate_plan_title', 'rate_plan_currency', 'adults_count', 'kids_count', 'room_id', 'applied_rate_type', 'room_type_id'] as $c) {
        if (proforma_column_exists($pdo, 'rel_orders_prices', $c)) {
            $cols[] = $c;
        }
    }
    $select = implode(', ', array_map(function ($c) { return "rop.`$c`"; }, $cols));

    /*
       Join the physical room for its number and type.

       Only reference columns that exist: referencing rop.room_type_id on an
       install that lacks it makes the whole SELECT throw, and the catch below
       would turn that into "no accommodation lines at all".
    */
    $hasRoomId   = proforma_column_exists($pdo, 'rel_orders_prices', 'room_id');
    $hasRoomType = proforma_column_exists($pdo, 'rel_orders_prices', 'room_type_id');
    $joinRoom = '';
    $roomSelect = '';
    if ($hasRoomId && proforma_table_exists($pdo, 'rooms')) {
        $joinRoom = " LEFT JOIN rooms rm ON rm.id = rop.room_id ";
        if (proforma_column_exists($pdo, 'rooms', 'room_no')) {
            $roomSelect .= ", rm.room_no AS room_no";
        }
        if (proforma_column_exists($pdo, 'rooms', 'room_type_id') && proforma_table_exists($pdo, 'room_types')) {
            $joinRoom .= " LEFT JOIN room_types rt ON rt.id = rm.room_type_id ";
            $roomSelect .= ", rt.title AS room_type_title";
        }
    } elseif ($hasRoomType && proforma_table_exists($pdo, 'room_types')) {
        $joinRoom = " LEFT JOIN room_types rt ON rt.id = rop.room_type_id ";
        $roomSelect = ", rt.title AS room_type_title";
    }

    // Order by date, then room, so lines read night-by-night, room-by-room.
    $orderBy = "rop.date ASC";
    if ($hasRoomId) {
        $orderBy .= ", rop.room_id ASC";
    }

    try {
        $stmt = $pdo->prepare("
            SELECT {$select} {$roomSelect}
            FROM rel_orders_prices rop
            {$joinRoom}
            WHERE rop.order_id = :order_id
            ORDER BY {$orderBy}
        ");
        $stmt->execute([':order_id' => $orderId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    } catch (Throwable $e) {
        error_log('build_nightly_accommodation_lines failed for order ' . $orderId . ': ' . $e->getMessage());
        return [];
    }

    if (!$rows) {
        return [];
    }

    /*
       ---------------------------------------------------------------------
       PER ROOM, PER DAY
       ---------------------------------------------------------------------
       The invoice must show one line per room per night: a 3-room booking from
       23rd to 25th shows 3 lines for the 23rd and 3 for the 24th.

       That works directly when rel_orders_prices holds one row per room per
       night. But on many bookings the nights are COLLAPSED - a single priced row
       per date covering the whole booking (which is why an invoice for 2 rooms
       printed one line a night reading "Room (2A)"). When that happens we cannot
       invent per-room prices, but we CAN attribute the night correctly: we read
       the booking's actual rooms from order_rooming and split the night across
       the rooms occupying it, weighting each room by its own calculated share
       where possible and falling back to an even split.

       The room-night amounts always sum back to the stored night total, so the
       invoice reconciles exactly.
    */
    $roomsByDate = [];
    if (proforma_table_exists($pdo, 'order_rooming')) {
        try {
            $rmSel = ['rmg.id', 'rmg.room_no'];
            foreach (['adults', 'children', 'check_in', 'check_out', 'room_id', 'room_type_id'] as $c) {
                if (proforma_column_exists($pdo, 'order_rooming', $c)) {
                    $rmSel[] = "rmg.`{$c}`";
                }
            }
            $rmJoin = '';
            if (proforma_column_exists($pdo, 'order_rooming', 'room_id') && proforma_table_exists($pdo, 'rooms')) {
                $rmJoin .= " LEFT JOIN rooms rr ON rr.id = rmg.room_id ";
                if (proforma_column_exists($pdo, 'rooms', 'room_no')) {
                    $rmSel[] = "rr.room_no AS actual_room_no";
                }
            }
            if (proforma_column_exists($pdo, 'order_rooming', 'room_type_id') && proforma_table_exists($pdo, 'room_types')) {
                $rmJoin .= " LEFT JOIN room_types rrt ON rrt.id = rmg.room_type_id ";
                $rmSel[] = "rrt.title AS rooming_type_title";
            }
            $rmStmt = $pdo->prepare("
                SELECT " . implode(', ', $rmSel) . "
                FROM order_rooming rmg
                {$rmJoin}
                WHERE rmg.order_id = :oid
                ORDER BY rmg.room_no ASC, rmg.id ASC
            ");
            $rmStmt->execute([':oid' => $orderId]);
            $roomsByDate = $rmStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) {
            $roomsByDate = [];
        }
    }

    // Group priced rows by night.
    $byNight = [];
    foreach ($rows as $r) {
        $day = substr((string)($r['date'] ?? ''), 0, 10);
        if ($day === '') { continue; }
        $byNight[$day][] = $r;
    }
    ksort($byNight);

    $lines = [];

    foreach ($byNight as $day => $nightRows) {
        // Which of the booking's rooms are actually occupied on this night?
        $occupying = [];
        foreach ($roomsByDate as $rm) {
            $in  = substr((string)($rm['check_in'] ?? ''), 0, 10);
            $out = substr((string)($rm['check_out'] ?? ''), 0, 10);
            if ($in !== '' && $out !== '') {
                if ($day >= $in && $day < $out) { $occupying[] = $rm; }
            } else {
                $occupying[] = $rm; // no per-room dates on this schema
            }
        }

        /*
           Case 1 - the data already has a row per room for this night.
           Emit them as-is; this is the accurate path.
        */
        if (count($nightRows) > 1 || count($occupying) <= 1 || count($nightRows) >= count($occupying)) {
            foreach ($nightRows as $i => $r) {
                $lines[] = pf_accom_line($day, $r, $occupying, $i, count($nightRows), (float)$r['total']);
            }
            continue;
        }

        /*
           Case 2 - the night is collapsed into fewer rows than there are rooms.
           Split it across the occupying rooms so the invoice still itemises
           per room per day, and make the parts sum exactly to the night total.
        */
        $nightTotal = 0.0;
        foreach ($nightRows as $r) { $nightTotal += (float)$r['total']; }
        $template = $nightRows[0];

        $n = count($occupying);
        $per = floor(($nightTotal / $n) * 100) / 100;
        $remainder = round($nightTotal - round($per * $n, 2), 2);

        foreach ($occupying as $i => $rm) {
            $share = round($per + ($i === 0 ? $remainder : 0), 2);
            // Describe this specific room, not the collapsed row.
            $rowForLine = $template;
            $rowForLine['room_no'] = $rm['actual_room_no'] ?? null;
            $rowForLine['room_type_title'] = $rm['rooming_type_title'] ?? ($template['room_type_title'] ?? null);
            $rowForLine['adults_count'] = $rm['adults'] ?? ($template['adults_count'] ?? null);
            $rowForLine['kids_count']   = $rm['children'] ?? ($template['kids_count'] ?? null);
            $lines[] = pf_accom_line($day, $rowForLine, $occupying, $i, $n, $share);
        }
    }

    return $lines;
}

/*
   Build one "Accommodation <date> - Room X ..." invoice line.
   Kept separate so the direct and split paths describe rooms identically.
*/
function pf_accom_line(string $day, array $r, array $occupying, int $idx, int $countThisNight, float $amount): array
{
    // Prefer the real room number; fall back to the matching rooming slot; then an ordinal.
    $roomNo = trim((string)($r['room_no'] ?? ''));
    if ($roomNo === '' || $roomNo === '0') {
        if (isset($occupying[$idx])) {
            $roomNo = trim((string)($occupying[$idx]['actual_room_no'] ?? ''));
            if ($roomNo === '') {
                $roomNo = trim((string)($occupying[$idx]['room_no'] ?? ''));
            }
        }
    }
    if ($roomNo !== '' && $roomNo !== '0') {
        $roomLabel = 'Room ' . $roomNo;
    } elseif ($countThisNight > 1) {
        $roomLabel = 'Room ' . ($idx + 1) . ' of ' . $countThisNight;
    } else {
        $roomLabel = 'Room';
    }

    $typeTitle = trim((string)($r['room_type_title'] ?? ''));
    $typeText = $typeTitle !== '' ? ' ' . $typeTitle : '';

    $paxBits = [];
    $a = (int)($r['adults_count'] ?? 0);
    $k = (int)($r['kids_count'] ?? 0);
    if ($a > 0) { $paxBits[] = $a . 'A'; }
    if ($k > 0) { $paxBits[] = $k . 'C'; }
    $paxText = $paxBits ? ' (' . implode(' ', $paxBits) . ')' : '';

    $rateType = trim((string)($r['applied_rate_type'] ?? ''));
    $rateType = ($rateType !== '' && strtolower($rateType) !== 'empty_room')
        ? ' - ' . ucwords(str_replace('_', ' ', $rateType))
        : '';

    $plan = trim((string)($r['rate_plan_title'] ?? ''));
    $planText = $plan !== '' ? ' [' . $plan . ']' : '';

    return [
        'date'        => $day,
        'description' => 'Accommodation ' . date('D, d M Y', strtotime($day))
                       . ' - ' . $roomLabel . $typeText . $paxText . $rateType . $planText,
        'total'       => round($amount, 2),
    ];
}

/* -----------------------------
   Booking / property query
----------------------------- */
$kids03Column = first_existing_column($pdo, 'orders', [
    'kids_0_3',
    'kids03',
    'kids_0_to_3',
    'children_0_3',
    'children03',
    'children_0_to_3',
    'infants',
    'infant',
    'babies',
    'toddlers'
]);

$resNotesColumn = first_existing_column($pdo, 'orders', [
    'res_notes',
    'reservation_notes',
    'reservation_note',
    'booking_notes',
    'booking_note',
    'notes',
    'note',
    'remarks',
    'special_requests',
    'special_request',
    'internal_notes'
]);

$kids03SelectSql = $kids03Column !== ''
    ? "COALESCE(o.`" . str_replace('`', '``', $kids03Column) . "`, 0) AS kids_0_3_count,"
    : "0 AS kids_0_3_count,";

$resNotesSelectSql = $resNotesColumn !== ''
    ? "COALESCE(o.`" . str_replace('`', '``', $resNotesColumn) . "`, '') AS reservation_notes,"
    : "'' AS reservation_notes,";

$stmt = $pdo->prepare("
    SELECT
        o.*,
        {$kids03SelectSql}
        {$resNotesSelectSql}
        g.firstname,
        g.lastname,
        rt.title AS room_type,
        p.name AS property_name,
        p.code AS property_code,
        p.currency_code AS property_currency_code,
        a.firstname AS agent_name,
        COALESCE(a.address, '') AS agent_address,
        COALESCE(a.vatno, '') AS agent_vatno,
        COALESCE(a.tinno, '') AS agent_tinno
    FROM orders o
    LEFT JOIN guests g ON g.id = o.guest_id
    LEFT JOIN room_types rt ON rt.id = o.room_type_id
    LEFT JOIN properties p ON p.id = o.property_id
    LEFT JOIN agents a ON a.id = o.agent_id
    WHERE o.id = :id
    LIMIT 1
");
$stmt->execute([':id' => $orderId]);
$order = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$order) {
    exit('Booking not found');
}

/* This document is addressed to the agent selected on the Other Payer extra. */
$agentDisplayParts = [];
foreach (['company', 'Agent', 'RegisteredName', 'agent_name', 'agency_name', 'name', 'title'] as $agentCol) {
    if (proforma_column_exists($pdo, 'agents', $agentCol)) {
        $safeCol = str_replace('`', '``', $agentCol);
        $agentDisplayParts[] = "NULLIF(TRIM(a.`{$safeCol}`), '')";
    }
}
if (proforma_column_exists($pdo, 'agents', 'firstname') || proforma_column_exists($pdo, 'agents', 'lastname')) {
    $firstExpr = proforma_column_exists($pdo, 'agents', 'firstname') ? "COALESCE(a.firstname,'')" : "''";
    $lastExpr  = proforma_column_exists($pdo, 'agents', 'lastname') ? "COALESCE(a.lastname,'')" : "''";
    $agentDisplayParts[] = "NULLIF(TRIM(CONCAT({$firstExpr}, ' ', {$lastExpr})), '')";
}
if (proforma_column_exists($pdo, 'agents', 'email')) {
    $agentDisplayParts[] = "NULLIF(TRIM(a.email), '')";
}
$agentDisplayParts[] = "CONCAT('Agent #', a.id)";
$agentDisplayExpr = 'COALESCE(' . implode(', ', $agentDisplayParts) . ')';
$addressExpr = proforma_column_exists($pdo, 'agents', 'address') ? "COALESCE(a.address,'')" : "''";
$vatExpr = proforma_column_exists($pdo, 'agents', 'vatno') ? "COALESCE(a.vatno,'')" : "''";
$tinExpr = proforma_column_exists($pdo, 'agents', 'tinno') ? "COALESCE(a.tinno,'')" : "''";

$payerStmt = $pdo->prepare("SELECT {$agentDisplayExpr} AS payer_name, {$addressExpr} AS payer_address, {$vatExpr} AS payer_vatno, {$tinExpr} AS payer_tinno FROM agents a WHERE a.id = :payer_agent_id LIMIT 1");
$payerStmt->execute([':payer_agent_id' => $payerAgentId]);
$payerAgent = $payerStmt->fetch(PDO::FETCH_ASSOC);
if (!$payerAgent) {
    exit('Selected paying agent was not found');
}

$order['agent_name'] = (string)$payerAgent['payer_name'];
$order['agent_address'] = (string)$payerAgent['payer_address'];
$order['agent_vatno'] = (string)$payerAgent['payer_vatno'];
$order['agent_tinno'] = (string)$payerAgent['payer_tinno'];

$propertyId = (int)($order['property_id'] ?? 0);

/*
   Detect cancelled bookings across the different schemas used by RoomZ.
   Prefer explicit cancellation flags/dates/reasons, then textual status fields,
   and finally resolve numeric status IDs through a status lookup table where
   one is available.
*/
function pf_order_is_cancelled(PDO $pdo, array $order): bool
{
    foreach (['is_cancelled', 'is_canceled', 'cancelled', 'canceled'] as $flagColumn) {
        if (array_key_exists($flagColumn, $order) && (int)$order[$flagColumn] === 1) {
            return true;
        }
    }

    foreach (['cancelled_at', 'canceled_at', 'cancellation_date', 'cancelled_date', 'date_cancelled'] as $dateColumn) {
        if (!empty($order[$dateColumn]) && $order[$dateColumn] !== '0000-00-00' && $order[$dateColumn] !== '0000-00-00 00:00:00') {
            return true;
        }
    }

    foreach (['cancelled_reason', 'canceled_reason', 'cancellation_reason'] as $reasonColumn) {
        if (trim((string)($order[$reasonColumn] ?? '')) !== '') {
            return true;
        }
    }

    foreach (['booking_status', 'status', 'status_name', 'booking_status_name', 'reservation_status'] as $statusColumn) {
        if (!array_key_exists($statusColumn, $order)) continue;
        $statusValue = strtolower(trim((string)$order[$statusColumn]));
        if ($statusValue !== '' && (strpos($statusValue, 'cancel') !== false || strpos($statusValue, 'void') !== false)) {
            return true;
        }
    }

    $statusId = 0;
    foreach (['booking_status_id', 'status_id', 'booking_status', 'status'] as $idColumn) {
        if (isset($order[$idColumn]) && is_numeric($order[$idColumn])) {
            $candidate = (int)$order[$idColumn];
            if ($candidate > 0) {
                $statusId = $candidate;
                break;
            }
        }
    }

    if ($statusId > 0) {
        foreach (['booking_statuses', 'booking_status', 'order_statuses', 'reservation_statuses', 'statuses'] as $table) {
            if (!proforma_table_exists($pdo, $table)) continue;

            $idColumn = first_existing_column($pdo, $table, ['id', 'ID', 'status_id']);
            $nameColumn = first_existing_column($pdo, $table, ['name', 'title', 'status', 'status_name', 'description']);
            if ($idColumn === '' || $nameColumn === '') continue;

            try {
                $sql = "SELECT `" . str_replace('`', '``', $nameColumn) . "` FROM `" . str_replace('`', '``', $table) . "` WHERE `" . str_replace('`', '``', $idColumn) . "` = :id LIMIT 1";
                $statusStmt = $pdo->prepare($sql);
                $statusStmt->execute([':id' => $statusId]);
                $statusName = strtolower(trim((string)($statusStmt->fetchColumn() ?: '')));
                if ($statusName !== '' && (strpos($statusName, 'cancel') !== false || strpos($statusName, 'void') !== false)) {
                    return true;
                }
            } catch (Throwable $e) {
                // Try the next possible status table.
            }
        }
    }

    return false;
}

$isCancelledBooking = false;

$guestName = trim(($order['firstname'] ?? '') . ' ' . ($order['lastname'] ?? ''));
if ($guestName === '') $guestName = 'Guest';

$agentName = trim((string)($order['agent_name'] ?? ''));
if ($agentName === '') $agentName = 'Direct';

/*
   Customer billing details for the CLIENT / PROFORMA TO panel.
   Prefer the selected agent/customer record. For direct/company bookings,
   fall back to common company-invoice fields already present on orders.
*/
$clientAddress = trim((string)($order['agent_address'] ?? ''));
if ($clientAddress === '') {
    foreach (['CompanyAddress', 'company_address', 'invoice_address', 'billing_address'] as $clientAddressKey) {
        $candidate = trim((string)($order[$clientAddressKey] ?? ''));
        if ($candidate !== '') {
            $clientAddress = $candidate;
            break;
        }
    }
}
if ($clientAddress === '') {
    $clientAddress = '';
}
$clientAddress = preg_replace('/\s*[\r\n]+\s*/', ', ', $clientAddress);
$clientAddress = preg_replace('/\s{2,}/', ' ', $clientAddress);

$clientVatNo = trim((string)($order['agent_vatno'] ?? ''));
if ($clientVatNo === '') {
    foreach (['VATNo', 'vat_no', 'vatno', 'company_vat_no'] as $clientVatKey) {
        $candidate = trim((string)($order[$clientVatKey] ?? ''));
        if ($candidate !== '') {
            $clientVatNo = $candidate;
            break;
        }
    }
}

$clientTinNo = trim((string)($order['agent_tinno'] ?? ''));
if ($clientTinNo === '') {
    foreach (['TINNo', 'tin_no', 'tinno', 'BPNo', 'bp_no', 'company_tin_no'] as $clientTinKey) {
        $candidate = trim((string)($order[$clientTinKey] ?? ''));
        if ($candidate !== '') {
            $clientTinNo = $candidate;
            break;
        }
    }
}

$currencyCode = trim((string)($order['property_currency_code'] ?? 'USD'));
if ($currencyCode === '') {
    $currencyCode = 'USD';
}

$currency  = $currencyCode;
$symbolMap = [
    'USD' => '$',
    'EUR' => '€',
    'GBP' => '£',
    'ZAR' => 'R',
    'ZWG' => 'ZWG ',
    'ZWL' => 'ZWL ',
    'BWP' => 'P',
];
$symbol = $symbolMap[$currencyCode] ?? ($currencyCode . ' ');

$roomMix = build_room_mix_for_order(
    $pdo,
    $orderId,
    (string)($order['room_type'] ?? ''),
    (int)($order['totalrooms'] ?? 1)
);
$roomMixSummary = (string)($roomMix['summary'] ?? '');
$roomMixRows = $roomMix['rows'] ?? [];

$allocatedRooms = build_allocated_rooms_for_order($pdo, $orderId);
$allocatedRoomSummary = (string)($allocatedRooms['summary'] ?? 'Unassigned');
$allocatedRoomRows = $allocatedRooms['rows'] ?? [];

/* -----------------------------------------------------------------
   Group bookings jumble the header when every room number is listed
   individually (e.g. "1 (Double Room), 2 (Double Room), ...").
   For anything above the threshold, collapse the header display to
   room types and quantities ("Double Room x 11") using the room mix.
   The full per-room allocation is still available lower down / in the
   detail table for small bookings.
------------------------------------------------------------------ */
$GROUP_ROOM_THRESHOLD = 3; // bookings with more than this many rooms are treated as groups
$allocatedRoomCount = count($allocatedRoomRows);
$isGroupBooking = $allocatedRoomCount > $GROUP_ROOM_THRESHOLD;

// What the header cells should show for "Room(s)".
$headerRoomSummary = $allocatedRoomCount . ' rooms';

/* -----------------------------------------------------------------
   Real pax totals. The orders header columns (totalrooms/adults/kids)
   are unreliable for group bookings imported via bulk/channel paths,
   so derive rooms + adults + children from the rooming rows instead.
------------------------------------------------------------------ */
$paxRooms = 0;
$paxAdults = 0;
$paxChildren = 0;
foreach ($roomMixRows as $mixRow) {
    $paxRooms    += (int)($mixRow['qty_rooms'] ?? 0);
    $paxAdults   += (int)($mixRow['adults'] ?? 0);
    $paxChildren += (int)($mixRow['children'] ?? 0);
}
if ($paxRooms <= 0) {
    $paxRooms = (int)($order['totalrooms'] ?? 1);
}
if ($paxAdults <= 0 && $paxChildren <= 0) {
    $paxAdults   = (int)($order['adults'] ?? 0);
    $paxChildren = (int)($order['kids'] ?? 0);
}

$kids03Count = (int)($order['kids_0_3_count'] ?? 0);
$reservationNotes = trim((string)($order['reservation_notes'] ?? ''));
$reservationNotes = preg_replace('/\s+/', ' ', $reservationNotes);

$otherPayerJoinSql = other_agent_payer_join_sql($pdo, 'ft', 'beap');
$otherPayerFilterSql = other_agent_payer_filter_sql($pdo, 'beap');
if ($otherPayerJoinSql === '') {
    exit('Other payer mapping table is not available');
}
$otherPayerParams = [
    ':order_id' => $orderId,
    ':payer_agent_id' => $payerAgentId,
    ':payer_agent_id_modern' => $payerAgentId,
];

/* -----------------------------
   Booking folio totals
----------------------------- */
/*
   Proforma totals must be calculated from live folio_transactions,
   not stale folio totals.

   Important:
   - Voided payments must NOT increase charges.
   - Original voided payments must NOT count as payments.
   - Separate reversal/void rows must reduce payments, not add invoice value.
*/
$stmt = $pdo->prepare("
    SELECT
        COALESCE(SUM(
            CASE
                WHEN COALESCE(ft.is_voided, 0) = 0
                     AND COALESCE(ft.debit, 0) > 0
                     AND LOWER(COALESCE(ft.tx_type, '')) NOT IN (
                        'payment_void','void_payment','payment_reversal',
                        'reversal','refund','payment_refund'
                     )
                     AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%void%'
                     AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%reversal%'
                     AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%refund%'
                THEN COALESCE(ft.debit, 0)
                ELSE 0
            END
        ), 0) AS total_charges,

        /*
           Valid master-folio payments received. Voided, reversed and refunded
           entries are excluded completely.
        */
        COALESCE(SUM(
            CASE
                WHEN (
                        LOWER(COALESCE(ft.tx_type, '')) IN (
                            'payment','receipt','deposit','payment_received',
                            'folio_payment','guest_payment'
                        )
                        OR LOWER(COALESCE(ft.source_table, '')) IN (
                            'payments','payment','receipts','receipt'
                        )
                     )
                     AND COALESCE(ft.is_voided, 0) = 0
                     AND COALESCE(ft.credit, 0) > 0
                     AND LOWER(COALESCE(ft.tx_type, '')) NOT IN (
                        'payment_void','void_payment','payment_reversal',
                        'reversal','refund','payment_refund'
                     )
                     AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%void%'
                     AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%reversal%'
                     AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%refund%'
                THEN COALESCE(ft.credit, 0)
                ELSE 0
            END
        ), 0) AS gross_payments,

        /*
           Voided/reversed payments:
           Supports original payment row marked is_voided=1 and separate reversal rows.
        */
        COALESCE(SUM(
            CASE
                WHEN COALESCE(ft.is_voided, 0) = 1
                     AND COALESCE(ft.credit, 0) > 0
                THEN COALESCE(ft.credit, 0)

                WHEN COALESCE(ft.is_voided, 0) = 0
                     AND (
                        LOWER(COALESCE(ft.tx_type, '')) IN (
                            'payment_void','void_payment','payment_reversal',
                            'reversal','refund','payment_refund'
                        )
                        OR LOWER(COALESCE(ft.description, '')) LIKE '%void%'
                        OR LOWER(COALESCE(ft.description, '')) LIKE '%reversal%'
                        OR LOWER(COALESCE(ft.description, '')) LIKE '%refund%'
                     )
                     AND COALESCE(ft.debit, 0) > 0
                THEN COALESCE(ft.debit, 0)

                WHEN COALESCE(ft.is_voided, 0) = 0
                     AND (
                        LOWER(COALESCE(ft.tx_type, '')) IN (
                            'payment_void','void_payment','payment_reversal',
                            'reversal','refund','payment_refund'
                        )
                        OR LOWER(COALESCE(ft.description, '')) LIKE '%void%'
                        OR LOWER(COALESCE(ft.description, '')) LIKE '%reversal%'
                        OR LOWER(COALESCE(ft.description, '')) LIKE '%refund%'
                     )
                     AND COALESCE(ft.credit, 0) < 0
                THEN ABS(COALESCE(ft.credit, 0))

                ELSE 0
            END
        ), 0) AS voided_payments
    FROM folio_transactions ft
    INNER JOIN folios f ON f.id = ft.folio_id
    {$otherPayerJoinSql}
    WHERE ft.order_id = :order_id
      AND {$otherPayerFilterSql}
");
$stmt->execute($otherPayerParams);
$folioTotals = $stmt->fetch(PDO::FETCH_ASSOC);

$totalCharges   = (float)($folioTotals['total_charges'] ?? 0);
$grossPayments  = (float)($folioTotals['gross_payments'] ?? 0);
$voidedPayments = 0.0; // Voided/reversed items are excluded entirely from this proforma.

/* Only valid, non-voided master-folio payments affect the balance. */
$totalPayments = $grossPayments;
$netPayments   = max(0, $grossPayments);
$totalBalance  = max(0, $totalCharges - $netPayments);

/* 
   Accommodation total:
   Proforma must always include accommodation, even if folio_transactions
   currently only contain extras. Prefer nightly prices because they include
   any nightly/component overrides. Fallback to orders.totalamount.
*/
$stmt = $pdo->prepare("
    SELECT COALESCE(SUM(total), 0)
    FROM rel_orders_prices
    WHERE order_id = :order_id
");
$stmt->execute([':order_id' => $orderId]);
$accommodationTotal = 0.0;

$bookingTotal = (float)($order['totalamount'] ?? 0);
/* Extras-only proforma: accommodation is deliberately excluded. */

$folioChargeTotal = $totalCharges;
$hasAccommodationPosted = false;

/* -----------------------------
   Representative folio
   (for displaying a folio context only)
----------------------------- */
$stmt = $pdo->prepare("
    SELECT f.*
    FROM folios f
    INNER JOIN folio_transactions ft ON ft.folio_id = f.id
    {$otherPayerJoinSql}
    WHERE ft.order_id = :order_id
      AND {$otherPayerFilterSql}
    ORDER BY f.id ASC
    LIMIT 1
");
$stmt->execute($otherPayerParams);
$displayFolio = $stmt->fetch(PDO::FETCH_ASSOC);

$displayFolioNo = (string)($displayFolio['folio_no'] ?? '');
$displayFolioName = trim((string)($displayFolio['folio_name'] ?? 'Booking Folios'));
if ($displayFolioName === '') {
    $displayFolioName = 'Booking Folios';
}

/* -----------------------------
   Company profile
   Source: lodge_settings first, then old tblmailinglist fallback.
   Layout is unchanged below; only the data source is changed.
----------------------------- */
$company = [
    'CompanyName'     => defined('APP_NAME') ? APP_NAME : 'RoomZ Lodge',
    'LegalName'       => '',
    'Postal'          => '',
    'Street'          => '',
    'City'            => '',
    'Email'           => '',
    'Phone'           => '',
    'AltPhone'        => '',
    'Website'         => '',
    'VATNO'           => '',
    'TIN'             => '',
    'CompanyRegNo'    => '',
    'Motto'           => 'Reservations • Folios • Payments',
    'ProformaPrefix'  => 'PF',
    'LogoPath'        => '',
    'PaymentTerms'    => '',
    'Terms'           => '',
    'InvoiceFooter'   => '',
];

$lodgeSettings = false;

try {
    if (proforma_table_exists($pdo, 'lodge_settings')) {
        $stmtLodge = $pdo->query("SELECT * FROM lodge_settings ORDER BY id ASC LIMIT 1");
        $lodgeSettings = $stmtLodge ? $stmtLodge->fetch(PDO::FETCH_ASSOC) : false;
    }
} catch (Throwable $e) {
    $lodgeSettings = false;
}

if ($lodgeSettings) {
    $cityParts = [];
    foreach (['city', 'state_region', 'country'] as $partCol) {
        $part = trim((string)($lodgeSettings[$partCol] ?? ''));
        if ($part !== '') {
            $cityParts[] = $part;
        }
    }

    $postalParts = [];
    foreach (['address_line2', 'postal_code'] as $partCol) {
        $part = trim((string)($lodgeSettings[$partCol] ?? ''));
        if ($part !== '') {
            $postalParts[] = $part;
        }
    }

    $phoneParts = [];
    foreach (['phone', 'alt_phone'] as $phoneCol) {
        $phone = trim((string)($lodgeSettings[$phoneCol] ?? ''));
        if ($phone !== '') {
            $phoneParts[] = $phone;
        }
    }

    $company['CompanyName']    = trim((string)($lodgeSettings['lodge_name'] ?? '')) ?: $company['CompanyName'];
    $company['LegalName']      = trim((string)($lodgeSettings['legal_name'] ?? ''));
    $company['Street']         = trim((string)($lodgeSettings['address_line1'] ?? ''));
    $company['Postal']         = implode(', ', $postalParts);
    $company['City']           = implode(', ', $cityParts);
    $company['Email']          = trim((string)($lodgeSettings['email'] ?? ''));
    $company['Phone']          = implode(' / ', $phoneParts);
    $company['AltPhone']       = trim((string)($lodgeSettings['alt_phone'] ?? ''));
    $company['Website']        = trim((string)($lodgeSettings['website'] ?? ''));
    $company['VATNO']          = trim((string)($lodgeSettings['vat_number'] ?? ''));
    $company['TIN']            = trim((string)($lodgeSettings['tin_number'] ?? ''));
    $company['CompanyRegNo']   = trim((string)($lodgeSettings['company_reg_number'] ?? ''));
    $company['Motto']          = trim((string)($lodgeSettings['tag_line'] ?? '')) ?: $company['Motto'];
    $company['LogoPath']       = trim((string)($lodgeSettings['logo_path'] ?? ''));
    $company['PaymentTerms']   = trim((string)($lodgeSettings['payment_terms'] ?? ''));
    $company['Terms']          = trim((string)($lodgeSettings['terms_and_conditions'] ?? ''));
    $company['InvoiceFooter']  = trim((string)($lodgeSettings['invoice_footer'] ?? ''));
} else {
    try {
        $address = false;

        try {
            $stmtCompany = $pdo->prepare("
                SELECT *
                FROM tblmailinglist
                WHERE property_id = :property_id
                ORDER BY ID DESC
                LIMIT 1
            ");
            $stmtCompany->execute([':property_id' => $propertyId]);
            $address = $stmtCompany->fetch(PDO::FETCH_ASSOC);
        } catch (Throwable $e) {
            $address = false;
        }

        if (!$address) {
            try {
                $q = $pdo->query("SELECT * FROM tblmailinglist WHERE ID = 2 LIMIT 1");
                $address = $q ? $q->fetch(PDO::FETCH_ASSOC) : false;
            } catch (Throwable $e) {
                $address = false;
            }
        }

        if ($address) {
            $company['CompanyName']    = $address['CompanyName'] ?: $company['CompanyName'];
            $company['Postal']         = $address['Postal'] ?? '';
            $company['Street']         = $address['Street'] ?? '';
            $company['City']           = $address['City'] ?? '';
            $company['Email']          = $address['Email'] ?? '';
            $company['Phone']          = $address['Phone'] ?? '';
            $company['Website']        = $address['Website'] ?? '';
            $company['VATNO']          = $address['VATNO'] ?? '';
            $company['TIN']            = $address['TIN'] ?? '';
            $company['Motto']          = $address['Motto'] ?: $company['Motto'];
            $company['ProformaPrefix'] = $address['ProformaPrefix'] ?: $company['ProformaPrefix'];
        }
    } catch (Throwable $e) {
        // keep defaults
    }
}

$propertyDisplay = trim((string)($order['property_name'] ?? ''));
if ($propertyDisplay !== '' && stripos($company['CompanyName'], $propertyDisplay) === false) {
    $company['Motto'] = $company['Motto'];
}

/* -----------------------------
   Charge lines across booking
----------------------------- */
$hasExtraChargeItems = proforma_table_exists($pdo, 'extra_charge_items');

$extraItemJoinSql = $hasExtraChargeItems ? "
    LEFT JOIN (
        SELECT
            folio_transaction_id,
            GROUP_CONCAT(
                CONCAT(
                    COALESCE(NULLIF(item_name,''), 'Item'),
                    CASE WHEN COALESCE(item_code,'') <> '' THEN CONCAT(' [', item_code, ']') ELSE '' END,
                    ' x ', TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM FORMAT(COALESCE(qty,0), 4))),
                    ' @ ', FORMAT(COALESCE(unit_price,0), 2)
                )
                ORDER BY id ASC SEPARATOR '\n'
            ) AS linked_item_description,
            GROUP_CONCAT(DISTINCT NULLIF(category_name,'') ORDER BY category_name SEPARATOR ', ') AS linked_category_name,
            SUM(COALESCE(line_total,0)) AS linked_line_total
        FROM extra_charge_items
        GROUP BY folio_transaction_id
    ) eci ON eci.folio_transaction_id = ft.id
" : "";

$extraItemSelectSql = $hasExtraChargeItems ? "
        eci.linked_item_description,
        eci.linked_category_name,
        eci.linked_line_total,
" : "
        NULL AS linked_item_description,
        NULL AS linked_category_name,
        NULL AS linked_line_total,
";

/*
   Only trust the linked extra_charge_items description when those items
   RECONCILE to the transaction they claim to describe.

   Bug this fixes: a transfer of 189.00 printed as "Ice cream x 1 @ 3.00",
   and a phantom "Imported Extras 19.00" appeared in the charge breakdown.
   Both came from stale extra_charge_items rows still attached to a
   folio_transaction whose amount had since changed. The old rule was
   "if a linked description exists, print it" - so orphaned itemisation
   silently replaced the real charge on a customer-facing invoice while the
   amount stayed at 189.00, producing a document that contradicted itself.

   The itemisation is only a faithful description of the charge if the items
   sum to the charge. When they don't, the transaction's own description is
   the truth and the linked rows are ignored.
*/
$printableDescriptionSql = $hasExtraChargeItems ? "
        CASE
            WHEN COALESCE(eci.linked_item_description, '') <> ''
             AND ABS(COALESCE(eci.linked_line_total, 0) - COALESCE(ft.debit, 0)) < 0.01
                THEN eci.linked_item_description
            ELSE ft.description
        END AS printable_description
" : "
        ft.description AS printable_description
";

$stmt = $pdo->prepare("
    SELECT
        ft.*, 
        f.folio_name,
        f.folio_type,
        r.room_no,
        {$extraItemSelectSql}
        {$printableDescriptionSql}
    FROM folio_transactions ft
    INNER JOIN folios f ON f.id = ft.folio_id
    {$otherPayerJoinSql}
    LEFT JOIN rooms r ON r.id = f.room_id
    {$extraItemJoinSql}
    WHERE ft.order_id = :order_id
      AND {$otherPayerFilterSql}
      AND COALESCE(ft.is_voided, 0) = 0
      AND ft.debit > 0
      AND LOWER(COALESCE(ft.tx_type, '')) NOT IN (
            'payment_void','void_payment','payment_reversal',
            'reversal','refund','payment_refund'
      )
      AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%void%'
      AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%reversal%'
      AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%refund%'
    ORDER BY ft.tx_date ASC, ft.id ASC
");
$stmt->execute($otherPayerParams);
$lines = $stmt->fetchAll(PDO::FETCH_ASSOC);

/*
   Charge breakdown for the totals panel.

   Two bugs fixed here:

   1. DOUBLE COUNTING. This joined extra_charge_items row-by-row, so a single
      transaction itemised into 3 lines was multiplied into 3 rows and its
      ft.debit fallback counted 3 times. The items are now pre-aggregated to one
      row per transaction before the join.

   2. PHANTOM GROUPS. Stale itemisation (items whose totals no longer match the
      transaction) was silently believed, producing entries like
      "Imported Extras 19.00" against a 189.00 transfer. Linked items are now
      only trusted when they reconcile to the transaction amount; otherwise the
      transaction's own tx_type and debit are used.
*/
$chargeBreakdownRows = [];
try {
    if ($hasExtraChargeItems) {
        $stmtBreakdown = $pdo->prepare("
            SELECT
                CASE
                    WHEN COALESCE(agg.category_name,'') <> ''
                     AND ABS(COALESCE(agg.items_total,0) - COALESCE(ft.debit,0)) < 0.01
                        THEN agg.category_name
                    WHEN COALESCE(ft.tx_type,'') <> '' THEN REPLACE(ft.tx_type, '_', ' ')
                    ELSE 'Other Charges'
                END AS charge_group,
                SUM(COALESCE(ft.debit, 0) - COALESCE(ft.credit, 0)) AS amount
            FROM folio_transactions ft
            INNER JOIN folios f ON f.id = ft.folio_id
            {$otherPayerJoinSql}
            LEFT JOIN (
                SELECT
                    folio_transaction_id,
                    SUM(COALESCE(line_total,0)) AS items_total,
                    GROUP_CONCAT(DISTINCT NULLIF(category_name,'')) AS category_name
                FROM extra_charge_items
                GROUP BY folio_transaction_id
            ) agg ON agg.folio_transaction_id = ft.id
            WHERE ft.order_id = :order_id
              AND {$otherPayerFilterSql}
              AND COALESCE(ft.is_voided, 0) = 0
              AND ft.debit > 0
              AND LOWER(COALESCE(ft.tx_type, '')) NOT IN (
                    'payment_void','void_payment','payment_reversal',
                    'reversal','refund','payment_refund'
              )
              AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%void%'
              AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%reversal%'
              AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%refund%'
            GROUP BY charge_group
            HAVING amount <> 0
            ORDER BY charge_group
        ");
    } else {
        $stmtBreakdown = $pdo->prepare("
            SELECT
                CASE
                    WHEN COALESCE(ft.tx_type,'') <> '' THEN REPLACE(ft.tx_type, '_', ' ')
                    ELSE 'Other Charges'
                END AS charge_group,
                SUM(COALESCE(ft.debit, 0) - COALESCE(ft.credit, 0)) AS amount
            FROM folio_transactions ft
            INNER JOIN folios f ON f.id = ft.folio_id
            {$otherPayerJoinSql}
            WHERE ft.order_id = :order_id
              AND {$otherPayerFilterSql}
              AND COALESCE(ft.is_voided, 0) = 0
              AND ft.debit > 0
              AND LOWER(COALESCE(ft.tx_type, '')) NOT IN (
                    'payment_void','void_payment','payment_reversal',
                    'reversal','refund','payment_refund'
              )
              AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%void%'
              AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%reversal%'
              AND LOWER(COALESCE(ft.description, '')) NOT LIKE '%refund%'
            GROUP BY charge_group
            HAVING amount <> 0
            ORDER BY charge_group
        ");
    }
    $stmtBreakdown->execute($otherPayerParams);
    $chargeBreakdownRows = $stmtBreakdown->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {
    $chargeBreakdownRows = [];
}

/*
   Strict master-folio mode:
   Do not create invoice charges from rel_orders_prices. Only actual, non-voided
   transactions posted to the master folio may appear on this proforma.
*/
$nightlyLines = [];

// Separate existing folio lines into accommodation vs the rest (extras/other).
$existingAccomLines = [];
$nonAccomLines = [];
foreach ($lines as $line) {
    $txType = strtolower(trim((string)($line['tx_type'] ?? '')));
    $sourceTable = strtolower(trim((string)($line['source_table'] ?? '')));
    $desc = strtolower(trim((string)($line['description'] ?? '')));

    $isExtra = in_array($txType, ['extra', 'extras', 'extra_charge', 'addon', 'add_on', 'transfer', 'restaurant', 'bar', 'spar', 'spa', 'curio', 'minibar', 'laundry', 'park_fee'], true)
        || in_array($sourceTable, ['tblservices', 'restaurant_menu_items', 'inventory_items', 'extra_charge_items', 'extras', 'order_extras', 'booking_extras', 'reservation_extras', 'order_extra_items', 'manual_extra'], true);

    $isAccommodation = !$isExtra && (
        in_array($txType, ['accommodation', 'room', 'room_charge', 'accommodation_charge', 'nightly', 'room_night'], true)
        || $sourceTable === 'rel_orders_prices'
        || strpos($desc, 'accommodation') !== false
        || strpos($desc, 'room charge') !== false
    );

    if ($isAccommodation) {
        $existingAccomLines[] = $line;
    } else {
        $nonAccomLines[] = $line;
    }
}

$hasAccommodationPosted = !empty($existingAccomLines);

/*
   Add room-level pax to accommodation transactions that already exist in the
   master folio. The previous pax change only affected pf_accom_line(), but this
   proforma runs in strict master-folio mode with $nightlyLines = [], so those
   generated descriptions are never used. These are the actual rows printed.
*/
if ($hasAccommodationPosted) {
    $roomPaxRows = array_values($allocatedRoomRows);
    $roomPaxCount = count($roomPaxRows);
    $accomIndex = 0;

    foreach ($lines as &$printedLine) {
        $printedTxType = strtolower(trim((string)($printedLine['tx_type'] ?? '')));
        $printedSource = strtolower(trim((string)($printedLine['source_table'] ?? '')));
        $printedDesc   = strtolower(trim((string)($printedLine['description'] ?? '')));

        $printedIsExtra = in_array($printedTxType, ['extra', 'extras', 'extra_charge', 'addon', 'add_on', 'transfer', 'restaurant', 'bar', 'spar', 'spa', 'curio', 'minibar', 'laundry', 'park_fee'], true)
            || in_array($printedSource, ['tblservices', 'restaurant_menu_items', 'inventory_items', 'extra_charge_items', 'extras', 'order_extras', 'booking_extras', 'reservation_extras', 'order_extra_items', 'manual_extra'], true);

        $printedIsAccommodation = !$printedIsExtra && (
            in_array($printedTxType, ['accommodation', 'room', 'room_charge', 'accommodation_charge', 'nightly', 'room_night'], true)
            || $printedSource === 'rel_orders_prices'
            || strpos($printedDesc, 'accommodation') !== false
            || strpos($printedDesc, 'room charge') !== false
        );

        if (!$printedIsAccommodation) {
            continue;
        }

        $adults = 0;
        $children = 0;

        if ($roomPaxCount > 0) {
            $roomPax = $roomPaxRows[$accomIndex % $roomPaxCount];
            $adults = (int)($roomPax['adults'] ?? 0);
            $children = (int)($roomPax['children'] ?? 0);
        }

        $totalPax = $adults + $children;
        if ($totalPax > 0) {
            $paxParts = [];
            if ($adults > 0) {
                $paxParts[] = $adults . 'A';
            }
            if ($children > 0) {
                $paxParts[] = $children . 'C';
            }

            $paxSuffix = ' | Pax: ' . $totalPax . ' (' . implode(' ', $paxParts) . ')';

            $baseDescription = trim((string)($printedLine['description'] ?? 'Accommodation'));
            if (stripos($baseDescription, 'Pax:') === false) {
                $printedLine['description'] = $baseDescription . $paxSuffix;
            }

            $basePrintable = trim((string)($printedLine['printable_description'] ?? $printedLine['description']));
            if (stripos($basePrintable, 'Pax:') === false) {
                $printedLine['printable_description'] = $basePrintable . $paxSuffix;
            }
        }

        $accomIndex++;
    }
    unset($printedLine);
}

/*
   Cancelled bookings must show ONLY explicitly posted cancellation-fee
   transactions. Never relabel the full accommodation value as a cancellation
   fee, because the retained fee may be only 20%, 50%, or another partial
   amount.

   A transaction qualifies when its type, source, or description clearly says
   cancellation/cancel fee. Original accommodation rows are hidden from the
   cancelled-booking proforma.

   If no explicit cancellation-fee transaction exists, the document shows no
   charge and clearly states that no cancellation fee has been posted.
*/
$cancellationFeeTotal = 0.0;
$cancellationFeeMissing = false;

if ($isCancelledBooking) {
    $cancellationDate = '';
    foreach (['cancelled_at', 'canceled_at', 'cancellation_date', 'cancelled_date', 'date_cancelled'] as $cancelDateColumn) {
        if (!empty($order[$cancelDateColumn])) {
            $cancellationDate = (string)$order[$cancelDateColumn];
            break;
        }
    }

    $cancellationReason = '';
    foreach (['cancelled_reason', 'canceled_reason', 'cancellation_reason'] as $reasonColumn) {
        $candidateReason = trim((string)($order[$reasonColumn] ?? ''));
        if ($candidateReason !== '') {
            $cancellationReason = $candidateReason;
            break;
        }
    }

    $cancellationLines = [];

    foreach ($lines as $candidateLine) {
        $txType = strtolower(trim((string)($candidateLine['tx_type'] ?? '')));
        $sourceTable = strtolower(trim((string)($candidateLine['source_table'] ?? '')));
        $description = strtolower(trim((string)($candidateLine['description'] ?? '')));
        $printableDescription = strtolower(trim((string)($candidateLine['printable_description'] ?? '')));

        $isCancellationFee =
            in_array($txType, [
                'cancellation_fee',
                'cancellation',
                'cancel_fee',
                'cancellation_charge',
                'cancel_charge',
                'late_cancellation_fee'
            ], true)
            || in_array($sourceTable, [
                'cancellation_fees',
                'cancellation_fee',
                'booking_cancellation_fees',
                'cancel_fees'
            ], true)
            || strpos($description, 'cancellation fee') !== false
            || strpos($description, 'cancel fee') !== false
            || strpos($description, 'cancellation charge') !== false
            || strpos($description, 'cancel charge') !== false
            || strpos($printableDescription, 'cancellation fee') !== false
            || strpos($printableDescription, 'cancel fee') !== false
            || strpos($printableDescription, 'cancellation charge') !== false
            || strpos($printableDescription, 'cancel charge') !== false;

        if (!$isCancellationFee) {
            continue;
        }

        $lineAmount = max(0, (float)($candidateLine['debit'] ?? 0) - max(0, (float)($candidateLine['credit'] ?? 0)));
        if ($lineAmount <= 0) {
            continue;
        }

        $candidateLine['description'] = trim((string)($candidateLine['description'] ?? '')) ?: 'Cancellation Fee';
        $candidateLine['printable_description'] = trim((string)($candidateLine['printable_description'] ?? ''))
            ?: $candidateLine['description'];
        $candidateLine['debit'] = round($lineAmount, 2);
        $candidateLine['credit'] = 0;
        $candidateLine['tx_type'] = 'cancellation_fee';

        $cancellationLines[] = $candidateLine;
        $cancellationFeeTotal += $lineAmount;

        if ($cancellationDate === '' && !empty($candidateLine['tx_date'])) {
            $cancellationDate = (string)$candidateLine['tx_date'];
        }
    }

    $cancellationFeeTotal = round($cancellationFeeTotal, 2);

    if ($cancellationDate === '') {
        $cancellationDate = date('Y-m-d');
    }

    $lines = $cancellationLines;
    $chargeBreakdownRows = [];

    if ($cancellationFeeTotal > 0) {
        $chargeBreakdownRows[] = [
            'charge_group' => 'Cancellation Fee',
            'amount' => $cancellationFeeTotal,
        ];
    } else {
        $cancellationFeeMissing = true;
    }

    /*
       Recalculate the cancelled proforma strictly from cancellation-fee
       transactions only. Valid payments remain available to reduce the balance.
    */
    $totalCharges = $cancellationFeeTotal;
    $folioChargeTotal = $cancellationFeeTotal;
    $accommodationTotal = 0.0;
    $netPayments = max(0, $totalPayments);
    $totalBalance = max(0, $totalCharges - $netPayments);

    $existingAccomLines = [];
    $nonAccomLines = $lines;
    $hasAccommodationPosted = false;
}

if (!$isCancelledBooking && $hasAccommodationPosted && !empty($nightlyLines)) {
    // Build the detailed per-room-per-night accommodation lines.
    $accLines = [];
    foreach ($nightlyLines as $nl) {
        $accLines[] = [
            'tx_date'      => $nl['date'],
            'description'  => $nl['description'],
            'debit'        => $nl['total'],
            'folio_type'   => 'master',
            'room_no'      => '',
            'folio_name'   => 'Booking Folio',
            'tx_type'      => 'accommodation',
            'source_table' => 'rel_orders_prices'
        ];
    }

    // Replace any existing accommodation lines with the detailed breakdown,
    // keeping all non-accommodation (extras) lines after it.
    $lines = array_merge($accLines, $nonAccomLines);

    // Keep the accommodation total consistent with the sum of the nightly lines.
    $nightlySum = 0.0;
    foreach ($nightlyLines as $nl) {
        $nightlySum += (float)$nl['total'];
    }
    if ($nightlySum > 0) {
        $accommodationTotal = round($nightlySum, 2);
    }
    // From here, accommodation is represented by our own lines (not a prior post).
    $hasAccommodationPosted = false;

    /*
       Recompute the folio (extras) total as the sum of the NON-accommodation
       lines only. The original $folioChargeTotal included any posted
       accommodation; since we've stripped those and replaced them with our
       nightly breakdown, counting extras-only prevents double-counting the
       accommodation in the grand total.
    */
    $folioChargeTotal = 0.0;
    foreach ($nonAccomLines as $nl2) {
        $folioChargeTotal += (float)($nl2['debit'] ?? 0);
    }
    $folioChargeTotal = round($folioChargeTotal, 2);
}

// Totals remain strictly based on non-voided master-folio transactions.
$netPayments = max(0, $totalPayments);
$totalBalance = max(0, $totalCharges - $netPayments);

/* -----------------------------
   Bank details
   Always source up to THREE accounts directly from tblbankdetails.
   The selected ?bank= ID is shown first when it belongs to the property,
   followed by the remaining property bank accounts in ID order.
----------------------------- */
$bankId = is_numeric($bankParam) ? (int)$bankParam : 0;
$bankDetailsList = [];

function pf_bank_value(array $row, array $candidates): string
{
    foreach ($candidates as $candidate) {
        foreach ($row as $key => $value) {
            if (strcasecmp((string)$key, (string)$candidate) === 0) {
                return trim((string)$value);
            }
        }
    }
    return '';
}

function pf_normalise_bank_row(array $row): array
{
    return [
        'ID'          => (int)pf_bank_value($row, ['ID', 'id']),
        'Title'       => pf_bank_value($row, ['Title', 'AccountTitle', 'BankTitle', 'Description', 'Name']),
        'SwiftCode'   => pf_bank_value($row, ['SwiftCode', 'Swift', 'SWIFT', 'BIC']),
        'BranchCode'  => pf_bank_value($row, ['BranchCode', 'SortCode', 'SortBranchCode', 'RoutingCode']),
        'Branch'      => pf_bank_value($row, ['Branch', 'BankBranch', 'BranchName']),
        'Bank'        => pf_bank_value($row, ['Bank', 'BankName']),
        'AccountNo'   => pf_bank_value($row, ['AccountNo', 'AccountNumber', 'BankAccountNo']),
        'AccountName' => pf_bank_value($row, ['AccountName', 'AccountHolder', 'BankAccountName']),
        'Currency'    => pf_bank_value($row, ['Currency', 'CurrencyCode', 'AccountCurrency']),
        'Address'     => pf_bank_value($row, ['Address', 'BankAddress', 'Details', 'Detail']),
    ];
}

try {
    if (proforma_table_exists($pdo, 'tblbankdetails')) {
        $rawBankRows = [];
        $hasBankProperty = proforma_column_exists($pdo, 'tblbankdetails', 'property_id');

        if ($hasBankProperty && $propertyId > 0) {
            $stmtBanks = $pdo->prepare("
                SELECT *
                FROM tblbankdetails
                WHERE property_id = :property_id
                ORDER BY CASE WHEN ID = :selected_id THEN 0 ELSE 1 END, ID ASC
                LIMIT 3
            ");
            $stmtBanks->execute([
                ':property_id' => $propertyId,
                ':selected_id' => $bankId,
            ]);
            $rawBankRows = $stmtBanks->fetchAll(PDO::FETCH_ASSOC) ?: [];
        }

        /* Older installations may not have property_id or may not yet have
           property-specific rows. In that case use the first three records,
           still placing the requested bank ID first where possible. */
        if (!$rawBankRows) {
            $stmtBanks = $pdo->prepare("
                SELECT *
                FROM tblbankdetails
                ORDER BY CASE WHEN ID = :selected_id THEN 0 ELSE 1 END, ID ASC
                LIMIT 3
            ");
            $stmtBanks->execute([':selected_id' => $bankId]);
            $rawBankRows = $stmtBanks->fetchAll(PDO::FETCH_ASSOC) ?: [];
        }

        foreach ($rawBankRows as $rawBankRow) {
            $normalisedBank = pf_normalise_bank_row($rawBankRow);
            if ($normalisedBank['Bank'] !== '' || $normalisedBank['AccountNo'] !== '' || $normalisedBank['AccountName'] !== '') {
                $bankDetailsList[] = $normalisedBank;
            }
        }
    }
} catch (Throwable $e) {
    error_log('Proforma bank details load failed: ' . $e->getMessage());
    $bankDetailsList = [];
}

/* -----------------------------
   PDF - Kaufeld-style layout structure
   Layout only: top document bar, company hero, 3 summary cards,
   item table, bank/totals grid, notes/footer.
----------------------------- */
class PDF extends FPDF
{
    public $company = [];
    public $docTitle = 'PROFORMA INVOICE';
    public $docRef = '';

    function RoundedRect($x, $y, $w, $h, $r, $style = '')
    {
        $k = $this->k;
        $hp = $this->h;
        if ($style == 'F') {
            $op = 'f';
        } elseif ($style == 'FD' || $style == 'DF') {
            $op = 'B';
        } else {
            $op = 'S';
        }
        $MyArc = 4 / 3 * (sqrt(2) - 1);
        $this->_out(sprintf('%.2F %.2F m', ($x + $r) * $k, ($hp - $y) * $k));
        $xc = $x + $w - $r;
        $yc = $y + $r;
        $this->_out(sprintf('%.2F %.2F l', $xc * $k, ($hp - $y) * $k));
        $this->_Arc($xc + $r * $MyArc, $yc - $r, $xc + $r, $yc - $r * $MyArc, $xc + $r, $yc);
        $xc = $x + $w - $r;
        $yc = $y + $h - $r;
        $this->_out(sprintf('%.2F %.2F l', ($x + $w) * $k, ($hp - $yc) * $k));
        $this->_Arc($xc + $r, $yc + $r * $MyArc, $xc + $r * $MyArc, $yc + $r, $xc, $yc + $r);
        $xc = $x + $r;
        $yc = $y + $h - $r;
        $this->_out(sprintf('%.2F %.2F l', $xc * $k, ($hp - ($y + $h)) * $k));
        $this->_Arc($xc - $r * $MyArc, $yc + $r, $xc - $r, $yc + $r * $MyArc, $xc - $r, $yc);
        $xc = $x + $r;
        $yc = $y + $r;
        $this->_out(sprintf('%.2F %.2F l', $x * $k, ($hp - $yc) * $k));
        $this->_Arc($xc - $r, $yc - $r * $MyArc, $xc - $r * $MyArc, $yc - $r, $xc, $yc - $r);
        $this->_out($op);
    }

    function _Arc($x1, $y1, $x2, $y2, $x3, $y3)
    {
        $h = $this->h;
        $this->_out(sprintf('%.2F %.2F %.2F %.2F %.2F %.2F c ',
            $x1 * $this->k, ($h - $y1) * $this->k,
            $x2 * $this->k, ($h - $y2) * $this->k,
            $x3 * $this->k, ($h - $y3) * $this->k));
    }

    function Header()
    {
        // Intentionally blank. The document header is drawn manually on page 1
        // to keep the layout close to the supplied reference.
    }

    function Footer()
    {
        $this->SetY(-15);
        $this->SetDrawColor(196, 205, 214);
        $this->Line(10, $this->GetY(), 200, $this->GetY());
        $this->SetY(-11);
        $this->SetFont('Arial', '', 7.5);
        $this->SetTextColor(45, 55, 70);
        $this->Cell(95, 5, 'Prepared By: Reservations Department', 0, 0, 'L');
        $this->Cell(55, 5, 'Generated On: ' . date('d-M-Y H:i'), 0, 0, 'C');
        $this->Cell(40, 5, 'Page ' . $this->PageNo() . ' of {nb}', 0, 0, 'R');
    }

    function docTopBar(string $title, string $ref): void
    {
        $this->SetFillColor(36, 50, 66);
        $this->SetDrawColor(36, 50, 66);
        $this->RoundedRect(10, 8, 190, 12, 3, 'F');
        $this->SetTextColor(255, 255, 255);
        $this->SetFont('Arial', 'B', 14);
        $this->SetXY(18, 10.5);
        $this->Cell(90, 6, pdf_txt($title), 0, 0, 'L');
        $this->SetFont('Arial', 'B', 10);
        $this->SetXY(115, 10.8);
        $this->Cell(78, 6, pdf_txt('Ref: ' . $ref), 0, 0, 'R');
    }

    function card($x, $y, $w, $h, $title = '', $icon = ''): void
    {
        $this->SetFillColor(255, 255, 255);
        $this->SetDrawColor(157, 169, 184);
        $this->RoundedRect($x, $y, $w, $h, 3, 'D');
        if ($title !== '') {
            $this->SetFont('Arial', 'B', 8.5);
            $this->SetTextColor(18, 28, 43);
            $this->SetXY($x + 6, $y + 5);
            $this->Cell($w - 12, 5, pdf_txt($title), 0, 0, 'L');
            $this->SetDrawColor(222, 226, 232);
            $this->Line($x, $y + 13, $x + $w, $y + 13);
        }
    }

    function labelValue($x, $y, $label, $value, $w = 60, $labelW = 24, $h = 6): void
    {
        $this->SetXY($x, $y);
        $this->SetFont('Arial', 'B', 8);
        $this->SetTextColor(18, 28, 43);
        $this->Cell($labelW, $h, pdf_txt($label), 0, 0, 'L');
        $this->SetFont('Arial', '', 8);
        $this->Cell($w - $labelW, $h, pdf_txt($value), 0, 0, 'R');
    }

    function addItemsHeader(): void
    {
        $this->SetFillColor(36, 50, 66);
        $this->SetTextColor(255, 255, 255);
        $this->SetFont('Arial', 'B', 8.5);
        $this->SetX(10);
        $this->Cell(9, 8, '#', 0, 0, 'C', true);
        $this->Cell(23, 8, 'DATE', 0, 0, 'L', true);
        $this->Cell(98, 8, 'DESCRIPTION', 0, 0, 'L', true);
        $this->Cell(27, 8, 'FOLIO', 0, 0, 'L', true);
        $this->Cell(33, 8, 'AMOUNT', 0, 1, 'R', true);
    }

    function checkPageSpace(float $needed): void
    {
        if ($this->GetY() + $needed > 266) {
            $this->AddPage();
            $this->SetY(18);
            $this->addItemsHeader();

            /*
             * addItemsHeader() switches the text colour to white for the dark
             * heading band. Restore the normal item-row colour immediately,
             * otherwise every row on continuation pages is printed white on a
             * white background and only appears to be missing.
             */
            $this->SetTextColor(18, 28, 43);
            $this->SetFont('Arial', '', 8);
        }
    }
}

$proformaPrefix = trim((string)($company['ProformaPrefix'] ?? 'PF'));
if ($proformaPrefix === '') {
    $proformaPrefix = 'PF';
}
if (!empty($order['property_code'])) {
    $proformaPrefix .= '-' . strtoupper((string)$order['property_code']);
}
$proformaNo = $proformaPrefix . '-' . $orderId;

$pdf = new PDF();
$pdf->company  = $company;
$pdf->docTitle = 'OTHER PAYER EXTRAS PROFORMA';
$pdf->docRef   = $proformaNo;
$pdf->AliasNbPages();
$pdf->AddPage();
$pdf->SetAutoPageBreak(true, 18);

/* -----------------------------
   Top document bar + company hero
----------------------------- */
$pdf->docTopBar('OTHER PAYER EXTRAS PROFORMA', $proformaNo);

$pdf->SetFillColor(255, 255, 255);
$pdf->SetDrawColor(157, 169, 184);

/*
   Compact company panel:
   reduced from 48mm to 34mm and tightened line spacing so the company
   information remains clear without consuming unnecessary page height.
*/
$pdf->RoundedRect(10, 20, 190, 34, 3, 'D');

$companyName = (string)($company['CompanyName'] ?? 'Company');
$pdf->SetTextColor(18, 28, 43);
$pdf->SetFont('Arial', 'B', 12.5);
$pdf->SetXY(16, 23.5);
$pdf->Cell(105, 5, pdf_txt($companyName), 0, 1, 'L');

$pdf->SetFont('Arial', 'I', 7.5);
$pdf->SetTextColor(72, 82, 96);
$pdf->SetX(16);
$pdf->Cell(105, 3.8, pdf_txt(trim((string)($company['Motto'] ?? 'Reservations | Folios | Payments'))), 0, 1, 'L');

$pdf->SetTextColor(18, 28, 43);
$pdf->SetFont('Arial', '', 7.4);
$pdf->SetX(16);
$pdf->Cell(105, 3.8, pdf_txt(trim((string)($company['Street'] ?? ''))), 0, 1, 'L');
$pdf->SetX(16);
$pdf->Cell(105, 3.8, pdf_txt(trim((string)($company['Postal'] ?? '') . ' ' . (string)($company['City'] ?? ''))), 0, 1, 'L');
$pdf->SetX(16);
$pdf->Cell(105, 3.8, pdf_txt('Email: ' . (string)($company['Email'] ?? '') . '   Tel: ' . (string)($company['Phone'] ?? '')), 0, 1, 'L');

$statParts = [];
if (trim((string)($company['TIN'] ?? '')) !== '') {
    $statParts[] = 'TIN: ' . trim((string)$company['TIN']);
}
if (trim((string)($company['VATNO'] ?? '')) !== '') {
    $statParts[] = 'VAT: ' . trim((string)$company['VATNO']);
}
if (trim((string)($company['CompanyRegNo'] ?? '')) !== '') {
    $statParts[] = 'Reg No: ' . trim((string)$company['CompanyRegNo']);
}
if ($statParts) {
    $pdf->SetX(16);
    $pdf->SetFont('Arial', '', 7.2);
    $pdf->Cell(105, 3.8, pdf_txt(implode('   |   ', $statParts)), 0, 1, 'L');
}

$logo = __DIR__ . '/att.png';
if (!empty($company['LogoPath'])) {
    $configuredLogo = (string)$company['LogoPath'];
    $logoCandidates = [];

    if (preg_match('/^https?:\/\//i', $configuredLogo)) {
        $logoCandidates[] = $configuredLogo;
    } else {
        $logoCandidates[] = $configuredLogo;
        $logoCandidates[] = __DIR__ . '/' . ltrim($configuredLogo, '/');
        $logoCandidates[] = dirname(__DIR__, 2) . '/' . ltrim($configuredLogo, '/');
        $logoCandidates[] = rtrim((string)($_SERVER['DOCUMENT_ROOT'] ?? ''), '/') . '/' . ltrim($configuredLogo, '/');
    }

    foreach ($logoCandidates as $candidateLogo) {
        if (preg_match('/^https?:\/\//i', $candidateLogo) || file_exists($candidateLogo)) {
            $logo = $candidateLogo;
            break;
        }
    }
}
if (file_exists($logo) || preg_match('/^https?:\/\//i', $logo)) {
    $pdf->Image($logo, 140, 25, 46);
}

/* -----------------------------
   Client + wide booking summary cards
   Final polish: remove duplicated "From" card because company details
   already appear in the hero header. Summary gets more width and cleaner rows.
----------------------------- */
$propertyLabel = trim((string)($order['property_name'] ?? ''));
if (!empty($order['property_code'])) {
    $propertyLabel .= ($propertyLabel !== '' ? ' (' . $order['property_code'] . ')' : $order['property_code']);
}

$cardY = 60;
$clientW = 82;     // widened so the customer's complete billing details are clear
$summaryX0 = 97;
$summaryW = 103;
$topCardH = 62;

/* CLIENT CARD */
$pdf->card(10, $cardY, $clientW, $topCardH, 'PROFORMA TO');
$pdf->SetTextColor(18, 28, 43);
$pdf->SetFont('Arial', 'B', 9);
$pdf->SetXY(14, $cardY + 17);
$pdf->MultiCell($clientW - 8, 4.6, pdf_txt($agentName), 0, 'L');

/*
   Compact aligned client details:
   labels remain on the left and values begin immediately to their right.
   Missing Address, VAT and TIN values are intentionally left blank.
*/
$clientLabelX = 14;
$clientValueX = 31;
$clientLabelW = 16;
$clientValueW = $clientW - 25;

$pdf->SetTextColor(18, 28, 43);

/* Address may wrap, but starts on the same line as the label. */
$pdf->SetFont('Arial', 'B', 7.4);
$pdf->SetXY($clientLabelX, $cardY + 25);
$pdf->Cell($clientLabelW, 4, 'Address:', 0, 0, 'L');
$pdf->SetFont('Arial', '', 7.4);
$pdf->SetXY($clientValueX, $cardY + 25);
$pdf->MultiCell($clientValueW, 3.8, pdf_txt($clientAddress), 0, 'L');

/* Remaining rows use fixed aligned labels and values. */
$clientRows = [
    ['Guest:', $guestName],
    ['VAT No.:', $clientVatNo],
    ['TIN No.:', $clientTinNo],
    ['Currency:', $currency],
];

$clientRowY = $cardY + 42;
foreach ($clientRows as $idx => $clientRow) {
    $pdf->SetFont('Arial', 'B', 7.5);
    $pdf->SetXY($clientLabelX, $clientRowY);
    $pdf->Cell($clientLabelW, 4.4, pdf_txt($clientRow[0]), 0, 0, 'L');

    $pdf->SetFont('Arial', $idx === 3 ? 'B' : '', 7.5);
    $pdf->SetXY($clientValueX, $clientRowY);
    $pdf->Cell($clientValueW, 4.4, pdf_txt((string)$clientRow[1]), 0, 0, 'L');

    $clientRowY += 4.2;
}

/* WIDE BOOKING SUMMARY CARD */
$pdf->card($summaryX0, $cardY, $summaryW, $topCardH, 'BOOKING SUMMARY');

$paxText = $paxRooms . ' rooms | ' . $paxAdults . 'A / ' . $paxChildren . 'C';
if ($kids03Count > 0) {
    $paxText .= ' / ' . $kids03Count . ' kids 0-3yrs';
}

$leftX = $summaryX0 + 5;
$rightX = $summaryX0 + 53;
$rowY = $cardY + 16;

$summaryLeft = [
    ['Proforma No.', $proformaNo],
    ['Proforma Date', safe_date_fmt_pdf(date('Y-m-d'), 'd-M-Y')],
    ['Booking Ref', (string)($orderId)],
    ['Folio No.', $displayFolioNo !== '' ? $displayFolioNo : '-'],
];
$summaryRight = [
    ['Check In', safe_date_fmt_pdf($order['check_in'], 'd-M-Y')],
    ['Check Out', safe_date_fmt_pdf($order['check_out'], 'd-M-Y')],
    ['Room(s)', $headerRoomSummary],
    ['Guest', $guestName],
];

for ($i = 0; $i < 4; $i++) {
    $y = $rowY + ($i * 8);
    $pdf->SetDrawColor(232, 236, 241);
    if ($i > 0) {
        $pdf->Line($summaryX0 + 4, $y - 1.5, $summaryX0 + $summaryW - 4, $y - 1.5);
    }

    $pdf->SetXY($leftX, $y);
    $pdf->SetFont('Arial', 'B', 7.5);
    $pdf->SetTextColor(18, 28, 43);
    $pdf->Cell(23, 5, pdf_txt($summaryLeft[$i][0]), 0, 0, 'L');
    $pdf->SetFont('Arial', '', 7.3);
    $pdf->Cell(23, 5, pdf_txt($summaryLeft[$i][1]), 0, 0, 'R');

    $pdf->SetXY($rightX, $y);
    $pdf->SetFont('Arial', 'B', 7.3);
    $pdf->Cell(19, 5, pdf_txt($summaryRight[$i][0]), 0, 0, 'L');
    $pdf->SetFont('Arial', '', 7.2);
    $pdf->Cell(26, 5, pdf_txt($summaryRight[$i][1]), 0, 0, 'R');
}

/* Highlighted pax strip inside summary */
$pdf->SetFillColor(245, 247, 249);
$pdf->SetDrawColor(222, 226, 232);
$pdf->RoundedRect($summaryX0 + 5, $cardY + 52, $summaryW - 10, 8, 2, 'DF');
$pdf->SetXY($summaryX0 + 8, $cardY + 54);
$pdf->SetFont('Arial', 'B', 7.8);
$pdf->SetTextColor(18, 28, 43);
$pdf->Cell(18, 4, 'Pax', 0, 0, 'L');
$pdf->SetFont('Arial', '', 7.5);
$pdf->Cell($summaryW - 34, 4, pdf_txt($paxText), 0, 1, 'R');

/* -----------------------------
   Room mix mini row
----------------------------- */
$pdf->SetY(128);

/* For group bookings, skip the long individual room list (it jumbles the
   layout) and show only the room-type mix. Small bookings keep both lines. */
if (!$isGroupBooking) {
    $pdf->SetFont('Arial', 'B', 8.5);
    $pdf->SetTextColor(18, 28, 43);
    $pdf->Cell(25, 5, 'Rooms:', 0, 0, 'L');
    $pdf->SetFont('Arial', '', 8.3);
    $pdf->MultiCell(165, 5, pdf_txt($allocatedRoomSummary !== '' ? $allocatedRoomSummary : 'Unassigned'), 0, 'L');
}

$pdf->SetFont('Arial', 'B', 8.5);
$pdf->SetTextColor(18, 28, 43);
$pdf->Cell(25, 5, 'Room Mix:', 0, 0, 'L');
$pdf->SetFont('Arial', '', 8.3);
$pdf->MultiCell(165, 5, pdf_txt($roomMixSummary !== '' ? $roomMixSummary : 'Not set'), 0, 'L');

if ($isGroupBooking) {
    $pdf->SetFont('Arial', 'I', 8);
    $pdf->SetTextColor(90, 100, 115);
    $pdf->Cell(190, 5, pdf_txt('Group booking - ' . $allocatedRoomCount . ' rooms. Individual room allocation available on the rooming list.'), 0, 1, 'L');
    $pdf->SetTextColor(18, 28, 43);
}

if ($kids03Count > 0) {
    $pdf->SetFont('Arial', 'I', 8);
    $pdf->Cell(190, 5, pdf_txt('Kids 0-3yrs booked: ' . $kids03Count), 0, 1, 'L');
}

/* -----------------------------
   Items table
----------------------------- */
if ($isCancelledBooking) {
    $pdf->Ln(2);
    $pdf->SetFillColor(254, 242, 242);
    $pdf->SetDrawColor(220, 38, 38);
    $pdf->RoundedRect(10, $pdf->GetY(), 190, 10, 2, 'DF');
    $pdf->SetXY(14, $pdf->GetY() + 2.5);
    $pdf->SetFont('Arial', 'B', 8.5);
    $pdf->SetTextColor(153, 27, 27);
    $pdf->Cell(182, 5, pdf_txt('CANCELLED BOOKING - THIS PROFORMA IS FOR CANCELLATION FEES ONLY'), 0, 1, 'C');
    $pdf->SetTextColor(18, 28, 43);
}
$pdf->Ln(2);
$pdf->addItemsHeader();
$pdf->SetTextColor(18, 28, 43);
$pdf->SetFont('Arial', '', 8);

$idx = 1;
$fill = false;
$rowLineH = 4.5; // must match the description MultiCell line height below
foreach ($lines as $line) {
    /*
       Do not force the final three items onto a continuation page. Each row is
       now moved only when its ACTUAL calculated height will not fit above the
       footer. This allows page 1 to use all available space while still keeping
       every wrapped row intact and repeating the table heading when required.
    */
    $dateTxt = safe_date_fmt_pdf($line['tx_date'], 'd-M-y');
    $descTxt = (string)($line['printable_description'] ?? $line['description'] ?? '');
    $amt = (float)($line['debit'] ?? 0);

    $folioShort = 'Master';
    if (($line['folio_type'] ?? '') === 'room') {
        $folioShort = 'Room';
        if (!empty($line['room_no'])) {
            $folioShort .= ' ' . $line['room_no'];
        }
    } elseif (($line['folio_type'] ?? '') === 'guest') {
        $folioShort = 'Guest';
    }

    // Row height tracks the actual wrapped description height (4.5mm per line
    // + ~1.5mm vertical padding) instead of a fixed 9mm floor, which was
    // leaving a large blank gap under every single-line row.
    $descLines = max(1, (int)ceil(strlen($descTxt) / 62));
    $rowH = min(20, ($descLines * $rowLineH) + 1.5);

    // Ensure the whole row fits on the current page before drawing it.
    $pdf->checkPageSpace($rowH + 1);

    $rowY = $pdf->GetY();
    $pdf->SetFillColor($fill ? 248 : 255, $fill ? 250 : 255, $fill ? 252 : 255);
    $pdf->SetDrawColor(222, 226, 232);

    $pdf->SetX(10);
    $pdf->Cell(9, $rowH, (string)$idx, 1, 0, 'C', true);
    $pdf->Cell(23, $rowH, pdf_txt($dateTxt), 1, 0, 'L', true);
    $xDesc = $pdf->GetX();
    // Paint the description cell background (zebra) before the text.
    $pdf->Rect($xDesc, $rowY, 98, $rowH, 'F');
    // Vertically centre a short description within the row by padding the top.
    $descBlockH = $descLines * $rowLineH;
    $descPadTop = max(0, ($rowH - $descBlockH) / 2);
    $pdf->SetXY($xDesc, $rowY + $descPadTop);
    $pdf->MultiCell(98, $rowLineH, pdf_txt($descTxt), 0, 'L', false);
    // Draw the description cell border on top so padding doesn't distort it.
    $pdf->Rect($xDesc, $rowY, 98, $rowH);
    $pdf->SetXY($xDesc + 98, $rowY);
    $pdf->Cell(27, $rowH, pdf_txt($folioShort), 1, 0, 'L', true);
    $pdf->Cell(33, $rowH, money_fmt_pdf($amt), 1, 1, 'R', true);

    $fill = !$fill;
    $idx++;
}

if (empty($lines)) {
    $pdf->SetX(10);
    $pdf->Cell(190, 10, 'No charge items found.', 1, 1, 'C');
}

/* -----------------------------
   Totals + three-bank payment section
----------------------------- */
if ($pdf->GetY() > 210) {
    $pdf->AddPage();
    $pdf->SetY(18);
} else {
    $pdf->Ln(5);
}

/*
   Stable full-width totals summary.

   The previous totals card was only 94mm wide and its height changed according
   to the charge breakdown. On invoices with several groups, labels and values
   could look cramped or appear out of sequence. This version uses one fixed
   full-width card with two clearly separated columns:
     - left: charge breakdown
     - right: invoice totals
   The TOTAL DUE band is anchored to the bottom of the card, so it cannot be
   pushed into or overlap the rows above it.
*/
$gridY = $pdf->GetY();
$breakdownDisplayRows = array_slice($chargeBreakdownRows, 0, 6);
$breakdownCount = max(1, count($breakdownDisplayRows));
$totalsH = max(58, 34 + ($breakdownCount * 5));

/* Always keep the entire totals card together on one page. */
if ($gridY + $totalsH > 266) {
    $pdf->AddPage();
    $gridY = 18;
}

$pdf->card(10, $gridY, 190, $totalsH, 'PROFORMA TOTALS');

/* Column headings */
$pdf->SetFillColor(245, 247, 249);
$pdf->SetDrawColor(222, 226, 232);
$pdf->RoundedRect(14, $gridY + 15, 87, 8, 2, 'DF');
$pdf->RoundedRect(105, $gridY + 15, 91, 8, 2, 'DF');
$pdf->SetTextColor(18, 28, 43);
$pdf->SetFont('Arial', 'B', 7.8);
$pdf->SetXY(18, $gridY + 17);
$pdf->Cell(79, 4, 'CHARGE BREAKDOWN', 0, 0, 'L');
$pdf->SetXY(109, $gridY + 17);
$pdf->Cell(83, 4, 'INVOICE SUMMARY', 0, 0, 'L');

/* Vertical divider */
$pdf->SetDrawColor(222, 226, 232);
$pdf->Line(103, $gridY + 15, 103, $gridY + $totalsH - 15);

/* Left column: grouped charges */
$leftY = $gridY + 26;
if (!empty($breakdownDisplayRows)) {
    foreach ($breakdownDisplayRows as $bd) {
        $label = ucwords(strtolower(trim((string)($bd['charge_group'] ?? 'Other Charges'))));
        if ($label === '') $label = 'Other Charges';
        if (strlen($label) > 33) $label = substr($label, 0, 30) . '...';

        $pdf->SetXY(18, $leftY);
        $pdf->SetFont('Arial', '', 7.5);
        $pdf->SetTextColor(45, 55, 70);
        $pdf->Cell(55, 5, pdf_txt($label), 0, 0, 'L');
        $pdf->SetFont('Arial', 'B', 7.6);
        $pdf->SetTextColor(18, 28, 43);
        $pdf->Cell(24, 5, pdf_txt($currency . ' ' . money_fmt_pdf((float)($bd['amount'] ?? 0))), 0, 1, 'R');
        $leftY += 5;
    }
} else {
    $pdf->SetXY(18, $leftY);
    $pdf->SetFont('Arial', 'I', 7.5);
    $pdf->SetTextColor(100, 110, 122);
    $pdf->Cell(79, 5, 'No separate charge groups available.', 0, 1, 'L');
}

/* Right column: fixed totals rows */
$rightY = $gridY + 26;
$summaryRows = [
    ['Total Charges', $totalCharges, false],
    ['Payments Received', $totalPayments, false],
    ['Balance Due', $totalBalance, true],
];

foreach ($summaryRows as $row) {
    $pdf->SetXY(109, $rightY);
    $pdf->SetFont('Arial', $row[2] ? 'B' : '', $row[2] ? 8.5 : 8);
    $pdf->SetTextColor(18, 28, 43);
    $pdf->Cell(47, 6, pdf_txt($row[0] . ' (' . $currency . ')'), 0, 0, 'L');
    $pdf->Cell(36, 6, pdf_txt(money_fmt_pdf((float)$row[1])), 0, 1, 'R');

    $pdf->SetDrawColor(235, 238, 242);
    $pdf->Line(109, $rightY + 6, 192, $rightY + 6);
    $rightY += 7;
}

/* Bottom total band - fixed to card bottom. */
$totalBandY = $gridY + $totalsH - 12;
$pdf->SetFillColor(36, 50, 66);
$pdf->RoundedRect(10, $totalBandY, 190, 12, 2, 'F');
$pdf->SetTextColor(255, 255, 255);
$pdf->SetFont('Arial', 'B', 10.5);
$pdf->SetXY(18, $totalBandY + 2.4);
$pdf->Cell(92, 7, 'TOTAL DUE', 0, 0, 'L');
$pdf->SetFont('Arial', 'B', 12.5);
$pdf->Cell(82, 7, pdf_txt($currency . ' ' . money_fmt_pdf($totalBalance)), 0, 1, 'R');

/* Move the cursor below the card for the next section. */
$pdf->SetY($gridY + $totalsH);



/* Full-width banking section. The reference PDF presents three accounts as
   stacked alternatives separated by OR, rather than squeezing them into one
   small bank card. */
$bankCount = count($bankDetailsList);
$bankSectionH = 27;
foreach ($bankDetailsList as $bankRow) {
    $visibleRows = 0;
    foreach (['AccountName','Bank','AccountNo','Branch','SwiftCode','BranchCode','Currency','Address'] as $field) {
        if (trim((string)($bankRow[$field] ?? '')) !== '') $visibleRows++;
    }
    $bankSectionH += max(23, 8 + ($visibleRows * 4.5));
}
if ($bankCount > 1) $bankSectionH += ($bankCount - 1) * 6;
$bankSectionH = max(48, $bankSectionH);

$bankStartY = max($gridY + $totalsH + 6, $pdf->GetY() + 5);
if ($bankStartY + $bankSectionH > 267) {
    $pdf->AddPage();
    $bankStartY = 18;
}

$pdf->card(10, $bankStartY, 190, $bankSectionH, 'BANKING DETAILS');
$pdf->SetXY(14, $bankStartY + 16);
$pdf->SetFont('Arial', 'B', 7.5);
$pdf->SetTextColor(18, 28, 43);
$pdf->MultiCell(182, 4.2, pdf_txt('NB: FOR ALL PAYMENTS BEING MADE FROM ZIMBABWE KINDLY USE THE DOMESTIC ACCOUNT BANKING DETAILS.'), 0, 'L');
$pdf->SetFont('Arial', '', 7.2);
$pdf->MultiCell(182, 4.2, pdf_txt('Please note that we will never change our bank account details. Please contact our offices to verify banking details before making any payment or deposit.'), 0, 'L');

$by = $pdf->GetY() + 2;
if (!$bankDetailsList) {
    $pdf->SetXY(14, $by);
    $pdf->SetFont('Arial', 'I', 8);
    $pdf->Cell(182, 6, pdf_txt('No bank accounts are configured in tblbankdetails for this property.'), 0, 1, 'L');
    $by += 7;
} else {
    foreach ($bankDetailsList as $bankIndex => $bankRow) {
        if ($bankIndex > 0) {
            $pdf->SetXY(14, $by);
            $pdf->SetFont('Arial', 'B', 8);
            $pdf->Cell(182, 5, 'OR', 0, 1, 'L');
            $by += 6;
        }

        $heading = trim((string)($bankRow['Title'] ?? ''));
        if ($heading === '') {
            $heading = trim((string)($bankRow['Bank'] ?? ''));
        }
        if ($heading !== '') {
            $pdf->SetXY(14, $by);
            $pdf->SetFont('Arial', 'B', 8.2);
            $pdf->Cell(182, 5, pdf_txt($heading), 0, 1, 'L');
            $by += 5;
        }

        $rowsToPrint = [
            ['Account Name', $bankRow['AccountName'] ?? ''],
            ['Bank Name', $bankRow['Bank'] ?? ''],
            ['Account No', $bankRow['AccountNo'] ?? ''],
            ['Branch', $bankRow['Branch'] ?? ''],
            ['Swift Code', $bankRow['SwiftCode'] ?? ''],
            ['Sort/Branch Code', $bankRow['BranchCode'] ?? ''],
            ['Currency', 'USD'],
            ['Bank Address', $bankRow['Address'] ?? ''],
        ];

        foreach ($rowsToPrint as $bankPrintRow) {
            $value = trim((string)$bankPrintRow[1]);
            if ($value === '') continue;
            $pdf->SetXY(14, $by);
            $pdf->SetFont('Arial', 'B', 7.4);
            $pdf->Cell(31, 4.5, pdf_txt($bankPrintRow[0] . ':'), 0, 0, 'L');
            $pdf->SetFont('Arial', '', 7.4);
            $pdf->Cell(151, 4.5, pdf_txt($value), 0, 1, 'L');
            $by += 4.5;
        }
        $by += 1.5;
    }
}
$pdf->SetY($bankStartY + $bankSectionH + 3);

/* -----------------------------
   Notes card removed per requirement.
----------------------------- */



/* -----------------------------
   Cancellation Policy
----------------------------- */
$pdf->Ln(6);
$pdf->SetFont('Arial','B',10);
$pdf->SetTextColor(18,28,43);
$pdf->Cell(0,6,'Cancellation Policy',0,1,'L');

$pdf->SetFont('Arial','B',8.5);
$pdf->Cell(0,5,'Booking and Cancellations',0,1,'L');
$pdf->SetFont('Arial','',8);

$policyLines = [
"- All bookings are deemed to be provisional unless a confirmation voucher has been issued to Pioneers Guest Lodge or payment has been received.",
"- Any cancellation within 46 and 60 days will attract 20% cancellation fees.",
"- Any cancellation within 31 and 45 days will attract 50% cancellation fees.",
"- Any cancellation within 30 days will attract 100% cancellation fees.",
"",
"Payment Policy",
"- Payment must be received in full prior to arrival date unless a credit facility has been granted.",
"- All bank charges are for the Tour Operator's account.",
"- A surcharge of 3.5% will be levied on credit card payments."
];

foreach ($policyLines as $line) {
    if ($pdf->GetY() > 255) {
        $pdf->AddPage();
    }
    if ($line === "") {
        $pdf->Ln(2);
        continue;
    }
    if ($line === "Payment Policy") {
        $pdf->SetFont('Arial','B',8.5);
        $pdf->Cell(0,5,$line,0,1,'L');
        $pdf->SetFont('Arial','',8);
        continue;
    }
    $pdf->MultiCell(0,4,pdf_txt($line),0,'L');
}

$pdf->Output('Other_Payer_Extras_Proforma_' . $orderId . '_' . $payerAgentId . '.pdf', 'I');
exit;
