# ============================================================================
# 인증 모듈 - Goalskill_login DB 연동
# ============================================================================

import mysql.connector
import bcrypt
import uuid
import logging
from datetime import datetime
from app.core.config import get_db_config
from app.models.goalskill_module import create_result_row

logger = logging.getLogger(__name__)

LOGIN_DB = "Goalskill_login"


def _get_login_db():
    """Goalskill_login DB 연결"""
    config = get_db_config(LOGIN_DB)
    return mysql.connector.connect(**config)


# --------------------------------------------------------------------------
# 회원가입
# --------------------------------------------------------------------------
def create_user(user_id: str, password: str) -> dict:
    """
    새 유저를 등록합니다.
    Returns: {"success": bool, "session_id": str|None, "message": str}
    """
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        # 1) 유저 ID 중복 확인
        cursor.execute("SELECT id FROM login WHERE user_id = %s", (user_id,))
        if cursor.fetchone():
            return {"success": False, "session_id": None, "message": "すでに存在するIDです。"}

        # 2) 비밀번호 해싱
        password_hash = bcrypt.hashpw(password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")

        # 3) 세션 ID 생성
        session_id = f"sess_{uuid.uuid4().hex[:16]}{int(datetime.now().timestamp())}"

        # 4) INSERT
        cursor.execute(
            """
            INSERT INTO login (user_id, password_hash, session_id, created_at)
            VALUES (%s, %s, %s, NOW())
            """,
            (user_id, password_hash, session_id),
        )
        conn.commit()

        logger.info(f"[Auth] User registered: {user_id}, session: {session_id}")

        # 5) 커리큘럼 자동 생성 (master_items → user_curriculum)
        _create_user_curriculum(session_id)
        # 6) result 테이블에 daily=1 행 생성 (첫 방문)
        create_result_row(session_id, 1)
        # 7) カテゴリ別進捗の初期行を作成 (12カテゴリ × total_pct=0.0)
        _init_category_progress(session_id)

        return {"success": True, "session_id": session_id, "message": "会員登録成功！"}

    except Exception as e:
        conn.rollback()
        logger.error(f"[Auth] Registration error: {e}")
        return {"success": False, "session_id": None, "message": f"会員登録失敗: {str(e)}"}
    finally:
        cursor.close()
        conn.close()


def _create_user_curriculum(session_id: str):
    """master_items(C_DB)의 커리큘럼을 user_curriculum에 복사합니다."""
    config = get_db_config("C_DB")
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            INSERT INTO user_curriculum (session_id, item_id, block_id, sort_order, status, category, weight_pct)
            SELECT %s, item_id, block_id, sort_order, 'NOT_STARTED', category, weight_pct
            FROM master_items
            ORDER BY block_id, sort_order, item_id
            """,
            (session_id,),
        )
        conn.commit()
        count = cursor.rowcount
        logger.info(f"[Auth] Curriculum created: session={session_id}, items={count}")
    except Exception as e:
        conn.rollback()
        logger.error(f"[Auth] Curriculum creation error: {e}")
    finally:
        cursor.close()
        conn.close()


# 33カテゴリの固定リスト (CERT 22章分 + IT 11カテゴリ)
_ALL_CATEGORIES = [
    'CERT_第1章', 'CERT_第2章', 'CERT_第3章', 'CERT_第4章', 'CERT_第5章', 
    'CERT_第6章', 'CERT_第7章', 'CERT_第8章', 'CERT_第9章', 'CERT_第10章', 
    'CERT_第11章', 'CERT_第12章', 'CERT_第13章', 'CERT_第14章', 'CERT_第15章', 
    'CERT_第16章', 'CERT_第17章', 'CERT_第18章', 'CERT_第19章', 'CERT_第20章', 
    'CERT_第21章', 'CERT_第22章',
    'Python', 'JavaScript', 'MySQL', 'FastAPI',
    '外部API連携', 'AWS', 'MCP', 'PJT企画', 'PJT設計', 'PJT開発', 'テスト'
]

def _init_category_progress(session_id: str):
    """회원 가입 시 카테고리별 진행 상황의 초기 행(12행, total%).을 생성합니다."""
    config = get_db_config("Goalskill_DB")
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()
    try:
        sql = """
            INSERT INTO user_category_progress (session_id, category, total_pct)
            VALUES (%s, %s, 0.0)
            ON DUPLICATE KEY UPDATE total_pct = total_pct
        """
        rows = [(session_id, cat) for cat in _ALL_CATEGORIES]
        cursor.executemany(sql, rows)
        conn.commit()
        logger.info(f"[Auth] Category progress initialized: session={session_id}, categories={len(rows)}")
    except Exception as e:
        conn.rollback()
        logger.error(f"[Auth] Category progress init error: {e}")
    finally:
        cursor.close()
        conn.close()


# --------------------------------------------------------------------------
# 로그인
# --------------------------------------------------------------------------
def authenticate_user(user_id: str, password: str) -> dict:
    """
    유저 인증을 수행합니다.
    Returns: {"success": bool, "session_id": str|None, "message": str}
    """
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(
            "SELECT id, password_hash, session_id FROM login WHERE user_id = %s",
            (user_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": "パスワードが一致しません。"}

        # 최근 로그인 시간 업데이트
        cursor.execute(
            "UPDATE login SET last_login = NOW() WHERE id = %s",
            (user["id"],),
        )
        conn.commit()

        logger.info(f"[Auth] User logged in: {user_id}")
        return {"success": True, "session_id": user["session_id"], "message": "ログイン成功！"}

    except Exception as e:
        logger.error(f"[Auth] Login error: {e}")
        return {"success": False, "session_id": None, "message": f"ログイン失敗: {str(e)}"}
    finally:
        cursor.close()
        conn.close()


# --------------------------------------------------------------------------
# 세션 검증
# --------------------------------------------------------------------------
def get_user_by_session(session_id: str) -> dict | None:
    """세션 ID로 유저 정보를 조회합니다."""
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(
            "SELECT id, user_id, session_id, created_at, last_login FROM login WHERE session_id = %s",
            (session_id,),
        )
        return cursor.fetchone()
    except Exception as e:
        logger.error(f"[Auth] Session lookup error: {e}")
        return None
    finally:
        cursor.close()
        conn.close()


# --------------------------------------------------------------------------
# 학습 진행도
# --------------------------------------------------------------------------
def get_study_progress(session_id: str) -> list:
    """유저의 학습 진행도 목록을 조회합니다."""
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(
            """
            SELECT id, session_id, item_id, status, score, updated_at
            FROM user_study_progress
            WHERE session_id = %s
            ORDER BY item_id
            """,
            (session_id,),
        )
        return cursor.fetchall()
    except Exception as e:
        logger.error(f"[Auth] Progress fetch error: {e}")
        return []
    finally:
        cursor.close()
        conn.close()


def update_study_progress(session_id: str, item_id: int, status: str, score: int = 0) -> dict:
    """학습 진행도를 업데이트합니다 (UPSERT)."""
    conn = _get_login_db()
    cursor = conn.cursor(dictionary=True)
    try:
        cursor.execute(
            """
            INSERT INTO user_study_progress (session_id, item_id, status, score)
            VALUES (%s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                status = VALUES(status),
                score = VALUES(score),
                updated_at = NOW()
            """,
            (session_id, item_id, status, score),
        )
        conn.commit()
        logger.info(f"[Auth] Progress updated: session={session_id}, item={item_id}, status={status}")
        return {"success": True, "message": "進捗更新完了"}
    except Exception as e:
        conn.rollback()
        logger.error(f"[Auth] Progress update error: {e}")
        return {"success": False, "message": f"更新失敗: {str(e)}"}
    finally:
        cursor.close()
        conn.close()