-- =============================================
-- Repsly API Integration - Verify Customer Change Tracking
-- Created: 2025-12-03
-- Purpose: Verify that change tracking column exists in mstCustomer
--          This field is already maintained by application logic
--          This script only verifies it exists (does not add it)
-- =============================================

USE [Superbowl]
GO

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

-- =============================================
-- Notes
-- =============================================
-- Change Tracking Field (already exists and maintained by application):
--   mstCustomer.dtStamp - Updated when customer is modified
--
-- This field enables:
--   1. Incremental processing (only sync changed customers)
--   2. Change detection (compare dtStamp > LastProcessed in queue table)
--   3. Automatic reprocessing when data is updated
--
-- The queue table (sysCronJobCustomers) will use this timestamp
-- to determine which customers need to be reprocessed.
--
-- Note: If dtStamp is nvarchar(12) format (YYYYMMDDHHMM), conversion
--       logic similar to products will be needed when comparing with
--       datetime fields in queue tables (LastProcessed, LastUpdated)

PRINT 'Customer change tracking verification script completed'
GO

