-- =============================================
-- Repsly API Integration - Queue Tables
-- Created: 2025-01-XX
-- Purpose: Track products and promotions to be synced to Repsly
-- Naming Convention: sysCronJob* (follows existing sysCronJob table pattern)
-- 
-- Note: mstProduct uses strProdCode (nvarchar(6)) as primary key, not ProductID
-- Note: blnDataTransfer field will be added in script 02_Add_DataTransfer_Field.sql
-- =============================================

USE [Superbowl]
GO

-- =============================================
-- Queue Table for Products (Repsly Integration)
-- Follows naming convention: sysCronJob*
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sysCronJobProducts]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[sysCronJobProducts](
        [QueueID] [int] IDENTITY(1,1) NOT NULL,
        [strProdCode] [nvarchar](6) NOT NULL,
        [Status] [varchar](20) NOT NULL DEFAULT 'Pending',
        [LastProcessed] [datetime] NULL,
        [LastUpdated] [datetime] NULL,
        [ErrorLog] [nvarchar](max) NULL,
        [ImportJobID] [varchar](100) NULL,
        [RequestPayload] [nvarchar](max) NULL,
        [dtCreated] [datetime] NOT NULL DEFAULT GETDATE(),
        [dtLastEdit] [datetime] NOT NULL DEFAULT GETDATE(),
        CONSTRAINT [PK_sysCronJobProducts] PRIMARY KEY CLUSTERED ([QueueID] ASC)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    -- Foreign Key to mstProduct (using strProdCode as PK)
    ALTER TABLE [dbo].[sysCronJobProducts] 
    ADD CONSTRAINT [FK_sysCronJobProducts_Product] 
    FOREIGN KEY([strProdCode]) REFERENCES [dbo].[mstProduct]([strProdCode])
    
    -- Indexes for performance
    CREATE NONCLUSTERED INDEX [IX_sysCronJobProducts_Status] 
    ON [dbo].[sysCronJobProducts]([Status])
    
    CREATE NONCLUSTERED INDEX [IX_sysCronJobProducts_strProdCode] 
    ON [dbo].[sysCronJobProducts]([strProdCode])
    
    CREATE NONCLUSTERED INDEX [IX_sysCronJobProducts_ImportJobID] 
    ON [dbo].[sysCronJobProducts]([ImportJobID])
    
    PRINT 'Table sysCronJobProducts created successfully'
END
ELSE
BEGIN
    PRINT 'Table sysCronJobProducts already exists'
END
GO

-- =============================================
-- Queue Table for Promotions (Repsly Integration)
-- Follows naming convention: sysCronJob*
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sysCronJobPromotions]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[sysCronJobPromotions](
        [QueueID] [int] IDENTITY(1,1) NOT NULL,
        [PromoID] [int] NOT NULL,
        [refActivityID] [int] NOT NULL,
        [Status] [varchar](20) NOT NULL DEFAULT 'Pending',
        [LastProcessed] [datetime] NULL,
        [LastUpdated] [datetime] NULL,
        [ErrorLog] [nvarchar](max) NULL,
        [ImportJobID] [varchar](100) NULL,
        [RequestPayload] [nvarchar](max) NULL,
        [dtCreated] [datetime] NOT NULL DEFAULT GETDATE(),
        [dtLastEdit] [datetime] NOT NULL DEFAULT GETDATE(),
        CONSTRAINT [PK_sysCronJobPromotions] PRIMARY KEY CLUSTERED ([QueueID] ASC)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    -- Foreign Key to dwfPromo
    ALTER TABLE [dbo].[sysCronJobPromotions] 
    ADD CONSTRAINT [FK_sysCronJobPromotions_Promo] 
    FOREIGN KEY([PromoID]) REFERENCES [dbo].[dwfPromo]([PromoID])
    
    -- Indexes for performance
    CREATE NONCLUSTERED INDEX [IX_sysCronJobPromotions_Status] 
    ON [dbo].[sysCronJobPromotions]([Status])
    
    CREATE NONCLUSTERED INDEX [IX_sysCronJobPromotions_PromoID] 
    ON [dbo].[sysCronJobPromotions]([PromoID])
    
    CREATE NONCLUSTERED INDEX [IX_sysCronJobPromotions_ImportJobID] 
    ON [dbo].[sysCronJobPromotions]([ImportJobID])
    
    PRINT 'Table sysCronJobPromotions created successfully'
END
ELSE
BEGIN
    PRINT 'Table sysCronJobPromotions already exists'
END
GO

-- =============================================
-- Status Values Reference
-- =============================================
-- Status field 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

PRINT 'Queue tables creation script completed'
GO

