--liquibase formatted sql
--changeset agentneo:081_xyz_name_unique_constraints runOnChange:true stripComments:true splitStatements:true endDelimiter:;
--comment: Per-project unique Name constraints for commissioning/asset tables

----------------------------------------------------------------------------------------------------
-- Created by:  AgentNeo
-- Created on:  26/06/2026
-- Description: Composite UNIQUE ("ProjectShardId", "Name") per-project uniqueness on AssetType,
--              SystemType, Asset, CommissioningSystem and CommissioningWorkflow. ProjectShardId is
--              the leading column so the constraint is valid on the Citus-distributed tables.
----------------------------------------------------------------------------------------------------
ALTER TABLE xyz."AssetType" DROP CONSTRAINT IF EXISTS "AssetType_ProjectShardId_Name_uq";
ALTER TABLE xyz."AssetType" ADD CONSTRAINT "AssetType_ProjectShardId_Name_uq"
    UNIQUE ("ProjectShardId", "Name");

ALTER TABLE xyz."SystemType" DROP CONSTRAINT IF EXISTS "SystemType_ProjectShardId_Name_uq";
ALTER TABLE xyz."SystemType" ADD CONSTRAINT "SystemType_ProjectShardId_Name_uq"
    UNIQUE ("ProjectShardId", "Name");

ALTER TABLE xyz."Asset" DROP CONSTRAINT IF EXISTS "Asset_ProjectShardId_Name_uq";
ALTER TABLE xyz."Asset" ADD CONSTRAINT "Asset_ProjectShardId_Name_uq"
    UNIQUE ("ProjectShardId", "Name");

ALTER TABLE xyz."CommissioningSystem" DROP CONSTRAINT IF EXISTS "CommissioningSystem_ProjectShardId_Name_uq";
ALTER TABLE xyz."CommissioningSystem" ADD CONSTRAINT "CommissioningSystem_ProjectShardId_Name_uq"
    UNIQUE ("ProjectShardId", "Name");

ALTER TABLE xyz."CommissioningWorkflow" DROP CONSTRAINT IF EXISTS "CommissioningWorkflow_ProjectShardId_Name_uq";
ALTER TABLE xyz."CommissioningWorkflow" ADD CONSTRAINT "CommissioningWorkflow_ProjectShardId_Name_uq"
    UNIQUE ("ProjectShardId", "Name");