// Reorganisation der Daten des SQL-Adapter (mySQL getestet)
//
// Alle Datensätze in datapoints bzw. ts_number, ts_string,und ts_bool von nicht mehr aktiven states werden gelöscht
// Im ersten Durchlauf werden die Datensätze gelöscht - im zweiten Durchlauf dann die datapoints

var del = false; // true = löschen   false = nur Anzeige

sendTo('sql.0', 'query', 'SELECT * FROM iobroker.datapoints order by id;', function (result)
{
    if (result.error) {
        console.error(result.error);
    } 
    else 
    { 
        list1 = result;
        sendTo('sql.0', 'getEnabledDPs', {}, function (result) {
            list2 = result;

            // die ersten beiden Einträge behalten (admin)
            list1.result[0].id = 0;
            list1.result[0].name = "";
            list1.result[1].id = 0;
            list1.result[1].name = "";

            for (i = 2; i < list1.result.length; i++) // die ersten beiden sind admin Einträge 
            { 
                //var feld = JSON.stringify(list1.result[i].name);
                var name = list1.result[i].name;
                var id = list1.result[i].id;
                var status = list2[name];
                if (status == undefined) 
                {
                    // in der liste lassen, da nicht mehr enabled                    
                }
                else
                {
                    // aus der Liste austragen, da enabled
                    list1.result[i].id = 0;
                    list1.result[i].name = "";
                }
            }

        for (i = 0; i < list1.result.length; i++)
        {
            id =   list1.result[i].id;
            type = list1.result[i].type;
            name = list1.result[i].name;

            if (id > 0) 
            {
                var query = "SELECT id,name,type, count(*) as count FROM iobroker.datapoints where id = " + id + " group by datapoints.id union "
                query += " select id, '' as name, '' as type, count(*) as count from ";
                switch(type)
                {
                    case 0:
                    {
                    query += "iobroker.ts_number where ts_number.id = " + id + " group by ts_number.id;"
                    break;
                    }
                    case 1:
                    {
                    query += "iobroker.ts_string where ts_string.id = " + id + " group by ts_string.id;" 
                    break;
                    }
                    case 2:
                    {
                    query += "iobroker.ts_bool where ts_bool.id = " + id + " group by ts_bool.id;"   
                    break;
                    }
                }
                
                sendTo('sql.0', 'query', query, function (result) 
                {
                    var le = result.result.length;
                    var count = 0;
                    if (le === 2) count = result.result[1].count;
                    id = result.result[0].id;
                    name = result.result[0].name;
                    var typ =  result.result[0].type;

                    console.log(id+": "+name+ " Typ: " + typ + " ---> count:" +count);
                    if (del === true && count > 0) 
                    {
                        if (typ == 0) var query = "delete from iobroker.ts_number where id = " + id + ";";
                        if (typ == 1) var query = "delete from iobroker.ts_string where id = " + id + ";";
                        if (typ == 2) var query = "delete from iobroker.ts_bool where id = " + id + ";";
                        console.warn(query);
                        sendTo ('sql.0', 'query', query, function (result) {});
                    }
                    if (del === true && count == 0) 
                    {
                        var query = "delete from iobroker.datapoints where id = " + id + ";";
                        console.warn(query);
                        sendTo ('sql.0', 'query', query, function (result) {});
                    }
                                       
                });
            }
        }
        });
    }
});