Basic ASP.NET Identity 2 DDL

USE master
GO

DROP DATABASE TechnicalMarineSolutions
GO

CREATE DATABASE TechnicalMarineSolutions ON PRIMARY
(
    NAME = N'TechnicalMarineSolutions',
    FILENAME = N'D:\Databases\TechnicalMarineSolutions.mdf',
    SIZE = 10240KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10240KB
)
LOG ON
(
    NAME = N'TechnicalMarineSolutions_log',
    FILENAME = N'D:\Databases\Logs\TechnicalMarineSolutions_log.ldf',
    SIZE = 10240KB,
    MAXSIZE = 2048GB,
    FILEGROWTH = 10%
)
GO

-- Set the compatability to Sql Server 2014
ALTER DATABASE TechnicalMarineSolutions
SET COMPATIBILITY_LEVEL = 120
GO

USE TechnicalMarineSolutions
GO

-- Enable full text search functionality on the database
IF (FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1)
BEGIN
    EXEC sp_fulltext_database @action = 'enable'
END
GO

CREATE SCHEMA [Entity]
GO

CREATE SCHEMA [Schema]
GO

CREATE SCHEMA [User]
GO

CREATE SCHEMA [Work]
GO

CREATE FULLTEXT CATALOG [Entity]
GO

CREATE FULLTEXT CATALOG [Schema]
GO

CREATE FULLTEXT CATALOG [User]
GO

CREATE FULLTEXT CATALOG [Work]
GO

-- Entity Framework migration history for automatic code-based database migrations
CREATE TABLE [Entity].__MigrationHistory
(
    MigrationId                             NVARCHAR(150)                                           NOT NULL,
    ContextKey                              NVARCHAR(300)                                           NOT NULL,
    Model                                   VARBINARY(MAX)                                          NOT NULL,
    ProductVersion                          NVARCHAR(32)                                            NOT NULL,
    CONSTRAINT PK___MigrationHistory PRIMARY KEY CLUSTERED (MigrationId ASC, ContextKey ASC)
)
GO

CREATE FULLTEXT INDEX ON [Entity].__MigrationHistory (MigrationId Language 1033, ContextKey Language 1033, ProductVersion Language 1033) KEY INDEX PK___MigrationHistory ON [Entity] WITH STOPLIST = SYSTEM
GO

-- The actual roles table
CREATE TABLE [User].AspNetRoles
(
    Id                                      NVARCHAR(128)                                           NOT NULL,
    Name                                    NVARCHAR(256)                                           NOT NULL,
    CONSTRAINT PK_AspNetRoles PRIMARY KEY CLUSTERED (Id ASC),
    CONSTRAINT UK_AspNetRoles_Name UNIQUE (Name ASC)
)
GO

CREATE FULLTEXT INDEX ON [User].AspNetRoles (Id Language 1033, Name Language 1033) KEY INDEX PK_AspNetRoles ON [User] WITH STOPLIST = SYSTEM
GO

INSERT INTO [User].AspNetRoles VALUES ('SYSTEM', 'System Administrator'),
                                      ('DEV','Developer'),
                                      ('ADMIN','Administrator'),
                                      ('MOD','Moderator'),
                                      ('USER','Registered User'),
                                      ('PREMIUM', 'Subscribed User')
GO

-- The actual user accounts table
CREATE TABLE [User].AspNetUsers
(
    Id                                      NVARCHAR(128)                                           NOT NULL,
    Email                                   NVARCHAR(256)                                           NULL,
    EmailConfirmed                          BIT                                                     NOT NULL,
    PasswordHash                            NVARCHAR(MAX)                                           NULL,
    SecurityStamp                           NVARCHAR(MAX)                                           NULL,
    PhoneNumber                             NVARCHAR(MAX)                                           NULL,
    PhoneNumberConfirmed                    BIT                                                     NOT NULL,
    TwoFactorEnabled                        BIT                                                     NOT NULL,
    LockoutENDDateUtc                       DATETIME                                                NULL,
    LockoutEnabled                          BIT                                                     NOT NULL,
    AccessFailedCount                       INT                                                     NOT NULL,
    UserName                                NVARCHAR(256)                                           NOT NULL,
    CONSTRAINT PK_AspNetUsers PRIMARY KEY CLUSTERED (Id ASC),
    CONSTRAINT UK_AspNetUsers_UserName UNIQUE (UserName ASC)
)
GO

CREATE INDEX IDX_AspNetUsers_Email_UserName ON [User].AspNetUsers (Email ASC, UserName ASC)
GO

CREATE INDEX IDX_AspNetUsers_Confirmation ON [User].AspNetUsers (LockoutEnabled DESC, EmailConfirmed ASC, PhoneNumberConfirmed ASC)
GO

CREATE FULLTEXT INDEX ON [User].AspNetUsers (Email Language 1033, PhoneNumber Language 1033, Username Language 1033) KEY INDEX PK_AspNetUsers ON [User] WITH STOPLIST = SYSTEM
GO

-- Identity claims that are associated with the user. Part of the Identity schema. Currently no plans for use but required as it would be auto-generated otherwise
CREATE TABLE [User].AspNetUserClaims
(
    Id                                      INT                         IDENTITY(1,1)               NOT NULL,
    UserId                                  NVARCHAR(128)                                           NOT NULL,
    ClaimType                               NVARCHAR(MAX)                                           NULL,
    ClaimValue                              NVARCHAR(MAX)                                           NULL,
    CONSTRAINT PK_AspNetUserClaims PRIMARY KEY CLUSTERED (Id ASC),
    CONSTRAINT FK_AspNetUserClaims_AspNetUsers_UserId FOREIGN KEY(UserId) REFERENCES [User].AspNetUsers (Id) ON DELETE CASCADE
)
GO

CREATE INDEX IDX_AspNetUserClaims_UserId ON [User].AspNetUserClaims (UserId ASC)
GO

CREATE FULLTEXT INDEX ON [User].AspNetUserClaims (UserId Language 1033, ClaimType Language 1033, ClaimValue Language 1033) KEY INDEX PK_AspNetUserClaims ON [User] WITH STOPLIST = SYSTEM
GO

-- This table stores records relevant to social-network account logins that are associated with the user account
CREATE TABLE [User].AspNetUserLogins
(
    LoginProvider                           NVARCHAR(128)                                           NOT NULL,
    ProviderKey                             NVARCHAR(128)                                           NOT NULL,
    UserId                                  NVARCHAR(128)                                           NOT NULL,
    CONSTRAINT PK_AspNetUserLogins PRIMARY KEY CLUSTERED (LoginProvider ASC, ProviderKey ASC, UserId ASC),
    CONSTRAINT FK_AspNetUserLogins_AspNetUsers_UserId FOREIGN KEY(UserId) REFERENCES [User].AspNetUsers (Id) ON DELETE CASCADE
)
GO

CREATE INDEX IDX_AspNetUserLogins_UserId ON [User].AspNetUserLogins (UserId ASC)
GO

CREATE FULLTEXT INDEX ON [User].AspNetUserLogins (LoginProvider Language 1033, ProviderKey Language 1033, UserId Language 1033) KEY INDEX PK_AspNetUserLogins ON [User] WITH STOPLIST = SYSTEM
GO

-- This table stores the records that indicate which user is in which role
CREATE TABLE [User].AspNetUserRoles
(
    UserId                                  NVARCHAR(128)                                           NOT NULL,
    RoleId                                  NVARCHAR(128)                                           NOT NULL,
    CONSTRAINT PK_AspNetUserRoles PRIMARY KEY CLUSTERED (UserId ASC, RoleId ASC),
    CONSTRAINT FK_AspNetUserRoles_AspNetRoles_RoleId FOREIGN KEY(RoleId) REFERENCES [User].AspNetRoles (Id) ON DELETE CASCADE,
    CONSTRAINT FK_AspNetUserRoles_AspNetUsers_UserId FOREIGN KEY(UserId) REFERENCES [User].AspNetUsers (Id) ON DELETE CASCADE,
    CONSTRAINT UK_AspNetUserRoles_UserId_RoleId UNIQUE (UserId ASC, RoleId ASC)
)
GO

CREATE INDEX IDX_AspNetUserRoles_UserId ON [User].AspNetUserRoles (UserId ASC)
GO

CREATE INDEX IDX_AspNetUserRoles_RoleId ON [User].AspNetUserRoles (RoleId ASC)
GO

CREATE FULLTEXT INDEX ON [User].AspNetUserRoles (UserId Language 1033, RoleId Language 1033) KEY INDEX PK_AspNetUserRoles ON [User] WITH STOPLIST = SYSTEM
GO

 -- Info specific to the user
CREATE TABLE [User].UserInfo
(
    Id                                      BIGINT                      IDENTITY(1,1)               NOT NULL,
    UserId                                  NVARCHAR(128)                                           NOT NULL,
    DisplayName                             NVARCHAR(128)                                           NOT NULL,
    CONSTRAINT PK_UserInfo PRIMARY KEY CLUSTERED (Id DESC),
    CONSTRAINT FK_UserInfo_AspNetUsers_UserId FOREIGN KEY(UserId) REFERENCES [User].AspNetUsers (Id) ON DELETE CASCADE,
    CONSTRAINT UK_UserInfo_UserId UNIQUE NONCLUSTERED (UserId ASC)
)
GO

CREATE INDEX IDX_UserInfo_UserId ON [User].UserInfo (UserId ASC)
GO

CREATE FULLTEXT INDEX ON [User].UserInfo (UserId Language 1033, DisplayName Language 1033) KEY INDEX PK_UserInfo ON [User] WITH STOPLIST = SYSTEM
GO

-- Info specific to the person. Multiple people can be associated with a single user. IE: Family members
CREATE TABLE [User].PersonalInfo
(
    Id                                      BIGINT                      IDENTITY(1,1)               NOT NULL,
    RecordStatus                            BIGINT                                                  NOT NULL,
    UserInfoId                              BIGINT                                                  NOT NULL,
    FirstName                               NVARCHAR(128)                                           NOT NULL,
    MiddleName                              NVARCHAR(128)                                           NULL,
    LastName                                NVARCHAR(128)                                           NOT NULL,
    Hometown                                NVARCHAR(128)                                           NULL,
    CurrentTown                             NVARCHAR(128)                                           NULL,
    BirthDate                               DATETIME                                                NULL,
    CONSTRAINT PK_PersonalInfo PRIMARY KEY CLUSTERED (Id DESC),
    CONSTRAINT FK_PersonalInfo_UserInfo_UserInfoId FOREIGN KEY(UserInfoId) REFERENCES [User].UserInfo (Id) ON DELETE CASCADE,
    CONSTRAINT UK_PersonalInfo_UserInfoId UNIQUE NONCLUSTERED (UserInfoId DESC)
)
GO

CREATE INDEX IDX_PersonalInfo_UserInfoId ON [User].PersonalInfo (UserInfoId DESC)
GO

CREATE FULLTEXT INDEX ON [User].PersonalInfo (FirstName Language 1033, MiddleName Language 1033, LastName Language 1033, Hometown Language 1033, CurrentTown Language 1033) KEY INDEX PK_PersonalInfo ON [User] WITH STOPLIST = SYSTEM
GO

-- Each person can have various types of postal addresses
CREATE TABLE [User].PostalAddress
(
    Id                                      BIGINT                      IDENTITY(1,1)               NOT NULL,
    PersonalInfoId                          BIGINT                                                  NOT NULL,
    AddressTypeId                           BIGINT                                                  NOT NULL,
    RecordStatus                            BIGINT                                                  NOT NULL,
    Recipient                               NVARCHAR(128)                                           NULL,
    Attention                               NVARCHAR(128)                                           NULL,
    Address1                                NVARCHAR(64)                                            NOT NULL,
    Address2                                NVARCHAR(64)                                            NULL,
    City                                    NVARCHAR(64)                                            NOT NULL,
    [State]                                 NVARCHAR(2)                                             NOT NULL,
    PostalCode                              NVARCHAR(10)                                            NOT NULL,
    CONSTRAINT PK_PostalAddress PRIMARY KEY CLUSTERED (Id ASC),
    CONSTRAINT FK_PostalAddress_PersonalInfo_PersonalInfoId FOREIGN KEY (PersonalInfoId) REFERENCES [User].PersonalInfo (Id) ON DELETE CASCADE
)
GO

CREATE INDEX IDX_PostalAddress_PersonalInfoId ON [User].PostalAddress (PersonalInfoId DESC)
GO

CREATE INDEX IDX_PostalAddress_AddressTypeId ON [User].PostalAddress (AddressTypeId ASC)
GO

CREATE FULLTEXT INDEX ON [User].PostalAddress (Recipient Language 1033, Attention Language 1033, Address1 Language 1033, Address2 Language 1033, City Language 1033, [State] Language 1033, PostalCode Language 1033) KEY INDEX PK_PostalAddress ON [User] WITH STOPLIST = SYSTEM
GO

-- TODO: CREATE INDEXES FOR REMAINING TABLES

-- Projects that are or have been in the works
CREATE TABLE [Work].Project
(
    Id                                      BIGINT                      IDENTITY(1,1)               NOT NULL,
    RecordStatus                            BIGINT                                                  NOT NULL,
    ProjectStatusId                         BIGINT                                                  NOT NULL,
    Name                                    NVARCHAR(256)                                           NOT NULL,
    [Description]                           NVARCHAR(MAX)                                           NULL,
    EstimatedDuration                       TIME                                                    NULL,
    StartDate                               DATETIME                                                NOT NULL,
    ProjectedEndDate                        DATETIME                                                NOT NULL,
    ActualEndDate                           DATETIME                                                NULL,
    TotalDuration                           TIME                                                    NULL,
    CONSTRAINT PK_Project PRIMARY KEY CLUSTERED (Id ASC)
)
GO

-- Scheduled appointments
CREATE TABLE [Work].Appointment
(
    Id                                      BIGINT                      IDENTITY(1,1)               NOT NULL,
    PostalAddressId                         BIGINT                                                  NOT NULL,
    RecordStatus                            BIGINT                                                  NOT NULL,
    AppointmentStatusId                     BIGINT                                                  NOT NULL,
    Name                                    NVARCHAR(256)                                           NOT NULL,
    [Description]                           NVARCHAR(MAX)                                           NULL,
    ScheduledTime                           DATETIME                                                NOT NULL,
    ScheduledDuration                       TIME                                                    NOT NULL,
    EstimatedDuration                       TIME                                                    NOT NULL,
    CONSTRAINT PK_Appointment PRIMARY KEY CLUSTERED (Id ASC),
    CONSTRAINT FK_Appointment_PostalAddress_PostalAddressId FOREIGN KEY (PostalAddressId) REFERENCES [User].PostalAddress (Id) ON DELETE CASCADE
)
GO

-- Tasks specific to individual pieces of work
CREATE TABLE [Work].Task
(
    Id                                      BIGINT                      IDENTITY(1,1)               NOT NULL,
    RecordStatus                            BIGINT                                                  NOT NULL,
    Name                                    NVARCHAR(256)                                           NOT NULL,
    [Description]                           NVARCHAR(MAX)                                           NULL,
    EstimatedDuration                       TIME                                                    NULL,
    CONSTRAINT PK_Task PRIMARY KEY CLUSTERED (Id ASC)
)
GO

-- Appointments specific to a Project. Projects can have multiple appointments
CREATE TABLE [Work].ProjectAppointment
(
    ProjectId                               BIGINT                                                  NOT NULL,
    AppointmentId                           BIGINT                                                  NOT NULL,
    ArrivalTime                             DATETIME                                                NULL,
    FinishTime                              DATETIME                                                NULL,
    DestinationTime                         DATETIME                                                NULL,
    Notes                                   NVARCHAR(MAX)                                           NULL,
    CONSTRAINT PK_ProjectAppointment PRIMARY KEY CLUSTERED (ProjectId ASC, AppointmentId ASC),
    CONSTRAINT FK_ProjectAppointment_ProjectId FOREIGN KEY (ProjectId) REFERENCES [Work].Project (Id) ON DELETE CASCADE,
    CONSTRAINT FK_ProjectAppointment_AppointmentId FOREIGN KEY (AppointmentId) REFERENCES [Work].Appointment (Id) ON DELETE CASCADE
)
GO

-- Tasks specific to a Project. Projects can have multiple tasks.
CREATE TABLE [Work].ProjectTask
(
    ProjectId                               BIGINT                                                  NOT NULL,
    TaskId                                  BIGINT                                                  NOT NULL,
    TaskStatusId                            BIGINT                                                  NOT NULL,
    EstimatedHours                          DECIMAL                                                 NOT NULL,
    StartTime                               DATETIME                                                NULL,
    FinishTime                              DATETIME                                                NULL,
    TotalHours                              DECIMAL                                                 NOT NULL,
    CONSTRAINT PK_ProjectTask PRIMARY KEY CLUSTERED (ProjectId ASC, TaskId ASC)
)
GO

-- Tasks appointments
-- ProjectTaskAppointment? TaskAppointment?
-- Tasks themselves can have specific appointments, but tasks were meant to be generic and reusable so that they can be easily added to future projects.
-- Should the task appointments only be tasks that have been related to a project?
-- What about tasks that aren't related to specific projects?
-- Appointments are remote . . as in the guy travels to the client . . .
-- How to handle travel times in addition to hourly intervaled appointment schedules?