-- ============================================================
-- Datu bāze: nin_db
-- Nekustamā īpašuma nodokļa (NĪN) maksājumu uzskaites sistēma
-- Avots: NĪN maksājuma grafiks (MS Excel, lapa "03")
-- Eksportēts: tabulas, relācijas, indeksi, dati, pieprasījums (view), funkcija
-- ============================================================

DROP DATABASE IF EXISTS nin_db;
CREATE DATABASE nin_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE nin_db;

-- ------------------------------------------------------------
-- Tabula: MAKSATAJI
-- Nodokļa maksātāji (īpašumu īpašnieki)
-- ------------------------------------------------------------
CREATE TABLE maksataji (
  maksatajs_id  INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Maksātāja unikālais identifikators',
  vards         VARCHAR(50)  NOT NULL COMMENT 'Maksātāja vārds',
  uzvards       VARCHAR(50)  NOT NULL COMMENT 'Maksātāja uzvārds',
  PRIMARY KEY (maksatajs_id)
) ENGINE=InnoDB
  COMMENT='Nodokļa maksātāji';

-- ------------------------------------------------------------
-- Tabula: IPASUMI
-- Nekustamie īpašumi, par kuriem tiek maksāts NĪN
-- ------------------------------------------------------------
CREATE TABLE ipasumi (
  ipasums_id    INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Īpašuma unikālais identifikators',
  nosaukums     VARCHAR(100) NOT NULL COMMENT 'Īpašuma nosaukums/adrese',
  kadastra_nr   VARCHAR(20)  NOT NULL COMMENT 'Kadastra numurs',
  maksatajs_id  INT UNSIGNED NOT NULL COMMENT 'Atsauce uz īpašuma maksātāju',
  PRIMARY KEY (ipasums_id),
  UNIQUE KEY uq_kadastra_nr (kadastra_nr),
  KEY idx_ipasumi_maksatajs (maksatajs_id),
  CONSTRAINT fk_ipasumi_maksataji
    FOREIGN KEY (maksatajs_id) REFERENCES maksataji (maksatajs_id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB
  COMMENT='Nekustamie īpašumi';

-- ------------------------------------------------------------
-- Tabula: NIN_TIPI
-- Uzziņas tabula — NĪN veidi (klasifikators)
-- ------------------------------------------------------------
CREATE TABLE nin_tipi (
  tips_id    INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'NĪN tipa identifikators',
  nosaukums  VARCHAR(50)  NOT NULL COMMENT 'NĪN tipa nosaukums, piem. "NĪN par zemi"',
  PRIMARY KEY (tips_id),
  UNIQUE KEY uq_nin_tips_nosaukums (nosaukums)
) ENGINE=InnoDB
  COMMENT='NĪN tipu uzziņas (klasifikatora) tabula';

-- ------------------------------------------------------------
-- Tabula: NODOKLU_KONTI
-- Nodokļa konti — viens konts par viena tipa nodokli vienam īpašumam
-- ------------------------------------------------------------
CREATE TABLE nodoklu_konti (
  konta_nr          INT UNSIGNED NOT NULL COMMENT 'Nodokļa konta numurs (dabīgā atslēga no avota sistēmas)',
  ipasums_id        INT UNSIGNED NOT NULL COMMENT 'Atsauce uz īpašumu',
  tips_id           INT UNSIGNED NOT NULL COMMENT 'Atsauce uz NĪN tipu',
  pamatparads       DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Pamatparāda summa',
  nokavejuma_nauda  DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Nokavējuma naudas summa',
  kopsumma          DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Kopējā maksājamā summa',
  valuta            CHAR(3)       NOT NULL DEFAULT 'EUR' COMMENT 'Valūtas kods (ISO 4217)',
  PRIMARY KEY (konta_nr),
  KEY idx_konti_ipasums (ipasums_id),
  KEY idx_konti_tips (tips_id),
  CONSTRAINT fk_konti_ipasumi
    FOREIGN KEY (ipasums_id) REFERENCES ipasumi (ipasums_id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_konti_nin_tipi
    FOREIGN KEY (tips_id) REFERENCES nin_tipi (tips_id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB
  COMMENT='Nodokļa konti';

-- ------------------------------------------------------------
-- Tabula: MAKSAJUMU_TERMINI
-- Katra konta maksājumu termiņi (1NF labojums — atkārtotā grupa izdalīta)
-- ------------------------------------------------------------
CREATE TABLE maksajumu_termini (
  termina_id  INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Maksājuma termiņa identifikators',
  konta_nr    INT UNSIGNED NOT NULL COMMENT 'Atsauce uz nodokļa kontu',
  datums      DATE          NOT NULL COMMENT 'Maksājuma termiņa datums',
  summa       DECIMAL(10,2) NOT NULL COMMENT 'Maksājamā summa šajā termiņā',
  PRIMARY KEY (termina_id),
  KEY idx_termini_konts (konta_nr),
  KEY idx_termini_datums (datums),
  CONSTRAINT fk_termini_konti
    FOREIGN KEY (konta_nr) REFERENCES nodoklu_konti (konta_nr)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB
  COMMENT='Nodokļa konta maksājumu termiņi';

-- ============================================================
-- DATI (atbilst sākotnējam Excel failam)
-- ============================================================

INSERT INTO maksataji (maksatajs_id, vards, uzvards) VALUES
  (1, 'xxxx', 'xxxxxxxxxxx');

INSERT INTO ipasumi (ipasums_id, nosaukums, kadastra_nr, maksatajs_id) VALUES
  (1, 'Alkšņi- 9',  '4076 900 0077', 1),
  (2, 'Urani',      '4088 001 0014', 1),
  (3, 'Protoni',    '4088 003 0171', 1),
  (4, 'Garāža 16',  '4076 003 0376', 1);

INSERT INTO nin_tipi (tips_id, nosaukums) VALUES
  (1, 'NĪN par zemi'),
  (2, 'NĪN par ēkām');

INSERT INTO nodoklu_konti (konta_nr, ipasums_id, tips_id, pamatparads, nokavejuma_nauda, kopsumma, valuta) VALUES
  (595194, 1, 1,  0.00,  0.00,   1.64, 'EUR'),
  (595197, 1, 2,  0.00,  0.00,  21.98, 'EUR'),
  (175196, 2, 1, 98.99, 12.58, 210.56, 'EUR'),
  (185855, 2, 2, 41.97,  5.33,  89.27, 'EUR'),
  (227348, 3, 1,  0.00,  0.00, 192.75, 'EUR'),
  (230112, 4, 1,  0.00,  0.00,   0.78, 'EUR'),
  (230114, 4, 2,  0.00,  0.00,   0.76, 'EUR');

INSERT INTO maksajumu_termini (konta_nr, datums, summa) VALUES
  (595194, '2024-04-02', 0.41), (595194, '2024-05-15', 0.41), (595194, '2024-08-15', 0.41), (595194, '2024-11-15', 0.41),
  (595197, '2024-04-02', 5.50), (595197, '2024-05-15', 5.50), (595197, '2024-08-15', 5.49), (595197, '2024-11-15', 5.49),
  (175196, '2024-04-02', 24.75), (175196, '2024-05-15', 24.75), (175196, '2024-08-15', 24.75), (175196, '2024-11-15', 24.74),
  (185855, '2024-04-02', 10.50), (185855, '2024-05-15', 10.49), (185855, '2024-08-15', 10.49), (185855, '2024-11-15', 10.49),
  (227348, '2024-04-02', 48.19), (227348, '2024-05-15', 48.19), (227348, '2024-08-15', 48.19), (227348, '2024-11-15', 48.18),
  (230112, '2024-04-02', 0.20), (230112, '2024-05-15', 0.20), (230112, '2024-08-15', 0.19), (230112, '2024-11-15', 0.19),
  (230114, '2024-04-02', 0.19), (230114, '2024-05-15', 0.19), (230114, '2024-08-15', 0.19), (230114, '2024-11-15', 0.19);

-- ============================================================
-- PIEPRASĪJUMS (VIEW) — atspoguļo sākotnējos (avota) datus
-- ============================================================

CREATE VIEW v_nin_maksajumi AS
-- Apvieno visas tabulas, lai atjaunotu sākotnējā Excel faila izklāstu:
-- maksātājs -> īpašums -> nodokļa konts (ar tipu) -> maksājuma termiņš
SELECT
  nk.konta_nr,
  i.nosaukums          AS ipasums,
  i.kadastra_nr,
  nk.pamatparads,
  nk.nokavejuma_nauda,
  nk.kopsumma,
  nk.valuta,
  nt.nosaukums          AS nin_tips,
  mt.datums             AS termina_datums,
  mt.summa              AS termina_summa,
  CONCAT(m.vards, ' ', m.uzvards) AS maksatajs
FROM nodoklu_konti nk
JOIN ipasumi i           ON i.ipasums_id  = nk.ipasums_id
JOIN maksataji m         ON m.maksatajs_id = i.maksatajs_id
JOIN nin_tipi nt         ON nt.tips_id    = nk.tips_id
JOIN maksajumu_termini mt ON mt.konta_nr   = nk.konta_nr
ORDER BY nk.konta_nr, mt.datums;

-- ============================================================
-- FUNKCIJA — konta kopsummas pārrēķins un validācija
-- Biznesa likums (atklāts, salīdzinot ar avota datiem):
--   kopsumma = pamatparads + nokavejuma_nauda + SUM(maksajumu_termini.summa)
-- ============================================================

DELIMITER //

CREATE FUNCTION fn_konta_kopsumma(p_konta_nr INT UNSIGNED)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
COMMENT 'Pārrēķina konta kopsummu: pamatparāds + nokavējuma nauda + visu maksājumu termiņu summa'
BEGIN
  DECLARE v_termini    DECIMAL(10,2);
  DECLARE v_pamats     DECIMAL(10,2);
  DECLARE v_nokavejuma DECIMAL(10,2);

  SELECT COALESCE(SUM(summa), 0.00) INTO v_termini
  FROM maksajumu_termini
  WHERE konta_nr = p_konta_nr;

  SELECT pamatparads, nokavejuma_nauda INTO v_pamats, v_nokavejuma
  FROM nodoklu_konti
  WHERE konta_nr = p_konta_nr;

  RETURN v_pamats + v_nokavejuma + v_termini;
END //

DELIMITER ;

-- Validācijas piemērs — pārrēķinātajai un saglabātajai kopsummai jāsakrīt visās rindās:
-- SELECT konta_nr, fn_konta_kopsumma(konta_nr) AS pareizinata_kopsumma, kopsumma,
--        (fn_konta_kopsumma(konta_nr) = kopsumma) AS sakrit
-- FROM nodoklu_konti;
