import mysql.connector
from db import DB_CONFIG

def update_schema():
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        
        try:
            cursor.execute("ALTER TABLE orders ADD COLUMN user_id INT NULL;")
            cursor.execute("ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;")
        except mysql.connector.Error as e:
            if e.errno == 1060: # Column already exists
                print("Column user_id already exists")
            else:
                print(f"Error adding user_id: {e}")
                    
        conn.commit()
        print("Schema update for orders successful")
        
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    update_schema()