CREATE DATABASE IF NOT EXISTS gn_traffic_signals CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gn_traffic_signals;

-- -------------------------------------------------------------
-- Tabula: intersections
-- Glabā visus satiksmes krustojumus
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS intersections (
    id   INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Unikāls krustojuma identifikators',
    name VARCHAR(100) NOT NULL           COMMENT 'Krustojuma nosaukums (iela1-iela2)'
) COMMENT='Satiksmes signālu krustojumi Jelgavā';

-- -------------------------------------------------------------
-- Tabula: signal_plans
-- Glabā signālplāna laika parametrus katram krustojumam
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS signal_plans (
    id              INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Unikāls signālplāna identifikators',
    intersection_id INT NOT NULL                   COMMENT 'Atsauce uz krustojumu (intersections.id)',
    cycle           INT NOT NULL                   COMMENT 'Pilna cikla ilgums sekundēs',
    amber           INT NOT NULL                   COMMENT 'Dzeltenā (amber) fāzes ilgums sekundēs',
    red_amber       INT NOT NULL                   COMMENT 'Sarkans un dzeltens pārejas ilgums sekundēs',
    yellow          INT NOT NULL                   COMMENT 'Gājēju mirgošanas fāzes ilgums sekundēs',
    FOREIGN KEY (intersection_id) REFERENCES intersections(id)
) COMMENT='Signālplāna laika parametri katram krustojumam';

-- -------------------------------------------------------------
-- Tabula: directions
-- Glabā katru virzienu un tā precīzos signālu intervālus
-- Visas sekundes ir 1-indeksētas (1 = pirmā sekunde ciklā)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS directions (
    id              INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Unikāls virziena identifikators',
    signal_plan_id  INT NOT NULL                   COMMENT 'Atsauce uz signālplānu (signal_plans.id)',
    name            VARCHAR(10) NOT NULL           COMMENT 'Virziena nosaukums: V4, V7, GP1, utt.',
    type            CHAR(1) NOT NULL               COMMENT 'Tips: V=transportlīdzeklis, A=bulta, P=gājējs',
    -- Zaļās fāzes robežas
    green_start     INT NOT NULL                   COMMENT 'Zaļās gaismas sākuma sekunde (ieskaitot)',
    green_end       INT NOT NULL                   COMMENT 'Zaļās gaismas beigu sekunde (ieskaitot)',
    -- Dzeltenā (amber) fāze — transportlīdzekļiem pēc zaļās
    amber_start     INT DEFAULT NULL               COMMENT 'Dzeltenās gaismas sākuma sekunde',
    amber_end       INT DEFAULT NULL               COMMENT 'Dzeltenās gaismas beigu sekunde',
    -- Sarkans un dzeltens pārejas fāze — pirms zaļās vai pēc sarkanās
    red_amber_start INT DEFAULT NULL               COMMENT 'Sarkanas un dzeltenās gaismas sākuma sekunde',
    red_amber_end   INT DEFAULT NULL               COMMENT 'Sarkanas un dzeltenās gaismas beigu sekunde',
    -- Gājēju mirgošanas fāze — puse balta, puse zaļa (vertikāli)
    flash_start     INT DEFAULT NULL               COMMENT 'Gājēju mirgošanas sākuma sekunde',
    flash_end       INT DEFAULT NULL               COMMENT 'Gājēju mirgošanas beigu sekunde',
    FOREIGN KEY (signal_plan_id) REFERENCES signal_plans(id)
) COMMENT='Virzieni un precīzi signālu intervāli katrā signālplānā';

-- =============================================================
-- VISI KRUSTOJUMI
-- =============================================================
INSERT INTO intersections (id, name) VALUES
(1,  'Atmodas-Asteru'),
(2,  'Brīvības-Garozas'),
(3,  'Brīvības-Lāčplēša'),
(4,  'Dobeles-4.līnija'),
(5,  'Dobeles-5.līnija'),
(6,  'Dobeles-Atmodas'),
(7,  'Dobeles-Brieža'),
(8,  'Dobeles-Pasta-Blaumaņa'),
(9,  'Dobeles-Satiksmes-Aspazijas'),
(10, 'Dobeles-Uzvaras'),
(11, 'Lielā-Akadēmijas-Uzvaras'),
(12, 'Lielā-Čakstes'),
(13, 'Lielā-Dambja-Barona'),
(14, 'Lielā-IKI'),
(15, 'Lielā-Kalpaka'),
(16, 'Lielā-Katoļu'),
(17, 'Lielā-Māras-Dobeles'),
(18, 'Lielā-Mātera'),
(19, 'Lielā-Pasta'),
(20, 'Lielā-Pētera-Barona'),
(21, 'Lietuvas-Miera-Platones'),
(22, 'Lietuvas-Savienības'),
(23, 'Mātera-Svētes'),
(24, 'Mātera-Zirgu'),
(26, 'Pasta-Driksas'),
(27, 'Pasta-Edžus'),
(28, 'Pasta-Sporta-Zirgu'),
(29, 'Pasta-Stacijas'),
(30, 'Raiņa-Akadēmijas'),
(31, 'Raiņa-Kalpaka'),
(32, 'Raiņa-Katoļu'),
(33, 'Raiņa-Mātera'),
(34, 'Raiņa-Pasta'),
(35, 'Raiņa-Tērvetes-Sarmas-Filozofu'),
(36, 'Rīgas-Brīvības'),
(37, 'Rīgas-Institūta-Lāčplēša'),
(38, 'Rīgas-Izstādes'),
(39, 'Rīgas-Kalnciema-Garozas'),
(40, 'Rīgas-Keramika'),
(41, 'Rīgas-Loka'),
(42, 'Rīgas-Pērnavas-Pumpura'),
(43, 'Rīgas-Strazdu'),
(44, 'Rūpniecības'),
(45, 'Rūpniecības-Dambja-Atmodas'),
(46, 'Rūpniecības-Filozofu'),
(47, 'Rūpniecības-Kalpaka-Dzelzceļnieku'),
(48, 'Rūpniecības-Mātera'),
(49, 'Rūpniecības-Tērvetes'),
(50, 'Satiksmes-Meiju-Zvejnieku'),
(51, 'Uzvaras-Barona'),
(52, 'Zemgales-Jāņa-Palīdzības'),
(53, 'Zemgales-Sporta'),
(54, 'Zemgales-Stacijas'),
(55, 'Pasta-Barona'),
(56, 'Dobeles-3.līnija-Malkas');

-- =============================================================
-- DOBELES / ATMODAS  (krustojuma id=6)
-- Cikls=90s
-- =============================================================
INSERT INTO signal_plans (id, intersection_id, cycle, amber, red_amber, yellow)
VALUES (1, 6, 90, 3, 3, 5);

-- V4: 1-53 zaļš | 54-56 dzeltens | 57-87 sarkans | 88-90 sark.+dzelt.
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (1,'V4','V',1,53,54,56,88,90,NULL,NULL);

-- V6: 1-32 sarkans | 33-53 zaļš | 54-90 sarkans  (bulta — nav dzeltenā fāze)
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (1,'V6','A',33,53,NULL,NULL,NULL,NULL,NULL,NULL);

-- V7: 1-58 sarkans | 59-61 sark.+dzelt. | 62-82 zaļš | 83-85 dzeltens | 86-90 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (1,'V7','V',62,82,83,85,59,61,NULL,NULL);

-- V10: 1-27 zaļš | 28-30 dzeltens | 31-87 sarkans | 88-90 sark.+dzelt.
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (1,'V10','A',1,27,28,30,88,90,NULL,NULL);

-- GP4: 1-61 sarkans | 62-73 zaļš | 74-78 mirgošana (balta/zaļa) | 79-90 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (1,'GP4','P',62,73,NULL,NULL,NULL,NULL,74,78);

-- GP7: 1-22 zaļš | 23-27 mirgošana (balta/zaļa) | 28-90 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (1,'GP7','P',1,22,NULL,NULL,NULL,NULL,23,27);

-- =============================================================
-- RAIŅA / KATOĻU  (krustojuma id=32)
-- Cikls=75s
-- =============================================================
INSERT INTO signal_plans (id, intersection_id, cycle, amber, red_amber, yellow)
VALUES (2, 32, 75, 3, 3, 5);

-- V4: 1-30 zaļš | 31-33 dzeltens | 34-72 sarkans | 73-75 sark.+dzelt.
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (2,'V4','V',1,30,31,33,73,75,NULL,NULL);

-- V10: 1-30 zaļš | 31-33 dzeltens | 34-72 sarkans | 73-75 sark.+dzelt.
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (2,'V10','V',1,30,31,33,73,75,NULL,NULL);

-- V1: 1-35 sarkans | 36-38 sark.+dzelt. | 39-67 zaļš | 68-70 dzeltens | 71-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (2,'V1','V',39,67,68,70,36,38,NULL,NULL);

-- V7: 1-35 sarkans | 36-38 sark.+dzelt. | 39-67 zaļš | 68-70 dzeltens | 71-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (2,'V7','V',39,67,68,70,36,38,NULL,NULL);

-- GP1: 1-20 zaļš | 21-25 mirgošana | 26-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (2,'GP1','P',1,20,NULL,NULL,NULL,NULL,21,25);

-- GP4: 1-38 sarkans | 39-58 zaļš | 59-63 mirgošana | 64-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (2,'GP4','P',39,58,NULL,NULL,NULL,NULL,59,63);

-- GP7: 1-20 zaļš | 21-25 mirgošana | 26-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (2,'GP7','P',1,20,NULL,NULL,NULL,NULL,21,25);

-- GP10: 1-38 sarkans | 39-58 zaļš | 59-63 mirgošana | 64-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (2,'GP10','P',39,58,NULL,NULL,NULL,NULL,59,63);

-- =============================================================
-- MĀTERA / SVĒTES  (krustojuma id=23)
-- Cikls=75s
-- =============================================================
INSERT INTO signal_plans (id, intersection_id, cycle, amber, red_amber, yellow)
VALUES (3, 23, 75, 3, 2, 5);

-- V1: 1-33 zaļš | 34-36 dzeltens | 37-73 sarkans | 74-75 sark.+dzelt.
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (3,'V1','V',1,33,34,36,74,75,NULL,NULL);

-- V4: 1-36 sarkans | 37-38 sark.+dzelt. | 39-70 zaļš | 71-73 dzeltens | 74-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (3,'V4','V',39,70,71,73,37,38,NULL,NULL);

-- V7: 1-33 zaļš | 34-36 dzeltens | 37-73 sarkans | 74-75 sark.+dzelt.
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (3,'V7','V',1,33,34,36,74,75,NULL,NULL);

-- V10: 1-36 sarkans | 37-38 sark.+dzelt. | 39-70 zaļš | 71-73 dzeltens | 74-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (3,'V10','V',39,70,71,73,37,38,NULL,NULL);

-- GP10: 1-25 zaļš | 26-30 mirgošana | 31-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (3,'GP10','P',1,25,NULL,NULL,NULL,NULL,26,30);

-- GP1: 1-38 sarkans | 39-59 zaļš | 60-64 mirgošana | 65-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (3,'GP1','P',39,59,NULL,NULL,NULL,NULL,60,64);

-- GP4: 1-25 zaļš | 26-30 mirgošana | 31-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (3,'GP4','P',1,25,NULL,NULL,NULL,NULL,26,30);

-- GP7: 1-38 sarkans | 39-59 zaļš | 60-64 mirgošana | 65-75 sarkans
INSERT INTO directions (signal_plan_id,name,type,green_start,green_end,amber_start,amber_end,red_amber_start,red_amber_end,flash_start,flash_end)
VALUES (3,'GP7','P',39,59,NULL,NULL,NULL,NULL,60,64);