-- Read-only sales-fact check. Run the whole file in a fresh SSMS query window.
-- No permanent object or data is changed. Only session temporary tables are used.
-- First pass 03-check-warehouse.sql against the unchanged imported Northwind data.
-- Confirm the actual generated fact view and each source-origin field below.
-- Do not substitute surrogate warehouse keys for original Northwind business IDs.
SET NOCOUNT ON;
DECLARE @ExpectedDatabase sysname = N'NorthwindWarehouse';
IF DB_NAME() <> @ExpectedDatabase
    THROW 51200, 'Select the recorded deployed warehouse before running this check.', 1;

-- Example view name only: replace it if your recorded generated name differs.
DECLARE @FactSchema sysname = N'DWH', @FactView sysname = N'FACT_Order Details';
-- Replace these six placeholders using the fact column origins in AnalyticsCreator.
DECLARE @FactOrderID sysname = N'REPLACE_ORDER_DETAILS_ORDER_ID';
DECLARE @FactProductID sysname = N'REPLACE_ORDER_DETAILS_PRODUCT_ID';
DECLARE @FactCustomerID sysname = N'REPLACE_ORDERS_CUSTOMER_ID';
DECLARE @FactQuantity sysname = N'REPLACE_ORDER_DETAILS_QUANTITY';
DECLARE @FactUnitPrice sysname = N'REPLACE_ORDER_DETAILS_UNIT_PRICE';
DECLARE @FactDiscount sysname = N'REPLACE_ORDER_DETAILS_DISCOUNT';

-- Imported target names and columns: use the mappings checked in 03-check-warehouse.sql.
DECLARE @LinesSchema sysname = N'IMP', @LinesTable sysname = N'Order Details';
DECLARE @OrdersSchema sysname = N'IMP', @OrdersTable sysname = N'Orders';
DECLARE @LinesOrderID sysname = N'OrderID', @LinesProductID sysname = N'ProductID';
DECLARE @LinesQuantity sysname = N'Quantity', @LinesUnitPrice sysname = N'UnitPrice';
DECLARE @LinesDiscount sysname = N'Discount';
DECLARE @OrdersOrderID sysname = N'OrderID', @OrdersCustomerID sysname = N'CustomerID';

IF EXISTS (SELECT 1 FROM (VALUES (@FactOrderID), (@FactProductID), (@FactCustomerID),
    (@FactQuantity), (@FactUnitPrice), (@FactDiscount)) AS fields(Name)
    WHERE Name IS NULL OR Name = N'' OR LEFT(Name, 8) = N'REPLACE_')
    THROW 51201, 'Replace every fact-field placeholder with a confirmed source-origin field name.', 1;
IF OBJECT_ID(QUOTENAME(@FactSchema) + N'.' + QUOTENAME(@FactView), N'V') IS NULL
    THROW 51202, 'The recorded fact view was not found or is not visible to this account.', 1;

CREATE TABLE #Expected (
    OrderID int, ProductID int,
    CustomerID nvarchar(4000) COLLATE Latin1_General_100_BIN2,
    Quantity decimal(10,0), UnitPrice decimal(19,4), Discount decimal(9,4));
CREATE TABLE #Actual (
    OrderID decimal(28,10), ProductID decimal(28,10),
    CustomerID nvarchar(4000) COLLATE Latin1_General_100_BIN2,
    Quantity decimal(19,4), UnitPrice decimal(19,4), Discount decimal(9,4));

DECLARE @Sql nvarchar(max);
SET @Sql = N'INSERT #Expected (OrderID,ProductID,CustomerID,Quantity,UnitPrice,Discount)
SELECT l.' + QUOTENAME(@LinesOrderID) + N',l.' + QUOTENAME(@LinesProductID)
    + N',o.' + QUOTENAME(@OrdersCustomerID) + N',l.' + QUOTENAME(@LinesQuantity)
    + N',l.' + QUOTENAME(@LinesUnitPrice) + N',l.' + QUOTENAME(@LinesDiscount)
    + N' FROM ' + QUOTENAME(@LinesSchema) + N'.' + QUOTENAME(@LinesTable) + N' AS l
JOIN ' + QUOTENAME(@OrdersSchema) + N'.' + QUOTENAME(@OrdersTable) + N' AS o
ON o.' + QUOTENAME(@OrdersOrderID) + N'=l.' + QUOTENAME(@LinesOrderID) + N';';
EXEC sys.sp_executesql @Sql;

SET @Sql = N'INSERT #Actual (OrderID,ProductID,CustomerID,Quantity,UnitPrice,Discount)
SELECT ' + QUOTENAME(@FactOrderID) + N',' + QUOTENAME(@FactProductID)
    + N',' + QUOTENAME(@FactCustomerID) + N',' + QUOTENAME(@FactQuantity)
    + N',' + QUOTENAME(@FactUnitPrice) + N',' + QUOTENAME(@FactDiscount)
    + N' FROM ' + QUOTENAME(@FactSchema) + N'.' + QUOTENAME(@FactView) + N';';
EXEC sys.sp_executesql @Sql;

SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,
       @FactSchema AS FactSchema, @FactView AS FactView;
IF EXISTS (SELECT 1 FROM #Expected WHERE OrderID IS NULL OR ProductID IS NULL
    OR CustomerID IS NULL OR Quantity IS NULL OR UnitPrice IS NULL OR Discount IS NULL)
    THROW 51203, 'An expected source-origin value is null. Recheck the imported sample.', 1;
IF EXISTS (SELECT 1 FROM #Actual WHERE OrderID IS NULL OR ProductID IS NULL
    OR CustomerID IS NULL OR Quantity IS NULL OR UnitPrice IS NULL OR Discount IS NULL)
    THROW 51204, 'A fact key or sales value is null. Inspect the field mapping and joins.', 1;
IF EXISTS (SELECT OrderID, ProductID FROM #Expected GROUP BY OrderID, ProductID HAVING COUNT(*) <> 1)
    THROW 51205, 'The imported order-line join is not unique. Repeat the imported-data check.', 1;
IF EXISTS (SELECT OrderID, ProductID FROM #Actual GROUP BY OrderID, ProductID HAVING COUNT(*) <> 1)
BEGIN
    SELECT OrderID, ProductID, COUNT(*) AS Copies
    FROM #Actual GROUP BY OrderID, ProductID HAVING COUNT(*) <> 1;
    THROW 51206, 'The fact repeats an order-line key. Inspect joins before using its sales totals.', 1;
END;

DECLARE @ExpectedRows int = (SELECT COUNT(*) FROM #Expected);
DECLARE @ActualRows int = (SELECT COUNT(*) FROM #Actual);
DECLARE @MissingOrDifferent int = (SELECT COUNT(*) FROM
    (SELECT * FROM #Expected EXCEPT SELECT * FROM #Actual) AS differences);
DECLARE @UnexpectedOrDifferent int = (SELECT COUNT(*) FROM
    (SELECT * FROM #Actual EXCEPT SELECT * FROM #Expected) AS differences);
SELECT @ExpectedRows AS ImportedOrderLines, @ActualRows AS FactOrderLines,
       @MissingOrDifferent AS MissingOrDifferent,
       @UnexpectedOrDifferent AS UnexpectedOrDifferent,
       CASE WHEN @ExpectedRows = 2155 AND @ActualRows = 2155
                 AND @MissingOrDifferent = 0 AND @UnexpectedOrDifferent = 0
            THEN N'PASS' ELSE N'FAIL' END AS FactValueCheck;
IF @ExpectedRows <> 2155 OR @ActualRows <> 2155
   OR @MissingOrDifferent <> 0 OR @UnexpectedOrDifferent <> 0
BEGIN
    SELECT TOP (20) N'Missing or different' AS Difference, d.*
    FROM (SELECT * FROM #Expected EXCEPT SELECT * FROM #Actual) AS d
    ORDER BY d.OrderID, d.ProductID;
    SELECT TOP (20) N'Unexpected or different' AS Difference, d.*
    FROM (SELECT * FROM #Actual EXCEPT SELECT * FROM #Expected) AS d
    ORDER BY d.OrderID, d.ProductID;
    THROW 51207, 'The fact does not match the checked imported order lines and customer assignments.', 1;
END;

-- Comparisons above reject fractional fact keys/quantities that differ from
-- the source. Normalize Quantity to an integer only AFTER those checks.
-- UnitPrice and Discount use four decimals; round each net line BEFORE summing.
DECLARE @Units decimal(38,0), @NetSales decimal(38,2);
SELECT @Units = SUM(Quantity),
       @NetSales = SUM(CAST(ROUND(UnitPrice * CAST(Quantity AS decimal(10,0)) *
           (CAST(1 AS decimal(9,4)) - Discount), 2) AS decimal(19,2)))
FROM #Actual;
SELECT @ActualRows AS OrderLines, @Units AS Units, @NetSales AS NetSales;
IF @Units IS NULL OR @Units <> 51317 OR @NetSales IS NULL OR @NetSales <> 1265793.29
    THROW 51208, 'The fact units or line-rounded sales total differs from the fixed Northwind sample.', 1;

SELECT N'PASS: fact grain, business keys, customer assignments, sales values and totals match.' AS Result;
-- This does not execute PBIP measures or certify every analytical-model column.
-- Dimension row counts may include an explicit unknown member: inspect their
-- generated definition rather than assuming they equal the source counts.