//Url config spreadsheet----------------------------------------------------------------------------------
//********************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
var developer_email = 'developer email';
/*********************************************************************************************************
Script:      Merchant control
Version:     08.08.2026
Created By:  Stanislav Jilek [standajilek.cz]
/********************************************************************************************************/

function main() {
//Settings------------------------------------------------------------------------------------------------
    var settings_sheet = ss_config.getSheetByName("merchant_control");
    var mail = settings_sheet.getRange("B2").getValue();
    var subject = settings_sheet.getRange("B3").getValue();
    var send_mail = 0;
    var row = 0;
    var settings = settings_sheet.getRange("A5:E" + settings_sheet.getLastRow()).getValues();

//HTML body table
    var table = "<table border='1' style='border-collapse: collapse;' cellpadding='5'>";
    table += "<tr><td colspan='6' bgcolor='#4fabe5'><strong>Google Merchant Center control</strong></td></tr>";
    table += "<tr bgcolor='#ffd75d'><th>Account</th><th>Products</th><th>Active</th><th>Disapproved</th><th>Pending</th><th>Expiring</th></tr>";

    //Cycle for a number of accounts--------------------------------------------------------------------------
    for (var i = 0; i < settings.length; i++)
    {
        var accountId = settings[i][1];
        try {
            if (accountId != "")
            {
                var products = aggregate_product_statuses(accountId);
                products = products !== undefined ? products : [{country: settings[i][2].toLowerCase(), reportingContext: "SHOPPING_ADS", stats: {expiringCount: 0, pendingCount: 0, activeCount: 0, disapprovedCount: 0}}];

                for (var j = 0; j < products.length; j++)
                {
                    if ((products[j].country.toLowerCase() === settings[i][2].toLowerCase() || settings[i][2] === "") && products[j].reportingContext === "SHOPPING_ADS")
                    {
                        var accountName = products.length > 1 && settings[i][2] === "" ? settings[i][0] + ": " + products[j].country + " (id: " + accountId + ")" : settings[i][0] + " (id: " + accountId + ")";
                        var statistics = products[j].stats !== undefined ? products[j].stats : {};
                        var expiring = parseFloat(statistics.expiringCount) || 0;
                        var pending = parseFloat(statistics.pendingCount) || 0;
                        var active = parseFloat(statistics.activeCount) || 0;
                        var disapproved = parseFloat(statistics.disapprovedCount) || 0;
                        var sum = expiring + pending + active + disapproved;
                        var percent = null_control(disapproved / sum, disapproved, sum);

                        Logger.log(accountName + " | products: " + sum + " (active: " + active + ", disapproved: " + disapproved + " (" + (percent * 100).toFixed(2) + " %), pending: " + pending + ", expiring: " + expiring + ")");

                        if ((percent > settings[i][3]) || (sum < settings[i][4]))
                        {
                            table += "<tr bgcolor='" + row_color(row) + "'><td nowrap>" + accountName + "</td>" +
                                "<td nowrap align='right'><font color='" + sum_color(sum, settings[i][4]) + "'>" + number_format(sum.toFixed(0)) + "</font></td>" +
                                "<td nowrap align='right'>" + number_format(active.toFixed(0)) + "</td>" +
                                "<td nowrap align='right'><font color='" + disapproved_color(percent, settings[i][3]) + "'>" + number_format(disapproved.toFixed(0)) + " (" + (percent * 100).toFixed(2) + " %)</font></td>" +
                                "<td nowrap align='right'>" + number_format(pending.toFixed(0)) + "</td>" +
                                "<td nowrap align='right'>" + number_format(expiring.toFixed(0)) + "</td></tr>";
                            send_mail = 1;
                            row++;
                        }
                    }
                }
            }
        } catch (err)
        {
            Logger.log(settings[i][0] + " (id: " + accountId + ") | " + err);
        }
    }

//Send mail-----------------------------------------------------------------------------------------------
    if (send_mail == 1)
    {
        MailApp.sendEmail({to: mail, subject: subject, htmlBody: table});
    }
}
//--------------------------------------------------------------------------------------------------------
var gcp_wait = 0;

function aggregate_product_statuses(accountId) {
    try {
        return (MerchantIssueResolution.Accounts.AggregateProductStatuses.list("accounts/" + accountId).aggregateProductStatuses)
    } catch (err)
    {
        if (String(err).indexOf("not registered") === -1)
        {
            throw err
        }

        if (developer_email === "" || developer_email === "developer email")
        {
            throw new Error("GCP projekt neni zaregistrovan, vyplnte developer_email na zacatku skriptu")
        }

        MerchantAccounts.Accounts.DeveloperRegistration.registerGcp({developerEmail: developer_email}, "accounts/" + accountId + "/developerRegistration");

        try {
            return (MerchantIssueResolution.Accounts.AggregateProductStatuses.list("accounts/" + accountId).aggregateProductStatuses)
        } catch (e)
        {
            if (gcp_wait === 0)
            {
                Utilities.sleep(300000);
                gcp_wait = 1;
            }
            return (MerchantIssueResolution.Accounts.AggregateProductStatuses.list("accounts/" + accountId).aggregateProductStatuses)
        }
    }
}
//--------------------------------------------------------------------------------------------------------
function number_format(number) {
    number = number.toString();
    number = number.split("").reverse().join("");
    number = number.substr(0, 3) + " " + number.substr(3, 3) + " " + number.substr(6, 3) + " " + number.substr(9, 3) + " " + number.substr(12, 3);
    number = number.split("").reverse().join("");
    number = number.trim();
    return(number)
}
//--------------------------------------------------------------------------------------------------------
function row_color(row) {
    if (row % 2 == 0)
    {
        row = "#ffffff";
    } else
    {
        row = "#d5d5d5";
    }
    return(row)
}
//--------------------------------------------------------------------------------------------------------
function null_control(number, a, b) {
    if (a == 0 || b == 0) {
        number = 0;
    }
    return (number);
}
//--------------------------------------------------------------------------------------------------------
function sum_color(number, control) {
    var color = "black";
    if (control > number)
    {
        color = "red";
    }
    return(color)
}
//--------------------------------------------------------------------------------------------------------
function disapproved_color(number, control) {
    var color = "black";
    if (control < number)
    {
        color = "red";
    }
    return(color)
}