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

#-----------------------------------------------------------------------------
#-- property
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `property`;


CREATE TABLE `property`
(
	`key` VARCHAR(50)  NOT NULL,
	`value` TEXT,
	PRIMARY KEY (`key`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- invoice
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `invoice`;


CREATE TABLE `invoice`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`series` VARCHAR(25),
	`number` INTEGER,
	`customer` VARCHAR(100),
	`customer_identification` VARCHAR(50),
	`customer_email` VARCHAR(100),
	`invoicing_address` TEXT,
	`shipping_address` TEXT,
	`contact_person` VARCHAR(100),
	`terms` TEXT,
	`notes` TEXT,
	`issue_date` DATE,
	`due_date` DATE,
	`draft` TINYINT default 1,
	`sent_by_email` TINYINT default 0,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	`base` DOUBLE,
	`discount` DOUBLE,
	`net` DOUBLE,
	`taxes` DOUBLE,
	`gross` DOUBLE,
	`paid` DOUBLE,
	`due` DOUBLE,
	PRIMARY KEY (`id`),
	KEY `prefx_index`(`series`, `number`),
	KEY `cstnm_index`(`customer`),
	KEY `cstid_index`(`customer_identification`),
	KEY `cstml_index`(`customer_email`),
	KEY `cntct_index`(`contact_person`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- invoice_item
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `invoice_item`;


CREATE TABLE `invoice_item`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`invoice_id` INTEGER,
	`description` VARCHAR(255),
	`unitary_cost` DOUBLE,
	`quantity` INTEGER default 1,
	`discount` INTEGER default 0,
	PRIMARY KEY (`id`),
	INDEX `invoice_item_FI_1` (`invoice_id`),
	CONSTRAINT `invoice_item_FK_1`
		FOREIGN KEY (`invoice_id`)
		REFERENCES `invoice` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- invoice_item_tax
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `invoice_item_tax`;


CREATE TABLE `invoice_item_tax`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`invoice_item_id` INTEGER,
	`name` VARCHAR(50),
	`value` DOUBLE,
	PRIMARY KEY (`id`),
	INDEX `invoice_item_tax_FI_1` (`invoice_item_id`),
	CONSTRAINT `invoice_item_tax_FK_1`
		FOREIGN KEY (`invoice_item_id`)
		REFERENCES `invoice_item` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- payment
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `payment`;


CREATE TABLE `payment`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`invoice_id` INTEGER,
	`date` DATE,
	`amount` DOUBLE,
	`notes` TEXT,
	PRIMARY KEY (`id`),
	INDEX `payment_FI_1` (`invoice_id`),
	CONSTRAINT `payment_FK_1`
		FOREIGN KEY (`invoice_id`)
		REFERENCES `invoice` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- sf_guard_user_profile
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `sf_guard_user_profile`;


CREATE TABLE `sf_guard_user_profile`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`user_id` INTEGER  NOT NULL,
	`first_name` VARCHAR(20),
	`last_name` VARCHAR(20),
	`birthday` DATE,
	`nb_display_results` INTEGER default 10,
	`culture` VARCHAR(2),
	`search_filter` VARCHAR(30),
	`series` VARCHAR(50),
	PRIMARY KEY (`id`),
	INDEX `sf_guard_user_profile_FI_1` (`user_id`),
	CONSTRAINT `sf_guard_user_profile_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `sf_guard_user` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- recurring_invoice
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `recurring_invoice`;


CREATE TABLE `recurring_invoice`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`series` VARCHAR(25),
	`name` VARCHAR(255),
	`customer` VARCHAR(100),
	`customer_identification` VARCHAR(50),
	`customer_email` VARCHAR(100),
	`invoicing_address` TEXT,
	`shipping_address` TEXT,
	`contact_person` VARCHAR(100),
	`terms` TEXT,
	`notes` TEXT,
	`days_to_due` SMALLINT default 30,
	`draft` TINYINT default 1,
	`enabled` TINYINT default 0,
	`day_of_week` VARCHAR(3),
	`month_period` VARCHAR(3),
	`day_period` VARCHAR(4),
	`year_period` VARCHAR(2),
	`starting_date` DATE,
	`last_execution_date` DATE,
	`max_occurrences` INTEGER default 0,
	`occurrences` INTEGER default 0,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`),
	KEY `prefx_index`(`series`),
	KEY `cstnm_index`(`customer`),
	KEY `cstid_index`(`customer_identification`),
	KEY `cstml_index`(`customer_email`),
	KEY `cntct_index`(`contact_person`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- recurring_invoice_item
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `recurring_invoice_item`;


CREATE TABLE `recurring_invoice_item`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`recurring_invoice_id` INTEGER,
	`description` VARCHAR(255),
	`unitary_cost` DOUBLE,
	`quantity` INTEGER default 1,
	`discount` INTEGER default 0,
	PRIMARY KEY (`id`),
	INDEX `recurring_invoice_item_FI_1` (`recurring_invoice_id`),
	CONSTRAINT `recurring_invoice_item_FK_1`
		FOREIGN KEY (`recurring_invoice_id`)
		REFERENCES `recurring_invoice` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- recurring_invoice_item_tax
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `recurring_invoice_item_tax`;


CREATE TABLE `recurring_invoice_item_tax`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`recurring_invoice_item_id` INTEGER,
	`name` VARCHAR(50),
	`value` DOUBLE,
	PRIMARY KEY (`id`),
	INDEX `recurring_invoice_item_tax_FI_1` (`recurring_invoice_item_id`),
	CONSTRAINT `recurring_invoice_item_tax_FK_1`
		FOREIGN KEY (`recurring_invoice_item_id`)
		REFERENCES `recurring_invoice_item` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

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