fix_schema.py

import sqlite3
import os

db_path = "/home/kali/hosting-company/data/game.db"

def fix_table(cursor, table_name, needed_columns):
    # Check existing columns
    cursor.execute(f"PRAGMA table_info({table_name})")
    columns = [row[1] for row in cursor.fetchall()]
    
    for col_name, col_type in needed_columns:
        if col_name not in columns:
            print(f"Adding column {col_name} to {table_name}")
            try:
                cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {col_name} {col_type}")
            except Exception as e:
                print(f"Error adding column {col_name} to {table_name}: {e}")

def fix_schema():
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    server_states_columns = [
        ("os_vendor", "TEXT"),
        ("os_name", "TEXT"),
        ("os_version", "TEXT"),
        ("os_codename", "TEXT"),
        ("os_arch", "TEXT"),
        ("os_package_manager", "TEXT"),
        ("os_notes", "TEXT"),
        ("os_install_status", "TEXT"),
        ("os_install_progress", "INTEGER DEFAULT 0"),
        ("os_installed_at", "INTEGER"),
        ("ip_v4", "TEXT"),
        ("ip_v6", "TEXT"),
        ("hosting_customer_id", "INTEGER"),
        ("hosting_plan_id", "INTEGER"),
        ("hosting_label", "TEXT"),
        ("last_power_state", "TEXT"),
        ("power_cycles", "INTEGER DEFAULT 0"),
        ("usage_hours", "FLOAT DEFAULT 0"),
        ("health_percent", "FLOAT DEFAULT 100.0"),
        ("rma_ready_ts", "INTEGER"),
        ("cpu_profile_id", "INTEGER"),
        ("cpu_profile_label", "TEXT"),
        ("cpu_limit_percent", "INTEGER DEFAULT 100"),
        ("cpu_alert_threshold_percent", "INTEGER DEFAULT 90"),
    ]
    fix_table(cursor, "server_states", server_states_columns)

    servers_columns = [
        ("specs_json", "TEXT DEFAULT '{}'"),
    ]
    fix_table(cursor, "servers", servers_columns)
    
    software_licenses_columns = [
        ("purchase_price_usd", "FLOAT DEFAULT 0"),
    ]
    fix_table(cursor, "software_licenses", software_licenses_columns)
    
    conn.commit()
    conn.close()

if __name__ == "__main__":
    if os.path.exists(db_path):
        fix_schema()
        print("Schema fix completed.")
    else:
        print(f"Database not found at {db_path}")