Appendices

-- Coupon Pool Table Create Statement
CREATE TABLE [dbo].[RPI_COUPON_POOL](
	[promotion_code] [varchar](100) NOT NULL,
	[coupon_code] [varchar](100) NOT NULL,
	[display_name] [varchar](100) NOT NULL,
	[max_uses] [int],
	[start_date] [datetime],
	[expiration_date] [datetime],
	[allocated] [int],
	[allocation_ts] [datetime],
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RPI_COUPON_ASSIGNMENT](
      [coupon_assignment_id] [int] IDENTITY(1,1) NOT NULL,
      [promotion_code] [varchar](100) NOT NULL,
      [coupon_code] [varchar](100) NOT NULL,
      [coupon_type] [varchar](100) NOT NULL,
      [RPIResolutionKey] [varchar](100),
      [resolution_key_int01] [bigint],
      [resolution_key_int02] [bigint],
      [resolution_key_int03] [bigint],
      [resolution_key_str01] [varchar](100),
      [resolution_key_str02] [varchar](100),
      [resolution_key_str03] [varchar](100),
      [insert_ts] [varchar](100) NOT NULL,
      [dataflow_id] [varchar](100) NOT NULL,
      [workflow_id] [varchar](100) NOT NULL,
      [output_id] [varchar](100) NOT NULL
) ON [PRIMARY]
GO

USE <RPI Primary Database Name>;

GO

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

CREATE VIEW [dbo].[RPI_UNIQUE_COUPON_PROMOTION_CODE] AS
    SELECT DISTINCT Promotion_Code 
    FROM <RPI Primary Database Name>.[dbo].[RPI_COUPON_POOL];

GO

CREATE PROCEDURE [dbo].[sp_RedPoint_ForceWaitDM]
    @outvar     int      OUTPUT,
    @retryDelay char(8)  = '00:00:00'
AS
BEGIN
    DECLARE @ReturnInfo varchar(255);

    -- Validate the @retryDelay parameter
    IF ISDATE('2000-01-01 ' + @retryDelay + '.000') = 0
    BEGIN
        SELECT @ReturnInfo = 'Invalid time ' + @retryDelay + ', hh:mm:ss, submitted.';
        PRINT @ReturnInfo;
        RETURN(1);
    END

    -- Delay execution for the specified time
    WAITFOR DELAY @retryDelay;

    -- Set output variable
    SELECT @outvar = 1;
END

CREATE PROCEDURE [dbo].[sp_RPI_CouponProject_Lock]
    @result     int      OUTPUT,
    @numRetries int,
    @retryDelay char(8)  = '00:00:00'
AS
BEGIN
    DECLARE @ReturnInfo varchar(255);

    -- Validate the @retryDelay parameter
    IF ISDATE('2000-01-01 ' + @retryDelay + '.000') = 0
    BEGIN
        SELECT @ReturnInfo = 'Invalid time ' + @retryDelay + ', hh:mm:ss, submitted.';
        PRINT @ReturnInfo;
        RETURN(1);
    END

    SET NOCOUNT ON;

    DECLARE @lockCheck int = 0;

    -- Check for idle status with lock_id = 1
    SELECT @lockCheck = COUNT(*) 
    FROM coupon_project_runstatus WITH (TABLOCKX) 
    WHERE lock_id = 1 AND STATUS = 'idle';

    -- If lock is available, update the status to 'running'
    IF @lockCheck = 1
    BEGIN
        UPDATE coupon_project_runstatus 
        SET STATUS = 'running' 
        WHERE lock_id = 1;
        SET @result = @lockCheck;
    END
    ELSE
    BEGIN
        DECLARE @retry int = 1;

        -- Retry mechanism if lock is not available
        WHILE @retry <= @numRetries
        BEGIN
            -- Wait for the specified delay
            WAITFOR DELAY @retryDelay;

            -- Increment retry counter
            SET @retry = @retry + 1;

            -- Check lock status again
            SELECT @lockCheck = COUNT(*) 
            FROM coupon_project_runstatus WITH (TABLOCKX) 
            WHERE lock_id = 1 AND STATUS = 'idle';

            -- If lock becomes available, update status and exit loop
            IF @lockCheck = 1
            BEGIN
                UPDATE coupon_project_runstatus 
                SET STATUS = 'running' 
                WHERE lock_id = 1;
                SET @result = @lockCheck;
                BREAK;
            END
        END
    END

    -- Set the final result
    SET @result = @lockCheck;

END;

GO

CREATE TABLE [dbo].[coupon_project_runstatus] (
    [lock_id]   [int]          NOT NULL,
        NOT NULL,
    CONSTRAINT [PK_coupon_project_runstatus] PRIMARY KEY CLUSTERED 
    (
        [lock_id] ASC
    )
    WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
) ON [PRIMARY];

GO

-- Insert an initial record into the table
INSERT INTO [dbo].[coupon_project_runstatus] ([lock_id], [status])
VALUES (1, 'idle');

GO

/****** 
Object:  View [dbo].[RPI_COUPON_ASSIGNMENT_RESEND]   
Notes:   By having an inner join to Offer History this will
         ensure that any Coupon assignments that have been
         Rolled back will not be eligible for resend                         
******/

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

CREATE VIEW [dbo].[RPI_COUPON_ASSIGNMENT_RESEND] AS
SELECT
    CA.[coupon_assignment_id],
    CA.[promotion_code],
    CA.[coupon_code],
    CA.[coupon_type],
    CA.[RPIResolutionKey],
    CA.[resolution_key_int01],
    CA.[resolution_key_int02],
    CA.[resolution_key_int03],
    CA.[resolution_key_str01],
    CA.[resolution_key_str02],
    CA.[resolution_key_str03],
    CA.[insert_ts],
    CA.[dataflow_id],
    CA.[workflow_id],
    CA.[output_id],
    P.[FirstName],
    P.[LastName],
    P.[PID],
    P.[EmailAddress],
    OH.OfferActivityName
FROM 
    [RPIQASampleTenant].[dbo].[RPI_COUPON_ASSIGNMENT] CA
INNER JOIN 
    Person P ON P.PID = CA.[resolution_key_int01]
INNER JOIN 
    [dbo].[OfferHistory] OH ON CA.[resolution_key_int01] = OH.[PID]
    AND CA.[dataflow_id] = OH.[DataflowID]
    AND CA.[workflow_id] = OH.[WorkflowID];

GO