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, study_days:int, preferred_time_slot:str, weekday_hours:int):
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()

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

        cursor.execute(sql, (session_id, total_duration, study_days, preferred_time_slot, weekday_hours))
        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_study_days: int, studyplan_time_slot: str, studyplan_weekday_hours: int):
    """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_study_days, studyplan_time_slot, studyplan_weekday_hours)
            VALUES (%s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                studyplan_duration = VALUES(studyplan_duration),
                studyplan_study_days = VALUES(studyplan_study_days),
                studyplan_time_slot = VALUES(studyplan_time_slot),
                studyplan_weekday_hours = VALUES(studyplan_weekday_hours),
                updated_at = NOW()
        """

        cursor.execute(sql, (session_id, studyplan_duration, studyplan_study_days, studyplan_time_slot, studyplan_weekday_hours))
        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_study_days': int,
            'studyplan_time_slot': str,
            'studyplan_weekday_hours': int
        } 또는 None
    """
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT 
                studyplan_duration,
                studyplan_study_days,
                studyplan_time_slot,
                studyplan_weekday_hours
            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()