PlotDirector/PlotLine/Sql/056_Phase9B_WritingScheduleFoundation.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.WritingPlans', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.WritingPlans
    (
        WritingPlanID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_WritingPlans PRIMARY KEY,
        UserID int NOT NULL,
        ProjectID int NOT NULL,
        BookID int NULL,
        PlanName nvarchar(200) NOT NULL,
        GoalType nvarchar(50) NOT NULL,
        StartDate date NOT NULL,
        DeadlineDate date NOT NULL,
        AvailableDaysJson nvarchar(max) NOT NULL,
        SessionLengthMinutes int NOT NULL,
        IncludeBlockedScenes bit NOT NULL CONSTRAINT DF_WritingPlans_IncludeBlockedScenes DEFAULT 0,
        RebalanceMode nvarchar(50) NOT NULL CONSTRAINT DF_WritingPlans_RebalanceMode DEFAULT N'Ask',
        IsActive bit NOT NULL CONSTRAINT DF_WritingPlans_IsActive DEFAULT 1,
        CreatedDateUtc datetime2 NOT NULL CONSTRAINT DF_WritingPlans_CreatedDateUtc DEFAULT GETUTCDATE(),
        ModifiedDateUtc datetime2 NOT NULL CONSTRAINT DF_WritingPlans_ModifiedDateUtc DEFAULT GETUTCDATE(),
        CONSTRAINT FK_WritingPlans_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID),
        CONSTRAINT FK_WritingPlans_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
        CONSTRAINT FK_WritingPlans_Books FOREIGN KEY (BookID) REFERENCES dbo.Books(BookID)
    );
END;
GO

IF OBJECT_ID(N'dbo.WritingScheduleItems', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.WritingScheduleItems
    (
        WritingScheduleItemID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_WritingScheduleItems PRIMARY KEY,
        WritingPlanID int NOT NULL,
        SceneID int NOT NULL,
        ScheduledDate date NOT NULL,
        TaskType nvarchar(50) NOT NULL,
        EstimatedMinutes int NULL,
        TargetWords int NULL,
        ScheduleStatus nvarchar(50) NOT NULL,
        CompletedDateUtc datetime2 NULL,
        Notes nvarchar(1000) NULL,
        CreatedDateUtc datetime2 NOT NULL CONSTRAINT DF_WritingScheduleItems_CreatedDateUtc DEFAULT GETUTCDATE(),
        ModifiedDateUtc datetime2 NOT NULL CONSTRAINT DF_WritingScheduleItems_ModifiedDateUtc DEFAULT GETUTCDATE(),
        CONSTRAINT FK_WritingScheduleItems_WritingPlans FOREIGN KEY (WritingPlanID) REFERENCES dbo.WritingPlans(WritingPlanID),
        CONSTRAINT FK_WritingScheduleItems_Scenes FOREIGN KEY (SceneID) REFERENCES dbo.Scenes(SceneID)
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_WritingPlans_UserID' AND object_id = OBJECT_ID(N'dbo.WritingPlans'))
    CREATE INDEX IX_WritingPlans_UserID ON dbo.WritingPlans(UserID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_WritingPlans_ProjectID' AND object_id = OBJECT_ID(N'dbo.WritingPlans'))
    CREATE INDEX IX_WritingPlans_ProjectID ON dbo.WritingPlans(ProjectID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_WritingPlans_IsActive' AND object_id = OBJECT_ID(N'dbo.WritingPlans'))
    CREATE INDEX IX_WritingPlans_IsActive ON dbo.WritingPlans(IsActive);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_WritingScheduleItems_WritingPlanID' AND object_id = OBJECT_ID(N'dbo.WritingScheduleItems'))
    CREATE INDEX IX_WritingScheduleItems_WritingPlanID ON dbo.WritingScheduleItems(WritingPlanID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_WritingScheduleItems_SceneID' AND object_id = OBJECT_ID(N'dbo.WritingScheduleItems'))
    CREATE INDEX IX_WritingScheduleItems_SceneID ON dbo.WritingScheduleItems(SceneID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_WritingScheduleItems_ScheduledDate' AND object_id = OBJECT_ID(N'dbo.WritingScheduleItems'))
    CREATE INDEX IX_WritingScheduleItems_ScheduledDate ON dbo.WritingScheduleItems(ScheduledDate);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_WritingScheduleItems_ScheduleStatus' AND object_id = OBJECT_ID(N'dbo.WritingScheduleItems'))
    CREATE INDEX IX_WritingScheduleItems_ScheduleStatus ON dbo.WritingScheduleItems(ScheduleStatus);
GO

CREATE OR ALTER PROCEDURE dbo.GetWritingPlansByUser
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT WritingPlanID, UserID, ProjectID, BookID, PlanName, GoalType, StartDate, DeadlineDate,
        AvailableDaysJson, SessionLengthMinutes, IncludeBlockedScenes, RebalanceMode, IsActive,
        CreatedDateUtc, ModifiedDateUtc
    FROM dbo.WritingPlans
    WHERE UserID = @UserID
    ORDER BY IsActive DESC, DeadlineDate, StartDate, WritingPlanID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.GetWritingPlanByID
    @WritingPlanID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT WritingPlanID, UserID, ProjectID, BookID, PlanName, GoalType, StartDate, DeadlineDate,
        AvailableDaysJson, SessionLengthMinutes, IncludeBlockedScenes, RebalanceMode, IsActive,
        CreatedDateUtc, ModifiedDateUtc
    FROM dbo.WritingPlans
    WHERE WritingPlanID = @WritingPlanID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.CreateWritingPlan
    @UserID int,
    @ProjectID int,
    @BookID int = NULL,
    @PlanName nvarchar(200),
    @GoalType nvarchar(50),
    @StartDate date,
    @DeadlineDate date,
    @AvailableDaysJson nvarchar(max),
    @SessionLengthMinutes int,
    @IncludeBlockedScenes bit = 0,
    @RebalanceMode nvarchar(50) = N'Ask',
    @IsActive bit = 1
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.WritingPlans
    (
        UserID, ProjectID, BookID, PlanName, GoalType, StartDate, DeadlineDate, AvailableDaysJson,
        SessionLengthMinutes, IncludeBlockedScenes, RebalanceMode, IsActive
    )
    VALUES
    (
        @UserID, @ProjectID, @BookID, @PlanName, @GoalType, @StartDate, @DeadlineDate, @AvailableDaysJson,
        @SessionLengthMinutes, @IncludeBlockedScenes, @RebalanceMode, @IsActive
    );

    SELECT CONVERT(int, SCOPE_IDENTITY());
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateWritingPlan
    @WritingPlanID int,
    @UserID int,
    @ProjectID int,
    @BookID int = NULL,
    @PlanName nvarchar(200),
    @GoalType nvarchar(50),
    @StartDate date,
    @DeadlineDate date,
    @AvailableDaysJson nvarchar(max),
    @SessionLengthMinutes int,
    @IncludeBlockedScenes bit = 0,
    @RebalanceMode nvarchar(50) = N'Ask',
    @IsActive bit = 1
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.WritingPlans
    SET UserID = @UserID,
        ProjectID = @ProjectID,
        BookID = @BookID,
        PlanName = @PlanName,
        GoalType = @GoalType,
        StartDate = @StartDate,
        DeadlineDate = @DeadlineDate,
        AvailableDaysJson = @AvailableDaysJson,
        SessionLengthMinutes = @SessionLengthMinutes,
        IncludeBlockedScenes = @IncludeBlockedScenes,
        RebalanceMode = @RebalanceMode,
        IsActive = @IsActive,
        ModifiedDateUtc = GETUTCDATE()
    WHERE WritingPlanID = @WritingPlanID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.DeleteWritingPlan
    @WritingPlanID int
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM dbo.WritingScheduleItems
    WHERE WritingPlanID = @WritingPlanID;

    DELETE FROM dbo.WritingPlans
    WHERE WritingPlanID = @WritingPlanID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SetWritingPlanActive
    @WritingPlanID int,
    @IsActive bit
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.WritingPlans
    SET IsActive = @IsActive,
        ModifiedDateUtc = GETUTCDATE()
    WHERE WritingPlanID = @WritingPlanID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.GetScheduleItemsByPlan
    @WritingPlanID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT WritingScheduleItemID, WritingPlanID, SceneID, ScheduledDate, TaskType, EstimatedMinutes,
        TargetWords, ScheduleStatus, CompletedDateUtc, Notes, CreatedDateUtc, ModifiedDateUtc
    FROM dbo.WritingScheduleItems
    WHERE WritingPlanID = @WritingPlanID
    ORDER BY ScheduledDate, WritingScheduleItemID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.GetScheduleItemsByDateRange
    @WritingPlanID int,
    @StartDate date,
    @EndDate date
AS
BEGIN
    SET NOCOUNT ON;

    SELECT WritingScheduleItemID, WritingPlanID, SceneID, ScheduledDate, TaskType, EstimatedMinutes,
        TargetWords, ScheduleStatus, CompletedDateUtc, Notes, CreatedDateUtc, ModifiedDateUtc
    FROM dbo.WritingScheduleItems
    WHERE WritingPlanID = @WritingPlanID
        AND ScheduledDate >= @StartDate
        AND ScheduledDate <= @EndDate
    ORDER BY ScheduledDate, WritingScheduleItemID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.CreateScheduleItem
    @WritingPlanID int,
    @SceneID int,
    @ScheduledDate date,
    @TaskType nvarchar(50),
    @EstimatedMinutes int = NULL,
    @TargetWords int = NULL,
    @ScheduleStatus nvarchar(50),
    @CompletedDateUtc datetime2 = NULL,
    @Notes nvarchar(1000) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.WritingScheduleItems
    (
        WritingPlanID, SceneID, ScheduledDate, TaskType, EstimatedMinutes, TargetWords,
        ScheduleStatus, CompletedDateUtc, Notes
    )
    VALUES
    (
        @WritingPlanID, @SceneID, @ScheduledDate, @TaskType, @EstimatedMinutes, @TargetWords,
        @ScheduleStatus, @CompletedDateUtc, @Notes
    );

    SELECT CONVERT(int, SCOPE_IDENTITY());
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateScheduleItem
    @WritingScheduleItemID int,
    @WritingPlanID int,
    @SceneID int,
    @ScheduledDate date,
    @TaskType nvarchar(50),
    @EstimatedMinutes int = NULL,
    @TargetWords int = NULL,
    @ScheduleStatus nvarchar(50),
    @CompletedDateUtc datetime2 = NULL,
    @Notes nvarchar(1000) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.WritingScheduleItems
    SET WritingPlanID = @WritingPlanID,
        SceneID = @SceneID,
        ScheduledDate = @ScheduledDate,
        TaskType = @TaskType,
        EstimatedMinutes = @EstimatedMinutes,
        TargetWords = @TargetWords,
        ScheduleStatus = @ScheduleStatus,
        CompletedDateUtc = @CompletedDateUtc,
        Notes = @Notes,
        ModifiedDateUtc = GETUTCDATE()
    WHERE WritingScheduleItemID = @WritingScheduleItemID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.DeleteScheduleItem
    @WritingScheduleItemID int
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM dbo.WritingScheduleItems
    WHERE WritingScheduleItemID = @WritingScheduleItemID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateScheduleItemStatus
    @WritingScheduleItemID int,
    @ScheduleStatus nvarchar(50),
    @CompletedDateUtc datetime2 = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.WritingScheduleItems
    SET ScheduleStatus = @ScheduleStatus,
        CompletedDateUtc = @CompletedDateUtc,
        ModifiedDateUtc = GETUTCDATE()
    WHERE WritingScheduleItemID = @WritingScheduleItemID;
END;
GO