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");
