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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  07/07/2026
-- Description: Insert a new asset type and return the created row.
--              Raises an exception if the commissioning workflow does not exist
--              in the same project.
------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_InsertAssetType";

CREATE OR REPLACE FUNCTION xyz."fn_InsertAssetType" (
    _projectId               UUID,
    _commissioningWorkflowId UUID,
    _name                    TEXT,
    _createdBy               TEXT
)
RETURNS TABLE (
    "AssetTypeId"             UUID,
    "CommissioningWorkflowId" UUID,
    "Name"                    TEXT,
    "CreatedBy"               TEXT,
    "InsertedOn"              TIMESTAMP WITH TIME ZONE,
    "LastModifiedOn"          TIMESTAMP WITH TIME ZONE,
    "LastModifiedBy"          TEXT
)
AS $$
DECLARE
    -- project id map
    _projectShardId INT;
    _newAssetTypeId UUID;
BEGIN
    -- get shard id from uuid
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

    -- validate commissioning workflow exists in this project
    IF NOT EXISTS (
        SELECT 1 FROM xyz."CommissioningWorkflow" AS cw
        WHERE cw."ProjectShardId"          = _projectShardId
          AND cw."CommissioningWorkflowId" = _commissioningWorkflowId
    ) THEN
        RAISE EXCEPTION 'CommissioningWorkflow with id % not found in project', _commissioningWorkflowId;
    END IF;

    _newAssetTypeId := GEN_RANDOM_UUID();

    INSERT INTO xyz."AssetType" (
        "AssetTypeId",
        "ProjectShardId",
        "CommissioningWorkflowId",
        "Name",
        "CreatedBy"
    )
    VALUES (
        _newAssetTypeId,
        _projectShardId,
        _commissioningWorkflowId,
        _name,
        _createdBy
    );

    RETURN QUERY
    SELECT * FROM xyz."fn_GetAssetType"(_projectId, _newAssetTypeId);
END;
$$
LANGUAGE plpgsql;
/