CREATE DATABASE swiftWheels;

USE swiftWheels;

-- Buses Table
CREATE TABLE buses (
    bus_id INT AUTO_INCREMENT PRIMARY KEY,
    plates_number VARCHAR(20) NOT NULL UNIQUE,
    total_seat INT NOT NULL
);

-- Routes Table
CREATE TABLE routes (
    r_id INT AUTO_INCREMENT PRIMARY KEY,
    source VARCHAR(100) NOT NULL,
    destination VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- Users Table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    role ENUM('customer', 'driver', 'fleet_manager') 
        DEFAULT 'customer'
);

-- Schedules Table
CREATE TABLE schedules (
    sch_id INT AUTO_INCREMENT PRIMARY KEY,
    bus_id INT NOT NULL,
    r_id INT NOT NULL,
    departure_time DATETIME NOT NULL,

    CONSTRAINT fk_schedule_bus
        FOREIGN KEY (bus_id)
        REFERENCES buses(bus_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_schedule_route
        FOREIGN KEY (r_id)
        REFERENCES routes(r_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Tickets Table
CREATE TABLE ticket (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    customername VARCHAR(100) NOT NULL,
    sch_id INT NOT NULL,
    seat_number INT NOT NULL,

    CONSTRAINT fk_ticket_schedule
        FOREIGN KEY (sch_id)
        REFERENCES schedules(sch_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);