import pymysql

try:
    conn = pymysql.connect(host='localhost', user='root', password='', db='gestion_mei_1')
    cursor = conn.cursor()
    cursor.execute("SHOW COLUMNS FROM roles")
    existing_cols = [r[0] for r in cursor.fetchall()]

    new_cols = [
        ('roles', 'TINYINT(1) DEFAULT 0 AFTER `utilisateurs`'),
        ('activites', 'TINYINT(1) DEFAULT 0 AFTER `roles`'),
        ('export_data', 'TINYINT(1) DEFAULT 0 AFTER `can_delete`'),
        ('valider_demande', 'TINYINT(1) DEFAULT 0 AFTER `export_data`')
    ]

    for col_name, col_def in new_cols:
        if col_name not in existing_cols:
            print(f"Adding column '{col_name}' to roles table...")
            cursor.execute(f"ALTER TABLE roles ADD COLUMN `{col_name}` {col_def}")
            # Grant 1 for built-in SUPER ADMIN
            cursor.execute(f"UPDATE roles SET `{col_name}` = 1 WHERE name = 'SUPER ADMIN'")

    conn.commit()

    # Re-fetch columns
    cursor.execute("SHOW COLUMNS FROM roles")
    updated_cols = [r[0] for r in cursor.fetchall()]
    print("Updated columns in roles table:", updated_cols)
    conn.close()
    print("SUCCESS: Roles table updated successfully!")
except Exception as e:
    print("Error during migration:", e)