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 string
import time
import requests
import os
import pymysql
import random
import calendar
import json
import schedule
from pymysql.cursors import DictCursor

productionMode = True
staggingMode = False


def getUser():
    if productionMode == True or staggingMode == True:
        return "ovpn"
    else:
        return "root"


def getUserPwd():
    if productionMode == True or staggingMode == True:
        return "Vr3EqDO60Vs0N"
    else:
        return "password"


def getDbName():
    if productionMode == True or staggingMode == True:
        return "ovpn"
    else:
        return "ovpn"


def getHost():
    if productionMode == True or staggingMode == True:
        return "144.126.211.210"
    else:
        return "localhost"


def getOvpnTableName():
    if productionMode == True or staggingMode == True:
        return "ovpn_servers"
    else:
        return "ovpn_servers"


def getCountriesTableName():
    if productionMode == True or staggingMode == True:
        return "countries"
    else:
        return "countries"


def getPhpConnectionPath():
    if productionMode == True:
        return "http://172.16.16.82/openvpn-purple/vpn-purple/check_connection.php"
    else:
        return "http://localhost/ovpn_fetch_script/vpnGate/check_connection.php"


def getApiData():
    if staggingMode == True:
        return "https://web.theappauthors.com/1120-vpn-purple-backend/api/v1/get_open_servers_list_py"
    elif productionMode == True:
        return "https://xmid.vpnclean.com/api/v1/get_open_servers_list_py"
    else:
        return "http://172.16.16.88/1120-vpn-purple-backend/api/v1/get_open_servers_list_py"


def getApiUrl():
    if staggingMode == True:
        return "https://web.theappauthors.com/1120-vpn-purple-backend/api/v1/update_ovpn_servers"
    elif productionMode == True:
        return "https://xmid.vpnclean.com/api/v1/update_ovpn_servers"
    else:
        return "http://172.16.16.88/1120-vpn-purple-backend/api/v1/update_ovpn_servers"


def getFilePathPrefix():
    if productionMode == True or staggingMode == True:
        return "http://144.126.211.210/ovpn/vpnGate/Ovpn/"
    else:
        return "http://localhost/ovpn_fetch_script/vpnGate/Ovpn/"

def getProjectCode():
    if staggingMode == True:
        return "1120-staging"
    elif productionMode == True:
        return "1120"
    else:
        return "1120-local"

# Host Details
live_user = getUser()
live_user_pwd = getUserPwd()
live_database_name = getDbName()
live_host = getHost()
live_ovpn_table_name = getOvpnTableName()
live_country_table_name = getCountriesTableName()
connection_php_path = getPhpConnectionPath()
ovpn_filepath_purple = "Ovpn_purple/"
get_api_response = getApiData()
update_api_url = getApiUrl()
filepath_prefix = getFilePathPrefix()
project_code = getProjectCode()


def ovpnConnected(filepath):
    url = connection_php_path
    # 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 insertwithlimit():
    db = pymysql.connect(
        host=live_host,
        user=live_user,
        passwd=live_user_pwd,
        database=live_database_name,
    )

    api_response = get_api_response
    response = requests.get(api_response)
    if response.status_code == 200:
        # If the request was successful (status code 200)
        print("Request successful")
        print("Response content:")
        response = response.text
        data = json.loads(response)
        servers = data["data"]["server_list"]
        failed_servers = []
        os.makedirs(ovpn_filepath_purple, exist_ok=True)
        files = os.listdir(ovpn_filepath_purple)
        for file in files:
            file_path = os.path.join(ovpn_filepath_purple, file)
            if os.path.isfile(file_path):
                os.remove(file_path)
        for server in servers:
            print("Server ID : ",server["server_id"])

            # Iterate through the files and remove them

            file_name_aaa = (
                ovpn_filepath_purple
                + str(server["server_id"])
                + "_"
                + str(server["ip_address"])
                + ".ovpn"
            )
            with open(file_name_aaa, "w") as fh:
                fh.write(server["ovpn"])

            filepath = file_name_aaa
            # print(filepath)
            # exit()
            response = ovpnConnected(file_name_aaa)
            print("response: ", response)
            # print("pingcount", simpledata[0][2])
            # pingcount00 = pingcount // pingcount01 = serverid // pingcount02 = countryid
            if response == False:
                # print(pingCount[0][0]-1)
                print(server["ip_address"], "is down")
                failed_servers.append(
                    {
                        "server_id": server["server_id"],
                        "ip_address": server["ip_address"],
                        "country_id": int(server["country_id"]),
                        "country_code": server["country_code"],
                        "is_active": 0,
                        "filepath": filepath,
                    }
                )
            else:
                print(server["ip_address"], "is up")

        country_count = {}
        for server in failed_servers:
            country_code = server["country_code"]
            ip_address = server["ip_address"]
            print(ip_address)
            cursor = db.cursor(DictCursor)
            dt = time.gmtime()
            ts = calendar.timegm(dt)
            inactivate_query = "UPDATE {} SET is_active = %s, updated_at = %s WHERE ip_address = %s AND project_code = %s".format(
                live_ovpn_table_name
            )

            param = (0, ts, ip_address, str(project_code))
            cursor.execute(inactivate_query, param)
            db.commit()
            if country_code:
                # Increment the count for the country
                country_count[country_code] = country_count.get(country_code, 0) + 1

        new_servers = []
        for country, count in country_count.items():
            print(f"Country: {country}, Count: {count}")
            country_code = str(country)
            cursor = db.cursor(DictCursor)
            # sql = f"SELECT * FROM ovpn_servers WHERE country_code = '{country_code}' AND is_allocated = 0 ORDER BY ping_count DESC LIMIT {count};"
            # sql = f"SELECT ovpn.* FROM ovpn_servers as ovpn JOIN countries as country on country.country_id = ovpn.country_id WHERE country.country_code = '{country_code}' AND ovpn.is_allocated = 0 ORDER BY ping_count DESC LIMIT {count};"
            sql = f"SELECT ovpn.*, country.country as country_name FROM {live_ovpn_table_name} as ovpn JOIN {live_country_table_name} as country on country.country_id = ovpn.country_id WHERE country.country_code = '{country_code}' AND ovpn.is_allocated = 0 ORDER BY ping_count DESC LIMIT {count};"
            # print(sql)
            cursor.execute(sql)
            number0flines = cursor.execute(sql)
            results = cursor.fetchall()

            if len(results) == 0:
                cursor = db.cursor(DictCursor)
                sql = f"SELECT * FROM ovpn_servers WHERE is_allocated = 0 ORDER BY ping_count DESC LIMIT {count};"
                # sql = f"SELECT ovpn.*, country.country as country_name FROM {live_ovpn_table_name} as ovpn JOIN {live_country_table_name} as country on country.country_id = ovpn.country_id WHERE ovpn.is_allocated = 0 ORDER BY ping_count DESC LIMIT {remaining_servers}"
                # print(sql)
                cursor.execute(sql)
                number0flines = cursor.execute(sql)
                results = cursor.fetchall()

            for each_server in results:
                server_id = each_server["server_id"]
                ip_address = each_server["ip_address"]
                country_id = each_server["country_id"]
                country_code = each_server["country_code"].upper()
                filepath = filepath_prefix + str(server_id) + "_" + ip_address + ".ovpn"
                print("new_server", country_code, "->", ip_address)
                new_servers.append(
                    {
                        "server_id": 0,
                        "ip_address": ip_address,
                        "country_id": 0,
                        "country_code": country_code,
                        "is_active": 1,
                        "filepath": filepath,
                    }
                )
                dt = time.gmtime()
                ts = calendar.timegm(dt)
                cursor = db.cursor(DictCursor)
                sql = f"UPDATE ovpn_servers SET is_allocated = 1, updated_at = {ts}, project_code = '{project_code}' WHERE server_id = {server_id}"

                cursor.execute(sql)
                db.commit()

        # exit()
        db.close()
        url = update_api_url
        payload = {"old_servers": failed_servers, "new_servers": new_servers}

        json_payload = json.dumps(payload)
        # print(json_payload)
        print(json_payload)
        # exit()
        headers = {"Content-Type": "application/json"}

        response = requests.post(url, data=json_payload, headers=headers)

        if response.status_code == 200:
            print("API call successful")
            print(response.json())

            # for server in failed_servers:
            #     country_code = server["country_code"]
            #     ip_address = server["ip_address"]
            #     # print(ip_address)
            #     cursor = db.cursor(DictCursor)
            #     dt = time.gmtime()
            #     ts = calendar.timegm(dt)
            #     inactivate_query = "SELECT * FROM {} WHERE ip_address = %s AND project_code = %s".format(
            #         live_ovpn_table_name
            #     )
            #     param = (ip_address, project_code)
            #     cursor.execute(inactivate_query, param)
            #     inactive_servers = cursor.fetchall()
            #     for inactive_server in inactive_servers:
            #         ip_address = inactive_server['ip_address']
            #         inactivate_query = "UPDATE {} SET is_active = %s, updated_at = %s, is_allocated = %s, project_code = null WHERE ip_address = %s AND project_code = %s".format(
            #             live_ovpn_table_name
            #         )
            #         param = (0, ts, 0, ip_address, project_code)
            #         cursor.execute(inactivate_query, param)
            #         db.commit()
            
        else:
            print(f"API call failed with status code {response.status_code}")
            print(response.text)
    else:
        # If the request was not successful, print the status code and response content
        print(f"Request failed with status code {response.status_code}")
        print(response.text)


insertwithlimit()


def job():
    insertwithlimit()  # Call your main function or the code you want to execute


schedule.every(15).minutes.do(job)

while True:
    schedule.run_pending()
    time.sleep(1)