// Author:        Ricardas Stoma
// Company:       Kolmisoft
// Year:          2014
// About:         Script merges several tariffs into one

// DEFINITIONS

#define SCRIPT_VERSION        "1.22"
#define SCRIPT_NAME           "m2_tariff_generator"
#define TMP_TABLE_NAME        "tmp_tarrif_generator_table"
#define RATES_CSV_FILE        "/tmp/m2_tariff_generator_rates.csv"
#define RATEDETAILS_CSV_FILE  "/tmp/m2_tariff_generator_ratedetails.csv"
#define MAX_TARIFFS           50
#define MAX_RATE              999999999

// INCLUDES

#include "m2_functions.c"

// GLOBAL VARIABLES

int owner_id = 0;
char tariff_name[256] = "";
char currency[256] = "";
char currency_name[256] = "";
char profit_margin[256] = "";
char profit_margin_from[256] = "";
char data7[256] = "";
char data8[256] = "";
char data9[256] = "";
char data10[256] = "";
double profit_margin_decimal = 0;
double profit_margin_from_decimal = 0;
int nth_value = 0;
char tariff_list[1024] = "";
double exchange_rate = 1;
int tariff_id = 0;
int prefixes_tariff_id = 0;
char tmp_table_uid[64] = "";
char effective_from[64] = "";
int use_assigned_destinations = 0;
int use_prefix_from_tariff = 0;
int generate_new_tariff = 1;
double do_not_add_margin_if_rate_more_than = 0;
int existing_tariff_id = 0;

typedef struct prefix_list_struct {
    char prefix[128];
} prefix_list_t;

typedef struct rates_struct {
    char prefix[128];
    double rate[MAX_TARIFFS];
    int rates_count;
    unsigned long int rate_id;
    int destination_id;
    int destinationgroup_id;
    double connection_fee;
    int min_time;
    int increment;
    int prefix_len;
    int blocked;
} rates_t;

prefix_list_t *prefix_list = NULL;
int prefix_list_count = 0;

rates_t *final_rates = NULL;
int frates_count = 0;

rates_t *tmp_rates = NULL;
int trates_count = 0;

int tariffs[MAX_TARIFFS] = { 0 };
int tariffs_count = 0;

// FUNCTION DECLARATIONS

int get_existing_tariff_currency(int tariff_id, char *currency);
int get_exchange_rate(char *currency);
int export_rates_to_csv();
int export_ratedetails_to_csv();
int create_new_tariff(char *tariff_name);
int insert_rates();
int insert_ratedetails();
int get_rates_id();
int parse_tariffs(char *tariffs);
int get_destinations();
int find_destination_gaps(int for_tariff_id);
double find_rate_for_prefix(int tmp_index, int final_index);
int compare_rates(const void *a, const void *b);
void set_effective_from();
double get_nth_cheapest_rate(int index);
int prefix_in_tariff(char *prefix);
void get_prefix_list(int tariff_id);
void check_rates();

// MAIN FUNCTION

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

    m2_init("Starting M2 tariff generator\n");

    if (m2_task_get(
            3,
            &owner_id,
            NULL,
            tariff_name,
            currency,
            profit_margin,
            tariff_list,
            profit_margin_from,
            effective_from,
            data7,
            data8,
            data9,
            data10)) {
        return 1;
    }

    m2_task_lock();

    char *separator = NULL;
    separator = strchr(profit_margin_from, ';');
    if (separator) {
        nth_value = atoi(separator + 1) + 1;
        *separator = 0;
        profit_margin_from_decimal = (1 + atof(profit_margin_from)/100);
    }

    profit_margin_decimal = (1 + atof(profit_margin)/100);

    if (strcmp(data7, "use_prefix_only_assigned_to_dg") == 0) {
        use_assigned_destinations = 1;
    }

    if (strstr(data7, "use_prefix_from_tariff,")) {
        use_prefix_from_tariff = 1;
        prefixes_tariff_id = atoi(data7 + strlen("use_prefix_from_tariff,"));
    }

    if (strlen(data8)) {
        do_not_add_margin_if_rate_more_than = atof(data8);
    }

    if (strcmp(data9, "existing_tariff") == 0) {
        generate_new_tariff = 0;
    }

    if (strlen(data10)) {
        existing_tariff_id = atoi(data10);
    }

    m2_log("---------------------------------------------\n");
    if (generate_new_tariff) {
        m2_log("New tariff name: %s\n", tariff_name);
        m2_log("Currency id for new tariff: %s\n", currency);
    } else {
        m2_log("Generate rates for tariff: %d\n", existing_tariff_id);
        strcpy(tariff_name, "tariff_generator_tmp");
        if (get_existing_tariff_currency(existing_tariff_id, currency)) {
            m2_log("Cannot retrieve existing tariff currency\n");
            m2_task_unlock(4);
            return 1;
        }
        m2_log("Tariff currency: %s\n", currency);
    }
    m2_log("Tariffs to merge: %s\n", tariff_list);
    m2_log("Profit margin at least: %s%% (multiplier %0.2f)\n", profit_margin, profit_margin_decimal);
    m2_log("Profit margin %s%% on %d cheapest rate (multiplier %0.2f)\n", profit_margin_from, nth_value, profit_margin_from_decimal);
    m2_log("Do not add profit margin if rate is more than: %f\n", do_not_add_margin_if_rate_more_than);
    m2_log("Data7: %s\n", data7);
    m2_log("Owner id: %d\n", owner_id);
    m2_log("---------------------------------------------\n");

    if (use_prefix_from_tariff && prefixes_tariff_id) {
        get_prefix_list(prefixes_tariff_id);
    }

    parse_tariffs(tariff_list);

    if (get_destinations()) {
        m2_task_unlock(4);
        return 1;
    }

    int i;

    for (i = 0; i < tariffs_count; i++) {
        if (find_destination_gaps(tariffs[i])) {
            m2_task_unlock(4);
            return 1;
        }
    }

    // get exchange rate
    if (get_exchange_rate(currency)) {
        m2_log("Exchange rate for currency %s not found\n", currency);
        m2_task_unlock(4);
        return 1;
    }

    if (global_debug) m2_log("---------------------------------------------\n");
    if (global_debug) m2_log("Final rate table\n");
    if (global_debug) m2_log("---------------------------------------------\n");
    for (i = 0; i < frates_count; i++) {

        double cheapest_rate = 0;
        double nth_rate = 0;

        // just in case
        if (final_rates[i].increment < 1) final_rates[i].increment = 1;

        // sort rates by rate value
        if (final_rates[i].rates_count > 1) {
            qsort(final_rates[i].rate, final_rates[i].rates_count, sizeof(double), compare_rates);
        }

        if (final_rates[i].rates_count == 0) final_rates[i].rates_count = 1;

        int j = 0;
        for (j = 0; j < final_rates[j].rates_count; j++) {
            if (final_rates[i].rate[j] == MAX_RATE) {
                final_rates[i].rate[j] = 0;
            }
        }

        if (global_debug) {
            int j = 0;
            char buffer[2048] = "";
            char small_buffer[256] = "";
            for (j = 0; j < final_rates[i].rates_count; j++) {
                sprintf(small_buffer, "%0.6f ", final_rates[i].rate[j]);
                strcat(buffer, small_buffer);
            }
            m2_log("Prefix: %s\t\t rates: %s\n", final_rates[i].prefix, buffer);
        }

        if (nth_value > 1 && final_rates[i].rates_count > 1 && final_rates[i].rates_count >= nth_value) {
            // compare cheapest rate and n-th rate
            cheapest_rate = final_rates[i].rate[0] * exchange_rate;
            nth_rate = get_nth_cheapest_rate(i) * exchange_rate;

            // only add profit margin if rate is not higher than X (or when feature is disabled)
            if (!(do_not_add_margin_if_rate_more_than > 0 && cheapest_rate > do_not_add_margin_if_rate_more_than)) {
                cheapest_rate = cheapest_rate * profit_margin_decimal;
            }

            // only add profit margin if rate is not higher than X (or when feature is disabled)
            if (!(do_not_add_margin_if_rate_more_than > 0 && nth_rate > do_not_add_margin_if_rate_more_than)) {
                nth_rate = nth_rate * profit_margin_from_decimal;
            }

            if (nth_rate < cheapest_rate) {
                final_rates[i].rate[0] = cheapest_rate;
            } else {
                final_rates[i].rate[0] = nth_rate;
            }
        } else {
            // we have less than n rates so let's use cheapest rate
            final_rates[i].rate[0] = final_rates[i].rate[0] * exchange_rate;

            // only add profit margin if rate is not higher than X (or when feature is disabled)
            if (!(do_not_add_margin_if_rate_more_than > 0 && final_rates[i].rate[0] > do_not_add_margin_if_rate_more_than)) {
                final_rates[i].rate[0] = final_rates[i].rate[0] * profit_margin_decimal;
            }
        }
    }

    if (global_debug) m2_log("\n");

    check_rates();

    if (frates_count > 0) {

        if (create_new_tariff(tariff_name)) {
            m2_log("New tariff '%s' was not created\n", tariff_name);
            m2_task_unlock(4);
            return 1;
        }

        if (export_rates_to_csv()) {
            m2_log("Rates CSV export failed\n");
            m2_task_unlock(4);
            return 1;
        }

        if (insert_rates()) {
            m2_log("Failed to insert new rates\n");
            m2_task_unlock(4);
            return 1;
        }

        if (get_rates_id()) {
            m2_log("Failed to get rates id\n");
            m2_task_unlock(4);
            return 1;
        }

        if (export_ratedetails_to_csv()) {
            m2_log("Ratedetails CSV export failed\n");
            m2_task_unlock(4);
            return 1;
        }

        if (insert_ratedetails()) {
            m2_log("Failed to insert new ratedetails\n");
            m2_task_unlock(4);
            return 1;
        }

        // set effective from
        if (strlen(effective_from)) {
            set_effective_from();
        }

        // Set new rates for existing tariff
        if (generate_new_tariff == 0 && existing_tariff_id) {
            char query[512] = "";
            m2_log("Inserting rates to existing tariff: %d\n", existing_tariff_id);

            // Delete rates from existing tariff that have the same prefix and the same effective from in the new tariff
            sprintf(query, "DELETE rates "
                             "FROM rates "
                             "JOIN rates AS new_rates ON (rates.tariff_id = %d AND new_rates.tariff_id = %d AND rates.prefix = new_rates.prefix AND rates.effective_from = new_rates.effective_from) "
                            "WHERE rates.tariff_id = %d AND rates.effective_from IS NOT NULL", existing_tariff_id, tariff_id, existing_tariff_id);
            m2_mysql_query(query);

            // Set tariff id for new rates
            sprintf(query, "UPDATE rates SET tariff_id = %d WHERE tariff_id = %d", existing_tariff_id, tariff_id);
            m2_mysql_query(query);

            sprintf(query, "UPDATE tariffs SET last_change_datetime = NOW(), changes_present = 1 WHERE id = %d", existing_tariff_id);
            m2_mysql_query(query);

            // Delete unused tariff
            sprintf(query, "DELETE FROM tariffs WHERE id = %d\n", tariff_id);
            m2_mysql_query(query);
        }

    } else {
        m2_log("Rates not found\n");
    }

    m2_task_finish();

    if (final_rates) {
        free(final_rates);
        final_rates = NULL;
    }

    if (tmp_rates) {
        free(tmp_rates);
        tmp_rates = NULL;
    }

    m2_log("Task complete\n");

    return 0;

}


/*

    ############  FUNCTIONS #######################################################

*/


/*
    Get exchange rate for specific currency
*/


int get_exchange_rate(char *currency) {

    m2_log("Reading exchange rate for currency: %s\n", currency);

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[4096] = "";

    sprintf(query, "SELECT exchange_rate, name FROM currencies WHERE id = %s LIMIT 1", currency);

    if (m2_mysql_query(query)) {
        return 1;
    }

    result = mysql_store_result(&mysql);
    if (result) {
        row = mysql_fetch_row(result);
        if (row) {
            if (row[0]) exchange_rate = atof(row[0]);
            if (row[1]) strcpy(currency_name, row[1]);
        }
    }

    mysql_free_result(result);

    m2_log("Currency name: %s, exchange_rate: %0.5f\n", currency_name, exchange_rate);

    return 0;

}


/*
    Insert rates to database
*/


int insert_rates() {

    m2_log("Inserting rates into database\n");

    char query[4096] = "";

    sprintf(query, "LOAD DATA LOCAL INFILE '" RATES_CSV_FILE "' INTO TABLE rates "
        "FIELDS TERMINATED BY ';' ENCLOSED BY '\\'' LINES TERMINATED BY '\\n' "
        "(prefix, tariff_id, destination_id, destinationgroup_id)");

    if (m2_mysql_query(query)) {
        return 1;
    }

    return 0;

}


/*
    Export rates to CSV file
*/


int export_rates_to_csv() {

    m2_log("Exporting rates to CSV file " RATES_CSV_FILE "\n");

    unsigned long int i = 0;
    FILE *fp = fopen(RATES_CSV_FILE, "w");

    if (fp == NULL) {
        m2_log("Cannot open " RATES_CSV_FILE "\n");
        return 1;
    }

    for (i = 0; i < frates_count; i++) {
        fprintf(fp, "'%s';'%d';'%d';'%d'\n", final_rates[i].prefix, tariff_id, final_rates[i].destination_id, final_rates[i].destinationgroup_id);
    }

    fclose(fp);

    return 0;

}


/*
    Export ratedetails to CSV file
*/


int export_ratedetails_to_csv() {

    m2_log("Exporting ratedetails to CSV file " RATEDETAILS_CSV_FILE "\n");

    unsigned long int i = 0;
    FILE *fp = fopen(RATEDETAILS_CSV_FILE, "w");

    if (fp == NULL) {
        m2_log("Cannot open " RATEDETAILS_CSV_FILE "\n");
        return 1;
    }

    for (i = 0; i < frates_count; i++) {
        fprintf(fp, "'%lu';'%f';'%d';'%d';'%f';'%d'\n",
            final_rates[i].rate_id, final_rates[i].rate[0], final_rates[i].min_time, final_rates[i].increment, final_rates[i].connection_fee, final_rates[i].blocked);
    }

    fclose(fp);

    return 0;

}


/*
    Create new tariff with given name and check if tariff already exists with this name
*/


int create_new_tariff(char *tariff_name) {

    if (generate_new_tariff) {
        m2_log("Checking if tariff '%s' already exists\n", tariff_name);
    }

    m2_escape_string(tariff_name, '\'');

    MYSQL_RES *result1;
    MYSQL_RES *result2;
    MYSQL_ROW row;

    char query[4096] = "";

    sprintf(query, "SELECT id FROM tariffs WHERE name = '%s' LIMIT 1", tariff_name);

    if (m2_mysql_query(query)) {
        return 1;
    }

    result1 = mysql_store_result(&mysql);
    if (result1) {
        row = mysql_fetch_row(result1);
        if (row) {
            if (row[0]) tariff_id = atoi(row[0]);
        }
    }

    mysql_free_result(result1);

    if (tariff_id) {

        m2_log("Tariff '%s' already exists! Another tariff with the same name will not be created\n",
            tariff_name);
        m2_task_unlock(4);
        exit(1);

    } else {

        if (generate_new_tariff) {
            m2_log("Tariff '%s' does not exists. New tariff will be created\n", tariff_name);
        }

        sprintf(query, "INSERT INTO tariffs (name, purpose, currency, last_change_datetime, changes_present) VALUES ('%s', 'user_wholesale', '%s', NOW(), 1)",
            tariff_name, currency_name);

        if (m2_mysql_query(query)) {
            return 1;
        }

        sprintf(query, "SELECT id FROM tariffs WHERE name = '%s' LIMIT 1", tariff_name);

        if (m2_mysql_query(query)) {
            return 1;
        }

        result2 = mysql_store_result(&mysql);
        if (result2) {
            row = mysql_fetch_row(result2);
            if (row) {
                if (row[0]) tariff_id = atoi(row[0]);
            }
        }

        mysql_free_result(result2);

        if (!tariff_id) {
            return 1;
        }

    }

    if (generate_new_tariff) {
        m2_log("New tariff '%s' was successfully created! Tariff id: %d\n", tariff_name, tariff_id);
    }

    // create action for new tariff
    if (generate_new_tariff == 0) {
        sprintf(query, "INSERT INTO actions(date, action, target_id, target_type, data, user_id) VALUES(NOW(), 'tariff_created', %d, 'tariff', 'Name: %s', %d)",
            tariff_id, tariff_name, owner_id);
    } else {
        sprintf(query, "INSERT INTO actions(date, action, target_id, target_type, data, user_id) VALUES(NOW(), 'tariff_updated', %d, 'tariff', '', %d)",
            existing_tariff_id, owner_id);
    }

    if (m2_mysql_query(query)) {
        return 1;
    }

    return 0;

}


/*
    Get rates id for ratedetails mapping (because we will be sorting by destinations, then mapping between ratedetails and rates should be direct)
*/


int get_rates_id() {

    m2_log("Reading rates id from database\n");

    MYSQL_RES *result;
    MYSQL_ROW row;
    int count = 0;

    char query[4096] = "";

    if (use_assigned_destinations) {
        sprintf(query, "SELECT rates.id FROM rates INNER JOIN destinations ON destinations.prefix = rates.prefix WHERE rates.prefix REGEXP '^[0-9]+$' = 1 AND tariff_id = %d AND destinations.destinationgroup_id > 0 ORDER BY rates.prefix", tariff_id);
    } else {
        sprintf(query, "SELECT id FROM rates WHERE rates.prefix REGEXP '^[0-9]+$' = 1 AND tariff_id = %d ORDER BY prefix", tariff_id);
    }

    if (m2_mysql_query(query)) {
        return 1;
    }

    result = mysql_store_result(&mysql);

    if (result) {
        while (( row = mysql_fetch_row(result) )) {
            if (row[0]) {
                if (count < frates_count) {
                    final_rates[count].rate_id = atol(row[0]);
                }
                count++;
            }
        }
    }

    mysql_free_result(result);

    return 0;

}


/*
    Get rates id for ratedetails mapping (because we will be sorting by destinations,
    then mapping between ratedetails and rates should be direct)
*/


int insert_ratedetails() {

    m2_log("Inserting ratedetails into database\n");

    char query[4096] = "";

    sprintf(query, "LOAD DATA LOCAL INFILE '" RATEDETAILS_CSV_FILE "' INTO TABLE ratedetails "
        "FIELDS TERMINATED BY ';' ENCLOSED BY '\\'' LINES TERMINATED BY '\\n' (rate_id, rate, min_time, increment_s, connection_fee, blocked)");

    if (m2_mysql_query(query)) {
        return 1;
    }

    return 0;

}


/*
    Parse tariffs from string like '1,2,3,4,5,88,94'
*/


int parse_tariffs(char *tariffs_str) {

    if (!strlen(tariffs_str)) return 0;

    char tmp_tariff_list[1024] = "";
    strcpy(tmp_tariff_list, tariffs_str);
    char *ch = NULL;

    ch = strtok(tmp_tariff_list, ",");
    if (ch) {
        tariffs[tariffs_count] = atoi(ch);
        tariffs_count++;
    }
    while (ch != NULL) {
        ch = strtok(NULL, ",");
        if (ch) {
            tariffs[tariffs_count] = atoi(ch);
            tariffs_count++;
        }
    }

    return 0;

}


/*
    Get unique destinations from all of the selected tariffs
*/


int get_destinations() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[4096] = "";
    char local_tariff_list[256] = "";

    strcpy(local_tariff_list, tariff_list);

    if (use_prefix_from_tariff && prefixes_tariff_id) {
        char buffer[50] = "";

        sprintf(buffer, ",%d", prefixes_tariff_id);
        strcat(local_tariff_list, buffer);
    }

    m2_log("Reading unique destinations from tariffs '%s'\n", local_tariff_list);

    if (use_assigned_destinations) {
        sprintf(query, "SELECT * FROM (SELECT rates.prefix, destinations.id, destinations.destinationgroup_id FROM rates LEFT JOIN destinations ON destinations.prefix = rates.prefix WHERE tariff_id IN (%s) AND destinations.destinationgroup_id > 0 "
            "AND (rates.effective_from <= NOW() OR rates.effective_from IS NULL) AND rates.prefix REGEXP '^[0-9]+$' = 1 ORDER BY rates.effective_from DESC) A GROUP BY A.prefix ORDER BY A.prefix", local_tariff_list);
    } else {
        sprintf(query, "SELECT * FROM (SELECT rates.prefix, destinations.id, destinations.destinationgroup_id FROM rates LEFT JOIN destinations ON destinations.prefix = rates.prefix WHERE tariff_id IN (%s) AND "
            "(rates.effective_from <= NOW() OR rates.effective_from IS NULL) AND rates.prefix REGEXP '^[0-9]+$' = 1 ORDER BY rates.effective_from DESC) A GROUP BY A.prefix ORDER BY A.prefix", local_tariff_list);
    }

    if (m2_mysql_query(query)) {
        return 1;
    }

    result = mysql_store_result(&mysql);

    if (result) {
        while (( row = mysql_fetch_row(result) )) {
            if (row[0] && row[1]) {
                final_rates = realloc(final_rates, (frates_count + 1) * sizeof(rates_t));
                memset(&final_rates[frates_count], 0, sizeof(rates_t));
                final_rates[frates_count].prefix_len = 0;
                strcpy(final_rates[frates_count].prefix, row[0]);
                final_rates[frates_count].rate[0] = MAX_RATE; // for comparison
                final_rates[frates_count].rate_id = 0;
                final_rates[frates_count].connection_fee = 0;
                final_rates[frates_count].increment = 1;
                final_rates[frates_count].min_time = 0;
                final_rates[frates_count].blocked = 0;
                if (row[1]) {
                    final_rates[frates_count].destination_id = atoi(row[1]);
                } else {
                    final_rates[frates_count].destination_id = 0;
                }
                if (row[2]) {
                    final_rates[frates_count].destinationgroup_id = atoi(row[2]);
                } else {
                    final_rates[frates_count].destinationgroup_id = 0;
                }
                frates_count++;
            }
        }
    }

    m2_log("Got %d unique destinations\n", frates_count);

    mysql_free_result(result);

    return 0;

}


/*
    Find gaps in tariffs
*/


int find_destination_gaps(int for_tariff_id) {

    m2_log("Reading destinations from tariff '%d'\n", for_tariff_id);

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[4096] = "";
    int count = 0;
    int fixed_count = 0;
    int i = 0;
    int j = 0;

    if (tmp_rates) {
        free(tmp_rates);
        tmp_rates = NULL;
    }
    trates_count = 0;

    if (use_assigned_destinations) {
        sprintf(query, "SELECT rates_prefix, rate_after_exchange, MAX(min_time), MAX(increment_s), MAX(connection_fee), MAX(blocked) FROM (SELECT rates.prefix AS 'rates_prefix', rate / exchange_rate AS rate_after_exchange, "
            "min_time, increment_s, connection_fee, blocked "
            "FROM rates "
            "INNER JOIN tariffs ON (rates.tariff_id = tariffs.id AND tariffs.id = %d) "
            "INNER JOIN ratedetails ON rates.id = ratedetails.rate_id "
            "INNER JOIN currencies ON currencies.name = tariffs.currency "
            "INNER JOIN destinations ON destinations.prefix = rates.prefix "
            "WHERE rates.prefix REGEXP '^[0-9]+$' = 1 AND destinations.destinationgroup_id > 0 AND (rates.effective_from <= NOW() OR rates.effective_from IS NULL) ORDER BY rates.effective_from DESC) A "
            "GROUP BY A.rates_prefix ORDER BY A.rates_prefix", for_tariff_id);
    } else {
        sprintf(query, "SELECT prefix, rate_after_exchange, MAX(min_time), MAX(increment_s), MAX(connection_fee), MAX(blocked) FROM (SELECT prefix, rate / exchange_rate AS rate_after_exchange, "
            "min_time, increment_s, connection_fee, blocked "
            "FROM rates "
            "INNER JOIN tariffs ON (rates.tariff_id = tariffs.id AND tariffs.id = %d) "
            "INNER JOIN ratedetails ON rates.id = ratedetails.rate_id "
            "INNER JOIN currencies ON currencies.name = tariffs.currency "
            "WHERE rates.prefix REGEXP '^[0-9]+$' = 1 AND (rates.effective_from <= NOW() OR rates.effective_from IS NULL) ORDER BY rates.effective_from DESC) A "
            "GROUP BY A.prefix ORDER BY A.prefix", for_tariff_id);
    }

    if (m2_mysql_query(query)) {
        return 1;
    }

    result = mysql_store_result(&mysql);

    if (result) {
        while (( row = mysql_fetch_row(result) )) {
            if (row[0] && row[1]) {

                tmp_rates = realloc(tmp_rates, (trates_count + 1) * sizeof(rates_t));
                memset(&tmp_rates[trates_count], 0, sizeof(rates_t));
                tmp_rates[trates_count].prefix_len = strlen(row[0]);
                strcpy(tmp_rates[trates_count].prefix, row[0]);
                tmp_rates[trates_count].rate[0] = atof(row[1]);
                if (row[2]) tmp_rates[trates_count].min_time = atoi(row[2]);
                if (row[3]) tmp_rates[trates_count].increment = atoi(row[3]);
                if (row[4]) tmp_rates[trates_count].connection_fee = atof(row[4]);
                if (row[5]) tmp_rates[trates_count].blocked = atoi(row[5]);

                trates_count++;

            }
        }
    }

    if (trates_count == 0) {
        if (global_debug) m2_log("\n");
        if (global_debug) m2_log("---------------------------------------------\n");
        m2_log("No rates in tariff: %d\n", for_tariff_id);
        if (global_debug) m2_log("---------------------------------------------\n");
        if (global_debug) m2_log("\n");
        return 0;
    } else {
        if (global_debug) {
            m2_log("\n");
            m2_log("---------------------------------------------\n");
            m2_log("Rate table for tariff: %d\n", for_tariff_id);
            m2_log("---------------------------------------------\n");
            m2_log("\n");
        }
    }

    for (i = 0; i < frates_count; i++) {

        if (j < trates_count && strcmp(final_rates[i].prefix, tmp_rates[j].prefix) == 0) {
            if (global_debug) m2_log("Prefix: %s\t\t%0.6f\n", tmp_rates[j].prefix, tmp_rates[j].rate[0]);
            final_rates[i].rate[final_rates[i].rates_count] = tmp_rates[j].rate[0];

            // get max increment
            if (final_rates[i].increment < tmp_rates[j].increment) {
                final_rates[i].increment = tmp_rates[j].increment;
            }

            // get max min_time
            if (final_rates[i].min_time < tmp_rates[j].min_time) {
                final_rates[i].min_time = tmp_rates[j].min_time;
            }

            // get blocked
            if (tmp_rates[j].blocked == 1) {
                final_rates[i].blocked = 1;
            }

            // get max connection_fee
            if (final_rates[i].connection_fee < tmp_rates[j].connection_fee) {
                final_rates[i].connection_fee = tmp_rates[j].connection_fee;
            }

            final_rates[i].rates_count++;
            j++;
        } else {
            count++;
            double new_rate = find_rate_for_prefix(j - 1, i);

            if (new_rate != -2) {
                fixed_count++;
                if (global_debug) m2_log("Prefix: %s\t\t%0.6f fixed gap\n", final_rates[i].prefix, new_rate);
            } else {
                if (global_debug) m2_log("Prefix: %s\t\t-\n", final_rates[i].prefix);
            }
        }

    }

    if (global_debug) m2_log("\n");
    m2_log("Found %d destination gaps, fixed %d gaps in tariff: %d\n", count, fixed_count, for_tariff_id);
    if (global_debug) m2_log("\n");

    mysql_free_result(result);

    return 0;

}


/*
    Get closest shorter prefix
*/


double find_rate_for_prefix(int tmp_index, int final_index) {

    int i = 0;
    int prefix_strlen = 0;
    char prefix[128] = "";

    if (tmp_index < 0) return -2;

    prefix_strlen = strlen(final_rates[final_index].prefix);
    strcpy(prefix, final_rates[final_index].prefix);

    for (i = tmp_index; i >= 0; i--) {
        if (tmp_rates[i].prefix[0] != prefix[0]) return -2;
        if (tmp_rates[i].prefix_len < prefix_strlen) {
            if (strncmp(tmp_rates[i].prefix, prefix, tmp_rates[i].prefix_len) == 0) {
                final_rates[final_index].rate[final_rates[final_index].rates_count] = tmp_rates[i].rate[0];

                // get max increment
                if (final_rates[final_index].increment < tmp_rates[i].increment) {
                    final_rates[final_index].increment = tmp_rates[i].increment;
                }

                // get max min_time
                if (final_rates[final_index].min_time < tmp_rates[i].min_time) {
                    final_rates[final_index].min_time = tmp_rates[i].min_time;
                }

                // get blocked
                if (tmp_rates[i].blocked == 1) {
                    final_rates[final_index].blocked = 1;
                }

                // get max connection_fee
                if (final_rates[final_index].connection_fee < tmp_rates[i].connection_fee) {
                    final_rates[final_index].connection_fee = tmp_rates[i].connection_fee;
                }

                final_rates[final_index].rates_count++;
                return tmp_rates[i].rate[0];
            }
        }
    }

    return 0;

}


/*
    Sort function
*/


int compare_rates(const void *a, const void *b) {
    if (*(double*)a > *(double*)b) {
        return 1;
    } else if (*(double*)a < *(double*)b) {
        return -1;
    } else {
        return 0;
    }
}


/*
     Set effective from date for new tariff
*/


void set_effective_from() {
    char query[1024] = "";
    sprintf(query, "UPDATE rates SET effective_from = '%s' WHERE tariff_id = %d", effective_from, tariff_id);
    m2_mysql_query(query);
}


/*
    Get n-th cheapest rate
*/


double get_nth_cheapest_rate(int index) {

    double nth_cheapest_rate = final_rates[index].rate[0];
    int unique_count = 0;
    int i = 0;

    for (i = 0; i < final_rates[index].rates_count; i++) {
        if (i == 0) {
            unique_count++;
        } else if (nth_cheapest_rate != final_rates[index].rate[i]) {
            unique_count++;
        }
        nth_cheapest_rate = final_rates[index].rate[i];
        if (unique_count == nth_value) break;
    }

    return nth_cheapest_rate;

}


/*
    Check if prefix is in tariff
*/


int prefix_in_tariff(char *prefix) {

    int in_prefix = 0;
    int i = 0;

    for (i = 0; i < prefix_list_count; i++) {
        if (prefix[0] != prefix_list[i].prefix[0]) continue;

        if (strcmp(prefix, prefix_list[i].prefix) == 0) {
            in_prefix = 1;
            break;
        }
    }

    return in_prefix;
}


/*
    Get list of prefixes from specific tariff
*/


void get_prefix_list(int tariff_id) {

    m2_log("Reading rates id from database\n");

    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[4096] = "";

    sprintf(query, "SELECT prefix FROM rates WHERE tariff_id = %d AND prefix REGEXP '^[0-9]+$' = 1 ORDER BY prefix", tariff_id);

    if (m2_mysql_query(query)) {
        return;
    }

    result = mysql_store_result(&mysql);

    if (result) {
        while (( row = mysql_fetch_row(result) )) {
            if (row[0]) {
                prefix_list = realloc(prefix_list, (prefix_list_count + 1) * sizeof(prefix_list_t));
                memset(&prefix_list[prefix_list_count], 0, sizeof(prefix_list_t));
                strcpy(prefix_list[prefix_list_count].prefix, row[0]);
                prefix_list_count++;
            }
        }
    }

    mysql_free_result(result);

}


/*
    Check if rates are valid
*/


void check_rates() {
    int i = 0;

    if (use_prefix_from_tariff) {
        rates_t *new_rates = NULL;
        int new_rates_count = 0;

        for (i = 0; i < frates_count; i++) {
            if (prefix_in_tariff(final_rates[i].prefix)) {
                new_rates = realloc(new_rates, (new_rates_count + 1) * sizeof(rates_t));
                memcpy(&new_rates[new_rates_count], &final_rates[i], sizeof(rates_t));
                new_rates_count++;
            }
        }

        memcpy(final_rates, new_rates, new_rates_count * sizeof(rates_t));
        frates_count = new_rates_count;
    }
}


/*
    Get existing tariff currency
*/


int get_existing_tariff_currency(int tariff_id, char *currency) {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[4096] = "";

    sprintf(query, "SELECT currencies.id FROM tariffs JOIN currencies ON currencies.name = tariffs.currency WHERE tariffs.id = %d LIMIT 1", tariff_id);

    if (m2_mysql_query(query)) {
        return 1;
    }

    result = mysql_store_result(&mysql);
    if (result) {
        row = mysql_fetch_row(result);
        if (row) {
            if (row[0]) strcpy(currency, row[0]);
        }
    }

    mysql_free_result(result);

    return 0;
}