# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- sn_user
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_user`;


CREATE TABLE `sn_user`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`login` VARCHAR(10),
	`password` VARCHAR(10),
	`email` VARCHAR(50),
	`first_name` VARCHAR(50),
	`last_name` VARCHAR(50),
	`is_admin` INTEGER default 0,
	`created_at` DATETIME,
	PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sn_snippet
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_snippet`;


CREATE TABLE `sn_snippet`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`user_id` INTEGER,
	`title` TEXT,
	`body` TEXT,
	`html_body` TEXT,
	`created_at` DATETIME,
	`average_vote` DOUBLE,
	`nb_comments` INTEGER,
	`all_tags` VARCHAR(255),
	PRIMARY KEY (`id`),
	INDEX `sn_snippet_FI_1` (`user_id`),
	CONSTRAINT `sn_snippet_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `sn_user` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sn_tag
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_tag`;


CREATE TABLE `sn_tag`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`user_id` INTEGER,
	`snippet_id` INTEGER,
	`name` VARCHAR(50),
	`created_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `sn_tag_FI_1` (`user_id`),
	CONSTRAINT `sn_tag_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `sn_user` (`id`),
	INDEX `sn_tag_FI_2` (`snippet_id`),
	CONSTRAINT `sn_tag_FK_2`
		FOREIGN KEY (`snippet_id`)
		REFERENCES `sn_snippet` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sn_comment
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_comment`;


CREATE TABLE `sn_comment`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`user_id` INTEGER,
	`snippet_id` INTEGER,
	`body` TEXT,
	`html_body` TEXT,
	`created_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `sn_comment_FI_1` (`user_id`),
	CONSTRAINT `sn_comment_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `sn_user` (`id`),
	INDEX `sn_comment_FI_2` (`snippet_id`),
	CONSTRAINT `sn_comment_FK_2`
		FOREIGN KEY (`snippet_id`)
		REFERENCES `sn_snippet` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sn_vote
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sn_vote`;


CREATE TABLE `sn_vote`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`user_id` INTEGER,
	`snippet_id` INTEGER,
	`vote` INTEGER,
	`created_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `sn_vote_FI_1` (`user_id`),
	CONSTRAINT `sn_vote_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `sn_user` (`id`),
	INDEX `sn_vote_FI_2` (`snippet_id`),
	CONSTRAINT `sn_vote_FK_2`
		FOREIGN KEY (`snippet_id`)
		REFERENCES `sn_snippet` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;