-- =============================================
-- Promotion Activities Queue (promoActivity-based export)
-- Created: 2026-03
-- Purpose: Queue table for Repsly promotions export driven by promoActivity only.
--          One row per activity (ActivityID); export list comes from promoActivity, not dwfPromo.
--          Run after 01_Create_Queue_Tables.sql and 05_Add_RequestPayload_Column.sql pattern.
-- =============================================

USE [Superbowl]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sysCronJobPromotionActivities]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[sysCronJobPromotionActivities](
        [QueueID] [int] IDENTITY(1,1) NOT NULL,
        [ActivityID] [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_sysCronJobPromotionActivities] PRIMARY KEY CLUSTERED ([QueueID] ASC),
        CONSTRAINT [UQ_sysCronJobPromotionActivities_ActivityID] UNIQUE ([ActivityID])
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    ALTER TABLE [dbo].[sysCronJobPromotionActivities]
    ADD CONSTRAINT [FK_sysCronJobPromotionActivities_Activity]
    FOREIGN KEY([ActivityID]) REFERENCES [dbo].[promoActivity]([ActivityID])

    CREATE NONCLUSTERED INDEX [IX_sysCronJobPromotionActivities_Status]
    ON [dbo].[sysCronJobPromotionActivities]([Status])

    CREATE NONCLUSTERED INDEX [IX_sysCronJobPromotionActivities_ActivityID]
    ON [dbo].[sysCronJobPromotionActivities]([ActivityID])

    CREATE NONCLUSTERED INDEX [IX_sysCronJobPromotionActivities_ImportJobID]
    ON [dbo].[sysCronJobPromotionActivities]([ImportJobID])

    PRINT 'Table sysCronJobPromotionActivities created successfully'
END
ELSE
BEGIN
    PRINT 'Table sysCronJobPromotionActivities already exists'
END
GO
