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
from collections import Counter
import os

start_time = time.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_firday = next_weekday(todayDate, 5)  # 0 = Monday, 1=Tuesday, 2=Wednesday...


def ovpnConnected(filepath):
    url = "http://172.16.16.82/ovpn/vpnGate/check_connection.php"

    # Assuming the API expects the 'filepath' parameter
    payload = {"filepath": filepath}

    response = requests.post(url, data=payload)

    if response.status_code == 200:
        data = response.json()
        return data["isConnected"]
    else:
        return f"Error: {response.status_code}, {response.text}"


def checkcountrydata():
    try:
        db = pymysql.connect(
            host="172.16.16.82",
            user="ovpn",
            passwd="Vr3EqDO60Vs0N",
            database="ovpn",
        )
        # 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 1", 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(set(webcountrycode))
            print(commanlist)
            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)
            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="kr_temporary_server.py")


def insertwithlimit():
    try:
        db = pymysql.connect(
            host="172.16.16.82",
            user="ovpn",
            passwd="Vr3EqDO60Vs0N",
            database="ovpn",
        )
        # 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 2", 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])

        my_list = my_list[2:-1]

        # for record in filtered_list:
        #     print(record[1])
        #     exit()
        cursor = db.cursor()
        sql = "SELECT country_id, (SELECT country_code from countries as c WHERE c.country_id = v.country_id) FROM ovpn_servers v GROUP by country_id"

        cursor.execute(sql)

        number0flines = cursor.execute(sql)
        checkdata = cursor.fetchall()

        for arrRowsData in my_list:
            ipcheck = arrRowsData[1]
            # print(ipcheck)
            webcountryip.append(ipcheck)
            # webcountrycode.append(countrycode)

        # exit()
        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 ovpn_servers"

        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 result:
            # print(result)
            # exit()
            hostname = iplist[0]

            mysqlarrip.append(hostname)
        print("mysqlip = ", mysqlarrip)
        print("web arr ip = ", webarrip)

        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])

        # print(commanlist, newcode)
        updatedlist = list(set(commanlist) - set(newcode))
        # print(updatedlist)
        # exit()
        print("comman list = ", commanlist)
        print("newcode list = ", set(newcode))
        print("updated list = ", updatedlist)
        # exit()

        # counts = 0
        # print(len(filtered_list))
        # exit()
        for temp in newcode:
            print(temp)
            counts = 0
            for abc in my_list:
                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  -------->", len(newmylist))
        print("total newmylist  -------->", len(my_list))
        # exit()
        cursor = db.cursor()
        sql = "select * from ovpn_servers"
        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(sqliparr)
        # exit()
        print("newmylist  -------->", len(newmylist))
        # exit()

        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 ovpn_servers as vs GROUP by country_id"
        cursor.execute(query)
        checktotalcount = cursor.fetchall()
        # print("total data = " , checktotalcount)
        print(checktotalcount)

        for i in range(0, len(checktotalcount)):
            contrynumber = checktotalcount[i][1]

            for abc in newmylist:
                # print("newcount =" , newcount)
                if abc[6] == contrynumber:
                    ip_address = abc[1]

                    # if ip_address in unique_ip_addresses:
                    #     print(f"Skipping duplicate IP address: {ip_address}")
                    #     continue
                    arrRowsData = abc
                    hostname = abc[0]
                    a = base64.b64decode(arrRowsData[14])

                    cursor = db.cursor()
                    query = "select country_id from `countries` WHERE country_code = %s"
                    alldata = arrRowsData[6]

                    b = cursor.execute(query, alldata)
                    results = cursor.fetchall()
                    # print("result = ",results)s
                    countrycode = results[0]
                    code = countrycode[0]
                    dt = time.gmtime()
                    ts = calendar.timegm(dt)
                    os.makedirs("decodeOvpn/", exist_ok=True)

                    file_name_aaa = (
                        "decodeOvpn/" + hostname + "_" + arrRowsData[1] + ".text"
                    )
                    with open(file_name_aaa, "wb") as fh:
                        fh.write(a)

                    os.makedirs("Ovpn/", exist_ok=True)
                    file_name_bbb = "Ovpn/" + hostname + "_" + arrRowsData[1] + ".ovpn"
                    f = open(file_name_bbb, "a+")
                    with open(file_name_aaa, "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(file_name_bbb, "r") as thelib:
                        reopendata = thelib.read()

                    print("newmylist_ip = ", ip_address)
                    query = "INSERT INTO ovpn_servers (country_id, ip_address, ovpn, created_at , updated_at) VALUES (%s, %s, %s, %s, %s)"
                    values = (
                        code,
                        ip_address,
                        reopendata,
                        ts,
                        ts,
                    )
                    # if ip_address not in unique_ip_addresses:
                    cursor.execute(query, values)
                    db.commit()
                    last_inserted_id = cursor.lastrowid
                    print(last_inserted_id)
                    # exit()
                    # Rename the file_bbb
                    new_filename = f"Ovpn/{last_inserted_id}_{ip_address}.ovpn"
                    os.rename(file_name_bbb, new_filename)

        for temp in updatedlist:
            counts = 0
            for abc in my_list[2:-1]:
                if abc[6] == temp:
                    ip_address = abc[1]
                    hostname = abc[0]

                    # if ip_address in unique_ip_addresses:
                    #     print(f"Skipping duplicate IP address: {ip_address}")
                    #     continue

                    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("updatedlist_ip = ", ip_address)
                    db.commit()
                    os.makedirs("decodeOvpn/", exist_ok=True)

                    file_name_aaa = "decodeOvpn/" + hostname + "_" + abc[1] + ".text"
                    with open(file_name_aaa, "wb") as fh:
                        fh.write(a)

                    os.makedirs("Ovpn/", exist_ok=True)

                    file_name_bbb = "Ovpn/" + hostname + "_" + abc[1] + ".ovpn"
                    f = open(file_name_bbb, "a+")
                    with open(file_name_aaa, "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(file_name_bbb, "r") as thelib:
                        reopendata = thelib.read()

                    cursor = db.cursor()
                    # dt = time.gmtime()
                    dt = time.gmtime()
                    ts = calendar.timegm(dt)

                    query = "INSERT INTO ovpn_servers (country_id, ip_address, ovpn, created_at , updated_at) VALUES (%s ,%s ,%s, %s, %s)"
                    alldata = (code, ip_address, reopendata, ts, ts)
                    cursor.execute(query, alldata)

                    db.commit()
                    last_inserted_id = cursor.lastrowid
                    print(last_inserted_id)

                    # Rename the file_bbb
                    new_filename = f"Ovpn/{last_inserted_id}_{ip_address}.ovpn"
                    os.rename(file_name_bbb, new_filename)

        db.close()
        print("last line")


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


def pingCount():
    try:
        db = pymysql.connect(
            host="172.16.16.82",
            user="ovpn",
            passwd="Vr3EqDO60Vs0N",
            database="ovpn",
        )
        cursor = db.cursor()
        sql = "select * from ovpn_servers"
        cursor.execute(sql)
        number0flines = cursor.execute(sql)
        ipfetch = cursor.fetchall()
        for iplist in ipfetch:
            # print(iplist[2])
            # exit()
            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()
            ip_address = iplist[2]
            server_id = iplist[0]

            cursor = db.cursor()
            query = "select ping_count from ovpn_servers WHERE ip_address = %s AND server_id = %s"
            qr_parm = (ip_address, server_id)
            cursor.execute(query, qr_parm)
            data = cursor.fetchall()
            pingCount = data[0][0]

            filepath = "Ovpn/" + str(server_id) + "_" + ip_address + ".ovpn"
            response = ovpnConnected(filepath)
            print("response: " + ip_address, response)
            print("pingcount", pingCount)
            # exit()
            if todayDate == next_firday:
                if current_time > startTime and current_time < endTime:
                    sql = "UPDATE ovpn_servers SET ping_count = %s"
                    ip = 0
                    a = cursor.execute(sql, ip)
                    results = cursor.fetchall()
                else:
                    if response == True:
                        # print(pingCount[0][0]+1)
                        sql = "UPDATE ovpn_servers SET is_active  = %s , ping_count = %s  WHERE ip_address = %s AND server_id = %s"
                        ip = (1, pingCount + 1, ip_address, server_id)
                        a = cursor.execute(sql, ip)
                        results = cursor.fetchall()
                        print(a)
                        db.commit()
                    else:
                        # print(pingCount[0][0]-1)
                        print(ip_address, "is down")
                        # sqliparr.append(hostname)
                        sql = "UPDATE ovpn_servers SET is_active = %s , pings_count = %s  WHERE ip_address = %s AND server_id = %s"
                        ip = (0, pingCount - 1, ip_address, server_id)
                        a = cursor.execute(sql, ip)
                        results = cursor.fetchall()
                        db.commit()

            else:
                if response == True:
                    # print(pingCount[0][0]+1)
                    sql = "UPDATE ovpn_servers SET is_active  = %s , ping_count = %s  WHERE ip_address = %s AND server_id = %s"
                    ip = (1, pingCount + 1, ip_address, server_id)
                    a = cursor.execute(sql, ip)
                    results = cursor.fetchall()
                    print(a)

                    db.commit()

                else:
                    print(pingCount)
                    print(ip_address, "is down")
                    sql = "UPDATE ovpn_servers SET is_active = %s , ping_count = %s  WHERE ip_address = %s AND server_id = %s"
                    ip = (0, pingCount - 1, ip_address, server_id)
                    a = cursor.execute(sql, ip)
                    results = cursor.fetchall()
                    db.commit()

            time.sleep(2)

        db.close()

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

end_time = time.time()

# Calculate the time taken for the script execution
elapsed_time = end_time - start_time
print("1_script_time-->", elapsed_time)

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