SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID(N'dbo.CharacterSexValues', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.CharacterSexValues
    (
        CharacterSexValueID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_CharacterSexValues PRIMARY KEY,
        OwnerUserID int NULL,
        SexName nvarchar(100) NOT NULL,
        IsSystem bit NOT NULL CONSTRAINT DF_CharacterSexValues_IsSystem DEFAULT (0),
        SortOrder int NOT NULL CONSTRAINT DF_CharacterSexValues_SortOrder DEFAULT (0),
        IsActive bit NOT NULL CONSTRAINT DF_CharacterSexValues_IsActive DEFAULT (1),
        CreatedDateUTC datetime2 NOT NULL CONSTRAINT DF_CharacterSexValues_CreatedDateUTC DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT FK_CharacterSexValues_AppUser FOREIGN KEY (OwnerUserID) REFERENCES dbo.AppUser(UserID)
    );
END;
GO

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

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_Characters_CharacterSexValues')
    ALTER TABLE dbo.Characters ADD CONSTRAINT FK_Characters_CharacterSexValues FOREIGN KEY (SexValueID) REFERENCES dbo.CharacterSexValues(CharacterSexValueID);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_CharacterSexValues_SystemName' AND object_id = OBJECT_ID(N'dbo.CharacterSexValues'))
    CREATE UNIQUE INDEX UX_CharacterSexValues_SystemName ON dbo.CharacterSexValues(SexName) WHERE IsSystem = 1 AND IsActive = 1;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_CharacterSexValues_OwnerName' AND object_id = OBJECT_ID(N'dbo.CharacterSexValues'))
    CREATE UNIQUE INDEX UX_CharacterSexValues_OwnerName ON dbo.CharacterSexValues(OwnerUserID, SexName) WHERE OwnerUserID IS NOT NULL AND IsActive = 1;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.CharacterSexValues WHERE IsSystem = 1 AND SexName = N'Male')
    INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder) VALUES (NULL, N'Male', 1, 10);
GO

IF NOT EXISTS (SELECT 1 FROM dbo.CharacterSexValues WHERE IsSystem = 1 AND SexName = N'Female')
    INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder) VALUES (NULL, N'Female', 1, 20);
GO

IF NOT EXISTS (SELECT 1 FROM dbo.CharacterSexValues WHERE IsSystem = 1 AND SexName = N'Other')
    INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder) VALUES (NULL, N'Other', 1, 30);
GO

;WITH CharacterOwners AS
(
    SELECT c.CharacterID, ownerAccess.UserID AS OwnerUserID, LTRIM(RTRIM(c.Sex)) AS SexName
    FROM dbo.Characters c
    INNER JOIN dbo.ProjectUserAccess ownerAccess ON ownerAccess.ProjectID = c.ProjectID
        AND ownerAccess.AccessRole = N'Owner'
        AND ownerAccess.IsActive = 1
    WHERE NULLIF(LTRIM(RTRIM(c.Sex)), N'') IS NOT NULL
),
CustomSexValues AS
(
    SELECT DISTINCT co.OwnerUserID, co.SexName
    FROM CharacterOwners co
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM dbo.CharacterSexValues csv
        WHERE csv.IsSystem = 1
            AND csv.IsActive = 1
            AND LOWER(csv.SexName) = LOWER(co.SexName)
    )
      AND NOT EXISTS
    (
        SELECT 1
        FROM dbo.CharacterSexValues csv
        WHERE csv.OwnerUserID = co.OwnerUserID
            AND csv.IsActive = 1
            AND LOWER(csv.SexName) = LOWER(co.SexName)
    )
)
INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder)
SELECT OwnerUserID, SexName, 0, 100
FROM CustomSexValues;
GO

;WITH CharacterOwners AS
(
    SELECT c.CharacterID, ownerAccess.UserID AS OwnerUserID, LTRIM(RTRIM(c.Sex)) AS SexName
    FROM dbo.Characters c
    INNER JOIN dbo.ProjectUserAccess ownerAccess ON ownerAccess.ProjectID = c.ProjectID
        AND ownerAccess.AccessRole = N'Owner'
        AND ownerAccess.IsActive = 1
    WHERE NULLIF(LTRIM(RTRIM(c.Sex)), N'') IS NOT NULL
)
UPDATE c
SET SexValueID = csv.CharacterSexValueID
FROM dbo.Characters c
INNER JOIN CharacterOwners co ON co.CharacterID = c.CharacterID
INNER JOIN dbo.CharacterSexValues csv ON csv.IsActive = 1
    AND LOWER(csv.SexName) = LOWER(co.SexName)
    AND (csv.IsSystem = 1 OR csv.OwnerUserID = co.OwnerUserID)
WHERE c.SexValueID IS NULL;
GO

CREATE OR ALTER PROCEDURE dbo.CharacterSexValue_GetOrCreate
    @OwnerUserID int,
    @SexName nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    SET @SexName = NULLIF(LTRIM(RTRIM(@SexName)), N'');
    IF @SexName IS NULL
        THROW 53001, 'Sex name is required.', 1;

    DECLARE @CharacterSexValueID int;

    SELECT TOP (1) @CharacterSexValueID = CharacterSexValueID
    FROM dbo.CharacterSexValues
    WHERE IsActive = 1
        AND LOWER(SexName) = LOWER(@SexName)
        AND (IsSystem = 1 OR OwnerUserID = @OwnerUserID)
    ORDER BY IsSystem DESC, SortOrder, SexName;

    IF @CharacterSexValueID IS NULL
    BEGIN
        INSERT dbo.CharacterSexValues (OwnerUserID, SexName, IsSystem, SortOrder)
        VALUES (@OwnerUserID, @SexName, 0, 100);

        SET @CharacterSexValueID = CAST(SCOPE_IDENTITY() AS int);
    END

    SELECT @CharacterSexValueID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.Character_ListByProject
    @ProjectID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CharacterID, ProjectID, CharacterName, ShortName, SexValueID, 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, SexValueID, 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,
    @SexValueID int = NULL,
    @Sex nvarchar(100) = 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, SexValueID, Sex, BirthDate, AgeAtSeriesStart, AgeReferenceSceneID, Height, EyeColour, CharacterImportance, ShowInQuickAddBar, DefaultDescription, ImagePath, ThumbnailPath)
        VALUES (@ProjectID, @CharacterName, @ShortName, @SexValueID, @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, SexValueID = @SexValueID, 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.SceneFloorPlanOccupancy_ListByScene
    @SceneID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.SceneFloorPlanOccupancyID, o.SceneID, o.FloorPlanID, o.FloorPlanFloorID,
        o.CharacterID, c.CharacterName, c.ShortName AS CharacterShortName,
        COALESCE(c.AvatarImagePath, c.ImagePath) AS CharacterImagePath,
        COALESCE(c.AvatarThumbnailPath, c.ThumbnailPath) AS CharacterThumbnailPath,
        o.StoryAssetID, sa.AssetName, sa.ImagePath AS AssetImagePath, sa.ThumbnailPath AS AssetThumbnailPath,
        o.LocationID, COALESCE(lp.LocationName, l.LocationName) AS LocationName, COALESCE(lp.LocationPath, l.LocationName) AS LocationPath,
        fpf.Name AS FloorName, o.CreatedDate, o.ModifiedDate
    FROM dbo.SceneFloorPlanOccupancy o
    INNER JOIN dbo.Locations l ON l.LocationID = o.LocationID
    LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = o.LocationID
    LEFT JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = o.FloorPlanFloorID
    LEFT JOIN dbo.Characters c ON c.CharacterID = o.CharacterID
    LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = o.StoryAssetID
    WHERE o.SceneID = @SceneID
    ORDER BY COALESCE(c.CharacterName, sa.AssetName);
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneFloorPlanOccupancy_ListByFloorPlan
    @FloorPlanID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.SceneFloorPlanOccupancyID, o.SceneID, o.FloorPlanID, o.FloorPlanFloorID,
        o.CharacterID, c.CharacterName, c.ShortName AS CharacterShortName,
        COALESCE(c.AvatarImagePath, c.ImagePath) AS CharacterImagePath,
        COALESCE(c.AvatarThumbnailPath, c.ThumbnailPath) AS CharacterThumbnailPath,
        o.StoryAssetID, sa.AssetName, sa.ImagePath AS AssetImagePath, sa.ThumbnailPath AS AssetThumbnailPath,
        o.LocationID, COALESCE(lp.LocationName, l.LocationName) AS LocationName, COALESCE(lp.LocationPath, l.LocationName) AS LocationPath,
        fpf.Name AS FloorName, o.CreatedDate, o.ModifiedDate
    FROM dbo.SceneFloorPlanOccupancy o
    INNER JOIN dbo.Locations l ON l.LocationID = o.LocationID
    LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = o.LocationID
    LEFT JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = o.FloorPlanFloorID
    LEFT JOIN dbo.Characters c ON c.CharacterID = o.CharacterID
    LEFT JOIN dbo.StoryAssets sa ON sa.StoryAssetID = o.StoryAssetID
    WHERE o.FloorPlanID = @FloorPlanID
    ORDER BY o.SceneID, COALESCE(c.CharacterName, sa.AssetName);
END;
GO