ALTER TABLE  `affiliate_org` CHANGE  `token_expires`  `token_expires` DATETIME NULL DEFAULT NULL;

# update 01/21/08

CREATE TABLE `website_backup` (
  `website_backup` int(11) NOT NULL auto_increment,
  `backup` int(11) NOT NULL default '0',
  `backup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `backup_type` int(11) NOT NULL default '0',
  `org` int(11) NOT NULL default '0',
  `service_display` tinyint(1) NOT NULL default '0',
  `site_template` int(11) default NULL,
  `previous_site_template` int(11) default NULL,
  `color` int(11) default NULL,
  `previous_color` int(11) default NULL,
  `website_css` text NOT NULL,
  `linked_template` int(11) default NULL,
  `linked_html` text NOT NULL,
  `font` varchar(50) NOT NULL default '',
  `linked_css` text NOT NULL,
  `background_color` varchar(7) NOT NULL default '',
  `heading_color` varchar(7) NOT NULL default '',
  `text_color` varchar(7) NOT NULL default '',
  `link_color` varchar(7) NOT NULL default '',
  `hover_color` varchar(7) NOT NULL default '',
  `width` smallint(4) NOT NULL default '0',
  `include_logo` tinyint(1) NOT NULL default '0',
  `logo_type` tinyint(1) NOT NULL default '0',
  `logo_text` text NOT NULL,
  `logo_align` tinyint(1) NOT NULL default '0',
  `logo_background_jpeg` tinyint(1) NOT NULL default '0',
  `logo_background` tinyint(1) NOT NULL default '0',
  `logo_background_website` tinyint(1) NOT NULL default '0',
  `logo_color` varchar(7) NOT NULL default '',
  `logo_background_color` varchar(7) NOT NULL default '',
  `logo_font` varchar(50) NOT NULL default '',
  `logo_font_size` tinyint(2) NOT NULL default '24',
  `include_home` tinyint(1) NOT NULL default '0',
  `meta_keyword` text NOT NULL,
  `meta_desc` text NOT NULL,
  `meta_gmap` text NOT NULL,
  `ping_gmap` datetime default NULL,
  PRIMARY KEY  (`website_backup`),
  KEY `org` (`org`),
  KEY `website_ibfk_2` (`linked_template`),
  KEY `website_ibfk_3` (`site_template`),
  KEY `website_ibfk_4` (`color`),
  KEY `backup` (`backup`),
  CONSTRAINT `website_backup_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`),
  CONSTRAINT `website_backup_ibfk_2` FOREIGN KEY (`linked_template`) REFERENCES `template` (`template`),
  CONSTRAINT `website_backup_ibfk_3` FOREIGN KEY (`site_template`) REFERENCES `template` (`template`),
  CONSTRAINT `website_backup_ibfk_4` FOREIGN KEY (`color`) REFERENCES `color` (`color`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `page_backup` (
  `page_backup` int(11) NOT NULL auto_increment,
  `backup` int(11) NOT NULL default '0',
  `backup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `backup_type` int(11) NOT NULL default '0',
  `page` int(11) NOT NULL default '0',
  `org` int(11) default NULL,
  `template` int(11) default NULL,
  `version` tinyint(1) NOT NULL default '0',
  `header_page` int(11) default NULL,
  `page_id` varchar(50) NOT NULL default '',
  `type` tinyint(1) NOT NULL default '0',
  `title` varchar(100) NOT NULL default '',
  `keyword` text,
  `content` text NOT NULL,
  `sidebar` text,
  `sidebar_2` text,
  `include_sidebar` tinyint(1) NOT NULL default '0',
  `include_sidebar_2` tinyint(1) NOT NULL default '0',
  `include_slideshow` tinyint(4) default '0',
  `custom_css` text,
  `tab_page` int(11) default NULL,
  `hide` tinyint(1) NOT NULL default '0',
  `sitemap` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`page_backup`),
  KEY `backup` (`backup`),
  KEY `org` (`org`,`version`,`page_backup`),
  KEY `header_page` (`header_page`),
  KEY `template` (`template`),
  CONSTRAINT `page_backup_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`),
  CONSTRAINT `page_backup_ibfk_2` FOREIGN KEY (`template`) REFERENCES `template` (`template`),
  CONSTRAINT `page_backup_ibfk_3` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `page_nav_backup` (
  `page_nav_backup` int(11) NOT NULL auto_increment,
  `backup` int(11) NOT NULL default '0',
  `backup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `backup_type` int(11) NOT NULL default '0',
  `org` int(11) default NULL,
  `template` int(11) default NULL,
  `version` tinyint(4) NOT NULL default '0',
  `nav_id` varchar(16) NOT NULL default 'default',
  `page` int(11) default NULL,
  `name` varchar(50) NOT NULL default '',
  `url` text NOT NULL,
  `title` text NOT NULL,
  `new_window` tinyint(4) NOT NULL default '0',
  `sort` decimal(12,8) NOT NULL default '0.00000000',
  PRIMARY KEY  (`page_nav_backup`),
  KEY `backup` (`backup`),
  KEY `org` (`org`),
  KEY `page_nav_ibfk_3` (`template`),
  CONSTRAINT `page_nav_backup_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`),
  CONSTRAINT `page_nav_backup_ibfk_2` FOREIGN KEY (`template`) REFERENCES `template` (`template`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `part_backup` (
  `part_backup` int(11) NOT NULL auto_increment,
  `backup` int(11) NOT NULL default '0',
  `backup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `backup_type` int(11) NOT NULL default '0',
  `org` int(11) NOT NULL default '0',
  `part_id` varchar(16) NOT NULL default '',
  `content` text NOT NULL,
  PRIMARY KEY  (`part_backup`),
  KEY `backup` (`backup`),
  UNIQUE KEY `org` (`org`,`part_id`),
  CONSTRAINT `part_backup_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 01/24/08

ALTER TABLE part_backup DROP FOREIGN KEY part_backup_ibfk_1;
ALTER TABLE part_backup DROP KEY org;
ALTER TABLE part_backup ADD KEY org (backup, org, part_id);
ALTER TABLE part_backup ADD FOREIGN KEY part_backup_ibfk_1 (org) REFERENCES org (org);

# update 02/01/08

ALTER TABLE  `affiliate` ADD  `is_directory` TINYINT( 1 ) NOT NULL DEFAULT  '0';

# update 02/11/08

ALTER TABLE  `gift_amount` ADD  `member_paid` TINYINT( 1 ) NOT NULL DEFAULT  '0';

# update 02/13/08

INSERT INTO part (org, part_id, content) VALUES (3, 'sidebar_tagline', 'treat yourself... relax with us!');

ALTER TABLE `brand` ADD `brand_website` TINYINT( 1 ) NOT NULL AFTER `brand_email_marketing` ;
update brand set brand_website = 1 where brand in (2, 3);

ALTER TABLE brand ADD `billing_calc_gc` INT( 11 ) NOT NULL;
ALTER TABLE brand ADD INDEX ( `billing_calc_gc` ) ;
ALTER TABLE brand ADD `billing_calc_website` INT( 11 ) NOT NULL;
ALTER TABLE brand ADD INDEX ( `billing_calc_website` ) ;
UPDATE brand SET billing_calc_gc = 1, billing_calc_website = 3;
ALTER TABLE brand ADD CONSTRAINT FOREIGN KEY (billing_calc_gc) REFERENCES billing_calc (billing_calc);
ALTER TABLE brand ADD CONSTRAINT FOREIGN KEY (billing_calc_website) REFERENCES billing_calc (billing_calc);

ALTER TABLE brand ADD `billing_calc_website_solo` INT( 11 ) NOT NULL;
ALTER TABLE brand ADD INDEX ( `billing_calc_website_solo` ) ;
UPDATE brand SET billing_calc_website_solo = 8;
ALTER TABLE brand ADD CONSTRAINT FOREIGN KEY (billing_calc_website_solo) REFERENCES billing_calc (billing_calc);

INSERT INTO `billing_calc` (`name`, `calc_function`, `calc_param`, `calc_multiplier`, `calc_function_2`, `calc_param_2`, `calc_multiplier_2`, `type`)
VALUES ('8%', 'percent', '8', '1', 'percent', '8', '1', '1');

# update 02/15/08

INSERT INTO `billing_calc` (`billing_calc`, `name`, `calc_function`, `calc_param`, `calc_multiplier`, `calc_function_2`, `calc_param_2`, `calc_multiplier_2`, `type`) VALUES
(16, '5%', 'percent', '5', 1.0000, 'percent', '5', 1.0000, 1);
UPDATE `brand` SET `billing_calc_gc` = '16' WHERE `brand` = 1 LIMIT 1;
UPDATE `brand` SET `billing_calc_gc` = '16' WHERE `brand` = 2 LIMIT 1;

# update 02/29/08

ALTER TABLE `brand` ADD `brand_request` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `brand_website`;
update brand set brand_request = 1 where org IN (409, 13);
ALTER TABLE `brand` CHANGE `brand_website` `brand_can_website` TINYINT( 1 ) NOT NULL DEFAULT '0';
ALTER TABLE `brand` CHANGE `brand_request` `brand_can_request` TINYINT( 1 ) NOT NULL DEFAULT '0' ;
ALTER TABLE `help_topic` ADD `feature` TINYINT( 1 ) NOT NULL DEFAULT '0';

# update 03/08/08

CREATE TABLE `partner` (
  `partner` int(11) NOT NULL default '0',
  `name` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`partner`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO partner (partner, name) VALUES (-100, 'Harms');

# update 03/24/08

# multiple hours open
CREATE TABLE `org_hour` (
  `org_hour` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL default '0',
  `client_address` int(11) NOT NULL default '0',
  `hour_set` tinyint(4) NOT NULL default '0',
  `effective_date` datetime default NULL,
  `sunday_open` time NOT NULL default '00:00:00',
  `sunday_close` time NOT NULL default '00:00:00',
  `monday_open` time NOT NULL default '00:00:00',
  `monday_close` time NOT NULL default '00:00:00',
  `tuesday_open` time NOT NULL default '00:00:00',
  `tuesday_close` time NOT NULL default '00:00:00',
  `wednesday_open` time NOT NULL default '00:00:00',
  `wednesday_close` time NOT NULL default '00:00:00',
  `thursday_open` time NOT NULL default '00:00:00',
  `thursday_close` time NOT NULL default '00:00:00',
  `friday_open` time NOT NULL default '00:00:00',
  `friday_close` time NOT NULL default '00:00:00',
  `saturday_open` time NOT NULL default '00:00:00',
  `saturday_close` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`org_hour`),
  KEY `org` (`org`),
  CONSTRAINT `org_hour_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO org_hour (org, hour_set, sunday_open, sunday_close, monday_open, monday_close, tuesday_open, tuesday_close, wednesday_open, wednesday_close, thursday_open, thursday_close, friday_open, friday_close, saturday_open, saturday_close)
	SELECT org, 0, sunday_open, sunday_close, monday_open, monday_close, tuesday_open, tuesday_close, wednesday_open, wednesday_close, thursday_open, thursday_close, friday_open, friday_close, saturday_open, saturday_close FROM org;

# menus
CREATE TABLE `menu_cat` (
  `menu_cat` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `sort` decimal(12,6) NOT NULL default '0.000000',
  PRIMARY KEY  (`menu_cat`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `menu_cat` VALUES (1,'Breakfast','1.000000'),(2,'Lunch','2.000000'),(3,'Dinner','3.000000'),(4,'Dessert','4.000000'),(5,'Wine','5.000000'),(6,'Appetizer','6.000000');

CREATE TABLE `menu` (
  `menu` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL default '0',
  `entered_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `edit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `menu_cat` int(11) NOT NULL default '0',
  `name` varchar(50) default NULL,
  `description` text,
  `footer_text` text,
  `prix_fixe` tinyint(4) NOT NULL default '0',
  `price` decimal(12,2) NOT NULL default '0.00',
  `default_display` tinyint(4) NOT NULL default '0',
  `hide` tinyint(4) NOT NULL default '0',
  `menu_layout` int(11) default NULL,
  `paper_page` tinyint(4) NOT NULL default '0',
  `sort` decimal(12,6) NOT NULL default '0.000000',
  PRIMARY KEY  (`menu`),
  KEY `org` (`org`),
  KEY `menu_cat` (`menu_cat`),
  CONSTRAINT `menu_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`),
  CONSTRAINT `menu_ibfk_2` FOREIGN KEY (`menu_cat`) REFERENCES `menu_cat` (`menu_cat`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `menu_header` (
  `menu_header` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `logo_position` tinyint(4) NOT NULL default '0',
  `logo_x` decimal(12,6) NOT NULL default '0.000000',
  `logo_y` decimal(12,6) NOT NULL default '0.000000',
  `margin_left` float NOT NULL default '0',
  `margin_top` float NOT NULL default '0',
  `margin_right` float NOT NULL default '0',
  `margin_bottom` float NOT NULL default '0',
  `text_block_position` tinyint(4) NOT NULL default '0',
  `text_block` text,
  `text_block_x` decimal(12,6) NOT NULL default '0.000000',
  `text_block_y` decimal(12,6) NOT NULL default '0.000000',
  `text_block_font` varchar(50) default NULL,
  `text_block_fontsize` tinyint(4) default NULL,
  `text_block_color` varchar(7) default NULL,
  `text_block_align` char(1) default 'D',
  `photo` int(11) default NULL,
  `photo_filename` varchar(100) default NULL,
  PRIMARY KEY  (`menu_header`),
  KEY `org` (`org`),
  KEY `photo` (`photo`),
  CONSTRAINT `menu_header_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `menu_section` (
  `menu_section` int(11) NOT NULL auto_increment,
  `menu` int(11) NOT NULL default '0',
  `entered_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `edit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `name` varchar(255) NOT NULL default '',
  `description` text,
  `footer_text` text,
  `price` decimal(12,2) NOT NULL default '0.00',
  `page_on` tinyint(4) default NULL,
  `section_columns` int(11) NOT NULL default '1',
  `hide` tinyint(4) NOT NULL default '0',
  `hide_header` tinyint(4) NOT NULL default '0',
  `specials` tinyint(4) NOT NULL default '0',
  `sort` decimal(12,6) NOT NULL default '0.000000',
  PRIMARY KEY  (`menu_section`),
  KEY `menu` (`menu`),
  CONSTRAINT `menu_section_ibfk_1` FOREIGN KEY (`menu`) REFERENCES `menu` (`menu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `menu_item` (
  `menu_item` int(11) NOT NULL auto_increment,
  `menu_section` int(11) NOT NULL default '0',
  `entered_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `edit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `name` varchar(255) NOT NULL default '',
  `description` text,
  `price_type` tinyint(4) NOT NULL default '0',
  `price` decimal(12,2) NOT NULL default '0.00',
  `portion_price` text,
  `hide` tinyint(4) NOT NULL default '0',
  `tag` text,
  `start_date` date default NULL,
  `end_date` date default NULL,
  `sort` decimal(12,6) NOT NULL default '0.000000',
  PRIMARY KEY  (`menu_item`),
  KEY `menu_section` (`menu_section`),
  CONSTRAINT `menu_item_ibfk_2` FOREIGN KEY (`menu_section`) REFERENCES `menu_section` (`menu_section`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `menu_layout` (
  `menu_layout` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL default '0',
  `name` varchar(50) default NULL,
  `menu_header` int(11) NOT NULL default '0',
  `paper_size` varchar(20) default NULL,
  `margin_left` float NOT NULL default '1',
  `margin_top` float NOT NULL default '1',
  `margin_right` float NOT NULL default '1',
  `margin_bottom` float NOT NULL default '1',
  `paper_page` tinyint(4) NOT NULL default '0',
  `menu_font` varchar(50) default NULL,
  `menu_fontsize` tinyint(4) default NULL,
  `menu_color` varchar(7) default NULL,
  `menu_align` char(1) NOT NULL default 'L',
  `menu_pre_space` float NOT NULL default '1',
  `menu_post_space` float NOT NULL default '1',
  `section_font` varchar(50) default NULL,
  `section_fontsize` tinyint(4) default NULL,
  `section_color` varchar(7) default NULL,
  `section_align` char(1) NOT NULL default 'L',
  `section_pre_space` float NOT NULL default '1',
  `section_post_space` float NOT NULL default '1',
  `item_font` varchar(50) default NULL,
  `item_fontsize` tinyint(4) default NULL,
  `item_color` varchar(7) default NULL,
  `item_align` char(1) NOT NULL default 'L',
  `item_pre_space` float NOT NULL default '1',
  `item_post_space` float NOT NULL default '1',
  `description_font` varchar(50) default NULL,
  `description_fontsize` tinyint(4) default NULL,
  `description_color` varchar(7) default NULL,
  `description_align` char(1) NOT NULL default 'L',
  `description_margin_left` float NOT NULL default '0',
  `description_pre_space` float NOT NULL default '1',
  `description_post_space` float NOT NULL default '1',
  `header_font` varchar(50) default NULL,
  `header_fontsize` tinyint(4) default NULL,
  `header_color` varchar(7) default NULL,
  `header_align` char(1) NOT NULL default 'L',
  `header_pre_space` float NOT NULL default '1',
  `header_post_space` float NOT NULL default '1',
  PRIMARY KEY  (`menu_layout`),
  KEY `org` (`org`),
  KEY `menu_header` (`menu_header`),
  CONSTRAINT `menu_layout_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `menu_setting` (
  `menu_setting` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL default '0',
  `price_style` int(11) NOT NULL default '0',
  `show_dollar` tinyint(4) NOT NULL default '1',
  `price_location` int(11) NOT NULL default '0',
  `price_portion` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`menu_setting`),
  KEY `org` (`org`),
  CONSTRAINT `menu_setting_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `menu_tag` (
  `menu_tag` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL default '0',
  `tag` varchar(10) default NULL,
  `default_value` tinyint(4) NOT NULL default '0',
  `name` varchar(50) default NULL,
  `photo` int(11) default NULL,
  `photo_filename` varchar(100) default NULL,
  PRIMARY KEY  (`menu_tag`),
  KEY `photo` (`photo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

# update 03/30/08

ALTER TABLE menu ADD COLUMN show_name tinyint NOT null DEFAULT 1 after name;

# update 04/01/08

UPDATE org_hour SET client_address = (SELECT client_address.client_address FROM org JOIN client_address ON org.client=client_address.client AND address_type = 0 WHERE org.org=org_hour.org) WHERE org_hour.client_address=0;
ALTER TABLE org_hour ADD FOREIGN KEY (client_address) REFERENCES client_address(client_address);

# update 04/07/08

ALTER TABLE email_marketing ADD COLUMN org_restrict varchar(50) NOT NULL DEFAULT 'NONE' AFTER org_type;
INSERT INTO partner (partner, name) VALUES (2, 'SpaFinder');
INSERT INTO partner (partner, name) VALUES (3, 'SpaBooker');
ALTER TABLE org_partner CHANGE COLUMN partner partner int not null;
ALTER TABLE org_partner ADD FOREIGN KEY (partner) REFERENCES partner (partner);

ALTER TABLE `slideshow` CHANGE `active` `active` TINYINT( 1 ) NOT NULL DEFAULT '0',
CHANGE `effect` `effect` TINYINT( 1 ) NOT NULL DEFAULT '0'
ALTER TABLE `slideshow` ADD `duration` TINYINT( 1 ) NOT NULL DEFAULT '6' AFTER `effect` ;

# update 04/08/08

INSERT INTO partner (partner, name) VALUES (4, 'Zagat');
CREATE TABLE `org_client_partner` (
  `org_client_partner` int(11) NOT NULL auto_increment,
  `org_client` int(11) NOT NULL default '0',
  `partner` int(11) NOT NULL default '0',
  `id` varchar(20) default NULL,
  `web_id` varchar(20) default NULL,
  PRIMARY KEY  (`org_client_partner`),
  KEY `org_client` (`org_client`),
  KEY `partner` (`partner`),
  KEY `id` (`id`),
  CONSTRAINT `org_client_partner_ibfk_1` FOREIGN KEY (`org_client`) REFERENCES `org_client` (`org_client`),
  CONSTRAINT `org_client_partner_ibfk_2` FOREIGN KEY (`partner`) REFERENCES `partner` (`partner`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 04/28/08

ALTER TABLE `promo` ADD `marketing_type` TINYINT( 1 ) NULL ,
ADD `note` TEXT NULL ;
UPDATE promo, affiliate SET marketing_type = 2 WHERE promo.code = affiliate.id;

# update 04/17/08

UPDATE org_client SET source=5 WHERE source=0 AND verify_token IS NOT NULL;
ALTER TABLE email_marketing ADD COLUMN org_restrict varchar(50) NOT NULL DEFAULT 'NONE' AFTER org_type;
CREATE TABLE `email_domain` (
  `email_domain` int(11) NOT NULL auto_increment,
  `domain` varchar(100) NOT NULL,
  `sort` decimal(12,6) NOT NULL,
  PRIMARY KEY  (`email_domain`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
INSERT INTO email_domain (domain, sort) VALUES ('boomtime.com', 1);
INSERT INTO queue_recurring (type, transaction, name, process_on, process_at, active) VALUES (3,4,'Confirm opt-in', '0,1,2,3,4,5,6', '02:00', 1);
INSERT INTO queue_recurring (type, transaction, name, process_on,process_at,active) VALUES (4,2,'System website backup','EOM','03:00',1);

# update 04/29/08

ALTER TABLE `org` ADD `email_marketing_on` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `harms_millennium` ;

update part set content="<h1>Thank you!</h1><p>Thanks for confirming your email address with us.</p><p>We will only email you occasionaly, and you can unsubscribe at any time.</p>" where part = 229;

# update 05/04/08

# enhanced subscribe verification and ability to turn off opt in confirmation at the org level
INSERT INTO part (org, part_id, content) VALUES (3,'email_token_err',"<h1>Verification Failed</h1>I could not verify your information; please take a moment and sign up with us again!</p>");
INSERT INTO part (org, part_id, content) VALUES (3,'email_verfy_err',"<h1>Not found...</h1>I didn't find you; please take a moment and sign up with us!</p>");
ALTER TABLE `org` ADD `email_marketing_confirm` tinyint(1) NOT NULL DEFAULT '1' AFTER `email_marketing_on`;

# update 05/16/08

CREATE TABLE `color_backup` (
  `color_backup` int(11) NOT NULL auto_increment,
  `backup` int(11) NOT NULL default '0',
  `backup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `backup_type` int(11) NOT NULL default '0',
  `org` int(11) default NULL,
  `name` varchar(50) NOT NULL,
  `bg_dark` varchar(7) NOT NULL,
  `bg` varchar(7) NOT NULL,
  `bg_light` varchar(7) NOT NULL,
  `fg_dark` varchar(7) NOT NULL,
  `fg` varchar(7) NOT NULL,
  `fg_light` varchar(7) NOT NULL,
  `highlight_1` varchar(7) NOT NULL,
  `highlight_2` varchar(7) NOT NULL,
  `msg` varchar(7) NOT NULL,
  `special` varchar(7) NOT NULL,
  `hide` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`color_backup`),
  KEY `backup` (`backup`),
  KEY `org` (`org`),
  CONSTRAINT `color_backup_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
CREATE TABLE `logo_backup` (
  `logo_backup` int(11) NOT NULL auto_increment,
  `backup` int(11) NOT NULL default '0',
  `backup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `backup_type` int(11) NOT NULL default '0',
  `org` int(11) NOT NULL default '0',
  `logo_file` varchar(50) NOT NULL default '',
  `logo_backup_file` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`logo_backup`),
  KEY `backup` (`backup`),
  KEY `logo_backup_ibfk_1` (`org`),
  CONSTRAINT `logo_backup_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

# update 06/05/08

ALTER TABLE `org` ADD `api_key` VARCHAR(8) NULL, ADD `api_tx_key` VARCHAR(8) NULL;
UPDATE ORG set api_tx_key = SUBSTR(REVERSE(REPLACE(REPLACE(ENCRYPT(
  CONV(FLOOR(RAND() * 99999999999999), 10, 36), CONV(FLOOR(RAND() * 99999999999999),10,36)),
  '/', ''), '.', '')),1,8) WHERE api_tx_key IS NULL;
ALTER TABLE `partner` ADD `api_key` VARCHAR(8) NULL;
UPDATE `partner` set `api_key` = '0y8Sbj9t' WHERE `name` = 'SpaBooker';
UPDATE `org` set `api_key` = '11111111' WHERE `org` = 5;
UPDATE `org` set `api_tx_key` = '22222222' WHERE `org` = 5;

# update 06/10/08

INSERT INTO `sevence`.`prof` VALUES ( '2', '977', 'API', 'API', '0', 'BoomTime', 'Services',
  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, NULL, '0', '0', '0',
  '0', '0', '0', '0', '0', '0000-00-00 00:00:00');

# update 06/15/08

ALTER TABLE client_address ADD COLUMN email_status tinyint not null default 0 AFTER email;

ALTER TABLE review ADD COLUMN org int NOT NULL after review;
UPDATE review JOIN org_client ON review.org_client=org_client.org_client SET review.org=org_client.org;
ALTER TABLE review ADD FOREIGN KEY (org) REFERENCES org (org);
ALTER TABLE review CHANGE org_client org_client int;
ALTER TABLE review ADD COLUMN highlight tinyint NOT NULL default 0;
ALTER TABLE review ADD COLUMN hide tinyint NOT NULL default 0;
ALTER TABLE review ADD COLUMN hide_reason tinyint;
ALTER TABLE review ADD COLUMN source tinyint NOT NULL default 1;
ALTER TABLE review ADD COLUMN review_type tinyint NOT NULL default 1;
INSERT INTO email_marketing (type,id,org,active,name,subject,headline,narrative,body,send_on,email_cycle,hide,show_event,email_design,client_type,org_type,org_restrict,editor,photo,photo_filename) VALUES (1,'reset_password',3,1,'Reset Password','Here\'s your new password','Here\'s your new password','It\'s the perfect thoughtful, gift of relaxation -- an Instant Gift Certificate from {company}.\r\n\r\n{instant_gc_link(\'Purchase an Instant Gift Certificate\')}\r\n{website_link(\'Visit our website\')}','It\'s the perfect thoughtful, gift of relaxation -- an Instant Gift Certificate from {company}.<br />\r\n<br />\r\n{instant_gc_link(\'Purchase an Instant Gift Certificate\')}<br />\r\n{website_link(\'Visit our website\')}',NULL,0,0,1,5,'0','ALL','NONE',0,0,'');

# update 06/16/08

ALTER TABLE `affiliate` ADD `feed_key` VARCHAR( 8 ) NULL AFTER `api_pass` ;
UPDATE `affiliate` set `feed_key` = '0x8Gaj2h' WHERE `id` = 'yellowbot';

ALTER TABLE org ADD COLUMN dir_review tinyint NOT NULL DEFAULT 1 after dir_point;

# update 07/09/08

INSERT INTO `billing_calc` (`billing_calc`, `name`, `calc_function`, `calc_param`, `calc_multiplier`, `calc_function_2`, `calc_param_2`, `calc_multiplier_2`, `type`) VALUES(18, '$99.95', 'flat', '99.95', 1.0000, NULL, NULL, 1.0000, 2);
INSERT INTO `billing_calc` (`billing_calc`, `name`, `calc_function`, `calc_param`, `calc_multiplier`, `calc_function_2`, `calc_param_2`, `calc_multiplier_2`, `type`) VALUES(17, '$49.95', 'flat', '49.95', 1.0000, NULL, NULL, 1.0000, 2);

# update 07/10/08

CREATE TABLE `collateral` (
  `collateral` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `paper_size` varchar(20) default NULL,
  PRIMARY KEY  (`collateral`),
  KEY `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `collateral_element` (
  `collateral_element` int(11) NOT NULL auto_increment,
  `collateral` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `element_color` varchar(7) default NULL,
  `x` float default NULL,
  `y` float default NULL,
  `font` varchar(50) default NULL,
  `fontsize` tinyint(4) default NULL,
  `align` char(1) default NULL,
  `width` int(11) default NULL,
  `element_text` blob,
  `border` varchar(4) default NULL,
  `x_end` float default NULL,
  `y_end` float default NULL,
  `fill_color` varchar(7) default NULL,
  `rect_width` float default NULL,
  `rect_height` float default NULL,
  PRIMARY KEY  (`collateral_element`),
  KEY `collateral` (`collateral`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 07/28/08

ALTER TABLE `org` ADD `spafinder_id` VARCHAR(16) NULL AFTER `paypal_login`;

# update 08/07/08

ALTER TABLE review ADD COLUMN ip INT(11) unsigned default NULL;
ALTER TABLE  `org_tran` ADD  `ping` TINYINT( 1 ) NOT NULL DEFAULT  '0';

# update 08/12/08

ALTER TABLE `org` ADD `first_billing` DATE NULL AFTER `first_sale` ;

# update 08/20/08

ALTER TABLE client_help ADD COLUMN billing_email VARCHAR(100);

# update 08/26/08

ALTER TABLE `billing` ADD  `billing_hold` TINYINT( 1 ) NOT NULL DEFAULT  '0';

# update 09/04/08

ALTER TABLE collateral_element ADD COLUMN height int AFTER width;
ALTER TABLE org ADD COLUMN marketing tinyint NOT NULL default 0 AFTER harms_millennium;
ALTER TABLE client_address ADD COLUMN anniversary VARCHAR(10) AFTER birthday;
ALTER TABLE collateral_element ADD COLUMN sort decimal(12,6) NOT NULL;

# update 09/08/08

ALTER TABLE collateral_element ADD COLUMN photo int;
ALTER TABLE collateral_element ADD COLUMN photo_filename varchar(100);

# update 09/15/08

# multi-format for collateral
ALTER TABLE collateral ADD COLUMN paper_format VARCHAR(20) NOT NULL DEFAULT '1X1';
ALTER TABLE collateral_element CHANGE width width float;
ALTER TABLE collateral_element CHANGE height height float;

# update 09/19/208

CREATE TABLE `report` (
  `report` int(11) NOT NULL auto_increment,
  `report_key` varchar(255) NOT NULL default '',
  `name` varchar(255) default NULL,
  `width` int(11) NOT NULL default '0',
  `height` int(11) NOT NULL default '0',
  `default_depict` varchar(255) default NULL,
  `default_options` varchar(255) default NULL,
  `depict` varchar(255) default NULL,
  `depict_type` varchar(255) default NULL,
  `options` varchar(255) default NULL,
  PRIMARY KEY  (`report`),
  KEY `report_key` (`report_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `report` VALUES (1,'gc_cash_flow','Cash Flow Analysis',4,2,'purchased,redeemed','months=24','purchased,redeemed','checkbox','months=12*24'),
	(2,'client_location','Client Location',1,1,'','precision=0','','checkbox','precision=0*1*2'),
	(3,'gc_analysis_per','Gift Certificate Percentage Analysis',4,2,'email_per,service_per,delayed_per','months=12,type=bar,headroom=1','email_per,service_per,delayed_per','checkbox','type=bar,headroom=1'),
	(4,'average_review','Average Review',2,1,'','','','checkbox',''),
	(5,'gc_performance','Gift Certificate Performance',2,2,'paid','period=mtd','gc_count,paid,average','radio','period=ytd*mtd'),
	(6,'average_response','Average Campaign Response',1,1,'','','','',''),
	(7,'gc_analysis_sales','Gift Certificate Sales Analysis',4,2,'paid','months=24','paid,gc_count','radio','months=12*24'),
	(8,'website_referrer','Website Referrals',2,2,NULL,NULL,NULL,NULL,NULL);
UPDATE report SET default_options='days=30', options='days=30*60*90*180*360*All' WHERE report_key IN ('average_response', 'average_review','website_referrer');

CREATE TABLE `report_option` (
  `report_option` int(11) NOT NULL auto_increment,
  `report_key` varchar(100) NOT NULL default '',
  `option_key` varchar(100) NOT NULL default '',
  `option_type` varchar(20) NOT NULL default '',
  `name` varchar(255) default NULL,
  `option_text` varchar(255) default NULL,
  `description` text,
  PRIMARY KEY  (`report_option`),
  KEY `report_key` (`report_key`),
  CONSTRAINT `report_option_ibfk_1` FOREIGN KEY (`report_key`) REFERENCES `report` (`report_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `report_option` VALUES (1,'client_location','precision','user','Number of decimal places',NULL,'The number of decimal places to show for each pie slice.'),
(2,'gc_cash_flow','months','user','Months to show',NULL,'The number of months to show on the report'),
(3,'gc_cash_flow','redeemed','user','Gift Certificates Redeemed','','Total number of gift certificates redeemed'),
(4,'gc_cash_flow','purchased','user','Gift Certificates Sold','','Total number of gift certificates sold'),
(5,'gc_analysis_per','email_per','user','Email Percentage',NULL,'Percentage of sales via Email'),
(6,'gc_analysis_per','service_per','user','Service Percentage',NULL,'Percentage of sales for services'),
(7,'gc_analysis_per','delayed_per','user','Delayed Percentage',NULL,'Percentage of sales delayed'),
(8,'gc_analysis_per','months','user','Months to show',NULL,'The number of months to show on the report'),
(9,'gc_performance','gc_count','user','Gift Certificates',NULL,'Total number of gift certificates'),
(10,'gc_performance','paid','user','Gift Certificates Sales',NULL,'Total gift certificate value'),
(11,'gc_performance','average','user','Average Gift Certificates Sale',NULL,'Average gift certificate value'),
(12,'gc_performance','period','user','Period to report','mtd=Month To Date,ytd=Year To Date','YTD gives year to date values, and MTD gives month to date values'),
(13,'gc_analysis_per','type','hidden','Type of Chart',NULL,'Determines if this is a bar or line graph'),
(14,'gc_analysis_per','headroom','hidden','Chart Headroom',NULL,'Determines if bars go all the way to the top of the graph'),
(15,'gc_analysis_sales','paid','user','Sales',NULL,'Gift certificate sales'),
(16,'gc_analysis_sales','gc_count','user','Gift Certificates',NULL,'Number of gift certificates sold'),
(17,'gc_analysis_sales','months','user','Months to show',NULL,'The number of months to show on the report');
INSERT INTO report_option (report_key, option_key, option_type, name, option_text, description) VALUES ('average_response','days','user','Days',NULL,'Indicates how many days to include. For example, 30 means include the last 30 days');
INSERT INTO report_option (report_key, option_key, option_type, name, option_text, description) VALUES ('average_review','days','user','Days',NULL,'Indicates how many days to include. For example, 30 means include the last 30 days');
INSERT INTO report_option (report_key, option_key, option_type, name, option_text, description) VALUES ('website_referrer','days','user','Days',NULL,'Indicates how many days to include. For example, 30 means include the last 30 days');

CREATE TABLE `report_org` (
  `report_org` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL default '0',
  `report_key` varchar(100) default NULL,
  `row_at` int(11) NOT NULL default '0',
  `column_at` int(11) NOT NULL default '0',
  `width` int(11) NOT NULL default '0',
  `height` int(11) NOT NULL default '0',
  `depict` varchar(100) default NULL,
  `options` varchar(255) default NULL,
  PRIMARY KEY  (`report_org`),
  KEY `report_key` (`report_key`),
  CONSTRAINT `report_org_ibfk_1` FOREIGN KEY (`report_key`) REFERENCES `report` (`report_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `report_org` VALUES (1,3,'gc_cash_flow',0,0,4,2,'purchased,redeemed','months=24'),
(2,3,'gc_analysis_sales',2,0,4,2,'paid','months=24'),
(3,3,'average_response',0,4,1,1,'',''),
(4,3,'average_review',1,4,2,1,'',''),
(5,3,'client_location',0,5,1,1,'','precision=0'),
(6,3,'website_referrer',2,4,2,2,NULL,NULL);
UPDATE report_org SET options='days=30' WHERE report_key IN ('average_response', 'average_review','website_referrer');
UPDATE report SET name='Average Campaign\r\nResponse' WHERE report_key='average_response';
UPDATE report_option SET option_text='mtd=Month To Date,ytd=Year To Date,trail365=Trailing Year,trail30=Trailing 30 Days' WHERE report_key='gc_performance' AND option_key='period';

INSERT INTO report (report_key, name, width, height, default_depict, default_options, depict, depict_type, options) VALUES ('gc_void','Voided',4,2,'void_amount','months=12','void_count,void_amount','checkbox','months=12*24');
INSERT INTO report_option (report_key, option_key, option_type, name, description) VALUES ('gc_void','months','user','Months to show','The number of months to show on the report');
INSERT INTO report_option (report_key, option_key, option_type, name, description) VALUES ('gc_void','void_count','user','Voided','The number of voided gift certificates');
INSERT INTO report_option (report_key, option_key, option_type, name, description) VALUES ('gc_void','void_amount','user','Voided Amount','The value of voided gift certificates');
UPDATE report_org SET report_key='gc_void', depict='void_amount', options='months=12' WHERE report_key='gc_analysis_sales';
UPDATE report_option SET option_text='mtd=Month To Date,ytd=Year To Date,trail365=Trailing Year,trail30=Trailing 30 Days', description='YTD gives year to date values, MTD gives month to date values, Trailing Year gives trailing year, and Trailing 30 days gives last 30 days' WHERE  report_key='gc_performance' AND option_key='period';
ALTER TABLE report ADD COLUMN link varchar(100);
UPDATE report SET link='report_email_campaign.php?1=campaign&start_date={start_date}' WHERE report_key='average_response';
UPDATE report SET link='report_review.php?start_date={start_date}' WHERE report_key='average_review';
UPDATE report SET link='report_gift.php?1=cash_flow&start_date={start_date}' WHERE report_key='gc_cash_flow';
UPDATE report SET link='report_gift.php?1=cash_flow&start_date={start_date}' WHERE report_key='gc_void';
UPDATE report SET link='report_website.php?1=referrer&start_date={start_date}' WHERE report_key='website_referrer';

UPDATE report set options='period=ytd*mtd*trail365*trail30' WHERE report_key='gc_performance';

# update 09/22/08

UPDATE report SET name = 'GC Analysis' WHERE report_key = 'gc_analysis_per';
UPDATE report SET name = 'Email Campaigns' WHERE report_key = 'average_response';
UPDATE report SET name = 'Cash Flow' WHERE report_key = 'gc_cash_flow';
UPDATE report SET name = 'Voided GCs', link = 'report_gift.php?1=voided&start_date={start_date}' WHERE report_key = 'gc_void';
delete from report_option where report_key = 'gc_analysis_sales';
delete from report where report_key = 'gc_analysis_sales';
UPDATE report SET name = 'GC Sales Comps' WHERE report_key = 'gc_performance';
UPDATE report_option SET option_key = 'service_per', name = 'Service Percentage' WHERE report_option = 5;
UPDATE report_option SET option_key = 'email_per', name = 'Email Percentage' WHERE report_option = 6;
UPDATE report_option SET name = 'Delayed Percentage' WHERE report_option = 7;
UPDATE report SET default_depict = 'service_per,email_per,delayed_per,existing_client_per', depict = 'service_per,email_per,delayed_per,existing_client_per' WHERE report = 3;
INSERT INTO `report_option` VALUES (24,'gc_analysis_per','existing_client_per','user','Existing Client Percentage',NULL,'Percentage of sales from existing clients');

# update 09/25/08

# offer
ALTER TABLE email_marketing ADD COLUMN offer int;

# marketing lists
ALTER TABLE email_marketing ADD COLUMN marketing_list varchar(100) NOT NULL DEFAULT '0' AFTER client_type;

CREATE TABLE `marketing_list` (
  `marketing_list` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `hide` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`marketing_list`),
  KEY `org` (`org`),
  CONSTRAINT `marketing_list_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `org_client_marketing_list` (
  `org_client` int(11) NOT NULL,
  `marketing_list` int(11) NOT NULL,
  `active` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`org_client`,`marketing_list`),
  KEY `org_client` (`org_client`),
  KEY `marketing_list` (`marketing_list`),
  CONSTRAINT `org_client_marketing_list_ibfk_1` FOREIGN KEY (`org_client`) REFERENCES `org_client` (`org_client`),
  CONSTRAINT `org_client_marketing_list_ibfk_2` FOREIGN KEY (`marketing_list`) REFERENCES `marketing_list` (`marketing_list`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO part (org, part_id, content) values (3,'ml_subscribe','Subscribe to:');
INSERT INTO part (org, part_id, content) values (3,'ml_opt_after','<h2>Additional Options</h2>');

# stock photos by brand
ALTER TABLE `photo_group` ADD `brand_org` INT( 11 ) NULL AFTER `photo_group` ;
ALTER TABLE `photo_group` ADD INDEX ( `brand_org` ) ;
ALTER TABLE `photo_group` ADD FOREIGN KEY ( `brand_org` ) REFERENCES `sevence`.`org` (`org`);
UPDATE photo_group SET brand_org = 13 WHERE brand_org IS NULL;

# update 10/01/08

ALTER TABLE `org` ADD `email_marketing_sfl` TINYINT( 1 ) NOT NULL DEFAULT '1';
UPDATE org SET email_marketing_sfl = -1;

# terminal support
ALTER TABLE  `gift_amount` ADD  `location` VARCHAR( 16 ) NULL DEFAULT NULL ,
  ADD  `sequence` VARCHAR( 16 ) NULL DEFAULT NULL;
ALTER TABLE  `gift` ADD  `secure_id` CHAR( 16 ) NULL DEFAULT NULL AFTER  `gift_id`;
ALTER TABLE  `gift` ADD  `old_secure_id` CHAR( 16 ) NULL DEFAULT NULL AFTER  `gift_id`;
ALTER TABLE  `gift` ADD UNIQUE (`secure_id`);
ALTER TABLE  `gift` ADD UNIQUE (`old_secure_id`);
ALTER TABLE  `partner` CHANGE  `api_key`  `api_key` VARCHAR( 8 ) CHARACTER SET latin1 COLLATE latin1_general_cs NULL DEFAULT NULL;
ALTER TABLE  `org` CHANGE  `api_key`  `api_key` VARCHAR( 8 ) CHARACTER SET latin1 COLLATE latin1_general_cs NULL DEFAULT NULL ,
  CHANGE  `api_tx_key`  `api_tx_key` VARCHAR( 8 ) CHARACTER SET latin1 COLLATE latin1_general_cs NULL DEFAULT NULL;
INSERT INTO `partner` (`partner`, `name`, `api_key`) VALUES ('-999', 'EZHost', '0z6crUma');

# update 10/27/08

CREATE TABLE `offer_type` (
  `offer_type` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL,
  `offer_base` varchar(20) NOT NULL,
  `name` varchar(50) default NULL,
  `email_id` varchar(16) NOT NULL,
  `lead_days` tinyint(4) NOT NULL default '30',
  `expire_days` tinyint(4) NOT NULL default '60',
  `last_offer_id` int(11) default NULL,
  `active` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`offer_type`),
  KEY `org` (`org`),
  CONSTRAINT `offer_type_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `offer` (
  `offer` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL,
  `offer_type` int(11) NOT NULL,
  `default_offer` tinyint(4) NOT NULL default '0',
  `name` varchar(50) NOT NULL,
  `active` tinyint(4) NOT NULL default '1',
  `value` varchar(50) default NULL,
  `message` text,
  `gift_legalese` varchar(100) default NULL,
  `org_gift_design` int(11) NOT NULL,
  `lead_days` tinyint(4) NOT NULL default '30',
  `expire_days` tinyint(4) NOT NULL default '60',
  PRIMARY KEY  (`offer`),
  KEY `org` (`org`),
  KEY `offer_type` (`offer_type`),
  CONSTRAINT `offer_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`),
  CONSTRAINT `offer_ibfk_2` FOREIGN KEY (`offer_type`) REFERENCES `offer_type` (`offer_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `gift_promo` (
  `gift_promo` int(11) NOT NULL auto_increment,
  `org_client` int(11) NOT NULL,
  `offer` int(11) NOT NULL,
  `offer_id` varchar(20) default NULL,
  `purchase_date` date default NULL,
  `issue_date` date default NULL,
  `pickup_date` datetime default NULL,
  `expiration_date` datetime default NULL,
  `secure_id` char(16) default NULL,
  PRIMARY KEY  (`gift_promo`),
  UNIQUE KEY `secure_id` (`secure_id`),
  KEY `org_client` (`org_client`),
  KEY `offer` (`offer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE email_marketing ADD COLUMN offer INT;
ALTER TABLE email_marketing ADD INDEX (offer);
INSERT INTO report (report_key, name, width, height, default_options, options) VALUES ('offer_response', 'Offer Response', 1, 1, 'days=All', 'days=30*60*90*180*360*All');
INSERT INTO report_option (report_key, option_key, option_type, name, description) VALUES ('offer_response', 'days', 'user', 'Days','Indicates how many days to include. For example, 30 means include the last 30 days');

ALTER TABLE gift_promo ADD COLUMN location varchar(16);
ALTER TABLE gift_promo ADD COLUMN sequence varchar(16);
ALTER TABLE gift_promo ADD COLUMN redeemed_date datetime;
ALTER TABLE gift_promo ADD COLUMN prof int;
ALTER TABLE gift_promo ADD FOREIGN KEY (prof) REFERENCES prof (prof);
ALTER TABLE gift_promo ADD COLUMN void tinyint NOT NULL DEFAULT 0 AFTER expiration_date;
ALTER TABLE gift_promo ADD COLUMN transaction_date datetime AFTER expiration_date;
ALTER TABLE gift_promo ADD COLUMN comment varchar(50);
ALTER TABLE offer ADD COLUMN send_on datetime;

INSERT INTO part (org, part_id, content) VALUES (3, 'signup_offer', "Thank you for signing up! We've just sent you an exciting offer to your email address as our way of saying Thank You!");
ALTER TABLE offer CHANGE expire_days expire_days INT NOT NULL DEFAULT 60;
ALTER TABLE offer_type CHANGE expire_days expire_days INT NOT NULL DEFAULT 60;

# update 11/05/2008

ALTER TABLE email_marketing ADD COLUMN campaign_type tinyint NOT NULL DEFAULT 0;

# update 11/06/2008

ALTER TABLE email_design ADD COLUMN photo int;
ALTER TABLE email_design ADD COLUMN photo_filename varchar(100);
ALTER TABLE email_design ADD COLuMN logo_position varchar(10) DEFAULT 'NONE';
ALTER TABLE email_design ADD COLUMN logo_padding tinyint NOT NULL default 0;
ALTER TABLE email_design DROP COLUMN logo_position;
ALTER TABLE email_design ADD COLUMN logo_pos tinyint NOT NULL DEFAULT 0 AFTER photo_filename;

# update 11/09/2008

ALTER TABLE email_marketing ADD COLUMN push_type tinyint NOT NULL DEFAULT 0;

# update 11/10/2008

ALTER TABLE `org` ADD `gift_verhoeff` INT( 1 ) NOT NULL AFTER `gift_legalese` ,
ADD `gift_redeem_ucc` INT( 1 ) NOT NULL AFTER `gift_verhoeff`;

# update 11/13/2008

ALTER TABLE  `email_queue` ADD  `email_return_path` VARCHAR( 255 ) NULL DEFAULT NULL AFTER  `email_from`;

# update 11/15/2008

# offer master data
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) values (2850, 'birthday', 'Birthday', 'birthday', 30, 60, 10000, 1);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) values (2850, 'anniversary', 'Anniversary', 'anniversary', 30, 60, 10000, 1);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) values (2850, 'signup', 'Email Signup', 'signup', 0, 60, 10000, 1);
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) values (2850, 'onetime', 'One Time Offers', 'onetime', 30, 60, 10000, 1);
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='birthday' AND org = 2850 LIMIT 1), 1, 'Glass of Wine with Meal', 1, 'Free Glass of Wine', 'It\'s nearly time for that most important occasion - your own personal holiday, your birthday.  Celebrate it with a wonderful dinner at {short_name} and we\'ll help you toast with a free glass of wine from our custom selection.  Cheers!','Valid during your birthday month, for 1 glass of wine when 1 entree is purchased, up to $10 value.', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);

# Birthday Offers
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='birthday' AND org = 2850 LIMIT 1), 1, 'Glass of Wine with Meal', 1, 'Free Glass of Wine', 'It\'s nearly time for that most important occasion - your own personal holiday, your birthday.  Celebrate it with a wonderful dinner at {short_name} and we\'ll help you toast with a free glass of wine from our custom selection.  Cheers!','Valid during your birthday month, for 1 glass of wine when 1 entree is purchased, up to $10 value.', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='birthday' AND org = 2850 LIMIT 1), 1, 'Dessert with Entree', 1, 'Free Dessert with Entree', 'It\'s nearly time for that most important occasion - your own personal holiday, your birthday.  Celebrate it with a wonderful dinner at {short_name} and we\'ll help you start out a sweet new year with a free dessert from our tempting selection.  Cheers!','Valid during your birthday month, for 1 dessert when 1 entree is purchased.', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='birthday' AND org = 2850 LIMIT 1), 1, '10% Off Meal', 1, '10% Off Your Dinner', 'It\'s nearly time for that most important occasion - your own personal holiday, your birthday.  Celebrate it with a wonderful dinner at {short_name} and we\'ll help you start out a great new year with 10% off your check, for you and your guest.  Cheers!','Valid during your birthday month, for 1 dessert when 1 entree is purchased.', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);

# Anniversary
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='anniversary' AND org = 2850 LIMIT 1), 1, 'Glass of Wine with Meal', 1, 'Free Glass of Wine', 'It\'s nearly time for a most important occasion - your anniversary.  Celebrate it with a wonderful dinner with your special someone at {short_name} and we\'ll help you toast with a free glass of wine from our custom selection.  Cheers!', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='anniversary' AND org = 2850 LIMIT 1), 1, '10% Off Meal', 1, '10% Off Your Dinner', 'Make sure you don\'t forget that most important shared event - your anniversary.  Celebrate it with a wonderful dinner with your sweetie at {short_name} and we\'ll help you start another great year with 10% off your check.  Cheers!', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='anniversary' AND org = 2850 LIMIT 1), 1, 'Lovebirds Special Dessert', 1, 'One Free Dessert for Two', 'Dessert is on us!  You\'ve enjoyed another year together, and we\'d love to help you celebrate by providing a sweet ending to your meal for two.  We\'ll serve the two of you a special dessert created just for couples by our pastry chef. Congratulations!', 'Valid during your anniversary month, for a special dessert for two when you dine with your sweetie.', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);

# Email Sign Ups
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='signup' AND org = 2850 LIMIT 1), 1, 'Ten dollars off your tab', 1, '$10', 'We really appreciate you joining our events list and we\'d love to see you again soon - come by and we\'ll take $10 off your next dinner check as our way of saying thank you.', 'Valid for $10 off total dinner check.  Limit one per party, minimum check of $20.', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='signup' AND org = 2850 LIMIT 1), 1, 'Five Bucks Off', 1, '$5', 'We\'re so glad to have you on our mailing list and we would like to say thank you the next time you come to see us. On your next visit, present this certificate for $5 off your check with no strings attached.   Enjoy, and take care!', 'Valid for $5 off total dining check.  If check under $5, change not given.', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);
INSERT INTO offer (org, offer_type, default_offer, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days) VALUES (2850, (SELECT offer_type FROM offer_type WHERE offer_base='signup' AND org = 2850 LIMIT 1), 1, '10% Off Dinner', 1, '$5', 'Thank you so much for joining our events mailing list - you\'ll find great information about what we\'ve been up to and our upcoming specials. In appreciation, this certificate is good for 10% off your next dinner with us.  Come on by, say hello, and enjoy yourself!', 'Valid for 10% off total dinner check.  Limit one per party, maximum check amount of $200', (select org_gift_design from org_gift_design where org = 2850 order by popularity desc limit 1), 30, 60);

# Email Marketing
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, 'birthday', 2850, 1, 'Happy Birthday!', 'Happy Birthday!', 'Have a Happy Birthday!', 'To: {name}
From: {short_name}
For: {offer_value}

{offer}

{offer_link}', 'To: {name}<br />
From: {short_name}<br />
For: {offer_value}<br />
<br />
{offer}<br />
<br />
{offer_link}', 1, (select email_design from email_design where name = 'Newsletter'), 0, 0, (SELECT offer_type FROM offer_type WHERE offer_base='birthday' 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, 'anniversary', 2850, 1, 'Happy Anniversary!', 'Happy Anniversary!', 'Happy Anniversary!', 'To: {name}
From: {short_name}
For: {offer_value}

{offer}

{offer_link}', 'To: {name}<br />
From: {short_name}<br />
For: {offer_value}<br />
<br />
{offer}<br />
<br />
{offer_link}', 1, (select email_design from email_design where name = 'Newsletter'), 0, 0, (SELECT offer_type FROM offer_type WHERE offer_base='anniversary' 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, 'signup', 2850, 1, 'Thanks for signing up!', 'Thanks for signing up!', 'Thanks for signing up!', 'To: {name}
From: {short_name}
For: {offer_value}

{offer}

{offer_link}', 'To: {name}<br />
From: {short_name}<br />
For: {offer_value}<br />
<br />
{offer}<br />
<br />
{offer_link}', 1, (select email_design from email_design where name = 'Newsletter'), 0, 0, (SELECT offer_type FROM offer_type WHERE offer_base='signup' AND org = 2850 LIMIT 1), 1, 0);

# Missing parts
INSERT INTO part (org, part_id, content) VALUES (3, 'opt_subscribe', 'Please provide us with the following optional information.');
INSERT INTO part (org, part_id, content) VALUES (3, 'email_opt', '<h1>Thank you</h1><p>Thank you for telling us about yourself.</p>');

# update 11/21/2008

ALTER TABLE `event` ADD `special_type` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `file_anchor_text` ,
ADD `discount` TINYINT( 2 ) NOT NULL AFTER `special_type` ,
ADD `offer` INT( 11 ) NULL DEFAULT NULL AFTER `discount`;
ALTER TABLE `sevence`.`event` ADD INDEX ( `offer` );
ALTER TABLE `event` ADD FOREIGN KEY ( `offer` ) REFERENCES `sevence`.`offer` (`offer`);
ALTER TABLE `event` ADD `discount_desc` VARCHAR( 100 ) NOT NULL AFTER `discount`;

# update 11/29/08

ALTER TABLE offer ADD COLUMN expire_type tinyint NOT NULL DEFAULT 1 AFTER lead_days;
ALTER TABLE offer ADD COLUMN start date;
ALTER TABLE offer ADD COLUMN end date;

INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) values (2850, 'event', 'Events', 'event', 0, 30, 1000, 1);

# update 12/01/08

ALTER TABLE `event` ADD `discount_desc` VARCHAR( 100 ) NOT NULL AFTER `discount`;
ALTER TABLE `event` ADD `threshold` INT( 5 ) NOT NULL DEFAULT '0' AFTER `discount_desc` ;
ALTER TABLE `gift_promo` ADD `gift` INT( 11 ) NULL AFTER `offer` ;
ALTER TABLE `gift_promo` ADD INDEX ( `gift` ) ;
ALTER TABLE `offer` DROP `default_offer`;
ALTER TABLE `offer` ADD `default_offer` INT( 11 ) NULL AFTER `offer_type` ;
ALTER TABLE `offer` ADD INDEX ( `default_offer` ) ;
ALTER TABLE `offer` ADD FOREIGN KEY ( `default_offer` ) REFERENCES `sevence`.`offer` (`offer`);

INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active)
values (3, 'event', 'Events', 'event', 0, 90, 1000, 1);

INSERT INTO offer (org, offer_type, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days)
VALUES (3, (SELECT offer_type FROM offer_type WHERE offer_base='event' AND org = 3 LIMIT 1),
'Free $10 Gift Certificate', 1, '$10', 'Thank you!', 'Not valid with any other promotion.',
(SELECT org_gift_design FROM org_gift_design WHERE org = 3 ORDER BY popularity DESC LIMIT 1), 0, 90);
INSERT INTO offer (org, offer_type, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days)
VALUES (3, (SELECT offer_type FROM offer_type WHERE offer_base='event' AND org = 3 LIMIT 1),
'Free $20 Gift Certificate', 1, '$20', 'Thank you!', 'Not valid with any other promotion.',
(SELECT org_gift_design FROM org_gift_design WHERE org = 3 ORDER BY popularity DESC LIMIT 1), 0, 90);
INSERT INTO offer (org, offer_type, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days)
VALUES (3, (SELECT offer_type FROM offer_type WHERE offer_base='event' AND org = 3 LIMIT 1),
'Free $30 Gift Certificate', 1, '$30', 'Thank you!', 'Not valid with any other promotion.',
(SELECT org_gift_design FROM org_gift_design WHERE org = 3 ORDER BY popularity DESC LIMIT 1), 0, 90);
INSERT INTO offer (org, offer_type, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days)
VALUES (3, (SELECT offer_type FROM offer_type WHERE offer_base='event' AND org = 3 LIMIT 1),
'Free $40 Gift Certificate', 1, '$40', 'Thank you!', 'Not valid with any other promotion.',
(SELECT org_gift_design FROM org_gift_design WHERE org = 3 ORDER BY popularity DESC LIMIT 1), 0, 90);
INSERT INTO offer (org, offer_type, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days)
VALUES (3, (SELECT offer_type FROM offer_type WHERE offer_base='event' AND org = 3 LIMIT 1),
'Free $50 Gift Certificate', 1, '$50', 'Thank you!', 'Not valid with any other promotion.',
(SELECT org_gift_design FROM org_gift_design WHERE org = 3 ORDER BY popularity DESC LIMIT 1), 0, 90);
INSERT INTO offer (org, offer_type, name, active, value, message, gift_legalese, org_gift_design, lead_days, expire_days)
VALUES (3, (SELECT offer_type FROM offer_type WHERE offer_base='event' AND org = 3 LIMIT 1),
'20% Product Discount Coupon', 1, '20% discount on retail products', 'Thank you!', 'Not valid with any other promotion.',
(SELECT org_gift_design FROM org_gift_design WHERE org = 3 ORDER BY popularity DESC LIMIT 1), 0, 90);

# update 12/10/08

ALTER TABLE  `email_queue_failed` ADD  `email_return_path` VARCHAR( 255 ) NULL DEFAULT NULL AFTER  `email_from`;

# update 12/11/08

CREATE TABLE  `sevence`.`org_client_bounce` (
`org_client_bounce` INT NOT NULL AUTO_INCREMENT,
`org_client` INT NOT NULL ,
`bounce` INT NOT NULL DEFAULT  '0',
`last_client_journal` INT NULL DEFAULT NULL ,
`last_bounce_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (  `org_client_bounce` ) ,
UNIQUE (  `org_client` )
) ENGINE = INNODB;

ALTER TABLE `org_client_bounce` ADD FOREIGN KEY (  `org_client` ) REFERENCES  `sevence`.`org_client` (`org_client`);
ALTER TABLE `client_journal` add `bounced` TINYINT(1) NOT NULL DEFAULT 0;

# update 12/12/08

ALTER TABLE  `email_queue` ADD  `email_list_unsubscribe` VARCHAR( 100 ) NULL DEFAULT NULL AFTER  `email_return_path`;
ALTER TABLE  `email_queue_failed` ADD  `email_list_unsubscribe` VARCHAR( 100 ) NULL DEFAULT NULL AFTER  `email_return_path`;

# update 12/17/08

