<input value='Apply' type='button' id="apply" class="form-button" style="float: right;" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.1/moment.min.js"></script>

<script>
  $(document).ready(function () {
    /**
     * Programmatically sets a value in a specific cell of the Advance Grid
     * @param {Object} grid - The jQuery object of the grid field
     * @param {number} rowIndx - Row index (starting from 0)
     * @param {number} colIndx - Column index (mapping to the columnKey array)
     * @param {string} colValue - The value to be set
     */
    function setValue(grid, rowIndx, colIndx, colValue) {
      return new Promise(resolve => {
        setTimeout(() => {
          var DM = $(grid).find(".pq_grid").pqGrid("option", "dataModel");
          var data = DM.data;

          // Access the hidden JSON string stored in the last column of the row
          var json = data[rowIndx][data[rowIndx].length - 1];
          var obj;
          try {
            obj = JSON.parse(json);
          } catch (e) {
            console.error('Error parsing JSON:', e);
            resolve();
            return;
          }

          var colKey = DM.columnKey[colIndx];
          obj[colKey] = colValue;

          // Update dataModel
          json = JSON.stringify(obj);
          DM.data[rowIndx][colIndx + 1] = colValue; // Index + 1 because column 0 is the row index
          DM.data[rowIndx][data[rowIndx].length - 1] = json;

          // Sync with the hidden table (Joget's persistence layer)
          var indexKey = DM.data[rowIndx][0];
          var tbl = $(grid).find('.table_container table');
          tbl.find("tr.key_" + indexKey).find("[column_key=" + colKey + "]").text(colValue);
          tbl.find("tr.key_" + indexKey).find("textarea").val(json);

          // Update visual UI cell
          var colCell = $(grid).find("tr[pq-row-indx=" + rowIndx + "] .pq-grid-cell[pq-col-indx=" + (colIndx + 1) + "]");
          $(colCell).find(".pq-td-div").html('<div class="subform-cell-value"><span>' + colValue + '</span></div>');

          resolve();
        }, 100);
      });
    }

    $("#apply").click(async function () {
      var gridWidget = $(FormUtil.getField("statusgrid")).find(".pq_grid");

      // 1. Clear existing data and UI rows
      gridWidget.pqGrid("option", "dataModel.data", []);
      $(FormUtil.getField("statusgrid")).find('.table_container table tbody tr.grid-row').remove();

      // 2. Business Logic Validation
      var BillNo = $('select[name="if_bill_exist"]').val();
      if (BillNo !== null) {
        alert("This TimeSheet bill is already Created");
        return;
      }

      var advgrid = FormUtil.getField("statusgrid");
      var fromDate = $('input[name="from_date"]');
      var toDate = $('input[name="to_date"]');
      var newFromDate = moment(fromDate.val(), 'DD-MM-YYYY');
      var newToDate = moment(toDate.val(), 'DD-MM-YYYY');

      var normalHours = $('select[name="normal_hours"]');
      var WeekendOverTimeH = $('[name="weekend_overtime_hour"]');
      var overTimeHour = $('#overtime_hours');

      var selectedCheckboxValues = [];
      $("input[name='select_overtime_hour_type']:checked").each(function () {
        selectedCheckboxValues.push($(this).val());
      });

      var rowIndex = 0;
      var promises = [];

      let sum_of_Normal_working_hours = 0;
      let sum_total_days = 0;
      let sum = 0;
      let weekendsum = 0;
      let publicsum = 0;

      // 3. Row Generation Loop
      for (var date = moment(newFromDate); date.isSameOrBefore(newToDate); date.add(1, 'days')) {
        var dayOfWeek = date.format('dddd');
        var isWeekHoliday = $('#week_holiday_chosen ul li:eq(0)').text().toLowerCase().split(';').includes(dayOfWeek.toLowerCase());

        var hour = (isWeekHoliday || !selectedCheckboxValues.includes('Normal Hour')) ? '' : normalHours.val();
        var day_calc = (isWeekHoliday) ? '' : 1;
        var weekend_overtime_hour = (isWeekHoliday && selectedCheckboxValues.includes('Weekend OverTime')) ? WeekendOverTimeH.val() : '';
        var overtime_hour = (!isWeekHoliday && selectedCheckboxValues.includes('OverTime Hour')) ? overTimeHour.val() : '';

        // Add row via Joget's native icon trigger
        advgrid.find(".ui-icon-circle-plus").trigger("click");

        // 4. Batch Updates via Promises
        promises.push(setValue(advgrid, rowIndex, 0, date.format('MMM'))); // Month
        promises.push(setValue(advgrid, rowIndex, 1, date.date()));        // Day
        promises.push(setValue(advgrid, rowIndex, 2, dayOfWeek);          // Weekday
        promises.push(setValue(advgrid, rowIndex, 3, date.year()));       // Year
        promises.push(setValue(advgrid, rowIndex, 4, date.format('DD-MM-YYYY'))); // Full Date
        promises.push(setValue(advgrid, rowIndex, 5, hour));
        promises.push(setValue(advgrid, rowIndex, 6, day_calc));
        promises.push(setValue(advgrid, rowIndex, 7, overtime_hour));
        promises.push(setValue(advgrid, rowIndex, 8, weekend_overtime_hour));
        promises.push(setValue(advgrid, rowIndex, 14, date.format('YYYYMMDD'))); // Unique ID

        // Aggregate totals for the parent form
        sum_of_Normal_working_hours += parseFloat(hour) || 0;
        sum_total_days += parseFloat(day_calc) || 0;
        sum += parseFloat(overtime_hour) || 0;
        weekendsum += parseFloat(weekend_overtime_hour) || 0;

        rowIndex++;
      }

      await Promise.all(promises);
      Calculate({
        sum_of_Normal_working_hours,
        sum_total_days,
        sum,
        weekendsum,
        publicsum: 0 // logic to be added based on public_holiday field
      });
    });

    function Calculate(obj) {
      $('[name=total_normal_working_hours]').val(obj.sum_of_Normal_working_hours);
      $('[name=total_days]').val(obj.sum_total_days);
      $('[name=total_overtime_hour]').val(obj.sum);
      $('[name=total_weekend_overtime_hour]').val(obj.weekendsum);
    }
  });
</script>