# update MM/DD/2011 XXX

ALTER TABLE `org` DROP `sunday_open` ,
DROP `sunday_close` ,
DROP `monday_open` ,
DROP `monday_close` ,
DROP `tuesday_open` ,
DROP `tuesday_close` ,
DROP `wednesday_open` ,
DROP `wednesday_close` ,
DROP `thursday_open` ,
DROP `thursday_close` ,
DROP `friday_open` ,
DROP `friday_close` ,
DROP `saturday_open` ,
DROP `saturday_close` ;
ALTER TABLE `org` ADD `gift_receipt` TINYINT( 1 ) NOT NULL  DEFAULT '1' AFTER `gift_redeem_ucc` ,
ADD `gift_bcc` TINYINT( 1 ) NOT NULL  DEFAULT '0' AFTER `gift_receipt` ;

# update 01/09/2011 WFB

ALTER TABLE template_color ADD COLUMN status tinyint not null default 0, ADD COLUMN token int;

# update 01/24/2011 WFB

ALTER TABLE email_design ADD COLUMN restricted tinyint NOT NULL default 0;

ALTER TABLE org_type ADD COLUMN default_org INT AFTER org;

CREATE TABLE `event_org_client` (
  `event_org_client` int(11) NOT NULL AUTO_INCREMENT,
  `event` int(11) NOT NULL,
  `org_client` int(11) NOT NULL,
  `type` tinyint(4) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `entered_date` datetime DEFAULT NULL,
  `referred_org_client` int(11) NOT NULL,
  PRIMARY KEY (`event_org_client`),
  KEY `event` (`event`),
  KEY `org_client` (`org_client`),
  CONSTRAINT `event_org_client_ibfk_1` FOREIGN KEY (`event`) REFERENCES `event` (`event`),
  CONSTRAINT `event_org_client_ibfk_2` FOREIGN KEY (`org_client`) REFERENCES `org_client` (`org_client`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE event_org_client ADD COLUMN referral_code INT;
ALTER TABLE event CHANGE deal_process process tinyint NOT NULL DEFAULT 0;

# update 02/3/2011 WFB

ALTER TABLE resource ADD COLUMN abbr varchar(8) NULL DEFAULT '';

# update 02/17/2011 WFB

update email_marketing set default_email = null where default_email = 0;
update email_marketing a left outer join email_marketing b on a.default_email = b.email_marketing set a.default_email = NULL where a.default_email is not null and b.email_marketing is null;
update email_marketing a join email_marketing b on a.default_email = b.email_marketing set a.default_email = null where a.org = b.org;

# update 02/27/2011 BEM

ALTER TABLE `website` ADD `deal_bg` VARCHAR( 7 ) NULL DEFAULT NULL AFTER `hover_color` ,
ADD `deal_fg` VARCHAR( 7 ) NULL DEFAULT NULL AFTER `deal_bg`;

# update 03/10/2011 WFB

#email permalink
ALTER TABLE email_marketing ADD COLUMN permalink varchar(100);
ALTER TABLE email_marketing ADD KEY(org, permalink);

# update 03/13/2011 WFB

CREATE TABLE `sevence`.`headline_font` (
`headline_font` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL ,
`ref` VARCHAR( 50 ) NOT NULL
) ENGINE = InnoDB;
ALTER TABLE `sevence`.`headline_font` ADD INDEX ( `name` );
INSERT INTO headline_font VALUES (1, "Aclonica", "Aclonica");
INSERT INTO headline_font VALUES (2, "Allan", "Allan:bold");
INSERT INTO headline_font VALUES (3, "Allerta", "Allerta");
INSERT INTO headline_font VALUES (4, "Allerta Stencil", "Allerta+Stencil");
INSERT INTO headline_font VALUES (5, "Amaranth", "Amaranth");
INSERT INTO headline_font VALUES (6, "Annie Use Your Telescope", "Annie+Use+Your+Telescope");
INSERT INTO headline_font VALUES (7, "Anonymous Pro", "Anonymous+Pro");
INSERT INTO headline_font VALUES (8, "Anton", "Anton");
INSERT INTO headline_font VALUES (9, "Architects Daughter", "Architects+Daughter");
INSERT INTO headline_font VALUES (10, "Arimo", "Arimo");
INSERT INTO headline_font VALUES (11, "Arvo", "Arvo");
INSERT INTO headline_font VALUES (12, "Bangers", "Bangers");
INSERT INTO headline_font VALUES (13, "Bentham", "Bentham");
INSERT INTO headline_font VALUES (14, "Bevan", "Bevan");
INSERT INTO headline_font VALUES (15, "Bigshot One", "Bigshot+One");
INSERT INTO headline_font VALUES (17, "Buda", "Buda:light");
INSERT INTO headline_font VALUES (18, "Cabin", "Cabin");
INSERT INTO headline_font VALUES (19, "Cabin Sketch", "Cabin+Sketch:bold");
INSERT INTO headline_font VALUES (20, "Calligraffitti", "Calligraffitti");
INSERT INTO headline_font VALUES (21, "Candal", "Candal");
INSERT INTO headline_font VALUES (22, "Cantarell", "Cantarell");
INSERT INTO headline_font VALUES (23, "Cardo", "Cardo");
INSERT INTO headline_font VALUES (24, "Carter One", "Carter+One");
INSERT INTO headline_font VALUES (25, "Cherry Cream Soda", "Cherry+Cream+Soda");
INSERT INTO headline_font VALUES (26, "Chewy", "Chewy");
INSERT INTO headline_font VALUES (27, "Coda", "Coda:800");
INSERT INTO headline_font VALUES (28, "Coming Soon", "Coming+Soon");
INSERT INTO headline_font VALUES (29, "Copse", "Copse");
INSERT INTO headline_font VALUES (30, "Corben", "Corben:bold");
INSERT INTO headline_font VALUES (31, "Cousine", "Cousine");
INSERT INTO headline_font VALUES (32, "Covered By Your Grace", "Covered+By+Your+Grace");
INSERT INTO headline_font VALUES (33, "Crafty Girls", "Crafty+Girls");
INSERT INTO headline_font VALUES (34, "Crimson Text", "Crimson+Text");
INSERT INTO headline_font VALUES (35, "Cuprum", "Cuprum");
INSERT INTO headline_font VALUES (36, "Damion", "Damion");
INSERT INTO headline_font VALUES (37, "Dancing Script", "Dancing+Script");
INSERT INTO headline_font VALUES (38, "Dawning of a New Day", "Dawning+of+a+New+Day");
INSERT INTO headline_font VALUES (39, "Didact Gothic", "Didact+Gothic");
INSERT INTO headline_font VALUES (40, "Droid Sans", "Droid+Sans");
INSERT INTO headline_font VALUES (41, "EB Garamond", "EB+Garamond");
INSERT INTO headline_font VALUES (42, "Expletus Sans", "Expletus+Sans");
INSERT INTO headline_font VALUES (43, "Fontdiner Swanky", "Fontdiner+Swanky");
INSERT INTO headline_font VALUES (44, "Francois One", "Francois+One");
INSERT INTO headline_font VALUES (45, "Geo", "Geo");
INSERT INTO headline_font VALUES (46, "Goudy Bookletter 1911", "Goudy+Bookletter+1911");
INSERT INTO headline_font VALUES (47, "Gruppo", "Gruppo");
INSERT INTO headline_font VALUES (48, "Holtwood One SC", "Holtwood+One+SC");
INSERT INTO headline_font VALUES (49, "Homemade Apple", "Homemade+Apple");
INSERT INTO headline_font VALUES (50, "IM Fell Double Pica", "IM+Fell+Double+Pica");
INSERT INTO headline_font VALUES (51, "Inconsolata", "Inconsolata");
INSERT INTO headline_font VALUES (52, "Indie Flower", "Indie+Flower");
INSERT INTO headline_font VALUES (53, "Irish Grover", "Irish+Grover");
INSERT INTO headline_font VALUES (54, "Josefin Sans", "Josefin+Sans");
INSERT INTO headline_font VALUES (55, "Josefin Slab", "Josefin+Slab");
INSERT INTO headline_font VALUES (56, "Judson", "Judson");
INSERT INTO headline_font VALUES (57, "Just Another Hand", "Just+Another+Hand");
INSERT INTO headline_font VALUES (58, "Just Me Again Down Here", "Just+Me+Again+Down+Here");
INSERT INTO headline_font VALUES (59, "Kenia", "Kenia");
INSERT INTO headline_font VALUES (60, "Kranky", "Kranky");
INSERT INTO headline_font VALUES (61, "Kreon", "Kreon");
INSERT INTO headline_font VALUES (62, "Kristi", "Kristi");
INSERT INTO headline_font VALUES (63, "Lato", "Lato");
INSERT INTO headline_font VALUES (64, "League Script", "League+Script");
INSERT INTO headline_font VALUES (65, "Lekton", "Lekton");
INSERT INTO headline_font VALUES (66, "Lobster", "Lobster");
INSERT INTO headline_font VALUES (67, "Luckiest Guy", "Luckiest+Guy");
INSERT INTO headline_font VALUES (68, "Maiden Orange", "Maiden+Orange");
INSERT INTO headline_font VALUES (69, "Meddon", "Meddon");
INSERT INTO headline_font VALUES (70, "MedievalSharp", "MedievalSharp");
INSERT INTO headline_font VALUES (71, "Megrim", "Megrim");
INSERT INTO headline_font VALUES (72, "Merriweather", "Merriweather");
INSERT INTO headline_font VALUES (73, "Michroma", "Michroma");
INSERT INTO headline_font VALUES (74, "Miltonian Tattoo", "Miltonian+Tattoo");
INSERT INTO headline_font VALUES (75, "Molengo", "Molengo");
INSERT INTO headline_font VALUES (76, "Mountains of Christmas", "Mountains+of+Christmas");
INSERT INTO headline_font VALUES (77, "Neucha", "Neucha");
INSERT INTO headline_font VALUES (78, "Neuton", "Neuton");
INSERT INTO headline_font VALUES (79, "News Cycle", "News+Cycle");
INSERT INTO headline_font VALUES (80, "Nova Cut", "Nova+Cut");
INSERT INTO headline_font VALUES (81, "OFL Sorts Mill Goudy TT", "OFL+Sorts+Mill+Goudy+TT");
INSERT INTO headline_font VALUES (82, "Old Standard TT", "Old+Standard+TT");
INSERT INTO headline_font VALUES (83, "Orbitron", "Orbitron");
INSERT INTO headline_font VALUES (84, "Oswald", "Oswald");
INSERT INTO headline_font VALUES (85, "Over the Rainbow", "Over+the+Rainbow");
INSERT INTO headline_font VALUES (86, "PT Sans", "PT+Sans");
INSERT INTO headline_font VALUES (87, "PT Serif Caption", "PT+Serif+Caption");
INSERT INTO headline_font VALUES (88, "Pacifico", "Pacifico");
INSERT INTO headline_font VALUES (89, "Paytone One", "Paytone One");
INSERT INTO headline_font VALUES (90, "Permanent Marker", "Permanent+Marker");
INSERT INTO headline_font VALUES (91, "Philosopher", "Philosopher");
INSERT INTO headline_font VALUES (92, "Play", "Play");
INSERT INTO headline_font VALUES (93, "Puritan", "Puritan");
INSERT INTO headline_font VALUES (94, "Quattrocento", "Quattrocento");
INSERT INTO headline_font VALUES (95, "Quattrocento Sans", "Quattrocento+Sans");
INSERT INTO headline_font VALUES (96, "Radley", "Radley");
INSERT INTO headline_font VALUES (97, "Raleway", "Raleway:100");
INSERT INTO headline_font VALUES (98, "Reenie Beanie", "Reenie+Beanie");
INSERT INTO headline_font VALUES (99, "Rock Salt", "Rock+Salt");
INSERT INTO headline_font VALUES (100, "Rokkitt", "Rokkitt");
INSERT INTO headline_font VALUES (101, "Schoolbell", "Schoolbell");
INSERT INTO headline_font VALUES (102, "Sigmar One", "Sigmar+One");
INSERT INTO headline_font VALUES (103, "Six Caps", "Six+Caps");
INSERT INTO headline_font VALUES (104, "Slackey", "Slackey");
INSERT INTO headline_font VALUES (105, "Smythe", "Smythe");
INSERT INTO headline_font VALUES (106, "Sniglet", "Sniglet:800");
INSERT INTO headline_font VALUES (107, "Special Elite", "Special+Elite");
INSERT INTO headline_font VALUES (108, "Sue Ellen Francisco", "Sue+Ellen+Francisco");
INSERT INTO headline_font VALUES (109, "Sunshiney", "Sunshiney");
INSERT INTO headline_font VALUES (110, "Swanky and Moo Moo", "Swanky+and+Moo+Moo");
INSERT INTO headline_font VALUES (111, "Syncopate", "Syncopate");
INSERT INTO headline_font VALUES (112, "Tangerine", "Tangerine");
INSERT INTO headline_font VALUES (113, "Terminal Dosis Light", "Terminal+Dosis+Light");
INSERT INTO headline_font VALUES (114, "The Girl Next Door", "The+Girl+Next+Door");
INSERT INTO headline_font VALUES (115, "Tinos", "Tinos");
INSERT INTO headline_font VALUES (116, "Ubuntu", "Ubuntu");
INSERT INTO headline_font VALUES (117, "Ultra", "Ultra");
INSERT INTO headline_font VALUES (118, "Unkempt", "Unkempt");
INSERT INTO headline_font VALUES (119, "Vibur", "Vibur");
INSERT INTO headline_font VALUES (120, "Vollkorn", "Vollkorn");
INSERT INTO headline_font VALUES (121, "Waiting for the Sunrise", "Waiting+for+the+Sunrise");
INSERT INTO headline_font VALUES (122, "Wallpoet", "Wallpoet");
INSERT INTO headline_font VALUES (123, "Walter Turncoat", "Walter+Turncoat");
INSERT INTO headline_font VALUES (124, "Yanone Kaffeesatz", "Yanone+Kaffeesatz");
INSERT INTO `template` (`template`, `type`, `name`, `content`, `no_sidebar_css`, `tab`, `hide`, `home_page`, `background_photo`, `background_photo_css`) VALUES
(97, 3, 'Headline Font', 'h1, #content h1, #sidebar h1, #sidebar_2 h1, h2, #content h2, #sidebar h2, #sidebar_2 h2\r\n{\r\n  font-family: <?headline_font?>, <?font?>;\r\n}\r\n\r\n', NULL, 0, 0, 'home', 0, '');

ALTER TABLE `website` ADD `headline_font` INT( 11 ) NULL DEFAULT NULL AFTER `linked_html`;
ALTER TABLE `website_backup` ADD `headline_font` INT( 11 ) NULL DEFAULT NULL AFTER `linked_html`;

ALTER TABLE `email_design` ADD `hide` TINYINT( 1 ) NOT NULL ;

#sms
ALTER TABLE client_address ADD COLUMN phone_1_type tinyint AFTER phone_1, ADD COLUMN phone_2_type tinyint AFTER phone_2;
ALTER TABLE queue_social ADD COLUMN temp_type varchar(20) AFTER photo_filename, ADD COLUMN marketing_list VARCHAR(100), ADD COLUMN marketing_list_opt tinyint NOT NULL DEFAULT 0, ADD COLUMN sms_to varchar(14), ADD COLUMN org_client INT;
ALTER TABLE org_client ADD COLUMN sms_marketing tinyint NOT NULL DEFAULT 0, ADD COLUMN sms_confirm tinyint NOT NULL DEFAULT 0;
ALTER TABLE client_journal ADD COLUMN message_id varchar(100);
CREATE TABLE `twilio_log` (
  `twilio_log` int(11) NOT NULL AUTO_INCREMENT,
  `org` int(11) NOT NULL,
  `org_client` int(11) DEFAULT NULL,
  `log_date` datetime DEFAULT NULL,
  `type` tinyint(4) NOT NULL,
  `phone_to` varchar(20) DEFAULT NULL,
  `phone_from` varchar(20) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `message_id` varchar(50) DEFAULT NULL,
  `message` varchar(160) DEFAULT NULL,
  PRIMARY KEY (`twilio_log`),
  KEY `org` (`org`),
  CONSTRAINT `twilio_log_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `twilio_status` (
  `vuid` varchar(32) NOT NULL,
  `status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`vuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE queue_social ADD COLUMN sent_count int;

INSERT INTO part (org, part_id, content) VALUES (3, 'sms_appointment', '<br />Last-minute appointments via text message<br />');
ALTER TABLE website ADD COLUMN dialog_bg varchar(7) AFTER deal_fg, ADD COLUMN dialog_fg varchar(7) AFTER dialog_bg;

# include articles in email
ALTER TABLE article_setting ADD COLUMN email_display tinyint NOT NULL DEFAULT 1, ADD COLUMN days_back tinyint NOT NULL DEFAULT 30,
ADD COLUMN article_max tinyint NOT NULL DEFAULT 5;
ALTER TABLE email_marketing ADD COLUMN show_article tinyint NOT NULL DEFAULT 0 AFTER show_event, ADD COLUMN queued_on DATETIME AFTER email_cycle;
ALTER TABLE article ADD COLUMN email_marketing INT;
ALTER TABLE article ADD COLUMN email_include tinyint NOT NULL DEFAULT 1;
ALTER TABLE article_setting CHANGE days_back days_back tinyint NOT NULL DEFAULT 30;

# allow empty dates in event
ALTER TABLE event_service_option CHANGE start start date NULL default '0000-00-00';

# move dialog colors
ALTER TABLE color ADD COLUMN dialog_fg varchar(7) AFTER special, ADD COLUMN dialog_bg varchar(7) AFTER dialog_fg;
UPDATE color JOIN website ON color.org=website.org SET color.dialog_fg=website.dialog_fg, color.dialog_bg=website.dialog_bg WHERE color.org=website.org;
ALTER TABLE website DROP COLUMN dialog_fg, DROP COLUMN dialog_bg;

ALTER TABLE client_journal ADD INDEX (message_id);

# update 06/05/11 WFB

INSERT INTO part (org, part_id, content) VALUES (3, 'text_confirm', '<h1>Text Confirmation</h1><p>Thanks for subscribing to text messaging. We\'re big believers in your privacy, so we\'ve just sent you a text message to confirm your subscription.</p><p>Please reply to the message with a \'1\'. Otherwise, you\'ll miss out on great opportunities.</p>');
DELETE FROM part WHERE part_id = 'sms_appointment';
INSERT INTO part (org, part_id, content) VALUES (3, 'text_subscribe', '<p>For exclusive offers and appointment opportunities via text:</p>');
INSERT INTO part (org, part_id, content) VALUES (2850, 'text_subscribe', '<p>For exclusive offers via text:</p>');
INSERT INTO part (org, part_id, content) VALUES (967, 'text_subscribe', '<p>For exclusive offers via text:</p>');
UPDATE part SET content = "<h1>Special Offers</h1><p>Join our list for great specials and our latest events! We'll only use your information with your permission, and take your privacy seriously.</p>" WHERE org = 3 AND part_id = 'email_subscribe';
DELETE FROM part WHERE org = 2850 AND part_id = 'email_subscribe';
UPDATE part SET content = "Subscribe to:" WHERE org = 3 AND part_id = "ml_subscribe";
DELETE FROM part WHERE part_id='email_verfy_err';

ALTER TABLE prof ADD COLUMN phone_1_type tinyint AFTER phone_1, ADD COLUMN phone_2_type tinyint AFTER phone_2,ADD column send_sms tinyint NOT NULL DEFAULT 0;

ALTER TABLE `client_address` CHANGE `first_name` `first_name` VARCHAR( 30 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
CHANGE `last_name` `last_name` VARCHAR( 30 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
CHANGE `file_as` `file_as` VARCHAR( 50 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
UPDATE client_address SET file_as = NULL, last_name = NULL, first_name = NULL WHERE file_as = 'None' AND (last_name = '' OR last_name = 'None') AND
(first_name = '' OR first_name = 'None');
UPDATE client_address SET file_as = CASE WHEN COALESCE(first_name, '') > '' AND COALESCE(last_name, '') > '' THEN CONCAT(last_name, ', ', first_name)
WHEN COALESCE(last_name, '') > '' THEN last_name WHEN COALESCE(first_name, '') > '' THEN first_name WHEN COALESCE(company, '') > '' THEN company
WHEN COALESCE(email, '') > '' THEN email WHEN
COALESCE(phone_1, '') > '' THEN phone_1 WHEN COALESCE(phone_2, '') > '' THEN phone_2 ELSE 'None' END
WHERE COALESCE(file_as, '') = '';

# update 6/20/11 WFB

ALTER TABLE twilio_log ADD COLUMN action tinyint AFTER type;

# update 6/23/11 WFB

# rewards
CREATE TABLE `reward_point` (
  `reward_point` int(11) NOT NULL AUTO_INCREMENT,
  `org` int(11) NOT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `buy_gc` tinyint(4) NOT NULL DEFAULT '0',
  `buy_service` tinyint(4) NOT NULL DEFAULT '0',
  `buy_deal` tinyint(4) NOT NULL DEFAULT '0',
  `referral_gc` tinyint(4) NOT NULL DEFAULT '0',
  `referral_service` tinyint(4) NOT NULL DEFAULT '0',
  `referral_deal` tinyint(4) NOT NULL DEFAULT '0',
  `email_signup` tinyint(4) NOT NULL DEFAULT '0',
  `share_friend` tinyint(4) NOT NULL DEFAULT '0',
  `share_signup` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`reward_point`),
  KEY `org` (`org`),
  CONSTRAINT `reward_point_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB;
ALTER TABLE org_client ADD COLUMN referring_org_client int AFTER referral;
ALTER TABLE gift_amount ADD COLUMN reward_action varchar(20), ADD COLUMN reward_org_client int, ADD COLUMN reward_gift_amount int;
ALTER TABLE event_service_option ADD COLUMN offer_mult tinyint NOT NULL DEFAULT 1, ADD COLUMN offer_mult_desc varchar(50);
ALTER TABLE event ADD COLUMN reward_mult tinyint NOT NULL DEFAULT 1, ADD COLUMN reward_mult_desc varchar(50);
INSERT INTO org_parm (org, type, parm, value) VALUES (3, 'Rewards', 'program_name', 'Rewards');
INSERT INTO reward_point (org, email_signup, share_friend, share_signup) values (3, 10, 10, 10);
INSERT INTO reward_point (org, email_signup, share_friend, share_signup) values (2850, 10, 10, 10);

# add org_tran column to book_slot
ALTER TABLE book_slot ADD COLUMN org_tran int(11) NULL default NULL AFTER book;
ALTER TABLE book_slot ADD COLUMN show_cc date NOT NULL default '0000-00-00';

# add checkin column to book_slot
ALTER TABLE book_slot ADD COLUMN check_in datetime default NULL;

# add colors for booking
CREATE TABLE `book_color` (
	`book_color` int(11) NOT NULL auto_increment,
	`org` int(11) NOT NULL default '0',
	`book_css` text default NULL,
	`bg_title` varchar(7) default NULL,
	`bg_note` varchar(7) default NULL,
	`text` varchar(7) default NULL,
	`bg_title_checked_in` varchar(7) default NULL,
	`bg_note_checked_in` varchar(7) default NULL,
	`text_checked_in` varchar(7) default NULL,
	`bg_title_missing` varchar(7) default NULL,
	`bg_note_missing` varchar(7) default NULL,
	`text_missing` varchar(7) default NULL,
	`bg_title_paid` varchar(7) default NULL,
	`bg_note_paid` varchar(7) default NULL,
	`text_paid` varchar(7) default NULL,
	`bg_title_upcoming` varchar(7) default NULL,
	`bg_note_upcoming` varchar(7) default NULL,
	`text_upcoming` varchar(7) default NULL,
	PRIMARY KEY (`book_color`),
	KEY `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# book_design defaults
INSERT INTO `book_color` (`org`, `book_css`, `bg_title`, `bg_note`, `text`, `bg_title_checked_in`, `bg_note_checked_in`, `text_checked_in`, `bg_title_missing`, `bg_note_missing`, `text_missing`, `bg_title_paid`, `bg_note_paid`, `text_paid`, `bg_title_upcoming`, `bg_note_upcoming`, `text_upcoming`)
	VALUES (3, '
#book_grid .book_slot
{
	color: <?text?>;
}

#book_grid .book_slot .book_slot_title
{
	background: <?bg_title?>;
	border-bottom: 1px solid <?text?>;
}

#book_grid .book_slot .book_slot_note
{
	background: <?bg_note?>;
}

#book_grid .book_slot.checked_in
{
	color: <?text_checked_in?>;
}

#book_grid .book_slot.checked_in .book_slot_title
{
	background: <?bg_title_checked_in?>;
	border-bottom: 1px solid <?text_checked_in?>;
}

#book_grid .book_slot.checked_in .book_slot_note
{
	background: <?bg_note_checked_in?>;
}

#book_grid .book_slot.paid
{
	color: <?text_paid?>;
}

#book_grid .book_slot.paid .book_slot_title
{
	background: <?bg_title_paid?>;
	border-bottom: 1px solid <?text_paid?>;
}

#book_grid .book_slot.paid .book_slot_note
{
	background: <?bg_note_paid?>;
}

#book_grid .book_slot.missing
{
	color: <?text_missing?>;
}

#book_grid .book_slot.missing .book_slot_title
{
	background: <?bg_title_missing?>;
	border-bottom: 1px solid <?text_missing?>;
}

#book_grid .book_slot.missing .book_slot_note
{
	background: <?bg_note_missing?>;
}

#book_grid .book_slot.upcoming
{
	color: <?text_upcoming?>;
}

#book_grid .book_slot.upcoming .book_slot_title
{
	background: <?bg_title_upcoming?>;
	border-bottom: 1px solid <?text_upcoming?>;
}

#book_grid .book_slot.upcoming .book_slot_note
{
	background: <?bg_note_upcoming?>;
}
', '#27A7AD', '#34BAC0', '#FFFFFF', '#FBA600', '#FDBE2A', '#FFFFFF', '#C51800', '#D04D47', '#FFFFFF', '#46B56E', '#6DC38E', '#FFFFFF', '#C0BC24', '#E3DE4A', '#FFFFFF');


# update 07/18/11 WFB

ALTER TABLE `brand` ADD `white_label` TINYINT( 1 ) NOT NULL DEFAULT '0';

# update 07/26/11 WFB

INSERT INTO part (org, part_id, content) VALUES (3, 'signup_org_id', "Please choose a {short_name} ID for your business. A shortened version of the business name is usually good. It can be up to 16 letters and numbers, with no spaces or symbols. <span class='hotspot' name='hotspot' id='hotspot'>How is it used?</span><span class='hotspot_content' name='hotspot_content' id='hotspot_content'> We'll use your {short_name} ID to make the website address for your secure e-commerce pages.</span>");

# update 07/27/11 WFB

ALTER TABLE `billing_tran` ADD `brand_org` INT NULL DEFAULT NULL AFTER `processing`, ADD INDEX ( `brand_org` );
ALTER TABLE  `billing_tran` ADD FOREIGN KEY (  `brand_org` ) REFERENCES  `sevence`.`brand` (`org`);
ALTER TABLE `billing_signup_tran` ADD `brand_org` INT NULL DEFAULT NULL AFTER `processing`, ADD INDEX ( `brand_org` );
ALTER TABLE  `billing_signup_tran` ADD FOREIGN KEY (  `brand_org` ) REFERENCES  `sevence`.`brand` (`org`);

# update 08/05/11 BEM

ALTER TABLE  `brand` ADD  `accounting_email` VARCHAR( 40 ) NULL DEFAULT NULL;
UPDATE  `sevence`.`brand` SET  `accounting_email` =  'accounting@boomtime.com' WHERE  `brand`.`brand` =1;
UPDATE  `sevence`.`brand` SET  `accounting_email` =  'accounting@spaboom.com' WHERE  `brand`.`brand` =2;
UPDATE  `sevence`.`brand` SET  `accounting_email` =  'accounting@coverboom.com' WHERE  `brand`.`brand` =3;
UPDATE  `sevence`.`brand` SET  `accounting_email` =  'accounting@spaboom.com' WHERE  `brand`.`brand` =4;
UPDATE  `sevence`.`brand` SET  `accounting_email` =  'accounting@spaboom.com' WHERE  `brand`.`brand` =5;
UPDATE  `sevence`.`brand` SET  `accounting_email` =  'onlineservices@printingforless.com' WHERE  `brand`.`brand` =6;

# update 08/06/11 BEM

ALTER TABLE `billing_calc` ADD `org` INT( 11 ) NULL DEFAULT NULL AFTER `billing_calc` ,
ADD INDEX ( org );
alter table brand drop foreign key brand_ibfk_2;
alter table brand drop foreign key brand_ibfk_3;
alter table brand drop foreign key brand_ibfk_4;
ALTER TABLE `brand` DROP INDEX `billing_calc_gc`;
ALTER TABLE `brand` DROP INDEX `billing_calc_website` ;
ALTER TABLE `brand` DROP INDEX `billing_calc_website_solo`;
ALTER TABLE `brand`
  DROP `billing_calc_gc`,
  DROP `billing_calc_website`,
  DROP `billing_calc_website_solo`;

# update 08/16/11 WFB

INSERT INTO headline_font VALUES (125, "Gentium Book Basic", "Gentium+Book+Basic");
INSERT INTO headline_font VALUES (126, "Short Stack", "Short+Stack");
INSERT INTO headline_font VALUES (127, "Days One", "Days+One");
INSERT INTO headline_font VALUES (128, "Vidaloka", "Vidaloka");
INSERT INTO headline_font VALUES (129, "Volkhov", "Volkhov");
INSERT INTO headline_font VALUES (130, "Numans", "Numans");
INSERT INTO headline_font VALUES (131, "Voltaire", "Voltaire");
INSERT INTO headline_font VALUES (132, "Alice", "Alice");
INSERT INTO headline_font VALUES (133, "Coustard", "Coustard");
INSERT INTO headline_font VALUES (134, "Andika", "Andika");
INSERT INTO headline_font VALUES (135, "Questrial", "Questrial");
INSERT INTO headline_font VALUES (136, "Comfortaa", "Comfortaa");
INSERT INTO headline_font VALUES (137, "Gloria Hallelujah", "Gloria+Hallelujah");
INSERT INTO headline_font VALUES (138, "Rochester", "Rochester");
INSERT INTO headline_font VALUES (139, "Delius Swash Caps", "Delius+Swash+Caps");
INSERT INTO headline_font VALUES (140, "Actor", "Actor");
INSERT INTO headline_font VALUES (141, "Abel", "Abel");
INSERT INTO headline_font VALUES (142, "Marvel", "Marvel");
INSERT INTO headline_font VALUES (143, "Bowlby One SC", "Bowlby+One+SC");
INSERT INTO headline_font VALUES (144, "Federo", "Federo");
INSERT INTO headline_font VALUES (145, "Gentium Basic", "Gentium+Basic");
INSERT INTO headline_font VALUES (146, "Modern Antiqua", "Modern+Antiqua");
INSERT INTO headline_font VALUES (147, "Snippet", "Snippet");
INSERT INTO headline_font VALUES (149, "Yellowtail", "Yellowtail");
INSERT INTO headline_font VALUES (150, "Redressed", "Redressed");
INSERT INTO headline_font VALUES (151, "Brawler", "Brawler");

# update 08/19/11 WFB

ALTER TABLE `page` ADD `canonical_link` VARCHAR( 100 ) NULL DEFAULT NULL;
ALTER TABLE `page_backup` ADD `canonical_link` VARCHAR( 100 ) NULL DEFAULT NULL;

# update 08/29/11 WFB (via BEM)

ALTER TABLE `brand` ADD `animated_fee` INT( 11 ) NOT NULL DEFAULT '0' AFTER `custom_fee`;
update brand set animated_fee = 2499;
ALTER TABLE `promo` ADD `animated_fee` INT( 11 ) NULL AFTER `custom_fee`;

# update 08/30/11 WFB

// Facebook Keys
// Execute on LOCAL DEVELOMENT ONLY
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Facebook', 'api_key', 'f2643b37a5abee00c1c2ee171b71f499');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Facebook', 'secret', '2f8423b85e005464032ce657764d94f3');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Facebook', 'app_id', '307254163844');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Twitter', 'consumer_key', '3oV4NxKU4IRxDnUIJzBwA');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Twitter', 'consumer_secret', 'FOwevKdKj0H07C4kMV6F5klh0HureNctqzhodRGY');

// Execute on TESTING ONLY
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Facebook', 'api_key', '20c4a76a4a5057a4a224e0cf9074a9f8');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Facebook', 'secret', '16caf5a2f93d3647c78686c602b0b07f');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Facebook', 'app_id', '337964302916');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Twitter', 'consumer_key', '3oV4NxKU4IRxDnUIJzBwA');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Twitter', 'consumer_secret', 'FOwevKdKj0H07C4kMV6F5klh0HureNctqzhodRGY');

// Execute on PRODUCTION ONLY
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Facebook', 'api_key', 'e5261b9af158f1478abbdb86057878df');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Facebook', 'secret', '4aa51b2864918119f15b2f62880c5cc6');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Facebook', 'app_id', '358768202592');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Twitter', 'consumer_key', '1JZQiiz3J1BSxlNkUFZjxw');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'Twitter', 'consumer_secret', 'TZPy1uhrrdu7cjoeiAmZFnnC5ldTCzO5cu9Zl8iE');

INSERT INTO org_parm (org, type, parm, value) VALUES (967, 'Facebook', 'api_key', 'caff3cc5a19a8ac1df0bdfc1ee2ddeba');
INSERT INTO org_parm (org, type, parm, value) VALUES (967, 'Facebook', 'secret', '8930a322b83020540ae374190454b065');
INSERT INTO org_parm (org, type, parm, value) VALUES (967, 'Facebook', 'app_id', '324971506625');
INSERT INTO org_parm (org, type, parm, value) VALUES (967, 'Twitter', 'consumer_key', '1JZQiiz3J1BSxlNkUFZjxw');
INSERT INTO org_parm (org, type, parm, value) VALUES (967, 'Twitter', 'consumer_secret', 'TZPy1uhrrdu7cjoeiAmZFnnC5ldTCzO5cu9Zl8iE');

INSERT INTO org_parm (org, type, parm, value) VALUES (2646, 'Facebook', 'api_key', '322bf61c1beb13a6f4c55ed976d4c234');
INSERT INTO org_parm (org, type, parm, value) VALUES (2646, 'Facebook', 'secret', '1155d4c88b1b239124e15dae04030a41');
INSERT INTO org_parm (org, type, parm, value) VALUES (2646, 'Facebook', 'app_id', '340803779925');
INSERT INTO org_parm (org, type, parm, value) VALUES (2646, 'Twitter', 'consumer_key', '7nE45paKFENwVBnZwCUbKg');
INSERT INTO org_parm (org, type, parm, value) VALUES (2646, 'Twitter', 'consumer_secret', 'BYfZ6xb5XVBGil0HzfXTeAmGc458tNJaYBsBKLUAgM');

INSERT INTO org_parm (org, type, parm, value) VALUES (7400, 'Facebook', 'secret', 'ccbb7b372ce95a7d855f6c0bbea93beb');
INSERT INTO org_parm (org, type, parm, value) VALUES (7400, 'Facebook', 'app_id', '233169363389393');
INSERT INTO org_parm (org, type, parm, value) VALUES (7400, 'Twitter', 'consumer_key', 'pcJdsrmK2Hdysjv6wQGhIw');
INSERT INTO org_parm (org, type, parm, value) VALUES (7400, 'Twitter', 'consumer_secret', 'jdVw9LcxWpbBwH399MeWwFpMkKykNIvUau0oA4ANU');

CREATE TABLE `event_org_client_request` (
  `event_org_client_request` int(11) NOT NULL AUTO_INCREMENT,
  `event` int(11) NOT NULL,
  `org_client` int(11) NOT NULL,
  `invite_date` datetime DEFAULT NULL,
  `request_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`event_org_client_request`),
  KEY `request_id` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 09/08/11 WFB

CREATE TABLE `org_tran_amazon_ipn` (
  `correlation_id` varchar(64) NOT NULL,
  PRIMARY KEY (`correlation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 09/18/11 BEM

ALTER TABLE event_org_client_request ADD COLUMN processed tinyint NOT NULL DEFAULT 0;

INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'FacebookSweepstakes', 'app_id', '151844824868722');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'FacebookSweepstakes', 'secret', 'de026a4c6797812e99dd09af8af0da36');
INSERT INTO org_parm (org, type, parm, value) VALUES (13, 'FacebookSweepstakes', 'canvas', 'boomtime_welcome');

INSERT INTO org_parm (org, type, parm, value) VALUES (967, 'FacebookSweepstakes', 'app_id', '151844824868722');
INSERT INTO org_parm (org, type, parm, value) VALUES (967, 'FacebookSweepstakes', 'secret', 'de026a4c6797812e99dd09af8af0da36');
INSERT INTO org_parm (org, type, parm, value) VALUES (967, 'FacebookSweepstakes', 'canvas', 'boomtime_welcome');

INSERT INTO org_parm (org, type, parm, value) VALUES (2646, 'FacebookSweepstakes', 'app_id', '151844824868722');
INSERT INTO org_parm (org, type, parm, value) VALUES (2646, 'FacebookSweepstakes', 'secret', 'de026a4c6797812e99dd09af8af0da36');
INSERT INTO org_parm (org, type, parm, value) VALUES (2646, 'FacebookSweepstakes', 'canvas', 'boomtime_welcome');

INSERT INTO org_parm (org, type, parm, value) VALUES (7400, 'FacebookSweepstakes', 'app_id', '151844824868722');
INSERT INTO org_parm (org, type, parm, value) VALUES (7400, 'FacebookSweepstakes', 'secret', 'de026a4c6797812e99dd09af8af0da36');
INSERT INTO org_parm (org, type, parm, value) VALUES (7400, 'FacebookSweepstakes', 'canvas', 'boomtime_welcome');

# update 09/21/11 DWS via BEM

ALTER TABLE website ADD COLUMN menu_display tinyint NOT NULL DEFAULT 0 AFTER service_display;
UPDATE website SET menu_display=service_display WHERE website.org IN (SELECT org.org FROM org, org_client, org AS brand_org, brand WHERE org.client = org_client.client AND org_client.org = brand_org.org AND brand_org.org = brand.org AND org.org=website.org AND brand_org.org <> 13);
UPDATE website SET menu_display=2 WHERE menu_display=6;
// UPDATE website SET service_display=0 WHERE website.org IN (SELECT org.org FROM org, org_client, org AS brand_org, brand WHERE org.client = org_client.client AND org_client.org = brand_org.org AND brand_org.org = brand.org AND org.org=website.org AND brand_org.org <> 13);

# update 10/30/11 DWS via BEM

ALTER TABLE short_url CHANGE url url varchar(255) NOT NULL;

# update 11/14/11 DWS via BEM

ALTER TABLE `brand` ADD `billing_calc_suite_intg_mo` INT( 11 ) NULL DEFAULT NULL AFTER `billing_calc_suite_mo`;
UPDATE brand SET integrated_fee = 199;

# update 11/20/11 WFB

ALTER TABLE `article` ADD `publish_on` DATETIME;

ALTER TABLE event ADD COLUMN sweepstakes_code varchar(20);
ALTER TABLE event_org_client ADD COLUMN gift_promo int, ADD COLUMN sweepstakes_code varchar(20);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (3, 'sweepstakes', 'Sweepstakes', 'sweepstakewinner', 0, 90, 600000, 1);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (967, 'sweepstakes', 'Sweepstakes', 'sweepstakewinner', 0, 90, 600000, 1);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (2850, 'sweepstakes', 'Sweepstakes', 'sweepstakewinner', 0, 90, 600000, 1);

CREATE TABLE `org_content` (
  `org` int(11) NOT NULL,
  `page_id` varchar(50) NOT NULL,
  `headline` text,
  `content` text NOT NULL,
  `hide` tinyint(4) NOT NULL DEFAULT '0',
  KEY `org` (`org`),
  CONSTRAINT `org_content_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `org_type_content` (
  `org_type` int(11) NOT NULL,
  `page_id` varchar(50) NOT NULL,
  `headline` text,
  `content` text NOT NULL,
  `hide` tinyint(4) NOT NULL DEFAULT '0',
  KEY `org_type` (`org_type`),
  CONSTRAINT `org_type_content_ibfk_1` FOREIGN KEY (`org_type`) REFERENCES `org_type` (`org_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 11/20/11 SLOW via BEM
