import mysql.connector
from app.core.config import get_db_config, logger, PROGRESS_PDF_PATH
from datetime import timedelta
from datetime import datetime, timedelta
import openpyxl 
import subprocess
import os
from openpyxl.formatting.rule import DataBarRule

import re

# C_DB 설정 가져오기
C_DB_CONFIG = get_db_config("C_DB")
Goalskill_login_DB_CONFIG = get_db_config("Goalskill_login")
Goalskill_DB_CONFIG = get_db_config("Goalskill_DB")
THEORY_DB_CONFIG = get_db_config("Theory_DB")
A_DB_CONFIG = get_db_config("A_DB")

# =========================================================
# 이론 서브챕터 범위 매핑 (일차별 분할)
# 같은 章이 2일에 걸쳐 배정된 경우, item_id → (시작, 끝) 서브챕터 번호
# 예: item_id 4 → 第1章의 서브챕터 1-1 ~ 1-4 만 표시
# 이 매핑에 없는 item_id는 해당 섹션 전체를 표시
# =========================================================
THEORY_SUB_RANGES = {
    # 第1章 基礎理論① (서브챕터 1-1 ~ 1-8)
    4:  (1, 4),    # M1-D3: データの単位・基数変換 → 1-1 ~ 1-4
    8:  (5, 8),    # M1-D5: 2進数の演算・シフト演算 → 1-5 ~ 1-8
    # 第2章 基礎理論② (서브챕터 2-1 ~ 2-5)
    12: (1, 2),    # M1-D7: 集合と論理演算 → 2-1 ~ 2-2
    16: (3, 5),    # M1-D9: 確率・統計・情報量 → 2-3 ~ 2-5
    # 第9章 データベース (서브챕터 9-1 ~ 9-6)
    68: (1, 3),    # M3-D5: DB基本・関係DB・設計 → 9-1 ~ 9-3
    72: (4, 6),    # M3-D7: 管理システム・トランザクション・排他制御 → 9-4 ~ 9-6
    # 第10章 ネットワーク (서브챕터 10-1 ~ 10-28)
    76: (1, 14),   # M3-D9: 回線・LAN/WAN・IPアドレス → 10-1 ~ 10-14
    80: (15, 28),  # M3-D11: プロトコル・中継装置・メール → 10-15 ~ 10-28
    # 第11章 情報セキュリティ (서브챕터 11-1 ~ 11-43)
    94: (1, 21),   # M4-D3: 脅威・攻撃・暗号化・デジタル署名 → 11-1 ~ 11-21
    98: (22, 43),  # M4-D5: PKI・リスク管理・対策・認証 → 11-22 ~ 11-43
}

def get_cert_item_for_day(session_id: str, current_day: int):
    """현재 일차의 CERT 아이템 조회 (THEORY/PRACTICE 모두)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        block_id, sort_order = _day_to_block_and_sort(current_day)
        
        sql = """
            SELECT uc.item_id, mi.name, mi.category, mi.item_type
            FROM user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            WHERE uc.session_id = %s
              AND mi.block_id = %s
              AND mi.sort_order = %s
              AND (mi.category = 'CERT' OR mi.category LIKE 'CERT\\_%')
            ORDER BY mi.item_id ASC
            LIMIT 1
        """
        cursor.execute(sql, (session_id, block_id, sort_order))
        return cursor.fetchone()
    except Exception as e:
        logger.error(f"get_cert_item_for_day error: {e}")
        return None
    finally:
        if conn: conn.close()

def get_cert_chapters_for_month(session_id: str, current_day: int):
    """해당 월(block_id)에 속한 모든 CERT 장 번호 목록 반환 (13~20일 복습용)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        block_id, _ = _day_to_block_and_sort(current_day)
        
        sql = """
            SELECT mi.name, mi.sort_order
            FROM user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            WHERE uc.session_id = %s
              AND mi.block_id = %s
              AND (mi.category = 'CERT' OR mi.category LIKE 'CERT\\_%')
              AND mi.item_type = 'THEORY'
            ORDER BY mi.sort_order ASC
        """
        cursor.execute(sql, (session_id, block_id))
        rows = cursor.fetchall()
        
        # 이름에서 "第N章" 추출하여 중복 없이 반환
        chapters = []
        seen = set()
        for row in rows:
            match = re.search(r'第(\d+)章', row['name'])
            if match:
                ch_num = int(match.group(1))
                if ch_num not in seen:
                    seen.add(ch_num)
                    chapters.append({
                        "chapter": ch_num,
                        "name": row['name']
                    })
        return chapters
    except Exception as e:
        logger.error(f"get_cert_chapters_for_month error: {e}")
        return []
    finally:
        if conn: conn.close()

def get_theory_data(section_num: int, chapter_num: str = None, sub_range: tuple = None):
    """Theory_DB.section_N 테이블에서 이론 데이터를 조회
    
    Args:
        section_num: 섹션(章) 번호
        chapter_num: 특정 챕터만 조회 (예: '2-1')
        sub_range: (start, end) 서브챕터 번호 범위 필터 (예: (1, 4) → X-1 ~ X-4)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**THEORY_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        table_name = f"section_{section_num}"
        
        if chapter_num:
            sql = f"""
                SELECT id, chapter, main_title, sub_title, 
                       theory_description, tip, img, img_description
                FROM `{table_name}`
                WHERE chapter = %s
                ORDER BY id ASC
            """
            cursor.execute(sql, (chapter_num,))
        elif sub_range:
            # 서브챕터 번호 범위로 필터링 (예: chapter '1-3' → 서브번호 3)
            sql = f"""
                SELECT id, chapter, main_title, sub_title, 
                       theory_description, tip, img, img_description
                FROM `{table_name}`
                WHERE CAST(SUBSTRING_INDEX(chapter, '-', -1) AS UNSIGNED) BETWEEN %s AND %s
                ORDER BY id ASC
            """
            cursor.execute(sql, (sub_range[0], sub_range[1]))
        else:
            sql = f"""
                SELECT id, chapter, main_title, sub_title, 
                       theory_description, tip, img, img_description
                FROM `{table_name}`
                ORDER BY id ASC
            """
            cursor.execute(sql)
        
        return cursor.fetchall()
    except Exception as e:
        logger.error(f"get_theory_data error: {e}")
        return []
    finally:
        if conn: conn.close()

def get_user_progress(session_id: str):
    """유저의 현재 일차 확인 (Goalskill_DB.result.daily)"""
    conn = None
    try:
        conn = mysql.connector.connect(**Goalskill_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # result 테이블에서 해당 유저의 최신 daily 값을 가져온다
        sql = """
            SELECT daily
            FROM result
            WHERE session_id = %s
            ORDER BY id DESC
            LIMIT 1
        """
        cursor.execute(sql, (session_id,))
        row = cursor.fetchone()
        
        if not row:
            # result에 데이터가 없으면 1일차, 진행중(0) 으로 간주
            return {'current_day': 1, 'status': 0}
        
        return {'current_day': row['daily'], 'status': 0}
    finally:
        if conn: conn.close()

def get_previous_feedback(session_id: str, current_day: int):
    """N-1일차의 디버그 일지 조회 (C_DB)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # [수정] 복잡한 날짜 계산 제거. 
        # current_day가 3이면, 어제는 무조건 2입니다.
        prev_day = current_day - 1
        
        if prev_day < 1:
            return None # 1일차면 어제가 없음

        sql_journal = """
            SELECT summary_content, ai_feedback, praise_content
            FROM debug_journals
            WHERE session_id = %s AND target_date = %s
        """
        cursor.execute(sql_journal, (session_id, prev_day))
        return cursor.fetchone()
        
    finally:
        if conn: conn.close()

def init_user_curriculum(session_id: str, current_day: int):
    """오늘(current_day)에 해당하는 커리큘럼이 user_curriculum에 없으면 자동 배정"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # 오늘의 block_id, sort_order 계산
        block_id, sort_order = _day_to_block_and_sort(current_day)
        
        # 오늘치가 이미 배정되어 있는지 확인
        sql_check = """
            SELECT COUNT(*) as cnt 
            FROM user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            WHERE uc.session_id = %s AND mi.block_id = %s AND mi.sort_order = %s
        """
        cursor.execute(sql_check, (session_id, block_id, sort_order))
        result = cursor.fetchone()
        if result and result['cnt'] > 0:
            return  # 오늘치가 이미 있으면 패스
        
        # 없으면 오늘 해당하는 항목만 배정 (보통 1~2개: 각종 과목 + CERT/MCP)
        sql_insert = """
            INSERT INTO user_curriculum (session_id, item_id, category, weight_pct, status, sort_order)
            SELECT %s, item_id, category, weight_pct, 'NOT_STARTED', item_id
            FROM master_items
            WHERE block_id = %s AND sort_order = %s
        """
        cursor.execute(sql_insert, (session_id, block_id, sort_order))
        conn.commit()
        logger.info(f"[init_user_curriculum] {session_id}: Day{current_day}(block={block_id}, sort={sort_order}) {cursor.rowcount}件を配定しました")
    except Exception as e:
        logger.error(f"[init_user_curriculum] エラー: {e}")
    finally:
        if conn: conn.close()

def _day_to_block_and_sort(current_day: int):
    """글로벌 일차(1~120)를 block_id(7~12)와 sort_order(1~20)로 변환"""
    block_index = (current_day - 1) // 20  # 0~5
    sort_order = ((current_day - 1) % 20) + 1  # 1~20
    block_id = 7 + block_index  # 7~12
    return block_id, sort_order

def get_today_item_info(session_id: str, current_day: int):
    """오늘(N일차)의 커리큘럼 아이템 정보 조회 (user_curriculum + master_items)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        block_id, sort_order = _day_to_block_and_sort(current_day)
        
        sql = """
            SELECT uc.item_id, mi.name
            FROM user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            WHERE uc.session_id = %s
              AND mi.block_id = %s
              AND mi.sort_order = %s
            ORDER BY mi.item_id ASC
        """
        cursor.execute(sql, (session_id, block_id, sort_order))
        items = cursor.fetchall() 
        
        if not items:
            return None
        combined_name = ", ".join([item['name'] for item in items])
        
        return {
            "item_id": items[0]['item_id'], 
            "name": combined_name
        }
        
    finally:
        if conn: conn.close()

def get_learning_resources(item_id: int):
    """유튜브 리소스 조회 (C_DB)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT title, url, thumbnail_url
            FROM learning_resources
            WHERE item_id = %s AND resource_type = 'YOUTUBE'
        """
        cursor.execute(sql, (item_id,))
        return cursor.fetchall()
    finally:
        if conn: conn.close()

def get_quiz_problem(item_id: int, q_num: int = 1):
    """퀴즈 문제 조회 (C_DB)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # q_num 컬럼이 없다고 가정하고 LIMIT으로 처리 (첫번째 문제)
        offset = q_num - 1
        sql = """
            SELECT question_text, correct_answer, explanation
            FROM quiz_problems
            WHERE item_id = %s
            LIMIT 1 OFFSET %s
        """
        cursor.execute(sql, (item_id, offset))
        return cursor.fetchone()
    finally:
        if conn: conn.close()

def get_item_info_by_id(item_id: int):
    """item_id로 특정 아이템의 이름과 정보를 조회 (master_items)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = "SELECT name, item_id FROM master_items WHERE item_id = %s"
        cursor.execute(sql, (item_id,))
        return cursor.fetchone()
    finally:
        if conn: conn.close()

def get_daily_items_and_resources(session_id: str, item_id: int):
    """
    특정 item_id가 포함된 '오늘(Day)'의 모든 아이템과 리소스를 조회
    논리: item_id -> master_items에서 block_id, sort_order 찾기 -> 같은 날의 모든 아이템 -> 리소스 조회
    """
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        # 1. item_id로 block_id와 sort_order 찾기
        sql_info = """
            SELECT block_id, sort_order 
            FROM master_items 
            WHERE item_id = %s
        """
        cursor.execute(sql_info, (item_id,))
        info_row = cursor.fetchone()
        
        if not info_row:
            return None, [] 

        # 2. 같은 block_id + sort_order의 모든 아이템 (PJT+CERT)
        sql_items = """
            SELECT uc.item_id, mi.name
            FROM user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            WHERE uc.session_id = %s
              AND mi.block_id = %s
              AND mi.sort_order = %s
            ORDER BY mi.item_id ASC
        """
        cursor.execute(sql_items, (session_id, info_row['block_id'], info_row['sort_order']))
        daily_items = cursor.fetchall()
        item_ids = [item['item_id'] for item in daily_items]
        
        if not item_ids:
            return daily_items, []

        # 3. 리소스 조회
        format_strings = ','.join(['%s'] * len(item_ids))
        sql_resources = f"""
            SELECT resource_id, item_id, title, url, thumbnail_url
            FROM learning_resources
            WHERE item_id IN ({format_strings}) AND resource_type = 'YOUTUBE'
            ORDER BY sort_order ASC
        """
        cursor.execute(sql_resources, tuple(item_ids))
        resources = cursor.fetchall()

        return daily_items, resources

    finally:
        if conn: conn.close()

def get_ppt_resources(item_ids: list):
    """해당 item_id 목록에 대한 PPT 리소스를 learning_resources에서 조회"""
    if not item_ids:
        return []
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        format_strings = ','.join(['%s'] * len(item_ids))
        sql = f"""
            SELECT resource_id, item_id, title, url, thumbnail_url
            FROM learning_resources
            WHERE item_id IN ({format_strings}) AND resource_type = 'PPT'
            ORDER BY item_id ASC, sort_order ASC
        """
        cursor.execute(sql, tuple(item_ids))
        return cursor.fetchall()
    finally:
        if conn: conn.close()

def get_quiz_problem(item_id: int, q_num: int = 1):
    """퀴즈 문제 조회 (C_DB) + 마지막 문제 여부 확인"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # [추가] 1. 해당 아이템의 전체 문제 수 조회
        sql_count = "SELECT COUNT(*) as cnt FROM quiz_problems WHERE item_id = %s"
        cursor.execute(sql_count, (item_id,))
        count_result = cursor.fetchone()
        total_count = count_result['cnt'] if count_result else 0
        
        # 2. 문제 조회
        offset = q_num - 1
        sql = """
            SELECT problem_id, question_text, correct_answer, explanation
            FROM quiz_problems
            WHERE item_id = %s
            LIMIT 1 OFFSET %s
        """
        cursor.execute(sql, (item_id, offset))
        problem = cursor.fetchone()
        
        if problem:
            # [핵심] 현재 문제 번호가 전체 개수보다 크거나 같으면 마지막 문제
            # (예: 총 2문제 중 2번째 문제 요청 시 -> 2 >= 2 -> True)
            problem['is_last'] = (q_num >= total_count)
            
        return problem
    finally:
        if conn: conn.close()

def get_fe_problem_count():
    """FE 문제(Q_DB)의 총 개수 조회 (최대 2개로 제한)"""
    conn = None
    try:
        # Q_DB 연결 설정 가져오기
        config = get_db_config("Q_DB") 
        conn = mysql.connector.connect(**config)
        cursor = conn.cursor(dictionary=True)
        
        # 현재는 2개만 가져온다고 하셨으므로 LIMIT을 걸거나 전체 수를 셉니다.
        # 우선 데이터가 있는지 확인하는 용도입니다.
        sql = "SELECT COUNT(*) as cnt FROM section_1"
        cursor.execute(sql)
        result = cursor.fetchone()
        
        # 요청사항: "먼저 들어온 2개 문제만 가져올거야" -> 최대 2개로 고정
        total = result['cnt'] if result else 0
        return min(total, 2) 
        
    except Exception as e:
        logger.error(f"FE Count Error: {e}")
        return 0
    finally:
        if conn: conn.close()

def get_fe_problem_detail(q_num: int):
    """
    문제 번호(q_num)에 해당하는 문제(Q), 보기(Example)를 조회
    논리: Q_DB에서 문제 가져오기 -> 해당 question_number로 Example_DB에서 보기 가져오기
    """
    # 1. 문제(Q) 가져오기
    q_data = None
    conn_q = None
    try:
        conn_q = mysql.connector.connect(**get_db_config("Q_DB"))
        cursor = conn_q.cursor(dictionary=True)
        
        # q_num 번째 문제 가져오기 (1-based index)
        offset = q_num - 1
        sql_q = "SELECT question_number, Q FROM section_1 LIMIT 1 OFFSET %s"
        cursor.execute(sql_q, (offset,))
        q_data = cursor.fetchone()
    finally:
        if conn_q: conn_q.close()
        
    if not q_data:
        return None

    q_no = q_data['question_number']
    
    # 2. 보기(Choices) 가져오기
    choices = None
    conn_ex = None
    try:
        conn_ex = mysql.connector.connect(**get_db_config("Example_DB"))
        cursor = conn_ex.cursor(dictionary=True)
        
        sql_ex = "SELECT one, two, three, four FROM section_1 WHERE question_number = %s"
        cursor.execute(sql_ex, (q_no,))
        choices = cursor.fetchone()
    finally:
        if conn_ex: conn_ex.close()
        
    return {
        "question_number": q_no,
        "question_text": q_data['Q'],
        "choices": choices,
        "is_last": False # 라우터에서 처리
    }

def check_fe_answer_logic(question_number: int, user_answer: str):
    """
    정답 확인 및 해설 조회
    1. Correct_Answer_DB에서 정답(A) 조회 및 비교
    2. Solution_DB에서 해설(text/img) 조회
    """
    # 1. 정답 확인
    correct_data = None
    conn_ans = None
    try:
        conn_ans = mysql.connector.connect(**get_db_config("Correct_Answer_DB"))
        cursor = conn_ans.cursor(dictionary=True)
        
        sql_ans = "SELECT A FROM section_1 WHERE question_number = %s"
        cursor.execute(sql_ans, (question_number,))
        correct_data = cursor.fetchone()
    finally:
        if conn_ans: conn_ans.close()
        
    if not correct_data:
        return {"is_correct": False, "correct_answer": "Unknown", "explanation": "해설 데이터 없음", "explanation_type": "text"}

    real_answer = correct_data['A']
    # 대소문자 무시, 공백 제거 후 비교
    is_correct = (user_answer.strip().lower() == real_answer.strip().lower())

    # 2. 해설 조회
    sol_data = None
    conn_sol = None
    try:
        conn_sol = mysql.connector.connect(**get_db_config("Solution_DB"))
        cursor = conn_sol.cursor(dictionary=True)
        
        sql_sol = "SELECT text, img FROM section_1 WHERE question_number = %s"
        cursor.execute(sql_sol, (question_number,))
        sol_data = cursor.fetchone()
    finally:
        if conn_sol: conn_sol.close()
        
    explanation = "해설이 없습니다."
    explanation_type = "text"
    
    if sol_data:
        if sol_data.get('text'):
            explanation = sol_data['text']
            explanation_type = "text"
        elif sol_data.get('img'):
            explanation = sol_data['img']
            explanation_type = "img"

    return {
        "is_correct": is_correct,
        "correct_answer": real_answer,
        "explanation": explanation,
        "explanation_type": explanation_type
    }

# =========================================================
# cert_questions 기반 FE 퀴즈 (챕터별 동적 출제)
# =========================================================
def get_cert_question_count(chapter: int):
    """해당 챕터의 cert_questions 문제 수 조회"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        sql = "SELECT COUNT(*) as cnt FROM cert_questions WHERE chapter = %s"
        cursor.execute(sql, (chapter,))
        result = cursor.fetchone()
        return result['cnt'] if result else 0
    except Exception as e:
        logger.error(f"cert_question_count error: {e}")
        return 0
    finally:
        if conn: conn.close()

def get_cert_question_detail(chapter: int, q_num: int):
    """챕터 내 q_num번째 문제 조회 (cert_questions)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        offset = q_num - 1
        sql = """
            SELECT id, chapter, question_number, question_text, question_img,
                   option_1, option_2, option_3, option_4,
                   correct_answer, solution_text, solution_img
            FROM cert_questions
            WHERE chapter = %s
            ORDER BY id ASC
            LIMIT 1 OFFSET %s
        """
        cursor.execute(sql, (chapter, offset))
        row = cursor.fetchone()
        
        if not row:
            return None
        
        return {
            "question_number": row['question_number'],
            "question_text": row['question_text'],
            "question_img": row['question_img'],
            "choices": {
                "one": row['option_1'],
                "two": row['option_2'],
                "three": row['option_3'],
                "four": row['option_4']
            },
            "correct_answer": row['correct_answer'],
            "solution_text": row['solution_text'],
            "solution_img": row['solution_img'],
            "is_last": False
        }
    except Exception as e:
        logger.error(f"cert_question_detail error: {e}")
        return None
    finally:
        if conn: conn.close()

def check_cert_answer(chapter: int, question_number: str, user_answer: str, session_id: str = None):
    """cert_questions에서 정답 확인 + 해설 반환 + 로그 저장"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT id, correct_answer, solution_text, solution_img
            FROM cert_questions
            WHERE chapter = %s AND question_number = %s
            LIMIT 1
        """
        cursor.execute(sql, (chapter, question_number))
        row = cursor.fetchone()
        
        if not row:
            return {"is_correct": False, "correct_answer": "Unknown", "explanation": "해설 데이터 없음", "explanation_type": "text"}
        
        real_answer = row['correct_answer']
        is_correct = (user_answer.strip() == real_answer.strip())
        
        explanation = "解説がありません。"
        explanation_type = "text"
        
        if row.get('solution_img') and row['solution_img']:
            explanation = row['solution_img']
            explanation_type = "img"
        elif row.get('solution_text') and row['solution_text']:
            explanation = row['solution_text']
            explanation_type = "text"
        
        # 결과 저장 (cert_results 는 C_DB 에 있음)
        if session_id and row.get('id'):
            conn_c = None
            try:
                conn_c = mysql.connector.connect(**C_DB_CONFIG)
                cursor_c = conn_c.cursor()
                sql_insert = """
                    INSERT INTO cert_results (session_id, question_id, is_correct, user_answer)
                    VALUES (%s, %s, %s, %s)
                """
                cursor_c.execute(sql_insert, (session_id, row['id'], is_correct, user_answer))
                conn_c.commit()
            except Exception as e:
                logger.error(f"Save cert_results error: {e}")
            finally:
                if conn_c:
                    conn_c.close()

        return {
            "is_correct": is_correct,
            "correct_answer": real_answer,
            "explanation": explanation,
            "explanation_type": explanation_type
        }
    except Exception as e:
        logger.error(f"check_cert_answer error: {e}")
        return {"is_correct": False, "correct_answer": "Unknown", "explanation": "エラー", "explanation_type": "text"}
    finally:
        if conn: conn.close()

def save_debug_journal(session_id: str, current_day: int, summary: str, score: int, feedback: str, praise: str):
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        # ★ UNIQUE 제약 없이 동작하는 명시적 UPSERT:
        # 1) 같은 (session_id, target_date) 행이 있으면 UPDATE
        # 2) 없으면 INSERT
        cursor.execute(
            "SELECT journal_id FROM debug_journals WHERE session_id = %s AND target_date = %s LIMIT 1",
            (session_id, current_day)
        )
        existing = cursor.fetchone()

        if existing:
            # 기존 행 업데이트 (첫 번째 일지가 있어도 덮어씀)
            update_sql = """
                UPDATE debug_journals
                SET summary_content = %s,
                    understanding_score = %s,
                    ai_feedback = %s,
                    praise_content = %s
                WHERE journal_id = %s
            """
            cursor.execute(update_sql, (summary, score, feedback, praise, existing['journal_id']))
            logger.info(f"[Journal] UPDATED journal_id={existing['journal_id']} for session={session_id} day={current_day}")
        else:
            # 신규 INSERT
            insert_sql = """
                INSERT INTO debug_journals
                (session_id, target_date, summary_content, understanding_score, ai_feedback, praise_content, created_at)
                VALUES (%s, %s, %s, %s, %s, %s, NOW())
            """
            cursor.execute(insert_sql, (session_id, current_day, summary, score, feedback, praise))
            logger.info(f"[Journal] INSERTED new journal for session={session_id} day={current_day}")

        conn.commit()
        return True
    except Exception as e:
        logger.error(f"Save Journal Error: {e}")
        return False
    finally:
        if conn: conn.close()


def get_debug_journal(session_id: str, current_day: int):
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # 사용자님 의도대로 target_date = current_day 조건으로 조회
        sql = """
            SELECT summary_content, understanding_score, ai_feedback, praise_content, created_at
            FROM debug_journals
            WHERE session_id = %s AND target_date = %s
        """
        cursor.execute(sql, (session_id, current_day))
        return cursor.fetchone()
    finally:
        if conn: conn.close()

def get_all_debug_journals(session_id: str):
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT created_at, summary_content, ai_feedback, praise_content
            FROM debug_journals
            WHERE session_id = %s
            ORDER BY created_at DESC
        """
        cursor.execute(sql, (session_id,))
        return cursor.fetchall()
    finally:
        if conn: conn.close()

def update_progress_excel_and_pdf(session_id: str):
    import logging
    logger = logging.getLogger(__name__)
    
    # openpyxl 경고 무시
    import warnings
    warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl")
    
    # [필수] 스타일 모듈
    from openpyxl.styles import Alignment, PatternFill
    import openpyxl 
    import os
    import subprocess

    try:
        logger.info(f"🚀 [Start] Excel & PDF Update Process for Session: {session_id}")
        
        # 1. 경로 설정
        base_dir = PROGRESS_PDF_PATH
        template_file = os.path.join(base_dir, "GOALSKill_progress.xlsx") 
        user_file = os.path.join(base_dir, f"progress_{session_id}.xlsx") 
        
        # 2. 파일 로드
        if os.path.exists(user_file):
            target_path = user_file
        else:
            target_path = template_file

        wb = openpyxl.load_workbook(target_path)
        ws = wb.active 

        # =========================================================
        # [Step 1] 기존 조건부 서식 완전 초기화
        # =========================================================
        ws.conditional_formatting = type(ws.conditional_formatting)()
        logger.info("🧹 Cleared all existing conditional formatting rules.")

        # =========================================================
        # [Step 2] 스타일 객체 정의
        # =========================================================
        # 1. 채우기 스타일 (진한 초록색)
        green_fill = PatternFill(start_color="63C384", end_color="63C384", fill_type="solid")
        no_fill = PatternFill(fill_type=None)
        
        # 2. 텍스트 정렬 스타일 (세로 쓰기 + 정중앙 정렬)
        vertical_center_align = Alignment(
            horizontal='center',  # 가로 가운데
            vertical='center',    # 세로 가운데
            text_rotation=255,    # 세로 쓰기
            wrap_text=False       # [중요] 줄바꿈을 꺼야 정렬이 흐트러지지 않습니다.
        )

        # =========================================================
        # [Step 3] 업데이트 및 색칠 로직 함수
        # =========================================================
        def process_row_update(target_row_idx, increment_value):
            # --- 1. 값 업데이트 (Column X = 24번째) ---
            x_col_idx = 24
            cell_x = ws.cell(row=target_row_idx, column=x_col_idx)
            
            try:
                current_val = float(cell_x.value) if cell_x.value is not None else 0.0
            except ValueError:
                current_val = 0.0
            
            new_val = current_val + increment_value
            if new_val > 100.0: new_val = 100.0
                
            cell_x.value = new_val
            cell_x.number_format = ';;;' # 숨김 처리
            
            logger.info(f"✏️ Row {target_row_idx}: {current_val} + {increment_value} -> {new_val}")
            
            # --- 2. 색칠하기 (Column D~W = 4~23번째) ---
            header_row_idx = 3
            start_col = 4  
            end_col = 23   
            
            for col_idx in range(start_col, end_col + 1):
                # 기준값 읽기 (3행)
                header_cell = ws.cell(row=header_row_idx, column=col_idx)
                try:
                    threshold = float(header_cell.value) if header_cell.value is not None else 0.0
                except ValueError:
                    threshold = 0.0
                
                target_cell = ws.cell(row=target_row_idx, column=col_idx)
                
                # 기준값 이상이면 초록색
                if new_val >= threshold:
                    target_cell.fill = green_fill
                else:
                    target_cell.fill = no_fill

        # =========================================================
        # [Step 4] 실제 실행
        # =========================================================
        process_row_update(4, 16.7)  # 자격증 (Row 4)
        process_row_update(29, 1.67) # PJT (Row 29)

        # =========================================================
        # [Step 5] 타이틀 정렬 교정 (세로 쓰기 + 가운데 정렬)
        # =========================================================
        for merged_range in ws.merged_cells.ranges:
            min_col, min_row, max_col, max_row = merged_range.bounds
            
            # B열(Column 2)에 있는 병합 셀 확인
            if min_col == 2:
                # 병합 범위 내 모든 셀에 스타일 강제 적용
                for r in range(min_row, max_row + 1):
                    for c in range(min_col, max_col + 1):
                        cell = ws.cell(row=r, column=c)
                        cell.alignment = vertical_center_align
                
                logger.info(f"📐 Fixed Alignment (Center/Vertical) for: {merged_range}")

        # =========================================================
        # [Step 6] 저장 및 PDF 변환
        # =========================================================
        wb.save(user_file)
        wb.close()
        logger.info(f"💾 Excel Saved: {user_file}")
        
        # LibreOffice 변환
        cmd = [
            "libreoffice", 
            "--headless", 
            "--convert-to", "pdf", 
            "--outdir", base_dir, 
            user_file
        ]
        
        result = subprocess.run(cmd, capture_output=True, text=True)
        
        if result.returncode == 0:
            logger.info("✅ LibreOffice Conversion Success!")
            return True
        else:
            logger.error(f"❌ LibreOffice Failed: {result.stderr}")
            return False

    except Exception as e:
        logger.error(f"🔥 Critical Error: {e}")
        import traceback
        logger.error(traceback.format_exc())
        return False


# =========================================================
# カテゴリ別 学習進捗 (user_category_progress)
# =========================================================

def update_category_progress(session_id: str, current_day: int):
    """
    デバッグ日誌完了時にカテゴリ別の進捗率を再計算して保存する。

    ロジック（毎回ゼロから再計算 → 重複防止）:
    1. debug_journals から完了済みの全日次(target_date)を取得
    2. 各日次 → block_id + sort_order → user_curriculum + master_items
    3. カテゴリ別に weight_pct を合算
    4. Goalskill_DB.user_category_progress に UPSERT
    """
    conn_c = None
    conn_g = None
    try:
        # ── Step 1: 完了済み日次を取得 (C_DB) ──
        conn_c = mysql.connector.connect(**C_DB_CONFIG)
        cursor_c = conn_c.cursor(dictionary=True)

        cursor_c.execute(
            "SELECT DISTINCT target_date FROM debug_journals WHERE session_id = %s",
            (session_id,)
        )
        completed_days = [row['target_date'] for row in cursor_c.fetchall()]

        if not completed_days:
            logger.info(f"[CategoryProgress] No completed days for {session_id}")
            return

        # ── Step 2: 完了日次のアイテム → カテゴリ別 weight_pct 合算 ──
        category_pct = {}  # {'Python': 12.5, 'CERT': 8.0, ...}

        for day in completed_days:
            block_id, sort_order = _day_to_block_and_sort(day)

            sql = """
                SELECT mi.category, mi.weight_pct
                FROM user_curriculum uc
                JOIN master_items mi ON uc.item_id = mi.item_id
                WHERE uc.session_id = %s
                  AND mi.block_id = %s
                  AND mi.sort_order = %s
                  AND mi.weight_pct > 0
            """
            cursor_c.execute(sql, (session_id, block_id, sort_order))
            rows = cursor_c.fetchall()

            for row in rows:
                cat = row['category']
                pct = float(row['weight_pct'])
                category_pct[cat] = category_pct.get(cat, 0.0) + pct

        cursor_c.close()

        if not category_pct:
            logger.info(f"[CategoryProgress] No weight_pct items for {session_id}")
            return

        # ── Step 3: Goalskill_DB に UPSERT ──
        conn_g = mysql.connector.connect(**Goalskill_DB_CONFIG)
        cursor_g = conn_g.cursor()

        upsert_sql = """
            INSERT INTO user_category_progress (session_id, category, total_pct)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE
                total_pct = VALUES(total_pct)
        """
        rows_to_insert = [
            (session_id, cat, round(min(pct, 100.0), 1))
            for cat, pct in category_pct.items()
        ]
        cursor_g.executemany(upsert_sql, rows_to_insert)
        conn_g.commit()

        logger.info(
            f"[CategoryProgress] Updated {len(rows_to_insert)} categories for {session_id}: "
            + ", ".join([f"{cat}={pct}%" for cat, pct in category_pct.items()])
        )

    except Exception as e:
        logger.error(f"[CategoryProgress] Error: {e}")
    finally:
        if conn_c: conn_c.close()
        if conn_g: conn_g.close()


def get_category_progress(session_id: str) -> list:
    """
    ユーザーのカテゴリ別進捗率を取得する。
    master_items に存在する全カテゴリを基準にし、user_category_progress の値で
    上書きする。学習を開始していないカテゴリは 0% を返すことで、進捗モーダルで
    全カテゴリを常に表示できるようにする。
    Returns: [{'category': 'Python', 'total_pct': 25.5}, ...]
    """
    conn_c = None
    conn_g = None
    try:
        conn_c = mysql.connector.connect(**C_DB_CONFIG)
        cursor_c = conn_c.cursor(dictionary=True)
        cursor_c.execute(
            """
            SELECT DISTINCT category
            FROM master_items
            WHERE category IS NOT NULL AND category <> ''
            ORDER BY
                CASE WHEN category LIKE 'CERT\\_%' THEN 0 ELSE 1 END,
                FIELD(category,
                    'Python','JavaScript','MySQL','FastAPI',
                    '外部API連携','AWS','MCP','PJT企画','PJT設計','PJT開発','テスト'),
                category
            """
        )
        all_categories = [r['category'] for r in cursor_c.fetchall()]

        conn_g = mysql.connector.connect(**Goalskill_DB_CONFIG)
        cursor_g = conn_g.cursor(dictionary=True)
        cursor_g.execute(
            "SELECT category, total_pct FROM user_category_progress WHERE session_id = %s",
            (session_id,)
        )
        progress_map = {r['category']: r['total_pct'] for r in cursor_g.fetchall()}

        return [
            {"category": cat, "total_pct": progress_map.get(cat, 0.0)}
            for cat in all_categories
        ]
    except Exception as e:
        logger.error(f"[CategoryProgress] Get Error: {e}")
        return []
    finally:
        if conn_c: conn_c.close()
        if conn_g: conn_g.close()

def get_category_detail(session_id: str, group: str):
    """
    카테고리(자격증/PJT)별 학습 항목 상세 조회.
    user_curriculum.status에 'NOT_STARTED' 외의 값(완료일자 등)이 있으면 활성으로 판정.

    Args:
        session_id: 학습자 세션 ID
        group: 'CERT' 또는 'PJT'

    Returns:
        {
          "group": "CERT"|"PJT",
          "tabs": [
            { "tab_key": str, "tab_label": str,
              "items": [ { item_id, name, category, block_id, sort_order,
                           is_active, status_value } ] }
          ]
        }
    """
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        if group == 'CERT':
            cat_filter = "(mi.category = 'CERT' OR mi.category LIKE 'CERT\\_%')"
        else:
            cat_filter = (
                "mi.category IS NOT NULL "
                "AND mi.category <> 'CERT' "
                "AND mi.category NOT LIKE 'CERT\\_%'"
            )

        sql = f"""
            SELECT mi.item_id, mi.name, mi.category, mi.block_id, mi.sort_order,
                   mb.month_no, mb.block_title,
                   uc.status AS uc_status
            FROM master_items mi
            LEFT JOIN monthly_blocks mb ON mi.block_id = mb.block_id
            LEFT JOIN user_curriculum uc
                   ON uc.item_id = mi.item_id AND uc.session_id = %s
            WHERE {cat_filter}
            ORDER BY mi.block_id ASC, mi.sort_order ASC, mi.item_id ASC
        """
        cursor.execute(sql, (session_id,))
        rows = cursor.fetchall()

        tabs = {}
        for r in rows:
            status_value = r.get('uc_status')
            inactive_marks = (None, '', 'NOT_STARTED')
            is_active = status_value not in inactive_marks

            if group == 'CERT':
                cat_raw = r.get('category') or 'CERT'
                tab_key = cat_raw
                tab_label = cat_raw.replace('CERT_', '') if cat_raw.startswith('CERT_') else cat_raw
            else:
                tab_key = r.get('category') or 'その他'
                tab_label = tab_key

            if tab_key not in tabs:
                tabs[tab_key] = {
                    "tab_key": tab_key,
                    "tab_label": tab_label,
                    "items": []
                }

            tabs[tab_key]["items"].append({
                "item_id": r['item_id'],
                "name": r['name'],
                "category": r.get('category'),
                "block_id": r.get('block_id'),
                "sort_order": r.get('sort_order'),
                "is_active": is_active,
                "status_value": status_value if is_active else None
            })

        return {"group": group, "tabs": list(tabs.values())}
    except Exception as e:
        logger.error(f"[get_category_detail] Error: {e}")
        return {"group": group, "tabs": []}
    finally:
        if conn: conn.close()


def get_item_resources(session_id: str, item_id: int):
    """
    특정 item에 대한 학습 리소스 조회. user_curriculum.status가 활성(완료)된 항목만 반환.
    비활성/미완료 항목이면 None을 돌려준다(프론트에서 차단).
    """
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        cursor.execute(
            "SELECT status FROM user_curriculum WHERE session_id = %s AND item_id = %s LIMIT 1",
            (session_id, item_id)
        )
        row = cursor.fetchone()
        status_value = row['status'] if row else None
        inactive_marks = (None, '', 'NOT_STARTED')
        if status_value in inactive_marks:
            return None

        cursor.execute(
            "SELECT name, category FROM master_items WHERE item_id = %s LIMIT 1",
            (item_id,)
        )
        item_info = cursor.fetchone() or {}
        item_name = item_info.get('name')
        item_category = item_info.get('category')

        cursor.execute(
            """
            SELECT resource_id, title, url, resource_type, thumbnail_url, sort_order
            FROM learning_resources
            WHERE item_id = %s
            ORDER BY sort_order ASC, resource_id ASC
            """,
            (item_id,)
        )
        resources = cursor.fetchall()

        # PPT는 ファイル名만 저장되어 있으므로 viewer URL로 감싼다
        for r in resources:
            if (r.get('resource_type') or '').upper() == 'PPT':
                file_name = (r.get('url') or '').split('/')[-1]
                if file_name:
                    r['url'] = f"components/ppt_viewer.html?file={file_name}"

        quizzes = _get_item_quiz_history(session_id, item_id, item_name, item_category)

        return {
            "item_id": item_id,
            "name": item_name,
            "category": item_category,
            "status_value": status_value,
            "resources": resources,
            "quizzes": quizzes
        }
    except Exception as e:
        logger.error(f"[get_item_resources] Error: {e}")
        return None
    finally:
        if conn: conn.close()


def _get_item_quiz_history(session_id: str, item_id: int, item_name: str, item_category: str):
    """item 종류에 따라 CERT/IT 분기로 풀이 이력을 가져온다."""
    is_cert = bool(item_category) and (
        item_category == 'CERT' or item_category.startswith('CERT_')
    )
    if is_cert:
        return _get_cert_quiz_history(session_id, item_id, item_name)
    return _get_python_quiz_history(session_id, item_id)


def _block_and_sort_to_day(block_id: int, sort_order: int) -> int:
    """master_items.block_id + sort_order → 글로벌 일차(1~120) 역변환"""
    if block_id is None or sort_order is None:
        return 0
    return (int(block_id) - 7) * 20 + int(sort_order)


def _get_item_day(item_id: int):
    """item_id로 해당 아이템의 글로벌 일차를 조회"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        cursor.execute(
            "SELECT block_id, sort_order FROM master_items WHERE item_id = %s LIMIT 1",
            (item_id,)
        )
        row = cursor.fetchone()
        if not row:
            return None
        return _block_and_sort_to_day(row['block_id'], row['sort_order'])
    except Exception as e:
        logger.error(f"[_get_item_day] Error: {e}")
        return None
    finally:
        if conn: conn.close()


def _load_chat_quiz_messages(session_id: str, item_day: int, target_category: str):
    """chat_log_DB.chat_messages에서 해당 일차의 퀴즈 카테고리 로그를 가져온다."""
    if not item_day:
        return []
    from app.core.config import CHAT_LOG_DB_CONFIG
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        cursor.execute(
            """
            SELECT id, log, sender, daily, category, created_at
            FROM chat_messages
            WHERE session_id = %s AND category = %s AND daily = %s
            ORDER BY id ASC
            """,
            (session_id, target_category, item_day)
        )
        return cursor.fetchall()
    except Exception as e:
        logger.error(f"[_load_chat_quiz_messages] Error: {e}")
        return []
    finally:
        if conn: conn.close()


def _pair_chat_with_pool_python(messages, problems):
    """user/bot 메시지를 순서대로 quiz_problems(C_DB)와 페어링"""
    if not messages:
        return []
    pool_iter = iter(problems or [])
    results = []
    i = 0
    n = len(messages)
    while i < n:
        msg = messages[i]
        if msg['sender'] in ('user', 'M'):
            try:
                problem = next(pool_iter)
            except StopIteration:
                problem = None
            user_answer = msg.get('log') or ''
            bot_log = None
            bot_at = None
            if i + 1 < n and messages[i + 1]['sender'] in ('bot', 'I'):
                bot_log = messages[i + 1].get('log') or ''
                bot_at = messages[i + 1].get('created_at')
                i += 1
            is_correct = (
                bot_log
                and ('正解' in bot_log or 'さすが' in bot_log)
                and '不正解' not in bot_log
            )
            label_num = (problem.get('q_num') if problem else None) or (len(results) + 1)
            results.append({
                "quiz_type": "python",
                "ref_id": problem['problem_id'] if problem else msg['id'],
                "label": f"問題{label_num}",
                "question_text": problem.get('question_text') if problem else None,
                "question_img": None,
                "options": None,
                "correct_answer": problem.get('correct_answer') if problem else None,
                "explanation": (problem.get('explanation') if problem else None) or bot_log,
                "user_answer": user_answer,
                "is_correct": bool(is_correct),
                "solved_at": str(bot_at or msg.get('created_at') or ''),
                "from_chat_log": True,
            })
        i += 1
    return results


def _pair_chat_with_pool_cert(messages, questions, chapter):
    """user 메시지의 [資格問題 X-Y]를 cert_questions(C_DB)에서 매칭"""
    if not messages:
        return []
    questions_by_num = {q.get('question_number'): q for q in (questions or [])}

    def _img_url(path):
        if not path or chapter is None:
            return None
        fname = path.split('/')[-1]
        return f"/goal-skill-t/api/file/cert-img/section{chapter}/{fname}"

    def _option_value(value):
        """option_1~4 가 이미지 경로면 URL 로 변환, 텍스트면 그대로 반환"""
        if value is None:
            return None
        v = str(value).strip()
        if not v:
            return value
        lower = v.lower()
        is_img = lower.endswith(('.png', '.jpg', '.jpeg', '.gif', '.webp'))
        looks_like_path = v.startswith('/') or '\\' in v or '/img_q/' in v or '/img_explanation/' in v
        if is_img or looks_like_path:
            url = _img_url(v)
            return url if url else value
        return value

    results = []
    i = 0
    n = len(messages)
    while i < n:
        msg = messages[i]
        if msg['sender'] in ('user', 'M'):
            user_log = msg.get('log') or ''
            m_user = re.match(
                r'^\[資格問題\s+([0-9\-]+)\]\s*ユーザー回答[::]?\s*(.*)$',
                user_log
            )
            if m_user:
                q_num_str = m_user.group(1)
                user_answer = m_user.group(2).strip()
            else:
                q_num_str = str(len(results) + 1)
                user_answer = user_log

            bot_log = None
            bot_at = None
            if i + 1 < n and messages[i + 1]['sender'] in ('bot', 'I'):
                bot_log = messages[i + 1].get('log') or ''
                bot_at = messages[i + 1].get('created_at')
                i += 1

            is_correct = False
            parsed_correct = None
            if bot_log:
                m_bot = re.search(
                    r'正解[::]\s*([^\s\(]+)\s*\(判定[::]\s*(正解|不正解)\)',
                    bot_log
                )
                if m_bot:
                    parsed_correct = m_bot.group(1).strip()
                    is_correct = (m_bot.group(2) == '正解')
                else:
                    is_correct = ('正解' in bot_log) and ('不正解' not in bot_log)

            q = questions_by_num.get(q_num_str)
            if q:
                question_text = q.get('question_text')
                question_img = _img_url(q.get('question_img'))
                options = {
                    "ア": _option_value(q.get('option_1')),
                    "イ": _option_value(q.get('option_2')),
                    "ウ": _option_value(q.get('option_3')),
                    "エ": _option_value(q.get('option_4')),
                }
                correct_answer = parsed_correct or q.get('correct_answer')
                explanation = q.get('solution_text') or bot_log
                explanation_img = _img_url(q.get('solution_img'))
                ref_id = q['id']
            else:
                question_text = None
                question_img = None
                options = None
                correct_answer = parsed_correct
                explanation = bot_log
                explanation_img = None
                ref_id = msg['id']

            results.append({
                "quiz_type": "cert",
                "ref_id": ref_id,
                "label": f"問題{q_num_str}",
                "question_text": question_text,
                "question_img": question_img,
                "options": options,
                "correct_answer": correct_answer,
                "explanation": explanation,
                "explanation_img": explanation_img,
                "user_answer": user_answer,
                "is_correct": bool(is_correct),
                "solved_at": str(bot_at or msg.get('created_at') or ''),
                "from_chat_log": True,
            })
        i += 1
    return results


def _get_python_quiz_history(session_id: str, item_id: int):
    """C_DB.quiz_problems(問題プール) × chat_log_DB.chat_messages(category='quiz') 머지"""
    conn_c = None
    try:
        conn_c = mysql.connector.connect(**C_DB_CONFIG)
        cursor_c = conn_c.cursor(dictionary=True)
        cursor_c.execute(
            """
            SELECT problem_id, q_num, question_text, correct_answer, explanation
            FROM quiz_problems
            WHERE item_id = %s
            ORDER BY q_num ASC, problem_id ASC
            """,
            (item_id,)
        )
        problems = cursor_c.fetchall()

        day = _get_item_day(item_id)
        messages = _load_chat_quiz_messages(session_id, day, 'quiz') if day else []
        return _pair_chat_with_pool_python(messages, problems)
    except Exception as e:
        logger.error(f"[_get_python_quiz_history] Error: {e}")
        return []
    finally:
        if conn_c: conn_c.close()


def _get_cert_quiz_history(session_id: str, item_id: int, item_name: str):
    """C_DB.cert_questions(問題プール) × chat_log_DB.chat_messages(category='cert_quiz') 머지"""
    chapter = None
    if item_name:
        match = re.search(r'第(\d+)章', item_name)
        if match:
            chapter = int(match.group(1))

    conn_c = None
    try:
        questions = []
        if chapter is not None:
            conn_c = mysql.connector.connect(**C_DB_CONFIG)
            cursor_c = conn_c.cursor(dictionary=True)
            cursor_c.execute(
                """
                SELECT id, question_number, question_text, question_img,
                       option_1, option_2, option_3, option_4,
                       correct_answer, solution_text, solution_img
                FROM cert_questions
                WHERE chapter = %s
                ORDER BY id ASC
                """,
                (chapter,)
            )
            questions = cursor_c.fetchall()

        day = _get_item_day(item_id)
        messages = _load_chat_quiz_messages(session_id, day, 'cert_quiz') if day else []
        return _pair_chat_with_pool_cert(messages, questions, chapter)
    except Exception as e:
        logger.error(f"[_get_cert_quiz_history] Error: {e}")
        return []
    finally:
        if conn_c: conn_c.close()


def mark_curriculum_completed(session_id: str, current_day: int):
    """
    指定された日次(current_day)に該当する user_curriculum 行の status を
    本日の日付('YYYY-MM-DD')に更新する。デバッグ日誌書込み完了時に呼ぶ。
    既に日付が入っている場合は上書きしない(初回完了日を保持)。
    """
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor()

        block_id, sort_order = _day_to_block_and_sort(current_day)

        sql = """
            UPDATE user_curriculum uc
            JOIN master_items mi ON uc.item_id = mi.item_id
            SET uc.status = CAST(CURDATE() AS CHAR)
            WHERE uc.session_id = %s
              AND mi.block_id = %s
              AND mi.sort_order = %s
              AND (uc.status IS NULL OR uc.status = '' OR uc.status IN ('NOT_STARTED','IN_PROGRESS'))
        """
        cursor.execute(sql, (session_id, block_id, sort_order))
        conn.commit()
        logger.info(
            f"[mark_curriculum_completed] {session_id} day={current_day} "
            f"(block={block_id}, sort={sort_order}) {cursor.rowcount} 行を完了マーク"
        )
        return cursor.rowcount
    except Exception as e:
        logger.error(f"[mark_curriculum_completed] Error: {e}")
        return 0
    finally:
        if conn: conn.close()


def get_curriculum_plan(session_id: str):
    """
    유저의 커리큘럼 계획표(간트 차트용) 데이터를 가져옵니다.
    최우선: C_DB.user_curriculum
    차선: C_DB.master_items (user_curriculum에 데이터가 없을 경우)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # user_curriculum의 데이터가 불완전한 상태(과거 테스트 데이터 등)일 경우 차트가 깨지는 것을 방지하기 위해 
        # 현재는 항상 master_items의 정규 청사진을 가져오도록 처리합니다. (추후 user_curriculum이 완벽해지면 복구)
        sql_master = """
            SELECT category, MIN(block_id) as min_block, MAX(block_id) as max_block
            FROM master_items
            WHERE category IS NOT NULL
            GROUP BY category
        """
        cursor.execute(sql_master)
        rows = cursor.fetchall()
            
        categories = []
        for r in rows:
            if not r['category'] or r['min_block'] is None or r['max_block'] is None:
                continue
            categories.append({
                "category": r['category'],
                "is_cert": r['category'] == 'CERT' or str(r['category']).startswith('CERT_'),
                "start_month": int(r['min_block']) - 6,
                "end_month": int(r['max_block']) - 6
            })
            
        return categories
    except Exception as e:
        logger.error(f"[get_curriculum_plan] Error: {e}")
        return []
    finally:
        if conn: conn.close()