DatabaseCreation: views_.sql

CREATE OR REPLACE VIEW vw_neplateni_kazni_posledni_2_nedeli AS
SELECT
    k.kazna_id,
    k.datum,
    k.rok_na_plakanje,
    k.iznos_za_plakanje,

    p.prekrsok_id,
    p.opis,

    g.ime,
    g.prezime,

    v.registarska_oznaka,

    CASE
        WHEN k.kazna_id IS NULL THEN 'Nema kazna'
        ELSE 'Postoi kazna'
    END AS proverka_kazna,

    CASE
        WHEN k.datum IS NULL THEN 'Kaznata nema datum'
        ELSE 'Kaznata ima datum'
    END AS proverka_datum_kazna,

    CASE
        WHEN k.datum >= CURRENT_DATE - INTERVAL '14 days' THEN 'Kaznata e vo posledni 2 nedeli'
        ELSE 'Kaznata ne e vo posledni 2 nedeli'
    END AS proverka_period_kazna,

    CASE
        WHEN k.rok_na_plakanje IS NULL THEN 'Kaznata nema rok za plakanje'
        WHEN k.rok_na_plakanje < CURRENT_DATE THEN 'Rokot za plakanje e istecen'
        WHEN k.rok_na_plakanje = CURRENT_DATE THEN 'Rokot za plakanje istekuva denes'
        ELSE 'Rokot za plakanje ne e istecen'
    END AS proverka_rok_na_plakanje,

    CASE
        WHEN k.iznos_za_plakanje IS NULL THEN 'Nema iznos za plakanje'
        WHEN k.iznos_za_plakanje <= 0 THEN 'Nevaliden iznos za plakanje'
        ELSE 'Ima validen iznos za plakanje'
    END AS proverka_iznos_za_plakanje,

    CASE
        WHEN p.prekrsok_id IS NULL THEN 'Kaznata nema povrzan prekrsok'
        ELSE 'Kaznata ima povrzan prekrsok'
    END AS proverka_prekrsok,

    CASE
        WHEN p.opis IS NULL OR TRIM(p.opis) = '' THEN 'Nema opis za prekrsokot'
        ELSE 'Ima opis za prekrsokot'
    END AS proverka_opis_prekrsok,

    CASE
        WHEN ps.storitel_embg IS NULL THEN 'Prekrsokot nema povrzan storitel'
        ELSE 'Prekrsokot ima povrzan storitel'
    END AS proverka_storitel,

    CASE
        WHEN g.embg IS NULL THEN 'Nema podatoci za gragjaninot'
        ELSE 'Ima podatoci za gragjaninot'
    END AS proverka_gragjanin,

    CASE
        WHEN g.ime IS NULL OR TRIM(g.ime) = '' THEN 'Nedostasuva ime na gragjaninot'
        ELSE 'Ima ime na gragjaninot'
    END AS proverka_ime_gragjanin,

    CASE
        WHEN g.prezime IS NULL OR TRIM(g.prezime) = '' THEN 'Nedostasuva prezime na gragjaninot'
        ELSE 'Ima prezime na gragjaninot'
    END AS proverka_prezime_gragjanin,

    CASE
        WHEN pv.vozilo_id IS NULL THEN 'Prekrsokot nema povrzano vozilo'
        ELSE 'Prekrsokot ima povrzano vozilo'
    END AS proverka_prekrsok_vozilo,

    CASE
        WHEN v.vozilo_id IS NULL THEN 'Nema podatoci za voziloto'
        ELSE 'Ima podatoci za voziloto'
    END AS proverka_vozilo,

    CASE
        WHEN v.registarska_oznaka IS NULL OR TRIM(v.registarska_oznaka) = '' THEN 'Nema registarska oznaka'
        ELSE 'Ima registarska oznaka'
    END AS proverka_registarska_oznaka,

    CASE
        WHEN pl.kazna_id IS NULL THEN 'Kaznata ne e platena'
        ELSE 'Kaznata e platena'
    END AS proverka_plakanje

FROM Kazna k
         JOIN Prekrsok p ON p.kazna_id = k.kazna_id
         LEFT JOIN Prekrsok_Storitel ps ON p.prekrsok_id = ps.prekrsok_id
         LEFT JOIN Gragjanin g ON ps.storitel_embg = g.embg
         LEFT JOIN Prekrsok_Vozilo pv ON p.prekrsok_id = pv.prekrsok_id
         LEFT JOIN Vozilo v ON pv.vozilo_id = v.vozilo_id
         LEFT JOIN Plakanje pl ON pl.kazna_id = k.kazna_id
WHERE k.datum >= CURRENT_DATE - INTERVAL '14 days'
  AND pl.kazna_id IS NULL;



CREATE OR REPLACE VIEW vw_prekrsoci_denes AS
SELECT
    p.prekrsok_id,
    p.datum,
    p.vreme,
    p.opis,

    z.ime AS zona,
    z.dozvolena_brzina,

    v.registarska_oznaka,

    g.ime AS ime_gragjanin,
    g.prezime AS prezime_gragjanin,

    CASE
        WHEN p.prekrsok_id IS NULL THEN 'Nema prekrsok'
        ELSE 'Postoi prekrsok'
    END AS proverka_prekrsok,

    CASE
        WHEN p.opis IS NULL OR TRIM(p.opis) = '' THEN 'Nema opis za prekrsokot'
        ELSE 'Ima opis za prekrsokot'
    END AS proverka_opis,

    CASE
        WHEN p.datum IS NULL THEN 'Nema datum za prekrsokot'
        ELSE 'Ima datum za prekrsokot'
    END AS proverka_datum,

    CASE
        WHEN p.vreme IS NULL THEN 'Nema vreme za prekrsokot'
        ELSE 'Ima vreme za prekrsokot'
    END AS proverka_vreme,

    CASE
        WHEN k.kamera_id IS NULL THEN 'Prekrsokot nema povrzana kamera'
        ELSE 'Prekrsokot ima povrzana kamera'
    END AS proverka_kamera,

    CASE
        WHEN l.lokacija_id IS NULL THEN 'Kamerata ne lokacija'
        ELSE 'Kamerata ima povrzana lokacija'
    END AS proverka_lokacija,

    CASE
        WHEN z.zona_id IS NULL THEN 'Lokacijata nema ma povrzanapovrzana zona'
        ELSE 'Lokacijata ima povrzana zona'
    END AS proverka_zona,

    CASE
        WHEN z.dozvolena_brzina IS NULL THEN 'Nema podatok za dozvolena brzina'
        ELSE 'Ima podatok za dozvolena brzina'
    END AS proverka_dozvolena_brzina,

    CASE
        WHEN pv.vozilo_id IS NULL THEN 'Prekrsokot nema povrzano vozilo'
        ELSE 'Prekrsokot ima povrzano vozilo'
    END AS proverka_prekrsok_vozilo,

    CASE
        WHEN v.vozilo_id IS NULL THEN 'Nema podatoci za voziloto'
        ELSE 'Ima podatoci za voziloto'
    END AS proverka_vozilo,

    CASE
        WHEN v.registarska_oznaka IS NULL OR TRIM(v.registarska_oznaka) = '' THEN 'Nema registarska oznaka'
        ELSE 'Ima registarska oznaka'
    END AS proverka_registarska_oznaka,

    CASE
        WHEN ps.storitel_embg IS NULL THEN 'Prekrsokot nema povrzan storitel'
        ELSE 'Prekrsokot ima povrzan storitel'
    END AS proverka_prekrsok_storitel,

    CASE
        WHEN g.embg IS NULL THEN 'Nema podatoci za gragjaninot'
        ELSE 'Ima podatoci za gragjaninot'
    END AS proverka_gragjanin,

    CASE
        WHEN g.ime IS NULL OR g.prezime IS NULL THEN 'Nedostasuva ime ili prezime na gragjaninot'
        ELSE 'Gragjaninot ima celosni podatoci'
    END AS proverka_podatoci_gragjanin

FROM Prekrsok p
LEFT JOIN Kamera k ON p.kamera_id = k.kamera_id
LEFT JOIN Lokacija l ON l.lokacija_id = k.lokacija_id
LEFT JOIN Zona z ON z.zona_id = l.zona_id
LEFT JOIN Prekrsok_Vozilo pv ON pv.prekrsok_id = p.prekrsok_id
LEFT JOIN Vozilo v ON v.vozilo_id = pv.vozilo_id
LEFT JOIN Prekrsok_Storitel ps ON ps.prekrsok_id = p.prekrsok_id
LEFT JOIN Gragjanin g ON g.embg = ps.storitel_embg
WHERE p.datum = (
    SELECT MAX(datum)
    FROM Prekrsok
);


CREATE OR REPLACE VIEW vw_sopstvenici_na_vozila AS
SELECT
    v.vozilo_id,
    v.registarska_oznaka,
    v.marka,
    v.model,

    g.ime,
    g.prezime,
    g.telefonski_broj,

    CASE
        WHEN v.vozilo_id IS NULL THEN 'Nema vozilo'
        ELSE 'Postoi vozilo'
    END AS proverka_vozilo,

    CASE
        WHEN v.registarska_oznaka IS NULL OR TRIM(v.registarska_oznaka) = '' THEN 'Nema registarska oznaka'
        ELSE 'Ima registarska oznaka'
    END AS proverka_registarska_oznaka,

    CASE
        WHEN v.marka IS NULL OR TRIM(v.marka) = '' THEN 'Nema marka na vozilo'
        ELSE 'Ima marka na vozilo'
    END AS proverka_marka,

    CASE
        WHEN v.model IS NULL OR TRIM(v.model) = '' THEN 'Nema model na vozilo'
        ELSE 'Ima model na vozilo'
    END AS proverka_model,

    CASE
        WHEN sv.vozilo_id IS NULL THEN 'Voziloto nema sopstvenik'
        ELSE 'Voziloto ima sopstvenik'
    END AS proverka_sopstvenost,

    CASE
        WHEN sv.embg IS NULL OR TRIM(sv.embg) = '' THEN 'Nema EMBG za sopstvenik'
        ELSE 'Ima EMBG za sopstvenik'
    END AS proverka_embg_sopstvenik,

    CASE
        WHEN g.embg IS NULL THEN 'Nema podatoci za gragjaninot'
        ELSE 'Ima podatoci za gragjaninot'
    END AS proverka_gragjanin,

    CASE
        WHEN g.ime IS NULL OR TRIM(g.ime) = '' THEN 'Nedostasuva ime na sopstvenikot'
        ELSE 'Ima ime na sopstvenikot'
    END AS proverka_ime_sopstvenik,

    CASE
        WHEN g.prezime IS NULL OR TRIM(g.prezime) = '' THEN 'Nedostasuva prezime na sopstvenikot'
        ELSE 'Ima prezime na sopstvenikot'
    END AS proverka_prezime_sopstvenik,

    CASE
        WHEN g.telefonski_broj IS NULL OR TRIM(g.telefonski_broj) = '' THEN 'Nema telefonski broj'
        ELSE 'Ima telefonski broj'
    END AS proverka_telefon,

    CASE
        WHEN v.registarska_oznaka IS NOT NULL
             AND v.marka IS NOT NULL
             AND v.model IS NOT NULL
             AND g.ime IS NOT NULL
             AND g.prezime IS NOT NULL
        THEN 'Zapisot e kompleten'
        ELSE 'Zapisot ne e kompleten'
    END AS proverka_celosen_zapis

FROM Vozilo v
         JOIN Sopstvenik_Vozilo sv ON v.vozilo_id = sv.vozilo_id
         JOIN Gragjanin g ON sv.embg = g.embg;

CREATE OR REPLACE VIEW vw_aktivni_zhalbi AS
SELECT
    z.zalba_id,
    z.datum_na_podnesuvanje,
    z.sodrzina,

    p.status AS status_prekrsok,

    g.ime,
    g.prezime,

    CASE
        WHEN z.zalba_id IS NULL THEN 'Nema zalba'
        ELSE 'Postoi zalba'
    END AS proverka_zalba,

    CASE
        WHEN z.datum_na_podnesuvanje IS NULL THEN 'Zalbata nema datum na podnesuvanje'
        ELSE 'Zalbata ima datum na podnesuvanje'
    END AS proverka_datum_podnesuvanje,

    CASE
        WHEN z.datum_na_podnesuvanje > CURRENT_DATE THEN 'Datumot na zalbata e vo idnina'
        WHEN z.datum_na_podnesuvanje = CURRENT_DATE THEN 'Zalbata e podnesena denes'
        WHEN z.datum_na_podnesuvanje < CURRENT_DATE THEN 'Zalbata e podnesena vo minatoto'
        ELSE 'Nema datum za proverka'
    END AS proverka_period_zalba,

    CASE
        WHEN z.sodrzina IS NULL OR TRIM(z.sodrzina) = '' THEN 'Zalbata nema sodrzina'
        ELSE 'Zalbata ima sodrzina'
    END AS proverka_sodrzina,

    CASE
        WHEN z.korisnik_id IS NULL THEN 'Zalbata nema povrzan korisnik'
        ELSE 'Zalbata ima povrzan korisnik'
    END AS proverka_korisnik_id,

    CASE
        WHEN k.korisnik_id IS NULL THEN 'Nema podatoci za korisnikot'
        ELSE 'Ima podatoci za korisnikot'
    END AS proverka_korisnik,

    CASE
        WHEN k.embg IS NULL OR TRIM(k.embg) = '' THEN 'Korisnikot nema EMBG'
        ELSE 'Korisnikot ima EMBG'
    END AS proverka_embg_korisnik,

    CASE
        WHEN g.embg IS NULL THEN 'Nema podatoci za gragjaninot'
        ELSE 'Ima podatoci za gragjaninot'
    END AS proverka_gragjanin,

    CASE
        WHEN g.ime IS NULL OR TRIM(g.ime) = '' THEN 'Nedostasuva ime na gragjaninot'
        ELSE 'Ima ime na gragjaninot'
    END AS proverka_ime_gragjanin,

    CASE
        WHEN g.prezime IS NULL OR TRIM(g.prezime) = '' THEN 'Nedostasuva prezime na gragjaninot'
        ELSE 'Ima prezime na gragjaninot'
    END AS proverka_prezime_gragjanin,

    CASE
        WHEN z.prekrsok_id IS NULL THEN 'Zalbata nema povrzan prekrsok'
        ELSE 'Zalbata ima povrzan prekrsok'
    END AS proverka_prekrsok_id,

    CASE
        WHEN p.prekrsok_id IS NULL THEN 'Nema podatoci za prekrsokot'
        ELSE 'Ima podatoci za prekrsokot'
    END AS proverka_prekrsok,

    CASE
        WHEN p.status IS NULL OR TRIM(p.status) = '' THEN 'Prekrsokot nema status'
        ELSE 'Prekrsokot ima status'
    END AS proverka_status_prekrsok,

    CASE
        WHEN LOWER(p.status) = 'aktiven' THEN 'Prekrsokot e aktiven'
        WHEN LOWER(p.status) = 'zavrshen' THEN 'Prekrsokot e zavrshen'
        WHEN LOWER(p.status) = 'otkazan' THEN 'Prekrsokot e otkazan'
        WHEN p.status IS NULL OR TRIM(p.status) = '' THEN 'Nepoznat status na prekrsok'
        ELSE 'Drug status na prekrsok'
    END AS opis_status_prekrsok,

    CASE
        WHEN z.datum_na_podnesuvanje IS NOT NULL
             AND z.sodrzina IS NOT NULL
             AND TRIM(z.sodrzina) <> ''
             AND k.korisnik_id IS NOT NULL
             AND g.embg IS NOT NULL
             AND p.prekrsok_id IS NOT NULL
        THEN 'Zapisot za zalbata e kompleten'
        ELSE 'Zapisot za zalbata ne e kompleten'
    END AS proverka_celosen_zapis

FROM Zalba z
         LEFT JOIN Korisnik k ON z.korisnik_id = k.korisnik_id
         LEFT JOIN Gragjanin g ON k.embg = g.embg
         LEFT JOIN Prekrsok p ON z.prekrsok_id = p.prekrsok_id;

CREATE VIEW vw_neaktivni_kameri AS
SELECT k.kamera_id,
       k.status,
       k.seriski_broj,
       l.ulica,
       z.ime AS zona
FROM Kamera k
         LEFT JOIN Lokacija l ON k.lokacija_id = l.lokacija_id
         LEFT JOIN Zona z ON l.zona_id = z.zona_id
WHERE k.status IN ('neaktivna', 'servis');



CREATE VIEW vw_prekrsoci_so_nadminata_brzina AS
SELECT p.prekrsok_id,
       p.detektirana_brzina,
       z.dozvolena_brzina,
       (p.detektirana_brzina - z.dozvolena_brzina) AS razlika,
       z.ime                                       AS zona
FROM Prekrsok p
         JOIN Kamera k ON p.kamera_id = k.kamera_id
         JOIN Lokacija l ON k.lokacija_id = l.lokacija_id
         JOIN Zona z ON l.zona_id = z.zona_id
WHERE p.detektirana_brzina > z.dozvolena_brzina;


CREATE OR REPLACE VIEW vw_istecheni_registracii AS
SELECT
    r.registracija_id,
    r.broj,
    r.mesto,
    r.datum,
    r.datum_istekuvanje,

    v.vozilo_id,
    v.registarska_oznaka,

    s.embg,

    CASE
        WHEN r.registracija_id IS NULL THEN 'Nema registracija'
        ELSE 'Postoi registracija'
    END AS proverka_registracija,

    CASE
        WHEN r.broj IS NULL OR TRIM(r.broj) = '' THEN 'Nema broj na registracija'
        ELSE 'Ima broj na registracija'
    END AS proverka_broj_registracija,

    CASE
        WHEN r.mesto IS NULL OR TRIM(r.mesto) = '' THEN 'Nema mesto na registracija'
        ELSE 'Ima mesto na registracija'
    END AS proverka_mesto_registracija,

    CASE
        WHEN r.datum IS NULL THEN 'Nema datum na registracija'
        ELSE 'Ima datum na registracija'
    END AS proverka_datum_registracija,

    CASE
        WHEN r.datum_istekuvanje IS NULL THEN 'Nema datum na istekuvanje'
        ELSE 'Ima datum na istekuvanje'
    END AS proverka_datum_istekuvanje,

    CASE
        WHEN r.datum_istekuvanje < CURRENT_DATE THEN 'Registracijata e istecena'
        WHEN r.datum_istekuvanje = CURRENT_DATE THEN 'Registracijata istekuva denes'
        WHEN r.datum_istekuvanje > CURRENT_DATE THEN 'Registracijata e validna'
        ELSE 'Nema podatok za proverka na registracija'
    END AS proverka_status_registracija,

    CASE
        WHEN r.datum IS NOT NULL
             AND r.datum_istekuvanje IS NOT NULL
             AND r.datum_istekuvanje < r.datum
        THEN 'Datumot na istekuvanje e pred datumot na registracija'
        WHEN r.datum IS NOT NULL
             AND r.datum_istekuvanje IS NOT NULL
             AND r.datum_istekuvanje >= r.datum
        THEN 'Datumite na registracijata se validni'
        ELSE 'Nema dovolno podatoci za proverka na datumi'
    END AS proverka_validnost_datumi,

    CASE
        WHEN v.vozilo_id IS NULL THEN 'Registracijata nema povrzano vozilo'
        ELSE 'Registracijata ima povrzano vozilo'
    END AS proverka_vozilo,

    CASE
        WHEN v.registarska_oznaka IS NULL OR TRIM(v.registarska_oznaka) = '' THEN 'Nema registarska oznaka'
        ELSE 'Ima registarska oznaka'
    END AS proverka_registarska_oznaka,

    CASE
        WHEN s.embg IS NULL OR TRIM(s.embg) = '' THEN 'Registracijata nema povrzan sopstvenik'
        ELSE 'Registracijata ima povrzan sopstvenik'
    END AS proverka_sopstvenik,

    CASE
        WHEN r.broj IS NOT NULL
             AND TRIM(r.broj) <> ''
             AND r.mesto IS NOT NULL
             AND TRIM(r.mesto) <> ''
             AND r.datum IS NOT NULL
             AND r.datum_istekuvanje IS NOT NULL
             AND v.vozilo_id IS NOT NULL
             AND v.registarska_oznaka IS NOT NULL
             AND TRIM(v.registarska_oznaka) <> ''
             AND s.embg IS NOT NULL
        THEN 'Zapisot za registracija e kompleten'
        ELSE 'Zapisot za registracija ne e kompleten'
    END AS proverka_celosen_zapis

FROM Registracija r
         JOIN Vozilo v ON r.Vozilovozilo_id = v.vozilo_id
         JOIN Sopstvenik s ON r.sopstvenikEmbg = s.embg
WHERE r.datum_istekuvanje < CURRENT_DATE;


CREATE OR REPLACE VIEW vw_lokacii_so_najmnogu_prekrsoci AS
SELECT
    l.lokacija_id,
    l.ulica,
    z.ime AS zona,
    COUNT(p.prekrsok_id) AS broj_prekrsoci,

    CASE
        WHEN l.lokacija_id IS NULL THEN 'Nema lokacija'
        ELSE 'Postoi lokacija'
    END AS proverka_lokacija,

    CASE
        WHEN l.ulica IS NULL OR TRIM(l.ulica) = '' THEN 'Nema podatok za ulica'
        ELSE 'Ima podatok za ulica'
    END AS proverka_ulica,

    CASE
        WHEN z.ime IS NULL OR TRIM(z.ime) = '' THEN 'Lokacijata nema povrzana zona'
        ELSE 'Lokacijata ima povrzana zona'
    END AS proverka_zona,

    CASE
        WHEN COUNT(k.kamera_id) = 0 THEN 'Lokacijata nema kameri'
        ELSE 'Lokacijata ima kameri'
    END AS proverka_kameri,

    CASE
        WHEN COUNT(p.prekrsok_id) = 0 THEN 'Nema prekrsoci na ovaa lokacija'
        WHEN COUNT(p.prekrsok_id) BETWEEN 1 AND 10 THEN 'Mal broj prekrsoci'
        WHEN COUNT(p.prekrsok_id) BETWEEN 11 AND 50 THEN 'Sreden broj prekrsoci'
        ELSE 'Golem broj prekrsoci'
    END AS kategorija_broj_prekrsoci,

    CASE
        WHEN COUNT(p.prekrsok_id) = 0 THEN 'Lokacijata ne e kriticna'
        WHEN COUNT(p.prekrsok_id) >= 50 THEN 'Lokacijata e kriticna'
        ELSE 'Lokacijata treba da se sledi'
    END AS proverka_rizik_lokacija,

    CASE
        WHEN l.ulica IS NOT NULL
             AND TRIM(l.ulica) <> ''
             AND z.ime IS NOT NULL
             AND TRIM(z.ime) <> ''
        THEN 'Zapisot za lokacijata e kompleten'
        ELSE 'Zapisot za lokacijata ne e kompleten'
    END AS proverka_celosen_zapis

FROM Lokacija l
LEFT JOIN Zona z
    ON z.zona_id = l.zona_id
LEFT JOIN Kamera k
    ON k.lokacija_id = l.lokacija_id
LEFT JOIN Prekrsok p
    ON p.kamera_id = k.kamera_id
GROUP BY
    l.lokacija_id,
    l.ulica,
    z.ime;


CREATE OR REPLACE VIEW vw_vozila_najmnogu_prekshoci AS
SELECT
    v.vozilo_id,
    v.registarska_oznaka,

    COUNT(pv.prekrsok_id) AS vkupno_prekshoci,

    CASE
        WHEN v.vozilo_id IS NULL THEN 'Nema vozilo'
        ELSE 'Postoi vozilo'
    END AS proverka_vozilo,

    CASE
        WHEN v.registarska_oznaka IS NULL OR TRIM(v.registarska_oznaka) = '' THEN 'Nema registarska oznaka'
        ELSE 'Ima registarska oznaka'
    END AS proverka_registarska_oznaka,

    CASE
        WHEN COUNT(pv.prekrsok_id) = 0 THEN 'Voziloto nema prekrsoci'
        ELSE 'Voziloto ima prekrsoci'
    END AS proverka_prekrsoci,

    CASE
        WHEN COUNT(pv.prekrsok_id) = 0 THEN 'Bez prekrsoci'
        WHEN COUNT(pv.prekrsok_id) BETWEEN 1 AND 3 THEN 'Mal broj prekrsoci'
        WHEN COUNT(pv.prekrsok_id) BETWEEN 4 AND 10 THEN 'Sreden broj prekrsoci'
        ELSE 'Golem broj prekrsoci'
    END AS kategorija_broj_prekrsoci,

    CASE
        WHEN COUNT(pv.prekrsok_id) = 0 THEN 'Voziloto ne e rizicno'
        WHEN COUNT(pv.prekrsok_id) BETWEEN 1 AND 3 THEN 'Voziloto treba da se sledi'
        WHEN COUNT(pv.prekrsok_id) BETWEEN 4 AND 10 THEN 'Voziloto e rizicno'
        ELSE 'Voziloto e visokorizicno'
    END AS proverka_rizik_vozilo,

    CASE
        WHEN v.registarska_oznaka IS NOT NULL
             AND TRIM(v.registarska_oznaka) <> ''
        THEN 'Zapisot za voziloto e kompleten'
        ELSE 'Zapisot za voziloto ne e kompleten'
    END AS proverka_celosen_zapis

FROM Vozilo v
         LEFT JOIN Prekrsok_Vozilo pv ON v.vozilo_id = pv.vozilo_id
GROUP BY
    v.vozilo_id,
    v.registarska_oznaka;



CREATE VIEW vw_povtoreni_prekshoci AS
SELECT tk.ime   AS tip_prekrsok,
       v.registarska_oznaka,
       COUNT(*) AS broj_povtoruvanja
FROM Prekrsok p
         JOIN TipPrekrsok tk ON p.tip_prekrsok_id = tk.tip_prekrsok_id
         JOIN Prekrsok_Vozilo pv ON p.prekrsok_id = pv.prekrsok_id
         JOIN Vozilo v ON pv.vozilo_id = v.vozilo_id
GROUP BY tk.ime, v.registarska_oznaka
HAVING COUNT(*) > 1;



CREATE VIEW vw_kamera_tip_prekshok AS
SELECT k.kamera_id,
       k.seriski_broj,
       tk.ime               AS tip_prekrsok,
       z.ime                AS zona,
       COUNT(p.prekrsok_id) AS broj_deteckii
FROM Kamera k
         LEFT JOIN Prekrsok p ON k.kamera_id = p.kamera_id
         LEFT JOIN TipPrekrsok tk ON p.tip_prekrsok_id = tk.tip_prekrsok_id
         LEFT JOIN Lokacija l ON k.lokacija_id = l.lokacija_id
         LEFT JOIN Zona z ON l.zona_id = z.zona_id
GROUP BY k.kamera_id, k.seriski_broj, tk.ime, z.ime;