--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
-- Unique Name per project (case-sensitive). No IsDeleted column, so unfiltered.
CREATE UNIQUE INDEX IF NOT EXISTS "AssetType_Name_key"
    ON xyz."AssetType" ("ProjectShardId", "Name");

-- xyz.SystemType
-- Unique Name per project (case-sensitive). No IsDeleted column, so unfiltered.
CREATE UNIQUE INDEX IF NOT EXISTS "SystemType_Name_key"
    ON xyz."SystemType" ("ProjectShardId", "Name");

-- xyz.Asset
-- Unique Name per project (case-sensitive). Partial so a Name can be re-used after soft-delete.
CREATE UNIQUE INDEX IF NOT EXISTS "Asset_Name_key"
    ON xyz."Asset" ("ProjectShardId", "Name")
    WHERE "IsDeleted" = FALSE;

-- xyz.CommissioningSystem
-- Unique Name per project (case-sensitive). Partial so a Name can be re-used after soft-delete.
CREATE UNIQUE INDEX IF NOT EXISTS "CommissioningSystem_Name_key"
    ON xyz."CommissioningSystem" ("ProjectShardId", "Name")
    WHERE "IsDeleted" = FALSE;