import re
import pandas as pd
import mysql.connector
from used_pc_price_from_api_cpu_gpu_ram_hdd_table_entire_update_for_item_save_joblib import ensure_connection, process_na_price, clean_price
from db_config import get_db_connection
import numpy as np
import requests

# db 객체 생성
db = get_db_connection()
cursor = db.cursor()

def get_exchange_rate():
    try:
        response = requests.get("https://api.exchangerate-api.com/v4/latest/USD")
        data = response.json()
        return data['rates']['KRW']

    except:
        return 1300  # API 호출 실패 시 기본 환율 사용

exchange_rate = get_exchange_rate()

# USD를 KRW로 변환하는 함수
def usd_to_krw(usd_price):
    return int(usd_price * exchange_rate)

def convert_to_gb(size_str):
    if pd.isna(size_str):
        return None

    size_str = str(size_str).strip().lower()
    match = re.search(r'(\d+(?:\.\d+)?)\s*(TB|GB)', size_str, re.IGNORECASE)
    if match:
        size, unit = match.groups()
        size = float(size)
        if unit.lower() == 'tb':
            return int(size * 1000)
        else:
            return int(size)
    return None

def extract_number(price_str):
    if pd.isna(price_str):
        return 0
    # 문자열에서 숫자와 소수점만 추출
    number = re.sub(r'[^\d.]', '', str(price_str))
    try:
        return float(number)
    except ValueError:
        return 0

def update_storage_type(row):
    drive_name = str(row['drive_name']).lower()
    size = row['size']
    price = usd_to_krw(extract_number(row['price']))

    # 가격을 GB 단위의 크기로 나눈 값 계산
    if ((size * 150) > price):
        return 'HDD SATA'
    else:
        if 'm.2' in drive_name and 'nvme' in drive_name:
            return 'SSD M.2 NVMe'
        elif 'nvme' in drive_name:
            return 'SSD M.2 NVMe'
        elif 'ssd' in drive_name:
            if size > 1024:
                return 'SSD M.2 NVMe'
            elif 'm.2' in drive_name:
                return 'SSD M.2 SATA'
            else:
                return 'SSD SATA'
        # elif any(x in drive_name for x in ['wdc wd', 'seagate st', 'hgst ht', 'samsung hd', 'hitachi hd']) and 'ssd' not in drive_name and 'nvme' not in drive_name:
        #     return 'HDD SATA'
        else:
            if ((size * 150) <= price):
                return 'SSD SATA'
            else:
                return 'HDD SATA'

# 테이블 재생성
cursor.execute("DROP TABLE IF EXISTS api___ai_suggested_price_of_hdd")
cursor.execute("DROP TABLE IF EXISTS api___ai_suggested_price_of_ssd")

cursor.execute("""
CREATE TABLE api___ai_suggested_price_of_hdd (
    id INT AUTO_INCREMENT PRIMARY KEY,
    storage_type VARCHAR(50),
    size INT,
    price INT
)
""")

cursor.execute("""
CREATE TABLE api___ai_suggested_price_of_ssd (
    id INT AUTO_INCREMENT PRIMARY KEY,
    storage_type VARCHAR(50),
    size INT,
    price INT
)
""")

# 데이터 가져오기
query = "SELECT id, drive_name, size, price FROM api___price_hdd WHERE price != 'NA' AND price != '0' AND price != '' AND price NOT LIKE '%*' "
ensure_connection(db)
df_hdd = pd.read_sql_query(query, db)

# 데이터 전처리
df_hdd['size'] = df_hdd['size'].apply(convert_to_gb)
df_hdd['storage_type'] = df_hdd.apply(update_storage_type, axis=1)
df_hdd['price'] = df_hdd['price'].apply(clean_price)

# 유효하지 않은 크기, 가격, "Unknown" storage_type 제거
df_hdd = df_hdd[(df_hdd['size'].notna()) & (df_hdd['price'].notna()) & (df_hdd['storage_type'] != 'Unknown')]

def calculate_median_price(group):
    if group.empty:
        return pd.Series({'price': np.nan, 'id': group['id'].iloc[0]})
    return pd.Series({'price': group['price'].median(), 'id': group['id'].iloc[0]})

def calculate_average_price(group):
    median = group['price'].median()
    lower_bound = median * 0.5
    upper_bound = median * 1.5
    filtered_group = group[(group['price'] >= lower_bound) & (group['price'] <= upper_bound)]
    return filtered_group['price'].mean() if not filtered_group.empty else np.nan

# SSD 데이터 처리
df_ssd = df_hdd[df_hdd['storage_type'].isin(['SSD M.2 NVMe', 'SSD M.2 SATA', 'SSD SATA'])]
df_ssd_grouped = df_ssd.groupby(['storage_type', 'size']).apply(calculate_median_price).reset_index()
df_ssd_grouped.columns = ['storage_type', 'size', 'price', 'id']

# HDD 데이터 처리
df_hdd = df_hdd[df_hdd['storage_type'] == 'HDD SATA']
df_hdd_grouped = df_hdd.groupby(['storage_type', 'size']).apply(calculate_median_price).reset_index()
df_hdd_grouped.columns = ['storage_type', 'size', 'price', 'id']



# 데이터베이스에 삽입하는 함수
def insert_data(df, table_name):
    for _, row in df.iterrows():
        if pd.notna(row['price']):
            krw_price = usd_to_krw(row['price'])
            print(f"Inserting into {table_name}: {row['storage_type']}, {int(row['size'])}, {krw_price}")
            cursor.execute(f"""
            INSERT INTO {table_name} (storage_type, size, price)
            VALUES (%s, %s, %s)
            """, (row['storage_type'], int(row['size']), krw_price))

# 새로운 데이터 삽입
insert_data(df_ssd_grouped, "api___ai_suggested_price_of_ssd")
insert_data(df_hdd_grouped, "api___ai_suggested_price_of_hdd")

# HDD 평균 단가 계산 및 삽입
hdd_avg_price = df_hdd_grouped.groupby('storage_type').apply(lambda x: (x['price'] / x['size']).mean()).reset_index()
hdd_avg_price.columns = ['storage_type', 'avg_price_per_gb']
hdd_avg_price['storage_type'] = hdd_avg_price['storage_type'] + ' AVG'
hdd_avg_price['size'] = 1
hdd_avg_price['price'] = hdd_avg_price['avg_price_per_gb'].round(5)

print("HDD 평균 단가 데이터:")
print(hdd_avg_price)

# HDD 평균 단가 데이터 삽입
insert_data(hdd_avg_price[['storage_type', 'size', 'price']], "api___ai_suggested_price_of_hdd")

# SSD 평균 단가 계산 및 삽입
ssd_avg_price = df_ssd_grouped.groupby('storage_type').apply(lambda x: (x['price'] / x['size']).mean()).reset_index()
ssd_avg_price.columns = ['storage_type', 'avg_price_per_gb']
ssd_avg_price['storage_type'] = ssd_avg_price['storage_type'] + ' AVG'
ssd_avg_price['size'] = 1
ssd_avg_price['price'] = ssd_avg_price['avg_price_per_gb'].round(5)

print("SSD 평균 단가 데이터:")
print(ssd_avg_price)

# SSD 평균 단가 데이터 삽입
insert_data(ssd_avg_price[['storage_type', 'size', 'price']], "api___ai_suggested_price_of_ssd")

print("HDD 및 SSD 평균 단가 데이터가 각각의 테이블에 삽입되었습니다.")


db.commit()
cursor.close()
db.close()

print("api___ai_suggested_price_of_ssd 및 api___ai_suggested_price_of_hdd 테이블이 업데이트되었습니다.")
