init-app.js

// Initialization script for College Event Management System
// This script creates the database tables and an initial admin user

require('dotenv').config();
const db = require('./backend/config/db');
const { hashPassword } = require('./backend/utils/passwordUtils');

async function initializeApp() {
  try {
    console.log('Initializing College Event Management System...');
    
    // Create database
    await db.query('CREATE DATABASE IF NOT EXISTS em');
    await db.query('USE em');
    console.log('Database created/selected successfully');
    
    // Drop existing tables in correct order to avoid foreign key constraints
    console.log('Dropping existing tables...');
    await db.execute('DROP TABLE IF EXISTS event_feedback');
    await db.execute('DROP TABLE IF EXISTS registrations');
    await db.execute('DROP TABLE IF EXISTS events');
    await db.execute('DROP TABLE IF EXISTS students');
    await db.execute('DROP TABLE IF EXISTS admins');
    await db.execute('DROP TABLE IF EXISTS event_categories');
    await db.execute('DROP TABLE IF EXISTS departments');
    console.log('Existing tables dropped successfully');
    
    // Create departments table
    await db.execute(`
      CREATE TABLE departments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) UNIQUE NOT NULL,
        code VARCHAR(10) UNIQUE NOT NULL,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);
    console.log('Departments table created successfully');
    
    // Create event_categories table
    await db.execute(`
      CREATE TABLE event_categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) UNIQUE NOT NULL,
        description TEXT,
        color VARCHAR(7) DEFAULT '#007bff',
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);
    console.log('Event categories table created successfully');
    
    // Create admins table
    await db.execute(`
      CREATE TABLE admins (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        role ENUM('super_admin', 'admin', 'moderator') DEFAULT 'admin',
        is_active BOOLEAN DEFAULT TRUE,
        last_login TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_username (username),
        INDEX idx_email (email),
        INDEX idx_role (role)
      )
    `);
    console.log('Admins table created successfully');
    
    // Create students table
    await db.execute(`
      CREATE TABLE students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id VARCHAR(20) UNIQUE NOT NULL,
        first_name VARCHAR(50) NOT NULL,
        last_name VARCHAR(50) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        phone VARCHAR(15),
        department_id INT,
        year INT,
        semester INT,
        date_of_birth DATE,
        gender ENUM('male', 'female', 'other'),
        address TEXT,
        profile_picture VARCHAR(255),
        is_active BOOLEAN DEFAULT TRUE,
        last_login TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
        INDEX idx_student_id (student_id),
        INDEX idx_email (email),
        INDEX idx_department (department_id),
        INDEX idx_year (year)
      )
    `);
    console.log('Students table created successfully');
    
    // Create events table
    await db.execute(`
      CREATE TABLE events (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(200) NOT NULL,
        description TEXT,
        category_id INT,
        date DATE NOT NULL,
        start_time TIME,
        end_time TIME,
        venue VARCHAR(200) NOT NULL,
        max_participants INT DEFAULT 100,
        registration_deadline DATETIME,
        is_published BOOLEAN DEFAULT FALSE,
        is_featured BOOLEAN DEFAULT FALSE,
        banner_image VARCHAR(255),
        organizer_name VARCHAR(100),
        organizer_contact VARCHAR(100),
        created_by_admin_id INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (category_id) REFERENCES event_categories(id) ON DELETE SET NULL,
        FOREIGN KEY (created_by_admin_id) REFERENCES admins(id) ON DELETE SET NULL,
        INDEX idx_date (date),
        INDEX idx_category (category_id),
        INDEX idx_published (is_published),
        INDEX idx_featured (is_featured)
      )
    `);
    console.log('Events table created successfully');
    
    // Create registrations table
    await db.execute(`
      CREATE TABLE registrations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL,
        event_id INT NOT NULL,
        registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        status ENUM('registered', 'cancelled', 'attended', 'absent') DEFAULT 'registered',
        attendance_status ENUM('pending', 'attended', 'absent') DEFAULT 'pending',
        cancellation_reason TEXT,
        confirmation_code VARCHAR(50) UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
        FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
        UNIQUE KEY unique_registration (student_id, event_id),
        INDEX idx_student (student_id),
        INDEX idx_event (event_id),
        INDEX idx_status (status),
        INDEX idx_attendance (attendance_status)
      )
    `);
    console.log('Registrations table created successfully');
    
    // Create event_feedback table
    await db.execute(`
      CREATE TABLE event_feedback (
        id INT AUTO_INCREMENT PRIMARY KEY,
        event_id INT NOT NULL,
        student_id INT NOT NULL,
        rating INT CHECK (rating >= 1 AND rating <= 5),
        comment TEXT,
        is_anonymous BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
        FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
        UNIQUE KEY unique_feedback (event_id, student_id),
        INDEX idx_event (event_id),
        INDEX idx_rating (rating)
      )
    `);
    console.log('Event feedback table created successfully');
    
    // Insert default departments
    const departments = [
      ['Computer Science', 'CS', 'Department of Computer Science'],
      ['Electrical Engineering', 'EE', 'Department of Electrical Engineering'],
      ['Mechanical Engineering', 'ME', 'Department of Mechanical Engineering'],
      ['Civil Engineering', 'CE', 'Department of Civil Engineering'],
      ['Business Administration', 'BA', 'Department of Business Administration']
    ];
    
    for (const [name, code, description] of departments) {
      await db.execute(
        'INSERT INTO departments (name, code, description) VALUES (?, ?, ?)',
        [name, code, description]
      );
    }
    console.log('Default departments inserted');
    
    // Insert default event categories
    const categories = [
      ['Academic', 'Academic events and workshops', '#007bff'],
      ['Sports', 'Sports competitions and activities', '#28a745'],
      ['Cultural', 'Cultural festivals and performances', '#ffc107'],
      ['Technical', 'Technical workshops and seminars', '#17a2b8'],
      ['Social', 'Social gatherings and networking', '#6f42c1']
    ];
    
    for (const [name, description, color] of categories) {
      await db.execute(
        'INSERT INTO event_categories (name, description, color) VALUES (?, ?, ?)',
        [name, description, color]
      );
    }
    console.log('Default event categories inserted');
    
    // Check if admin user exists, if not create one
    const [admins] = await db.execute('SELECT * FROM admins LIMIT 1');
    
    if (admins.length === 0) {
      // Hash the password
      const hashedPassword = await hashPassword('admin123');
      
      // Insert initial admin user
      await db.execute(
        'INSERT INTO admins (username, email, password, first_name, last_name, role) VALUES (?, ?, ?, ?, ?, ?)',
        ['admin', 'admin@college.edu', hashedPassword, 'System', 'Administrator', 'super_admin']
      );
      console.log('Initial admin user created:');
      console.log('Username: admin');
      console.log('Password: admin123');
      console.log('Please change this password after first login!');
    } else {
      console.log('Admin user already exists');
    }
    
    console.log('Application initialized successfully!');
    console.log('You can now start the server with: npm start');
    
  } catch (error) {
    console.error('Initialization failed:', error);
  } finally {
    // Close the connection pool
    db.end();
  }
}

initializeApp();