SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF COL_LENGTH(N'dbo.FloorPlanFloors', N'Description') IS NULL
    ALTER TABLE dbo.FloorPlanFloors ADD Description nvarchar(max) NULL;
GO

CREATE OR ALTER PROCEDURE dbo.FloorPlan_GetEditor
    @FloorPlanID int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ProjectID int;
    SELECT @ProjectID = ProjectID
    FROM dbo.FloorPlans
    WHERE FloorPlanID = @FloorPlanID;

    EXEC dbo.FloorPlan_Get @FloorPlanID;

    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
        fpf.FloorPlanFloorID,
        fpf.FloorPlanID,
        fpf.LocationID,
        lp.LocationName,
        lp.LocationPath,
        fpf.Name,
        fpf.Description,
        fpf.SortOrder,
        fpf.GridWidth,
        fpf.GridHeight,
        fpf.BackgroundImagePath,
        fpf.BackgroundImageOriginalFileName,
        COALESCE(fpf.BackgroundOpacity, CONVERT(decimal(5,2), 0.35)) AS BackgroundOpacity,
        COALESCE(fpf.BackgroundScale, CONVERT(decimal(8,3), 1.000)) AS BackgroundScale,
        COALESCE(fpf.BackgroundOffsetX, CONVERT(decimal(10,2), 0.00)) AS BackgroundOffsetX,
        COALESCE(fpf.BackgroundOffsetY, CONVERT(decimal(10,2), 0.00)) AS BackgroundOffsetY,
        fpf.BackgroundLocked,
        fpf.CreatedDate,
        fpf.ModifiedDate
    FROM dbo.FloorPlanFloors fpf
    LEFT JOIN #ProjectLocationPaths lp ON lp.LocationID = fpf.LocationID
    WHERE fpf.FloorPlanID = @FloorPlanID
    ORDER BY fpf.SortOrder, fpf.Name;

    SELECT
        fpb.FloorPlanBlockID,
        fpb.FloorPlanFloorID,
        fpb.LocationID,
        lp.LocationName,
        COALESCE(NULLIF(lp.LocationPath, N''), lp.LocationName) AS LocationPath,
        fpb.X,
        fpb.Y,
        fpb.WidthCells,
        fpb.HeightCells,
        fpb.BlockType,
        fpb.LabelOverride,
        fpb.Notes,
        fpb.CreatedDate,
        fpb.ModifiedDate
    FROM dbo.FloorPlanBlocks fpb
    INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpb.FloorPlanFloorID
    INNER JOIN #ProjectLocationPaths lp ON lp.LocationID = fpb.LocationID
    WHERE fpf.FloorPlanID = @FloorPlanID
    ORDER BY fpf.SortOrder, fpb.Y, fpb.X, lp.LocationName;

    SELECT
        fpt.FloorPlanTransitionID,
        fpt.FloorPlanFloorID,
        fpt.FromLocationID,
        fromPath.LocationName AS FromLocationName,
        COALESCE(NULLIF(fromPath.LocationPath, N''), fromPath.LocationName) AS FromLocationPath,
        fpt.ToLocationID,
        toPath.LocationName AS ToLocationName,
        COALESCE(NULLIF(toPath.LocationPath, N''), toPath.LocationName) AS ToLocationPath,
        fpt.TransitionType,
        fpt.Notes,
        fpt.IsLocked,
        fpt.DisplayLabel,
        fpt.PositionWithinLocation,
        fpt.CreatedDate,
        fpt.ModifiedDate
    FROM dbo.FloorPlanTransitions fpt
    INNER JOIN dbo.FloorPlanFloors fpf ON fpf.FloorPlanFloorID = fpt.FloorPlanFloorID
    INNER JOIN #ProjectLocationPaths fromPath ON fromPath.LocationID = fpt.FromLocationID
    INNER JOIN #ProjectLocationPaths toPath ON toPath.LocationID = fpt.ToLocationID
    WHERE fpf.FloorPlanID = @FloorPlanID
    ORDER BY fpf.SortOrder, fromPath.LocationName, toPath.LocationName, fpt.TransitionType;
END;
GO

CREATE OR ALTER PROCEDURE dbo.SceneFloorPlanParticipant_ListByFloorPlan
    @FloorPlanID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT DISTINCT
        s.SceneID,
        N'Character' AS EntityType,
        sc.CharacterID AS EntityID,
        c.CharacterName AS DisplayName
    FROM dbo.Scenes s
    INNER JOIN dbo.SceneCharacters sc ON sc.SceneID = s.SceneID
    INNER JOIN dbo.Characters c ON c.CharacterID = sc.CharacterID
    WHERE s.FloorPlanID = @FloorPlanID
      AND s.IsArchived = 0

    UNION

    SELECT DISTINCT
        s.SceneID,
        N'Asset' AS EntityType,
        ae.StoryAssetID AS EntityID,
        sa.AssetName AS DisplayName
    FROM dbo.Scenes s
    INNER JOIN dbo.AssetEvents ae ON ae.SceneID = s.SceneID
    INNER JOIN dbo.StoryAssets sa ON sa.StoryAssetID = ae.StoryAssetID
    WHERE s.FloorPlanID = @FloorPlanID
      AND s.IsArchived = 0;
END;
GO

CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_Get
    @FloorPlanFloorID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        fpf.FloorPlanFloorID,
        fpf.FloorPlanID,
        fpf.LocationID,
        lp.LocationName,
        lp.LocationPath,
        fpf.Name,
        fpf.Description,
        fpf.SortOrder,
        fpf.GridWidth,
        fpf.GridHeight,
        fpf.BackgroundImagePath,
        fpf.BackgroundImageOriginalFileName,
        COALESCE(fpf.BackgroundOpacity, CONVERT(decimal(5,2), 0.35)) AS BackgroundOpacity,
        COALESCE(fpf.BackgroundScale, CONVERT(decimal(8,3), 1.000)) AS BackgroundScale,
        COALESCE(fpf.BackgroundOffsetX, CONVERT(decimal(10,2), 0.00)) AS BackgroundOffsetX,
        COALESCE(fpf.BackgroundOffsetY, CONVERT(decimal(10,2), 0.00)) AS BackgroundOffsetY,
        fpf.BackgroundLocked,
        fpf.CreatedDate,
        fpf.ModifiedDate
    FROM dbo.FloorPlanFloors fpf
    LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = fpf.LocationID
    WHERE fpf.FloorPlanFloorID = @FloorPlanFloorID;
END;
GO

CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_ListByPlan
    @FloorPlanID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        fpf.FloorPlanFloorID,
        fpf.FloorPlanID,
        fpf.LocationID,
        lp.LocationName,
        lp.LocationPath,
        fpf.Name,
        fpf.Description,
        fpf.SortOrder,
        fpf.GridWidth,
        fpf.GridHeight,
        fpf.BackgroundImagePath,
        fpf.BackgroundImageOriginalFileName,
        fpf.BackgroundOpacity,
        fpf.BackgroundScale,
        fpf.BackgroundOffsetX,
        fpf.BackgroundOffsetY,
        fpf.BackgroundLocked,
        fpf.CreatedDate,
        fpf.ModifiedDate
    FROM dbo.FloorPlanFloors fpf
    LEFT JOIN dbo.LocationPaths lp ON lp.LocationID = fpf.LocationID
    WHERE fpf.FloorPlanID = @FloorPlanID
    ORDER BY fpf.SortOrder, fpf.Name;
END;
GO

CREATE OR ALTER PROCEDURE dbo.FloorPlanFloor_Save
    @FloorPlanFloorID int,
    @FloorPlanID int,
    @LocationID int = NULL,
    @Name nvarchar(100),
    @Description nvarchar(max) = NULL,
    @SortOrder int,
    @GridWidth int,
    @GridHeight int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ProjectID int;
    SELECT @ProjectID = ProjectID FROM dbo.FloorPlans WHERE FloorPlanID = @FloorPlanID;

    IF @ProjectID IS NULL
        THROW 51003, 'Floor plan not found.', 1;

    SET @Name = NULLIF(LTRIM(RTRIM(@Name)), N'');
    IF @Name IS NULL
        THROW 51001, 'Floor name is required.', 1;

    IF @GridWidth NOT BETWEEN 8 AND 80 OR @GridHeight NOT BETWEEN 8 AND 80
        THROW 51002, 'Grid width and height must be between 8 and 80.', 1;

    IF @LocationID IS NOT NULL
       AND NOT EXISTS (SELECT 1 FROM dbo.Locations WHERE LocationID = @LocationID AND ProjectID = @ProjectID AND IsArchived = 0)
        THROW 51008, 'Floor location must belong to this project.', 1;

    IF @FloorPlanFloorID = 0
    BEGIN
        INSERT dbo.FloorPlanFloors (FloorPlanID, LocationID, Name, Description, SortOrder, GridWidth, GridHeight, BackgroundOpacity, BackgroundScale, BackgroundOffsetX, BackgroundOffsetY, BackgroundLocked)
        VALUES (@FloorPlanID, @LocationID, @Name, @Description, @SortOrder, @GridWidth, @GridHeight, 0.35, 1.000, 0.00, 0.00, 1);

        SELECT CONVERT(int, SCOPE_IDENTITY());
        RETURN;
    END;

    UPDATE dbo.FloorPlanFloors
    SET LocationID = @LocationID,
        Name = @Name,
        Description = @Description,
        SortOrder = @SortOrder,
        GridWidth = @GridWidth,
        GridHeight = @GridHeight,
        ModifiedDate = SYSUTCDATETIME()
    WHERE FloorPlanFloorID = @FloorPlanFloorID
      AND FloorPlanID = @FloorPlanID;

    SELECT @FloorPlanFloorID;
END;
GO