-- Servera SQL fails priekš md_traffic
-- IZPILDI TO ATTĀLAJĀ DB SAVIENOJUMĀ (153.92.1.116:3308)
-- Ja DB md_traffic jau ir izveidota, pietiek ar USE md_traffic;

USE md_traffic;

DROP TABLE IF EXISTS plan_signal;
DROP TABLE IF EXISTS plan;
DROP TABLE IF EXISTS intersections;

CREATE TABLE intersections (
    id INT PRIMARY KEY
);

CREATE TABLE plan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    intersection_id INT,
    cycle_len INT,
    amber_len INT,
    red_clear INT,
    yellow_len INT
);

CREATE TABLE plan_signal (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_id INT,
    signal_group INT,
    signal_kind TINYINT,
    green_time INT,
    offset INT
);

INSERT INTO intersections VALUES
(10),
(14),
(40),
(99);

-- 10 Uzvaras / Dobeles
INSERT INTO plan (intersection_id,cycle_len,amber_len,red_clear,yellow_len)
VALUES (10,90,2,1,3);

SET @p1 = LAST_INSERT_ID();

INSERT INTO plan_signal VALUES
(NULL,@p1,1,1,53,0),
(NULL,@p1,2,1,53,0),
(NULL,@p1,101,2,10,55),
(NULL,@p1,102,2,10,55);

-- 14 Lielā / IKI
INSERT INTO plan (intersection_id,cycle_len,amber_len,red_clear,yellow_len)
VALUES (14,90,2,1,3);

SET @p2 = LAST_INSERT_ID();

INSERT INTO plan_signal VALUES
(NULL,@p2,4,1,49,0),
(NULL,@p2,10,1,49,0),
(NULL,@p2,104,2,11,60),
(NULL,@p2,110,2,11,60);

-- 40 Rīgas / Keramika
INSERT INTO plan (intersection_id,cycle_len,amber_len,red_clear,yellow_len)
VALUES (40,150,2,1,3);

SET @p3 = LAST_INSERT_ID();

INSERT INTO plan_signal VALUES
(NULL,@p3,1,1,51,0),
(NULL,@p3,4,1,79,60),
(NULL,@p3,10,1,62,0),
(NULL,@p3,101,2,39,0),
(NULL,@p3,104,2,17,90),
(NULL,@p3,110,2,17,90);

-- 99 Rūpniecības / Tērvetes
INSERT INTO plan (intersection_id,cycle_len,amber_len,red_clear,yellow_len)
VALUES (99,62,2,1,3);

SET @p99 = LAST_INSERT_ID();

INSERT INTO plan_signal VALUES
(NULL,@p99,1,1,26,0),
(NULL,@p99,7,1,26,0),
(NULL,@p99,4,1,25,31),
(NULL,@p99,10,1,25,31),
(NULL,@p99,11,1,23,31),
(NULL,@p99,101,2,19,31),
(NULL,@p99,104,2,21,0),
(NULL,@p99,110,2,21,0),
(NULL,@p99,111,2,21,0);
