---------------------VIEWS-------------------------------------------------------------------------------


---- Listanje na site zapisnici so osnovnite koloni
CREATE OR REPLACE VIEW v_site_zapisnici AS
SELECT
    id_na_zapisnik,
    datum,
    vreme,
    lokacija,
    Potpis,
    id_slucaj,
    EMBG_Prekrsuvach,
    Vozilo_Broj_Sasija,
    EMBG_Policaec
FROM Zapisnik;



-- Listanje na zapisnici so site detali: prekrsitel, vozilo, policaec,
-- tip prekrsok, iznos na kazna i status na uplata.

CREATE OR REPLACE VIEW v_zapisnici_detalno AS
SELECT
    z.id_na_zapisnik,z.datum,z.vreme,z.lokacija,z.id_slucaj,
    g.EMBG AS embg_prekrsitel,g.ime AS prekrsitel_ime,g.prezime AS prekrsitel_prezime,
    v.broj_na_sasija, v.model AS vozilo_model,pol.EMBG_P AS embg_policaec,
    g_p.ime AS policaec_ime,g_p.prezime AS policaec_prezime,
    p.ime AS prekrsok,
    k.iznos_kazna,
    u.iznos AS iznos_uplata,u.status AS status_uplata
FROM Zapisnik z
LEFT JOIN Gragjanin g ON z.EMBG_Prekrsuvach = g.EMBG
LEFT JOIN Vozilo v ON z.Vozilo_Broj_Sasija = v.broj_na_sasija
LEFT JOIN Policaec pol ON z.EMBG_Policaec = pol.EMBG_P
LEFT JOIN Gragjanin g_p ON pol.EMBG_P = g_p.EMBG
LEFT JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
LEFT JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
LEFT JOIN Kazna k ON p.id_kazna = k.id_kazna
LEFT JOIN Uplata u ON z.id_na_zapisnik = u.id_zapisnik;



-- Site neplateni kazni so podatoci za prekrsitelot, prekrsokot, iznosot
-- i kolku denovi se vekje neplateni.

CREATE OR REPLACE VIEW v_neplateni_kazni AS
SELECT
    z.id_na_zapisnik,z.datum AS datum_prekrsok,z.lokacija,
    g.EMBG AS embg_prekrsitel,g.ime AS ime_prekrsitel, g.prezime AS prezime_prekrsitel,
    p.ime AS prekrsok,
    k.iznos_kazna,
    u.iznos AS iznos_za_naplata,
    (CURRENT_DATE - z.datum) AS denovi_neplateno
FROM Uplata u
JOIN Zapisnik z ON u.id_zapisnik = z.id_na_zapisnik
JOIN Gragjanin g ON z.EMBG_Prekrsuvach = g.EMBG
JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
JOIN Kazna k ON p.id_kazna = k.id_kazna
WHERE u.status = 'Neplateno';




-- Pokazuva koi gragjani povtoruvaat eden ist tip prekrsok (COUNT > 1).
-- Vrakja kolku pati, prv i posleden pat, i vkupen iznos kazni za toj prekrsok.
CREATE OR REPLACE VIEW v_povtoreni_prekrsoci AS
WITH Prekrsoci_Stats AS (
    SELECT
        z.EMBG_Prekrsuvach,
        sz.id_na_prekrsok,
        COUNT(*) AS broj_povtori,
        MIN(z.datum) AS prv_pat,
        MAX(z.datum) AS posleden_pat,
        SUM(k.iznos_kazna) AS vkupen_iznos_kazni
    FROM Zapisnik z
    JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
    JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
    JOIN Kazna k ON p.id_kazna = k.id_kazna
    GROUP BY z.EMBG_Prekrsuvach, sz.id_na_prekrsok
    HAVING COUNT(*) > 1
)
SELECT
    g.EMBG,g.ime,g.prezime,
    p.id_prekrsok,p.ime AS prekrsok,
    ps.broj_povtori,ps.prv_pat,ps.posleden_pat,ps.vkupen_iznos_kazni
FROM Prekrsoci_Stats ps
JOIN Gragjanin g ON ps.EMBG_Prekrsuvach = g.EMBG
JOIN Prekrsok p  ON ps.id_na_prekrsok = p.id_prekrsok;

select * from v_povtoreni_prekrsoci vpp 

-- Prosek na izdadeni zapisnici po policaec: vkupno zapisnici, prv i posleden,
-- aktivni meseci i prosechen broj zapisnici po mesec aktivnost.
CREATE OR REPLACE VIEW v_policajci_prosek_zapisnici AS
WITH Policaec_Stats AS (
    SELECT
        EMBG_Policaec,
        COUNT(id_na_zapisnik) AS vkupno_zapisnici,
        MIN(datum) AS prv_zapisnik,
        MAX(datum) AS posleden_zapisnik
    FROM Zapisnik
    GROUP BY EMBG_Policaec
)
SELECT
    pol.EMBG_P,pol.broj_na_znacka,
    g.ime AS policaec_ime,g.prezime AS policaec_prezime,
    ps.vkupno_zapisnici,ps.prv_zapisnik,ps.posleden_zapisnik,
    GREATEST(DATE_PART('year', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)) * 12 +
             DATE_PART('month', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)), 1) AS aktivni_meseci,
    (ps.vkupno_zapisnici::numeric /
        GREATEST(DATE_PART('year', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)) * 12 +
            DATE_PART('month', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)),
            1)::numeric) AS prosek_zapisnici_po_mesec
FROM Policaec_Stats ps
JOIN Policaec pol ON ps.EMBG_Policaec = pol.EMBG_P
JOIN Gragjanin g  ON pol.EMBG_P = g.EMBG;


select *
from v_policaici_prosek_zapisnici vppz 



-- Istorija/profil za sekoj gragjanin: vkupno prekrshoci, vkupno platenо,
-- vkupen dolg, prv i posleden prekrshok, broj na razlichni tipovi prekrshoci.
CREATE OR REPLACE VIEW v_istorija_gragjanin AS
WITH Zapisnik_Stats AS (
    SELECT
        EMBG_Prekrsuvach,
        COUNT(DISTINCT z.id_na_zapisnik) AS vkupno_zapisnici,
        MIN(z.datum) AS prv_prekrsok,
        MAX(z.datum) AS posleden_prekrsok,
        COUNT(sz.id_stavka) AS vkupno_prekrshoci,
        COUNT(DISTINCT sz.id_na_prekrsok) AS razlichni_tipovi,
        COALESCE(SUM(k.iznos_kazna), 0) AS vkupen_iznos_kazni
    FROM Zapisnik z
    LEFT JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
    LEFT JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
    LEFT JOIN Kazna k ON p.id_kazna = k.id_kazna
    GROUP BY EMBG_Prekrsuvach
),
Uplata_Stats AS (
    SELECT
        z.EMBG_Prekrsuvach,
        COALESCE(SUM(CASE WHEN u.status = 'Plateno' THEN u.iznos END), 0) AS vkupno_plateno,
        COALESCE(SUM(CASE WHEN u.status = 'Neplateno' THEN u.iznos END), 0) AS vkupen_dolg,
        COUNT(CASE WHEN u.status = 'Neplateno' THEN u.id_uplata END) AS broj_neplateni
    FROM Uplata u
    JOIN Zapisnik z ON u.id_zapisnik = z.id_na_zapisnik
    GROUP BY z.EMBG_Prekrsuvach
)
SELECT
    g.EMBG,g.ime,g.prezime,g.datum_ragjanje,
    zs.vkupno_zapisnici,zs.vkupno_prekrshoci,zs.razlichni_tipovi,zs.vkupen_iznos_kazni,
    COALESCE(us.vkupno_plateno, 0) AS vkupno_plateno,
    COALESCE(us.vkupen_dolg, 0) AS vkupen_dolg,
    COALESCE(us.broj_neplateni, 0) AS broj_neplateni,
    zs.prv_prekrsok,
    zs.posleden_prekrsok
FROM Gragjanin g
JOIN Zapisnik_Stats zs ON g.EMBG = zs.EMBG_Prekrsuvach
LEFT JOIN Uplata_Stats us ON g.EMBG = us.EMBG_Prekrsuvach;




select * from v_istorija_gragjanin vig 


------------------------INDEKSI---------------------------------------------------

--za v_site_zapisnici

select * 
from  v_site_zapisnici vsz 
where vsz.embg_prekrsuvach='1508004480145'

--insert 

INSERT INTO Zapisnik 
(vreme, datum, lokacija, Potpis, id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec)
VALUES
('16:40:00', '2025-07-11', 'Bulevar Partizanski Odredi', true, 1, '1508004480145', 'CB6BC94C191376CC6', '1101960480659');
--update
 UPDATE Zapisnik
SET 
  
    Potpis = false,
    vreme = '17:15:00'
WHERE id_na_zapisnik = 30000006;

--posle indeks za v_site_zapisnici

CREATE INDEX idx_zapisnik_prekrsuvach ON Zapisnik(EMBG_Prekrsuvach);
--insert posle indeks
INSERT INTO Zapisnik 
(vreme, datum, lokacija, Potpis, id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec)
VALUES
('16:40:00', '2025-07-11', 'Bulevar Partizanski Odredi', true, 1, '1508004480145', 'CB6BC94C191376CC6', '1101960480659');

--update posle indeks
 UPDATE Zapisnik
SET 
  
    Potpis = false,
    vreme = '18:15:00'
WHERE id_na_zapisnik = 30000011;

--za v_zapisnici_detalno

SELECT * FROM v_zapisnici_detalno 
WHERE embg_prekrsitel = '0412992470302';

--insert

INSERT INTO Stavka_Zapisnik
(reden_broj, id_na_zapisnik, id_na_prekrsok)
VALUES
(1, 30000006, 1);

select * from stavka_zapisnik sz 
where sz.id_na_zapisnik =30000006;

--update
UPDATE Stavka_Zapisnik
SET
    id_na_prekrsok = 2
WHERE id_na_zapisnik = 30000006 and id_stavka =32910317;

--insert posle indeks
INSERT INTO Stavka_Zapisnik
(reden_broj, id_na_zapisnik, id_na_prekrsok)
VALUES
(1, 30000011, 1);

select * from stavka_zapisnik sz 
where sz.id_na_zapisnik =30000011;

--update posle indeks
UPDATE Stavka_Zapisnik
SET
    id_na_prekrsok = 2
WHERE id_na_zapisnik = 30000011 and id_stavka =32910318;


-- za v_neplateni_kazni
select *
from v_neplateni_kazni vnk 
where vnk.embg_prekrsitel ='0412992470302';


--insert
INSERT INTO Uplata
(iznos, status, datum_uplata, Uplatil_Gragjanin, id_zapisnik, nacin_plakanje)
VALUES
(50, 'Neplateno', NULL, '0412992470302', 30000006, NULL);

--update
UPDATE Uplata
SET
    status = 'Plateno',
    datum_uplata = CURRENT_DATE,
    nacin_plakanje = 'Platezna karticka'
WHERE id_zapisnik = 30000006;

--posle indeks


CREATE INDEX idx_stavka_zapisnik_id_zapisnik ON stavka_zapisnik(id_na_zapisnik);


CREATE INDEX idx_uplata_id_zapisnik ON uplata(id_zapisnik);

--insert posle indeks
INSERT INTO Uplata
(iznos, status, datum_uplata, Uplatil_Gragjanin, id_zapisnik, nacin_plakanje)
VALUES
(50, 'Neplateno', NULL, '0412992470302', 30000006, NULL);

-- update posle indeks
UPDATE Uplata
SET
    status = 'Plateno',
    datum_uplata = CURRENT_DATE,
    nacin_plakanje = 'Platezna karticka'
WHERE id_zapisnik = 30000006;

select *
from uplata u 
where u.id_zapisnik =30000006

-- za v_policaici_prosek_zapisnici
select *
from v_policaici_prosek_zapisnici vppz 
where vppz.embg_p ='3007982480021'

--za v_povtoreni_prekrsoci

select *
from v_povtoreni_prekrsoci vpp   
where vpp.embg ='0412992470302'


--za v_istorija_gragjanin
select *
from v_istorija_gragjanin vig    
where vig.embg ='0412992470302'


-- za v_policaici_prosek_zapisnici
select *
from v_policaici_prosek_zapisnici vppz 
where vppz.policaec_ime ='Никола'



--za v_povtoreni_prekrsoci

select *
from v_povtoreni_prekrsoci vpp   
where vpp.ime ='Виолета'


--za v_istorija_gragjanin
select *
from v_istorija_gragjanin vig    
where vig.ime='Оливера'



----materialized
 --view #4
CREATE MATERIALIZED VIEW v_povtoreni_prekrsoci_mv AS
WITH Prekrsoci_Stats AS (
    SELECT
        z.EMBG_Prekrsuvach,
        sz.id_na_prekrsok,
        COUNT(*) AS broj_povtori,
        MIN(z.datum) AS prv_pat,
        MAX(z.datum) AS posleden_pat,
        SUM(k.iznos_kazna) AS vkupen_iznos_kazni
    FROM Zapisnik z
    JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
    JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
    JOIN Kazna k ON p.id_kazna = k.id_kazna
    GROUP BY z.EMBG_Prekrsuvach, sz.id_na_prekrsok
    HAVING COUNT(*) > 1
)
SELECT
    g.EMBG,g.ime,g.prezime,
    p.id_prekrsok,p.ime AS prekrsok,
    ps.broj_povtori,ps.prv_pat,ps.posleden_pat,ps.vkupen_iznos_kazni
FROM Prekrsoci_Stats ps
JOIN Gragjanin g ON ps.EMBG_Prekrsuvach = g.EMBG
JOIN Prekrsok p  ON ps.id_na_prekrsok = p.id_prekrsok;

select *
from v_povtoreni_prekrsoci_mv vppm  
where vppm.embg ='0412992470302'


-- view #5
CREATE MATERIALIZED VIEW v_policaici_prosek_zapisnici_mv AS
WITH Policaec_Stats AS (
    SELECT
        EMBG_Policaec,
        COUNT(id_na_zapisnik) AS vkupno_zapisnici,
        MIN(datum) AS prv_zapisnik,
        MAX(datum) AS posleden_zapisnik
    FROM Zapisnik
    GROUP BY EMBG_Policaec
)
SELECT
    pol.EMBG_P,pol.broj_na_znacka,
    g.ime AS policaec_ime,g.prezime AS policaec_prezime,
    ps.vkupno_zapisnici,ps.prv_zapisnik,ps.posleden_zapisnik,
    GREATEST(DATE_PART('year', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)) * 12 +
             DATE_PART('month', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)), 1) AS aktivni_meseci,
    (ps.vkupno_zapisnici::numeric /
        GREATEST(DATE_PART('year', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)) * 12 +
            DATE_PART('month', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)),
            1)::numeric) AS prosek_zapisnici_po_mesec
FROM Policaec_Stats ps
JOIN Policaec pol ON ps.EMBG_Policaec = pol.EMBG_P
JOIN Gragjanin g  ON pol.EMBG_P = g.EMBG;


select *
from v_policaici_prosek_zapisnici_mv vppzm    
where vppzm.broj_na_znacka ='ZN-S-0006'


-- view #6
CREATE MATERIALIZED VIEW v_istorija_gragjanin_mv AS
WITH Zapisnik_Stats AS (
    SELECT
        EMBG_Prekrsuvach,
        COUNT(DISTINCT z.id_na_zapisnik) AS vkupno_zapisnici,MIN(z.datum) AS prv_prekrsok,MAX(z.datum) AS posleden_prekrsok,
        COUNT(sz.id_stavka) AS vkupno_prekrshoci,COUNT(DISTINCT sz.id_na_prekrsok) AS razlichni_tipovi,
        COALESCE(SUM(k.iznos_kazna), 0) AS vkupen_iznos_kazni
    FROM Zapisnik z
    LEFT JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
    LEFT JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
    LEFT JOIN Kazna k ON p.id_kazna = k.id_kazna
    GROUP BY EMBG_Prekrsuvach
),
Uplata_Stats AS (
    SELECT
        z.EMBG_Prekrsuvach,
        COALESCE(SUM(CASE WHEN u.status = 'Plateno' THEN u.iznos END), 0) AS vkupno_plateno,
        COALESCE(SUM(CASE WHEN u.status = 'Neplateno' THEN u.iznos END), 0) AS vkupen_dolg,
        COUNT(CASE WHEN u.status = 'Neplateno' THEN u.id_uplata END) AS broj_neplateni
    FROM Uplata u
    JOIN Zapisnik z ON u.id_zapisnik = z.id_na_zapisnik
    GROUP BY z.EMBG_Prekrsuvach
)
SELECT
    g.EMBG,g.ime,g.prezime,g.datum_ragjanje,
    zs.vkupno_zapisnici,zs.vkupno_prekrshoci,zs.razlichni_tipovi,zs.vkupen_iznos_kazni,
    COALESCE(us.vkupno_plateno, 0) AS vkupno_plateno,
    COALESCE(us.vkupen_dolg, 0) AS vkupen_dolg,
    COALESCE(us.broj_neplateni, 0) AS broj_neplateni,
    zs.prv_prekrsok,zs.posleden_prekrsok
FROM Gragjanin g
JOIN Zapisnik_Stats zs ON g.EMBG = zs.EMBG_Prekrsuvach
LEFT JOIN Uplata_Stats us ON g.EMBG = us.EMBG_Prekrsuvach;

select *
from v_istorija_gragjanin_mv vig   
where vig.prezime='Петровски'


-----------refresh
REFRESH MATERIALIZED VIEW v_povtoreni_prekrsoci_mv;
REFRESH MATERIALIZED VIEW v_policaici_prosek_zapisnici_mv;
REFRESH MATERIALIZED VIEW v_istorija_gragjanin_mv;