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

----------------------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  03/07/2026
-- Description: Return a single AssetType (full column set) for a project, filtered by ProjectShardId
--              AND AssetTypeId. An AssetType belonging to another project returns no rows, preserving
--              project isolation.
----------------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_GetAssetType";
CREATE OR REPLACE FUNCTION xyz."fn_GetAssetType"(_projectId UUID, _assetTypeId UUID)
RETURNS TABLE (
    "AssetTypeId" UUID,
    "ProjectId" UUID,
    "CommissioningWorkflowId" UUID,
    "Name" TEXT,
    "Code" TEXT,
    "CreatedBy" TEXT,
    "InsertedOn" TIMESTAMP WITH TIME ZONE,
    "LastModifiedOn" TIMESTAMP WITH TIME ZONE,
    "LastModifiedBy" TEXT
)
AS $$
DECLARE
    _projectShardId INT;
BEGIN
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

    RETURN QUERY
    SELECT
        at."AssetTypeId",
        _projectId,
        at."CommissioningWorkflowId",
        at."Name",
        at."Code",
        at."CreatedBy",
        at."InsertedOn",
        at."LastModifiedOn",
        at."LastModifiedBy"
    FROM xyz."AssetType" at
    WHERE at."ProjectShardId" = _projectShardId
        AND at."AssetTypeId" = _assetTypeId;
END
$$ LANGUAGE plpgsql;
/