###########################################################
# Create tables for formbuilder Database
###########################################################

CREATE DATABASE IF NOT EXISTS formbuilderDB;
USE formbuilderDB;

GRANT SELECT, UPDATE, INSERT, DELETE, DROP, CREATE, INDEX, ALTER
	ON formbuilderDB.* to fb@localhost identified by "fb";

DROP TABLE IF EXISTS movies;
CREATE TABLE movies
(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255) NOT NULL,
	showTime TIME DEFAULT '00:00', # HH:MM
	categoryId SMALLINT UNSIGNED NOT NULL, # movie_categories.id
	actorId SMALLINT UNSIGNED NOT NULL # movie_actors.id
) TYPE = MyISAM;

# ALL IDs MUST BE GREATER THAN ZERO!
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (1,		'Hoodwinked',						'15:00',	1,	1);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (2,		'RV',										'13:30',	1,	2);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (3,		'Aladin',								'14:05',	1,	2);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (4,		'School of Rock',				'18:45',	1,	3);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (5,		'Nacho Libre',					'21:00',	1,	3);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (6,		'MI:I',									'20:20',	2,	4);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (7,		'MI:II',								'19:00',	2,	4);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (8,		'MI:III',								'17:50',	2,	4);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (9,		'The Rock',							'22:00',	2,	5);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (10,	'Con Air',							'16:00',	2,	5);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (11,	'The Matrix',						'17:00',	2,	6);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (12,	'Spiderman',						'19:00',	2,	7);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (13,	'Shawshank Redemption',	'21:45',	3,	8);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (14,	'Castaway',							'20:10',	3,	9);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (15,	'The Green Mile',				'18:45',	3,	9);
INSERT INTO movies (id, title, showTime, categoryId, actorId) VALUES (16,	'Road To Perdition',		'21:30',	3,	9);

DROP TABLE IF EXISTS movie_categories;
CREATE TABLE movie_categories
(
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL
) TYPE = MyISAM;

# ALL IDs MUST BE GREATER THAN ZERO!
INSERT INTO movie_categories (id, name) VALUES (1,	'Comedy');
INSERT INTO movie_categories (id, name) VALUES (2,	'Action');
INSERT INTO movie_categories (id, name) VALUES (3,	'Drama');

DROP TABLE IF EXISTS movie_actors;
CREATE TABLE movie_actors
(
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL
) TYPE = MyISAM;

# ALL IDs MUST BE GREATER THAN ZERO!
INSERT INTO movie_actors (id, name) VALUES (1,	'Anne Hathaway');
INSERT INTO movie_actors (id, name) VALUES (2,	'Robin Williams');
INSERT INTO movie_actors (id, name) VALUES (3,	'Jack Black');
INSERT INTO movie_actors (id, name) VALUES (4,	'Tom Cruise');
INSERT INTO movie_actors (id, name) VALUES (5,	'Nicholas Cage');
INSERT INTO movie_actors (id, name) VALUES (6,	'Keanu Reeves');
INSERT INTO movie_actors (id, name) VALUES (7,	'Tobey Maguire');
INSERT INTO movie_actors (id, name) VALUES (8,	'Tim Robins');
INSERT INTO movie_actors (id, name) VALUES (9,	'Tom Hanks');

DROP TABLE IF EXISTS movie_comments;
CREATE TABLE movie_comments
(
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(255) NOT NULL,
	movieId INT UNSIGNED NOT NULL,
	comment TEXT NOT NULL
) TYPE = MyISAM;