PlotDirector/PlotLine/Sql/086_Phase14A_CharacterGalleriesAvatars.sql

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

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

IF COL_LENGTH(N'dbo.Characters', N'AvatarImagePath') IS NULL
    ALTER TABLE dbo.Characters ADD AvatarImagePath nvarchar(500) NULL;
GO

IF COL_LENGTH(N'dbo.Characters', N'AvatarThumbnailPath') IS NULL
    ALTER TABLE dbo.Characters ADD AvatarThumbnailPath nvarchar(500) NULL;
GO

IF OBJECT_ID(N'dbo.CharacterImages', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.CharacterImages
    (
        CharacterImageID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_CharacterImages PRIMARY KEY,
        CharacterID int NOT NULL,
        ProjectID int NOT NULL,
        ImagePath nvarchar(500) NOT NULL,
        ThumbnailPath nvarchar(500) NOT NULL,
        Caption nvarchar(500) NULL,
        SortOrder int NOT NULL CONSTRAINT DF_CharacterImages_SortOrder DEFAULT (0),
        UploadedDateUTC datetime2 NOT NULL CONSTRAINT DF_CharacterImages_UploadedDateUTC DEFAULT (SYSUTCDATETIME()),
        IsDeleted bit NOT NULL CONSTRAINT DF_CharacterImages_IsDeleted DEFAULT (0),
        DeletedDateUTC datetime2 NULL,
        CONSTRAINT FK_CharacterImages_Characters FOREIGN KEY (CharacterID) REFERENCES dbo.Characters(CharacterID),
        CONSTRAINT FK_CharacterImages_Projects FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID)
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_CharacterImages_CharacterID_IsDeleted_SortOrder' AND object_id = OBJECT_ID(N'dbo.CharacterImages'))
    CREATE INDEX IX_CharacterImages_CharacterID_IsDeleted_SortOrder ON dbo.CharacterImages(CharacterID, IsDeleted, SortOrder, UploadedDateUTC, CharacterImageID);
GO

INSERT dbo.CharacterImages (CharacterID, ProjectID, ImagePath, ThumbnailPath, Caption, SortOrder, UploadedDateUTC)
SELECT c.CharacterID, c.ProjectID, c.ImagePath, COALESCE(c.ThumbnailPath, c.ImagePath), N'Original character image', 0, COALESCE(c.UpdatedDate, SYSUTCDATETIME())
FROM dbo.Characters c
WHERE NULLIF(LTRIM(RTRIM(c.ImagePath)), N'') IS NOT NULL
  AND NOT EXISTS
  (
      SELECT 1
      FROM dbo.CharacterImages ci
      WHERE ci.CharacterID = c.CharacterID
        AND ci.ImagePath = c.ImagePath
        AND ci.IsDeleted = 0
  );
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, ImagePath, ThumbnailPath,
        AvatarSourceCharacterImageID, AvatarImagePath, AvatarThumbnailPath,
        CreatedDate, UpdatedDate, IsArchived, ArchivedDate, ArchivedReason
    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, ImagePath, ThumbnailPath,
        AvatarSourceCharacterImageID, AvatarImagePath, AvatarThumbnailPath,
        CreatedDate, UpdatedDate, IsArchived, ArchivedDate, ArchivedReason
    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,
    @ImagePath nvarchar(500) = NULL,
    @ThumbnailPath nvarchar(500) = 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, ImagePath, ThumbnailPath)
        VALUES (@ProjectID, @CharacterName, @ShortName, @Sex, @BirthDate, @AgeAtSeriesStart, @AgeReferenceSceneID, @Height, @EyeColour, @CharacterImportance, @ShowInQuickAddBar, @DefaultDescription, @ImagePath, @ThumbnailPath);
        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,
            ImagePath = @ImagePath, ThumbnailPath = @ThumbnailPath,
            UpdatedDate = SYSUTCDATETIME()
        WHERE CharacterID = @CharacterID;
    END
    SELECT @CharacterID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Character_UpdateImage
    @CharacterID int,
    @ImagePath nvarchar(500) = NULL,
    @ThumbnailPath nvarchar(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Characters
    SET ImagePath = @ImagePath,
        ThumbnailPath = @ThumbnailPath,
        UpdatedDate = SYSUTCDATETIME()
    WHERE CharacterID = @CharacterID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Character_UpdateAvatar
    @CharacterID int,
    @AvatarSourceCharacterImageID int = NULL,
    @AvatarImagePath nvarchar(500) = NULL,
    @AvatarThumbnailPath nvarchar(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Characters
    SET AvatarSourceCharacterImageID = @AvatarSourceCharacterImageID,
        AvatarImagePath = @AvatarImagePath,
        AvatarThumbnailPath = @AvatarThumbnailPath,
        UpdatedDate = SYSUTCDATETIME()
    WHERE CharacterID = @CharacterID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneCharacter_ListByScene
    @SceneID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT sc.SceneCharacterID, sc.SceneID, sc.CharacterID, c.ProjectID,
        CASE WHEN c.IsArchived = 1 THEN CONCAT(c.CharacterName, N' (archived)') ELSE c.CharacterName END AS CharacterName,
        c.ShortName,
        COALESCE(c.AvatarImagePath, c.ImagePath) AS CharacterImagePath,
        COALESCE(c.AvatarThumbnailPath, c.ThumbnailPath) AS CharacterThumbnailPath,
        c.BirthDate, c.AgeAtSeriesStart,
        sc.RoleInSceneTypeID, role.TypeName AS RoleInSceneTypeName, sc.PresenceTypeID, presence.TypeName AS PresenceTypeName,
        sc.LocationID, location.LocationName, location.LocationPath, sc.EntryLocationID, entryLocation.LocationName AS EntryLocationName,
        sc.ExitLocationID, exitLocation.LocationName AS ExitLocationName,
        sc.AppearanceNotes, sc.OutfitDescription, sc.PhysicalCondition, sc.EmotionalState, sc.KnowledgeNotes,
        sc.CreatedDate, sc.UpdatedDate
    FROM dbo.SceneCharacters sc
    INNER JOIN dbo.Characters 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 dbo.LocationPaths location ON location.LocationID = sc.LocationID
    LEFT JOIN dbo.LocationPaths entryLocation ON entryLocation.LocationID = sc.EntryLocationID
    LEFT JOIN dbo.LocationPaths exitLocation ON exitLocation.LocationID = sc.ExitLocationID
    WHERE sc.SceneID = @SceneID
    ORDER BY role.SortOrder, c.CharacterName;
END;
GO