-- =============================================================================
-- TCORRESPONDENCE — tenant-wide list index (SQL Server variant)
-- Migration: 20260719 — Correspondence Phase 2 List Index
--
-- Purpose: support the new GetCorrespondenceList endpoint (paged, tenant-wide, not scoped to one
-- ObjectTypeId+ObjectId). Existing indexes (IX_TCORRESPONDENCE_OBJECTTYPEID_OBJECTID,
-- IX_TCORRESPONDENCE_ACTIONSTATUS) don't cover an unscoped tenant-wide scan filtered by
-- DATABASENAME + STATUS and ordered by CREATEDON DESC -- without this index that scan would be a
-- full clustered-index scan at scale.
-- =============================================================================

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE  name = 'IX_TCORRESPONDENCE_DATABASENAME_STATUS_CREATEDON'
      AND  object_id = OBJECT_ID('TCORRESPONDENCE'))
CREATE INDEX IX_TCORRESPONDENCE_DATABASENAME_STATUS_CREATEDON
    ON TCORRESPONDENCE (DATABASENAME, STATUS, CREATEDON DESC);
GO

-- =============================================================================
-- END OF MIGRATION 20260719_Correspondence_Phase2_ListIndex (SQL Server variant)
-- =============================================================================