
#UPDATE 01.08.16 BEGIN
ALTER TABLE `sevence`.`promo_images`
ADD COLUMN `quarter_image` TEXT NULL COMMENT '' AFTER `sponsor_image`;
UPDATE promo_images SET quarter_image = coupon_image
#RC 12.07.15 Mantis 3163: More fun with image sizes
#UPDATE 01.08.16 END

#UPDATE 01.19.16 BEGIN
UPDATE promo_images,
    (SELECT
        business_org.org, promo_images.promotion
    FROM
        business_org
    JOIN promotion ON business_org.business = promotion.business_org
    JOIN promo_images ON promo_images.promotion = promotion.promotion
    WHERE
        promo_images.promotion IS NOT NULL) AS promotion_promo_images
SET
    promo_images.org = promotion_promo_images.org,
    promo_images.promotion = NULL
WHERE
    promo_images.promotion = promotion_promo_images.promotion;
#RC 01.14.16 Mantis 3198: Can't delete promo that has images attached

ALTER TABLE `sevence`.`promo_email_queue`
CHANGE COLUMN `priority` `priority` TINYINT(4) NULL DEFAULT 0 COMMENT '' ;
#KA 01.19.16 M3131: Error message during promo_email_queue process
#UPDATE 01.09.16 END

#UPDATE 03.01.16 BEGIN
ALTER TABLE `sevence`.`promo_images`
ADD COLUMN `facebook_ad_image` TEXT NULL DEFAULT NULL COMMENT '' AFTER `optimize_image`,
ADD COLUMN `facebook_carousel_image` TEXT NULL DEFAULT NULL COMMENT '' AFTER `facebook_ad_image`;
#RC 02.11.16 Mantis 3242: We need to support two new sizes of promo image
#UPDATE 03.01.16 END

#UPDATE 04.19.16 BEGIN
CREATE TABLE `sevence`.`metro_cat` (
  `metro_cat` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `metro_area` INT(11) NULL COMMENT '',
  `reach_category` INT(11) NULL COMMENT '',
  PRIMARY KEY (`metro_cat`)  COMMENT '',
  INDEX `metro_fk1_idx` (`metro_area` ASC)  COMMENT '',
  INDEX `reach_cat_fk1_idx` (`reach_category` ASC)  COMMENT '',
  CONSTRAINT `metro_fk1`
    FOREIGN KEY (`metro_area`)
    REFERENCES `sevence`.`metro_area` (`metro_area`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `reach_cat_fk1`
    FOREIGN KEY (`reach_category`)
    REFERENCES `sevence`.`reach_category` (`reach_category`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
#RC 02.22.16 Mantis 3256: Changes to reach categories

ALTER TABLE `sevence`.`org_metro`
ADD COLUMN `primary_metro` INT(1) NULL COMMENT '' DEFAULT 0  AFTER `metro`;
#DCS 02.22.16 Mantis 3255: Changes to org metro

CREATE TABLE `sevence`.`metro_neighborhood` (
  `metro_neighborhood` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `metro` INT(11) NULL COMMENT '',
  `neighborhood_name` VARCHAR(100) NULL COMMENT '',
  PRIMARY KEY (`metro_neighborhood`)  COMMENT '',
  INDEX `mn_metro_idx_idx` (`metro` ASC)  COMMENT '',
  CONSTRAINT `mn_metro_idx`
    FOREIGN KEY (`metro`)
    REFERENCES `sevence`.`metro_area` (`metro_area`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
#DCS 02.23.16 Mantis 3261: Creating neighborhoods (metro_neighborhood)

CREATE TABLE `org_neighborhood` (
  `org_neighborhood` int(11) NOT NULL AUTO_INCREMENT,
  `org` int(11) DEFAULT NULL,
  `metro_neighborhood` int(11) DEFAULT NULL,
  PRIMARY KEY (`org_neighborhood`),
  KEY `org_neighborhood_fk_1_idx` (`org`),
  KEY `org_neighborhood_fk_2_idx` (`metro_neighborhood`),
  CONSTRAINT `org_neighborhood_fk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `org_neighborhood_fk_2` FOREIGN KEY (`metro_neighborhood`) REFERENCES `metro_neighborhood` (`metro_neighborhood`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
#KA 02.24.15 M3261: Creating Neighborhoods AND Assigning neighborhoods to businesses

CREATE TABLE `sevence`.`client_address_product` (
  `client_address_product` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `client_address` INT(11) NULL COMMENT '',
  `bt_reach` TINYINT(1) NULL COMMENT '',
  `collect` TINYINT(1) NULL COMMENT '',
  `mailing_list` INT(11) NULL COMMENT '',
  PRIMARY KEY (`client_address_product`)  COMMENT '',
  INDEX `cap_mailing_list_idx_idx` (`client_address` ASC)  COMMENT '',
  INDEX `cap_mailing_list_idx_idx1` (`mailing_list` ASC)  COMMENT '',
  CONSTRAINT `cap_client_address_idx`
    FOREIGN KEY (`client_address`)
    REFERENCES `sevence`.`client_address` (`client_address`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `cap_mailing_list_idx`
    FOREIGN KEY (`mailing_list`)
    REFERENCES `sevence`.`marketing_list` (`marketing_list`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

#DCS 03.01.2016 M3264: Changes to support collect for multiple locations
#UPDATE 04.19.16 END

#UPDATE 04.24.16 BEGIN
ALTER TABLE `sevence`.`prof`
ADD COLUMN `login_token` TEXT NULL COMMENT '',
ADD COLUMN `token_date` DATETIME NULL COMMENT '';
INSERT INTO `sevence`.`email_marketing` (`email_marketing`, `default_email`, `type`, `id`, `org`, `active`, `name`, `subject`, `headline`, `narrative`, `body`, `email_cycle`, `hide`, `show_event`, `show_article`, `email_design`, `client_or_org`, `client_type`, `marketing_list`, `marketing_list_opt`, `org_type`, `org_restrict`, `editor`, `campaign_type`, `push_type`, `permalink`, `show_reward`) VALUES (NULL, NULL, '1', 'jos_login_link', '5', '1', 'jos login link', 'Login Link', 'Login Link', 'Follow the link below to redeem GC:\n{gift_utran_link}', 'Follow the link below to redeem GC:\n{gift_utran_link}', '1', '0', '1', '0', '0', '0', '0', '0', '0', 'ALL', 'NONE', '0', '0', '0', 'jos-login-link', '0');
#KA 04.13.2016 M3349: Changes to the gift_value tag

ALTER TABLE `sevence`.`gift`
ADD COLUMN `org_client_recip` INT(11) NULL COMMENT '' AFTER `notify_purchaser`,
ADD COLUMN `pos_status` TINYINT(1) NULL DEFAULT 0 COMMENT '' AFTER `notify_purchaser`,
ADD INDEX `gift_ibfk_9_idx` (`org_client_recip` ASC)  COMMENT '';
ALTER TABLE `sevence`.`gift`
ADD CONSTRAINT `gift_ibfk_9`
  FOREIGN KEY (`org_client_recip`)
  REFERENCES `sevence`.`org_client` (`org_client`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
#RC 04.13.16 Mantis 3350: Changes to IGC process just for joy of spa
#KA 04.21.16 M3379: "New" flag on GCs

ALTER TABLE `sevence`.`queue_social`
ADD COLUMN `promotion` INT(11) NULL COMMENT '' AFTER `sent_count`;
#RC 03.02.16 Mantis 3267: Text promotions
#UPDATE 04.24.16 END

#UPDATE 04.25.16 BEGIN
CREATE TABLE `sevence`.`org_bff` (
  `org_bff` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `org` INT(11) NULL COMMENT '',
  `bff` INT(11) NULL COMMENT '',
  PRIMARY KEY (`org_bff`)  COMMENT '',
  INDEX `org_bff_fk1_idx` (`org` ASC)  COMMENT '',
  INDEX `org_bff_fk2_idx` (`bff` ASC)  COMMENT '',
  CONSTRAINT `org_bff_fk1`
    FOREIGN KEY (`org`)
    REFERENCES `sevence`.`org` (`org`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `org_bff_fk2`
    FOREIGN KEY (`bff`)
    REFERENCES `sevence`.`org` (`org`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `sevence`.`org_competitor` (
  `org_competitor` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `org` INT(11) NULL COMMENT '',
  `competitor` INT(11) NULL COMMENT '',
  PRIMARY KEY (`org_competitor`)  COMMENT '',
  INDEX `fk_competitor_1_idx` (`org` ASC)  COMMENT '',
  INDEX `fk_competitor_2_idx` (`competitor` ASC)  COMMENT '',
  CONSTRAINT `fk_competitor_1`
    FOREIGN KEY (`org`)
    REFERENCES `sevence`.`org` (`org`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_competitor_2`
    FOREIGN KEY (`competitor`)
    REFERENCES `sevence`.`org` (`org`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
#RC 03.23.16 Mantis 3304: BFFs and competitors

ALTER TABLE `sevence`.`promotion`
ADD COLUMN `status` TINYINT(1) NULL DEFAULT 0 COMMENT '' AFTER `mosaic`;
ALTER TABLE `sevence`.`promotion`
ADD COLUMN `is_sms` TINYINT(1) NULL DEFAULT 0 COMMENT '' AFTER `status`,
ADD COLUMN `is_email` TINYINT(1) NULL DEFAULT 0 COMMENT '' AFTER `is_sms`;
ALTER TABLE `sevence`.`email_promo`
CHANGE COLUMN `send_email` `send_email` DATETIME NULL DEFAULT NULL COMMENT '' ;
ALTER TABLE `sevence`.`email_promo`
ADD COLUMN `network_email` TINYINT(1) NULL DEFAULT 0 COMMENT '' AFTER `network_introduction_text`;
CREATE TABLE `text_promo` (
  `text_promo` int(11) NOT NULL AUTO_INCREMENT,
  `org` int(11) NOT NULL,
  `send_on` datetime NOT NULL,
  `sent_on` datetime DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `attempts` tinyint(4) NOT NULL DEFAULT '0',
  `type` tinyint(4) NOT NULL,
  `post_option` tinyint(4) NOT NULL DEFAULT '0',
  `sequence` tinyint(4) DEFAULT NULL,
  `editor` tinyint(4) NOT NULL DEFAULT '0',
  `headline` blob,
  `result` varchar(100) DEFAULT NULL,
  `short_url` varchar(6) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
  `temp_type` varchar(20) DEFAULT NULL,
  `temp_link` varchar(100) DEFAULT NULL,
  `message_id` varchar(50) DEFAULT NULL,
  `marketing_list` varchar(100) DEFAULT NULL,
  `marketing_list_opt` tinyint(4) NOT NULL DEFAULT '0',
  `sms_to` varchar(20) DEFAULT NULL,
  `org_client` int(11) DEFAULT NULL,
  `sent_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`text_promo`),
  KEY `org` (`org`),
  CONSTRAINT `text_promo_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;
ALTER TABLE `sevence`.`text_promo`
ADD COLUMN `promotion` INT(11) NULL DEFAULT 0 COMMENT '' AFTER `sent_count`;
#KA 03.22.16 M3305: Promo status and Promo distribution method

-- table for tracking org credits
CREATE TABLE `sevence`.`org_credit_tracking` (
  `org` INT(11) NOT NULL COMMENT '',
  `date` DATETIME NULL COMMENT '',
  `credits` INT NULL COMMENT '',
  INDEX `fk_org_idx_idx` (`org` ASC)  COMMENT '',
  PRIMARY KEY (`org`)  COMMENT '',
  CONSTRAINT `fk_org_idx`
    FOREIGN KEY (`org`)
    REFERENCES `sevence`.`org` (`org`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
#JAN 05.03.2016 M3329: For the more PDF ethical_bribe_pdf

-- populate table initially with 2 credits for each unique opted-in email
INSERT INTO org_credit_tracking
SELECT org_client.org, now(), count(distinct email)*2 from org_client
JOIN client on client.client = org_client.client
JOIN org on org.org = org_client.org
WHERE org_inactive = 0 AND org_opt_in = 1 AND bt_reach = 1
AND TRIM(client.email) <> ''
GROUP BY org;
#DCS 03.23.16 M3305: table for tracking org credits

-- creating real business category
CREATE TABLE `sevence`.`org_real_category` (
  `org_real_category` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `org` INT(11) NULL COMMENT '',
  `real_category` INT(11) NULL COMMENT '',
  PRIMARY KEY (`org_real_category`)  COMMENT '',
  INDEX `fk_real_category_idx_idx` (`real_category` ASC)  COMMENT '',
  CONSTRAINT `fk_real_category_idx`
    FOREIGN KEY (`real_category`)
    REFERENCES `sevence`.`reach_category` (`reach_category`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
#DCS 03.28.2016 M3303: Changes to reach email (add org real category)

ALTER TABLE `sevence`.`promotion`
ADD COLUMN `exclude_marketing` TINYINT(1) NULL DEFAULT '0' COMMENT '' AFTER `delayed_content`;
#RC 04.07.16 ADD field 'exclude_marketing' to promotion table

CREATE TABLE `promotion_marketing_list` (
  `promotion_marketing_list` int(11) NOT NULL AUTO_INCREMENT,
  `promotion` int(11) DEFAULT NULL,
  `marketing_list` int(11) DEFAULT NULL,
  PRIMARY KEY (`promotion_marketing_list`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
;
#RC 04.07.16 CREATE table name as promotion_marketing_list

CREATE TABLE `sevence`.`real_category` (
  `real_category` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `category` VARCHAR(50) NULL COMMENT '',
  `jpg_png_image` TEXT(65535) NULL COMMENT '',
  `gif_image` TEXT(65535) NULL COMMENT '',
  `b2b_cat` VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY (`real_category`)  COMMENT '',
  UNIQUE INDEX `real_category_UNIQUE` (`real_category` ASC)  COMMENT '');
#JAN 04.11.16 added a new table for real_category

INSERT INTO real_category (category) VALUES('Acupuncture');
INSERT INTO real_category (category) VALUES('Apartments');
INSERT INTO real_category (category) VALUES('Apparel Childrens');
INSERT INTO real_category (category) VALUES('Apparel Mens');
INSERT INTO real_category (category) VALUES('Apparel Womens');
INSERT INTO real_category (category) VALUES('Arcades Amusement Parks');
INSERT INTO real_category (category) VALUES('Art Dealers & Galleries');
INSERT INTO real_category (category) VALUES('Audiovisual Services');
INSERT INTO real_category (category) VALUES('Auto Maintenance Repair');
INSERT INTO real_category (category) VALUES('Automotive Parts & Repair');
INSERT INTO real_category (category) VALUES('Bagel & Donuts Shops');
INSERT INTO real_category (category) VALUES('Bakeries');
INSERT INTO real_category (category) VALUES('Barbers');
INSERT INTO real_category (category) VALUES('Bars Pubs');
INSERT INTO real_category (category) VALUES('Beauty Salons');
INSERT INTO real_category (category) VALUES('BeautyProducts Bath Body Shop');
INSERT INTO real_category (category) VALUES('Bed & Breakfasts');
INSERT INTO real_category (category) VALUES('Billiard & Pool');
INSERT INTO real_category (category) VALUES('Body Art Salons');
INSERT INTO real_category (category) VALUES('Bookstores');
INSERT INTO real_category (category) VALUES('Bowling');
INSERT INTO real_category (category) VALUES('Breweries');
INSERT INTO real_category (category) VALUES('Building Supplies');
INSERT INTO real_category (category) VALUES('Campgrounds & RV Parks');
INSERT INTO real_category (category) VALUES('Car Rentals');
INSERT INTO real_category (category) VALUES('Car Wash & Detail');
INSERT INTO real_category (category) VALUES('Carpenters');
INSERT INTO real_category (category) VALUES('Carpet & Upholstery Cleaning');
INSERT INTO real_category (category) VALUES('Caterers');
INSERT INTO real_category (category) VALUES('Chiropractors');
INSERT INTO real_category (category) VALUES('Coffee Shops');
INSERT INTO real_category (category) VALUES('Combat Sports');
INSERT INTO real_category (category) VALUES('Copying & Signage');
INSERT INTO real_category (category) VALUES('Costume Rental');
INSERT INTO real_category (category) VALUES('Cycling');
INSERT INTO real_category (category) VALUES('Dance');
INSERT INTO real_category (category) VALUES('Day Care & Preschools');
INSERT INTO real_category (category) VALUES('Decks & Patio');
INSERT INTO real_category (category) VALUES('Dentists');
INSERT INTO real_category (category) VALUES('Doors & Windows');
INSERT INTO real_category (category) VALUES('Dry Cleaners');
INSERT INTO real_category (category) VALUES('Electricians');
INSERT INTO real_category (category) VALUES('Electronics & Cameras');
INSERT INTO real_category (category) VALUES('Equestrian');
INSERT INTO real_category (category) VALUES('Florists');
INSERT INTO real_category (category) VALUES('Food Retail');
INSERT INTO real_category (category) VALUES('Framing Stores');
INSERT INTO real_category (category) VALUES('Golf Courses');
INSERT INTO real_category (category) VALUES('Gun Ranges');
INSERT INTO real_category (category) VALUES('Gymnastics');
INSERT INTO real_category (category) VALUES('Gyms & Fitness Centers');
INSERT INTO real_category (category) VALUES('Hardware Stores');
INSERT INTO real_category (category) VALUES('Hobby & Collectibles');
INSERT INTO real_category (category) VALUES('Holistic Medicine');
INSERT INTO real_category (category) VALUES('Home Furnishings');
INSERT INTO real_category (category) VALUES('Home Improvement');
INSERT INTO real_category (category) VALUES('Home Inspection');
INSERT INTO real_category (category) VALUES('Hotels');
INSERT INTO real_category (category) VALUES('Hunting & Fishing');
INSERT INTO real_category (category) VALUES('Ice Cream Parlors');
INSERT INTO real_category (category) VALUES('Interior Design');
INSERT INTO real_category (category) VALUES('Jewelry Stores');
INSERT INTO real_category (category) VALUES('Juice Bars');
INSERT INTO real_category (category) VALUES('Karaoke');
INSERT INTO real_category (category) VALUES('Landscaping & Gardeners');
INSERT INTO real_category (category) VALUES('Lawyers');
INSERT INTO real_category (category) VALUES('Liquor Stores');
INSERT INTO real_category (category) VALUES('Lodges');
INSERT INTO real_category (category) VALUES('Massage Therapists');
INSERT INTO real_category (category) VALUES('Mobile Homes');
INSERT INTO real_category (category) VALUES('Motels');
INSERT INTO real_category (category) VALUES('Motorcycle Dealers');
INSERT INTO real_category (category) VALUES('Motorcycle Maintenance Repair');
INSERT INTO real_category (category) VALUES('Movers');
INSERT INTO real_category (category) VALUES('Music & Show Venues');
INSERT INTO real_category (category) VALUES('Nail Salons');
INSERT INTO real_category (category) VALUES('Night Clubs');
INSERT INTO real_category (category) VALUES('Optometrists');
INSERT INTO real_category (category) VALUES('Other');
INSERT INTO real_category (category) VALUES('Paint Supplies');
INSERT INTO real_category (category) VALUES('Painters');
INSERT INTO real_category (category) VALUES('Pediatricians');
INSERT INTO real_category (category) VALUES('Personal Trainers');
INSERT INTO real_category (category) VALUES('Pest Control');
INSERT INTO real_category (category) VALUES('Pet Supplies');
INSERT INTO real_category (category) VALUES('Photographers');
INSERT INTO real_category (category) VALUES('Plumbers');
INSERT INTO real_category (category) VALUES('Pools Spas & Supplies');
INSERT INTO real_category (category) VALUES('Professional Cleaning');
INSERT INTO real_category (category) VALUES('Psychiatrists');
INSERT INTO real_category (category) VALUES('Psychics & Astrologers');
INSERT INTO real_category (category) VALUES('Psychologists');
INSERT INTO real_category (category) VALUES('Realtors');
INSERT INTO real_category (category) VALUES('Resorts');
INSERT INTO real_category (category) VALUES('Restaurants ');
INSERT INTO real_category (category) VALUES('Restaurants Pizza');
INSERT INTO real_category (category) VALUES('Restaurants Sushi');
INSERT INTO real_category (category) VALUES('Retail Misc');
INSERT INTO real_category (category) VALUES('Roofers');
INSERT INTO real_category (category) VALUES('Shoe stores');
INSERT INTO real_category (category) VALUES('Skin Care Salons');
INSERT INTO real_category (category) VALUES('Spas');
INSERT INTO real_category (category) VALUES('Sporting Goods');
INSERT INTO real_category (category) VALUES('Sports Medicine');
INSERT INTO real_category (category) VALUES('Stationary');
INSERT INTO real_category (category) VALUES('Storage');
INSERT INTO real_category (category) VALUES('Tailors');
INSERT INTO real_category (category) VALUES('Tanning Salons');
INSERT INTO real_category (category) VALUES('Taxi Services');
INSERT INTO real_category (category) VALUES('Tea Houses');
INSERT INTO real_category (category) VALUES('Therapists');
INSERT INTO real_category (category) VALUES('Tire stores');
INSERT INTO real_category (category) VALUES('Tobacco eCig');
INSERT INTO real_category (category) VALUES('Toy Stores');
INSERT INTO real_category (category) VALUES('Travel Agents');
INSERT INTO real_category (category) VALUES('Tree Services');
INSERT INTO real_category (category) VALUES('Truck Rentals');
INSERT INTO real_category (category) VALUES('Tutoring & Educational Services');
INSERT INTO real_category (category) VALUES('Upholsterers');
INSERT INTO real_category (category) VALUES('Vacation Rentals');
INSERT INTO real_category (category) VALUES('Veterinarians');
INSERT INTO real_category (category) VALUES('Vintage & Thrift');
INSERT INTO real_category (category) VALUES('Yoga & Pilates');
#JAN 04.11.16 added the categories from the CSV to real_category table

ALTER TABLE `sevence`.`org_real_category`
DROP FOREIGN KEY `fk_real_category_idx`;
ALTER TABLE `sevence`.`org_real_category`
ADD CONSTRAINT `fk_real_category_idx`
  FOREIGN KEY (`real_category`)
  REFERENCES `sevence`.`real_category` (`real_category`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
#JAN 04.11.16 altered table org_real_category to reference real_category table instead of reach_category

ALTER TABLE `sevence`.`real_category`
DROP COLUMN `b2b_cat`,
DROP COLUMN `gif_image`,
DROP COLUMN `jpg_png_image`;
#JAN 04.15.16 DROP three columnds from the real_category table

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_or_org`,`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`,`email_trigger`) VALUES (NULL,1,''review_reminder'',967,1,''review reminder'',''Review Reminder'',''Review'',''Review'',''<p>Review</p>\r\n<p>&nbsp;</p>\r\n<p>{review_stars}</p>'',NULL,1,NULL,0,0,0,168,0,''0'',''0'',0,''ALL'',''NONE'',1,0,'''',0,0,0,0,''2016-04-14 05:39:42'',10753,''business-review-stars'',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_or_org`,`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`,`email_trigger`) VALUES (NULL,1,'review_alert',967,1,'review alert','Review Alert','Review','Review','<h1>New Business Review</h1><br><br>\n<p>Rating from: {client_name}</p>\n<p>Rating: {rating}</p>\n<p>Message: {message}</p>\n<p>&nbsp;</p>',NULL,1,NULL,0,0,0,168,0,'0','0',0,'ALL','NONE',1,0,'',0,0,0,0,'2016-04-14 05:39:42',10753,'business-review-stars',0,'');
#RC 04.18.16 ADD new email_id in email marketing

ALTER TABLE `sevence`.`org_credit_tracking`
ADD COLUMN `org_credit_tracking` INT(11) NOT NULL AUTO_INCREMENT COMMENT '' FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`org_credit_tracking`)  COMMENT '';
#DCS 04.18.16 Add PK to org_credit_tracking so we can insert multiple records of credits to orgs
#UPDATE 04.25.16 END

#UPDATE 04.27.16 BEGIN
ALTER TABLE `sevence`.`text_promo`
DROP COLUMN `sent_count`,
DROP COLUMN `org_client`,
DROP COLUMN `sms_to`,
DROP COLUMN `marketing_list_opt`,
DROP COLUMN `marketing_list`,
DROP COLUMN `message_id`,
DROP COLUMN `editor`,
DROP COLUMN `sequence`,
DROP COLUMN `post_option`,
DROP COLUMN `type`,
DROP COLUMN `attempts`,
CHANGE COLUMN `sent_on` `queued_on` DATETIME NULL DEFAULT NULL COMMENT '' ;
#KA 04.26.16 M3387: text promo API seems to be returning the wrong stuff

ALTER TABLE `sevence`.`sponsorships`
ADD COLUMN `metro` INT(11) NULL DEFAULT NULL COMMENT '' AFTER `category`;
#KA 04.26.16 M3387: text promo API seems to be returning the wrong stuff
#UPDATE 04.27.16 END

#UPDATE 04.28.16 BEGIN
ALTER TABLE `sevence`.`org_setting`
ADD COLUMN `flag_new_gc` TINYINT(1) NULL DEFAULT 0 COMMENT '' AFTER `gift_auto_expire`;

UPDATE org_setting
JOIN org ON org.org = org_setting.org
JOIN org_parm as harms_parm ON harms_parm.org = org.org AND harms_parm.type='HarmsMillennium' AND harms_parm.parm='Integration'
JOIN org_parm as aloha_parm ON aloha_parm.org = org.org AND aloha_parm.type='HarmsMillennium' AND aloha_parm.parm='Integration'
SET flag_new_gc = 1
WHERE (org.processing_cc_type = 1 OR org.processing_cc_type = 6) AND (harms_parm.value != 'PULL' AND aloha_parm.value != 'PULL');
#DCS 04.26.16 M3379: Set flag in org_setting
#UPDATE 04.28.16 END

#UPDATE 05.12.16 BEGIN
ALTER TABLE `sevence`.`promotion`
ADD COLUMN `click_through_to` VARCHAR(45) NULL COMMENT '' AFTER `is_email`;
#KA 05.03.16 M3411: New URL setting on promo

ALTER TABLE `sevence`.`marketing_list`
ADD COLUMN `b2b` TINYINT(1) NULL DEFAULT 0 COMMENT '' AFTER `locale`;
#KA 05.04.16 M3413: Refinements to the B2B reach email stuff
#UPDATE 05.12.16 END

#UPDATE 05.17.16 BEGIN
ALTER TABLE `sevence`.`bt_product`
ADD COLUMN `setup_desc` VARCHAR(20) NULL COMMENT '' AFTER `billing_calc`,
ADD COLUMN `bt_reach` TINYINT(1) NULL DEFAULT '0' COMMENT '' AFTER `setup_desc`,
ADD COLUMN `collect` TINYINT(1) NULL DEFAULT '0' COMMENT '' AFTER `bt_reach`,
ADD COLUMN `wp_website` TINYINT(1) NULL DEFAULT '0' COMMENT '' AFTER `collect`,
ADD COLUMN `bt_bundle` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '' AFTER `wp_website`,
ADD COLUMN `mosaic_only` TINYINT(1) NULL DEFAULT '0' COMMENT '' AFTER `bt_bundle`,
ADD COLUMN `service_level` VARCHAR(4) NULL COMMENT '' AFTER `mosaic_only`;
#RC 05.16.16 Mantis 3428: More signup/upgrade changes

ALTER TABLE `sevence`.`org`
ADD COLUMN `bt_bundle` TINYINT(1) NULL DEFAULT '0' COMMENT '' AFTER `bt_referral`;
#RC 05.16.16 Mantis 3428: More signup/upgrade changes

ALTER TABLE `sevence`.`billing_calc`
ADD COLUMN `bt_bundle` TINYINT(1) NULL DEFAULT '0' COMMENT '';
#RC 05.16.16 Mantis 3428: More signup/upgrade changes
#UPDATE 05.17.16 END

#UPDATE 05.24.16 BEGIN
ALTER TABLE `sevence`.`org` ADD COLUMN `bt_product_desc` VARCHAR(100) NULL COMMENT '' AFTER `bt_bundle`
#UPDATE 05.24.16 END

#UPDATE 6.13.15 BEGIN
ALTER TABLE `sevence`.`org_category`
ADD COLUMN `metro_cat` INT(11) NULL COMMENT '' AFTER `reach_category`;

ALTER TABLE `sevence`.`org_category`
ADD INDEX `org_category_metro_cat_foreign_idx` (`metro_cat` ASC)  COMMENT '';
ALTER TABLE `sevence`.`org_category`
ADD CONSTRAINT `org_category_metro_cat_foreign`
  FOREIGN KEY (`metro_cat`)
  REFERENCES `sevence`.`metro_cat` (`metro_cat`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

UPDATE org_category oc1
JOIN org_metro om1 ON om1.org = oc1.org AND om1.primary_metro = 1
JOIN metro_cat mc1 ON mc1.reach_category = oc1.reach_category AND  mc1.metro_area = om1.metro
SET oc1.metro_cat = mc1.metro_cat;

ALTER TABLE `sevence`.`org_category`
DROP FOREIGN KEY `reach_category_fk_1`;
ALTER TABLE `sevence`.`org_category`
DROP COLUMN `reach_category`,
DROP INDEX `reach_category_fk_1_idx` ;
#KA 06.09.2016 M3476: Org category

CREATE TABLE `lightbox_page` (
  `org` int(11) NOT NULL,
  `page_type` varchar(45) NOT NULL DEFAULT '',
  `css` text,
  `html` text,
  PRIMARY KEY (`org`,`page_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `sevence`.`lightbox_page`
ADD COLUMN `after_css` TEXT NULL DEFAULT NULL COMMENT '' AFTER `html`,
ADD COLUMN `after_html` TEXT NULL DEFAULT NULL COMMENT '' AFTER `after_css`;
#KA 04.11.2016 M3329: More fun with lightboxes: functionality

CREATE TABLE `ethical_bribe_pdf` (
  `ethical_bribe_pdf` int(11) NOT NULL AUTO_INCREMENT,
  `unique_name` varchar(45) DEFAULT NULL,
  `file_name` varchar(45) DEFAULT NULL,
  `marketing_list` varchar(255) DEFAULT NULL,
  `org` int(11) DEFAULT NULL,
  PRIMARY KEY (`ethical_bribe_pdf`),
  KEY `org_npm_fk_idx` (`org`),
  CONSTRAINT `org_npm_fk` FOREIGN KEY (`org`) REFERENCES `org` (`org`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;


ALTER TABLE `sevence`.`lightbox_page`
ADD COLUMN `3_css` TEXT NULL COMMENT '' AFTER `after_html`,
ADD COLUMN `3_html` TEXT NULL COMMENT '' AFTER `3_css`;
#KA 05.20.16 M3329: More fun with lightboxes: functionality
#UPDATE 6.13.15 END

#UPDATE 7.21.16 BEGIN
ALTER TABLE `sevence`.`org`
ADD COLUMN `future_service_level` VARCHAR(50) NULL DEFAULT NULL COMMENT '' AFTER `bt_product_desc`,
ADD COLUMN `future_billing_calc` INT(11) NULL DEFAULT NULL COMMENT '' AFTER `future_service_level`;
#RC 07.13.2016 M3544: Changes to the reach signup and reach upgrade page
#UPDATE 7.21.16 END

#UPDATE 8.2.16 BEGIN
ALTER TABLE sevence.bt_product
ADD COLUMN setup_installment tinyint(4) DEFAULT 1;
#SLOW 08.01.16 3576: More changes to signup
#UPDATE 8.2.16 END

#UPDATE 8.18.16 BEGIN
ALTER TABLE `sevence`.`promotion`
ADD COLUMN `informational_url` TEXT NULL COMMENT '' AFTER `exclude_marketing`;
#RC 06.30.16 Mantis 3523: Collect app: fuse settings

CREATE TABLE `sevence`.`collect_settings` (
  `collect_settings` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `background_image` TEXT NULL COMMENT '',
  `primary_color` VARCHAR(10) NULL COMMENT '',
  `primary_color_opacity` INT NULL COMMENT '',
  `text_color` VARCHAR(10) NULL COMMENT '',
  `text_color_opacity` INT NULL COMMENT '',
  `background_color` VARCHAR(10) NULL COMMENT '',
  `font` VARCHAR(50) NULL COMMENT '',
  `button_style` VARCHAR(30) NULL COMMENT '',
  `button_background` VARCHAR(10) NULL COMMENT '',
  `button_font_color` VARCHAR(10) NULL COMMENT '',
  `button_text` TEXT NULL COMMENT '',
  `ready_to_upgrade` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '',
  `org` INT NULL COMMENT '',
  PRIMARY KEY (`collect_settings`)  COMMENT '',
  INDEX `collect_settings_org_fk1_idx` (`org` ASC)  COMMENT '',
  CONSTRAINT `collect_settings_org_fk1`
    FOREIGN KEY (`org`)
    REFERENCES `sevence`.`org` (`org`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
#RC 06.30.16 Mantis 3523: Collect app: fuse settings

ALTER TABLE `sevence`.`collect_settings`
ADD COLUMN `collect_cellphone` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '' AFTER `ready_to_upgrade`,
ADD COLUMN `send_special_deals` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '' AFTER `collect_cellphone`,
ADD COLUMN `text_deal_text` TEXT NULL DEFAULT NULL COMMENT '' AFTER `send_special_deals`,
ADD COLUMN `punches` INT(11) NULL DEFAULT NULL COMMENT '' AFTER `text_deal_text`;
#RC 07.19.16 Mantis 3557: More changes to the collect stuff for the new kiosk

CREATE TABLE `sevence`.`collect_visit` (
  `collect_visit` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `org_client` INT NULL COMMENT '',
  `visit_date` DATETIME NULL COMMENT '',
  PRIMARY KEY (`collect_visit`)  COMMENT '',
  INDEX `collect_visit_fk_1_idx` (`org_client` ASC)  COMMENT '',
  CONSTRAINT `collect_visit_fk_1`
    FOREIGN KEY (`org_client`)
    REFERENCES `sevence`.`org_client` (`org_client`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
#RC 07.19.16 Mantis 3557: Create table collect_visit

INSERT INTO `sevence`.`org_parm` (`org`, `type`, `parm`, `value`) VALUES ('409', 'Mailchimp', 'api_key', 'cc45bcc1ee15a4ed8dd2616f3566231b-us12');
#KA 08.17.2016 M3531: Opt in monster integration

#UPDATE 8.18.16 UPDATE END

#UPDATE 10.25.16 BEGIN

ALTER TABLE `sevence`.`org`
ADD COLUMN `yearly_clients_review_reminder` TINYINT(1) NULL DEFAULT '0' COMMENT '' AFTER `future_billing_calc`;
#RC 10.10.2016 M3744: Remind clients cron job

#UPDATE 10.25.16 END

ALTER TABLE  org ADD  recurly_import TINYINT( 4 ) NULL DEFAULT NULL;
#BEM 12.5.16 column to track which orgs have been moved to recurly for billing

CREATE TABLE IF NOT EXISTS `billing_to_usd` (
  `country` char(2) NOT NULL,
  `to_usd` decimal(10,4) NOT NULL DEFAULT '1.0000',
  PRIMARY KEY (`country`)
);
INSERT IGNORE INTO `billing_to_usd` (`country`, `to_usd`) VALUES
('AU', '0.7437'),('CA', '0.7527'),('IE', '1.0723'),('MX', '0.0490'),('UK', '1.2663');
#BEM 12.6.16 currency conversion

ALTER TABLE `billing_item` ADD `recurly_uuid` VARCHAR(32) NULL DEFAULT NULL AFTER `affiliate_tran`;
#BEN 12.7.16 add column to track recurly adjustment

ALTER TABLE  `promo_email_info` ADD  `transmission_id` VARCHAR( 64 ) NULL DEFAULT NULL AFTER  `message_id` ,
  ADD INDEX (  `transmission_id` );
ALTER TABLE `client_journal` CHANGE  `message_id`  `message_id` VARCHAR( 64 ) NULL DEFAULT NULL,
  ADD  `transmission_id` VARCHAR( 64 ) NULL DEFAULT NULL AFTER  `message_id` ,
  ADD INDEX (  `transmission_id` );
ALTER TABLE `edb`.`e` ADD `accept` DATETIME NULL DEFAULT NULL AFTER `reject_count`,
	ADD `accept_count` INT NOT NULL DEFAULT '0' AFTER `accept`;
CREATE TABLE `edb`.`ip` (
  `ip` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `sending_ip` char(16) NOT NULL,
  `routing_domain` varchar(40) NOT NULL,
  `injection_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ip`),
  KEY `date` (`date`,`sending_ip`,`routing_domain`)
);
#BEN 3.10.17 sparkpost

alter table promo_email_layout change column `layout_image text` `layout_image_text` text;
create table email_queue_deferred like email_queue;
#BEN 3.14.17 fix messed up schema

#UPDATE 05-15-17 BEGIN
ALTER TABLE `sevence`.`marketing_list`
  ADD COLUMN `engage` TINYINT(1) NOT NULL DEFAULT '0' AFTER `b2b`;
#JTW 10.19.2016 M3753: Changes to the campaign list, already on testing

UPDATE event SET hide=1 WHERE name = "Share with a friend" or name= "Spa wish list";
#AB 04.26.17 M4063 : hide share with a friend and spa wish list

CREATE TABLE `client_optin` (
  `client_optin` int(11) NOT NULL AUTO_INCREMENT,
  `client` int(11) DEFAULT NULL,
  `client_optin_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`client_optin`),
  KEY `client_fk_1_idx` (`client`),
  CONSTRAINT `client_fk_1` FOREIGN KEY (`client`) REFERENCES `client` (`client`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
#RC 5.05.2016 M4062: Spammers are abusing the email signup form and we need to stop them

INSERT INTO `sevence`.`org_parm` (`org`, `type`, `parm`, `value`) VALUES ('409', 'Recurly', 'subdomain', 'boomtimetest');
INSERT INTO `sevence`.`org_parm` (`org`, `type`, `parm`, `value`) VALUES ('409', 'Recurly', 'public_key', 'ewr1-XIhdfeRK5F2QVYUZf4ocNn');
INSERT INTO `sevence`.`org_parm` (`org`, `type`, `parm`, `value`) VALUES ('409', 'Recurly', 'private_key', 'c7b2bc10e7144cfaab80afa9945cf307');
INSERT INTO `sevence`.`org_parm` (`org`, `type`, `parm`, `value`) VALUES ('409', 'Recurly', 'plan', 'Plan0');
#RC 04.11.2017 M3935: Recurly Support for reach signup/upgrade

ALTER TABLE `sevence`.`bt_product`
ADD COLUMN `stream` DECIMAL(11,2) NULL COMMENT '' AFTER `setup_installment`,
ADD COLUMN `discounted_stream` DECIMAL(11,2) NULL COMMENT '' AFTER `stream`;
#RC 04.11.2017 M3935: Recurly Support for reach signup/upgrade

ALTER TABLE `sevence`.`bt_product`
CHANGE COLUMN `stream` `level1` DECIMAL(11,2) NULL DEFAULT NULL COMMENT '' ,
CHANGE COLUMN `discounted_stream` `level2` DECIMAL(11,2) NULL DEFAULT NULL COMMENT '' ,
ADD COLUMN `level3` DECIMAL(11,2) NULL COMMENT '' AFTER `level2`;
#RC 05.04.2017 M3935: Recurly Support for reach signup/upgrade

UPDATE bt_product SET level1=400, level2=300, level3=200 where bt_bundle=1;
#RC 05.04.2017 M3935: Recurly Support for reach signup/upgrade

ALTER TABLE `sevence`.`org`
ADD COLUMN `recurly_sub` DECIMAL(12,2) NULL COMMENT '' AFTER `yearly_clients_review_reminder`;
#RC 05.05.2017 M3935: Recurly Support for reach signup/upgrade

#UPDATE 05-15-17 END

ALTER TABLE `edb`.`sparkpost_subaccount` ADD `sending_domain` VARCHAR(40) NULL DEFAULT NULL AFTER `api_key`;
ALTER TABLE `edb`.`sparkpost_subaccount` ADD `dkim_selector` VARCHAR(8) NULL DEFAULT NULL;
ALTER TABLE `edb`.`sparkpost_subaccount` ADD `dkim_public` VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE `edb`.`sparkpost_subaccount` ADD `verified` TINYINT NOT NULL DEFAULT '0';
ALTER TABLE `edb`.`sparkpost_subaccount` ADD `from_mailbox` VARCHAR(20) NULL DEFAULT NULL;
ALTER TABLE `edb`.`sparkpost_subaccount` ADD `force_from` TINYINT NOT NULL DEFAULT '0';
#BEM 8.15.19 columns store nfo needed to verify sparkpost sending domain

# Dev updates start here #################################################
#
# Add your updates to the end.
# Please include your initials, the date and Mantis ID in a comment following your statements.
#
###############################################################################


