|
CREATE TABLE "AppFleet" (
|
|
"FleetID" int PRIMARY KEY,
|
|
"FleetName" varchar,
|
|
"Priority" int,
|
|
"GPSServiceName" varchar,
|
|
"GPSAPIEndPoint" varchar,
|
|
"GPSAPIUserName" varchar,
|
|
"GPSAPIPassword" varchar
|
|
);
|
|
|
|
CREATE TABLE "AppVehiclesCategory" (
|
|
"CategoryID" int PRIMARY KEY,
|
|
"CategoryName" varchar
|
|
);
|
|
|
|
CREATE TABLE "AppVehicles" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"FleetID" int NOT NULL,
|
|
"Type" varchar,
|
|
"CategoryID" int,
|
|
"TowingCapability" bool,
|
|
"VehiclePlateNumber" varchar UNIQUE,
|
|
"Weight" int,
|
|
"Height" double,
|
|
"Width" double,
|
|
"Length" double,
|
|
"MinimumCapacity" int,
|
|
"Capacity" int,
|
|
"DriverCapacity" int,
|
|
"VehiclePhoneNumber" varchar,
|
|
"UnloadingTimeInMinutes" int,
|
|
"LoadTimeInMinutes" int,
|
|
"Status" varchar,
|
|
"CreatedOn" datetime,
|
|
"ChangedOn" datetime,
|
|
"ChangedBy" int
|
|
);
|
|
|
|
CREATE TABLE "AppVehicleSpeciality" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"VehicleID" int,
|
|
"SpecialityTypeID" int
|
|
);
|
|
|
|
CREATE TABLE "SpecialityTypes" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"Name" varchar
|
|
);
|
|
|
|
CREATE TABLE "AppDrivers" (
|
|
"DriverID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"FleetID" int,
|
|
"UserID" int,
|
|
"FullName" varchar,
|
|
"PrivatePhoneNumber" varchar,
|
|
"DrivingLicenseCategoryID" int,
|
|
"IsDriverPairable" bool,
|
|
"Status" varchar,
|
|
"CreatedOn" datetime,
|
|
"ChangedOn" datetime,
|
|
"ChangedBy" int
|
|
);
|
|
|
|
CREATE TABLE "LicenseCategories" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"Code" varchar UNIQUE,
|
|
"Description" varchar
|
|
);
|
|
|
|
CREATE TABLE "AppLocations" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"LocationCode" varchar UNIQUE,
|
|
"LocationName" varchar,
|
|
"PostNumber" varchar,
|
|
"City" varchar,
|
|
"Address" varchar,
|
|
"GPSLatitude" double,
|
|
"GPSLongitude" double,
|
|
"StoreOpeningTime" time,
|
|
"StoreClosingTime" time,
|
|
"TimegateStart" time,
|
|
"TimegateClose" time,
|
|
"UnloadTimePerRollyInMin" int,
|
|
"Permissions" varchar,
|
|
"NotificationPhoneNumber" varchar,
|
|
"Status" varchar,
|
|
"CreatedOn" datetime,
|
|
"ChangedOn" datetime,
|
|
"ChangedBy" int
|
|
);
|
|
|
|
CREATE TABLE "AppDriveLog" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"DriverID" int,
|
|
"FleetID" int,
|
|
"WorkingHoursStart" datetime,
|
|
"WorkingHoursEnd" datetime,
|
|
"DrivingHoursStart" datetime,
|
|
"DrivingHoursEnd" datetime,
|
|
"CreatedOn" datetime
|
|
);
|
|
|
|
CREATE TABLE "AppFleetOrderLog" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"LoadingStationID" int,
|
|
"PreviousLoadingStationID" int,
|
|
"UnloadingStationID" int,
|
|
"TimegateStart" time,
|
|
"TimegateClose" time,
|
|
"PlannedArrivalTime" time,
|
|
"RealArrivalTime" time,
|
|
"VehicleID" int,
|
|
"ConsignmentNoteNumber" varchar,
|
|
"StartingOdometer" int,
|
|
"FinishingOdometer" int,
|
|
"BoxCount" int
|
|
);
|
|
|
|
CREATE TABLE "AppEventLog" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"FleetOrderID" int,
|
|
"PlanningDate" datetime,
|
|
"StartingDate" datetime,
|
|
"EndingDate" datetime,
|
|
"CountOfBeingLate" int,
|
|
"SumDistanceInKM" int,
|
|
"SumBoxCount" int
|
|
);
|
|
|
|
CREATE TABLE "SMSLog" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"DriverID" int,
|
|
"SMSContent" varchar,
|
|
"CreationDate" datetime
|
|
);
|
|
|
|
CREATE TABLE "AppUsers" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"Username" varchar UNIQUE NOT NULL,
|
|
"Email" varchar UNIQUE NOT NULL,
|
|
"FullName" varchar,
|
|
"PasswordHash" varchar,
|
|
"RoleID" int,
|
|
"Status" varchar,
|
|
"CreationDate" datetime,
|
|
"ChangeDate" datetime
|
|
);
|
|
|
|
CREATE TABLE "UserRoles" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"RoleName" varchar UNIQUE,
|
|
"Description" varchar
|
|
);
|
|
|
|
CREATE TABLE "PermissionTypes" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"PermissionKey" varchar UNIQUE,
|
|
"PermissionDescription" varchar,
|
|
"ModuleName" varchar,
|
|
"Action" varchar
|
|
);
|
|
|
|
CREATE TABLE "RolePermissions" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"RoleID" int,
|
|
"PermissionID" int
|
|
);
|
|
|
|
CREATE TABLE "BacklogUserInteractionLog" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"UserID" int,
|
|
"ActionType" varchar,
|
|
"ActionDetail" text,
|
|
"Timestamp" datetime,
|
|
"Success" bool,
|
|
"ErrorMessage" text
|
|
);
|
|
|
|
CREATE TABLE "BacklogAPIRequestLog" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"UserID" int,
|
|
"Endpoint" varchar,
|
|
"RequestMethod" varchar,
|
|
"RequestPayload" text,
|
|
"ResponseStatus" int,
|
|
"ResponseTimeMs" int,
|
|
"Timestamp" datetime
|
|
);
|
|
|
|
CREATE TABLE "DataChangeLog" (
|
|
"ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
"UserID" int,
|
|
"TableName" varchar,
|
|
"RecordID" int,
|
|
"EditType" varchar,
|
|
"OldValue" text,
|
|
"NewValue" text,
|
|
"ChangeTime" datetime
|
|
);
|
|
|
|
ALTER TABLE "AppVehicles" ADD FOREIGN KEY ("FleetID") REFERENCES "AppFleet" ("FleetID");
|
|
|
|
ALTER TABLE "AppVehicles" ADD FOREIGN KEY ("CategoryID") REFERENCES "AppVehiclesCategory" ("CategoryID");
|
|
|
|
ALTER TABLE "AppVehicleSpeciality" ADD FOREIGN KEY ("VehicleID") REFERENCES "AppVehicles" ("ID");
|
|
|
|
ALTER TABLE "AppVehicleSpeciality" ADD FOREIGN KEY ("SpecialityTypeID") REFERENCES "SpecialityTypes" ("ID");
|
|
|
|
ALTER TABLE "AppDrivers" ADD FOREIGN KEY ("FleetID") REFERENCES "AppFleet" ("FleetID");
|
|
|
|
ALTER TABLE "AppDrivers" ADD FOREIGN KEY ("UserID") REFERENCES "AppUsers" ("ID");
|
|
|
|
ALTER TABLE "AppDrivers" ADD FOREIGN KEY ("DrivingLicenseCategoryID") REFERENCES "LicenseCategories" ("ID");
|
|
|
|
ALTER TABLE "AppDriveLog" ADD FOREIGN KEY ("DriverID") REFERENCES "AppDrivers" ("DriverID");
|
|
|
|
ALTER TABLE "AppDriveLog" ADD FOREIGN KEY ("FleetID") REFERENCES "AppFleet" ("FleetID");
|
|
|
|
ALTER TABLE "AppFleetOrderLog" ADD FOREIGN KEY ("LoadingStationID") REFERENCES "AppLocations" ("ID");
|
|
|
|
ALTER TABLE "AppFleetOrderLog" ADD FOREIGN KEY ("PreviousLoadingStationID") REFERENCES "AppLocations" ("ID");
|
|
|
|
ALTER TABLE "AppFleetOrderLog" ADD FOREIGN KEY ("UnloadingStationID") REFERENCES "AppLocations" ("ID");
|
|
|
|
ALTER TABLE "AppFleetOrderLog" ADD FOREIGN KEY ("VehicleID") REFERENCES "AppVehicles" ("ID");
|
|
|
|
ALTER TABLE "AppEventLog" ADD FOREIGN KEY ("FleetOrderID") REFERENCES "AppFleetOrderLog" ("ID");
|
|
|
|
ALTER TABLE "SMSLog" ADD FOREIGN KEY ("DriverID") REFERENCES "AppDrivers" ("DriverID");
|
|
|
|
ALTER TABLE "AppUsers" ADD FOREIGN KEY ("RoleID") REFERENCES "UserRoles" ("ID");
|
|
|
|
ALTER TABLE "RolePermissions" ADD FOREIGN KEY ("RoleID") REFERENCES "UserRoles" ("ID");
|
|
|
|
ALTER TABLE "RolePermissions" ADD FOREIGN KEY ("PermissionID") REFERENCES "PermissionTypes" ("ID");
|
|
|
|
ALTER TABLE "BacklogUserInteractionLog" ADD FOREIGN KEY ("UserID") REFERENCES "AppUsers" ("ID");
|
|
|
|
ALTER TABLE "BacklogAPIRequestLog" ADD FOREIGN KEY ("UserID") REFERENCES "AppUsers" ("ID");
|
|
|
|
ALTER TABLE "DataChangeLog" ADD FOREIGN KEY ("UserID") REFERENCES "AppUsers" ("ID");
|