-- Table: WMS
CREATE TABLE WMS (
    Address VARCHAR(255),
    Inventory VARCHAR(50),
    Admins VARCHAR(50),
    Dispatchers VARCHAR(50),
    Warehouses VARCHAR(50)
);

-- Table: Location
CREATE TABLE Location (
    PickingType VARCHAR(50),
    PickingPriority INT,
    Bay VARCHAR(50),
    Level VARCHAR(50),
    Aisle VARCHAR(50),
    IsLocked BIT,
    LocationZone VARCHAR(50),
    LocationName VARCHAR(100),
    Warehouse VARCHAR(50),
);

-- Table: Role
CREATE TABLE Role (
    roleID INT PRIMARY KEY,
    Username VARCHAR(50),
    Password VARCHAR(255),
    Email VARCHAR(100),
    SecurityLevel VARCHAR(50),
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Table: SecurityQuestion
CREATE TABLE SecurityQuestion (
    Question VARCHAR(255),
    Answer VARCHAR(255)
);

-- Table: Warehouse
CREATE TABLE Warehouse (
    roleID INT,
    Username VARCHAR(50),
    Password VARCHAR(255),
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    SecurityLevel VARCHAR(50),
    Email VARCHAR(100),
    String VARCHAR(255),
    FOREIGN KEY (roleID) REFERENCES Role(roleID)
);

-- Table: OrderHandler
CREATE TABLE OrderHandler (
    roleID INT,
    Username VARCHAR(50),
    Password VARCHAR(255),
    Email VARCHAR(100),
    SecurityLevel VARCHAR(50),
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    FOREIGN KEY (roleID) REFERENCES Role(roleID)
);

-- Table: Dispatcher
CREATE TABLE Dispatcher (
    roleID INT,
    Username VARCHAR(50),
    Password VARCHAR(255),
    Email VARCHAR(100),
    SecurityLevel VARCHAR(50),
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    TruckID INT,
    RouteSchedule VARCHAR(100),
    FOREIGN KEY (roleID) REFERENCES Role(roleID)
);

-- Table: Admin
CREATE TABLE Admin (
    roleID INT,
    Username VARCHAR(50),
    Password VARCHAR(255),
    Email VARCHAR(100),
    SecurityLevel VARCHAR(50),
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    FOREIGN KEY (roleID) REFERENCES Role(roleID)
);

-- Table: UkayMiamiOffice
CREATE TABLE UkayMiamiOffice (
    roleID INT,
    Username VARCHAR(50),
    Password VARCHAR(255),
    Email VARCHAR(100),
    SecurityLevel VARCHAR(50),
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    FOREIGN KEY (roleID) REFERENCES Role(roleID)
);

-- Table: UkayCorporateOffice
CREATE TABLE UkayCorporateOffice (
    roleID INT,
    Username VARCHAR(50),
    Password VARCHAR(255),
    Email VARCHAR(100),
    SecurityLevel VARCHAR(50),
    FirstName VARCHAR(50),
    MiddleName VARCHAR(50),
    LastName VARCHAR(50),
    FOREIGN KEY (roleID) REFERENCES Role(roleID)
);
