database_setup.sql

CREATE DATABASE bus_reservation_db;
USE bus_reservation_db;

CREATE TABLE buses (
                       bus_id INT AUTO_INCREMENT PRIMARY KEY,
                       bus_name VARCHAR(100) NOT NULL,
                       bus_type ENUM('AC Sleeper', 'Non-AC Sleeper', 'AC Seater', 'Non-AC Seater') NOT NULL,
                       total_seats INT NOT NULL
);

CREATE TABLE routes (
                        route_id INT AUTO_INCREMENT PRIMARY KEY,
                        source_city VARCHAR(50) NOT NULL,
                        destination_city VARCHAR(50) NOT NULL,
                        distance_km INT,
                        UNIQUE(source_city, destination_city)
);

CREATE TABLE schedules (
                           schedule_id INT AUTO_INCREMENT PRIMARY KEY,
                           bus_id INT,
                           route_id INT,
                           departure_time DATETIME NOT NULL,
                           arrival_time DATETIME NOT NULL,
                           ticket_price DECIMAL(10, 2) NOT NULL,
                           FOREIGN KEY (bus_id) REFERENCES buses(bus_id),
                           FOREIGN KEY (route_id) REFERENCES routes(route_id)
);

CREATE TABLE users (
                       user_id INT AUTO_INCREMENT PRIMARY KEY,
                       full_name VARCHAR(100) NOT NULL,
                       email VARCHAR(100) UNIQUE NOT NULL,
                       password_hash VARCHAR(255) NOT NULL,
                       phone VARCHAR(15),
                       role ENUM('CUSTOMER', 'ADMIN') DEFAULT 'CUSTOMER',
                       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE bookings (
                          booking_id INT AUTO_INCREMENT PRIMARY KEY,
                          user_id INT,
                          schedule_id INT,
                          seat_number VARCHAR(10) NOT NULL,
                          booking_status ENUM('CONFIRMED', 'CANCELLED') DEFAULT 'CONFIRMED',
                          booking_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                          FOREIGN KEY (user_id) REFERENCES users(user_id),
                          FOREIGN KEY (schedule_id) REFERENCES schedules(schedule_id),
                          UNIQUE(schedule_id, seat_number)
);