-- /**********************************************************************
-- *
-- * Configuration Data for CiviCase Component
-- * For: Sample Case Types - Housing Support and Adult Day Care Referral
-- *
-- **********************************************************************/

SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';

-- /*******************************************************
-- *
-- * Case Types
-- *
-- *******************************************************/
SELECT @option_group_id_case_type        := max(id) from civicrm_option_group where name = 'case_type';
SELECT @max_val :=  IF ( value <> 'NULL',max(value) , 0 ) from civicrm_option_value where option_group_id=@option_group_id_case_type;
SELECT @max_wt  :=  IF ( value <> 'NULL',max(weight), 0 ) from civicrm_option_value where option_group_id=@option_group_id_case_type;

INSERT INTO `civicrm_option_value` (  `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
(SELECT @option_group_id_case_type, 'Housing Support',  @max_val + 1, 'housing_support', NULL, 0,  0, @max_wt + 1, 'Help homeless individuals obtain temporary and long-term housing', 0, 0, 1
 FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'housing_support'));

INSERT INTO `civicrm_option_value` (  `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
(SELECT @option_group_id_case_type, 'Adult Day Care Referral',  @max_val + 2, 'adult_day_care_referral', NULL, 0,  0, @max_wt + 2, 'Arranging adult day care for senior individuals', 0, 0, 1
 FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'adult_day_care_referral'));


-- /*******************************************************
-- *
-- * Case Status - Set names for Open and Closed
-- *
-- *******************************************************/
SELECT @csgId        := max(id) from civicrm_option_group where name = 'case_status';
UPDATE civicrm_option_value SET name = 'Open' where option_group_id = @csgId AND label = 'Ongoing';
UPDATE civicrm_option_value SET name = 'Closed' where option_group_id = @csgId AND label = 'Resolved';

-- /*******************************************************
-- *
-- * Activity Types
-- *
-- *******************************************************/
SELECT @option_group_id_activity_type        := max(id) from civicrm_option_group where name = 'activity_type';

SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_activity_type;

INSERT INTO `civicrm_option_value` ( `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id` )
(SELECT @option_group_id_activity_type, 'Medical evaluation', (SELECT @max_val := @max_val+1), 'Medical evaluation',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
 FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Medical evaluation'));

INSERT INTO `civicrm_option_value` ( `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id` )
(SELECT @option_group_id_activity_type, 'Mental health evaluation', (SELECT @max_val := @max_val+1), 'Mental health evaluation',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
 FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Mental health evaluation'));

INSERT INTO `civicrm_option_value` ( `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id` )
(SELECT @option_group_id_activity_type, 'Secure temporary housing', (SELECT @max_val := @max_val+1), 'Secure temporary housing',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
 FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Secure temporary housing'));

INSERT INTO `civicrm_option_value` ( `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id` )
(SELECT @option_group_id_activity_type, 'Income and benefits stabilization', (SELECT @max_val := @max_val+1), 'Income and benefits stabilization',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
 FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Income and benefits stabilization'));

INSERT INTO `civicrm_option_value` ( `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id` )
(SELECT @option_group_id_activity_type, 'Long-term housing plan', (SELECT @max_val := @max_val+1), 'Long-term housing plan',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
 FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'Long-term housing plan'));

INSERT INTO `civicrm_option_value` ( `option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id` )
(SELECT @option_group_id_activity_type, 'ADC referral', (SELECT @max_val := @max_val+1), 'ADC referral',  NULL, 0,  0, (SELECT @max_val := @max_val+1), '', 0, 0, 1, @caseCompId
 FROM dual WHERE NOT EXISTS (SELECT * FROM `civicrm_option_value`  WHERE `name` = 'ADC referral'));