IF NOT EXISTS(
  SELECT * FROM sys.tables WHERE name = 'BA_SiteRolePrincipals')
BEGIN		
	-- Creating table 'BA_SiteRolePrincipals'
	CREATE TABLE [dbo].[BA_SiteRolePrincipals] (
		[Created] datetime  NOT NULL,
		[BA_SiteRoleId] int  NOT NULL,
		[BA_PrincipalPrincipalId] int  NOT NULL,
		[CreatedBy_PrincipalId] int  NOT NULL
	);

	-- Creating primary key on [BA_PrincipalPrincipalId], [BA_SiteRoleId] in table 'BA_SiteRolePrincipals'
	ALTER TABLE [dbo].[BA_SiteRolePrincipals]
	ADD CONSTRAINT [PK_BA_SiteRolePrincipals]
		PRIMARY KEY CLUSTERED ([BA_PrincipalPrincipalId], [BA_SiteRoleId] ASC);

	-- Creating foreign key on [BA_SiteRoleId] in table 'BA_SiteRolePrincipals'
	ALTER TABLE [dbo].[BA_SiteRolePrincipals]
	ADD CONSTRAINT [FK_BA_SiteRoleBA_SiteRolePrincipal]
		FOREIGN KEY ([BA_SiteRoleId])
		REFERENCES [dbo].[BA_SiteRole]
			([Id])
		ON DELETE NO ACTION ON UPDATE NO ACTION;

	-- Creating non-clustered index for FOREIGN KEY 'FK_BA_SiteRoleBA_SiteRolePrincipal'
	CREATE INDEX [IX_FK_BA_SiteRoleBA_SiteRolePrincipal]
	ON [dbo].[BA_SiteRolePrincipals]
		([BA_SiteRoleId]);

	-- Creating foreign key on [BA_PrincipalPrincipalId] in table 'BA_SiteRolePrincipals'
	ALTER TABLE [dbo].[BA_SiteRolePrincipals]
	ADD CONSTRAINT [FK_BA_PrincipalBA_SiteRolePrincipal]
		FOREIGN KEY ([BA_PrincipalPrincipalId])
		REFERENCES [dbo].[BA_Principal]
			([PrincipalId])
		ON DELETE NO ACTION ON UPDATE NO ACTION;

	-- Creating foreign key on [CreatedBy_PrincipalId] in table 'BA_SiteRolePrincipals'
	ALTER TABLE [dbo].[BA_SiteRolePrincipals]
	ADD CONSTRAINT [FK_PrincipalAddedToRoleBy]
		FOREIGN KEY ([CreatedBy_PrincipalId])
		REFERENCES [dbo].[BA_Principal]
			([PrincipalId])
		ON DELETE NO ACTION ON UPDATE NO ACTION;

	-- Creating non-clustered index for FOREIGN KEY 'FK_PrincipalAddedToRoleBy'
	CREATE INDEX [IX_FK_PrincipalAddedToRoleBy]
	ON [dbo].[BA_SiteRolePrincipals]
		([CreatedBy_PrincipalId]);

	-- Fill the new table with existing. created is set to creationtime of site and created by is set to creator of the site
	INSERT INTO [dbo].[BA_SiteRolePrincipals](BA_SiteRoleId, BA_PrincipalPrincipalId, Created, CreatedBy_PrincipalId)
	SELECT [BA_SiteRoleBA_Principal_BA_Principal_Id], [Principals_PrincipalId], BA_SiteHistory.Created, BA_SiteHistory.CreatedBy_PrincipalId Principals_PrincipalId
		FROM [dbo].[BA_SiteRoleBA_Principal]	
		INNER JOIN BA_SiteRole ON BA_SiteRole.Id = BA_SiteRoleBA_Principal.BA_SiteRoleBA_Principal_BA_Principal_Id
		INNER JOIN BA_Site ON BA_Site.Id = BA_SiteRole.Site_Id
		INNER JOIN BA_SiteHistory ON BA_SiteHistory.Site_Id = BA_Site.Id
		WHERE BA_SiteHistory.StatusSetTo = 0
END;