import concurrent.futures
import queue
import mysql.connector
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException, StaleElementReferenceException, WebDriverException
import time
import logging
import signal
import threading
from retrying import retry

# Set up logging
logging.basicConfig(level=logging.INFO, format='%(levelname)s - %(message)s')

# Database connection settings
db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '1234',
    'database': 'car_data',
    'ssl_ca': 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\ca.pem',
    'ssl_cert': 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\client-cert.pem',
    'ssl_key': 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\client-key.pem'
}
chrome_options = webdriver.ChromeOptions()
chrome_options.add_argument("--disable-infobars")
chrome_options.add_argument("--disable-notifications")
chrome_options.add_argument("--disable-extensions")
#chrome_options.add_argument("--start-maximized")
chrome_options.add_argument("--disable-popup-blocking")
chrome_options.add_argument("--incognito")
chrome_options.add_argument("--headless")  # Optional: Run in headless mode

chrome_options.add_argument("--no-first-run")
chrome_options.add_argument("--no-default-browser-check")
chrome_options.add_argument("--disable-search-engine-choice-screen")


# Initialize WebDriver
service = Service()  # Optionally specify the path to your ChromeDriver here

visited_urls = set()

db_conn = None

brand_queue = queue.Queue()


def create_connection():
    """Create a persistent MySQL connection."""
    global db_conn
    if db_conn is None:
        db_conn = mysql.connector.connect(
            host=db_config['host'],
            port=db_config['port'],
            user=db_config['user'],
            password=db_config['password'],
            database=db_config['database'],
            ssl_ca=db_config['ssl_ca'],
            ssl_cert=db_config['ssl_cert'],
            ssl_key=db_config['ssl_key']
        )
    return db_conn

def close_connection():
    """Close the persistent MySQL connection."""
    global db_conn
    if db_conn:
        db_conn.close()
        db_conn = None

# Load ignore lists
def load_ignore_list(filename):
    with open(filename, 'r') as file:
        return [line.strip() for line in file if line.strip()]

def append_to_ignore_list(filename,url):
    
    with open(filename,'a') as file:
        file.write(url+"\n")

def get_ignore_lists():
    global ignoreBrandsFile, ignoreModelsFile, ignoreGenerationsFile, ignoreCarsFile, ignoreBaureiheFile, brandsFile, buggyGenerationsFile
    ignoreBrandsFile = "ignoreBrands.txt"
    ignoreModelsFile = "ignoreModels.txt"
    ignoreGenerationsFile = "ignoreGenerations.txt"
    ignoreCarsFile = "ignoreCars.txt"
    ignoreBaureiheFile = "ignoreBaureihe.txt"
    brandsFile = "VW.txt"
    buggyGenerationsFile = "buggyGenerations.txt"
    global brands_to_be_ignored, models_to_be_ignored, generations_to_be_ignored, cars_to_be_ignored, baureihen_to_be_ignored, brands
    brands_to_be_ignored = set(load_ignore_list(ignoreBrandsFile))
    models_to_be_ignored = set(load_ignore_list(ignoreModelsFile))
    generations_to_be_ignored = set(load_ignore_list(ignoreGenerationsFile))
    cars_to_be_ignored = set(load_ignore_list(ignoreCarsFile))
    baureihen_to_be_ignored = set(load_ignore_list(ignoreBaureiheFile))
    brands = load_ignore_list(brandsFile)

# Web scraping functions
def extract_text(element):
    return element.text if element else ""

def extract_text_with_retry(element, retries=2, delay=0.5):
    for attempt in range(retries):
        try:
            return element.text if element else ""
        except StaleElementReferenceException:
            time.sleep(delay)  # Wait before retrying
    return ""  # Return an empty string if it fails after all retries

def getX_cached(kat, katNames, hersNames):
    if kat in katNames:
        return hersNames[katNames.index(kat)]
    return None

def process_model(driver, brand, modelUrl, modelName):
    if modelUrl in models_to_be_ignored:
        logging.info(f"Skipping ignored model: {modelName}")
        return []
    
    # if modelUrl in visited_urls:
    #     logging.info(f"Skipping already visited model URL: {modelUrl}")
    #     return []     ---- THIS IS CAUSING MODEL TO BE APPENDED TO IGNORE LIST
    
    visited_urls.add(modelUrl)

    driver.get(modelUrl)
    try:
        generations = WebDriverWait(driver, 10).until(
            EC.presence_of_all_elements_located(
                (By.CSS_SELECTOR, 'div.sc-a4fa3bc-0.jSIPZE.sc-2395b325-0.sc-4c2c58ad-0.kliwJ > div > a'))
        )
        logging.info(f"Found {len(generations)} generations for {brand} {modelName}")
    except TimeoutException:
        logging.error(f"No generations found for {modelName}")
        return []

    return [(gen.get_attribute('href'), gen.text.split("\n")[0]) for gen in generations]

def process_generation(driver, brand, modelName, genUrl, genName):
    if genUrl in generations_to_be_ignored:
        logging.info(f"Skipping ignored generation: {genName}")
        return zip([],[])
    
    if genUrl in buggyGenerationsFile:
        logging.info(f"Skipping buggy generation: {genName}")
        return zip([],[])

    driver.get(genUrl)
    try: 
        expand_buttons = WebDriverWait(driver,10).until(
            EC.presence_of_all_elements_located((By.CSS_SELECTOR, '[data-testid="carpages:generation:model-expander"] > button.hO47y1oigRnevquT5Md6'))
        )
        print(str(len(expand_buttons)) + " expand buttons")

        baureihe_elems = WebDriverWait(driver,10).until(
            EC.presence_of_all_elements_located((By.CSS_SELECTOR, '[data-testid="carpages:generation:model-expander"]'))
        )
        print(f"Found {str(len(baureihe_elems))} Baureihen")

        baureihen = [baureihe_elem.get_attribute("data-tracking").split('"label":"')[1].removesuffix('"}}') for baureihe_elem in baureihe_elems]

        print(f"Baureihen {baureihen}")
        for expButton in expand_buttons:
            
            if expButton.get_attribute('aria-expanded') == "false":
                driver.execute_script("arguments[0].click();", expButton)
        
    except (TimeoutException):
        if driver.current_url != genUrl:
            append_to_ignore_list(buggyGenerationsFile, genUrl)
            return zip([],[])
        print("No expand button found")

    carBatch = []
    
    for baureihe,expButton in zip(baureihen,expand_buttons):
        # if genName + baureihe in baureihen_to_be_ignored:
        #     print(f"Skipping ignored Baureihe: {baureihe}")
        #     continue     ---- THIS DOESNT WORK: BREAKS THE ZIP FUNCTION
        try:
            cars = WebDriverWait(driver,10).until(
                EC.visibility_of_all_elements_located((By.CSS_SELECTOR, f'[aria-controls="{expButton.get_attribute("aria-controls")}"] + div > div > table > thead + tbody > tr > td > a'))
            )
            print(f"Found {str(len(cars))} cars for {baureihe}")
            carBatch.append([(car.get_attribute('href'), car.text) for car in cars])
        except TimeoutException:
            print(f"No cars found.")
            continue

    return zip(baureihen,carBatch)


def scrape_car_data(driver, cursor, carUrl, carName,conn):
    
    


    driver.get(carUrl + "#technische-daten")
    #logging.info(f"Opening car (): {carName}")

    try:
        kategorien = WebDriverWait(driver, 10).until(
            EC.presence_of_all_elements_located((By.CSS_SELECTOR, 'div.sc-a4fa3bc-0.hOEvxo.sc-2395b325-0.hyHcmC > div > div > table > tbody > tr > :first-child'))
        )
        herstellerangaben = WebDriverWait(driver, 10).until(
            EC.presence_of_all_elements_located((By.CSS_SELECTOR, 'div.sc-a4fa3bc-0.hOEvxo.sc-2395b325-0.hyHcmC > div > div > table > tbody > tr > td + td'))
        )
    except TimeoutException:
        logging.error(f"Couldn't find Kategorien or Herrstellerangaben for {carName}")
        return

    katNames = [extract_text_with_retry(kategorie) for kategorie in kategorien]
    hersNames = [extract_text_with_retry(herstellerangabe) for herstellerangabe in herstellerangaben]
    
    if carUrl in visited_urls:
            return True
    data = {
        'Name': carName,
        'Marke': getX_cached("Marke", katNames, hersNames),
        'Baureihe': getX_cached("Baureihe", katNames, hersNames),
        'Modellstart': getX_cached("Modellstart", katNames, hersNames),
        'Karosserie': getX_cached("Karosserie", katNames, hersNames),
        'Kraftstoffart': getX_cached("Kraftstoffart", katNames, hersNames),
        'Leistung_maximal_kW': getX_cached("Leistung maximal in kW (Systemleistung)", katNames, hersNames),
        'Leistung_maximal_PS': getX_cached("Leistung maximal in PS (Systemleistung)", katNames, hersNames),
        'Modell': getX_cached("Modell", katNames, hersNames),
        'Getriebeart': getX_cached("Getriebeart", katNames, hersNames),
        'Türanzahl': getX_cached("Türanzahl", katNames, hersNames),
        'Typ': getX_cached("Typ", katNames, hersNames)
    }
    logging.info(f"Extracted data: {data}")

    try:
        if carUrl in visited_urls:
            return True
        cursor.execute(
            '''INSERT INTO cars (Name, Marke, Baureihe, Modellstart, Karosserie, Kraftstoffart,
                                Leistung_maximal_kW, Leistung_maximal_PS, Modell, Getriebeart, Türanzahl, Typ)
               VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''',
            (data['Name'], data['Marke'], data['Baureihe'], data['Modellstart'], data['Karosserie'],
             data['Kraftstoffart'], data['Leistung_maximal_kW'], data['Leistung_maximal_PS'],
             data['Modell'], data['Getriebeart'], data['Türanzahl'], data['Typ'])
        )
        conn.commit()
        append_to_ignore_list(ignoreCarsFile,carUrl)
        logging.info("Record inserted successfully.")
        visited_urls.add(carUrl)
        return True
    except mysql.connector.Error as err:
        logging.error(f"Error while inserting into the database: {err}")
        return False

stop_event = threading.Event()

def signal_handler(sig, frame):
    logging.info("KeyboardInterrupt detected, setting stop flag...")
    stop_event.set()

signal.signal(signal.SIGINT, signal_handler)

@retry(stop_max_attempt_number=1, wait_fixed=2000)
def create_driver():
    if stop_event.is_set():
        raise Exception("Stop event set, aborting driver creation.")
    try:
        driver = webdriver.Chrome(service=service, options=chrome_options)
        return driver
    except WebDriverException as e:
        logging.error(f"Failed to create WebDriver: {e}")
        raise

def scrape_data_for_brand():
    if stop_event.is_set():
        return
    while not brand_queue.empty() and not stop_event.is_set():
        try:
            brand = brand_queue.get_nowait()
        except queue.Empty:
            return
        logging.info(f"Scraping data for brand: {brand}")

        if brand in brands_to_be_ignored:
            return []


        driver = create_driver()
        conn = create_connection()
        cursor = conn.cursor()
        driver.get(f"https://www.adac.de/rund-ums-fahrzeug/autokatalog/marken-modelle/{brand}/")

        try:
            models = WebDriverWait(driver, 10).until(
                EC.presence_of_all_elements_located((By.CSS_SELECTOR, 'div.sc-a4fa3bc-0.jSIPZE.sc-2395b325-0.sc-4c2c58ad-0.kliwJ > div > a'))
            )
        except TimeoutException:
            logging.error(f"Couldn't find models for {brand}")
            return

        modelUrls = [model.get_attribute('href') for model in models]
        modelNames = [model.text.split("\n")[0] for model in models]

        #all_models_scraped = True
        for modelUrl, modelName in zip(modelUrls, modelNames):
            all_generations_scraped = True
            for genUrl, genName in process_model(driver, brand, modelUrl, modelName):
                all_baureihen_scraped = True
                for baureiheName,cars in process_generation(driver, brand, modelName, genUrl, genName):
                    if not baureiheName or not cars:
                        continue
                    all_cars_scraped = True
                    for i, (carUrl,carName) in enumerate(cars):
                            if carUrl in cars_to_be_ignored:
                                logging.info(f"Skipping ignored car: {carName}")
                                continue
                            if carUrl in visited_urls:
                                logging.info(f"Skipping already visited car: {carName}")
                                continue
                            logging.info(f"Scraping car {i+1}/{len(cars)}: {carName}")
                            successful = scrape_car_data(driver, cursor, carUrl, carName,conn=conn)
                            if not successful:
                                all_cars_scraped = False
                                
                    if all_cars_scraped & (genName + baureiheName not in baureihen_to_be_ignored):
                        append_to_ignore_list(ignoreBaureiheFile, genName + baureiheName)
                    else:
                        all_baureihen_scraped = False
                if all_baureihen_scraped & (genUrl not in generations_to_be_ignored):
                    append_to_ignore_list(ignoreGenerationsFile, genUrl)
                else:
                    all_generations_scraped = False
            if all_generations_scraped & (modelUrl not in models_to_be_ignored):
                logging.info(f"Appending {modelUrl} to ignore list")
                append_to_ignore_list(ignoreModelsFile, modelUrl)
            else:
                all_models_scraped = False
        if all_models_scraped & (brand not in brands_to_be_ignored):
            append_to_ignore_list(ignoreBrandsFile, brand)
        
        cursor.close()
        driver.quit()
        brand_queue.task_done()
    
def main():
    get_ignore_lists()
    for brand in brands:
        brand_queue.put(brand)
    executor = None
    try:
        executor = concurrent.futures.ThreadPoolExecutor()
        futures = [executor.submit(scrape_data_for_brand) for _ in range(len(brands))]

        for future in concurrent.futures.as_completed(futures):
            if stop_event.is_set():
                break
            try:
                future.result()
            except Exception as e:
                logging.error(f"Error occurred: {e}")
    except KeyboardInterrupt:
        logging.info("KeyboardInterrupt detected, shutting down executor...")
        stop_event.set()
        if executor:
            executor.shutdown(wait=False)
    finally:
        close_connection()
        if executor:
            executor.shutdown(wait=True)
        logging.info("Scraper stopped.")

if __name__ == "__main__":
    main()







This is my very slow code. please optimize