CREATE TABLE `listing`
(
 `listing` int(11) NOT NULL AUTO_INCREMENT,
 `org_client` int(11) DEFAULT NULL,
 `org` int(11) DEFAULT NULL,
 `headline` text,
 `narrative` text,
 `content` text,
 `start` date DEFAULT NULL,
 `end` date DEFAULT NULL,
 `permalink` varchar(100) DEFAULT NULL,
 `hide` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`listing`),
 KEY `org_client` (`org_client`),
 KEY `org` (`org`)
) ENGINE=InnoDB;

ALTER TABLE `listing`
 ADD FOREIGN KEY (`org_client`) REFERENCES `org_client` (`org_client`),
 ADD FOREIGN KEY (`org`) REFERENCES `org` (`org`);

CREATE TABLE `listing_cat`
(
 `listing_cat` int(11) NOT NULL AUTO_INCREMENT,
 `org` int(11) NOT NULL,
 `name` varchar(50) NOT NULL,
 `description` text,
 `hide` int(1) NOT NULL,
 `photo` int(11) DEFAULT NULL,
 `photo_filename` varchar(100) DEFAULT NULL,
 `permalink` varchar(100) DEFAULT NULL,
 `sort` decimal(12,6) DEFAULT '0.000000',
 PRIMARY KEY (`listing_cat`),
 KEY `org` (`org`)
) ENGINE=InnoDB;

ALTER TABLE `listing_cat`
 ADD FOREIGN KEY (`org`) REFERENCES `org` (`org`);

CREATE TABLE `listing_listing_cat`
(
 `listing` int(11) NOT NULL,
 `listing_cat` int(11) NOT NULL,
 PRIMARY KEY (`listing`,`listing_cat`),
 KEY `listing_cat` (`listing_cat`)
) ENGINE=InnoDB;

ALTER TABLE `listing_listing_cat`
 ADD FOREIGN KEY (`listing`) REFERENCES `listing` (`listing`),
 ADD FOREIGN KEY (`listing_cat`) REFERENCES `listing_cat` (`listing_cat`);

CREATE TABLE `listing_media`
(
 `listing_media` int(11) NOT NULL AUTO_INCREMENT,
 `listing` int(11) NOT NULL,
 `photo` int(11) DEFAULT NULL,
 `photo_filename` varchar(100) DEFAULT NULL,
 `embed` text,
 `sort` decimal(12,6) NOT NULL DEFAULT '0.000000',
 PRIMARY KEY (`listing_media`),
 KEY `listing` (`listing`)
) ENGINE=InnoDB;

ALTER TABLE `listing_media`
 ADD FOREIGN KEY (`listing`) REFERENCES `listing` (`listing`);

# update 01/09/12 KS via BEM

ALTER TABLE `listing_media` ADD `list_photo_filename` VARCHAR(100) DEFAULT NULL AFTER `photo_filename`;
ALTER TABLE `listing_cat` ADD `content` TEXT DEFAULT NULL AFTER `description`;

# update 01/10/12 SLOW via BEM

ALTER TABLE `listing` ADD `priority` TINYINT NOT NULL DEFAULT 99;

# update 02/15/12 KS via BEM

CREATE TABLE `sevence`.`auto_trait` (
`auto_trait` INT( 11 ) NOT NULL AUTO_INCREMENT,
`default_org` INT( 11 ) NOT NULL ,
`yin` VARCHAR( 20 ) NULL DEFAULT NULL ,
`yang` VARCHAR( 20 ) NULL DEFAULT NULL ,
PRIMARY KEY ( `auto_trait` ) ,
INDEX ( `default_org` )
) ENGINE = InnoDB;

ALTER TABLE `auto_trait` ADD `trait` TINYINT( 1 ) NULL DEFAULT NULL AFTER `default_org`;

INSERT INTO auto_trait (default_org, trait, yin, yang) VALUES (3, 0, 'Wellness', 'Luxury');
INSERT INTO auto_trait (default_org, trait, yin, yang) VALUES (3, 1, 'Playful', 'Reserved');
INSERT INTO auto_trait (default_org, trait, yin, yang) VALUES (3, 2, 'Urban', 'Rustic');
INSERT INTO auto_trait (default_org, trait, yin, yang) VALUES (3, 3, 'Low discount', 'High discount');

CREATE TABLE `sevence`.`auto_question` (
`auto_question` INT( 11 ) NOT NULL AUTO_INCREMENT,
`auto_trait` INT( 11 ) NOT NULL ,
`question` TEXT NOT NULL ,
`yin` VARCHAR( 50 ) NOT NULL ,
`yang` VARCHAR( 50 ) NOT NULL DEFAULT '',
PRIMARY KEY ( `auto_question` ) ,
INDEX ( `auto_trait` )
) ENGINE = InnoDB;

INSERT INTO `auto_question` (`auto_question`, `auto_trait`, `question`, `yin`, `yang`) VALUES
(2, 1, 'Would you describe your spa as...', 'Communal', 'Exclusive'),
(3, 1, 'What is your service mission?', 'Wellness oriented/whole body', 'Relaxation/pampering'),
(4, 1, 'What is your overall focus for treatment responses?', 'Balance enhancing', 'Relaxation'),
(5, 1, 'How would you classify the style or delivery technique of your services?', 'Comforting', 'Professional'),
(6, 1, "What is your spa�s mission?", 'Nurture', 'Indulge');

INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (4, 'We offer discounts...', 'Occasionally', 'Often');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (4, 'We provide service upgrade incentives...', 'Rarely', 'Often');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (4, 'We create special packages mostly through...', 'New service combinations', 'Discounting existing package prices');

INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (1, 'Is your spa driven by...', 'Maintenance', 'Indulgence');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (1, 'Are you focused on providing...', 'Lifestyle', 'Luxury');

INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (2, 'What is the energy level of your spa?', 'Bustling', 'Quiet');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (2, 'Is your brand emphasis...', 'Energizing', 'Relaxing');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (2, 'Is your creative spa atmosphere...', 'Artsy', 'Contemporary');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (2, 'You consider your spa to be...', 'Whimsical', 'Understated');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (2, "How would you categorize your spa's communication style?", 'Artistic / hip', 'Urban / professional');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (2, 'Are your clients mostly...', '18-35', '35+');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (2, 'Is your service menu...', 'Experimental / cutting edge', 'Classic sophistication');

INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (3, 'Is the atmosphere of your spa...', 'Refreshing', 'Enveloping');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (3, 'Is the focus of the lounge areas...', 'Meditative', 'Gathering focused');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (3, 'Where are you located?', 'Urban dwelling', 'Spacious surroundings');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (3, 'Is the ambiance of your facility...', 'Polished', 'Artsy');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (3, 'Is your spa atmosphere...', 'Professional escape', 'Gathering hub');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (3, 'Is your lounge area...', 'Serene', 'Familial');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (3, 'Is your color scheme...', 'Soothing', 'Energizing');

CREATE TABLE `sevence`.`org_auto` (
`org_auto` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`org` INT( 11 ) NOT NULL ,
`yinyang` TINYINT( 1 ) NOT NULL ,
INDEX ( `org` )
) ENGINE = InnoDB;

ALTER TABLE `org_auto` ADD `trait0` TINYINT( 1 ) NULL DEFAULT NULL ,
ADD `trait1` TINYINT( 1 ) NULL DEFAULT NULL ,
ADD `trait2` TINYINT( 1 ) NULL DEFAULT NULL ,
ADD `trait3` TINYINT( 1 ) NULL DEFAULT NULL ,
ADD `trait4` TINYINT( 1 ) NULL DEFAULT NULL;

CREATE TABLE `sevence`.`org_question` (
`org_question` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`org` INT( 11 ) NOT NULL ,
`auto_question` INT( 11 ) NOT NULL,
`yinyang` TINYINT NOT NULL
) ENGINE = InnoDB;

ALTER TABLE `sevence`.`org_question` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `org_question` , `org` , `auto_question` );
ALTER TABLE `org_question` DROP `org_question`;
ALTER TABLE `org_auto` DROP INDEX `org`;
ALTER TABLE `org_auto` DROP `org_auto` ;
ALTER TABLE `org_auto` ADD PRIMARY KEY ( `org` ) ;

INSERT INTO auto_trait (default_org, trait, yin, yang) VALUES (7193, 0, 'Trendy', 'Traditional');
INSERT INTO auto_trait (default_org, trait, yin, yang) VALUES (7193, 1, 'Playful', 'Reserved');
INSERT INTO auto_trait (default_org, trait, yin, yang) VALUES (7193, 2, 'Urban', 'Rustic');
INSERT INTO auto_trait (default_org, trait, yin, yang) VALUES (7193, 3, 'Low discount', 'High discount');

INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (5, 'Are your salon services...', 'Culture driven', 'Community driven');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (5, 'Does your salon/spa specialize in...', 'Event specific makeovers', 'Everyday glamour');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (5, 'Are your clients...', 'Lifestyle driven', 'Loyalty driven');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (5, 'Do your stylists use...', 'Celebrity stylists techniques', 'Continuing education for technique enhancement');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (5, 'Is your salon/spa...', 'Product centric', 'Service centric');

INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (6, 'What is the energy level of your salon/spa?', 'Bustling', 'Quiet');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (6, 'Is your brand emphasis...', 'Energizing', 'Relaxing');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (6, 'Is your creative atmosphere...', 'Artsy', 'Contemporary');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (6, 'You consider your salon/spa to be...', 'Whimsical', 'Understated');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (6, "How would you categorize your communication style?", 'Artistic / hip', 'Urban / professional');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (6, 'Are your clients mostly...', '18-35', '35+');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (6, 'Is your service menu...', 'Experimental / cutting edge', 'Classic sophistication');

INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (7, 'Is the atmosphere of your salon/spa...', 'Refreshing', 'Enveloping');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (7, 'Is the focus of the lounge areas...', 'Meditative', 'Gathering focused');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (7, 'Where are you located?', 'Urban dwelling', 'Spacious surroundings');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (7, 'Is the ambiance of your facility...', 'Polished', 'Artsy');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (7, 'Is your atmosphere...', 'Professional escape', 'Gathering hub');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (7, 'Is your color scheme...', 'Soothing', 'Energizing');

INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (8, 'We offer discounts...', 'Occasionally', 'Often');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (8, 'We provide service upgrade incentives...', 'Rarely', 'Often');
INSERT INTO auto_question (auto_trait, question, yin, yang) VALUES (8, 'We create special packages mostly through...', 'New service combinations', 'Discounting existing package prices');

# update 3/6/12 WFB

ALTER TABLE org_type ADD COLUMN photo_org INT AFTER default_org;
ALTER TABLE `photo_group` ADD `photo_org` INT NULL DEFAULT NULL AFTER `photo_group` ,
ADD INDEX ( `photo_org` );
UPDATE photo_group SET photo_org = brand_org WHERE photo_org IS NULL;
ALTER TABLE `photo_group` ADD FOREIGN KEY ( `photo_org` ) REFERENCES `sevence`.`org` (
`org`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

# update 3/13/12 SLOW via BEM

INSERT INTO part (org, part_id, content) VALUES (3, 'reward_subscribe', "<p>Subscribing to our newsletter also enrolls you in our rewards program.</p>");
INSERT INTO part (org, part_id, content) VALUES (3, 'reward_share', "<p>Sharing is a great way to earn points in our rewards program.</p>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'reward_subscribe', "<p>Subscribing to our newsletter also enrolls you in our rewards program.</p>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'reward_share', "<p>Sharing is a great way to earn points in our rewards program.</p>");
INSERT INTO part (org, part_id, content) VALUES (967, 'reward_subscribe', "<p>Subscribing to our newsletter also enrolls you in our rewards program.</p>");
INSERT INTO part (org, part_id, content) VALUES (967, 'reward_share', "<p>Sharing is a great way to earn points in our rewards program.</p>");

ALTER TABLE client ADD COLUMN password_pending varchar(41);

INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (3, 'referral', 'Referrals', 'referral', 0, 90, 700000, 1);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (967, 'referral', 'Referrals', 'referral', 0, 90, 700000, 1);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (2850, 'referral', 'Referrals', 'referral', 0, 90, 700000, 1);
ALTER TABLE reward_point CHANGE buy_gc buy_gc INT, CHANGE buy_service buy_service INT, CHANGE buy_deal buy_deal INT, CHANGE referral_gc referral_gc INT, CHANGE referral_service referral_service INT, CHANGE referral_deal referral_deal INT, CHANGE email_signup email_signup INT, CHANGE share_friend share_friend INT, CHANGE share_signup share_signup INT;

UPDATE reward_point SET buy_gc=1, buy_service=1, referral_gc=1, referral_service=1, email_signup=100, share_friend=1, share_signup=100 where org in (3, 2850);
INSERT INTO reward_point (org, buy_gc, buy_service, referral_gc, referral_service, email_signup, share_friend, share_signup) VALUES (967, 1, 1, 1, 1, 100, 1, 100);
# update 4/8/2012 SLOW via BEM

ALTER TABLE listing ADD rank VARCHAR(4) DEFAULT NULL;
#5.11.2012 JBB via BEM Mantis0000519: Change how we order listings

CREATE TABLE `sevence`.`auto_time` (
`auto_time` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`start` DATE NOT NULL ,
`end` INT NULL DEFAULT NULL ,
`name` VARCHAR( 50 ) NOT NULL ,
`type` TINYINT( 1 ) NOT NULL
) ENGINE = InnoDB;
ALTER TABLE `auto_time` ADD `default_org` INT( 11 ) NOT NULL AFTER `auto_time`;
ALTER TABLE `auto_time` ADD `display` TINYINT( 1 ) NOT NULL;
ALTER TABLE `auto_time` CHANGE `end` `end` DATE NULL DEFAULT NULL;
INSERT INTO auto_time (default_org, start, end, name, type, display) VALUES (3, "2012-02-14", "2012-02-14", "Valentine's Day", 1, 1);
INSERT INTO auto_time (default_org, start, end, name, type, display) VALUES (3, "2012-05-13", "2012-05-13", "Mother's Day", 1, 1);
INSERT INTO auto_time (default_org, start, end, name, type, display) VALUES (3, "2012-06-17", "2012-06-17", "Father's Day", 1, 1);
INSERT INTO auto_time (default_org, start, end, name, type, display) VALUES (8090, "2012-06-17", "2012-06-17", "Father's Day", 1, 1);
# 5.31.2012 WFB

ALTER TABLE `email_marketing` ADD `client_or_org` TINYINT( 11 ) NOT NULL DEFAULT '0' AFTER `email_design`;
#06-06-12 WFB via BEM 0000530: Roboboom

INSERT INTO part (org, part_id, content) VALUES (3, 'offer_subscribe', '<h1>Claim Your Offer</h1><p>Enter your email address to receive an email with a link to claim your offer. We\'ll only use your information with your permission, and take your privacy seriously. <br></p>');
INSERT INTO part (org, part_id, content) VALUES (3, 'offer_sent', '<h1>Thanks! <br></h1><p>Your offer has been sent to you. Click the link in the email to claim and print your offer. <i>Note: To ensure proper delivery of our<br>emails, take a moment now and add us, {email_address}, to your address book, trusted<br>sender list, or company white list.</i></p>');
INSERT INTO part (org, part_id, content) VALUES (967, 'offer_subscribe', '<h1>Claim Your Offer</h1><p>Enter your email address to receive an email with a link to claim your offer. We\'ll only use your information with your permission, and take your privacy seriously. <br></p>');
INSERT INTO part (org, part_id, content) VALUES (967, 'offer_sent', '<h1>Thanks! <br></h1><p>Your offer has been sent to you. Click the link in the email to claim and print your offer. <i>Note: To ensure proper delivery of our<br>emails, take a moment now and add us, {email_address}, to your address book, trusted<br>sender list, or company white list.</i></p>');
INSERT INTO part (org, part_id, content) VALUES (2850, 'offer_subscribe', '<h1>Claim Your Offer</h1><p>Enter your email address to receive an email with a link to claim your offer. We\'ll only use your information with your permission, and take your privacy seriously. <br></p>');
INSERT INTO part (org, part_id, content) VALUES (2850, 'offer_sent', '<h1>Thanks! <br></h1><p>Your offer has been sent to you. Click the link in the email to claim and print your offer. <i>Note: To ensure proper delivery of our<br>emails, take a moment now and add us, {email_address}, to your address book, trusted<br>sender list, or company white list.</i></p>');
INSERT INTO email_marketing (default_email,type,id,org,active,name,subject,headline,narrative,body,send_on,email_cycle,queued_on,hide,show_event,show_article,email_design,client_type,marketing_list,marketing_list_opt,org_type,org_restrict,editor,photo,photo_filename,offer,event,campaign_type,push_type,edit_date,edit_prof,permalink,show_reward) 
	VALUES (null,1,'send_offer',3,1,'send offer','Here\'s your offer from {short_name}','Here\'s your offer','From: {short_name}\r\n\r\nValue: {offer_value}\r\n\r\nOffer: {offer}\r\n\r\nOffer link: {offer_link}','Here&#039;s your offer!<br />\r\n<br />\r\nValue: {offer_value}<br />\r\n<br />\r\nOffer: {offer}<br />\r\n<br />\r\nOffer link: {offer_link}',NULL,0,NULL,0,1,1,5,'0','0',0,'ALL','NONE',0,0,'',0,0,0,0,'2012-05-03 14:50:39',2130,'heres-your-offer',0);
INSERT INTO email_marketing (default_email,type,id,org,active,name,subject,headline,narrative,body,send_on,email_cycle,queued_on,hide,show_event,show_article,email_design,client_type,marketing_list,marketing_list_opt,org_type,org_restrict,editor,photo,photo_filename,offer,event,campaign_type,push_type,edit_date,edit_prof,permalink,show_reward) 
	VALUES (null,1,'send_offer',967,1,'send offer','Here\'s your offer from {short_name}','Here\'s your offer','From: {short_name}\r\n\r\nValue: {offer_value}\r\n\r\nOffer: {offer}\r\n\r\nOffer link: {offer_link}','Here&#039;s your offer!<br />\r\n<br />\r\nValue: {offer_value}<br />\r\n<br />\r\nOffer: {offer}<br />\r\n<br />\r\nOffer link: {offer_link}',NULL,0,NULL,0,1,1,5,'0','0',0,'ALL','NONE',0,0,'',0,0,0,0,'2012-05-03 14:50:39',2130,'heres-your-offer',0);
INSERT INTO email_marketing (default_email,type,id,org,active,name,subject,headline,narrative,body,send_on,email_cycle,queued_on,hide,show_event,show_article,email_design,client_type,marketing_list,marketing_list_opt,org_type,org_restrict,editor,photo,photo_filename,offer,event,campaign_type,push_type,edit_date,edit_prof,permalink,show_reward) 
	VALUES (null,1,'send_offer',2850,1,'send offer','Here\'s your offer from {short_name}','Here\'s your offer','From: {short_name}\r\n\r\nValue: {offer_value}\r\n\r\nOffer: {offer}\r\n\r\nOffer link: {offer_link}','Here&#039;s your offer!<br />\r\n<br />\r\nValue: {offer_value}<br />\r\n<br />\r\nOffer: {offer}<br />\r\n<br />\r\nOffer link: {offer_link}',NULL,0,NULL,0,1,1,5,'0','0',0,'ALL','NONE',0,0,'',0,0,0,0,'2012-05-03 14:50:39',2130,'heres-your-offer',0);
INSERT INTO email_marketing (default_email,type,id,org,active,name,subject,headline,narrative,body,send_on,email_cycle,queued_on,hide,show_event,show_article,email_design,client_type,marketing_list,marketing_list_opt,org_type,org_restrict,editor,photo,photo_filename,offer,event,campaign_type,push_type,edit_date,edit_prof,permalink,show_reward) 
	VALUES (null,1,'send_offer',7703,1,'send offer','Here\'s your offer from {short_name}','Here\'s your offer','From: {short_name}\r\n\r\nValue: {offer_value}\r\n\r\nOffer: {offer}\r\n\r\nOffer link: {offer_link}','Here&#039;s your offer!<br />\r\n<br />\r\nValue: {offer_value}<br />\r\n<br />\r\nOffer: {offer}<br />\r\n<br />\r\nOffer link: {offer_link}',NULL,0,NULL,0,1,1,5,'0','0',0,'ALL','NONE',0,0,'',0,0,0,0,'2012-05-03 14:50:39',2130,'heres-your-offer',0);
INSERT INTO email_marketing (default_email,type,id,org,active,name,subject,headline,narrative,body,send_on,email_cycle,queued_on,hide,show_event,show_article,email_design,client_type,marketing_list,marketing_list_opt,org_type,org_restrict,editor,photo,photo_filename,offer,event,campaign_type,push_type,edit_date,edit_prof,permalink,show_reward) 
	VALUES (null,1,'send_offer',8090,1,'send offer','Here\'s your offer from {short_name}','Here\'s your offer','From: {short_name}\r\n\r\nValue: {offer_value}\r\n\r\nOffer: {offer}\r\n\r\nOffer link: {offer_link}','Here&#039;s your offer!<br />\r\n<br />\r\nValue: {offer_value}<br />\r\n<br />\r\nOffer: {offer}<br />\r\n<br />\r\nOffer link: {offer_link}',NULL,0,NULL,0,1,1,5,'0','0',0,'ALL','NONE',0,0,'',0,0,0,0,'2012-05-03 14:50:39',2130,'heres-your-offer',0);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (3, 'ondemand', 'Instant', 'ondemand', 30, 60, 800000, 1);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (967, 'ondemand', 'Instant', 'ondemand', 30, 60, 800000, 1);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (2850, 'ondemand', 'Instant', 'ondemand', 30, 60, 800000, 1);
#06-06-12 DWS via BEM Mantis0000517: On Demand Offers

ALTER TABLE  `org` ADD  `authnet_sim` TINYINT( 1 ) NOT NULL DEFAULT  '0' AFTER  `authnet_md5_hash`;
#6.12.2012 BEM 0000522: SIM integration

ALTER TABLE `email_marketing` CHANGE `org_type` `org_type` VARCHAR( 100 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'ALL';
#6.15.2012 WFB CMS service option support

ALTER TABLE `book` 
 ADD `book_date` DATETIME NOT NULL AFTER `entered_date`,
 ADD `package_service_option` INT(11) NULL DEFAULT NULL AFTER `book_date`,
 ADD `note` TEXT NULL DEFAULT NULL AFTER `package_service_option`,
 ADD INDEX (`package_service_option`);

ALTER TABLE `book` ADD FOREIGN KEY (`package_service_option`) REFERENCES `service_option` (`service_option`);

ALTER TABLE `queue_social` 
 ADD `book` INT(11) NULL DEFAULT NULL AFTER `event`,
 ADD INDEX (`book`);

ALTER TABLE `queue_social` ADD FOREIGN KEY (`book`) REFERENCES `book` (`book`);

ALTER TABLE `book_slot` 
 ADD `partner` INT(11) NULL DEFAULT NULL,
 ADD INDEX (`partner`);

ALTER TABLE `book_slot` ADD FOREIGN KEY (`partner`) REFERENCES `book_slot` (`book_slot`);

ALTER TABLE `reward_point` 
 ADD `buy_booking` INT(11) NULL DEFAULT NULL AFTER `buy_deal`,
 ADD `referral_booking` INT(11) NULL DEFAULT NULL AFTER `referral_deal`;

UPDATE `reward_point` SET `buy_booking` = 1, `referral_booking` = 1 WHERE `org` = 3;

ALTER TABLE `book` 
 ADD `org_client` INT(11) NULL DEFAULT NULL AFTER `org_tran`,
 ADD `check_in` DATETIME NULL DEFAULT NULL AFTER `book_date`,
 ADD `show_cc` DATE NULL DEFAULT NULL AFTER `check_in`,
 ADD `partner` INT(11) NULL DEFAULT NULL AFTER `package_service_option`,
 ADD INDEX (`org_client`),
 ADD INDEX (`partner`);

ALTER TABLE `book` 
 ADD FOREIGN KEY (`partner`) REFERENCES `book` (`book`),
 ADD FOREIGN KEY (`org_client`) REFERENCES `org_client` (`org_client`);

UPDATE `book` JOIN `book_slot` ON `book`.`book` = `book_slot`.`book` 
 SET `book`.`org_client` = `book_slot`.`org_client`,
     `book`.`check_in` = `book_slot`.`check_in`, 
     `book`.`show_cc` = `book_slot`.`show_cc`;

UPDATE `book` SET `show_cc` = NULL WHERE `show_cc` = '0000-00-00';

ALTER TABLE `book_slot` 
 DROP COLUMN org_client,
 DROP COLUMN show_cc,
 DROP COLUMN check_in;

UPDATE `book` JOIN `book_slot` ON `book`.`book` = `book_slot`.`book` 
 SET `book`.`book_date` = `book_slot`.`start` 
 WHERE `book`.`book_date` = '0000-00-00';

ALTER TABLE `org` 
 ADD `event_opt_in` TINYINT(1) NOT NULL DEFAULT 0 AFTER `email_marketing_opt_in`;

UPDATE `org` SET `event_opt_in` = `email_marketing_opt_in`;

ALTER TABLE `org_client` ADD `booking_reminder` TINYINT(3) DEFAULT 2;

CREATE TABLE `org_domain` (
`org_domain` int(11) NOT NULL AUTO_INCREMENT,
`org` int(11) NOT NULL,
`url` varchar(255) NOT NULL,
`main` tinyint(1) NOT NULL DEFAULT '0',
`pending` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`org_domain`),
UNIQUE KEY `unique_url` (`url`),
KEY `org` (`org`),
CONSTRAINT `org` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
);
#07.05.12 BEM Booking, DNS generation, etc. 

ALTER TABLE auto_time ADD COLUMN `country` varchar(50) NOT NULL;
UPDATE auto_time SET country='United States';
#7.24.2012 BEM 0000634: Time Periods

CREATE TABLE `sevence`.`auto_attr` (
`auto_attr` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`default_org` INT( 11 ) NOT NULL ,
`attr` TINYINT( 1 ) NULL ,
`name` VARCHAR( 50 ) NOT NULL ,
INDEX ( `default_org` )
) ENGINE = InnoDB;

INSERT INTO auto_attr (default_org, attr, name) VALUES (3, 0, "Couples services");
INSERT INTO auto_attr (default_org, attr, name) VALUES (3, 1, "Market to men");
INSERT INTO auto_attr (default_org, attr, name) VALUES (3, 2, "Sell retail products");
INSERT INTO auto_attr (default_org, attr, name) VALUES (3, 3, "Spa parties");

ALTER TABLE `org_auto` ADD `attr0` TINYINT( 1 ) NULL ,
ADD `attr1` TINYINT( 1 ) NULL ,
ADD `attr2` TINYINT( 1 ) NULL ,
ADD `attr3` TINYINT( 1 ) NULL ,
ADD `attr4` TINYINT( 1 ) NULL ,
ADD `attr5` TINYINT( 1 ) NULL ,
ADD `attr6` TINYINT( 1 ) NULL ,
ADD `attr7` TINYINT( 1 ) NULL ,
ADD `attr8` TINYINT( 1 ) NULL ,
ADD `attr9` TINYINT( 1 ) NULL ;

CREATE TABLE `sevence`.`auto_offering` (
`auto_offering` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`default_org` INT( 11 ) NOT NULL ,
`offering` TINYINT( 1 ) NULL ,
`name` VARCHAR( 50 ) NOT NULL ,
INDEX ( `default_org` )
) ENGINE = InnoDB;

INSERT INTO auto_offering (default_org, offering, name) VALUES (3, 0, "Massage");
INSERT INTO auto_offering (default_org, offering, name) VALUES (3, 1, "Body Treatments");
INSERT INTO auto_offering (default_org, offering, name) VALUES (3, 2, "Facials");
INSERT INTO auto_offering (default_org, offering, name) VALUES (3, 3, "Hair");
INSERT INTO auto_offering (default_org, offering, name) VALUES (3, 4, "Nails");
INSERT INTO auto_offering (default_org, offering, name) VALUES (3, 5, "Waxing");
INSERT INTO auto_offering (default_org, offering, name) VALUES (3, 6, "Tanning");
INSERT INTO auto_offering (default_org, offering, name) VALUES (3, 7, "Retail Products");

ALTER TABLE `org_auto` ADD `offering0` TINYINT( 1 ) NULL ,
ADD `offering1` TINYINT( 1 ) NULL ,
ADD `offering2` TINYINT( 1 ) NULL ,
ADD `offering3` TINYINT( 1 ) NULL ,
ADD `offering4` TINYINT( 1 ) NULL ,
ADD `offering5` TINYINT( 1 ) NULL ,
ADD `offering6` TINYINT( 1 ) NULL ,
ADD `offering7` TINYINT( 1 ) NULL ,
ADD `offering8` TINYINT( 1 ) NULL ,
ADD `offering9` TINYINT( 1 ) NULL ;

CREATE TABLE `article_auto` (
  `article` int(11) NOT NULL AUTO_INCREMENT,
  `trait0` tinyint(1) DEFAULT NULL,
  `trait1` tinyint(1) DEFAULT NULL,
  `trait2` tinyint(1) DEFAULT NULL,
  `trait3` tinyint(1) DEFAULT NULL,
  `attr0` tinyint(1) DEFAULT NULL,
  `attr1` tinyint(1) DEFAULT NULL,
  `attr2` tinyint(1) DEFAULT NULL,
  `attr3` tinyint(1) DEFAULT NULL,
  `attr4` tinyint(1) DEFAULT NULL,
  `attr5` tinyint(1) DEFAULT NULL,
  `attr6` tinyint(1) DEFAULT NULL,
  `attr7` tinyint(1) DEFAULT NULL,
  `attr8` tinyint(1) DEFAULT NULL,
  `attr9` tinyint(1) DEFAULT NULL,
  `offering0` tinyint(1) DEFAULT NULL,
  `offering1` tinyint(1) DEFAULT NULL,
  `offering2` tinyint(1) DEFAULT NULL,
  `offering3` tinyint(1) DEFAULT NULL,
  `offering4` tinyint(1) DEFAULT NULL,
  `offering5` tinyint(1) DEFAULT NULL,
  `offering6` tinyint(1) DEFAULT NULL,
  `offering7` tinyint(1) DEFAULT NULL,
  `offering8` tinyint(1) DEFAULT NULL,
  `offering9` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`article`)
) ENGINE=InnoDB;

ALTER TABLE `article_auto` ADD `rank` TINYINT( 1 ) NOT NULL AFTER `article` ;
ALTER TABLE `article_auto` ADD `current_event` TINYINT( 1 ) NOT NULL AFTER `rank` ;
ALTER TABLE `org_auto` ADD `current_event` TINYINT( 1 ) NOT NULL AFTER `org` ;
ALTER TABLE `article_auto` ADD `expiration` DATE NULL AFTER `current_event` ,
ADD `start` VARCHAR( 5 ) NULL DEFAULT '' AFTER `expiration` ,
ADD `end` VARCHAR( 5 ) NULL DEFAULT '' AFTER `start` ;

ALTER TABLE `sevence`.`queue_social` ADD COLUMN `article` INT(11) NULL DEFAULT NULL  AFTER `book` ,
  ADD CONSTRAINT `article`
  FOREIGN KEY (`article` )
  REFERENCES `sevence`.`article` (`article` )
, ADD INDEX `article` (`article` ASC) ;
#7.31.12 Queue Social interface for articles

CREATE TABLE `sevence`.`article_auto_headline` (
`article_auto_headline` INT( 11 ) NOT NULL ,
`article` INT( 11 ) NOT NULL ,
`headline` TEXT NOT NULL ,
`trait0` TINYINT( 1 ) NULL DEFAULT NULL ,
`trait1` TINYINT( 1 ) NULL DEFAULT NULL ,
`trait2` TINYINT( 1 ) NULL DEFAULT NULL ,
`trait3` TINYINT( 1 ) NULL DEFAULT NULL ,
PRIMARY KEY ( `article_auto_headline` ) ,
INDEX ( `article` )
) ENGINE = InnoDB;

ALTER TABLE article_auto_headline CHANGE COLUMN article_auto_headline article_auto_headline INT(11) AUTO_INCREMENT;

ALTER TABLE `book` ADD `reminder_sent` TINYINT(1) NOT NULL DEFAULT 0;
#8.24.2012 JBB M753: Changes to queue book reminder


#Add org_parm rows for default orgs
INSERT into org_parm (org, type, parm, value) VALUES (3, 'Booking', 'booking_reminder', 'Reminder: your appointment is tomorrow at {booking_time}!');
INSERT into org_parm (org, type, parm, value) VALUES (7415, 'Booking', 'booking_reminder', 'Reminder: your appointment is tomorrow at {booking_time}!');
INSERT into org_parm (org, type, parm, value) VALUES (7193, 'Booking', 'booking_reminder', 'Reminder: your appointment is tomorrow at {booking_time}!');
INSERT into org_parm (org, type, parm, value) VALUES (7194, 'Booking', 'booking_reminder', 'Reminder: your appointment is tomorrow at {booking_time}!');
INSERT into org_parm (org, type, parm, value) VALUES (7195, 'Booking', 'booking_reminder', 'Reminder: your appointment is tomorrow at {booking_time}!');
INSERT into org_parm (org, type, parm, value) VALUES (7703, 'Booking', 'booking_reminder', 'Reminder: your appointment is tomorrow at {booking_time}!');
INSERT into org_parm (org, type, parm, value) VALUES (8090, 'Booking', 'booking_reminder', 'Reminder: your appointment is tomorrow at {booking_time}!');
#0000675: 7.30.2012 SLOW Changes to booking settings

#These statements were run on production on 8-28-12
CREATE TABLE `org_client_trigger` (
  `org_client` int(11) NOT NULL,
  `trigger_group` char(5) NOT NULL,
  `atom` int,
  `date_1` datetime DEFAULT NULL,
  `date_2` datetime DEFAULT NULL,
  PRIMARY KEY (`org_client`,`trigger_group`,`atom`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE email_marketing ADD COLUMN email_trigger char(2);

ALTER TABLE org_type ADD COLUMN remind_short int, ADD COLUMN remind_long int, ADD COLUMN purch int, ADD COLUMN purch_cutoff int;
UPDATE org_type SET purch = 351, purch_cutoff=364;

UPDATE email_marketing SET type=2, email_trigger='RR' WHERE id = 'reviewremind';

ALTER TABLE org_setting ADD COLUMN review_at tinyint NOT NULL DEFAULT 0 AFTER review_remind_client, ADD COLUMN review_alt varchar(100) AFTER review_at;
#6.19.12 DWS Mantis0000511 Follow up emails to clients who have not had any manual points added or points from booking

update org_type set remind_short=60, remind_long=120 where org_type=1;
update org_type set remind_short=60, remind_long=90 where org_type=2;
update org_type set remind_short=60, remind_long=90 where org_type=3;
update org_type set remind_short=60, remind_long=90 where org_type=4;
update org_type set remind_short=120, remind_long=180 where org_type=5;
update org_type set remind_short=90, remind_long=180 where org_type=6;
update org_type set remind_short=120, remind_long=180 where org_type=7;
update org_type set remind_short=60, remind_long=90 where org_type=8;
update org_type set remind_short=60, remind_long=90 where org_type=10;
update org_type set remind_short=60, remind_long=120 where org_type=11;
update org_type set remind_short=60, remind_long=90 where org_type=40;
update org_type set remind_short=90, remind_long=180 where org_type=41;
update org_type set remind_short=60, remind_long=90 where org_type=42;
update org_type set remind_short=60, remind_long=90 where org_type=44;
update org_type set remind_short=60, remind_long=90 where org_type=46;
update org_type set remind_short=60 where remind_short is NULL;
update org_type set remind_long=120 where remind_long is NULL;
#08.28.12 SLOW setting the org_type defaults. These refer to org_types on production so YMMV

ALTER TABLE `auto_time` ADD `article_count` TINYINT( 4 ) NULL DEFAULT 0 AFTER `display`;
#08.15.12 SG: Roboblog
#end 8-28-12 update SLOW

#These statements were run on production sometime before 9/14/12
CREATE TABLE `sevence`.`article_org` (
`article` INT( 11 ) NOT NULL ,
`org` INT( 11 ) NOT NULL ,
`date_published` DATE NOT NULL ,
PRIMARY KEY ( `article` , `org` )
) ENGINE = InnoDB;

alter table article_org change column article default_article int(11) not null;
alter table article_org add column article int(11) null default null after default_article;
alter table article_org add column temp_created_at timestamp null default null;
alter table article_org drop column date_published;

alter table article_org add column declined tinyint(1) not null default 0;

ALTER TABLE `org_status` ADD `status` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `name`;

UPDATE org_status SET status = -2 WHERE org_status BETWEEN -11 AND -1 AND org_status NOT IN (-6, -3);
UPDATE org_status SET status = -3 WHERE org_status IN (-101);
UPDATE org_status SET status = -1 WHERE org_status IN (-100);
UPDATE org_status SET status = 0 WHERE org_status IN (-6, -3);
UPDATE org_status SET status = 2 WHERE org_status > 9;
#End 9/14/12 update SLOW

##These updates were run on Production on 9-20-2012
ALTER TABLE `org` ADD `external_gc_url` VARCHAR( 80 ) NOT NULL AFTER `gift_legalese`;
#08.29.12 JBB M755: Need to support external gift certificates

ALTER TABLE article_auto ADD COLUMN headline TEXT AFTER article;
ALTER TABLE article_auto ADD COLUMN excerpt VARCHAR(255) NULL DEFAULT NULL AFTER headline;
ALTER TABLE article_auto ADD COLUMN narrative TEXT NOT NULL AFTER excerpt;
ALTER TABLE article_auto ADD COLUMN content TEXT NOT NULL AFTER narrative;
ALTER TABLE article_auto ADD COLUMN permalink VARCHAR(255) NOT NULL AFTER content;
ALTER TABLE article_auto ADD COLUMN org INT(11) NOT NULL AFTER article;
UPDATE article_auto JOIN article ON article_auto.article = article.article SET article_auto.org=article.org, article_auto.headline=article.headline, article_auto.excerpt=article.excerpt,
	article_auto.narrative=article.narrative, article_auto.content=article.content, article_auto.permalink=article.permalink;
ALTER TABLE article_auto CHANGE COLUMN article auto_article INT(11) AUTO_INCREMENT NOT NULL;
ALTER TABLE article_auto RENAME TO auto_article;
ALTER TABLE article_auto_headline CHANGE COLUMN article auto_article INT(11);

CREATE TABLE `auto_social_post` (
  `auto_social_post` int(11) NOT NULL AUTO_INCREMENT,
  `auto_article` int(11) NOT NULL,
  `type` tinyint(4) NOT NULL,
  `sequence` tinyint(4) NOT NULL,
  `headline` varchar(255) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`auto_social_post`)
) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=latin1;
INSERT INTO auto_social_post (auto_article, type, sequence) (SELECT auto_article, 1 AS type, 1 AS sequence FROM auto_article);
INSERT INTO auto_social_post (auto_article, type, sequence) (SELECT auto_article, 2 AS type, 1 AS sequence FROM auto_article);
INSERT INTO auto_social_post (auto_article, type, sequence) (SELECT auto_article, 1 AS type, 2 AS sequence FROM auto_article);
INSERT INTO auto_social_post (auto_article, type, sequence) (SELECT auto_article, 2 AS type, 2 AS sequence FROM auto_article);

insert into org_parm (org, type, parm, value) VALUES (7415, 'Feature', 'sms_marketing_confirm', 1);
insert into org_parm (org, type, parm, value) VALUES (7193, 'Feature', 'sms_marketing_confirm', 1);
insert into org_parm (org, type, parm, value) VALUES (7195, 'Feature', 'sms_marketing_confirm', 1);
insert into org_parm (org, type, parm, value) VALUES (7194, 'Feature', 'sms_marketing_confirm', 1);
insert into org_parm (org, type, parm, value) VALUES (8215, 'Feature', 'sms_marketing_confirm', 1);
insert into org_parm (org, type, parm, value) VALUES (2850, 'Feature', 'sms_marketing_confirm', 1);
insert into org_parm (org, type, parm, value) VALUES (7703, 'Feature', 'sms_marketing_confirm', 1);
insert into org_parm (org, type, parm, value) VALUES (7883, 'Feature', 'sms_marketing_confirm', 1);
insert into org_parm (org, type, parm, value) VALUES (8090, 'Feature', 'sms_marketing_confirm', 1);
insert into org_parm (org, type, parm, value) VALUES (3, 'Feature', 'sms_marketing_confirm', 1);
#END 9.20.12 Update

CREATE TABLE `sevence`.`postal_radius`
(
  `postal` VARCHAR( 20 ) NOT NULL ,
  `to_postal` VARCHAR( 20 ) NOT NULL ,
  `level` TINYINT( 4 ) NOT NULL DEFAULT 1 ,
  `distance` decimal(6,2) NOT NULL DEFAULT 0 ,
  PRIMARY KEY ( `postal`, `to_postal` ) ,
  INDEX ( `to_postal`, `postal` )
) ENGINE = InnoDB;

ALTER TABLE article_org
  ADD INDEX (org);

ALTER TABLE org_status
	ADD INDEX (hide),
	ADD INDEX (sort),
	ADD INDEX (status);

# 9/28/12 update BEM

ALTER TABLE `org` ADD `handheld` TINYINT( 1 ) NOT NULL DEFAULT 1 AFTER `service_info`; 
UPDATE org SET handheld = 0 WHERE service_level <= 3;

# 9/29/12 update WFB

#DWS Mantis 783
CREATE TABLE `page_facebook` (
  `org` int(11) NOT NULL,
  `page_id` varchar(50) NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

ALTER TABLE website ADD COLUMN facebook_css text AFTER website_css;
ALTER TABLE gift ADD COLUMN source tinyint NOT NULL default 0;
ALTER TABLE gift_p ADD COLUMN source tinyint NOT NULL default 0;
#Run on production 11.13.12 SLOW via BEM

CREATE TABLE `sevence`.`photo_auto` (
`photo` INT( 11 ) NOT NULL ,
`org` INT( 11 ) NOT NULL ,
  `trait0` tinyint(4) DEFAULT NULL,
  `trait1` tinyint(4) DEFAULT NULL,
  `trait2` tinyint(4) DEFAULT NULL,
  `trait3` tinyint(4) DEFAULT NULL,
  `attr0` tinyint(1) DEFAULT NULL,
  `attr1` tinyint(1) DEFAULT NULL,
  `attr2` tinyint(1) DEFAULT NULL,
  `attr3` tinyint(1) DEFAULT NULL,
  `attr4` tinyint(1) DEFAULT NULL,
  `attr5` tinyint(1) DEFAULT NULL,
  `attr6` tinyint(1) DEFAULT NULL,
  `attr7` tinyint(1) DEFAULT NULL,
  `attr8` tinyint(1) DEFAULT NULL,
  `attr9` tinyint(1) DEFAULT NULL,
  `offering0` tinyint(1) DEFAULT NULL,
  `offering1` tinyint(1) DEFAULT NULL,
  `offering2` tinyint(1) DEFAULT NULL,
  `offering3` tinyint(1) DEFAULT NULL,
  `offering4` tinyint(1) DEFAULT NULL,
  `offering5` tinyint(1) DEFAULT NULL,
  `offering6` tinyint(1) DEFAULT NULL,
  `offering7` tinyint(1) DEFAULT NULL,
  `offering8` tinyint(1) DEFAULT NULL,
  `offering9` tinyint(1) DEFAULT NULL,
  `created_at` datetime NOT NULL,
PRIMARY KEY ( `photo` , `org` )
) ENGINE = InnoDB;

insert into photo_auto (photo, org, created_at) (select photo, org, CONVERT_TZ(NOW(), "MST", "UTC") from photo where org is not null);
# Create empty photo_auto rows for all photos


# NOTE: Some of these may be reduntant, I am including all columns that have a model that could be a problem
ALTER TABLE article_auto_headline MODIFY COLUMN trait0 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE article_auto_headline MODIFY COLUMN trait1 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE article_auto_headline MODIFY COLUMN trait2 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE article_auto_headline MODIFY COLUMN trait3 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE auto_article MODIFY COLUMN rank tinyint(4) NULL DEFAULT NULL;
ALTER TABLE auto_article MODIFY COLUMN trait0 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE auto_article MODIFY COLUMN trait1 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE auto_article MODIFY COLUMN trait2 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE auto_article MODIFY COLUMN trait3 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE auto_attr MODIFY COLUMN attr tinyint(4) NOT NULL;
ALTER TABLE auto_offering MODIFY COLUMN offering tinyint(4) NOT NULL;
ALTER TABLE auto_social_post MODIFY COLUMN `type` tinyint(4) NOT NULL;
ALTER TABLE auto_social_post MODIFY COLUMN sequence tinyint(4) NOT NULL;
ALTER TABLE auto_time MODIFY COLUMN `type` tinyint(4) NOT NULL;
ALTER TABLE auto_time MODIFY COLUMN article_count tinyint(4) NULL DEFAULT 0;
ALTER TABLE auto_trait MODIFY COLUMN trait tinyint(4) NOT NULL;
ALTER TABLE org MODIFY COLUMN service_level tinyint(4) NOT NULL;
ALTER TABLE org_auto MODIFY COLUMN trait0 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN trait1 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN trait2 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN trait3 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN trait4 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE photo_auto MODIFY COLUMN trait0 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE photo_auto MODIFY COLUMN trait1 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE photo_auto MODIFY COLUMN trait2 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE photo_auto MODIFY COLUMN trait3 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE queue_social MODIFY COLUMN status tinyint(4) NOT NULL DEFAULT 0;
ALTER TABLE queue_social MODIFY COLUMN attempts tinyint(4) NOT NULL DEFAULT 0;
ALTER TABLE queue_social MODIFY COLUMN `type` tinyint(4) NOT NULL;
ALTER TABLE queue_social MODIFY COLUMN sequence tinyint(4) NULL DEFAULT NULL;
ALTER TABLE queue_social MODIFY COLUMN editor tinyint(4) NOT NULL DEFAULT 0;
ALTER TABLE queue_social MODIFY COLUMN marketing_list_opt tinyint(4) NOT NULL DEFAULT 0;
# Compaitibility with schema reads

ALTER TABLE org_auto MODIFY COLUMN offering0 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN offering1 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN offering2 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN offering3 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN offering4 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN offering5 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN offering6 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN offering7 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN offering8 tinyint(4) NULL DEFAULT NULL;
ALTER TABLE org_auto MODIFY COLUMN offering9 tinyint(4) NULL DEFAULT NULL;
# Offerings are not boolean

CREATE TABLE `auto_sub_offering` (
  `auto_sub_offering` int(11) NOT NULL AUTO_INCREMENT,
  `auto_offering` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`auto_sub_offering`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `auto_article_sub_offering` (
  `auto_article` int(11) NOT NULL,
  `auto_sub_offering` int(11) NOT NULL,
  PRIMARY KEY (`auto_article`,`auto_sub_offering`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `org_auto_sub_offering` (
  `org` int(11) NOT NULL,
  `auto_sub_offering` int(11) NOT NULL,
  PRIMARY KEY (`org`,`auto_sub_offering`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE org_auto ADD COLUMN date_established date NULL DEFAULT NULL;
ALTER TABLE org_auto ADD COLUMN average_ticket_size decimal(8,2) NOT NULL DEFAULT 0.00;
ALTER TABLE org_auto ADD COLUMN neighborhood varchar(40) NULL DEFAULT NULL;
ALTER TABLE org_auto ADD COLUMN city varchar(40) NULL DEFAULT NULL;
ALTER TABLE org_auto ADD COLUMN surrounding_area varchar(40) NULL DEFAULT NULL;
ALTER TABLE org_auto ADD COLUMN state varchar(40) NULL DEFAULT NULL;

UPDATE org_auto JOIN org ON org.org=org_auto.org
JOIN client_address ON org.client=client_address.client AND client_address.address_type=0
SET org_auto.city = client_address.city, org_auto.state = client_address.state;

ALTER TABLE org_auto ADD COLUMN max_discount tinyint(4) NOT NULL DEFAULT 0;
UPDATE org_auto SET max_discount = 10 WHERE trait3 = -1;
UPDATE org_auto SET max_discount = 20 WHERE trait3 = 1;
UPDATE org_auto SET max_discount = 30 WHERE trait3 > 1;

ALTER TABLE org_auto ADD COLUMN sweepstakes_gc_value decimal(8,2) NOT NULL DEFAULT 0;
ALTER TABLE org_auto ADD COLUMN sweepstakes_count tinyint(4) NOT NULL DEFAULT 4;

ALTER TABLE org_status MODIFY COLUMN status tinyint(4) NOT NULL DEFAULT 1;
INSERT INTO org_status SET name = "Auto Org", status = 2, sort = 25;
ALTER TABLE org ADD COLUMN auto_org int(11) NULL DEFAULT NULL;

ALTER TABLE event MODIFY COLUMN event_type tinyint(4) NOT NULL DEFAULT 0;
ALTER TABLE event MODIFY COLUMN type tinyint(4) NOT NULL DEFAULT 2;
ALTER TABLE event MODIFY COLUMN special_type tinyint(4) NOT NULL DEFAULT 1;

ALTER TABLE `org_client` ADD `last_visit` DATE NULL AFTER `org_opt_in_date`;
#10.29.2012 JBB Mantis0000765: Changes to retention functionality
