-- =============================================
-- Item 3 - Promotions & Planning Enhancements
-- Phase 1: Database Schema Changes
-- =============================================
-- Description: Creates mstActivity table and adds refActivityID column to dwfPromo
-- Database: Superbowl
-- Date: 2025-01-XX
-- =============================================

USE Superbowl
GO

-- =============================================
-- Step 1: Create promoActivity Table
-- =============================================
-- Master table for activity definitions
-- =============================================

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[promoActivity]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[promoActivity] (
        [ActivityID] INT IDENTITY(1,1) NOT NULL,
        [strActivityName] VARCHAR(255) NOT NULL,
        [strActivityType] VARCHAR(255) NOT NULL,
        [refBrandID] INT NOT NULL,
        [strChannel] VARCHAR(255) NOT NULL,
        [blnActive] BIT NOT NULL DEFAULT 1,
        [strLastUser] VARCHAR(50) NULL,
        [dtStamp] VARCHAR(14) NULL,
        [dtCreated] DATETIME DEFAULT GETDATE(),
        
        -- Primary Key
        CONSTRAINT [PK_promoActivity] PRIMARY KEY CLUSTERED ([ActivityID] ASC),
        
        -- Foreign Key to mstBrand
        CONSTRAINT [FK_promoActivity_mstBrand] 
            FOREIGN KEY ([refBrandID]) 
            REFERENCES [dbo].[mstBrand]([BrandID])
        
        -- Note: No unique constraint on Activity Name + Type + Brand + Channel
        -- ActivityID is the unique identifier. Same Activity Name can exist multiple times with different ActivityIDs
    )
    
    PRINT 'Table promoActivity created successfully.'
END
ELSE
BEGIN
    PRINT 'Table promoActivity already exists.'
END
GO

-- =============================================
-- Step 2: Create Indexes on mstActivity
-- =============================================

-- Index for Active/Inactive filtering
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_promoActivity_blnActive' AND object_id = OBJECT_ID('dbo.promoActivity'))
BEGIN
    CREATE INDEX [IX_promoActivity_blnActive] ON [dbo].[promoActivity]([blnActive])
    PRINT 'Index IX_promoActivity_blnActive created successfully.'
END
GO

-- Index for Brand filtering
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_promoActivity_refBrandID' AND object_id = OBJECT_ID('dbo.promoActivity'))
BEGIN
    CREATE INDEX [IX_promoActivity_refBrandID] ON [dbo].[promoActivity]([refBrandID])
    PRINT 'Index IX_promoActivity_refBrandID created successfully.'
END
GO

-- Index for Activity Name searching
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_promoActivity_strActivityName' AND object_id = OBJECT_ID('dbo.promoActivity'))
BEGIN
    CREATE INDEX [IX_promoActivity_strActivityName] ON [dbo].[promoActivity]([strActivityName])
    PRINT 'Index IX_promoActivity_strActivityName created successfully.'
END
GO

-- =============================================
-- Step 3: Add refActivityID column to dwfPromo
-- =============================================

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[dwfPromo]') AND name = 'refActivityID')
BEGIN
    ALTER TABLE [dbo].[dwfPromo]
    ADD [refActivityID] INT NULL
    
    PRINT 'Column refActivityID added to dwfPromo table successfully.'
END
ELSE
BEGIN
    PRINT 'Column refActivityID already exists in dwfPromo table.'
END
GO

-- =============================================
-- Step 4: Create Index on refActivityID
-- =============================================

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_dwfPromo_refActivityID' AND object_id = OBJECT_ID('dbo.dwfPromo'))
BEGIN
    CREATE INDEX [IX_dwfPromo_refActivityID] ON [dbo].[dwfPromo]([refActivityID])
    PRINT 'Index IX_dwfPromo_refActivityID created successfully.'
END
GO

-- =============================================
-- Step 5: Add Foreign Key Constraint
-- =============================================
-- Note: This will be added after data migration is complete
-- Run this after Phase 2 (Data Migration) is complete
-- =============================================

-- Uncomment the following after data migration:
/*
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_dwfPromo_promoActivity')
BEGIN
    ALTER TABLE [dbo].[dwfPromo]
    ADD CONSTRAINT [FK_dwfPromo_promoActivity] 
        FOREIGN KEY ([refActivityID]) 
        REFERENCES [dbo].[promoActivity]([ActivityID])
    
    PRINT 'Foreign key constraint FK_dwfPromo_promoActivity created successfully.'
END
ELSE
BEGIN
    PRINT 'Foreign key constraint FK_dwfPromo_promoActivity already exists.'
END
GO
*/

-- =============================================
-- Verification Queries
-- =============================================

-- Check if promoActivity table exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[promoActivity]') AND type in (N'U'))
BEGIN
    PRINT 'VERIFICATION: promoActivity table exists.'
    SELECT COUNT(*) AS ActivityCount FROM [dbo].[promoActivity]
END
ELSE
BEGIN
    PRINT 'ERROR: promoActivity table does not exist.'
END
GO

-- Check if refActivityID column exists
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[dwfPromo]') AND name = 'refActivityID')
BEGIN
    PRINT 'VERIFICATION: refActivityID column exists in dwfPromo table.'
    SELECT COUNT(*) AS TotalPromotions, 
           COUNT(refActivityID) AS PromotionsWithActivityID,
           COUNT(*) - COUNT(refActivityID) AS PromotionsWithoutActivityID
    FROM [dbo].[dwfPromo]
END
ELSE
BEGIN
    PRINT 'ERROR: refActivityID column does not exist in dwfPromo table.'
END
GO

PRINT '============================================='
PRINT 'Phase 1: Database Schema Changes - COMPLETE'
PRINT 'Next Step: Run Phase 2 - Data Migration'
PRINT '============================================='

