@extends('layouts.excel')
@push('style')
<style>
    .x-spreadsheet-toolbar{
        width:100% !important;
    }
    /* .jss_worksheet > tbody > tr > td.readonly {
        color: #000 !important;
    } */
    .jss_worksheet > tbody > tr > td {
        line-height: normal !important;
    }
    .jss_worksheet > thead > tr > td {
        text-align: center !important;
    }
    .jss_worksheet > tfoot > tr > td {
        text-align: center !important;
        font-weight: bold;
        background-color: transparent !important
    }
    .jss_worksheet > tfoot > tr > td:first-child {
        background-color: #f3f3f3 !important
    }
    .jss_worksheet > tfoot > tr > td:last-child {
        border-top: 1px solid #000;
        border-bottom: 1px solid #000;
        border-bottom: 3px solid #000;
        border-bottom-style: double;
    }
    .jss_worksheet thead > tr:last-child {
        text-align: center !important;
        font-weight: bold;

    }
    .jss_worksheet thead > tr:last-child > td {
        background-color: #fff !important
    }
    .jss_worksheet thead > tr:last-child > td:first-child {
        background-color: #f3f3f3 !important
    }

    #spreadsheet-preview .jss_worksheet thead > tr:first-child {
        opacity: 0;
    }

    #spreadsheet-preview .jss_worksheet tbody > tr > td:first-child{
        opacity: 0 !important;
    }
    #spreadsheet-preview .jss_worksheet tfoot > tr > td:first-child{
        opacity: 0 !important;
    }
    #spreadsheet-preview .jss_worksheet tbody > tr > td:nth-child(2){
        opacity: 0;
    }
    #table-container .jss_worksheet {
        table-layout: auto !important;
        min-width: 800px;
    }
    #spreadsheet-preview .jss_worksheet.jss_overflow{
        border-bottom:none;
    }

    #spreadsheet-preview .jss_worksheet tfoot > tr > td:not(:first-child){
        border-bottom: 1px solid #ccc;
    }

    .hidden-header{
        display: none;
    }

</style>
@endpush
@section('content')
<div class="row mt-4">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <div class="row">
                    <div class="col"><h3>Tender owner schedule of work excel<h3></div>
                    <div class="col">
                        <button id="save" class="btn btn-primary float-end">Save</button>
                        {{--<a href="{{route('showUploadExl')}}" class="btn btn-danger mx-2 float-end">Cancel</a> --}}
                    </div>
                </div>
            </div>
            <div class="card-body">
                <div id='spreadsheet'></div>
                <div class="my-2 p-2"><small class="error text-danger"></small></div>
                <button id="preview" class="btn btn-primary mx-2 float-end">Preview</button>
                <button class="btn btn-primary" id="btn1">Add Row</button>
                <button class="btn btn-danger" id="removeRow" style="display:none;">Remove Row</button>
            </div>
        </div>
        <div class="card previewdiv mt-3" id="previewdiv" style="display:none;">
            <div class="card-header">
                <h6>Preview</h6>
            </div>
            <div class="card-body">
                <div id='spreadsheet-preview' style="width:100%;"></div>
            </div>
        </div>
    </div>
</div>
@endsection
@push('script')
<script>
    let rowSelection = [];
    // Format Pricing to comma seprated
    function formatPricing(value){
        let price = value == '' ? 0:value;
        let formatted = parseInt(price).toFixed(2).replace(/\d(?=(\d{3})+\.)/g, '$&,');
        return formatted;
    }
    // Format pricing to from comma-seprated to decimal
    function roundToOneDecimal(value) {
        return Math.round(value * 10) / 10;
    }
    // Regenrate the reference after selected row
    function regenrateReferenceAfterSelectedRow(){
        // get worksheet
        let worksheet =  table[0];
        // get length of worksheet
        let rowLength = worksheet.rows.length;

        for(let rows=1;rows<rowLength;rows++){
            let row = worksheet.getRowData([rows]);
            let rowLineType = row[0]
            let rowItemDescription = row[2];
            let rowPricing = row[3];
            // if the rowLineType is group_heading
            if(rowLineType == 'group_heading'){
              
                let previousRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,rows-1);
                let previousRowReferenceValue = worksheet.getValue(previousRowReferenceCellName);
                let currentRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,rows);
                let currentRowReferenceCellValue = worksheet.getValue(1,rows);
                let currentRowReference = parseInt(previousRowReferenceValue) + 1;
                console.log('currentRowReference',currentRowReference);
                if(currentRowReferenceCellValue == null){
                    worksheet.setValue(currentRowReferenceCellName,currentRowReference.toFixed(1),true);
                }
            }else if(rowLineType.length!=0 && rowLineType != 'group_heading'){
                let previousRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,rows-1);
                let previousRowReferenceValue = worksheet.getValue(previousRowReferenceCellName);
                let currentRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,rows);
                let currentRowReferenceCellValue = worksheet.getValue(1,rows);
                let currentRowReference = incrementValue(previousRowReferenceValue);
                if(currentRowReferenceCellValue == null){
                    worksheet.setValue(currentRowReferenceCellName,currentRowReference,true);
                }
            }else{
                let previousRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,rows-1);
                let previousRowReferenceValue = worksheet.getValue(previousRowReferenceCellName);
                let currentRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,rows);
                let currentRowReferenceCellValue = worksheet.getValue(1,rows);
                let currentRowReference = incrementValue(previousRowReferenceValue);
                if(currentRowReferenceCellValue == null){
                    worksheet.setValue(currentRowReferenceCellName,currentRowReference,true);
                }
            }


        }
    }

    // validate the item description and pricing
    function validateRow () {
        // get worksheet
        let worksheet =  table[0];
        // get length of worksheet
        let rowLength = worksheet.rows.length;
        // error count if greater then 0 then return false (for not add the new row)
        let errorCount = 0;
        for(let rows=0;rows<rowLength;rows++){
            let row = worksheet.getRowData([rows]);
            let rowLineType = row[0]
            let rowItemDescription = row[2];
            let rowPricing = row[3];
            if(rowItemDescription == ''){
                // Check if the getItemDescription empty of last row then set background to red
                let getItemDescriptionCellName = jspreadsheet.helpers.getCellNameFromCoords(2,rows);
                let getItemDescriptionCellValue = worksheet.getValue(getItemDescriptionCellName);
                if(getItemDescriptionCellValue==""){
                    worksheet.getCell(getItemDescriptionCellName).style.backgroundColor = 'red';
                }else{
                    worksheet.getCell(getItemDescriptionCellName).style.backgroundColor = 'transparent';
                }
                $('.error').text('Item Description cannot be empty');
                $('#btn1').attr('disabled',true);
                errorCount++;
                // return false;
            }else if(rowLineType=='fixed_priced_item' && rowPricing == ''){
                // Check if the getPricing empty of last row then set background to red
                let getPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,rows);
                let getPricingCellValue = worksheet.getValue(getPricingCellName);
                console.log('getPricingCellValuebefore',getPricingCellValue)
                if(getPricingCellValue!=""){
                    console.log('getPricingCellValuebeforeif',getPricingCellValue)
                    worksheet.getCell(getPricingCellName).style.backgroundColor = 'transparent';
                }else{
                    worksheet.getCell(getPricingCellName).style.backgroundColor = 'red';
                }
                $('.error').text('Pricing is compulsory in fixed_priced_item');
                $('#btn1').attr('disabled',true);
                errorCount++;
                // return false;
            }
            else{
                $('.error').text('');
                $('#btn1').attr('disabled',false);
            }
        }

        // check if the errorCount is greater than 0 then return false(not to add the new row)
        if(errorCount > 0){
            console.log('errorCount',errorCount);
            return false;
        }else{
            rowSelection = [];
            return true;
        }
    }

    // increament value for reference
    function incrementValue(value) {
        let stringValue = value.toString();
        let stringValueParts = stringValue.split(".");
        let NumericNumber = stringValueParts[0];
        let DecimalNumber = stringValueParts[1]?stringValueParts[1]:"0";
        let increamentedDecimalValue = parseInt(DecimalNumber) +1;
        let incrementValue = NumericNumber+"."+increamentedDecimalValue.toString();
        return incrementValue;
    }

    function convertSpecialCharactersToUnicode(inputString) {
        return inputString.replace(/</g, '&#60').replace(/>/g, '&#60');
    }

    // A custom method to SUM all the cells in the current column
    let SUMCOL = function(columnId) {
        let instance = jspreadsheet.current
        let worksheet =  table[0];
        let total = 0;

        for (let j = 0; j < instance.options.data.length-1; j++) {
            let cellYAxis = instance.records[j][columnId].y;
            let getCellName = jspreadsheet.helpers.getCellNameFromCoords(3,cellYAxis);
            let getCellValue = worksheet.getValue(getCellName);
            console.log('checkif',Number(getCellValue));
            if (Number(getCellValue)) {
                total += Number(getCellValue);
            }
        }
        console.log('total',total);
        return formatPricing(total);
    }
    formula.setFormula({ SUMCOL })
    let update = function (instance, cell, x, y, value) {
        console.log('instance',instance);
        console.log('cell',cell);
        console.log('x',x);
        console.log('y',y);
        console.log('value',value);
        let worksheet =  table[0];
       

        // Logic to check group_heading validation
        if(x==0 && y!=0) {

            let previousCellName = jspreadsheet.helpers.getCellNameFromCoords(0,y-1);
            let previousRowValue = worksheet.getValue(previousCellName);
            let currentCellName = jspreadsheet.helpers.getCellNameFromCoords(0,y);
            let currentPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,y);
            let previousCellValue = instance.getValue(previousCellName);
            
            let nextCellName = jspreadsheet.helpers.getCellNameFromCoords(x + 1, y);
            let nextCellValue = instance.getValue(nextCellName);

            if(previousRowValue=='group_heading' && value=="group_heading") {
                $('.error').text('Cannot add group_heading after group_heading');
                $('#btn1').attr('disabled',true);
                worksheet.getCell(currentCellName).style.backgroundColor = 'red';
                console.log('before');
                $('.error').text('Cannot add group_heading after group_heading');
                console.log('after');

            }
            else if(previousRowValue!='group_heading' && value=="group_heading"){

                worksheet.getCell(currentCellName).style.backgroundColor = 'none';
                $('.error').text('');
                $('#btn1').attr('disabled',false);
                // Call the reference number
                $reference = regenrateReferenceAfterSelectedRow();
                // Archived Code
                // let previousRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,y-1);
                // let previousRowReferenceValue = worksheet.getValue(previousRowReferenceCellName);
                // let currentRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,y);
                // let currentRowReferenceCellValue = worksheet.getValue(1,y);
                // let currentRowReference = parseInt(previousRowReferenceValue) + 1;
                // if(currentRowReferenceCellValue == null){
                //     worksheet.setValue(currentRowReferenceCellName,currentRowReference.toFixed(1),true);
                // }

            }
            else{
                worksheet.getCell(currentCellName).style.backgroundColor = 'transparent';
                worksheet.getCell(currentPricingCellName).style.backgroundColor = 'transparent';
                $('.error').text('');
                $('#btn1').attr('disabled',false);
                $reference = regenrateReferenceAfterSelectedRow();
                // Archived Code
                // let previousRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,y-1);
                // let previousRowReferenceValue = worksheet.getValue(previousRowReferenceCellName);
                // let currentRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,y);
                // let currentRowReferenceCellValue = worksheet.getValue(1,y);
                // let currentRowReference = incrementValue(previousRowReferenceValue);
                // if(currentRowReferenceCellValue == null){
                //     worksheet.setValue(currentRowReferenceCellName,currentRowReference,true);
                // }
            }

            if(value=="fixed_priced_item"){
                let currentRowPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,y);
                let beforeRowHeadingCellName = jspreadsheet.helpers.getCellNameFromCoords(0,y-1);
                let beforeRowHeadingCellName2 = jspreadsheet.helpers.getCellNameFromCoords(0,y-2);
                let currentRowItemCellName = jspreadsheet.helpers.getCellNameFromCoords(2,y);
                let currentRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,y);
                let currentRowReferenceValue = worksheet.getValue(currentRowReferenceCellName)
                let currentRowLineTypeCellName = jspreadsheet.helpers.getCellNameFromCoords(0,y);
                
               
                let pricingCell = worksheet.getCell(currentRowPricingCellName);
                let beforeCell = worksheet.getCell(beforeRowHeadingCellName);
                let beforeCell2 = worksheet.getCell(beforeRowHeadingCellName2);
             
                let beforeRowPricingValue = instance.getValue(beforeRowHeadingCellName);
                let beforeRowPricingValue2 = instance.getValue(beforeRowHeadingCellName2);
               
                let itemCell =  worksheet.getCell(currentRowItemCellName);
                let referenceCell =  worksheet.getCell(currentRowReferenceCellName);
                let lineTypeCell =  worksheet.getCell(currentRowLineTypeCellName);
                // parsing the number of y and adding to 1 sos it does not concatinate
                let parseY = Number(y) + 1;
                let afterRowHeadingCellName = jspreadsheet.helpers.getCellNameFromCoords(0,parseY);
                // check if the the data-y exists on frontend
                let cellElement = document.querySelector(`[data-x="0"][data-y="${parseY}"]`);
                if(afterRowHeadingCellName){
                    if(cellElement){
                    var afterRowPricingValue = instance.getValue(afterRowHeadingCellName)
                   var  afterCell = worksheet.getCell(afterRowHeadingCellName);
                    }
                
                }
                    pricingCell.classList.remove('readonly');
                    pricingCell.style.fontStyle = 'italic';
                    pricingCell.style.textAlign = 'right';
                    itemCell.style.fontStyle = 'italic';
                    referenceCell.style.fontStyle = 'italic';
                    lineTypeCell.style.fontStyle = 'italic';
                    lineTypeCell.style.color = 'green';
                    pricingCell.style.fontWeight = 'normal';
                    itemCell.style.fontWeight = 'normal';
                    referenceCell.style.fontWeight = 'normal';
                    lineTypeCell.style.fontWeight = 'normal';   
                // if before row is group heading and its background color is red and the 2 values before is not group heading then update the color to red 
                if(beforeRowPricingValue == 'group_heading' && beforeCell.style.backgroundColor == 'red' && beforeRowPricingValue2 !== "group_heading"){
                   beforeCell.style.backgroundColor = "";
              }
              // if the after element exists on frontend  and it is a group heading and its color is red then update the bg color to none
              if(cellElement){
              if(afterRowPricingValue == 'group_heading' && afterCell.style.backgroundColor == 'red' ){
                afterCell.style.backgroundColor = "";
              } 
            }
               
            }else{
                let beforeRowHeadingCellName = jspreadsheet.helpers.getCellNameFromCoords(0,y-1);
                let beforeRowHeadingCellName2 = jspreadsheet.helpers.getCellNameFromCoords(0,y-2);
                let currentRowPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,y);
                let currentRowItemCellName = jspreadsheet.helpers.getCellNameFromCoords(2,y);
                let currentRowReferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,y);
                let currentRowLineTypeCellName = jspreadsheet.helpers.getCellNameFromCoords(0,y);
                let beforeCell = worksheet.getCell(beforeRowHeadingCellName)
                let beforeRowPricingValue = instance.getValue(beforeRowHeadingCellName);
                let beforeCell2 = worksheet.getCell(beforeRowHeadingCellName2);
                let beforeRowPricingValue2 = instance.getValue(beforeRowHeadingCellName2);
                let itemCell =  worksheet.getCell(currentRowItemCellName);
                let referenceCell =  worksheet.getCell(currentRowReferenceCellName);
                let lineTypeCell =  worksheet.getCell(currentRowLineTypeCellName);
                let pricingCell = worksheet.getCell(currentRowPricingCellName);
                let parseY = Number(y) + 1;
                let afterRowHeadingCellName = jspreadsheet.helpers.getCellNameFromCoords(0,parseY); 
                let cellElement = document.querySelector(`[data-x="0"][data-y="${parseY}"]`);
                if(afterRowHeadingCellName){
                    if(cellElement){
                    var afterRowPricingValue = instance.getValue(afterRowHeadingCellName)
                   var  afterCell = worksheet.getCell(afterRowHeadingCellName);
                    }
                
                }
                // let parseY = Number(y) + 1;
                // let afterRowHeadingCellName = jspreadsheet.helpers.getCellNameFromCoords(0,parseY);   
                // console.log(afterRowHeadingCellName,"after row") 
                // if(afterRowHeadingCellName){
                // let afterRowPricingValue = instance.getValue(afterRowHeadingCellName)
                // let afterCell = worksheet.getCell(afterRowHeadingCellName);
                // }
                // console.log(afterRowPricingValue,"after cell") 
                if(value=="group_heading"){

                    pricingCell.style.backgroundColor = '#eee';
                    pricingCell.style.textAlign = 'center';
                    pricingCell.style.fontWeight = 'bold';
                    itemCell.style.fontWeight = 'bold';
                    referenceCell.style.fontWeight = 'bold';
                    lineTypeCell.style.fontWeight = 'bold';
                    lineTypeCell.style.color = 'black';
                    lineTypeCell.style.fontStyle = 'normal';
                    referenceCell.style.fontStyle = 'normal';
                    itemCell.style.fontStyle = 'normal';
                    pricingCell.style.fontStyle = 'normal';
                    if(cellElement){
              if(afterRowPricingValue == 'group_heading' && afterCell.style.backgroundColor == '' ){
                afterCell.style.backgroundColor = "red";
              } 
            }

                }
                if(value=="priced_item"){
                    lineTypeCell.style.fontStyle = 'normal';
                    referenceCell.style.fontStyle = 'normal';
                    itemCell.style.fontStyle = 'normal';
                    pricingCell.style.fontStyle = 'normal';
                    pricingCell.style.fontWeight = 'normal';
                    itemCell.style.fontWeight = 'normal';
                    referenceCell.style.fontWeight = 'normal';
                    lineTypeCell.style.fontWeight = 'normal';
                    lineTypeCell.style.color = 'blue';
                    pricingCell.style.textAlign = 'right';
                     if(beforeRowPricingValue == 'group_heading' && beforeCell.style.backgroundColor == 'red' && beforeRowPricingValue2 !== "group_heading"){
                   beforeCell.style.backgroundColor = "";
                }
                if(cellElement){
              if(afterRowPricingValue == 'group_heading' && afterCell.style.backgroundColor == 'red' ){
                afterCell.style.backgroundColor = "";
              } 
            }
                }
                worksheet.setValue(currentRowPricingCellName,0.00,true);
                pricingCell.classList.add('readonly');
            }

        }else{

            let firstCellName = jspreadsheet.helpers.getCellNameFromCoords(0,0);
            let firstCellNameValue = worksheet.getValue(firstCellName);
            let firstCellpricingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,0);
            let firstCellitemCellName = jspreadsheet.helpers.getCellNameFromCoords(2,0);
            let firstCellreferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,0);

            if(firstCellNameValue != "group_heading") {
                worksheet.getCell(firstCellName).style.backgroundColor = 'red';
                $('.error').text('The first row should be group_heading');
                $('#btn1').attr('disabled',true);
            }else{
                worksheet.getCell(firstCellName).style.backgroundColor = 'transparent';
                worksheet.getCell(firstCellpricingCellName).style.backgroundColor = '#eee';

                worksheet.getCell(firstCellpricingCellName).style.fontWeight = 'bold';
                worksheet.getCell(firstCellitemCellName).style.fontWeight = 'bold';
                worksheet.getCell(firstCellreferenceCellName).style.fontWeight = 'bold';
                worksheet.getCell(firstCellName).style.fontWeight = 'bold';

                $('.error').text('');
                $('#btn1').attr('disabled',false);
            }
        }
        // Check the item description cell if the value is empty then set background to red else make it transparent
        if(x==2){
            let getItemDescriptionCellName = jspreadsheet.helpers.getCellNameFromCoords(2,y);
            let getItemDescriptionCellValue = worksheet.getValue(getItemDescriptionCellName);
            console.log('getItemDescriptionCellValue',getItemDescriptionCellValue);
            if(getItemDescriptionCellValue==""){
                worksheet.getCell(getItemDescriptionCellName).style.backgroundColor = 'red';
            }else{
                worksheet.getCell(getItemDescriptionCellName).style.backgroundColor = 'transparent';
            }
        }
        // check if the pricing is not empty in cell
        if(x==3){
            let getPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,y);
            let getPricingCellValue = worksheet.getValue(getPricingCellName);
            let firstCellName = jspreadsheet.helpers.getCellNameFromCoords(0,y);
            let firstCellNameValue = worksheet.getValue(firstCellName);
            // console.log('getPricingCellValue',getPricingCellValue.length);
            if(firstCellNameValue == "fixed_priced_item") {
                if(getPricingCellValue!='' || getPricingCellValue>0){
                    console.log('getPricingCellValueif',getPricingCellValue)
                    worksheet.getCell(getPricingCellName).style.backgroundColor = 'transparent';
                }else{
                    worksheet.getCell(getPricingCellName).style.backgroundColor = 'red';
                }
            }
        }

    }
    let beforeInsertRow = (worksheet,rows) => {
        // let rowIndex = worksheet.rows.length -1;
        // let row = worksheet.getRowData([rowIndex]);
        // let rowLineType = row[0]
        // let rowItemDescription = row[2];
        // let rowPricing = row[3];

        // console.log('rowItemDescription',rowItemDescription);
        // console.log('rowLineType',rowLineType);
        // console.log('rowPricing',rowPricing);
        // if(rowItemDescription == ''){
        //     // Check if the getItemDescription empty of last row then set background to red
        //     let getItemDescriptionCellName = jspreadsheet.helpers.getCellNameFromCoords(2,rowIndex);
        //     let getItemDescriptionCellValue = worksheet.getValue(getItemDescriptionCellName);
        //     if(getItemDescriptionCellValue==""){
        //         worksheet.getCell(getItemDescriptionCellName).style.backgroundColor = 'red';
        //     }else{
        //         worksheet.getCell(getItemDescriptionCellName).style.backgroundColor = 'transparent';
        //     }
        //     $('.error').text('Item Description cannot be empty');
        //     $('#btn1').attr('disabled',true);
        //     return false;
        // }else if(rowLineType=='fixed_priced_item' && rowPricing == ''){
        //     // Check if the getPricing empty of last row then set background to red
        //     let getPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,rowIndex);
        //     let getPricingCellValue = worksheet.getValue(getPricingCellName);
        //     console.log('getPricingCellValuebefore',getPricingCellValue)
        //     if(getPricingCellValue!=""){
        //         console.log('getPricingCellValuebeforeif',getPricingCellValue)
        //         worksheet.getCell(getPricingCellName).style.backgroundColor = 'transparent';
        //     }else{
        //         worksheet.getCell(getPricingCellName).style.backgroundColor = 'red';
        //     }
        //     $('.error').text('Pricing is compulsory in fixed_priced_item');
        //     $('#btn1').attr('disabled',true);
        //     return false;
        // }
        // else{
        //     $('.error').text('');
        //     $('#btn1').attr('disabled',false);
        // }
        let validate = validateRow();
        return validate;
    }
    let onload = (instance) => {
        let worksheet =  table[0];
        let firstCellName = jspreadsheet.helpers.getCellNameFromCoords(0,0);
        let firstRowPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,0);
        let firstCellitemCellName = jspreadsheet.helpers.getCellNameFromCoords(2,0);
        let firstCellreferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,0);
        let firstCellValue = worksheet.getValue(firstCellName);
        if(firstCellValue == 'group_heading'){
            worksheet.getCell(firstRowPricingCellName).style.backgroundColor = '#eee';
            worksheet.getCell(firstRowPricingCellName).style.textAlign = 'center';

            worksheet.getCell(firstRowPricingCellName).style.fontWeight = 'bold';
            worksheet.getCell(firstCellitemCellName).style.fontWeight = 'bold';
            worksheet.getCell(firstCellreferenceCellName).style.fontWeight = 'bold';
            worksheet.getCell(firstCellName).style.fontWeight = 'bold';
        }
        regenrateReferenceAfterSelectedRow();
    }
    let selectionRow = () => {
        rowSelection = [];
        let worksheet =  table[0];
        let selectedRows = worksheet.getSelectedRows();
        rowSelection = selectedRows;
        $('#removeRow').css('display','inline-block');
        console.log('selectionRow',selectedRows);
    }
    let deleteExcelRow = (rows) =>{
        let worksheet = table[0];
        let selectedRows = rowSelection;

        if(selectedRows.length == 0){
            alert('Please select the row');
        }else{
            let confirmation = confirm("Are you sure you want to delete the selected rows?");
            if(confirmation){
                worksheet.deleteRow(selectedRows[0],selectedRows.length);
                // // Loop through each selected row and delete it
                // selectedRows.forEach(function(rowIndex,index) {
                //     console.log('rowIndex',rowIndex);
                //     console.log('index',index);
                //     if(index > 0){
                //         rowIndex = rowIndex-1;
                //     }
                //     // Delete the row by index
                //     worksheet.deleteRow(rowIndex,1);
                // });
                rowSelection = [];
                regenrateReferenceAfterSelectedRow();
            }
        }
    }
    let onpaste = () =>{
        regenrateReferenceAfterSelectedRow();
    }
    let afterInsertRow = ()=> {
        regenrateReferenceAfterSelectedRow();
    }
    const table = jspreadsheet(document.getElementById('spreadsheet'),{
        worksheets: [{
            minDimensions: [4,1],
            data:[['group_heading','1.0','',0.00]],
            tableOverflow: true,
            columns: [
                {
                    type: 'dropdown',
                    title:'Line Type',
                    tooltip: 'Line Type',
                    width: '200px',
                    source:['group_heading','priced_item','fixed_priced_item'],
                },
                {
                    type: 'text',
                    title:'Reference',
                    tooltip: 'Reference',
                    width: '100px',
                    readOnly:true
                },
                {
                    type: 'text',
                    title:'Item',
                    tooltip: 'Item',
                    width: '300px',
                    align:'left',
                    wordWrap:true
                },
                {
                    type: 'text',
                    title:'Price (£)',
                    tooltip: 'Price',
                    width: '200px',
                    align:'right',
                    mask:'#,##0.00',
                    readOnly:true
                }
            ],
            footers: [
                [
                    '',
                    '',
                    'Tender Sum (carried to Form of Tender)',
                    '0.00',
                ]
            ],
            nestedHeaders:[
                [{'title':'A'},{'title':'B'},{'title':'C'},{'title':'D'}]
            ],
            allowInsertColumn:false,
        }],
        onchange:update,
        onbeforeinsertrow:beforeInsertRow,
        onload:onload,
        onselection: selectionRow,
        onpaste: onpaste,
        oninsertrow : afterInsertRow,
        contextMenu: function() {
            return false;
        }
    })
    document.getElementById("btn1").onclick = () => table[0].insertRow(1,rowSelection[0]);
    document.getElementById("removeRow").onclick = () => {
        deleteExcelRow();
        // console.log('selectedRow',selectedRow);
    };
    document.getElementById("save").onclick = () => {
        console.log('save')
        let worksheet = table[0];
        // Check the last record if the last-row is group_heading then highlight the last row and add message
        let rowIndex = worksheet.rows.length -1;
        let row = worksheet.getRowData([rowIndex]);
        let rowLineType = row[0]
        let rowItemDescription = row[2];
        let rowPricing = row[3];
        // check if the last row is group_heading then show the error
        if(rowLineType == "group_heading"){
            $('.error').text('Group heading must be followed by either a price item or fixed price item.');
            return false;
        }else{
            // submit the table
            let worksheetData = worksheet.getData();
            console.log(worksheetData);
        }


    };

    /*** Preview Functionality  */

    document.getElementById("preview").onclick = () => {
        // onload function to adjust the CSS of the preview
        let onloadPreview = (instance) => {
            let worksheet =  tablePreview[0];
            // get length of worksheet
            let rowLength = worksheet.rows.length;

            for(let rows=0;rows<rowLength;rows++){
                let row = worksheet.getRowData([rows]);
                let rowLineType = row[0]
                let rowItemDescription = row[2];
                let rowPricing = row[3];
                // Get the cell names and values
                let firstCellName =  jspreadsheet.helpers.getCellNameFromCoords(0,rows);
                let firstRowPricingCellName = jspreadsheet.helpers.getCellNameFromCoords(3,rows);
                let firstCellitemCellName = jspreadsheet.helpers.getCellNameFromCoords(2,rows);
                let firstCellreferenceCellName = jspreadsheet.helpers.getCellNameFromCoords(1,rows);
                let firstCellValue = worksheet.getValue(firstCellName);
                // if the rowLineType is group_heading
                if(firstCellValue == 'group_heading'){
                    worksheet.getCell(firstRowPricingCellName).style.backgroundColor = '#eee';
                    worksheet.getCell(firstRowPricingCellName).style.textAlign = 'center';
                    worksheet.getCell(firstRowPricingCellName).style.fontWeight = 'bold';
                    worksheet.getCell(firstRowPricingCellName).style.fontStyle = 'normal';

                    worksheet.getCell(firstCellitemCellName).style.fontWeight = 'bold';
                    worksheet.getCell(firstCellitemCellName).style.fontStyle = 'normal';
                    worksheet.getCell(firstCellitemCellName).style.color = '#000';

                    worksheet.getCell(firstCellreferenceCellName).style.fontWeight = 'normal';
                    worksheet.getCell(firstCellreferenceCellName).style.fontStyle = 'normal';
                    worksheet.getCell(firstCellreferenceCellName).style.color = '#000';
                }else if(firstCellValue == 'fixed_priced_item'){
                    worksheet.getCell(firstRowPricingCellName).style.textAlign = 'right';
                    worksheet.getCell(firstRowPricingCellName).style.fontWeight = 'normal';
                    worksheet.getCell(firstRowPricingCellName).style.fontStyle = 'italic';
                    worksheet.getCell(firstCellitemCellName).style.color = '#000';

                    worksheet.getCell(firstCellitemCellName).style.fontWeight = 'normal';
                    worksheet.getCell(firstCellitemCellName).style.fontStyle = 'italic';
                    worksheet.getCell(firstCellitemCellName).style.color = '#000';

                    worksheet.getCell(firstCellreferenceCellName).style.fontWeight = 'normal';
                    worksheet.getCell(firstCellreferenceCellName).style.fontStyle = 'italic';
                    worksheet.getCell(firstCellreferenceCellName).style.color = '#000';
                }else{
                    worksheet.getCell(firstRowPricingCellName).style.textAlign = 'right';
                    worksheet.getCell(firstRowPricingCellName).style.fontWeight = 'normal';
                    worksheet.getCell(firstRowPricingCellName).style.fontStyle = 'normal';
                    worksheet.getCell(firstCellitemCellName).style.color = '#ccc';

                    worksheet.getCell(firstCellitemCellName).style.fontWeight = 'normal';
                    worksheet.getCell(firstCellitemCellName).style.fontStyle = 'normal';
                    worksheet.getCell(firstCellitemCellName).style.color = '#000';

                    worksheet.getCell(firstCellreferenceCellName).style.fontWeight = 'normal';
                    worksheet.getCell(firstCellreferenceCellName).style.fontStyle = 'normal';
                    worksheet.getCell(firstCellreferenceCellName).style.color = '#000';
                }
            }
        }
        // make the spreadsheet-preview div empty to create table again
        document.getElementById('spreadsheet-preview').innerHTML = '';
        // get workheet data
        let worksheetData = table[0].getData();
        // Call the function to create the html of table then intialized the jspreadsheet but commented no need to use
        // html(worksheetData);

        // intialized the preview spreadsheet
        const tablePreview = jspreadsheet(document.getElementById('spreadsheet-preview'),{
            worksheets: [{
                minDimensions: [4,worksheetData.length],
                data:worksheetData,
                tableOverflow: true,
                columns: [
                    {
                        type: 'text',
                        title:'Line Type',
                        tooltip: 'Line Type',
                        width: '100px',
                        readOnly:true,
                        data: 0,
                    },

                    {
                        type: 'text',
                        title:'Reference',
                        tooltip: 'Reference',
                        width: '100px',
                        readOnly:true,
                        data : '1',
                    },
                    {
                        type: 'text',
                        title:'Item',
                        tooltip: 'Item',
                        width: '400px',
                        align:'left',
                        readOnly:true,
                        wordWrap:true,
                        data : 2
                    },
                    {
                        type: 'text',
                        title:'Price (£)',
                        tooltip: 'Price',
                        width: '300px',
                        align:'right',
                        mask:'#,##0.00',
                        readOnly:true,
                        data : 3
                    }
                ],
                footers: [
                    [
                        '',
                        '',
                        'Tender Sum (carried to Form of Tender)',
                        '0.00',
                    ]
                ],
                nestedHeaders:[
                    [{'title':'A'},{'title':'B'},{'title':'C'}]
                ],
                allowInsertColumn:false,
                allowInsertRow:false,
                allowDeleteRow:false,
                allowDeleteColumn:false
            }],
            onload:onloadPreview,
            contextMenu: function() {
                return false;
            },
        })

        let preiewdiv = document.getElementById('previewdiv');
        preiewdiv.style.display = "block";
        tablePreview[0].hideColumn([0]);

    }

        // function html(worksheetData){

        //     // Create the table element
        //     const table = document.createElement('table');
        //     table.setAttribute('border', '1'); // Add border to the table
        //     table.setAttribute('id', 'spreadsheet-preview'); // Add border to the table
        //     table.setAttribute('style', 'width:80%;');

        //     // Create the thead
        //     const thead = document.createElement('thead');
        //     const theadRow = document.createElement('tr');
        //     ['Reference', 'Item', 'Price (£)'].forEach(header => {
        //         const th = document.createElement('th');
        //         th.textContent = header;
        //         theadRow.appendChild(th);
        //     });
        //     thead.appendChild(theadRow);
        //     table.appendChild(thead);

        //     // Create the tbody
        //     const tbody = document.createElement('tbody');

        //     // Loop through the array and create rows
        //     worksheetData.forEach(rowData => {
        //         const tr = document.createElement('tr'); // Create a row
        //         rowData.forEach((cellData, index) => {
        //             if (index !== 0) {
        //                 const lineType = rowData[0];
        //                 const td = document.createElement('td'); // Create a cell
        //                 // This is for the group_heading
        //                 if(lineType == 'group_heading'){
        //                     if(index==1){
        //                         td.setAttribute('style', 'font-weight:bold;text-align:center;white-space: normal;line-height: normal;color:rgba(0, 0, 0, 0.3);');
        //                         td.setAttribute('class','readonly');
        //                         td.textContent = cellData; // Add data to the cell
        //                     }else if(index==2){
        //                         td.setAttribute('style','font-weight:bold;text-align:left;white-space: normal;line-height: normal;color:#000;');
        //                         td.setAttribute('class','readonly');
        //                         let content = convertSpecialCharactersToUnicode(cellData);
        //                         console.log('content',content);
        //                         td.innerHTML = content; // Add data to the cell
        //                     }else{
        //                         td.setAttribute('style', 'font-weight:bold;text-align:center;white-space: normal;line-height: normal;background-color:#eee;color:rgba(0, 0, 0, 0.3);');
        //                         td.setAttribute('class','readonly');
        //                         td.textContent = formatPricing(cellData) // Add data to the cell
        //                         td.setAttribute('width','100');
        //                     }

        //                 }
        //                 // This is for the fixed_priced_item
        //                 else if(lineType == 'fixed_priced_item'){
        //                     if(index==1){
        //                         td.setAttribute('style', 'font-style:italic;text-align:center;white-space:normal;line-height: normal;');
        //                         td.setAttribute('class','readonly');
        //                         td.textContent = cellData;
        //                     }else if(index==2){
        //                         td.setAttribute('style','font-style:italic;text-align:left;white-space:normal;line-height: normal;color:#000;text-align:left;');
        //                         td.setAttribute('class','readonly');
        //                         td.textContent = convertSpecialCharactersToUnicode(cellData);
        //                     }else{
        //                         td.setAttribute('style', 'font-style:italic;text-align:left;white-space:normal;line-height: normal;color:#000;text-align:right;');
        //                         td.setAttribute('class','readonly');
        //                         td.setAttribute('width','100');
        //                         td.textContent = formatPricing(cellData);
        //                     }
        //                 }
        //                 // This is for the priced_item
        //                 else{
        //                     if(index==1){
        //                         td.setAttribute('style', 'font-style:normal;text-align:center;white-space:normal;line-height: normal;');
        //                         td.setAttribute('class','readonly');
        //                         td.textContent = convertSpecialCharactersToUnicode(cellData);
        //                     }else if(index==2){
        //                         td.setAttribute('style','font-style:normal;text-align:left;white-space:normal;line-height: normal;color:#000;text-align:left;');
        //                         td.setAttribute('class','readonly');
        //                         td.textContent = convertSpecialCharactersToUnicode(cellData);
        //                     }else{
        //                         td.setAttribute('style', 'font-style:normal;text-align:left;white-space:normal;line-height: normal;color:#000;text-align:right;color:rgba(0, 0, 0, 0.3);');
        //                         td.setAttribute('class','readonly');
        //                         td.setAttribute('width','100');
        //                         td.textContent = formatPricing(cellData);
        //                     }
        //                 }


        //                 tr.appendChild(td); // Append the cell to the row
        //             }
        //         });

        //         tbody.appendChild(tr); // Append the row to the tbody
        //     });

        //     // Append the tbody to the table
        //     table.appendChild(tbody);

        //     // Append the table to the container
        //     const container = document.getElementById('table-container');
        //     container.innerHTML='';
        //     container.appendChild(table);


        // }

    /*** Preview Functionality  */
    let lastScrollTop = 0;
    const header = document.querySelector('.jss_nested'); // Assuming you want to hide the <thead> section
    const secondRow = document.querySelectorAll('thead tr')[1];

    window.addEventListener('scroll', function () {
    let currentScroll = window.pageYOffset || document.documentElement.scrollTop;

    // Detect scroll direction
    if (currentScroll > lastScrollTop) {
        // Scroll down, hide the header
        header.classList.add('hidden-header');
    } else {
        if (currentScroll <= 0) {
            // Scroll up, show the header
            header.classList.remove('hidden-header');
        }

    }

    // Update the last scroll position
    lastScrollTop = currentScroll <= 0 ? 0 : currentScroll;
    }, { passive: true });
</script>
@endpush