--liquibase formatted sql
--changeset agentneo:fn_UpdateAsset runOnChange:true stripComments:false endDelimiter:/
--comment: Create or replace xyz."fn_UpdateAsset"

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  10/07/2026
-- Description: Update the mutable columns of an Asset and return the updated row.
--              Applies RFC 7396 merge-patch semantics: a NULL parameter means the
--              field was omitted and is left unchanged. Patchable columns are
--              "AssetTypeId", "ParentAssetId" and "Name". Because "ParentAssetId" is
--              nullable, the caller distinguishes "omitted" from "explicit null" via
--              _clearParentAssetId — when TRUE, "ParentAssetId" is set to NULL and
--              _parentAssetId is ignored. Identity/audit columns are out of scope.
--              Validates that _assetTypeId exists in the project and that
--              _parentAssetId points at a live asset in the same project and is not
--              the asset being updated. Resolves the shard id via
--              fn_GetProjectShardId (raises if the project does not exist). Returns
--              zero rows if no matching, non-deleted asset exists so the API layer
--              can map to 404.
------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_UpdateAsset";

CREATE OR REPLACE FUNCTION xyz."fn_UpdateAsset" (
    _projectId           UUID,
    _assetId             UUID,
    _assetTypeId         UUID,
    _parentAssetId       UUID,
    _clearParentAssetId  BOOLEAN,
    _name                TEXT,
    _lastModifiedBy      TEXT
)
RETURNS TABLE (
    "AssetId"        UUID,
    "ProjectId"      UUID,
    "AssetTypeId"    UUID,
    "ParentAssetId"  UUID,
    "Name"           TEXT,
    "CreatedBy"      TEXT,
    "InsertedOn"     TIMESTAMP WITH TIME ZONE,
    "LastModifiedOn" TIMESTAMP WITH TIME ZONE,
    "LastModifiedBy" TEXT,
    "IsDeleted"      BOOLEAN,
    "DeletedOn"      TIMESTAMP WITH TIME ZONE,
    "DeletedBy"      TEXT
)
AS $$
DECLARE
    -- project id map
    _projectShardId INT;
BEGIN
    -- get shard id from uuid
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

    -- validate the asset type exists in this project
    IF _assetTypeId IS NOT NULL AND NOT EXISTS (
        SELECT 1 FROM xyz."AssetType" AS at
        WHERE at."ProjectShardId" = _projectShardId
          AND at."AssetTypeId"    = _assetTypeId
    ) THEN
        RAISE EXCEPTION 'AssetType with id % not found in project', _assetTypeId;
    END IF;

    -- validate the parent asset exists in this project, is not deleted, and is not the asset itself
    IF _clearParentAssetId IS NOT TRUE AND _parentAssetId IS NOT NULL THEN
        IF _parentAssetId = _assetId THEN
            RAISE EXCEPTION 'Asset % cannot be its own parent', _assetId;
        END IF;

        IF NOT EXISTS (
            SELECT 1 FROM xyz."Asset" AS pa
            WHERE pa."ProjectShardId" = _projectShardId
              AND pa."AssetId"        = _parentAssetId
              AND pa."IsDeleted"      = FALSE
        ) THEN
            RAISE EXCEPTION 'ParentAsset with id % not found in project', _parentAssetId;
        END IF;
    END IF;

    UPDATE xyz."Asset" a
    SET
        "AssetTypeId"    = COALESCE(_assetTypeId, a."AssetTypeId"),
        "ParentAssetId"  = CASE
                               WHEN _clearParentAssetId IS TRUE THEN NULL
                               ELSE COALESCE(_parentAssetId, a."ParentAssetId")
                           END,
        "Name"           = COALESCE(_name, a."Name"),
        "LastModifiedOn" = TIMEZONE('UTC', NOW()),
        "LastModifiedBy" = _lastModifiedBy
    WHERE a."ProjectShardId" = _projectShardId
      AND a."AssetId"        = _assetId
      AND a."IsDeleted"      = FALSE;

    -- explicit column list: fn_GetAsset also returns "SystemId", which is out of
    -- scope for the Asset write response, so it is intentionally not projected here
    RETURN QUERY
    SELECT
        g."AssetId",
        g."ProjectId",
        g."AssetTypeId",
        g."ParentAssetId",
        g."Name",
        g."CreatedBy",
        g."InsertedOn",
        g."LastModifiedOn",
        g."LastModifiedBy",
        g."IsDeleted",
        g."DeletedOn",
        g."DeletedBy"
    FROM xyz."fn_GetAsset"(_projectId, _assetId) g;
END;
$$
LANGUAGE plpgsql;
/