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

def insert_evaluation(session_id, rating, advice, interviewer_name=None, interview_type=None):
    """
    Inserts or Updates an interview evaluation record based on session_id.
    """
    conn = None
    try:
        # Use v2 config method
        db_config = get_db_config(DB_NAME)
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()
        
        # 1. Check if record exists
        check_sql = "SELECT id FROM interview_evaluation WHERE session_id = %s"
        cursor.execute(check_sql, (session_id,))
        row = cursor.fetchone()
        
        if row:
            # UPDATE
            log_id = row[0]
            update_fields = []
            params = []
            
            # Update provided fields
            update_fields.append("rating=%s")
            params.append(rating)
            
            if advice is not None:
                update_fields.append("advice=%s")
                params.append(advice)
            
            if interviewer_name is not None:
                update_fields.append("interviewer_name=%s")
                params.append(interviewer_name)
                
            if interview_type is not None:
                update_fields.append("interview_type=%s")
                params.append(interview_type)
            
            if update_fields:
                sql = f"UPDATE interview_evaluation SET {', '.join(update_fields)} WHERE id = %s"
                params.append(log_id)
                cursor.execute(sql, tuple(params))
                conn.commit()
            
            return log_id
        else:
            # INSERT
            sql = """
                INSERT INTO interview_evaluation 
                (session_id, rating, advice, interviewer_name, interview_type) 
                VALUES (%s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (session_id, rating, advice, interviewer_name, interview_type))
            conn.commit()
            
            return cursor.lastrowid
        
    except Exception as e:
        print(f"[Interview Eval DB Error] {e}")
        raise e
    finally:
        if conn:
            conn.close()

def get_username_from_profile(session_id):
    """
    Fetches the username from Sales_A_DB.user_profile_summary based on session_id.
    Returns 'Guest' if not found or error.
    """
    conn = None
    try:
        # Use Sales_A_DB
        db_config = get_db_config("Sales_A_DB")
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        sql = "SELECT username FROM user_profile_summary WHERE session_id = %s"
        cursor.execute(sql, (session_id,))
        row = cursor.fetchone()
        
        if row and row['username']:
            return row['username']
        return "Guest"
        
    except Exception as e:
        print(f"[Interview Profile Load Error] {e}")
        return "Guest"
    finally:
        if conn:
            conn.close()