-- ============================================================================
-- DATABASE CHANGES - Consolidated Script
-- Date: 30 November 2025
-- Purpose: Performance optimization for AquaBarrier project management API
-- ============================================================================

-- ============================================================================
-- PART 1: ADD TIMESTAMP COLUMNS TO PROJECTS TABLE
-- ============================================================================
-- Purpose: Support dashboard metrics and track record lifecycle
-- Impact: Enables "New Projects" filtering in dashboard
-- ============================================================================

-- Add CreatedDate column (if not exists)
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = 'Projects' AND COLUMN_NAME = 'CreatedDate')
BEGIN
    ALTER TABLE [dbo].[Projects] ADD [CreatedDate] [datetime2](7) NULL DEFAULT (getutcdate());
    PRINT 'Added CreatedDate column to Projects table';
END
ELSE
    PRINT 'CreatedDate already exists in Projects table';
GO

-- Add ModifiedDate column (if not exists)
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = 'Projects' AND COLUMN_NAME = 'ModifiedDate')
BEGIN
    ALTER TABLE [dbo].[Projects] ADD [ModifiedDate] [datetime2](7) NULL DEFAULT (getutcdate());
    PRINT 'Added ModifiedDate column to Projects table';
END
ELSE
    PRINT 'ModifiedDate already exists in Projects table';
GO

-- Backfill CreatedDate with current date for existing records (if NULL)
UPDATE [dbo].[Projects]
SET [CreatedDate] = GETUTCDATE()
WHERE [CreatedDate] IS NULL;
PRINT 'Backfilled CreatedDate for ' + CAST(@@ROWCOUNT AS varchar) + ' existing Projects';
GO

-- ============================================================================
-- PART 2: CREATE PERFORMANCE INDEXES
-- ============================================================================
-- Purpose: Optimize query performance for project and customer endpoints
-- Impact: Reduces query execution time from 5+ seconds to sub-second
-- ============================================================================

-- Index for dashboard metrics filtering by CreatedDate
IF NOT EXISTS (SELECT 1 FROM sys.indexes 
               WHERE name = 'IX_Projects_CreatedDate' AND object_id = OBJECT_ID('dbo.Projects'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Projects_CreatedDate] ON [dbo].[Projects]
    (
        [CreatedDate] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
           DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];
    PRINT 'Created index IX_Projects_CreatedDate';
END
ELSE
    PRINT 'Index IX_Projects_CreatedDate already exists';
GO

-- Index for Bidders ProjectNo lookups (if not exists)
IF NOT EXISTS (SELECT 1 FROM sys.indexes 
               WHERE name = 'IX_Bidders_ProjectNo' AND object_id = OBJECT_ID('dbo.Bidders'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Bidders_ProjectNo] ON [dbo].[Bidders]
    (
        [ProjNo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
           DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];
    PRINT 'Created index IX_Bidders_ProjectNo';
END
ELSE
    PRINT 'Index IX_Bidders_ProjectNo already exists';
GO

-- Index for Bidders Id (keyset pagination)
IF NOT EXISTS (SELECT 1 FROM sys.indexes 
               WHERE name = 'IX_Bidders_Id' AND object_id = OBJECT_ID('dbo.Bidders'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Bidders_Id] ON [dbo].[Bidders]
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
           DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];
    PRINT 'Created index IX_Bidders_Id';
END
ELSE
    PRINT 'Index IX_Bidders_Id already exists';
GO

-- Index for Bidders CustId lookups
IF NOT EXISTS (SELECT 1 FROM sys.indexes 
               WHERE name = 'IX_Bidders_CustId' AND object_id = OBJECT_ID('dbo.Bidders'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Bidders_CustId] ON [dbo].[Bidders]
    (
        [CustId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
           DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];
    PRINT 'Created index IX_Bidders_CustId';
END
ELSE
    PRINT 'Index IX_Bidders_CustId already exists';
GO

-- Index for Customers CustomerID lookups
IF EXISTS (SELECT 1 FROM sys.indexes 
           WHERE name = 'IX_Customers_CustomerID' AND object_id = OBJECT_ID('dbo.Customers'))
BEGIN
    DROP INDEX [IX_Customers_CustomerID] ON [dbo].[Customers];
    PRINT 'Dropped existing IX_Customers_CustomerID to recreate with INCLUDE(CompanyName)';
END
GO

CREATE NONCLUSTERED INDEX [IX_Customers_CustomerID] ON [dbo].[Customers]
(
    [CustomerID] ASC
)
INCLUDE ([CompanyName])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
      DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];
PRINT 'Created index IX_Customers_CustomerID with INCLUDE(CompanyName)';
GO

-- Composite index to optimize keyset pagination and project bidders fetch
IF NOT EXISTS (SELECT 1 FROM sys.indexes 
               WHERE name = 'IX_Bidders_ProjNo_Id' AND object_id = OBJECT_ID('dbo.Bidders'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Bidders_ProjNo_Id] ON [dbo].[Bidders]
    (
        [ProjNo] ASC,
        [Id] ASC
    )
    INCLUDE ([CustId])
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
          DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, 
          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];
    PRINT 'Created index IX_Bidders_ProjNo_Id';
END
ELSE
    PRINT 'Index IX_Bidders_ProjNo_Id already exists';
GO

-- Composite index to support ORDER BY [Primary] DESC, Id for offset pagination
IF NOT EXISTS (SELECT 1 FROM sys.indexes 
               WHERE name = 'IX_Bidders_ProjNo_Primary_Id' AND object_id = OBJECT_ID('dbo.Bidders'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Bidders_ProjNo_Primary_Id] ON [dbo].[Bidders]
    (
        [ProjNo] ASC,
        [Primary] DESC,
        [Id] ASC
    )
    INCLUDE (
        [CustId], [Quote], [DateLastContact], [DateFollowup],
        [CustType], [EmailAddress], [ReplacementCost], [Enabled]
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
          DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, 
          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];
    PRINT 'Created index IX_Bidders_ProjNo_Primary_Id';
END
ELSE
    PRINT 'Index IX_Bidders_ProjNo_Primary_Id already exists';
GO

-- ============================================================================
-- PART 3: OPTIONAL - MIGRATE Bidders.CustId FROM nvarchar(15) TO int
-- ============================================================================
-- Purpose: Eliminate CAST() in JOIN clause for better index usage
-- Impact: Removes type conversion overhead, allows index seek on Customers
-- WARNING: This is a breaking schema change - test thoroughly before applying
-- Status: OPTIONAL - Only apply if type mismatch is causing performance issues
-- ============================================================================

/*
-- Uncomment this section to perform the migration

-- Step 1: Add new int column
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = 'Bidders' AND COLUMN_NAME = 'CustId_New')
BEGIN
    ALTER TABLE [dbo].[Bidders] ADD [CustId_New] [int] NULL;
    PRINT 'Added CustId_New column';
END
GO

-- Step 2: Migrate data
UPDATE [dbo].[Bidders] 
SET [CustId_New] = CAST([CustId] AS int) 
WHERE ISNUMERIC([CustId]) = 1 
  AND [CustId_New] IS NULL;
PRINT 'Migrated ' + CAST(@@ROWCOUNT AS varchar) + ' rows to CustId_New';
GO

-- Step 3: Drop index on old column
IF EXISTS (SELECT 1 FROM sys.indexes 
           WHERE name = 'IX_Bidders_CustId' AND object_id = OBJECT_ID('dbo.Bidders'))
BEGIN
    DROP INDEX [IX_Bidders_CustId] ON [dbo].[Bidders];
    PRINT 'Dropped index IX_Bidders_CustId';
END
GO

-- Step 4: Drop old nvarchar column
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME = 'Bidders' AND COLUMN_NAME = 'CustId' AND DATA_TYPE = 'nvarchar')
BEGIN
    ALTER TABLE [dbo].[Bidders] DROP COLUMN [CustId];
    PRINT 'Dropped old nvarchar CustId column';
END
GO

-- Step 5: Rename new column
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME = 'Bidders' AND COLUMN_NAME = 'CustId_New')
   AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_NAME = 'Bidders' AND COLUMN_NAME = 'CustId')
BEGIN
    EXEC sp_rename 'dbo.Bidders.CustId_New', 'CustId', 'COLUMN';
    PRINT 'Renamed CustId_New to CustId';
END
GO

-- Step 6: Set NOT NULL constraint
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
           WHERE TABLE_NAME = 'Bidders' AND COLUMN_NAME = 'CustId' 
           AND DATA_TYPE = 'int' AND IS_NULLABLE = 'YES')
BEGIN
    IF NOT EXISTS (SELECT 1 FROM [dbo].[Bidders] WHERE [CustId] IS NULL)
    BEGIN
        ALTER TABLE [dbo].[Bidders] ALTER COLUMN [CustId] [int] NOT NULL;
        PRINT 'Set CustId to NOT NULL';
    END
    ELSE
    BEGIN
        PRINT 'WARNING: NULL values exist in CustId, cannot set NOT NULL';
    END
END
GO

-- Step 7: Recreate index on new int column
IF NOT EXISTS (SELECT 1 FROM sys.indexes 
               WHERE name = 'IX_Bidders_CustId' AND object_id = OBJECT_ID('dbo.Bidders'))
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Bidders_CustId] ON [dbo].[Bidders]
    (
        [CustId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
           DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];
    PRINT 'Recreated index IX_Bidders_CustId on int column';
END
GO
*/

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

PRINT '';
PRINT '============================================================================';
PRINT 'VERIFICATION RESULTS';
PRINT '============================================================================';

-- Verify Projects timestamp columns
SELECT 'Projects Timestamp Columns' AS CheckType;
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Projects' 
  AND COLUMN_NAME IN ('CreatedDate', 'ModifiedDate')
ORDER BY COLUMN_NAME;

-- Verify all indexes exist
SELECT 'Performance Indexes' AS CheckType;
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    c.name AS ColumnName,
    ty.name AS DataType
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.name IN (
    'IX_Projects_CreatedDate',
    'IX_Bidders_ProjectNo',
    'IX_Bidders_Id',
    'IX_Bidders_CustId',
    'IX_Customers_CustomerID'
)
ORDER BY t.name, i.name;

-- Verify Bidders.CustId data type
SELECT 'Bidders.CustId Data Type' AS CheckType;
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Bidders' AND COLUMN_NAME = 'CustId';

PRINT '';
PRINT '============================================================================';
PRINT 'DATABASE CHANGES COMPLETE';
PRINT '============================================================================';
GO