#-----------------------------------------------------------------------------------------------------------------------
# new fields
#-----------------------------------------------------------------------------------------------------------------------
ALTER TABLE `mn_Member` 
ADD `id_mncar_type` INT NOT NULL AFTER `edam_id` ,
ADD `id_mncar_status` INT NOT NULL AFTER `id_mncar_type` ,
ADD `mncar_status_changed` DATETIME NULL AFTER `id_mncar_status` ,
ADD `id_mncar_l_type` INT NOT NULL AFTER `mncar_status_changed` ,
ADD `id_mncar_l_status` INT NOT NULL AFTER `id_mncar_l_type` ,
ADD `alternate_status_changed` DATETIME NULL AFTER `id_mncar_l_status` ,
ADD `id_misc_type` INT NOT NULL AFTER `alternate_status_changed`,
ADD `id_misc_status` INT NOT NULL AFTER `id_misc_type`,
ADD `misc_status_changed` DATETIME NULL AFTER `id_misc_status`;

ALTER TABLE `mn_Member` ADD `is_support_account` ENUM( 'n', 'y' ) NOT NULL AFTER `misc_status_changed` ;

ALTER TABLE `mn_Member` ADD INDEX ( `id_mncar_type` );
ALTER TABLE `mn_Member` ADD INDEX ( `id_mncar_status` );
ALTER TABLE `mn_Member` ADD INDEX ( `id_mncar_l_type` );
ALTER TABLE `mn_Member` ADD INDEX ( `id_mncar_l_status` );
ALTER TABLE `mn_Member` ADD INDEX ( `id_misc_type` );
ALTER TABLE `mn_Member` ADD INDEX ( `id_misc_status` );

#-----------------------------------------------------------------------------------------------------------------------
# new dictionaries
#-----------------------------------------------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `mn_DMncarMemberType` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

INSERT INTO `mn_DMncarMemberType` (`id`, `title`, `sorter`) VALUES
(1, 'Designated Realtor', 1),
(2, 'Realtor', 2),
(3, 'Secondary Member', 3),
(4, 'Senior Member', 4),
(5, 'Paid Non Member', 5),
(6, 'Institute Affiliate', 6),
(7, 'Designated Affiliate', 7);

CREATE TABLE IF NOT EXISTS `mn_DMncarLMemberType` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `mn_DMncarLMemberType` (`id`, `title`, `sorter`) VALUES
(1, 'Broker', 1),
(2, 'Affiliate', 2),
(3, 'EDC Affiliate', 3),
(4, 'EDC EDAM Affiliate', 4);

DROP TABLE IF EXISTS `mn_DMiscMemberType`;
CREATE TABLE `mn_DMiscMemberType` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Дамп даних таблиці `mn_DMiscMemberType`
--

INSERT INTO `mn_DMiscMemberType` (`id`, `title`, `sorter`) VALUES
(1, 'Alternate Association Member', 1),
(2, 'Staff', 2),
(3, 'Non-Member', 3),
(4, 'Support', 4);


#-----------------------------------------------------------------------------------------------------------------------
# patch for new fields
#-----------------------------------------------------------------------------------------------------------------------
# MNCAR
UPDATE `mn_Member` SET `id_mncar_type` = 1 WHERE `id_member` = 1 AND `id_member_type` = 1; -- Designated Realtor
UPDATE `mn_Member` SET `id_mncar_type` = 2 WHERE `id_member` = 1 AND `id_member_type` = 2; -- Realtor
UPDATE `mn_Member` SET `id_mncar_type` = 3 WHERE `id_member` = 1 AND `id_member_type` = 3; -- Secondary Member
UPDATE `mn_Member` SET `id_mncar_type` = 4 WHERE `id_member` = 1 AND `id_member_type` = 4; -- Senior Member
UPDATE `mn_Member` SET `id_mncar_type` = 5 WHERE `id_member` = 1 AND `id_member_type` = 5; -- Paid Non Member
UPDATE `mn_Member` SET `id_mncar_type` = 6 WHERE `id_member` = 1 AND `id_member_type` = 11; -- Institute Affiliate
UPDATE `mn_Member` SET `id_mncar_type` = 7 WHERE `id_member` = 1 AND `id_member_type` = 13; -- Designated Affiliate
# Alternate
UPDATE `mn_Member` SET `id_mncar_l_type` = 2 WHERE `id_member` = 1 AND `id_member_type` = 10; -- Affiliate
UPDATE `mn_Member` SET `id_mncar_l_type` = 3 WHERE `id_member` = 1 AND `id_member_type` = 12; -- EDC Affiliate
UPDATE `mn_Member` SET `id_mncar_l_type` = 4 WHERE `id_member` = 1 AND `id_member_type` = 14; -- EDC EDAM Affiliate
# Misc
UPDATE `mn_Member` SET `id_misc_type` = 1 WHERE `id_member` = 2; -- Alternate Association Member
UPDATE `mn_Member` SET `id_misc_type` = 2 WHERE `id_member` = 1 AND `id_member_type` = 7; -- Staff
UPDATE `mn_Member` SET `id_misc_type` = 3, `send_focus_newsletter` = 'y' WHERE `id_member` = 7; -- Non-Member
UPDATE `mn_Member` SET `id_misc_type` = 3 WHERE `id_member` IN (3, 4, 8); -- Non-Member
UPDATE `mn_Member` SET `id_misc_type` = 3, `is_support_account` = 'y' WHERE `id_member` = 9; -- Support Account


#-----------------------------------------------------------------------------------------------------------------------
# Statuses map
#-----------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `mn_DMemberStatus2Type` (
  `type` enum('mncar','mncar_l','misc') NOT NULL default 'mncar',
  `id_type` int(11) NOT NULL default '0',
  `id_status` int(11) NOT NULL default '0',
  PRIMARY KEY  (`type`,`id_type`,`id_status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

TRUNCATE `mn_DMemberStatus2Type`;
-- MNCAR
INSERT INTO `mn_DMemberStatus2Type` (`type`, `id_type`, `id_status`) 
SELECT 'mncar', `t`.`id`, `s`.`id`
FROM `mn_DMncarMemberType` AS `t`
JOIN  `mn_DMemberStatus` AS `s` ON `s`.`id` IN (1, 2, 3, 4, 5)
WHERE `t`.`id` IN (1, 2, 3);
-- MNCAR
INSERT INTO `mn_DMemberStatus2Type` (`type`, `id_type`, `id_status`) 
SELECT 'mncar', `t`.`id`, `s`.`id`
FROM `mn_DMncarMemberType` AS `t`
JOIN  `mn_DMemberStatus` AS `s` ON `s`.`id` IN (1, 2)
WHERE `t`.`id` IN (4, 5, 6, 7);

-- MNCAR-L
INSERT INTO `mn_DMemberStatus2Type` (`type`, `id_type`, `id_status`) 
SELECT 'mncar_l', `t`.`id`, `s`.`id`
FROM `mn_DMncarMemberType` AS `t`
JOIN  `mn_DMemberStatus` AS `s` ON `s`.`id` IN (1, 2, 3)
WHERE `t`.`id` IN (1);

-- MNCAR-L
INSERT INTO `mn_DMemberStatus2Type` (`type`, `id_type`, `id_status`) 
SELECT 'mncar_l', `t`.`id`, `s`.`id`
FROM `mn_DMncarMemberType` AS `t`
JOIN `mn_DMemberStatus` AS `s` ON `s`.`id` IN (1, 2)
WHERE `t`.`id` IN (2, 3, 4);

-- MISC
INSERT INTO `mn_DMemberStatus2Type` (`type`, `id_type`, `id_status`) 
SELECT 'misc', `t`.`id`, `s`.`id`
FROM `mn_DMncarMemberType` AS `t`
JOIN  `mn_DMemberStatus` AS `s` ON `s`.`id` IN (1, 2)
WHERE `t`.`id` IN (1, 2, 3, 4);
#-----------------------------------------------------------------------------------------------------------------------

# Status migration
UPDATE `mn_Member` AS `m` 
SET `id_mncar_status` = IFNULL((SELECT `s2t`.`id_status` FROM `mn_DMemberStatus2Type` AS `s2t` WHERE 1 AND `s2t`.`type` = 'mncar' AND `s2t`.`id_type` = `m`.`id_mncar_type` AND `s2t`.`id_status` = `m`.`id_status`), 2)
WHERE `id_mncar_type` > 0;

UPDATE `mn_Member` AS `m` 
SET `id_mncar_l_status` = IFNULL((SELECT `s2t`.`id_status` FROM `mn_DMemberStatus2Type` AS `s2t` WHERE 1 AND `s2t`.`type` = 'mncar_l' AND `s2t`.`id_type` = `m`.`id_mncar_l_type` AND `s2t`.`id_status` = `m`.`id_status`), 2)
WHERE `id_mncar_l_type` > 0;

UPDATE `mn_Member` AS `m` 
SET `id_misc_status` = IFNULL((SELECT `s2t`.`id_status` FROM `mn_DMemberStatus2Type` AS `s2t` WHERE 1 AND `s2t`.`type` = 'misc' AND `s2t`.`id_type` = `m`.`id_misc_type` AND `s2t`.`id_status` = `m`.`id_status`), 2)
WHERE `id_misc_type` > 0;

# other
UPDATE `mn_Member` 
SET `send_focus_newsletter` = 'y' 
WHERE 1
    AND (
        (`id_mncar_type` IN (1, 2, 3, 4, 7) AND `id_mncar_status` IN (1, 3))
        OR  (`id_mncar_l_type` IN (1, 2, 3, 4) AND `id_mncar_l_status` IN (1, 3))
        OR  (`id_misc_type` IN (2) AND `id_misc_status` IN (1, 3))
    );


ALTER TABLE `mn_DuesReportItems` CHANGE `member_type_id` `id_mncar_type` INT NULL DEFAULT '0';
ALTER TABLE `mn_DuesReportItems` ADD `id_mncar_l_type` INT NULL AFTER `id_mncar_type` ;
UPDATE `mn_DuesReportItems` SET `id_mncar_l_type` = `id_mncar_type`, `id_mncar_type` = NULL WHERE `id_mncar_type` IN ( 10, 12 );

# Task 11662#c16
# Please set Receive Focus to No for any Misc./Non-Member accounts that have Support account set to No.
#
UPDATE `mn_Member` SET `send_focus_newsletter` = 'n' WHERE `id_misc_type` IN (3) AND `is_support_account` = 'n';

#-----------------------------------------------------------------------------------------------------------------------
# Remove Forum
DELETE FROM `mn_zz_DocItem` WHERE `type` LIKE '%Forum%';
DELETE FROM `mn_zz_DocItemType` WHERE `name` LIKE '%Forum%';
#-----------------------------------------------------------------------------------------------------------------------

# Task 11566
# Awards Committee = Yes to MNCAR-L\Broker 
/*
UPDATE `mn_Member` AS `m`
JOIN `mn_Member2Volunteer` AS `m2v`
ON `m2v`.`id_parent` = `m`.`id`
AND `m2v`.`id_volunteer` = 5
SET `m`.`id_mncar_l_type` = 1,
    `m`.`id_mncar_l_status` = `m`.`id_mncar_status`,
    `m`.`id_mncar_type` = 0,
    `m`.`id_mncar_status` = 0;
 */
 
#ALTER TABLE `mn_Member`
#  DROP `id_member`,
#  DROP `id_member_type`,
#  DROP `id_status`;

# Task 11675	
UPDATE `mn_Member` 
SET `id_misc_type` = 4
WHERE 1
AND `id_misc_type` = 3 
AND `id_misc_status` = 1
AND is_support_account = 'y'

# Task 11676
UPDATE `mn_Member` 	
SET `allow_mcpe_access` = 'y', 
    `id_mcpe_access_type` = 4
WHERE 1
AND `id_misc_type` = 4 
AND `id_misc_status` = 1;

 
#####################################################################################
# Cms Changes
#--------------------------------------------------------------------------------------
# 1. Перемістити сторінку MNCAR Association > MNAR/NAR Membership Benefits в гілку account
#