import mysql.connector
import bcrypt
import uuid
from datetime import datetime, timezone, timedelta
from app.core.config import get_db_config, B_DB_CONFIG
from app.core import logger

LOGIN_DB = "Goalskill_login"

def _get_login_db():
    config = get_db_config(LOGIN_DB)
    return mysql.connector.connect(**config)

def _get_daily_db():
    return mysql.connector.connect(**B_DB_CONFIG)

def authenticate_supervisor(supervisor_id: str, password: str) -> dict:
    """관리자 로그인 인증 및 토큰 발급"""
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("SELECT id, supervisor_id, password_hash FROM supervisor_users WHERE supervisor_id = %s", (supervisor_id,))
        user = cursor.fetchone()

        if not user:
            return {"success": False, "session_id": None, "message": "存在しないIDです。"}

        if not bcrypt.checkpw(password.encode("utf-8"), user["password_hash"].encode("utf-8")):
            return {"success": False, "session_id": None, "message": "パスワードが一致しません。"}

        # 토큰 발급
        session_id = f"sup_{uuid.uuid4().hex[:16]}{int(datetime.now().timestamp())}"
        
        cursor.execute("UPDATE supervisor_users SET session_id = %s WHERE id = %s", (session_id, user["id"]))
        conn.commit()

        logger.info(f"[Supervisor Auth] Logged in: {supervisor_id}")
        return {"success": True, "session_id": session_id, "message": "ログイン成功！"}

    except Exception as e:
        logger.error(f"[Supervisor Auth] Error: {e}")
        return {"success": False, "session_id": None, "message": "アカウントなし"}
    finally:
        cursor.close()
        conn.close()

def get_supervisor_by_session(session_id: str) -> dict | None:
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("SELECT id, supervisor_id FROM supervisor_users WHERE session_id = %s", (session_id,))
        return cursor.fetchone()
    except Exception as e:
        logger.error(f"[Supervisor] session lookup error: {e}")
        return None
    finally:
        cursor.close()
        conn.close()

def get_mapped_learners(supervisor_id: str):
    """자신에게 매핑된 학습자 세션 목록과 최신 진도 가져오기"""
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        # 로그인 DB에서 매핑된 learner 정보
        cursor.execute("""
            SELECT m.learner_session_id, l.user_id as learner_name
            FROM supervisor_learner_mapping m
            LEFT JOIN login l ON m.learner_session_id COLLATE utf8mb4_unicode_ci = l.session_id COLLATE utf8mb4_unicode_ci
            WHERE m.supervisor_id = %s
        """, (supervisor_id,))
        learners = cursor.fetchall()
    except Exception as e:
        logger.error(f"[Supervisor DB] Error fetching mapping: {e}")
        return []
    finally:
        cursor.close()
        conn.close()
        
    if not learners:
        return []

    # 이제 B_DB에 가서 해당 learner_session_id 들의 상태를 요약한다.
    daily_conn = _get_daily_db()
    daily_cursor = daily_conn.cursor(dictionary=True)
    try:
        results = []
        for l in learners:
            session_id = l['learner_session_id']
            # 최신 일지 1개와 전체 일지 갯수, 미평가(Boss_comment = NULL) 갯수를 가져온다.
            # 미평가 갯수:
            daily_cursor.execute("SELECT COUNT(*) as pending_count FROM daily_note WHERE session_id = %s AND (boss_comment IS NULL OR boss_comment = '')", (session_id,))
            pending_count = daily_cursor.fetchone()['pending_count']
            
            # 최신 기록
            daily_cursor.execute("SELECT date FROM daily_note WHERE session_id = %s ORDER BY date DESC, id DESC LIMIT 1", (session_id,))
            last_record = daily_cursor.fetchone()
            
            results.append({
                "learner_session_id": session_id,
                "learner_name": l['learner_name'] or session_id,
                "pending_count": pending_count,
                "last_active_date": last_record['date'] if last_record else "No Record"
            })
        return results
    except Exception as e:
        logger.error(f"[Supervisor DB] Error fetching daily_note stats: {e}")
        return []
    finally:
        daily_cursor.close()
        daily_conn.close()

def get_learner_daily_notes(supervisor_id: str, learner_session_id: str):
    """매핑이 올바른지 확인 후 학습자의 일지 반환"""
    # 권한 체크
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("SELECT id FROM supervisor_learner_mapping WHERE supervisor_id = %s AND learner_session_id = %s", (supervisor_id, learner_session_id))
        if not cursor.fetchone():
            return None # 권한 없음
    finally:
        cursor.close()
        conn.close()
        
    # 데이터 로드
    daily_conn = _get_daily_db()
    daily_cursor = daily_conn.cursor(dictionary=True)
    try:
        sql = "SELECT id, session_id, date, learning, today_mindset, ai_comment, boss_comment FROM daily_note WHERE session_id = %s ORDER BY date DESC, id DESC"
        daily_cursor.execute(sql, (learner_session_id,))
        return daily_cursor.fetchall()
    finally:
        daily_cursor.close()
        daily_conn.close()

def update_boss_comment_for_learner(supervisor_id: str, learner_session_id: str, date: str, comment: str):
    # 권한 체크
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute("SELECT id FROM supervisor_learner_mapping WHERE supervisor_id = %s AND learner_session_id = %s", (supervisor_id, learner_session_id))
        if not cursor.fetchone():
            return {"success": False, "message": "担当していない学習者です。"}
    finally:
        cursor.close()
        conn.close()

    daily_conn = _get_daily_db()
    daily_cursor = daily_conn.cursor()
    try:
        sql = "UPDATE daily_note SET boss_comment = %s WHERE session_id = %s AND date = %s"
        daily_cursor.execute(sql, (comment, learner_session_id, date))
        daily_conn.commit()
        return {"success": True, "affected": daily_cursor.rowcount}
    except Exception as e:
        return {"success": False, "message": str(e)}
    finally:
        daily_cursor.close()
        daily_conn.close()