-- =============================================
-- Repsly API Integration - Verify Change Tracking Columns
-- Created: 2025-01-XX
-- Purpose: Verify that change tracking columns exist in mstProduct and dwfPromo
--          These fields are already maintained by application logic
--          This script only verifies they exist (does not add them)
-- =============================================

USE [Superbowl]
GO

-- =============================================
-- Verify dtStamp exists in mstProduct
-- Note: Application logic already updates this field when products are modified
-- =============================================
IF EXISTS (SELECT * FROM sys.columns 
           WHERE object_id = OBJECT_ID(N'[dbo].[mstProduct]') 
           AND name = 'dtStamp')
BEGIN
    PRINT 'Field dtStamp exists in mstProduct - OK'
END
ELSE
BEGIN
    PRINT 'WARNING: Field dtStamp does NOT exist in mstProduct'
    PRINT '         This field is required for change tracking'
    PRINT '         Please verify the table structure'
END
GO

-- =============================================
-- Verify dtStamp exists in dwfPromo
-- Note: Application logic already updates this field when promotions are modified
-- =============================================
IF EXISTS (SELECT * FROM sys.columns 
           WHERE object_id = OBJECT_ID(N'[dbo].[dwfPromo]') 
           AND name = 'dtStamp')
BEGIN
    PRINT 'Field dtStamp exists in dwfPromo - OK'
END
ELSE
BEGIN
    PRINT 'WARNING: Field dtStamp does NOT exist in dwfPromo'
    PRINT '         This field is required for change tracking'
    PRINT '         Please verify the table structure'
END
GO

-- =============================================
-- Notes
-- =============================================
-- Change Tracking Fields (already exist and maintained by application):
--   mstProduct.dtStamp - Updated when product is modified (nvarchar(12))
--   dwfPromo.dtStamp - Updated when promotion is modified
--   promoActivity.dtStamp - Already exists (tracks activity changes)
--
-- These fields enable:
--   1. Incremental processing (only sync changed items)
--   2. Change detection (compare dtStamp > LastProcessed in queue tables)
--   3. Automatic reprocessing when data is updated
--
-- The queue tables (sysCronJobProducts, sysCronJobPromotions) will use
-- these timestamps to determine which items need to be reprocessed.
--
-- Note: mstProduct.dtStamp is nvarchar(12) - may need conversion when comparing
--       with datetime fields in queue tables (LastProcessed, LastUpdated)

PRINT 'Change tracking verification script completed'
GO

