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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  10/07/2026
-- Description: Get a paginated list of non-deleted commissioning systems for a
--              given project, ordered by Id ASC for stable keyset pagination.
------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_GetCommissioningSystemList";

CREATE OR REPLACE FUNCTION xyz."fn_GetCommissioningSystemList" (
    _projectId          UUID,
    _lastFetchedIndexId INTEGER DEFAULT NULL,
    _pageSize           INTEGER DEFAULT 1000
)
RETURNS TABLE (
    "Id"                    INTEGER,
    "CommissioningSystemId" UUID,
    "ProjectShardId"        INT,
    "SystemTypeId"          UUID,
    "Name"                  TEXT,
    "CreatedBy"             TEXT,
    "InsertedOn"            TIMESTAMP WITH TIME ZONE,
    "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
        cs."Id",
        cs."CommissioningSystemId",
        cs."ProjectShardId",
        cs."SystemTypeId",
        cs."Name",
        cs."CreatedBy",
        cs."InsertedOn",
        cs."LastModifiedOn",
        cs."LastModifiedBy"
    FROM xyz."CommissioningSystem" cs
    WHERE cs."ProjectShardId" = _projectShardId
      AND cs."IsDeleted"      = FALSE
      AND (
        CASE
            WHEN _lastFetchedIndexId IS NOT NULL THEN cs."Id" > _lastFetchedIndexId
            ELSE TRUE
        END
      )
    ORDER BY cs."Id" ASC
    LIMIT _pageSize;
END;
$$
LANGUAGE plpgsql;
/