IF OBJECT_ID('UserProfile') IS NOT NULL DROP TABLE UserProfile;
IF OBJECT_ID('UserContact') IS NOT NULL DROP TABLE UserContact;
IF OBJECT_ID('UserGroup') IS NOT NULL DROP TABLE UserGroup;
IF OBJECT_ID('UserData') IS NOT NULL DROP TABLE UserData;

USE SeriousDatabase
CREATE TABLE UserData (
	UserName	VARCHAR(32)		NOT NULL,
	Password	VARCHAR(32)		NOT NULL,
	IPAddress	VARCHAR(15),
	LastLogin	DATETIME		NOT NULL,
	JoinDate	DATETIME		NOT NULL,
	PRIMARY KEY(UserName)
);

USE SeriousDatabase
CREATE TABLE UserGroup (
	UserName	VARCHAR(32)		NOT NULL,
	GroupName	VARCHAR(32)		NOT NULL,
	PRIMARY KEY(UserName, GroupName),
	FOREIGN KEY(UserName) REFERENCES UserData(UserName)
);

USE SeriousDatabase
CREATE TABLE UserProfile (
	UserName	VARCHAR(32)		NOT NULL,
	FirstName	VARCHAR(32)		NOT NULL,
	MiddleName	VARCHAR(32),
	LastName	VARCHAR(32) 	NOT NULL,
	Gender		CHAR			NOT NULL,
	BirthDate	DATETIME		NOT NULL,
	Email		VARCHAR(64),
	HomePhone	VARCHAR(10),
	MobilePhone	VARCHAR(10),
	WorkPhone	VARCHAR(10),
	Country		VARCHAR(32)		NOT NULL,
	StateProv	VARCHAR(32)		NOT NULL,
	ZipPostal	VARCHAR(6)		NOT NULL,
	PRIMARY KEY(UserName),
	FOREIGN KEY(UserName) REFERENCES UserData(UserName)
);

USE SeriousDatabase
CREATE TABLE UserContact (
	UserName	VARCHAR(32)		NOT NULL,
	Contact		VARCHAR(32)		NOT NULL,
	GroupName	VARCHAR(32),
	Blocked		INT				NOT NULL DEFAULT 0,
	PRIMARY KEY(UserName, Contact),
	FOREIGN KEY(UserName) REFERENCES UserData(UserName),
	FOREIGN KEY(Contact) REFERENCES UserData(UserName),
	FOREIGN KEY(GroupName) REFERENCES UserGroup(GroupName)
);

USE SeriousDatabase INSERT INTO UserData VALUES('nitro404', 'lolwut', '', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
USE SeriousDatabase INSERT INTO UserData VALUES('shades', 'ohyeah', '', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
USE SeriousDatabase INSERT INTO UserData VALUES('1337skillz', 'awesome', '', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
USE SeriousDatabase INSERT INTO UserData VALUES('gtfo', 'plz', '', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

USE SeriousDatabase INSERT INTO UserProfile VALUES('nitro404', 'Kevin', 'Andrew', 'Scroggins', 'M', '1987-06-18', 'nitro404@hotmail.com', '6138234547', '6133242284', '', 'Canada', 'Ontario', 'K2J4K9');

USE SeriousDatabase INSERT INTO UserContact VALUES('nitro404', 'shades', 0);
USE SeriousDatabase INSERT INTO UserContact VALUES('nitro404', 'gtfo', 1);
USE SeriousDatabase INSERT INTO UserContact VALUES('shades', 'nitro404', 0);
USE SeriousDatabase INSERT INTO UserContact VALUES('shades', 'gtfo', 1);
USE SeriousDatabase INSERT INTO UserContact VALUES('shades', '1337skillz', 0);
USE SeriousDatabase INSERT INTO UserContact VALUES('1337skillz', 'shades', 0);
USE SeriousDatabase INSERT INTO UserContact VALUES('1337skillz', 'nitro404', 1);

-- GET USER INFO
USE SeriousDatabase SELECT * FROM UserData WHERE UserName = 'nitro404';

-- GET USER PROFILE
USE SeriousDatabase SELECT * FROM UserProfile WHERE UserName = 'nitro404';

-- GET CONTACTS
USE SeriousDatabase SELECT * FROM UserContact WHERE UserName = 'nitro404';

-- CREATE USER - see above

-- REMOVE USER
USE SeriousDatabase DELETE FROM UserProfile WHERE UserName = 'shades';
USE SeriousDatabase DELETE FROM UserContact WHERE UserName = 'shades' OR Contact = 'shades';
USE SeriousDatabase DELETE FROM UserData WHERE UserName = 'shades';

-- CREATE USER PROFILE - see above

-- REMOVE USER PROFILE
USE SeriousDatabase DELETE FROM UserProfile WHERE UserName = 'shades';

-- CHANGE PASSWORD
USE SeriousDatabase UPDATE UserData SET Password = 'password'
	WHERE UserName = 'nitro404' AND Password = 'lolwut';

-- USER LOGIN
USE SeriousDatabase UPDATE UserData SET LastLogin = CURRENT_TIMESTAMP
	WHERE UserName = 'nitro404';
	
-- USER LOGOUT
//update multiple values?
//clear ip address

-- UPDATE PROFILE - easy.. just need variable names to insert and such

-- ADD CONTACT - see above

-- REMOVE CONTACT
USE SeriousDatabase DELETE FROM UserContact WHERE UserName = 'nitro404' AND Contact = 'shades';

-- [UN]BLOCK CONTACT
USE SeriousDatabase UPDATE UserContact SET Blocked = 1
	WHERE UserName = 'nitro404' AND Contact = 'shades';
