--liquibase formatted sql
--changeset davewebb:999_indexes runOnChange:true stripComments:true splitStatements:true endDelimiter:;
--comment: Changeset for indexes.

-- NOTE: Add all indexes for xyz schema here. Please ctrl + F the table name so we can group them together.

-- xyz.ActivityCategoryMapping
CREATE INDEX IF NOT EXISTS "ActivityCategoryMapping_ActivityCategoryId_idx" ON xyz."ActivityCategoryMapping" ("ActivityCategoryId");

-- xyz.ActivityCategory
DROP INDEX IF EXISTS "ActivityCategory_ActivityCategoryId_idx"; -- this was a duplicate of the primary key.
DROP INDEX IF EXISTS xyz."ActivityCategory_ActivityCategoryId_idx"; -- this was a duplicate of the primary key.
CREATE INDEX IF NOT EXISTS "ActivityCategory_ParentActivityCategoryId_idx" ON xyz."ActivityCategory" ("ProjectShardId", "ParentActivityCategoryId");

-- xyz.IssueHistory
CREATE INDEX IF NOT EXISTS "IssueHistory_IssueId_idx" ON xyz."IssueHistory" ("ProjectShardId", "IssueId", "InsertedOn");

-- xyz.ModelElementActivityMapping

-- NOTE: this constraint is due a change in design reqs. In ideal world, we would just have xyz.ModelElement.ActivityId column
-- instead of a mapping table, but too late to refactor now.
CREATE UNIQUE INDEX IF NOT EXISTS "ModelElementActivityMapping_UniqueActiveMapping"
    ON xyz."ModelElementActivityMapping" ("ProjectShardId", "ModelElementId")
    WHERE "IsActive" = TRUE;

-- xyz.AssetType
-- Enforce unique Name per (ProjectShardId, CommissioningWorkflowId).
CREATE UNIQUE INDEX IF NOT EXISTS "AssetType_UniqueName"
    ON xyz."AssetType" ("ProjectShardId", "CommissioningWorkflowId", "Name");

-- xyz.SystemType
-- Enforce unique Name per (ProjectShardId, CommissioningWorkflowId).
CREATE UNIQUE INDEX IF NOT EXISTS "SystemType_UniqueName"
    ON xyz."SystemType" ("ProjectShardId", "CommissioningWorkflowId", "Name");

-- xyz.Asset
-- Enforce unique Name per (ProjectShardId, ParentAssetId) among non-deleted rows.
-- NULLS NOT DISTINCT so root-level assets (NULL ParentAssetId) are also constrained.
CREATE UNIQUE INDEX IF NOT EXISTS "Asset_UniqueName"
    ON xyz."Asset" ("ProjectShardId", "ParentAssetId", "Name") NULLS NOT DISTINCT
    WHERE "IsDeleted" = FALSE;

-- xyz.CommissioningSystem
-- Enforce unique Name per (ProjectShardId, SystemTypeId) among non-deleted rows.
CREATE UNIQUE INDEX IF NOT EXISTS "CommissioningSystem_UniqueName"
    ON xyz."CommissioningSystem" ("ProjectShardId", "SystemTypeId", "Name")
    WHERE "IsDeleted" = FALSE;

-- xyz.CommissioningWorkflow
-- Enforce unique Name per (ProjectShardId).
CREATE UNIQUE INDEX IF NOT EXISTS "CommissioningWorkflow_UniqueName"
    ON xyz."CommissioningWorkflow" ("ProjectShardId", "Name");