#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
import datetime
import mysql.connector
from mysql.connector import errorcode


def query_from_mysql(user, password, host, database, port, query_sql):
    conf_eleme_report = {
        'user': user,
        'password': password,
        'host': host,
        'database': database,
        'raise_on_warnings': True,
        'port': port,
    }
    retries_count = 0
    while retries_count <= 3:
        try:
            con = mysql.connector.connect(**conf_eleme_report)
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                print("Something is wrong with your user name or password")
            elif err.errno == errorcode.ER_BAD_DB_ERROR:
                print("Database does not exist")
            else:
                print(err)
        else:
            try:
                print('mysql connnect')
                cursor = con.cursor()
                cursor.execute(query_sql)
                datasTuple = cursor.fetchall()
            except mysql.connector.Error as err:
                print(err)
                return []
            cursor.close()
            con.close()
            return datasTuple
        retries_count += 1
        time.sleep(3)


def query_cluster_info(project_id, user, password, host, database, port, start_time, end_time):
    new_start_time = start_time.strftime('%Y-%m-%d') + " 00:00:00"
    new_end_time = end_time.strftime('%Y-%m-%d') + " 23:59:59"
    print(new_start_time, new_end_time, project_id)
    cluster_info_query = "select clusters.id, clusters.name, clusters.created_at, clusters.deleted_time, " \
                         "clusters.status, clusters.provider_id from clusters, providers where " \
                         "clusters.created_at<'%s' and (clusters.deleted_time>'%s' or clusters.deleted_time is NULL) " \
                         "and clusters.project_id='%s' and clusters.provider_id=providers.id and " \
                         "providers.name = 'aws' and clusters.deploy_type='dedicated';" % \
                         (new_end_time, new_start_time, project_id)
    print(database, cluster_info_query)
    clusters_info = query_from_mysql(user, password, host, database, port, cluster_info_query)
    print(clusters_info)
    return clusters_info


def query_org_info(project_id, user, password, host, database, port):
    org_info_query = "select tenant_id,id,status from projects where id='%s';" % project_id
    org_info = query_from_mysql(user, password, host, database, port, org_info_query)
    print(org_info)
    return org_info


def query_user_project_bills(org_id, project_id, start_time, end_time, user, password, host, database, port):
    new_project = '%' + project_id + '%'
    project_bills_query = "select sum(cost_price) from user_unit_costs where labels like '%s' " \
                          "and substr(from_unixtime(cost_time_stamp), 1, 10 ) >='%s' and " \
                          "substr(from_unixtime(cost_time_stamp), 1, 10 ) <='%s' " \
                          "and tenant_id='%s' and status = 'done'" % (new_project, start_time, end_time, org_id)
    print(project_bills_query)
    project_bills_info = query_from_mysql(user, password, host, database, port, project_bills_query)
    print(project_bills_info)
    return project_bills_info