from typing import List, Dict
import mysql.connector
from app.core.config import B_DB_CONFIG
from app.core import logger
from datetime import datetime, timezone, timedelta

# ---------------------------------------------------------
# [Report/Chat 전용 DB 모듈]
# 대상 DB: B_DB (DAILY_DB_CONFIG 사용)
# ---------------------------------------------------------

# 1. 대화 내역(로그) 저장 (변경 없음)
def save_daily_log(session_id, sender, message):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        sql = "INSERT INTO daily_log (session_id, sender, message) VALUES (%s, %s, %s)"
        cursor.execute(sql, (session_id, sender, message))
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        logger.error(f"Daily Log Save Error: {e}")

# 2. 유저 일기(노트) 저장 -> daily_note 테이블 (통일)
def save_structured_diary(session_id, date, learning, mindset, ai_comment, daily=None):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        
        # boss_comment 컬럼이 있다면 NULL로 들어가거나 DB 기본값 사용
        sql = """
            INSERT INTO daily_note 
            (session_id, daily, date, learning, today_mindset, ai_comment) 
            VALUES (%s, %s, %s, %s, %s, %s)
        """
        
        cursor.execute(sql, (session_id, daily, date, learning, mindset, ai_comment))
        conn.commit()
        cursor.close()
        conn.close()
        logger.info(f"Daily Note Saved: {date} - {session_id} (day={daily})")
        
    except Exception as e:
        logger.error(f"Daily Note Save Error: {e}")
        

def get_daily_log_from_db(session_id):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        sql = "SELECT sender, message, created_at FROM daily_log WHERE session_id = %s ORDER BY id ASC"
        cursor.execute(sql, (session_id,))
        rows = cursor.fetchall()
        cursor.close()
        conn.close()
        return rows
    except Exception as e:
        logger.error(f"Daily Log Load Error: {e}")
        return []

def get_today_daily_log(session_id):
    """오늘의 daily_log만 가져오기 (A' 파트용: 과거 대화를 참조하지 않음)"""
    try:
        JST = timezone(timedelta(hours=9))
        today_str = datetime.now(JST).strftime("%Y-%m-%d")
        
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        sql = "SELECT sender, message, created_at FROM daily_log WHERE session_id = %s AND DATE(created_at) = %s ORDER BY id ASC"
        cursor.execute(sql, (session_id, today_str))
        rows = cursor.fetchall()
        cursor.close()
        conn.close()
        return rows
    except Exception as e:
        logger.error(f"Today Daily Log Load Error: {e}")
        return []
        
# 3. 모든 일기 조회 -> daily_note 테이블
# ★ 수정사항: ORDER BY date DESC, id DESC (최신순 정렬)
def get_all_daily_notes(session_id):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT * FROM daily_note 
            WHERE session_id = %s 
            ORDER BY date DESC, id DESC
        """
        cursor.execute(sql, (session_id,))
        results = cursor.fetchall()
        
        cursor.close()
        conn.close()
        return results
    except Exception as e:
        logger.error(f"All Daily Notes Load Error: {e}")
        return []
    
# 4. 가장 최근 데이터 1개 조회 -> daily_note 테이블
# ★ 수정사항: daily_note에서 가장 늦게 만들어진(최신) 데이터 가져오기
def get_yesterday_data(session_id):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT * FROM daily_note 
            WHERE session_id = %s 
            ORDER BY date DESC, id DESC 
            LIMIT 1
        """
        
        cursor.execute(sql, (session_id,))
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        return result
    except Exception as e:
        logger.error(f"Latest Data Load Error: {e}")
        return None

# [추가] 오늘 날짜의 데이터만 조회하는 함수
def get_today_data(session_id):
    try:
        JST = timezone(timedelta(hours=9))
        today_str = datetime.now(JST).strftime("%Y-%m-%d")
        
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = "SELECT * FROM daily_note WHERE session_id = %s AND date = %s ORDER BY id DESC LIMIT 1"
        cursor.execute(sql, (session_id, today_str))
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        return result
    except Exception as e:
        logger.error(f"Today Data Load Error: {e}")
        return None
    
# 5. AI 피드백 업데이트 -> daily_note 테이블
def update_yesterday_ai_feedback(session_id, ai_comment, advice):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        
        # id 기준 업데이트
        sql = "UPDATE daily_note SET ai_comment = %s, advice = %s WHERE id = %s"
        
        cursor.execute(sql, (ai_comment, advice, session_id))
        conn.commit()
        
        cursor.close()
        conn.close()
        logger.info(f"Updated AI Feedback for ID: {session_id}")
        
    except Exception as e:
        logger.error(f"AI Feedback Update Error: {e}")

# 6. 상사(Boss) 코멘트 업데이트 -> daily_note 테이블
def update_boss_comment(session_id, date, boss_comment):
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        
        # session_id와 date가 일치하는 행 업데이트
        sql = "UPDATE daily_note SET boss_comment = %s WHERE session_id = %s AND date = %s"
        
        cursor.execute(sql, (boss_comment, session_id, date))
        conn.commit()
        
        row_count = cursor.rowcount
        cursor.close()
        conn.close()
        
        logger.info(f"Updated Boss Comment for {session_id} on {date}. Affected: {row_count}")
        return row_count > 0
        
    except Exception as e:
        logger.error(f"Boss Comment Update Error: {e}")
        return False

# 7. 오늘 작성 여부 확인 (변경 없음, 테이블명만 daily_note 확인)
def check_today_daily_log_exists(session_id: str) -> bool:
    conn = None
    try:
        JST = timezone(timedelta(hours=9))
        now = datetime.now(JST)
        today_str = now.strftime("%Y-%m-%d")
        
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = "SELECT id FROM daily_note WHERE session_id = %s AND date = %s LIMIT 1"
        
        cursor.execute(sql, (session_id, today_str))
        row = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        return row is not None
        
    except Exception as e:
        logger.error(f"Check Today Log Error: {e}")
        if conn and conn.is_connected():
            conn.close()
        return False

# 8. 특정 Day의 일지가 존재하는지 확인 (같은 날 연속 학습 판별용)
def check_daily_note_for_day(session_id: str, daily: int) -> bool:
    """daily_note에 해당 day 번호의 레코드가 있는지 확인"""
    conn = None
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        sql = "SELECT id FROM daily_note WHERE session_id = %s AND daily = %s LIMIT 1"
        cursor.execute(sql, (session_id, daily))
        row = cursor.fetchone()
        cursor.close()
        conn.close()
        return row is not None
    except Exception as e:
        logger.error(f"Check Daily Note For Day Error: {e}")
        if conn and conn.is_connected():
            conn.close()
        return False

# 9. 이전 Day 일지의 작성 날짜 조회 (연속 학습 감지용)
def get_daily_note_date_for_day(session_id: str, daily: int):
    """daily_note에서 해당 day의 작성 날짜(date 컨럼)를 반환"""
    conn = None
    try:
        conn = mysql.connector.connect(**B_DB_CONFIG)
        cursor = conn.cursor()
        sql = "SELECT date FROM daily_note WHERE session_id = %s AND daily = %s ORDER BY id DESC LIMIT 1"
        cursor.execute(sql, (session_id, daily))
        row = cursor.fetchone()
        cursor.close()
        conn.close()
        return row[0] if row else None
    except Exception as e:
        logger.error(f"Get Daily Note Date For Day Error: {e}")
        if conn and conn.is_connected():
            conn.close()
        return None
        

def get_daily_quiz_logs(session_id: str) -> str:
    """Fetches quiz and cert results for the session solved today to understand user's struggle."""
    from app.core.config import A_DB_CONFIG
    import mysql.connector
    conn = None
    quiz_str = []
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        # Python quiz results
        sql_quiz = """
            SELECT q.question_text, r.user_answer, r.is_correct 
            FROM quiz_results r 
            JOIN quiz_problems q ON r.problem_id = q.problem_id 
            WHERE r.session_id = %s AND DATE(r.solved_at) = CURDATE()
            ORDER BY r.solved_at DESC
            LIMIT 10
        """
        cursor.execute(sql_quiz, (session_id,))
        rows_quiz = cursor.fetchall()
        for r in rows_quiz:
            q_text = str(r['question_text']).replace('\n', ' ')[:100]
            corr = "O" if r['is_correct'] else "X"
            quiz_str.append(f"[Python Quiz] Q: {q_text}... | Ans: {r['user_answer']} | Result: {corr}")

        # Cert question results
        sql_cert = """
            SELECT q.question_text, r.user_answer, r.is_correct 
            FROM cert_results r 
            JOIN cert_questions q ON r.question_id = q.id 
            WHERE r.session_id = %s AND DATE(r.solved_at) = CURDATE()
            ORDER BY r.solved_at DESC
            LIMIT 10
        """
        cursor.execute(sql_cert, (session_id,))
        rows_cert = cursor.fetchall()
        for r in rows_cert:
            q_text = str(r['question_text']).replace('\n', ' ')[:100]
            corr = "O" if r['is_correct'] else "X"
            quiz_str.append(f"[Cert Quiz] Q: {q_text}... | Ans: {r['user_answer']} | Result: {corr}")

        cursor.close()
        conn.close()
        
        if not quiz_str:
            return "クイズ履歴なし"
        return "\n".join(quiz_str)
        
    except Exception as e:
        logger.error(f"Get Quiz Logs Error: {e}")
        if conn and conn.is_connected():
            conn.close()
        return "クイズ履歴取得失敗"