-- =============================================
-- Repsly API Integration - Add blnDataTransfer Field
-- Created: 2025-01-XX
-- Purpose: Add blnDataTransfer field to mstProduct and dwdProduct tables
--          This field marks products that should be synced to Repsly
-- =============================================

USE [Superbowl]
GO

-- =============================================
-- Add blnDataTransfer to mstProduct
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.columns 
               WHERE object_id = OBJECT_ID(N'[dbo].[mstProduct]') 
               AND name = 'blnDataTransfer')
BEGIN
    ALTER TABLE [dbo].[mstProduct]
    ADD [blnDataTransfer] [bit] NOT NULL DEFAULT 0
    
    PRINT 'Field blnDataTransfer added to mstProduct successfully'
END
ELSE
BEGIN
    PRINT 'Field blnDataTransfer already exists in mstProduct'
END
GO

-- =============================================
-- Add blnDataTransfer to dwdProduct
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.columns 
               WHERE object_id = OBJECT_ID(N'[dbo].[dwdProduct]') 
               AND name = 'blnDataTransfer')
BEGIN
    ALTER TABLE [dbo].[dwdProduct]
    ADD [blnDataTransfer] [bit] NOT NULL DEFAULT 0
    
    PRINT 'Field blnDataTransfer added to dwdProduct successfully'
END
ELSE
BEGIN
    PRINT 'Field blnDataTransfer already exists in dwdProduct'
END
GO

-- =============================================
-- Notes
-- =============================================
-- blnDataTransfer field:
--   0 (false) = Product should NOT be synced to Repsly
--   1 (true)  = Product SHOULD be synced to Repsly
--
-- Users can mark products as Data Transfer Yes/No via the Superbowl website
-- The daily cron job will process products where blnDataTransfer = 1

PRINT 'DataTransfer field addition script completed'
GO

