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

----------------------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  03/07/2026
-- Description: Insert a new AssetType scoped to a project and return the full inserted row so the
--              caller avoids a second fetch. The AssetType is mapped to the project's default
--              CommissioningWorkflow (the first one created for the project). Per-project uniqueness
--              of Name and Code is enforced by the unique constraints on xyz."AssetType"; a
--              violation propagates so the API layer can map it to 409 Conflict.
----------------------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_InsertAssetType";
CREATE OR REPLACE FUNCTION xyz."fn_InsertAssetType"(
    _projectId UUID,
    _name TEXT,
    _code TEXT,
    _createdBy TEXT
)
RETURNS TABLE (
    "AssetTypeId" UUID,
    "ProjectId" UUID,
    "CommissioningWorkflowId" UUID,
    "Name" TEXT,
    "Code" TEXT,
    "CreatedBy" TEXT,
    "InsertedOn" TIMESTAMP WITH TIME ZONE,
    "LastModifiedOn" TIMESTAMP WITH TIME ZONE,
    "LastModifiedBy" TEXT
)
AS $$
DECLARE
    _projectShardId INT;
    _commissioningWorkflowId UUID;
    _newAssetTypeId UUID := GEN_RANDOM_UUID();
BEGIN
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

    -- AssetType maps to exactly one CommissioningWorkflow (NOT NULL). The create contract does not
    -- supply one, so default to the project's first (default) workflow.
    SELECT cw."CommissioningWorkflowId" INTO _commissioningWorkflowId
    FROM xyz."CommissioningWorkflow" cw
    WHERE cw."ProjectShardId" = _projectShardId
    ORDER BY cw."InsertedOn" ASC, cw."Id" ASC
    LIMIT 1;

    IF _commissioningWorkflowId IS NULL THEN
        RAISE EXCEPTION 'No CommissioningWorkflow exists for project %.', _projectId;
    END IF;

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

    RETURN QUERY
    SELECT
        at."AssetTypeId",
        _projectId,
        at."CommissioningWorkflowId",
        at."Name",
        at."Code",
        at."CreatedBy",
        at."InsertedOn",
        at."LastModifiedOn",
        at."LastModifiedBy"
    FROM xyz."AssetType" at
    WHERE at."ProjectShardId" = _projectShardId
        AND at."AssetTypeId" = _newAssetTypeId;
END
$$ LANGUAGE plpgsql;
/