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

------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  10/07/2026
-- Description: Insert a new CommissioningSystem 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 system type belongs to the same project, raising if it does not.
------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS xyz."fn_InsertCommissioningSystem";

CREATE OR REPLACE FUNCTION xyz."fn_InsertCommissioningSystem" (
    _projectId    UUID,
    _systemTypeId UUID,
    _name         TEXT,
    _createdBy    TEXT
)
RETURNS TABLE (
    "CommissioningSystemId" UUID,
    "ProjectShardId"        INT,
    "SystemTypeId"          UUID,
    "Name"                  TEXT,
    "CreatedBy"             TEXT,
    "InsertedOn"            TIMESTAMP WITH TIME ZONE,
    "LastModifiedOn"        TIMESTAMP WITH TIME ZONE,
    "LastModifiedBy"        TEXT,
    "IsDeleted"             BOOLEAN
)
AS $$
DECLARE
    -- project id map
    _projectShardId INT;
    _newCommissioningSystemId UUID;
BEGIN
    -- get shard id from uuid
    SELECT xyz."fn_GetProjectShardId"(_projectId) INTO _projectShardId;

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

    _newCommissioningSystemId := GEN_RANDOM_UUID();

    INSERT INTO xyz."CommissioningSystem" (
        "ProjectShardId",
        "CommissioningSystemId",
        "SystemTypeId",
        "Name",
        "CreatedBy",
        "IsDeleted"
    )
    VALUES (
        _projectShardId,
        _newCommissioningSystemId,
        _systemTypeId,
        _name,
        _createdBy,
        FALSE
    );

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