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


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

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

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,
    ];
}


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

$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);

/* -----------------------------
   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 room/guest-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
    WHERE ft.order_id = :order_id
      AND LOWER(COALESCE(f.folio_type, '')) IN ('room','guest')
");
$stmt->execute([':order_id' => $orderId]);
$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 room/guest-folio payments affect the balance. */
$totalPayments = $grossPayments;
$netPayments   = max(0, $grossPayments);
$totalBalance  = max(0, $totalCharges - $netPayments);

/* 
   Booking accommodation reference total (not added to this room-account proforma).
   Only actual room/guest folio transactions are printed and totalled.
*/
$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;
}

$folioChargeTotal = $totalCharges;
$hasAccommodationPosted = false;

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

$displayFolioNo = (string)($displayFolio['folio_no'] ?? '');
$displayFolioName = trim((string)($displayFolio['folio_name'] ?? 'Room / Guest Folios'));
if ($displayFolioName === '') {
    $displayFolioName = 'Room / Guest 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
    LEFT JOIN rooms r ON r.id = f.room_id
    {$extraItemJoinSql}
    WHERE ft.order_id = :order_id
      AND LOWER(COALESCE(f.folio_type, '')) IN ('room','guest')
      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]);
$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
            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 LOWER(COALESCE(f.folio_type, '')) IN ('room','guest')
              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
            WHERE ft.order_id = :order_id
              AND LOWER(COALESCE(f.folio_type, '')) IN ('room','guest')
              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]);
    $chargeBreakdownRows = $stmtBreakdown->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {
    $chargeBreakdownRows = [];
}

/*
   Strict room-account mode:
   Do not create invoice charges from rel_orders_prices. Only actual, non-voided
   transactions posted to room or guest folios 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);

if ($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'   => 'room',
            'room_no'      => '',
            'folio_name'   => 'Room Account',
            '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 room/guest-folio transactions.
$netPayments = max(0, $totalPayments);
$totalBalance = max(0, $totalCharges - $netPayments);

/* -----------------------------
   Bank details
   Source: lodge_settings first, then old tblbankdetails fallback.
----------------------------- */
$bankId = is_numeric($bankParam) ? (int)$bankParam : 1;
if ($bankId <= 0) $bankId = 1;

$bankDetails = [
    'SwiftCode'   => '',
    'BranchCode'  => '',
    'Branch'      => '',
    'Bank'        => '',
    'AccountNo'   => '',
    'AccountName' => ''
];

try {
    if (!empty($lodgeSettings)) {
        $bankDetails['SwiftCode']   = trim((string)($lodgeSettings['bank_swift'] ?? ''));
        $bankDetails['BranchCode']  = '';
        $bankDetails['Branch']      = trim((string)($lodgeSettings['bank_branch'] ?? ''));
        $bankDetails['Bank']        = trim((string)($lodgeSettings['bank_name'] ?? ''));
        $bankDetails['AccountNo']   = trim((string)($lodgeSettings['bank_account_no'] ?? ''));
        $bankDetails['AccountName'] = trim((string)($lodgeSettings['bank_account_name'] ?? ''));
    }

    if ($bankDetails['Bank'] === '' && $bankDetails['AccountNo'] === '') {
        $rowbank = false;

        try {
            $stmtBank = $pdo->prepare("
                SELECT *
                FROM tblbankdetails
                WHERE property_id = :property_id
                  AND ID = :id
                LIMIT 1
            ");
            $stmtBank->execute([
                ':property_id' => $propertyId,
                ':id'          => $bankId,
            ]);
            $rowbank = $stmtBank->fetch(PDO::FETCH_ASSOC);
        } catch (Throwable $e) {
            $rowbank = false;
        }

        if (!$rowbank) {
            try {
                $stmtBank = $pdo->prepare("
                    SELECT *
                    FROM tblbankdetails
                    WHERE property_id = :property_id
                    ORDER BY ID ASC
                    LIMIT 1
                ");
                $stmtBank->execute([':property_id' => $propertyId]);
                $rowbank = $stmtBank->fetch(PDO::FETCH_ASSOC);
            } catch (Throwable $e) {
                $rowbank = false;
            }
        }

        if (!$rowbank) {
            try {
                $stmtBank = $pdo->prepare("SELECT * FROM tblbankdetails WHERE ID = :id LIMIT 1");
                $stmtBank->execute([':id' => $bankId]);
                $rowbank = $stmtBank->fetch(PDO::FETCH_ASSOC);
            } catch (Throwable $e) {
                $rowbank = false;
            }
        }

        if ($rowbank) {
            $bankDetails['SwiftCode']   = $rowbank['SwiftCode'] ?? '';
            $bankDetails['BranchCode']  = $rowbank['BranchCode'] ?? '';
            $bankDetails['Branch']      = $rowbank['Branch'] ?? '';
            $bankDetails['Bank']        = $rowbank['Bank'] ?? '';
            $bankDetails['AccountNo']   = $rowbank['AccountNo'] ?? '';
            $bankDetails['AccountName'] = $rowbank['AccountName'] ?? '';
        }
    }
} 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 = 'ROOM ACCOUNT PROFORMA';
    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']);
}
$proformaPrefix = 'R' . $proformaPrefix;
$proformaNo = $proformaPrefix . '-' . $orderId;

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

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

$pdf->SetFillColor(255, 255, 255);
$pdf->SetDrawColor(157, 169, 184);
$pdf->RoundedRect(10, 20, 190, 48, 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');
$pdf->SetFont('Arial', 'I', 8.5);
$pdf->SetTextColor(72, 82, 96);
$pdf->SetX(16);
$pdf->Cell(95, 5, pdf_txt(trim((string)($company['Motto'] ?? 'Reservations | Folios | Payments'))), 0, 1, 'L');
$pdf->SetTextColor(18, 28, 43);
$pdf->SetFont('Arial', '', 8.2);
$pdf->Ln(1);
$pdf->SetX(16);
$pdf->Cell(95, 4.6, pdf_txt(trim((string)($company['Street'] ?? ''))), 0, 1, 'L');
$pdf->SetX(16);
$pdf->Cell(95, 4.6, pdf_txt(trim((string)($company['Postal'] ?? '') . ' ' . (string)($company['City'] ?? ''))), 0, 1, 'L');
$pdf->SetX(16);
$pdf->Cell(95, 4.6, 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->Cell(95, 4.6, 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, 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 = 75;
$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.4, pdf_txt('Guest: ' . $guestName), 0, 'L');
$pdf->SetXY(14, $cardY + 38);
$pdf->MultiCell($clientW - 8, 4.4, pdf_txt('Property: ' . ($propertyLabel !== '' ? $propertyLabel : 'Not set')), 0, 'L');
$pdf->SetXY(14, $cardY + 47);
$pdf->SetFont('Arial', '', 7.8);
$pdf->MultiCell($clientW - 8, 4.4, pdf_txt('Room(s): ' . $headerRoomSummary), 0, 'L');
$pdf->SetXY(14, $cardY + 55);
$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 = $paxRooms . ' rooms | ' . $paxAdults . 'A / ' . $paxChildren . 'C';
if ($kids03Count > 0) {
    $paxText .= ' / ' . $kids03Count . ' kids 0-3yrs';
}

$leftX = $summaryX0 + 6;
$rightX = $summaryX0 + 61;
$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(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 + 50, $summaryW - 10, 8, 2, 'DF');
$pdf->SetXY($summaryX0 + 8, $cardY + 52);
$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(141);

/* 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
----------------------------- */
$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) {
    $dateTxt = safe_date_fmt_pdf($line['tx_date'], 'd-M-y');
    $descTxt = (string)($line['printable_description'] ?? $line['description'] ?? '');
    $amt = (float)($line['debit'] ?? 0);

    $folioShort = 'Account';
    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');
}

/* -----------------------------
   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],
];
$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 removed per requirement.
----------------------------- */

$pdf->Output('Room_Account_Proforma_' . $orderId . '.pdf', 'I');
exit;
