-- ### Add instance metadata ###
-- Find all InstanceIds for courseManagement
select BA_Instance.Id, BA_MetadataDefinition.name
into #InstanceIds
from BA_Instance
	inner join ba_app on BA_App.Id = BA_Instance.AppId
	left outer join BA_MetadataDefinition on BA_MetadataDefinition.Instance_Id = BA_Instance.Id and BA_MetadataDefinition.name = 'W365CM_AllowCancelSignupAfterDeadline'
where BA_App.Name = 'CourseManagement'

insert into BA_MetadataDefinition
	(name, Instance_Id, Definition, type, Required)
select 'W365CM_AllowCancelSignupAfterDeadline' as name, #InstanceIds.Id as instance_id, '{"definition": {
    "type": "checkbox",
    "canBeDeleted": false,
    "canBeModified": true,
    "required": true,
    "label": "Allow members to add colleagues",
    "defaultvalue": true
  },"type":"checkbox","canBeDeleted":false,"canBeModified":true,"required":false,"label":"Allow cancel signup after deadline","defaultvalue":false}' as definition, 'checkbox' as type, 0 as required
from #InstanceIds
where #InstanceIds.Name is null

-- ### Add sitetype metadata ###
-- Find all SiteTypeIds for the instances, which we need to add reference to metadata def.

--select -- this is just for debugging. It will return all sitetypes, including the definitionId we need to add and a column (DefFromBindingTable) which will be null if its not already added. Also returns the max sortOrder from BA_SiteTypeMetadataDefinitions for the sitetype
--	BA_MetadataDefinition.id as definitionId,
--	BA_SiteTypeMetadataDefinitions.BA_MetadataDefinitionId as DefFromBindingTable, -- If this value is null, we need to insert a record
--	BA_SiteType.id as sitetypeId,
--	BA_SiteType.InstanceId,
--	BA_SiteType.Name,
--	(select MAX(SortOrder)
--	from BA_SiteTypeMetadataDefinitions sub
--	where sub.BA_SiteTypeId = BA_SiteType.Id) as MaxSortOrder
--from BA_SiteType
--	inner join #InstanceIds on #InstanceIds.Id = BA_SiteType.InstanceId
--	inner join BA_MetadataDefinition on BA_MetadataDefinition.Instance_Id = #InstanceIds.Id and BA_MetadataDefinition.Name = 'W365CM_AllowCancelSignupAfterDeadline'
--	left outer join BA_SiteTypeMetadataDefinitions on BA_SiteTypeMetadataDefinitions.BA_SiteTypeId = BA_SiteType.Id and BA_SiteTypeMetadataDefinitions.BA_MetadataDefinitionId = BA_MetadataDefinition.id

-- do the actual insert, from what i've learned from the select above
insert into BA_SiteTypeMetadataDefinitions
	(BA_SiteTypeId, BA_MetadataDefinitionId, SortOrder)
select
	BA_SiteType.id as BA_SiteTypeId,
	BA_MetadataDefinition.id as BA_MetadataDefinitionId,
	(select MAX(SortOrder)
	from BA_SiteTypeMetadataDefinitions sub
	where sub.BA_SiteTypeId = BA_SiteType.Id)+1 as SortOrder
from BA_SiteType
	inner join #InstanceIds on #InstanceIds.Id = BA_SiteType.InstanceId
	inner join BA_MetadataDefinition on BA_MetadataDefinition.Instance_Id = #InstanceIds.Id and BA_MetadataDefinition.Name = 'W365CM_AllowCancelSignupAfterDeadline'
	left outer join BA_SiteTypeMetadataDefinitions on BA_SiteTypeMetadataDefinitions.BA_SiteTypeId = BA_SiteType.Id and BA_SiteTypeMetadataDefinitions.BA_MetadataDefinitionId = BA_MetadataDefinition.id
where BA_SiteTypeMetadataDefinitions.BA_MetadataDefinitionId is null

-- ### Add data to existing coursed ###
insert into BA_Metadata
	(Value, MetadataDefinition_Id, Site_Id)
select
	'true' as Value,
	(select BA_SiteTypeMetadataDefinitions.BA_MetadataDefinitionId
	from BA_SiteTypeMetadataDefinitions inner join BA_MetadataDefinition on BA_MetadataDefinition.Id = BA_SiteTypeMetadataDefinitions.BA_MetadataDefinitionId and BA_SiteTypeMetadataDefinitions.BA_SiteTypeId = BA_Site.SiteType_Id
	where BA_MetadataDefinition.Name = 'W365CM_AllowCancelSignupAfterDeadline') as MetadataDefinition_Id,
	ba_site.id as Site_Id
from ba_site
	inner join BA_SiteType on BA_Site.SiteType_Id = BA_SiteType.Id
	inner join BA_Instance on BA_SiteType.InstanceId = BA_Instance.Id
	inner join BA_App on BA_Instance.AppId = BA_App.Id
where
    BA_App.Name = 'CourseManagement'
	and ( -- find those where we have no data for definition 'W365CM_AllowCancelSignupAfterDeadline'
        select BA_Metadata.Id
	from BA_Metadata
		inner join BA_MetadataDefinition on BA_Metadata.MetadataDefinition_Id = BA_MetadataDefinition.Id
	where BA_Metadata.Site_Id = BA_Site.Id and BA_MetadataDefinition.Name = 'W365CM_AllowCancelSignupAfterDeadline'
    ) is null