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

#-----------------------------------------------------------------------------
#-- ad_banner
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ad_banner`;


CREATE TABLE `ad_banner`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`ad_user_profile_id` INTEGER  NOT NULL,
	`name` VARCHAR(50),
	`file_name` VARCHAR(255),
	PRIMARY KEY (`id`),
	INDEX `ad_banner_FI_1` (`ad_user_profile_id`),
	CONSTRAINT `ad_banner_FK_1`
		FOREIGN KEY (`ad_user_profile_id`)
		REFERENCES `ad_user_profile` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ad_campaign
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ad_campaign`;


CREATE TABLE `ad_campaign`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`ad_user_profile_id` INTEGER  NOT NULL,
	`ad_banner_id` INTEGER,
	`ad_site_id` INTEGER,
	`start_date` DATE,
	`end_date` DATE,
	PRIMARY KEY (`id`),
	INDEX `ad_campaign_FI_1` (`ad_user_profile_id`),
	CONSTRAINT `ad_campaign_FK_1`
		FOREIGN KEY (`ad_user_profile_id`)
		REFERENCES `ad_user_profile` (`id`)
		ON DELETE CASCADE,
	INDEX `ad_campaign_FI_2` (`ad_banner_id`),
	CONSTRAINT `ad_campaign_FK_2`
		FOREIGN KEY (`ad_banner_id`)
		REFERENCES `ad_banner` (`id`)
		ON DELETE SET NULL,
	INDEX `ad_campaign_FI_3` (`ad_site_id`),
	CONSTRAINT `ad_campaign_FK_3`
		FOREIGN KEY (`ad_site_id`)
		REFERENCES `ad_site` (`id`)
		ON DELETE SET NULL
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ad_site
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ad_site`;


CREATE TABLE `ad_site`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`ad_user_profile_id` INTEGER  NOT NULL,
	`address` VARCHAR(100),
	PRIMARY KEY (`id`),
	INDEX `ad_site_FI_1` (`ad_user_profile_id`),
	CONSTRAINT `ad_site_FK_1`
		FOREIGN KEY (`ad_user_profile_id`)
		REFERENCES `ad_user_profile` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ad_tag_word
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ad_tag_word`;


CREATE TABLE `ad_tag_word`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`word` VARCHAR(50),
	PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ad_site_tag
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ad_site_tag`;


CREATE TABLE `ad_site_tag`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`ad_site_id` INTEGER  NOT NULL,
	`ad_tag_word_id` INTEGER,
	PRIMARY KEY (`id`),
	INDEX `ad_site_tag_FI_1` (`ad_site_id`),
	CONSTRAINT `ad_site_tag_FK_1`
		FOREIGN KEY (`ad_site_id`)
		REFERENCES `ad_site` (`id`)
		ON DELETE CASCADE,
	INDEX `ad_site_tag_FI_2` (`ad_tag_word_id`),
	CONSTRAINT `ad_site_tag_FK_2`
		FOREIGN KEY (`ad_tag_word_id`)
		REFERENCES `ad_tag_word` (`id`)
		ON DELETE SET NULL
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ad_user_profile
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ad_user_profile`;


CREATE TABLE `ad_user_profile`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`user_id` INTEGER  NOT NULL,
	`email` VARCHAR(128),
	`banner_hit_taken` INTEGER default 0 NOT NULL,
	`banner_hit_given` INTEGER default 0 NOT NULL,
	`banner_show_taken` INTEGER default 0 NOT NULL,
	`banner_show_given` INTEGER default 0 NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `ad_user_profile_FI_1` (`user_id`),
	CONSTRAINT `ad_user_profile_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `sf_guard_user` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- stat_banner
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `stat_banner`;


CREATE TABLE `stat_banner`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`ad_banner_id` INTEGER,
	`ad_site_id` INTEGER,
	`ad_campaign_id` INTEGER,
	`times_shown` INTEGER,
	`times_clicked` INTEGER,
	PRIMARY KEY (`id`),
	INDEX `stat_banner_FI_1` (`ad_banner_id`),
	CONSTRAINT `stat_banner_FK_1`
		FOREIGN KEY (`ad_banner_id`)
		REFERENCES `ad_banner` (`id`)
		ON DELETE SET NULL,
	INDEX `stat_banner_FI_2` (`ad_site_id`),
	CONSTRAINT `stat_banner_FK_2`
		FOREIGN KEY (`ad_site_id`)
		REFERENCES `ad_site` (`id`)
		ON DELETE SET NULL,
	INDEX `stat_banner_FI_3` (`ad_campaign_id`),
	CONSTRAINT `stat_banner_FK_3`
		FOREIGN KEY (`ad_campaign_id`)
		REFERENCES `ad_campaign` (`id`)
		ON DELETE SET NULL
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- stat_site
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `stat_site`;


CREATE TABLE `stat_site`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`ad_site_id1` INTEGER,
	`ad_site_id2` INTEGER,
	`times_redirected` INTEGER,
	PRIMARY KEY (`id`),
	INDEX `stat_site_FI_1` (`ad_site_id1`),
	CONSTRAINT `stat_site_FK_1`
		FOREIGN KEY (`ad_site_id1`)
		REFERENCES `ad_site` (`id`),
	INDEX `stat_site_FI_2` (`ad_site_id2`),
	CONSTRAINT `stat_site_FK_2`
		FOREIGN KEY (`ad_site_id2`)
		REFERENCES `ad_site` (`id`)
)Type=InnoDB;

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