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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  15/07/2026
-- Description: Get a single AssetType<->SystemType mapping by project and mapping id.
--              Empty result set when not found (API layer maps to 404).
------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_GetAssetTypeSystemTypeMapping";

CREATE OR REPLACE FUNCTION xyz."fn_GetAssetTypeSystemTypeMapping" (
    _projectId                     UUID,
    _assetTypeSystemTypeMappingId  UUID
)
RETURNS TABLE (
    "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."AssetTypeSystemTypeMappingId",
        m."AssetTypeId",
        m."SystemTypeId",
        m."InsertedOn",
        m."CreatedBy",
        m."LastModifiedOn",
        m."LastModifiedBy"
    FROM xyz."AssetTypeSystemTypeMapping" m
    WHERE m."ProjectShardId"               = _projectShardId
      AND m."AssetTypeSystemTypeMappingId" = _assetTypeSystemTypeMappingId;
END;
$$
LANGUAGE plpgsql;
/