import pymysql

# Database configuration for fetching disabled projects
SOURCE_DB = {
    'host': '37.27.12.199',
    'user': 'axg_crm_new1',
    'password': 'jB^8KG*nyrmBejB^8',
    'database': 'axg_crm_new',
    'port': 3306  # Default MySQL port
}

# Target databases where deletion will occur
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'},
}

# List of tables where project_id should be deleted
TABLES_TO_CLEAN = [
    "axgbot_scan_websites",
    "full_website_search",
    # Add more tables here as needed
]

def get_disabled_project_ids():
    """Fetch project_ids from disabled_projects_to_remove from the given source database"""
    try:
        connection = pymysql.connect(
            host=SOURCE_DB['host'], port=SOURCE_DB['port'],
            user=SOURCE_DB['user'], password=SOURCE_DB['password'],
            database=SOURCE_DB['database']
        )
        with connection.cursor() as cursor:
            cursor.execute("SELECT project_id FROM disabled_projects_to_remove")
            project_ids = [row[0] for row in cursor.fetchall()]
        return project_ids
    except Exception as e:
        print(f"Error fetching disabled projects: {e}")
        return []
    finally:
        if connection:
            connection.close()

def delete_from_tables(project_ids):
    """Delete project_ids from specified tables in all target databases"""
    if not project_ids:
        print("No disabled projects found.")
        return
    
    placeholders = ', '.join(['%s'] * len(project_ids))

    for db_name, config in DB_CONFIGS.items():
        print(f"Processing {db_name}...")

        try:
            connection = pymysql.connect(
                host=config['host'], port=config['port'],
                user=config['user'], password=config['password'],
                database=config['database']
            )
            with connection.cursor() as cursor:
                for table in TABLES_TO_CLEAN:
                    query = f"DELETE FROM {table} WHERE project_id IN ({placeholders})"
                    cursor.execute(query, project_ids)
            connection.commit()
            print(f"Deleted project IDs from {db_name}")
        except Exception as e:
            print(f"Error processing {db_name}: {e}")
        finally:
            if connection:
                connection.close()

if __name__ == "__main__":
    project_ids = get_disabled_project_ids()
    delete_from_tables(project_ids)