-- =============================================
-- Item 3 - Promotions & Planning Enhancements
-- Phase 2: Data Migration
-- =============================================
-- Description: Migrates existing activities from dwfPromo to promoActivity
--              and updates dwfPromo records with refActivityID
--              NOTE: Only migrates activities created in 2025
-- Database: Superbowl
-- Prerequisites: Phase 1 must be completed first
-- Date: 2025-01-XX
-- =============================================

USE Superbowl
GO

-- =============================================
-- IMPORTANT: Backup database before running this script
-- =============================================

PRINT '============================================='
PRINT 'Starting Phase 2: Data Migration'
PRINT '============================================='
PRINT ''

-- =============================================
-- Pre-Migration Verification
-- =============================================

-- Check if promoActivity table exists
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[promoActivity]') AND type in (N'U'))
BEGIN
    PRINT 'ERROR: promoActivity table does not exist. Please run Phase 1 first.'
    RETURN
END

-- Check if refActivityID column exists
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[dwfPromo]') AND name = 'refActivityID')
BEGIN
    PRINT 'ERROR: refActivityID column does not exist in dwfPromo. Please run Phase 1 first.'
    RETURN
END

PRINT 'Pre-migration checks passed.'
PRINT ''

-- =============================================
-- Step 1: Extract and Insert Distinct Activities
-- =============================================
-- Extract all unique combinations of Activity Name + Activity Type + Brand + Channel
-- from existing dwfPromo records created in 2025 and create corresponding records in promoActivity
-- =============================================

PRINT 'Step 1: Extracting distinct activities from dwfPromo (2025 only)...'

-- Count distinct activity names before migration (2025 only)
DECLARE @DistinctActivityNamesCount INT
SELECT @DistinctActivityNamesCount = COUNT(DISTINCT strActivityName)
FROM dwfPromo
WHERE strActivityName IS NOT NULL 
    AND strActivityName <> ''
    AND strActivityType IS NOT NULL
    AND strActivityType <> ''
    AND refBrandID IS NOT NULL
    AND strSegments IS NOT NULL
    AND strSegments <> ''
    AND dtStamp IS NOT NULL
    AND LEN(dtStamp) >= 4
    AND LEFT(dtStamp, 4) = '2025'  -- Only activities created in 2025

PRINT 'Found ' + CAST(@DistinctActivityNamesCount AS VARCHAR) + ' distinct activity names from 2025 to migrate.'
PRINT ''

-- Insert distinct activities into promoActivity
-- Migrate by Activity Name only, using the most common Type/Brand/Channel combination
INSERT INTO [dbo].[promoActivity] 
    (strActivityName, strActivityType, refBrandID, strChannel, blnActive, strLastUser, dtStamp)
SELECT 
    strActivityName,
    strActivityType,
    refBrandID,
    strChannel,
    blnActive,
    strLastUser,
    dtStamp
FROM (
    SELECT 
        strActivityName,
        strActivityType,
        refBrandID,
        strSegments AS strChannel,
        CASE 
            WHEN MAX(CAST(blnActive AS INT)) > 0 THEN 1 
            ELSE 0 
        END AS blnActive,
        MAX(strLastUser) AS strLastUser,
        MAX(dtStamp) AS dtStamp,
        ROW_NUMBER() OVER (
            PARTITION BY strActivityName 
            ORDER BY COUNT(*) DESC, MAX(dtStamp) DESC
        ) AS RowNum
    FROM dwfPromo
    WHERE strActivityName IS NOT NULL 
        AND strActivityName <> ''
        AND strActivityType IS NOT NULL
        AND strActivityType <> ''
        AND refBrandID IS NOT NULL
        AND strSegments IS NOT NULL
        AND strSegments <> ''
        AND dtStamp IS NOT NULL
        AND LEN(dtStamp) >= 4
        AND LEFT(dtStamp, 4) = '2025'  -- Only activities created in 2025
    GROUP BY strActivityName, strActivityType, refBrandID, strSegments
) AS ActivityCombinations
WHERE RowNum = 1  -- Use the most common combination (by promotion count, then most recent)
    -- Only insert if this activity name doesn't already exist
    AND NOT EXISTS (
        SELECT 1 FROM promoActivity pa
        WHERE pa.strActivityName = ActivityCombinations.strActivityName
    )
ORDER BY strActivityName

DECLARE @ActivitiesInserted INT = @@ROWCOUNT
PRINT 'Inserted ' + CAST(@ActivitiesInserted AS VARCHAR) + ' new activity records into promoActivity.'
PRINT ''

-- =============================================
-- Step 2: Update dwfPromo Records with refActivityID
-- =============================================
-- Link all existing dwfPromo records to their corresponding ActivityID
-- =============================================

PRINT 'Step 2: Updating dwfPromo records with refActivityID...'

-- Count promotions to be updated (2025 only)
DECLARE @PromotionsToUpdate INT
SELECT @PromotionsToUpdate = COUNT(*)
FROM dwfPromo d
WHERE d.strActivityName IS NOT NULL
    AND d.strActivityName <> ''
    AND d.strActivityType IS NOT NULL
    AND d.strActivityType <> ''
    AND d.refBrandID IS NOT NULL
    AND d.strSegments IS NOT NULL
    AND d.strSegments <> ''
    AND d.refActivityID IS NULL
    AND d.dtStamp IS NOT NULL
    AND LEN(d.dtStamp) >= 4
    AND LEFT(d.dtStamp, 4) = '2025'  -- Only promotions created in 2025
    AND EXISTS (
        SELECT 1 FROM promoActivity pa
        WHERE pa.strActivityName = d.strActivityName
            AND pa.strActivityType = d.strActivityType
            AND pa.refBrandID = d.refBrandID
            AND pa.strChannel = d.strSegments
    )

PRINT 'Found ' + CAST(@PromotionsToUpdate AS VARCHAR) + ' promotion records to update.'
PRINT ''

-- Update dwfPromo records with refActivityID (2025 only)
-- Match by Activity Name only (since we migrated by Activity Name only)
UPDATE dwfPromo
SET refActivityID = promoActivity.ActivityID
FROM dwfPromo
INNER JOIN promoActivity ON 
    dwfPromo.strActivityName = promoActivity.strActivityName
WHERE dwfPromo.strActivityName IS NOT NULL
    AND dwfPromo.strActivityName <> ''
    AND dwfPromo.strActivityType IS NOT NULL
    AND dwfPromo.strActivityType <> ''
    AND dwfPromo.refBrandID IS NOT NULL
    AND dwfPromo.strSegments IS NOT NULL
    AND dwfPromo.strSegments <> ''
    AND dwfPromo.refActivityID IS NULL -- Only update records that don't have ActivityID yet
    AND dwfPromo.dtStamp IS NOT NULL
    AND LEN(dwfPromo.dtStamp) >= 4
    AND LEFT(dwfPromo.dtStamp, 4) = '2025'  -- Only promotions created in 2025

DECLARE @PromotionsUpdated INT = @@ROWCOUNT
PRINT 'Updated ' + CAST(@PromotionsUpdated AS VARCHAR) + ' promotion records with refActivityID.'
PRINT ''

-- =============================================
-- Step 3: Verification Queries
-- =============================================

PRINT '============================================='
PRINT 'Step 3: Verification'
PRINT '============================================='
PRINT ''

-- Check for any dwfPromo records without ActivityID (should be 0 or only NULL activity names)
DECLARE @UnlinkedPromotions INT
SELECT @UnlinkedPromotions = COUNT(*)
FROM dwfPromo 
WHERE refActivityID IS NULL 
    AND strActivityName IS NOT NULL 
    AND strActivityName <> ''
    AND strActivityType IS NOT NULL
    AND strActivityType <> ''

IF @UnlinkedPromotions > 0
BEGIN
    PRINT 'WARNING: Found ' + CAST(@UnlinkedPromotions AS VARCHAR) + ' promotion records without ActivityID.'
    PRINT 'These records may have NULL or invalid activity data.'
    PRINT ''
    
    -- Show sample of unlinked records
    SELECT TOP 10 
        PromoID,
        strActivityName,
        strActivityType,
        refBrandID,
        strSegments,
        CASE 
            WHEN strActivityName IS NULL OR strActivityName = '' THEN 'Missing Activity Name'
            WHEN strActivityType IS NULL OR strActivityType = '' THEN 'Missing Activity Type'
            WHEN refBrandID IS NULL THEN 'Missing Brand ID'
            WHEN strSegments IS NULL OR strSegments = '' THEN 'Missing Channel'
            ELSE 'No matching activity found'
        END AS Reason
    FROM dwfPromo
    WHERE refActivityID IS NULL 
        AND strActivityName IS NOT NULL 
        AND strActivityName <> ''
    ORDER BY PromoID
END
ELSE
BEGIN
    PRINT 'SUCCESS: All promotion records with valid activity data have been linked.'
END
PRINT ''

-- Check activity name counts match (by Activity Name only, not combinations) - 2025 only
DECLARE @DistinctNamesInDwfPromo INT
DECLARE @TotalInPromoActivity INT

SELECT @DistinctNamesInDwfPromo = COUNT(DISTINCT strActivityName)
FROM dwfPromo
WHERE strActivityName IS NOT NULL 
    AND strActivityName <> ''
    AND strActivityType IS NOT NULL
    AND strActivityType <> ''
    AND dtStamp IS NOT NULL
    AND LEN(dtStamp) >= 4
    AND LEFT(dtStamp, 4) = '2025'  -- Only activities created in 2025

SELECT @TotalInPromoActivity = COUNT(*)
FROM promoActivity

PRINT 'Activity Name Count Comparison (2025 only):'
PRINT '  Distinct activity names in dwfPromo (2025): ' + CAST(@DistinctNamesInDwfPromo AS VARCHAR)
PRINT '  Total activities in promoActivity: ' + CAST(@TotalInPromoActivity AS VARCHAR)

IF @DistinctNamesInDwfPromo = @TotalInPromoActivity
BEGIN
    PRINT 'SUCCESS: Activity name counts match!'
END
ELSE
BEGIN
    PRINT 'WARNING: Activity name counts do not match. This may be due to:'
    PRINT '  - Activities with NULL/empty values in dwfPromo'
    PRINT '  - Activities that were filtered out during migration'
END
PRINT ''

-- Check for orphaned records (should return 0)
DECLARE @OrphanedRecords INT
SELECT @OrphanedRecords = COUNT(*)
FROM dwfPromo d
WHERE d.strActivityName IS NOT NULL
    AND d.strActivityName <> ''
    AND d.refActivityID IS NOT NULL
    AND NOT EXISTS (
        SELECT 1 FROM promoActivity a
        WHERE a.ActivityID = d.refActivityID
    )

IF @OrphanedRecords > 0
BEGIN
    PRINT 'ERROR: Found ' + CAST(@OrphanedRecords AS VARCHAR) + ' orphaned records (promotions with invalid ActivityID).'
    PRINT 'This should not happen. Please investigate.'
END
ELSE
BEGIN
    PRINT 'SUCCESS: No orphaned records found.'
END
PRINT ''

-- Summary Report
DECLARE @PromotionsWithActivityID INT
SELECT @PromotionsWithActivityID = COUNT(*) FROM dwfPromo WHERE refActivityID IS NOT NULL

PRINT '============================================='
PRINT 'Migration Summary'
PRINT '============================================='
PRINT 'Activities in promoActivity: ' + CAST(@TotalInPromoActivity AS VARCHAR)
PRINT 'Promotions with ActivityID: ' + CAST(@PromotionsWithActivityID AS VARCHAR)
PRINT 'Promotions without ActivityID: ' + CAST(@UnlinkedPromotions AS VARCHAR)
PRINT 'Orphaned records: ' + CAST(@OrphanedRecords AS VARCHAR)
PRINT ''

-- =============================================
-- Step 4: Add Foreign Key Constraint (Optional)
-- =============================================
-- Uncomment this section after verifying migration is successful
-- This will enforce referential integrity
-- =============================================

/*
PRINT '============================================='
PRINT 'Step 4: Adding Foreign Key Constraint'
PRINT '============================================='
PRINT ''

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
PRINT ''
*/

-- =============================================
-- Final Verification
-- =============================================

PRINT '============================================='
PRINT 'Sample Data Verification'
PRINT '============================================='
PRINT ''

-- Show sample of migrated activities
PRINT 'Sample of migrated activities (first 10):'
SELECT TOP 10 
    ActivityID,
    strActivityName,
    strActivityType,
    refBrandID,
    strChannel,
    blnActive,
    dtCreated
FROM promoActivity
ORDER BY ActivityID

PRINT ''

-- Show sample of linked promotions
PRINT 'Sample of linked promotions (first 10):'
SELECT TOP 10 
    d.PromoID,
    d.refActivityID,
    d.strActivityName,
    d.strActivityType,
    d.refBrandID,
    d.strSegments,
    pa.strActivityName AS ActivityNameFromTable,
    pa.blnActive AS ActivityActiveStatus
FROM dwfPromo d
INNER JOIN promoActivity pa ON d.refActivityID = pa.ActivityID
ORDER BY d.PromoID

PRINT ''
PRINT '============================================='
PRINT 'Phase 2: Data Migration - COMPLETE'
PRINT '============================================='
PRINT ''
PRINT 'Next Steps:'
PRINT '1. Review the verification results above'
PRINT '2. If all checks pass, uncomment Step 4 to add foreign key constraint'
PRINT '3. Proceed with Phase 3: Activity Menu Item'
PRINT '============================================='

