CREATE TABLE [dbo].[Task] (
    [Id]              INT              IDENTITY (1, 1) NOT NULL,
    [DocumentId]      INT              NOT NULL,
    [StatusId]        INT              NOT NULL,
    [Created]         DATETIME         NOT NULL,
    [Started]         DATETIME         NULL,
    [Finished]        DATETIME         NULL,
    [WorkflowId]      INT              NOT NULL,
    [StageId]         INT              NOT NULL,
    [TaskPerformerId] INT              NOT NULL,
    [PrevTaskId]      INT              NULL,
    [NextTaskId]      INT              NULL,
    [RoleId]          UNIQUEIDENTIFIER NULL,
    [UserId]          UNIQUEIDENTIFIER NULL,
    [Comment]         NVARCHAR (1000)  NULL,
    CONSTRAINT [PK_dbo.Task] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.Task_dbo.Task_NextTaskId] FOREIGN KEY ([NextTaskId]) REFERENCES [dbo].[Task] ([Id]),
    CONSTRAINT [FK_dbo.Task_dbo.Task_PrevTaskId] FOREIGN KEY ([PrevTaskId]) REFERENCES [dbo].[Task] ([Id]),
    CONSTRAINT [FK_dbo.Task_dbo.Workflow_WorkflowId] FOREIGN KEY ([WorkflowId]) REFERENCES [dbo].[Workflow] ([Id]),
    CONSTRAINT [FK_dbo.Task_dbo.Stage_StageId] FOREIGN KEY ([StageId]) REFERENCES [dbo].[Stage] ([Id]),
    CONSTRAINT [FK_dbo.Task_dbo.TaskStatus_StatusId] FOREIGN KEY ([StatusId]) REFERENCES [dbo].[TaskStatus] ([Id]),
    CONSTRAINT [FK_dbo.Task_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]),
    CONSTRAINT [FK_dbo.Task_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]),
    CONSTRAINT [FK_dbo.Task_dbo.Document_DocumentId] FOREIGN KEY ([DocumentId]) REFERENCES [dbo].[Document] ([Id])
);


GO
CREATE NONCLUSTERED INDEX [IX_DocumentId]
    ON [dbo].[Task]([DocumentId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_StatusId]
    ON [dbo].[Task]([StatusId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_WorkflowId]
    ON [dbo].[Task]([WorkflowId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_StageId]
    ON [dbo].[Task]([StageId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_PrevTaskId]
    ON [dbo].[Task]([PrevTaskId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_NextTaskId]
    ON [dbo].[Task]([NextTaskId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_RoleId]
    ON [dbo].[Task]([RoleId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[Task]([UserId] ASC);





CREATE TABLE [dbo].[Document] (
    [Id]                  INT              IDENTITY (1, 1) NOT NULL,
    [Data]                NVARCHAR (MAX)   NULL,
    [Created]             DATETIME         NOT NULL,
    [IsCurrentVersion]    BIT              NOT NULL,
    [ChronicalDocumentId] INT              NULL,
    [DocumentStatusId]    INT              NOT NULL,
    [SourceSystemId]      INT              NULL,
    [DocumentTemplateId]  INT              NOT NULL,
    [AuthorId]            UNIQUEIDENTIFIER NOT NULL,
    [LastUserId]          UNIQUEIDENTIFIER NULL,
    [RejectionTargetId]   UNIQUEIDENTIFIER NULL,
    [AuditBNMId]          BIGINT           NOT NULL,
    [AuditDetailId]       BIGINT           NOT NULL,
    [PeriodType_Id]       INT              NULL,
    CONSTRAINT [PK_dbo.Document] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.Document_dbo.AspNetUsers_AuthorId] FOREIGN KEY ([AuthorId]) REFERENCES [dbo].[AspNetUsers] ([Id]),
    CONSTRAINT [FK_dbo.Document_dbo.AspNetUsers_RejectionTargetId] FOREIGN KEY ([RejectionTargetId]) REFERENCES [dbo].[AspNetUsers] ([Id]),
    CONSTRAINT [FK_dbo.Document_dbo.DocumentTemplate_DocumentTemplateId] FOREIGN KEY ([DocumentTemplateId]) REFERENCES [dbo].[DocumentTemplate] ([Id]),
    CONSTRAINT [FK_dbo.Document_dbo.PeriodType_PeriodType_Id] FOREIGN KEY ([PeriodType_Id]) REFERENCES [dbo].[PeriodType] ([Id]),
    CONSTRAINT [FK_dbo.Document_dbo.SourceSystem_SourceSystemId] FOREIGN KEY ([SourceSystemId]) REFERENCES [dbo].[SourceSystem] ([Id]),
    CONSTRAINT [FK_dbo.Document_dbo.DocumentStatus_DocumentStatusId] FOREIGN KEY ([DocumentStatusId]) REFERENCES [dbo].[DocumentStatus] ([Id]),
    CONSTRAINT [FK_dbo.Document_dbo.AspNetUsers_LastUserId] FOREIGN KEY ([LastUserId]) REFERENCES [dbo].[AspNetUsers] ([Id])
);


GO
CREATE NONCLUSTERED INDEX [IX_DocumentStatusId]
    ON [dbo].[Document]([DocumentStatusId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_SourceSystemId]
    ON [dbo].[Document]([SourceSystemId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_DocumentTemplateId]
    ON [dbo].[Document]([DocumentTemplateId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_AuthorId]
    ON [dbo].[Document]([AuthorId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_LastUserId]
    ON [dbo].[Document]([LastUserId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_RejectionTargetId]
    ON [dbo].[Document]([RejectionTargetId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_PeriodType_Id]
    ON [dbo].[Document]([PeriodType_Id] ASC);

GO
CREATE NONCLUSTERED INDEX [AuditIDIndex]
    ON [dbo].[Document]([AuditBNMId] ASC, [AuditDetailId] ASC);



CREATE TABLE [dbo].[rpt_Audit_BNM] (
    [IncrementalID]            INT            IDENTITY (1, 1) NOT NULL,
    [AuditBNMID]               BIGINT         NOT NULL,
    [AuditDetailID]            BIGINT         NOT NULL,
    [RowID_in_tbl_Audit]       BIGINT         NULL,
    [recno_in_Live]            BIGINT         NULL,
    [RowNumber]                INT            NULL,
    [FiCode]                   VARCHAR (9)    NULL,
    [AppSysCode]               VARCHAR (10)   NULL,
    [NameOfBank]               VARCHAR (200)  NULL,
    [ReportingPeriod]          VARCHAR (20)   NULL,
    [DateOfOriginalSubmission] VARCHAR (20)   NULL,
    [DateOfResubmission]       VARCHAR (20)   NULL,
    [NameOfFile]               VARCHAR (10)   NULL,
    [BorrowerName]             VARCHAR (200)  NULL,
    [IDNumber]                 VARCHAR (20)   NULL,
    [DateOf_BirthOrReg]        VARCHAR (8)    NULL,
    [Entity]                   VARCHAR (2)    NULL,
    [ApplicationRefNumber]     VARCHAR (100)  NULL,
    [MasterAccountNumber]      VARCHAR (30)   NULL,
    [SubAccountNumber]         VARCHAR (30)   NULL,
    [DataItem]                 VARCHAR (250)  NULL,
    [ItemOldValue]             VARCHAR (1000) NULL,
    [ItemNewValue]             VARCHAR (1000) NULL,
    [ItemJustification]        VARCHAR (MAX)  NULL
);


GO
CREATE NONCLUSTERED INDEX [AuditIDIndex]
    ON [dbo].[rpt_Audit_BNM]([AuditBNMID] ASC, [AuditDetailID] ASC);