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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  09/07/2026
-- Description: Insert a new Asset for a project and return the created row.
--              Resolves the shard id via fn_GetProjectShardId (raises if the project
--              does not exist so the API layer can map to 404) and validates the
--              asset type belongs to the same project, raising if it does not.
------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_InsertAsset";

CREATE OR REPLACE FUNCTION xyz."fn_InsertAsset" (
    _projectId   UUID,
    _assetTypeId UUID,
    _name        TEXT,
    _createdBy   TEXT
)
RETURNS TABLE (
    "AssetId"        UUID,
    "ProjectId"      UUID,
    "AssetTypeId"    UUID,
    "ParentAssetId"  UUID,
    "Name"           TEXT,
    "CreatedBy"      TEXT,
    "InsertedOn"     TIMESTAMP WITH TIME ZONE,
    "LastModifiedOn" TIMESTAMP WITH TIME ZONE,
    "LastModifiedBy" TEXT,
    "IsDeleted"      BOOLEAN,
    "DeletedOn"      TIMESTAMP WITH TIME ZONE,
    "DeletedBy"      TEXT
)
AS $$
DECLARE
    -- project id map
    _projectShardId INT;
    _newAssetId     UUID;
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;

    _newAssetId := GEN_RANDOM_UUID();

    INSERT INTO xyz."Asset" (
        "ProjectShardId",
        "AssetId",
        "AssetTypeId",
        "Name",
        "CreatedBy",
        "IsDeleted"
    )
    VALUES (
        _projectShardId,
        _newAssetId,
        _assetTypeId,
        _name,
        _createdBy,
        FALSE
    );

    -- explicit column list: fn_GetAsset also returns "SystemId", which is out of
    -- scope for the Asset write response, so it is intentionally not projected here
    RETURN QUERY
    SELECT
        g."AssetId",
        g."ProjectId",
        g."AssetTypeId",
        g."ParentAssetId",
        g."Name",
        g."CreatedBy",
        g."InsertedOn",
        g."LastModifiedOn",
        g."LastModifiedBy",
        g."IsDeleted",
        g."DeletedOn",
        g."DeletedBy"
    FROM xyz."fn_GetAsset"(_projectId, _newAssetId) g;
END;
$$
LANGUAGE plpgsql;
/