import mysql.connector
from mysql.connector import Error
from datetime import datetime
from db_config import connection, cursor  # db_config.py에서 connection과 cursor를 가져옴
from playwright.sync_api import sync_playwright

def create_table(cursor):
    create_table_query = """
    CREATE TABLE IF NOT EXISTS api___price_cpu_mega (
        id INT AUTO_INCREMENT PRIMARY KEY,
        datetime DATETIME NOT NULL,
        datetime_prev DATETIME NOT NULL,
        cpu_name VARCHAR(255) UNIQUE NOT NULL,
        cores VARCHAR(255),
        cpu_mark VARCHAR(255) COMMENT '(higher is better)',
        thread_mark VARCHAR(255) COMMENT '(higher is better)',
        tdp VARCHAR(255),
        socket VARCHAR(255),
        category VARCHAR(255)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """
    cursor.execute(create_table_query)

def fetch_cpu_data(playwright):
    browser = playwright.chromium.launch(headless=True)
    page = browser.new_page()
    page.goto("https://www.cpubenchmark.net/CPU_mega_page.html")

    # 페이지 길이 옵션을 "All"로 설정
    page.evaluate("""
    var table = $('#cputable').DataTable();
    table.page.len(-1).draw();
    """)

    # 페이지 로딩을 기다리기 (데이터가 모두 로드될 때까지)
    page.wait_for_timeout(5000)  # 적절한 대기 시간을 설정

    # 테이블 추출
    rows = page.query_selector_all('table#cputable tr')

    cpu_data = []

    for row in rows:
        cells = row.query_selector_all('td')
        if len(cells) > 0:
            cpu_name = cells[1].inner_text().strip()
            cores = cells[2].inner_text().strip() if len(cells) > 2 else ''
            cpu_mark = cells[3].inner_text().strip() if len(cells) > 3 else ''
            thread_mark = cells[4].inner_text().strip() if len(cells) > 4 else ''
            tdp = cells[5].inner_text().strip() if len(cells) > 5 else ''
            socket = cells[6].inner_text().strip() if len(cells) > 6 else ''
            category = cells[7].inner_text().strip() if len(cells) > 7 else ''

            cpu_data.append({
                'cpu_name': cpu_name,
                'cores': cores,
                'cpu_mark': cpu_mark,
                'thread_mark': thread_mark,
                'tdp': tdp,
                'socket': socket,
                'category': category
            })

    browser.close()
    return cpu_data

def insert_or_update_cpu_data(connection, cpu_data):
    cursor = connection.cursor()

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

        now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        for cpu in cpu_data:
            cpu_name = cpu['cpu_name']
            if cpu_name in existing_cpus:
                # Update existing record
                datetime_prev = existing_cpus[cpu_name]
                update_query = """
                UPDATE api___price_cpu_mega
                SET datetime = %s,
                    datetime_prev = %s,
                    cores = %s,
                    cpu_mark = %s,
                    thread_mark = %s,
                    tdp = %s,
                    socket = %s,
                    category = %s
                WHERE cpu_name = %s
                """
                cursor.execute(update_query, (now, datetime_prev, cpu['cores'], cpu['cpu_mark'], cpu['thread_mark'], cpu['tdp'], cpu['socket'], cpu['category'], cpu_name))
            else:
                # Insert new record
                insert_query = """
                INSERT INTO api___price_cpu_mega (datetime, datetime_prev, cpu_name, cores, cpu_mark, thread_mark, tdp, socket, category)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                cursor.execute(insert_query, (now, now, cpu_name, cpu['cores'], cpu['cpu_mark'], cpu['thread_mark'], cpu['tdp'], cpu['socket'], cpu['category']))

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

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

    finally:
        cursor.close()

def main():
    # 테이블 생성
    cursor = connection.cursor()
    create_table(cursor)

    # 데이터 추출
    with sync_playwright() as playwright:
        cpu_data = fetch_cpu_data(playwright)

    # 데이터베이스에 데이터 삽입/업데이트
    insert_or_update_cpu_data(connection, cpu_data)

    # 연결 종료
    if connection.is_connected():
        connection.close()

if __name__ == "__main__":
    main()
