-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table ''AccessDocuments''
CREATE TABLE [dbo].[AccessDocuments] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [ObjectId] uniqueidentifier  NOT NULL
);


-- Creating table ''AccessTasks''
CREATE TABLE [dbo].[AccessTasks] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [ObjectId] uniqueidentifier  NOT NULL
);


-- Creating table ''Activities''
CREATE TABLE [dbo].[Activities] (
    [Id] uniqueidentifier  NOT NULL,
    [Text] nvarchar(max)  NULL,
    [Type] int  NULL
);


-- Creating table ''Reports''
CREATE TABLE [dbo].[Reports] (
    [Id] uniqueidentifier  NOT NULL,
    [Type] int  NULL,
    [Text] nvarchar(max)  NULL
);


-- Creating table ''Settings''
CREATE TABLE [dbo].[Settings] (
    [Id] uniqueidentifier  NOT NULL,
    [UniqueName] nvarchar(50)  NULL,
    [Type] nvarchar(50)  NULL
);


-- Creating table ''Tasks''
CREATE TABLE [dbo].[Tasks] (
    [Id] uniqueidentifier  NOT NULL,
    [Text] nvarchar(max)  NULL,
    [ExecutionDate] datetime  NULL
);


-- Creating table ''PrismReports''
CREATE TABLE [dbo].[PrismReports] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NULL,
    [Category] nvarchar(max)  NULL
);


-- Creating table ''Journals''
CREATE TABLE [dbo].[Journals] (
    [Id] uniqueidentifier  NOT NULL,
    [DocumentId] uniqueidentifier  NOT NULL,
    [DocumentValue] varchar(max)  NOT NULL
);


-- Creating table ''Templates''
CREATE TABLE [dbo].[Templates] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NULL,
    [Description] nvarchar(max)  NULL
);


-- Creating table ''Histories''
CREATE TABLE [dbo].[Histories] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [GroupId] uniqueidentifier  NOT NULL,
    [OperationId] nvarchar(max)  NOT NULL
);


-- Creating table ''Employees''
CREATE TABLE [dbo].[Employees] (
    [Id] uniqueidentifier  NOT NULL,
    [CreatedDate] datetime  NOT NULL,
    [ModifiedDate] datetime  NOT NULL
);


-- Creating table ''Units''
CREATE TABLE [dbo].[Units] (
    [Id] uniqueidentifier  NOT NULL,
    [CreatedDate] datetime  NOT NULL,
    [ModifiedDate] datetime  NOT NULL
);


-- Creating table ''PermissionKeys''
CREATE TABLE [dbo].[PermissionKeys] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Key] nvarchar(max)  NULL,
    [Type] int  NOT NULL
);


-- Creating table ''PermissionValues''
CREATE TABLE [dbo].[PermissionValues] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [PermissionKey] nvarchar(max)  NULL,
    [Value] nvarchar(max)  NULL
);


-- Creating table ''EmployeePermissions''
CREATE TABLE [dbo].[EmployeePermissions] (
    [EmployeeId] uniqueidentifier  NOT NULL,
    [PermissionKey] nvarchar(max)  NOT NULL,
    [PermissionValue] nvarchar(max)  NOT NULL
);


-- Creating table ''Dictionaries''
CREATE TABLE [dbo].[Dictionaries] (
    [Id] uniqueidentifier  NOT NULL,
    [code] nvarchar(max)  NULL,
    [Name] nvarchar(max)  NULL
);


-- Creating table ''Documents''
CREATE TABLE [dbo].[Documents] (
    [Id] uniqueidentifier  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [IsAdministrativeUse] bit  NOT NULL
);


-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table ''AccessDocuments''
ALTER TABLE [dbo].[AccessDocuments]
ADD CONSTRAINT [PK_AccessDocuments]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''AccessTasks''
ALTER TABLE [dbo].[AccessTasks]
ADD CONSTRAINT [PK_AccessTasks]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Activities''
ALTER TABLE [dbo].[Activities]
ADD CONSTRAINT [PK_Activities]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Reports''
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [PK_Reports]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Settings''
ALTER TABLE [dbo].[Settings]
ADD CONSTRAINT [PK_Settings]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Tasks''
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [PK_Tasks]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''PrismReports''
ALTER TABLE [dbo].[PrismReports]
ADD CONSTRAINT [PK_PrismReports]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Journals''
ALTER TABLE [dbo].[Journals]
ADD CONSTRAINT [PK_Journals]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Templates''
ALTER TABLE [dbo].[Templates]
ADD CONSTRAINT [PK_Templates]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Histories''
ALTER TABLE [dbo].[Histories]
ADD CONSTRAINT [PK_Histories]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Employees''
ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [PK_Employees]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Units''
ALTER TABLE [dbo].[Units]
ADD CONSTRAINT [PK_Units]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''PermissionKeys''
ALTER TABLE [dbo].[PermissionKeys]
ADD CONSTRAINT [PK_PermissionKeys]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''PermissionValues''
ALTER TABLE [dbo].[PermissionValues]
ADD CONSTRAINT [PK_PermissionValues]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''EmployeePermissions''
ALTER TABLE [dbo].[EmployeePermissions]
ADD CONSTRAINT [PK_EmployeePermissions]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Dictionaries''
ALTER TABLE [dbo].[Dictionaries]
ADD CONSTRAINT [PK_Dictionaries]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- Creating primary key on [Id] in table ''Documents''
ALTER TABLE [dbo].[Documents]
ADD CONSTRAINT [PK_Documents]
    PRIMARY KEY CLUSTERED ([Id] ASC);


-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ObjectId] in table ''AccessTasks''
ALTER TABLE [dbo].[AccessTasks]
ADD CONSTRAINT [FK_AccessTasksObjectId_TasksId]
    FOREIGN KEY ([ObjectId])
    REFERENCES [dbo].[Tasks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_AccessTasksObjectId_TasksId''
CREATE INDEX [IX_FK_AccessTasksObjectId_TasksId]
ON [dbo].[AccessTasks]
    ([ObjectId]);


-- Creating foreign key on [ActivityId] in table ''Tasks''
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [FK_Tasks_Activities]
    FOREIGN KEY ([ActivityId])
    REFERENCES [dbo].[Activities]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_Tasks_Activities''
CREATE INDEX [IX_FK_Tasks_Activities]
ON [dbo].[Tasks]
    ([ActivityId]);


-- Creating foreign key on [TaskId] in table ''Reports''
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [FK_ReportsTaskId_TasksId]
    FOREIGN KEY ([TaskId])
    REFERENCES [dbo].[Tasks]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_ReportsTaskId_TasksId''
CREATE INDEX [IX_FK_ReportsTaskId_TasksId]
ON [dbo].[Reports]
    ([TaskId]);


-- Creating foreign key on [PositionId] in table ''Employees''
ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [FK_EmployeesPositionId_UnitsId]
    FOREIGN KEY ([PositionId])
    REFERENCES [dbo].[Units]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_EmployeesPositionId_UnitsId''
CREATE INDEX [IX_FK_EmployeesPositionId_UnitsId]
ON [dbo].[Employees]
    ([PositionId]);


-- Creating foreign key on [EmployeeId] in table ''Units''
ALTER TABLE [dbo].[Units]
ADD CONSTRAINT [FK_UnitsEmployeeId_EmployeesId]
    FOREIGN KEY ([EmployeeId])
    REFERENCES [dbo].[Employees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_UnitsEmployeeId_EmployeesId''
CREATE INDEX [IX_FK_UnitsEmployeeId_EmployeesId]
ON [dbo].[Units]
    ([EmployeeId]);


-- Creating foreign key on [ParentId] in table ''Units''
ALTER TABLE [dbo].[Units]
ADD CONSTRAINT [FK_UnitsParentId_UnitsId]
    FOREIGN KEY ([ParentId])
    REFERENCES [dbo].[Units]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_UnitsParentId_UnitsId''
CREATE INDEX [IX_FK_UnitsParentId_UnitsId]
ON [dbo].[Units]
    ([ParentId]);


-- Creating foreign key on [ParentId] in table ''Dictionaries''
ALTER TABLE [dbo].[Dictionaries]
ADD CONSTRAINT [FK_Dictionaries_ParenId_Dictionaries]
    FOREIGN KEY ([ParentId])
    REFERENCES [dbo].[Dictionaries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_Dictionaries_ParenId_Dictionaries''
CREATE INDEX [IX_FK_Dictionaries_ParenId_Dictionaries]
ON [dbo].[Dictionaries]
    ([ParentId]);


-- Creating foreign key on [ObjectId] in table ''AccessDocuments''
ALTER TABLE [dbo].[AccessDocuments]
ADD CONSTRAINT [FK_AccessDocumentsObjectId_DocumentsId]
    FOREIGN KEY ([ObjectId])
    REFERENCES [dbo].[Documents]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_AccessDocumentsObjectId_DocumentsId''
CREATE INDEX [IX_FK_AccessDocumentsObjectId_DocumentsId]
ON [dbo].[AccessDocuments]
    ([ObjectId]);


-- Creating foreign key on [DocumentId] in table ''Activities''
ALTER TABLE [dbo].[Activities]
ADD CONSTRAINT [FK_Activities_DocumentId_Documents]
    FOREIGN KEY ([DocumentId])
    REFERENCES [dbo].[Documents]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_Activities_DocumentId_Documents''
CREATE INDEX [IX_FK_Activities_DocumentId_Documents]
ON [dbo].[Activities]
    ([DocumentId]);


-- Creating foreign key on [TemplateId] in table ''Documents''
ALTER TABLE [dbo].[Documents]
ADD CONSTRAINT [FK_Documents_Template]
    FOREIGN KEY ([TemplateId])
    REFERENCES [dbo].[Templates]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_Documents_Template''
CREATE INDEX [IX_FK_Documents_Template]
ON [dbo].[Documents]
    ([TemplateId]);


-- Creating foreign key on [DocumentId] in table ''Reports''
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [FK_Reports_DocumentId_Documents]
    FOREIGN KEY ([DocumentId])
    REFERENCES [dbo].[Documents]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_Reports_DocumentId_Documents''
CREATE INDEX [IX_FK_Reports_DocumentId_Documents]
ON [dbo].[Reports]
    ([DocumentId]);


-- Creating foreign key on [DocumentId] in table ''Tasks''
ALTER TABLE [dbo].[Tasks]
ADD CONSTRAINT [FK_Tasks_Documents]
    FOREIGN KEY ([DocumentId])
    REFERENCES [dbo].[Documents]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY ''FK_Tasks_Documents''
CREATE INDEX [IX_FK_Tasks_Documents]
ON [dbo].[Tasks]
    ([DocumentId]);


-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------