####
# Author: Sean Colombo
# Date: 20080210
#
# Setup script for the database.
####

###############################################################################
## USERS AND USER-DATA
###############################################################################

####
# 
####
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,

	picsPosted BIGINT 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

	# TODO: As customization options become available, add them here.
	
	createdOn DATETIME,
	updatedAt TIMESTAMP
);


###############################################################################


###############################################################################
## MEDIA AND META-DATA
###############################################################################

####
#
####
DROP TABLE IF EXISTS pictures;
CREATE TABLE pictures(
	id INT(11) NOT NULL AUTO_INCREMENT,

	# Locations of the various-sized versions of the picture.
	url_orig VARCHAR(255),
	url_scaled VARCHAR(255),
	url_thumb VARCHAR(255),

	title VARCHAR(255),
	caption BLOB,
	tags VARCHAR(255),
	categories VARCHAR(255),
	score INT(11) DEFAULT 0,
	numVotes INT(11) DEFAULT 0,
	numViews INT(11) DEFAULT 0,

	dateTaken DATETIME,
	logoGravity VARCHAR(255),
	submittedOn DATETIME,
	submittedBy_username VARCHAR(255), # cache of the username of the user who posted this
	submittedBy_id INT(11),

	lowestAge INT(11) DEFAULT 19, # lowest age required to view this picture if age settings are turned on
	
	createdOn DATETIME,
	updatedAt TIMESTAMP,

	PRIMARY KEY(id)
);
# TODO: ADD THE MOVIES, AND OTHER TYPES OF MEDIA HERE IF THAT ENDS UP BEING PART OF THE CORE ENGINE.

####
#
####
DROP TABLE IF EXISTS tags;
CREATE TABLE tags(
	
);

####
#
####
DROP TABLE IF EXISTS categories;
CREATE TABLE categories(
	mediaType VARCHAR(255) DEFAULT NULL,
	category VARCHAR(255) DEFAULT NULL
);

###############################################################################


###############################################################################
## CACHING AND OTHER PERFORMANCE DATA
###############################################################################
CREATE TABLE pageCache(
	keyName VARCHAR(255),
	pageData TEXT,
	updatedAt TIMESTAMP,

	PRIMARY KEY(keyName)
);
###############################################################################


###############################################################################
## ADMINISTRATIVE
###############################################################################

####
# The site-wide logging tool for significant events
# that should be looked into further.
#
# The lower the defcon level, the more urgent the event.
####
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`)
);

###############################################################################