@extends('admin.layouts')

@section('title', 'GST Sales With HSN')

@section('header')

    {{-- =========================================================
        DATATABLE CSS
    ========================================================== --}}

    <link rel="stylesheet"
        href="{{ asset('/assets/css/datatable/dataTables.bootstrap5.min.css') }}">

    <link rel="stylesheet"
        href="{{ asset('/assets/css/datatable/responsive.bootstrap.min.css') }}">

    <style>



        .gst-page {
            padding: 20px;
        }

        .gst-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 20px;
        }

        .gst-header h3 {
            margin: 0;
            font-size: 22px;
            font-weight: 600;
        }

        .filter-card,
        .report-card {
            background: #fff;
            border: 1px solid #e5e5e5;
            border-radius: 8px;
            padding: 20px;
            margin-bottom: 20px;
        }

        #gstSalesTable {
            width: 100% !important;
        }

        #gstSalesTable thead th {
            background: #f5f6f8;
            color: #343a40;
            font-size: 13px;
            font-weight: 600;
            white-space: nowrap;
        }

        #gstSalesTable tbody td {
            font-size: 13px;
            white-space: nowrap;
            vertical-align: middle;
        }

        .empty-report {
            text-align: center;
            padding: 60px 20px !important;
            color: #777;
            font-size: 15px;
        }

        #excelBtn {
            min-width: 175px;
            font-weight: 600;
        }

        #excelBtn i {
            margin-right: 6px;
        }

        .dataTables_wrapper .dataTables_filter input {
            margin-left: 8px;
        }

        .dataTables_wrapper .dataTables_length select {
            margin: 0 5px;
        }

    </style>

@endsection


@section('content')

    <div class="container-fluid gst-page">

        {{-- =========================================================
            HEADER
        ========================================================== --}}

        <div class="gst-header">

            <div>

                <h3>
                    GST Sales Report With HSN
                </h3>

                <small class="text-muted">
                    GSTR-1 Sales / HSN Wise Report
                </small>

            </div>


            <div>

                {{-- =================================================
                    SINGLE EXCEL EXPORT BUTTON
                ================================================== --}}

                <button
                    type="button"
                    class="btn btn-success"
                    id="excelBtn">

                    <i class="fas fa-file-excel"></i>

                    Export GST Excel

                </button>

            </div>

        </div>


        {{-- =========================================================
            FILTER
        ========================================================== --}}

        <div class="filter-card">

            <div class="row align-items-end g-3">

                {{-- FROM DATE --}}
                <div class="col-md-3">

                    <label
                        for="fromDate"
                        class="form-label">

                        From Date

                    </label>

                    <input
                        type="date"
                        id="fromDate"
                        class="form-control">

                </div>


                {{-- TO DATE --}}
                <div class="col-md-3">

                    <label
                        for="toDate"
                        class="form-label">

                        To Date

                    </label>

                    <input
                        type="date"
                        id="toDate"
                        class="form-control">

                </div>


                {{-- FILTER --}}
                <div class="col-md-2">

                    <button
                        type="button"
                        id="filterBtn"
                        class="btn btn-primary w-100">

                        <i class="fas fa-filter"></i>

                        Filter

                    </button>

                </div>


                {{-- RESET --}}
                <div class="col-md-2">

                    <button
                        type="button"
                        id="resetBtn"
                        class="btn btn-secondary w-100">

                        <i class="fas fa-sync-alt"></i>

                        Reset

                    </button>

                </div>

            </div>

        </div>


        {{-- =========================================================
            REPORT
        ========================================================== --}}

        <div class="report-card">

            <div class="table-responsive">

                <table
                    id="gstSalesTable"
                    class="table table-bordered table-hover">

                    <thead>
                        <tr class="text-center">
                            <th>DATE</th>
                            <th>INVOICE NO</th>
                            <th>PARTY GSTIN</th>
                            <th>PARTY NAME</th>
                            <th>ITEM NAME</th>
                            <th>HSN CODE</th>
                            <th>QTY</th>
                            <th>PRICE/UNIT</th>
                            <th>SGST</th>
                            <th>CGST</th>
                            <th>IGST</th>
                            <th>AMOUNT</th>
                            <th>ACTION</th>
                        </tr>
                    </thead>

                    <tbody>
                        @forelse($report ?? [] as $row)
                            <tr>
                                {{-- DATE --}}
                                <td>{{ $row['date'] ?? '-' }}</td>

                                {{-- INVOICE NO --}}
                                <td>
                                    <strong>{{ $row['invoice_no'] ?? '-' }}</strong>
                                </td>

                                {{-- PARTY GSTIN --}}
                                <td>{{ $row['party_gstin'] ?? '-' }}</td>

                                {{-- PARTY NAME --}}
                                <td>{{ $row['party_name'] ?? '-' }}</td>

                                {{-- ITEM NAME --}}
                                <td>{{ $row['item_name'] ?? '-' }}</td>

                                {{-- HSN CODE --}}
                                <td>
                                    <strong>{{ $row['hsn_code'] ?? '-' }}</strong>
                                </td>

                                {{-- QTY --}}
                                <td class="text-end">
                                    {{ number_format((float)($row['qty'] ?? 0), 1) }}
                                    {{ $row['unit'] ?? 'PCS' }}
                                </td>

                                {{-- PRICE / UNIT --}}
                                <td class="text-end">
                                    ₹{{ number_format((float)($row['price'] ?? 0), 2) }}
                                </td>

                                {{-- SGST --}}
                                <td class="text-end">
                                    @if(isset($row['sgst']) && (float)$row['sgst'] > 0)
                                        ₹{{ number_format((float)$row['sgst'], 2) }}
                                        @if(!empty($row['gst_rate']))
                                            <br>
                                            <small>({{ number_format((float)$row['gst_rate'] / 2, 0) }}%)</small>
                                        @endif
                                    @else
                                        -
                                    @endif
                                </td>

                                {{-- CGST --}}
                                <td class="text-end">
                                    @if(isset($row['cgst']) && (float)$row['cgst'] > 0)
                                        ₹{{ number_format((float)$row['cgst'], 2) }}
                                        @if(!empty($row['gst_rate']))
                                            <br>
                                            <small>({{ number_format((float)$row['gst_rate'] / 2, 0) }}%)</small>
                                        @endif
                                    @else
                                        -
                                    @endif
                                </td>

                                {{-- IGST --}}
                                <td class="text-end">
                                    @if(isset($row['igst']) && (float)$row['igst'] > 0)
                                        ₹{{ number_format((float)$row['igst'], 2) }}
                                        @if(!empty($row['gst_rate']))
                                            <br>
                                            <small>({{ number_format((float)$row['gst_rate'], 0) }}%)</small>
                                        @endif
                                    @else
                                        -
                                    @endif
                                </td>

                                {{-- AMOUNT --}}
                                <td class="text-end fw-bold">
                                    ₹{{ number_format((float)($row['amount'] ?? 0), 2) }}
                                </td>

                                {{-- ACTION --}}
                                <td class="text-center">
                                  @if(!empty($row['invoice_no']) && $row['invoice_no'] !== '-')
    <a
        href="{{ url('admin/invoice_a4') . '/' . $row['invoice_no'] }}"
        target="_blank"
        rel="noopener"
        class="btn btn-sm btn-primary"
    >
        View Invoice
    </a>
@else
    <span class="text-muted">-</span>
@endif
                                </td>
                            </tr>
                        @empty
                            <tr>
                                <td colspan="13" class="empty-report">
                                    No GST sales transactions available.
                                </td>
                            </tr>
                        @endforelse
                    </tbody>

                </table>

            </div>

        </div>

    </div>

@endsection


@section('footer')

    {{-- =========================================================
        JQUERY
    ========================================================== --}}

    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>


    {{-- =========================================================
        DATATABLE JS
    ========================================================== --}}

    <script src="{{ asset('/assets/js/datatable/jquery.dataTables.min.js') }}"></script>

    <script src="{{ asset('/assets/js/datatable/dataTables.bootstrap5.min.js') }}"></script>

    <script src="{{ asset('/assets/js/datatable/dataTables.responsive.min.js') }}"></script>


    {{-- =========================================================
        SHEETJS
        Used for multi-sheet Excel workbook.
    ========================================================== --}}

    <script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>


    <script>

        $(document).ready(function () {

            /*
            |--------------------------------------------------------------------------
            | ORIGINAL REPORT DATA FROM LARAVEL
            |--------------------------------------------------------------------------
            */

            const reportData = @json($report ?? []);


            /*
            |--------------------------------------------------------------------------
            | DATATABLE
            |--------------------------------------------------------------------------
            */

            let table = $('#gstSalesTable').DataTable({

                responsive: false,

                scrollX: true,

                pageLength: 10,

                lengthMenu: [
                    [10, 25, 50, 100, -1],
                    [10, 25, 50, 100, "All"]
                ],

                ordering: true,

                searching: true,

                lengthChange: true,

                autoWidth: false,

                order: [
                    [0, 'desc']
                ],

                columnDefs: [

                    {
                        targets: -1,
                        orderable: false,
                        searchable: false
                    }

                ],

                language: {

                    search: "Search:",

                    searchPlaceholder: "Search GST sales...",

                    lengthMenu: "Show _MENU_ entries",

                    info: "Showing _START_ to _END_ of _TOTAL_ entries",

                    infoEmpty: "No entries available",

                    zeroRecords: "No matching records found",

                    emptyTable: "No GST sales transactions available.",

                    paginate: {

                        first: "First",

                        last: "Last",

                        next: "Next",

                        previous: "Previous"

                    }

                }

            });


            /*
            |--------------------------------------------------------------------------
            | HELPER
            |--------------------------------------------------------------------------
            */

            function numberValue(value) {

                let number = parseFloat(value);

                return isNaN(number)
                    ? 0
                    : number;

            }


            /*
            |--------------------------------------------------------------------------
            | DATE PARSER
            |--------------------------------------------------------------------------
            |
            | Supports:
            | DD-MM-YYYY
            | YYYY-MM-DD
            |
            */

            function parseReportDate(dateValue) {

                if (!dateValue) {

                    return null;

                }


                let value = String(dateValue).trim();


                /*
                | YYYY-MM-DD
                */

                if (
                    /^\d{4}-\d{2}-\d{2}$/.test(value)
                ) {

                    let parts = value.split('-');

                    return new Date(
                        parseInt(parts[0]),
                        parseInt(parts[1]) - 1,
                        parseInt(parts[2])
                    );

                }


                /*
                | DD-MM-YYYY
                */

                if (
                    /^\d{2}-\d{2}-\d{4}$/.test(value)
                ) {

                    let parts = value.split('-');

                    return new Date(
                        parseInt(parts[2]),
                        parseInt(parts[1]) - 1,
                        parseInt(parts[0])
                    );

                }


                return null;

            }


            /*
            |--------------------------------------------------------------------------
            | CHECK DATE FILTER
            |--------------------------------------------------------------------------
            */

            function isDateInRange(row) {

                let fromDate =
                    $('#fromDate').val();

                let toDate =
                    $('#toDate').val();


                /*
                | No filter
                */

                if (
                    !fromDate &&
                    !toDate
                ) {

                    return true;

                }


                let rowDate =
                    parseReportDate(row.date);


                /*
                | Cannot parse row date
                */

                if (!rowDate) {

                    return false;

                }


                rowDate.setHours(
                    0,
                    0,
                    0,
                    0
                );


                /*
                | FROM
                */

                if (fromDate) {

                    let from =
                        new Date(fromDate);

                    from.setHours(
                        0,
                        0,
                        0,
                        0
                    );


                    if (
                        rowDate < from
                    ) {

                        return false;

                    }

                }


                /*
                | TO
                */

                if (toDate) {

                    let to =
                        new Date(toDate);

                    to.setHours(
                        0,
                        0,
                        0,
                        0
                    );


                    if (
                        rowDate > to
                    ) {

                        return false;

                    }

                }


                return true;

            }


            /*
            |--------------------------------------------------------------------------
            | GET FILTERED DATA
            |--------------------------------------------------------------------------
            |
            | Uses:
            | 1. Date filter
            | 2. DataTables search
            |
            */

            function getFilteredReportData() {

                let filtered =
                    reportData.filter(function (row) {

                        return isDateInRange(row);

                    });


                /*
                |--------------------------------------------------------------------------
                | DataTables GLOBAL SEARCH
                |--------------------------------------------------------------------------
                */

                let searchValue =
                    table.search()
                        .trim()
                        .toLowerCase();


                if (searchValue !== '') {

                    filtered =
                        filtered.filter(function (row) {

                            let searchableText = [

                                row.date,

                                row.invoice_no,

                                row.party_gstin,

                                row.party_name,

                                row.item_name,

                                row.hsn_code,

                                row.gst_rate,

                                row.qty,

                                row.price,

                                row.taxable_value,

                                row.cgst,

                                row.sgst,

                                row.igst,

                                row.amount

                            ]
                            .map(function (value) {

                                return value === null ||
                                       value === undefined
                                    ? ''
                                    : String(value);

                            })
                            .join(' ')
                            .toLowerCase();


                            return searchableText
                                .includes(searchValue);

                        });

                }


                return filtered;

            }


            /*
            |--------------------------------------------------------------------------
            | REMOVE DUPLICATE INVOICES
            |--------------------------------------------------------------------------
            */

            function getInvoiceRows(rows) {

                const invoiceMap = new Map();


                rows.forEach(function (row) {

                    let invoiceNo =
                        row.invoice_no || '-';


                    /*
                    |--------------------------------------------------------------------------
                    | If multiple items belong to same invoice,
                    | aggregate them into one invoice row.
                    |--------------------------------------------------------------------------
                    */

                    if (!invoiceMap.has(invoiceNo)) {

                        invoiceMap.set(
                            invoiceNo,
                            {

                                date:
                                    row.date || '',

                                invoice_no:
                                    invoiceNo,

                                party_gstin:
                                    row.party_gstin || '',

                                party_name:
                                    row.party_name || '',

                                taxable_value:
                                    numberValue(
                                        row.taxable_value
                                    ),

                                cgst:
                                    numberValue(
                                        row.cgst
                                    ),

                                sgst:
                                    numberValue(
                                        row.sgst
                                    ),

                                igst:
                                    numberValue(
                                        row.igst
                                    ),

                                amount:
                                    numberValue(
                                        row.amount
                                    )

                            }
                        );

                    } else {

                        let invoice =
                            invoiceMap.get(invoiceNo);


                        invoice.taxable_value +=
                            numberValue(
                                row.taxable_value
                            );


                        invoice.cgst +=
                            numberValue(
                                row.cgst
                            );


                        invoice.sgst +=
                            numberValue(
                                row.sgst
                            );


                        invoice.igst +=
                            numberValue(
                                row.igst
                            );


                        /*
                        |--------------------------------------------------------------------------
                        | IMPORTANT
                        |--------------------------------------------------------------------------
                        |
                        | If amount is an invoice total repeated on every
                        | item row, do NOT add it repeatedly.
                        |
                        | We keep invoice total based on first row.
                        |
                        */

                    }

                });


                /*
                |--------------------------------------------------------------------------
                | Recalculate invoice total
                |--------------------------------------------------------------------------
                |
                | Taxable + CGST + SGST + IGST
                |
                */

                invoiceMap.forEach(function (invoice) {

                    invoice.amount =
                        invoice.taxable_value +
                        invoice.cgst +
                        invoice.sgst +
                        invoice.igst;

                });


                return Array.from(
                    invoiceMap.values()
                );

            }


            /*
            |--------------------------------------------------------------------------
            | TAB 1 - SALES
            |--------------------------------------------------------------------------
            */

            function buildSalesSheet(rows) {

                let invoices =
                    getInvoiceRows(rows);


                let data = [

                    [
                        'Date',
                        'Invoice No',
                        'Customer GSTIN',
                        'Customer',
                        'Taxable Value',
                        'GST %',
                        'CGST',
                        'SGST',
                        'IGST',
                        'Invoice Total'
                    ]

                ];


                invoices.forEach(function (invoice) {

                    /*
                    |--------------------------------------------------------------------------
                    | GST %
                    |--------------------------------------------------------------------------
                    | For invoice-level sheet, derive from
                    | taxable/tax values where possible.
                    |--------------------------------------------------------------------------
                    */

                    let totalGST =
                        invoice.cgst +
                        invoice.sgst +
                        invoice.igst;


                    let gstRate =
                        invoice.taxable_value > 0
                            ? (
                                totalGST /
                                invoice.taxable_value
                            ) * 100
                            : 0;


                    data.push([

                        invoice.date,

                        invoice.invoice_no,

                        invoice.party_gstin || '',

                        invoice.party_name || '',

                        Number(
                            invoice.taxable_value.toFixed(2)
                        ),

                        Number(
                            gstRate.toFixed(2)
                        ),

                        Number(
                            invoice.cgst.toFixed(2)
                        ),

                        Number(
                            invoice.sgst.toFixed(2)
                        ),

                        Number(
                            invoice.igst.toFixed(2)
                        ),

                        Number(
                            invoice.amount.toFixed(2)
                        )

                    ]);

                });


                return data;

            }


            /*
            |--------------------------------------------------------------------------
            | TAB 2 - B2B
            |--------------------------------------------------------------------------
            */

            function buildB2BSheet(rows) {

                let invoices =
                    getInvoiceRows(rows);


                let data = [

                    [
                        'Invoice No',
                        'Date',
                        'Customer',
                        'GSTIN',
                        'Taxable Value',
                        'CGST',
                        'SGST',
                        'IGST',
                        'Total'
                    ]

                ];


                invoices
                    .filter(function (invoice) {

                        return (
                            invoice.party_gstin &&
                            String(
                                invoice.party_gstin
                            ).trim() !== ''
                        );

                    })
                    .forEach(function (invoice) {

                        data.push([

                            invoice.invoice_no,

                            invoice.date,

                            invoice.party_name || '',

                            invoice.party_gstin,

                            Number(
                                invoice.taxable_value
                                    .toFixed(2)
                            ),

                            Number(
                                invoice.cgst
                                    .toFixed(2)
                            ),

                            Number(
                                invoice.sgst
                                    .toFixed(2)
                            ),

                            Number(
                                invoice.igst
                                    .toFixed(2)
                            ),

                            Number(
                                invoice.amount
                                    .toFixed(2)
                            )

                        ]);

                    });


                return data;

            }


            /*
            |--------------------------------------------------------------------------
            | TAB 3 - B2C
            |--------------------------------------------------------------------------
            */

            function buildB2CSheet(rows) {

                let invoices =
                    getInvoiceRows(rows);


                let data = [

                    [
                        'Invoice No',
                        'Date',
                        'Customer',
                        'Taxable Value',
                        'GST',
                        'Total'
                    ]

                ];


                invoices
                    .filter(function (invoice) {

                        return (
                            !invoice.party_gstin ||
                            String(
                                invoice.party_gstin
                            ).trim() === ''
                        );

                    })
                    .forEach(function (invoice) {

                        let totalGST =
                            invoice.cgst +
                            invoice.sgst +
                            invoice.igst;


                        data.push([

                            invoice.invoice_no,

                            invoice.date,

                            invoice.party_name || '',

                            Number(
                                invoice.taxable_value
                                    .toFixed(2)
                            ),

                            Number(
                                totalGST
                                    .toFixed(2)
                            ),

                            Number(
                                invoice.amount
                                    .toFixed(2)
                            )

                        ]);

                    });


                return data;

            }


            /*
            |--------------------------------------------------------------------------
            | TAB 4 - HSN SUMMARY
            |--------------------------------------------------------------------------
            */

            function buildHSNSheet(rows) {

                const hsnMap = new Map();


                rows.forEach(function (row) {

                    let hsnCode =
                        row.hsn_code || 'N/A';


                    let itemName =
                        row.item_name || '';


                    /*
                    |--------------------------------------------------------------------------
                    | HSN + Item combination
                    |--------------------------------------------------------------------------
                    */

                    let key =
                        hsnCode +
                        '|' +
                        itemName;


                    if (!hsnMap.has(key)) {

                        hsnMap.set(
                            key,
                            {

                                hsn_code:
                                    hsnCode,

                                item_name:
                                    itemName,

                                qty: 0,

                                taxable_value: 0,

                                gst_rate: 0,

                                cgst: 0,

                                sgst: 0,

                                igst: 0

                            }
                        );

                    }


                    let hsn =
                        hsnMap.get(key);


                    hsn.qty +=
                        numberValue(
                            row.qty
                        );


                    hsn.taxable_value +=
                        numberValue(
                            row.taxable_value
                        );


                    hsn.cgst +=
                        numberValue(
                            row.cgst
                        );


                    hsn.sgst +=
                        numberValue(
                            row.sgst
                        );


                    hsn.igst +=
                        numberValue(
                            row.igst
                        );


                    /*
                    |--------------------------------------------------------------------------
                    | Keep GST rate
                    |--------------------------------------------------------------------------
                    */

                    let currentRate =
                        numberValue(
                            row.gst_rate
                        );


                    if (
                        currentRate >
                        hsn.gst_rate
                    ) {

                        hsn.gst_rate =
                            currentRate;

                    }

                });


                let data = [

                    [
                        'HSN Code',
                        'Item',
                        'Qty',
                        'Taxable Value',
                        'GST %',
                        'CGST',
                        'SGST',
                        'IGST'
                    ]

                ];


                hsnMap.forEach(function (hsn) {

                    data.push([

                        hsn.hsn_code,

                        hsn.item_name,

                        Number(
                            hsn.qty.toFixed(2)
                        ),

                        Number(
                            hsn.taxable_value
                                .toFixed(2)
                        ),

                        Number(
                            hsn.gst_rate
                                .toFixed(2)
                        ),

                        Number(
                            hsn.cgst
                                .toFixed(2)
                        ),

                        Number(
                            hsn.sgst
                                .toFixed(2)
                        ),

                        Number(
                            hsn.igst
                                .toFixed(2)
                        )

                    ]);

                });


                return data;

            }


            /*
            |--------------------------------------------------------------------------
            | CALCULATION TOTALS
            |--------------------------------------------------------------------------
            */

            function calculateTotals(rows) {

                let totals = {

                    taxable: 0,

                    cgst: 0,

                    sgst: 0,

                    igst: 0,

                    gst: 0,

                    total: 0

                };


                rows.forEach(function (row) {

                    totals.taxable +=
                        numberValue(
                            row.taxable_value
                        );


                    totals.cgst +=
                        numberValue(
                            row.cgst
                        );


                    totals.sgst +=
                        numberValue(
                            row.sgst
                        );


                    totals.igst +=
                        numberValue(
                            row.igst
                        );

                });


                totals.gst =
                    totals.cgst +
                    totals.sgst +
                    totals.igst;


                /*
                |--------------------------------------------------------------------------
                | Use invoice-level totals
                |--------------------------------------------------------------------------
                */

                let invoices =
                    getInvoiceRows(rows);


                totals.total =
                    invoices.reduce(
                        function (
                            sum,
                            invoice
                        ) {

                            return (
                                sum +
                                numberValue(
                                    invoice.amount
                                )
                            );

                        },
                        0
                    );


                return totals;

            }


            /*
            |--------------------------------------------------------------------------
            | TAB 5 - CALCULATION
            |--------------------------------------------------------------------------
            */

            function buildCalculationSheet(rows) {

                let totals =
                    calculateTotals(rows);


                let data = [

                    [
                        'Particular',
                        'Amount'
                    ],

                    [
                        'Total Taxable Value',
                        Number(
                            totals.taxable
                                .toFixed(2)
                        )
                    ],

                    [
                        'Total CGST',
                        Number(
                            totals.cgst
                                .toFixed(2)
                        )
                    ],

                    [
                        'Total SGST',
                        Number(
                            totals.sgst
                                .toFixed(2)
                        )
                    ],

                    [
                        'Total IGST',
                        Number(
                            totals.igst
                                .toFixed(2)
                        )
                    ],

                    [
                        'Total GST',
                        Number(
                            totals.gst
                                .toFixed(2)
                        )
                    ],

                    [
                        'Invoice Total',
                        Number(
                            totals.total
                                .toFixed(2)
                        )
                    ]

                ];


                return data;

            }


            /*
            |--------------------------------------------------------------------------
            | TAB 6 - AUDIT / RECONCILIATION
            |--------------------------------------------------------------------------
            */

            function buildAuditSheet(rows) {

                let totals =
                    calculateTotals(rows);


                /*
                |--------------------------------------------------------------------------
                | HSN totals
                |--------------------------------------------------------------------------
                */

                let hsnData =
                    buildHSNSheet(rows);


                let hsnTaxable = 0;

                let hsnCGST = 0;

                let hsnSGST = 0;

                let hsnIGST = 0;


                /*
                |--------------------------------------------------------------------------
                | Skip header row
                |--------------------------------------------------------------------------
                */

                for (
                    let i = 1;
                    i < hsnData.length;
                    i++
                ) {

                    hsnTaxable +=
                        numberValue(
                            hsnData[i][3]
                        );


                    hsnCGST +=
                        numberValue(
                            hsnData[i][5]
                        );


                    hsnSGST +=
                        numberValue(
                            hsnData[i][6]
                        );


                    hsnIGST +=
                        numberValue(
                            hsnData[i][7]
                        );

                }


                let hsnGST =
                    hsnCGST +
                    hsnSGST +
                    hsnIGST;


                /*
                |--------------------------------------------------------------------------
                | Helper
                |--------------------------------------------------------------------------
                */

                function auditRow(
                    particular,
                    invoiceTotal,
                    reportTotal
                ) {

                    let difference =
                        invoiceTotal -
                        reportTotal;


                    /*
                    |--------------------------------------------------------------------------
                    | Floating point protection
                    |--------------------------------------------------------------------------
                    */

                    difference =
                        Math.round(
                            difference * 100
                        ) / 100;


                    let status =
                        Math.abs(difference) < 0.01
                            ? 'Matched'
                            : 'Mismatch';


                    return [

                        particular,

                        Number(
                            invoiceTotal.toFixed(2)
                        ),

                        Number(
                            reportTotal.toFixed(2)
                        ),

                        Number(
                            difference.toFixed(2)
                        ),

                        status

                    ];

                }


                let data = [

                    [
                        'Particular',
                        'Invoice Total',
                        'Report Total',
                        'Difference',
                        'Status'
                    ],


                    /*
                    |--------------------------------------------------------------------------
                    | TAXABLE
                    |--------------------------------------------------------------------------
                    */

                    auditRow(
                        'Taxable Value',
                        totals.taxable,
                        hsnTaxable
                    ),


                    /*
                    |--------------------------------------------------------------------------
                    | CGST
                    |--------------------------------------------------------------------------
                    */

                    auditRow(
                        'CGST',
                        totals.cgst,
                        hsnCGST
                    ),


                    /*
                    |--------------------------------------------------------------------------
                    | SGST
                    |--------------------------------------------------------------------------
                    */

                    auditRow(
                        'SGST',
                        totals.sgst,
                        hsnSGST
                    ),


                    /*
                    |--------------------------------------------------------------------------
                    | IGST
                    |--------------------------------------------------------------------------
                    */

                    auditRow(
                        'IGST',
                        totals.igst,
                        hsnIGST
                    ),


                    /*
                    |--------------------------------------------------------------------------
                    | TOTAL GST
                    |--------------------------------------------------------------------------
                    */

                    auditRow(
                        'Total GST',
                        totals.gst,
                        hsnGST
                    )

                ];


                /*
                |--------------------------------------------------------------------------
                | OVERALL STATUS
                |--------------------------------------------------------------------------
                */

                let allMatched =
                    data
                        .slice(1)
                        .every(function (row) {

                            return row[4] === 'Matched';

                        });


                data.push([

                    'OVERALL STATUS',

                    '',

                    '',

                    '',

                    allMatched
                        ? 'Matched'
                        : 'Mismatch'

                ]);


                return data;

            }


            /*
            |--------------------------------------------------------------------------
            | FORMAT WORKSHEET
            |--------------------------------------------------------------------------
            */

            function formatWorksheet(
                worksheet,
                columnWidths
            ) {

                worksheet['!cols'] =
                    columnWidths.map(
                        function (width) {

                            return {
                                wch: width
                            };

                        }
                    );


                /*
                |--------------------------------------------------------------------------
                | Freeze header row
                |--------------------------------------------------------------------------
                */

                worksheet['!freeze'] = {

                    xSplit: 0,

                    ySplit: 1

                };

            }


            /*
            |--------------------------------------------------------------------------
            | CREATE WORKBOOK
            |--------------------------------------------------------------------------
            */

            /*
            |--------------------------------------------------------------------------
            | LOAD ORIGINAL FORMAT.XLSX
            |--------------------------------------------------------------------------
            |
            | IMPORTANT:
            | This does NOT create a new workbook.
            | It loads the real FORMAT.xlsx so ALL existing tabs remain.
            |
            | Put the exact FORMAT.xlsx here:
            |
            | public/excel/FORMAT.xlsx
            |
            |--------------------------------------------------------------------------
            */
            async function loadFormatWorkbook() {

                const response = await fetch(
                    "{{ asset('excel/FORMAT.xlsx') }}",
                    {
                        method: 'GET',
                        cache: 'no-store'
                    }
                );

                if (!response.ok) {

                    throw new Error(
                        'FORMAT.xlsx not found. Please upload the original FORMAT.xlsx to public/excel/FORMAT.xlsx'
                    );

                }

                const buffer =
                    await response.arrayBuffer();

                return XLSX.read(
                    buffer,
                    {
                        type: 'array',
                        cellStyles: true,
                        cellDates: true,
                        cellNF: true,
                        cellFormula: true
                    }
                );

            }


            /*
            |--------------------------------------------------------------------------
            | CLEAR DATA ONLY
            |--------------------------------------------------------------------------
            |
            | Header row is preserved.
            | Existing FORMAT tabs are NOT removed.
            |
            |--------------------------------------------------------------------------
            */
            function clearSheetData(
                worksheet,
                headerRow = 1
            ) {

                if (!worksheet) {
                    return;
                }

                const ref =
                    worksheet['!ref'];

                if (!ref) {
                    return;
                }

                const range =
                    XLSX.utils.decode_range(ref);

                /*
                |--------------------------------------------------------------------------
                | Delete rows after the header
                |--------------------------------------------------------------------------
                */
                for (
                    let r = headerRow;
                    r <= range.e.r;
                    r++
                ) {

                    for (
                        let c = range.s.c;
                        c <= range.e.c;
                        c++
                    ) {

                        const address =
                            XLSX.utils.encode_cell({
                                r: r,
                                c: c
                            });

                        delete worksheet[address];

                    }

                }

                /*
                |--------------------------------------------------------------------------
                | Keep original header row
                |--------------------------------------------------------------------------
                */
                worksheet['!ref'] =
                    XLSX.utils.encode_range({
                        s: {
                            r: range.s.r,
                            c: range.s.c
                        },
                        e: {
                            r: headerRow - 1,
                            c: range.e.c
                        }
                    });

            }


            /*
            |--------------------------------------------------------------------------
            | WRITE ARRAY DATA INTO EXISTING FORMAT SHEET
            |--------------------------------------------------------------------------
            |
            | This writes below the existing FORMAT header.
            | It never creates a new sheet.
            |
            |--------------------------------------------------------------------------
            */
            function writeDataToExistingSheet(
                workbook,
                sheetName,
                data,
                headerRow = 1
            ) {

                const worksheet =
                    workbook.Sheets[sheetName];

                if (!worksheet) {

                    console.warn(
                        'FORMAT sheet not found:',
                        sheetName
                    );

                    return;

                }

                if (
                    !Array.isArray(data) ||
                    data.length === 0
                ) {

                    return;

                }

                clearSheetData(
                    worksheet,
                    headerRow
                );

                const startRow =
                    headerRow;

                const startColumn = 0;

                for (
                    let r = 0;
                    r < data.length;
                    r++
                ) {

                    const row =
                        data[r] || [];

                    for (
                        let c = 0;
                        c < row.length;
                        c++
                    ) {

                        const value =
                            row[c];

                        const address =
                            XLSX.utils.encode_cell({
                                r:
                                    startRow + r,
                                c:
                                    startColumn + c
                            });

                        if (
                            value instanceof Date
                        ) {

                            worksheet[address] = {
                                v: value,
                                t: 'd'
                            };

                        } else if (
                            typeof value === 'number'
                        ) {

                            worksheet[address] = {
                                v: value,
                                t: 'n'
                            };

                        } else {

                            worksheet[address] = {
                                v:
                                    value === null ||
                                    value === undefined
                                        ? ''
                                        : String(value),
                                t: 's'
                            };

                        }

                    }

                }

                /*
                |--------------------------------------------------------------------------
                | Extend worksheet range
                |--------------------------------------------------------------------------
                */
                const existingRef =
                    worksheet['!ref'];

                let range;

                if (existingRef) {

                    range =
                        XLSX.utils.decode_range(
                            existingRef
                        );

                } else {

                    range = {
                        s: {
                            r: 0,
                            c: 0
                        },
                        e: {
                            r: 0,
                            c: 0
                        }
                    };

                }

                range.e.r =
                    Math.max(
                        range.e.r,
                        startRow +
                        data.length -
                        1
                    );

                range.e.c =
                    Math.max(
                        range.e.c,
                        data.reduce(
                            function (
                                max,
                                row
                            ) {
                                return Math.max(
                                    max,
                                    (row || []).length - 1
                                );
                            },
                            0
                        )
                    );

                worksheet['!ref'] =
                    XLSX.utils.encode_range(
                        range
                    );

            }


            /*
            |--------------------------------------------------------------------------
            | FORMAT SHEET HELPER
            |--------------------------------------------------------------------------
            |
            | Adds data to the EXISTING FORMAT tab.
            | If a sheet does not exist, it is skipped.
            |
            |--------------------------------------------------------------------------
            */
            function populateFormatSheet(
                workbook,
                sheetName,
                data
            ) {

                if (
                    !workbook.Sheets[sheetName]
                ) {

                    console.warn(
                        'Skipping missing FORMAT tab:',
                        sheetName
                    );

                    return;

                }

                writeDataToExistingSheet(
                    workbook,
                    sheetName,
                    data,
                    1
                );

            }


            /*
            |--------------------------------------------------------------------------
            | CREATE GST WORKBOOK FROM ORIGINAL FORMAT
            |--------------------------------------------------------------------------
            */
            async function createGSTWorkbook(rows) {

                /*
                |--------------------------------------------------------------------------
                | LOAD ORIGINAL FORMAT.xlsx
                |--------------------------------------------------------------------------
                */
                const workbook =
                    await loadFormatWorkbook();


                /*
                |--------------------------------------------------------------------------
                | IMPORTANT
                |--------------------------------------------------------------------------
                |
                | We DO NOT call:
                |
                | XLSX.utils.book_new()
                |
                | We DO NOT call:
                |
                | XLSX.utils.book_append_sheet()
                |
                | Therefore all tabs already present in FORMAT.xlsx
                | remain in the final downloaded workbook.
                |
                |--------------------------------------------------------------------------
                */


                /*
                |--------------------------------------------------------------------------
                | B2B
                |--------------------------------------------------------------------------
                |
                | Existing FORMAT tab:
                | b2b,sez,de
                |
                */
                populateFormatSheet(
                    workbook,
                    'b2b,sez,de',
                    buildB2BSheet(rows).slice(1)
                );


                /*
                |--------------------------------------------------------------------------
                | B2CS
                |--------------------------------------------------------------------------
                |
                | Existing FORMAT tab:
                | b2cs
                |
                */
                populateFormatSheet(
                    workbook,
                    'b2cs',
                    buildB2CSheet(rows).slice(1)
                );


                /*
                |--------------------------------------------------------------------------
                | HSN B2B
                |--------------------------------------------------------------------------
                |
                | Existing FORMAT tab:
                | hsn(b2b)
                |
                */
                const hsnB2BRows =
                    buildHSNSheet(
                        rows.filter(
                            function (row) {

                                return (
                                    row.party_gstin &&
                                    String(
                                        row.party_gstin
                                    ).trim() !== ''
                                );

                            }
                        )
                    );

                populateFormatSheet(
                    workbook,
                    'hsn(b2b)',
                    hsnB2BRows.slice(1)
                );


                /*
                |--------------------------------------------------------------------------
                | HSN B2C
                |--------------------------------------------------------------------------
                |
                | Existing FORMAT tab:
                | hsn(b2c)
                |
                */
                const hsnB2CRows =
                    buildHSNSheet(
                        rows.filter(
                            function (row) {

                                return (
                                    !row.party_gstin ||
                                    String(
                                        row.party_gstin
                                    ).trim() === ''
                                );

                            }
                        )
                    );

                populateFormatSheet(
                    workbook,
                    'hsn(b2c)',
                    hsnB2CRows.slice(1)
                );


                /*
                |--------------------------------------------------------------------------
                | CALCULATION
                |--------------------------------------------------------------------------
                |
                | Only populate if your FORMAT.xlsx contains this tab.
                | It is NOT created if absent.
                |
                |--------------------------------------------------------------------------
                */
                populateFormatSheet(
                    workbook,
                    'Calculation',
                    buildCalculationSheet(rows).slice(1)
                );


                /*
                |--------------------------------------------------------------------------
                | AUDIT
                |--------------------------------------------------------------------------
                |
                | Only populate if your FORMAT.xlsx contains this tab.
                | It is NOT created if absent.
                |
                |--------------------------------------------------------------------------
                */
                populateFormatSheet(
                    workbook,
                    'Audit',
                    buildAuditSheet(rows).slice(1)
                );


                /*
                |--------------------------------------------------------------------------
                | EXEMPT
                |--------------------------------------------------------------------------
                |
                | FORMAT.xlsx uses "exemp".
                |
                | We intentionally do NOT create "Exempt".
                |
                |--------------------------------------------------------------------------
                */
                if (
                    workbook.Sheets['exemp']
                ) {

                    const exemptRows = [
                        [
                            'Inter-State supplies to registered persons',
                            0,
                            0,
                            0
                        ],
                        [
                            'Intra-State supplies to registered persons',
                            0,
                            0,
                            0
                        ],
                        [
                            'Inter-State supplies to unregistered persons',
                            0,
                            0,
                            0
                        ],
                        [
                            'Intra-State supplies to unregistered persons',
                            0,
                            0,
                            0
                        ]
                    ];

                    populateFormatSheet(
                        workbook,
                        'exemp',
                        exemptRows
                    );

                }


                /*
                |--------------------------------------------------------------------------
                | PRINT ALL ORIGINAL SHEET NAMES
                |--------------------------------------------------------------------------
                */
                console.log(
                    'FORMAT.xlsx tabs retained:',
                    workbook.SheetNames
                );


                /*
                |--------------------------------------------------------------------------
                | RETURN ORIGINAL MULTI-SHEET WORKBOOK
                |--------------------------------------------------------------------------
                */
                return workbook;

            }


            /*
            |--------------------------------------------------------------------------
            | GENERATE FILE NAME
            |--------------------------------------------------------------------------
            */

            function getExcelFileName() {

                let fromDate =
                    $('#fromDate').val();

                let toDate =
                    $('#toDate').val();


                /*
                |--------------------------------------------------------------------------
                | Both dates
                |--------------------------------------------------------------------------
                */

                if (
                    fromDate &&
                    toDate
                ) {

                    return (
                        'GST_Report_' +
                        fromDate +
                        '_to_' +
                        toDate +
                        '.xlsx'
                    );

                }


                /*
                |--------------------------------------------------------------------------
                | From only
                |--------------------------------------------------------------------------
                */

                if (fromDate) {

                    return (
                        'GST_Report_From_' +
                        fromDate +
                        '.xlsx'
                    );

                }


                /*
                |--------------------------------------------------------------------------
                | To only
                |--------------------------------------------------------------------------
                */

                if (toDate) {

                    return (
                        'GST_Report_Upto_' +
                        toDate +
                        '.xlsx'
                    );

                }


                /*
                |--------------------------------------------------------------------------
                | Current month
                |--------------------------------------------------------------------------
                */

                let now =
                    new Date();


                let month =
                    now.toLocaleString(
                        'en-US',
                        {
                            month: 'long'
                        }
                    );


                let year =
                    now.getFullYear();


                return (
                    'GST_Report_' +
                    month +
                    '_' +
                    year +
                    '.xlsx'
                );

            }


            /*
            |--------------------------------------------------------------------------
            | EXPORT EXCEL
            |--------------------------------------------------------------------------
            */

            $('#excelBtn').on(
                'click',
                async function () {

                    let button =
                        $(this);


                    /*
                    |--------------------------------------------------------------------------
                    | Prevent duplicate clicks
                    |--------------------------------------------------------------------------
                    */
                    if (
                        button.prop('disabled')
                    ) {

                        return;

                    }


                    /*
                    |--------------------------------------------------------------------------
                    | Get filtered records
                    |--------------------------------------------------------------------------
                    */
                    let filteredRows =
                        getFilteredReportData();


                    /*
                    |--------------------------------------------------------------------------
                    | No records
                    |--------------------------------------------------------------------------
                    */
                    if (
                        filteredRows.length === 0
                    ) {

                        alert(
                            'No GST sales data available for export.'
                        );

                        return;

                    }


                    /*
                    |--------------------------------------------------------------------------
                    | Loading state
                    |--------------------------------------------------------------------------
                    */
                    let originalHtml =
                        button.html();

                    button.prop(
                        'disabled',
                        true
                    );

                    button.html(
                        '<i class="fas fa-spinner fa-spin"></i> Preparing Excel...'
                    );


                    try {

                        /*
                        |--------------------------------------------------------------------------
                        | Load ORIGINAL FORMAT.xlsx
                        |--------------------------------------------------------------------------
                        */
                        let workbook =
                            await createGSTWorkbook(
                                filteredRows
                            );


                        /*
                        |--------------------------------------------------------------------------
                        | File name
                        |--------------------------------------------------------------------------
                        */
                        let fileName =
                            getExcelFileName();


                        /*
                        |--------------------------------------------------------------------------
                        | DOWNLOAD
                        |--------------------------------------------------------------------------
                        |
                        | The resulting file is downloaded to the user's
                        | normal browser Downloads folder.
                        |
                        |--------------------------------------------------------------------------
                        */
                        XLSX.writeFile(
                            workbook,
                            fileName,
                            {
                                bookType: 'xlsx',
                                compression: true
                            }
                        );


                    } catch (error) {

                        console.error(
                            'GST Excel Export Error:',
                            error
                        );


                        alert(
                            'Unable to generate GST Excel report.\n\n' +
                            error.message
                        );


                    } finally {

                        button.prop(
                            'disabled',
                            false
                        );

                        button.html(
                            originalHtml
                        );

                    }

                }
            );


            /*
            |--------------------------------------------------------------------------
            | DATATABLE DATE FILTER
            |--------------------------------------------------------------------------
            */

            $.fn.dataTable.ext.search.push(

                function (
                    settings,
                    data,
                    dataIndex
                ) {

                    /*
                    |--------------------------------------------------------------------------
                    | Only GST table
                    |--------------------------------------------------------------------------
                    */

                    if (
                        settings.nTable.id !==
                        'gstSalesTable'
                    ) {

                        return true;

                    }


                    let fromDate =
                        $('#fromDate').val();


                    let toDate =
                        $('#toDate').val();


                    let dateText =
                        data[0];


                    /*
                    |--------------------------------------------------------------------------
                    | No date filter
                    |--------------------------------------------------------------------------
                    */

                    if (
                        !fromDate &&
                        !toDate
                    ) {

                        return true;

                    }


                    if (
                        !dateText ||
                        dateText === '-'
                    ) {

                        return false;

                    }


                    let rowDate =
                        parseReportDate(
                            dateText
                        );


                    if (!rowDate) {

                        return false;

                    }


                    rowDate.setHours(
                        0,
                        0,
                        0,
                        0
                    );


                    /*
                    |--------------------------------------------------------------------------
                    | FROM DATE
                    |--------------------------------------------------------------------------
                    */

                    if (fromDate) {

                        let from =
                            new Date(
                                fromDate
                            );


                        from.setHours(
                            0,
                            0,
                            0,
                            0
                        );


                        if (
                            rowDate < from
                        ) {

                            return false;

                        }

                    }


                    /*
                    |--------------------------------------------------------------------------
                    | TO DATE
                    |--------------------------------------------------------------------------
                    */

                    if (toDate) {

                        let to =
                            new Date(
                                toDate
                            );


                        to.setHours(
                            0,
                            0,
                            0,
                            0
                        );


                        if (
                            rowDate > to
                        ) {

                            return false;

                        }

                    }


                    return true;

                }

            );


            /*
            |--------------------------------------------------------------------------
            | FILTER BUTTON
            |--------------------------------------------------------------------------
            */

            $('#filterBtn').on(
                'click',
                function () {

                    table.draw();

                }
            );


            /*
            |--------------------------------------------------------------------------
            | RESET BUTTON
            |--------------------------------------------------------------------------
            */

            $('#resetBtn').on(
                'click',
                function () {

                    $('#fromDate').val('');

                    $('#toDate').val('');

                    table.search('');

                    table.draw();

                }
            );


            /*
            |--------------------------------------------------------------------------
            | DATE CHANGE
            |--------------------------------------------------------------------------
            */

            $('#fromDate, #toDate').on(
                'change',
                function () {

                    table.draw();

                }
            );

        });

    </script>

@endsection