SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.CharacterAliases', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.CharacterAliases
    (
        CharacterAliasID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_CharacterAliases PRIMARY KEY,
        CharacterID int NOT NULL,
        Alias nvarchar(200) NOT NULL,
        SortOrder int NOT NULL CONSTRAINT DF_CharacterAliases_SortOrder DEFAULT (0),
        CreatedUtc datetime2 NOT NULL CONSTRAINT DF_CharacterAliases_CreatedUtc DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_CharacterAliases_Characters FOREIGN KEY (CharacterID) REFERENCES dbo.Characters(CharacterID) ON DELETE CASCADE
    );
END;
GO

IF OBJECT_ID(N'dbo.AssetAliases', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.AssetAliases
    (
        AssetAliasID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssetAliases PRIMARY KEY,
        StoryAssetID int NOT NULL,
        Alias nvarchar(200) NOT NULL,
        SortOrder int NOT NULL CONSTRAINT DF_AssetAliases_SortOrder DEFAULT (0),
        CreatedUtc datetime2 NOT NULL CONSTRAINT DF_AssetAliases_CreatedUtc DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_AssetAliases_StoryAssets FOREIGN KEY (StoryAssetID) REFERENCES dbo.StoryAssets(StoryAssetID) ON DELETE CASCADE
    );
END;
GO

IF OBJECT_ID(N'dbo.LocationAliases', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.LocationAliases
    (
        LocationAliasID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_LocationAliases PRIMARY KEY,
        LocationID int NOT NULL,
        Alias nvarchar(200) NOT NULL,
        SortOrder int NOT NULL CONSTRAINT DF_LocationAliases_SortOrder DEFAULT (0),
        CreatedUtc datetime2 NOT NULL CONSTRAINT DF_LocationAliases_CreatedUtc DEFAULT SYSUTCDATETIME(),
        CONSTRAINT FK_LocationAliases_Locations FOREIGN KEY (LocationID) REFERENCES dbo.Locations(LocationID) ON DELETE CASCADE
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_CharacterAliases_CharacterAlias' AND object_id = OBJECT_ID(N'dbo.CharacterAliases'))
    CREATE UNIQUE INDEX UX_CharacterAliases_CharacterAlias ON dbo.CharacterAliases(CharacterID, Alias);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_CharacterAliases_CharacterSort' AND object_id = OBJECT_ID(N'dbo.CharacterAliases'))
    CREATE INDEX IX_CharacterAliases_CharacterSort ON dbo.CharacterAliases(CharacterID, SortOrder, CharacterAliasID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_AssetAliases_AssetAlias' AND object_id = OBJECT_ID(N'dbo.AssetAliases'))
    CREATE UNIQUE INDEX UX_AssetAliases_AssetAlias ON dbo.AssetAliases(StoryAssetID, Alias);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_AssetAliases_AssetSort' AND object_id = OBJECT_ID(N'dbo.AssetAliases'))
    CREATE INDEX IX_AssetAliases_AssetSort ON dbo.AssetAliases(StoryAssetID, SortOrder, AssetAliasID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_LocationAliases_LocationAlias' AND object_id = OBJECT_ID(N'dbo.LocationAliases'))
    CREATE UNIQUE INDEX UX_LocationAliases_LocationAlias ON dbo.LocationAliases(LocationID, Alias);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_LocationAliases_LocationSort' AND object_id = OBJECT_ID(N'dbo.LocationAliases'))
    CREATE INDEX IX_LocationAliases_LocationSort ON dbo.LocationAliases(LocationID, SortOrder, LocationAliasID);
GO

INSERT dbo.CharacterAliases (CharacterID, Alias, SortOrder)
SELECT c.CharacterID, LTRIM(RTRIM(c.ShortName)), 10
FROM dbo.Characters c
WHERE NULLIF(LTRIM(RTRIM(c.ShortName)), N'') IS NOT NULL
    AND NOT EXISTS
    (
        SELECT 1
        FROM dbo.CharacterAliases ca
        WHERE ca.CharacterID = c.CharacterID
            AND ca.Alias = LTRIM(RTRIM(c.ShortName))
    );
GO

IF COL_LENGTH(N'dbo.Books', N'UsesExplicitScenes') IS NULL
    ALTER TABLE dbo.Books ADD UsesExplicitScenes bit NOT NULL CONSTRAINT DF_Books_UsesExplicitScenes DEFAULT (0);
GO

CREATE OR ALTER PROCEDURE dbo.CharacterAlias_ListByCharacter
    @CharacterID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT CharacterAliasID, CharacterID, Alias, SortOrder, CreatedUtc
    FROM dbo.CharacterAliases
    WHERE CharacterID = @CharacterID
    ORDER BY SortOrder, CharacterAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.CharacterAlias_Add
    @CharacterID int,
    @Alias nvarchar(200),
    @SortOrder int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SET @Alias = LTRIM(RTRIM(@Alias));
    IF NULLIF(@Alias, N'') IS NULL
        THROW 51000, 'Enter an alias before saving.', 1;

    IF @SortOrder IS NULL
        SELECT @SortOrder = ISNULL(MAX(SortOrder), 0) + 10 FROM dbo.CharacterAliases WHERE CharacterID = @CharacterID;

    INSERT dbo.CharacterAliases (CharacterID, Alias, SortOrder)
    VALUES (@CharacterID, @Alias, @SortOrder);

    SELECT CAST(SCOPE_IDENTITY() AS int) AS CharacterAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.CharacterAlias_Update
    @CharacterAliasID int,
    @Alias nvarchar(200),
    @SortOrder int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SET @Alias = LTRIM(RTRIM(@Alias));
    IF NULLIF(@Alias, N'') IS NULL
        THROW 51000, 'Enter an alias before saving.', 1;

    UPDATE dbo.CharacterAliases
    SET Alias = @Alias,
        SortOrder = COALESCE(@SortOrder, SortOrder)
    WHERE CharacterAliasID = @CharacterAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.CharacterAlias_Delete
    @CharacterAliasID int
AS
BEGIN
    SET NOCOUNT ON;
    DELETE dbo.CharacterAliases WHERE CharacterAliasID = @CharacterAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.AssetAlias_ListByAsset
    @StoryAssetID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT AssetAliasID, StoryAssetID, Alias, SortOrder, CreatedUtc
    FROM dbo.AssetAliases
    WHERE StoryAssetID = @StoryAssetID
    ORDER BY SortOrder, AssetAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.AssetAlias_Add
    @StoryAssetID int,
    @Alias nvarchar(200),
    @SortOrder int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SET @Alias = LTRIM(RTRIM(@Alias));
    IF NULLIF(@Alias, N'') IS NULL
        THROW 51000, 'Enter an alias before saving.', 1;

    IF @SortOrder IS NULL
        SELECT @SortOrder = ISNULL(MAX(SortOrder), 0) + 10 FROM dbo.AssetAliases WHERE StoryAssetID = @StoryAssetID;

    INSERT dbo.AssetAliases (StoryAssetID, Alias, SortOrder)
    VALUES (@StoryAssetID, @Alias, @SortOrder);

    SELECT CAST(SCOPE_IDENTITY() AS int) AS AssetAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.AssetAlias_Update
    @AssetAliasID int,
    @Alias nvarchar(200),
    @SortOrder int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SET @Alias = LTRIM(RTRIM(@Alias));
    IF NULLIF(@Alias, N'') IS NULL
        THROW 51000, 'Enter an alias before saving.', 1;

    UPDATE dbo.AssetAliases
    SET Alias = @Alias,
        SortOrder = COALESCE(@SortOrder, SortOrder)
    WHERE AssetAliasID = @AssetAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.AssetAlias_Delete
    @AssetAliasID int
AS
BEGIN
    SET NOCOUNT ON;
    DELETE dbo.AssetAliases WHERE AssetAliasID = @AssetAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.LocationAlias_ListByLocation
    @LocationID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT LocationAliasID, LocationID, Alias, SortOrder, CreatedUtc
    FROM dbo.LocationAliases
    WHERE LocationID = @LocationID
    ORDER BY SortOrder, LocationAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.LocationAlias_Add
    @LocationID int,
    @Alias nvarchar(200),
    @SortOrder int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SET @Alias = LTRIM(RTRIM(@Alias));
    IF NULLIF(@Alias, N'') IS NULL
        THROW 51000, 'Enter an alias before saving.', 1;

    IF @SortOrder IS NULL
        SELECT @SortOrder = ISNULL(MAX(SortOrder), 0) + 10 FROM dbo.LocationAliases WHERE LocationID = @LocationID;

    INSERT dbo.LocationAliases (LocationID, Alias, SortOrder)
    VALUES (@LocationID, @Alias, @SortOrder);

    SELECT CAST(SCOPE_IDENTITY() AS int) AS LocationAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.LocationAlias_Update
    @LocationAliasID int,
    @Alias nvarchar(200),
    @SortOrder int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SET @Alias = LTRIM(RTRIM(@Alias));
    IF NULLIF(@Alias, N'') IS NULL
        THROW 51000, 'Enter an alias before saving.', 1;

    UPDATE dbo.LocationAliases
    SET Alias = @Alias,
        SortOrder = COALESCE(@SortOrder, SortOrder)
    WHERE LocationAliasID = @LocationAliasID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.LocationAlias_Delete
    @LocationAliasID int
AS
BEGIN
    SET NOCOUNT ON;
    DELETE dbo.LocationAliases WHERE LocationAliasID = @LocationAliasID;
END;
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.UsesExplicitScenes, 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_ListByProjects
    @ProjectIDs nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH ProjectIDs AS
    (
        SELECT DISTINCT TRY_CONVERT(int, value) AS ProjectID
        FROM STRING_SPLIT(COALESCE(@ProjectIDs, N''), N',')
        WHERE TRY_CONVERT(int, value) IS NOT NULL
    )
    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.UsesExplicitScenes, 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
    INNER JOIN ProjectIDs ids ON ids.ProjectID = b.ProjectID
    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.IsArchived = 0
    ORDER BY b.ProjectID, 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.UsesExplicitScenes, 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_Save
    @BookID int = NULL,
    @ProjectID int,
    @BookTitle nvarchar(200),
    @Subtitle nvarchar(200) = NULL,
    @Tagline nvarchar(300) = NULL,
    @ShortDescription nvarchar(max) = NULL,
    @Status nvarchar(50) = N'Planning',
    @BookNumber int,
    @Description nvarchar(max) = NULL,
    @TargetWordCount int = NULL,
    @WritingStartDate date = NULL,
    @TargetCompletionDate date = NULL,
    @PublicationDate date = NULL,
    @UsesExplicitScenes bit = 0
AS
BEGIN
    SET NOCOUNT ON;

    IF @TargetWordCount IS NOT NULL AND @TargetWordCount <= 0
        THROW 51000, 'Target word count must be greater than zero.', 1;

    IF NULLIF(LTRIM(RTRIM(@Status)), N'') IS NULL
        SET @Status = N'Planning';

    IF @BookID IS NULL OR @BookID = 0
    BEGIN
        DECLARE @NextBookOrder int = ISNULL((SELECT MAX(SortOrder) FROM dbo.Books WHERE ProjectID = @ProjectID), 0) + 10;

        INSERT dbo.Books
        (
            ProjectID, BookTitle, Subtitle, Tagline, ShortDescription, Status, BookNumber, Description,
            TargetWordCount, WritingStartDate, TargetCompletionDate, PublicationDate, UsesExplicitScenes, SortOrder
        )
        VALUES
        (
            @ProjectID, @BookTitle, @Subtitle, @Tagline, @ShortDescription, @Status, @BookNumber, @Description,
            @TargetWordCount, @WritingStartDate, @TargetCompletionDate, @PublicationDate, @UsesExplicitScenes, @NextBookOrder
        );

        SELECT CAST(SCOPE_IDENTITY() AS int) AS BookID;
        RETURN;
    END;

    UPDATE dbo.Books
    SET BookTitle = @BookTitle,
        Subtitle = @Subtitle,
        Tagline = @Tagline,
        ShortDescription = @ShortDescription,
        Status = @Status,
        BookNumber = @BookNumber,
        Description = @Description,
        TargetWordCount = @TargetWordCount,
        WritingStartDate = @WritingStartDate,
        TargetCompletionDate = @TargetCompletionDate,
        PublicationDate = @PublicationDate,
        UsesExplicitScenes = @UsesExplicitScenes,
        UpdatedDate = SYSUTCDATETIME()
    WHERE BookID = @BookID;

    SELECT @BookID AS BookID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Chapter_Save
    @ChapterID int = NULL,
    @BookID int,
    @ChapterNumber decimal(9,2),
    @ChapterTitle nvarchar(200),
    @Summary nvarchar(max) = 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;
        DECLARE @NewChapterID int;

        INSERT dbo.Chapters (BookID, ChapterNumber, ChapterTitle, Summary, SortOrder, RevisionStatusID)
        VALUES (@BookID, @ChapterNumber, @ChapterTitle, @Summary, @NextChapterOrder, @RevisionStatusID);

        SET @NewChapterID = CAST(SCOPE_IDENTITY() AS int);

        SELECT @NewChapterID AS ChapterID;
        RETURN;
    END;

    UPDATE dbo.Chapters
    SET ChapterNumber = @ChapterNumber,
        ChapterTitle = @ChapterTitle,
        Summary = @Summary,
        RevisionStatusID = @RevisionStatusID,
        UpdatedDate = SYSUTCDATETIME()
    WHERE ChapterID = @ChapterID;

    SELECT @ChapterID AS ChapterID;
END;
GO

CREATE OR ALTER TRIGGER dbo.TR_Chapters_CreateDefaultScene
ON dbo.Chapters
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DefaultTimeModeID int;
    DECLARE @DefaultTimeConfidenceID int;

    SELECT TOP (1) @DefaultTimeModeID = TimeModeID
    FROM dbo.TimeModes
    ORDER BY CASE WHEN TimeModeName = N'Unknown' THEN 0 ELSE 1 END, SortOrder, TimeModeID;

    SELECT TOP (1) @DefaultTimeConfidenceID = TimeConfidenceID
    FROM dbo.TimeConfidences
    ORDER BY CASE WHEN TimeConfidenceName = N'Unknown' THEN 0 ELSE 1 END, SortOrder, TimeConfidenceID;

    INSERT dbo.Scenes
    (
        ChapterID, SceneNumber, SceneTitle, Summary, SortOrder, TimeModeID, TimeConfidenceID, RevisionStatusID
    )
    SELECT i.ChapterID, 1, N'Scene 1', NULL, 10, @DefaultTimeModeID, @DefaultTimeConfidenceID, i.RevisionStatusID
    FROM inserted i
    WHERE i.IsArchived = 0
        AND NOT EXISTS
        (
            SELECT 1
            FROM dbo.Scenes s
            WHERE s.ChapterID = i.ChapterID
                AND s.IsArchived = 0
        );
END;
GO

DECLARE @MigrationTimeModeID int;
DECLARE @MigrationTimeConfidenceID int;

SELECT TOP (1) @MigrationTimeModeID = TimeModeID
FROM dbo.TimeModes
ORDER BY CASE WHEN TimeModeName = N'Unknown' THEN 0 ELSE 1 END, SortOrder, TimeModeID;

SELECT TOP (1) @MigrationTimeConfidenceID = TimeConfidenceID
FROM dbo.TimeConfidences
ORDER BY CASE WHEN TimeConfidenceName = N'Unknown' THEN 0 ELSE 1 END, SortOrder, TimeConfidenceID;

INSERT dbo.Scenes
(
    ChapterID, SceneNumber, SceneTitle, Summary, SortOrder, TimeModeID, TimeConfidenceID, RevisionStatusID
)
SELECT c.ChapterID, 1, N'Scene 1', NULL, 10, @MigrationTimeModeID, @MigrationTimeConfidenceID, c.RevisionStatusID
FROM dbo.Chapters c
WHERE c.IsArchived = 0
    AND NOT EXISTS
    (
        SELECT 1
        FROM dbo.Scenes s
        WHERE s.ChapterID = c.ChapterID
            AND s.IsArchived = 0
    );
GO

CREATE OR ALTER PROCEDURE dbo.Scene_Archive
    @SceneID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ChapterID int;
    SELECT @ChapterID = ChapterID
    FROM dbo.Scenes
    WHERE SceneID = @SceneID AND IsArchived = 0;

    IF @ChapterID IS NULL
        RETURN;

    IF (SELECT COUNT(*) FROM dbo.Scenes WHERE ChapterID = @ChapterID AND IsArchived = 0) <= 1
        THROW 51000, 'A chapter must contain at least one scene.', 1;

    UPDATE dbo.Scenes
    SET IsArchived = 1,
        ArchivedDate = SYSUTCDATETIME(),
        ArchivedReason = NULL,
        UpdatedDate = SYSUTCDATETIME()
    WHERE SceneID = @SceneID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Archive_Entity
    @EntityType nvarchar(80),
    @EntityID int,
    @ArchivedReason nvarchar(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF @EntityType = N'Project' UPDATE dbo.Projects SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE ProjectID = @EntityID;
    ELSE IF @EntityType = N'Book' UPDATE dbo.Books SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE BookID = @EntityID;
    ELSE IF @EntityType = N'Chapter' UPDATE dbo.Chapters SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE ChapterID = @EntityID;
    ELSE IF @EntityType = N'Scene'
    BEGIN
        DECLARE @ChapterID int;
        SELECT @ChapterID = ChapterID FROM dbo.Scenes WHERE SceneID = @EntityID AND IsArchived = 0;

        IF @ChapterID IS NULL
            RETURN;

        IF (SELECT COUNT(*) FROM dbo.Scenes WHERE ChapterID = @ChapterID AND IsArchived = 0) <= 1
            THROW 51000, 'A chapter must contain at least one scene.', 1;

        UPDATE dbo.Scenes SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE SceneID = @EntityID;
    END
    ELSE IF @EntityType = N'PlotLine' UPDATE dbo.PlotLines SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE PlotLineID = @EntityID;
    ELSE IF @EntityType = N'PlotThread' UPDATE dbo.PlotThreads SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE PlotThreadID = @EntityID;
    ELSE IF @EntityType = N'StoryAsset' UPDATE dbo.StoryAssets SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE StoryAssetID = @EntityID;
    ELSE IF @EntityType = N'Character' UPDATE dbo.Characters SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE CharacterID = @EntityID;
    ELSE IF @EntityType = N'Location' UPDATE dbo.Locations SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE LocationID = @EntityID;
    ELSE IF @EntityType = N'Scenario' UPDATE dbo.Scenarios SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE ScenarioID = @EntityID;
    ELSE IF @EntityType = N'Relationship' UPDATE dbo.CharacterRelationships SET IsArchived = 1, ArchivedDate = SYSUTCDATETIME(), ArchivedReason = @ArchivedReason, UpdatedDate = SYSUTCDATETIME() WHERE CharacterRelationshipID = @EntityID;
END;
GO