USE bazy_danych;
GO

-- Widok na podsumowanie szkolenia
IF OBJECT_ID('dbo.vSzkoleniePodsumowanie', 'V') IS NOT NULL
    DROP VIEW dbo.vSzkoleniePodsumowanie;
GO

CREATE VIEW dbo.vSzkoleniePodsumowanie
AS
SELECT
    s.id_szkolenia,
    s.nazwa,
    s.data_rozpoczecia,
    s.data_zakonczenia,
    s.cena,
    s.cena_ze_znizka,
    s.max_wielkosc_grupy,
    s.numer_pracownika,

    COUNT(DISTINCT r.id_rezerwacji) AS liczba_rezerwacji,
    COUNT(DISTINCT CASE WHEN r.czy_oplacono = 1 THEN r.id_rezerwacji END) AS liczba_oplaconych,
    COUNT(DISTINCT r.indeks) AS liczba_uczniow,

    AVG(CAST(o.liczba_gwiazdek AS DECIMAL(10,2))) AS srednia_gwiazdek,
    SUM(CASE WHEN o.czy_pozytywna = 1 THEN 1 ELSE 0 END) AS liczba_pozytywnych_ocen,

    (SELECT STRING_AGG(zk2.iso, ', ') WITHIN GROUP (ORDER BY zk2.iso)
     FROM dbo.ZAPEWNIANE_KWALIFIKACJE zk2
     WHERE zk2.id_szkolenia = s.id_szkolenia) AS kwalifikacje_iso
FROM dbo.SZKOLENIA s
LEFT JOIN dbo.REZERWACJE r
    ON r.id_szkolenia = s.id_szkolenia
LEFT JOIN dbo.OCENY o
    ON o.id_szkolenia = s.id_szkolenia
LEFT JOIN dbo.ZAPEWNIANE_KWALIFIKACJE zk
    ON zk.id_szkolenia = s.id_szkolenia
GROUP BY
    s.id_szkolenia, s.nazwa, s.data_rozpoczecia, s.data_zakonczenia, s.cena, s.cena_ze_znizka, s.max_wielkosc_grupy, s.numer_pracownika;
GO

-- znajdź 10 szkoleń o najwyższym przychodzie z opłaconych rezerwacji, wyświetl z obłożeniem i średnią oceną.
-- posortuj po przychodzie (malejąco), a potem po średniej ocenie (malejąco).
PRINT N'1. 10 szkoleń o najwyższym przychodzie z opłaconych rezerwacji:';
SELECT TOP (10)
    v.id_szkolenia,
    v.nazwa,
    v.data_rozpoczecia,
    v.data_zakonczenia,
    v.liczba_oplaconych,
    CAST(v.liczba_oplaconych * v.cena_ze_znizka AS DECIMAL(12,2)) AS przychod_szacowany,
    v.max_wielkosc_grupy,
    CAST(100.0 * v.liczba_uczniow / v.max_wielkosc_grupy AS DECIMAL(5,2)) AS procent_oblozenia,
    v.srednia_gwiazdek
FROM dbo.vSzkoleniePodsumowanie v
ORDER BY przychod_szacowany DESC, v.srednia_gwiazdek DESC;
GO



-- znajdź szkoleniowców z liczbą szkoleń > 0, posortowani średnią oceną, liczbą uczestników.
PRINT N'2. Szkoleniowcy z liczbą szkoleń > 0:';
SELECT
    p.numer_pracownika,
    p.imie,
    p.nazwisko,
    COUNT(DISTINCT s.id_szkolenia) AS liczba_szkolen,
    COUNT(DISTINCT r.indeks) AS liczba_uczestnikow,
    AVG(CAST(o.liczba_gwiazdek AS DECIMAL(10,2))) AS srednia_gwiazdek
FROM dbo.SZKOLENIOWCY p
LEFT JOIN dbo.SZKOLENIA s
    ON s.numer_pracownika = p.numer_pracownika
LEFT JOIN dbo.REZERWACJE r
    ON r.id_szkolenia = s.id_szkolenia
LEFT JOIN dbo.OCENY o
    ON o.id_szkolenia = s.id_szkolenia
GROUP BY p.numer_pracownika, p.imie, p.nazwisko
HAVING COUNT(DISTINCT s.id_szkolenia) > 0
ORDER BY srednia_gwiazdek DESC, liczba_uczestnikow DESC;
GO


-- znajdź 10 firm, które wysłały najwięcej (unikalnych) uczniów na szkolenia i wypisz ile łącznie zrobiły rezerwacji.
-- Posortuj po liczbie unikalnych uczniów, a potem po liczbie rezerwacji.
PRINT N'3. 10 firm, które wysłały najwięcej uczniów na szkolenia:';
SELECT TOP (10)
    f.nip,
    f.nazwa,
    COUNT(DISTINCT u.indeks) AS liczba_uczniow_z_firmy,
    COUNT(DISTINCT r.id_rezerwacji) AS liczba_rezerwacji,
    COUNT(DISTINCT CASE WHEN r.czy_oplacono = 1 THEN r.id_rezerwacji END) AS liczba_oplaconych
FROM dbo.FIRMY f
JOIN dbo.UCZNIOWIE u
    ON u.nip = f.nip
LEFT JOIN dbo.REZERWACJE r
    ON r.indeks = u.indeks
GROUP BY f.nip, f.nazwa
ORDER BY liczba_uczniow_z_firmy DESC, liczba_rezerwacji DESC;
GO



-- Znajdź uczniów niezadowolonych (czyli takich, którzy oceniali szkolenia poniżej średniej), oraz ile ocen poniżej średniej wystawili.
-- Posortuj po liczbie ocen poniżej średniej (malejąco), a potem po indeksie ucznia (rosnąco).
PRINT N'4. Uczniowie niezadowoleni:';
SELECT
    u.indeks,
    u.imie,
    u.nazwisko,
    COUNT(*) AS liczba_ocen_ponizej_sredniej
FROM dbo.UCZNIOWIE u
JOIN dbo.OCENY o
    ON o.indeks = u.indeks
WHERE o.liczba_gwiazdek < (
    SELECT AVG(CAST(o2.liczba_gwiazdek AS DECIMAL(10,2)))
    FROM dbo.OCENY o2
)
GROUP BY u.indeks, u.imie, u.nazwisko
ORDER BY liczba_ocen_ponizej_sredniej DESC, u.indeks ASC;
GO


-- znajdź szkolenia z największą zniżką w stosunku do ceny i porównaj do średniej zniżki
-- Posortuj po wielkości zniżki (malejąco), a potem po id_szkolenia (rosnąco).
PRINT N'5. 10 szkoleń z największą zniżką w stosunku do ceny:';
SELECT TOP (10)
    s.id_szkolenia,
    s.nazwa,
    s.cena,
    s.cena_ze_znizka,
    CAST(100.0 * (s.cena - s.cena_ze_znizka) / s.cena AS DECIMAL(6,2)) AS znizka_procent,
    (SELECT CAST(AVG(100.0 * (s2.cena - s2.cena_ze_znizka) / s2.cena) AS DECIMAL(6,2))
    FROM dbo.SZKOLENIA s2) AS srednia_znizka_w_bazie
FROM dbo.SZKOLENIA s
ORDER BY znizka_procent DESC, s.id_szkolenia ASC;
GO


-- Znajdź kwalifikacje ISO, które nie są zapewniane przez żadne szkolenie.
-- Posortuj po kodzie ISO rosnąco.
PRINT N'6. Kwalifikacje ISO, które nie są zapewniane przez żadne szkolenie:';
SELECT
    k.iso,
    k.nazwa
FROM dbo.KWALIFIKACJE k
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.ZAPEWNIANE_KWALIFIKACJE zk
    WHERE zk.iso = k.iso
)
ORDER BY k.iso;
GO

-- znajdź szkoleniowców, którzy mają upoważnienia do jakiejś kwalifikacji ISO, ale nie prowadzą żadnego szkolenia zapewniającego tą kwalifikację.
-- Posortuj po nazwisku, imieniu rosnąco.
PRINT N'7. Szkoleniowcy z niewykorzystanymi upoważnieniami';
SELECT
    p.numer_pracownika,
    p.imie,
    p.nazwisko,
    CAST(STRING_AGG(u.iso, ', ') WITHIN GROUP (ORDER BY u.iso) AS NVARCHAR(200)) AS niewykorzystane_uprawnienia
FROM dbo.SZKOLENIOWCY p
JOIN dbo.UPOWAZNIENIA u
    ON u.numer_pracownika = p.numer_pracownika
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.SZKOLENIA s
    JOIN dbo.ZAPEWNIANE_KWALIFIKACJE zk
    ON zk.id_szkolenia = s.id_szkolenia
    WHERE s.numer_pracownika = p.numer_pracownika
    AND zk.iso = u.iso
)
GROUP BY p.numer_pracownika, p.imie, p.nazwisko
ORDER BY p.nazwisko, p.imie;
GO

-- Dla każdego szkolenia znajdź: ile rezerwacji, ile opłaconych oraz “skuteczność płatności” w %
-- Posortuj po "skuteczności płatności" (malejąco), a potem po liczbie rezerwacji (malejąco).
PRINT N'8. Podsumowanie rezerwacji dla każdego szkolenia:';
SELECT
    s.id_szkolenia,
    s.nazwa,
    COUNT(*) AS rezerwacje,
    SUM(CASE WHEN r.czy_oplacono = 1 THEN 1 ELSE 0 END) AS oplacone,
    CAST(100.0 * SUM(CASE WHEN r.czy_oplacono = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS DECIMAL(6,2)) AS skutecznosc_platnosci
FROM dbo.SZKOLENIA s
JOIN dbo.REZERWACJE r
    ON r.id_szkolenia = s.id_szkolenia
GROUP BY s.id_szkolenia, s.nazwa
ORDER BY skutecznosc_platnosci DESC, rezerwacje DESC;
GO



-- Wypisz listę najbliższych szkoleń (w perspektywie 60 dni) z informacją: prowadzący, kwalifikacje ISO, liczba zapisanych.
-- Posortuj po dacie rozpoczęcia (rosnąco), a potem po liczbie zapisanych (malejąco).
PRINT N'9. Najbliższe szkolenia w perspektywie 60 dni:';
SELECT
    s.id_szkolenia,
    s.nazwa,
    s.data_rozpoczecia,
    s.data_zakonczenia,
    p.imie + N' ' + p.nazwisko AS szkoleniowiec,
    COUNT(DISTINCT r.indeks) AS liczba_zapisanych,
    CAST((SELECT STRING_AGG(zk2.iso, ', ') WITHIN GROUP (ORDER BY zk2.iso)
          FROM dbo.ZAPEWNIANE_KWALIFIKACJE zk2
          WHERE zk2.id_szkolenia = s.id_szkolenia) AS NVARCHAR(100)) AS kwalifikacje_iso
FROM dbo.SZKOLENIA s
JOIN dbo.SZKOLENIOWCY p
    ON p.numer_pracownika = s.numer_pracownika
LEFT JOIN dbo.REZERWACJE r
    ON r.id_szkolenia = s.id_szkolenia
WHERE s.data_rozpoczecia >= CAST(GETDATE() AS DATE)
    AND s.data_rozpoczecia < DATEADD(DAY, 60, CAST(GETDATE() AS DATE))
GROUP BY
    s.id_szkolenia, s.nazwa, s.data_rozpoczecia, s.data_zakonczenia, p.imie, p.nazwisko
ORDER BY s.data_rozpoczecia ASC, liczba_zapisanych DESC;
GO

-- znajdź 20 kwalifikacji z najmniejszą liczbą uprawnionych szkoleniowców (ale chociaż 1)
-- Posortuj po liczbie uprawnionych (rosnąco), a potem po kodzie ISO (rosnąco).
PRINT N'10. Kwalifikacje z najmniejszą liczbą uprawnionych szkoleniowców (ale chociaż 1):';
SELECT TOP (20)
    k.iso,
    k.nazwa,
    COUNT(DISTINCT u.numer_pracownika) AS liczba_uprawnionych_pracownikow
FROM dbo.KWALIFIKACJE k
JOIN dbo.UPOWAZNIENIA u
    ON u.iso = k.iso
GROUP BY k.iso, k.nazwa
HAVING COUNT(DISTINCT u.numer_pracownika) >= 1
ORDER BY liczba_uprawnionych_pracownikow ASC, k.iso ASC;
GO