import pandas as pd
import numpy as np
import mysql.connector
import logging
import joblib
from sklearn.preprocessing import MinMaxScaler
from datetime import datetime
import gc
import psutil
import re
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error

# 로깅 설정
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

def log_memory_usage(message):
    process = psutil.Process()
    memory_info = process.memory_info()
    logging.info(f"{message} - 메모리 사용량: {memory_info.rss / 1024 / 1024:.2f} MB")

# 청크 크기 설정
chunk_size = 500

# MySQL 연결
db = mysql.connector.connect(
    host="localhost",
    user="global",
    password="vdP)QRBKOY]5QNdw",
    database="global",
    connect_timeout=3600,
    buffered=True
)

# 인덱스 추가를 위한 SQL 문
index_queries = [
    "CREATE INDEX idx_cpu_rank ON api___price_cpu_single (`rank`);",
    "CREATE INDEX idx_gpu_rank ON api___price_gpu (`rank`);",
    "CREATE INDEX idx_hdd_rank ON api___price_hdd (`rank`);",
    "CREATE INDEX idx_hdd_drive_name ON api___price_hdd (drive_name);",
    "CREATE INDEX idx_hdd_storage_type ON api___price_hdd (storage_type);",
    "CREATE INDEX idx_ram_rank ON api___price_ram (`rank`);",
    "CREATE INDEX idx_ram_ramtype ON api___price_ram (ramtype);",
    "CREATE INDEX idx_ram_memory_name ON api___price_ram (memory_name);"
]

# 인덱스 추가
cursor = db.cursor()
for query in index_queries:
    try:
        cursor.execute(query)
        print(f"인덱스 추가 성공: {query}")
    except mysql.connector.Error as err:
        if err.errno == 1061:  # 이미 존재하는 인덱스 에러 코드
            print(f"인덱스가 이미 존재합니다: {query}")
        else:
            print(f"인덱스 추가 중 오류 발생: {err}")

db.commit()
cursor.close()

def ensure_connection(db):
    try:
        db.ping(reconnect=True, attempts=3, delay=5)
    except mysql.connector.Error as err:
        print(f"Connection error: {err}")
        db.reconnect(attempts=5, delay=10)
    return db

def clean_price(price):
    if isinstance(price, str):
        price = re.sub(r'[^\d.]', '', price)
        parts = price.split('.')
        if len(parts) > 1:
            price = parts[0] + '.' + parts[1][:2]
        else:
            price = parts[0]
    try:
        return float(price)
    except ValueError:
        return None

# CPU 데이터 처리
log_memory_usage("CPU 데이터 처리 시작")
query = "SELECT * FROM api___price_cpu_single ORDER BY `rank`"
db = ensure_connection(db)

df_list = []
total_rows = 0

for chunk in pd.read_sql_query(query, db, chunksize=chunk_size):
    df_list.append(chunk)
    total_rows += len(chunk)
    log_memory_usage(f"CPU: {total_rows}행 처리 후")

    gc.collect()

    if total_rows % (chunk_size * 10) == 0:
        df_cpu = pd.concat(df_list, ignore_index=True)
        df_cpu['price_clean'] = df_cpu['price_org'].apply(clean_price)
        df_cpu['price'] = df_cpu['price_clean'].interpolate()
        df_cpu['price'] = pd.to_numeric(df_cpu['price'], errors='coerce')
        df_cpu['price'] = df_cpu['price'].fillna(df_cpu['price'].mean())
        df_cpu['price'] = df_cpu['price'].round(2)

        cursor = db.cursor()
        for index, row in df_cpu.iterrows():
            update_query = """
            UPDATE api___price_cpu_single
            SET price = %s
            WHERE id = %s
            """
            cursor.execute(update_query, (row['price'], row['id']))
        db.commit()

        df_list = []
        gc.collect()
        log_memory_usage(f"CPU: {total_rows}행 처리 및 정리 후")

if df_list:
    df_cpu = pd.concat(df_list, ignore_index=True)
    # 남은 CPU 데이터 처리 로직 (위와 동일)

log_memory_usage("CPU 데이터 처리 후")
print("api___price_cpu_single 테이블의 price 컬럼이 업데이트되었습니다.")

# GPU 데이터 처리
log_memory_usage("GPU 데이터 처리 시작")
query = "SELECT * FROM api___price_gpu ORDER BY `rank`"
db = ensure_connection(db)

df_list = []
total_rows = 0

for chunk in pd.read_sql_query(query, db, chunksize=chunk_size):
    df_list.append(chunk)
    total_rows += len(chunk)
    log_memory_usage(f"GPU: {total_rows}행 처리 후")

    gc.collect()

    if total_rows % (chunk_size * 10) == 0:
        df_gpu = pd.concat(df_list, ignore_index=True)
        df_gpu['price_clean'] = df_gpu['price_org'].apply(clean_price)
        df_gpu['price'] = df_gpu['price_clean'].interpolate()
        df_gpu['price'] = pd.to_numeric(df_gpu['price'], errors='coerce')
        df_gpu['price'] = df_gpu['price'].fillna(df_gpu['price'].mean())
        df_gpu['price'] = df_gpu['price'].round(2)

        cursor = db.cursor()
        for index, row in df_gpu.iterrows():
            update_query = """
            UPDATE api___price_gpu
            SET price = %s
            WHERE id = %s
            """
            cursor.execute(update_query, (row['price'], row['id']))
        db.commit()

        df_list = []
        gc.collect()
        log_memory_usage(f"GPU: {total_rows}행 처리 및 정리 후")

if df_list:
    df_gpu = pd.concat(df_list, ignore_index=True)
    # 남은 GPU 데이터 처리 로직 (위와 동일)

log_memory_usage("GPU 데이터 처리 후")
print("api___price_gpu 테이블의 price 컬럼이 업데이트되었습니다.")

# HDD 데이터 처리
log_memory_usage("HDD 데이터 처리 시작")
query = "SELECT * FROM api___price_hdd WHERE `drive_name` LIKE '%M.2%' OR `drive_name` LIKE '%SSD%' OR `drive_name` LIKE '%NVMe%' OR `drive_name` LIKE '%HDD%' OR `drive_name` LIKE '%USB%' ORDER BY `rank`"
db = ensure_connection(db)

df_list = []
total_rows = 0

for chunk in pd.read_sql_query(query, db, chunksize=chunk_size):
    df_list.append(chunk)
    total_rows += len(chunk)
    log_memory_usage(f"HDD: {total_rows}행 처리 후")

    gc.collect()

    if total_rows % (chunk_size * 10) == 0:
        df_hdd = pd.concat(df_list, ignore_index=True)
        df_hdd['price_clean'] = df_hdd['price_org'].apply(clean_price)
        df_hdd['price'] = df_hdd['price_clean'].fillna(method='ffill')
        df_hdd['price'] = pd.to_numeric(df_hdd['price'], errors='coerce')
        df_hdd['price'] = df_hdd['price'].fillna(df_hdd['price'].mean())
        df_hdd['price'] = df_hdd['price'].round(2)

        cursor = db.cursor()
        for index, row in df_hdd.iterrows():
            update_query = """
            UPDATE api___price_hdd
            SET price = %s, storage_type = %s
            WHERE id = %s
            """
            storage_type = 'SSD' if any(x in row['drive_name'].upper() for x in ['SSD', 'M.2', 'NVME']) else 'HDD'
            cursor.execute(update_query, (row['price'], storage_type, row['id']))
        db.commit()

        df_list = []
        gc.collect()
        log_memory_usage(f"HDD: {total_rows}행 처리 및 정리 후")

if df_list:
    df_hdd = pd.concat(df_list, ignore_index=True)
    # 남은 HDD 데이터 처리 로직 (위와 동일)

log_memory_usage("HDD 데이터 처리 후")
print("api___price_hdd 테이블의 price 및 storage_type 컬럼이 업데이트되었습니다.")

# RAM 데이터 처리
log_memory_usage("RAM 데이터 처리 시작")
query = "SELECT * FROM api___price_ram ORDER BY `rank`"
db = ensure_connection(db)

df_list = []
total_rows = 0

for chunk in pd.read_sql_query(query, db, chunksize=chunk_size):
    df_list.append(chunk)
    total_rows += len(chunk)
    log_memory_usage(f"RAM: {total_rows}행 처리 후")

    gc.collect()

    if total_rows % (chunk_size * 10) == 0:
        df_ram = pd.concat(df_list, ignore_index=True)
        df_ram['price_clean'] = df_ram['price_org'].apply(clean_price)
        df_ram['price'] = df_ram['price_clean'].interpolate()
        df_ram['price'] = pd.to_numeric(df_ram['price'], errors='coerce')
        df_ram['price'] = df_ram['price'].fillna(df_ram['price'].mean())
        df_ram['price'] = df_ram['price'].round(2)

        cursor = db.cursor()
        for index, row in df_ram.iterrows():
            update_query = """
            UPDATE api___price_ram
            SET price = %s
            WHERE id = %s
            """
            cursor.execute(update_query, (float(row['price']), row['id']))
        db.commit()

        df_list = []
        gc.collect()
        log_memory_usage(f"RAM: {total_rows}행 처리 및 정리 후")

if df_list:
    df_ram = pd.concat(df_list, ignore_index=True)
    # 남은 RAM 데이터 처리 로직 (위와 동일)

log_memory_usage("RAM 데이터 처리 후")
print("api___price_ram 테이블의 price 컬럼이 업데이트되었습니다.")

# api___ai_suggested_price_of_ram 테이블 생성 및 데이터 삽입
cursor.execute("DROP TABLE IF EXISTS api___ai_suggested_price_of_ram")
create_table_query = """
CREATE TABLE api___ai_suggested_price_of_ram (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ramtype VARCHAR(255),
    memory_size VARCHAR(255),
    price FLOAT
)
"""
cursor.execute(create_table_query)

query = "SELECT ramtype, memory_name, price FROM api___price_ram"
db = ensure_connection(db)

df_list = []
total_rows = 0

for chunk in pd.read_sql_query(query, db, chunksize=chunk_size):
    df_list.append(chunk)
    total_rows += len(chunk)
    log_memory_usage(f"RAM 그룹화: {total_rows}행 처리 후")

    gc.collect()

df_ram = pd.concat(df_list, ignore_index=True)

df_ram['price'] = df_ram['price'].apply(clean_price)

def extract_memory_size(memory_name):
    match = re.search(r'(\d+)\s*(GB|MB)$', memory_name, re.IGNORECASE)
    if match:
        size, unit = match.groups()
        return f"{size}{unit}"
    return None

df_ram['memory_size'] = df_ram['memory_name'].apply(extract_memory_size)
df_ram = df_ram.dropna(subset=['memory_size'])
df_ram = df_ram.dropna(subset=['ramtype', 'memory_size', 'price'])

grouped = df_ram.groupby(['ramtype', 'memory_size'])['price'].agg(['sum', 'count', 'mean']).reset_index()

for _, row in grouped.iterrows():
    insert_query = """
    INSERT INTO api___ai_suggested_price_of_ram (ramtype, memory_size, price)
    VALUES (%s, %s, %s)
    """
    cursor.execute(insert_query, (row['ramtype'], row['memory_size'], row['mean']))

print("RAM 타입 및 용량별 가격 정보:")
for _, row in grouped.iterrows():
    print(f"RAM 타입: {row['ramtype']}, 용량: {row['memory_size']}")
    print(f"  총합: {row['sum']:.2f}, 개수: {row['count']}, 평균: {row['mean']:.2f}")
    print(f"  계산식: {row['sum']} / {row['count']} = {row['mean']:.2f}")
    print()

db.commit()
print("api___ai_suggested_price_of_ram 테이블이 생성되었습니다.")
print("")

# 모델 학습
log_memory_usage("모델 학습 시작")

query = """
SELECT
    cpu.cpu_name AS cpu_name, cpu.price AS cpu_price,
    gpu.videocard_name AS gpu_name, gpu.price AS gpu_price,
    ram.memory_size AS ram_size, ram.ramtype AS ram_type, ram.price AS ram_price,
    hdd.drive_name AS hdd_name, hdd.storage_type AS storage_type, hdd.price AS hdd_price
FROM api___price_cpu_single cpu
CROSS JOIN api___price_gpu gpu
CROSS JOIN api___ai_suggested_price_of_ram ram
CROSS JOIN api___price_hdd hdd
"""

cursor = db.cursor(dictionary=True, buffered=False)
cursor.execute(query)

df_list = []
total_rows = 0
max_rows = 50  # Adjust this value based on your system's memory capacity

while True:
    chunk = cursor.fetchmany(chunk_size)
    if not chunk:
        break
    df_chunk = pd.DataFrame(chunk)
    df_list.append(df_chunk)
    total_rows += len(chunk)
    logging.info(f"Processed {total_rows} rows")
    log_memory_usage(f"모델 데이터: {total_rows}행 처리 후")

    gc.collect()

    if total_rows >= max_rows:
        break

df = pd.concat(df_list, ignore_index=True)
log_memory_usage("데이터프레임 연결 후")

del df_list
gc.collect()

def normalize_cpu_gpu(model):
    model_lower = model.lower()
    if 'intel' in model_lower:
        return 'Intel'
    elif 'amd' in model_lower:
        return 'AMD'
    elif 'nvidia' in model_lower or 'geforce' in model_lower:
        return 'NVIDIA'
    else:
        return 'Other'

df['cpu_brand'] = df['cpu_name'].apply(normalize_cpu_gpu)
df['gpu_brand'] = df['gpu_name'].apply(normalize_cpu_gpu)

df['ram_type'] = df['ram_type'].fillna('Unknown')
df['storage_type'] = df['storage_type'].str.lower()

df = pd.get_dummies(df, columns=['cpu_brand', 'gpu_brand', 'ram_type', 'storage_type'])

df['total_price'] = df['cpu_price'] + df['gpu_price'] + df['ram_price'] + df['hdd_price']

X = df.drop(['cpu_name', 'gpu_name', 'hdd_name', 'cpu_price', 'gpu_price', 'ram_price', 'hdd_price', 'total_price'], axis=1)
y = df['total_price']

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

model = LinearRegression()
model.fit(X_train, y_train)

y_pred = model.predict(X_test)
mse = mean_squared_error(y_test, y_pred)
print(f"평균 제곱 오차: {mse}")

joblib.dump(model, '/mnt/s/source/_rpcworld/html/python_source/used_pc_price_model.joblib')
joblib.dump(X.columns.tolist(), '/mnt/s/source/_rpcworld/html/python_source/model_columns.joblib')

print("모델과 컬럼 정보가 저장되었습니다.")

db.close()

print("AI 모델 학습을 완료 하였습니다.")
log_memory_usage("프로그램 종료")