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

if @@ROWCOUNT!=0
	BEGIN
		RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1);
		RETURN;
	END
GO
-- add new countryid column to seller table
IF COL_LENGTH ('seller', 'CountryId') IS NULL
BEGIN
  ALTER TABLE seller
    ADD CountryId int NULL
END;
Go
-- add new order column to country table
IF COL_LENGTH('Country', 'Order') IS NULL
BEGIN
	ALTER TABLE Country
	ADD [Order] int NOT NULL DEFAULT(0)
END;

GO

-- insert list of countries
set identity_insert country on;

-- count table should already have south africa, zimbabwe or botswana and namiba so we dont
-- want to add duplicate records. But if the table does not have these two
-- records then we add them before adding the rest of the countries
if (select count(0) from country where Description = 'South Africa') = 0
begin
	insert into Country(Country_ID, [Description], [order]) values(1, 'South Africa', 1);
end
go

if (select count(0) from country where Description = 'Zimbabwe') = 0
begin
	insert into Country(Country_ID, [Description], [order]) values(2, 'Zimbabwe', 5);
end
go

if (select count(0) from country where Description = 'Namibia') = 0
begin
insert into country(country_id, [description], [order]) values(3, 'Namibia', 2);
end
go

if (select count(0) from country where Description = 'Botswana') = 0
begin
insert into country(country_id, [description], [order]) values(4, 'Botswana', 3);
end
go

insert into country(country_id, [description], [order]) values(5, 'Afghanistan', 4);
insert into country(country_id, [description], [order]) values(6, 'Malawi', 6);
insert into country(country_id, [description], [order]) values(7, 'Mozambique', 7);
insert into country(country_id, [description], [order]) values(8, 'Albania', 8);
insert into country(country_id, [description], [order]) values(9, 'Algeria', 9);
insert into country(country_id, [description], [order]) values(10, 'Andorra', 10);
insert into country(country_id, [description], [order]) values(11, 'Angola', 11);
insert into country(country_id, [description], [order]) values(12, 'Antigua and Barbuda', 12);
insert into country(country_id, [description], [order]) values(13, 'Argentina', 13);
insert into country(country_id, [description], [order]) values(14, 'Armenia', 14);
insert into country(country_id, [description], [order]) values(15, 'Australia', 15);
insert into country(country_id, [description], [order]) values(16, 'Austria', 16);
insert into country(country_id, [description], [order]) values(17, 'Azerbaijan', 17);
insert into country(country_id, [description], [order]) values(18, 'The Bahamas', 18);
insert into country(country_id, [description], [order]) values(19, 'Bahrain', 19);
insert into country(country_id, [description], [order]) values(20, 'Bangladesh', 20);
insert into country(country_id, [description], [order]) values(21, 'Barbados', 21);
insert into country(country_id, [description], [order]) values(22, 'Belarus', 22);
insert into country(country_id, [description], [order]) values(23, 'Belgium', 23);
insert into country(country_id, [description], [order]) values(24, 'Belize', 24);
insert into country(country_id, [description], [order]) values(25, 'Benin', 25);
insert into country(country_id, [description], [order]) values(26, 'Bhutan', 26);
insert into country(country_id, [description], [order]) values(27, 'Bolivia', 27);
insert into country(country_id, [description], [order]) values(28, 'Bosnia and Herzegovina', 28);
insert into country(country_id, [description], [order]) values(29, 'Brazil', 29);
insert into country(country_id, [description], [order]) values(30, 'Brunei', 30);
insert into country(country_id, [description], [order]) values(31, 'Bulgaria', 31);
insert into country(country_id, [description], [order]) values(32, 'Burkina Faso', 32);
insert into country(country_id, [description], [order]) values(33, 'Burundi', 33);
insert into country(country_id, [description], [order]) values(34, 'Cabo Verde', 34);
insert into country(country_id, [description], [order]) values(35, 'Cambodia', 35);
insert into country(country_id, [description], [order]) values(36, 'Cameroon', 36);
insert into country(country_id, [description], [order]) values(37, 'Canada', 37);
insert into country(country_id, [description], [order]) values(38, 'Cayman Islands, The', 38);
insert into country(country_id, [description], [order]) values(39, 'Central African Republic', 39);
insert into country(country_id, [description], [order]) values(40, 'Central American Federation*', 40);
insert into country(country_id, [description], [order]) values(41, 'Chad', 41);
insert into country(country_id, [description], [order]) values(42, 'Chile', 42);
insert into country(country_id, [description], [order]) values(43, 'China', 43);
insert into country(country_id, [description], [order]) values(44, 'Colombia', 44);
insert into country(country_id, [description], [order]) values(45, 'Comoros', 45);
insert into country(country_id, [description], [order]) values(46, 'Congo Free State, The*', 46);
insert into country(country_id, [description], [order]) values(47, 'Cook Islands', 47);
insert into country(country_id, [description], [order]) values(48, 'Costa Rica', 48);
insert into country(country_id, [description], [order]) values(49, 'Cote d’Ivoire (Ivory Coast)', 49);
insert into country(country_id, [description], [order]) values(50, 'Croatia', 50);
insert into country(country_id, [description], [order]) values(51, 'Cuba', 51);
insert into country(country_id, [description], [order]) values(52, 'Cyprus', 52);
insert into country(country_id, [description], [order]) values(53, 'Czechia', 53);
insert into country(country_id, [description], [order]) values(54, 'Czechoslovakia*', 54);
insert into country(country_id, [description], [order]) values(55, 'Democratic Republic of the Congo', 55);
insert into country(country_id, [description], [order]) values(56, 'Denmark', 56);
insert into country(country_id, [description], [order]) values(57, 'Djibouti', 57);
insert into country(country_id, [description], [order]) values(58, 'Dominica', 58);
insert into country(country_id, [description], [order]) values(59, 'Dominican Republic', 59);
insert into country(country_id, [description], [order]) values(60, 'Duchy of Parma, The*', 60);
insert into country(country_id, [description], [order]) values(61, 'East Germany (German Democratic Republic)*', 61);
insert into country(country_id, [description], [order]) values(62, 'Ecuador', 62);
insert into country(country_id, [description], [order]) values(63, 'Egypt', 63);
insert into country(country_id, [description], [order]) values(64, 'El Salvador', 64);
insert into country(country_id, [description], [order]) values(65, 'Equatorial Guinea', 65);
insert into country(country_id, [description], [order]) values(66, 'Eritrea', 66);
insert into country(country_id, [description], [order]) values(67, 'Estonia', 67);
insert into country(country_id, [description], [order]) values(68, 'Eswatini', 68);
insert into country(country_id, [description], [order]) values(69, 'Ethiopia', 69);
insert into country(country_id, [description], [order]) values(70, 'Federal Government of Germany (1848-49)*', 70);
insert into country(country_id, [description], [order]) values(71, 'Fiji', 71);
insert into country(country_id, [description], [order]) values(72, 'Finland', 72);
insert into country(country_id, [description], [order]) values(73, 'France', 73);
insert into country(country_id, [description], [order]) values(74, 'Gabon', 74);
insert into country(country_id, [description], [order]) values(75, 'Gambia, The', 75);
insert into country(country_id, [description], [order]) values(76, 'Georgia', 76);
insert into country(country_id, [description], [order]) values(77, 'Germany', 77);
insert into country(country_id, [description], [order]) values(78, 'Ghana', 78);
insert into country(country_id, [description], [order]) values(79, 'Grand Duchy of Tuscany, The*', 79);
insert into country(country_id, [description], [order]) values(80, 'Greece', 80);
insert into country(country_id, [description], [order]) values(81, 'Grenada', 81);
insert into country(country_id, [description], [order]) values(82, 'Guatemala', 82);
insert into country(country_id, [description], [order]) values(83, 'Guinea', 83);
insert into country(country_id, [description], [order]) values(84, 'Guinea-Bissau', 84);
insert into country(country_id, [description], [order]) values(85, 'Guyana', 85);
insert into country(country_id, [description], [order]) values(86, 'Haiti', 86);
insert into country(country_id, [description], [order]) values(87, 'Hanover*', 87);
insert into country(country_id, [description], [order]) values(88, 'Hanseatic Republics*', 88);
insert into country(country_id, [description], [order]) values(89, 'Hawaii*', 89);
insert into country(country_id, [description], [order]) values(90, 'Hesse*', 90);
insert into country(country_id, [description], [order]) values(91, 'Holy See', 91);
insert into country(country_id, [description], [order]) values(92, 'Honduras', 92);
insert into country(country_id, [description], [order]) values(93, 'Hungary', 93);
insert into country(country_id, [description], [order]) values(94, 'Iceland', 94);
insert into country(country_id, [description], [order]) values(95, 'India', 95);
insert into country(country_id, [description], [order]) values(96, 'Indonesia', 96);
insert into country(country_id, [description], [order]) values(97, 'Iran', 97);
insert into country(country_id, [description], [order]) values(98, 'Iraq', 98);
insert into country(country_id, [description], [order]) values(99, 'Ireland', 99);
insert into country(country_id, [description], [order]) values(100, 'Israel', 100);
insert into country(country_id, [description], [order]) values(101, 'Italy', 101);
insert into country(country_id, [description], [order]) values(102, 'Jamaica', 102);
insert into country(country_id, [description], [order]) values(103, 'Japan', 103);
insert into country(country_id, [description], [order]) values(104, 'Jordan', 104);
insert into country(country_id, [description], [order]) values(105, 'Kazakhstan', 105);
insert into country(country_id, [description], [order]) values(106, 'Kenya', 106);
insert into country(country_id, [description], [order]) values(107, 'Kingdom of Serbia/Yugoslavia*', 107);
insert into country(country_id, [description], [order]) values(108, 'Kiribati', 108);
insert into country(country_id, [description], [order]) values(109, 'Korea', 109);
insert into country(country_id, [description], [order]) values(110, 'Kosovo', 110);
insert into country(country_id, [description], [order]) values(111, 'Kuwait', 111);
insert into country(country_id, [description], [order]) values(112, 'Kyrgyzstan', 112);
insert into country(country_id, [description], [order]) values(113, 'Laos', 113);
insert into country(country_id, [description], [order]) values(114, 'Latvia', 114);
insert into country(country_id, [description], [order]) values(115, 'Lebanon', 115);
insert into country(country_id, [description], [order]) values(116, 'Lesotho', 116);
insert into country(country_id, [description], [order]) values(117, 'Lew Chew (Loochoo)*', 117);
insert into country(country_id, [description], [order]) values(118, 'Liberia', 118);
insert into country(country_id, [description], [order]) values(119, 'Libya', 119);
insert into country(country_id, [description], [order]) values(120, 'Liechtenstein', 120);
insert into country(country_id, [description], [order]) values(121, 'Lithuania', 121);
insert into country(country_id, [description], [order]) values(122, 'Luxembourg', 122);
insert into country(country_id, [description], [order]) values(123, 'Madagascar', 123);
insert into country(country_id, [description], [order]) values(124, 'Malaysia', 124);
insert into country(country_id, [description], [order]) values(125, 'Maldives', 125);
insert into country(country_id, [description], [order]) values(126, 'Mali', 126);
insert into country(country_id, [description], [order]) values(127, 'Malta', 127);
insert into country(country_id, [description], [order]) values(128, 'Marshall Islands', 128);
insert into country(country_id, [description], [order]) values(129, 'Mauritania', 129);
insert into country(country_id, [description], [order]) values(130, 'Mauritius', 130);
insert into country(country_id, [description], [order]) values(131, 'Mecklenburg-Schwerin*', 131);
insert into country(country_id, [description], [order]) values(132, 'Mecklenburg-Strelitz*', 132);
insert into country(country_id, [description], [order]) values(133, 'Mexico', 133);
insert into country(country_id, [description], [order]) values(134, 'Micronesia', 134);
insert into country(country_id, [description], [order]) values(135, 'Moldova', 135);
insert into country(country_id, [description], [order]) values(136, 'Monaco', 136);
insert into country(country_id, [description], [order]) values(137, 'Mongolia', 137);
insert into country(country_id, [description], [order]) values(138, 'Montenegro', 138);
insert into country(country_id, [description], [order]) values(139, 'Morocco', 139);
insert into country(country_id, [description], [order]) values(140, 'Nassau*', 140);
insert into country(country_id, [description], [order]) values(141, 'Nauru', 141);
insert into country(country_id, [description], [order]) values(142, 'Nepal', 142);
insert into country(country_id, [description], [order]) values(143, 'Netherlands, The', 143);
insert into country(country_id, [description], [order]) values(144, 'New Zealand', 144);
insert into country(country_id, [description], [order]) values(145, 'Nicaragua', 145);
insert into country(country_id, [description], [order]) values(146, 'Niger', 146);
insert into country(country_id, [description], [order]) values(147, 'Nigeria', 147);
insert into country(country_id, [description], [order]) values(148, 'Niue', 148);
insert into country(country_id, [description], [order]) values(149, 'North German Confederation*', 149);
insert into country(country_id, [description], [order]) values(150, 'North German Union*', 150);
insert into country(country_id, [description], [order]) values(151, 'North Macedonia', 151);
insert into country(country_id, [description], [order]) values(152, 'Norway', 152);
insert into country(country_id, [description], [order]) values(153, 'Oldenburg*', 153);
insert into country(country_id, [description], [order]) values(154, 'Oman', 154);
insert into country(country_id, [description], [order]) values(155, 'Orange Free State*', 155);
insert into country(country_id, [description], [order]) values(156, 'Pakistan', 156);
insert into country(country_id, [description], [order]) values(157, 'Palau', 157);
insert into country(country_id, [description], [order]) values(158, 'Panama', 158);
insert into country(country_id, [description], [order]) values(159, 'Papal States*', 159);
insert into country(country_id, [description], [order]) values(160, 'Papua New Guinea', 160);
insert into country(country_id, [description], [order]) values(161, 'Paraguay', 161);
insert into country(country_id, [description], [order]) values(162, 'Peru', 162);
insert into country(country_id, [description], [order]) values(163, 'Philippines', 163);
insert into country(country_id, [description], [order]) values(164, 'Piedmont-Sardinia*', 164);
insert into country(country_id, [description], [order]) values(165, 'Poland', 165);
insert into country(country_id, [description], [order]) values(166, 'Portugal', 166);
insert into country(country_id, [description], [order]) values(167, 'Qatar', 167);
insert into country(country_id, [description], [order]) values(168, 'Republic of Genoa*', 168);
insert into country(country_id, [description], [order]) values(169, 'Republic of Korea (South Korea)', 169);
insert into country(country_id, [description], [order]) values(170, 'Republic of the Congo', 170);
insert into country(country_id, [description], [order]) values(171, 'Romania', 171);
insert into country(country_id, [description], [order]) values(172, 'Russia', 172);
insert into country(country_id, [description], [order]) values(173, 'Rwanda', 173);
insert into country(country_id, [description], [order]) values(174, 'Saint Kitts and Nevis', 174);
insert into country(country_id, [description], [order]) values(175, 'Saint Lucia', 175);
insert into country(country_id, [description], [order]) values(176, 'Saint Vincent and the Grenadines', 176);
insert into country(country_id, [description], [order]) values(177, 'Samoa', 177);
insert into country(country_id, [description], [order]) values(178, 'San Marino', 178);
insert into country(country_id, [description], [order]) values(179, 'Sao Tome and Principe', 179);
insert into country(country_id, [description], [order]) values(180, 'Saudi Arabia', 180);
insert into country(country_id, [description], [order]) values(181, 'Schaumburg-Lippe*', 181);
insert into country(country_id, [description], [order]) values(182, 'Senegal', 182);
insert into country(country_id, [description], [order]) values(183, 'Serbia', 183);
insert into country(country_id, [description], [order]) values(184, 'Seychelles', 184);
insert into country(country_id, [description], [order]) values(185, 'Sierra Leone', 185);
insert into country(country_id, [description], [order]) values(186, 'Singapore', 186);
insert into country(country_id, [description], [order]) values(187, 'Slovakia', 187);
insert into country(country_id, [description], [order]) values(188, 'Slovenia', 188);
insert into country(country_id, [description], [order]) values(189, 'Solomon Islands, The', 189);
insert into country(country_id, [description], [order]) values(190, 'Somalia', 190);
insert into country(country_id, [description], [order]) values(191, 'South Sudan', 191);
insert into country(country_id, [description], [order]) values(192, 'Spain', 192);
insert into country(country_id, [description], [order]) values(193, 'Sri Lanka', 193);
insert into country(country_id, [description], [order]) values(194, 'Sudan', 194);
insert into country(country_id, [description], [order]) values(195, 'Suriname', 195);
insert into country(country_id, [description], [order]) values(196, 'Sweden', 196);
insert into country(country_id, [description], [order]) values(197, 'Switzerland', 197);
insert into country(country_id, [description], [order]) values(198, 'Syria', 198);
insert into country(country_id, [description], [order]) values(199, 'Tajikistan', 199);
insert into country(country_id, [description], [order]) values(200, 'Tanzania', 200);
insert into country(country_id, [description], [order]) values(201, 'Texas*', 201);
insert into country(country_id, [description], [order]) values(202, 'Thailand', 202);
insert into country(country_id, [description], [order]) values(203, 'Timor-Leste', 203);
insert into country(country_id, [description], [order]) values(204, 'Togo', 204);
insert into country(country_id, [description], [order]) values(205, 'Tonga', 205);
insert into country(country_id, [description], [order]) values(206, 'Trinidad and Tobago', 206);
insert into country(country_id, [description], [order]) values(207, 'Tunisia', 207);
insert into country(country_id, [description], [order]) values(208, 'Turkey', 208);
insert into country(country_id, [description], [order]) values(209, 'Turkmenistan', 209);
insert into country(country_id, [description], [order]) values(210, 'Tuvalu', 210);
insert into country(country_id, [description], [order]) values(211, 'Two Sicilies*', 211);
insert into country(country_id, [description], [order]) values(212, 'Uganda', 212);
insert into country(country_id, [description], [order]) values(213, 'Ukraine', 213);
insert into country(country_id, [description], [order]) values(214, 'Union of Soviet Socialist Republics*', 214);
insert into country(country_id, [description], [order]) values(215, 'United Arab Emirates, The', 215);
insert into country(country_id, [description], [order]) values(216, 'United Kingdom, The', 216);
insert into country(country_id, [description], [order]) values(217, 'Uruguay', 217);
insert into country(country_id, [description], [order]) values(218, 'Uzbekistan', 218);
insert into country(country_id, [description], [order]) values(219, 'Vanuatu', 219);
insert into country(country_id, [description], [order]) values(220, 'Venezuela', 220);
insert into country(country_id, [description], [order]) values(221, 'Vietnam', 221);
insert into country(country_id, [description], [order]) values(222, 'Württemberg*', 222);
insert into country(country_id, [description], [order]) values(223, 'Yemen', 223);
insert into country(country_id, [description], [order]) values(224, 'Zambia', 224);
set identity_insert country off;
go

-- set all seller country id to 'south africa' where their identity type id is 'south african' and the store is an south african store
/*
if (select count(0) from System_Default where Default_Name = 'Region_Code' and Default_Value = 'SADC') = 0
begin
	update seller set CountryId = 1 where Identity_Type_ID = 1;
end;
go
*/

CREATE TABLE [dbo].[Buyshop_FIC_Seller_Info](
	[Buyshop_FIC_Seller_Info_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Created] [DateTime] NOT NULL,
	[Buyshop_Transaction_ID] [bigint] NOT NULL,
	[Seller_ID] [bigint] NOT NULL,
	[IsAssociate] [bit] NOT NULL,
	[IsSanction] [bit] NOT NULL,
 CONSTRAINT [PK_Buyshop_FIC_Seller_Info] PRIMARY KEY CLUSTERED 
(
	[Buyshop_FIC_Seller_Info_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

IF COL_LENGTH ('Seller', 'CompanyRegTypeId') IS NULL
BEGIN
  ALTER TABLE Seller
    ADD CompanyRegTypeId bigint NULL
END;

IF (OBJECT_ID('AddBuyshopSellerFicAnswers', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE AddBuyshopSellerFicAnswers
END
GO
CREATE PROCEDURE AddBuyshopSellerFicAnswers 
	-- Add the parameters for the stored procedure here
	@buyshopId bigint,
	@sellerId bigint,
	@answer1 bit, 
	@answer2 bit
AS
BEGIN
	SET NOCOUNT ON;

    INSERT INTO Buyshop_FIC_Seller_Info 
	(Buyshop_Transaction_ID, Created, Seller_ID, IsAssociate, IsSanction) 
	VALUES 
	(@buyshopId, GETDATE(), @sellerId, @answer1, @answer2)
END
GO

IF (OBJECT_ID('CheckBuyshopSellerFicAnswers', 'P')) IS NOT NULL
BEGIN
DROP PROCEDURE CheckBuyshopSellerFicAnswers
END
GO
CREATE PROCEDURE CheckBuyshopSellerFicAnswers 
	-- Add the parameters for the stored procedure here
	@sellerId bigint
AS
BEGIN
	SET NOCOUNT ON;

    SELECT TOP(1) COUNT(1) FROM Buyshop_FIC_Seller_Info WHERE Seller_ID = @sellerId AND Created > DATEADD(DAY, -730, GETDATE())
END
GO

CREATE TABLE [dbo].[Buyshop_Company_Registration_Type](
	[Buyshop_Company_Registration_Type_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Description] [varchar] (100) NOT NULL
 CONSTRAINT [PK_Buyshop_Company_Registration_Type] PRIMARY KEY CLUSTERED 
(
	[Buyshop_Company_Registration_Type_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

IF (SELECT COUNT(1) FROM Buyshop_Company_Registration_Type) = 0
BEGIN
INSERT INTO Buyshop_Company_Registration_Type 
(Description) 
VALUES 
('Trust'),
('Partnership'),
('Non-profit Organization'),
('Government Organization'),
('Private Company'),
('Listed Company')
END

IF COL_LENGTH ('Buyshop_Transaction', 'RepresentativeDetails') IS NULL
BEGIN
  ALTER TABLE Buyshop_Transaction
    ADD RepresentativeDetails varchar(max) NULL
END;

GO

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

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