####
# Author: Sean Colombo
# Date: 20080324
#
# This script does a conversion of the live doItLater database into the
# new OffhandWay database structure.  After the line is run on the release
# server, it will be commented out (but the record will be kept in case
# we have to restore from a mysqldump).
####

#DROP TABLE IF EXISTS users;
#CREATE TABLE users(
#	id INT(11) AUTO_INCREMENT NOT NULL,
#
#	username VARCHAR(255) NOT NULL,
#	password VARCHAR(255) NOT NULL,
#	emailAddr VARCHAR(255) NOT NULL,
#	dateOfBirth DATETIME,
#	imageVer INT(11) DEFAULT 0, # so that browsers expire their caches appropriately
#
#	isAdmin TINYINT DEFAULT 0,
#
#	isVerified TINYINT DEFAULT 0, # whether the email-address has been verified
#	emailHash VARCHAR(32) DEFAULT NULL, # need this to verify the email address
#
#	createdOn DATETIME,
#	updatedAt TIMESTAMP,
#
#	lastLogin DATETIME,
#	numLogins INT(11) DEFAULT 0,
#
#	# TODO: Create another table linked from this one which has user-stats and some sort of aggregate score here.
#
#	UNIQUE(username),
#	PRIMARY KEY(id)
#);
#
#DROP TABLE IF EXISTS userStats;
#CREATE TABLE userStats(
#	user_id INT(11) NOT NULL,
#	profileViews INT(11) DEFAULT 0,
#
#	# People invited to the site and those who actually came.
#	numInvited INT(11) DEFAULT 0,
#	numResponded INT(11) DEFAULT 0,
#
#	pageViews INT(11) DEFAULT 0,
#	adViews INT(11) DEFAULT 0,
#
#	UNIQUE(user_id)
#);
#
#DROP TABLE IF EXISTS userPreferences;
#CREATE TABLE userPreferences(
#	user_id INT(11) NOT NULL, # foreign key to users.id
#
#	createdOn DATETIME,
#	updatedAt TIMESTAMP
#);
# ALL OF THE ABOVE CHANGES WERE APPLIED 20080325 00:05.
###############################################################################


#DROP TABLE eventlog; # THIS VERSION IS LOWERCASE
#DROP TABLE IF EXISTS eventLog;
#CREATE TABLE eventLog(
#	`id` int(11) NOT NULL auto_increment,
#	`defcon` int(1) default '5',
#	`message` longtext,
#	`timestamp` datetime default NULL,
#	PRIMARY KEY  (`id`)
#);
# ALL OF THE ABOVE CHANGES WERE APPLIED 20080326 10:04.
###############################################################################


# Delete the old motive traffic tables.  There in some sql dumps if we need them, but we're not adding to them.  Google Analytics is used for tracking now.
#drop table if exists motive_iplog_daily;
#drop table if exists motive_iplog_monthly;
#drop table if exists motive_iplog_weekly;
#drop table if exists motive_map;
#drop table if exists motive_pagehits;
#drop table if exists motive_referers;
#drop table if exists motive_survey;
#drop table if exists motive_traffic_daily;
#drop table if exists motive_traffic_monthly;
#drop table if exists motive_traffic_weekly;
#drop table if exists motive_traffic_total;
#drop table if exists referers;
# ALL OF THE ABOVE CHANGES WERE APPLIED 20090731 20:47.
###############################################################################


## Change how we store the different file locations.
#ALTER TABLE pictures CHANGE url url_orig VARCHAR(255);
#ALTER TABLE flash CHANGE url url_orig VARCHAR(255);
#ALTER TABLE movies CHANGE url url_orig VARCHAR(255);
#ALTER TABLE games CHANGE url url_orig VARCHAR(255);
#ALTER TABLE pictures CHANGE thumbUrl url_thumb VARCHAR(255);
#ALTER TABLE movies CHANGE thumbUrl url_thumb VARCHAR(255);
#ALTER TABLE flash CHANGE thumbUrl url_thumb VARCHAR(255);
#ALTER TABLE games CHANGE thumbUrl url_thumb VARCHAR(255);
#ALTER TABLE pictures ADD COLUMN url_scaled VARCHAR(255);
#UPDATE pictures SET url_scaled = url_orig; # copy the url for scaled since they ARE scaled images and they are the only originals that were stored.
#
## Change how we track who uploaded the media.
#ALTER TABLE pictures ADD COLUMN submittedBy_id INT(11);
#ALTER TABLE flash ADD COLUMN submittedBy_id INT(11);
#ALTER TABLE movies ADD COLUMN submittedBy_id INT(11);
#ALTER TABLE games ADD COLUMN submittedBy_id INT(11);
#ALTER TABLE pictures CHANGE submittedBy submittedBy_username VARCHAR(255);
#ALTER TABLE flash CHANGE submittedBy submittedBy_username VARCHAR(255);
#ALTER TABLE movies CHANGE submittedBy submittedBy_username VARCHAR(255);
#ALTER TABLE games CHANGE submittedBy submittedBy_username VARCHAR(255);
#UPDATE pictures SET submittedBy_username = poster;
#UPDATE flash SET submittedBy_username = poster;
#UPDATE movies SET submittedBy_username = poster;
#UPDATE games SET submittedBy_username = poster;
#ALTER TABLE pictures DROP COLUMN poster;
#ALTER TABLE flash DROP COLUMN poster;
#ALTER TABLE movies DROP COLUMN poster;
#ALTER TABLE games DROP COLUMN poster;
#ALTER TABLE pictures ADD COLUMN dateTaken DATETIME;
#ALTER TABLE pictures ADD COLUMN album_id INT(11);
#ALTER TABLE pictures ADD COLUMN createdOn DATETIME;
#ALTER TABLE pictures ADD COLUMN updatedAt TIMESTAMP;
#ALTER TABLE userStats ADD COLUMN picsPosted BIGINT DEFAULT 0;
#
## Other database conversion
#DROP TABLE IF EXISTS articles;
#DROP TABLE IF EXISTS iplog;
#DROP TABLE IF EXISTS pagehits;
# ALL OF THE ABOVE CHANGES WERE APPLIED 20090731 22:45.
###############################################################################
