Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='ReleaseMarch2025Week1';
if @@ROWCOUNT!=0
	BEGIN
		RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1);
		RETURN;
	END
GO

if (select count(1) from Day_End_Summary_Category where Day_End_Summary_Category_ID = 253) = 0
BEGIN
SET IDENTITY_INSERT [dbo].[Day_End_Summary_Category] ON 
insert into Day_End_Summary_Category 
(Day_End_Summary_Category_ID, Description, Type, FriendlyDescription, CalculationTypeId, VatIncl, Detail, ReportingDivisionId, isamount) 
values 
(253, 'DiscStockAllSuppliersValue', 1, 'All Suppliers Discontinued Stock Value', null, null, null, null, 1)
SET IDENTITY_INSERT [dbo].[Day_End_Summary_Category] OFF
END
GO

declare @StoreId int;

set @StoreId = (select default_value from system_default where Default_Name = 'Main_store_id');

If(@StoreId in (2,3,6,17,24,25,26,27,38,41,50,54,62,67,68,69,70,75,76,82,85,86,90,93,96,100,106,110,111,113,118,121,124,126,127,128,129,131,135,142,146,149,150,155,156,158,162,164,165,173,174,177,182,183,185,199,215,219,220,227,242,246,251,262,276,9,35,43,101,186,243))
begin
				insert into Product_Catalogue (Product_ID,Supplier_ID,Out_Of_Stock,Category_ID,Default_Initial_Par,Imported_Sales_90,Imported_Sales_60,Imported_Sales_30,Product_Catalogue_Status_ID,Last_Catalogue_Status_ID,Last_Catalogue_Changed,Last_Change_Reason,OverridePar)	 
	 SELECT si.product_id,
                    CASE WHEN
                    (SELECT  top 1 supplier_id FROM stock_receipt_line srl LEFT JOIN stock_receipt sr ON srl.stock_receipt_id = sr.stock_receipt_id WHERE srl.stock_item_id = si.stock_item_id ORDER BY stock_receipt_line_id DESC ) IS NULL
                    THEN 
                    (SELECT top 1 supplier_id FROM supplier WHERE supplier_id !=1 )
                    ELSE 
                    (SELECT top 1 supplier_id FROM stock_receipt_line srl LEFT JOIN stock_receipt sr ON srl.stock_receipt_id = sr.stock_receipt_id WHERE srl.stock_item_id = si.stock_item_id ORDER BY stock_receipt_line_id DESC)
                    END AS Supplier_id,0,
                    p.product_category_id,0,0,0,0,3,1,GETDATE(), 'Store DC old 3rd party stock',0
                     FROM stock_item_count sic
                    LEFT JOIN stock_item si ON si.stock_item_id = sic.stock_item_id
                    LEFT JOIN product p ON p.product_id = si.product_id
                    WHERE item_stock_type_id = 4 AND sic.stock_holding_group_id = 2 AND sic.stock_count>0
                    AND p.hq_product_id IS NULL AND original_purchase_date < '2024-11-15' AND 
                
                    (SELECT COUNT(0) FROM product_catalogue pc WHERE 
                    pc.product_id = p.product_id ) = 0

end

Print 'Insert into script history if everything else is cool';
	Insert into Script_History (RunDate,Script_Name) Values (CURRENT_TIMESTAMP,'ReleaseMarch2025Week1');
	IF @@ERROR != 0
		BEGIN
			RAISERROR ('UNABLE TO CONTINUE WITH SCRIPT', 11, 1);
			RETURN;
		END
GO