--10 - Create tables and views - Check for Missing SLA Work Target Fields and Option Values
-- check for any changes to the reference data
--TICKET
--SERVICE_LEVEL_TARGET
--WORK_TARGET
if OBJECT_ID('fpscdb001_system.v_option_values_reference','V') is not null
begin
	drop view fpscdb001_system.v_option_values_reference;
end;
go
create view fpscdb001_system.v_option_values_reference
as select ds.defn_subtype_id, q.*
from
(
select 'TICKET' as defn_subtype_name,'COLOR' as fieldRole
,'fieldType' as cfg_setting_const,'COLORFIELD' as Value_as_String
, 'READ_ONLY' as fieldPermission
) q
join fpscdb001_system.v_defn_subtype ds on ds.defn_subtype_name = q.defn_subtype_name
;
go
if OBJECT_ID('fpscdb001_system.v_option_fields_reference','V') is not null
begin
	drop view fpscdb001_system.v_option_fields_reference;
end;
go
create view fpscdb001_system.v_option_fields_reference
as
select distinct defn_subtype_id,defn_subtype_name,fieldRole,fieldPermission
from  fpscdb001_system.v_option_values_reference;
go
if OBJECT_ID('fpscdb001_system.v_option_tables_reference','V') is not null
begin
	drop view fpscdb001_system.v_option_tables_reference;
end;
go
create view fpscdb001_system.v_option_tables_reference
as
select distinct defn_subtype_id,defn_subtype_name
from  fpscdb001_system.v_option_values_reference;
go

-- now check field values.
-- save as a table for later updates and query simplicity

if OBJECT_ID('fpscdb001_system.vt_system_health$populate','P') is not null
begin
	drop proc fpscdb001_system.vt_system_health$populate;
end;
go

create proc fpscdb001_system.vt_system_health$populate
as
begin

if OBJECT_ID('fpscdb001_system.vt_cntnr_item_fld','U') is not null
begin
	drop table fpscdb001_system.vt_cntnr_item_fld;
end;

select c.*,cid.defn_subtype_id
into fpscdb001_system.vt_cntnr_item_fld
from fpscdb001_system.v_option_fields_reference fr
join (fpscdb001_system.v_cntnr_item_fld c
join fpscdb001_system.defn cid on cid.defn_id = c.item_defn_id)
on fr.fieldRole = c.fieldRole and fr.defn_subtype_id = cid.defn_subtype_id;

if OBJECT_ID('fpscdb001_system.vt_system_health$option_values','U') is not null
begin
	drop table fpscdb001_system.vt_system_health$option_values;
end;


select qq."02.Missing/Extra Field Option Values"
,qq.cntnr_external_name, qq.item_external_name_en_US, qq.field_externalName_en_US
,qq.HealthCheck, qq.OptionValue, qq.Reference_Value
,qq.fieldPermission
,qq.fld_defn_id, qq.fld_defn_ver_id
,qq.option_defn_id
,qq.option_defn_ver_id
,qq.active_cfg_setting_val_id
, qq.containerSchemaName + '.' + qq.table_name as FullTableName
,qq.col_name
,qq.totalCt, qq.OptionValueCt,qq.Reference_ValueCt
into fpscdb001_system.vt_system_health$option_values
from
(
select 
q.*
,count(*) over (partition by fld_defn_ver_id) as totalCt
,count(OptionValue) over (partition by fld_defn_ver_id) as OptionValueCt
,count(Reference_Value) over (partition by fld_defn_ver_id) as Reference_ValueCt
from (
select ' ' as "02.Missing/Extra Field Option Values"
, case 
when rv.Value_as_String is null then 'Extra Field Value' 
else '' end as HealthCheck
,'active' as active_draft
,c.cntnr_external_name, 
c.containerSchemaName,
c.table_name,
c.col_name,
c.item_external_name_en_US,c.fld_defn_id,c.fld_defn_ver_id
,v.defn_id as option_defn_id
,v.defn_ver_id as option_defn_ver_id
, c.field_externalName_en_US 
,v.value_as_string as OptionValue
,rv.value_as_string as Reference_Value
,v.cfg_setting_val_id as active_cfg_setting_val_id
,rv.fieldPermission
--,rv.Is_Default
from fpscdb001_system.vt_cntnr_item_fld c
join fpscdb001_system.v_option_fields_reference r on r.defn_subtype_id = c.defn_subtype_id and r.fieldRole = c.fieldRole
join (fpscdb001_system.defn_reln dr 
join fpscdb001_system.v_defn_ver_cfg_setting_val v on v.defn_ver_id = dr.to_defn_ver_id and v.cfg_setting_const = 'optionValue') 
on dr.from_defn_ver_id = c.fld_defn_ver_id --and v.value_as_string = r.value_as_string
and not(c.fieldRole='STATUS' and r.defn_subtype_name='TICKET')
--
left join fpscdb001_system.v_option_values_reference rv on rv.defn_subtype_id = r.defn_subtype_id and rv.fieldRole = r.fieldRole and rv.Value_as_String = v.Value_as_String 
--
union
-- now check field values
select ' ' as "02.Missing/Extra Field Option Values"
, case when v.Value_as_String is null then 'Missing Field Value' else '' end as HealthCheck
,'active' as active_draft
,c.cntnr_external_name, 
c.containerSchemaName,
c.table_name,
c.col_name,
c.item_external_name_en_US,c.fld_defn_id,c.fld_defn_ver_id
,v.defn_id as option_defn_id
,v.defn_ver_id as option_defn_ver_id
, c.field_externalName_en_US 
,v.value_as_string as OptionValue
,r.value_as_string as Reference_Value
,v.cfg_setting_val_id as active_cfg_setting_val_id
,r.fieldPermission
--,r.Is_Default
from fpscdb001_system.vt_cntnr_item_fld c
join fpscdb001_system.v_option_values_reference r on r.defn_subtype_id = c.defn_subtype_id and r.fieldRole = c.fieldRole
left join (fpscdb001_system.defn_reln dr 
join fpscdb001_system.v_defn_ver_cfg_setting_val v on v.defn_ver_id = dr.to_defn_ver_id and v.cfg_setting_const = 'optionValue') 
on dr.from_defn_ver_id = c.fld_defn_ver_id and v.value_as_string = r.value_as_string
and not(c.fieldRole='STATUS' and r.defn_subtype_name='TICKET')
--
) q
) qq
order by cntnr_external_name, fld_defn_ver_id,HealthCheck desc, Reference_Value;


end;
go
exec fpscdb001_system.vt_system_health$populate;


--select * from fpscdb001_system.vt_system_health$option_values;

select qq.*
from (
select q.*
,count(*) over (partition by item_defn_id) as totalCt
,count(Reference_FieldRole) over (partition by item_defn_id) as ReferenceRoleCt
,count(field_externalName_en_US) over (partition by item_defn_id) as FieldCt
from (
select ' ' as "01.Missing Fields"
, case when f.fieldrole is null then 'Missing Field' else '' end as HealthCheck
,'active' as active_Draft
,i.container_description,i.cntnr_external_name,i.item_external_name_en_US, i.item_external_name_other,i.containerSchemaName,dts.table_name, i.item_defn_id 
,'#' as "#"
,r.fieldRole as Reference_FieldRole
,f.field_externalName_en_US
--,'||' as "||"
--,f.*
from (fpscdb001_system.v_cntnr_item_active i -- item
join fpscdb001_system.dyn_table_spec dts on dts.reference_id = i.item_defn_id
join fpscdb001_system.defn cid on cid.defn_id = i.item_defn_id)
join fpscdb001_system.v_option_fields_reference r on r.defn_subtype_id = cid.defn_subtype_id   -- xmult required item field 
left join fpscdb001_system.v_item_fld_defn f on f.item_defn_id = i.item_defn_id and f.fieldRole = r.fieldRole
) q
) qq
where not (qq.FieldCt = qq.ReferenceRoleCt );


