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

/*!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 */;


CREATE DATABASE IF NOT EXISTS `va_trenesanas_eksamenam_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `va_trenesanas_eksamenam_1`;

CREATE TABLE IF NOT EXISTS `mera_vieniba` (
  `vieniba_id` int(11) NOT NULL AUTO_INCREMENT,
  `kods` varchar(20) NOT NULL,
  `nosaukums` varchar(100) NOT NULL,
  PRIMARY KEY (`vieniba_id`),
  UNIQUE KEY `kods` (`kods`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Mērvienību saraksts';

INSERT IGNORE INTO `mera_vieniba` (`vieniba_id`, `kods`, `nosaukums`) VALUES
	(1, 'kompl', 'komplekts'),
	(2, 'gab', 'gabals'),
	(3, 'm', 'metrs'),
	(4, 'm2', 'kvadratmetrs');

CREATE TABLE IF NOT EXISTS `projekts` (
  `projekts_id` int(11) NOT NULL AUTO_INCREMENT,
  `nosaukums` varchar(300) NOT NULL,
  `apraksts` text DEFAULT NULL,
  `izveidots_datums` date NOT NULL DEFAULT curdate(),
  PRIMARY KEY (`projekts_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Projektu pamatinformācija';

INSERT IGNORE INTO `projekts` (`projekts_id`, `nosaukums`, `apraksts`, `izveidots_datums`) VALUES
	(1, 'Jelgava luksofori', 'Papildus darbi Lielas ielas un Dobeles sosejas posmam', '2026-05-10');

CREATE TABLE IF NOT EXISTS `tame` (
  `tame_id` int(11) NOT NULL AUTO_INCREMENT,
  `projekts_id` int(11) NOT NULL,
  `nosaukums` varchar(300) NOT NULL,
  `valuta` varchar(10) NOT NULL DEFAULT 'EUR',
  `izveidots_datums` date NOT NULL DEFAULT curdate(),
  PRIMARY KEY (`tame_id`),
  KEY `fk_tame_projekts` (`projekts_id`),
  CONSTRAINT `fk_tame_projekts` FOREIGN KEY (`projekts_id`) REFERENCES `projekts` (`projekts_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Konkrēta projekta tāme';

INSERT IGNORE INTO `tame` (`tame_id`, `projekts_id`, `nosaukums`, `valuta`, `izveidots_datums`) VALUES
	(1, 1, 'Lokala tame', 'EUR', '2026-05-10');

CREATE TABLE IF NOT EXISTS `tames_pozicija` (
  `pozicija_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tame_id` int(11) NOT NULL,
  `secibas_nr` int(11) NOT NULL,
  `izmaksu_pozicija` varchar(20) NOT NULL,
  `specifikacijas_nr` varchar(50) DEFAULT NULL,
  `darba_nosaukums` text NOT NULL,
  `vieniba_id` int(11) DEFAULT NULL,
  `daudzums` decimal(14,3) NOT NULL,
  PRIMARY KEY (`pozicija_id`),
  UNIQUE KEY `uq_tame_seciba` (`tame_id`,`secibas_nr`),
  UNIQUE KEY `uq_tame_pozicija` (`tame_id`,`izmaksu_pozicija`),
  KEY `fk_pozicija_vieniba` (`vieniba_id`),
  CONSTRAINT `fk_pozicija_tame` FOREIGN KEY (`tame_id`) REFERENCES `tame` (`tame_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pozicija_vieniba` FOREIGN KEY (`vieniba_id`) REFERENCES `mera_vieniba` (`vieniba_id`),
  CONSTRAINT `chk_daudzums` CHECK (`daudzums` >= 0)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Tames detalizētās pozīcijas';

INSERT IGNORE INTO `tames_pozicija` (`pozicija_id`, `tame_id`, `secibas_nr`, `izmaksu_pozicija`, `specifikacijas_nr`, `darba_nosaukums`, `vieniba_id`, `daudzums`) VALUES
	(1, 1, 1, '1,1', '1,1', 'Kontrolieris "SWARCO Traffic Hungaria Kft" VTC 2000 ar OCTI CPU mini vai analogs, kas savienojams ar esosiem uzstaditiem luksoforiem un pievienojams optiskajam kabelu tiklam', 1, 1.000),
	(2, 1, 2, '1,2', '1,2', 'Kontroliera skapis', 2, 1.000),
	(3, 1, 3, '1,3', '1,3', 'Elektroenergijas uzskaites skapis LIS-A', 2, 1.000),
	(4, 1, 4, '1,4', '1,4', 'Luksoforu signalgalvas autotransportam ar gaismas diodem LED 300x3 (SDZ)', 1, 1.000),
	(5, 1, 5, '1,5', '1,5', 'Luksofora signalgalvas gajejiem ar gaismas diodem LED 200x2 (SZ)', 1, 1.000),
	(6, 1, 6, '1,6', '1,6', 'Luksoforu stabi ar pamati', 1, 1.000),
	(7, 1, 7, '1,7', '1,7', 'Izsaukuma pogas', 1, 1.000),
	(8, 1, 8, '1,8', '1,8', 'Kabelis MCMO', 3, 80.000),
	(9, 1, 9, '1,9', '1,9', 'Kabelis EKKJ 5x1,5 mm2', 3, 20.000),
	(10, 1, 10, '1,10', '1,10', 'Kabelis 5x6 mm2', 3, 300.000),
	(11, 1, 11, '1,11', '1,11', 'Kabelis FTP 4x2x0,5 mm2', 3, 300.000),
	(12, 1, 12, '1,12', '1,12', 'Aizsargcaurule AROT - 110 mm', 3, 50.000),
	(13, 1, 13, '1,13', '1,13', 'Signalente kabelim', 3, 50.000),
	(14, 1, 14, '1,14', '1,14', 'Atkartotais zemejums', 1, 1.000),
	(15, 1, 15, '1,15', '1,15', 'Transejas raksana 1 m dziluma un aizbersana ar smiltim bietejot', 3, 50.000),
	(16, 1, 16, '1,16', '1,16', 'Luksoforu pieslegsana un', 2, 1.000),
	(17, 1, 17, '1,17', '1,17', 'Ekrani', 1, 4.000),
	(18, 1, 18, '1,18', '1,18', 'Kontrolieru montaza', 2, 1.000),
	(19, 1, 19, '1,19', '1,19', 'Signalplana izstrade', 2, 1.000),
	(20, 1, 20, '1,20', '1,20', 'Betona brugakmens seguma', 4, 16.000),
	(21, 1, 21, '1,21', '1,21', 'Betona brugakmens seguma', 4, 16.000),
	(22, 1, 22, '1,22', '1,22', 'Skanas signalu izbve 5 luksoforu objektiem', 1, 5.000),
	(23, 1, 23, '1,23', '1,23', '5 Luksoforu sinhronizacija', 2, 1.000),
	(24, 1, 24, '1,24', '1,24', 'Programmesana', 1, 5.000),
	(25, 1, 25, '1,25', '1,25', 'Signalplanu parstrade 4 luksoforiem', 1, 5.000),
	(26, 1, 26, '1,26', '1,26', '5 luksoforu pieslegsana optiskajam kabelu tiklam', 2, 4.000);

CREATE TABLE IF NOT EXISTS `tames_uzcenojums` (
  `uzcenojums_id` int(11) NOT NULL AUTO_INCREMENT,
  `tame_id` int(11) NOT NULL,
  `tips` varchar(40) NOT NULL,
  `procents` decimal(7,3) NOT NULL,
  `secibas_nr` int(11) NOT NULL,
  `komentars` text DEFAULT NULL,
  PRIMARY KEY (`uzcenojums_id`),
  UNIQUE KEY `uq_tame_tips` (`tame_id`,`tips`),
  CONSTRAINT `fk_uzc_tame` FOREIGN KEY (`tame_id`) REFERENCES `tame` (`tame_id`) ON DELETE CASCADE,
  CONSTRAINT `chk_procents` CHECK (`procents` >= 0),
  CONSTRAINT `chk_tips` CHECK (`tips` in ('VIRSIZDEVUMI','PELNA','SOC_NODOKLIS','PVN','DARBA_AIZSARDZIBA_INFO'))
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Uzcenojumu un nodokļu procenti';

INSERT IGNORE INTO `tames_uzcenojums` (`uzcenojums_id`, `tame_id`, `tips`, `procents`, `secibas_nr`, `komentars`) VALUES
	(4, 1, 'VIRSIZDEVUMI', 5.000, 1, NULL),
	(5, 1, 'DARBA_AIZSARDZIBA_INFO', 0.000, 2, 't.sk. darba aizsardziba'),
	(6, 1, 'PELNA', 2.000, 3, NULL),
	(7, 1, 'SOC_NODOKLIS', 24.000, 4, NULL),
	(8, 1, 'PVN', 21.000, 5, NULL);

/*!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) */;
