import mysql.connector
from db import DB_CONFIG

def alter_database():
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        
        alter_queries = [
            # Add available_colors to materials
            "ALTER TABLE materials ADD COLUMN available_colors JSON DEFAULT NULL AFTER price_per_cm3",
            
            # Add material_id to orders to link strictly to reference
            "ALTER TABLE orders ADD COLUMN material_id INT DEFAULT NULL AFTER user_id",
            "ALTER TABLE orders ADD CONSTRAINT fk_order_material FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE SET NULL"
        ]
        
        for query in alter_queries:
            try:
                cursor.execute(query)
                print(f"Executed: {query}")
            except mysql.connector.Error as e:
                print(f"Command error: {e}")
                
        conn.commit()
        print("Database migration completed successfully")
        
    except mysql.connector.Error as err:
        print(f"Error connecting to MySQL: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    alter_database()