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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  09/07/2026
-- Description: Get a keyset-paginated list of assets for a given project. The
--              cursor is the internal "Id" (per-shard IDENTITY), which is monotonic
--              within a shard, giving stable pagination. Excludes soft-deleted rows.
--              Each row includes a scalar "SystemId" resolved from the asset's
--              AssetType via xyz."AssetTypeSystemTypeMapping" (NULL when no mapping
--              exists). The AssetType->SystemType relationship may be many, so the
--              lookup picks a single deterministic mapping (earliest created) to
--              avoid row fan-out.
------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_GetAssetList";

CREATE OR REPLACE FUNCTION xyz."fn_GetAssetList" (
    _projectId          UUID,
    _lastFetchedIndexId INT DEFAULT NULL,
    _size               INT DEFAULT 1000
)
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,
    "SystemId"       UUID,
    "Id"             INT
)
AS $$
DECLARE
    -- project id map
    _projectShardId INT;
BEGIN
    -- get shard id from uuid
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

    RETURN QUERY
    SELECT
        a."AssetId",
        _projectId,
        a."AssetTypeId",
        a."ParentAssetId",
        a."Name",
        a."CreatedBy",
        a."InsertedOn",
        a."LastModifiedOn",
        a."LastModifiedBy",
        a."IsDeleted",
        a."DeletedOn",
        a."DeletedBy",
        sys."SystemTypeId" AS "SystemId",
        a."Id"
    FROM xyz."Asset" a
    LEFT JOIN LATERAL (
        SELECT m."SystemTypeId"
        FROM xyz."AssetTypeSystemTypeMapping" m
        WHERE m."ProjectShardId" = a."ProjectShardId"
          AND m."AssetTypeId"    = a."AssetTypeId"
        ORDER BY m."Id" ASC
        LIMIT 1
    ) sys ON TRUE
    WHERE a."ProjectShardId" = _projectShardId
      AND a."IsDeleted"      = FALSE
      AND (
        CASE
            WHEN _lastFetchedIndexId IS NOT NULL THEN a."Id" > _lastFetchedIndexId
            ELSE TRUE
        END
      )
    ORDER BY a."Id" ASC
    LIMIT _size;
END;
$$
LANGUAGE plpgsql;
/