ALTER TABLE `gift_amount` ADD `currency` TINYINT( 1 ) NOT NULL AFTER `handling` ,
ADD `billing_amount` DECIMAL( 12, 2 ) NOT NULL AFTER `currency`;
UPDATE gift_amount, gift SET billing_amount = paid - tax WHERE gift_amount.gift = gift.gift AND gift.sold_via = 1 AND
transaction_type = 0;

# update 01/10/09

CREATE TABLE `sevence`.`org_child` (
`org_child` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`parent_org` INT( 11 ) NOT NULL ,
`child_org` INT( 11 ) NOT NULL
) ENGINE = InnoDB;
ALTER TABLE `sevence`.`org_child` ADD INDEX ( `parent_org` ) ;
ALTER TABLE `sevence`.`org_child` ADD INDEX ( `child_org` ) ;
ALTER TABLE `org_child` ADD FOREIGN KEY ( `parent_org` ) REFERENCES `sevence`.`org` (`org`);
ALTER TABLE `org_child` ADD FOREIGN KEY ( `child_org` ) REFERENCES `sevence`.`org` (`org`);

# update 01/16/09

# offers for SpaBoom (data only)
INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (3, 'signup', 'Email Signups', 'signup', 0, 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 (3, (SELECT offer_type FROM offer_type WHERE offer_base='signup' AND org = 3 LIMIT 1), 1, 'Ten dollars off your services total', 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 visit as our way of saying thank you.', 'Valid for $10 off total services.  Limit one per customer, minimum service of $20.', (select org_gift_design from org_gift_design where org = 3 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 (3, (SELECT offer_type FROM offer_type WHERE offer_base='signup' AND org = 3 LIMIT 1), 1, 'Five Dollars 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 services with no strings attached.", 'Valid for $5 off total services.', (select org_gift_design from org_gift_design where org = 3 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 (3, (SELECT offer_type FROM offer_type WHERE offer_base='signup' AND org = 3 LIMIT 1), 1, '10% Off Total Services', 1, '10%', "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 visit with us.  Come on by, say hello, and enjoy yourself!", 'Valid for 10% off total services.  Limit one per customer, minimum service total of $50 required', (select org_gift_design from org_gift_design where org = 3 order by popularity desc limit 1), 30, 60);

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', 3, 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 = 3 LIMIT 1), 1, 0);

# update 01/19/09

# hours
ALTER TABLE org_hour CHANGE sunday_open sunday_open time NULL;
ALTER TABLE org_hour CHANGE sunday_close sunday_close time NULL;
ALTER TABLE org_hour CHANGE monday_open monday_open time NULL;
ALTER TABLE org_hour CHANGE monday_close monday_close time NULL;
ALTER TABLE org_hour CHANGE tuesday_open tuesday_open time NULL;
ALTER TABLE org_hour CHANGE tuesday_close tuesday_close time NULL;
ALTER TABLE org_hour CHANGE wednesday_open wednesday_open time NULL;
ALTER TABLE org_hour CHANGE wednesday_close wednesday_close time NULL;
ALTER TABLE org_hour CHANGE thursday_open thursday_open time NULL;
ALTER TABLE org_hour CHANGE thursday_close thursday_close time NULL;
ALTER TABLE org_hour CHANGE friday_open friday_open time NULL;
ALTER TABLE org_hour CHANGE friday_close friday_close time NULL;
ALTER TABLE org_hour CHANGE saturday_open saturday_open time NULL;
ALTER TABLE org_hour CHANGE saturday_close saturday_close time NULL;
UPDATE org_hour SET sunday_open=NULL WHERE sunday_open='00:00:00';
UPDATE org_hour SET sunday_close=NULL WHERE sunday_close='00:00:00';
UPDATE org_hour SET monday_open=NULL WHERE monday_open='00:00:00';
UPDATE org_hour SET monday_close=NULL WHERE monday_close='00:00:00';
UPDATE org_hour SET tuesday_open=NULL WHERE tuesday_open='00:00:00';
UPDATE org_hour SET tuesday_close=NULL WHERE tuesday_close='00:00:00';
UPDATE org_hour SET wednesday_open=NULL WHERE wednesday_open='00:00:00';
UPDATE org_hour SET wednesday_close=NULL WHERE wednesday_close='00:00:00';
UPDATE org_hour SET thursday_open=NULL WHERE thursday_open='00:00:00';
UPDATE org_hour SET thursday_close=NULL WHERE thursday_close='00:00:00';
UPDATE org_hour SET friday_open=NULL WHERE friday_open='00:00:00';
UPDATE org_hour SET friday_close=NULL WHERE friday_close='00:00:00';
UPDATE org_hour SET saturday_open=NULL WHERE saturday_open='00:00:00';
UPDATE org_hour SET saturday_close=NULL WHERE saturday_close='00:00:00';

# update 01/20/09

INSERT INTO offer_type (org, offer_base, name, email_id, lead_days, expire_days, last_offer_id, active) VALUES (3,'onetime','One Time Offers', 'onetime', 30, 60, 1000, 1);

# update 02/03/09

CREATE TABLE `part_master` (
  `part_master` int(11) NOT NULL auto_increment,
  `part_id` varchar(16) NOT NULL,
  `description` varchar(50) NOT NULL,
  `service_linked` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`part_master`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO part_master (part_id, description, service_linked) VALUES ('ar_comment', 'A/R Comment', 0);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('ar_headline', 'A/R Headline', 0);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('ar_intro', 'A/R Introduction', 0);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('client_wish_c', 'Wish List Sent', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('client_wish_h', 'Wish List Heading', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('client_wish_l', 'Wish List Add More', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('client_wish_l2', 'Wish List Hint', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('client_wish_s', 'Wish List Main Text', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('client_wish_w', 'Wish List Web Text', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_confirm', 'Email Confirmation', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_notsub', 'Email Subscriber Not Found', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_opt', 'Email Optional Data', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_removed', 'Email Unsubscribe', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_subscribe', 'Email Subscribe', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_token_err', 'Email Verification Error', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_unsub', 'Email Unsubscribe Confirm', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_verfy_err', 'Email Verify Not Found', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_verify', 'Email Subscribe Complete', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('gc_headline', 'Gift Certificate Headline', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('gc_intro', 'Gift Certificate Introduction', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('ml_opt_after', 'Mailing List Additional Mailings', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('ml_subscribe', 'Mailing List Subscription Selection', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('notify_all_h', 'Notify All Headline', 0);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('notify_all_r', 'Notify All Remove Alerts', 0);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('opt_subscribe', 'Email Optional Information', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('share_c', 'Share With a Friend Confirmation', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('share_h', 'Share With a Friend Headline', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('sidebar_tagline', 'Sidebar Tagline', 0);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('signup_offer', 'Offer Signup Enticement', 1);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('tagline', 'Tagline', 0);
INSERT INTO part_master (part_id, description, service_linked) VALUES ('se_headline', 'Spa Emergency Headline', 0);

# update 02/06/09

ALTER TABLE `org` ADD `request_days_out` TINYINT( 1 ) NOT NULL DEFAULT '2';
ALTER TABLE `org` ADD `booking_type` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `api_tx_key`;
ALTER TABLE `org` ADD `booking_url` VARCHAR( 100 ) NULL DEFAULT NULL;
ALTER TABLE `org` ADD `opentable_id` VARCHAR( 16 ) NULL DEFAULT NULL;
ALTER TABLE `org` ADD `booking_desc` VARCHAR( 100 ) NULL DEFAULT NULL;

UPDATE org, client_address, org_client SET booking_type = 2 WHERE
org.client = client_address.client AND client_address.address_type = 0 AND org.client = org_client.client AND org_client.org = 13 AND
service_website = 2;
UPDATE org, client_address, org_client SET booking_desc = "Call {phone} for reservations" WHERE
org.client = client_address.client AND client_address.address_type = 0 AND org.client = org_client.client AND org_client.org = 2646 AND
COALESCE(booking_desc, '') = '';
UPDATE org, client_address, org_client SET booking_desc = "Call {phone} for your appointment" WHERE
org.client = client_address.client AND client_address.address_type = 0 AND org.client = org_client.client AND org_client.org = 13 AND
COALESCE(booking_desc, '') = '';

ALTER TABLE `gift_amount` ADD `gratuity` DECIMAL( 12, 2 ) NOT NULL DEFAULT '0' AFTER `handling`;
ALTER TABLE `org` ADD `gift_gratuity` INT( 3 ) NOT NULL DEFAULT '-1' AFTER `gift_max`;
UPDATE gift_amount, gift SET billing_amount = paid WHERE gift_amount.gift = gift.gift AND gift.sold_via = 1 AND
transaction_type = 3;

# update 03/08/09

INSERT INTO part (org, part_id, content) values (3, 'signup_resend', "<h1>Already signed up</h1>You've already confirmed your email with us, but that's OK - we have sent you a copy of our promotional offer just in case you've misplaced it. Thank you!");
INSERT INTO part_master (part_id, description, service_linked) VALUES ('signup_resend', 'Duplicate offer re-sent', 1);

# update 03/10/09

ALTER TABLE  `affiliate_org` ADD  `signup_paid` TINYINT( 1 ) NOT NULL DEFAULT  '0';

# update 03/12/09

INSERT INTO part (org, part_id, content) values (3, 'email_techerr', "<h1>Email Specials</h1>I'm sorry, but due to a technical error we cannot locate your information. Please sign up with us again! Thank you!");
INSERT INTO part_master (part_id, description, service_linked) VALUES ('email_techerr', 'Cannot find client journal error', 1);

# update 03/14/09

ALTER TABLE postal ADD COLUMN gmt_offset tinyint;
ALTER TABLE postal ADD COLUMN daylight_savings char(1);

# update 03/19/09

CREATE TABLE `sevence`.`org_book` (
`org` INT( 11 ) NOT NULL ,
PRIMARY KEY ( `org` )
) ENGINE = InnoDB;
ALTER TABLE `org_book` ADD `request_days_out` TINYINT( 1 ) NOT NULL DEFAULT '2';
ALTER TABLE `org_book` ADD `booking_type` TINYINT( 1 ) NOT NULL DEFAULT '1';
ALTER TABLE `org_book` ADD `booking_url` VARCHAR( 100 ) NULL DEFAULT NULL;
ALTER TABLE `org_book` ADD `opentable_id` VARCHAR( 16 ) NULL DEFAULT NULL;
ALTER TABLE `org_book` ADD `booking_desc` VARCHAR( 100 ) NULL DEFAULT NULL;
INSERT INTO org_book (org, request_days_out, booking_type, booking_url, opentable_id, booking_desc)
SELECT org, request_days_out, booking_type, booking_url, opentable_id, booking_desc FROM org;
ALTER TABLE `org_book` CHANGE `booking_desc` `booking_desc` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `org_book` ADD `booking_cancel` TEXT NULL DEFAULT NULL;

UPDATE part SET content = '<h1>Book Online</h1>' WHERE part_id = 'ar_headline' AND content = '<h1>Request Appointment</h1>';

UPDATE page_nav SET name = 'Book Online', title = 'Book online, right now!' WHERE version = 0 AND name = BINARY 'Request Appointment' AND
COALESCE(url, '') = '';
UPDATE page_nav SET name = 'book online', title = 'Book online, right now!' WHERE version = 0 AND name = BINARY 'request appointment' AND
COALESCE(url, '') = '';

UPDATE org, client_address, org_client, org_book SET booking_cancel = "Appointments are held with a credit card. At the time of service, you can alternatively use a gift certificate. The full service fee is charged for cancellations or no-shows without 24 hour notice." WHERE
org.client = client_address.client AND client_address.address_type = 0 AND org.client = org_client.client AND org_client.org = 13 AND
COALESCE(booking_cancel, '') = '';

INSERT INTO part (org, part_id, content) VALUES (3, 'booking_call',
"<h1>Appointments</h1><div boomtime='left' id='online_booking'></div><p><span class='mceNonEditable'>{booking_desc}</span></p><p><span class='mceNonEditable'>{booking_cancel}</span></p><h3>Our hours</h3><div boomtime='left' id='hour_edit'><span class='mceNonEditable'>{hours}</span></div>");

UPDATE org_book SET booking_desc = 'Please call {phone} to schedule your appointment.' WHERE booking_desc = 'Call {phone} for your appointment';
UPDATE org_book SET booking_desc = 'Please call {phone} for reservations.' WHERE booking_desc = 'Call {phone} for reservations';

INSERT INTO part (org, part_id, content) VALUES (2850, 'booking_call',
"<h1>Reservations</h1><div boomtime='left' id='online_booking'></div><p><span class='mceNonEditable'>{booking_desc}</span></p><p><span class='mceNonEditable'>{booking_cancel}</span></p><h3>Our hours</h3><div boomtime='left' id='hour_edit'><span class='mceNonEditable'>{hours}</span></div>");

INSERT INTO part (org, part_id, content) VALUES (2850, 'booking_ot',
"<h1>Reservations</h1><div boomtime='left' id='online_booking'></div><p><span class='mceNonEditable'>{booking_cancel}</span></p>");

UPDATE part SET content = "<p>Please select your desired services, date and time. We'll respond shortly to confirm your&nbsp;reservation.</p><div boomtime='left' id='online_booking'></div><p><span class='mceNonEditable'>{booking_cancel}</span></p>"
WHERE part_id = 'ar_intro' and org = 3;

SELECT @default_content := content FROM event WHERE org = 3 AND event_type = 5;
UPDATE event SET content = "<h1>Instant Gift Certificates</h1>
<p>Design your gift certificate, preview it, and then send or print it immediately. It's fast and easy!</p>
<p>{instant_gc_link}</p>
<h1>Appointments</h1>
<p>Book your next appointment, right now!</p>
<p>{booking_link}</p>"
WHERE event_type = 5 AND content = @default_content;

ALTER TABLE `org` DROP `booking_type` ,
DROP `request_days_out` ,
DROP `booking_url` ,
DROP `opentable_id` ,
DROP `booking_desc` ;

UPDATE org_book SET booking_type = 2 WHERE org IN (SELECT org_client.org FROM request, org_client WHERE request.org_client = org_client.org_client);

# blog
CREATE TABLE `article` (
  `article` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL,
  `prof` int(11) NOT NULL,
  `headline` text,
  `excerpt` varchar(255) default NULL,
  `narrative` text NOT NULL,
  `content` text,
  `editor` tinyint(4) NOT NULL default '0',
  `date_added` datetime default NULL,
  `date_published` datetime default NULL,
  `date_edited` datetime default NULL,
  `edited_prof` int(11) default NULL,
  `article_status` tinyint(4) NOT NULL default '0',
  `comment_status` tinyint(4) NOT NULL default '0',
  `parent_article` int(11) default NULL,
  `photo` int(11) default NULL,
  `photo_filename` varchar(100) default NULL,
  `photo_pos` tinyint(4) NOT NULL default '1',
  `permalink` varchar(100) default NULL,
  PRIMARY KEY  (`article`),
  KEY `org` (`org`),
  KEY `prof` (`prof`),
  KEY `permalink` (`permalink`),
  CONSTRAINT `article_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`),
  CONSTRAINT `article_ibfk_2` FOREIGN KEY (`prof`) REFERENCES `prof` (`prof`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `article_category` (
  `article_category` int(11) NOT NULL auto_increment,
  `default_article_category` int(11) default NULL,
  `org` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) default NULL,
  `hide` tinyint(4) NOT NULL default '0',
  `permalink` varchar(100) default NULL,
  PRIMARY KEY  (`article_category`),
  KEY `org` (`org`),
  KEY `default_article_category` (`default_article_category`),
  CONSTRAINT `article_category_ibfk_2` FOREIGN KEY (`default_article_category`) REFERENCES `article_category` (`article_category`),
  CONSTRAINT `article_category_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `article_article_list` (
  `article_article_list` int(11) NOT NULL auto_increment,
  `article` int(11) NOT NULL,
  `article_category` int(11) NOT NULL,
  PRIMARY KEY  (`article_article_list`),
  KEY `article` (`article`),
  KEY `article_category` (`article_category`),
  CONSTRAINT `article_article_list_ibfk_1` FOREIGN KEY (`article`) REFERENCES `article` (`article`),
  CONSTRAINT `article_article_list_ibfk_2` FOREIGN KEY (`article_category`) REFERENCES `article_category` (`article_category`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `article_setting` (
  `article_setting` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL,
  `display` tinyint(4) NOT NULL default '1',
  `per_page` int(11) NOT NULL default '3',
  PRIMARY KEY  (`article_setting`),
  KEY `org` (`org`),
  CONSTRAINT `article_setting_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `article_category` VALUES
	(1,NULL,3,'General','General News',0,'general'),
	(2,NULL,3,'Special Announcements','Special Announcements',0,'special');

# update 04/08/09

ALTER TABLE `request` ADD `date_proposed` DATETIME NULL AFTER `date_option_3`;

# update 04/11/09

ALTER TABLE `website` ADD `widget_css` TEXT NOT NULL DEFAULT '';

# update 04/12/09

ALTER TABLE `org` ADD `run_final_bill` TINYINT( 1 ) NOT NULL DEFAULT '0';

# update 04/14/09

ALTER TABLE `org_book` ADD `booking_no` TINYINT( 1 ) NOT NULL DEFAULT '0';

# update 04/20/09

ALTER TABLE prof ADD COLUMN can_market tinyint NOT NULL DEFAULT 0 AFTER can_cc;
UPDATE prof SET can_market=1 WHERE can_setup=1 OR can_client=1 OR can_gc=1 OR can_cc=1;

# update 04/23/09

CREATE TABLE `geo_block` (
  `start_ip` bigint(20) NOT NULL,
  `end_ip` bigint(20) NOT NULL,
  `location_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `geo_location` (
  `id` bigint(20) NOT NULL,
  `country` char(2) NOT NULL,
  `region` char(2) default NULL,
  `city` varchar(75) default NULL,
  `postal_code` varchar(15) default NULL,
  `latitude` decimal(6,4) NOT NULL,
  `longitude` decimal(7,4) default NULL,
  `metro_code` int(11) default NULL,
  `area_code` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#LOAD DATA LOCAL INFILE "GeoLiteCity-Blocks.csv" INTO TABLE geo_block FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n";
#LOAD DATA LOCAL INFILE "GeoLiteCity-Location.csv" INTO TABLE geo_location FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n";

# update 04/27/09

ALTER TABLE  `gift` ADD  `referring_org` INT NULL DEFAULT NULL AFTER `affiliate`, ADD INDEX ( `referring_org` );
ALTER TABLE  `gift` ADD FOREIGN KEY (  `referring_org` ) REFERENCES  `sevence`.`org` (`org`);

# update 04/28/09

ALTER TABLE prof CHANGE can_market can_market tinyint(1) NOT NULL DEFAULT 0;

INSERT INTO part (org, part_id, content) values (3, 'article_headline', "<h1>{short_name}'s Blog</h1>");
INSERT INTO part_master (part_id, description, service_linked) VALUES ('article_headline', 'Blog Title', 0);

# update 05/05/09

ALTER TABLE article ADD COLUMN locale_specific tinyint NOT NULL DEFAULT 0;
ALTER TABLE article ADD COLUMN locale int DEFAULT 0;
ALTER TABLE article ADD COLUMN summary_photo int;
ALTER TABLE article ADD COLUMN summary_photo_filename varchar(100);

CREATE TABLE `locale` (
  `locale` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `hide` tinyint(4) NOT NULL default '0',
  `slug` varchar(50) NOT NULL,
  `location` int(11) NOT NULL,
  PRIMARY KEY  (`locale`),
  KEY `org` (`org`),
  CONSTRAINT `locale_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB;
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Atlanta',0,'atlanta',209);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Chicago',0,'chicago',287);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Dallas/Ft Worth',0,'dallas',869);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Denver/Boulder',0,'denver',156);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Fort Lauderdale/Palm Beaches',0,'fortlauderdale',187);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Los Angeles',0,'losangeles',127);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Miami',0,'miami',187);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'New Jersey',0,'newjersey',35);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Orange County',0,'orangecounty',127);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Philadelphia',0,'philadelphia',761);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'New York',0,'newyork',650);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Phoenix/Scottsdale',0,'phoenix',112);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'San Diego',0,'sandiego',139);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'San Francisco',0,'sanfrancisco',140);
INSERT INTO locale (org,name,hide,slug,location) VALUES (3727,'Seattle',0,'seattle',957);
INSERT INTO locale (org,name,hide,slug) VALUES (3727,'Toronto',0,'toronto');

CREATE TABLE `article_locale` (
  `article_locale` int(11) NOT NULL auto_increment,
  `article` int(11) NOT NULL,
  `locale` int(11) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY  (`article_locale`),
  KEY `article` (`article`),
  KEY `locale` (`locale`),
  CONSTRAINT `article_locale_ibfk_1` FOREIGN KEY (`article`) REFERENCES `article` (`article`),
  CONSTRAINT `article_locale_ibfk_2` FOREIGN KEY (`locale`) REFERENCES `locale` (`locale`)
) ENGINE=InnoDB;

CREATE TABLE `tag` (
  `tag` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL default '0',
  `type` int(11) NOT NULL,
  `tag_tag` varchar(20) NOT 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  (`tag`),
  KEY `photo` (`photo`),
  KEY `org` (`org`),
  CONSTRAINT `tag_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `article_tag` (
  `article_tag` int(11) NOT NULL auto_increment,
  `article` int(11) NOT NULL,
  `tag` int(11) NOT NULL,
  PRIMARY KEY  (`article_tag`),
  KEY `article` (`article`),
  KEY `tag` (`tag`),
  CONSTRAINT `article_tag_ibfk_2` FOREIGN KEY (`tag`) REFERENCES `tag` (`tag`),
  CONSTRAINT `article_tag_ibfk_1` FOREIGN KEY (`article`) REFERENCES `article` (`article`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `org_tag` (
  `org_tag` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL,
  `tag` int(11) NOT NULL,
  PRIMARY KEY  (`org_tag`),
  KEY `article` (`org`),
  KEY `tag` (`tag`),
  CONSTRAINT `org_tag_ibfk_2` FOREIGN KEY (`tag`) REFERENCES `tag` (`tag`),
  CONSTRAINT `org_tag_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 06/01/09

ALTER TABLE article ADD COLUMN show_tagged tinyint NOT NULL DEFAULT 1;

# update 06/02/09

ALTER TABLE article_locale ADD COLUMN editor tinyint not null default 0 AFTER content;
ALTER TABLE article_locale ADD COLUMN narrative text NOT NULL AFTER locale;

# update 06/02/09

INSERT INTO part (org, part_id, content) VALUES (3, 'art_locale_links', '<h4>More on {headline} in:</h4>');
INSERT INTO part (org, part_id, content) VALUES (2850, 'art_locale_links', '<h4>More on {headline} in:</h4>');
INSERT INTO part_master (part_id, description, service_linked) VALUES ('art_locale_links', 'Local heading for more articles', 0);

# update 06/03/09

ALTER TABLE article_setting ADD COLUMN feature tinyint NOT NULL DEFAULT 0;
DELETE FROM part_master WHERE part_id='art_locale_links';
DELETE FROM part WHERE part_id='art_locale_links';
ALTER TABLE article ADD COLUMN feature tinyint NOT NULL DEFAULT 1;

# update 06/04/09

ALTER TABLE LOCALE ADD COLUMN header_page_id VARCHAR(50);

# update 06/05/09

ALTER TABLE article ADD COLUMN carousel_photo int;
ALTER TABLE article ADD COLUMN carousel_photo_filename varchar(100);

# update 06/11/09

ALTER TABLE article ADD COLUMN summary_list_photo_filename VARCHAR(100) AFTER summary_photo_filename;
ALTER TABLE article ADD COLUMN carousel_list_photo_filename VARCHAR(100) AFTER carousel_photo_filename;

# update 06/16/09

ALTER TABLE locale ADD COLUMN text_color varchar(7);

# update 06/19/09

ALTER TABLE `org_book` ADD `booking_manage` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `booking_type` ;
ALTER TABLE `org` ADD `service_agc` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `service_directory` ;
UPDATE org SET service_agc = -1;

# update 06/23/09

ALTER TABLE org_hour ADD COLUMN start date AFTER client_address;
ALTER TABLE org_hour ADD COLUMN end date AFTER start;

ALTER TABLE org_hour ADD COLUMN sunday_open_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN sunday_close_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN monday_open_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN monday_close_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN tuesday_open_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN tuesday_close_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN wednesday_open_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN wednesday_close_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN thursday_open_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN thursday_close_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN friday_open_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN friday_close_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN saturday_open_ext time default NULL;
ALTER TABLE org_hour ADD COLUMN saturday_close_ext time default NULL;
ALTER TABLE org_hour ADD INDEX (org, start, end);

UPDATE org_hour, org SET start=DATE_FORMAT(entered_date, '%Y-%m-%d') WHERE org_hour.org=org.org;
UPDATE org_hour a, org_hour b SET a.sunday_open_ext = b.sunday_open, a.sunday_close_ext = b.sunday_close,
	a.monday_open_ext = b.monday_open, a.monday_close_ext = b.monday_close,
	a.tuesday_open_ext = b.tuesday_open, a.tuesday_close_ext = b.tuesday_close,
	a.wednesday_open_ext = b.wednesday_open, a.wednesday_close_ext = b.wednesday_close,
	a.thursday_open_ext = b.thursday_open, a.thursday_close_ext = b.thursday_close,
	a.friday_open_ext = b.friday_open, a.friday_close_ext = b.friday_close,
	a.saturday_open_ext = b.saturday_open, a.saturday_close_ext = b.saturday_close
	WHERE a.org=b.org AND a.client_address=b.client_address AND a.hour_set=0 AND b.hour_set=1;

# update 07/12/09

CREATE TABLE `org_hour_except` (
  `org_hour_except` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL,
  `client_address` int(11) NOT NULL,
  `start` date default NULL,
  `end` date default NULL,
  `open` time default NULL,
  `close` time default NULL,
  `open_ext` time default NULL,
  `close_ext` time default NULL,
  PRIMARY KEY  (`org_hour_except`),
  KEY `org` (`org`),
  CONSTRAINT `org_hour_except_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE org_hour_except ADD FOREIGN KEY (org) references org (org);
ALTER TABLE org_hour_except ADD FOREIGN KEY (client_address) REFERENCES client_address (client_address);

# update 07/17/09

ALTER TABLE article_setting ADD COLUMN byline tinyint NOT NULL DEFAULT 0;
ALTER TABLE article_setting ADD COLUMN tagline tinyint NOT NULL DEFAULT 0;
ALTER TABLE article ADD COLUMN byline varchar(100) AFTER content;
ALTER TABLE article ADD COLUMN tagline varchar(100) after byline;

# update 07/21/09

CREATE TABLE `prof_hour` (
  `prof_hour` int(11) NOT NULL auto_increment,
  `prof` int(11) NOT NULL default '0',
  `client_address` int(11) NOT NULL,
  `start` date default NULL,
  `end` date default NULL,
  `sunday_start` time default NULL,
  `sunday_end` time default NULL,
  `monday_start` time default NULL,
  `monday_end` time default NULL,
  `tuesday_start` time default NULL,
  `tuesday_end` time default NULL,
  `wednesday_start` time default NULL,
  `wednesday_end` time default NULL,
  `thursday_start` time default NULL,
  `thursday_end` time default NULL,
  `friday_start` time default NULL,
  `friday_end` time default NULL,
  `saturday_start` time default NULL,
  `saturday_end` time default NULL,
  `sunday_start_ext` time default NULL,
  `sunday_end_ext` time default NULL,
  `monday_start_ext` time default NULL,
  `monday_end_ext` time default NULL,
  `tuesday_start_ext` time default NULL,
  `tuesday_end_ext` time default NULL,
  `wednesday_start_ext` time default NULL,
  `wednesday_end_ext` time default NULL,
  `thursday_start_ext` time default NULL,
  `thursday_end_ext` time default NULL,
  `friday_start_ext` time default NULL,
  `friday_end_ext` time default NULL,
  `saturday_start_ext` time default NULL,
  `saturday_end_ext` time default NULL,
  PRIMARY KEY  (`prof_hour`),
  KEY `prof` (`prof`),
  KEY `prof_2` (`prof`,`start`,`end`),
  KEY `client_address` (`client_address`),
  CONSTRAINT `prof_hour_ibfk_2` FOREIGN KEY (`client_address`) REFERENCES `client_address` (`client_address`),
  CONSTRAINT `prof_hour_ibfk_1` FOREIGN KEY (`prof`) REFERENCES `prof` (`prof`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

CREATE TABLE `prof_hour_except` (
  `prof_hour_except` int(11) NOT NULL auto_increment,
  `prof` int(11) NOT NULL,
  `client_address` int(11) NOT NULL,
  `start` date default NULL,
  `end` date default NULL,
  `start_pri` time default NULL,
  `end_pri` time default NULL,
  `start_ext` time default NULL,
  `end_ext` time default NULL,
  PRIMARY KEY  (`prof_hour_except`),
  KEY `prof` (`prof`),
  KEY `client_address` (`client_address`),
  CONSTRAINT `prof_hour_except_ibfk_3` FOREIGN KEY (`client_address`) REFERENCES `client_address` (`client_address`),
  CONSTRAINT `prof_hour_except_ibfk_2` FOREIGN KEY (`prof`) REFERENCES `prof` (`prof`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 07/24/09

INSERT INTO partner (partner, name) VALUES (5, 'Dining Out');

# update 08/05/09

ALTER TABLE page ADD COLUMN page_class VARCHAR(100);
ALTER TABLE page_backup ADD COLUMN page_class VARCHAR(100);

ALTER TABLE page ADD COLUMN meta_desc TEXT;
ALTER TABLE page_backup ADD COLUMN meta_desc TEXT;

ALTER TABLE page ADD COLUMN meta_keyword TEXT;
ALTER TABLE page_backup ADD COLUMN meta_keyword TEXT;

UPDATE page, website SET page.meta_desc = website.meta_desc, page.meta_keyword = website.meta_keyword where page.org = website.org and page.page_id='home' or page.org = website.org and page.page_id='lgift';
UPDATE page_backup, page SET page_backup.meta_desc = page.meta_desc, page_backup.meta_keyword = page.meta_keyword where page_backup.org = page.org and page_backup.page_id='home' and page.page_id='home';

# update 08/19/09

CREATE TABLE `photo_tag` (
  `photo_tag` int(11) NOT NULL auto_increment,
  `org` int(11) NOT NULL,
  `photo` int(11) NOT NULL,
  `tag` varchar(100) default NULL,
  PRIMARY KEY  (`photo_tag`),
  UNIQUE KEY `org` (`org`,`photo`,`tag`),
  KEY `photo` (`photo`),
  CONSTRAINT `photo_tag_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`),
  CONSTRAINT `photo_tag_ibfk_2` FOREIGN KEY (`photo`) REFERENCES `photo` (`photo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# update 08/15/09

ALTER TABLE org_partner ADD COLUMN id varchar(20);
ALTER TABLE org ADD COLUMN service_magazine tinyint NOT NULL DEFAULT 0 AFTER service_dns;

# update 08/18/09

ALTER TABLE org CHANGE service_magazine service_info tinyint not null default 0;

# update 08/21/09

ALTER TABLE menu_section ADD COLUMN partner_xref varchar(20);
ALTER TABLE menu_section ADD KEY (partner_xref);
ALTER TABLE menu_item ADD COLUMN partner_xref varchar(20);
ALTER TABLE menu_item ADD KEY (partner_xref);

# update 08/27/09

CREATE TABLE `org_analysis` (
  `org` int(11) NOT NULL,
  `run_date` datetime default NULL,
  `analysis_point` int(11) NOT NULL default '0',
  `max_point` int(11) NOT NULL,
  `website_active` tinyint(4) default NULL,
  `website_link` tinyint(4) default NULL,
  `website_widget` tinyint(4) default NULL,
  `email_created` tinyint(4) default NULL,
  `igc_email_per` float default NULL,
  PRIMARY KEY  (`org`));

# update 09/22/09

ALTER TABLE org_analysis ADD COLUMN schedule_link tinyint after website_link;

# update 09/24/09

ALTER TABLE org_analysis ADD COLUMN offer_created int after email_created;
ALTER TABLE org_analysis ADD COLUMN article_created int after offer_created;
ALTER TABLE org_analysis CHANGE email_created email_created int;

# update 09/29/09

ALTER TABLE org ADD COLUMN directory_badge tinyint(1) NOT NULL DEFAULT 1;

# update 10/02/09

CREATE TABLE `sevence`.`book` (
`book` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`org_tran` INT( 11 ) NULL ,
`book_id` INT( 10 ) NOT NULL ,
`entered_date` DATETIME NOT NULL
) ENGINE = InnoDB;
ALTER TABLE `sevence`.`book` ADD INDEX `org_tran` ( `org_tran` );
ALTER TABLE `sevence`.`book` ADD INDEX `book_id` ( `book_id` );
ALTER TABLE `book` ADD FOREIGN KEY ( `org_tran` ) REFERENCES `sevence`.`org_tran` (`org_tran`);

CREATE TABLE `sevence`.`book_slot` (
`book_slot` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`book` INT( 11 ) NOT NULL ,
`resource` INT( 11 ) NULL ,
`service_ingr` INT( 11 ) NULL ,
`start` DATETIME NOT NULL ,
`end` DATETIME NOT NULL
) ENGINE = InnoDB;
ALTER TABLE `sevence`.`book_slot` ADD INDEX `book` ( `book` );
ALTER TABLE `sevence`.`book_slot` ADD INDEX `resource` ( `resource` );
ALTER TABLE `sevence`.`book_slot` ADD INDEX `service_ingr` ( `service_ingr` );
ALTER TABLE `book_slot` ADD FOREIGN KEY ( `book` ) REFERENCES `sevence`.`book` (`book`);
ALTER TABLE `book_slot` ADD FOREIGN KEY ( `service_ingr` ) REFERENCES `sevence`.`service_ingr` (`service_ingr`);
ALTER TABLE `book_slot` ADD FOREIGN KEY ( `resource` ) REFERENCES `sevence`.`resource` (`resource`);

ALTER TABLE `resource` ADD `name` VARCHAR( 16 ) NOT NULL DEFAULT '';
ALTER TABLE `resource` ADD `hide` TINYINT( 1 ) NOT NULL DEFAULT '0';
ALTER TABLE `resource` ADD `sort` DECIMAL( 12, 8 ) NOT NULL DEFAULT '0' AFTER `name`;

ALTER TABLE `book` ADD `org` INT( 11 ) NOT NULL AFTER `book`;
ALTER TABLE `sevence`.`book` ADD INDEX `org` ( `org` );
ALTER TABLE `book` ADD FOREIGN KEY ( `org` ) REFERENCES `sevence`.`org` (`org`);
ALTER TABLE `book_slot` ADD `note` TEXT NULL DEFAULT NULL;
ALTER TABLE `resource` ADD `client_count` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `name`;
ALTER TABLE `book_slot` ADD `org_client` INT( 11 ) NULL AFTER `resource`;
ALTER TABLE `sevence`.`book_slot` ADD INDEX `org_client` ( `org_client` );
ALTER TABLE `book_slot` ADD FOREIGN KEY ( `org_client` ) REFERENCES `sevence`.`org_client` (`org_client`);
ALTER TABLE `service_ingr` ADD `prof_count` TINYINT( 1 ) NOT NULL DEFAULT '1' AFTER `duration`;

CREATE TABLE `sevence`.`book_slot_prof` (
`book_slot_prof` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`book_slot` INT( 11 ) NOT NULL ,
`prof` INT( 11 ) NOT NULL
) ENGINE = InnoDB;
ALTER TABLE `sevence`.`book_slot_prof` ADD INDEX `prof` ( `prof` );
ALTER TABLE `sevence`.`book_slot_prof` ADD INDEX `book_slot` ( `book_slot` );
ALTER TABLE `book_slot_prof` ADD FOREIGN KEY ( `prof` ) REFERENCES `sevence`.`prof` (`prof`);
ALTER TABLE `book_slot_prof` ADD FOREIGN KEY ( `book_slot` ) REFERENCES `sevence`.`book_slot` (`book_slot`);

ALTER TABLE `prof` ADD `gender` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `initials`;

CREATE TABLE `sevence`.`prof_group` (
`prof_group` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`org` INT( 11 ) NOT NULL ,
`name` VARCHAR( 50 ) NOT NULL DEFAULT '',
`hide` TINYINT( 1 ) NOT NULL DEFAULT '0'
) ENGINE = InnoDB;
ALTER TABLE `sevence`.`prof_group` ADD INDEX ( `org` );
ALTER TABLE `prof_group` ADD FOREIGN KEY ( `org` ) REFERENCES `sevence`.`org` (`org`);
ALTER TABLE `prof_group` ADD `sort` DECIMAL( 12, 8 ) NOT NULL DEFAULT '0' AFTER `name`;
ALTER TABLE `prof` ADD `prof_group` INT( 11 ) NULL AFTER `gender` , ADD INDEX ( prof_group );
ALTER TABLE `prof` ADD FOREIGN KEY ( `prof_group` ) REFERENCES `sevence`.`prof_group` (`prof_group`);

CREATE TABLE `sevence`.`bookday_prof` (
`prof` INT( 11 ) NOT NULL ,
`date` DATE NOT NULL ,
`timeline` VARBINARY( 288 ) NOT NULL ,
PRIMARY KEY ( `prof` , `date` )
) ENGINE = InnoDB;
ALTER TABLE `bookday_prof` ADD FOREIGN KEY ( `prof` ) REFERENCES `sevence`.`prof` (`prof`);

CREATE TABLE `sevence`.`bookday_resource` (
`resource` INT( 11 ) NOT NULL ,
`date` DATE NOT NULL ,
`timeline` VARBINARY( 288 ) NOT NULL ,
PRIMARY KEY ( `resource` , `date` )
) ENGINE = InnoDB;
ALTER TABLE `bookday_resource` ADD FOREIGN KEY ( `resource` ) REFERENCES `sevence`.`resource` (`resource`);

ALTER TABLE `book_slot` ADD `prof_request` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `end` ;

CREATE TABLE `sevence`.`prof_setting` (
`prof` INT( 11 ) NOT NULL ,
`page` VARCHAR( 20 ) NOT NULL ,
`data` TEXT NOT NULL ,
PRIMARY KEY ( `prof` , `page` )
) ENGINE = InnoDB;
ALTER TABLE `prof_setting` ADD FOREIGN KEY ( `prof` ) REFERENCES `sevence`.`prof` (`prof`);

# update 10/04/09

ALTER TABLE org_analysis ADD COLUMN jos_link int(11) AFTER schedule_link;
ALTER TABLE org_analysis ADD COLUMN bogo int(11) AFTER offer_created;
ALTER TABLE org_analysis ADD COLUMN event_service int(11);
ALTER TABLE org_analysis ADD COLUMN gc_design int(11);
ALTER TABLE org_analysis ADD COLUMN custom_email int(11) after email_created;

# update 10/06/09

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

# update 10/07/09

# email design
ALTER TABLE email_design ADD COLUMN org int NOT NULL AFTER email_design;
UPDATE email_design SET org=3;
ALTER TABLE email_design ADD FOREIGN KEY (org) REFERENCES org (org);

# update 10/08/09

CREATE TABLE `client_journal_type` (
  `client_journal_type` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `sort` decimal(12,6) default NULL,
  `default_type` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`client_journal_type`)
) ENGINE=InnoDB;
INSERT INTO `client_journal_type` VALUES (1,'General','1.000000',1),(2,'A/R','2.000000',0),(3,'Joy of Spa','3.000000',0),(4,'Booking','4.000000',0),(5,'Beta','5.000000',0),(6,'Dining Out','6.000000',0),(7,'Sales Follow Up','7.000000',0),(8,'Write-Off','8.000000',0),(9,'Web Implementation','9.000000',0);

ALTER TABLE client_journal ADD COLUMN client_journal_type int(11) NOT NULL DEFAULT 1;
ALTER TABLE client_journal ADD FOREIGN KEY (client_journal_type) REFERENCES client_journal_type (client_journal_type);

# update 10/09/09

CREATE TABLE `org_analysis_def` (
  `org_analysis_def` int(11) NOT NULL AUTO_INCREMENT,
  `link_tag` varchar(20) DEFAULT NULL,
  `link` varchar(50) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`org_analysis_def`),
  KEY `link_tag` (`link_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO org_analysis_def (link_tag, link, name) VALUES ('SpaBooker', 'spa-booker.com', 'SpaBooker');
INSERT INTO org_analysis_def (link_tag, link, name) VALUES ('GiftCardCafe', 'thegiftcardcafe.com', 'Gift Card Cafe');
INSERT INTO org_analysis_def (link_tag, link, name) VALUES ('MindBody', 'clients.mindbodyonline.com', 'MindBody');

CREATE TABLE `org_analysis_link` (
  `org` int(11) NOT NULL,
  `link_tag` varchar(20) NOT NULL,
  `link_date` date DEFAULT NULL,
  `active` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`org`,`link_tag`),
  KEY `link_tag` (`link_tag`),
  CONSTRAINT `org_analysis_link_ibfk_2` FOREIGN KEY (`link_tag`) REFERENCES `org_analysis_def` (`link_tag`),
  CONSTRAINT `org_analysis_link_ibfk_1` FOREIGN KEY (`org`) REFERENCES `org` (`org`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE org_analysis ADD COLUMN flash tinyint NOT NULL DEFAULT 0;

# update 10/21/09

CREATE TABLE `sevence`.`org_payment` (
`org_payment` INT( 11 ) NOT NULL ,
`org` INT( 11 ) NOT NULL ,
`gift_amount` INT( 11 ) NOT NULL ,
`date` DATETIME NOT NULL ,
`amount` DECIMAL( 12, 2 ) NOT NULL ,
PRIMARY KEY ( `org_payment` )
) ENGINE = InnoDB;
ALTER TABLE `sevence`.`org_payment` ADD INDEX ( `org` ) ;
ALTER TABLE `sevence`.`org_payment` ADD INDEX ( `gift_amount` ) ;
ALTER TABLE org_payment ADD FOREIGN KEY ( org ) REFERENCES org (org);
ALTER TABLE org_payment ADD FOREIGN KEY ( gift_amount ) REFERENCES gift_amount (gift_amount);
ALTER TABLE `org_payment` CHANGE `date` `entered_date` DATETIME NOT NULL;
ALTER TABLE `org_payment` CHANGE `org_payment` `org_payment` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `org_payment` ADD `paid_date` DATETIME NULL;
ALTER TABLE `org_payment` CHANGE `amount` `redeemed` DECIMAL( 12, 2 ) NOT NULL;
ALTER TABLE `org_payment` ADD `paid` DECIMAL( 12, 2 ) NULL AFTER `redeemed`;

ALTER TABLE org_analysis ADD COLUMN manual_link datetime;
ALTER TABLE org_analysis ADD COLUMN notes text;

# update 10/23/09

ALTER TABLE org_client ADD COLUMN signup_offer tinyint NOT NULL DEFAULT 0, ADD COLUMN birthday_offer DATE, ADD COLUMN anniversary_offer DATE;

UPDATE org_client JOIN gift_promo ON org_client.org_client=gift_promo.org_client JOIN offer ON gift_promo.offer=offer.offer JOIN offer_type ON offer.offer_type=offer_type.offer_type SET signup_offer = 1  WHERE offer_base='signup';
UPDATE org_client JOIN gift_promo ON org_client.org_client=gift_promo.org_client JOIN offer ON gift_promo.offer=offer.offer JOIN offer_type ON offer.offer_type=offer_type.offer_type SET birthday_offer = issue_date  WHERE offer_base='birthday';
UPDATE org_client JOIN gift_promo ON org_client.org_client=gift_promo.org_client JOIN offer ON gift_promo.offer=offer.offer JOIN offer_type ON offer.offer_type=offer_type.offer_type SET anniversary_offer = issue_date  WHERE offer_base='anniversary';

# update 11/12/09

ALTER TABLE instant_pop ADD COLUMN photo int(11), ADD COLUMN photo_filename varchar(100);

# update 11/15/09

CREATE TABLE IF NOT EXISTS `org_amazon` (
  `org_amazon` int(11) NOT NULL AUTO_INCREMENT,
  `org` int(11) NOT NULL,
  `token_type` tinyint(4) NOT NULL,
  `caller_reference` varchar(64) DEFAULT NULL,
  `recipient_token` varchar(64) DEFAULT NULL,
  `refund_token` int(64) DEFAULT NULL,
  PRIMARY KEY (`org_amazon`),
  UNIQUE KEY `org` (`org`,`token_type`)
) ENGINE=InnoDB;

ALTER TABLE `org_amazon` ADD FOREIGN KEY (`org`) REFERENCES `sevence`.`org` (`org`);

CREATE TABLE IF NOT EXISTS `org_tran_amazon` (
  `caller_reference` varchar(64) NOT NULL,
  `org` int(11) NOT NULL,
  `entered_date` datetime NOT NULL,
  `pay_date` datetime DEFAULT NULL,
  `ipn_date` datetime DEFAULT NULL,
  `ip` int(11) NOT NULL DEFAULT '0',
  `transaction_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `transaction_status` tinyint(4) NOT NULL DEFAULT '0',
  `gift` int(11) DEFAULT NULL,
  `process_id` varchar(32) DEFAULT NULL,
  `sender_token` varchar(64) DEFAULT NULL,
  `transaction_id` varchar(40) DEFAULT NULL,
  `transaction_data` text,
  PRIMARY KEY (`caller_reference`),
  KEY `gift` (`gift`),
  KEY `org` (`org`)
) ENGINE=InnoDB;

ALTER TABLE `org_tran_amazon` ADD FOREIGN KEY (`gift`) REFERENCES `gift` (`gift`);
ALTER TABLE `org_tran_amazon` ADD FOREIGN KEY (`org`) REFERENCES `org` (`org`);

# update 11/23/09

