/* DirectorOS — Microsoft SQL Server schema (port of the Prisma schema). */
SET NOCOUNT ON;

IF OBJECT_ID('dbo.DocumentPermission', 'U') IS NOT NULL DROP TABLE dbo.DocumentPermission;
IF OBJECT_ID('dbo.DocumentVersion', 'U') IS NOT NULL DROP TABLE dbo.DocumentVersion;
IF OBJECT_ID('dbo.Document', 'U') IS NOT NULL DROP TABLE dbo.Document;
IF OBJECT_ID('dbo.WeeklyReview', 'U') IS NOT NULL DROP TABLE dbo.WeeklyReview;
IF OBJECT_ID('dbo.Notification', 'U') IS NOT NULL DROP TABLE dbo.Notification;
IF OBJECT_ID('dbo.Reminder', 'U') IS NOT NULL DROP TABLE dbo.Reminder;
IF OBJECT_ID('dbo.Payment', 'U') IS NOT NULL DROP TABLE dbo.Payment;
IF OBJECT_ID('dbo.FollowUp', 'U') IS NOT NULL DROP TABLE dbo.FollowUp;
IF OBJECT_ID('dbo.Contact', 'U') IS NOT NULL DROP TABLE dbo.Contact;
IF OBJECT_ID('dbo.NoteConversion', 'U') IS NOT NULL DROP TABLE dbo.NoteConversion;
IF OBJECT_ID('dbo.Note', 'U') IS NOT NULL DROP TABLE dbo.Note;
IF OBJECT_ID('dbo.MedicineStockTransaction', 'U') IS NOT NULL DROP TABLE dbo.MedicineStockTransaction;
IF OBJECT_ID('dbo.MedicineLog', 'U') IS NOT NULL DROP TABLE dbo.MedicineLog;
IF OBJECT_ID('dbo.MedicineSchedule', 'U') IS NOT NULL DROP TABLE dbo.MedicineSchedule;
IF OBJECT_ID('dbo.Medicine', 'U') IS NOT NULL DROP TABLE dbo.Medicine;
IF OBJECT_ID('dbo.MeetingReminder', 'U') IS NOT NULL DROP TABLE dbo.MeetingReminder;
IF OBJECT_ID('dbo.MeetingAttendee', 'U') IS NOT NULL DROP TABLE dbo.MeetingAttendee;
IF OBJECT_ID('dbo.Meeting', 'U') IS NOT NULL DROP TABLE dbo.Meeting;
IF OBJECT_ID('dbo.DailyReport', 'U') IS NOT NULL DROP TABLE dbo.DailyReport;
IF OBJECT_ID('dbo.Task', 'U') IS NOT NULL DROP TABLE dbo.Task;
IF OBJECT_ID('dbo.EmployeeProfile', 'U') IS NOT NULL DROP TABLE dbo.EmployeeProfile;
IF OBJECT_ID('dbo.[User]', 'U') IS NOT NULL DROP TABLE dbo.[User];

CREATE TABLE dbo.[User] (
  Id                 INT IDENTITY(1,1) PRIMARY KEY,
  Email              NVARCHAR(254) NOT NULL UNIQUE,
  PasswordHash       NVARCHAR(100) NOT NULL,
  Name               NVARCHAR(120) NOT NULL,
  Role               VARCHAR(10)   NOT NULL DEFAULT 'EMPLOYEE' CHECK (Role IN ('DIRECTOR','EMPLOYEE')),
  Active             BIT           NOT NULL DEFAULT 1,
  Timezone           NVARCHAR(80)  NOT NULL DEFAULT 'UTC',
  WorkingHoursStart  NVARCHAR(5)   NULL DEFAULT '09:00',
  WorkingHoursEnd    NVARCHAR(5)   NULL DEFAULT '18:00',
  Settings           NVARCHAR(MAX) NULL,
  LastLoginAt        DATETIME2     NULL,
  CreatedAt          DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt          DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.EmployeeProfile (
  Id          INT IDENTITY(1,1) PRIMARY KEY,
  UserId      INT NOT NULL UNIQUE REFERENCES dbo.[User](Id) ON DELETE CASCADE,
  Phone       NVARCHAR(30)  NULL,
  Department  NVARCHAR(80)  NULL,
  Designation NVARCHAR(120) NULL,
  JoiningDate DATE          NULL,
  PhotoUrl    NVARCHAR(500) NULL,
  Active      BIT           NOT NULL DEFAULT 1,
  CreatedAt   DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt   DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_EmployeeProfile_Department ON dbo.EmployeeProfile(Department);

CREATE TABLE dbo.Task (
  Id                   INT IDENTITY(1,1) PRIMARY KEY,
  Title                NVARCHAR(200)  NOT NULL,
  Description          NVARCHAR(MAX)  NULL,
  Category             VARCHAR(10)    NOT NULL DEFAULT 'PERSONAL' CHECK (Category IN ('PERSONAL','ASSIGNED')),
  CreatedById          INT            NOT NULL REFERENCES dbo.[User](Id),
  AssignedToId         INT            NULL REFERENCES dbo.[User](Id) ON DELETE SET NULL,
  Priority             VARCHAR(10)    NOT NULL DEFAULT 'MEDIUM' CHECK (Priority IN ('CRITICAL','HIGH','MEDIUM','LOW')),
  StartDate            DATETIME2      NULL,
  Deadline             DATETIME2      NULL,
  ExpectedDurationHrs  FLOAT          NULL,
  ActualDurationHrs    FLOAT          NULL,
  Status               VARCHAR(12)    NOT NULL DEFAULT 'TODO' CHECK (Status IN ('TODO','IN_PROGRESS','COMPLETED','BLOCKED','CANCELLED')),
  CompletionPercentage INT            NOT NULL DEFAULT 0,
  DelayDays            INT            NOT NULL DEFAULT 0,
  DelayReason          NVARCHAR(1000) NULL,
  Notes                NVARCHAR(MAX)  NULL,
  ParentId             INT            NULL REFERENCES dbo.Task(Id),
  Attachments          NVARCHAR(MAX)  NULL,
  AssignedAt           DATETIME2      NULL,
  LastProgressAt       DATETIME2      NULL,
  CompletedAt          DATETIME2      NULL,
  CreatedAt            DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt            DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Task_CreatedById ON dbo.Task(CreatedById);
CREATE INDEX IX_Task_AssignedToId ON dbo.Task(AssignedToId);
CREATE INDEX IX_Task_Deadline ON dbo.Task(Deadline);
CREATE INDEX IX_Task_Status ON dbo.Task(Status);
CREATE INDEX IX_Task_ParentId ON dbo.Task(ParentId);

CREATE TABLE dbo.DailyReport (
  Id                   INT IDENTITY(1,1) PRIMARY KEY,
  UserId               INT           NOT NULL REFERENCES dbo.[User](Id) ON DELETE CASCADE,
  ReportDate           DATE          NOT NULL,
  AssignedWork         NVARCHAR(MAX) NULL,
  WorkCompleted        NVARCHAR(MAX) NULL,
  CompletionPercentage INT           NOT NULL DEFAULT 0,
  Status               VARCHAR(12)   NOT NULL DEFAULT 'PENDING' CHECK (Status IN ('COMPLETED','IN_PROGRESS','PENDING','DELAYED')),
  PendingWork          NVARCHAR(MAX) NULL,
  DelayReason          NVARCHAR(2000) NULL,
  ProblemsBlockers     NVARCHAR(2000) NULL,
  SupportRequired      NVARCHAR(2000) NULL,
  TimeSpentHrs         FLOAT         NULL,
  TomorrowPlan         NVARCHAR(MAX) NULL,
  CreatedAt            DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt            DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UQ_DailyReport_UserDate UNIQUE (UserId, ReportDate)
);
CREATE INDEX IX_DailyReport_ReportDate ON dbo.DailyReport(ReportDate);
CREATE INDEX IX_DailyReport_Status ON dbo.DailyReport(Status);

CREATE TABLE dbo.Meeting (
  Id                  INT IDENTITY(1,1) PRIMARY KEY,
  Title               NVARCHAR(200) NOT NULL,
  StartTime           DATETIME2     NOT NULL,
  EndTime             DATETIME2     NULL,
  PersonCompany       NVARCHAR(200) NULL,
  Purpose             NVARCHAR(MAX) NULL,
  PreparationDetails  NVARCHAR(MAX) NULL,
  Location            NVARCHAR(200) NULL,
  MeetingLink         NVARCHAR(500) NULL,
  Status              VARCHAR(12)   NOT NULL DEFAULT 'SCHEDULED' CHECK (Status IN ('SCHEDULED','COMPLETED','CANCELLED')),
  Notes               NVARCHAR(MAX) NULL,
  FollowUpDate        DATETIME2     NULL,
  CreatedById         INT           NOT NULL REFERENCES dbo.[User](Id),
  Attendees           NVARCHAR(MAX) NULL,
  Attachments         NVARCHAR(MAX) NULL,
  CreatedAt           DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt           DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Meeting_StartTime ON dbo.Meeting(StartTime);
CREATE INDEX IX_Meeting_Status ON dbo.Meeting(Status);

CREATE TABLE dbo.MeetingAttendee (
  Id        INT IDENTITY(1,1) PRIMARY KEY,
  MeetingId INT           NOT NULL REFERENCES dbo.Meeting(Id) ON DELETE CASCADE,
  UserId    INT           NOT NULL REFERENCES dbo.[User](Id) ON DELETE CASCADE,
  Name      NVARCHAR(120) NOT NULL,
  CONSTRAINT UQ_MeetingAttendee UNIQUE (MeetingId, UserId)
);
CREATE INDEX IX_MeetingAttendee_UserId ON dbo.MeetingAttendee(UserId);

CREATE TABLE dbo.MeetingReminder (
  Id           INT IDENTITY(1,1) PRIMARY KEY,
  MeetingId    INT           NOT NULL REFERENCES dbo.Meeting(Id) ON DELETE CASCADE,
  DaysBefore   INT           NOT NULL,
  ScheduledFor DATETIME2     NOT NULL,
  Sent         BIT           NOT NULL DEFAULT 0,
  SentAt       DATETIME2     NULL,
  ErrorInfo    NVARCHAR(500) NULL,
  CreatedAt    DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UQ_MeetingReminder UNIQUE (MeetingId, DaysBefore)
);
CREATE INDEX IX_MeetingReminder_ScheduledFor ON dbo.MeetingReminder(ScheduledFor);
CREATE INDEX IX_MeetingReminder_Sent ON dbo.MeetingReminder(Sent);

CREATE TABLE dbo.Medicine (
  Id                 INT IDENTITY(1,1) PRIMARY KEY,
  UserId             INT           NOT NULL REFERENCES dbo.[User](Id) ON DELETE CASCADE,
  Name               NVARCHAR(150) NOT NULL,
  DosageInfo         NVARCHAR(500) NULL,
  PillsPerDose       INT           NOT NULL DEFAULT 1,
  CurrentStock       INT           NOT NULL DEFAULT 0,
  MinStockThreshold  INT           NOT NULL DEFAULT 10,
  StartDate          DATE          NULL,
  EndDate            DATE          NULL,
  Notes              NVARCHAR(MAX) NULL,
  Active             BIT           NOT NULL DEFAULT 1,
  CreatedAt          DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt          DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Medicine_UserId ON dbo.Medicine(UserId);

CREATE TABLE dbo.MedicineSchedule (
  Id         INT IDENTITY(1,1) PRIMARY KEY,
  MedicineId INT          NOT NULL REFERENCES dbo.Medicine(Id) ON DELETE CASCADE,
  TimeOfDay  NVARCHAR(5)  NOT NULL,
  Pills      INT          NOT NULL DEFAULT 1
);
CREATE INDEX IX_MedicineSchedule_MedicineId ON dbo.MedicineSchedule(MedicineId);

CREATE TABLE dbo.MedicineLog (
  Id           INT IDENTITY(1,1) PRIMARY KEY,
  MedicineId   INT           NOT NULL REFERENCES dbo.Medicine(Id) ON DELETE CASCADE,
  UserId       INT           NOT NULL REFERENCES dbo.[User](Id),
  ScheduledFor DATETIME2     NOT NULL,
  Status       VARCHAR(10)   NOT NULL DEFAULT 'PENDING' CHECK (Status IN ('PENDING','TAKEN','SKIPPED')),
  TakenAt      DATETIME2     NULL,
  NotifiedAt   DATETIME2     NULL,
  CreatedAt    DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt    DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UQ_MedicineLog UNIQUE (MedicineId, ScheduledFor)
);
CREATE INDEX IX_MedicineLog_UserScheduled ON dbo.MedicineLog(UserId, ScheduledFor);
CREATE INDEX IX_MedicineLog_Status ON dbo.MedicineLog(Status);

CREATE TABLE dbo.MedicineStockTransaction (
  Id          INT IDENTITY(1,1) PRIMARY KEY,
  MedicineId  INT           NOT NULL REFERENCES dbo.Medicine(Id) ON DELETE CASCADE,
  Type        VARCHAR(12)   NOT NULL DEFAULT 'PURCHASE' CHECK (Type IN ('PURCHASE','ADJUSTMENT')),
  Quantity    INT           NOT NULL,
  Date        DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  Notes       NVARCHAR(500) NULL,
  CreatedById INT           NOT NULL REFERENCES dbo.[User](Id)
);
CREATE INDEX IX_MedicineStock_MedicineId ON dbo.MedicineStockTransaction(MedicineId);

CREATE TABLE dbo.Note (
  Id        INT IDENTITY(1,1) PRIMARY KEY,
  UserId    INT           NOT NULL REFERENCES dbo.[User](Id) ON DELETE CASCADE,
  Content   NVARCHAR(MAX) NOT NULL,
  Tags      NVARCHAR(MAX) NULL,
  CreatedAt DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Note_UserId ON dbo.Note(UserId);

CREATE TABLE dbo.NoteConversion (
  Id           INT IDENTITY(1,1) PRIMARY KEY,
  NoteId       INT          NOT NULL REFERENCES dbo.Note(Id) ON DELETE CASCADE,
  TargetModule VARCHAR(10)  NOT NULL,
  TargetId     INT          NOT NULL,
  CreatedAt    DATETIME2    NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_NoteConversion_NoteId ON dbo.NoteConversion(NoteId);

CREATE TABLE dbo.Contact (
  Id          INT IDENTITY(1,1) PRIMARY KEY,
  Name        NVARCHAR(150) NOT NULL,
  Phone       NVARCHAR(30)  NULL,
  Email       NVARCHAR(254) NULL,
  Company     NVARCHAR(150) NULL,
  Type        VARCHAR(10)   NOT NULL DEFAULT 'OTHER' CHECK (Type IN ('CLIENT','EMPLOYEE','OTHER')),
  Notes       NVARCHAR(MAX) NULL,
  CreatedById INT           NOT NULL REFERENCES dbo.[User](Id),
  CreatedAt   DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt   DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Contact_Name ON dbo.Contact(Name);

CREATE TABLE dbo.FollowUp (
  Id               INT IDENTITY(1,1) PRIMARY KEY,
  ContactId        INT           NOT NULL REFERENCES dbo.Contact(Id) ON DELETE CASCADE,
  DiscussionDate   DATETIME2     NULL,
  DiscussionNotes  NVARCHAR(MAX) NULL,
  Outcome          NVARCHAR(MAX) NULL,
  NextFollowUpDate DATETIME2     NULL,
  Status           VARCHAR(12)   NOT NULL DEFAULT 'SCHEDULED' CHECK (Status IN ('SCHEDULED','COMPLETED','OVERDUE')),
  CreatedById      INT           NOT NULL REFERENCES dbo.[User](Id),
  CreatedAt        DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt        DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_FollowUp_NextDate ON dbo.FollowUp(NextFollowUpDate);
CREATE INDEX IX_FollowUp_Status ON dbo.FollowUp(Status);

CREATE TABLE dbo.Payment (
  Id          INT IDENTITY(1,1) PRIMARY KEY,
  Title       NVARCHAR(200) NOT NULL,
  Amount      DECIMAL(12,2) NOT NULL,
  Type        VARCHAR(15)   NOT NULL DEFAULT 'OTHER' CHECK (Type IN ('CLIENT_PAYMENT','SALARY','SUBSCRIPTION','DOMAIN','HOSTING','OTHER')),
  DueDate     DATE          NULL,
  Status      VARCHAR(10)   NOT NULL DEFAULT 'PENDING' CHECK (Status IN ('PENDING','PAID','OVERDUE')),
  Recurring   BIT           NOT NULL DEFAULT 0,
  Frequency   VARCHAR(10)   NOT NULL DEFAULT 'ONE_TIME' CHECK (Frequency IN ('ONE_TIME','MONTHLY','YEARLY')),
  PaidAt      DATETIME2     NULL,
  Notes       NVARCHAR(MAX) NULL,
  CreatedById INT           NOT NULL REFERENCES dbo.[User](Id),
  CreatedAt   DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt   DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Payment_DueDate ON dbo.Payment(DueDate);
CREATE INDEX IX_Payment_Status ON dbo.Payment(Status);

CREATE TABLE dbo.Reminder (
  Id              INT IDENTITY(1,1) PRIMARY KEY,
  UserId          INT           NOT NULL REFERENCES dbo.[User](Id) ON DELETE CASCADE,
  Title           NVARCHAR(200) NOT NULL,
  Description     NVARCHAR(MAX) NULL,
  RemindAt        DATETIME2     NOT NULL,
  Priority        VARCHAR(10)   NOT NULL DEFAULT 'NORMAL' CHECK (Priority IN ('NORMAL','IMPORTANT','CRITICAL')),
  RelatedModule   NVARCHAR(40)  NULL,
  RelatedEntityId INT           NULL,
  Recurrence      VARCHAR(10)   NOT NULL DEFAULT 'NONE' CHECK (Recurrence IN ('NONE','DAILY','WEEKLY','MONTHLY','YEARLY','CUSTOM')),
  CustomRule      NVARCHAR(MAX) NULL,
  Status          VARCHAR(10)   NOT NULL DEFAULT 'PENDING' CHECK (Status IN ('PENDING','SNOOZED','DISMISSED')),
  SnoozedUntil    DATETIME2     NULL,
  DismissedAt     DATETIME2     NULL,
  NotifiedAt      DATETIME2     NULL,
  CreatedAt       DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt       DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Reminder_UserRemind ON dbo.Reminder(UserId, RemindAt);
CREATE INDEX IX_Reminder_Status ON dbo.Reminder(Status);

CREATE TABLE dbo.Notification (
  Id              INT IDENTITY(1,1) PRIMARY KEY,
  UserId          INT           NOT NULL REFERENCES dbo.[User](Id) ON DELETE CASCADE,
  Title           NVARCHAR(200) NOT NULL,
  Body            NVARCHAR(MAX) NULL,
  Type            VARCHAR(15)   NOT NULL DEFAULT 'SYSTEM',
  RelatedModule   NVARCHAR(40)  NULL,
  RelatedEntityId INT           NULL,
  ReadAt          DATETIME2     NULL,
  CreatedAt       DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Notification_UserCreated ON dbo.Notification(UserId, CreatedAt);

CREATE TABLE dbo.WeeklyReview (
  Id         INT IDENTITY(1,1) PRIMARY KEY,
  UserId     INT           NOT NULL REFERENCES dbo.[User](Id) ON DELETE CASCADE,
  WeekStart  DATE          NOT NULL,
  WeekEnd    DATE          NOT NULL,
  Metrics    NVARCHAR(MAX) NULL,
  Reflection NVARCHAR(MAX) NULL,
  CreatedAt  DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt  DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UQ_WeeklyReview UNIQUE (UserId, WeekStart)
);
CREATE INDEX IX_WeeklyReview_UserWeek ON dbo.WeeklyReview(UserId, WeekStart);

CREATE TABLE dbo.Document (
  Id              INT IDENTITY(1,1) PRIMARY KEY,
  Title           NVARCHAR(200) NOT NULL,
  Description     NVARCHAR(MAX) NULL,
  FileName        NVARCHAR(200) NOT NULL,
  StorageKey      NVARCHAR(500) NOT NULL,
  MimeType        NVARCHAR(100) NOT NULL,
  SizeBytes       INT           NOT NULL,
  UploadedById    INT           NOT NULL REFERENCES dbo.[User](Id),
  RelatedModule   NVARCHAR(20)  NULL,
  RelatedEntityId INT           NULL,
  CurrentVersion  INT           NOT NULL DEFAULT 1,
  Status          VARCHAR(10)   NOT NULL DEFAULT 'ACTIVE' CHECK (Status IN ('ACTIVE','DRAFT','ARCHIVED')),
  Visibility      VARCHAR(15)   NOT NULL DEFAULT 'PRIVATE' CHECK (Visibility IN ('PRIVATE','ORGANIZATION')),
  ReviewDate      DATE          NULL,
  ExpiryDate      DATE          NULL,
  UpdateDate      DATE          NULL,
  CreatedAt       DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt       DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Document_UploadedBy ON dbo.Document(UploadedById);
CREATE INDEX IX_Document_Related ON dbo.Document(RelatedModule, RelatedEntityId);
CREATE INDEX IX_Document_UpdateDate ON dbo.Document(UpdateDate);
CREATE INDEX IX_Document_ReviewDate ON dbo.Document(ReviewDate);

CREATE TABLE dbo.DocumentVersion (
  Id           INT IDENTITY(1,1) PRIMARY KEY,
  DocumentId   INT           NOT NULL REFERENCES dbo.Document(Id) ON DELETE CASCADE,
  Version      INT           NOT NULL,
  StorageKey   NVARCHAR(500) NOT NULL,
  FileName     NVARCHAR(200) NOT NULL,
  MimeType     NVARCHAR(100) NOT NULL,
  SizeBytes    INT           NOT NULL,
  UploadedById INT           NOT NULL REFERENCES dbo.[User](Id),
  ChangeNotes  NVARCHAR(MAX) NULL,
  FileHash     NVARCHAR(64)  NULL,
  IsCurrent    BIT           NOT NULL DEFAULT 0,
  IsDraft      BIT           NOT NULL DEFAULT 0,
  CreatedAt    DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UQ_DocumentVersion UNIQUE (DocumentId, Version)
);
CREATE INDEX IX_DocumentVersion_DocumentId ON dbo.DocumentVersion(DocumentId);

CREATE TABLE dbo.DocumentPermission (
  Id         INT IDENTITY(1,1) PRIMARY KEY,
  DocumentId INT       NOT NULL REFERENCES dbo.Document(Id) ON DELETE CASCADE,
  UserId     INT       NOT NULL REFERENCES dbo.[User](Id) ON DELETE CASCADE,
  CanEdit    BIT       NOT NULL DEFAULT 0,
  CreatedAt  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  CONSTRAINT UQ_DocumentPermission UNIQUE (DocumentId, UserId)
);
CREATE INDEX IX_DocumentPermission_UserId ON dbo.DocumentPermission(UserId);