--liquibase formatted sql
--changeset davewebb:118_xyz_asset_type runOnChange:true stripComments:true splitStatements:true endDelimiter:;
--comment: Create AssetType table

-----------------------------------------------------------------------------------------------------
-- Created by:  Dave Webb
-- Created on:  16/06/2026
-- Description: An asset type, e.g. Air Handling Units or Chillers. 
--              Maps to exactly one CommissioningWorkflow (i.e. the readiness template that should 
--              be auto-applied to assets of this type).
--              Different asset types may share the same workflow.
-----------------------------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS xyz."AssetType" (
    "Id" INT GENERATED ALWAYS AS IDENTITY,
    "InsertedOn" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT TIMEZONE('UTC', NOW()),
    "AssetTypeId" UUID NOT NULL DEFAULT GEN_RANDOM_UUID(),
    "ProjectShardId" INT NOT NULL,
    "CommissioningWorkflowId" UUID NOT NULL,
    "Name" TEXT NOT NULL,
    "CreatedBy" TEXT NOT NULL,
    "LastModifiedOn" TIMESTAMP WITH TIME ZONE NULL,
    "LastModifiedBy" TEXT NULL,
    CONSTRAINT "AssetType_pkey" PRIMARY KEY ("ProjectShardId", "AssetTypeId")
);
ALTER TABLE xyz."AssetType" OWNER TO datapipeline;

-- Added by AgentNeo on 03/07/2026: per-project short code for the asset type.
-- Per-project uniqueness of "Code" is enforced by the unique constraint in
-- Database/xyz/Constraints/083_xyz_asset_type_name_code_unique.sql.
ALTER TABLE xyz."AssetType" ADD COLUMN IF NOT EXISTS "Code" TEXT NULL;