--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.Asset
-- Enforce Name uniqueness within a project (shard), ignoring soft-deleted rows so a
-- deleted asset's name can be reused. ProjectShardId leads the key as required for the
-- Citus distributed deployment. A partial UNIQUE INDEX is used (not ALTER TABLE ADD
-- CONSTRAINT UNIQUE) because a table constraint cannot be made partial on IsDeleted.
-- The preceding UPDATE is a one-time, in-changeset dedupe of any pre-existing colliding
-- non-deleted Names (Constraints run before Patch/, so the dedupe must live here to run
-- before the index is built). It renames losers by appending their AssetId and is
-- idempotent under runOnChange: once Names are unique it matches no rows.
WITH "Duplicates" AS (
    SELECT "ProjectShardId", "AssetId",
        ROW_NUMBER() OVER (PARTITION BY "ProjectShardId", "Name" ORDER BY "InsertedOn", "AssetId") AS "RowNumber"
    FROM xyz."Asset"
    WHERE "IsDeleted" = FALSE
)
UPDATE xyz."Asset" a
SET "Name" = a."Name" || ' (' || a."AssetId"::TEXT || ')'
FROM "Duplicates" d
WHERE a."ProjectShardId" = d."ProjectShardId"
    AND a."AssetId" = d."AssetId"
    AND d."RowNumber" > 1;
CREATE UNIQUE INDEX IF NOT EXISTS "Asset_ProjectShardId_Name_key"
    ON xyz."Asset" ("ProjectShardId", "Name")
    WHERE "IsDeleted" = FALSE;

-- xyz.CommissioningSystem
-- Enforce Name uniqueness within a project (shard), ignoring soft-deleted rows. Same
-- rationale as xyz.Asset above: partial unique index plus an idempotent in-changeset
-- dedupe of pre-existing non-deleted collisions before the index is built.
WITH "Duplicates" AS (
    SELECT "ProjectShardId", "CommissioningSystemId",
        ROW_NUMBER() OVER (PARTITION BY "ProjectShardId", "Name" ORDER BY "InsertedOn", "CommissioningSystemId") AS "RowNumber"
    FROM xyz."CommissioningSystem"
    WHERE "IsDeleted" = FALSE
)
UPDATE xyz."CommissioningSystem" cs
SET "Name" = cs."Name" || ' (' || cs."CommissioningSystemId"::TEXT || ')'
FROM "Duplicates" d
WHERE cs."ProjectShardId" = d."ProjectShardId"
    AND cs."CommissioningSystemId" = d."CommissioningSystemId"
    AND d."RowNumber" > 1;
CREATE UNIQUE INDEX IF NOT EXISTS "CommissioningSystem_ProjectShardId_Name_key"
    ON xyz."CommissioningSystem" ("ProjectShardId", "Name")
    WHERE "IsDeleted" = FALSE;