














CREATE DATABASE y_bus_booking;

USE y_bus_booking;

CREATE TABLE yk_buses(
    BusID INT AUTO_INCREMENT PRIMARY KEY,
    Plates_Number VARCHAR(20) NOT NULL UNIQUE,
    TotalSeats INT NOT NULL,
    BusType VARCHAR NOT NULL,
);

CREATE TABLE yk_users(
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    UserName VARCHAR(100) NOT NULL,
    Password VARCHAR (100) NOT NULL UNIQUE,
    UserRole ENUM("Agent","Manager")
);

CREATE TABLE yk_schedules(
    ScheduleID INT AUTO_INCREMENT PRIMARY KEY,
    BusID INT NOT NULL,
    RouteName VARCHAR(100) NOT NULL,
    DeparturePoint VARCHAR(100) NOT NULL,
    Destination VARCHAR(100) NOT NULL,
    DepartureTime DATETIME NOT NULL,
    EstimatedArrivalTime DATETIME NOT NULL,
    TicketPrice INT (100) NOT NULL,
    ScheduleStatus VARCHAR(100),  

    CONSTRAINT fk_schedule_bus
        FOREIGN KEY (BusID)
        REFERENCES yk_buses(BusID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
);

CREATE TABLE yk_bookings(
    BookingID INT AUTO_INCREMENT PRIMARY KEY,
    ScheduleID INT NOT NULL,
    UserID INT NOT NULL,
    PassengerName VARCHAR(100) NOT NULL,
    PassengerGender VARCHAR(100) NOT NULL,
    SeatNumber INT(100) NOT NULL,
    PaymentStatus VARCHAR(100),
    BookingDate DATET NOT NULL,

    CONSTRAINT fk_bookins_schedule
    FOREIGN KEY (ScheduleID)
    REFERENCES yk_schedules(ScheduleID)
    ON DELETE CASCADE
    ON UPDATE CASCADE

    CONSTRAINT fk_bookins_users
    FOREIGN KEY (UserID)
    REFERENCES yk_users(UserID)
    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
);



















create me the query of creating this database () with  table {yk_buses(BusID(PK),plateNumber,TotalSeats,BusType),yk_schedules(ScheduleID(PK),BusID(fk),RouteName,DepartureName,DeparturePoint,Destination,DepartureTime,EstimatedArrivalTime,TicketPrice,ScheduleStatus),yk_bookings(BookingID(pk),ScheduleID(fk),UserID(FK),PassengerName,PassengerGender,PassengerPhone,SeatNumber,PaymeenetStatus,BookingDate),yk_users(UserID(Pk),UserName,Password,UserRole) }