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

# ========================================
# 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 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()