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")

# =========================================================
# 이론 서브챕터 범위 매핑 (일차별 분할)
# 같은 章이 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'
            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'
              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 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):
    """cert_questions에서 정답 확인 + 해설 반환"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT 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"
        
        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()
        
        # 중복 방지 (UPSERT): 이미 같은 날짜의 일지가 있으면 내용을 업데이트
        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())
            ON DUPLICATE KEY UPDATE
                summary_content = VALUES(summary_content),
                understanding_score = VALUES(understanding_score),
                ai_feedback = VALUES(ai_feedback),
                praise_content = VALUES(praise_content)
        """
        cursor.execute(sql, (session_id, current_day, summary, score, feedback, praise))
        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:
    """
    ユーザーのカテゴリ別進捗率を取得する。
    Returns: [{'category': 'Python', 'total_pct': 25.5}, ...]
    """
    conn = None
    try:
        conn = mysql.connector.connect(**Goalskill_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        sql = """
            SELECT category, total_pct
            FROM user_category_progress
            WHERE session_id = %s
            ORDER BY FIELD(category,
                'CERT','Python','JavaScript','MySQL','FastAPI',
                '外部API連携','AWS','MCP','PJT企画','PJT設計','PJT開発','テスト')
        """
        cursor.execute(sql, (session_id,))
        return cursor.fetchall()
    except Exception as e:
        logger.error(f"[CategoryProgress] Get Error: {e}")
        return []
    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',
                "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()