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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  15/07/2026
-- Description: Hard-delete an AssetType<->SystemType mapping by project and mapping
--              id. The table has no soft-delete columns, so this is a DELETE. Raises
--              if the mapping does not exist within the project (API maps to 404).
------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS xyz."usp_DeleteAssetTypeSystemTypeMapping";
CREATE OR REPLACE PROCEDURE xyz."usp_DeleteAssetTypeSystemTypeMapping" (
    _projectId                    UUID,
    _assetTypeSystemTypeMappingId UUID
)
LANGUAGE plpgsql
AS $$
DECLARE
    -- project id map
    _projectShardId INT;
    _rowCount       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;

    DELETE FROM xyz."AssetTypeSystemTypeMapping"
    WHERE "ProjectShardId"               = _projectShardId
      AND "AssetTypeSystemTypeMappingId" = _assetTypeSystemTypeMappingId;

    GET DIAGNOSTICS _rowCount = ROW_COUNT;

    IF _rowCount = 0 THEN
        RAISE EXCEPTION 'AssetTypeSystemTypeMapping with id % not found in project', _assetTypeSystemTypeMappingId;
    END IF;

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;$$;
/