ALTER TABLE event ADD COLUMN event_priority tinyint(1) NOT NULL DEFAULT 0 AFTER type;
ALTER TABLE event ADD COLUMN event_date DATE NULL DEFAULT '0000-00-00' AFTER start;
ALTER TABLE event_service_option ADD COLUMN event_date DATE NULL DEFAULT '0000-00-00' AFTER start;
UPDATE event SET event_date = end WHERE end IS NOT NULL;
UPDATE event_service_option SET event_date = end WHERE end IS NOT NULL;

# update 01/14/10

ALTER TABLE `org_amazon` CHANGE `token_type` `token_type` TINYINT( 1 ) NOT NULL;
ALTER TABLE `org_amazon` DROP COLUMN `refund_token`;
ALTER TABLE `org_amazon` ADD COLUMN `token_status` tinyint(1) NOT NULL DEFAULT '0' after `token_type`;
ALTER TABLE `org_amazon` CHANGE `recipient_token` `token` VARCHAR( 64 ) NULL DEFAULT NULL;

# update 01/18/10

ALTER TABLE event_service_option MODIFY COLUMN end DATETIME DEFAULT '0000-00-00 00:00';
ALTER TABLE event MODIFY COLUMN event_date DATETIME DEFAULT '0000-00-00 00:00';
ALTER TABLE event MODIFY COLUMN end DATETIME DEFAULT '0000-00-00 00:00';

# update 01/21/10

CREATE TABLE `offer_deal` (
  `offer_deal` int(11) NOT NULL AUTO_INCREMENT,
  `org` int(11) NOT NULL,
  `offer_date` datetime NOT NULL,
  `offer_num` tinyint(4) NOT NULL DEFAULT '1',
  `type` tinyint(4) DEFAULT NULL,
  `offer` int(11) NOT NULL,
  `offer_limit` int(11) NOT NULL DEFAULT '0',
  `offer_remain` int(11) NOT NULL DEFAULT '0',
  `expire_days` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`offer_deal`),
  KEY `org` (`org`),
  KEY `offer` (`offer`),
  CONSTRAINT `offer_deal_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`),
  CONSTRAINT `offer_deal_ibfk_2` FOREIGN KEY (`offer`) REFERENCES `offer` (`offer`)
) ENGINE=InnoDB;

ALTER TABLE marketing_list ADD COLUMN type tinyint NOT NULL DEFAULT 0, ADD COLUMN marketing_list_parent int NOT NULL DEFAULT 0 AFTER marketing_list;
ALTER TABLE marketing_list ADD COLUMN locale int;

ALTER TABLE email_marketing ADD COLUMN marketing_list_opt tinyint NOT NULL DEFAULT 0 AFTER marketing_list;

INSERT INTO part (org, part_id, content) VALUES (3, 'ml_dailydeal', 'Also Include:');
INSERT INTO part (org, part_id, content) VALUES (3, 'daily_deal_h', '<h1>Daily Deals</h1>');
INSERT INTO part (org, part_id, content) VALUES (3, 'daily_deal_c', "<h1 class='event'>Your daily deal has been sent to you</h1>");
INSERT INTO part (org, part_id, content) VALUES (3, 'daily_deal_l', "<h1>Daily Deal</h1><p>Select the deal you want and we'll send it right now to your email address.</p>");
INSERT INTO part (org, part_id, content) VALUES (3, 'daily_deal_r', "<h1 class='event'>Your daily deal has been sent to you again</h1>");
INSERT INTO part (org, part_id, content) VALUES (3, 'daily_deal_s', "<p>Don't be left out! Sign up for our newsletter now.</p>");
INSERT INTO part (org, part_id, content) VALUES (3, 'daily_deal_a', "Send me occasional specials and gift-giving idea emails");
INSERT INTO part (org, part_id, content) VALUES (2850, 'ml_dailydeal', 'Also Include:');
INSERT INTO part (org, part_id, content) VALUES (2850, 'daily_deal_h', '<h1>Daily Deals</h1>');
INSERT INTO part (org, part_id, content) VALUES (2850, 'daily_deal_c', "<h1 class='event'>Your daily deal has been sent to you</h1>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'daily_deal_l', "<h1>Daily Deal</h1><p>Select the deal you want and we'll send it right now to your email address.</p>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'daily_deal_r', "<h1 class='event'>Your daily deal has been sent to you again</h1>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'daily_deal_s', "<p>Don't be left out! Sign up for our newsletter now.</p>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'daily_deal_a', "Send me occasional specials and exicting offers");

ALTER TABLE gift_promo ADD COLUMN offer_deal int;

INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (3, 'dailydeal', 'Daily Deals', 'dailydeal', 1, 1, 500000, 1);

INSERT INTO email_marketing (type, id, org, active, name, subject, headline, narrative, body, show_event, email_design, client_type, marketing_list, offer, push_type, editor) VALUES (1, 'dailydeal', 3, 1, "Here's your daily deal!", "Here's your daily deals from {short_name}", "Here's your daily deal!",
'Here are some great daily deals for you!

{daily_deal_list}',
'Here are some great daily deals for you!

{daily_deal_list}', 1, (select email_design from email_design where name = 'Newsletter'), 0, 0, (SELECT offer_type FROM offer_type WHERE offer_base='dailydeal' AND org = 3 LIMIT 1), 1, 0);
INSERT INTO email_marketing (type, id, org, active, name, subject, headline, narrative, body, show_event, email_design, client_type, marketing_list, offer, push_type, editor) VALUES (1, 'dailydealsend', 3, 1, "Here's your deal!", "Here's your deal from {short_name}", "Here's your deal!",
'From: {short_name}
For: {offer_value}

{offer}

{offer_link}','From: {short_name}
For: {offer_value}

{offer}

{offer_link}', 1, (select email_design from email_design where name = 'Newsletter'), 0, 0, (SELECT offer_type FROM offer_type WHERE offer_base='dailydeal' AND org = 3 LIMIT 1), 1, 0);

INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (2850, 'dailydeal', 'Daily Deals', 'dailydeal', 1, 1, 500000, 1);
INSERT INTO email_marketing (type, id, org, active, name, subject, headline, narrative, body, show_event, email_design, client_type, marketing_list, offer, push_type, editor) VALUES (1, 'dailydeal', 2850, 1, "Here's your daily deal!", "Here's your daily deals from {short_name}", "Here's your daily deal!",
'Here are some great daily deals for you!

{daily_deal_list}',
'Here are some great daily deals for you!

{daily_deal_list}', 1, (select email_design from email_design where name = 'Newsletter'), 0, 0, (SELECT offer_type FROM offer_type WHERE offer_base='dailydeal' AND org = 2850 LIMIT 1), 1, 0);
INSERT INTO email_marketing (type, id, org, active, name, subject, headline, narrative, body, show_event, email_design, client_type, marketing_list, offer, push_type, editor) VALUES (1, 'dailydealsend', 2850, 1, "Here's your deal!", "Here's your deal from {short_name}", "Here's your deal!",
'From: {short_name}
For: {offer_value}

{offer}

{offer_link}','From: {short_name}
For: {offer_value}

{offer}

{offer_link}', 1, (select email_design from email_design where name = 'Newsletter'), 0, 0, (SELECT offer_type FROM offer_type WHERE offer_base='dailydeal' AND org = 2850 LIMIT 1), 1, 0);

INSERT INTO email_marketing (type, id, org, active, name, subject, headline, narrative, body, send_on, email_cycle, show_event, email_design, client_type, marketing_list, offer, push_type, editor) VALUES (2, null, 3, 1, "Daily Deal Signup", "Don't miss the deals from {short_name}", "Don't miss out on deals from {short_name}",
"Don't miss out on the great deals from {short_name} {daily_deal_signup}",
"Don't miss out on the great deals from {short_name} {daily_deal_signup}",
'2031-12-31', 1, 1, (select email_design from email_design where name = 'Newsletter'), 0, 0, (SELECT offer_type FROM offer_type WHERE offer_base='dailydeal' AND org = 2850 LIMIT 1), 1, 0);
INSERT INTO email_marketing (type, id, org, active, name, subject, headline, narrative, body, send_on, email_cycle, show_event, email_design, client_type, marketing_list, offer, push_type, editor) VALUES (2, null, 2850, 1, "Daily Deal Signup", "Don't miss the deals from {short_name}", "Don't miss out on deals from {short_name}",
"Don't miss out on the great deals from {short_name} {daily_deal_signup}",
"Don't miss out on the great deals from {short_name} {daily_deal_signup}",
'2031-12-31', 1, 1, (select email_design from email_design where name = 'Newsletter'), 0, 0, (SELECT offer_type FROM offer_type WHERE offer_base='dailydeal' AND org = 3 LIMIT 1), 1, 0);

# update 01/24/10

ALTER TABLE event ADD COLUMN ref_code varchar(20) default NULL;

CREATE TABLE `gift_event` (
  `gift` int(11) NOT NULL default '0',
  `event_atom` int(11) NOT NULL default '0',
  `primary_event` int(1) default NULL,
  PRIMARY KEY (`gift`, `event_atom`)
) ENGINE=InnoDB;

# update 02/04/10

ALTER TABLE brand ADD COLUMN short_host varchar(50);
UPDATE brand SET short_host='dine.vu' WHERE org = 2646;
UPDATE brand SET short_host='spa.vu' WHERE org <> 2646;

CREATE TABLE `short_url` (
  `short_url` varchar(6) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  `org` int(11) NOT NULL,
  `url` varchar(100) NOT NULL,
  `added` datetime DEFAULT NULL,
  `first_hit` timestamp NULL DEFAULT NULL,
  `last_hit` timestamp NULL DEFAULT NULL,
  `hit_count` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`short_url`),
  KEY `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `queue_social` (
  `queue_social` 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,
  `event` int(11) DEFAULT NULL,
  `sequence` tinyint(4) DEFAULT NULL,
  `editor` tinyint(4) NOT NULL DEFAULT '0',
  `content` blob,
  `result` varchar(100) DEFAULT NULL,
  `short_url` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`queue_social`),
  KEY `org` (`org`),
  CONSTRAINT `queue_social_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 02/16/10

ALTER TABLE `org_payment` ADD  `org_payment_amazon` INT NULL DEFAULT NULL,
  ADD  `notes` VARCHAR( 40 ) NULL DEFAULT NULL,
  ADD INDEX ( `org_payment_amazon` );

CREATE TABLE `org_payment_amazon` (
  `org_payment_amazon` int(11) NOT NULL auto_increment,
  `entered_date` datetime NOT NULL,
  `sender` tinyint(1) NOT NULL,
  `caller_reference` varchar(64) NOT NULL,
  `transaction_status` tinyint(1) NOT NULL default '0',
  `transaction_id` varchar(40) default NULL,
  PRIMARY KEY  (`org_payment_amazon`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 03/03/10

ALTER TABLE queue_social MODIFY short_url varchar(6) CHARACTER SET latin1 COLLATE latin1_general_cs;
ALTER TABLE short_url ADD COLUMN source tinyint;
ALTER TABLE short_url ADD COLUMN type tinyint;
ALTER TABLE short_url ADD COLUMN atom int;
UPDATE short_url SET source=1, type=1, atom = (SELECT event FROM queue_social WHERE short_url.short_url=queue_social.short_url);

# update 03/04/10

ALTER TABLE queue_social ADD COLUMN headline varchar(100) AFTER content;

# update 03/23/10

ALTER TABLE queue_social CHANGE headline headline varchar(140);

# update 03/29/10

CREATE TABLE `social_media_log` (
  `social_media_log` int(11) NOT NULL AUTO_INCREMENT,
  `org` int(11) NOT NULL,
  `entered_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip` int(11) unsigned DEFAULT NULL,
  `type` tinyint(4) NOT NULL,
  `gift` int(11) DEFAULT NULL,
  `gift_promo` int(11) DEFAULT NULL,
  `offer` int(11) DEFAULT NULL,
  `result` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`social_media_log`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 04/05/10 DWS

CREATE TABLE `referral_code` (
  `referral_code` int(11) NOT NULL auto_increment,
  `ref_code` varchar(20) NOT NULL default '0',
  `org` int(11) NOT NULL default '0',
  `type` int(1) NOT NULL default '0',
  `event` int(11) default NULL,
  `value` int(3) default '0',
  `discount` tinyint(3) default '0',
  `discount_desc` varchar(100) default NULL,
  `used` int(3) default '0',
  `code_limit` int(3) default '-1',
  `end` date NOT NULL DEFAULT '0000-00-00',
  `hide` tinyint(1) default '0',
  PRIMARY KEY  (`referral_code`),
  UNIQUE (`ref_code`, `org`),
  CONSTRAINT FOREIGN KEY (`event`) REFERENCES `event` (`event`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `gift`
    ADD COLUMN `referral_code` int(11) default NULL,
    ADD CONSTRAINT FOREIGN KEY (`referral_code`) REFERENCES `referral_code` (`referral_code`);

INSERT INTO referral_code (referral_code.ref_code, referral_code.org, referral_code.event, referral_code.end, referral_code.hide) SELECT event.ref_code, event.org, event.event, event.end, event.hide FROM event WHERE event.ref_code IS NOT NULL;
ALTER TABLE event DROP COLUMN ref_code;

# update 04/12/10 ZAW

ALTER TABLE  `org_tran` CHANGE  `client_help`  `client_help` INT( 11 ) NULL DEFAULT NULL ,
  CHANGE  `trans_id`  `trans_id` VARCHAR( 40 ) NULL DEFAULT NULL ,
  CHANGE  `correlation_id`  `correlation_id` VARCHAR( 64 ) NULL DEFAULT NULL,
  ADD  `fps_caller` TINYINT( 1 ) NOT NULL DEFAULT  '0' AFTER  `correlation_id`;
ALTER TABLE  `org_payment_amazon` CHANGE  `sender`  `fps_caller` TINYINT( 1 ) NOT NULL DEFAULT  '0';

# update 04/14/10 BEM

ALTER TABLE referral_code ADD COLUMN notes varchar(100) DEFAULT NULL;

# update 04/20/10 ZAW

UPDATE prof SET can_gc = can_gc + 1 WHERE can_gc >= 2;

# update 04/20/10 WFB

INSERT INTO part (org, part_id, content) VALUES (3, 'email_manage', "<h1>Subscriptions</h1><p>Here's your subscriptions for {client_email}:</p>");
INSERT INTO part (org, part_id, content) VALUES (3, 'email_manageopt', "<h1>Optional Information</h1><p>Please tell us a little bit about yourself:</p>");
INSERT INTO part (org, part_id, content) VALUES (3, 'email_nomanage', "<p>There are no settings that need to be adjusted.</p>");
INSERT INTO part (org, part_id, content) VALUES (3, 'email_managecomp', "<h1>Thank you!</h1><p>Your changes have been saved.</p>");
INSERT INTO part (org, part_id, content) VALUES (3, 'email_notfound', "<h1>Subscriptions</h1><p>I'm sorry, but I couldn't find that email address.</p>");

INSERT INTO part (org, part_id, content) VALUES (2850, 'email_manage', "<h1>Subscriptions</h1><p>Here's your subscriptions for {client_email}:</p>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'email_manageopt', "<h1>Optional Information</h1><p>Please tell us a little bit about yourself:</p>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'email_nomanage', "<p>There are no settings that need to be adjusted.</p>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'email_managecomp', "<h1>Thank you!</h1><p>Your changes have been saved.</p>");
INSERT INTO part (org, part_id, content) VALUES (2850, 'email_notfound', "<h1>Subscriptions</h1><p>I'm sorry, but I couldn't find that email address.</p>");

# update 04/21/10 DWS

CREATE TABLE `org_temp` (`org` INT NOT NULL ,`amazon_billing_calc` INT NOT NULL,
PRIMARY KEY (`org`), INDEX (`amazon_billing_calc`)) ENGINE = INNODB;

ALTER TABLE `org_temp` ADD FOREIGN KEY (`org`) REFERENCES `org` (`org`);
ALTER TABLE `org_temp` ADD FOREIGN KEY (`amazon_billing_calc`) REFERENCES `billing_calc` (`billing_calc`);

ALTER TABLE  `org_amazon` ADD  `verified` TINYINT( 1 ) NOT NULL;

# update 05/01/10 BEM

ALTER TABLE queue_social CHANGE headline headline blob;

# update 05/03/10 DWS

ALTER TABLE review ADD COLUMN private tinyint NOT NULL DEFAULT 0, ADD COLUMN response text;

# update 05/12/10 DWS

ALTER TABLE `email_marketing` CHANGE `client_type` `client_type` VARCHAR( 100 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '0';

# update 06/11/10 WFB

ALTER TABLE queue_social ADD COLUMN photo int(11);
ALTER TABLE queue_social ADD COLUMN photo_filename varchar(100);
ALTER TABLE queue_social ADD COLUMN temp_link varchar(100);

# update 06/09/10 DWS

ALTER TABLE org_client ADD COLUMN review_opt_in tinyint NOT NULL DEFAULT 1;

INSERT INTO `email_marketing` VALUES (NULL,0,1,'reviewremind',3,1,'Review Reminder','Tell us how we did','Tell us how we did',
'Hi {name},\r\n\r\nThank you for your recent visit. We value your opinion, and would love to hear about your experience or receive other comments {review_link}. \r\n\r\nWe continually review our overall guest experience and our services to provide the best experience for you. \r\n\r\nWe look forward to seeing you again soon!\r\n\r\nThank you,\r\n{short_name}\r\n\r\n\r\n\r\nIf you no longer wish to be reminded of review opportunities, you can opt out {review_unsubscribe}.',
'Hi {name},\r\n\r\nThank you for your recent visit. We value your opinion, and would love to hear about your experience or receive other comments {review_link}. \r\n\r\nWe continually review our overall guest experience and our services to provide the best experience for you. \r\n\r\nWe look forward to seeing you again soon!\r\n\r\nThank you,\r\n{short_name}\r\n\r\n\r\n\r\nIf you no longer wish to be reminded of review opportunities, you can opt out {review_unsubscribe}.',
NULL,0,0,0,4,'0','0',0,'ALL','NONE',0,0,'',0,0,0);

INSERT INTO part (org, part_id, content) VALUES (3,'review_unsubcomp', "<p>Thank you.</p><p>You have been removed from our review reminder mailing list, and we won't ask you again in the future.</p>");

CREATE TABLE org_setting (org int(11) NOT NULL PRIMARY KEY, review_remind_client tinyint NOT NULL DEFAULT 1, fb_share_gc tinyint NOT NULL DEFAULT 1, fb_share_event tinyint NOT NULL DEFAULT 1) ENGINE=innodb;
INSERT INTO org_setting (org, review_remind_client, fb_share_gc, fb_share_event) SELECT org, 1, 1, 1 FROM org;
UPDATE org_setting SET fb_share_gc = 0 WHERE org IN (SELECT org FROM org_parm WHERE type = 'Facebook' AND parm='share_gc' AND value='0');
UPDATE org_setting SET fb_share_event = 0 WHERE org IN (SELECT org FROM org_parm WHERE type = 'Facebook' AND parm='share_event' AND value=0);
DELETE FROM org_parm WHERE type='Facebook' AND parm='share_gc';
ALTER TABLE social_media_log ADD COLUMN event int(11) AFTER offer;

*** PLEASE ADD YOUR INITIALS AFTER THE DATE ***

# update 06/20/10 DWS

ALTER TABLE org_setting ADD COLUMN fb_share_article tinyint NOT NULL DEFAULT 1 AFTER fb_share_event;
ALTER TABLE website ADD COLUMN meta_insights varchar(50);

# update 06/29/10 DWS

ALTER TABLE social_media_log ADD COLUMN article int(11) AFTER event;

# update 07/01/10 DWS

ALTER TABLE queue_social ADD COLUMN message_id varchar(50);

# update 07/06/10 DWS

ALTER TABLE event ADD COLUMN permalink varchar(100);
ALTER TABLE event ADD INDEX permliank (permalink);

# update 07/12/10 DWS

ALTER TABLE org ADD COLUMN service_level tinyint NOT NULL after service_website;
ALTER TABLE org ADD COLUMN service_custom tinyint not null AFTER service_level;

# update 07/15/10 WFB

UPDATE org SET service_level = 2;
UPDATE org SET service_level = 0 WHERE service_gc = 0;
UPDATE org, org_client SET service_level = 1 WHERE org.client = org_client.client AND org_client.org = 409;
UPDATE org SET service_gc = 1 WHERE service_gc = 2 AND service_website = 0;
UPDATE org SET service_level = 2 WHERE service_website = 2 AND service_level = 1 AND service_custom = 0;

# update 08/01/10 WFB

ALTER TABLE  `billing_tran` CHANGE  `client_help`  `client_help` INT( 11 ) NULL DEFAULT NULL;

INSERT INTO proc_reason VALUES (0, 1, 0, 'Fatal Error', NULL);
UPDATE proc_reason SET proc_reason = 0 WHERE proc_reason.desc = 'Fatal Error' LIMIT 1;

# update 08/06/10 BEM

ALTER TABLE `brand` CHANGE `signup_fee` `integrated_fee` INT( 11 ) NOT NULL DEFAULT '0',
CHANGE `website_fee` `theme_fee` INT( 11 ) NOT NULL DEFAULT '0',
CHANGE `directory_fee` `custom_fee` INT( 11 ) NOT NULL DEFAULT '0';
ALTER TABLE `brand` ADD `igc_only_fee` INT( 11 ) NOT NULL AFTER `default_org`;

ALTER TABLE `promo` CHANGE `signup_fee` `integrated_fee` INT( 11 ) NULL DEFAULT NULL ,
CHANGE `website_fee` `theme_fee` INT( 11 ) NULL DEFAULT NULL ,
CHANGE `directory_fee` `custom_fee` INT( 11 ) NULL DEFAULT NULL;
ALTER TABLE `promo` ADD `igc_only_fee` INT( 11 ) NULL DEFAULT NULL AFTER `code`;

UPDATE brand SET igc_only_fee = 99, integrated_fee = 299, theme_fee = 299, custom_fee = 999;

UPDATE promo SET custom_fee = theme_fee;
UPDATE promo SET theme_fee = integrated_fee;
UPDATE promo SET igc_only_fee = 59;

UPDATE promo SET good_thru = "2030-12-31" WHERE code = "__SOLO__";

UPDATE promo SET igc_only_fee = 29, integrated_fee = 299, theme_fee = 29, custom_fee = 999 WHERE promo = 4;

ALTER TABLE `email_marketing` CHANGE `client_type` `client_type` VARCHAR( 200 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '0';
UPDATE brand SET brand_can_website = 1, brand_email_marketing = 1 WHERE org = 409;

ALTER TABLE event ADD COLUMN static tinyint NOT NULL DEFAULT 0;

# update 08/08/10 WFB

ALTER TABLE event ADD COLUMN embed text;
ALTER TABLE event ADD COLUMN embed_anchor_text varchar(50);

# update 08/10/10 DWS

ALTER TABLE `org` ADD `upgrade_date` DATETIME NULL DEFAULT NULL AFTER `entered_date`;

# update 08/13/10 WFB

CREATE TABLE `sevence`.`state_exp` (
`state_exp` VARCHAR( 2 ) NOT NULL ,
`exp` TINYINT( 1 ) NULL DEFAULT NULL ,
PRIMARY KEY ( `state_exp` )
) ENGINE = InnoDB;
INSERT INTO state_exp VALUES ('AR', 24), ('CA', 0), ('CT', 0), ('HI', 24), ('IL', 60), ('KS', 60), ('KY', 12), ('LA', 61), ('ME', 0), ('MD', 49),
('MA', 84), ('MI', 60), ('MN', 0), ('MT', 0), ('NH', 0), ('NJ', 24), ('NM', 60), ('ND', 73), ('OH', 24), ('OK', 24), ('OR', 0), ('RI', 0), ('SC', 12),
('TN', 24), ('VT', 36), ('WA', 0);
INSERT INTO state_exp VALUES ('FL', 0);
UPDATE state_exp SET exp = 60 WHERE state_exp = 'OH';

ALTER TABLE `gift_type` ADD `exp_mo_dollar` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `sold_via` ,
ADD `exp_mo_service` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `exp_mo_dollar` ,
ADD `exp_mo_free` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `exp_mo_service` ,
ADD `exp_mo_discount` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `exp_mo_free`;
ALTER TABLE `gift_type` ADD `exp_mo_service_valid` TINYINT( 1 ) NULL DEFAULT NULL AFTER `exp_mo_service`;
ALTER TABLE `gift_type` CHANGE `exp_mo_service_valid` `exp_mo_service_valid` TINYINT( 1 ) NOT NULL DEFAULT '0';
UPDATE gift_type SET exp_mo_dollar = expiration_length, exp_mo_service = expiration_length, exp_mo_service_valid = expiration_length,
exp_mo_discount = expiration_length, exp_mo_free = expiration_length;

ALTER TABLE `gift` ADD `exp_date` DATE NULL DEFAULT NULL AFTER `issue_date` ,
ADD `exp_discount_date` DATE NULL DEFAULT NULL AFTER `exp_date`;
UPDATE gift, gift_type SET exp_date = DATE_ADD(COALESCE(issue_date, purchase_date), INTERVAL expiration_length MONTH) WHERE gift.gift_type = gift_type.gift_type;

# update 08/21/10 WFB

ALTER TABLE `brand` ADD `billing_calc_igc_only` INT( 11 ) NULL DEFAULT NULL AFTER `custom_fee` ,
ADD `billing_calc_suite` INT( 11 ) NULL DEFAULT NULL AFTER `billing_calc_igc_only` ,
ADD `billing_calc_suite_mo` INT( 11 ) NULL DEFAULT NULL AFTER `billing_calc_suite` ,
ADD `billing_calc_solo` INT( 11 ) NULL AFTER `billing_calc_suite_mo` ,
ADD `billing_calc_solo_mo` INT( 11 ) NULL AFTER `billing_calc_solo` ;

# update 08/24/10 WFB

UPDATE email_marketing SET narrative=REPLACE(narrative, 'Hi {name},' , 'Hello from {short_name},'), body=REPLACE(body, 'Hi {name},', 'Hello from {short_name},') WHERE id='reviewremind';
CREATE TABLE `email_opt_out` (
  `email_opt_out` int(11) NOT NULL AUTO_INCREMENT,
  `org` int(11) NOT NULL,
  `email_recipient` varchar(100) NOT NULL,
  `type` tinyint(4) NOT NULL DEFAULT '0',
  `ip` int(11) NOT NULL,
  `entered_date` datetime NOT NULL,
  PRIMARY KEY (`email_opt_out`),
  KEY `org` (`org`),
  CONSTRAINT `email_opt_out_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB;

ALTER TABLE gift ADD COLUMN review_remind DATETIME;

# update 08/30/10 DWS

CREATE TABLE `article_comment` (
  `article_comment` int(11) NOT NULL AUTO_INCREMENT,
  `article` int(11) NOT NULL,
  `org_client` int(11) NOT NULL,
  `comment_prof` int(11) DEFAULT NULL,
  `comment_date` datetime DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `comment` text NOT NULL,
  `ip` int(11) unsigned DEFAULT NULL,
  `action_date` datetime DEFAULT NULL,
  `prof` int(11) DEFAULT NULL,
  PRIMARY KEY (`article_comment`),
  KEY `article` (`article`),
  CONSTRAINT `article_comment_ibfk_1` FOREIGN KEY (`article`) REFERENCES `article` (`article`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE article_setting ADD COLUMN allow_comments tinyint NOT NULL DEFAULT 1;
ALTER TABLE article_setting ADD COLUMN initial_comments tinyint NOT NULL DEFAULT 0;
ALTER TABLE article_setting ADD COLUMN mail_comment tinyint NOT NULL DEFAULT 1;
ALTER TABLE org_setting ADD COLUMN fb_like_article tinyint NOT NULL DEFAULT 1;
UPDATE org_setting SET fb_like_article=0 WHERE fb_share_article=0;
INSERT INTO article_setting (org) SELECT org FROM page WHERE page_id='blog' AND version=0 AND NOT EXISTS (SELECT * FROM article_setting WHERE article_setting.org=page.org);

# update 09/13/10 DWS

ALTER TABLE `email_queue` ADD INDEX `priority` (`priority`,`entered_date`);
ALTER TABLE `email_queue` ADD INDEX `priority_group` (`priority`,`smtp_source`,`email_subject`);
ALTER TABLE `email_queue` ADD INDEX `next_attempt` (`next_attempt`,`email_subject`);

ALTER TABLE `email_queue_failed` ADD INDEX `priority` (`priority`,`entered_date`);
ALTER TABLE `email_queue_failed` ADD INDEX `priority_group` (`priority`,`smtp_source`,`email_subject`);
ALTER TABLE `email_queue_failed` ADD INDEX `next_attempt` (`next_attempt`,`email_subject`);

# update 10/01/10 BEM

ALTER TABLE `org_tran` ADD `capture_date` DATETIME NULL DEFAULT NULL AFTER `amount`,
  ADD `capture_amount` DECIMAL(12,2) NULL DEFAULT NULL AFTER `capture_date`;

# update 10/25/10 BEM

ALTER TABLE `email_color` ADD `highlight_2` VARCHAR( 7 ) NULL DEFAULT NULL;

# update 11/1?/10 WFB

# BoomTime Deals
ALTER TABLE `event` ADD `deal_value` DECIMAL( 12, 2 ) NULL DEFAULT NULL AFTER `offer` ,
ADD `deal_min` INT( 6 ) NULL DEFAULT NULL AFTER `deal_value` ,
ADD `deal_max` INT( 6 ) NULL DEFAULT NULL AFTER `deal_min` ,
ADD `deal_expiration` DATE NULL DEFAULT NULL AFTER `deal_max` ,
ADD `deal_legalese` TEXT NULL DEFAULT NULL AFTER `deal_expiration`;

ALTER TABLE `event` ADD `deal_2` TINYINT( 1 ) NULL DEFAULT NULL AFTER `deal_max` ,
ADD `deal_min_2` TINYINT( 1 ) NULL DEFAULT NULL AFTER `deal_2` ,
ADD `discount_2` TINYINT( 2 ) NULL DEFAULT NULL AFTER `deal_min_2`;

ALTER TABLE `event` CHANGE `deal_legalese` `deal_legalese` VARCHAR( 100 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

ALTER TABLE `event` ADD `photo` INT( 11 ) NULL DEFAULT NULL ,
ADD `photo_filename` VARCHAR( 100 ) NULL DEFAULT NULL;

ALTER TABLE client ADD COLUMN facebook_id BIGINT;
ALTER TABLE client ADD KEY (facebook_id);

# Pending Gift Tables
CREATE TABLE `gift_p` (
  `gift_p` int(11) NOT NULL AUTO_INCREMENT,
  `org_client` int(11) NOT NULL DEFAULT '0',
  `org_tran` int(11) DEFAULT NULL,
  `gift_type` int(11) NOT NULL DEFAULT '0',
  `purchase_date` datetime DEFAULT NULL,
  `exp_date` date DEFAULT NULL,
  `exp_discount_date` date DEFAULT NULL,
  `gift_id` varchar(20) DEFAULT NULL,
  `recipient_first` varchar(30) NOT NULL DEFAULT '',
  `recipient_last` varchar(30) NOT NULL DEFAULT '',
  `to` varchar(50) DEFAULT NULL,
  `from` varchar(50) DEFAULT NULL,
  `message` text,
  `note` text,
  `to_email` varchar(40) DEFAULT NULL,
  `email_when` date DEFAULT NULL,
  `delivery` tinyint(1) NOT NULL DEFAULT '0',
  `mail_status` tinyint(1) NOT NULL DEFAULT '0',
  `org_gift_design` int(11) DEFAULT NULL,
  `value_type` tinyint(1) NOT NULL DEFAULT '0',
  `sold_via` tinyint(11) NOT NULL DEFAULT '0',
  `entered_date` datetime DEFAULT '0000-00-00 00:00:00',
  `void` tinyint(1) DEFAULT '0',
  `vuid` varchar(32) DEFAULT NULL,
  `directory` int(11) DEFAULT NULL,
  `affiliate` int(11) DEFAULT NULL,
  `referring_org` int(11) DEFAULT NULL,
  `client_address` int(11) DEFAULT NULL,
  `referral_code` int(11) DEFAULT NULL,
  PRIMARY KEY (`gift_p`),
  KEY `orgclient` (`org_client`),
  KEY `gift_type` (`gift_type`),
  KEY `org_gift_design` (`org_gift_design`),
  KEY `org_tran` (`org_tran`),
  KEY `directory` (`directory`),
  KEY `affiliate` (`affiliate`),
  KEY `client_address` (`client_address`),
  KEY `referring_org` (`referring_org`),
  KEY `referral_code` (`referral_code`),
  CONSTRAINT `gift_p_ibfk_1` FOREIGN KEY (`org_client`) REFERENCES `org_client` (`org_client`),
  CONSTRAINT `gift_p_ibfk_2` FOREIGN KEY (`gift_type`) REFERENCES `gift_type` (`gift_type`),
  CONSTRAINT `gift_p_ibfk_3` FOREIGN KEY (`org_gift_design`) REFERENCES `org_gift_design` (`org_gift_design`),
  CONSTRAINT `gift_p_ibfk_4` FOREIGN KEY (`org_tran`) REFERENCES `org_tran` (`org_tran`),
  CONSTRAINT `gift_p_ibfk_5` FOREIGN KEY (`affiliate`) REFERENCES `affiliate` (`affiliate`),
  CONSTRAINT `gift_p_ibfk_6` FOREIGN KEY (`client_address`) REFERENCES `client_address` (`client_address`),
  CONSTRAINT `gift_p_ibfk_7` FOREIGN KEY (`referring_org`) REFERENCES `org` (`org`),
  CONSTRAINT `gift_p_ibfk_8` FOREIGN KEY (`referral_code`) REFERENCES `referral_code` (`referral_code`)
) ENGINE=InnoDB;

CREATE TABLE `gift_amount_p` (
  `gift_amount_p` int(11) NOT NULL AUTO_INCREMENT,
  `gift_p` int(11) NOT NULL DEFAULT '0',
  `transaction_date` datetime DEFAULT '0000-00-00 00:00:00',
  `transaction_type` tinyint(11) NOT NULL DEFAULT '0',
  `adj` tinyint(1) NOT NULL DEFAULT '0',
  `value` decimal(12,2) DEFAULT NULL,
  `paid` decimal(12,2) DEFAULT NULL,
  `tax` decimal(12,2) NOT NULL DEFAULT '0.00',
  `handling` decimal(12,2) NOT NULL DEFAULT '0.00',
  `gratuity` decimal(12,2) NOT NULL DEFAULT '0.00',
  `currency` tinyint(1) NOT NULL,
  `billing_amount` decimal(12,2) NOT NULL,
  `point` int(10) DEFAULT NULL,
  `comment` varchar(50) DEFAULT NULL,
  `prof` int(11) DEFAULT NULL,
  `entered_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `billing_item` int(11) DEFAULT NULL,
  `affiliate_tran` int(11) DEFAULT NULL,
  `member_paid` tinyint(1) NOT NULL DEFAULT '0',
  `location` varchar(16) DEFAULT NULL,
  `sequence` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`gift_amount_p`),
  KEY `gift_p` (`gift_p`),
  KEY `prof` (`prof`),
  KEY `billing_item` (`billing_item`),
  KEY `affiliate_tran` (`affiliate_tran`),
  CONSTRAINT `gift_amount_p_ibfk_1` FOREIGN KEY (`prof`) REFERENCES `prof` (`prof`),
  CONSTRAINT `gift_amount_p_ibfk_2` FOREIGN KEY (`billing_item`) REFERENCES `billing_item` (`billing_item`),
  CONSTRAINT `gift_amount_p_ibfk_3` FOREIGN KEY (`affiliate_tran`) REFERENCES `affiliate_tran` (`affiliate_tran`),
  CONSTRAINT `gift_amount_p_ibfk_4` FOREIGN KEY (`gift_p`) REFERENCES `gift_p` (`gift_p`)
) ENGINE=InnoDB;

CREATE TABLE `gift_amount_service_option_p` (
  `gift_amount_service_option_p` int(11) NOT NULL AUTO_INCREMENT,
  `value` decimal(12,2) NOT NULL DEFAULT '0.00',
  `tax` decimal(12,2) NOT NULL DEFAULT '0.00',
  `gift_amount_p` int(11) NOT NULL DEFAULT '0',
  `service_option` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`gift_amount_service_option_p`),
  KEY `gift_amount_p` (`gift_amount_p`),
  KEY `service_option` (`service_option`),
  CONSTRAINT `gift_amount_service_option_p_ibfk_1` FOREIGN KEY (`gift_amount_p`) REFERENCES `gift_amount_p` (`gift_amount_p`),
  CONSTRAINT `gift_amount_service_option_p_ibfk_2` FOREIGN KEY (`service_option`) REFERENCES `service_option` (`service_option`)
) ENGINE=InnoDB;

CREATE TABLE `gift_event_p` (
  `gift_p` int(11) NOT NULL DEFAULT '0',
  `event_atom` int(11) NOT NULL DEFAULT '0',
  `primary_event` int(1) DEFAULT NULL,
  PRIMARY KEY (`gift_p`,`event_atom`)
) ENGINE=InnoDB;

ALTER TABLE event ADD COLUMN deal_process tinyint NOT NULL DEFAULT 0;
ALTER TABLE event ADD INDEX (deal_process);
ALTER TABLE event ADD COLUMN org_gift_design int(11) AFTER deal_legalese;

ALTER TABLE email_marketing ADD COLUMN event INT AFTER offer;
ALTER TABLE email_marketing ADD key (event);

ALTER TABLE template ADD COLUMN background_photo tinyint NOT NULL DEFAULT 0, ADD COLUMN background_photo_css text;
ALTER TABLE website ADD COLUMN photo int, ADD COLUMN photo_filename VARCHAR(100);
ALTER TABLE website_backup ADD COLUMN widget_css TEXT, ADD COLUMN meta_insights VARCHAR(50), ADD COLUMN photo INT, ADD COLUMN photo_filename VARCHAR(100);

# update 11/20/2010 WFB

# Remove push from slideshow
UPDATE slideshow SET effect = 3 WHERE effect = 2;
# update 11/23/2010 WFB

ALTER TABLE website ADD COLUMN photo_width int NOT NULL DEFAULT 0, ADD COLUMN photo_height int NOT NULL DEFAULT 0;

# update 11/24/2010 WFB

ALTER TABLE `gift_p` ADD `issue_date` DATE NULL DEFAULT NULL AFTER `purchase_date`;

ALTER TABLE email_design ADD COLUMN default_design tinyint NOT NULL DEFAULT 0;
ALTER TABLE email_design ADD COLUMN default_email_design INT AFTER email_design;
ALTER TABLE email_design ADD INDEX (default_email_design);

ALTER TABLE email_marketing ADD COLUMN edit_date datetime NULL, ADD COLUMN edit_prof INT NULL;
ALTER TABLE email_queue ADD COLUMN org INT;
ALTER TABLE email_queue ADD INDEX (org);
ALTER TABLE email_queue_failed ADD COLUMN org INT;
ALTER TABLE email_queue_failed ADD INDEX (org);

# update 12/5/2010 WFB

ALTER TABLE `billing_calc` ADD `hide` TINYINT( 1 ) NOT NULL;
CREATE TABLE `sevence`.`token_type` (
`token_type` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`fee_percent` DECIMAL( 12, 2 ) NOT NULL
) ENGINE = InnoDB
ALTER TABLE `billing_calc` ADD `token_type` INT( 11 ) NULL AFTER `type` ,
ADD INDEX ( token_type ) ;
ALTER TABLE `billing_calc` ADD FOREIGN KEY ( `token_type` ) REFERENCES `sevence`.`token_type` (
`token_type`
);
ALTER TABLE org ADD COLUMN amazon_fps tinyint NOT NULL AFTER processing_cc_type;

# update 12/8/2010 WFB

ALTER TABLE `org_setting` ADD `pm_prof` INT( 11 ) NULL DEFAULT NULL ,
ADD INDEX ( pm_prof );

# update 12/10/2010 WFB

ALTER TABLE  `org_tran` ADD  `ipn_date` DATETIME NULL DEFAULT NULL AFTER  `capture_amount`,
  ADD  `sender_token` VARCHAR( 64 ) NULL DEFAULT NULL AFTER  `correlation_id`,
  ADD INDEX (  `org` ,  `correlation_id` );

# update 12/29/2010 BEM