var oTableDash1;

function createDashboardTable(data) {
    // Create the table
    oTableDash1 = new sap.m.Table("dashboardTable", {
        fixedLayout: false,
        sticky: ["ColumnHeaders"],
        backgroundDesign: "Transparent"
    });
    
    // Add custom styles
    addCustomStyles();
    
    // Add columns
    addColumnsToTable(oTableDash1, data);
    
    // Add rows
    addRowsToTable(oTableDash1, data);
    
    return oTableDash1;
}

function addColumnsToTable(oTableDash1, data) {
    const years = data.years;
    const months = data.months;
    
    // Add City Breakdown column
    oTableDash1.addColumn(new sap.m.Column({
        header: new sap.m.Label({
            text: "City Breakdown",
            design: "Bold"
        }),
        width: "12rem",
        hAlign: "Begin"
    }));
    
    // Add WO_Status column
    oTableDash1.addColumn(new sap.m.Column({
        header: new sap.m.Label({
            text: "WO_Status",
            design: "Bold"
        }),
        width: "10rem",
        hAlign: "Begin"
    }));
    
    // Create year headers with month columns
    years.forEach(function(year) {
        // Add year column header
        oTableDash1.addColumn(new sap.m.Column({
            header: new sap.m.Label({
                text: "# " + year,
                design: "Bold"
            }),
            width: "6rem",
            hAlign: "End"
        }));
        
        // Add month columns for each year
        months.forEach(function(month) {
            oTableDash1.addColumn(new sap.m.Column({
                header: new sap.m.Label({
                    text: month,
                    design: "Bold"
                }),
                width: "6rem",
                hAlign: "End"
            }));
        });
    });
    
    // Add Grand Total column
    oTableDash1.addColumn(new sap.m.Column({
        header: new sap.m.Label({
            text: "Grand Total",
            design: "Bold"
        }),
        width: "8rem",
        hAlign: "End",
        styleClass: "totalColumn"
    }));
}

function addRowsToTable(oTableDash1, data) {
    const years = data.years;
    const months = data.months;
    
    // Loop through each city
    data.cities.forEach(function(city) {
        // Add rows for each status
        city.statuses.forEach(function(status, index) {
            const aCells = [];
            
            // City name (only on first status row)
            if (index === 0) {
                aCells.push(new sap.m.Text({ text: city.name }));
            } else {
                aCells.push(new sap.m.Text({ text: "" }));
            }
            
            // Status name
            const oStatusText = new sap.m.Text({ 
                text: status.name
            });
            
            if (status.textColor) {
                oStatusText.addStyleClass("coloredText-" + status.name.toLowerCase());
            }
            
            aCells.push(oStatusText);
            
            // Values for each year and month
            years.forEach(function(year) {
                // Year column (usually empty in this example)
                aCells.push(new sap.m.Text({ text: "" }));
                
                // Month values
                months.forEach(function(month) {
                    const key = year + "-" + month;
                    const value = status.values[key] || 0;
                    aCells.push(new sap.m.Text({ 
                        text: value > 0 ? value.toLocaleString() : "",
                        textAlign: "End"
                    }));
                });
            });
            
            // Grand total
            aCells.push(new sap.m.Text({ 
                text: status.total.toLocaleString(),
                textAlign: "End"
            }));
            
            // Create the row
            const oRow = new sap.m.ColumnListItem({
                cells: aCells
            });
            
            // Add custom style class based on status
            if (status.name === "Invoiced") {
                oRow.addStyleClass("statusRow-invoiced");
            } else {
                oRow.addStyleClass("statusRow-" + status.name.toLowerCase());
            }
            
            oTableDash1.addItem(oRow);
        });
        
        // Add total row for the city
        const aTotalCells = [];
        
        // City Total
        aTotalCells.push(new sap.m.Text({ 
            text: city.name + " Total",
            wrapping: false
        }));
        
        // Empty status cell
        aTotalCells.push(new sap.m.Text({ text: "" }));
        
        // Total values for each year and month
        years.forEach(function(year) {
            // Year column (usually empty in this example)
            aTotalCells.push(new sap.m.Text({ text: "" }));
            
            // Month values
            months.forEach(function(month) {
                const key = year + "-" + month;
                const value = city.total.values[key] || 0;
                aTotalCells.push(new sap.m.Text({ 
                    text: value > 0 ? value.toLocaleString() : "",
                    textAlign: "End"
                }));
            });
        });
        
        // Grand total
        aTotalCells.push(new sap.m.Text({ 
            text: city.total.total.toLocaleString(),
            textAlign: "End"
        }));
        
        // Create the total row
        const oTotalRow = new sap.m.ColumnListItem({
            cells: aTotalCells,
            type: "Inactive"
        });
        
        // Add custom style class for total row
        oTotalRow.addStyleClass("totalRow");
        
        oTableDash1.addItem(oTotalRow);
    });
}

function addCustomStyles() {
    // Check if styles are already added
    if (document.getElementById("dashboardStyles")) {
        return;
    }
    
    // Create style element
    const style = document.createElement("style");
    style.id = "dashboardStyles";
    
    // Define CSS rules
    const css = `
        .statusRow-invoiced {
            background-color: #fdd76e !important;
        }
        
        .statusRow-completed,
        .statusRow-new,
        .statusRow-open,
        .statusRow-pending,
        .statusRow-cancelled {
            background-color: #fce9d7 !important;
        }
        
        .coloredText-completed,
        .coloredText-new,
        .coloredText-open,
        .coloredText-pending,
        .coloredText-cancelled {
            color: #cc0000 !important;
        }
        
        .totalRow {
            background-color: #e6e6e6 !important;
            font-weight: bold;
        }
        
        .totalColumn {
            background-color: #f2f2f2;
        }
    `;
    
    // Add the styles to the style element
    style.appendChild(document.createTextNode(css));
    
    // Add the style element to the document head
    document.head.appendChild(style);
}


function transformDashboardData(apiData) {
    // Get unique years from the data
    const years = [...new Set(apiData.map(record => record.year))].sort();
    
    // Get unique months that appear in the data
    const uniqueMonths = [...new Set(apiData.map(record => record.month))];
    
    // Standard month ordering for sorting
    const standardMonthOrder = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
    
    // Sort months according to standard order (if they match standard months)
    const months = uniqueMonths.sort((a, b) => {
        const indexA = standardMonthOrder.indexOf(a);
        const indexB = standardMonthOrder.indexOf(b);
        
        // If both months are in the standard list, sort by their position
        if (indexA !== -1 && indexB !== -1) {
            return indexA - indexB;
        }
        
        // If one is in the standard list but not the other, prioritize the standard one
        if (indexA !== -1) return -1;
        if (indexB !== -1) return 1;
        
        // If neither is in the standard list, sort alphabetically
        return a.localeCompare(b);
    });
    
    // Get unique locations from the data
    const uniqueLocations = [...new Set(apiData.map(record => record.location))].sort();
    
    // Get unique statuses from the data
    const uniqueStatuses = [...new Set(apiData.map(record => record.wo_status))].sort();
    
    // Initialize the dashboard data structure
    const dashboardData = {
        years: years,
        months: months,
        cities: []
    };
    
    // Create a map to store city data during processing
    const citiesMap = {};
    
    // Initialize cities map with empty structure
    uniqueLocations.forEach(location => {
        citiesMap[location] = {
            name: location,
            statuses: {},
            total: {
                values: {},
                total: 0
            }
        };
        
        // Initialize statuses for each city
        uniqueStatuses.forEach(status => {
            citiesMap[location].statuses[status] = {
                name: status,
                // Set color based on status - "Invoiced" gets yellow, others get light orange
                color: (status === "Invoiced") ? "#fdd76e" : "#ffffff",
                // Set text color for non-Invoiced statuses to red
                textColor: (status !== "Invoiced") ? "#cc0000" : null,
                values: {},
                total: 0
            };
        });
    });
    
    // Process each record and populate the data structure
    apiData.forEach(record => {
        const location = record.location;
        const status = record.wo_status;
        const year = record.year;
        const month = record.month;
        const count = record.workorder_count;
        const key = `${year}-${month}`;
        
        // Add count to the status values
        if (!citiesMap[location].statuses[status].values[key]) {
            citiesMap[location].statuses[status].values[key] = 0;
        }
        citiesMap[location].statuses[status].values[key] += count;
        
        // Update status total
        citiesMap[location].statuses[status].total += count;
        
        // Update city total values
        if (!citiesMap[location].total.values[key]) {
            citiesMap[location].total.values[key] = 0;
        }
        citiesMap[location].total.values[key] += count;
        
        // Update city grand total
        citiesMap[location].total.total += count;
    });
    
    // Get all unique year-month combinations that appear in the data
    const yearMonthCombinations = [];
    years.forEach(year => {
        // Find all months that appear for this year
        const monthsForThisYear = [...new Set(apiData
            .filter(record => record.year === year)
            .map(record => record.month))];
        
        // Sort months for this year
        monthsForThisYear.sort((a, b) => {
            const indexA = standardMonthOrder.indexOf(a);
            const indexB = standardMonthOrder.indexOf(b);
            
            if (indexA !== -1 && indexB !== -1) {
                return indexA - indexB;
            }
            
            if (indexA !== -1) return -1;
            if (indexB !== -1) return 1;
            
            return a.localeCompare(b);
        });
        
        // Add to combinations array
        monthsForThisYear.forEach(month => {
            yearMonthCombinations.push({
                year: year,
                month: month,
                key: `${year}-${month}`
            });
        });
    });
    
    // Add year-month combinations to dashboard data for column generation
    dashboardData.yearMonthCombinations = yearMonthCombinations;
    
    // Convert citiesMap to the array format needed
    Object.keys(citiesMap).forEach(location => {
        const city = citiesMap[location];
        
        // Convert statuses map to array of statuses
        const statusesArray = Object.values(city.statuses)
            // Filter out statuses with no data
            .filter(status => status.total > 0)
            // Sort statuses (put "Invoiced" first if it exists, then alphabetically)
            .sort((a, b) => {
                if (a.name === "Invoiced") return -1;
                if (b.name === "Invoiced") return 1;
                return a.name.localeCompare(b.name);
            });
        
        // Create the city object with the array of statuses
        dashboardData.cities.push({
            name: city.name,
            statuses: statusesArray,
            total: city.total
        });
    });
    
    // Sort cities alphabetically
    dashboardData.cities.sort((a, b) => a.name.localeCompare(b.name));
    
    return dashboardData;
}


var oDatePickerFrom = new sap.m.DatePicker("dateFrom", {
    placeholder: "From Date",displayFormat: "yyyy-MM-dd",valueFormat: "yyyy-MM-dd"
});

var oDatePickerTo = new sap.m.DatePicker("dateTo", {
    placeholder: "To Date",displayFormat: "yyyy-MM-dd",valueFormat: "yyyy-MM-dd"
});

// Go Button
var oButton = new sap.m.Button({
    text: "Go",
    press: function () {
        var fromDate = oDatePickerFrom.getValue()
        var toDate = oDatePickerTo.getValue()

        if (!fromDate || !toDate) {
            alert("Please select both dates.");
            return;
        }

        // Call your function here
        reportWorkorderStatus(fromDate, toDate);
    }
});

// Add to page
var oVBoxDates = new sap.m.HBox({
    items: [oDatePickerFrom, oDatePickerTo, oButton],
    alignItems: "Center",
    justifyContent: "Center"
});

var dash1Page4W = new sap.m.Page("dash1_page_4w", {
    title:oBundle.getText("Dashboard1"),
    showHeader:true,
    enableScrolling:true,
    content : [oVBoxDates] 
}); 


//--------------------Sample and no use data---------------------------
var sample11Data = [
    {
      "grand_total_per_location": 191,
      "location": "BALI",
      "month": "Feb",
      "wo_status": "Cancelled",
      "workorder_count": 1,
      "year": 2024
    },
    {
      "grand_total_per_location": 191,
      "location": "MALANG",
      "month": "Mar",
      "wo_status": "Cancelled",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "JAKARTA",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 12,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MAKASSAR",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 14,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "BALI",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 49,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MEDAN",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 3,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MALANG",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "JAKARTA",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 5,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "BALI",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MAKASSAR",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 3,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MEDAN",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 2,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "JAKARTA",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 3,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MALANG",
      "month": "Feb",
      "wo_status": "Closed",
      "workorder_count": 10,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "BALI",
      "month": "Mar",
      "wo_status": "Closed",
      "workorder_count": 7,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MAKASSAR",
      "month": "Feb",
      "wo_status": "Completed",
      "workorder_count": 6,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MEDAN",
      "month": "Feb",
      "wo_status": "Completed",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MALANG",
      "month": "Feb",
      "wo_status": "Completed",
      "workorder_count": 2,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "JAKARTA",
      "month": "Feb",
      "wo_status": "GRN",
      "workorder_count": 3,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MAKASSAR",
      "month": "Feb",
      "wo_status": "GRN",
      "workorder_count": 19,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MEDAN",
      "month": "Feb",
      "wo_status": "GRN",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "BALI",
      "month": "Feb",
      "wo_status": "GRN",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MALANG",
      "month": "Feb",
      "wo_status": "GRN",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "JAKARTA",
      "month": "Mar",
      "wo_status": "GRN",
      "workorder_count": 2,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MAKASSAR",
      "month": "Feb",
      "wo_status": "Open",
      "workorder_count": 6,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MEDAN",
      "month": "Feb",
      "wo_status": "Open",
      "workorder_count": 5,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "BALI",
      "month": "Feb",
      "wo_status": "Open",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MALANG",
      "month": "Feb",
      "wo_status": "Open",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "JAKARTA",
      "month": "Feb",
      "wo_status": "Open",
      "workorder_count": 2,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MAKASSAR",
      "month": "Feb",
      "wo_status": "Open",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MEDAN",
      "month": "Feb",
      "wo_status": "Open",
      "workorder_count": 1,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "BALI",
      "month": "Feb",
      "wo_status": "Open",
      "workorder_count": 6,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MALANG",
      "month": "Feb",
      "wo_status": "Pending Approval",
      "workorder_count": 2,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "JAKARTA",
      "month": "Feb",
      "wo_status": "Pending Approval",
      "workorder_count": 12,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MAKASSAR",
      "month": "Feb",
      "wo_status": "Pending Approval",
      "workorder_count": 2,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "MEDAN",
      "month": "Mar",
      "wo_status": "Pending Approval",
      "workorder_count": 2,
      "year": 2025
    },
    {
      "grand_total_per_location": 191,
      "location": "BALI",
      "month": "Feb",
      "wo_status": "Reject",
      "workorder_count": 2,
      "year": 2025
    }
];

// function addItemTable(){
//     const dashboardData = transformDashboardData(sample11Data);
//     // Create table
//     oTableDash1  = createDashboardTable(dashboardData);
//     dash1Page4W.addContent(oTableDash1);
//     // const oTableDash1 = createDashboardTable(dashboardData);
// }


// function createDashboardData() {
//     // Get current date for dynamic year calculation
//     const currentDate = new Date();
//     const currentYear = currentDate.getFullYear();
//     const previousYear = currentYear - 1;
    
//     // Define months
//     const months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
    
//     // Sample data structure - replace with your actual data
//     return {
//         years: [previousYear, currentYear],
//         months: months,
//         cities: [
//             {
//                 name: "Jakarta",
//                 statuses: [
//                     { 
//                         name: "Invoiced", 
//                         color: "#fdd76e",
//                         values: {
//                             "2023-Dec": 10768, "2024-Jan": 14672, "2024-Feb": 13126, "2024-Mar": 15004, 
//                             "2024-Apr": 13199, "2024-May": 14966, "2024-Jun": 13731, "2024-Jul": 14303, 
//                             "2024-Aug": 14195, "2024-Sep": 15687, "2024-Oct": 17128, "2024-Nov": 11665, "2024-Dec": 917
//                         },
//                         total: 169361
//                     },
//                     { 
//                         name: "Completed", 
//                         color: "#ffffff",
//                         textColor: "#cc0000",
//                         values: {
//                             "2023-Dec": 1, "2024-Jan": 0, "2024-Feb": 1, "2024-Mar": 0, 
//                             "2024-Apr": 0, "2024-May": 1, "2024-Jun": 1, "2024-Jul": 1, 
//                             "2024-Aug": 10, "2024-Sep": 65, "2024-Oct": 1352, "2024-Nov": 814, "2024-Dec": 2
//                         },
//                         total: 2245
//                     },
//                     { 
//                         name: "New", 
//                         color: "#ffffff",
//                         textColor: "#cc0000",
//                         values: {
//                             "2023-Dec": 0, "2024-Jan": 3, "2024-Feb": 0, "2024-Mar": 1, 
//                             "2024-Apr": 0, "2024-May": 0, "2024-Jun": 2, "2024-Jul": 0, 
//                             "2024-Aug": 2, "2024-Sep": 19, "2024-Oct": 218, "2024-Nov": 222, "2024-Dec": 0
//                         },
//                         total: 465
//                     },
//                     { 
//                         name: "Open", 
//                         color: "#ffffff",
//                         textColor: "#cc0000",
//                         values: {
//                             "2023-Dec": 0, "2024-Jan": 0, "2024-Feb": 2, "2024-Mar": 0, 
//                             "2024-Apr": 5, "2024-May": 0, "2024-Jun": 0, "2024-Jul": 2, 
//                             "2024-Aug": 2, "2024-Sep": 4, "2024-Oct": 3134, "2024-Nov": 1306, "2024-Dec": 0
//                         },
//                         total: 4457
//                     },
//                     { 
//                         name: "Pending", 
//                         color: "#ffffff",
//                         textColor: "#cc0000",
//                         values: {
//                             "2023-Dec": 0, "2024-Jan": 9, "2024-Feb": 8, "2024-Mar": 17, 
//                             "2024-Apr": 28, "2024-May": 33, "2024-Jun": 24, "2024-Jul": 52, 
//                             "2024-Aug": 91, "2024-Sep": 153, "2024-Oct": 991, "2024-Nov": 2658, "2024-Dec": 0
//                         },
//                         total: 4116
//                     },
//                     { 
//                         name: "Cancelled", 
//                         color: "#ffffff",
//                         textColor: "#cc0000",
//                         values: {
//                             "2023-Dec": 0, "2024-Jan": 0, "2024-Feb": 0, "2024-Mar": 0, 
//                             "2024-Apr": 0, "2024-May": 0, "2024-Jun": 0, "2024-Jul": 0, 
//                             "2024-Aug": 0, "2024-Sep": 0, "2024-Oct": 0, "2024-Nov": 1, "2024-Dec": 0
//                         },
//                         total: 1
//                     }
//                 ],
//                 total: {
//                     values: {
//                         "2023-Dec": 10768, "2024-Jan": 14685, "2024-Feb": 13136, "2024-Mar": 15022, 
//                         "2024-Apr": 13232, "2024-May": 15000, "2024-Jun": 13755, "2024-Jul": 14360, 
//                         "2024-Aug": 14289, "2024-Sep": 15751, "2024-Oct": 17369, "2024-Nov": 17361, "2024-Dec": 5917
//                     },
//                     total: 180645
//                 }
//             },
//             {
//                 name: "Medan",
//                 statuses: [
//                     { 
//                         name: "Invoiced", 
//                         color: "#fdd76e",
//                         values: {
//                             "2023-Dec": 326, "2024-Jan": 541, "2024-Feb": 511, "2024-Mar": 602, 
//                             "2024-Apr": 684, "2024-May": 790, "2024-Jun": 635, "2024-Jul": 699, 
//                             "2024-Aug": 644, "2024-Sep": 712, "2024-Oct": 600, "2024-Nov": 630, "2024-Dec": 98
//                         },
//                         total: 7472
//                     },
//                     { 
//                         name: "Completed", 
//                         color: "#ffffff",
//                         textColor: "#cc0000",
//                         values: {
//                             "2023-Dec": 0, "2024-Jan": 0, "2024-Feb": 0, "2024-Mar": 0, 
//                             "2024-Apr": 1, "2024-May": 2, "2024-Jun": 1, "2024-Jul": 3, 
//                             "2024-Aug": 0, "2024-Sep": 0, "2024-Oct": 2, "2024-Nov": 51, "2024-Dec": 6
//                         },
//                         total: 62
//                     }
//                     // Add more statuses as needed
//                 ],
//                 total: {
//                     values: {
//                         "2023-Dec": 326, "2024-Jan": 541, "2024-Feb": 511, "2024-Mar": 602, 
//                         "2024-Apr": 685, "2024-May": 793, "2024-Jun": 636, "2024-Jul": 702, 
//                         "2024-Aug": 644, "2024-Sep": 713, "2024-Oct": 642, "2024-Nov": 816, "2024-Dec": 167
//                     },
//                     total: 7778
//                 }
//             }
//             // Add more cities as needed
//         ]
//     };
// }





// function loadDashboardDataDash1(callback) {
//     // Example: Load data from an API
//     // Replace this with your actual data loading logic
    
//     // Option 1: Using jQuery AJAX
//     /*
//     $.ajax({
//         url: "your/api/endpoint",
//         method: "GET",
//         success: function(data) {
//             // Transform data if needed
//             const transformedData = transformData(data);
//             callback(transformedData);
//         },
//         error: function(error) {
//             console.error("Error loading dashboard data:", error);
//         }
//     });
//     */
    
//     // For demo purposes, use sample data
//     callback(createDashboardData());
// }


// function transformData(apiData) {
//     // Get current date for dynamic year calculation
//     const currentDate = new Date();
//     const currentYear = currentDate.getFullYear();
//     const previousYear = currentYear - 1;
    
//     // Define months
//     const months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
    
//     // Initialize the dashboard data structure
//     const dashboardData = {
//         years: [previousYear, currentYear],
//         months: months,
//         cities: []
//     };
    
//     // Example transformation logic - replace with your actual logic
//     // This is just an example of how you might transform your API data
    
//     // Group data by city
//     const citiesData = {};
    
//     apiData.records.forEach(record => {
//         const city = record.city;
//         const status = record.status;
//         const date = new Date(record.date);
//         const year = date.getFullYear();
//         const month = months[date.getMonth()];
//         const key = `${year}-${month}`;
        
//         // Create city if it doesn't exist
//         if (!citiesData[city]) {
//             citiesData[city] = {
//                 name: city,
//                 statuses: {},
//                 total: {
//                     values: {},
//                     total: 0
//                 }
//             };
//         }
        
//         // Create status if it doesn't exist
//         if (!citiesData[city].statuses[status]) {
//             citiesData[city].statuses[status] = {
//                 name: status,
//                 color: status === "Invoiced" ? "#fdd76e" : "#ffffff",
//                 textColor: status !== "Invoiced" ? "#cc0000" : null,
//                 values: {},
//                 total: 0
//             };
//         }
        
//         // Increment count for this city, status, and date
//         const count = record.count || 1;
//         citiesData[city].statuses[status].values[key] = (citiesData[city].statuses[status].values[key] || 0) + count;
//         citiesData[city].statuses[status].total += count;
        
//         // Update city total
//         citiesData[city].total.values[key] = (citiesData[city].total.values[key] || 0) + count;
//         citiesData[city].total.total += count;
//     });
    
//     // Convert to array format
//     Object.values(citiesData).forEach(city => {
//         const cityData = {
//             name: city.name,
//             statuses: Object.values(city.statuses),
//             total: city.total
//         };
        
//         dashboardData.cities.push(cityData);
//     });
    
//     return dashboardData;
// }

// Example of how to use with real data
/*
document.addEventListener("DOMContentLoaded", function() {
    loadDashboardDataDash1(function(data) {
        const oTableDash1 = createDashboardTable(data);
        oTableDash1.placeAt("dashboardContent");
    });
});
*/




/*
function transformData(data) {
    const monthOrder = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", 
                        "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
    
    const yearsSet = new Set();
    const monthsSet = new Set();
    const monthsYearSet = new Set();
    const cityMap = new Map();

    // Step 1: Build dynamic years and months
    data.forEach(item => {
        yearsSet.add(item.year);
        monthsYearSet.add(`${item.year}-${item.month}`);
        monthsSet.add(`${item.month}`);
    });

    const years = Array.from(yearsSet).sort();
    const months = Array.from(monthsSet).sort((a, b) => {
        const [y1, m1] = a.split('-');
        const [y2, m2] = b.split('-');
        if (y1 !== y2) return y1 - y2;
        return monthOrder.indexOf(m1) - monthOrder.indexOf(m2);
    });
    const monthsYear = Array.from(monthsYearSet).sort((a, b) => {
        const [y1, m1] = a.split('-');
        const [y2, m2] = b.split('-');
        if (y1 !== y2) return y1 - y2;
        return monthOrder.indexOf(m1) - monthOrder.indexOf(m2);
    });

    // Step 2: Group by location and status
    data.forEach(item => {
        const key = item.location.toUpperCase();
        if (!cityMap.has(key)) {
            cityMap.set(key, {
                name: key.charAt(0) + key.slice(1).toLowerCase(),
                statuses: new Map()
            });
        }
        const city = cityMap.get(key);
        if (!city.statuses.has(item.wo_status)) {
            city.statuses.set(item.wo_status, {
                name: item.wo_status,
                values: {},
                total: 0,
                color: "#ffffff",
                textColor: "#cc0000"
            });
        }

        const status = city.statuses.get(item.wo_status);
        const monthKey = `${item.year}-${item.month}`;
        status.values[monthKey] = (status.values[monthKey] || 0) + item.workorder_count;
        status.total += item.workorder_count;
    });

    // Step 3: Format output
    const cities = [];
    for (const city of cityMap.values()) {
        const statuses = [];
        for (const status of city.statuses.values()) {
            // Ensure all months are included with 0 if missing
            for (const month of months) {
                if (!status.values[month]) {
                    status.values[month] = 0;
                }
            }
            statuses.push(status);
        }

        // Total per month for the city
        const totalValues = {};
        let totalSum = 0;
        for (const month of monthsYear) {
            console.log(month + ": "+ city)
            const sum = statuses.reduce((acc, status) => acc + status.values[month], 0);
            console.log(sum)
            totalValues[month] = sum;
            totalSum += sum;
        }

        cities.push({
            name: city.name,
            statuses: statuses,
            total: {
                values: totalValues,
                total: totalSum
            }
        });
    }

    return {
         years: years,
        months: months,
        cities:cities
    };
}*/


/*
function transformDashboardData(apiData) {
    // Get unique years from the data
    const years = [...new Set(apiData.map(record => record.year))].sort();
    
    // Define months in order (assuming standard abbreviations)
    const months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
    
    // Get unique locations from the data
    const uniqueLocations = [...new Set(apiData.map(record => record.location))].sort();
    
    // Get unique statuses from the data
    const uniqueStatuses = [...new Set(apiData.map(record => record.wo_status))].sort();
    
    // Initialize the dashboard data structure
    const dashboardData = {
        years: years,
        months: months,
        cities: []
    };
    
    // Create a map to store city data during processing
    const citiesMap = {};
    
    // Initialize cities map with empty structure
    uniqueLocations.forEach(location => {
        citiesMap[location] = {
            name: location,
            statuses: {},
            total: {
                values: {},
                total: 0
            }
        };
        
        // Initialize statuses for each city
        uniqueStatuses.forEach(status => {
            citiesMap[location].statuses[status] = {
                name: status,
                // Set color based on status - "Invoiced" gets yellow, others get light orange
                color: (status === "Invoiced") ? "#fdd76e" : "#ffffff",
                // Set text color for non-Invoiced statuses to red
                textColor: (status !== "Invoiced") ? "#cc0000" : null,
                values: {},
                total: 0
            };
        });
    });
    
    // Process each record and populate the data structure
    apiData.forEach(record => {
        const location = record.location;
        const status = record.wo_status;
        const year = record.year;
        const month = record.month;
        const count = record.workorder_count;
        const key = `${year}-${month}`;
        
        // Add count to the status values
        if (!citiesMap[location].statuses[status].values[key]) {
            citiesMap[location].statuses[status].values[key] = 0;
        }
        citiesMap[location].statuses[status].values[key] += count;
        
        // Update status total
        citiesMap[location].statuses[status].total += count;
        
        // Update city total values
        if (!citiesMap[location].total.values[key]) {
            citiesMap[location].total.values[key] = 0;
        }
        citiesMap[location].total.values[key] += count;
        
        // Update city grand total
        citiesMap[location].total.total += count;
    });
    
    // Convert citiesMap to the array format needed
    Object.keys(citiesMap).forEach(location => {
        const city = citiesMap[location];
        
        // Convert statuses map to array of statuses
        const statusesArray = Object.values(city.statuses)
            // Filter out statuses with no data
            .filter(status => status.total > 0)
            // Sort statuses (put "Invoiced" first if it exists, then alphabetically)
            .sort((a, b) => {
                if (a.name === "Invoiced") return -1;
                if (b.name === "Invoiced") return 1;
                return a.name.localeCompare(b.name);
            });
        
        // Create the city object with the array of statuses
        dashboardData.cities.push({
            name: city.name,
            statuses: statusesArray,
            total: city.total
        });
    });
    
    // Sort cities alphabetically
    dashboardData.cities.sort((a, b) => a.name.localeCompare(b.name));
    
    return dashboardData;
}*/
