import requests
from bs4 import BeautifulSoup
import mysql.connector
from mysql.connector import Error
from datetime import datetime

# MySQL 데이터베이스 연결 설정
from db_config import connection, cursor  # db_config.py에서 conn과 cursor를 가져옴

# Define the URL for the GPU list
url = "https://www.videocardbenchmark.net/gpu_list.php"

# Fetch the webpage content
response = requests.get(url)
soup = BeautifulSoup(response.content, 'html.parser')

# Find the table containing GPU data
gpu_table = soup.find('table', {'id': 'cputable'})

# Extract the GPU data from the table
gpu_data = []

if gpu_table:
    rows = gpu_table.find_all('tr')
    for row in rows:
        cells = row.find_all('td')
        if cells:
            # Extract information from cells
            videocard_name = cells[0].text.strip()
            passmark_g3d_mark = cells[1].text.strip() if len(cells) > 1 else ''
            rank = cells[2].text.strip() if len(cells) > 2 else ''
            videocard_value = cells[3].text.strip() if len(cells) > 3 else ''
            price = cells[4].text.strip() if len(cells) > 4 else ''

            # Use current datetime for 'datetime' and 'datetime_prev'
            now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            gpu_data.append((videocard_name, now, passmark_g3d_mark, rank, videocard_value, price))

try:
    # Connect to the database
    #if connection.is_connected():
        #cursor = connection.cursor()

        # Create table if it doesn't exist
        create_table_query = """
        CREATE TABLE IF NOT EXISTS api___price_gpu (
            id INT AUTO_INCREMENT PRIMARY KEY,
            datetime DATETIME NOT NULL,
            datetime_prev DATETIME NOT NULL,
            videocard_name VARCHAR(255) UNIQUE NOT NULL,
            passmark_g3d_mark VARCHAR(255) COMMENT '(higher is better)',
            rank VARCHAR(255) COMMENT '(lower is better)',
            videocard_value VARCHAR(255) COMMENT '(higher is better)',
            price VARCHAR(255) COMMENT '(USD)'
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """
        cursor.execute(create_table_query)

        # Fetch existing GPU names and their datetime values
        cursor.execute("SELECT videocard_name, datetime FROM api___price_gpu")
        existing_gpus = {row[0]: row[1] for row in cursor.fetchall()}

        # Insert or update GPU data
        for videocard_name, now, passmark_g3d_mark, rank, videocard_value, price in gpu_data:
            if videocard_name in existing_gpus:
                # Update existing record
                datetime_prev = existing_gpus[videocard_name]
                update_query = """
                UPDATE api___price_gpu
                SET datetime = %s,
                    datetime_prev = %s,
                    passmark_g3d_mark = %s,
                    rank = %s,
                    videocard_value = %s,
                    price = %s
                WHERE videocard_name = %s
                """
                cursor.execute(update_query, (now, datetime_prev, passmark_g3d_mark, rank, videocard_value, price, videocard_name))
            else:
                # Insert new record
                insert_query = """
                INSERT INTO api___price_gpu (datetime, datetime_prev, videocard_name, passmark_g3d_mark, rank, videocard_value, price)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                """
                cursor.execute(insert_query, (now, now, videocard_name, passmark_g3d_mark, rank, videocard_value, price))

        # Commit the changes
        connection.commit()
        print("GPU data has been inserted/updated in the database.")

except Error as e:
    print(f"Error: {e}")

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
