SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF COL_LENGTH(N'dbo.Books', N'CoverThumbnailPath') IS NULL
    ALTER TABLE dbo.Books ADD CoverThumbnailPath nvarchar(1000) NULL;
GO

IF COL_LENGTH(N'dbo.Books', N'CoverStandardPath') IS NULL
    ALTER TABLE dbo.Books ADD CoverStandardPath nvarchar(1000) NULL;
GO

CREATE OR ALTER PROCEDURE dbo.Book_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        b.BookID,
        b.ProjectID,
        b.BookTitle,
        b.Subtitle,
        b.Tagline,
        b.ShortDescription,
        b.Status,
        b.BookNumber,
        b.Description,
        b.TargetWordCount,
        b.WritingStartDate,
        b.TargetCompletionDate,
        b.PublicationDate,
        b.CoverThumbnailPath,
        b.CoverStandardPath,
        ISNULL(wordCounts.CurrentWordCount, 0) AS CurrentWordCount,
        ISNULL(counts.ChapterCount, 0) AS ChapterCount,
        ISNULL(counts.SceneCount, 0) AS SceneCount,
        b.SortOrder,
        b.CreatedDate,
        b.UpdatedDate,
        b.IsArchived,
        b.ArchivedDate,
        b.ArchivedReason
    FROM dbo.Books b
    OUTER APPLY
    (
        SELECT
            COUNT(DISTINCT c.ChapterID) AS ChapterCount,
            COUNT(s.SceneID) AS SceneCount
        FROM dbo.Chapters c
        LEFT JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
        WHERE c.BookID = b.BookID AND c.IsArchived = 0
    ) counts
    OUTER APPLY
    (
        SELECT SUM(ISNULL(sw.ActualWordCount, 0)) AS CurrentWordCount
        FROM dbo.Chapters c
        INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
        LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
        WHERE c.BookID = b.BookID AND c.IsArchived = 0
    ) wordCounts
    WHERE b.ProjectID = @ProjectID AND b.IsArchived = 0
    ORDER BY b.SortOrder, b.BookNumber, b.BookTitle;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Book_Get
    @BookID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        b.BookID,
        b.ProjectID,
        b.BookTitle,
        b.Subtitle,
        b.Tagline,
        b.ShortDescription,
        b.Status,
        b.BookNumber,
        b.Description,
        b.TargetWordCount,
        b.WritingStartDate,
        b.TargetCompletionDate,
        b.PublicationDate,
        b.CoverThumbnailPath,
        b.CoverStandardPath,
        ISNULL(wordCounts.CurrentWordCount, 0) AS CurrentWordCount,
        ISNULL(counts.ChapterCount, 0) AS ChapterCount,
        ISNULL(counts.SceneCount, 0) AS SceneCount,
        b.SortOrder,
        b.CreatedDate,
        b.UpdatedDate,
        b.IsArchived,
        b.ArchivedDate,
        b.ArchivedReason
    FROM dbo.Books b
    OUTER APPLY
    (
        SELECT
            COUNT(DISTINCT c.ChapterID) AS ChapterCount,
            COUNT(s.SceneID) AS SceneCount
        FROM dbo.Chapters c
        LEFT JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
        WHERE c.BookID = b.BookID AND c.IsArchived = 0
    ) counts
    OUTER APPLY
    (
        SELECT SUM(ISNULL(sw.ActualWordCount, 0)) AS CurrentWordCount
        FROM dbo.Chapters c
        INNER JOIN dbo.Scenes s ON s.ChapterID = c.ChapterID AND s.IsArchived = 0
        LEFT JOIN dbo.SceneWorkflow sw ON sw.SceneID = s.SceneID
        WHERE c.BookID = b.BookID AND c.IsArchived = 0
    ) wordCounts
    WHERE b.BookID = @BookID AND b.IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Book_UpdateCover
    @BookID int,
    @CoverThumbnailPath nvarchar(1000) = NULL,
    @CoverStandardPath nvarchar(1000) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Books
    SET CoverThumbnailPath = @CoverThumbnailPath,
        CoverStandardPath = @CoverStandardPath,
        UpdatedDate = SYSUTCDATETIME()
    WHERE BookID = @BookID;
END;
GO