# app/models/human_os_module.py

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

A_DB_CONFIG = get_db_config("Sales_A_DB")

# ========================================
# human_os_log (답변 저장)
# ========================================
def save_answer(session_id: str, question_id: int, selected_option: str, value: float):
    """
    human_os 답변 저장 (human_os_log 테이블)
    
    Args:
        session_id: 세션 ID
        question_id: 문제 ID
        selected_option: 선택한 답변
        value: 선택한 답변의 값
    """
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO human_os_log 
            (session_id, question_id, selected_option, value)
            VALUES (%s, %s, %s, %s)
        """
        
        cursor.execute(sql, (session_id, question_id, selected_option, value))
        conn.commit()
        
        return cursor.lastrowid
        
    except Exception as e:
        logger.error(f"human_os Answer Save Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()


# ========================================
# human_os_Output (최종 결과 저장)
# ========================================
def save_or_update_output(session_id: str, Node_score: str, output_value: float):

    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO human_os_Output 
            (session_id, Node_score, output_value)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE
                Node_score = VALUES(Node_score),
                output_value = VALUES(output_value)
        """
        
        cursor.execute(sql, (session_id, Node_score, output_value))
        conn.commit()
        
        logger.info(f"human_os_Output saved for session: {session_id}")
        
        # user_profile_summary에도 동기화 
        sync_to_user_profile_summary(
            session_id, Node_score
        )
        
    except Exception as e:
        logger.error(f"human_os Output Save Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()




def sync_to_user_profile_summary(session_id: str, Node_score: str):
    """ 人間OS 6軸 완료 시 user_profile_summary 테이블에도 동기화"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO user_profile_summary
            (session_id, human_os_score)
            VALUES (%s, %s)
            ON DUPLICATE KEY UPDATE
                human_os_score = VALUES(human_os_score),
                updated_at = NOW()
        """
        cursor.execute(sql, (session_id, Node_score))
        conn.commit()
        
        logger.info(f"human_os synced to user_profile_summary for session: {session_id}")
        
    except Exception as e:
        logger.error(f"human_os Summary Sync Error: {e}")
        # 동기화 실패해도 메인 로직은 계속 진행되도록 에러를 raise하지 않음
        pass
    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 human_os_log WHERE session_id = %s"
        cursor.execute(sql, (session_id,))
        
        result = cursor.fetchone()
        return result[0] if result else 0
        
    except Exception as e:
        logger.error(f"human_os 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, value
            FROM human_os_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"human_os All Answers Error: {e}")
        return []
    finally:
        if conn:
            cursor.close()
            conn.close()


def get_output(session_id: str) -> Optional[Dict]:
    """human_os_Output 최종 결과 조회"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = "SELECT * FROM human_os_Output WHERE session_id = %s"
        cursor.execute(sql, (session_id,))
        
        result = cursor.fetchone()
        return result
        
    except Exception as e:
        logger.error(f"human_os_Output Get Error: {e}")
        return None
    finally:
        if conn:
            cursor.close()
            conn.close()


# human_os 세션 초기화

def clear_session_logs(session_id: str):
    """
    테스트 시작 시 이전 기록 초기화 (재시작용)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        # spi_log 테이블에서 해당 세션의 기록 모두 삭제
        sql = "DELETE FROM human_os_log WHERE session_id = %s"
        cursor.execute(sql, (session_id,))
        conn.commit()
        
        logger.info(f"human_os Session Cleared: {session_id}")
        
    except Exception as e:
        logger.error(f"human_os Session Clear Error: {e}")
        # 에러가 나도 진행은 되도록 raise는 안 함
    finally:
        if conn:
            cursor.close()
            conn.close()