-- phpMyAdmin SQL Dump
-- version 5.1.1deb5ubuntu1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3308:3308
-- Generation Time: Mar 05, 2026 at 07:08 PM
-- Server version: 8.0.45-0ubuntu0.22.04.1
-- PHP Version: 8.1.2-1ubuntu2.23

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ua_signalplane`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`ulevics_a`@`localhost` PROCEDURE `migrate_patterns` ()  BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE row_id INT;
DECLARE p TEXT;
DECLARE g_id INT;

DECLARE cur CURSOR FOR 
SELECT r.id, r.pattern, g.id
FROM plan_signal_rows r
JOIN signal_groups g 
ON g.plan_id = r.plan_id 
AND g.code = r.signal_code;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP

FETCH cur INTO row_id, p, g_id;

IF done THEN
LEAVE read_loop;
END IF;

SET @i = 1;
SET @len = CHAR_LENGTH(p);
SET @pos = 1;

WHILE @i <= @len DO

SET @c = SUBSTRING(p,@i,1);
SET @count = 1;

WHILE @i+@count <= @len AND SUBSTRING(p,@i+@count,1)=@c DO
SET @count=@count+1;
END WHILE;

INSERT INTO signal_group_states(group_id,state,duration,position)
VALUES(g_id,@c,@count,@pos);

SET @pos=@pos+1;
SET @i=@i+@count;

END WHILE;

END LOOP;

CLOSE cur;

END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `intersections`
--

CREATE TABLE `intersections` (
  `id` int NOT NULL COMMENT 'Unikāls krustojuma identifikators',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Krustojuma nosaukums',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Krustojumu saraksts, kuriem sistēmā tiek saglabāti luksoforu signālplāni';

--
-- Dumping data for table `intersections`
--

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

-- --------------------------------------------------------

--
-- Table structure for table `signal_groups`
--

CREATE TABLE `signal_groups` (
  `id` int NOT NULL COMMENT 'Unikāls signālu grupas identifikators',
  `plan_id` int DEFAULT NULL COMMENT 'Signālplāna ID',
  `code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Signāla grupas kods (V1, GP1 utt.)',
  `type` char(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Luksoforu signālu grupas konkrētajā signālplānā';

--
-- Dumping data for table `signal_groups`
--

INSERT INTO `signal_groups` (`id`, `plan_id`, `code`, `type`, `created_at`) VALUES
(1, 1, 'V1', 'V', '2026-03-05 17:38:55'),
(2, 1, 'V4', 'V', '2026-03-05 17:38:55'),
(3, 1, 'V7', 'V', '2026-03-05 17:38:55'),
(4, 1, 'V10', 'V', '2026-03-05 17:38:55'),
(5, 1, 'GP1', 'P', '2026-03-05 17:38:55'),
(6, 1, 'GP4', 'P', '2026-03-05 17:38:55'),
(7, 1, 'GP7', 'P', '2026-03-05 17:38:55'),
(8, 1, 'GP10', 'P', '2026-03-05 17:38:55'),
(9, 2, 'V1', 'V', '2026-03-05 17:38:55'),
(10, 2, 'V4', 'V', '2026-03-05 17:38:55'),
(11, 2, 'V7', 'V', '2026-03-05 17:38:55'),
(12, 2, 'V10', 'V', '2026-03-05 17:38:55'),
(13, 2, 'GP1', 'P', '2026-03-05 17:38:55'),
(14, 2, 'GP4', 'P', '2026-03-05 17:38:55'),
(15, 2, 'GP7', 'P', '2026-03-05 17:38:55'),
(16, 2, 'GP10', 'P', '2026-03-05 17:38:55'),
(17, 3, 'V1', 'V', '2026-03-05 17:38:55'),
(18, 3, 'V2', 'V', '2026-03-05 17:38:55'),
(19, 3, 'V3', 'V', '2026-03-05 17:38:55'),
(20, 3, 'GP1', 'P', '2026-03-05 17:38:55'),
(21, 3, 'GP2', 'P', '2026-03-05 17:38:55'),
(22, 3, 'GP3', 'P', '2026-03-05 17:38:55'),
(23, 3, 'GP4', 'P', '2026-03-05 17:38:55');

-- --------------------------------------------------------

--
-- Table structure for table `signal_group_states`
--

CREATE TABLE `signal_group_states` (
  `id` int NOT NULL COMMENT 'Unikāls stāvokļa ieraksta identifikators',
  `group_id` int DEFAULT NULL COMMENT 'Signālu grupas ID',
  `state` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Signāla stāvoklis (R,G,Y,A,B)',
  `duration` int DEFAULT NULL COMMENT 'Stāvokļa ilgums sekundēs',
  `position` int DEFAULT NULL COMMENT 'Stāvokļa secība signālplānā'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Signālu stāvokļi katrai signāla grupai noteiktā secībā';

--
-- Dumping data for table `signal_group_states`
--

INSERT INTO `signal_group_states` (`id`, `group_id`, `state`, `duration`, `position`) VALUES
(1, 1, 'R', 82, 1),
(2, 1, 'A', 2, 2),
(3, 1, 'G', 32, 3),
(4, 1, 'Y', 3, 4),
(5, 1, 'R', 83, 5),
(6, 1, 'A', 2, 6),
(7, 1, 'G', 7, 7),
(8, 2, 'R', 2, 1),
(9, 2, 'A', 2, 2),
(10, 2, 'G', 59, 3),
(11, 2, 'Y', 3, 4),
(12, 2, 'R', 56, 5),
(13, 2, 'A', 2, 6),
(14, 2, 'G', 59, 7),
(15, 2, 'Y', 3, 8),
(16, 2, 'R', 25, 9),
(17, 3, 'R', 82, 1),
(18, 3, 'A', 2, 2),
(19, 3, 'G', 32, 3),
(20, 3, 'Y', 3, 4),
(21, 3, 'R', 83, 5),
(22, 3, 'A', 2, 6),
(23, 3, 'G', 7, 7),
(24, 4, 'R', 2, 1),
(25, 4, 'A', 2, 2),
(26, 4, 'G', 72, 3),
(27, 4, 'Y', 3, 4),
(28, 4, 'R', 43, 5),
(29, 4, 'A', 2, 6),
(30, 4, 'G', 72, 7),
(31, 4, 'Y', 3, 8),
(32, 4, 'R', 12, 9),
(33, 5, 'R', 4, 1),
(34, 5, 'G', 54, 2),
(35, 5, 'B', 5, 3),
(36, 5, 'R', 61, 4),
(37, 5, 'G', 54, 5),
(38, 5, 'B', 5, 6),
(39, 5, 'R', 28, 7),
(40, 6, 'R', 84, 1),
(41, 6, 'G', 15, 2),
(42, 6, 'B', 5, 3),
(43, 6, 'R', 100, 4),
(44, 6, 'G', 7, 5),
(45, 7, 'R', 4, 1),
(46, 7, 'G', 54, 2),
(47, 7, 'B', 5, 3),
(48, 7, 'R', 61, 4),
(49, 7, 'G', 54, 5),
(50, 7, 'B', 5, 6),
(51, 7, 'R', 28, 7),
(52, 8, 'R', 84, 1),
(53, 8, 'G', 15, 2),
(54, 8, 'B', 5, 3),
(55, 8, 'R', 100, 4),
(56, 8, 'G', 7, 5),
(57, 9, 'R', 6, 1),
(58, 9, 'A', 2, 2),
(59, 9, 'G', 15, 3),
(60, 9, 'Y', 3, 4),
(61, 9, 'R', 62, 5),
(62, 9, 'A', 1, 6),
(63, 9, 'G', 7, 7),
(64, 10, 'G', 3, 1),
(65, 10, 'Y', 1, 2),
(66, 10, 'R', 24, 3),
(67, 10, 'A', 2, 4),
(68, 10, 'G', 52, 5),
(69, 10, 'Y', 3, 6),
(70, 10, 'R', 11, 7),
(71, 11, 'R', 6, 1),
(72, 11, 'A', 2, 2),
(73, 11, 'G', 15, 3),
(74, 11, 'Y', 3, 4),
(75, 11, 'R', 62, 5),
(76, 11, 'A', 1, 6),
(77, 11, 'G', 7, 7),
(78, 12, 'G', 3, 1),
(79, 12, 'Y', 1, 2),
(80, 12, 'R', 24, 3),
(81, 12, 'A', 2, 4),
(82, 12, 'G', 52, 5),
(83, 12, 'Y', 3, 6),
(84, 12, 'R', 11, 7),
(85, 13, 'R', 30, 1),
(86, 13, 'G', 37, 2),
(87, 13, 'B', 6, 3),
(88, 13, 'R', 23, 4),
(89, 14, 'R', 8, 1),
(90, 14, 'G', 8, 2),
(91, 14, 'B', 5, 3),
(92, 14, 'R', 68, 4),
(93, 14, 'G', 7, 5),
(94, 15, 'R', 30, 1),
(95, 15, 'G', 37, 2),
(96, 15, 'B', 6, 3),
(97, 15, 'R', 23, 4),
(98, 16, 'R', 8, 1),
(99, 16, 'G', 8, 2),
(100, 16, 'B', 5, 3),
(101, 16, 'R', 68, 4),
(102, 16, 'G', 7, 5),
(103, 17, 'R', 33, 1),
(104, 17, 'A', 2, 2),
(105, 17, 'G', 16, 3),
(106, 17, 'Y', 3, 4),
(107, 17, 'R', 4, 5),
(108, 18, 'R', 33, 1),
(109, 18, 'A', 2, 2),
(110, 18, 'G', 16, 3),
(111, 18, 'Y', 3, 4),
(112, 18, 'R', 4, 5),
(113, 19, 'R', 1, 1),
(114, 19, 'A', 2, 2),
(115, 19, 'G', 26, 3),
(116, 19, 'Y', 3, 4),
(117, 19, 'R', 23, 5),
(118, 19, 'A', 2, 6),
(119, 19, 'G', 1, 7),
(120, 20, 'R', 3, 1),
(121, 20, 'G', 18, 2),
(122, 20, 'B', 5, 3),
(123, 20, 'R', 31, 4),
(124, 20, 'G', 1, 5),
(125, 21, 'R', 3, 1),
(126, 21, 'G', 18, 2),
(127, 21, 'B', 5, 3),
(128, 21, 'R', 31, 4),
(129, 21, 'G', 1, 5),
(130, 22, 'R', 35, 1),
(131, 22, 'G', 6, 2),
(132, 22, 'B', 5, 3),
(133, 22, 'R', 12, 4),
(134, 23, 'R', 35, 1),
(135, 23, 'G', 6, 2),
(136, 23, 'B', 5, 3),
(137, 23, 'R', 12, 4);

-- --------------------------------------------------------

--
-- Table structure for table `signal_plans`
--

CREATE TABLE `signal_plans` (
  `id` int NOT NULL COMMENT 'Unikāls signālplāna identifikators',
  `intersection_id` int DEFAULT NULL COMMENT 'Krustojuma ID, kuram pieder šis signālplāns',
  `plan_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Signālplāna nosaukums',
  `cycle_time_sec` int DEFAULT NULL COMMENT 'Signālplāna cikla ilgums sekundēs',
  `created_at` datetime DEFAULT NULL COMMENT 'Signālplāna izveides datums un laiks'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Krustojumu luksoforu signālplāni ar cikla garumu';

--
-- Dumping data for table `signal_plans`
--

INSERT INTO `signal_plans` (`id`, `intersection_id`, `plan_name`, `cycle_time_sec`, `created_at`) VALUES
(1, 20, 'Lielā-Pētera-Barona', 211, '2026-03-05 17:36:21'),
(2, 47, 'Rūpniecības-Kalpaka-Dzelzceļnieku', 96, '2026-03-05 17:36:21'),
(3, 8, 'Dobeles-Pasta-Blaumaņa', 58, '2026-03-05 17:36:21');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `intersections`
--
ALTER TABLE `intersections`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `signal_groups`
--
ALTER TABLE `signal_groups`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `signal_group_states`
--
ALTER TABLE `signal_group_states`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `signal_plans`
--
ALTER TABLE `signal_plans`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `intersections`
--
ALTER TABLE `intersections`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls krustojuma identifikators', AUTO_INCREMENT=57;

--
-- AUTO_INCREMENT for table `signal_groups`
--
ALTER TABLE `signal_groups`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls signālu grupas identifikators', AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for table `signal_group_states`
--
ALTER TABLE `signal_group_states`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls stāvokļa ieraksta identifikators', AUTO_INCREMENT=138;

--
-- AUTO_INCREMENT for table `signal_plans`
--
ALTER TABLE `signal_plans`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'Unikāls signālplāna identifikators', AUTO_INCREMENT=4;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;