import pymysql
import threading
from concurrent.futures import ThreadPoolExecutor

# Database credentials
DB_CONFIGS = {
    'web1': {'host': 'pma1.remov.ee', 'port': 32007, 'database': 'axg_crm_new', 'user': 'axg_crm_new1', 'password': 'jHa17aBpJD'},
    'web2': {'host': 'pma2.remov.ee', 'port': 32011, 'database': 'axg_crm_new', 'user': 'axg_crm_new1', 'password': 'jHa17aBpJD'},
    'web3': {'host': 'pma3.remov.ee', 'port': 32015, 'database': 'axg_crm_new', 'user': 'axg_crm_new1', 'password': 'jHa17aBpJD'},
    'web4': {'host': 'pma4.remov.ee', 'port': 32019, 'database': 'axg_crm_new', 'user': 'axg_crm_new1', 'password': 'jHa17aBpJD'},
    'web5': {'host': 'pma5.remov.ee', 'port': 32023, 'database': 'axg_crm_new', 'user': 'axg_crm_new1', 'password': 'jHa17aBpJD'},
}

CHUNK_SIZE = 1000  # Adjust chunk size for bulk inserts

# Thread-safe sets to store unique and existing tasks
all_tasks = set()
existing_tasks = set()
lock = threading.Lock()


def fetch_tasks(db_name, config):
    """Fetch tasks with active=0 and active=1 from a given database."""
    global all_tasks, existing_tasks

    try:
        conn = pymysql.connect(host=config['host'], port=config['port'], user=config['user'], password=config['password'], database=config['database'])
        cursor = conn.cursor()

        # Fetch active=0 tasks
        cursor.execute("SELECT project_id, website_id FROM axgbot_scan_websites WHERE active = 0")
        new_tasks = set(cursor.fetchall())

        # Fetch active=1 tasks
        cursor.execute("SELECT project_id, website_id FROM axgbot_scan_websites WHERE active = 1")
        active_tasks = set(cursor.fetchall())

        cursor.close()
        conn.close()

        with lock:
            all_tasks.update(new_tasks)
            existing_tasks.update(active_tasks)

    except Exception as e:
        print(f"[ERROR] Database {db_name}: {e}")


def delete_old_tasks(db_name, config):
    """Delete tasks with active=0 before inserting new tasks."""
    try:
        conn = pymysql.connect(host=config['host'], port=config['port'], user=config['user'], password=config['password'], database=config['database'])
        cursor = conn.cursor()
        cursor.execute("DELETE FROM axgbot_scan_websites WHERE active = 0")
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        print(f"[ERROR] Database {db_name}: {e}")


def insert_tasks(db_name, config, tasks):
    """Insert tasks into the database in chunks."""
    try:
        conn = pymysql.connect(host=config['host'], port=config['port'], user=config['user'], password=config['password'], database=config['database'])
        cursor = conn.cursor()

        task_list = [(task[0], task[1], 1, 0) for task in tasks]  # Assuming user_id=1 for now

        for i in range(0, len(task_list), CHUNK_SIZE):
            chunk = task_list[i:i + CHUNK_SIZE]
            cursor.executemany("INSERT IGNORE INTO axgbot_scan_websites (project_id, website_id, user_id, active) VALUES (%s, %s, %s, %s)", chunk)
            conn.commit()

        cursor.close()
        conn.close()
    except Exception as e:
        print(f"[ERROR] Database {db_name}: {e}")


def distribute_tasks():
    """Main function to distribute tasks across databases using threads."""
    with ThreadPoolExecutor(max_workers=5) as executor:
        # Fetch tasks in parallel
        executor.map(fetch_tasks, DB_CONFIGS.keys(), DB_CONFIGS.values())

    # Remove duplicates and filter out existing active tasks
    unique_tasks = all_tasks - existing_tasks
    unique_tasks = list(unique_tasks)

    # Distribute tasks evenly among databases
    total_servers = len(DB_CONFIGS)
    server_tasks = [[] for _ in range(total_servers)]

    for i, task in enumerate(unique_tasks):
        server_tasks[i % total_servers].append(task)

    with ThreadPoolExecutor(max_workers=5) as executor:
        # Delete old tasks
        executor.map(delete_old_tasks, DB_CONFIGS.keys(), DB_CONFIGS.values())

        # Insert new tasks
        for db_name, tasks in zip(DB_CONFIGS.keys(), server_tasks):
            executor.submit(insert_tasks, db_name, DB_CONFIGS[db_name], tasks)


if __name__ == "__main__":
    distribute_tasks()