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

def totalsms():
    
    mydb = mysql.connector.connect(
            host="localhost",
            user="smsEngine",
            password="guEnaR28T",
            database="smartsmsrouter"
    )
    mycursor = mydb.cursor()

    query = "SELECT sn,totalsms from totalfreesms" 
            #print(query)
    mycursor.execute(query)
            
    myresult = mycursor.fetchall()

    for x in myresult:
        sn = x[0]
        totalsms = str(int(x[1])+1)
        query ="update totalfreesms set totalsms='{}' where sn ={}".format(totalsms,sn)
        mycursor.execute(query)
        mydb.commit()

    mycursor.close()
    mydb.close()



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")
        update_time= datetime.now().strftime("%H:%M")
        
        print("Validate Free SMS",current_time_screen,sep=' ', end=" ")
        if update_time =='00:01':
            print("update records",sep=' ', end=" ")
            query ="update userlogin set league='1', vas='1' where domain='247sms.com'"
            mycursor.execute(query)
            mydb.commit()
            time.sleep(60)
        #mycursor.execute("SELECT sn,bookname,messtype,datedue FROM schedule where username ='system' and domain='247sms.com' and status ='2'")
        query = "SELECT sn,transid,username,phonenumber,message,sender,domain FROM messagesentsinglefree where status ='0'" 
        #print(query)
        mycursor.execute(query)
        
        myresult = mycursor.fetchall()

        for x in myresult:
            #date_due = str(x[3].strftime("%Y-%m-%d %H:%M"))

           sn = x[0]
           transid= x[1]
           username = x[2]
           phonenumber = x[3]
           message = x[4]
           sender = x[5]
           domain = x[6]
           
           print('Processing Records', x)
           query = "select league from userlogin where username ='"+username+"' and domain ='247sms.com'" 
           #print(query)
           mycursor.execute(query)
        
           myresult = mycursor.fetchall()
           freeFlag =""
           for x in myresult:
               freeFlag = x[0]
           if freeFlag[0] =="1":
                query = "insert messagesentsingle(transid,username,phonenumber,message,sender,domain,status) values ('" + transid + "','" + username + "','" + phonenumber + "','" + message + "','" + sender + "','" + domain + "','0')"
                #print(query)
                mycursor.execute(query)
                mydb.commit()
           query ="update messagesentsinglefree set status='1' where sn ={}".format(sn)
           mycursor.execute(query)
           mydb.commit()

           query ="update userlogin set league='0' where username ='{}' and domain='247sms.com'".format(username)
           mycursor.execute(query)
           mydb.commit()
           totalsms()
         
        
        mycursor.close()
        mydb.close()
        print("press cltr^c to shut down")  
        time.sleep(20)
except Exception as error :
    print("App shutdown", error);