-- Fix internal properties for manually added Color fields
--
/*
20 Fix Color Manually Entered Fields.sql 

1) Backup Database 
2) From the affected workspace create a new field named Color (Simple Text) 
2a) Save, Save & Publish 
3) Run the attached Script 
4) Restart Tomcat 
5) Publish 
6) Test 


*/
-- set parameters
declare @shouldCommit int = 0;  -- set to 1 when testing is done
declare @colorColumnName varchar(100) = 'color_1'
--
declare @colorDefnVerIds table (defn_id bigint, defn_ver_id bigint not null, defn_ver_status_id bigint not null);
declare @fieldRole_cs_id bigint = 0;
declare @fieldType_cs_id bigint = 0;
declare @fieldPermission_cs_id bigint = 0;
declare @fieldClassification_cs_id bigint = 0;
declare @isInternal_cs_id bigint = 0;
declare @rowsToInsert bigint;
declare @CfgSettingValTableName varchar(100) = 'fpscdb001_system.cfg_setting_val';
declare @Config_Setting_Val_next_id bigint;


declare @colorCfgSettings table (cfg_setting_name varchar(100) not null,val_type_cd varchar(20) not null, val_map_key varchar(20) null
	, string_val varchar(255) null, text_val varchar(max) null);
-- get ids

select @fieldRole_cs_id = cfg_setting_id
from fpscdb001_system.cfg_setting where cfg_setting_const = 'fieldRole';
select @fieldType_cs_id = cfg_setting_id
from fpscdb001_system.cfg_setting where cfg_setting_const = 'fieldType';
select @fieldPermission_cs_id = cfg_setting_id
from fpscdb001_system.cfg_setting where cfg_setting_const = 'fieldPermission';
select @fieldClassification_cs_id = cfg_setting_id
from fpscdb001_system.cfg_setting where cfg_setting_const = 'fieldClassification';
select @isInternal_cs_id = cfg_setting_id
from fpscdb001_system.cfg_setting where cfg_setting_const = 'isInternal';

insert into @colorCfgSettings (cfg_setting_name ,val_type_cd , val_map_key , string_val, text_val)
values ('fieldRole','S',null,'COLOR',null);
insert into @colorCfgSettings (cfg_setting_name ,val_type_cd , val_map_key , string_val, text_val)
values ('helptext','T','en_US',null,'');
insert into @colorCfgSettings (cfg_setting_name ,val_type_cd , val_map_key , string_val, text_val)
values ('mouseOverText','T','en_US',null,'');
insert into @colorCfgSettings (cfg_setting_name ,val_type_cd , val_map_key , string_val, text_val)
values ('fieldMapperFirstSortField','S',null,'',null);
insert into @colorCfgSettings (cfg_setting_name ,val_type_cd , val_map_key , string_val, text_val)
values ('fieldMapperFirstSortOrder','S',null,'ASC',null);
insert into @colorCfgSettings (cfg_setting_name ,val_type_cd , val_map_key , string_val, text_val)
values ('fieldMapperSecondSortField','S',null,'',null);
insert into @colorCfgSettings (cfg_setting_name ,val_type_cd , val_map_key , string_val, text_val)
values ('fieldMapperSecondSortOrder','S',null,'ASC',null);


-- find fields that need to be fixed

insert into @colorDefnVerIds (defn_id, defn_ver_id, defn_ver_status_id)
select defn_id, defn_ver_id, defn_ver_status_id
from fpscdb001_system.defn_ver
where defn_id in
(
select fld_defn_id from fpscdb001_system.v_cntnr_item_fld
where fieldrole is null and col_name = @colorColumnName and field_externalName_en_US = 'Color'
);

select i.*, c.* , cs.cfg_setting_id
from @colorDefnVerIds i
cross join @colorCfgSettings c  -- we want to add missing cfg_settings
join fpscdb001_system.cfg_setting cs on cs.cfg_setting_const = c.cfg_setting_name
left join fpscdb001_system.cfg_setting_val already on already.cfg_setting_id = cs.cfg_setting_id and already.owner_id = i.defn_ver_id
where already.cfg_setting_val_id is null -- not already existing
;

select @rowsToInsert =  @@rowcount;


if @rowsToInsert > 0
begin
	-- Update CSVs
	begin tran

	print 'perms'

	update fpscdb001_system.cfg_setting_val
	set string_val = 'READ_ONLY'
	from @colorDefnVerIds dv
	join fpscdb001_system.cfg_setting_val csv 
	on dv.defn_ver_id = csv.owner_id
	and cfg_setting_id = @fieldPermission_cs_id;

	update fpscdb001_system.cfg_setting_val
	set string_val = 'COLORFIELD'
	from @colorDefnVerIds dv
	join fpscdb001_system.cfg_setting_val csv 
	on dv.defn_ver_id = csv.owner_id
	and cfg_setting_id = @fieldType_cs_id;

	update fpscdb001_system.cfg_setting_val
	set boolean_val = 1
	from @colorDefnVerIds dv
	join fpscdb001_system.cfg_setting_val csv 
	on dv.defn_ver_id = csv.owner_id
	and cfg_setting_id = @isInternal_cs_id;

	update fpscdb001_system.cfg_setting_val
	set string_val = 'ATTRIBUTE'
	from @colorDefnVerIds dv
	join fpscdb001_system.cfg_setting_val csv 
	on dv.defn_ver_id = csv.owner_id
	and cfg_setting_id = @fieldClassification_cs_id;

	--insert into fpscdb001_system.cfg_setting_val
	exec fpscdb001_system.p_id_generator_getNext @db_table_name = @CfgSettingValTableName, @nextId=@Config_Setting_Val_next_id OUTPUT, @increment=@rowsToInsert;

	insert into fpscdb001_system.cfg_setting_val
	(cfg_setting_val_id, cfg_setting_owner_type_name, cfg_setting_id, owner_id, val_type_cd
	, string_val, text_val)
	select @Config_Setting_Val_next_id-@rowsToInsert+row_number() over (order by defn_ver_id) as id, 'DEFINITION',cs.cfg_setting_id
		,defn_ver_id as ownerId,c.val_type_cd,c.string_val,c.text_val
	from @colorDefnVerIds i
	cross join @colorCfgSettings c  -- we want to add missing cfg_settings
	join fpscdb001_system.cfg_setting cs on cs.cfg_setting_const = c.cfg_setting_name
	left join fpscdb001_system.cfg_setting_val already on already.cfg_setting_id = cs.cfg_setting_id and already.owner_id = i.defn_ver_id
	where already.cfg_setting_val_id is null -- not already existing;

	if @shouldCommit = 1
	begin
		commit;
	end
	else begin
		rollback tran;
	end;


end;

