"""
==============================================================================
Goalskill DB 모듈 - Goalskill_DB CRUD 레이어
==============================================================================
48개 테이블({H|S}{A|B|C|D}_{T|P|R}{I|M})에 대한 동적 INSERT/SELECT 함수.
"""

import mysql.connector
from app.core.config import get_db_config, logger

# Goalskill_DB 설정
GOALSKILL_DB_CONFIG = get_db_config("Goalskill_DB")

# 유효한 테이블명 목록 (48개)
VALID_TABLES = set()
for goal_scope in ["H", "S"]:
    for part in ["A", "B", "C", "D"]:
        for content_type in ["T", "P", "R"]:
            for sender in ["I", "M"]:
                VALID_TABLES.add(f"{goal_scope}{part}_{content_type}{sender}")


def _validate_table_name(table_name: str) -> bool:
    """테이블명이 48개 중 하나인지 검증 (SQL Injection 방지)"""
    return table_name in VALID_TABLES


# ============================================================================
# INSERT 함수
# ============================================================================

def save_to_goalskill_table(table_name: str, session_id: str, output_text: str, status = None) -> int:
    """
    Goalskill_DB 테이블에 text 데이터 저장.
    T/P/R 모든 타입에서 공통으로 사용. (A/C/D 파트)

    Args:
        table_name: 테이블명 (예: "HA_PI", "SA_TM")
        session_id: 유저 세션 ID
        output_text: 저장할 텍스트
        status: 컨디션 점수 (1~10). None이면 NULL로 저장.

    Returns:
        저장된 row의 id
    """
    if not _validate_table_name(table_name):
        raise ValueError(f"Invalid table name: {table_name}")

    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor()

        sql = f"INSERT INTO `{table_name}` (session_id, output, status, created_at) VALUES (%s, %s, %s, NOW())"
        cursor.execute(sql, (session_id, output_text, status))
        conn.commit()

        row_id = cursor.lastrowid
        logger.info(f"[Goalskill DB] Saved to {table_name}: session={session_id}, id={row_id}, status={status}")
        return row_id

    except Exception as e:
        logger.error(f"[Goalskill DB] Save Error ({table_name}): {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()


def save_to_goalskill_table_score(
    table_name: str, session_id: str, text: str, source_type: str, status: int
) -> int:
    """
    B파트 전용: Goalskill_DB 테이블에 수치 데이터 저장.
    source_type(learning/mindset)과 Gemini status(1~10)를 저장.

    Args:
        table_name: 테이블명 (예: "HB_TM", "SB_PM")
        session_id: 유저 세션 ID
        source_type: "learning" 또는 "mindset"
        status: 1~10 Gemini 점수

    Returns:
        저장된 row의 id
    """
    if not _validate_table_name(table_name):
        raise ValueError(f"Invalid table name: {table_name}")

    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor()

        sql = (
            f"INSERT INTO `{table_name}` "
            f"(session_id, output, source_type, status, created_at) "
            f"VALUES (%s, %s, %s, %s, NOW())"
        )
        cursor.execute(sql, (session_id, text, source_type, status))
        conn.commit()

        row_id = cursor.lastrowid
        logger.info(
            f"[Goalskill DB] Saved score to {table_name}: "
            f"session={session_id}, text={text}, source={source_type}, status={status}, id={row_id}"
        )
        return row_id

    except Exception as e:
        logger.error(f"[Goalskill DB] Save Score Error ({table_name}): {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()



def save_admin_log(
    session_id: str,
    sender: str,
    part: str,
    input_text: str,
    h_scoring: float,
    s_scoring: float,
    t_scoring: float,
    p_scoring: float,
    r_scoring: float,
    result_table: str,
) -> int:
    """
    admin 테이블에 분류 점수 로그 저장.
    어떤 기준으로 해당 테이블에 분류되었는지 확인할 수 있도록
    각 카테고리별 키워드 점수를 기록합니다.

    Args:
        session_id: 유저 세션 ID
        sender: "I" (AI) 또는 "M" (User)
        part: "A" / "B" / "C" / "D"
        input_text: 원문 텍스트
        h_scoring: H(큰 목표) 키워드 점수
        s_scoring: S(작은 목표) 키워드 점수
        t_scoring: T(대화) 최종 점수
        p_scoring: P(조언) 최종 점수
        r_scoring: R(방향성) 최종 점수
        result_table: 최종 분류된 테이블명

    Returns:
        저장된 row의 id
    """
    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor()

        sql = (
            "INSERT INTO `admin` "
            "(session_id, sender, part, input_text, H_scoring, S_scoring, "
            "T_scoring, P_scoring, R_scoring, result_table, created_at) "
            "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())"
        )
        cursor.execute(sql, (
            session_id, sender, part, input_text,
            h_scoring, s_scoring,
            t_scoring, p_scoring, r_scoring,
            result_table,
        ))
        conn.commit()

        row_id = cursor.lastrowid
        logger.info(f"[Goalskill DB] Admin log saved: table={result_table}, id={row_id}")
        return row_id

    except Exception as e:
        logger.error(f"[Goalskill DB] Admin Log Save Error: {e}")
        # admin 로그 실패는 메인 로직에 영향을 주지 않도록 0 반환
        return 0
    finally:
        if conn:
            cursor.close()
            conn.close()


# ============================================================================
# SELECT 함수
# ============================================================================

def get_from_goalskill_table(table_name: str, session_id: str) -> list:
    """
    특정 세션의 모든 데이터를 조회.

    Args:
        table_name: 테이블명 (예: "HA_PI")
        session_id: 유저 세션 ID

    Returns:
        [{"id": 1, "session_id": "...", "output": "...", "created_at": "..."}]
    """
    if not _validate_table_name(table_name):
        raise ValueError(f"Invalid table name: {table_name}")

    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        sql = f"SELECT * FROM `{table_name}` WHERE session_id = %s ORDER BY created_at ASC"
        cursor.execute(sql, (session_id,))
        results = cursor.fetchall()

        return results

    except Exception as e:
        logger.error(f"[Goalskill DB] Get Error ({table_name}): {e}")
        return []
    finally:
        if conn:
            cursor.close()
            conn.close()


def get_latest_from_goalskill_table(table_name: str, session_id: str) -> dict:
    """
    특정 세션의 최신 데이터 1건 조회.
    """
    if not _validate_table_name(table_name):
        raise ValueError(f"Invalid table name: {table_name}")

    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        sql = f"SELECT * FROM `{table_name}` WHERE session_id = %s ORDER BY created_at DESC LIMIT 1"
        cursor.execute(sql, (session_id,))
        result = cursor.fetchone()

        return result or {}

    except Exception as e:
        logger.error(f"[Goalskill DB] Get Latest Error ({table_name}): {e}")
        return {}
    finally:
        if conn:
            cursor.close()
            conn.close()


# ============================================================================
# 유저별 전체 Goalskill 데이터 조회
# ============================================================================

def get_all_goalskill_data(session_id: str) -> dict:
    """
    유저의 모든 Goalskill_DB 데이터를 48개 테이블에서 수집.

    Returns:
        {
            "HA_PI": [{"id": 1, "output": "..."}],
            "HA_TI": [{"id": 2, "output": 1}],
            ...
        }
    """
    result = {}
    conn = None

    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        for table_name in sorted(VALID_TABLES):
            sql = f"SELECT * FROM `{table_name}` WHERE session_id = %s ORDER BY created_at ASC"
            cursor.execute(sql, (session_id,))
            rows = cursor.fetchall()
            if rows:
                result[table_name] = rows

        return result

    except Exception as e:
        logger.error(f"[Goalskill DB] Get All Error: {e}")
        return {}
    finally:
        if conn:
            cursor.close()
            conn.close()


def get_talk_count(session_id: str, goal_scope: str = None, part: str = None) -> int:
    """
    특정 세션의 대화 횟수 합계를 조회.

    Args:
        session_id: 세션 ID
        goal_scope: "H" 또는 "S" (None이면 모두)
        part: "A"~"D" (None이면 모두)

    Returns:
        대화 횟수 합계
    """
    conn = None
    total = 0

    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor()

        # 대상 T 테이블 목록 생성
        scopes = [goal_scope] if goal_scope else ["H", "S"]
        parts = [part] if part else ["A", "B", "C", "D"]
        senders = ["I", "M"]

        for s in scopes:
            for p in parts:
                for sender in senders:
                    table_name = f"{s}{p}_T{sender}"
                    sql = f"SELECT COUNT(*) FROM `{table_name}` WHERE session_id = %s"
                    cursor.execute(sql, (session_id,))
                    row = cursor.fetchone()
                    if row:
                        total += row[0]

        return total

    except Exception as e:
        logger.error(f"[Goalskill DB] Talk Count Error: {e}")
        return 0
    finally:
        if conn:
            cursor.close()
            conn.close()


# ============================================================================
# result テーブル CRUD 関数
# ============================================================================

def get_current_daily(session_id: str) -> int:
    """
    result テーブルから現在の最大daily番号を取得。
    行がなければ0を返す。
    """
    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor()
        sql = "SELECT MAX(daily) FROM `result` WHERE session_id = %s"
        cursor.execute(sql, (session_id,))
        row = cursor.fetchone()
        return row[0] if row and row[0] is not None else 0
    except Exception as e:
        logger.error(f"[Result DB] Get Current Daily Error: {e}")
        return 0
    finally:
        if conn:
            cursor.close()
            conn.close()


def create_result_row(session_id: str, daily: int) -> int:
    """
    result テーブルに新しい行を作成（カリキュラム日次の開始）。
    既に同じdailyの行がある場合は作成しない。

    Returns:
        作成された行のid。既に存在する場合は既存行のid。
    """
    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor()

        # 重複チェック
        check_sql = "SELECT id FROM `result` WHERE session_id = %s AND daily = %s"
        cursor.execute(check_sql, (session_id, daily))
        existing = cursor.fetchone()
        if existing:
            logger.info(f"[Result DB] Row already exists: session={session_id}, daily={daily}, id={existing[0]}")
            return existing[0]

        # 新規作成
        sql = "INSERT INTO `result` (session_id, output, daily, created_at) VALUES (%s, %s, %s, NOW())"
        cursor.execute(sql, (session_id, "", daily))
        conn.commit()

        row_id = cursor.lastrowid
        logger.info(f"[Result DB] Created: session={session_id}, daily={daily}, id={row_id}")
        return row_id

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


def update_result_condition_score(session_id: str, daily: int, score: int) -> bool:
    """
    result テーブルの condition_score を更新。

    Args:
        session_id: セッションID
        daily: カリキュラム日次番号
        score: コンディションスコア (1~10)

    Returns:
        更新成功ならTrue
    """
    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor()

        sql = "UPDATE `result` SET condition_score = %s WHERE session_id = %s AND daily = %s"
        cursor.execute(sql, (score, session_id, daily))
        conn.commit()

        logger.info(f"[Result DB] Updated condition_score={score}: session={session_id}, daily={daily}")
        return cursor.rowcount > 0

    except Exception as e:
        logger.error(f"[Result DB] Update Condition Score Error: {e}")
        return False
    finally:
        if conn:
            cursor.close()
            conn.close()


def get_avg_condition_score(session_id: str) -> int | None:
    """
    48個テーブルから今日のcondition_score(status)の平均を集計。
    A'パート終了時に呼び出し、result テーブルに保存する用。

    対象テーブル: SA_TM, HA_TM (A パート、ユーザー発話のみ)

    Returns:
        平均スコア (1~10の整数) or None (データなし)
    """
    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor()

        # A パートのユーザー発話テーブル (condition が入るテーブル)
        target_tables = ["SA_TM", "HA_TM"]
        all_scores = []

        for table in target_tables:
            sql = f"""
                SELECT status FROM `{table}` 
                WHERE session_id = %s 
                AND status IS NOT NULL 
                AND DATE(created_at) = CURDATE()
            """
            cursor.execute(sql, (session_id,))
            rows = cursor.fetchall()
            for row in rows:
                if row[0] is not None:
                    all_scores.append(row[0])

        if not all_scores:
            return None

        avg = sum(all_scores) / len(all_scores)
        result = max(1, min(10, round(avg)))
        logger.info(f"[Result DB] AVG condition: scores={all_scores}, avg={avg:.1f}, result={result}")
        return result

    except Exception as e:
        logger.error(f"[Result DB] Get AVG Condition Score Error: {e}")
        return None
    finally:
        if conn:
            cursor.close()
            conn.close()


def update_result_b_scores(session_id: str, daily: int, mindset_score: int = None, understanding_score: int = None) -> bool:
    """
    result テーブルの mindset_score, understanding_score を更新。
    B パートのデイリーチェック完了時に呼び出す。

    Args:
        session_id: セッションID
        daily: カリキュラム日次番号
        mindset_score: マインドセットスコア (1~10), Noneなら更新しない
        understanding_score: 理解度スコア (1~10), Noneなら更新しない

    Returns:
        更新成功ならTrue
    """
    if mindset_score is None and understanding_score is None:
        return False

    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor()

        # 動的にSET句を構築
        set_parts = []
        params = []
        if mindset_score is not None:
            set_parts.append("mindset_score = %s")
            params.append(mindset_score)
        if understanding_score is not None:
            set_parts.append("understanding_score = %s")
            params.append(understanding_score)

        params.extend([session_id, daily])
        sql = f"UPDATE `result` SET {', '.join(set_parts)} WHERE session_id = %s AND daily = %s"
        cursor.execute(sql, tuple(params))
        conn.commit()

        logger.info(
            f"[Result DB] Updated B scores: session={session_id}, daily={daily}, "
            f"mindset={mindset_score}, understanding={understanding_score}"
        )
        return cursor.rowcount > 0

    except Exception as e:
        logger.error(f"[Result DB] Update B Scores Error: {e}")
        return False
    finally:
        if conn:
            cursor.close()
            conn.close()


def get_recent_condition_scores(session_id: str) -> list:
    """
    result テーブルから最近2回分のcondition_scoreをdaily降順で取得。
    再訪問ユーザーへの挨拶文に過去のコンディション情報を反映するために使用。

    Returns:
        [{"daily": 3, "score": 4}, {"daily": 2, "score": 7}]
        スコアがない場合は空リスト。
    """
    conn = None
    try:
        conn = mysql.connector.connect(**GOALSKILL_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        sql = """
            SELECT daily, condition_score 
            FROM `result` 
            WHERE session_id = %s AND condition_score IS NOT NULL
            ORDER BY daily DESC 
            LIMIT 2
        """
        cursor.execute(sql, (session_id,))
        rows = cursor.fetchall()

        result = [{"daily": row["daily"], "score": row["condition_score"]} for row in rows]
        logger.info(f"[Result DB] Recent condition scores: session={session_id}, scores={result}")
        return result

    except Exception as e:
        logger.error(f"[Result DB] Get Recent Condition Scores Error: {e}")
        return []
    finally:
        if conn:
            cursor.close()
            conn.close()