import mysql.connector
from mysql.connector import Error

def create_table():
    db_config = {
        'host': 'localhost',
        'user': 'root',
        'password': '',
        'database': 'gestion_mei_1'
    }

    print(f"Connexion à la base de données {db_config['database']}...")
    try:
        connection = mysql.connector.connect(**db_config)
    except Error as e:
        print(f"Erreur de connexion: {e}")
        return

    cursor = connection.cursor()

    create_table_sql = """
    CREATE TABLE IF NOT EXISTS `notifications` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `user_id` INT(11) DEFAULT NULL,
      `role_name` VARCHAR(50) DEFAULT NULL,
      `permission_required` VARCHAR(50) DEFAULT NULL,
      `title` VARCHAR(255) NOT NULL,
      `message` TEXT NOT NULL,
      `type` VARCHAR(50) NOT NULL DEFAULT 'info',
      `notif_key` VARCHAR(50) NOT NULL,
      `link` VARCHAR(255) DEFAULT NULL,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      INDEX `idx_user_id` (`user_id`),
      INDEX `idx_role_name` (`role_name`),
      INDEX `idx_permission_required` (`permission_required`),
      INDEX `idx_created_at` (`created_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
    """

    try:
        print("Création de la table `notifications`...")
        cursor.execute(create_table_sql)
        connection.commit()
        print("Table `notifications` créée ou déjà existante avec succès !")
    except Error as e:
        print(f"Erreur lors de la création de la table: {e}")
    finally:
        cursor.close()
        connection.close()

if __name__ == '__main__':
    create_table()