//Create a Node.js file that demonstrate create college database and table in MySQL 
// create_database.js

const mysql = require('mysql');

// Create a connection to the MySQL server
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'college'
});

// Connect to the MySQL server
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL server:', err);
    return;
  }
  console.log('Connected to MySQL server');

  // Create the college database
  connection.query('CREATE DATABASE IF NOT EXISTS college', (err) => {
    if (err) {
      console.error('Error creating college database:', err);
      return;
    }
    console.log('College database created successfully');

    // Use the college database
    connection.query('USE college', (err) => {
      if (err) {
        console.error('Error selecting college database:', err);
        return;
      }

      // Create the students table
      connection.query(`CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        age INT NOT NULL,
        gender ENUM('male', 'female', 'other') NOT NULL,
        department VARCHAR(255) NOT NULL
      )`, (err) => {
        if (err) {
          console.error('Error creating students table:', err);
          return;
        }
        console.log('Students table created successfully');
        
        // Close the connection
        connection.end((err) => {
          if (err) {
            console.error('Error closing connection:', err);
            return;
          }
          console.log('Connection closed');
        });
      });
    });
  });
});
