{% 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>
    @media print {
        body {
            margin: 0;
            padding-bottom: 100px; /* Enough room for footer */
        }
    }

    body {
        font-family: Arial, sans-serif;
        font-size: 12px;
        position: relative;
    }

    .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: #34495e;
        border-bottom: 1px solid #ccc;
        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: #f5f5f5;
        text-align: left;
        padding: 8px;
        border: 1px solid #ddd;
    }

    td {
        padding: 8px;
        border: 1px solid #ddd;
    }

    .balance-box {
        text-align: right;
        font-size: 16px;
        font-weight: bold;
        color: #2d3436;
        margin-top: 20px;
    }

    .small-text {
        font-size: 10px;
        color: #555;
    }

    /* Fixed footer */
    .footer-section {
        position: fixed;
        bottom: 20px;
        left: 0;
        width: 100%;
        font-size: 11px;
        font-family: Arial, sans-serif;
        text-align: center;
        padding: 0 30px;
        color: #000;
    }

    .footer-line {
        border-top: 1px solid #000;
        margin-bottom: 5px;
        width: 100%;
    }

    .footer-labels {
        display: flex;
        justify-content: space-around;
    }

    .created-by {
        text-align: left;
        margin-top: 5px;
    }
</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) }}<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>
        {% set total_amount = 0 %}
        {% for item in doc.items %}
        {% set total_amount = total_amount + (item.amount or 0) %}
        <tr>
            <td>{{ loop.index }}</td>
            <td>{{ item.item_name }}</td>
            <td>{{ item.qty }}</td>
            <td>{{ "{:,.0f}".format(item.rate or 0) }}</td>
            <td>{{ "{:,.0f}".format(item.amount or 0) }}</td>
        </tr>
        {% endfor %}
        <tr>
            <td colspan="4" style="text-align: right;"><b>Total</b></td>
            <td><b>{{ "{:,.0f}".format(doc.total or total_amount) }}</b></td>
        </tr>
    </tbody>
</table>

<div class="section-title">Ledger Entries Last 5 (till {{ frappe.utils.formatdate(doc.posting_date) }})</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) }}</td>
            <td>{{ entry.voucher_no }}</td>
            <td>{{ "{:,.0f}".format(entry.debit or 0) }}</td>
            <td>{{ "{:,.0f}".format(entry.credit or 0) }}</td>
            <td class="small-text">{{ entry.remarks or 'No Remarks' }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

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

<!-- FOOTER AT BOTTOM OF PDF PAGE -->
<style>
    html, body {
        height: 100%;
        margin: 0;
        padding: 0;
    }

    .footer-section {
        position: fixed;
        bottom: 0px;
        left: 0;
        right: 0;
        text-align: center;
        font-size: 11px;
        font-family: Arial, sans-serif;
        color: #000;
    }

    .footer-line {
        border-top: 1px solid #000;
        width: 95%;
        margin: 0 auto 4px auto;
    }

    .footer-labels {
        display: flex;
        justify-content: space-around;
        margin: 0 50px;
    }

    .footer-labels div {
        flex: 1;
    }

    .created-by {
        text-align: left;
        margin-left: 50px;
        margin-top: 4px;
        font-size: 10px;
    }
</style>

<div class="footer-section">
    <div class="footer-line"></div>
    <div class="footer-labels">
        <div>Posted By</div>
        <div>Submitted By</div>
        <div>Verified By</div>
    </div>
    <div class="created-by">
        Created by: {{ frappe.db.get_value('User', doc.owner, 'first_name') or "Unknown" }}
    </div>
</div>