
-- USER TABLE
CREATE TABLE User (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    Password VARCHAR(255),
    Phone VARCHAR(20),
    City VARCHAR(100),
    Address TEXT
);

-- ADMINISTRATOR TABLE
CREATE TABLE Administrator (
    UserID INT PRIMARY KEY,
    Name VARCHAR(100),
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    Password VARCHAR(255),
    Phone VARCHAR(20)
);

-- AGENT TABLE
CREATE TABLE Agent (
    UserID INT PRIMARY KEY,
    Name VARCHAR(100),
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    Password VARCHAR(255),
    Phone VARCHAR(20)
);

-- VEHICLE TYPE
CREATE TABLE VehicleType (
    TypeID INT PRIMARY KEY AUTO_INCREMENT,
    TypeName VARCHAR(50),
    Description TEXT,
    Capacity INT
);

-- VEHICLE
CREATE TABLE Vehicle (
    VehicleID INT PRIMARY KEY AUTO_INCREMENT,
    TypeID INT,
    VehicleNumber VARCHAR(20),
    FOREIGN KEY (TypeID) REFERENCES VehicleType(TypeID)
);

-- LOCATION
CREATE TABLE Location (
    LocationID INT PRIMARY KEY AUTO_INCREMENT,
    LocationName VARCHAR(100),
    LocationAddress TEXT
);

-- TRIP
CREATE TABLE Trip (
    TripID INT PRIMARY KEY AUTO_INCREMENT,
    StartPoint VARCHAR(100),
    EndPoint VARCHAR(100),
    StartTime DATETIME,
    EndTime DATETIME,
    VehicleID INT,
    FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID)
);

-- TRIP HALTS
CREATE TABLE TripHalts (
    TripID INT,
    SeqID INT,
    LocationID INT,
    PRIMARY KEY (TripID, SeqID),
    FOREIGN KEY (TripID) REFERENCES Trip(TripID),
    FOREIGN KEY (LocationID) REFERENCES Location(LocationID)
);

-- VEHICLE TICKET
CREATE TABLE VehicleTicket (
    TransactionID INT PRIMARY KEY AUTO_INCREMENT,
    VNumber VARCHAR(50),
    UserID INT,
    Email VARCHAR(100),
    DateOfJourney DATE,
    TripID INT,
    FOREIGN KEY (UserID) REFERENCES User(UserID),
    FOREIGN KEY (TripID) REFERENCES Trip(TripID)
);

-- OFFER
CREATE TABLE Offer (
    OfferID INT PRIMARY KEY AUTO_INCREMENT,
    OfferName VARCHAR(100),
    StartDate DATE,
    EndDate DATE,
    ApplicableFor VARCHAR(50),
    DetailDescription TEXT
);

-- MESSAGE / REQUEST
CREATE TABLE MessageRequest (
    RequestID INT PRIMARY KEY AUTO_INCREMENT,
    FromID INT,
    ToID INT,
    Subject VARCHAR(255),
    Date DATE,
    Description TEXT,
    FOREIGN KEY (FromID) REFERENCES User(UserID),
    FOREIGN KEY (ToID) REFERENCES Agent(UserID)
);
