SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.ChapterPurposeTypes', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ChapterPurposeTypes
    (
        ChapterPurposeID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ChapterPurposeTypes PRIMARY KEY,
        PurposeName nvarchar(100) NOT NULL,
        SortOrder int NOT NULL CONSTRAINT DF_ChapterPurposeTypes_SortOrder DEFAULT 0,
        IsArchived bit NOT NULL CONSTRAINT DF_ChapterPurposeTypes_IsArchived DEFAULT 0,
        CONSTRAINT UQ_ChapterPurposeTypes_PurposeName UNIQUE (PurposeName)
    );
END;
GO

MERGE dbo.ChapterPurposeTypes AS target
USING (VALUES
    (N'Opening', 10),
    (N'Character Development', 20),
    (N'Relationship Development', 30),
    (N'World Building', 40),
    (N'Investigation', 50),
    (N'Revelation', 60),
    (N'Escalation', 70),
    (N'Turning Point', 80),
    (N'Climax', 90),
    (N'Resolution', 100),
    (N'Transition', 110)
) AS source (PurposeName, SortOrder)
ON target.PurposeName = source.PurposeName
WHEN MATCHED THEN UPDATE SET SortOrder = source.SortOrder, IsArchived = 0
WHEN NOT MATCHED THEN INSERT (PurposeName, SortOrder, IsArchived) VALUES (source.PurposeName, source.SortOrder, 0);
GO

IF COL_LENGTH(N'dbo.Chapters', N'ChapterPurposeID') IS NULL
    ALTER TABLE dbo.Chapters ADD ChapterPurposeID int NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_Chapters_ChapterPurposeTypes')
    ALTER TABLE dbo.Chapters ADD CONSTRAINT FK_Chapters_ChapterPurposeTypes FOREIGN KEY (ChapterPurposeID) REFERENCES dbo.ChapterPurposeTypes(ChapterPurposeID);
GO

CREATE OR ALTER PROCEDURE dbo.Lookup_All
AS
BEGIN
    SET NOCOUNT ON;
    SELECT RevisionStatusID, StatusName, SortOrder FROM dbo.RevisionStatuses ORDER BY SortOrder;
    SELECT TimeModeID, TimeModeName, SortOrder FROM dbo.TimeModes ORDER BY SortOrder;
    SELECT DurationUnitID, DurationUnitName, SortOrder FROM dbo.DurationUnits ORDER BY SortOrder;
    SELECT TimeConfidenceID, TimeConfidenceName, SortOrder FROM dbo.TimeConfidences ORDER BY SortOrder;
    SELECT ScenePurposeTypeID, PurposeName, SortOrder FROM dbo.ScenePurposeTypes WHERE IsArchived = 0 ORDER BY SortOrder;
    SELECT ChapterPurposeID, PurposeName, SortOrder, IsArchived FROM dbo.ChapterPurposeTypes WHERE IsArchived = 0 ORDER BY SortOrder;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Chapter_ListByBook
    @BookID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT c.ChapterID, c.BookID, c.ChapterNumber, c.ChapterTitle, c.POVCharacterID, c.Summary,
        c.ChapterPurposeID, cpt.PurposeName AS ChapterPurposeName,
        c.SortOrder, c.RevisionStatusID, rs.StatusName AS RevisionStatusName,
        c.CreatedDate, c.UpdatedDate, c.IsArchived, c.IsLinkedToManuscript,
        c.ManuscriptDocumentID, c.ExternalReference, c.LinkedUtc
    FROM dbo.Chapters c
    INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = c.RevisionStatusID
    LEFT JOIN dbo.ChapterPurposeTypes cpt ON cpt.ChapterPurposeID = c.ChapterPurposeID
    WHERE c.BookID = @BookID AND c.IsArchived = 0
    ORDER BY c.SortOrder, c.ChapterNumber;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Chapter_Get
    @ChapterID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT c.ChapterID, c.BookID, c.ChapterNumber, c.ChapterTitle, c.POVCharacterID, c.Summary,
        c.ChapterPurposeID, cpt.PurposeName AS ChapterPurposeName,
        c.SortOrder, c.RevisionStatusID, rs.StatusName AS RevisionStatusName,
        c.CreatedDate, c.UpdatedDate, c.IsArchived, c.IsLinkedToManuscript,
        c.ManuscriptDocumentID, c.ExternalReference, c.LinkedUtc
    FROM dbo.Chapters c
    INNER JOIN dbo.RevisionStatuses rs ON rs.RevisionStatusID = c.RevisionStatusID
    LEFT JOIN dbo.ChapterPurposeTypes cpt ON cpt.ChapterPurposeID = c.ChapterPurposeID
    WHERE c.ChapterID = @ChapterID AND c.IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Chapter_Save
    @ChapterID int = NULL,
    @BookID int,
    @ChapterNumber decimal(10,2),
    @ChapterTitle nvarchar(200),
    @Summary nvarchar(max) = NULL,
    @ChapterPurposeID int = NULL,
    @RevisionStatusID int
AS
BEGIN
    SET NOCOUNT ON;
    IF @ChapterID IS NULL OR @ChapterID = 0
    BEGIN
        DECLARE @NextChapterOrder int = ISNULL((SELECT MAX(SortOrder) FROM dbo.Chapters WHERE BookID = @BookID), 0) + 10;
        INSERT dbo.Chapters (BookID, ChapterNumber, ChapterTitle, Summary, ChapterPurposeID, SortOrder, RevisionStatusID)
        VALUES (@BookID, @ChapterNumber, @ChapterTitle, @Summary, @ChapterPurposeID, @NextChapterOrder, @RevisionStatusID);
        SELECT CAST(SCOPE_IDENTITY() AS int) AS ChapterID;
        RETURN;
    END;

    UPDATE dbo.Chapters
    SET ChapterNumber = @ChapterNumber,
        ChapterTitle = @ChapterTitle,
        Summary = @Summary,
        ChapterPurposeID = @ChapterPurposeID,
        RevisionStatusID = @RevisionStatusID,
        UpdatedDate = SYSUTCDATETIME()
    WHERE ChapterID = @ChapterID;
    SELECT @ChapterID AS ChapterID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryIntelligenceImportCommit_UpdateChapterPlanning
    @ChapterID int,
    @Summary nvarchar(max) = NULL,
    @ChapterPurposeID int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Chapters
    SET Summary = COALESCE(NULLIF(LTRIM(RTRIM(@Summary)), N''), Summary),
        ChapterPurposeID = COALESCE(@ChapterPurposeID, ChapterPurposeID),
        UpdatedDate = SYSUTCDATETIME()
    WHERE ChapterID = @ChapterID;
END;
GO