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

def insert_selfpr_log(session_id, user_name=None, user_age=None, company_name=None, photo_path=None, selected_items=None, site_url=None):
    conn = None
    try:
        conn = mysql.connector.connect(**INPUT_DB_CONFIG)
        cursor = conn.cursor()
        
        # 1. Check if record exists for this session
        # We assume we want to update the latest record if it exists
        check_sql = "SELECT id FROM selfpr_logs WHERE session_id = %s ORDER BY created_at DESC LIMIT 1"
        cursor.execute(check_sql, (session_id,))
        row = cursor.fetchone()
        
        if row:
            # UPDATE
            log_id = row[0]
            update_fields = []
            params = []
            
            if user_name is not None:
                update_fields.append("user_name=%s")
                params.append(user_name)
            if user_age is not None:
                update_fields.append("user_age=%s")
                params.append(user_age)
            if company_name is not None:
                update_fields.append("company_name=%s")
                params.append(company_name)
            if photo_path is not None:
                update_fields.append("photo_path=%s")
                params.append(photo_path)
            if selected_items is not None:
                update_fields.append("selected_items=%s")
                params.append(selected_items)
            if site_url is not None:
                update_fields.append("site_url=%s")
                params.append(site_url)
            
            if update_fields:
                sql = f"UPDATE selfpr_logs 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 selfpr_logs 
                (session_id, user_name, user_age, company_name, photo_path, selected_items, site_url) 
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(sql, (session_id, user_name, user_age, company_name, photo_path, selected_items, site_url))
            conn.commit()
            return cursor.lastrowid
        
    except Exception as e:
        print(f"[SelfPR DB Error] {e}")
        raise e
    finally:
        if conn:
            conn.close()

def get_selfpr_log(session_id):
    conn = None
    try:
        conn = mysql.connector.connect(**INPUT_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        sql = """
            SELECT user_name, user_age, company_name, photo_path, selected_items, site_url, created_at
            FROM selfpr_logs 
            WHERE session_id = %s 
            ORDER BY created_at DESC 
            LIMIT 1
        """
        
        cursor.execute(sql, (session_id,))
        row = cursor.fetchone()
        
        return row
        
    except Exception as e:
        print(f"[SelfPR DB Load Error] {e}")
        return None
    finally:
        if conn:
            conn.close()

def get_username_from_profile(session_id):
    conn = None
    try:
        # Use A_DB config
        from app.core.config import A_DB_CONFIG
        conn = mysql.connector.connect(**A_DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # Check table and column based on user request: user_profile_summary, username
        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 "Unknown User"
        
    except Exception as e:
        print(f"[SelfPR Profile Load Error] {e}")
        return "Guest"
    finally:
        if conn:
            conn.close()