/* ---------------------------------------------------------------------------
   Fix for DNN 9.13.9 -> 10.2.5 upgrade
   Two CREATE INDEX statements from the 10.00.00 script failed on the 30s timeout.
   Run this in SSMS (no command timeout) WHILE THE APP POOL IS STOPPED.
   --------------------------------------------------------------------------- */

USE [2sxc-dnn];
GO

-- 1) Check current state
SELECT name, type_desc, is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.TabModules');
GO

SELECT COUNT(*) AS TabModulesRows, SUM(CASE WHEN IsDeleted = 1 THEN 1 ELSE 0 END) AS Deleted
FROM dbo.TabModules;
GO

-- 2) Make sure nothing is holding locks on the table (should return no rows)
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, r.command,
       t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID;
GO

-- 3) Create the indexes
IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE object_id = OBJECT_ID('dbo.TabModules')
                 AND name = 'IX_TabModules_ModuleID')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_TabModules_ModuleID]
    ON dbo.[TabModules] ([ModuleID], [TabID])
    INCLUDE ([Alignment], [Border], [CacheMethod], [CacheTime], [Color],
             [ContainerSrc], [CultureCode], [DefaultLanguageGuid], [DisplayPrint],
             [DisplaySyndicate], [DisplayTitle], [Footer], [Header], [IconFile],
             [IsDeleted], [LocalizedVersionGuid], [ModuleOrder], [ModuleTitle],
             [PaneName], [TabModuleID], [UniqueId], [VersionGuid], [Visibility]);
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE object_id = OBJECT_ID('dbo.TabModules')
                 AND name = 'IX_TabModules_ModuleOrder')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_TabModules_ModuleOrder]
    ON dbo.[TabModules] ([TabID], [PaneName], [ModuleOrder])
    INCLUDE ([Alignment], [Border], [CacheMethod], [CacheTime], [Color],
             [ContainerSrc], [CreatedByUserID], [CreatedOnDate], [CultureCode],
             [DefaultLanguageGuid], [DisplayPrint], [DisplaySyndicate],
             [DisplayTitle], [Footer], [Header], [IconFile], [IsDeleted],
             [LastModifiedByUserID], [LastModifiedOnDate], [LocalizedVersionGuid],
             [ModuleID], [ModuleTitle], [TabModuleID], [UniqueId], [VersionGuid],
             [Visibility]);
END
GO

-- 4) Verify
SELECT name FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.TabModules')
  AND name IN ('IX_TabModules_ModuleID', 'IX_TabModules_ModuleOrder');
GO

-- 5) Where the upgrade stopped
SELECT * FROM dbo.Version ORDER BY CreatedDate DESC;
GO