Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='ReleaseJuly2025Week1V2';

if @@ROWCOUNT!=0
	BEGIN
		RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1);
		RETURN;
	END
GO

-- log table
IF OBJECT_ID('dbo.PasswordResetLog', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.PasswordResetLog (
        PasswordResetLogId BIGINT IDENTITY(1,1) PRIMARY KEY,
        CreatedAt DATETIME NOT NULL DEFAULT GETDATE(),
        SystemUserId BIGINT NOT NULL,
        ResetBy BIGINT NOT NULL
    );
END
GO

-- trigger
IF OBJECT_ID('[PasswordReset_LOG_TR]', 'TR') IS NOT NULL
    DROP TRIGGER [PasswordReset_LOG_TR];
GO

CREATE TRIGGER [PasswordReset_LOG_TR]
    ON [PasswordResetLog]
    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 = 'PasswordResetLog', @Deleted = @D, @Inserted = @i;
go


-- proc
IF OBJECT_ID('dbo.spInsertPasswordResetLog', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.spInsertPasswordResetLog;
END
GO

CREATE PROCEDURE dbo.spInsertPasswordResetLog
    @SystemUserId BIGINT,
    @ResetBy BIGINT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO dbo.PasswordResetLog (SystemUserId, ResetBy)
        VALUES (@SystemUserId, @ResetBy);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Optionally: Return error details or rethrow
        DECLARE @ErrorMessage NVARCHAR(4000),
                @ErrorSeverity INT,
                @ErrorState INT;

        SELECT
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

if object_id('dbo.FicManualConfirmationLog', 'U') is null
    begin
        CREATE TABLE dbo.FicManualConfirmationLog
        (
            FicManualConfirmationLogId BIGINT IDENTITY (1,1) PRIMARY KEY,
            Created                    DATETIME     NOT NULL DEFAULT GETDATE(),
            UserId                     BIGINT       NOT NULL,
            BuyshopTransactionId       BIGINT       NOT NULL,
            WorkstationName            VARCHAR(250) NOT NULL
        );
    end
GO

IF EXISTS (SELECT 1
           FROM sys.triggers
           WHERE name = 'FicManualConfirmationLog_TR'
             AND parent_id = OBJECT_ID('dbo.FicManualConfirmationLog'))
    BEGIN
        DROP TRIGGER [dbo].[FicManualConfirmationLog_TR];
    END
GO

CREATE TRIGGER [dbo].[FicManualConfirmationLog_TR]
    ON [dbo].[FicManualConfirmationLog]
    FOR INSERT, UPDATE, DELETE
    AS
BEGIN
    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 = 'FicManualConfirmationLog', @Deleted = @d, @Inserted = @i;
END
GO


IF object_id('dbo.spInsertFicConfirmationLog', 'P') is not null
    begin
        drop procedure dbo.spInsertFicConfirmationLog
    end
go

CREATE PROCEDURE [dbo].[spInsertFicConfirmationLog] @BuyshopTransactionId BIGINT,
                                                    @UserId BIGINT,
                                                    @WorkstationName VARCHAR(250)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO dbo.FicManualConfirmationLog (userid, buyshoptransactionid, workstationname)
        VALUES (@UserId, @BuyshopTransactionId, @WorkstationName);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END

Print 'Insert into script history if everything else is cool';

	Insert into Script_History (RunDate,Script_Name) Values (CURRENT_TIMESTAMP,'ReleaseJuly2025Week1V2');
	IF @@ERROR != 0
		BEGIN
			RAISERROR ('UNABLE TO CONTINUE WITH SCRIPT', 11, 1);
			RETURN;
		END
GO