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

----------------------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  03/07/2026
-- Description: Return all AssetTypes (full column set) for a project, ordered by Name. Filtered by
--              ProjectShardId so the result is project-isolated by construction.
----------------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_GetAssetTypeList";
CREATE OR REPLACE FUNCTION xyz."fn_GetAssetTypeList"(_projectId 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
    ORDER BY at."Name";
END
$$ LANGUAGE plpgsql;
/