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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  07/07/2026
-- Description: Get a paginated list of asset types for a given project
------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_GetAssetTypeList";

CREATE OR REPLACE FUNCTION xyz."fn_GetAssetTypeList" (
    _projectId UUID,
    _lastFetchedIndexId INTEGER DEFAULT NULL,
    _limit INTEGER DEFAULT 1000,
    _lastSyncDateTime TIMESTAMP WITH TIME ZONE DEFAULT NULL
)
RETURNS TABLE (
    "AssetTypeId"             UUID,
    "CommissioningWorkflowId" UUID,
    "Name"                    TEXT,
    "CreatedBy"               TEXT,
    "InsertedOn"              TIMESTAMP WITH TIME ZONE,
    "LastModifiedOn"          TIMESTAMP WITH TIME ZONE,
    "LastModifiedBy"          TEXT,
    "Id"                      INTEGER
)
AS $$
DECLARE
    -- project id map
    _projectShardId INT;
BEGIN
    -- get shard id from uuid
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

    RETURN QUERY
    SELECT
        at."AssetTypeId",
        at."CommissioningWorkflowId",
        at."Name",
        at."CreatedBy",
        at."InsertedOn",
        at."LastModifiedOn",
        at."LastModifiedBy",
        at."Id"
    FROM xyz."AssetType" at
    WHERE at."ProjectShardId" = _projectShardId
      AND (
        CASE
            WHEN _lastFetchedIndexId IS NOT NULL THEN at."Id" > _lastFetchedIndexId
            ELSE TRUE
        END
      )
      AND (
        CASE
            WHEN _lastSyncDateTime IS NOT NULL THEN COALESCE(at."LastModifiedOn", at."InsertedOn") > _lastSyncDateTime
            ELSE TRUE
        END
      )
    ORDER BY at."Id" ASC
    LIMIT _limit;
END;
$$
LANGUAGE plpgsql;
/