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에서 connection과 cursor를 가져옴

# Define the URL for the CPU list
url = "https://www.cpubenchmark.net/cpu_list.php"

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

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

# Extract the CPU data from the table
cpu_data = []

if cpu_table:
    rows = cpu_table.find_all('tr')
    for row in rows:
        cells = row.find_all('td')
        if cells:
            # Extract information from cells
            cpu_name = cells[0].text.strip()
            cpu_mark = cells[1].text.strip() if len(cells) > 1 else ''
            rank = cells[2].text.strip() if len(cells) > 2 else ''
            cpu_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')
            cpu_data.append((cpu_name, now, cpu_mark, rank, cpu_value, price))

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

    # Fetch existing CPU names and their datetime values
    cursor.execute("SELECT cpu_name, datetime FROM api___price_cpu_single")
    existing_cpus = {row[0]: row[1] for row in cursor.fetchall()}

    # Insert or update CPU data
    for cpu_name, now, cpu_mark, rank, cpu_value, price in cpu_data:
        if cpu_name in existing_cpus:
            # Update existing record
            datetime_prev = existing_cpus[cpu_name]
            update_query = """
            UPDATE api___price_cpu_single
            SET datetime = %s,
                datetime_prev = %s,
                cpu_mark = %s,
                rank = %s,
                cpu_value = %s,
                price = %s
            WHERE cpu_name = %s
            """
            cursor.execute(update_query, (now, datetime_prev, cpu_mark, rank, cpu_value, price, cpu_name))
        else:
            # Insert new record
            insert_query = """
            INSERT INTO api___price_cpu_single (datetime, datetime_prev, cpu_name, cpu_mark, rank, cpu_value, price)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(insert_query, (now, now, cpu_name, cpu_mark, rank, cpu_value, price))

    # Commit the changes
    connection.commit()
    print("CPU 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()
