-- /************************************************************************
-- *
-- * MySQL Script for civicrm database/tables - upgradation from 2.0 -> 2.1
-- *
-- * Changes / updates related to option group & values
-- *
-- *************************************************************************/


-- Insert new option value 'Pledge Acknowledgment', 'Pledge Reminder' for option_group - activity_type (CRM-3244, CRM-3270)
-- First get max weight and value

SELECT   @option_act := civicrm_option_group.id,
	 @max_val    := max(ROUND(civicrm_option_value.value)), 
         @max_wt     := max(civicrm_option_value.weight) 
FROM     civicrm_option_value, civicrm_option_group
WHERE    civicrm_option_group.name = 'activity_type'
AND      civicrm_option_value.option_group_id = civicrm_option_group.id
GROUP BY civicrm_option_group.id;

-- Insert new option values for option_group - 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`) 
VALUES
  (@option_act, 'Pledge Acknowledgment', (SELECT @max_val := @max_val+1), 'Pledge Acknowledgment', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 'Send Pledge Acknowledgment.', 0, 1, 1, NULL),
  (@option_act, 'Pledge Reminder', (SELECT @max_val := @max_val+1), 'Pledge Reminder', NULL, 1, NULL, (SELECT @max_wt := @max_wt+1), 'Send Pledge Reminder.', 0, 1, 1, NULL);

-- Insert new option value for option_group - user_dashboard_options

SELECT @option_ud    := id from civicrm_option_group where name = 'user_dashboard_options';
INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `weight`, `is_active`, `is_default`) VALUES
( @option_ud, 'My Contacts / Organizations', 5, 5, 1, NULL);


-- add option group & values (CRM-2488)

INSERT INTO `civicrm_option_group` (`name`, `description`, `is_reserved`, `is_active`) VALUES ('recur_frequency_units', '{ts escape="sql"}Recurring Frequency Units{/ts}', 0, 1);

SELECT @option_group_id_fu := max(id) from civicrm_option_group where name = 'recur_frequency_units';

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`) 
VALUES
  (@option_group_id_fu, 'daily'    , 'day'  ,    'day',  NULL, 0, NULL, 1, NULL, 0, 1, 1, NULL),
  (@option_group_id_fu, 'weekly'   , 'week' ,   'week',  NULL, 0, NULL, 2, NULL, 0, 1, 1, NULL),
  (@option_group_id_fu, 'monthly'  , 'month',  'month',  NULL, 0, NULL, 3, NULL, 0, 1, 1, NULL),
  (@option_group_id_fu, 'yearly'   , 'year' ,   'year',  NULL, 0, NULL, 4, NULL, 0, 1, 1, NULL);


-- add option group and values CRM-2781

INSERT INTO `civicrm_option_group` (`name`, `description`, `is_reserved`, `is_active`) VALUES ('mapping_type', '{ts escape="sql"}Mapping Type{/ts}', 0, 1);

SELECT @option_group_id_mt := max(id) from civicrm_option_group where name = 'mapping_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`) 
VALUES
  (@option_group_id_mt, '{ts escape="sql"}Search Builder{/ts}',      1, 'Search Builder',      NULL, 0, 0,    1, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Contact{/ts}',      2, 'Import Contact',      NULL, 0, 0,    2, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Activity{/ts}',     3, 'Import Activity',     NULL, 0, 0,    3, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Contribution{/ts}', 4, 'Import Contribution', NULL, 0, 0,    4, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Membership{/ts}',   5, 'Import Membership',   NULL, 0, 0,    5, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Import Participant{/ts}',  6, 'Import Participant',  NULL, 0, 0,    6, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Contact{/ts}',      7, 'Export Contact',      NULL, 0, 0,    7, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Contribution{/ts}', 8, 'Export Contribution', NULL, 0, 0,    8, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Membership{/ts}',   9, 'Export Membership',   NULL, 0, 0,    9, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Participant{/ts}', 10, 'Export Participant',  NULL, 0, 0,   10, NULL, 0, 1, 1, NULL),
  (@option_group_id_mt, '{ts escape="sql"}Export Pledge{/ts}',      11, 'Export Pledge',       NULL, 0, 0,   11, NULL, 0, 1, 1, NULL);

-- fix too much escaping in civicrm_option_value

UPDATE civicrm_option_value SET label = 'Addt\'l Address 1' WHERE label = 'Addt\\\'l Address 1';
UPDATE civicrm_option_value SET label = 'Addt\'l Address 2' WHERE label = 'Addt\\\'l Address 2';


-- fix for CRM-3209

UPDATE civicrm_option_value v,
       civicrm_option_group g
SET    v.description = v.name, v.name = v.value
WHERE  v.option_group_id = g.id
AND    g.name LIKE 'civicrm_price_field.amount.%';


-- CRM-3217

ALTER TABLE `civicrm_option_value` MODIFY `label` VARCHAR(255) NULL DEFAULT '' COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.';


-- Insert new option value for option_group = 'contribution_status'

SELECT @option_cs    := id from civicrm_option_group where name = 'contribution_status';
INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `weight`, `is_active`, `is_default`) VALUES
( @option_cs, 'Overdue', 6, 'Overdue', 6, 1, NULL);


ALTER TABLE `civicrm_option_group`
    ADD UNIQUE `UI_name` (name);

ALTER TABLE `civicrm_option_value`
    MODIFY label varchar(255) NOT NULL DEFAULT '';


-- option group / value changes

-- those affiliated to contact_view_options group need updation for name column

SELECT @option_group_id_cvOpt := max(id) from civicrm_option_group where name = 'contact_view_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_cvOpt;

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`) 
VALUES
  (@option_group_id_cvOpt, 'Activities'   ,   1, 'activity', NULL, 0, NULL,  1,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Relationships',   2, 'rel', NULL, 0, NULL,  2,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Groups'       ,   3, 'group', NULL, 0, NULL,  3,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Notes'        ,   4, 'note', NULL, 0, NULL,  4,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Tags'         ,   5, 'tag', NULL, 0, NULL,  5,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Change Log'   ,   6, 'log', NULL, 0, NULL,  6,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Contributions',   7, 'CiviContribute', NULL, 0, NULL,  7,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Memberships'  ,   8, 'CiviMember', NULL, 0, NULL,  8,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Events'       ,   9, 'CiviEvent', NULL, 0, NULL,  9,  NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Cases'        ,  10, 'CiviCase', NULL, 0, NULL,  10, NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Grants'       ,  11, 'CiviGrant', NULL, 0, NULL,  11, NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'PledgeBank'   ,  12, 'PledgeBank', NULL, 0, NULL,  12, NULL, 0, 0, 1, NULL),
  (@option_group_id_cvOpt, 'Pledges'      ,  13, 'CiviPledge', NULL, 0, NULL,  13, NULL, 0, 0, 1, NULL);

-- those affiliated to contact_edit_options group need updation for name column

SELECT @option_group_id_ceOpt          := max(id) from civicrm_option_group where name = 'contact_edit_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_ceOpt;

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`) 
VALUES
  (@option_group_id_ceOpt, 'Communication Preferences',   1, 'CommBlock', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL),
  (@option_group_id_ceOpt, 'Demographics'             ,   2, 'Demographics', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL),
  (@option_group_id_ceOpt, 'Tags and Groups'          ,   3, 'TagsAndGroups', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL),
  (@option_group_id_ceOpt, 'Notes'                    ,   4, 'Notes', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL);


-- those affiliated to advanced_search_options group

SELECT @option_group_id_asOpt          := max(id) from civicrm_option_group where name = 'advanced_search_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_asOpt;

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`) 
VALUES
  (@option_group_id_asOpt, 'Address Fields'          ,   1, 'location', NULL, 0, NULL,  1, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Custom Fields'           ,   2, 'custom', NULL, 0, NULL,  2, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Activities'              ,   3, 'activity', NULL, 0, NULL,  3, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Relationships'           ,   4, 'relationship', NULL, 0, NULL,  4, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Notes'                   ,   5, 'notes', NULL, 0, NULL,  5, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Change Log'              ,   6, 'changeLog', NULL, 0, NULL,  6, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Contributions'           ,   7, 'CiviContribute', NULL, 0, NULL,  7, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Memberships'             ,   8, 'CiviMember', NULL, 0, NULL,  8, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Events'                  ,   9, 'CiviEvent', NULL, 0, NULL, 9, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Cases'                   ,  10, 'CiviCase', NULL, 0, NULL, 10, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Grants'                  ,  12, 'CiviGrant', NULL, 0, NULL, 12, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Demographics'            ,  13, 'demographics', NULL, 0, NULL, 13, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'PledgeBank'              ,  14, 'PledgeBank', NULL, 0, NULL, 14, NULL, 0, 0, 1, NULL),
  (@option_group_id_asOpt, 'Pledges'                 ,  15, 'CiviPledge', NULL, 0, NULL, 15, NULL, 0, 0, 1, NULL);


-- those affiliated to user_dashboard_options group

SELECT @option_group_id_udOpt          := max(id) from civicrm_option_group where name = 'user_dashboard_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_udOpt;

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`) 
VALUES
  (@option_group_id_udOpt, 'Groups'                     , 1, 'Groups', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'Contributions'              , 2, 'CiviContribute', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'Memberships'                , 3, 'CiviMember', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'Events'                     , 4, 'CiviEvent', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'My Contacts / Organizations', 5, 'Permissioned Orgs', NULL, 0, NULL, 5, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'PledgeBank'                 , 6, 'PledgeBank', NULL, 0, NULL, 6, NULL, 0, 0, 1, NULL),
  (@option_group_id_udOpt, 'Pledges'                    , 7, 'CiviPledge', NULL, 0, NULL, 7, NULL, 0, 0, 1, NULL);


-- those affiliated to address_options group

SELECT @option_group_id_adOpt          := max(id) from civicrm_option_group where name = 'address_options';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_adOpt;

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`) 
VALUES
  (@option_group_id_adOpt, 'Street Address'    ,  1, 'street_address', NULL, 0, NULL,  1, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Addt\'l Address 1'  ,  2, 'supplemental_address_1', NULL, 0, NULL,  2, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Addt\'l Address 2'  ,  3, 'supplemental_address_2', NULL, 0, NULL,  3, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'City'              ,  4, 'city'          , NULL, 0, NULL,  4, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Zip / Postal Code' ,  5, 'postal_code'   , NULL, 0, NULL,  5, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Postal Code Suffix',  6, 'postal_code_suffix', NULL, 0, NULL,  6, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'County'            ,  7, 'county'        , NULL, 0, NULL,  7, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'State / Province'  ,  8, 'state_province', NULL, 0, NULL,  8, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Country'           ,  9, 'country'       , NULL, 0, NULL,  9, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Latitude'          , 10, 'geo_code_1'    , NULL, 0, NULL, 10, NULL, 0, 0, 1, NULL),
  (@option_group_id_adOpt, 'Longitude'         , 11, 'geo_code_2', NULL, 0, NULL, 11, NULL, 0, 0, 1, NULL);


-- those affiliated to custom_search group

SELECT @option_group_id_csearch        := max(id) from civicrm_option_group where name = 'custom_search';

DELETE FROM civicrm_option_value WHERE option_group_id=@option_group_id_csearch;

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`) 
VALUES
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Sample'               , 1, 'CRM_Contact_Form_Search_Custom_Sample'      , NULL, 0, NULL, 1, 'Household Name and State', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_ContributionAggregate', 2, 'CRM_Contact_Form_Search_Custom_ContributionAggregate', NULL, 0, NULL, 2, 'Contribution Aggregate', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Basic'                , 3, 'CRM_Contact_Form_Search_Custom_Basic'       , NULL, 0, NULL, 3, 'Basic Search', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Group'                , 4, 'CRM_Contact_Form_Search_Custom_Group'       , NULL, 0, NULL, 4, 'Include / Exclude Contacts in a Group / Tag', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_PostalMailing'        , 5, 'CRM_Contact_Form_Search_Custom_PostalMailing', NULL, 0, NULL, 5, 'Postal Mailing', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_Proximity'            , 6, 'CRM_Contact_Form_Search_Custom_Proximity', NULL, 0, NULL, 6, 'Proximity Search', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_EventAggregate', 7, 'CRM_Contact_Form_Search_Custom_EventAggregate', NULL, 0, NULL, 7, 'Event Aggregate', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_ActivitySearch', 8, 'CRM_Contact_Form_Search_Custom_ActivitySearch', NULL, 0, NULL, 8, 'Activity Search', 0, 0, 1, NULL),
  (@option_group_id_csearch , 'CRM_Contact_Form_Search_Custom_PriceSet', 9, 'CRM_Contact_Form_Search_Custom_PriceSet', NULL, 0, NULL, 9, 'Price Set Details for Event Participants', 0, 0, 1, NULL);

-- option groups and values for 'wysiwyg_editor' , 'from_email_address'.
INSERT INTO 
   `civicrm_option_group` (`name`, `description`, `is_reserved`, `is_active`)
VALUES 
   ('from_email_address', 'From Email Address', 0, 1),
   ('wysiwyg_editor'    , 'WYSIWYG Editor'    , 0, 1);
SELECT @option_we := max(id) from civicrm_option_group where name = 'wysiwyg_editor';

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`) 
VALUES
  (@option_we, 'TinyMCE'    , 1, NULL, NULL, 0, NULL, 1, NULL, 0, 1, 1, NULL),
  (@option_we, 'FCKEditor'  , 2, NULL, NULL, 0, NULL, 2, NULL, 0, 1, 1, NULL);

-- Insert new option value address_name for option_group = 'address_options'

SELECT   @option_act := civicrm_option_group.id,
	 @max_val    := max(ROUND(civicrm_option_value.value)), 
         @max_wt     := max(civicrm_option_value.weight) 
FROM     civicrm_option_value, civicrm_option_group
WHERE    civicrm_option_group.name = 'address_options'
AND      civicrm_option_value.option_group_id = civicrm_option_group.id
GROUP BY civicrm_option_group.id;


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`) 
VALUES
  (@option_act, 'Address Name', (SELECT @max_val := @max_val+1), 'address_name', NULL, 0, NULL, (SELECT @max_wt := @max_wt+1), NULL, 0, 0, 1, NULL);

-- update filter for participant-status option values

SELECT @og_ps_id := id from civicrm_option_group where name = 'participant_status';

UPDATE civicrm_option_value SET filter=1 WHERE option_group_id=@og_ps_id AND label IN ('Registered', 'Attended');