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

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

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

$orderId   = (int)($_GET['order_id'] ?? 0);
$bankParam = isset($_GET['bank']) ? $_GET['bank'] : 1;
$targetFolioId = (int)($_GET['folio_id'] ?? 0);
$targetRoomingId = (int)($_GET['rooming_id'] ?? 0);
$folioScopeTitle = 'GUEST FOLIO PROFORMA';
$folioScopeSuffix = 'Guest_Folio';

if ($orderId <= 0) {
    exit('Missing order 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 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,
    ];
}

/* -----------------------------
   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,
        CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')) AS agent_name
    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');
}

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

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

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

$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'] ?? [];

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

/* -----------------------------
   Guest Folio scope
----------------------------- */
if ($targetFolioId <= 0) {
    $stmtGuestFolio = $pdo->prepare("
        SELECT id
        FROM folios
        WHERE order_id = :order_id
          AND folio_type = 'guest'
          AND COALESCE(status,1) = 1
        ORDER BY id ASC
        LIMIT 1
    ");
    $stmtGuestFolio->execute([':order_id' => $orderId]);
    $targetFolioId = (int)($stmtGuestFolio->fetchColumn() ?: 0);
}

if ($targetFolioId <= 0) {
    exit('No guest folio found for this booking. Check in occupants first or create an individual guest folio.');
}

$stmtGuestFolioCheck = $pdo->prepare("
    SELECT id
    FROM folios
    WHERE id = :folio_id
      AND order_id = :order_id
      AND folio_type = 'guest'
      AND COALESCE(status,1) = 1
    LIMIT 1
");
$stmtGuestFolioCheck->execute([
    ':folio_id' => $targetFolioId,
    ':order_id' => $orderId,
]);
if (!(int)($stmtGuestFolioCheck->fetchColumn() ?: 0)) {
    exit('Invalid guest folio for this booking.');
}

/* -----------------------------
   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,

        /*
           Gross payments received:
           This intentionally includes original payment rows even if later voided,
           so the summary can show Payment 100 and Void 100 separately.
        */
        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.credit, 0) > 0
                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
    WHERE ft.order_id = :order_id
      AND ft.folio_id = :target_folio_id
");
$stmt->execute([':order_id' => $orderId, ':target_folio_id' => $targetFolioId]);
$folioTotals = $stmt->fetch(PDO::FETCH_ASSOC);

$totalCharges   = (float)($folioTotals['total_charges'] ?? 0);
$grossPayments  = (float)($folioTotals['gross_payments'] ?? 0);
$voidedPayments = (float)($folioTotals['voided_payments'] ?? 0);

/*
   Display payments gross, display void separately, but calculate balance using net payments.
   Example: charges 350, payment 100, void 100 => balance 350.
*/
$totalPayments = $grossPayments;
$netPayments   = max(0, $grossPayments - $voidedPayments);
$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 = (float)($stmt->fetchColumn() ?: 0);

$bookingTotal = (float)($order['totalamount'] ?? 0);
if ($accommodationTotal <= 0 && $bookingTotal > 0) {
    $accommodationTotal = $bookingTotal;
}
/* Guest folio proforma must only show the selected guest folio.
   Do not inject full booking accommodation as a fallback here. */
$accommodationTotal = 0.00;

$folioChargeTotal = $totalCharges;
$hasAccommodationPosted = false;

/* -----------------------------
   Representative folio
   (for displaying a folio context only)
----------------------------- */
$stmt = $pdo->prepare("
    SELECT *
    FROM folios
    WHERE order_id = :order_id
      AND id = :target_folio_id
      AND folio_type = 'guest'
    LIMIT 1
");
$stmt->execute([':order_id' => $orderId, ':target_folio_id' => $targetFolioId]);
$displayFolio = $stmt->fetch(PDO::FETCH_ASSOC);

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

/*
   Actual guest for this guest folio:
   The order guest is only the lead booking guest. For a guest folio proforma
   we must show the occupant/guest attached to the selected folio/rooming line.
*/
$leadGuestName = $guestName;
$actualGuestName = '';

$folioOccupantName = trim((string)($displayFolio['occupant_name'] ?? ''));
if ($folioOccupantName !== '') {
    $actualGuestName = $folioOccupantName;
}

$folioRoomingId = (int)($displayFolio['rooming_id'] ?? 0);
$folioOccupantId = (int)($displayFolio['occupant_id'] ?? 0);

if ($actualGuestName === '' && $folioOccupantId > 0) {
    try {
        $stmtActualGuest = $pdo->prepare("
            SELECT TRIM(CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,''))) AS guest_name
            FROM order_rooming_guests
            WHERE id = :id
              AND order_id = :order_id
            LIMIT 1
        ");
        $stmtActualGuest->execute([
            ':id' => $folioOccupantId,
            ':order_id' => $orderId,
        ]);
        $actualGuestName = trim((string)($stmtActualGuest->fetchColumn() ?: ''));
    } catch (Throwable $e) {
        $actualGuestName = '';
    }
}

if ($actualGuestName === '' && $folioRoomingId > 0) {
    try {
        $stmtActualGuest = $pdo->prepare("
            SELECT TRIM(CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,''))) AS guest_name
            FROM order_rooming_guests
            WHERE rooming_id = :rooming_id
              AND order_id = :order_id
            ORDER BY is_primary DESC, id ASC
            LIMIT 1
        ");
        $stmtActualGuest->execute([
            ':rooming_id' => $folioRoomingId,
            ':order_id' => $orderId,
        ]);
        $actualGuestName = trim((string)($stmtActualGuest->fetchColumn() ?: ''));
    } catch (Throwable $e) {
        $actualGuestName = '';
    }
}

if ($actualGuestName === '' && $targetRoomingId > 0) {
    try {
        $stmtActualGuest = $pdo->prepare("
            SELECT TRIM(CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,''))) AS guest_name
            FROM order_rooming_guests
            WHERE rooming_id = :rooming_id
              AND order_id = :order_id
            ORDER BY is_primary DESC, id ASC
            LIMIT 1
        ");
        $stmtActualGuest->execute([
            ':rooming_id' => $targetRoomingId,
            ':order_id' => $orderId,
        ]);
        $actualGuestName = trim((string)($stmtActualGuest->fetchColumn() ?: ''));
    } catch (Throwable $e) {
        $actualGuestName = '';
    }
}

if ($actualGuestName === '') {
    $folioNameCandidate = $displayFolioName;
    $folioNameCandidate = preg_replace('/^Guest\s*Folio\s*[-:]*\s*/i', '', $folioNameCandidate);
    $folioNameCandidate = preg_replace('/^Folio\s*[-:]*\s*/i', '', $folioNameCandidate);
    $folioNameCandidate = trim((string)$folioNameCandidate);
    if ($folioNameCandidate !== '' && stripos($folioNameCandidate, 'booking folios') === false) {
        $actualGuestName = $folioNameCandidate;
    }
}

if ($actualGuestName === '') {
    $actualGuestName = $leadGuestName;
}

$guestName = $actualGuestName;

/*
   Actual room number for this guest folio:
   Prefer the room linked to the guest folio. If not available, fall back to
   the folio rooming line, the requested rooming id, then any room number found
   on the selected guest folio transactions.
*/
$actualRoomNo = '';

$folioRoomId = (int)($displayFolio['room_id'] ?? 0);
if ($folioRoomId > 0) {
    try {
        $stmtRoomNo = $pdo->prepare("
            SELECT room_no
            FROM rooms
            WHERE id = :room_id
            LIMIT 1
        ");
        $stmtRoomNo->execute([':room_id' => $folioRoomId]);
        $actualRoomNo = trim((string)($stmtRoomNo->fetchColumn() ?: ''));
    } catch (Throwable $e) {
        $actualRoomNo = '';
    }
}

if ($actualRoomNo === '' && $folioRoomingId > 0) {
    try {
        $stmtRoomNo = $pdo->prepare("
            SELECT COALESCE(NULLIF(r.room_no,''), NULLIF(rmg.room_no,''), '') AS room_no
            FROM order_rooming rmg
            LEFT JOIN rooms r ON r.id = rmg.room_id
            WHERE rmg.id = :rooming_id
              AND rmg.order_id = :order_id
            LIMIT 1
        ");
        $stmtRoomNo->execute([
            ':rooming_id' => $folioRoomingId,
            ':order_id'   => $orderId,
        ]);
        $actualRoomNo = trim((string)($stmtRoomNo->fetchColumn() ?: ''));
    } catch (Throwable $e) {
        $actualRoomNo = '';
    }
}

if ($actualRoomNo === '' && $targetRoomingId > 0) {
    try {
        $stmtRoomNo = $pdo->prepare("
            SELECT COALESCE(NULLIF(r.room_no,''), NULLIF(rmg.room_no,''), '') AS room_no
            FROM order_rooming rmg
            LEFT JOIN rooms r ON r.id = rmg.room_id
            WHERE rmg.id = :rooming_id
              AND rmg.order_id = :order_id
            LIMIT 1
        ");
        $stmtRoomNo->execute([
            ':rooming_id' => $targetRoomingId,
            ':order_id'   => $orderId,
        ]);
        $actualRoomNo = trim((string)($stmtRoomNo->fetchColumn() ?: ''));
    } catch (Throwable $e) {
        $actualRoomNo = '';
    }
}

if ($actualRoomNo === '' && isset($displayFolio['room_no'])) {
    $actualRoomNo = trim((string)$displayFolio['room_no']);
}

/* -----------------------------
   Company profile
----------------------------- */
$company = [
    'CompanyName'     => defined('APP_NAME') ? APP_NAME : 'RoomZ Lodge',
    'Postal'          => '',
    'Street'          => '',
    'City'            => '',
    'Email'           => '',
    'Phone'           => '',
    'Website'         => '',
    'VATNO'           => '',
    'TIN'             => '',
    'CompanyRegNo'    => '',
    'Motto'           => 'Reservations • Folios • Payments',
    'ProformaPrefix'  => 'PF',
    'LogoPath'        => ''
];

try {
    if (proforma_table_exists($pdo, 'lodge_settings')) {
        $stmtLodge = $pdo->prepare("
            SELECT *
            FROM lodge_settings
            WHERE id = 1
            LIMIT 1
        ");
        $stmtLodge->execute();
        $ls = $stmtLodge->fetch(PDO::FETCH_ASSOC);

        if ($ls) {
            $lodgeName = trim((string)($ls['lodge_name'] ?? ''));
            $legalName = trim((string)($ls['legal_name'] ?? ''));

            $company['CompanyName'] = $legalName !== ''
                ? $legalName
                : ($lodgeName !== '' ? $lodgeName : $company['CompanyName']);

            $line1 = trim((string)($ls['address_line1'] ?? ''));
            $line2 = trim((string)($ls['address_line2'] ?? ''));
            $city = trim((string)($ls['city'] ?? ''));
            $region = trim((string)($ls['state_region'] ?? ''));
            $country = trim((string)($ls['country'] ?? ''));
            $postal = trim((string)($ls['postal_code'] ?? ''));

            $company['Street'] = trim($line1 . ($line2 !== '' ? ', ' . $line2 : ''));
            $company['City'] = trim($city . ($region !== '' ? ', ' . $region : '') . ($country !== '' ? ', ' . $country : ''));
            $company['Postal'] = $postal;

            $company['Email'] = trim((string)($ls['email'] ?? ''));
            $company['Phone'] = trim((string)($ls['phone'] ?? ''));
            if (trim((string)($ls['alt_phone'] ?? '')) !== '') {
                $company['Phone'] .= ($company['Phone'] !== '' ? ' / ' : '') . trim((string)$ls['alt_phone']);
            }
            $company['Website'] = trim((string)($ls['website'] ?? ''));

            $company['VATNO'] = trim((string)($ls['vat_number'] ?? ''));
            $company['TIN'] = trim((string)($ls['tin_number'] ?? ''));
            $company['CompanyRegNo'] = trim((string)($ls['company_reg_number'] ?? ''));

            $tagLine = trim((string)($ls['tag_line'] ?? ''));
            $company['Motto'] = $tagLine !== ''
                ? $tagLine
                : (($lodgeName !== '' ? $lodgeName : $company['CompanyName']) . ' • Reservations • Folios • Payments');

            $prefix = trim((string)($ls['reservation_prefix'] ?? ''));
            if ($prefix === '') {
                $prefix = trim((string)($ls['invoice_prefix'] ?? ''));
            }
            $company['ProformaPrefix'] = $prefix !== '' ? $prefix : 'PF';

            $company['LogoPath'] = trim((string)($ls['logo_path'] ?? ''));
        }
    }
} catch (Throwable $e) {
    // keep defaults
}

$propertyDisplay = trim((string)($order['property_name'] ?? ''));
if ($propertyDisplay !== '' && stripos($company['CompanyName'], $propertyDisplay) === false) {
    // Keep lodge_settings.tag_line as the document tagline without adding property text.
    $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,
";

$stmt = $pdo->prepare("
    SELECT
        ft.*, 
        f.folio_name,
        f.folio_type,
        r.room_no,
        {$extraItemSelectSql}
        CASE
            WHEN COALESCE(eci.linked_item_description, '') <> '' THEN eci.linked_item_description
            ELSE ft.description
        END AS printable_description
    FROM folio_transactions ft
    INNER JOIN folios f ON f.id = ft.folio_id
    LEFT JOIN rooms r ON r.id = f.room_id
    {$extraItemJoinSql}
    WHERE ft.order_id = :order_id
      AND ft.folio_id = :target_folio_id
      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([':order_id' => $orderId, ':target_folio_id' => $targetFolioId]);
$lines = $stmt->fetchAll(PDO::FETCH_ASSOC);

/*
   Charge breakdown for the totals panel.
   New postings are grouped from extra_charge_items.category_name where present;
   older postings fall back to folio_transactions.tx_type/source_table.
*/
$chargeBreakdownRows = [];
try {
    if ($hasExtraChargeItems) {
        $stmtBreakdown = $pdo->prepare("
            SELECT
                CASE
                    WHEN COALESCE(eci.category_name,'') <> '' THEN eci.category_name
                    WHEN COALESCE(ft.tx_type,'') <> '' THEN REPLACE(ft.tx_type, '_', ' ')
                    ELSE 'Other Charges'
                END AS charge_group,
                SUM(CASE
                    WHEN eci.id IS NOT NULL THEN COALESCE(eci.line_total, 0)
                    ELSE COALESCE(ft.debit, 0) - COALESCE(ft.credit, 0)
                END) AS amount
            FROM folio_transactions ft
            LEFT JOIN extra_charge_items eci ON eci.folio_transaction_id = ft.id
            WHERE ft.order_id = :order_id
              AND ft.folio_id = :target_folio_id
              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
            WHERE ft.order_id = :order_id
              AND ft.folio_id = :target_folio_id
              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([':order_id' => $orderId, ':target_folio_id' => $targetFolioId]);
    $chargeBreakdownRows = $stmtBreakdown->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {
    $chargeBreakdownRows = [];
}

if ($actualRoomNo === '') {
    foreach ($lines as $lineRoomCheck) {
        $lineRoomNo = trim((string)($lineRoomCheck['room_no'] ?? ''));
        if ($lineRoomNo !== '') {
            $actualRoomNo = $lineRoomNo;
            break;
        }
    }
}

/*
   Detect whether accommodation is already posted as a folio transaction.
   This avoids duplicating accommodation if your folio posting already includes it.
*/
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', '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 = in_array($txType, ['accommodation', 'room', 'room_charge', 'accommodation_charge', 'nightly', 'room_night'], true)
        || strpos($desc, 'accommodation') !== false
        || strpos($desc, 'room charge') !== false
        || strpos($desc, 'night') !== false;

    if ($isAccommodation && !$isExtra) {
        $hasAccommodationPosted = true;
        break;
    }
}

/*
   If accommodation is not yet posted to the folio, add a provisional
   accommodation line before extras. This fixes proforma showing extras only.
*/
if (!$hasAccommodationPosted && $accommodationTotal > 0) {
    $fallbackDate = !empty($order['ordered_on']) ? $order['ordered_on'] : $order['check_in'];
    $fallbackDesc = 'Accommodation charges for booking ' . ($order['order_no'] ?: $orderId);

    if ($roomMixSummary !== '') {
        $fallbackDesc .= ' (' . $roomMixSummary . ')';
    }

    array_unshift($lines, [
        'tx_date'      => $fallbackDate,
        'description'  => $fallbackDesc,
        'debit'        => $accommodationTotal,
        'folio_type'   => 'master',
        'room_no'      => '',
        'folio_name'   => 'Booking Folio',
        'tx_type'      => 'accommodation',
        'source_table' => 'rel_orders_prices'
    ]);
}

if (!$hasAccommodationPosted && $accommodationTotal > 0) {
    $totalCharges = $accommodationTotal + $folioChargeTotal;
} elseif ($totalCharges <= 0 && $accommodationTotal > 0) {
    $totalCharges = $accommodationTotal;
}

$netPayments = max(0, $totalPayments - $voidedPayments);
$totalBalance = max(0, $totalCharges - $netPayments);

/* -----------------------------
   Bank details
----------------------------- */
$bankDetails = [
    'SwiftCode'   => '',
    'BranchCode'  => '',
    'Branch'      => '',
    'Bank'        => '',
    'AccountNo'   => '',
    'AccountName' => ''
];

try {
    if (proforma_table_exists($pdo, 'lodge_settings')) {
        $stmtLodgeBank = $pdo->prepare("
            SELECT *
            FROM lodge_settings
            WHERE id = 1
            LIMIT 1
        ");
        $stmtLodgeBank->execute();
        $lsBank = $stmtLodgeBank->fetch(PDO::FETCH_ASSOC);

        if ($lsBank) {
            $bankDetails['Bank']        = trim((string)($lsBank['bank_name'] ?? ''));
            $bankDetails['AccountName'] = trim((string)($lsBank['bank_account_name'] ?? ''));
            $bankDetails['AccountNo']   = trim((string)($lsBank['bank_account_no'] ?? ''));
            $bankDetails['Branch']      = trim((string)($lsBank['bank_branch'] ?? ''));
            $bankDetails['SwiftCode']   = trim((string)($lsBank['bank_swift'] ?? ''));
        }
    }
} catch (Throwable $e) {
    // keep defaults
}

/* -----------------------------
   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();
        }
    }
}

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

$pdf = new PDF();
$pdf->company  = $company;
$pdf->docTitle = $folioScopeTitle;
$pdf->docRef   = $proformaNo;
$pdf->AliasNbPages();
$pdf->AddPage();
$pdf->SetAutoPageBreak(true, 18);

/* -----------------------------
   Top document bar + company hero
----------------------------- */
$pdf->docTopBar($folioScopeTitle, $proformaNo);

$pdf->SetFillColor(255, 255, 255);
$pdf->SetDrawColor(157, 169, 184);
$pdf->RoundedRect(10, 20, 190, 42, 3, 'D');

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

$tagLine = trim((string)($company['Motto'] ?? ''));
if ($tagLine !== '') {
    $pdf->SetFont('Arial', 'I', 8.5);
    $pdf->SetTextColor(75, 85, 99);
    $pdf->SetX(16);
    $pdf->Cell(95, 5, pdf_txt($tagLine), 0, 1, 'L');
}
$pdf->SetTextColor(18, 28, 43);
$pdf->Ln(1);
$pdf->SetX(16);
$pdf->Cell(95, 5, pdf_txt(trim((string)($company['Street'] ?? ''))), 0, 1, 'L');
$pdf->SetX(16);
$pdf->Cell(95, 5, pdf_txt(trim((string)($company['Postal'] ?? '') . ' ' . (string)($company['City'] ?? ''))), 0, 1, 'L');
$pdf->SetX(16);
$pdf->Cell(95, 5, pdf_txt('Email: ' . (string)($company['Email'] ?? '') . '   Tel: ' . (string)($company['Phone'] ?? '')), 0, 1, 'L');
$regText = '';
if (trim((string)($company['CompanyRegNo'] ?? '')) !== '') {
    $regText = '   |   Reg No: ' . (string)$company['CompanyRegNo'];
}
$pdf->SetX(16);
$pdf->Cell(95, 5, pdf_txt('TIN: ' . (string)($company['TIN'] ?? '') . '   |   VAT: ' . (string)($company['VATNO'] ?? '') . $regText), 0, 1, 'L');

$logo = __DIR__ . '/att.png';
if (!empty($company['LogoPath'])) {
    $configuredLogo = (string)$company['LogoPath'];
    if (preg_match('/^https?:\/\//i', $configuredLogo)) {
        $logo = $configuredLogo;
    } else {
        $candidateLogo = $configuredLogo;
        if ($candidateLogo !== '' && $candidateLogo[0] !== '/') {
            $candidateLogo = __DIR__ . '/../../' . ltrim($candidateLogo, '/');
        }
        if (file_exists($candidateLogo)) {
            $logo = $candidateLogo;
        }
    }
}
if (file_exists($logo) || preg_match('/^https?:\/\//i', $logo)) {
    $pdf->Image($logo, 128, 31, 58);
}

/* -----------------------------
   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 = 67;
$clientW = 70;
$summaryX0 = 85;
$summaryW = 115;

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

$pdf->SetFont('Arial', '', 7.8);
$pdf->SetXY(14, $cardY + 29);
$pdf->MultiCell($clientW - 8, 4.2, pdf_txt('Guest: ' . $guestName), 0, 'L');
$pdf->SetXY(14, $cardY + 36);
$pdf->MultiCell($clientW - 8, 4.2, pdf_txt('Room: ' . ($actualRoomNo !== '' ? $actualRoomNo : '-')), 0, 'L');
$pdf->SetXY(14, $cardY + 43);
$pdf->MultiCell($clientW - 8, 4.2, pdf_txt('Property: ' . ($propertyLabel !== '' ? $propertyLabel : 'Not set')), 0, 'L');
$pdf->SetXY(14, $cardY + 53);
$pdf->SetFont('Arial', 'B', 7.8);
$pdf->Cell($clientW - 8, 4.5, pdf_txt('Currency: ' . $currency), 0, 1, 'L');

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

$paxText = (int)($order['totalrooms'] ?? 1) . ' rooms | ' . (int)($order['adults'] ?? 0) . 'A / ' . (int)($order['kids'] ?? 0) . 'C';
if ($kids03Count > 0) {
    $paxText .= ' / ' . $kids03Count . ' kids 0-3yrs';
}

$leftX = $summaryX0 + 6;
$rightX = $summaryX0 + 61;
$rowY = $cardY + 16;

$summaryLeft = [
    ['Proforma No.', $proformaNo],
    ['Booking Ref', (string)($order['order_no'] ?: $orderId)],
    ['Folio No.', $displayFolioNo !== '' ? $displayFolioNo : '-'],
    ['Currency', $currency],
];
$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')],
    ['Guest', $guestName],
    ['Room', $actualRoomNo !== '' ? $actualRoomNo : '-'],
];

for ($i = 0; $i < count($summaryRight); $i++) {
    $y = $rowY + ($i * 7);
    $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(25, 5, pdf_txt($summaryLeft[$i][0]), 0, 0, 'L');
    $pdf->SetFont('Arial', '', 7.5);
    $pdf->Cell(25, 5, pdf_txt($summaryLeft[$i][1]), 0, 0, 'R');

    $pdf->SetXY($rightX, $y);
    $pdf->SetFont('Arial', 'B', 7.5);
    $pdf->Cell(22, 5, pdf_txt($summaryRight[$i][0]), 0, 0, 'L');
    $pdf->SetFont('Arial', '', 7.5);
    $pdf->Cell(28, 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 + 51, $summaryW - 10, 8, 2, 'DF');
$pdf->SetXY($summaryX0 + 8, $cardY + 53);
$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(132);
$pdf->SetFont('Arial', 'B', 8.5);
$pdf->SetTextColor(18, 28, 43);
$pdf->Cell(25, 5, 'Guest:', 0, 0, 'L');
$pdf->SetFont('Arial', '', 8.5);
$pdf->Cell(85, 5, pdf_txt($guestName), 0, 0, 'L');
$pdf->SetFont('Arial', 'B', 8.5);
$pdf->Cell(18, 5, 'Room:', 0, 0, 'L');
$pdf->SetFont('Arial', '', 8.5);
$pdf->Cell(62, 5, pdf_txt($actualRoomNo !== '' ? $actualRoomNo : '-'), 0, 1, '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 ($kids03Count > 0) {
    $pdf->SetFont('Arial', 'I', 8);
    $pdf->Cell(190, 5, pdf_txt('Kids 0-3yrs booked: ' . $kids03Count), 0, 1, 'L');
}

/* -----------------------------
   Items table
----------------------------- */
$pdf->Ln(2);
$pdf->addItemsHeader();
$pdf->SetTextColor(18, 28, 43);
$pdf->SetFont('Arial', '', 8);

$idx = 1;
$fill = false;
foreach ($lines as $line) {
    $pdf->checkPageSpace(13);

    $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';
    }

    $rowY = $pdf->GetY();
    $descLines = max(1, substr_count($descTxt, "\n") + 1, ceil(strlen($descTxt) / 62));
    $rowH = max(9, min(28, $descLines * 4.5));
    $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();
    $pdf->MultiCell(98, 4.5, pdf_txt($descTxt), 1, 'L', true);
    $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');
}

/* -----------------------------
   Bank + totals grid
----------------------------- */
if ($pdf->GetY() > 205) {
    $pdf->AddPage();
    $pdf->SetY(18);
} else {
    $pdf->Ln(5);
}

$gridY = $pdf->GetY();
$breakdownDisplayRows = array_slice($chargeBreakdownRows, 0, 5);
$bankH = max(45, 48 + (count($breakdownDisplayRows) * 5));
$pdf->card(10, $gridY, 92, $bankH, 'BANK DETAILS - ALL PAYMENTS TO BE MADE TO');
$pdf->SetFont('Arial', '', 8);
$pdf->SetTextColor(18, 28, 43);
$by = $gridY + 16;
$bankRows = [
    ['Account Name', (string)$bankDetails['AccountName']],
    ['Account Number', (string)$bankDetails['AccountNo']],
    ['Bank', (string)$bankDetails['Bank']],
    ['Branch', (string)$bankDetails['Branch']],
    ['Currency', $currency],
];
foreach ($bankRows as $br) {
    $pdf->SetXY(14, $by);
    $pdf->SetFont('Arial', 'B', 7.7);
    $pdf->Cell(34, 5.5, pdf_txt($br[0]), 0, 0, 'L');
    $pdf->SetFont('Arial', '', 7.7);
    $pdf->Cell(49, 5.5, pdf_txt($br[1]), 0, 1, 'L');
    $by += 6;
}

$pdf->card(106, $gridY, 94, $bankH, '');
$tx = 112;
$ty = $gridY + 7;
$pdf->SetFont('Arial', '', 8.2);
$pdf->SetTextColor(18, 28, 43);

if (!empty($breakdownDisplayRows)) {
    $pdf->SetXY($tx, $ty);
    $pdf->SetFont('Arial', 'B', 7.6);
    $pdf->Cell(80, 5, pdf_txt('Charge Breakdown'), 0, 1, 'L');
    $ty += 5;
    foreach ($breakdownDisplayRows as $bd) {
        $label = ucwords(strtolower(trim((string)($bd['charge_group'] ?? 'Other Charges'))));
        if ($label === '') $label = 'Other Charges';
        $pdf->SetXY($tx, $ty);
        $pdf->SetFont('Arial', '', 7.2);
        $pdf->Cell(45, 4.6, pdf_txt($label), 0, 0, 'L');
        $pdf->Cell(35, 4.6, pdf_txt(money_fmt_pdf((float)($bd['amount'] ?? 0))), 0, 1, 'R');
        $ty += 4.8;
    }
    if (count($chargeBreakdownRows) > count($breakdownDisplayRows)) {
        $pdf->SetXY($tx, $ty);
        $pdf->SetFont('Arial', 'I', 7);
        $pdf->Cell(80, 4.5, pdf_txt('More charge groups included in Charges total.'), 0, 1, 'L');
        $ty += 4.8;
    }
    $pdf->SetDrawColor(222, 226, 232);
    $pdf->Line(112, $ty + 1, 194, $ty + 1);
    $ty += 4;
}

$totRows = [
    ['Charges (' . $currency . ')', money_fmt_pdf($totalCharges), false],
    ['Payments (' . $currency . ')', money_fmt_pdf($totalPayments), false],
];
if ($voidedPayments > 0) {
    $totRows[] = ['Voided (' . $currency . ')', money_fmt_pdf($voidedPayments), false];
}
$totRows[] = ['Balance Due (' . $currency . ')', money_fmt_pdf($totalBalance), true];
foreach ($totRows as $tr) {
    $pdf->SetXY($tx, $ty);
    $pdf->SetFont('Arial', $tr[2] ? 'B' : '', 8.2);
    $pdf->Cell(45, 6, pdf_txt($tr[0]), 0, 0, 'L');
    $pdf->Cell(35, 6, pdf_txt($tr[1]), 0, 1, 'R');
    $ty += 6.5;
}
$pdf->SetFillColor(36, 50, 66);
$pdf->RoundedRect(106, $gridY + $bankH - 12, 94, 12, 2, 'F');
$pdf->SetTextColor(255, 255, 255);
$pdf->SetFont('Arial', 'B', 11);
$pdf->SetXY(112, $gridY + $bankH - 9.5);
$pdf->Cell(38, 7, 'TOTAL DUE', 0, 0, 'L');
$pdf->SetFont('Arial', 'B', 12);
$pdf->Cell(44, 7, pdf_txt($currency . ' ' . money_fmt_pdf($totalBalance)), 0, 1, 'R');

/* -----------------------------
   Notes card
   Dynamic height so long notes stay inside the rounded rectangle.
----------------------------- */
$notesText = "Please make payment to the account details provided.\nKindly use the proforma number as payment reference.";
if ($reservationNotes !== '') {
    $notesText .= "\nReservation Notes: " . $reservationNotes;
}

$notesText = trim((string)$notesText);
$notesLineHeight = 4.6;
$notesTextWidth = 182;
$notesTextLines = 0;

foreach (explode("\n", $notesText) as $noteLine) {
    $noteLine = trim((string)$noteLine);
    if ($noteLine === '') {
        $notesTextLines += 1;
        continue;
    }

    /*
       Approximate FPDF MultiCell wrapping before drawing the rectangle.
       At Arial 8pt, this keeps the card height consistent without allowing
       long notes to spill outside the rounded border.
    */
    $approxCharsPerLine = 118;
    $notesTextLines += max(1, (int)ceil(strlen($noteLine) / $approxCharsPerLine));
}

$notesTextHeight = max($notesLineHeight, $notesTextLines * $notesLineHeight);
$notesH = max(23, 16 + $notesTextHeight + 8);
$notesY = $gridY + $bankH + 6;

if ($notesY + $notesH > 270) {
    $pdf->AddPage();
    $notesY = 20;
}

$pdf->card(10, $notesY, 190, $notesH, 'NOTES');
$pdf->SetXY(14, $notesY + 16);
$pdf->SetFont('Arial', '', 8);
$pdf->SetTextColor(18, 28, 43);
$pdf->MultiCell($notesTextWidth, $notesLineHeight, pdf_txt($notesText), 0, 'L');

$pdf->Output($folioScopeSuffix . '_Proforma_' . ($order['order_no'] ?: $orderId) . '.pdf', 'I');
exit;
