// Author:        Ricardas Stoma
// Company:       Kolmisoft
// Year:          2015
// About:         Script compares two tariffs

// DEFINITIONS

#define SCRIPT_VERSION  "1.1"
#define SCRIPT_NAME     "m2_compare_tariffs"
#define XLSX_PATH       "/tmp/m2"

// INCLUDES

#include "m2_functions.c"
#include "xlsxwriter.h"

// GLOBAL VARIABLES

char currency[60] = "";
double exchange_rate = 1;

char tariff_a_name[256] = "";
char tariff_b_name[256] = "";

int tariff_a_id = 0;
int tariff_b_id = 0;

char filename[512] = "";

typedef struct rates_struct {
    char name[256];
    char prefix[128];
    double rate[2];
    int rate_missing[2];
    int rate_found[2];
} rates_t;

rates_t *final_rates = NULL;
int frates_count = 0;

rates_t *tmp_rates = NULL;
int trates_count = 0;

// email settings
char email_server[256] = "";
char email_login[256] = "";
char email_password[256] = "";
char email_from[256] = "";
char target_email[256] = "";
int email_port = 0;

int debug = 1;

// FUNCTION DECLARATIONS

void get_destinations();
void get_detinations_from_tariffs(int tariff_index);
void fill_destinations_gaps(int tariff_index);
void get_tariff_names();
int name_compare(const void *elem1, const void *elem2);
int get_email_settings(int owner_id);
int send_xlsx_email();
void email_action_log(int user_id, char *email, int status, char *error);
double get_currency_exchange_rate(char *currency);

// MAIN FUNCTION

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

    int i = 0;
    int i_green = 0;

    m2_init("Starting M2 compare tariffs\n");

    if (argc != 5) {
        m2_log("Wrong number of arguments (%d)!\n", argc);
        return 1;
    }

    tariff_a_id = atoi(argv[1]);
    tariff_b_id = atoi(argv[2]);
    strcpy(target_email, argv[3]);
    strcpy(currency, argv[4]);

    if (!strlen(target_email)) {
        m2_log("Email is empty!\n");
        exit(1);
    }

    if (!strlen(target_email)) {
        m2_log("Currency is empty!\n");
        exit(1);
    }

    if (tariff_a_id <= 0 || tariff_b_id <= 0) {
        m2_log("Tariff ID is 0!\n");
        exit(1);
    }

    get_tariff_names();

    m2_log("Tariff A: %s (id: %d)\n", tariff_a_name, tariff_a_id);
    m2_log("Tariff B: %s (id: %d)\n", tariff_b_name, tariff_b_id);
    m2_log("Email: %s\n", target_email);

    sprintf(filename, XLSX_PATH "/tariff_compare_%d_%d_%s.xlsx", tariff_a_id, tariff_b_id, currency);
    m2_log("Filename: %s\n", filename);

    get_currency_exchange_rate(currency);

    // get unique destinations
    get_destinations();

    // get gaps for tariff A and tariff B
    get_detinations_from_tariffs(0);
    get_detinations_from_tariffs(1);

    // fill destination gaps for tariff A and tariff B
    fill_destinations_gaps(0);
    fill_destinations_gaps(1);

    m2_log("Sorting table by destination name\n");
    qsort(final_rates, frates_count, sizeof(rates_t), name_compare);

    if (debug) m2_log("\n");
    if (debug) m2_log("---------------------------------------------\n");
    if (debug) m2_log("Final rate table\n");
    if (debug) m2_log("---------------------------------------------\n");
    if (debug) m2_log("\n");
    m2_log("Generating XLSX file\n");

    lxw_workbook *workbook = new_workbook(filename);
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
    lxw_worksheet *worksheet_green = workbook_add_worksheet(workbook, NULL);

    lxw_format *header_format = workbook_add_format(workbook);
    format_set_bold(header_format);
    format_set_bg_color(header_format, 0xd0cece);

    lxw_format *green_format = workbook_add_format(workbook);
    format_set_bg_color(green_format, 0xe2efd9);
    format_set_align(green_format, LXW_ALIGN_LEFT);
    format_set_num_format(green_format, "0.00000");

    lxw_format *red_format = workbook_add_format(workbook);
    format_set_bg_color(red_format, 0xfadbd2);
    format_set_align(red_format, LXW_ALIGN_LEFT);
    format_set_num_format(red_format, "0.00000");

    lxw_format *align_format = workbook_add_format(workbook);
    format_set_align(align_format, LXW_ALIGN_LEFT);
    format_set_num_format(align_format, "0.00000");

    lxw_format *blocked_align_format = workbook_add_format(workbook);
    format_set_align(blocked_align_format, LXW_ALIGN_LEFT);
    format_set_num_format(blocked_align_format, "0.00000");
    format_set_font_color(blocked_align_format, 0xff0000);

    lxw_format *gray_align_format = workbook_add_format(workbook);
    format_set_align(gray_align_format, LXW_ALIGN_LEFT);
    format_set_font_color(gray_align_format, 0xd0cece);
    format_set_num_format(gray_align_format, "0.00000");

    lxw_format *green_format_percent = workbook_add_format(workbook);
    format_set_bg_color(green_format_percent, 0xe2efd9);
    format_set_align(green_format_percent, LXW_ALIGN_LEFT);
    format_set_num_format(green_format_percent, "0.0\"%\"");

    lxw_format *bright_green_format_percent = workbook_add_format(workbook);
    format_set_bg_color(bright_green_format_percent, 0x00ff00);
    format_set_align(bright_green_format_percent, LXW_ALIGN_LEFT);
    format_set_num_format(bright_green_format_percent, "0.0\"%\"");

    lxw_format *red_format_percent = workbook_add_format(workbook);
    format_set_bg_color(red_format_percent, 0xfadbd2);
    format_set_align(red_format_percent, LXW_ALIGN_LEFT);
    format_set_num_format(red_format_percent, "0.0\"%\"");

    lxw_format *bright_red_format_percent = workbook_add_format(workbook);
    format_set_bg_color(bright_red_format_percent, 0xff0000);
    format_set_align(bright_red_format_percent, LXW_ALIGN_LEFT);
    format_set_num_format(bright_red_format_percent, "0.0\"%\"");

    worksheet_set_column(worksheet, 0, 0, 30, NULL);
    worksheet_set_column(worksheet, 1, 1, 15, NULL);
    worksheet_set_column(worksheet, 2, 2, 15, NULL);
    worksheet_set_column(worksheet, 3, 3, 15, NULL);
    worksheet_set_column(worksheet, 4, 4, 10, NULL);
    worksheet_set_column(worksheet, 5, 5, 10, NULL);

    worksheet_write_string(worksheet, 0, 0, "Destination", header_format);
    worksheet_write_string(worksheet, 0, 1, "Prefix", header_format);
    worksheet_write_string(worksheet, 0, 2, tariff_a_name, header_format);
    worksheet_write_string(worksheet, 0, 3, tariff_b_name, header_format);
    worksheet_write_string(worksheet, 0, 4, "Diff", header_format);
    worksheet_write_string(worksheet, 0, 5, "Diff %", header_format);

    worksheet_set_column(worksheet_green, 0, 0, 30, NULL);
    worksheet_set_column(worksheet_green, 1, 1, 15, NULL);
    worksheet_set_column(worksheet_green, 2, 2, 15, NULL);
    worksheet_set_column(worksheet_green, 3, 3, 15, NULL);
    worksheet_set_column(worksheet_green, 4, 4, 10, NULL);
    worksheet_set_column(worksheet_green, 5, 5, 10, NULL);

    worksheet_write_string(worksheet_green, 0, 0, "Destination", header_format);
    worksheet_write_string(worksheet_green, 0, 1, "Prefix", header_format);
    worksheet_write_string(worksheet_green, 0, 2, tariff_a_name, header_format);
    worksheet_write_string(worksheet_green, 0, 3, tariff_b_name, header_format);
    worksheet_write_string(worksheet_green, 0, 4, "Diff", header_format);
    worksheet_write_string(worksheet_green, 0, 5, "Diff %", header_format);

    for (i = 0; i < frates_count; i++) {
        worksheet_write_string(worksheet, i + 1, 0, final_rates[i].name, align_format);
        worksheet_write_string(worksheet, i + 1, 1, final_rates[i].prefix, align_format);

        if (!final_rates[i].rate_missing[0]) {
            if (final_rates[i].rate_found[0]) {
                if (final_rates[i].rate[0] == -1) {
                    worksheet_write_string(worksheet, i + 1, 2, "blocked", blocked_align_format);
                } else {
                    worksheet_write_number(worksheet, i + 1, 2, final_rates[i].rate[0], gray_align_format);
                }
            } else {
                if (final_rates[i].rate[0] == -1) {
                    worksheet_write_string(worksheet, i + 1, 2, "blocked", blocked_align_format);
                } else {
                    worksheet_write_number(worksheet, i + 1, 2, final_rates[i].rate[0], align_format);
                }
            }
        }

        if (!final_rates[i].rate_missing[1]) {
            if (final_rates[i].rate_found[1]) {
                if (final_rates[i].rate[1] == -1) {
                    worksheet_write_string(worksheet, i + 1, 3, "blocked", blocked_align_format);
                } else {
                    worksheet_write_number(worksheet, i + 1, 3, final_rates[i].rate[1], gray_align_format);
                }
            } else {
                if (final_rates[i].rate[1] == -1) {
                    worksheet_write_string(worksheet, i + 1, 3, "blocked", blocked_align_format);
                } else {
                    worksheet_write_number(worksheet, i + 1, 3, final_rates[i].rate[1], align_format);
                }
            }
        }

        if (!final_rates[i].rate_missing[0] && !final_rates[i].rate_missing[1] && final_rates[i].rate[0] != -1 && final_rates[i].rate[1] != -1) {
            if ((final_rates[i].rate[0] - final_rates[i].rate[1]) < 0) {
                worksheet_write_number(worksheet, i + 1, 4, final_rates[i].rate[0] - final_rates[i].rate[1], red_format);
                if (final_rates[i].rate[0] == 0) {
                    worksheet_write_number(worksheet, i + 1, 5, 0, red_format_percent);
                } else {
                    double percent = 100 - (final_rates[i].rate[1]/final_rates[i].rate[0])*100;
                    if (percent < -50) {
                        worksheet_write_number(worksheet, i + 1, 5, percent, bright_red_format_percent);
                    } else {
                        worksheet_write_number(worksheet, i + 1, 5, percent, red_format_percent);
                    }
                }
            } else {
                worksheet_write_number(worksheet, i + 1, 4, final_rates[i].rate[0] - final_rates[i].rate[1], green_format);
                worksheet_write_string(worksheet_green, i_green + 1, 0, final_rates[i].name, align_format);
                worksheet_write_string(worksheet_green, i_green + 1, 1, final_rates[i].prefix, align_format);

                if (!final_rates[i].rate_missing[0]) {
                    if (final_rates[i].rate_found[0]) {
                        if (final_rates[i].rate[0] == -1) {
                            worksheet_write_string(worksheet_green, i_green + 1, 2, "blocked", blocked_align_format);
                        } else {
                            worksheet_write_number(worksheet_green, i_green + 1, 2, final_rates[i].rate[0], gray_align_format);
                        }
                    } else {
                        if (final_rates[i].rate[0] == -1) {
                            worksheet_write_string(worksheet_green, i_green + 1, 2, "blocked", blocked_align_format);
                        } else {
                            worksheet_write_number(worksheet_green, i_green + 1, 2, final_rates[i].rate[0], align_format);
                        }
                    }
                }

                if (!final_rates[i].rate_missing[1]) {
                    if (final_rates[i].rate_found[1]) {
                        if (final_rates[i].rate[1] == -1) {
                            worksheet_write_string(worksheet_green, i_green + 1, 3, "blocked", blocked_align_format);
                        } else {
                            worksheet_write_number(worksheet_green, i_green + 1, 3, final_rates[i].rate[1], gray_align_format);
                        }
                    } else {
                        if (final_rates[i].rate[1] == -1) {
                            worksheet_write_string(worksheet_green, i_green + 1, 3, "blocked", blocked_align_format);
                        } else {
                            worksheet_write_number(worksheet_green, i_green + 1, 3, final_rates[i].rate[1], align_format);
                        }
                    }
                }

                worksheet_write_number(worksheet_green, i_green + 1, 4, final_rates[i].rate[0] - final_rates[i].rate[1], green_format);
                if (final_rates[i].rate[0] == 0) {
                    worksheet_write_number(worksheet, i + 1, 5, 0, green_format_percent);
                    worksheet_write_number(worksheet_green, i_green + 1, 5, 0, green_format_percent);
                } else {
                    double percent = 100 - (final_rates[i].rate[1]/final_rates[i].rate[0])*100;
                    if (percent > 50) {
                        worksheet_write_number(worksheet, i + 1, 5, percent, bright_green_format_percent);
                        worksheet_write_number(worksheet_green, i_green + 1, 5, percent, bright_green_format_percent);
                    } else {
                        worksheet_write_number(worksheet, i + 1, 5, percent, green_format_percent);
                        worksheet_write_number(worksheet_green, i_green + 1, 5, percent, green_format_percent);
                    }
                }
                i_green++;
            }
        }

        if (debug) {
            m2_log("Prefix: %s\t\t rates: %.3f %.3f\n", final_rates[i].prefix, final_rates[i].rate[0], final_rates[i].rate[1]);
        }

    }

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

    workbook_close(workbook);

    if (frates_count && strlen(target_email)) {
        if (get_email_settings(0) == 0) {
            send_xlsx_email();
        }
    }

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

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

    m2_log("Task completed\n");

    return 0;

}


/*

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

*/


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


void get_destinations() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[4096] = "";

    m2_log("Calculating total unique destinations between tariffs\n");

    sprintf(query, "SELECT rates.prefix, destinations.name FROM rates LEFT JOIN destinations ON destinations.id = rates.destination_id WHERE tariff_id IN (%d, %d) "
        "GROUP BY rates.prefix ORDER BY rates.prefix", tariff_a_id, tariff_b_id);

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

    result = mysql_store_result(&mysql);

    if (result) {
        while (( row = mysql_fetch_row(result) )) {
            final_rates = realloc(final_rates, (frates_count + 1) * sizeof(rates_t));
            memset(&final_rates[frates_count], 0, sizeof(rates_t));
            if (row[0]) strcpy(final_rates[frates_count].prefix, row[0]);
            if (row[1]) strcpy(final_rates[frates_count].name, row[1]);
            frates_count++;
        }
    }

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

    mysql_free_result(result);

}


/*
    Find gaps in tariffs
*/


void get_detinations_from_tariffs(int tariff_index) {

    MYSQL_RES *result;
    MYSQL_ROW row;

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

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

    sprintf(query, "SELECT prefix, rate_after_exchange FROM (SELECT prefix, rate / exchange_rate AS rate_after_exchange "
        "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.effective_from <= NOW() OR rates.effective_from IS NULL) ORDER BY rates.effective_from DESC) A "
        "GROUP BY A.prefix ORDER BY A.prefix", tariff_index == 0 ? tariff_a_id : tariff_b_id);

    if (m2_mysql_query(query)) {
        exit(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));
                strcpy(tmp_rates[trates_count].prefix, row[0]);
                tmp_rates[trates_count].rate[0] = (atof(row[1]) * exchange_rate);
                trates_count++;
            }
        }
    }

    if (trates_count == 0) {
        if (debug) m2_log("\n");
        if (debug) m2_log("---------------------------------------------\n");
        m2_log("No rates in tariff: %d\n", tariff_index == 0 ? tariff_a_id : tariff_b_id);
        if (debug) m2_log("---------------------------------------------\n");
        if (debug) m2_log("\n");
        return;
    } else {
        if (debug) {
            m2_log("\n");
            m2_log("---------------------------------------------\n");
            m2_log("Rate table for tariff: %d\n", tariff_index == 0 ? tariff_a_id : tariff_b_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 (debug) m2_log("Prefix: %s\t\t%0.3f\n", tmp_rates[j].prefix, tmp_rates[j].rate[0]);
            final_rates[i].rate[tariff_index] = tmp_rates[j].rate[0];
            j++;
        } else {
            if (debug) m2_log("Prefix: %s\t\t-\n", final_rates[i].prefix);
            final_rates[i].rate_missing[tariff_index] = 1;
        }
    }

    mysql_free_result(result);

}


/*
    Get closest shorter prefix
*/


void fill_destinations_gaps(int tariff_index) {

    int i = 0;

    if (frates_count > 0) {
        for (i = 1; i < frates_count; i++) {
            if (final_rates[i].rate_missing[tariff_index]) {
                int index = i - 1;
                while (final_rates[i].prefix[0] == final_rates[index].prefix[0]) {
                    if (strncmp(final_rates[i].prefix, final_rates[index].prefix, strlen(final_rates[index].prefix)) == 0) {
                        if (final_rates[index].rate_missing[tariff_index] == 0) {
                            final_rates[i].rate[tariff_index] = final_rates[index].rate[tariff_index];
                            final_rates[i].rate_missing[tariff_index] = 0;
                            final_rates[i].rate_found[tariff_index] = 1;
                            break;
                        }
                    }
                    index--;
                    if (index < 0) break;
                }
            }
        }
    }

}


/*
    Get tariff names
*/


void get_tariff_names(int tariff_index) {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[4096] = "";

    sprintf(query, "SELECT (SELECT name FROM tariffs WHERE id = %d), (SELECT name FROM tariffs WHERE id = %d)", tariff_a_id, tariff_b_id);

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

    result = mysql_store_result(&mysql);

    if (result) {
        while (( row = mysql_fetch_row(result) )) {
            if (row[0] && row[1]) {
                strcpy(tariff_a_name, row[0]);
                strcpy(tariff_b_name, row[1]);
            }
        }
    }

    mysql_free_result(result);

}


/*
    sort function for qsort
*/


int name_compare(const void *elem1, const void *elem2) {
    rates_t *a = (rates_t *)elem1;
    rates_t *b = (rates_t *)elem2;
    return strcmp(a->name, b->name);
}


/*
    Get email settings
*/


int get_email_settings(int owner_id) {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char sqlcmd[2048] = "";

    // set default values
    strcpy(email_server, "");
    strcpy(email_login, "");
    strcpy(email_password, "");
    strcpy(email_from, "");
    email_port = 0;
    int res = 1;

    sprintf(sqlcmd, "SELECT (SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_Smtp_Server'), "
        "(SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_Login'), "
        "(SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_Password'), "
        "(SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_from'), "
        "(SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_port')", owner_id, owner_id, owner_id, owner_id, owner_id);

    if (m2_mysql_query(sqlcmd)) {
        exit(1);
    }

    result = mysql_store_result(&mysql);

    if (result) {

        if ((row = mysql_fetch_row(result)) == NULL) {
            m2_log("MySQL returned an empty result set\n");
            m2_log("MySQL query: %s\n", sqlcmd);
            exit(1);
        }

        if (row[0] && row[1] && row[2] && row[3] && row[4]) {

            res = 0;

            strcpy(email_server, row[0]);
            strcpy(email_login, row[1]);
            strcpy(email_password, row[2]);
            strcpy(email_from, row[3]);
            email_port = atoi(row[4]);

            if (!strlen(email_server)) { m2_log("Email server is empty\n"); res = 1; }
            if (!strlen(email_from)) { m2_log("Email from is empty\n"); res = 1; }
            if (!email_port) { m2_log("Email port is empty\n"); res = 1; }

            if (res == 1) m2_log("Emails are disabled for owner with id: %d\n", owner_id);
            m2_log("Email data: owner_id: %d, server: %s, login: %s, from: %s, port: %d\n", owner_id, email_server, email_login, email_from, email_port);

        }

        mysql_free_result(result);
    }

    return res;
}


/*
    Send XLSX file to owner email
*/


int send_xlsx_email() {

    char body[10000] = "";
    char response[2048] = "";

    // format email body
    sprintf(body, "Tariffs:\n\n%s\n%s\n", tariff_a_name, tariff_b_name);

    m2_log("Email to: %s\n", target_email);

    if (m2_send_email(NULL, target_email, "M2 Tariff Compare", body, filename, 0, 0, "tariff_compare", 0, response)) {
        m2_log("Failed to send email: %s\n", response);
        email_action_log(0, target_email, 0, response);
    } else {
        m2_log("Email was sent successfully\n");
        email_action_log(0, target_email, 1, "");
    }

    return 0;

}


/*
    Create action log for email sending (failed and successful attempts)
*/


void email_action_log(int user_id, char *email, int status, char *error) {

    char sqlcmd[1024] = "";
    char email_to_db[128] = "no email";
    char error_to_db[128] = "unknown reason";

    if (strlen(email)) {
        strcpy(email_to_db, email);
    }

    if (strlen(error)) {
        strcpy(error_to_db, error);
    }

    if (status == 1) {
        sprintf(sqlcmd, "INSERT INTO actions(action, user_id, target_id, data, date, target_type) VALUES('email_send', 0, '%d', '%s', NOW(), 'user')", user_id, email_to_db);
    } else {
        sprintf(sqlcmd, "INSERT INTO actions(action, user_id, target_id, data, data2, data3, date, target_type) VALUES('error', '0', '%d', '%s', \"Can't send email\", '%s', NOW(), 'user')", user_id, error_to_db, email_to_db);
    }

    if (m2_mysql_query(sqlcmd)) {
        exit(1);
    }

}


/*
    Get currency exchange rate
*/


double get_currency_exchange_rate(char *currency) {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char sqlcmd[2048] = "";

    sprintf(sqlcmd, "SELECT exchange_rate FROM currencies WHERE name = '%s'", currency);

    if (m2_mysql_query(sqlcmd)) {
        exit(1);
    }

    result = mysql_store_result(&mysql);

    if (result) {

        if ((row = mysql_fetch_row(result)) == NULL) {
            m2_log("MySQL returned an empty result set\n");
            m2_log("MySQL query: %s\n", sqlcmd);
            exit(1);
        }

        if (row[0]) exchange_rate = atof(row[0]);

        mysql_free_result(result);
    }

    if (exchange_rate == 0) exchange_rate = 1;

    m2_log("Exchange rate for currency %s is: %f\n", currency, exchange_rate);

    return exchange_rate;

}