-- =============================================
-- Repsly API Integration - Customer Queue Table
-- Created: 2025-12-03
-- Purpose: Track customers to be synced to Repsly
-- Naming Convention: sysCronJob* (follows existing sysCronJob table pattern)
-- 
-- Note: mstCustomer uses strCustomerNo as primary key
-- Note: All customers are synced (no blnDataTransfer field needed)
-- =============================================

USE [Superbowl]
GO

-- =============================================
-- Queue Table for Customers (Repsly Integration)
-- Follows naming convention: sysCronJob*
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sysCronJobCustomers]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[sysCronJobCustomers](
        [QueueID] [int] IDENTITY(1,1) NOT NULL,
        [strCustomerNo] [nvarchar](7) 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_sysCronJobCustomers] PRIMARY KEY CLUSTERED ([QueueID] ASC)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    -- Foreign Key to mstCustomer (using strCustomerNo as PK)
    ALTER TABLE [dbo].[sysCronJobCustomers] 
    ADD CONSTRAINT [FK_sysCronJobCustomers_Customer] 
    FOREIGN KEY([strCustomerNo]) REFERENCES [dbo].[mstCustomer]([strCustomerNo])
    
    -- Indexes for performance
    CREATE NONCLUSTERED INDEX [IX_sysCronJobCustomers_Status] 
    ON [dbo].[sysCronJobCustomers]([Status])
    
    CREATE NONCLUSTERED INDEX [IX_sysCronJobCustomers_strCustomerNo] 
    ON [dbo].[sysCronJobCustomers]([strCustomerNo])
    
    CREATE NONCLUSTERED INDEX [IX_sysCronJobCustomers_ImportJobID] 
    ON [dbo].[sysCronJobCustomers]([ImportJobID])
    
    PRINT 'Table sysCronJobCustomers created successfully'
END
ELSE
BEGIN
    PRINT 'Table sysCronJobCustomers already exists'
END
GO

-- =============================================
-- Status Values Reference
-- =============================================
-- Status field values:
-- 'Pending'    - New customer, not yet processed
-- 'Processing' - Currently being processed
-- 'Completed'  - Successfully synced to Repsly
-- 'Failed'     - Sync failed (check ErrorLog)
-- 'Updated'    - Customer was updated, needs reprocessing

PRINT 'Customer queue table creation script completed'
GO

