"""
study_note CRUD – 학습 노트 모듈
study_note 테이블은 chat_log_DB 에 생성한다.
"""

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


# ==========================================
# 테이블 자동 생성 (앱 시작 시 1회)
# ==========================================

def ensure_study_note_table():
    """study_note 테이블이 없으면 생성"""
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS study_note (
                id            INT AUTO_INCREMENT PRIMARY KEY,
                session_id    VARCHAR(100) NOT NULL,
                daily         INT DEFAULT 0,
                title         VARCHAR(200) DEFAULT '',
                content       TEXT,
                source_message TEXT,
                created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_session_daily (session_id, daily)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        conn.commit()
        cursor.close()
        logger.info("[note] study_note table ready")
    except Exception as e:
        logger.error(f"[note] ensure_study_note_table Error: {e}")
    finally:
        if conn:
            conn.close()


# ==========================================
# CRUD
# ==========================================

def create_study_note(session_id: str, title: str, content: str,
                      source_message: Optional[str] = None,
                      daily: Optional[int] = None) -> int:
    """새 노트 생성 → note_id 반환"""
    if daily is None:
        try:
            daily = get_current_daily(session_id)
        except Exception:
            daily = 0

    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor()
        sql = """
            INSERT INTO study_note (session_id, daily, title, content, source_message)
            VALUES (%s, %s, %s, %s, %s)
        """
        cursor.execute(sql, (session_id, daily, title, content, source_message))
        conn.commit()
        note_id = cursor.lastrowid
        cursor.close()
        logger.info(f"[note] Created note #{note_id} for {session_id}")
        return note_id
    except Exception as e:
        logger.error(f"[note] create_study_note Error: {e}")
        raise
    finally:
        if conn:
            conn.close()


def get_study_notes(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, daily, title, content, source_message, created_at, updated_at
            FROM study_note
            WHERE session_id = %s
            ORDER BY updated_at DESC
        """
        cursor.execute(sql, (session_id,))
        rows = cursor.fetchall()
        cursor.close()
        return rows
    except Exception as e:
        logger.error(f"[note] get_study_notes Error: {e}")
        return []
    finally:
        if conn:
            conn.close()


def get_study_note(note_id: int) -> Optional[Dict]:
    """단일 노트 조회"""
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        sql = "SELECT * FROM study_note WHERE id = %s"
        cursor.execute(sql, (note_id,))
        row = cursor.fetchone()
        cursor.close()
        return row
    except Exception as e:
        logger.error(f"[note] get_study_note Error: {e}")
        return None
    finally:
        if conn:
            conn.close()


def update_study_note(note_id: int, title: Optional[str] = None,
                      content: Optional[str] = None) -> bool:
    """노트 수정"""
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor()

        fields = []
        params = []
        if title is not None:
            fields.append("title = %s")
            params.append(title)
        if content is not None:
            fields.append("content = %s")
            params.append(content)

        if not fields:
            return False

        sql = f"UPDATE study_note SET {', '.join(fields)} WHERE id = %s"
        params.append(note_id)
        cursor.execute(sql, tuple(params))
        conn.commit()
        cursor.close()
        logger.info(f"[note] Updated note #{note_id}")
        return True
    except Exception as e:
        logger.error(f"[note] update_study_note Error: {e}")
        return False
    finally:
        if conn:
            conn.close()


def delete_study_note(note_id: int) -> bool:
    """노트 삭제"""
    conn = None
    try:
        conn = mysql.connector.connect(**CHAT_LOG_DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("DELETE FROM study_note WHERE id = %s", (note_id,))
        conn.commit()
        affected = cursor.rowcount
        cursor.close()
        logger.info(f"[note] Deleted note #{note_id}")
        return affected > 0
    except Exception as e:
        logger.error(f"[note] delete_study_note Error: {e}")
        return False
    finally:
        if conn:
            conn.close()