from typing import Optional, List, Dict
import mysql.connector
from app.core.config import CHAT_LOG_DB_CONFIG, logger
from app.models.goalskill_module import get_current_daily


# ==========================================
# chat_sessions CRUD
# ==========================================

def create_chat_session(session_id: str, category: str, title: Optional[str] = None, daily: Optional[int] = None) -> int:
    """
    새 채팅 세션 생성 → chat_session_id 반환
    """
    if daily is None:
        try:
            daily = get_current_daily(session_id)
        except Exception as e:
            logger.error(f"[chat_log] Error fetching daily: {e}")
            daily = 0

    if title is None:
        # 카테고리 한글명 매핑
        category_names = {
            'condition': 'コンディション',
            'journal': '振り返り日記',
            'curriculum': 'カリキュラム',
            'findgoal': '目標探し',
            'mbti': 'MBTI診断',
            'it': 'IT学習',
            'spi': 'SPI対策',
            'selfpr': '自己PR',
            'interview': '面接練習',
            'quiz': 'クイズ',
            'general': '一般会話',
            'summary': 'サマリー',
            'studyplan': '学習計画',
            'company_info': '会社情報',
        }
        cat_name = category_names.get(category, category)
        title = f"Day {daily} - {cat_name}"

    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor()
        sql = """
            INSERT INTO chat_sessions (session_id, title, category, daily)
            VALUES (%s, %s, %s, %s)
        """
        cursor.execute(sql, (session_id, title, category, daily))
        conn.commit()
        chat_session_id = cursor.lastrowid
        cursor.close()
        logger.info(f"[chat_log] Created session #{chat_session_id}: {title}")
        return chat_session_id
    except Exception as e:
        logger.error(f"[chat_log] create_chat_session Error: {e}")
        raise
    finally:
        if conn:
            conn.close()


def get_chat_sessions(session_id: str) -> List[Dict]:
    """
    유저의 채팅 세션 목록 조회 (최신순)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        sql = """
            SELECT id, session_id, title, category, daily, started_at, last_message_at
            FROM chat_sessions
            WHERE session_id = %s
            ORDER BY last_message_at DESC
        """
        cursor.execute(sql, (session_id,))
        rows = cursor.fetchall()
        cursor.close()
        return rows
    except Exception as e:
        logger.error(f"[chat_log] get_chat_sessions Error: {e}")
        return []
    finally:
        if conn:
            conn.close()


def get_session_messages(chat_session_id: int) -> List[Dict]:
    """
    특정 세션의 메시지 조회 (시간순)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        sql = """
            SELECT id, session_id, log, sender, daily, category, created_at
            FROM chat_messages
            WHERE chat_session_id = %s
            ORDER BY created_at ASC
        """
        cursor.execute(sql, (chat_session_id,))
        rows = cursor.fetchall()
        cursor.close()
        return rows
    except Exception as e:
        logger.error(f"[chat_log] get_session_messages Error: {e}")
        return []
    finally:
        if conn:
            conn.close()


def delete_chat_session(chat_session_id: int) -> bool:
    """
    세션 + 해당 메시지 삭제
    """
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor()
        # 메시지 먼저 삭제
        cursor.execute("DELETE FROM chat_messages WHERE chat_session_id = %s", (chat_session_id,))
        # 세션 삭제
        cursor.execute("DELETE FROM chat_sessions WHERE id = %s", (chat_session_id,))
        conn.commit()
        cursor.close()
        logger.info(f"[chat_log] Deleted session #{chat_session_id}")
        return True
    except Exception as e:
        logger.error(f"[chat_log] delete_chat_session Error: {e}")
        return False
    finally:
        if conn:
            conn.close()


def update_session_title(chat_session_id: int, title: str) -> bool:
    """
    세션 제목 업데이트
    """
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor()
        sql = "UPDATE chat_sessions SET title = %s WHERE id = %s"
        cursor.execute(sql, (title, chat_session_id))
        conn.commit()
        cursor.close()
        return True
    except Exception as e:
        logger.error(f"[chat_log] update_session_title Error: {e}")
        return False
    finally:
        if conn:
            conn.close()


# ==========================================
# chat_messages (기존 + chat_session_id 지원)
# ==========================================

def save_chat_log(session_id: str, log: str, sender: str, category: str,
                  daily: Optional[int] = None, chat_session_id: Optional[int] = None):
    """
    chat_messages 테이블에 대화 로그 저장
    - session_id: 유저 세션 ID
    - log: 대화 내용
    - sender: 발화자 (M=User, I=AI 등)
    - category: 카테고리 (condition, journal, curriculum, findgoal, mbti, it, spi 등)
    - daily: 일차 (optional, None이면 Goalskill_DB에서 가져옴)
    - chat_session_id: 소속 세션 ID (optional, 하위호환)
    """
    if daily is None:
        try:
            daily = get_current_daily(session_id)
            logger.info(f"[chat_log] Fetched current daily for {session_id}: {daily}")
        except Exception as e:
            logger.error(f"[chat_log] Error fetching daily: {e}")
            daily = 0

    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor()
        
        # 첫 시도: chat_session_id 컬럼을 포함하여 INSERT
        try:
            sql = """
                INSERT INTO chat_messages (session_id, log, sender, daily, category, chat_session_id)
                VALUES (%s, %s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (session_id, log, sender, daily, category, chat_session_id))
        except mysql.connector.Error as pe:
            # 에러 종류가 1054 Unknown column 인지 확인 후
            if "Unknown column" in str(pe) and "chat_session_id" in str(pe):
                # 구버전 테이블 대응: chat_session_id 없이 INSERT
                sql = """
                    INSERT INTO chat_messages (session_id, log, sender, daily, category)
                    VALUES (%s, %s, %s, %s, %s)
                """
                cursor.execute(sql, (session_id, log, sender, daily, category))
                chat_session_id = None # 업데이트 방지
            else:
                raise
                
        conn.commit()

        # 세션의 last_message_at 업데이트
        if chat_session_id:
            cursor2 = conn.cursor()
            cursor2.execute(
                "UPDATE chat_sessions SET last_message_at = NOW() WHERE id = %s",
                (chat_session_id,)
            )
            conn.commit()
            cursor2.close()

        cursor.close()
    except Exception as e:
        logger.error(f"[chat_log] save_chat_log Error: {e}")
        raise
    finally:
        if conn:
            conn.close()


def get_chat_logs(session_id: str, category: Optional[str] = None, daily: Optional[int] = None):
    """
    chat_messages 테이블에서 대화 로그 조회 (기존 호환)
    - session_id: 유저 세션 ID
    - category: 카테고리 필터 (optional)
    - daily: 일차 필터 (optional)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        sql = "SELECT id, session_id, log, sender, daily, category, created_at FROM chat_messages"
        conditions = ["session_id = %s"]
        params = [session_id]

        if category:
            conditions.append("category = %s")
            params.append(category)
        if daily is not None:
            conditions.append("daily = %s")
            params.append(daily)

        sql += " WHERE " + " AND ".join(conditions)
        sql += " ORDER BY created_at ASC"

        cursor.execute(sql, tuple(params))
        rows = cursor.fetchall()
        cursor.close()
        return rows
    except Exception as e:
        logger.error(f"[chat_log] get_chat_logs Error: {e}")
        return []
    finally:
        if conn:
            conn.close()