import time
from datetime import datetime
import os
import ast
import mysql.connector
os.system("cls")


def get_client_profile(username, domain):
    profile = ""
    mydb2 = mysql.connector.connect(
        host="localhost",
        user="smsEngine",
        password="guEnaR28T",
        database="smartsmsrouter"
    )
    mycursor2 = mydb2.cursor()
    query = "select subprofile from smsprofile where username ='{}' and domain ='{}'".format(
        username, domain)
    # print(query)
    mycursor2.execute(query)

    myresult = mycursor2.fetchall()
    

    for x in myresult:
        profile = x[0]

    mycursor2.close()
    mydb2.close()
    return profile

# End of get_client_profile


def get_client_balance(username, user_profile, domain):

    mydb2 = mysql.connector.connect(
        host="localhost",
        user="smsEngine",
        password="guEnaR28T",
        database="smartsmsrouter"
    )
    mycursor2 = mydb2.cursor()
    if user_profile == '1':
        query = "select balnaira from smsaccount where username ='{}'  and domain ='{}'".format(
            username, domain)

    if user_profile == '2':
        query = "select baleuro from smsaccount where username ='{}'  and domain ='{}'".format(
            username, domain)

    if user_profile == '3':
        query = "select baldollar from smsaccount where username ='{}'  and domain ='{}'".format(
            username, domain)

    # print(query)
    mycursor2.execute(query)

    myresult = mycursor2.fetchall()

    for x in myresult:
        account_balance = x[0]

    mycursor2.close()
    mydb2.close()
    return account_balance

# End of get_client_balance


def update_client_balance(username, user_profile, new_bal, domain):

    mydb2 = mysql.connector.connect(
        host="localhost",
        user="smsEngine",
        password="guEnaR28T",
        database="smartsmsrouter"
    )
    mycursor2 = mydb2.cursor()
    if user_profile == '1':
        query = "update smsaccount set balnaira = '{}' where username ='{}'  and domain ='{}'".format(
            new_bal, username, domain)

    if user_profile == '2':
        query = "update smsaccount set baleuro = '{}' where username ='{}'  and domain ='{}'".format(
            new_bal, username, domain)

    if user_profile == '3':
        query = "update smsaccount set baldollar = '{}' where username ='{}'  and domain ='{}'".format(
            new_bal, username, domain)

    
    mycursor2.execute(query)
    mydb2.commit()
    mycursor2.close()
    mydb2.close()


# End of update_client_balance

def insert_into_account_history(transid, username, phonenumber, oldbal, amtcharged, newbal, page, domain):

    mydb2 = mysql.connector.connect(
        host="localhost",
        user="smsEngine",
        password="guEnaR28T",
        database="smartsmsrouter"
    )
    mycursor2 = mydb2.cursor()
    query = "insert into accounthistory(transid,username,phonenumber,oldbal,amtcharged,newbal,page,domain) values('{}','{}','{}','{}','{}','{}','{}','{}') ".format(
         transid, username, phonenumber, oldbal, amtcharged, newbal, page, domain)
    mycursor2.execute(query)
    mydb2.commit()
    mycursor2.close()
    mydb2.close()
    

# End of update_account history


def update_messagesentsplittedvoice(sn,username, charge_amount, billcode,sentcode, domain):

    mydb2 = mysql.connector.connect(
        host="localhost",
        user="smsEngine",
        password="guEnaR28T",
        database="smartsmsrouter"
    )
    mycursor2 = mydb2.cursor()
    query = "update messagesentsplittedvoice set amtcharged ='{}',billcode ='{}',sentcode ='{}' where username ='{}' and sn ={} and domain='{}'".format(
         charge_amount, billcode, sentcode, username, sn, domain)
    mycursor2.execute(query)
    mydb2.commit()
    mycursor2.close()
    mydb2.close()


# End of update messagesentsplittedvoice


try:
    while True:

        mydb = mysql.connector.connect(
            host="localhost",
            user="smsEngine",
            password="guEnaR28T",
            database="smartsmsrouter"
        )

        mycursor = mydb.cursor()
        current_time_screen = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M")
        print("Charging Engine Voice ", current_time_screen, sep=' ', end=" ")
        query = "SELECT sn,transid,username,phonenumber,message,language,voicetype,domain FROM messagesentsplittedvoice where billcode ='0' order by sn asc limit 1"
        # print(query)
        mycursor.execute(query)

        

        myresult = mycursor.fetchall()

        for x in myresult:

            sn = x[0]
            transid = x[1]
            username = x[2]
            phonenumber = x[3]
            message = x[4]
            language = x[5]
            voicetype = x[6]
            domain = x[7]
            billcode = '0'
            sentcode = '0'
            print('Processing Records', x)

            # default charge is 0.0500
            rate = 0.0750
            if phonenumber[0:2] == '44':
                rate = 0.0674

            # amount of sms
            message_length = (len(message)) // 300
            if (len(message)) % 300 > 0:
                message_length += 1

            # amount charge

            charge_amount = rate * message_length

           

            # get his profile

            profile = get_client_profile(username, domain)

            # get client balance

            old_bal = get_client_balance(username, profile, domain)

            # charge the client

            if old_bal >= charge_amount:
                new_bal = float(old_bal) - float(charge_amount)
                
                # update client balance
                update_client_balance(username, profile, new_bal, domain)
                
                # update Account History
                insert_into_account_history(transid, username, phonenumber, old_bal, charge_amount, new_bal, message_length, domain)

                # update Account History
                billcode =1
                sentcode = 1
                update_messagesentsplittedvoice(sn,username, charge_amount, billcode,sentcode, domain)
            else:
                # user could not be charged
                billcode =4
                sentcode = 4
                charge_amount=0
                update_messagesentsplittedvoice(sn,username, charge_amount, billcode,sentcode, domain)

            

        mycursor.close()
        mydb.close()
        print("press cltr^c to shut down")
        time.sleep(20)
except Exception as error:
    print("App shutdown", error)