Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='ReleaseJan2025Week2';
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 SystemSetting where SettingKey = 'MarkdownApproval') = 0
BEGIN
	INSERT INTO SystemSetting 
	(SettingKey, SettingCategoryId, SettingValueString, SettingValueInt, SettingValueBit, TypeFlag) 
	VALUES
	('MarkdownApproval', 1, '0,10,15', -30, (select count(1) from System_Default where Default_Name = 'ISJV' and Default_Value = '1'), 4)
END

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MarkdownApprovalLog')
BEGIN
CREATE TABLE [dbo].[MarkdownApprovalLog](
	[MarkdownApprovalLogId] [bigint] IDENTITY(1,1) NOT NULL,
	[Product_ID] [bigint] NOT NULL,
	[OldRrp] [decimal](10, 2) NOT NULL,
	[NewRrp] [decimal](10, 2) NOT NULL,
	[Created] [datetime] NOT NULL,
	[Authoriser1] [bigint] NOT NULL,
	[Authoriser2] [bigint] NULL,
	[Reason] [varchar] (max) NOT NULL
 CONSTRAINT [PK_MarkdownApprovalLog] PRIMARY KEY CLUSTERED 
(
	[MarkdownApprovalLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF (OBJECT_ID('spCreateMarkdownApprovalLog', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE spCreateMarkdownApprovalLog
END
GO
CREATE PROCEDURE spCreateMarkdownApprovalLog 
	-- Add the parameters for the stored procedure here
	@Product_ID as bigint,
	@OldRrp as decimal(10, 2),
	@NewRrp as decimal(10, 2),
	@Authoriser1 as bigint,
	@Authoriser2 as bigint NULL,
	@Reason as varchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO MarkdownApprovalLog 
	(Product_ID, OldRrp, NewRrp, Created, Authoriser1, Authoriser2, Reason) 
	VALUES 
	(@Product_ID, @OldRrp, @NewRrp, GETDATE(), @Authoriser1, @Authoriser2, @Reason)
END
GO

IF (OBJECT_ID('spGetMarkdownApprovalLogLast30Days', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE spGetMarkdownApprovalLogLast30Days
END
GO
CREATE PROCEDURE spGetMarkdownApprovalLogLast30Days 
	-- Add the parameters for the stored procedure here
	@Product_ID as bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT 
		Created FROM MarkdownApprovalLog 
	WHERE 
		Product_ID = @Product_ID AND
		Created >= DATEADD(day, -30, GETDATE())
	ORDER BY 
		Created DESC
END
GO

IF (SELECT count(1) FROM GenericSystemMessageType WHERE Description = 'FDO Price Change Email Body') = 0
BEGIN
	INSERT INTO GenericSystemMessageType 
	(Description) 
	VALUES 
	('FDO Price Change Email Body')
END

IF (SELECT count(1) FROM GenericSystemMessage WHERE TemplateType = 8) = 0
BEGIN
	INSERT INTO GenericSystemMessage 
	(Template, Active, TemplateType) 
	VALUES 
	('<p>
	  Hi [FDONAME], <br/><br/>
	  Please take note of the below discount authorized at the point of sale at [STORENAME] on [DATE] <br/><br/>
	  [STOCKCODE]<br/><br/>
	  Original price: [OLDRRP] <br/><br/>
	  Discount amount: [DISCOUNTAMMOUNT] <br/><br/>
	  Updated amount: [NEWRRP] <br/><br/>
	  Days on retail floor: [DAYSONFLOOR] <br/><br/>
	  Days since last price change: [DAYSSINCELASTPRICECHANGE] <br/><br/><br/>
	  please do review the above info and contact the store if you have any questions around this discount offered.
	  </p>', 1, 8)
END
IF (SELECT count(1) FROM GenericSystemMessage WHERE TemplateType = 9) = 0
BEGIN
	INSERT INTO GenericSystemMessage 
	(Template, Active, TemplateType) 
	VALUES 
	('<p>
	  Hi [FDONAME], <br/><br/>
	  Please take note of the below price amendment that is being attempted to be done at the store [STORENAME] on [DATE], this amendment has been blocked and would require your authorization in order for the store to proceed.<br/><br/>
	  If you are satisfied with this price amendment please do log onto the store and provide the relevant authorization via cashpos.<br/><br/>  
	  [STOCKCODE]<br/><br/>
	  Original price: [OLDRRP] <br/><br/>
	  Discount amount: [DISCOUNTAMMOUNT] <br/><br/>
	  Updated amount: [NEWRRP] <br/><br/>
	  Days on retail floor: [DAYSONFLOOR] <br/><br/>
	  Days since last price change: [DAYSSINCELASTPRICECHANGE] <br/><br/><br/>
	  please do review the above info and contact the store if you have any questions around this discount offered.
	  </p>', 1, 9)
END

IF (select count(1) from System_Function where Function_Key_Word = 'MarkdownAuth') = 0
BEGIN
	SET IDENTITY_INSERT System_Function ON
	INSERT INTO System_Function 
	(System_Function_ID, Description, Tool_Tip_Text, Allow_Check_List, Requires_Authentification, Function_Key_Word, RightSet, IsMenuBased) 
	VALUES 
	(145, 'Authorise price change markdowns', 'None', 0, 1, 'MarkdownAuth', 1, 0)
	END
	SET IDENTITY_INSERT System_Function OFF
GO

IF (select count(1) from System_Function_Profile where Profile_ID = 20 AND System_Function_ID = 145) = 0
BEGIN
	INSERT INTO System_Function_Profile 
	(Profile_ID, System_Function_ID, Right_Type_ID) 
	VALUES 
	(20, 145, 5)
END

IF (select count(1) from System_User_Function where System_Function_ID = 145) = 0
BEGIN
	INSERT INTO System_User_Function 
	(System_User_ID, System_Function_ID, Right_Type_ID, Store_ID)
	SELECT System_User_ID, 145, 5, (SELECT Default_Value FROM System_Default where Default_Name = 'Main_Store_ID') 
	FROM [System_User] 
	WHERE Global_User_ID IS NOT NULL
END

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