-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.32-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.11.0.7065
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Dumping structure for table traffic_lights.cycles
CREATE TABLE IF NOT EXISTS `cycles` (
  `cycle_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for each cycle',
  `object_id` int(11) NOT NULL COMMENT 'Used to get info about a object',
  `cycle_length` int(11) NOT NULL DEFAULT 0 COMMENT 'How long is 1 cycle ',
  `amber` int(11) NOT NULL COMMENT 'How many amber (red/yellow) seconds are in 1 cycle',
  `yellow` int(11) NOT NULL COMMENT 'How many yellow seconds are in 1 cycle',
  PRIMARY KEY (`cycle_id`),
  KEY `FK_cycles_objects` (`object_id`),
  CONSTRAINT `FK_cycles_objects` FOREIGN KEY (`object_id`) REFERENCES `objects` (`object_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Contains information about 1 full cycle';

-- Dumping data for table traffic_lights.cycles: ~54 rows (approximately)
INSERT INTO `cycles` (`cycle_id`, `object_id`, `cycle_length`, `amber`, `yellow`) VALUES
	(1, 1, 75, 2, 3),
	(2, 2, 65, 2, 3),
	(3, 3, 100, 2, 3),
	(4, 4, 81, 2, 2),
	(5, 5, 81, 2, 3),
	(6, 6, 60, 2, 3),
	(7, 7, 62, 2, 3),
	(8, 8, 77, 2, 3),
	(9, 9, 0, 3, 2),
	(10, 10, 0, 0, 0),
	(11, 11, 0, 0, 0),
	(12, 12, 0, 0, 0),
	(13, 13, 0, 0, 0),
	(14, 14, 0, 0, 0),
	(15, 15, 0, 0, 0),
	(16, 16, 0, 0, 0),
	(17, 17, 0, 0, 0),
	(18, 18, 0, 0, 0),
	(19, 19, 0, 0, 0),
	(20, 20, 0, 0, 0),
	(21, 21, 0, 0, 0),
	(22, 22, 0, 0, 0),
	(23, 23, 0, 0, 0),
	(24, 24, 0, 0, 0),
	(25, 25, 0, 0, 0),
	(26, 26, 0, 0, 0),
	(27, 27, 0, 0, 0),
	(28, 28, 0, 0, 0),
	(29, 29, 0, 0, 0),
	(30, 30, 0, 0, 0),
	(31, 31, 0, 0, 0),
	(32, 32, 0, 0, 0),
	(33, 33, 0, 0, 0),
	(34, 34, 0, 0, 0),
	(35, 35, 0, 0, 0),
	(36, 36, 0, 0, 0),
	(37, 37, 0, 0, 0),
	(38, 38, 0, 0, 0),
	(39, 39, 0, 0, 0),
	(40, 40, 0, 0, 0),
	(41, 41, 0, 0, 0),
	(42, 42, 0, 0, 0),
	(43, 43, 0, 0, 0),
	(44, 44, 0, 0, 0),
	(45, 45, 0, 0, 0),
	(46, 46, 0, 0, 0),
	(47, 47, 0, 0, 0),
	(48, 48, 0, 0, 0),
	(49, 49, 0, 0, 0),
	(50, 50, 0, 0, 0),
	(51, 51, 0, 0, 0),
	(52, 52, 0, 0, 0),
	(53, 53, 0, 0, 0),
	(54, 54, 0, 0, 0);

-- Dumping structure for table traffic_lights.directions
CREATE TABLE IF NOT EXISTS `directions` (
  `direction_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for each direction',
  `cycle_id` int(11) NOT NULL COMMENT 'FK to know for which street this is for',
  `name` varchar(50) NOT NULL COMMENT 'Direction name',
  `type_id` int(11) NOT NULL COMMENT 'FK to know what type of direction this is',
  `green_time` int(11) NOT NULL COMMENT 'How long is the green time',
  `offset` int(11) NOT NULL COMMENT 'How many secs before the start of a cycle',
  PRIMARY KEY (`direction_id`),
  KEY `FK_directions_cycles` (`cycle_id`),
  KEY `FK_directions_signal_types` (`type_id`),
  CONSTRAINT `FK_directions_cycles` FOREIGN KEY (`cycle_id`) REFERENCES `cycles` (`cycle_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_directions_signal_types` FOREIGN KEY (`type_id`) REFERENCES `signal_types` (`type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Contains information about all of the directions and their specification';

-- Dumping data for table traffic_lights.directions: ~28 rows (approximately)
INSERT INTO `directions` (`direction_id`, `cycle_id`, `name`, `type_id`, `green_time`, `offset`) VALUES
	(1, 1, 'V1', 1, 35, 37),
	(2, 1, 'V2', 1, 35, 37),
	(3, 1, 'V3', 1, 28, 11),
	(4, 1, 'GP1', 2, 28, 11),
	(5, 1, 'GP2', 2, 28, 11),
	(6, 1, 'GP3', 2, 35, 40),
	(7, 2, 'V1', 1, 26, 0),
	(8, 2, 'V4', 1, 25, 33),
	(9, 2, 'V7', 1, 26, 0),
	(10, 2, 'V10', 1, 25, 33),
	(11, 2, 'V11_G', 3, 28, 33),
	(12, 2, 'V11_R', 1, 0, 65),
	(13, 2, 'GP1', 2, 25, 33),
	(14, 2, 'GP4', 2, 26, 0),
	(15, 2, 'GP10', 2, 26, 0),
	(16, 2, 'GP11', 2, 26, 0),
	(17, 3, 'V1', 1, 22, 33),
	(18, 3, 'V3', 3, 27, 59),
	(19, 3, 'V4', 1, 30, 87),
	(20, 3, 'V6', 3, 16, 9),
	(21, 3, 'V7', 1, 22, 33),
	(22, 3, 'V9', 3, 27, 59),
	(23, 3, 'V10', 1, 30, 87),
	(24, 3, 'V12', 3, 16, 9),
	(25, 3, 'GP1', 2, 30, 90),
	(26, 3, 'GP4', 2, 22, 33),
	(27, 3, 'GP7', 2, 31, 89),
	(28, 3, 'GP10', 2, 22, 33),
	(29, 4, 'V4', 1, 50, 29),
	(30, 4, 'V10', 1, 50, 29),
	(31, 4, 'GP10', 2, 20, 9);

-- Dumping structure for table traffic_lights.objects
CREATE TABLE IF NOT EXISTS `objects` (
  `object_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for each object',
  `name` varchar(100) NOT NULL COMMENT 'Street name that has traffic light',
  PRIMARY KEY (`object_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Contains information about multiple objects';

-- Dumping data for table traffic_lights.objects: ~54 rows (approximately)
INSERT INTO `objects` (`object_id`, `name`) VALUES
	(1, 'Lietuvas-Savienibas'),
	(2, 'Rupniecibas-Tervetes'),
	(3, 'Dobeles-Atmodas'),
	(4, 'Atmodas-Asteru'),
	(5, 'Brivibas-Garozas'),
	(6, 'Brivibas-Lacplesa'),
	(7, 'Dobeles-3.linija-Malkas'),
	(8, 'Dobeles-4.linija'),
	(9, 'Dobeles-5.linija'),
	(10, 'Dobeles-Brieza'),
	(11, 'Dobeles-Pasta-Blaumana'),
	(12, 'Dobeles-Satiksmes-Aspazijas'),
	(13, 'Dobeles-Uzvaras'),
	(14, 'Liela-Akademijas-Uzvaras'),
	(15, 'Liela-Cakstes'),
	(16, 'Liela-Dambja-Barona'),
	(17, 'Liela-IKI'),
	(18, 'Liela-Kalpaka'),
	(19, 'Liela-Katolu'),
	(20, 'Liela-Maras-Dobeles'),
	(21, 'Liela-Matera'),
	(22, 'Liela-Pasta'),
	(23, 'Liela-Petera-Barona'),
	(24, 'Lietuvas-Miera-Platones'),
	(25, 'Matera-Svetes'),
	(26, 'Matera-Zirgu'),
	(27, 'Pasta-Barona'),
	(28, 'Pasta-Driksas'),
	(29, 'Pasta-Edzus'),
	(30, 'Pasta-Sporta-Zirgu'),
	(31, 'Pasta-Stacijas'),
	(32, 'Raina-Akademijas'),
	(33, 'Raina-Kalpaka'),
	(34, 'Raina-Katolu'),
	(35, 'Raina-Matera'),
	(36, 'Raina-Pasta'),
	(37, 'Raina-Tervetes-Sarmas-Filozofu'),
	(38, 'Rigas-Brivibas'),
	(39, 'Rigas-Instituta-Lacplesa'),
	(40, 'Rigas-Izstades'),
	(41, 'Rigas-Kalnciema-Garozas'),
	(42, 'Rigas-Keramika'),
	(43, 'RIgas-Loka'),
	(44, 'RIgas-Pernavas-Pumpura'),
	(45, 'Rigas-Strazdu'),
	(46, 'Rupniecibas-Dambja-Atmodas'),
	(47, 'Rupniecibas-Filozofu'),
	(48, 'Rupniecibas-Kalpaka-Dzelzcelnieku'),
	(49, 'Rupniecibas-Matera'),
	(50, 'Satiksmes-Meiju-Zvejnieku'),
	(51, 'Uzvaras-Barona'),
	(52, 'Zemgales-Jana-Palidzibas'),
	(53, 'Zemgales-Sporta'),
	(54, 'Zemgales-Stacijas');

-- Dumping structure for table traffic_lights.signal_types
CREATE TABLE IF NOT EXISTS `signal_types` (
  `type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for each signal type',
  `type_code` varchar(10) NOT NULL DEFAULT '' COMMENT 'Signal type symbol',
  `description` varchar(255) NOT NULL COMMENT 'What the symbol symbolizes/ full name of the symbol',
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Contains information about signal types and information about them';

-- Dumping data for table traffic_lights.signal_types: ~3 rows (approximately)
INSERT INTO `signal_types` (`type_id`, `type_code`, `description`) VALUES
	(1, 'V', 'Vechicle'),
	(2, 'GP', 'Pedestrian'),
	(3, 'A', 'Addition');

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;