# Database Scripts for Repsly Integration

## Script Execution Order

Execute these scripts in order:

1. **01_Create_Queue_Tables.sql** - Creates queue tables for tracking sync status
2. **02_Add_DataTransfer_Field.sql** - Adds blnDataTransfer field to mstProduct and dwdProduct
3. **03_Add_Change_Tracking.sql** - Verifies change tracking columns exist (dtStamp in mstProduct and dwfPromo)
4. **04_Verify_And_Fix_Queue_Tables.sql** - Verifies and fixes queue table structure (run if you encounter column errors)
5. **05_Add_RequestPayload_Column.sql** - Adds RequestPayload column for logging JSON payloads sent to Repsly
6. **06_Fix_Stuck_Processing_Records.sql** - Fixes records stuck in 'Processing' status (one-time fix)
7. **07_Remove_Unused_RepslyID_Columns.sql** - Removes unused RepslyProductID and RepslyPromotionID columns
8. **08_Insert_Test_Products.sql** - (Optional) Inserts 100 test products (TEST001F-TEST100F) for Repsly API testing
9. **09_Create_Customer_Queue_Table.sql** - Creates queue table for tracking customer sync status (sysCronJobCustomers)
10. **12_Verify_Customer_Change_Tracking.sql** - Verifies change tracking column (dtStamp) exists in mstCustomer
11. **13_Add_CustomerType_Column.sql** - Adds CustomerType column to sysCronJobCustomers to support both RGBC and Non-RGBC customers
12. **14_Create_PromotionActivities_Queue.sql** - Creates sysCronJobPromotionActivities for Repsly promotions export (one row per promoActivity; export list from promoActivity only, not dwfPromo)

## Notes

- All scripts use `IF NOT EXISTS` checks to prevent errors if run multiple times
- Scripts are idempotent - safe to run multiple times
- Review each script before execution
- Test in development environment first

## Queue Table Status Values

- **Pending** - New item, not yet processed
- **Processing** - Currently being processed
- **Completed** - Successfully synced to Repsly
- **Failed** - Sync failed (check ErrorLog)
- **Updated** - Item was updated, needs reprocessing
- **Inactive** - Promotion is no longer active