--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 scalar columns of an Asset and return the updated
--              row. The only patchable scalar field is "Name"; relational fields
--              ("AssetTypeId", "ParentAssetId") and identity/audit columns are out
--              of scope. A NULL _name means the field was omitted and is left
--              unchanged (partial update); the caller's merge-patch handling lives
--              in the API layer. 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,
    _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;

    UPDATE xyz."Asset" a
    SET
        "Name"           = COALESCE(_name, a."Name"),
        "LastModifiedOn" = TIMEZONE('UTC', NOW()),
        "LastModifiedBy" = _lastModifiedBy
    WHERE a."ProjectShardId" = _projectShardId
      AND a."AssetId"        = _assetId
      AND a."IsDeleted"      = FALSE;

    RETURN QUERY
    SELECT * FROM xyz."fn_GetAsset"(_projectId, _assetId);
END;
$$
LANGUAGE plpgsql;
/