###########################################################
# Author: Robert Lie (mobilefish.com)
###########################################################

# Comment these if you already have database MOBILEFISHDB.
CREATE DATABASE MOBILEFISHDB;
USE MOBILEFISHDB;

# Uncomment these if you already have USER, ADDRESS
# DROP TABLE USER;
# DROP TABLE ADDRESS;

CREATE TABLE USER (
	USERID			INT AUTO_INCREMENT,
	USERNAME		VARCHAR(255) NOT NULL,
	FIRSTNAME		VARCHAR(255) NOT NULL,
	LASTNAME		VARCHAR(255) NOT NULL,
	TITLE			VARCHAR(255) NULL,
	GENDER			ENUM('m', 'f') NULL,
	DATE_OF_BIRTH 	DATE NULL,
	PASSWORD		VARCHAR(255) NOT NULL,
	EMAIL 			VARCHAR(255) NOT NULL,
	PHONE			VARCHAR(255) NULL,
	PRIMARY KEY (USERID)
);

CREATE TABLE ADDRESS (
    ADDRESSID 		INT AUTO_INCREMENT,
	USERID          INT UNSIGNED NOT NULL REFERENCES USER (USERID),	    
	STREET			VARCHAR(255) NOT NULL,
	HOUSENUMBER 	VARCHAR(255) NOT NULL,
	ZIP 			VARCHAR(255) NOT NULL,
	CITY 			VARCHAR(255) NOT NULL,
	STATE 			VARCHAR(255) NOT NULL,
	COUNTRY			VARCHAR(255) NOT NULL,	
	PRIMARY KEY (ADDRESSID)
);

# User 1 data
INSERT INTO USER (USERID, USERNAME, FIRSTNAME, LASTNAME, TITLE, GENDER, DATE_OF_BIRTH, PASSWORD, EMAIL, PHONE) 
VALUES (NULL, 'johndoe', 'John',  'Doe',  'ing.',  'm', '1970-06-30',   'secret', 'xyz@xyzcompany.com', '001-99999999');
INSERT INTO ADDRESS (ADDRESSID, USERID, STREET, HOUSENUMBER, ZIP, CITY, STATE, COUNTRY) 
VALUES (NULL, LAST_INSERT_ID(),    'fishstreet', '12', '1111 AA', 'Amsterdam', 'Noord-Holland', 'Netherland');

# User 2 data
INSERT INTO USER (USERID, USERNAME, FIRSTNAME, LASTNAME, TITLE, GENDER, DATE_OF_BIRTH, PASSWORD, EMAIL, PHONE) 
VALUES (NULL, 'joesmo' ,  'Joe',  'Smo',    NULL, NULL, '1970-10-09', 'password', 'abc@abccompany.com', '002-55555555');
INSERT INTO ADDRESS (ADDRESSID, USERID, STREET, HOUSENUMBER, ZIP, CITY, STATE, COUNTRY)
VALUES (NULL, LAST_INSERT_ID(), 'stationstreet', '78', '3333 ZZ',  'Den Haag', 'Zuid-Holland',  'Netherland');