{% set gl_entries = frappe.db.sql("""
    SELECT posting_date, voucher_no, debit, credit, remarks
    FROM `tabGL Entry`
    WHERE party_type = 'Customer'
    AND party = %s
    AND is_cancelled = 0
    AND posting_date <= %s
    ORDER BY posting_date DESC, creation DESC
    LIMIT 5
""", (doc.customer, doc.posting_date), as_dict=1) %}

{% set gl_totals = frappe.db.sql("""
    SELECT 
        COALESCE(SUM(debit), 0) AS total_debit,
        COALESCE(SUM(credit), 0) AS total_credit
    FROM `tabGL Entry`
    WHERE party_type = 'Customer'
    AND party = %s
    AND is_cancelled = 0
    AND posting_date BETWEEN '2024-01-01' AND %s
""", (doc.customer, doc.posting_date), as_dict=1)[0] %}

<style>
    body {
        font-family: Arial, sans-serif;
        font-size: 12px;
        color: #2c3e50;
    }
    .invoice-title {
        text-align: center;
        font-size: 24px;
        font-weight: bold;
        margin-bottom: 20px;
        color: #2c3e50;
    }
    .section-title {
        font-size: 16px;
        margin-top: 30px;
        font-weight: bold;
        color: #1e3799;
        border-bottom: 2px solid #1e3799;
        padding-bottom: 5px;
    }
    .details-box {
        display: flex;
        justify-content: space-between;
        margin-bottom: 20px;
    }
    .details-box div {
        width: 48%;
        line-height: 1.6;
    }
    table {
        width: 100%;
        border-collapse: collapse;
        margin-bottom: 25px;
    }
    th {
        background-color: #dff9fb;
        text-align: left;
        padding: 8px;
        border: 1px solid #dcdde1;
    }
    td {
        padding: 8px;
        border: 1px solid #dcdde1;
    }
    .balance-box {
        text-align: right;
        font-size: 16px;
        font-weight: bold;
        color: #2d3436;
        margin-top: 20px;
    }
    .remarks-column {
        font-size: 10px; /* Smaller font size for Remarks column */
    }
</style>

<div class="invoice-title">Sales Invoice</div>

<div class="details-box">
    <div>
        <b>Customer:</b> {{ doc.customer_name }}<br>
        <b>Customer Code:</b> {{ doc.customer }}<br>
        <b>DC No:</b> {{ doc.custom_dc_no or "-" }}
    </div>
    <div>
        <b>Invoice No:</b> {{ doc.name }}<br>
        <b>Posting Date:</b> {{ frappe.utils.formatdate(doc.posting_date, "dd-MM-yyyy") }}<br>
        <b>Bilty No:</b> {{ doc.custom_bilty_no or "-" }}<br>
        <b>Transporter:</b> {{ doc.custom_transport or "-" }}
    </div>
</div>

<div class="section-title">Current Sales Invoice</div>

<table>
    <thead>
        <tr>
            <th>#</th>
            <th>Item</th>
            <th>Qty</th>
            <th>Rate (PKR)</th>
            <th>Amount (PKR)</th>
        </tr>
    </thead>
    <tbody>
        {% for item in doc.items %}
        <tr>
            <td>{{ loop.index }}</td>
            <td>{{ item.item_name }}</td>
            <td>{{ (item.qty or 0) | int }}</td>
            <td>{{ "{:,}".format((item.rate or 0) | int) }}</td>
            <td>{{ "{:,}".format((item.amount or 0) | int) }}</td>
        </tr>
        {% endfor %}
        <tr>
            <td colspan="4" style="text-align: right;"><b>Total</b></td>
            <td><b>{{ "{:,}".format(doc.total | int) }}</b></td>
        </tr>
    </tbody>
</table>

<div class="section-title">Ledger Entries Last 5 (till {{ frappe.utils.formatdate(doc.posting_date, "dd-MM-yyyy") }})</div>

<table>
    <thead>
        <tr>
            <th>Date</th>
            <th>Voucher No</th>
            <th>Debit (PKR)</th>
            <th>Credit (PKR)</th>
            <th>Remarks</th>
        </tr>
    </thead>
    <tbody>
        {% for entry in gl_entries %}
        <tr>
            <td>{{ frappe.utils.formatdate(entry.posting_date, "dd-MM-yyyy") }}</td>
            <td>{{ entry.voucher_no }}</td>
            <td>{{ "{:,}".format((entry.debit or 0) | int) }}</td>
            <td>{{ "{:,}".format((entry.credit or 0) | int) }}</td>
            <td class="remarks-column">{{ entry.remarks or 'No Remarks' }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

<div class="balance-box">
    Closing Balance: PKR {{ "{:,}".format(((gl_totals.total_debit or 0) - (gl_totals.total_credit or 0)) | int) }}
</div>