const formID = '241516900942858';
const apiKey = '6e2676fa4067aecefdfae6fca7c4bb41';

fetch(`https://api.jotform.com/form/${formID}/submissions?apiKey=${apiKey}&limit=1000`)
    .then(response => response.json())
    .then(data => {
        console.log("All data:", data); // Log all data to see its structure

        const submissionIDs = data.content
            .filter(entry => {
                const answers = entry.answers;
                for (const key in answers) {
                    if (answers[key].name === "contratoFirmado" && answers[key].answer.length > 0) {
                        return true;
                    }
                }
                return false;
            })
            .map(entry => entry.id);

        console.log("Proyectos activos:", submissionIDs);

        function generateTable(submissionData) {
            if (!submissionData) return;

            const tic = parseFloat(submissionData["Tasa"]);
            const comision = parseFloat(submissionData["Comision"]);
            const monto = parseFloat(submissionData["Validaci\u00f3n Monto Solicitado"].replace("$", "").replace(/,/g, ''));
            const periodo = parseFloat(submissionData["Periodicidad"]);
            const plazo = parseFloat(submissionData["Plazo"]);
            const fechai = submissionData["Fecha de inicio"];
            function formatDate(fechai) {
            // Regular expression to check for "aaaa-mm-dd" format
            var regex = /^\d{4}-\d{2}-\d{2}$/;
            
            if (regex.test(fechai)) {
                // If the date is already in "aaaa-mm-dd" format, return it as is
                return fechai;
            } else {
                // Otherwise, split the date and reformat it
                var partesFecha = fechai.split("/");
                // Formar la fecha en formato "aaaa-mm-dd"
                return partesFecha[2] + "-" + partesFecha[1].padStart(2, '0') + "-" + partesFecha[0].padStart(2, '0');
            }
        }
        
        var fecha_inicio = formatDate(fechai);
            const fechaInicio = new Date(fecha_inicio);
            const n = Math.floor(plazo / periodo);
            localStorage.setItem('numperiodos', n);
            const amountt = 1 * monto;
            let comisionPorcentaje = comision * 0.01;
            let interes = tic * 0.01 * (periodo / 360);
            let payment = amountt * (1 + comisionPorcentaje) * interes / (1 - Math.pow(1 + interes, -n));
            let d = payment * n;
            let cost = (((d / amountt - 1) * 100) * 12) / ((plazo / 30) * 1.16);

            const paymentDates = getNextDates(fechaInicio, n+1);;

            const periodos = [0];
            const amort = [0];
            const int = [0];
            const saldo_inicial = [amountt * (1 + comisionPorcentaje)];
            const saldo_final = [amountt * (1 + comisionPorcentaje)];
            const pago = [0];
            const iva = [0];
            for (let i = 0; i <= n; i++) {
                periodos[i + 1] = periodos[i] + 1;
                pago[i + 1] = payment;
                int[i + 1] = saldo_final[i] * interes / 1.16;
                iva[i + 1] = int[i + 1] * 0.16;
                amort[i + 1] = payment - int[i + 1] - iva[i + 1];
                saldo_inicial[i + 1] = saldo_final[i] + int[i + 1] + iva[i + 1];
                saldo_final[i + 1] = saldo_final[i] - amort[i + 1];
            }

            function generateID(fullName) {
                var words = fullName.split(" ");
                var id = "";
                for (var i = 0; i < words.length; i++) {
                    id += words[i].charAt(0);
                }
                return id.slice(0, 3).toUpperCase();
            }

            const idinciales = generateID(submissionData["Nombre completo"]);
            const idcounter = submissionData["IDcounter"];
            const idunique = idinciales + idcounter;
            const nameAcred = submissionData["Nombre completo"];
            const empresa = submissionData["Empresa"];

            const headers = ["Periodo", "Saldo Inicial", "Inter\u00e9s", "I.V.A.", "Amortizaci\u00f3n", "Pago parcial", "Saldo Final", "Fecha", "ID", "Acreditada", "Empresa"];
            const table = document.createElement("table");
            table.setAttribute("id", "generated-table");

            const header = table.createTHead();
            const headerRow = header.insertRow(0);
            for (let i = 0; i < headers.length; i++) {
                const th = document.createElement("th");
                th.textContent = headers[i];
                headerRow.appendChild(th);
            }

            const tbody = table.createTBody();
            for (let i = 0; i <= n; i++) {
                const row = tbody.insertRow();
                row.insertCell(0).innerHTML = periodos[i] + " de " + n;
                row.insertCell(1).innerHTML = `$${saldo_inicial[i].toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}`;
                row.insertCell(2).innerHTML = `$${int[i].toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}`;
                row.insertCell(3).innerHTML = `$${iva[i].toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}`;
                row.insertCell(4).innerHTML = `$${amort[i].toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}`;
                row.insertCell(5).innerHTML = `$${pago[i].toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}`;
                row.insertCell(6).innerHTML = `$${saldo_final[i].toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}`;
                row.insertCell(7).textContent = paymentDates[i].toLocaleDateString('es-ES');
                row.insertCell(8).textContent = idunique;
                row.insertCell(9).textContent = nameAcred;
                row.insertCell(10).textContent = empresa;
            }

            const tfoot = table.createTFoot();
            const sumRow = tfoot.insertRow(0);
            sumRow.classList.add('totals-row');
            sumRow.insertCell(0).textContent = 'Sum of Displayed Rows';
            for (let i = 1; i <= 10; i++) {
                sumRow.insertCell(i).setAttribute('id', `demosum${i}`);
            }

            return table;
        }

          function getNextDates(fechaActual, cantidadPagos) {
    let fechas = [];
    
    // Insert the initial fechaActual as the first element
    fechas.unshift(new Date(fechaActual)); // Ensure it's a copy of fechaActual
    
    for (let i = 0; i < cantidadPagos; i++) {
        let dia5 = new Date(fechaActual.getFullYear(), fechaActual.getMonth(), 5); // Change to 5th
        let dia20 = new Date(fechaActual.getFullYear(), fechaActual.getMonth(), 20);
        
        if (fechaActual.getDate() < 5) {
            fechas.push(dia5);
        } else if (fechaActual.getDate() < 20) {
            fechas.push(dia20);
        } else {
            // Move to the next month's 5th day
            let nextMonth5 = new Date(fechaActual.getFullYear(), fechaActual.getMonth() + 1, 5);
            fechas.push(nextMonth5);
        }
        
        // Update the current date for the next iteration
        fechaActual = new Date(fechaActual.getFullYear(), fechaActual.getMonth(), fechaActual.getDate());
        if (fechaActual.getDate() < 20) {
            fechaActual.setDate(20); // Move to the 20th of the current month
        } else {
            fechaActual = new Date(fechaActual.getFullYear(), fechaActual.getMonth() + 1, 5); // Move to the next month's 5th
        }
    }
    
    return fechas;
}

        const tablesPromises = submissionIDs.map(submissionID => {
            return fetch(`https://api.jotform.com/submission/${submissionID}?apiKey=${apiKey}`)
                .then(response => response.json())
                .then(data => {
                    var submissions = data.content;
                    var jsonData = {};

                    var response = data.content.answers;
                    const answerTextPairs = [];

                    Object.entries(response).forEach(([key, value]) => {
                        if (value.hasOwnProperty("answer") && value.hasOwnProperty("text")) {
                            if (Object.prototype.toString.call(value.answer) === "[object Object]") {
                                value.answer = Object.values(value.answer).join(" ");
                            }
                            answerTextPairs.push([value.answer, value.text]);
                        }
                    });

                    const newDictionary = answerTextPairs.reduce((dictionary, [answer, text]) => {
                        dictionary[text] = answer;
                        return dictionary;
                    }, {});

                    jsonData = newDictionary;

                    const table = generateTable(jsonData);
                    return table;
                });
        });
        // Function to extract table data and convert to JSON
        function extractTableData(tableId) {
            const table = document.getElementById(tableId);
            const headers = Array.from(table.querySelectorAll('thead th')).map(th => th.textContent.trim());
            const rows = Array.from(table.querySelectorAll('tbody tr'));
        
            const data = rows.map(row => {
                const rowData = {};
                Array.from(row.cells).forEach((cell, index) => {
                    const header = headers[index];
                    rowData[header] = cell.textContent.trim();
                });
                return rowData;
            });
        
            return data;
        }

Promise.all(tablesPromises)
    .then(tables => {
        const tableContainer = document.getElementById("tables-container");
        console.log("Tabla:", tableContainer);

        const headers = ["Periodo", "Saldo Inicial", "Inter\u00e9s", "I.V.A.", "Amortizaci\u00f3n", "Pago parcial", "Saldo Final", "Fecha", "ID", "Acreditada", "Empresa"];
        const concatenatedTable = document.createElement("table");
        concatenatedTable.setAttribute("id", "concatenated-table");
        concatenatedTable.classList.add('TF');

        // Create and append the caption element
        const caption = document.createElement("caption");
        caption.textContent = "Your Table Caption Here";
        concatenatedTable.appendChild(caption);

        const thead = concatenatedTable.createTHead();
        const headerRow = thead.insertRow(0);
        for (let i = 0; i < headers.length; i++) {
            const th = document.createElement("th");
            th.textContent = headers[i];
            headerRow.appendChild(th);
        }

        const tbody = concatenatedTable.createTBody();

        tables.forEach(table => {
            if (table) {
                const rows = table.tBodies[0].rows;
                for (let i = 0; i < rows.length; i++) {
                    tbody.appendChild(rows[i].cloneNode(true));
                }
            }
        });

        const tfoot = concatenatedTable.createTFoot();
        const sumRow = tfoot.insertRow(0);
        sumRow.classList.add('totals-row');
        sumRow.insertCell(0).textContent = 'Total';
        for (let i = 1; i <= 6; i++) {
            sumRow.insertCell(i).setAttribute('id', `demosum${i}`);
        }

        tableContainer.appendChild(concatenatedTable);

        setTimeout(() => {
            console.log("Initializing TableFilter");
            var filtersConfig = {
                base_path: 'https://unpkg.com/tablefilter@latest/dist/tablefilter/',
                paging: {
                    results_per_page: ['Records: ', [10, 25, 50, 100]]
                },
                state: {
                    types: ['local_storage'],
                    filters: true,
                    page_number: true,
                    page_length: true,
                    sort: true
                },
                alternate_rows: true,
                btn_reset: true,
                rows_counter: true,
                loader: {
                    html: '<div id="lblMsg"></div>',
                    css_class: 'myLoader'
                },
                status_bar: {
                    target_id: 'lblMsg',
                    css_class: 'myStatus'
                },
                col_8: 'select',
                col_9: 'select',
                rows_counter: {
                    text: 'Items: '
                },
                ignore_diacritics: true,
                mark_active_columns: true,
                highlight_keywords: true,
                no_results_message: true,
                col_types: [
                    'number', 'formatted-number', 'formatted-number', 'formatted-number', 'formatted-number', 'formatted-number', 
                    'formatted-number', { type: 'date', locale: 'fr' }, 'string', 'string', 'string'
                ],
                extensions: [{
                    name: 'sort'
                }, {
                    name: 'colOps',
                    id: [
                        'demosum1',
                        'demosum2',
                        'demosum3',
                        'demosum4',
                        'demosum5',
                        'demosum6'
                    ],
                    col: [
                        1,
                        2,
                        3,
                        4,
                        5,
                        6
                    ],
                    operation: [
                        'sum',
                        'sum',
                        'sum',
                        'sum',
                        'sum',
                        'sum'
                    ],
                    write_method: [
                        'innerHTML',
                        'innerHTML',
                        'innerHTML',
                        'innerHTML',
                        'innerHTML',
                        'innerHTML'
                    ],
                    decimal_precision: [
                        2,
                        2,
                        2,
                        2,
                        2,
                        2
                    ],
                    tot_row_index: [
                        -1, -1, -1, -1, -1, -1
                    ],
                    tot_row_css_class: 'totals-row',
                    format_result: [
                      { prefix: '$' },
                      { prefix: '$' },
                      { prefix: '$' },
                      { prefix: '$' },
                      { prefix: '$' },
                      { prefix: '$' }
                    ]},
                    {   name: 'colsVisibility',
                      at_start: [2, 3, 4, 6],
                      text: 'Columns: ',
                      enable_tick_all: true
                  }],
            };

            var tf = new TableFilter('concatenated-table', filtersConfig);
            tf.init();

            console.log("TableFilter initialized", tf);
                    // Now that tables are rendered, extract data
                    const tableData = extractTableData('concatenated-table');
                
                    const jsonData = JSON.stringify(tableData); // Convert data to JSON string
                    const parsedData = JSON.parse(jsonData); // Parse JSON string back to array
                    console.log(parsedData)
                    
            
            
                    const parseCurrency = (value) => parseFloat(value.replace(/[\$,]/g, ""));
                    const parseDate = (dateString) => {
                        const [day, month, year] = dateString.split('/');
                        return new Date(year, month - 1, day);
                    };
            
                    const currentDate = new Date();
                    const amountsDue = new Array(12).fill(0);
                    const interestDue = new Array(12).fill(0);
                    const amortDue = new Array(12).fill(0);
            
                    parsedData.forEach(record => {
                        const recordDate = parseDate(record.Fecha);
                        const monthDiff = (recordDate.getFullYear() - currentDate.getFullYear()) * 12 + (recordDate.getMonth() - currentDate.getMonth());
                        if (monthDiff >= 0 && monthDiff < 12) {
                            amountsDue[monthDiff] += parseCurrency(record['Pago parcial']);
                        }
                    });
                    parsedData.forEach(record => {
                        const recordDate = parseDate(record.Fecha);
                        const monthDiff = (recordDate.getFullYear() - currentDate.getFullYear()) * 12 + (recordDate.getMonth() - currentDate.getMonth());
                        if (monthDiff >= 0 && monthDiff < 12) {
                            interestDue[monthDiff] += parseCurrency(record['Interés']);
                        }
                    });
                    parsedData.forEach(record => {
                        const recordDate = parseDate(record.Fecha);
                        const monthDiff = (recordDate.getFullYear() - currentDate.getFullYear()) * 12 + (recordDate.getMonth() - currentDate.getMonth());
                        if (monthDiff >= 0 && monthDiff < 12) {
                            amortDue[monthDiff] += parseCurrency(record['Amortización']);
                        }
                    });
            
                    const labels = [];
                    for (let i = 0; i < 12; i++) {
                        const date = new Date(currentDate.getFullYear(), currentDate.getMonth() + i, 1);
                        labels.push(date.toLocaleString('default', { month: 'short', year: 'numeric' }));
                    }
                    
                    // Insert the current month sum into the HTML
                    document.getElementById("cpcMonthSum").textContent = `$${amountsDue[0].toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}`;
                    document.getElementById("intMonthSum").textContent = `$${interestDue[0].toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}`;
                    
                    const now = new Date();
                    const currentMonth = now.getMonth(); // 0-11 (January is 0)
                    const currentYear = now.getFullYear(); // e.g., 2024
                    const previousMonthDate = new Date(now);
                    previousMonthDate.setMonth(currentMonth - 1); // Move to previous month
                    const pastMonth = previousMonthDate.getMonth(); // Get the month of the previous date
                    const pastYear = previousMonthDate.getFullYear(); // Handle year change
                     // Initialize counters
                    let interesThisMonth = 0;
                    let interesLastMonth = 0;
                    parsedData.forEach(record => {
                        const fecha = parseDate(record.Fecha);

                        if (fecha) {
                            const submissionMonth = fecha.getMonth();  // Get the month of submission (0-11)
                            const submissionYear = fecha.getFullYear();  // Get the year of submission
                    
                            // Check if the submission was created this month
                            if (submissionMonth === currentMonth && submissionYear === currentYear) {
                                interesThisMonth += parseCurrency(record['Interés']);  // Increment counter for this month
                            }
                            // Check if the submission was created up until last month (but not in the current month)
                            else if (fecha.getFullYear() === pastYear && fecha.getMonth() === pastMonth)  {
                                interesLastMonth += parseCurrency(record['Interés']);  // Increment counter for past months
                            }
                        }
                    });
                    
                    const VPUtilidades = ((interesThisMonth/interesLastMonth)-1)*100
                    console.log("Variacion utilidades:", VPUtilidades)
                     /// Get the element where the value will be displayed
                    const VPUtilidadesElement = document.getElementById("VPUtilidades");
                    
                    // Format the value with a plus sign if positive
                    let formattedVPUtilidades = VPUtilidades >= 0 
                        ? `+${VPUtilidades.toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}` 
                        : `${VPUtilidades.toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")}`;
                    
                    // Set the text content of the element
                    VPUtilidadesElement.textContent = `${formattedVPUtilidades}%`;
                    
                    // Change the color based on whether the value is positive or negative
                    if (VPUtilidades >= 0) {
                        VPUtilidadesElement.classList.remove("text-warning");
                        VPUtilidadesElement.classList.add("text-success"); // Green for positive values
                    } else {
                        VPUtilidadesElement.classList.remove("text-success");
                        VPUtilidadesElement.classList.add("text-warning"); // Red for negative values
                    }
                    
                    
                    const ctx = document.getElementById("cpcChart").getContext("2d");
                    new Chart(ctx, {
                        type: "line",
                        data: {
                            labels: labels,
                            datasets: [{
                                label: "Total",
                                tension: 0.4,
                                borderWidth: 0,
                                pointRadius: 2,
                                pointBackgroundColor: "#6a4c93",
                                borderColor: "#6a4c93",
                                borderWidth: 3,
                                backgroundColor: 'transparent',
                                fill: true,
                                data: amountsDue,
                                maxBarThickness: 6
                            },
                            {
                              label: "Interés",
                              tension: 0.4,
                              borderWidth: 0,
                              pointRadius: 2,
                              pointBackgroundColor: "#8ac926",
                              borderColor: "#8ac926",
                              borderWidth: 3,
                              backgroundColor: 'transparent',
                              data: interestDue,
                              maxBarThickness: 6
                            },
                            {
                              label: "Amortización",
                              tension: 0.4,
                              borderWidth: 0,
                              pointRadius: 2,
                              pointBackgroundColor: "#ff595e",
                              borderColor: "#ff595e",
                              borderWidth: 3,
                              backgroundColor: 'transparent',
                              data: amortDue,
                              maxBarThickness: 6
                            }]
                        },
                        options: {
                            responsive: true,
                            maintainAspectRatio: false,
                            plugins: {
                                legend: {
                                    display: false,
                                }
                            },
                            interaction: {
                                intersect: false,
                                mode: 'index',
                            },
                            scales: {
                                y: {
                                    grid: {
                                        drawBorder: false,
                                        display: true,
                                        drawOnChartArea: true,
                                        drawTicks: false,
                                        borderDash: [5, 5]
                                    },
                                    ticks: {
                                        display: true,
                                        padding: 10,
                                        color: '#b2b9bf',
                                        font: {
                                            size: 11,
                                            style: 'normal',
                                            lineHeight: 2
                                        },
                                    }
                                },
                                x: {
                                    grid: {
                                        drawBorder: false,
                                        display: false,
                                        drawOnChartArea: false,
                                        drawTicks: false,
                                        borderDash: [5, 5]
                                    },
                                    ticks: {
                                        display: true,
                                        color: '#b2b9bf',
                                        padding: 10,
                                        font: {
                                            size: 11,
                                            style: 'normal',
                                            lineHeight: 1
                                        },
                                        stepSize: 10000
                                    }
                                },
                            },
                        },
                    });
                }, 100); 
        
            });
    });
