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

# C_DB 설정 가져오기
C_DB_CONFIG = get_db_config("C_DB")
Goalskill_login_DB_CONFIG = get_db_config("Goalskill_login")
def get_user_progress(session_id: str):
    """유저의 현재 진도와 상태 확인 (Goalskill_login)"""
    conn = None
    try:
        conn = mysql.connector.connect(**Goalskill_login_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT current_day, status, created_at
            FROM user_study_progress
            WHERE session_id = %s
            ORDER BY current_day DESC
            LIMIT 1
        """
        cursor.execute(sql, (session_id,))
        return cursor.fetchone()
    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 get_today_item_info(session_id: str, current_day: int):
    """오늘(N일차)의 커리큘럼 아이템 정보 조회 (C_DB)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT C.item_id, C.name
            FROM daily_schedules D
            JOIN curriculum_items C ON D.item_id = C.item_id
            WHERE D.session_id = %s AND D.target_date = %s
            ORDER BY C.sort_order ASC
        """
        cursor.execute(sql, (session_id, current_day))
        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로 특정 아이템의 이름과 정보를 조회 (C_DB)"""
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = "SELECT name, item_id FROM curriculum_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 -> target_date 찾기 -> 해당 date의 모든 item_id 찾기 -> 리소스 조회
    """
    conn = None
    try:
        conn = mysql.connector.connect(**C_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        sql_date = """
            SELECT target_date 
            FROM daily_schedules 
            WHERE session_id = %s AND item_id = %s
        """
        cursor.execute(sql_date, (session_id, item_id))
        date_row = cursor.fetchone()
        
        if not date_row:
            return None, [] 

        target_date = date_row['target_date']
        sql_items = """
            SELECT C.item_id, C.name
            FROM daily_schedules D
            JOIN curriculum_items C ON D.item_id = C.item_id
            WHERE D.session_id = %s AND D.target_date = %s
            ORDER BY C.sort_order ASC
        """
        cursor.execute(sql_items, (session_id, target_date))
        daily_items = cursor.fetchall()
        item_ids = [item['item_id'] for item in daily_items]
        
        if not item_ids:
            return daily_items, []
        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_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
    }

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 = "/home/air/goalskill_t/front/progress_PDF/"
        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