-- Script de création et mise à jour de la base, en cas
-- de modification de la structure, il convient d'incrémenter le
-- numéro de version de la constante DATABASE_VERSION dans com.trackforce.mpost.dataaccess.DatabaseHelper
;

CREATE TABLE IF NOT EXISTS [Activity] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [ActivityClient] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [ActivityContract] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Activity]              text,
  [FK_Client]                text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [ActionLog] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_LoggedAction]          text,
  [StartDate]                datetime,
  [EndDate]                  datetime,
  [StateDate]                datetime,
  [ServerId]                 text,
  [LogType]                  integer
);


CREATE TABLE IF NOT EXISTS [Affiliation] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [StateDate]                datetime
);


#if_less_than 88
DROP TABLE IF EXISTS [ApplicationUser];

CREATE TABLE IF NOT EXISTS [ApplicationUser] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Lastname]                 text, 
  [Firstname]                text, 
  [Login]                    text, 
  [Password]                 text, 
  [Role]                     text, 
  [FK_Role]                  text,
  [FK_Language]              text,
  [FK_Affiliation]           text
);


#if_less_than 88
DROP TABLE IF EXISTS [AssignableUser];

CREATE TABLE IF NOT EXISTS [AssignableUser] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Lastname]                 text, 
  [Firstname]                text, 
  [Login]                    text, 
  [PasswordBag]              text,
  [FK_Role]                  text,
  [FK_Language]              text,
  [FK_Affiliation]           text,
  [StateDate]                datetime
);


#if_less_than 73
DROP TABLE IF EXISTS [AssignedOpenEvent];

CREATE TABLE IF NOT EXISTS [AssignedOpenEvent] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [StartDate]                datetime,  
  [OverdueDate]              datetime,
  [EventID]                  text,
  [EventName]                text,
  [EventType]                text,
  [EventCategory]            text,
  [StartUserID]              text,
  [StartUserFirstname]       text,
  [StartUserLastname]        text,
  [CustomFieldValue]         text,
  [Data]                     text
);


CREATE TABLE IF NOT EXISTS [Audit] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Type]                     text,
  [PlannedStartDate]         datetime,
  [PlannedEndDate]           datetime,
  [RealStartDate]            datetime,
  [RealEndDate]              datetime,
  [ClosingDate]              datetime,
  [IsClosed]                 integer,
  [FK_Auditor]               text,
  [FK_CenterOfOperation]     text,
  [FK_DomainOfExpertise]     text,
  [FK_Property]              text,
  [FK_Vendor]                text,
  [FK_OperationCategory]     text,
  [EventOccurrenceID]        text,
  [ProgressPercent]          integer,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [AuditChangeRecord] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Audit]                 text,
  [StateDate]                datetime,
  [FieldName]                text,
  [FromValue]                text,
  [ToValue]                  text,
  [ActionType]               text,
  [FK_Auditor]               text
);


#if_less_than 74
DROP TABLE IF EXISTS [AuditNonCompliance];

CREATE TABLE IF NOT EXISTS [AuditNonCompliance] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Audit]                 text,
  [FK_CreatorAuditor]        text,
  [CreateDate]               datetime,
  [Status]                   text,
  [EventFieldValue]          text,
  [SubFormCaption]           text,
  [EventFieldName]           text,
  [Comment]                  text,
  [PictureUrl]               text,
  [AWSVideo]                 text,
  [FK_Vendor]                text,
  [IsAssignedToVendor]       integer,
  [FK_AssignedAuditor]       text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [AuditNonComplianceAction] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_AuditNonCompliance]    text,
  [Comment]                  text,
  [PictureUrl]               text,
  [AWSVideo]                 text,
  [IsPrivate]                integer,
  [ActionDate]               datetime,
  [FK_ActionAuditor]         text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [Auditor] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Firstname]                text,
  [Lastname]                 text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [AuditorCenterOfOperation] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Auditor]               text,
  [FK_CenterOfOperation]     text
);


CREATE TABLE IF NOT EXISTS [AuditorDomainOfExpertise] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Auditor]               text,
  [FK_DomainOfExpertise]     text
);


#if_less_than 73
DROP TABLE IF EXISTS [AWSFile];

CREATE TABLE IF NOT EXISTS [AWSFile] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [LocaleFilename]           text,
  [AWSFilename]              text,
  [Type]                     text,
  [Extra]                    text
);


CREATE TABLE IF NOT EXISTS [Base64BufferObject] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Type]					 text      NOT NULL,
  [Base64Buffer]             text
);


#if_less_than 84
DROP TABLE IF EXISTS [BreakTemplate];

CREATE TABLE IF NOT EXISTS [BreakTemplate] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text, 
  [BreakDuration]            integer,
  [IsMandatory]              integer,
  [StateDate]                datetime  
);


#if_less_than 87
DROP TABLE IF EXISTS [Bunch];

CREATE TABLE IF NOT EXISTS [Bunch] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Type]                     text,
  [Instruction]              text,
  [IsSignatureRequired]      integer,
  [Active]                   integer,
  [StateDate]                datetime
);


#if_less_than 87
DROP TABLE IF EXISTS [BunchEvent];

CREATE TABLE IF NOT EXISTS [BunchEvent] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Bunch]                 text      NOT NULL,
  [TakerPersonID]            text,
  [StartDate]                datetime,
  [EndDate]                  datetime,
  [IsLocallyCreated]         integer,
  [TakerPersonAddedID]       integer,
  [Data]                     text,
  [ServerID]                 text
);

CREATE INDEX IF NOT EXISTS [idx_BunchEvent_FKBunch] ON [BunchEvent] (FK_Bunch);


#if_less_than 87
DROP TABLE IF EXISTS [BunchKey];

CREATE TABLE IF NOT EXISTS [BunchKey] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Bunch]                 text      NOT NULL,
  [Name]                     text
);

CREATE INDEX IF NOT EXISTS [idx_BunchKey_FKBunch] ON [BunchKey] (FK_Bunch);


#if_less_than 87
DROP TABLE IF EXISTS [BunchPermission];

CREATE TABLE IF NOT EXISTS [BunchPermission] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Bunch]                 text      NOT NULL,
  [PersonType]               text,
  [AuthorizationType]        text,
  [PersonID]                 text,
  [PersonCompany]            text
);

CREATE INDEX IF NOT EXISTS [idx_BunchPermission_FKBunch] ON [BunchPermission] (FK_Bunch);


CREATE TABLE IF NOT EXISTS [Campaign] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Status]                   text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [CampaignAttribute] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Description]              text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [CampaignCategory] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Periodicity]              text,
  [PeriodicityCount]         integer,
  [FK_Event]                 text,
  [FK_Instruction]           text,
  [FK_CampaignIcon]          text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [CampaignElement] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Comment]                  text,
  [LocationComment]          text,
  [PhotoUrl]                 text,
  [ElementActive]            integer,
  [MapLocationTopPercent]    double,
  [MapLocationLeftPercent]   double,
  [FK_CampaignCategory]      text      NOT NULL,
  [FK_CampaignMap]           text,
  [Active]                   integer,
  [StateDate]                datetime
);

CREATE INDEX IF NOT EXISTS [idx_CampaignElement_FKCampaignCategory] ON [CampaignElement] (FK_CampaignCategory);
CREATE INDEX IF NOT EXISTS [idx_CampaignElement_FKCampaignMap] ON [CampaignElement] (FK_CampaignMap);


CREATE TABLE IF NOT EXISTS [CampaignElementInspection] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_CampaignOccurrence]    text      NOT NULL,
  [FK_CampaignElement]       text      NOT NULL,
  [InspectionCompletedDate]  datetime,
  [EventOccurrenceData]      text,
  [StateDate]                datetime
);

CREATE INDEX IF NOT EXISTS [idx_CampaignElementInspection_FKCampaignOccurrence] ON [CampaignElementInspection] (FK_CampaignOccurrence);
CREATE INDEX IF NOT EXISTS [idx_CampaignElementInspection_FKCampaignElement] ON [CampaignElementInspection] (FK_CampaignElement);


CREATE TABLE IF NOT EXISTS [CampaignElementInspectionAnomaly] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_CampaignElementInspection] text  NOT NULL,
  [Comment]                  text,
  [CloseComment]             text,
  [Status]                   text,
  [StateDate]                datetime
);

CREATE INDEX IF NOT EXISTS [idx_CampaignElementInspectionAnomaly_FKCampaignElementInspection] ON [CampaignElementInspectionAnomaly] (FK_CampaignElementInspection);


CREATE TABLE IF NOT EXISTS [CampaignHierarchyLevel] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [FK_Parent]                text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [CampaignHierarchyNode] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [FK_HierarchyLevel]        text,
  [FK_Parent]                text,
  [FK_CampaignMap]           text,
  [Active]                   integer,
  [StateDate]                datetime
);

CREATE INDEX IF NOT EXISTS [idx_CampaignHierarchyNode_FKParent] ON [CampaignHierarchyNode] (FK_Parent);


CREATE TABLE IF NOT EXISTS [CampaignIcon] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [PictureUrl]               text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [CampaignMap] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [ImageUrl]                 text,
  [Width]                    integer,
  [Height]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [CampaignOccurrence] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Campaign]              text      NOT NULL,
  [Status]                   text,
  [PlannedStartDate]         datetime,
  [PlannedEndDate]           datetime,
  [RealStartDate]            datetime,
  [RealEndDate]              datetime,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [CampaignToCampaignCategory] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Campaign]              text      NOT NULL,
  [FK_CampaignCategory]      text      NOT NULL
);

CREATE INDEX IF NOT EXISTS [idx_CampaignToCampaignCategory_FKCampaign] ON [CampaignToCampaignCategory] (FK_Campaign);


CREATE TABLE IF NOT EXISTS [CampaignCategoryToCampaignAttribute] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_CampaignCategory]      text      NOT NULL,
  [FK_CampaignAttribute]     text      NOT NULL
);

CREATE INDEX IF NOT EXISTS [idx_CampaignCategoryToCampaignAttribute_FKCampaignCategory] ON [CampaignCategoryToCampaignAttribute] (FK_CampaignCategory);
CREATE INDEX IF NOT EXISTS [idx_CampaignCategoryToCampaignAttribute_FKCampaignAttribute] ON [CampaignCategoryToCampaignAttribute] (FK_CampaignAttribute);


CREATE TABLE IF NOT EXISTS [CampaignElementToCampaignAttribute] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_CampaignElement]       text      NOT NULL,
  [FK_CampaignAttribute]     text      NOT NULL
);

CREATE INDEX IF NOT EXISTS [idx_CampaignElementToCampaignAttribute_FKCampaignElement] ON [CampaignElementToCampaignAttribute] (FK_CampaignElement);
CREATE INDEX IF NOT EXISTS [idx_CampaignElementToCampaignAttribute_FKCampaignAttribute] ON [CampaignElementToCampaignAttribute] (FK_CampaignAttribute);


CREATE TABLE IF NOT EXISTS [CenterOfOperation] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [CertifiedMessage] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Message]                  text      NOT NULL,
  [ExpirationTimeUTC]        datetime  NOT NULL
);


#if_less_than 73
DROP TABLE IF EXISTS [Course];

CREATE TABLE IF NOT EXISTS [Course] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Name]                     text, 
  [MaxDuration]              integer, 
  [CourseType]               text, 
  [Memo]                     text, 
  [MinStripNumber]           integer,
  [LastUsageCount]           integer,
  [StateDate]                datetime,
  [Active]                   integer,
  [GpsTrackingInterval]      integer,
  [TrackGpsPositions]        integer
);


#if_less_than 86
DROP TABLE IF EXISTS [CustomList];

CREATE TABLE IF NOT EXISTS [CustomList] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Type]                     text,
  [FK_CustomList]            text,
  [IsControllingList]        integer,
  [StateDate]                datetime   
);


#if_less_than 86
DROP TABLE IF EXISTS [CustomListItem];

CREATE TABLE IF NOT EXISTS [CustomListItem] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text, 
  [Rank]                     integer,
  [Tag]                      text,
  [FK_CustomList]            text,
  [FK_CustomListItem]        text
);

CREATE INDEX IF NOT EXISTS [idx_CustomListItem_FKCustomList] ON [CustomListItem] (FK_CustomList);


CREATE TABLE IF NOT EXISTS [DataStore] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Data]                     text
);


CREATE TABLE IF NOT EXISTS [DomainOfExpertise] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [FK_Report]                text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [DomainOfExpertiseOperationCategory] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_DomainOfExpertise]     text,
  [FK_OperationCategory]     text,
  [FK_Report]                text
);


CREATE TABLE IF NOT EXISTS [DueReportRule] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Trigger]                  text      NOT NULL,
  [Rule]                     text      NOT NULL,
  [ObjectID]                 text,
  [FK_Report]                text      NOT NULL
);


#if_less_than 73
DROP TABLE IF EXISTS [Event];

CREATE TABLE IF NOT EXISTS [Event] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Name]                     text, 
  [Description]              text, 
  [Category]                 text, 
  [IconUrl]                  text, 
  [CanStandby]               integer, 
  [Type]                     text,
  [LastUsageCount]           integer,
  [StateDate]                datetime,
  [Active]                   integer,
  [FK_Role]                  text
);


#if_less_than 73
DROP TABLE IF EXISTS [EventAuthorization];

CREATE TABLE IF NOT EXISTS [EventAuthorization] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Event]                 text      NOT NULL,
  [AccessLevel]              integer   NOT NULL,
  [ObjectID]                 text,
  [CanView]                  integer   NOT NULL,
  [CanStart]                 integer   NOT NULL,
  [CanUpdate]                integer   NOT NULL,
  [CanClose]                 integer   NOT NULL
);

CREATE INDEX IF NOT EXISTS [idx_EventAuthorization_FKEvent] ON [EventAuthorization] (FK_Event);


#if_less_than 73
DROP TABLE IF EXISTS [EventField];

CREATE TABLE IF NOT EXISTS [EventField] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Name]                     text, 
  [FieldType]                text, 
  [IsMandatory]              integer, 
  [Rank]                     integer, 
  [GroupName]                text, 
  [GroupRank]                integer, 
  [FK_Event]                 text      NOT NULL,
  [Tag]                      text,
  [Data]                     text,
  [CustomFieldOrder]         integer,
  [FK_NonComplianceTopic]    text
);

CREATE INDEX IF NOT EXISTS [idx_EventField_FKEvent] ON [EventField] (FK_Event);


#if_less_than 73
DROP TABLE IF EXISTS [EventFieldItem];

CREATE TABLE IF NOT EXISTS [EventFieldItem] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Value]                    text, 
  [Rank]                     integer, 
  [FK_EventField]            text 
);

CREATE INDEX IF NOT EXISTS [idx_EventFieldItem_FKEventField] ON [EventFieldItem] (FK_EventField);


#if_less_than 73
DROP TABLE IF EXISTS [EventFieldValue];

CREATE TABLE IF NOT EXISTS [EventFieldValue] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Field]                 text      NOT NULL,
  [FK_EventOccurence]        text      NOT NULL,
  [Data]                     text,
  [ServerId]                 text
);


CREATE TABLE IF NOT EXISTS [EventFilled] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [SessionID]                text      NOT NULL,
  [FK_Event]                 text      NOT NULL,
  [Date]                     datetime  NOT NULL
);


#if_less_than 73
DROP TABLE IF EXISTS [EventInstruction];

CREATE TABLE IF NOT EXISTS [EventInstruction] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Event]                 text      NOT NULL,
  [FK_Instruction]           text      NOT NULL
);

CREATE INDEX IF NOT EXISTS [idx_EventInstruction_FKEvent] ON [EventInstruction] (FK_Event);


#if_less_than 73
DROP TABLE IF EXISTS [EventOccurence];

CREATE TABLE IF NOT EXISTS [EventOccurence] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [StartDateUTC]             datetime  NOT NULL, 
  [EndDateUTC]               datetime, 
  [FK_StartUser]             text, 
  [FK_Event]                 text      NOT NULL, 
  [FK_EndUser]               text, 
  [FK_GeoLocation]           text,
  [FK_Round]                 text,
  [FK_TaskStep]              text,
  [ExtraData]                text,
  [IsClosed]                 integer   NOT NULL, 
  [IsValidated]              integer, 
  [ServerId]                 text 
);


CREATE TABLE IF NOT EXISTS [ExternalEvent] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [EventType]                text      NOT NULL, 
  [DateUTC]                  datetime  NOT NULL, 
  [FK_User]                  text      NOT NULL, 
  [Comment]                  text
);


#if_less_than 73
DROP TABLE IF EXISTS [FieldCheck];

CREATE TABLE IF NOT EXISTS [FieldCheck] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [ScheduledDateUTC]         datetime  NOT NULL,
  [FK_FieldCheckList]        text      NOT NULL,
  [FK_Supervisor]            text      NOT NULL,
  [RealDateUTC]              datetime,
  [Comment]                  text,
  [FK_Officer]               text,
  [FK_Guardroom]             text,
  [IsUnscheduled]            int
);


#if_less_than 73
DROP TABLE IF EXISTS [FieldCheckList];

CREATE TABLE IF NOT EXISTS [FieldCheckList] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [StateDate]                datetime
);


#if_less_than 73
DROP TABLE IF EXISTS [FieldCheckItem];

CREATE TABLE IF NOT EXISTS [FieldCheckItem] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_FieldCheckList]        text      NOT NULL,
  [Name]                     text,
  [Rank]                     integer
);

CREATE INDEX IF NOT EXISTS [idx_FieldCheckItem_FKFieldCheckList] ON [FieldCheckItem] (FK_FieldCheckList);


#if_less_than 73
DROP TABLE IF EXISTS [FieldCheckValue];

CREATE TABLE IF NOT EXISTS [FieldCheckValue] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_FieldCheck]            text      NOT NULL,
  [FK_FieldCheckItem]        text      NOT NULL,
  [Answer]                   integer   NOT NULL
);


#if_less_than 73
DROP TABLE IF EXISTS [File];

CREATE TABLE IF NOT EXISTS [File] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Filename]                 text, 
  [ServerUrl]                text, 
  [Type]                     integer, 
  [FK_RelatedObject]         text,
  [RelatedObjectType]        text, 
  [FileData]                 text,
  [Extra]                    text
);


CREATE TABLE IF NOT EXISTS [FleetVehicle] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Make]                     text,
  [Model]                    text,
  [Color]                    text,
  [Plate]                    text,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [FleetVehicleReport] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FleetVehicleReportTemplateID] integer,
  [UserID]                   text,
  [FleetVehicleID]           integer,
  [TripID]                   integer,
  [Reason]                   integer,
  [Date]                     datetime,
  [Mileage]                  integer,
  [GasLevel]                 integer,
  [Status]                   integer,
  [Comment]                  text,
  [ExteriorSketch]			 text,
  [Data]                     text
);


CREATE TABLE IF NOT EXISTS [FleetVehicleReportTemplate] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Type]                     integer   NOT NULL,
  [ExteriorCheckPictureURL]  text,
  [Data]                     text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [GdprPolicy] (
  [Id]                       integer   PRIMARY KEY,
  [Language]                 text      NOT NULL,
  [Region]                   text      NOT NULL,
  [CompressedHtml]           text,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [GeoLocation] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [DateUTC]                  datetime  NOT NULL, 
  [Latitude]                 double    NOT NULL, 
  [Longitude]                double    NOT NULL, 
  [FK_PreviousId]            text,
  [ServerId]                 text
);


CREATE TABLE IF NOT EXISTS [Guardroom] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Name]                     text      NOT NULL, 
  [TagNumber]                text, 
  [Country]                  text, 
  [City]                     text, 
  [Street]                   text, 
  [StreetNumber]             text
);


CREATE TABLE IF NOT EXISTS [GuardroomMaterial] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Reference]                text,
  [MaterialState]            text,
  [StateDate]                datetime
);


#if_less_than 77
DROP TABLE IF EXISTS [Instruction];

CREATE TABLE IF NOT EXISTS [Instruction] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Module]                   text      NOT NULL, 
  [Type]                     text      NOT NULL, 
  [Topic]                    text      NOT NULL, 
  [TopicLanguageId]          text      NOT NULL, 
  [Category]                 text, 
  [Title]                    text      NOT NULL, 
  [Body]                     text, 
  [PDFUrl]                   text, 
  [AWSVideo]                 text,
  [Rank]                     integer,
  [StateDate]                datetime,
  [IsNew]                    integer   NOT NULL
);


CREATE TABLE IF NOT EXISTS [InstructionAuthorization] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Instruction]           text      NOT NULL,
  [AccessLevel]              integer   NOT NULL,
  [ObjectID]                 text,
  [CanView]                  integer   NOT NULL
);

CREATE INDEX IF NOT EXISTS [idx_InstructionAuthorization_FKInstruction] ON [InstructionAuthorization] (FK_Instruction);


CREATE TABLE IF NOT EXISTS [JSONStore] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Type]                     text      NOT NULL,
  [ObjectId]                 text,
  [IsCompressed]             integer   NOT NULL,
  [Value]                    text 
);


CREATE TABLE IF NOT EXISTS [Language] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Language]                 text,
  [Region]                   text
);


DROP TABLE IF EXISTS [MailableUser];


#if_less_than 88
DROP TABLE IF EXISTS [Mail];

CREATE TABLE IF NOT EXISTS [Mail] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Subject]                  text,
  [Body]                     text,
  [CreateDate]               datetime,
  [FK_CreatorMailUser]       text,
  [Important]                integer,
  [Received]                 integer,
  [Read]                     integer,
  [Replied]                  integer,
  [Starred]                  integer,
  [Deleted]                  integer
);

CREATE INDEX IF NOT EXISTS [idx_Mail_FKCreatorMailUser] ON [Mail] (FK_CreatorMailUser);


#if_less_than 88
DROP TABLE IF EXISTS [MailRecipient];

CREATE TABLE IF NOT EXISTS [MailRecipient] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Mail]                  text,
  [FK_MailUser]              text,
  [IsCC]                     integer
);

CREATE INDEX IF NOT EXISTS [idx_MailRecipient_FKMailMessage] ON [MailRecipient] (FK_Mail);


#if_less_than 88
DROP TABLE IF EXISTS [MailUser];

CREATE TABLE IF NOT EXISTS [MailUser] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Lastname]                 text,
  [Firstname]                text,
  [FK_Affiliation]           text,
  [Active]                   integer
);


CREATE TABLE IF NOT EXISTS [Map] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [ImageUrl]                 text,
  [Width]                    integer,
  [Height]                   integer,
  [IsRoot]                   integer,
  [Name]                     text,
  [Rank]                     integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [MapElement] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Map]                   text,
  [ElementType]              text,
  [ObjectId]                 text,
  [StateDate]                datetime,
  [Coordinates]              text
);

CREATE INDEX IF NOT EXISTS [idx_MapElement_ObjectId] ON [MapElement] (ObjectId);


DROP TABLE IF EXISTS [Message];


#if_less_than 73
DROP TABLE IF EXISTS [NonCompliance];

CREATE TABLE IF NOT EXISTS [NonCompliance] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_NonComplianceItem]     text,
  [DateUtc]                  datetime  NOT NULL,
  [FK_FieldCheck]            text,
  [FK_Officer]               text,
  [FK_Guardroom]             text,
  [Description]              text,
  [CorrectiveAction]         text,  
  [FK_OfficerSignature]      text,
  [OfficerRefuseToSign]      integer
);


#if_less_than 73
DROP TABLE IF EXISTS [NonComplianceTopic];

CREATE TABLE IF NOT EXISTS [NonComplianceTopic] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [Name]                     text,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [NonComplianceTopicPropertyVendor] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_NonComplianceTopic]    text,
  [FK_Property]              text,
  [FK_Vendor]                text
);


#if_less_than 73
DROP TABLE IF EXISTS [NonComplianceItem];

CREATE TABLE IF NOT EXISTS [NonComplianceItem] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [FK_NonComplianceTopic]    text      NOT NULL,
  [Name]                     text
);

CREATE INDEX IF NOT EXISTS [idx_NonComplianceItem_FKNonComplianceTopic] ON [NonComplianceItem] (FK_NonComplianceTopic);


CREATE TABLE IF NOT EXISTS [OperationCategory] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [ParkingVehicle] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Make]                     text,
  [Model]                    text,
  [Color]                    text,
  [LicensePlate]             text,
  [DisplayLicensePlate]      text,
  [FK_Person]                text,
  [StateDate]                datetime
);

CREATE INDEX IF NOT EXISTS [idx_ParkingVehicle_LicensePlate] ON [ParkingVehicle] (LicensePlate);


CREATE TABLE IF NOT EXISTS [ParkingVehicleAdded] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Make]                     text,
  [Model]                    text,
  [Color]                    text,
  [LicensePlate]             text,
  [DisplayLicensePlate]      text
);


#if_less_than 81
DROP TABLE IF EXISTS [ParkingViolation];

CREATE TABLE IF NOT EXISTS [ParkingViolation] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_ParkingVehicle]        integer,
  [FK_ParkingViolationType]  integer,
  [FK_ParkingViolationSanction] integer,
  [LinkedTagReference]       text,
  [ConsideredCount]          integer,
  [Date]                     datetime
);

CREATE INDEX IF NOT EXISTS [idx_ParkingViolation_FKParkingVehicle] ON [ParkingViolation] (FK_ParkingVehicle);
CREATE INDEX IF NOT EXISTS [idx_ParkingViolation_LinkedTagReference] ON [ParkingViolation] (LinkedTagReference);


#if_less_than 81
DROP TABLE IF EXISTS [ParkingViolationAdded];

CREATE TABLE IF NOT EXISTS [ParkingViolationAdded] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_ParkingVehicle]        integer,
  [LicensePlate]             text,
  [FK_ParkingViolationType]  integer,
  [FK_ParkingViolationSanction] integer,
  [LinkedTagReference]       text,
  [LinkedTagTechnology]      text,
  [ConsideredCount]          integer,
  [Date]                     datetime,
  [ServerID]                 integer
);


CREATE TABLE IF NOT EXISTS [ParkingViolationTypeGroup] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Name]                     text,
  [CombinableDays]           integer,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [ParkingViolationType] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_ParkingViolationTypeGroup] integer,
  [Name]                     text,
  [Category]                 text,
  [Description]              text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [ParkingViolationSanction] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Description]              text,
  [ExclusionDays]            integer,
  [IsWheelClamp]             integer,
  [IsSendEmail]              integer,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [ParkingViolationRule] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_ParkingViolationType]  integer,
  [FK_ParkingViolationSanction] integer,
  [FK_Event]                 text,  
  [MinOccurrences]           integer,
  [MaxOccurences]            integer,
  [Active]                   integer,
  [StateDate]                datetime
);


#if_less_than 73
DROP TABLE IF EXISTS [Persistent];


#if_less_than 79
DROP TABLE IF EXISTS [Person];

CREATE TABLE IF NOT EXISTS [Person] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Type]                     integer,
  [Firstname]                text,
  [Lastname]                 text,
  [FK_PersonCompany]         integer,
  [IsRecurrentVisitor]       integer,
  [PictureURL]               text,
  [AccessID]                 text,
  [Allowed]                  integer,
  [StateDate]                datetime
);

CREATE INDEX IF NOT EXISTS [idx_Person_AccessID] ON [Person] (AccessID);


#if_less_than 79
DROP TABLE IF EXISTS [PersonAdded];

CREATE TABLE IF NOT EXISTS [PersonAdded] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Type]                     integer,
  [Firstname]                text,
  [Lastname]                 text,
  [Company]                  text,
  [IsRecurrentVisitor]       integer,
  [PictureURL]               text,
  [AccessID]                 text,
  [ServerID]                 text
);


#if_less_than 79
DROP TABLE IF EXISTS [PersonCompany];

CREATE TABLE IF NOT EXISTS [PersonCompany] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Name]                     text
);


#if_less_than 73
DROP TABLE IF EXISTS [PlannedVisit];

CREATE TABLE IF NOT EXISTS [PlannedVisit] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_VisitType]             text,
  [StartDate]                datetime,
  [EndDate]                  datetime,
  [Direction]                integer,
  [FK_Employee]              text,
  [FK_Visitor]               text,
  [CheckInDone]              integer,
  [Data]                     text,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [Property] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [FK_CenterOfOperation]     text,
  [StreetAddress]            text,
  [Active]                   integer,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [PttChannel] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [IsDefault]                integer
);


CREATE TABLE IF NOT EXISTS [Right] (
  [Id]                       text      PRIMARY KEY NOT NULL 
);


#if_less_than 73
DROP TABLE IF EXISTS [Round];

CREATE TABLE IF NOT EXISTS [Round] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [FK_Course]                text      NOT NULL, 
  [StartDate]                datetime  NOT NULL,
  [EndDate]                  datetime,
  [IncompleteReason]         integer,
  [JustifyReason]            text,
  [IsEnded]                  integer   NOT NULL,
  [IsOnHold]                 integer,
  [ServerId]                 text
);


#if_less_than 73
DROP TABLE IF EXISTS [RoundIncident];

CREATE TABLE IF NOT EXISTS [RoundIncident] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [ReportRequested]          integer   NOT NULL, 
  [Name]                 	 text      NOT NULL, 
  [Priority]                 text, 
  [LastUsageCount]           integer,
  [StateDate]                datetime
);


#if_less_than 73
DROP TABLE IF EXISTS [RoundIncidentOccurence];

CREATE TABLE IF NOT EXISTS [RoundIncidentOccurence] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Comment]                  text,
  [FK_PictureFile]           text,
  [FK_VideoFile]             text,
  [FK_AudioFile]             text,
  [FK_RoundIncident]         text      NOT NULL,
  [FK_Round]                 text,
  [FK_GeoLocation]           text,
  [DateUTC]                  datetime  NOT NULL
);


CREATE TABLE IF NOT EXISTS [ScheduledEvent] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                 	 text, 
  [ScheduledDateUTC]         datetime  NOT NULL,
  [Type]                     text      NOT NULL,
  [FK_Object]                text, 
  [IsStarted]                integer   NOT NULL 
);


#if_less_than 73
DROP TABLE IF EXISTS [Session];

CREATE TABLE IF NOT EXISTS [Session] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_Guardroom]             text      NOT NULL,
  [FK_User]                  text      NOT NULL,
  [StartDate]                datetime  NOT NULL,
  [Data]                     text
);


#if_less_than 73
DROP TABLE IF EXISTS [Shift];


#if_less_than 90
DROP TABLE IF EXISTS [ShiftInfo];

CREATE TABLE IF NOT EXISTS [ShiftInfo] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [InShift]                  integer,
  [InPost]                   integer,
  [InBreak]                  integer,
  [InTraining]               integer,
  [InXRay]                   integer,
  [ShiftStatusKnown]         integer,
  [BreakStatusKnown]         integer,
  [ShiftScheduledStart]      datetime,
  [ShiftScheduledEnd]        datetime,
  [ShiftRealStart]           datetime,
  [PostStart]                datetime,
  [BreakStart]               datetime,
  [TrainingStart]            datetime,
  [RotateStart]              datetime,
  [FK_BreakTemplate]         text,
  [ShiftGuardroomID]         text
);


#if_less_than 73
DROP TABLE IF EXISTS [Strip];

CREATE TABLE IF NOT EXISTS [Strip] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Reference]                text,
  [Technology]               text,
  [Type]                     text,
  [ObjectID]                 text,
  [Rank]                     integer,
  [Extra]                    text,
  [StateDate]                datetime
);

CREATE INDEX IF NOT EXISTS [idx_Strip_ObjectID] ON [Strip] (ObjectID);
CREATE INDEX IF NOT EXISTS [idx_Strip_Reference] ON [Strip] (Reference);


#if_less_than 73
DROP TABLE IF EXISTS [StripChecked];

CREATE TABLE IF NOT EXISTS [StripChecked] (
  [Id]                       text      PRIMARY KEY NOT NULL, 
  [FK_Round]                 text,
  [DateUTC]                  datetime  NOT NULL,
  [Rank]                     integer,
  [Data]                     text
);


#if_less_than 73
DROP TABLE IF EXISTS [SynchronizedObject];

CREATE TABLE IF NOT EXISTS [SynchronizedObject] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_User]                  text, 
  [ObjectId]                 text      NOT NULL, 
  [ObjectType]               text      NOT NULL, 
  [CreateDateUTC]            datetime  NOT NULL, 
  [Action]                   text      NOT NULL 
);


CREATE TABLE IF NOT EXISTS [Task] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [State]                    integer   NOT NULL,
  [Type]                     integer   NOT NULL,
  [Category]                 text,
  [Name]                     text,
  [Description]              text,
  [EndComment]               text
);


CREATE TABLE IF NOT EXISTS [TaskStep] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Type]                     integer   NOT NULL,
  [FK_Task]                  text      NOT NULL,
  [Rank]                     integer   NOT NULL,
  [StartDateUTC]             datetime,
  [EndDateUTC]               datetime,
  [Data]                     text
);


CREATE TABLE IF NOT EXISTS [TrainingSession] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [ServerId]                 integer   NOT NULL,
  [StateDate]                datetime,
  [ScheduledDate]            datetime,
  [Status]                   text,
  [ParticipantGUID]          text,
  [OfficerID]                text,
  [OfficerName]              text,
  [CoursesDurationMinutes]   integer,
  [TrainingStartDate]        datetime,
  [TrainingEndDate]          datetime
);


CREATE TABLE IF NOT EXISTS [TrainingSessionContent] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [ServerId]                 integer   NOT NULL,
  [FK_TrainingSession]       text      NOT NULL,
  [CourseID]                 integer,
  [CourseGUID]               text,
  [CourseTitle]              text,
  [CourseDescription]        text,
  [CourseDurationMinutes]    integer,
  [Status]                   integer,
  [StartDate]                datetime,
  [StartNanos]               integer,
  [ActualDurationMinutes]    integer,
  [TrainingUnderstood]       integer,
  [TrainingComment]          text,
  [FK_OfficerSignature]      text,
  [FK_InstructorSignature]   text
);


CREATE TABLE IF NOT EXISTS [TranslationSet] (
  [Id]                       integer   PRIMARY KEY AUTOINCREMENT,
  [Language]                 text      NOT NULL,
  [Region]                   text      NOT NULL,
  [StateDate]                datetime
);


CREATE TABLE IF NOT EXISTS [Translation] (
  [Id]                       integer   PRIMARY KEY AUTOINCREMENT,
  [FK_TranslationSet]        integer   NOT NULL,
  [Key]                      text      NOT NULL,
  [Value]                    text
);

CREATE INDEX IF NOT EXISTS [idx_Translation_FKTranslationSet] ON [Translation] (FK_TranslationSet);


#if_less_than 82
DROP TABLE IF EXISTS [Trip];

CREATE TABLE IF NOT EXISTS [Trip] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Name]                     text,
  [ScheduledStartDate]       datetime,
  [ScheduledEndDate]         datetime,
  [FleetVehicleReportTemplateID] integer,
  [FleetVehicleID]           integer,
  [RealStartDate]            datetime,
  [RealEndDate]              datetime,
  [CheckVehicleStart]        integer,
  [CheckVehicleEnd]          integer,
  [CheckKeysStart]           integer,
  [CheckMaterialStart]       integer,
  [CheckMaterialEnd]         integer,
  [AbortReason]              text,
  [Data]                     text
);


CREATE TABLE IF NOT EXISTS [TripContract] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [Number]                   text,
  [TaskID]                   text
);


CREATE TABLE IF NOT EXISTS [TripContractKey] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_TripContract]          integer,
  [Number]                   text,
  [Name]                     text,
  [Type]                     integer,
  [ValidFrom]                datetime,
  [ValidTo]                  datetime,
  [LocationName]             text,
  [LocationStreet]           text,
  [LocationStreetNr]         text,
  [LocationZipCode]          text,
  [LocationCity]             text,
  [LocationLatitude]         double,
  [LocationLongitude]        double,
  [VaultNumber]              text,
  [VaultCabinetNr]           integer,
  [VaultKeyPosition]         integer,
  [VaultName]                text
);


#if_less_than 82
DROP TABLE IF EXISTS [TripJob];

CREATE TABLE IF NOT EXISTS [TripJob] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Trip]                  integer,
  [FK_TripContract]          integer,
  [Name]                     text,
  [Rank]                     integer,
  [Type]                     text,
  [Description]              text,
  [CustomerName]             text,
  [SiteName]                 text,
  [SiteAddress]              text,
  [SitePhone]                text,
  [SiteLatitude]             double,
  [SiteLongitude]            double,
  [AlarmCentral]             text,
  [AlarmTypeFlags]           integer,
  [AlarmSystem]              text,
  [AlarmLocation]            text,
  [AlarmKeyboardLocation]    text,
  [AlarmCodeArm]             text,
  [AlarmCodeDisarm]          text,
  [AlarmResetCode]           text,
  [AlarmAccessCode]          text,
  [ScheduledStartDate]       datetime,
  [ScheduledEndDate]         datetime,
  [RealStartDate]            datetime,
  [RealEndDate]              datetime,
  [LastPerformingStartDate]  datetime,
  [DurationSeconds]          integer,
  [Status]                   integer,
  [ScheduledOccurrenceCount] integer,
  [DoneOccurrenceCount]      integer,
  [AlarmDescription]         text,
  [AlarmReceiveDate]         datetime,
  [AlarmReceivedBy]          text,
  [Data]                     text
);


CREATE TABLE IF NOT EXISTS [TripMaterial] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Trip]                  integer,
  [Name]                     text,
  [Rank]                     integer,
  [CheckStart]               integer,
  [CheckEnd]                 integer
);


#if_less_than 73
DROP TABLE IF EXISTS [UserProfileDetail];


CREATE TABLE IF NOT EXISTS [UserRightCache] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Data]                     text
);


CREATE TABLE IF NOT EXISTS [UserSchedule] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [ScheduledStartDate]       datetime,
  [ScheduledEndDate]         datetime,
  [RealStartDate]            datetime,
  [RealEndDate]              datetime,
  [GuardroomName]            text,
  [FunctionName]             text
);


CREATE TABLE IF NOT EXISTS [Vendor] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [Active]                   integer,
  [StateDate]                datetime
);


#if_less_than 79
DROP TABLE IF EXISTS [Visit];

CREATE TABLE IF NOT EXISTS [Visit] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_VisitType]             text,
  [StartDate]                datetime,
  [Direction]                integer,
  [FK_Visitor]               text,
  [FK_Employee]              text,
  [CustomFieldValue]         text,
  [Data]                     text,
  [StateDate]                datetime
);


#if_less_than 79
DROP TABLE IF EXISTS [VisitIdentifier];

CREATE TABLE IF NOT EXISTS [VisitIdentifier] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_Visit]                 text      NOT NULL,
  [Key]                      text,
  [Value]                    text
);

CREATE INDEX IF NOT EXISTS [idx_VisitIdentifier_Key_Value] ON [VisitIdentifier] ( Key, Value );


#if_less_than 79
DROP TABLE IF EXISTS [VisitAdded];

CREATE TABLE IF NOT EXISTS [VisitAdded] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_VisitType]             text,
  [StartDate]                datetime,
  [EndDate]                  datetime,
  [Direction]                integer,
  [FK_Visitor]               text,
  [FK_VisitorAdded]          integer,
  [FK_PlannedVisit]          text,
  [CustomFieldValue]         text,
  [Data]                     text,
  [ServerID]                 text
);


#if_less_than 79
DROP TABLE IF EXISTS [VisitAddedIdentifier];

CREATE TABLE IF NOT EXISTS [VisitAddedIdentifier] (
  [Id]                       integer   PRIMARY KEY NOT NULL,
  [FK_VisitAdded]            integer   NOT NULL,
  [Key]                      text,
  [Value]                    text
);

CREATE INDEX IF NOT EXISTS [idx_VisitAddedIdentifier_Key_Value] ON [VisitAddedIdentifier] ( Key, Value );


#if_less_than 79
DROP TABLE IF EXISTS [VisitPermission];

CREATE TABLE IF NOT EXISTS [VisitPermission] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_VisitType]             text,
  [FK_Person]                text,
  [StartDate]                datetime,
  [EndDate]                  datetime,
  [StateDate]                datetime
);

CREATE INDEX IF NOT EXISTS [idx_VisitPermission_FKVisitType_FKPerson] ON [VisitPermission] (FK_VisitType, FK_Person);


#if_less_than 78
DROP TABLE IF EXISTS [VisitType];

CREATE TABLE IF NOT EXISTS [VisitType] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [Name]                     text,
  [IsGroup]                  integer,
  [IsRequireValidation]      integer,
  [IsAccompanied]            integer,
  [IsBadgePrinting]          integer,
  [IsPermissionRequired]     integer,
  [TrackOut]                 integer,
  [UseOutComment]            integer,
  [Memo]                     text,
  [LanguageID]               text,
  [Active]                   integer,
  [StateDate]                datetime
);


#if_less_than 78
DROP TABLE IF EXISTS [VisitSubForm];

CREATE TABLE IF NOT EXISTS [VisitSubForm] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_VisitType]             text      NOT NULL,
  [Rank]                     integer,
  [Type]                     text,
  [IsMandatory]              integer,
  [IsVisitorName]            integer,
  [IsVisitorPicture]         integer,
  [IsVisitorCompany]         integer,
  [CustomName]               text,
  [ForCheckout]              integer
);

CREATE INDEX IF NOT EXISTS [idx_VisitSubForm_FKVisitType] ON [VisitSubForm] (FK_VisitType);


#if_less_than 78
DROP TABLE IF EXISTS [VisitCustomField];

CREATE TABLE IF NOT EXISTS [VisitCustomField] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_VisitSubForm]          text      NOT NULL,
  [Name]                     text,
  [IsMandatory]              integer,
  [Rank]                     integer,
  [Type]                     text,
  [ImageCanvasUrl]           text,
  [FK_CustomList]            text,
  [Tag]                      text
);

CREATE INDEX IF NOT EXISTS [idx_VisitCustomField_FKVisitSubForm] ON [VisitCustomField] (FK_VisitSubForm);


#if_less_than 78
DROP TABLE IF EXISTS [VisitCustomFieldItem];

CREATE TABLE IF NOT EXISTS [VisitCustomFieldItem] (
  [Id]                       text      PRIMARY KEY NOT NULL,
  [FK_VisitCustomField]      text      NOT NULL,
  [Caption]                  text,
  [Rank]                     integer
);

CREATE INDEX IF NOT EXISTS [idx_VisitCustomFieldItem_FKVisitCustomField] ON [VisitCustomFieldItem] (FK_VisitCustomField);
