import mysql.connector
from app.core.config import INPUT_DB_CONFIG

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:
        conn = mysql.connector.connect(**INPUT_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()