--First Find the forecasting.forecast that will be modified as a result of the Sales Item event

DECLARE @ForecastID bigint = 3566313
DECLARE @Threshold tinyint = 1	-- How far apart the stored Systems Sales \ Transaction can be from our test calc. 
								-- This accounts for the code's "Rounding" process which we do not attempt here.

---------------------------------------------------------

DECLARE @EntityID bigint
DECLARE @ForecastDate datetime
SELECT @EntityID = EntityID, @ForecastDate = BusinessDay FROM Forecasting.Forecast WITH(NOLOCK) WHERE ForecastID = @ForecastID

DECLARE @AdjustmentsTable TABLE (SalesItemId bigint, Interval varchar(50), Price money, TotalAdjustment money)
INSERT INTO @AdjustmentsTable
	SELECT a.SalesItemId, FORMAT(a.IntervalStart, 'HH:mm') Interval, a.Price, SUM(a.TransactionAdjustment - 1) TotalAdjustment
	FROM Forecasting.EventSalesItemAdjustments a
	JOIN Forecasting.EventProfile p ON p.EventProfileId = a.EventProfileId AND p.EntityId = @EntityID
	JOIN Forecasting.EventProfileTags t ON t.EventProfileId = p.EventProfileId AND t.EntityId = @EntityID AND t.Date = @ForecastDate
	GROUP BY a.SalesItemId, FORMAT(a.IntervalStart, 'HH:mm'), a.Price

SELECT 'ALL ITEMS' [ALL ITEMS], d.SalesItemId, d.IntervalStart, d.ServiceType, d.RawTransactionCount, a.TotalAdjustment, d.SystemTransactionCount,
	ROUND(d.RawTransactionCount * (1 + a.TotalAdjustment), 0) SystemTransactionCalc
FROM Forecasting.SalesItemMetricDetail d
JOIN (SELECT SalesItemId, Interval, SUM(TotalAdjustment) TotalAdjustment FROM @AdjustmentsTable
	GROUP BY SalesItemId, Interval
) a ON a.SalesItemId = d.SalesItemId AND a.Interval = FORMAT(d.IntervalStart, 'HH:mm')
WHERE d.ForecastId = @ForecastID AND d.ServiceType NOT IN (1,2)
ORDER BY d.SalesItemId, d.IntervalStart, d.ServiceType

SELECT 'BEYOND THRESHOLD' [BEYOND THRESHOLD], d.SalesItemId, d.IntervalStart, d.ServiceType, d.RawTransactionCount, a.TotalAdjustment, d.SystemTransactionCount,
	ROUND(d.RawTransactionCount * (1 + a.TotalAdjustment), 0) SystemTransactionCalc
FROM Forecasting.SalesItemMetricDetail d
JOIN (SELECT SalesItemId, Interval, SUM(TotalAdjustment) TotalAdjustment FROM @AdjustmentsTable
	GROUP BY SalesItemId, Interval
) a ON a.SalesItemId = d.SalesItemId AND a.Interval = FORMAT(d.IntervalStart, 'HH:mm')
WHERE d.ForecastId = @ForecastID AND d.ServiceType NOT IN (1,2) AND ABS(ROUND(d.RawTransactionCount * (1 + a.TotalAdjustment), 0) - SystemTransactionCount) > 1
ORDER BY d.SalesItemId, d.IntervalStart, d.ServiceType

DECLARE @AdjustmentSalesTable TABLE (Interval varchar(50), ServiceType int, SalesAdjustment money)
INSERT INTO @AdjustmentSalesTable
	SELECT FORMAT(d.IntervalStart, 'HH:mm') Interval, d.ServiceType,
		SUM(d.RawTransactionCount * a.TotalAdjustment * a.Price) SalesAdjustment
	FROM Forecasting.SalesItemMetricDetail d
	JOIN @AdjustmentsTable a ON a.SalesItemId = d.SalesItemId AND a.Interval = FORMAT(d.IntervalStart, 'HH:mm')
	WHERE d.ForecastId = @ForecastID AND d.ServiceType NOT IN (1,2)
	GROUP BY d.IntervalStart, d.ServiceType

DECLARE @TotalSalesAdjustment money, @TotalAdjustedSalesOriginal money
DECLARE @InsideRawSales money
SELECT 
	@InsideRawSales = SUM(d.RawSales),
	@TotalSalesAdjustment = SUM(CASE WHEN a.SalesAdjustment > 0 THEN SalesAdjustment ELSE 0 END),
	@TotalAdjustedSalesOriginal = SUM(CASE WHEN a.SalesAdjustment > 0 THEN d.RawSales ELSE 0 END)
FROM Forecasting.MetricDetail d
LEFT JOIN @AdjustmentSalesTable a ON a.ServiceType = d.ServiceType AND a.Interval = FORMAT(d.IntervalStart, 'HH:mm')
WHERE d.ForecastId = @ForecastID AND d.ServiceType NOT IN (1,2)
--ORDER BY d.IntervalStart, d.ServiceType

SELECT BusinessDay, GenerationDate, @InsideRawSales InsideRawSales, SystemSales,
	@TotalSalesAdjustment TotalSalesAdjustment, @TotalAdjustedSalesOriginal TotalAdjustedSalesOriginal, @TotalSalesAdjustment / @TotalAdjustedSalesOriginal SalesAdjustmentPct,
	ROUND(@InsideRawSales + @TotalSalesAdjustment, 0) SystemSalesCalc
FROM Forecasting.Forecast 
WHERE ForecastID = @ForecastID

DECLARE @MetricTable TABLE (IntervalStart datetime, ServiceType int, RawSales money, SystemSales money, SystemSalesCalc money,
								RawTransactionCount money, SystemTransactionCount money, SystemTransactionCalc money)
INSERT INTO @MetricTable
SELECT d.IntervalStart, d.ServiceType, 
	d.RawSales, d.SystemSales, ROUND(d.RawSales + a.SalesAdjustment, 0) SystemSalesCalc,
	d.RawTransactionCount, d.SystemTransactionCount, 
	CASE WHEN a.SalesAdjustment > 0 THEN ROUND(d.RawTransactionCount * (1 + (@TotalSalesAdjustment / @TotalAdjustedSalesOriginal)), 0) ELSE d.SystemTransactionCount END SystemTransactionCalc
FROM Forecasting.MetricDetail d
JOIN @AdjustmentSalesTable a ON a.ServiceType = d.ServiceType AND a.Interval = FORMAT(d.IntervalStart, 'HH:mm')
WHERE d.ForecastId = @ForecastID AND d.ServiceType NOT IN (1,2)
ORDER BY d.IntervalStart, d.ServiceType

SELECT 'ALL METRICS' [ALL METRICS], * FROM @MetricTable

SELECT 'BEYOND THRESHOLD OF ' + CONVERT(varchar, @Threshold) [BEYOND THRESHOLD], * 
FROM @MetricTable WHERE ABS(SystemSalesCalc - SystemSales) > @Threshold OR ABS(SystemTransactionCalc - SystemTransactionCount) > @Threshold

/*
SELECT RawTransactionCount, RawSales, SystemTransactionCount, SystemSales 
FROM Forecasting.Forecast f 
JOIN (
	SELECT * FROM Forecasting.SalesItemMetricDetail i
	WHERE i.ForecastId = @ForecastID
) d ON d.ForecastId = f.ForecastId
WHERE f.ForecastId = @ForecastID
*/