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

#-----------------------------------------------------------------------------
#-- countries
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `countries`;


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

#-----------------------------------------------------------------------------
#-- accounts
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `accounts`;


CREATE TABLE `accounts`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100)  NOT NULL,
	`url` VARCHAR(100)  NOT NULL,
	`filename` VARCHAR(100),
	PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- departments
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `departments`;


CREATE TABLE `departments`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100)  NOT NULL,
	PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- users_accounts
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `users_accounts`;


CREATE TABLE `users_accounts`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`user_id` INTEGER  NOT NULL,
	`account_id` INTEGER  NOT NULL,
	`region_id` INTEGER  NOT NULL,
	`rol_id` INTEGER  NOT NULL,
	`deleted_at` DATETIME,
	`created_at` DATETIME,
	PRIMARY KEY (`id`,`region_id`),
	INDEX `users_accounts_FI_1` (`user_id`),
	CONSTRAINT `users_accounts_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE,
	INDEX `users_accounts_FI_2` (`account_id`),
	CONSTRAINT `users_accounts_FK_2`
		FOREIGN KEY (`account_id`)
		REFERENCES `users_rols_accounts` (`account_id`)
		ON DELETE CASCADE,
	INDEX `users_accounts_FI_3` (`region_id`),
	CONSTRAINT `users_accounts_FK_3`
		FOREIGN KEY (`region_id`)
		REFERENCES `regions` (`id`)
		ON DELETE CASCADE,
	INDEX `users_accounts_FI_4` (`rol_id`),
	CONSTRAINT `users_accounts_FK_4`
		FOREIGN KEY (`rol_id`)
		REFERENCES `users_rols_accounts` (`rol_id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- users_rols_accounts
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `users_rols_accounts`;


CREATE TABLE `users_rols_accounts`
(
	`rol_id` INTEGER  NOT NULL,
	`account_id` INTEGER  NOT NULL,
	`name` VARCHAR(100)  NOT NULL,
	PRIMARY KEY (`rol_id`,`account_id`),
	INDEX `I_referenced_users_accounts_FK_2_1` (`account_id`),
	INDEX `users_rols_accounts_FI_1` (`account_id`),
	CONSTRAINT `users_rols_accounts_FK_1`
		FOREIGN KEY (`account_id`)
		REFERENCES `accounts` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- regions
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `regions`;


CREATE TABLE `regions`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`account_id` INTEGER  NOT NULL,
	`name` VARCHAR(100)  NOT NULL,
	PRIMARY KEY (`id`,`account_id`),
	INDEX `regions_FI_1` (`account_id`),
	CONSTRAINT `regions_FK_1`
		FOREIGN KEY (`account_id`)
		REFERENCES `accounts` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- nodes_regions
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `nodes_regions`;


CREATE TABLE `nodes_regions`
(
	`node_id` INTEGER  NOT NULL,
	`region_id` INTEGER  NOT NULL,
	`account_id` INTEGER  NOT NULL,
	PRIMARY KEY (`node_id`,`region_id`,`account_id`),
	CONSTRAINT `nodes_regions_FK_1`
		FOREIGN KEY (`node_id`)
		REFERENCES `nodes` (`id`)
		ON DELETE CASCADE,
	INDEX `nodes_regions_FI_2` (`region_id`),
	CONSTRAINT `nodes_regions_FK_2`
		FOREIGN KEY (`region_id`)
		REFERENCES `regions` (`id`)
		ON DELETE CASCADE,
	INDEX `nodes_regions_FI_3` (`account_id`),
	CONSTRAINT `nodes_regions_FK_3`
		FOREIGN KEY (`account_id`)
		REFERENCES `accounts` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- users
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `users`;


CREATE TABLE `users`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(50)  NOT NULL,
	`password` VARCHAR(50)  NOT NULL,
	`name` VARCHAR(100)  NOT NULL,
	`lastname` VARCHAR(100)  NOT NULL,
	`dni` VARCHAR(100),
	`cuil` VARCHAR(100),
	`email` VARCHAR(100),
	`phone` VARCHAR(100),
	`phone_tmt` VARCHAR(100),
	`gender` INTEGER,
	`status` INTEGER,
	`filename` VARCHAR(150),
	`department_id` INTEGER,
	`city_id` INTEGER,
	`address` VARCHAR(150),
	`birthday` DATETIME,
	`joined_at` DATETIME,
	`health_book_date` DATETIME,
	`legajo` VARCHAR(70),
	`coorp` VARCHAR(100),
	`is_admin` INTEGER default 0,
	`hierarchy` INTEGER default 0,
	`shirt_size` VARCHAR(100),
	`coat_size` VARCHAR(100),
	`foot_size` VARCHAR(100),
	`belt_size` VARCHAR(100),
	`pant_size` VARCHAR(100),
	`regionm_id` INTEGER default NULL,
	`deleted_by` INTEGER,
	`deleted_at` DATETIME,
	`updated_by` INTEGER,
	`updated_at` DATETIME,
	`created_by` INTEGER,
	`created_at` DATETIME,
	PRIMARY KEY (`id`),
	UNIQUE KEY `users_username_unique` (`username`),
	UNIQUE KEY `users_password_unique` (`password`),
	INDEX `users_FI_1` (`department_id`),
	CONSTRAINT `users_FK_1`
		FOREIGN KEY (`department_id`)
		REFERENCES `departments` (`id`),
	INDEX `users_FI_2` (`city_id`),
	CONSTRAINT `users_FK_2`
		FOREIGN KEY (`city_id`)
		REFERENCES `cities` (`id`),
	INDEX `users_FI_3` (`deleted_by`),
	CONSTRAINT `users_FK_3`
		FOREIGN KEY (`deleted_by`)
		REFERENCES `users` (`id`),
	INDEX `users_FI_4` (`updated_by`),
	CONSTRAINT `users_FK_4`
		FOREIGN KEY (`updated_by`)
		REFERENCES `users` (`id`),
	INDEX `users_FI_5` (`created_by`),
	CONSTRAINT `users_FK_5`
		FOREIGN KEY (`created_by`)
		REFERENCES `users` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- user_supervisors
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `user_supervisors`;


CREATE TABLE `user_supervisors`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`account_id` INTEGER  NOT NULL,
	`user_id` INTEGER  NOT NULL,
	`supervisor_id` INTEGER  NOT NULL,
	PRIMARY KEY (`id`,`account_id`),
	INDEX `user_supervisors_FI_1` (`account_id`),
	CONSTRAINT `user_supervisors_FK_1`
		FOREIGN KEY (`account_id`)
		REFERENCES `accounts` (`id`)
		ON DELETE CASCADE,
	INDEX `user_supervisors_FI_2` (`user_id`),
	CONSTRAINT `user_supervisors_FK_2`
		FOREIGN KEY (`user_id`)
		REFERENCES `users` (`id`),
	INDEX `user_supervisors_FI_3` (`supervisor_id`),
	CONSTRAINT `user_supervisors_FK_3`
		FOREIGN KEY (`supervisor_id`)
		REFERENCES `users` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- provinces
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `provinces`;


CREATE TABLE `provinces`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100)  NOT NULL,
	`country_id` INTEGER  NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `provinces_FI_1` (`country_id`),
	CONSTRAINT `provinces_FK_1`
		FOREIGN KEY (`country_id`)
		REFERENCES `countries` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- cities
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `cities`;


CREATE TABLE `cities`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100)  NOT NULL,
	`cp` INTEGER(4)  NOT NULL,
	`province_id` INTEGER default 0,
	PRIMARY KEY (`id`),
	INDEX `cities_FI_1` (`province_id`),
	CONSTRAINT `cities_FK_1`
		FOREIGN KEY (`province_id`)
		REFERENCES `provinces` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- channels
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `channels`;


CREATE TABLE `channels`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100)  NOT NULL,
	PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- chains
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `chains`;


CREATE TABLE `chains`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100)  NOT NULL,
	`icon` VARCHAR(250),
	PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- nodes
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `nodes`;


CREATE TABLE `nodes`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(150)  NOT NULL,
	`address` VARCHAR(150)  NOT NULL,
	`city_id` INTEGER  NOT NULL,
	`cp` VARCHAR(100),
	`lat` VARCHAR(200),
	`lng` VARCHAR(200),
	`channel_id` INTEGER,
	`chain_id` INTEGER,
	`comercial_name` VARCHAR(150),
	`filename` VARCHAR(250)  NOT NULL,
	`phone` VARCHAR(100),
	`mail` VARCHAR(100),
	`manager` VARCHAR(100),
	`deleted_at` DATETIME,
	`created_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `nodes_FI_1` (`city_id`),
	CONSTRAINT `nodes_FK_1`
		FOREIGN KEY (`city_id`)
		REFERENCES `cities` (`id`),
	INDEX `nodes_FI_2` (`channel_id`),
	CONSTRAINT `nodes_FK_2`
		FOREIGN KEY (`channel_id`)
		REFERENCES `channels` (`id`),
	INDEX `nodes_FI_3` (`chain_id`),
	CONSTRAINT `nodes_FK_3`
		FOREIGN KEY (`chain_id`)
		REFERENCES `chains` (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- menu_items
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `menu_items`;


CREATE TABLE `menu_items`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`menu_item_id` INTEGER,
	`name` VARCHAR(100)  NOT NULL,
	`url` VARCHAR(100)  NOT NULL,
	`module` VARCHAR(100)  NOT NULL,
	`action` VARCHAR(100)  NOT NULL,
	`has_submenu` INTEGER default 0,
	`icon` VARCHAR(100)  NOT NULL,
	`ordering` INTEGER default 0,
	`active` INTEGER default 1,
	PRIMARY KEY (`id`),
	INDEX `menu_items_FI_1` (`menu_item_id`),
	CONSTRAINT `menu_items_FK_1`
		FOREIGN KEY (`menu_item_id`)
		REFERENCES `menu_items` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- routes
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `routes`;


CREATE TABLE `routes`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`account_id` INTEGER  NOT NULL,
	`name` VARCHAR(100)  NOT NULL,
	`frequency` INTEGER default 1 NOT NULL,
	`deleted_by` INTEGER,
	`deleted_at` DATETIME,
	`updated_by` INTEGER,
	`updated_at` DATETIME,
	`created_by` INTEGER,
	`created_at` DATETIME,
	PRIMARY KEY (`id`,`account_id`),
	INDEX `routes_FI_1` (`account_id`),
	CONSTRAINT `routes_FK_1`
		FOREIGN KEY (`account_id`)
		REFERENCES `accounts` (`id`)
		ON DELETE CASCADE,
	INDEX `routes_FI_2` (`deleted_by`),
	CONSTRAINT `routes_FK_2`
		FOREIGN KEY (`deleted_by`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE,
	INDEX `routes_FI_3` (`updated_by`),
	CONSTRAINT `routes_FK_3`
		FOREIGN KEY (`updated_by`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE,
	INDEX `routes_FI_4` (`created_by`),
	CONSTRAINT `routes_FK_4`
		FOREIGN KEY (`created_by`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- route_planification
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `route_planification`;


CREATE TABLE `route_planification`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`account_id` INTEGER  NOT NULL,
	`route_id` INTEGER,
	`day` INTEGER  NOT NULL,
	`date` DATETIME,
	PRIMARY KEY (`id`,`account_id`),
	INDEX `route_planification_FI_1` (`account_id`),
	CONSTRAINT `route_planification_FK_1`
		FOREIGN KEY (`account_id`)
		REFERENCES `accounts` (`id`)
		ON DELETE CASCADE,
	INDEX `route_planification_FI_2` (`route_id`),
	CONSTRAINT `route_planification_FK_2`
		FOREIGN KEY (`route_id`)
		REFERENCES `routes` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- route_nodes
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `route_nodes`;


CREATE TABLE `route_nodes`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`account_id` INTEGER  NOT NULL,
	`node_id` INTEGER,
	`route_id` INTEGER,
	`from_hour` FLOAT,
	`to_hour` FLOAT,
	PRIMARY KEY (`id`,`account_id`),
	INDEX `route_nodes_FI_1` (`account_id`),
	CONSTRAINT `route_nodes_FK_1`
		FOREIGN KEY (`account_id`)
		REFERENCES `accounts` (`id`)
		ON DELETE CASCADE,
	INDEX `route_nodes_FI_2` (`node_id`),
	CONSTRAINT `route_nodes_FK_2`
		FOREIGN KEY (`node_id`)
		REFERENCES `nodes` (`id`)
		ON DELETE CASCADE,
	INDEX `route_nodes_FI_3` (`route_id`),
	CONSTRAINT `route_nodes_FK_3`
		FOREIGN KEY (`route_id`)
		REFERENCES `routes` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- route_users
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `route_users`;


CREATE TABLE `route_users`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`account_id` INTEGER  NOT NULL,
	`user_id` INTEGER,
	`route_id` INTEGER,
	PRIMARY KEY (`id`,`account_id`),
	INDEX `route_users_FI_1` (`account_id`),
	CONSTRAINT `route_users_FK_1`
		FOREIGN KEY (`account_id`)
		REFERENCES `accounts` (`id`)
		ON DELETE CASCADE,
	INDEX `route_users_FI_2` (`user_id`),
	CONSTRAINT `route_users_FK_2`
		FOREIGN KEY (`user_id`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE,
	INDEX `route_users_FI_3` (`route_id`),
	CONSTRAINT `route_users_FK_3`
		FOREIGN KEY (`route_id`)
		REFERENCES `routes` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- checkouts
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `checkouts`;


CREATE TABLE `checkouts`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`account_id` INTEGER  NOT NULL,
	`node_id` INTEGER,
	`user_id` INTEGER,
	`route_id` INTEGER,
	`lat` VARCHAR(200),
	`lng` VARCHAR(200),
	`complete` INTEGER default 0,
	`json` VARCHAR(200),
	`is_mobile` INTEGER default 0,
	`visited_at` DATETIME,
	`left_at` DATETIME,
	`created_at` DATETIME,
	PRIMARY KEY (`id`,`account_id`),
	INDEX `checkouts_FI_1` (`account_id`),
	CONSTRAINT `checkouts_FK_1`
		FOREIGN KEY (`account_id`)
		REFERENCES `accounts` (`id`)
		ON DELETE CASCADE,
	INDEX `checkouts_FI_2` (`node_id`),
	CONSTRAINT `checkouts_FK_2`
		FOREIGN KEY (`node_id`)
		REFERENCES `nodes` (`id`)
		ON DELETE CASCADE,
	INDEX `checkouts_FI_3` (`user_id`),
	CONSTRAINT `checkouts_FK_3`
		FOREIGN KEY (`user_id`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE,
	INDEX `checkouts_FI_4` (`route_id`),
	CONSTRAINT `checkouts_FK_4`
		FOREIGN KEY (`route_id`)
		REFERENCES `routes` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- documents_categories
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `documents_categories`;


CREATE TABLE `documents_categories`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100)  NOT NULL,
	PRIMARY KEY (`id`)
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- documents
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `documents`;


CREATE TABLE `documents`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(100)  NOT NULL,
	`description` TEXT  NOT NULL,
	`filename` VARCHAR(100),
	`document_category_id` INTEGER,
	`deleted_by` INTEGER,
	`deleted_at` DATETIME,
	`updated_by` INTEGER,
	`updated_at` DATETIME,
	`created_by` INTEGER,
	`created_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `documents_FI_1` (`document_category_id`),
	CONSTRAINT `documents_FK_1`
		FOREIGN KEY (`document_category_id`)
		REFERENCES `documents_categories` (`id`)
		ON DELETE CASCADE,
	INDEX `documents_FI_2` (`deleted_by`),
	CONSTRAINT `documents_FK_2`
		FOREIGN KEY (`deleted_by`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE,
	INDEX `documents_FI_3` (`updated_by`),
	CONSTRAINT `documents_FK_3`
		FOREIGN KEY (`updated_by`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE,
	INDEX `documents_FI_4` (`created_by`),
	CONSTRAINT `documents_FK_4`
		FOREIGN KEY (`created_by`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- documents_users
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `documents_users`;


CREATE TABLE `documents_users`
(
	`document_id` INTEGER  NOT NULL,
	`user_id` INTEGER  NOT NULL,
	PRIMARY KEY (`document_id`,`user_id`),
	CONSTRAINT `documents_users_FK_1`
		FOREIGN KEY (`document_id`)
		REFERENCES `documents` (`id`)
		ON DELETE CASCADE,
	INDEX `documents_users_FI_2` (`user_id`),
	CONSTRAINT `documents_users_FK_2`
		FOREIGN KEY (`user_id`)
		REFERENCES `users` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

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