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


def save_studyplan_question(session_id: str, question_number: int, question_text: str, answer_text: str):
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()

        sql = """
            INSERT INTO studyplan_log
            (session_id, question_number, question_text, answer_text)
            VALUES (%s, %s, %s, %s)
        """

        cursor.execute(sql, (session_id, question_number, question_text, answer_text))
        conn.commit()

        return cursor.lastrowid
    except Exception as e:
        logger.error(f"StudyPlan Question Save Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()


def save_studyplan_output(session_id:str, total_duration:str, current_status:str, weekday_hours:int,weekend_hours:int, preferred_time_slot:str):
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()

        sql = """
            INSERT INTO studyplan_Output
            (session_id, total_duration, current_status, weekday_hours, weekend_hours, preferred_time_slot)
            VALUES (%s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                total_duration = VALUES(total_duration),
                current_status = VALUES(current_status),
                weekday_hours = VALUES(weekday_hours),
                weekend_hours = VALUES(weekend_hours),
                preferred_time_slot = VALUES(preferred_time_slot),
                updated_at = NOW()
        """

        cursor.execute(sql, (session_id, total_duration, current_status, weekday_hours, weekend_hours, preferred_time_slot))
        conn.commit()
        return cursor.lastrowid

    except Exception as e:
        logger.error(f"StudyPlan Output Save Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()



def get_answers_by_session(session_id: str) -> List[Dict]:
    """
    세션의 모든 답변 조회
    
    Returns:
        답변 리스트 (question_number, question_text, answer_text 포함)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT question_number, question_text, answer_text, id
            FROM studyplan_log
            WHERE session_id = %s
            ORDER BY question_number ASC, id DESC
        """
        
        cursor.execute(sql, (session_id,))
        all_results = cursor.fetchall()
        
        # 각 question_number별로 최신 답변만 가져오기 (id가 가장 큰 것)
        unique_answers = {}
        for row in all_results:
            q_num = row['question_number']
            # 이미 있으면 스킵 (id DESC로 정렬했으므로 첫 번째가 최신)
            if q_num not in unique_answers:
                unique_answers[q_num] = {
                    'question_number': row['question_number'],
                    'question_text': row['question_text'],
                    'answer_text': row['answer_text']
                }
        
        # question_number 순서대로 정렬하여 리스트로 반환
        results = [unique_answers[q_num] for q_num in sorted(unique_answers.keys())]
        
        return results
        
    except Exception as e:
        logger.error(f"StudyPlan Get Answers Error: {e}")
        return []
    finally:
        if conn:
            cursor.close()
            conn.close()


def sync_to_user_profile_summary(session_id: str, studyplan_duration: str, studyplan_status: str, studyplan_weekday_hours: int, studyplan_weekend_hours: int, studyplan_time_slot: str):
    """StudyPlan 완료 시 user_profile_summary 테이블에도 동기화"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()

        sql = """
            INSERT INTO user_profile_summary
            (session_id, studyplan_duration, studyplan_status, studyplan_weekday_hours, studyplan_weekend_hours, studyplan_time_slot)
            VALUES (%s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                studyplan_duration = VALUES(studyplan_duration),
                studyplan_status = VALUES(studyplan_status),
                studyplan_weekday_hours = VALUES(studyplan_weekday_hours),
                studyplan_weekend_hours = VALUES(studyplan_weekend_hours),
                studyplan_time_slot = VALUES(studyplan_time_slot),
                updated_at = NOW()
        """

        cursor.execute(sql, (session_id, studyplan_duration, studyplan_status, studyplan_weekday_hours, studyplan_weekend_hours, studyplan_time_slot))
        conn.commit()
        
        logger.info(f"StudyPlan synced to user_profile_summary for session: {session_id}")
        
    except Exception as e:
        logger.error(f"StudyPlan Summary Sync Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()


def get_studyplan_from_summary(session_id: str) -> dict:
    """
    user_profile_summary 테이블에서 StudyPlan 데이터 가져오기
    
    Returns:
        {
            'studyplan_duration': str,
            'studyplan_status': str,
            'studyplan_weekday_hours': int,
            'studyplan_weekend_hours': int,
            'studyplan_time_slot': str  # 원본 텍스트 (예: "夕方")
        } 또는 None
    """
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT 
                studyplan_duration,
                studyplan_status,
                studyplan_weekday_hours,
                studyplan_weekend_hours,
                studyplan_time_slot
            FROM user_profile_summary
            WHERE session_id = %s
        """
        
        cursor.execute(sql, (session_id,))
        result = cursor.fetchone()
        
        return result
        
    except Exception as e:
        logger.error(f"StudyPlan Get From Summary Error: {e}")
        return None
    finally:
        if conn:
            cursor.close()
            conn.close()