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

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 = 'SsbInitiationFeeInterest') = 0
BEGIN
INSERT INTO SystemSetting (SettingKey, SettingCategoryId, SettingValueString, SettingValueBit, SettingValueInt, TypeFlag) VALUES ('SsbInitiationFeeInterest', 1, '2024-12-18', 1, null, 4)
END

IF COL_LENGTH('dbo.Country', 'HideCountry') IS NULL
ALTER TABLE Country ADD HideCountry bit NOT NULL DEFAULT 0;
GO



UPDATE [dbo].[Country] SET HideCountry = 1 WHERE Country_ID = 55
GO

UPDATE ReturnQuestion
SET [Description] = 'Does the customer have their CashPOS till slip with the correct item and serial number (if applicable) and dated within 6 months from today?'
WHERE [Description] = 'Does the customer have their CashPOS till slip with the correct item and serial number (if applicable) and dated within 12 months from today?';
GO





-- TABLE CREATION
IF OBJECT_ID('dbo.HairColor', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.HairColor (
        HairColorId INT IDENTITY(1,1) PRIMARY KEY, 
        Description NVARCHAR(100) NOT NULL,
        Active BIT NOT NULL DEFAULT 1
    );
END;
GO

IF OBJECT_ID('dbo.EyeColor', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.EyeColor (
        EyeColorId INT IDENTITY(1,1) PRIMARY KEY,
        Description NVARCHAR(100) NOT NULL,
        Active BIT NOT NULL DEFAULT 1
    );
END;
GO

IF OBJECT_ID('dbo.FicTelephoneVerification', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.FicTelephoneVerification (
        FicTelephoneVerificationId INT IDENTITY(1,1) PRIMARY KEY, 
        Description NVARCHAR(100) NOT NULL,
        Active BIT NOT NULL DEFAULT 1
    );
END;
GO

-- TRIGGERS
CREATE TRIGGER [dbo].[HairColor_TR] ON [dbo].[HairColor] 
	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 = 'HairColor', @Deleted = @D,  @Inserted = @i;

	ALTER TABLE [dbo].[HairColor] ENABLE TRIGGER [HairColor_TR]
	GO
	
CREATE TRIGGER [dbo].[EyeColor_TR] ON [dbo].[EyeColor] 
	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 = 'EyeColor', @Deleted = @D,  @Inserted = @i;

	ALTER TABLE [dbo].[EyeColor] ENABLE TRIGGER [EyeColor_TR]
	GO

CREATE TRIGGER [dbo].[FicTelephoneVerification_TR] ON [dbo].[FicTelephoneVerification] 
	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 = 'FicTelephoneVerification', @Deleted = @D,  @Inserted = @i;

	ALTER TABLE [dbo].[FicTelephoneVerification] ENABLE TRIGGER [FicTelephoneVerification_TR]
	GO

-- DATA
INSERT INTO dbo.HairColor (Description, Active)
	VALUES 
    ('Blond', 1),
    ('Red', 1),
    ('Brown', 1),
    ('Black', 1),
    ('Other', 1);

GO

INSERT INTO dbo.EyeColor (Description, Active)
	VALUES 
    ('Blue', 1),
    ('Amber', 1),
    ('Brown', 1),
    ('Green', 1),
    ('Other', 1);
GO

INSERT INTO dbo.FicTelephoneVerification (Description, Active)
	VALUES 
    ('Called and answered', 1),
    ('Called and not answered', 1);
GO


-- FIC additional questionnaire responses
ALTER TABLE Buyshop_FIC_Seller_Info
ADD 
    PlaceOfBirth BIGINT NULL,
    PlaceOfEmployment VARCHAR(255) NULL,
    HairColor INT NULL,
    EyeColor INT NULL,
    ResidentialAddressVerification BIT NULL,
    SourceOfFundsVerification BIT NULL,
    TelephoneNumberVerification INT NULL;
GO





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

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