-- ============================================================
-- DB: eldars_trenesanas_eksamenam_1
-- Variants 4 — Akts par Janvāri
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

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

-- ============================================================
-- Tabula: mervienibas
-- Komentārs: Mērvienību uzziņu tabula
-- ============================================================
DROP TABLE IF EXISTS `mervienibas`;
CREATE TABLE `mervienibas` (
  `id`        INT          NOT NULL AUTO_INCREMENT COMMENT 'Primārā atslēga',
  `nosaukums` VARCHAR(20)  NOT NULL                COMMENT 'Mērvienības nosaukums (m., gab., kompl., km, st.)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_nosaukums` (`nosaukums`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Mērvienību uzziņu tabula. Ļauj filtrēt darbus pēc mērvienības.';

-- ============================================================
-- Tabula: darba_nri
-- Komentārs: Darba numuru uzziņu tabula
-- ============================================================
DROP TABLE IF EXISTS `darba_nri`;
CREATE TABLE `darba_nri` (
  `id` INT         NOT NULL AUTO_INCREMENT COMMENT 'Primārā atslēga',
  `nr` VARCHAR(10) NOT NULL                COMMENT 'Darba numurs (piem. 1.11, 2.4, 5.3)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_nr` (`nr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Darba numuru uzziņu tabula. Ļauj filtrēt darbus pēc numura.';

-- ============================================================
-- Tabula: darbu_veidi
-- Komentārs: Darbu veidu cenu saraksts
-- ============================================================
DROP TABLE IF EXISTS `darbu_veidi`;
CREATE TABLE `darbu_veidi` (
  `id`             INT           NOT NULL AUTO_INCREMENT COMMENT 'Primārā atslēga',
  `darba_nr_id`    INT           NOT NULL                COMMENT 'FK → darba_nri.id',
  `nosaukums`      VARCHAR(255)  NOT NULL                COMMENT 'Darba veida pilnais nosaukums',
  `mervieniba_id`  INT           NOT NULL                COMMENT 'FK → mervienibas.id',
  `cena_bez_pvn`   DECIMAL(10,2) NOT NULL                COMMENT 'Vienības cena bez PVN (Ls)',
  PRIMARY KEY (`id`),
  KEY `fk_dv_nr`   (`darba_nr_id`),
  KEY `fk_dv_merv` (`mervieniba_id`),
  CONSTRAINT `fk_dv_nr`   FOREIGN KEY (`darba_nr_id`)   REFERENCES `darba_nri`   (`id`),
  CONSTRAINT `fk_dv_merv` FOREIGN KEY (`mervieniba_id`) REFERENCES `mervienibas` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Darbu veidu cenu saraksts ar saitēm uz darba numuru un mērvienību tabulām.';

-- ============================================================
-- Tabula: akts
-- Komentārs: Mēneša darbu akta virsraksts
-- ============================================================
DROP TABLE IF EXISTS `akts`;
CREATE TABLE `akts` (
  `id`        INT         NOT NULL AUTO_INCREMENT COMMENT 'Primārā atslēga',
  `nosaukums` VARCHAR(50) NOT NULL                COMMENT 'Akta nosaukums',
  `menesis`   VARCHAR(20)   NOT NULL                COMMENT 'Mēneša nosaukums',
  `pvn_likme` DECIMAL(5,2)  NOT NULL DEFAULT 22.00  COMMENT 'PVN likme procentos',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Mēneša darbu akts.';

-- ============================================================
-- Tabula: akts_pozicijas
-- Komentārs: Akta rindas ar summām bez PVN un ar PVN
-- ============================================================
DROP TABLE IF EXISTS `akts_pozicijas`;
CREATE TABLE `akts_pozicijas` (
  `id`              INT           NOT NULL AUTO_INCREMENT COMMENT 'Primārā atslēga',
  `akts_id`         INT           NOT NULL                COMMENT 'FK → akts.id',
  `darba_veids_id`  INT           NOT NULL                COMMENT 'FK → darbu_veidi.id',
  `daudzums`        DECIMAL(10,3) NOT NULL                COMMENT 'Izpildītais daudzums',
  `kopa_bez_pvn`    DECIMAL(10,2) NOT NULL                COMMENT 'Kopā bez PVN (Ls)',
  `kopa_ar_pvn`     DECIMAL(10,2) GENERATED ALWAYS AS (ROUND(`kopa_bez_pvn` * 1.22, 2)) STORED COMMENT 'Kopā ar PVN 22% (aprēķināts automātiski)',
  PRIMARY KEY (`id`),
  KEY `fk_ap_akts`        (`akts_id`),
  KEY `fk_ap_darba_veids` (`darba_veids_id`),
  CONSTRAINT `fk_ap_akts`
    FOREIGN KEY (`akts_id`)        REFERENCES `akts`       (`id`),
  CONSTRAINT `fk_ap_darba_veids`
    FOREIGN KEY (`darba_veids_id`) REFERENCES `darbu_veidi` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Akta pozīcijas. Katra rinda satur summu bez PVN un ar PVN.';

-- ============================================================
-- DATI: mervienibas
-- ============================================================
INSERT INTO `mervienibas` (`nosaukums`) VALUES
('m.'), ('kompl.'), ('st.'), ('gab.'), ('km');

-- ============================================================
-- DATI: darba_nri
-- ============================================================
INSERT INTO `darba_nri` (`nr`) VALUES
('1.11'), ('1.25'), ('1.26'), ('1.28'),
('2.25'), ('2.27'), ('2.4'),  ('2.43'),
('3.17'), ('4.4'),  ('5.1'),  ('5.2'), ('5.3');

-- ============================================================
-- DATI: darbu_veidi
-- ============================================================
INSERT INTO `darbu_veidi` (`darba_nr_id`, `nosaukums`, `mervieniba_id`, `cena_bez_pvn`) VALUES
((SELECT id FROM darba_nri WHERE nr='1.11'), 'Signālkabeļa Cu 7x1,5 montāža stabā, konsolē',             (SELECT id FROM mervienibas WHERE nosaukums='m.'),     1.50),
((SELECT id FROM darba_nri WHERE nr='1.25'), 'Signālkabeļa spaiļu komplekta nomaiņa balstā, konsolē',    (SELECT id FROM mervienibas WHERE nosaukums='kompl.'), 5.00),
((SELECT id FROM darba_nri WHERE nr='1.26'), 'Luksofora objekta bojājuma noteikšana',                    (SELECT id FROM mervienibas WHERE nosaukums='st.'),    20.00),
((SELECT id FROM darba_nri WHERE nr='1.28'), 'SP-7 tipa sadalnes montāža',                               (SELECT id FROM mervienibas WHERE nosaukums='gab.'),   20.00),
((SELECT id FROM darba_nri WHERE nr='2.25'), 'Signālgalvas aizsargjumtiņa nomaiņa uz balsta',            (SELECT id FROM mervienibas WHERE nosaukums='gab.'),   20.00),
((SELECT id FROM darba_nri WHERE nr='2.27'), 'Spuldzes nomaiņa uz balsta',                               (SELECT id FROM mervienibas WHERE nosaukums='gab.'),    4.40),
((SELECT id FROM darba_nri WHERE nr='2.4'),  'Balsta vāka maiņa',                                        (SELECT id FROM mervienibas WHERE nosaukums='gab.'),    5.00),
((SELECT id FROM darba_nri WHERE nr='2.43'), 'Pasūtītāja transporta signālgalvas montāža uz balsta',     (SELECT id FROM mervienibas WHERE nosaukums='gab.'),   20.00),
((SELECT id FROM darba_nri WHERE nr='3.17'), 'Vadības plates ZKS tipa drošinātāja nomaiņa',              (SELECT id FROM mervienibas WHERE nosaukums='gab.'),   10.00),
((SELECT id FROM darba_nri WHERE nr='4.4'),  'Signālgalvas demontāža uz balsta',                         (SELECT id FROM mervienibas WHERE nosaukums='gab.'),   10.00),
((SELECT id FROM darba_nri WHERE nr='5.1'),  'Izsaukums luksofora objektā',                              (SELECT id FROM mervienibas WHERE nosaukums='gab.'),   10.00),
((SELECT id FROM darba_nri WHERE nr='5.2'),  'Maršruta apsekošana',                                      (SELECT id FROM mervienibas WHERE nosaukums='km'),      1.00),
((SELECT id FROM darba_nri WHERE nr='5.3'),  'Skaitītāju radījumu nolasīšana, objektu apsekošana',       (SELECT id FROM mervienibas WHERE nosaukums='gab.'), 2495.00);

-- ============================================================
-- DATI: akts
-- ============================================================
INSERT INTO `akts` (`nosaukums`, `menesis`, `pvn_likme`) VALUES
('Akts par Janvāri', 'Janvāris', 22.00);

-- ============================================================
-- DATI: akts_pozicijas (kopa_bez_pvn tieši no Excel, kopa_ar_pvn = *1.22)
-- ============================================================
INSERT INTO `akts_pozicijas` (`akts_id`, `darba_veids_id`, `daudzums`, `kopa_bez_pvn`)
SELECT 1, id,   3.000,    4.50 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='1.11') UNION ALL
SELECT 1, id,   1.000,    5.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='1.25') UNION ALL
SELECT 1, id,   5.500,  110.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='1.26') UNION ALL
SELECT 1, id,   1.000,   20.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='1.28') UNION ALL
SELECT 1, id,   5.000,  100.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='2.25') UNION ALL
SELECT 1, id,   7.000,   30.80 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='2.27') UNION ALL
SELECT 1, id,   2.000,   10.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='2.4')  UNION ALL
SELECT 1, id,   1.000,   20.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='2.43') UNION ALL
SELECT 1, id,   3.000,   30.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='3.17') UNION ALL
SELECT 1, id,   1.000,   10.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='4.4')  UNION ALL
SELECT 1, id,  50.000,  500.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='5.1')  UNION ALL
SELECT 1, id, 620.000,  620.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='5.2')  UNION ALL
SELECT 1, id,   1.000, 2495.00 FROM darbu_veidi WHERE darba_nr_id=(SELECT id FROM darba_nri WHERE nr='5.3');

SET FOREIGN_KEY_CHECKS = 1;