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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  15/07/2026
-- Description: Get a keyset-paginated list of AssetType<->SystemType mappings for a
--              given project. The cursor is the internal "Id" (per-shard IDENTITY),
--              which is monotonic within a shard, giving stable pagination.
------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_GetAssetTypeSystemTypeMappingList";

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

    RETURN QUERY
    SELECT
        m."Id",
        m."AssetTypeSystemTypeMappingId",
        m."AssetTypeId",
        m."SystemTypeId",
        m."InsertedOn",
        m."CreatedBy",
        m."LastModifiedOn",
        m."LastModifiedBy"
    FROM xyz."AssetTypeSystemTypeMapping" m
    WHERE m."ProjectShardId" = _projectShardId
      AND (
        CASE
            WHEN _lastFetchedIndexId IS NOT NULL THEN m."Id" > _lastFetchedIndexId
            ELSE TRUE
        END
      )
    ORDER BY m."Id" ASC
    LIMIT _size;
END;
$$
LANGUAGE plpgsql;
/