# app/models/it_module.py

import mysql.connector
from app.core.config import A_DB_CONFIG, logger
from typing import List, Dict, Optional


# ========================================
# it_log (답변 저장)
# ========================================
def save_answer(session_id: str, question_id: int, selected_answer: str, is_correct: bool):
    """
    IT 테스트 답변 저장 (it_log 테이블)
    
    Args:
        session_id: 세션 ID
        question_id: 문제 ID
        selected_answer: 선택한 답변
        is_correct: 정답 여부
    """
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO it_log 
            (session_id, question_id, selected_answer, is_correct)
            VALUES (%s, %s, %s, %s)
        """
        
        cursor.execute(sql, (session_id, question_id, selected_answer, is_correct))
        conn.commit()
        
        return cursor.lastrowid
        
    except Exception as e:
        logger.error(f"IT Answer Save Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()


# ========================================
# it_Output (최종 결과 저장)
# ========================================
def save_or_update_output(session_id: str, total_score: int, level: str):
    """
    IT 테스트 최종 결과 저장 (it_Output 테이블)
    
    Args:
        session_id: 세션 ID
        total_score: 맞힌 문제 수 (0~20)
        level: 레벨 (上級/中級/初級/入門)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO it_Output 
            (session_id, total_score, level)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE
                total_score = VALUES(total_score),
                level = VALUES(level)
        """
        
        cursor.execute(sql, (session_id, total_score, level))
        conn.commit()
        
        logger.info(f"IT Output saved for session: {session_id}")
        
        # user_profile_summary에도 동기화
        sync_to_user_profile_summary(session_id, level)
        
    except Exception as e:
        logger.error(f"IT Output Save Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()


# ========================================
# 답변 조회
# ========================================
def get_answer_count(session_id: str) -> int:
    """답변한 문제 수 조회"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = "SELECT COUNT(*) FROM it_log WHERE session_id = %s"
        cursor.execute(sql, (session_id,))
        count = cursor.fetchone()[0]
        
        return count
        
    except Exception as e:
        logger.error(f"IT Answer Count Error: {e}")
        return 0
    finally:
        if conn:
            cursor.close()
            conn.close()


def get_all_answers(session_id: str) -> List[Dict]:
    """모든 답변 조회"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT question_id, selected_answer, is_correct
            FROM it_log
            WHERE session_id = %s
            ORDER BY question_id
        """
        cursor.execute(sql, (session_id,))
        results = cursor.fetchall()
        
        return results
        
    except Exception as e:
        logger.error(f"IT Get Answers Error: {e}")
        return []
    finally:
        if conn:
            cursor.close()
            conn.close()


# ========================================
# 세션 초기화 (재시작용)
# ========================================
def clear_session_logs(session_id: str):
    """
    IT 테스트 재시작 시 이전 답변 기록 삭제
    
    Args:
        session_id: 세션 ID
    """
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = "DELETE FROM it_log WHERE session_id = %s"
        cursor.execute(sql, (session_id,))
        conn.commit()
        
        logger.info(f"IT logs cleared for session: {session_id}")
        
    except Exception as e:
        logger.error(f"IT Clear Logs Error: {e}")
        # 초기화 실패해도 계속 진행
    finally:
        if conn:
            cursor.close()
            conn.close()


# ========================================
# user_profile_summary 동기화
# ========================================
def sync_to_user_profile_summary(session_id: str, level: str):
    """
    it_Output의 level을 user_profile_summary.it_level에 저장
    
    Args:
        session_id: 세션 ID
        level: 레벨 (上級/中級/初級/入門)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO user_profile_summary (session_id, it_level)
            VALUES (%s, %s)
            ON DUPLICATE KEY UPDATE
                it_level = VALUES(it_level)
        """
        
        cursor.execute(sql, (session_id, level))
        conn.commit()
        
        logger.info(f"IT level synced to user_profile_summary: {session_id} -> {level}")
        
    except Exception as e:
        logger.error(f"IT Sync to Summary Error: {e}")
        # 동기화 실패해도 메인 로직은 계속 진행
    finally:
        if conn:
            cursor.close()
            conn.close()