SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF COL_LENGTH(N'dbo.Characters', N'ShowInQuickAddBar') IS NULL
BEGIN
    ALTER TABLE dbo.Characters
        ADD ShowInQuickAddBar bit NOT NULL
            CONSTRAINT DF_Characters_ShowInQuickAddBar DEFAULT 0 WITH VALUES;
END;
GO

IF COL_LENGTH(N'dbo.StoryAssets', N'ShowInQuickAddBar') IS NULL
BEGIN
    ALTER TABLE dbo.StoryAssets
        ADD ShowInQuickAddBar bit NOT NULL
            CONSTRAINT DF_StoryAssets_ShowInQuickAddBar DEFAULT 0 WITH VALUES;
END;
GO

IF COL_LENGTH(N'dbo.Locations', N'ShowInQuickAddBar') IS NULL
BEGIN
    ALTER TABLE dbo.Locations
        ADD ShowInQuickAddBar bit NOT NULL
            CONSTRAINT DF_Locations_ShowInQuickAddBar DEFAULT 0 WITH VALUES;
END;
GO

UPDATE dbo.Characters
SET ShowInQuickAddBar = 1
WHERE ShowInQuickAddBar = 0 AND ISNULL(CharacterImportance, 0) >= 6;
GO

UPDATE dbo.StoryAssets
SET ShowInQuickAddBar = 1
WHERE ShowInQuickAddBar = 0 AND (Importance >= 5 OR IsResolved = 0);
GO

WITH RankedCharacters AS
(
    SELECT CharacterID,
        ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY ISNULL(CharacterImportance, 0) DESC, CharacterName) AS RowNumber,
        SUM(CASE WHEN ShowInQuickAddBar = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ProjectID) AS FlaggedCount
    FROM dbo.Characters
    WHERE IsArchived = 0
)
UPDATE c
SET ShowInQuickAddBar = 1
FROM dbo.Characters c
INNER JOIN RankedCharacters rc ON rc.CharacterID = c.CharacterID
WHERE rc.FlaggedCount = 0 AND rc.RowNumber <= 10;
GO

WITH RankedAssets AS
(
    SELECT StoryAssetID,
        ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY Importance DESC, AssetName) AS RowNumber,
        SUM(CASE WHEN ShowInQuickAddBar = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ProjectID) AS FlaggedCount
    FROM dbo.StoryAssets
    WHERE IsArchived = 0
)
UPDATE sa
SET ShowInQuickAddBar = 1
FROM dbo.StoryAssets sa
INNER JOIN RankedAssets ra ON ra.StoryAssetID = sa.StoryAssetID
WHERE ra.FlaggedCount = 0 AND ra.RowNumber <= 10;
GO

WITH RankedLocations AS
(
    SELECT lp.LocationID,
        ROW_NUMBER() OVER (PARTITION BY lp.ProjectID ORDER BY lp.SortPath) AS RowNumber,
        SUM(CASE WHEN l.ShowInQuickAddBar = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY lp.ProjectID) AS FlaggedCount
    FROM dbo.LocationPaths lp
    INNER JOIN dbo.Locations l ON l.LocationID = lp.LocationID
)
UPDATE l
SET ShowInQuickAddBar = 1
FROM dbo.Locations l
INNER JOIN RankedLocations rl ON rl.LocationID = l.LocationID
WHERE rl.FlaggedCount = 0 AND rl.RowNumber <= 10;
GO

CREATE OR ALTER PROCEDURE dbo.Character_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
        Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
    FROM dbo.Characters
    WHERE ProjectID = @ProjectID AND IsArchived = 0
    ORDER BY CharacterName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Character_Get
    @CharacterID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
        Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
    FROM dbo.Characters
    WHERE CharacterID = @CharacterID AND IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Character_Save
    @CharacterID int = NULL,
    @ProjectID int,
    @CharacterName nvarchar(200),
    @ShortName nvarchar(100) = NULL,
    @Sex nvarchar(50) = NULL,
    @BirthDate date = NULL,
    @AgeAtSeriesStart int = NULL,
    @AgeReferenceSceneID int = NULL,
    @Height nvarchar(50) = NULL,
    @EyeColour nvarchar(50) = NULL,
    @CharacterImportance int = NULL,
    @ShowInQuickAddBar bit = 0,
    @DefaultDescription nvarchar(max) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF @CharacterID IS NULL OR @CharacterID = 0
    BEGIN
        INSERT dbo.Characters (ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID, Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription)
        VALUES (@ProjectID, @CharacterName, @ShortName, @Sex, @BirthDate, @AgeAtSeriesStart, @AgeReferenceSceneID, @Height, @EyeColour, @CharacterImportance, @ShowInQuickAddBar, @DefaultDescription);
        SET @CharacterID = CAST(SCOPE_IDENTITY() AS int);
    END
    ELSE
    BEGIN
        UPDATE dbo.Characters
        SET CharacterName = @CharacterName, ShortName = @ShortName, Sex = @Sex, BirthDate = @BirthDate,
            AgeAtSeriesStart = @AgeAtSeriesStart, AgeReferenceSceneID = @AgeReferenceSceneID,
            Height = @Height, EyeColour = @EyeColour, CharacterImportance = @CharacterImportance,
            ShowInQuickAddBar = @ShowInQuickAddBar, DefaultDescription = @DefaultDescription,
            UpdatedDate = SYSUTCDATETIME()
        WHERE CharacterID = @CharacterID;
    END
    SELECT @CharacterID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.CharacterTimeline_GetByProject
    @ProjectID int,
    @BookID int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #TimelineScenes
    (
        SceneID int NOT NULL PRIMARY KEY,
        SceneNumber decimal(9,2) NOT NULL,
        SceneTitle nvarchar(200) NOT NULL,
        ChapterNumber decimal(9,2) NOT NULL,
        BookTitle nvarchar(200) NOT NULL,
        BookSortOrder int NOT NULL,
        ChapterSortOrder int NOT NULL,
        SceneSortOrder int NOT NULL
    );

    INSERT #TimelineScenes (SceneID, SceneNumber, SceneTitle, ChapterNumber, BookTitle, BookSortOrder, ChapterSortOrder, SceneSortOrder)
    SELECT s.SceneID, s.SceneNumber, s.SceneTitle, ch.ChapterNumber, b.BookTitle, b.SortOrder, ch.SortOrder, s.SortOrder
    FROM dbo.Books b
    INNER JOIN dbo.Chapters ch ON ch.BookID = b.BookID
    INNER JOIN dbo.Scenes s ON s.ChapterID = ch.ChapterID
    WHERE b.ProjectID = @ProjectID
        AND b.IsArchived = 0
        AND ch.IsArchived = 0
        AND s.IsArchived = 0
        AND (@BookID IS NULL OR b.BookID = @BookID);

    CREATE TABLE #TimelineCharacters
    (
        CharacterID int NOT NULL PRIMARY KEY,
        ProjectID int NOT NULL,
        CharacterName nvarchar(200) NOT NULL,
        ShortName nvarchar(100) NULL,
        Sex nvarchar(50) NULL,
        BirthDate date NULL,
        AgeAtSeriesStart int NULL,
        AgeReferenceSceneID int NULL,
        Height nvarchar(50) NULL,
        EyeColour nvarchar(50) NULL,
        CharacterImportance int NULL,
        ShowInQuickAddBar bit NOT NULL,
        DefaultDescription nvarchar(max) NULL,
        CreatedDate datetime2 NOT NULL,
        UpdatedDate datetime2 NOT NULL,
        IsArchived bit NOT NULL
    );

    INSERT #TimelineCharacters (CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID, Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived)
    SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
        Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
    FROM dbo.Characters
    WHERE ProjectID = @ProjectID AND IsArchived = 0;

    CREATE TABLE #ProjectLocationPaths
    (
        LocationID int NOT NULL PRIMARY KEY,
        LocationName nvarchar(200) NOT NULL,
        LocationPath nvarchar(max) NOT NULL
    );

    ;WITH ProjectLocationTree AS
    (
        SELECT LocationID, LocationName, CAST(LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations
        WHERE ProjectID = @ProjectID AND ParentLocationID IS NULL AND IsArchived = 0

        UNION ALL

        SELECT child.LocationID, child.LocationName, CAST(parent.LocationPath + N' > ' + child.LocationName AS nvarchar(max)) AS LocationPath
        FROM dbo.Locations child
        INNER JOIN ProjectLocationTree parent ON parent.LocationID = child.ParentLocationID
        WHERE child.ProjectID = @ProjectID AND child.IsArchived = 0
    )
    INSERT #ProjectLocationPaths (LocationID, LocationName, LocationPath)
    SELECT LocationID, LocationName, LocationPath
    FROM ProjectLocationTree;

    SELECT CharacterID, ProjectID, CharacterName, ShortName, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID,
        Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, CreatedDate, UpdatedDate, IsArchived
    FROM #TimelineCharacters
    ORDER BY ISNULL(CharacterImportance, 0) DESC, CharacterName;

    SELECT sc.SceneCharacterID, sc.SceneID, sc.CharacterID, c.ProjectID, c.CharacterName, c.ShortName, c.BirthDate, c.AgeAtSeriesStart,
        sc.RoleInSceneTypeID, role.TypeName AS RoleInSceneTypeName, sc.PresenceTypeID, presence.TypeName AS PresenceTypeName,
        sc.LocationID, location.LocationName, location.LocationPath, sc.EntryLocationID, entry.LocationName AS EntryLocationName,
        sc.ExitLocationID, exitLocation.LocationName AS ExitLocationName, sc.AppearanceNotes, sc.OutfitDescription,
        sc.PhysicalCondition, sc.EmotionalState, sc.KnowledgeNotes, s.SceneNumber, s.SceneTitle, s.ChapterNumber, s.BookTitle,
        sc.CreatedDate, sc.UpdatedDate
    FROM #TimelineScenes s
    INNER JOIN dbo.SceneCharacters sc ON sc.SceneID = s.SceneID
    INNER JOIN #TimelineCharacters c ON c.CharacterID = sc.CharacterID
    LEFT JOIN dbo.CharacterRoleInSceneTypes role ON role.CharacterRoleInSceneTypeID = sc.RoleInSceneTypeID
    LEFT JOIN dbo.PresenceTypes presence ON presence.PresenceTypeID = sc.PresenceTypeID
    LEFT JOIN #ProjectLocationPaths location ON location.LocationID = sc.LocationID
    LEFT JOIN #ProjectLocationPaths entry ON entry.LocationID = sc.EntryLocationID
    LEFT JOIN #ProjectLocationPaths exitLocation ON exitLocation.LocationID = sc.ExitLocationID
    ORDER BY s.BookSortOrder, s.ChapterSortOrder, s.SceneSortOrder, c.CharacterName;
END;
GO

CREATE OR ALTER VIEW dbo.LocationPaths
AS
WITH LocationTree AS
(
    SELECT LocationID, ProjectID, ParentLocationID, LocationName, LocationTypeID, Description, ShowInQuickAddBar,
        CAST(LocationName AS nvarchar(max)) AS LocationPath,
        CAST(RIGHT('000000' + CAST(LocationID AS varchar(6)), 6) AS nvarchar(max)) AS SortPath,
        0 AS Depth
    FROM dbo.Locations
    WHERE ParentLocationID IS NULL AND IsArchived = 0
    UNION ALL
    SELECT child.LocationID, child.ProjectID, child.ParentLocationID, child.LocationName, child.LocationTypeID, child.Description, child.ShowInQuickAddBar,
        CAST(parent.LocationPath + N' > ' + child.LocationName AS nvarchar(max)),
        CAST(parent.SortPath + N'.' + RIGHT('000000' + CAST(child.LocationID AS varchar(6)), 6) AS nvarchar(max)),
        parent.Depth + 1
    FROM dbo.Locations child
    INNER JOIN LocationTree parent ON parent.LocationID = child.ParentLocationID
    WHERE child.IsArchived = 0
)
SELECT LocationID, ProjectID, ParentLocationID, LocationName, LocationTypeID, Description, ShowInQuickAddBar, LocationPath, SortPath, Depth
FROM LocationTree;
GO

CREATE OR ALTER PROCEDURE dbo.Location_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT lp.LocationID, lp.ProjectID, lp.ParentLocationID, lp.LocationName, lp.LocationTypeID, lt.TypeName AS LocationTypeName,
        lp.Description, lp.ShowInQuickAddBar, lp.LocationPath, lp.Depth, l.CreatedDate, l.UpdatedDate, l.IsArchived
    FROM dbo.LocationPaths lp
    INNER JOIN dbo.Locations l ON l.LocationID = lp.LocationID
    LEFT JOIN dbo.LocationTypes lt ON lt.LocationTypeID = lp.LocationTypeID
    WHERE lp.ProjectID = @ProjectID
    ORDER BY lp.SortPath;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Location_Get
    @LocationID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT lp.LocationID, lp.ProjectID, lp.ParentLocationID, parent.LocationName AS ParentLocationName,
        lp.LocationName, lp.LocationTypeID, lt.TypeName AS LocationTypeName, lp.Description, lp.ShowInQuickAddBar, lp.LocationPath, lp.Depth,
        l.CreatedDate, l.UpdatedDate, l.IsArchived
    FROM dbo.LocationPaths lp
    INNER JOIN dbo.Locations l ON l.LocationID = lp.LocationID
    LEFT JOIN dbo.Locations parent ON parent.LocationID = lp.ParentLocationID
    LEFT JOIN dbo.LocationTypes lt ON lt.LocationTypeID = lp.LocationTypeID
    WHERE lp.LocationID = @LocationID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Location_Save
    @LocationID int = NULL,
    @ProjectID int,
    @ParentLocationID int = NULL,
    @LocationName nvarchar(200),
    @LocationTypeID int = NULL,
    @Description nvarchar(max) = NULL,
    @ShowInQuickAddBar bit = 0
AS
BEGIN
    SET NOCOUNT ON;
    IF @LocationID IS NULL OR @LocationID = 0
    BEGIN
        INSERT dbo.Locations (ProjectID, ParentLocationID, LocationName, LocationTypeID, Description, ShowInQuickAddBar)
        VALUES (@ProjectID, @ParentLocationID, @LocationName, @LocationTypeID, @Description, @ShowInQuickAddBar);
        SET @LocationID = CAST(SCOPE_IDENTITY() AS int);
    END
    ELSE
    BEGIN
        UPDATE dbo.Locations
        SET ParentLocationID = @ParentLocationID, LocationName = @LocationName, LocationTypeID = @LocationTypeID,
            Description = @Description, ShowInQuickAddBar = @ShowInQuickAddBar, UpdatedDate = SYSUTCDATETIME()
        WHERE LocationID = @LocationID;
    END
    SELECT @LocationID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryAsset_Save
    @StoryAssetID int = NULL, @ProjectID int, @AssetName nvarchar(200), @AssetKindID int, @Description nvarchar(max) = NULL,
    @Importance int, @CurrentStateID int = NULL, @CurrentLocationID int = NULL, @IsResolved bit, @ShowInQuickAddBar bit = 0
AS
BEGIN
    SET NOCOUNT ON;
    IF @StoryAssetID IS NULL OR @StoryAssetID = 0
    BEGIN
        INSERT dbo.StoryAssets (ProjectID, AssetName, AssetKindID, Description, Importance, CurrentStateID, CurrentLocationID, IsResolved, ShowInQuickAddBar)
        VALUES (@ProjectID, @AssetName, @AssetKindID, @Description, @Importance, @CurrentStateID, @CurrentLocationID, @IsResolved, @ShowInQuickAddBar);
        SET @StoryAssetID = CAST(SCOPE_IDENTITY() AS int);
    END
    ELSE
    BEGIN
        UPDATE dbo.StoryAssets
        SET AssetName = @AssetName, AssetKindID = @AssetKindID, Description = @Description, Importance = @Importance,
            CurrentStateID = @CurrentStateID, CurrentLocationID = @CurrentLocationID, IsResolved = @IsResolved,
            ShowInQuickAddBar = @ShowInQuickAddBar, UpdatedDate = SYSUTCDATETIME()
        WHERE StoryAssetID = @StoryAssetID;
    END
    SELECT @StoryAssetID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryAsset_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
        sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
        location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
        sa.IsResolved, sa.ShowInQuickAddBar, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
    FROM dbo.StoryAssets sa
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
    LEFT JOIN dbo.LocationPaths location ON location.LocationID = sa.CurrentLocationID
    WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0
    ORDER BY sa.Importance DESC, sa.AssetName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.StoryAsset_Get
    @StoryAssetID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
        sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
        location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
        sa.IsResolved, sa.ShowInQuickAddBar, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
    FROM dbo.StoryAssets sa
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
    LEFT JOIN dbo.LocationPaths location ON location.LocationID = sa.CurrentLocationID
    WHERE sa.StoryAssetID = @StoryAssetID AND sa.IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.AssetTimeline_GetByProject
    @ProjectID int,
    @BookID int = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT sa.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName, sa.Description, sa.Importance,
        sa.CurrentStateID, ast.StateName AS CurrentStateName, sa.CurrentLocationID,
        location.LocationName AS CurrentLocationName, location.LocationPath AS CurrentLocationPath,
        sa.IsResolved, sa.ShowInQuickAddBar, sa.CreatedDate, sa.UpdatedDate, sa.IsArchived
    FROM dbo.StoryAssets sa
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    LEFT JOIN dbo.AssetStates ast ON ast.AssetStateID = sa.CurrentStateID
    LEFT JOIN dbo.LocationPaths location ON location.LocationID = sa.CurrentLocationID
    WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND (sa.Importance >= 5 OR sa.IsResolved = 0)
    ORDER BY sa.Importance DESC, sa.AssetName;

    SELECT ae.AssetEventID, ae.StoryAssetID, sa.ProjectID, sa.AssetName, sa.AssetKindID, ak.KindName,
        ae.SceneID, ae.AssetEventTypeID, aet.TypeName AS AssetEventTypeName, aet.MarkerText,
        ae.FromStateID, fs.StateName AS FromStateName, ae.ToStateID, ts.StateName AS ToStateName,
        ae.EventTitle, ae.EventDescription, ae.CreatedDate, ae.UpdatedDate
    FROM dbo.AssetEvents ae
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
    INNER JOIN dbo.AssetKinds ak ON ak.AssetKindID = sa.AssetKindID
    INNER JOIN dbo.AssetEventTypes aet ON aet.AssetEventTypeID = ae.AssetEventTypeID
    INNER JOIN dbo.Scenes s ON s.SceneID = ae.SceneID
    INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
    INNER JOIN dbo.Books b ON b.BookID = c.BookID
    LEFT JOIN dbo.AssetStates fs ON fs.AssetStateID = ae.FromStateID
    LEFT JOIN dbo.AssetStates ts ON ts.AssetStateID = ae.ToStateID
    WHERE sa.ProjectID = @ProjectID AND sa.IsArchived = 0 AND (@BookID IS NULL OR b.BookID = @BookID)
    ORDER BY b.SortOrder, c.SortOrder, s.SortOrder, sa.AssetName;
END;
GO