BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `migrations` (
	`id`	integer NOT NULL PRIMARY KEY AUTOINCREMENT,
	`migration`	varchar NOT NULL,
	`batch`	integer NOT NULL
);
INSERT INTO `migrations` VALUES (1,'2018_06_08_163535_create_Configurations_table',1);
INSERT INTO `migrations` VALUES (2,'2018_06_08_163535_create_Departments_table',1);
INSERT INTO `migrations` VALUES (3,'2018_06_08_163536_create_Organizations_table',1);
INSERT INTO `migrations` VALUES (4,'2018_06_08_163536_create_devices_table',1);
INSERT INTO `migrations` VALUES (5,'2018_06_08_163537_create_PoolsInDepartments_table',1);
INSERT INTO `migrations` VALUES (6,'2018_06_08_163537_create_Pools_table',1);
CREATE TABLE IF NOT EXISTS `devices` (
	`id`	integer NOT NULL,
	`serial_number`	varchar,
	`organizations_id`	integer NOT NULL,
	FOREIGN KEY(`organizations_id`) REFERENCES `Organizations`(`id`) on delete NO ACTION on update NO ACTION,
	PRIMARY KEY(`id`,`organizations_id`)
);
CREATE TABLE IF NOT EXISTS `PoolsInDepartments` (
	`pools_id`	integer NOT NULL,
	`departaments_id`	integer NOT NULL,
	FOREIGN KEY(`departaments_id`) REFERENCES `Departments`(`id`) on delete NO ACTION on update NO ACTION,
	PRIMARY KEY(`pools_id`,`departaments_id`),
	FOREIGN KEY(`pools_id`) REFERENCES `Pools`(`id`) on delete NO ACTION on update NO ACTION
);
CREATE TABLE IF NOT EXISTS `Pools` (
	`id`	integer NOT NULL,
	`configurations_id`	integer NOT NULL,
	`owner_organizations_id`	integer NOT NULL,
	`name`	TEXT,
	FOREIGN KEY(`owner_organizations_id`) REFERENCES `Organizations`(`id`) on delete NO ACTION on update NO ACTION,
	FOREIGN KEY(`configurations_id`) REFERENCES `Configurations`(`id`) on delete NO ACTION on update NO ACTION,
	PRIMARY KEY(`id`,`configurations_id`,`owner_organizations_id`)
);
CREATE TABLE IF NOT EXISTS `Organizations` (
	`id`	integer NOT NULL,
	`name`	TEXT,
	PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `Departments` (
	`id`	integer NOT NULL,
	`organizations_id`	integer NOT NULL,
	`default_departament`	integer,
	`name`	TEXT,
	FOREIGN KEY(`organizations_id`) REFERENCES `Organizations`(`id`) on delete NO ACTION on update NO ACTION,
	PRIMARY KEY(`id`,`organizations_id`)
);
CREATE TABLE IF NOT EXISTS `Configurations` (
	`id`	integer NOT NULL,
	`name`	TEXT,
	`options`	TEXT,
	PRIMARY KEY(`id`)
);
INSERT INTO `Configurations` VALUES (1,NULL,NULL);
INSERT INTO `Configurations` VALUES (2,NULL,NULL);
CREATE INDEX IF NOT EXISTS `fk_devices_Organizations1_idx` ON `devices` (
	`organizations_id`
);
CREATE INDEX IF NOT EXISTS `fk_Pools_Organizations1_idx` ON `Pools` (
	`owner_organizations_id`
);
CREATE INDEX IF NOT EXISTS `fk_Pools_Configurations1_idx` ON `Pools` (
	`configurations_id`
);
CREATE INDEX IF NOT EXISTS `fk_PoolsInDepartament_Pools` ON `PoolsInDepartments` (
	`pools_id`
);
CREATE INDEX IF NOT EXISTS `fk_PoolsInDepartament_Departament` ON `PoolsInDepartments` (
	`departaments_id`
);
CREATE INDEX IF NOT EXISTS `fk_Departments_Organizations1_idx` ON `Departments` (
	`organizations_id`
);
COMMIT;