schema.sql

CREATE DATABASE FullStack;
GO
USE FullStack;
GO

-- -- Drop and recreate the database
-- DROP DATABASE IF EXISTS FullStack;
-- GO
-- CREATE DATABASE FullStack;
-- GO
-- USE FullStack;

CREATE TABLE Users (
    UserId INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(150) NOT NULL UNIQUE,
    PasswordHash NVARCHAR(255) NOT NULL,
    Role NVARCHAR(50) DEFAULT 'User',
    CreatedAt DATETIME DEFAULT GETDATE()
);

CREATE TABLE Dashboards (
    DashboardId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Description NVARCHAR(MAX) NULL,
    IsPrivate BIT DEFAULT 0,
    CreatedAt DATETIME DEFAULT GETDATE()
);

CREATE TABLE UserDashboards (
    UserDashboardId INT IDENTITY(1,1) PRIMARY KEY,
    UserId INT NOT NULL FOREIGN KEY REFERENCES Users(UserId),
    DashboardId INT NOT NULL FOREIGN KEY REFERENCES Dashboards(DashboardId) ON DELETE CASCADE,
    Role NVARCHAR(50) DEFAULT 'Viewer',     -- e.g., 'Owner', 'Editor', 'Viewer'
    JoinedAt DATETIME DEFAULT GETDATE(),
    UNIQUE (UserId, DashboardId)            -- Prevent duplicate membership
);

CREATE TABLE PendingInvitations (
    InvitationId INT IDENTITY(1,1) PRIMARY KEY,
    DashboardId INT NOT NULL FOREIGN KEY REFERENCES Dashboards(DashboardId) ON DELETE CASCADE,
    Email NVARCHAR(150) NOT NULL,
    Role NVARCHAR(50) DEFAULT 'Viewer',     -- 'Owner', 'Editor', 'Viewer'
    InvitedBy INT NOT NULL FOREIGN KEY REFERENCES Users(UserId),
    Token NVARCHAR(255) UNIQUE,              -- Unique invitation token
    Status NVARCHAR(50) DEFAULT 'Pending',   -- 'Pending', 'Accepted', 'Expired'
    CreatedAt DATETIME DEFAULT GETDATE(),
    ExpiresAt DATETIME,                      -- Optional expiration
    UNIQUE (DashboardId, Email)              -- Prevent duplicate invitations to same email
);

CREATE TABLE Notifications (
    NotificationId INT IDENTITY(1,1) PRIMARY KEY,
    UserId INT NOT NULL FOREIGN KEY REFERENCES Users(UserId) ON DELETE CASCADE,
    Type NVARCHAR(50) NOT NULL,              -- 'invitation', 'task_assigned', 'dashboard_shared', etc.
    Title NVARCHAR(255) NOT NULL,
    Message NVARCHAR(MAX) NOT NULL,
    RelatedId INT NULL,                      -- ID of related entity (DashboardId, TaskId, etc.)
    RelatedType NVARCHAR(50) NULL,           -- 'dashboard', 'task', etc.
    IsRead BIT DEFAULT 0,
    CreatedAt DATETIME DEFAULT GETDATE(),
    ReadAt DATETIME NULL
);

CREATE TABLE Boards (
    BoardId INT IDENTITY(1,1) PRIMARY KEY,
    DashboardId INT FOREIGN KEY REFERENCES Dashboards(DashboardId) ON DELETE CASCADE,
    Position INT NOT NULL DEFAULT 0, --for ordering
    Name NVARCHAR(100) NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
);

CREATE TABLE Tasks (
    -- Identifiers
    TaskId INT IDENTITY(100,1) PRIMARY KEY,
    BoardId INT FOREIGN KEY REFERENCES Boards(BoardId) NOT NULL,
    
    Position INT NOT NULL DEFAULT 0, --for ordering
    -- User Provided Fields
    Title NVARCHAR(255) NULL,
    Description NVARCHAR(MAX) NULL, --optional
    -- Automatic Generated Fields
    CreatedBy INT NULL, -- UserId of creator
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME NULL,
    -- AI Generates the following fields
    AssignedAgent NVARCHAR(MAX) NULL, --manual/auto (csv)
    Skills NVARCHAR(MAX) NULL, --manual/auto
    Category NVARCHAR(100) NULL,
    Status NVARCHAR(50) NULL,
    EstimatedDuration NVARCHAR(50) NULL,
    AgentMatchScore INT NULL,
    AgentProgress INT NULL,
    Dependencies NVARCHAR(MAX) NULL,  --csv of TaskIds
    AIModel NVARCHAR(100) NULL,
    AIOutput NVARCHAR(MAX) NULL
);

-- Analytics logs for AI requests
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AiLogs]') AND type in (N'U'))
BEGIN
    CREATE TABLE AiLogs (
        Id INT IDENTITY(1,1) PRIMARY KEY,
        Timestamp DATETIME NOT NULL DEFAULT(GETDATE()),
        UserId INT NULL,
        DashboardId INT NULL,
        TaskId INT NULL,
        Provider NVARCHAR(50) NULL,
        Model NVARCHAR(128) NULL,
        RequestPath NVARCHAR(255) NULL,
        Prompt NVARCHAR(MAX) NULL,
        Response NVARCHAR(MAX) NULL,
        TokensIn INT NULL,
        TokensOut INT NULL,
        ResponseTimeMs INT NULL,
        Status NVARCHAR(32) NULL,
        ErrorMessage NVARCHAR(MAX) NULL,
        Hallucination BIT NOT NULL DEFAULT(0),
        RetryCount INT NOT NULL DEFAULT(0),
        RequestId UNIQUEIDENTIFIER NULL,
        Accepted BIT NOT NULL DEFAULT(0)
    );
END

-- User Activity Logs for tracking user actions
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserActivityLogs]') AND type in (N'U'))
BEGIN
    CREATE TABLE UserActivityLogs (
        ActivityId INT IDENTITY(1,1) PRIMARY KEY,
        UserId INT NOT NULL FOREIGN KEY REFERENCES Users(UserId) ON DELETE CASCADE,
        DashboardId INT NOT NULL FOREIGN KEY REFERENCES Dashboards(DashboardId) ON DELETE CASCADE,
        TaskId INT NULL FOREIGN KEY REFERENCES Tasks(TaskId) ON DELETE NO ACTION,
        BoardId INT NULL FOREIGN KEY REFERENCES Boards(BoardId) ON DELETE NO ACTION,
        ActivityType NVARCHAR(50) NOT NULL, -- 'task_created', 'task_updated', 'task_moved', 'task_completed', 'task_deleted', 'board_created', etc.
        Description NVARCHAR(MAX),
        Timestamp DATETIME DEFAULT GETDATE(),
        OldValue NVARCHAR(MAX), -- For tracking changes
        NewValue NVARCHAR(MAX)
    );
END