from calendar import Calendar, calendar
import csv
import base64
from itertools import chain, count
from random import Random, randint, random
from select import select
import time
import requests
import os
import pymysql
import random
import calendar
import datetime
import runpy
import time

data_insert_limit = 100
counts = 0
todayDate = datetime.date.today()


def next_weekday(d, weekday):
    days_ahead = weekday - d.weekday()
    if days_ahead < 0:  # Target day already happened this week
        days_ahead += 7
    return d + datetime.timedelta(days_ahead)


next_monday = next_weekday(todayDate, 0)  # 0 = Monday, 1=Tuesday, 2=Wednesday...


def checkcountrydata():
    try:
        db = pymysql.connect(
            host="104.248.13.122",
            user="root",
            passwd="upsquaredev",
            database="1134-vpn-blue-staging",
        )
        # db = pymysql.connect(host = "104.248.13.122",user = "ovpnuser",passwd = "y7OZho5X4jejchHM",database = "ovpn-temp")
        cursor = db.cursor()
        sql = "select country_code from countries"
        cursor.execute(sql)
        number0flines = cursor.execute(sql)
        results = cursor.fetchall()
        print("total line ", number0flines)
        webcountrycode = []
        commanlist = []
        sqlcountrycode = []
        CSV_URL = "http://www.vpngate.net/api/iphone/"
        with requests.Session() as s:
            download = s.get(CSV_URL)
            decoded_content = download.content.decode("utf-8")
            cr = csv.reader(decoded_content.splitlines(), delimiter=",")
            my_list = list(cr)

        if number0flines == 0:
            for contrydata in my_list[2:-1]:
                countrycode = contrydata[6]
                webcountrycode.append(countrycode)

            commanlist = list((webcountrycode))

            for checklist in commanlist:
                print(checklist)

                for i in range(2, len(my_list)):
                    if my_list[i][6] == checklist:
                        arraydata = my_list[i]
                        print(arraydata)

                        print("yes")
                        cursor = db.cursor()
                        query = "INSERT INTO countries (country , country_code ,     created_at ,    updated_at , premium , is_active) VALUES (%s , %s , %s ,%s , %s , %s)"
                        dt = time.gmtime()
                        ts = calendar.timegm(dt)
                        alldata = (arraydata[5], arraydata[6], ts, ts, 1, 0)
                        a = cursor.execute(query, alldata)
                        results = cursor.fetchall()
                        db.commit()
                        break
        else:
            print("noo")
            for codesof in results:
                codes = codesof[0]
                sqlcountrycode.append(codes)
            print(sqlcountrycode)
            for contrydata in my_list[2:-1]:
                countrycode = contrydata[6]
                webcountrycode.append(countrycode)

            commanlist = list(set(webcountrycode))
            print(commanlist)

            neewcode = list(set(commanlist) - set(sqlcountrycode))
            print("new ountry = ", neewcode)
            # exit()
            for checklist in neewcode:
                print(checklist)
                for i in range(2, len(my_list)):
                    if my_list[i][6] == checklist:
                        arraydata = my_list[i]
                        print(arraydata)

                        print("yes")
                        cursor = db.cursor()
                        query = "INSERT INTO countries (country , country_code ,     created_at ,    updated_at ,  is_active) VALUES (%s , %s , %s ,%s , %s)"
                        dt = time.gmtime()
                        ts = calendar.timegm(dt)
                        alldata = (arraydata[5], arraydata[6], ts, ts, 0)
                        a = cursor.execute(query, alldata)
                        results = cursor.fetchall()
                        db.commit()
                        break
        db.close()
    except:
        runpy.run_path(path_name="temparory_server.py")


def insertwithlimit():
    # try:
    db = pymysql.connect(
        host="104.248.13.122",
        user="root",
        passwd="upsquaredev",
        database="1134-vpn-blue-staging",
    )
    # db = pymysql.connect(host = "104.248.13.122",user = "ovpnuser",passwd = "y7OZho5X4jejchHM",database = "ovpn-temp")
    cursor = db.cursor()
    sql = "select country_code from countries"

    cursor.execute(sql)

    number0flines = cursor.execute(sql)
    results = cursor.fetchall()
    print("total line ", number0flines)
    webcountrycode = []
    commanlist = []
    sqlcountrycode = []
    sqliparr = []
    webarrip = []
    mysqlarrip = []
    newmylist = []
    webcountryip = []
    newcode = []
    CSV_URL = "http://www.vpngate.net/api/iphone/"
    with requests.Session() as s:
        download = s.get(CSV_URL)
        decoded_content = download.content.decode("utf-8")
        cr = csv.reader(decoded_content.splitlines(), delimiter=",")
        my_list = list(cr)
        my_list.remove(my_list[0])
        my_list.remove(my_list[1])
        my_list.remove(my_list[-1])

    cursor = db.cursor()
    sql = "SELECT country_id, (SELECT country_code from countries as c WHERE c.country_id = v.country_id) FROM temp_servers3 v GROUP by country_id"

    cursor.execute(sql)

    number0flines = cursor.execute(sql)
    checkdata = cursor.fetchall()
    print(checkdata)
    # exit()
    for arrRowsData in my_list:
        ipcheck = arrRowsData[1]
        # print(ipcheck)
        webcountryip.append(ipcheck)
        # webcountrycode.append(countrycode)
    for arrRowsData in my_list:
        ipcheck = arrRowsData[6]
        # print(ipcheck)
        webcountrycode.append(ipcheck)
    # for codesof in results:
    #         codes = codesof[0]
    #         sqlcountrycode.append(codes)
    # print("sql country code list" ,list(set(sqlcountrycode)))
    print("web country =", webcountrycode)
    commanlist = list(set(webcountrycode))
    print("comman list = ", commanlist)
    cursor = db.cursor()
    sql = "select * from temp_servers3"

    cursor.execute(sql)
    result = cursor.fetchall()
    # print('result', result)
    for arrRowsData in my_list:
        ipcheck = arrRowsData[1]
        # print(ipcheck)
        # print(ipcheck)
        webarrip.append(ipcheck)
    for iplist in results:
        # print(iplist[0])
        hostname = iplist[0]

        mysqlarrip.append(hostname)
    print("mysqlip = ", mysqlarrip)
    print("web arr ip = ", webarrip)
    # exit()
    newiplist = list(set(webarrip) - set(mysqlarrip))

    print("new ip list = ", newiplist)

    print(" total new ip list  = ", len(newiplist))
    for codelist in checkdata:
        newcode.append(codelist[1])
    updatedlist = list(set(commanlist) - set(newcode))
    print("comman list = ", commanlist)
    print("newcode list = ", set(newcode))
    print("updated list = ", updatedlist)
    # exit()

    # counts = 0
    for temp in newcode:
        print(temp)
        counts = 0
        for abc in my_list[2:-1]:
            if abc[6] == temp:
                if counts == data_insert_limit:
                    print("limit")
                    counts = 0
                    break
                else:
                    # print(abc)
                    counts = counts + 1
                    print("count = ", counts)
                    newmylist.append(abc)

    print("newmylist  -------->", newmylist)
    print("total newmylist  -------->", len(my_list))
    cursor = db.cursor()
    sql = "select * from temp_servers3"
    cursor.execute(sql)
    number0flines = cursor.execute(sql)
    ipfetch = cursor.fetchall()

    for iplist in ipfetch:
        hostname = iplist[2]
        sqliparr.append(hostname)

    for i in sqliparr:
        for abc in newmylist:
            if i == abc[1]:
                newmylist.remove(abc)

    # print(ipfetch)
    # exit()

    for iplist in ipfetch:
        ccc = time.strftime("%H:%M:%S")
        current_time = datetime.datetime.strptime(
            ccc, "%H:%M:%S"
        ).time()  # time.strftime("%H:%M:%S")
        startTime = datetime.datetime.strptime("00:00:00", "%H:%M:%S").time()
        endTime = datetime.datetime.strptime("00:35:00", "%H:%M:%S").time()
        hostname = iplist[2]
        response = os.system("ping -c 1 " + hostname)
        print("response: ", response)
        cursor = db.cursor()
        query = "select ping_count from temp_servers3 WHERE ip_address = %s "
        cursor.execute(query, hostname)
        pingCount = cursor.fetchall()
        print("pingcount", pingCount)

        if todayDate == next_monday:
            if current_time > startTime and current_time < endTime:
                sql = "UPDATE temp_servers3 SET ping_count = %s"
                ip = 0
                a = cursor.execute(sql, ip)
                results = cursor.fetchall()
            else:
                if response == 0:
                    # print(pingCount[0][0]+1)
                    sql = "UPDATE temp_servers3 SET is_active  = %s , ping_count = %s  WHERE ip_address = %s "
                    ip = (1, pingCount[0][0] + 1, hostname)
                    a = cursor.execute(sql, ip)
                    results = cursor.fetchall()
                    print(a)
                    db.commit()
                else:
                    # print(pingCount[0][0]-1)
                    print(hostname, "is down")
                    # sqliparr.append(hostname)
                    sql = "UPDATE temp_servers3 SET is_active = %s , ping_count = %s  WHERE ip_address = %s "
                    ip = (0, pingCount[0][0] - 1, hostname)
                    a = cursor.execute(sql, ip)
                    results = cursor.fetchall()
        else:
            if response == 0:
                # print(pingCount[0][0]+1)
                sql = "UPDATE temp_servers3 SET is_active  = %s , ping_count = %s  WHERE ip_address = %s "
                ip = (1, pingCount[0][0] + 1, hostname)
                a = cursor.execute(sql, ip)
                results = cursor.fetchall()
                print(a)
                sqliparr.append(hostname)
                db.commit()

            else:
                # print(pingCount[0][0]-1)
                print(hostname, "is down")
                sqliparr.append(hostname)
                sql = "UPDATE temp_servers3 SET is_active = %s , ping_count = %s  WHERE ip_address = %s "
                ip = (0, pingCount[0][0] - 1, hostname)
                a = cursor.execute(sql, ip)
                results = cursor.fetchall()

    cursor = db.cursor()
    query = "SELECT country_id,(SELECT country_code from countries c WHERE c.country_id = vs.country_id) as country_code, COUNT(country_id) countrycount FROM temp_servers3 as vs GROUP by country_id"
    cursor.execute(query)
    checktotalcount = cursor.fetchall()
    # print("total data = " , checktotalcount)
    print(checktotalcount)
    # exit()
    for i in range(0, len(checktotalcount)):
        numberinsert = checktotalcount[i][2]
        contrynumber = checktotalcount[i][1]

        if numberinsert == 50:
            for abc in newmylist:
                if abc[6] == contrynumber:
                    # print(abc[6])
                    # exit()
                    # newmylist.remove(abc)
                    print("remove success")

        else:
            newcount = 50 - numberinsert
            # print("new count = ",newcount)
            # for i in range(numberinsert+1,11):
            # print("i = ",i)

            for i in range(numberinsert + 1, 51):
                # print("outside =" , i)
                for abc in newmylist:
                    # print("newcount =" , newcount)
                    if abc[6] == contrynumber:
                        if newcount >= 51:
                            newcount = 0
                            break
                        else:
                            arrRowsData = abc
                            response = os.system("ping -c 1 " + abc[1])
                            if response == 0:
                                a = base64.b64decode(arrRowsData[-1])
                                cursor = db.cursor()
                                query = "select country_id from countries WHERE country_code = %s"
                                alldata = arrRowsData[6]
                                # print("alldata = ", alldata)
                                b = cursor.execute(query, alldata)
                                results = cursor.fetchall()
                                # print("result = ",results)
                                countrycode = results[0]
                                code = countrycode[0]
                                # print("countrycpde = ",code)
                                db.commit()
                                with open("aaa.text", "wb") as fh:
                                    fh.write(a)

                                f = open("bbb.text", "a+")
                                with open("aaa.text", "r") as thelib:
                                    mydata = thelib.readlines()
                                filedata = []
                                for data in mydata:
                                    if (
                                        data.startswith("#")
                                        or data.startswith(" ")
                                        or data.startswith("\n")
                                    ):
                                        continue
                                    else:
                                        # print("Main data",data)
                                        f.write(data)

                                f.close()
                                with open("bbb.text", "r") as thelib:
                                    reopendata = thelib.read()

                                    # print("my_list[2]-->",my_list[2])
                                cursor = db.cursor()
                                # dt = time.gmtime()
                                dt = time.gmtime()
                                ts = calendar.timegm(dt)

                                query = "INSERT INTO temp_servers3 (country_id, ip_address ,created_at , updated_at , ovpn ) VALUES (%s , %s ,%s ,%s,%s)"
                                alldata = (code, arrRowsData[1], ts, ts, reopendata)
                                # print(alldata)
                                cursor.execute(query, alldata)
                                results = cursor.fetchall()
                                # print(a)
                                db.commit()
                                os.remove("bbb.text")
                                newmylist.remove(arrRowsData)
                                newcount = newcount + 1

                            else:
                                newmylist.remove(arrRowsData)

    # print("after remove =",len(newmylist))
    # counts = 0
    # # print(newmylist
    # )
    # print(updatedlist)
    # print("updaated " ,len(updatedlist))

    for temp in updatedlist:
        counts = 0
        for abc in my_list[2:-1]:
            if abc[6] == temp:
                if counts == data_insert_limit:
                    print("limit")
                    counts = 0
                    break
                else:
                    # print(abc)
                    response = os.system("ping -c 1 " + abc[1])
                    if response == 0:
                        counts = counts + 1
                        # print("count = ",counts)
                        a = base64.b64decode(abc[-1])
                        cursor = db.cursor()
                        query = (
                            "select country_id from countries WHERE country_code = %s"
                        )
                        alldata = abc[6]
                        # print("alldata = ", alldata)
                        b = cursor.execute(query, alldata)
                        results = cursor.fetchall()
                        # print("result = ",results)
                        countrycode = results[0]
                        code = countrycode[0]
                        print("countrycpde = ", code)
                        db.commit()

                        with open("aaa.text", "wb") as fh:
                            fh.write(a)

                        f = open("bbb.text", "a+")
                        with open("aaa.text", "r") as thelib:
                            mydata = thelib.readlines()
                        filedata = []
                        for data in mydata:
                            if (
                                data.startswith("#")
                                or data.startswith(" ")
                                or data.startswith("\n")
                            ):
                                continue
                            else:
                                # print("Main data",data)
                                f.write(data)

                        f.close()
                        with open("bbb.text", "r") as thelib:
                            reopendata = thelib.read()

                            # print("my_list[2]-->",my_list[2])
                        cursor = db.cursor()
                        # dt = time.gmtime()
                        dt = time.gmtime()
                        ts = calendar.timegm(dt)

                        query = "INSERT INTO temp_servers3 (country_id, ip_address ,created_at , updated_at , ovpn ) VALUES (%s , %s ,%s ,%s,%s)"
                        alldata = (code, abc[1], ts, ts, reopendata)
                        # print(alldata)
                        cursor.execute(query, alldata)
                        results = cursor.fetchall()

                        db.commit()
                        os.remove("bbb.text")
                        my_list.remove(abc)
                        print(my_list)
                        exit()
                    else:
                        my_list.remove(abc)
    db.close()


# except Exception as e:
#     print(e)
# runpy.run_path(path_name="temparory_server.py")


for i in range(0, 100):
    checkcountrydata()
    insertwithlimit()
# time.sleep(600)