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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  15/07/2026
-- Description: Insert a new AssetType<->SystemType mapping for a project and return
--              the server-generated values via INOUT params. Resolves the shard id
--              via fn_GetProjectShardId (raises if the project does not exist so the
--              API layer can map to 404), validates that both the AssetType and the
--              SystemType belong to the project, and rejects a duplicate
--              (AssetTypeId, SystemTypeId) pair.
------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS xyz."usp_InsertAssetTypeSystemTypeMapping";
CREATE OR REPLACE PROCEDURE xyz."usp_InsertAssetTypeSystemTypeMapping" (
    _projectId                    UUID,
    _assetTypeId                  UUID,
    _systemTypeId                 UUID,
    _createdBy                    TEXT,
    _assetTypeSystemTypeMappingId INOUT UUID DEFAULT NULL,
    _insertedOn                   INOUT TIMESTAMP WITH TIME ZONE DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    -- project id map
    _projectShardId INT;
    -- used in exception block
    _sqlState TEXT;
    _message  TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
BEGIN
    -- get shard id from uuid
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

    -- validate the asset type exists in this project
    IF NOT EXISTS (
        SELECT 1 FROM xyz."AssetType" AS at
        WHERE at."ProjectShardId" = _projectShardId
          AND at."AssetTypeId"    = _assetTypeId
    ) THEN
        RAISE EXCEPTION 'AssetType with id % not found in project', _assetTypeId;
    END IF;

    -- validate the system type exists in this project
    IF NOT EXISTS (
        SELECT 1 FROM xyz."SystemType" AS st
        WHERE st."ProjectShardId" = _projectShardId
          AND st."SystemTypeId"   = _systemTypeId
    ) THEN
        RAISE EXCEPTION 'SystemType with id % not found in project', _systemTypeId;
    END IF;

    -- reject a duplicate (AssetTypeId, SystemTypeId) mapping for this project
    IF EXISTS (
        SELECT 1 FROM xyz."AssetTypeSystemTypeMapping" AS m
        WHERE m."ProjectShardId" = _projectShardId
          AND m."AssetTypeId"    = _assetTypeId
          AND m."SystemTypeId"   = _systemTypeId
    ) THEN
        RAISE EXCEPTION 'AssetType % is already mapped to SystemType % in project', _assetTypeId, _systemTypeId;
    END IF;

    _assetTypeSystemTypeMappingId := GEN_RANDOM_UUID();

    INSERT INTO xyz."AssetTypeSystemTypeMapping" (
        "ProjectShardId",
        "AssetTypeSystemTypeMappingId",
        "AssetTypeId",
        "SystemTypeId",
        "CreatedBy"
    )
    VALUES (
        _projectShardId,
        _assetTypeSystemTypeMappingId,
        _assetTypeId,
        _systemTypeId,
        _createdBy
    )
    RETURNING "InsertedOn" INTO _insertedOn;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        GET STACKED DIAGNOSTICS
            _sqlState := RETURNED_SQLSTATE,
            _message  := MESSAGE_TEXT,
            _detail   := PG_EXCEPTION_DETAIL,
            _hint     := PG_EXCEPTION_HINT,
            _context  := PG_EXCEPTION_CONTEXT;

        INSERT INTO xyz."DbException" ("DbExceptionId", "SqlState", "Message", "Detail", "Hint", "Context")
        VALUES (GEN_RANDOM_UUID(), _sqlState, _message, _detail, _hint, _context);
        COMMIT;
        RAISE;
END;$$;
/