CREATE DATABASE bazy_danych;
GO

SELECT name
FROM sys.databases;

GO

USE bazy_danych;
GO

CREATE FUNCTION dbo.DataPoczatkuFirmy()
RETURNS DATE
WITH SCHEMABINDING
AS
BEGIN
    RETURN CONVERT(date, '1999-03-01');
END;
GO


CREATE TABLE dbo.FIRMY (
    -- NIP może zawierać zero z przodu, więc nie używam INT
    nip CHAR(10) NOT NULL,
    nazwa NVARCHAR(300) NOT NULL,
    -- Walidacja numeru telefonu powinna odbywać się po stronie aplikacji ze względu na zmieniające się formaty
    numer_telefonu NVARCHAR(20) NOT NULL,
    adres_email NVARCHAR(255) NOT NULL,

    CONSTRAINT PK_FIRMY PRIMARY KEY (nip),

    CONSTRAINT CHK_FIRMY_NIP_FORMAT
        CHECK (nip NOT LIKE '%[^0-9]%' AND LEN(nip) = 10),

    CONSTRAINT CHK_FIRMY_NAZWA_FORMAT
        CHECK (LEN(nazwa) BETWEEN 1 AND 300),
    
    -- Bardziej skomplikowana walidacja powinna odbywać się po stronie aplikacji
    CONSTRAINT CHK_FIRMY_EMAIL_FORMAT
        CHECK (adres_email LIKE '%_@_%._%')
);
GO

CREATE TABLE dbo.SZKOLENIOWCY (
    numer_pracownika INT NOT NULL,
    imie NVARCHAR(100) NOT NULL,
    nazwisko NVARCHAR(100) NOT NULL,
    tytul_naukowy NVARCHAR(100) NULL,
    data_urodzenia DATE NOT NULL,
    pesel CHAR(11) NULL,
    numer_telefonu NVARCHAR(25) NOT NULL,
    adres_email NVARCHAR(255) NOT NULL,
    data_zatrudnienia DATE NOT NULL,
    data_zwolnienia DATE NULL,

    CONSTRAINT PK_SZKOLENIOWCY PRIMARY KEY (numer_pracownika),

    CONSTRAINT CHK_SZKOLENIOWCY_IMIE_FORMAT
        CHECK (LEN(imie) BETWEEN 2 AND 100),

    CONSTRAINT CHK_SZKOLENIOWCY_NAZWISKO_FORMAT
        CHECK (LEN(nazwisko) BETWEEN 2 AND 100),

    CONSTRAINT CHK_SZKOLENIOWCY_NUMER
        CHECK (numer_pracownika BETWEEN 10000 AND 99999),

    -- Można dodać dodatkowe warunki walidacji, ale zrobiłbym to po stronie aplikacji
    CONSTRAINT CHK_SZKOLENIOWCY_PESEL_FORMAT
        CHECK (pesel IS NULL OR (pesel NOT LIKE '%[^0-9]%' AND LEN(pesel) = 11)),

    CONSTRAINT CHK_SZKOLENIOWCY_DATA_URODZENIA
        CHECK (
            data_urodzenia >= '1900-01-01'
            AND data_urodzenia <= CAST(GETDATE() AS DATE)
        ),

    CONSTRAINT CHK_SZKOLENIOWCY_DATA_ZATR
        CHECK (
            data_zatrudnienia >= dbo.DataPoczatkuFirmy()
            AND data_zatrudnienia <= CAST(GETDATE() AS DATE)
        ),

    CONSTRAINT CHK_SZKOLENIOWCY_DATA_ZWOL
        CHECK (
            data_zwolnienia IS NULL
            OR (data_zwolnienia >= data_zatrudnienia
                AND data_zwolnienia <= CAST(GETDATE() AS DATE))
        ),

    -- Bardziej skomplikowana walidacja powinna odbywać się po stronie aplikacji
    CONSTRAINT CHK_SZKOLENIOWCY_EMAIL_FORMAT
        CHECK (adres_email LIKE '%_@_%._%')
);
GO

CREATE TABLE dbo.UCZNIOWIE (
    indeks INT NOT NULL,
    imie NVARCHAR(100) NOT NULL,
    nazwisko NVARCHAR(100) NOT NULL,
    data_urodzenia DATE NOT NULL,
    pesel CHAR(11) NULL,
    numer_telefonu NVARCHAR(25) NOT NULL,
    adres_email NVARCHAR(255) NOT NULL,
    nip CHAR(10) NULL,

    CONSTRAINT PK_UCZNIOWIE PRIMARY KEY (indeks),

    CONSTRAINT FK_UCZNIOWIE_FIRMY
        FOREIGN KEY (nip)
        REFERENCES dbo.FIRMY (nip)
        -- Przy usunięciu firmy, uczniowie pozostają w bazie
        -- Przy zmianie NIP firmy, NIP ucznia się zmienia
        ON UPDATE CASCADE
        ON DELETE NO ACTION,

    CONSTRAINT CHK_UCZNIOWIE_INDEKS_FORMAT
        CHECK (indeks BETWEEN 100000 AND 999999),

    -- Można dodać dodatkowe warunki walidacji, ale zrobiłbym to po stronie aplikacji
    CONSTRAINT CHK_UCZNIOWIE_PESEL_FORMAT
        CHECK (pesel IS NULL OR (pesel NOT LIKE '%[^0-9]%' AND LEN(pesel) = 11)),

    CONSTRAINT CHK_UCZNIOWIE_DATA_URODZENIA
        CHECK (
            data_urodzenia >= '1900-01-01'
            AND data_urodzenia <= CAST(GETDATE() AS DATE)
        ),

    -- Bardziej skomplikowana walidacja powinna odbywać się po stronie aplikacji
    CONSTRAINT CHK_UCZNIOWIE_EMAIL_FORMAT
        CHECK (adres_email LIKE '%_@_%._%')
);
GO

CREATE TABLE dbo.SZKOLENIA (
    id_szkolenia INT NOT NULL IDENTITY(1,1),
    data_rozpoczecia DATE NOT NULL,
    data_zakonczenia DATE NOT NULL,
    nazwa NVARCHAR(300) NOT NULL,
    opis NVARCHAR(MAX) NOT NULL,
    cena DECIMAL(10,2) NOT NULL,
    cena_ze_znizka DECIMAL(10,2) NOT NULL,
    max_wielkosc_grupy INT NOT NULL,
    numer_pracownika INT NOT NULL,

    CONSTRAINT PK_SZKOLENIA PRIMARY KEY (id_szkolenia),

    CONSTRAINT FK_SZKOLENIA_SZKOLENIOWCY
        FOREIGN KEY (numer_pracownika)
        REFERENCES dbo.SZKOLENIOWCY (numer_pracownika)
        -- Przy usunięciu szkoleniowca, szkolenia pozostają w bazie
        -- Przy zmianie numeru pracownika szkoleniowca, numer się zmienia
        ON UPDATE CASCADE
        ON DELETE NO ACTION,

    CONSTRAINT CHK_SZKOLENIA_DATY
        CHECK (
            data_rozpoczecia >= dbo.DataPoczatkuFirmy()
            AND data_zakonczenia >= data_rozpoczecia
        ),

    CONSTRAINT CHK_SZKOLENIA_NAZWA
        CHECK (LEN(nazwa) BETWEEN 1 AND 300),

    CONSTRAINT CHK_SZKOLENIA_OPIS
        CHECK (LEN(opis) BETWEEN 1 AND 10000),

    CONSTRAINT CHK_SZKOLENIA_CENY
        CHECK (
            cena BETWEEN 0 AND 10000
            AND cena_ze_znizka BETWEEN 0 AND 10000
        ),

    CONSTRAINT CHK_SZKOLENIA_GRUPA
        CHECK (max_wielkosc_grupy BETWEEN 1 AND 100)
);
GO

CREATE TABLE dbo.REZERWACJE (
    id_rezerwacji INT NOT NULL IDENTITY(1,1),
    data_zlozenia DATE NOT NULL,
    czy_oplacono BIT NOT NULL,
    indeks INT NOT NULL,
    id_szkolenia INT NOT NULL,

    CONSTRAINT PK_REZERWACJE PRIMARY KEY (id_rezerwacji),

    CONSTRAINT FK_REZERWACJE_UCZNIOWIE
        FOREIGN KEY (indeks)
        REFERENCES dbo.UCZNIOWIE (indeks)
        -- Przy usunięciu ucznia, rezerwacje pozostają w bazie
        -- Przy zmianie indeksu ucznia, indeks się zmienia
        ON UPDATE CASCADE
        ON DELETE NO ACTION,

    CONSTRAINT FK_REZERWACJE_SZKOLENIA
        FOREIGN KEY (id_szkolenia)
        REFERENCES dbo.SZKOLENIA (id_szkolenia)
        -- Przy usunięciu szkolenia, rezerwacje są usuwane
        -- Przy zmianie id szkolenia, id się zmienia
        ON UPDATE CASCADE
        ON DELETE CASCADE,

    CONSTRAINT UQ_REZERWACJE_UCZEN_SZKOLENIE
        UNIQUE (indeks, id_szkolenia),

    CONSTRAINT CHK_REZERWACJE_DATA
        CHECK (
            data_zlozenia >= dbo.DataPoczatkuFirmy()
            AND data_zlozenia <= CAST(GETDATE() AS DATE)
        )
);
GO

CREATE TABLE dbo.OCENY (
    id_oceny INT NOT NULL IDENTITY(1,1),
    czy_pozytywna BIT NOT NULL,
    czy_ukryta BIT NOT NULL,
    liczba_gwiazdek TINYINT NOT NULL,
    komentarz NVARCHAR(1000) NULL,
    data_wystawienia DATE NOT NULL,
    indeks INT NOT NULL,
    id_szkolenia INT NOT NULL,

    CONSTRAINT PK_OCENY PRIMARY KEY (id_oceny),

    CONSTRAINT FK_OCENY_UCZNIOWIE
        FOREIGN KEY (indeks)
        REFERENCES dbo.UCZNIOWIE (indeks)
        -- Przy usunięciu ucznia, oceny pozostają w bazie
        -- Przy zmianie indeksu ucznia, indeks się zmienia
        ON UPDATE CASCADE
        ON DELETE NO ACTION,

    CONSTRAINT FK_OCENY_SZKOLENIA
        FOREIGN KEY (id_szkolenia)
        REFERENCES dbo.SZKOLENIA (id_szkolenia)
        -- Przy usunięciu szkolenia, oceny są usuwane
        -- Przy zmianie id szkolenia, id się zmienia
        ON UPDATE CASCADE
        ON DELETE CASCADE,

    CONSTRAINT CHK_OCENY_GWIAZDKI
        CHECK (liczba_gwiazdek BETWEEN 1 AND 5),

    CONSTRAINT CHK_OCENY_KOMENTARZ
        CHECK (komentarz IS NULL OR (LEN(komentarz) BETWEEN 1 AND 1000)),

    CONSTRAINT UQ_OCENY_UCZEN_SZKOLENIE
        UNIQUE (indeks, id_szkolenia),

    CONSTRAINT CHK_OCENY_DATA
        CHECK (
            data_wystawienia >= dbo.DataPoczatkuFirmy()
            AND data_wystawienia <= CAST(GETDATE() AS DATE)
        )
);
GO

CREATE TABLE dbo.KWALIFIKACJE (
    iso VARCHAR(10) NOT NULL,
    nazwa NVARCHAR(300) NOT NULL,
    opis NVARCHAR(MAX) NOT NULL,

    CONSTRAINT PK_KWALIFIKACJE PRIMARY KEY (iso),

    CONSTRAINT CHK_KWALIFIKACJE_ISO
        CHECK (iso NOT LIKE '%[^0-9]%' AND LEN(iso) BETWEEN 3 AND 10),

    CONSTRAINT CHK_KWALIFIKACJE_NAZWA
        CHECK (LEN(nazwa) BETWEEN 1 AND 300),
    
    CONSTRAINT CHK_KWALIFIKACJE_OPIS
        CHECK (LEN(opis) BETWEEN 1 AND 10000)
);
GO

CREATE TABLE dbo.ZAPEWNIANE_KWALIFIKACJE (
    id_zapewnianej_kwalifikacji INT NOT NULL IDENTITY(1,1),
    opis NVARCHAR(MAX) NOT NULL,
    iso VARCHAR(10) NOT NULL,
    id_szkolenia INT NOT NULL,

    CONSTRAINT PK_ZAPEWNIANE_KWAL PRIMARY KEY (id_zapewnianej_kwalifikacji),

    CONSTRAINT FK_ZAPEWNIANE_KWAL_KWALIFIKACJE
        FOREIGN KEY (iso)
        REFERENCES dbo.KWALIFIKACJE (iso)
        -- Przy usunięciu kwalifikacji, zapewniane kwalifikacje są usuwane
        -- Przy zmianie ISO kwalifikacji, ISO się zmienia
        ON UPDATE CASCADE
        ON DELETE CASCADE,

    CONSTRAINT FK_ZAPEWNIANE_KWAL_SZKOLENIA
        FOREIGN KEY (id_szkolenia)
        REFERENCES dbo.SZKOLENIA (id_szkolenia)
        -- Przy usunięciu szkolenia, zapewniane kwalifikacje są usuwane
        -- Przy zmianie id szkolenia, id się zmienia
        ON UPDATE CASCADE
        ON DELETE CASCADE,

    CONSTRAINT UQ_ZAPEWNIANE_KWAL_SZKOLENIE_ISO
        UNIQUE (id_szkolenia, iso),

    CONSTRAINT CHK_ZAPEWNIANE_KWAL_OPIS
        CHECK (LEN(opis) BETWEEN 1 AND 10000)
);
GO

CREATE TABLE dbo.UPOWAZNIENIA (
    id_upowaznienia INT NOT NULL IDENTITY(1,1),
    data_wystawienia DATE NOT NULL,
    podmiot_wystawiajacy NVARCHAR(300) NOT NULL,
    numer_pracownika INT NOT NULL,
    iso VARCHAR(10) NOT NULL,

    CONSTRAINT PK_UPOWAZNIENIA PRIMARY KEY (id_upowaznienia),

    CONSTRAINT FK_UPOWAZNIENIA_SZKOLENIOWCY
        FOREIGN KEY (numer_pracownika)
        REFERENCES dbo.SZKOLENIOWCY (numer_pracownika)
        -- Przy usunięciu szkoleniowca, upoważnienia pozostają w bazie
        -- Przy zmianie numeru pracownika szkoleniowca, numer się zmienia
        ON UPDATE CASCADE
        ON DELETE NO ACTION,

    CONSTRAINT FK_UPOWAZNIENIA_KWALIFIKACJE
        FOREIGN KEY (iso)
        REFERENCES dbo.KWALIFIKACJE (iso)
        -- Przy usunięciu kwalifikacji, upoważnienia są usuwane
        -- Przy zmianie ISO kwalifikacji, ISO się zmienia
        ON UPDATE CASCADE
        ON DELETE CASCADE,

    CONSTRAINT UQ_UPOWAZNIENIA_NATURAL
        UNIQUE (podmiot_wystawiajacy, data_wystawienia, numer_pracownika, iso),

    CONSTRAINT CHK_UPOWAZNIENIA_DATA
        CHECK (
            data_wystawienia >= '1900-01-01'
            AND data_wystawienia <= CAST(GETDATE() AS DATE)
        ),

    CONSTRAINT CHK_UPOWAZNIENIA_PODMIOT
        CHECK (LEN(podmiot_wystawiajacy) BETWEEN 1 AND 300)
);
GO