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

/*
==========================
    Gold Calculator Switch
==========================
*/
if (select count(0) from SystemSetting where SettingKey = 'GoldCalculator') = 0
    begin
        insert into SystemSetting(SettingKey, SettingCategoryId, SettingValueBit, TypeFlag) values('GoldCalculator', 1, 1, 4)
    end
go

/*
===================================
Kruger, British, Protea, Natura
===================================
*/
if (select count(0)
    from SystemSetting
    where SettingKey = 'BCGoldExcludedCategories') = 0
    begin
        insert into SystemSetting(SettingKey, SettingCategoryId, SettingValueString, TypeFlag)
        values ('BCGoldExcludedCategories', 1,
                '3396, 3398, 3400, 3402, 4489, 4493, 4497, 4501, 4505, 4509, 4513, 4517, 4521', 4)
    end
go

/*
===================================
9ct, 14ct, 21ct, 22ct, 24ct
===================================
*/

if (select count(0)
    from SystemSetting
    where SettingKey = 'BCStandaloneExcludedCategories') = 0
    begin
        insert into SystemSetting(SettingKey, SettingCategoryId, SettingValueString, TypeFlag)
        values ('BCStandaloneExcludedCategories', 1,
                '46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 3380, 3382, 3384, 3386, 3388, 3390, 3392, 3394',
                4)
    end
go

/*
==========================
    Gold Pricing
==========================
*/

IF OBJECT_ID('BuyshopGoldPricing', 'U') IS NULL
    BEGIN
        CREATE TABLE BuyshopGoldPricing
        (
            BuyshopGoldPricingId BIGINT IDENTITY (1,1) PRIMARY KEY,
            Created              DATETIME                DEFAULT GETDATE(),
            Caret                VARCHAR(50)    NOT NULL,
            DefaultPricePerGram  DECIMAL(18, 2) NOT NULL DEFAULT 0,
            MinPricePerGram      DECIMAL(18, 2) NOT NULL DEFAULT 0,
            MaxPricePerGram      DECIMAL(18, 2) NOT NULL DEFAULT 0,
            Active               BIT                     DEFAULT 1
        );
    END;
GO

/*
==========================
    Retrieve Gold Pricing
==========================
*/

IF OBJECT_ID('[dbo].[spGetGoldCalculatorPricing]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[spGetGoldCalculatorPricing];
GO

CREATE PROCEDURE [dbo].[spGetGoldCalculatorPricing]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT BuyshopGoldPricingId, Caret, DefaultPricePerGram, MinPricePerGram, MaxPricePerGram
    FROM BuyshopGoldPricing
    WHERE Active = 1;

END
GO

/*
==========================
    Gold Pricing
==========================
*/

IF OBJECT_ID('[dbo].[spRefreshBuyshopGoldCalculatorPricing]', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[spRefreshBuyshopGoldCalculatorPricing];
GO

CREATE PROCEDURE [dbo].[spRefreshBuyshopGoldCalculatorPricing]
    @caret VARCHAR(50),
    @defaultPricePerGram DECIMAL(10, 2),
    @minPricePerGram DECIMAL(10, 2),
    @maxPricePerGram DECIMAL(10, 2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        DELETE FROM BuyshopGoldPricing WHERE Caret = @caret AND Active = 1;

        INSERT INTO BuyshopGoldPricing (caret, DefaultPricePerGram, MinPricePerGram, MaxPricePerGram)
        VALUES (@caret, @defaultPricePerGram, @minPricePerGram, @maxPricePerGram);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback the transaction on error
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Log the error
        DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
        SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

        -- Rethrow the error
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;
go

/*
==========================
    Gold Calculator Log
==========================
*/

if object_id('BuyshopGoldCalculatorLogs', 'U') is null
    begin
        CREATE TABLE BuyshopGoldCalculatorLogs
        (
            BuyshopGoldCalculatorLogId BIGINT PRIMARY KEY IDENTITY (1,1),
            Created                    DATETIME       NOT NULL DEFAULT GETDATE(),
            BuyshopTransactionId       BIGINT         NOT NULL,
            ProductId                  BIGINT         NOT NULL,
            Description                VARCHAR(200),
            Weight                     DECIMAL(10, 2) NOT NULL,
            Caret                      VARCHAR(20)    NOT NULL,
            BuyerId                    BIGINT         NOT NULL,
            Overwritten                BIT            NOT NULL DEFAULT 0,
            AuthorizerId               BIGINT         NULL,
            BuyshopTransLineId         BIGINT         NULL
        );
    end
go


if object_id('spInsertBuyshopGoldCalculatorLog', 'p') is not null
    begin
        drop procedure [dbo].[spInsertBuyshopGoldCalculatorLog];
    end
go

CREATE PROCEDURE  [dbo].[spInsertBuyshopGoldCalculatorLog]
    @BuyshopTransactionId bigint,
    @ProductId bigint,
    @Description varchar(200),
    @Weight decimal(10,2),
    @Caret varchar(20),
    @BuyerId bigint,
    @Overwitten bit,
    @AuthorizerId bigint,
    @BuyshopTransLineId bigint

AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO BuyshopGoldCalculatorLogs(buyshoptransactionid, productid, description, weight, caret, buyerid, Overwritten, authorizerid, BuyshopTransLineId)
        VALUES (@BuyshopTransactionId, @ProductId, @Description, @Weight, @Caret, @BuyerId, @Overwitten, @AuthorizerId, @BuyshopTransLineId)

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback the transaction on error
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Log the error
        DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
        SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

        -- Rethrow the error
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;

END
GO

/*
=================
     Triggers
=================
*/

IF OBJECT_ID('[dbo].[BuyshopGoldPricing_TR]', 'TR') IS NOT NULL
    DROP TRIGGER [dbo].[BuyshopGoldPricing_TR];
GO

CREATE TRIGGER [dbo].[BuyshopGoldPricing_TR]
    ON [dbo].[BuyshopGoldPricing]
    FOR INSERT, UPDATE, DELETE AS declare @i varchar(max), @d varchar(max)
    set @i = convert(varchar(max), (select * from inserted for xml auto, BINARY BASE64))
    set @d = convert(varchar(max), (select * from deleted for xml auto , BINARY BASE64))
    exec LogDMLEvent @TableName = 'BuyshopGoldPricing', @Deleted = @D, @Inserted = @i;
GO

IF OBJECT_ID('[dbo].[BuyshopGoldCalculatorLogs_TR]', 'TR') IS NOT NULL
    DROP TRIGGER [dbo].[BuyshopGoldCalculatorLogs_TR];
GO

CREATE TRIGGER [dbo].[BuyshopGoldCalculatorLogs_TR]
    ON [dbo].[BuyshopGoldCalculatorLogs]
    FOR INSERT, UPDATE, DELETE AS declare @i varchar(max), @d varchar(max)
    set @i = convert(varchar(max), (select * from inserted for xml auto, BINARY BASE64))
    set @d = convert(varchar(max), (select * from deleted for xml auto , BINARY BASE64))
    exec LogDMLEvent @TableName = 'BuyshopGoldCalculatorLogs', @Deleted = @D, @Inserted = @i;
GO


IF COL_LENGTH('Buyshop_Transaction', 'Transaction_Number') IS NOT NULL
BEGIN
    ALTER TABLE Buyshop_Transaction
    ALTER COLUMN Transaction_Number VARCHAR(30);
END
GO


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