CREATE MATERIALIZED VIEW mv_aktivita_citatelov
AS
SELECT
    p.ID_pouzivatela,
    p.Meno,
    p.Priezvisko,
    c.Cislo_preukazu,
    c.Status AS Status_citatela,
    COUNT(v.ID_vypozicky) AS Celkovy_pocet_vypoziciek,
    COUNT(CASE WHEN v.Stav IN ('Aktívna','Oneskorená') THEN 1 END) AS Aktivne_vypozicky,
    COUNT(CASE WHEN v.Stav = 'Vrátená' THEN 1 END) AS Ukoncene_vypozicky,
    COUNT(CASE WHEN v.Stav = 'Oneskorená' THEN 1 END) AS Oneskorene_vypozicky,
    ROUND(AVG(NVL(v.Skutocny_datum_vratenia, SYSDATE) - v.Datum_vypozicania)) AS Priemerna_dlzka_vypozicky,
    MAX(v.Datum_vypozicania) AS Posledna_vypozicka
FROM xnecasr00.Pouzivatel p
JOIN xnecasr00.Citatel c ON c.ID_pouzivatela = p.ID_pouzivatela
LEFT JOIN xnecasr00.Vypozicka v ON v.ID_pouzivatela = p.ID_pouzivatela
GROUP BY p.ID_pouzivatela, p.Meno, p.Priezvisko, c.Cislo_preukazu, c.Status;

GRANT SELECT ON mv_aktivita_citatelov TO xnecasr00;

BEGIN
    DBMS_MVIEW.REFRESH('mv_aktivita_citatelov');
END;