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 HDD list
url = "https://www.harddrivebenchmark.net/hdd_list.php"

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

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

# Extract the HDD data from the table
hdd_data = []

if hdd_table:
    rows = hdd_table.find_all('tr')
    for row in rows:
        cells = row.find_all('td')
        if cells:
            # Extract information from cells
            drive_name = cells[0].text.strip()
            size = cells[1].text.strip() if len(cells) > 1 else ''
            disk_rating = cells[2].text.strip() if len(cells) > 2 else ''
            rank = cells[3].text.strip() if len(cells) > 3 else ''
            drive_value = cells[4].text.strip() if len(cells) > 4 else ''
            price = cells[5].text.strip() if len(cells) > 5 else ''

            # Use current datetime for 'datetime' and 'datetime_prev'
            now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            hdd_data.append((now, now, drive_name, size, disk_rating, rank, drive_value, price))

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

    # Insert or update HDD data
    insert_query = """
    INSERT INTO api___price_hdd (datetime, datetime_prev, drive_name, size, disk_rating, rank, drive_value, price)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    """
    update_query = """
    UPDATE api___price_hdd
    SET datetime = %s,
        datetime_prev = %s,
        size = %s,
        disk_rating = %s,
        rank = %s,
        drive_value = %s,
        price = %s
    WHERE drive_name = %s
    """

    for hdd in hdd_data:
        try:
            cursor.execute(insert_query, hdd)
        except Error as e:
            if e.errno == 1062:  # Duplicate entry error code
                cursor.execute(update_query, hdd[1:] + (hdd[2],))
            else:
                print(f"Error during insert/update: {e}")

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