# app/models/findgoal_module.py
import mysql.connector  
from app.core.config import get_db_config , logger
import json
from typing import List, Dict, Optional


A_DB_CONFIG = get_db_config("Sales_A_DB")

# ========================================
# findgoal_Q function
# ========================================
def save_question(session_id: str, step_order: int, question_text: str):
    """질문 저장 (user_id 제거됨)"""
    conn = None
    try:
        # 1. DB connection
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        # 2. SQL statement (user_id 제거)
        sql = """
            INSERT INTO findgoal_Q 
            (session_id, step_order, question_text)
            VALUES (%s, %s, %s)
        """
        
        # 3. Execute
        cursor.execute(sql, (session_id, step_order, question_text))
        conn.commit() 
        
        # 4. Return the ID of the just saved question
        return cursor.lastrowid
        
    except Exception as e:
        logger.error(f"FindGoal Q Save Error: {e}")
        raise e
    finally:
        # 5. Close the connection (required!)
        if conn:
            cursor.close()
            conn.close()

def save_answer(session_id: str, question_id: int, answer_text: str):
    """Answer Save (user_id, step_order 제거됨)"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO findgoal_A 
            (session_id, question_id, answer_text)
            VALUES (%s, %s, %s)
        """
        cursor.execute(sql, (session_id, question_id, answer_text))
        conn.commit()

        return cursor.lastrowid
    except Exception as e:
        logger.error(f"FindGoal A Save Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()

def clear_history(session_id: str):
    """해당 세션의 FindGoal 대화/결과 초기화 (재시작용)"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        for table, col in [("findgoal_A", "session_id"), ("findgoal_P", "session_id"), ("findgoal_Output", "session_id"), ("findgoal_Q", "session_id")]:
            cursor.execute(f"DELETE FROM {table} WHERE {col} = %s", (session_id,))
        conn.commit()
    except Exception as e:
        logger.error(f"FindGoal clear_history Error: {e}")
        raise
    finally:
        if conn:
            cursor.close()
            conn.close()


def get_question_by_step(session_id: str, step_order: int):
    """해당 step_order의 질문 1건 조회 (답변 저장 시 question_id 확보용)"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        cursor.execute(
            "SELECT id, step_order, question_text FROM findgoal_Q WHERE session_id = %s AND step_order = %s ORDER BY id DESC LIMIT 1",
            (session_id, step_order),
        )
        return cursor.fetchone()
    except Exception as e:
        logger.error(f"FindGoal get_question_by_step Error: {e}")
        return None
    finally:
        if conn:
            cursor.close()
            conn.close()


def get_last_question(session_id: str):
    """Get the last question"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT id, step_order, question_text
            FROM findgoal_Q
            WHERE session_id = %s
            ORDER BY step_order DESC
            LIMIT 1
        """
        
        cursor.execute(sql, (session_id,))
        result = cursor.fetchone()
        
        return result
        
    except Exception as e:
        logger.error(f"FindGoal Q Load Error: {e}")
        return None
    finally:
        if conn:
            cursor.close()
            conn.close()


def save_processing(session_id: str, confidence_score: float, reasoning: str):
    """Save the AI processing result (P) - user_id, step_order, answer_id 모두 제거됨"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO findgoal_P
            (session_id, confidence_score, reasoning)
            VALUES (%s, %s, %s)
        """
        
        cursor.execute(sql, (
            session_id, 
            confidence_score, reasoning
        ))
        conn.commit()
        
        return cursor.lastrowid
        
    except Exception as e:
        logger.error(f"FindGoal P Save Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()

def save_or_update_output(session_id: str, final_goal: str):
    """Save or update the final result (Output) - user_id, updated_at 제거됨"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO findgoal_Output
            (session_id, final_goal)
            VALUES (%s, %s)
            ON DUPLICATE KEY UPDATE
                final_goal = VALUES(final_goal)
        """
        
        cursor.execute(sql, (session_id, final_goal))
        conn.commit()
        
    except Exception as e:
        logger.error(f"FindGoal Output Save Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()

def get_conversation_history(session_id: str):
    """Get the entire conversation history of the session"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT 
                Q.step_order,
                Q.question_text,
                A.answer_text
            FROM findgoal_Q Q
            LEFT JOIN findgoal_A A 
                ON Q.id = A.question_id
            WHERE Q.session_id = %s
            ORDER BY Q.step_order ASC
        """
        
        cursor.execute(sql, (session_id,))
        results = cursor.fetchall()
        
        return results
        
    except Exception as e:
        logger.error(f"FindGoal History Load Error: {e}")
        return []
    finally:
        if conn:
            cursor.close()
            conn.close()

def sync_to_user_profile_summary(session_id: str, final_goal: str):
    """FindGoal 완료 시 user_profile_summary 테이블에도 동기화"""
    conn = None
    try:
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor()
        
        sql = """
            INSERT INTO user_profile_summary
            (session_id, final_goal)
            VALUES (%s, %s)
            ON DUPLICATE KEY UPDATE
                final_goal = VALUES(final_goal),
                updated_at = NOW()
        """
        cursor.execute(sql, (session_id, final_goal))
        conn.commit()
        
        logger.info(f"FindGoal synced to user_profile_summary for session: {session_id}")
        
    except Exception as e:
        logger.error(f"FindGoal Summary Sync Error: {e}")
        raise e
    finally:
        if conn:
            cursor.close()
            conn.close()