// Author:        Ricardas Stoma
// Company:       Kolmisoft
// Year:          2015
// About:         Script adds missing destinations


#define SCRIPT_VERSION "1.1"
#define SCRIPT_NAME    "m2_destinations_fix"

#include "m2_functions.c"

void format_prefix_sql(char *prefixes, char *number);

// MAIN FUNCTION

int main(int argc, char *argv[]) {

    MYSQL_RES *result;
    MYSQL_RES *result_destinations;
    MYSQL_ROW row;
    MYSQL_ROW row_destinations;

    int missing_destinations = 0;
    char prefix_sql[9000] = "";
    char query[9900] = "";

    // starting sript
    m2_init("Starting M2 Destinations Fix script\n");

    // check if there are missing destinations
    if (m2_mysql_query("SELECT DISTINCT rates.prefix FROM rates LEFT JOIN destinations ON destinations.prefix = rates.prefix WHERE destinations.prefix IS NULL AND LENGTH(rates.prefix) > 0")) {
        exit(1);
    }

    result = mysql_store_result(&mysql);
    if (mysql_num_rows(result)) {
        while (( row = mysql_fetch_row(result) )) {
            if (row[0]) {

                char prefix[256] = "";
                char direction_code[256] = "";
                char name[256] = "";
                char destinationgroup_id[256] = "0";
                char subcode[256] = "";

                strcpy(prefix_sql, "");
                strcpy(prefix, row[0]);

                format_prefix_sql(prefix_sql, prefix);
                sprintf(query, "SELECT direction_code, name, destinationgroup_id, subcode FROM destinations WHERE prefix IN (%s) ORDER BY LENGTH(prefix) DESC LIMIT 1", prefix_sql);

                // get closest matching destination
                if (m2_mysql_query(query)) {
                    exit(1);
                }

                result_destinations = mysql_store_result(&mysql);
                if (mysql_num_rows(result_destinations)) {
                    while (( row_destinations = mysql_fetch_row(result_destinations) )) {
                        if (row_destinations[0]) {
                            strcpy(direction_code, row_destinations[0]);
                        }
                        if (row_destinations[1]) {
                            strcpy(name, row_destinations[1]);
                        }
                        if (row_destinations[2]) {
                            strcpy(destinationgroup_id, row_destinations[2]);
                        }
                        if (row_destinations[3]) {
                            strcpy(subcode, row_destinations[3]);
                        }
                    }
                }
                mysql_free_result(result_destinations);

                // escape strings
                m2_escape_string(direction_code, '\'');
                m2_escape_string(name, '\'');
                m2_escape_string(subcode, '\'');

                sprintf(query, "INSERT INTO destinations(prefix, direction_code, name, destinationgroup_id, subcode) VALUES('%s', '%s', '%s', '%s', '%s')",
                    prefix, direction_code, name, destinationgroup_id, subcode);

                m2_log("%s\n", query);

                // insert new destination
                if (m2_mysql_query(query)) {
                    exit(1);
                }

                missing_destinations++;
            }
        }
    }
    mysql_free_result(result);

    // final touch
    if (m2_mysql_query("UPDATE rates JOIN destinations ON destinations.prefix = rates.prefix SET rates.destination_id = destinations.id, rates.destinationgroup_id = destinations.destinationgroup_id")) {
        exit(1);
    }

    if (missing_destinations) {
        m2_log("Missing destinations fixed: %d\n", missing_destinations);
    } else {
        m2_log("Missing destinations not found. Everything looks OK.\n");
    }

    m2_log("Script completed\n");

    return 0;

}

void format_prefix_sql(char *prefixes, char *number) {

    char buffer[256] = "";
    int i = 0;

    memset(buffer, '\0', sizeof(buffer));
    memset(prefixes, '\0', 9000);

    for(i = 0; i < strlen(number); i++) {

        strcat(prefixes, "'");
        strncpy(buffer, number, i + 1);
        strcat(prefixes, buffer);
        strcat(prefixes, "'");
        if( i < (strlen(number) - 1)) strcat(prefixes, ",");
        memset(buffer, '\0', sizeof(buffer));

    }

}