# <center>Autorzy<br></center>
## <center>Dominik Motyl, Mikołaj Kraczek,<br> Tomasz Leniart , Michał Zięba</center>

## Tabele
- Accounts
- Attractions
- Carriers
- Cities
- CompanyClients
- Countries
- Departments
- Employees
- Offers
- Participants
- Passwords
- Payments
- PersonalClients
- Reservations
- ReservedAttractions
- Tours

## Użytkownicy i ich uprawnienia
### Konsultant
- Przeglądnie ofert
- Tworzenie kont
- Edycja konta
- Dokonywanie rezerwacji
- Anulowanie rezerwacji
- Dodawanie/usuwanie uczestników wycieczki do tygodnia przed
- Dodawanie płatności

grant select on dbo.Offers to consultant
grant select on dbo.Tours to consultant
grant insert, update on dbo.Accounts to consultant
grant insert, update on dbo.[Personal Clients] to consultant
grant insert, update on dbo.[Company Clients] to consultant
grant insert, delete on dbo.Reservations to consultant
grant insert on dbo.Payments to consultant
grant select on dbo.Reservations_view to consultant
grant select on dbo.Personal_clients_reservation to consultant
grant select on dbo.Company_clients_reservation to consultant
grant select on dbo.Payment_per_reservation_detalis to consultant
grant select on dbo.tour_view to consultant

### Pracownik biura
- Generowanie raportów dla innych osób
- Sprawdzanie poprawności rezerwacji
- Dodawnie wycieczek
- Dodawanie atrakcji do wycieczek
- Ustawianie/dodawanie parametrów wycieczki(transport/miejsce)
- Dodawanie przewoźników
- Dodawanie dostępnych miast
- Dodawanie dostępnych hoteli dla danego miasta
- Dodawanie obsługiwanych krajów
- Ustawianie kosztu wycieczki

grant select on database :: u_mizieba to manager
deny select on dbo.Passwords to manager
grant insert on dbo.Attractions to manager
grant insert on dbo.[Tour Attractions] to manager
grant insert on dbo.Tours to manager
grant insert on dbo.Carriers to manager
grant insert on dbo.Cities to manager
grant insert on dbo.Countries to manager
grant insert on dbo.Offers to manager

### Administrator bazy
- Tworzenie/testowanie backupów
- Pełna kontrola nad bazą danych

grant control, alter on database :: u_mizieba to admin

## Funkcjonalności
Zarządzanie rezerwacją odbywa się na naszej stronie po zalogowaniu na wcześniej utworzone konto. Można tam dodawać/usuwać/modyfikować uczestników i dodawać/usuwać atrakcje. Możliwe jest też z tego widoku anulowanie rezerwacji lub zmiany terminów wycieczki.

Konto może zostać utworzone przez osobę prywatną jak i firmę. Każde konto zawierało będzie te same kolumny w tabeli kont, jednak dla osoby prywatnej pola typowe dla firmy, takie jak NIP, będą wartością NULL.

Usuwanie uczestników odbywa się przez zmianę wartości w odpowiednim polu-fladze

Po rezerwacji otrzymuje się maila, na którego trzeba odpowiedzieć ( np. kliknąć w coś) aby potwierdzić rezerwację. W przypadku braku potwierdzenia rekord jest kasowany po tygodniu.

Do utworzenia rezerwacji wymagane jest logowanie/utworzenie konta w naszym serwisie.

Po dokonaniu rezerwacji nie jest wymagana zaliczka. Użytkownik który dokonał rezerwacji ma obowiązek zapłacić pełną kwotę nie później niż 7 dni przed terminem rozpoczęcia wycieczki.

User może sobie wybrać rejon i na jego bazie będą wykonywane niektóre checki po stronie backendu

## Raporty
- Dla strony internetowej z aktualnymi ofertami

# Implementacja

## Funkcje systemu i uprawnienia
- [ ] Wystawianie potwierdeń płatności
- [ ] Tworzenie backupów
- [ ] Testowanie backupów
- [ ] Zmiana timetables
- [ ] Zarządzanie dokumentami
- [ ] Ubezpieczenia
- [ ] Tworzenie użytkowników
- [ ] Dodawanie atrakcji
- [ ] Zarządzanie istniejącymi tranzakcjami
- [ ] Anulowanie
- [ ] Opłata
- [ ] Potwierdzenie płatności
- [ ] Lista uczestników
- [ ] Opinie
- [ ] Zarządzanie organizacją wycieczki
- [ ] Tworzenie wycieczek
- [ ] Zarządzanie dostępnymi miejscami wycieczek
- [ ] Rezerwacja wycieczek
- [ ] Dodawanie listy uczestników wycieczki
- [ ] Generowanie raportów
- [ ] Edycja danych
- [ ] Tworzenie atrakcji dla danej wycieczki

<table>
<tr>
<th>Funkcja</th>
<th>Osoba</th>
<th>Opis</th>
</tr>
<tr>
<td>Tworzenie konta</td>
<td>Każdy użytkownik</td>
<td>Aby dokonać rezerwacji każdy użytkownik musi założyć konto. Ma to na celu wygodną przyszłą edycję rezerwacji, której dokona oraz łatwy dostęp do wszelkich informacji na temat zarezerwowanych wycieczek. Konto może być założone przez osobę prywatną oraz firmy.</td>
</tr>
<tr>
<td>Przeglądanie oferty</td>
<td>Każdy użytkownik</td>
<td>Każdy użytkownik może przeglądać ofertę wycieczek oraz atrakcje dostępne dla każdej wycieczki.</td>
</tr>
<tr>
<td>Dokonywanie rezewacji</td>
<td>Każdy zalogowany użytkownik</td>
<td>Po zalogowaniu można dokonywać rezerwacji. Rezerwacji można dokononywać nie tylko dla siebie ale też dla innych. Przy rezerwacji należy podać liczbę uczesntików, ich dane oraz wybrane atrakcje.</td>
</tr>
<tr>
<td>Anulowanie rezerwacji</td>
<td>Osoba, która składała rezerwację</td>
<td>Anulowania rezerwacji można dokonać tylko osoba, która składała rezerwację, a nie każdy jej uczestnik.</td>
</tr>
<tr>
<td>Edycja wycieczki</td>
<td>Osoba, która składała rezerwację</td>
<td>Osoba, która składała rezerwację, może dodawać i usuwać uczestników wycieczki. Może również dodawć i usuwać atrakcję. Opcje te są dostępne tylko przed dokonaniem płatności.</td>
</tr>
<tr>
<td>Dokonywanie płatności</td>
<td>Osoba, która składała rezerwację</td>
<td>Po dokonaniu rezerwacji użytkonik, który ją złożył nie później niż 7 dni przed terminem rozpoczęcia wycieczki musi dokonać płatności. </td>
</tr>
<tr>
<td>Pobieranie odpowiednich dokumentów</td>
<td>Osoba, która dokonała rezerwacji</td>
<td>Osoba, która dokonała rezerwacji, może pobierać odpowiendie dokumenty, takie jak poetwierdzenie rezerwacji, potwierdzenie płatności, paragony, faktury, bilety na transport i/lub pobyt w hotelu</td>
</tr>
<tr>
<td>Generowanie raportów</td>
<td>Uprawniony pracownik biura</td>
<td>Generowanie raportów dotyczące osób, które biorą udział w wycieczce, aby mogły zostać później przekazane np. dla kierowców, hoteli itp.</td>
</tr>
<tr>
<td>Dodawnanie/usuwanie/edytowanie wycieczek</td>
<td>Uprawniony pracownik biura</td>
<td>Pracownik z odpowienimi uprawnieniami może dokonowyać zmian w wycieczkach (czas, miejsce noclegu, transport, koszt, dostępne dla danej wycieczki atrakcjie) oraz dodawć je i usuwać</td>
</tr>
<tr>
<td>Sprawdzenie poprawności rezerwacji</td>
<td>Uprawniony pracownik biura</td>
<td>Przed upływem 7 dni przed rozpoczęciem wycieczki, odpowiedni pracownik może sprawdzić poprwaność złożonej rezerwacji, podanych danych itp. W przypadku złych danych i praku kontaktu z rezerwującym może anulować rezerwację</td>
</tr>
<tr>
<td>Dodawanie nowego kraju, miasta, przewoźnika</td>
<td>Uprawniony pracownik biura</td>
<td>Pracownik biura ma możliwość dodania nowych destynacji, do jakich mogą odbywać się wycieczki. Może więc dodać do odpowiednich tabel rekordy. Może również dodać nowych przewoźników, jeśli takowi się znajdą.</td>
</tr>
<tr>
<td>Tworzenie/testowanie backupów</td>
<td>Administrator bazy danych</td>
<td>Jako najbardziej uprawniony użytkonik systemu administrator może tworzyć, usuwać i testować backupy.</td>
</tr>
<tr>
<td>Pełny dostęp do bazy danych</td>
<td>Administrator bazy danych</td>
<td>Administrator ma pełne uprawnienia do bazy danych. Może ją przeglądać i w razie potrzeby edytować</td>
</tr>
</table>

# Schemat

<!---->

# Szczególowy opis schematu

## Użytkownicy i pracownicy

### Accounts


Aby rezerwować wycieczki trzeba być zajerestrowanym uczestnikiem. Dane konta (oprócz hasła), przechowywane są w tabeli account. Zawiera one dane, które posiada każde konto, takie jak: unikalny adres email, numer telefonu oraz unikalny username i autmatycznie generowane account ID (klucz główny). Oprócz tego zawiera również passwordID (klucz obcy). Jest to niezbędne, ponieważ hasła przechowywane są w odzielnej tabeli.

**accountID: klucz główny
passwordID: klucz obcy łączący się z tabelą password 1:1.
email_addres: unikatowy adres email
username: unikatowa nazwa użytkowanika
surname: nazwisko**

CREATE TABLE [dbo].[Accounts] (
    [accountID]     INT          NOT NULL,
    [email_address] VARCHAR (60) NOT NULL,
    [username]      VARCHAR (40) NOT NULL,
    [phone]         VARCHAR (19) NOT NULL,
    [passwordID]    INT          NOT NULL,
    CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ([accountID] ASC),
    CONSTRAINT [Account_email_address 1] CHECK ([email_address] like '%[@]%[.]%'),
    CONSTRAINT [Account_phone] CHECK ([phone] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
    CONSTRAINT [Account_username] CHECK ([username] like '___%'),
    CONSTRAINT [PasswordID_FK] FOREIGN KEY ([passwordID]) REFERENCES [dbo].[Passwords] ([passwordID]),
    CONSTRAINT [Account_pk] UNIQUE NONCLUSTERED ([username] ASC),
    CONSTRAINT [Account_pk_2] UNIQUE NONCLUSTERED ([email_address] ASC),
    CONSTRAINT [Account_pk_3] UNIQUE NONCLUSTERED ([passwordID] ASC)
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>AccountID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>PasswordID</td>
<td>FK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>username</td>
<td></td>
<td><code>[username] like '%[@]%[.]%'</code></td>
<td>yes</td>
</tr>
<tr>
<td>email_address</td>
<td></td>
<td><code>[email_address] like '%[@]%[.]%'</code></td>
<td>yes</td>
</tr>
<tr>
<td>phone</td>
<td></td>
<td><code>[phone] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'</code></td>
<td></td>
</tr>
</table>

### Company Clients


Tabela połączona w relacji 1 do 1 z tabelą account. Zawiera dane opisujące klienta będącego firmą. Zawiera pola takie jak NIP i nazwa firmy. Oczywiście posiada klucz główny i obcy jednocześnie account ID.

**accountID: klucz główny i obcy jednocześnie, łączy się z tabelą account
company_name: nazwa firmy
NIP: NIP**

CREATE TABLE [dbo].[CompanyClients] (
    [accountID]    INT           NOT NULL,
    [company_name] VARCHAR (150) NOT NULL,
    [NIP]          VARCHAR (10)  NOT NULL,
    CONSTRAINT [PK_CompanyClient] PRIMARY KEY CLUSTERED ([accountID] ASC),
    CONSTRAINT [CompanyClient_company_name] CHECK (NOT [company_name] like '%[^a-z._ ]%'),
    CONSTRAINT [CompanyClient_nip] CHECK ([NIP] like '__________'),
    CONSTRAINT [FK_CompanyClient_account] FOREIGN KEY ([accountID]) REFERENCES [dbo].[Accounts] ([accountID]),
    CONSTRAINT [CompanyClient_pk] UNIQUE NONCLUSTERED ([NIP] ASC),
    CONSTRAINT [CompanyClient_pk_2] UNIQUE NONCLUSTERED ([company_name] ASC)
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>accountID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>NIP</td>
<td></td>
<td><code>[NIP] like '__________'</code></td>
<td>yes</td>
</tr>
<tr>
<td>Company Name</td>
<td></td>
<td><code>NOT [company_name] like '%[^a-z]%'</code></td>
<td>yes</td>
</tr>
</table>

### Personal Clients


Tabela podobnie jak CompanyClient zawiera dane tym razem o kliencie będącym osobą prywatną. Zawiera pola identyfikujące daną osobę takie jak:
imię, nazwisko, adres, kod pocztowy oraz datę urodzenia. Zawiera również klucz obcy wskazujący na kraj, gdyż może się zdarzyć, że dana osoba mieszka za granicą. Klucz główny i obcy łączący z Account to accountID.
<b>
- accountID: klucz główny i obcy łączący tabelę z tabelą Account
- cityID: klucz obcy wskazujący na tabelę City, wskazujące miejsce zamieszkania klienta
- names: pole typu varchar wskazujące na imiona klienta które podaje się przy rejestracji konta
- surname: pole typu varchar, zawiera nazwisko klienta, również potrzebne przy rejestracji
- address: pole typu varchar, zawiera informację o numerze domu/mieszkania.
- postal_code: pole typu varchar, wskazuje na kod pocztowy, potrzebne do ewentualnej korespondencji
- birth_date: pole typu date, zawiera datę urodzenia klienta, wybrana zamiast numeru PESEL
</b>

CREATE TABLE [dbo].[PersonalClients] (
    [accountID]   INT           NOT NULL,
    [cityID]      INT           NOT NULL,
    [names]       VARCHAR (50)  NOT NULL,
    [surname]     VARCHAR (50)  NOT NULL,
    [address]     VARCHAR (200) NOT NULL,
    [postal_code] VARCHAR (6)   NOT NULL,
    [birth_date]  DATE          NOT NULL,
    CONSTRAINT [PK_PersonalClient] PRIMARY KEY CLUSTERED ([accountID] ASC),
    CONSTRAINT [PersonalClient_first_name] CHECK (NOT [names] like '%[^a-z]%'),
    CONSTRAINT [PersonalClient_last_name] CHECK (NOT [surname] like '%[^a-z]%'),
    CONSTRAINT [PersonalClient_postal_code] CHECK ([postal_code] like '__-___'),
    CONSTRAINT [FK_PersonalClient_account] FOREIGN KEY ([accountID]) REFERENCES [dbo].[Accounts] ([accountID]),
    CONSTRAINT [FK_PersonalClient_city] FOREIGN KEY ([cityID]) REFERENCES [dbo].[Cities] ([cityID])
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>accountID</td>
<td>PK,FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>countryID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>names</td>
<td></td>
<td><code>NOT [names] like '%[^a-z]%'</code></td>
<td></td>
</tr>
<tr>
<td>last_name</td>
<td></td>
<td><code>NOT [surname] like '%[^a-z]%'</code></td>
<td></td>
</tr>
<tr>
<td>address</td>
<td></td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>postal_code</td>
<td></td>
<td><code>[postal_code] like '__-___'</code></td>
<td></td>
</tr>
<tr>
<td>birth_date</td>
<td></td>
<td><code></code></td>
<td></td>
</tr>
</table>

### Employees


Na bardzo podobnej zasadzie jak tabela account, ale dla pracowników nie tworzymy usernamów. Tabela employee przechowuje również ID działu w którym dana osoba pracuje.

**employeeID: klucz główny
passwordID: klucz obcy, łączy się z tabelą password
departmentID: klucz obcy, łączy się ze słownikiem department
names: imiona
surname: nazwisko
email_address: unikatowy adres email
phone: numer telefonu**

CREATE TABLE [dbo].[Employees] (
    [employeeID]   INT           NOT NULL,
    [passwordID]   INT           NOT NULL,
    [departmentID] INT           NOT NULL,
    [names]        VARCHAR (60)  NOT NULL,
    [surname]      VARCHAR (100) NOT NULL,
    [email]        VARCHAR (50)  NOT NULL,
    [phone]        VARCHAR (19)  NOT NULL,
    CONSTRAINT [employee_pk] PRIMARY KEY CLUSTERED ([employeeID] ASC),
    CONSTRAINT [Employee_email] CHECK ([email] like '%[@]%[.]%'),
    CONSTRAINT [Employee_name] CHECK (NOT [names] like '%[^a-z]%'),
    CONSTRAINT [Employee_phone] CHECK ([phone] like '_________'),
    CONSTRAINT [Employee_surname] CHECK (NOT [surname] like '%[^a-z]%'),
    CONSTRAINT [DepartamentID___fk] FOREIGN KEY ([departmentID]) REFERENCES [dbo].[Departments] ([departmentID]),
    CONSTRAINT [employeeID] FOREIGN KEY ([passwordID]) REFERENCES [dbo].[Passwords] ([passwordID]),
    CONSTRAINT [Employee_pk_1] UNIQUE NONCLUSTERED ([passwordID] ASC),
    CONSTRAINT [Employee_pk_2] UNIQUE NONCLUSTERED ([email] ASC),
    CONSTRAINT [Employee_pk_3] UNIQUE NONCLUSTERED ([phone] ASC)
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>employeeID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>passwordID</td>
<td>FK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>departmentID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>names</td>
<td></td>
<td><code>NOT [names] like '%[^a-z]%'</code></td>
<td>yes</td>
</tr>
<tr>
<td>surname</td>
<td></td>
<td><code>NOT [surname] like '%[^a-z]%'</code></td>
<td>yes</td>
</tr>
<tr>
<td>email</td>
<td></td>
<td><code>[email] like '%[@]%[.]%'</code></td>
<td>yes</td>
</tr>
<tr>
<td>phone</td>
<td></td>
<td><code>[phone] like '_________'</code></td>
<td>yes</td>
</tr>
</table>

### Departaments


Słownik przechowujący nazwy działów i odpowiadające im ID (klucz główny).

**departmentID: klucz główny
department_name: nazwa działu**

CREATE TABLE [dbo].[Departments] (
    [departmentID]    INT          NOT NULL,
    [department_name] VARCHAR (50) NOT NULL,
    CONSTRAINT [DepartamentID_pk] PRIMARY KEY CLUSTERED ([departmentID] ASC),
    CONSTRAINT [Department_department_name] CHECK (NOT [department_name] like '%[^a-z ]%')
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>departamentID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>departament_name</td>
<td></td>
<td><code>NOT [departament_name] like '%[^a-z ]%'</code></td>
<td></td>
</tr>
</table>

### Passwords


W tabeli password przechowywane są hasła w postaci hashy, używaną sól oraz klucz główny password ID. Po tym kluczu tabela password łączy się z tabelą account oraz employee 1:1.

**passwordID: klucz główny
hash: haseł nie przechowujemy w formie jawnej tylko w formie hasha
salt: sól używana do hasha (zakładamy, że zawsze dodajemy ją po pierwszym znaku hasła)**

CREATE TABLE [dbo].[Passwords] (
    [passwordID] INT           NOT NULL,
    [hash]       VARCHAR (100) NOT NULL,
    [salt]       VARCHAR (100) NOT NULL,
    CONSTRAINT [passwordID] PRIMARY KEY CLUSTERED ([passwordID] ASC),
    CONSTRAINT [Password_pk] UNIQUE NONCLUSTERED ([hash] ASC),
    CONSTRAINT [Password_pk_2] UNIQUE NONCLUSTERED ([salt] ASC)
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>passwordID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>hash</td>
<td></td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>salt</td>
<td></td>
<td><code></code></td>
<td>yes</td>
</tr>
</table>

## Wycieczki i rezerwacje

### Countries


Słownik przechowujący nazwy krajów i odpowiadające im ID (klucz główny)

**countryID: klucz główny
country_name: nazwa kraju**

CREATE TABLE [dbo].[Countries] (
    [countryID]    INT           NOT NULL,
    [country_name] VARCHAR (100) NOT NULL,
    CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED ([countryID] ASC),
    CONSTRAINT [Country_country_name] CHECK (NOT [country_name] like '%[^a-z ]%'),
    CONSTRAINT [Country_pk] UNIQUE NONCLUSTERED ([country_name] ASC)
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>countryID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>country_Name</td>
<td></td>
<td><code>NOT [country_name] like '%[^a-z ]%'</code></td>
<td>yes</td>
</tr>
</table>

### Cities


Słownik przechowujący nazwy miast i odpowiadające im ID (klucz główny). Tabela przechowuje również państwa, w których dane miasta się znajdują

**cityID: klucz główny
countryID: klucz obcy łączący się z tabelą countries
city_name: nazwa miasta**

CREATE TABLE [dbo].[Cities] (
    [cityID]    INT           NOT NULL,
    [city_name] VARCHAR (100) NOT NULL,
    [countryID] INT           NOT NULL,
    CONSTRAINT [cityID] PRIMARY KEY CLUSTERED ([cityID] ASC),
    CONSTRAINT [City_city_name ] CHECK (NOT [city_name] like '%[^a-z -]%'),
    CONSTRAINT [countryID] FOREIGN KEY ([countryID]) REFERENCES [dbo].[Countries] ([countryID]),
    CONSTRAINT [City_pk] UNIQUE NONCLUSTERED ([city_name] ASC)
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>cityID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>city_name</td>
<td></td>
<td><code>NOT [city_name] like '%[^a-z -]%'</code></td>
<td>yes</td>
</tr>
<tr>
<td>countryID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
</table>

### Offers


Tabela offer przechowuje ID miasta z którego zaczyna i kończy się wycieczka, aby nie powielać tych informacji w tabeli Tour.

<b>

- offerID: klucz główny, pole typu int, automatycznie inkrementowane podczas dodawaniu rekordów, jednoznacznie definiuje dany rekord.
- starting_city: pole typu int, klucz obcy do tabeli Cities, wskazuje na miejsce z którego będzie zaczynać się wycieczka
- destination_city: pole typu int, klucz obcy do tabeli Cities, wskazuje na miejsce docelowe wycieczki
</b>

CREATE TABLE [dbo].[Offers] (
    [offerID]            INT NOT NULL,
    [starting_cityID]    INT NOT NULL,
    [destination_cityID] INT NOT NULL,
    CONSTRAINT [offerID] PRIMARY KEY CLUSTERED ([offerID] ASC),
    CONSTRAINT [end_cityID_fk] FOREIGN KEY ([destination_cityID]) REFERENCES [dbo].[Cities] ([cityID]),
    CONSTRAINT [start_cityID_fk] FOREIGN KEY ([starting_cityID]) REFERENCES [dbo].[Cities] ([cityID])
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>offerID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>starting_cityID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>destination_cityID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
</table>

### Tours


Tour jest to tabela zawierająca szczególowy opis wycieczki. Zawiera informacje o dacie rozpoczęcia i końca, limicie uczestików, cenie oraz pracowniku, który jest "patronem" wycieczki. Tabela zawiera również klucz obcy offerID. Celem takiego rozwiącania jest to, że dla jednej offer możne być wiele Tour w różnych datach (czy nawet z innymi przewoźnikami, innym patronem wycieczki czy innym limitem osób). Kluczem główny to atomatycznie generowany tourID

**tourID: klucz główny
offerID: klucz obcy łączący się z tabelą offer
carrierID: klucz obcy łączący się z tabelą carrier
employee_supervisor: klucz obcy łączący się z tabelą employee
start_date: data początku
end_date: data końca
max_participants: maksymalna liczba uczestników

CREATE TABLE [dbo].[Tours] (
    [tourID]             INT      NOT NULL,
    [offerID]            INT      NOT NULL,
    [start_date]         DATETIME NOT NULL,
    [end_date]           DATETIME NOT NULL,
    [participants_limit] INT      NOT NULL,
    [carrierID]          INT      NOT NULL,
    [price]              MONEY    NOT NULL,
    [tour_supervisor]    INT      NOT NULL,
    CONSTRAINT [TourID] PRIMARY KEY CLUSTERED ([tourID] ASC),
    CONSTRAINT [Tour_participants_limit] CHECK (NOT [participants_limit] like '%[^0-9]%'),
    CONSTRAINT [Tour_price] CHECK (NOT [price] like '%[^0-9.]%'),
    CONSTRAINT [FK_Tour_employee] FOREIGN KEY ([tour_supervisor]) REFERENCES [dbo].[Employees] ([employeeID]),
    CONSTRAINT [Tour_Offer_offerID_fk] FOREIGN KEY ([offerID]) REFERENCES [dbo].[Offers] ([offerID])
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>tourID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>offerID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>carrierID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>tour_supervisor</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>price</td>
<td></td>
<td><code>NOT [price] like '%[^0-9.]%'</code></td>
<td></td>
</tr>
<tr>
<td>participants_limit</td>
<td></td>
<td><code>NOT [participants_limit] like '%[^0-9]%'</code></td>
<td></td>
</tr>
<tr>
<td>start_date</td>
<td></td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>end_date</td>
<td></td>
<td><code></code></td>
<td></td>
</tr>
</table>

### Carriers


Słownik, który przechowuje nazwę przewoźnika i odpowiadające mu ID (klucz główny)

**carrierID: klucz główny
carrier_name: nazwa przewoźnika**

CREATE TABLE [dbo].[Carriers] (
    [carrierID]    INT           NOT NULL,
    [carrier_name] VARCHAR (100) NOT NULL,
    CONSTRAINT [carrierID_pk] PRIMARY KEY CLUSTERED ([carrierID] ASC),
    CONSTRAINT [carriers_name ] CHECK ([carrier_name] like '%[a-zA-Z_- . ]%'),
    CONSTRAINT [Carriers_uk] UNIQUE NONCLUSTERED ([carrier_name] ASC)
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>carrierID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>carrier_name</td>
<td></td>
<td><code>[carrier_name] like '%[a-zA-Z_- . ]%'</code></td>
<td>yes</td>
</tr>
</table>

### Reservations


Rezerwacji jast przypisana do użytkownika z kontem, który ją składał poprzez klucz obcy accountID. Użytkownik rezerwuje tour a nie offer (kolejny klucz obcy - tourID). Ważne jest to, że użytkownik może złożyć rezerwację nie tylko dla siebie (wszystkie osoby są umieszczane w tabeli participants). Stąd też kolumna summary_price oraz reserved_seats. Oczywiście tabela zawiera też automatycznie generowany klucz główny reservationID.

CREATE TABLE [dbo].[Reservations] (
    [reservationID]  INT NOT NULL,
    [accountID]      INT NOT NULL,
    [tourID]         INT NOT NULL,
    [reserved_seats] INT NOT NULL,
    CONSTRAINT [reservationID_pk] PRIMARY KEY CLUSTERED ([reservationID] ASC),
    CONSTRAINT [Reservation_reserved_seats] CHECK (NOT [reserved_seats] like '%[^0-9]%'),
    CONSTRAINT [accountID_fk] FOREIGN KEY ([accountID]) REFERENCES [dbo].[Accounts] ([accountID]),
    CONSTRAINT [tourID___fk] FOREIGN KEY ([tourID]) REFERENCES [dbo].[Tours] ([tourID])
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>reservationID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>accountID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>tourID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>reserved_seats</td>
<td></td>
<td><code>NOT [reserved_seats] like '%[^0-9]%'</code></td>
<td></td>
</tr>
</table>

### Participants


Dokładne dane uczestników zgłoszonych przy rezerwacji przechowywane są w tabeli participants (tabela participants łączy się z tabelą reservation po kluczy obcym reservation ID w sposób wiele do 1). Tabela przechowuje dane użytkowników tj. imię, nazwisko oraz data urodzenia (zamiast peselu ze względu na RODO). Oczywiście tabela zawiera też automatycznie generowany klucz główny participantsID.

<b>
- participantID: pole typu int, klucz główny, autoinkrementowany. Jednoznacznie wskazuje na danego uczestnika
- reservationID: pole typu int, klucz obcy, wskazuje na rekord w tabeli reservation, pokazuje z jakiej rezerwacji pochodzi dany uczestnik.
- names: pole typu varchar, zawiera imiona uczestnika, służące do zidentyfikowania osoby
- surname: pole typu varchar, zawiera nazwisko uczestnika, służące do zidentyfikowania osoby
</b>

CREATE TABLE [dbo].[Participants] (
    [participantID] INT          NOT NULL,
    [names]         VARCHAR (30) NOT NULL,
    [surname]       VARCHAR (30) NOT NULL,
    [birth_date]    DATE         NOT NULL,
    [reservationID] INT          NOT NULL,
    CONSTRAINT [PK_Participant] PRIMARY KEY CLUSTERED ([participantID] ASC),
    CONSTRAINT [Participant_first_name] CHECK (NOT [names] like '%[^a-z]%'),
    CONSTRAINT [Participant_last_name] CHECK (NOT [surname] like '%[^a-z]%'),
    CONSTRAINT [reservationID_fk] FOREIGN KEY ([reservationID]) REFERENCES [dbo].[Reservations] ([reservationID])
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>participantID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>reservationID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>names</td>
<td></td>
<td><code>NOT [names] like '%[^a-z]%'</code></td>
<td></td>
</tr>
<tr>
<td>surname</td>
<td></td>
<td><code>NOT [surname] like '%[^a-z]%'</code></td>
<td></td>
</tr>
<tr>
<td>birth_date</td>
<td></td>
<td><code></code></td>
<td></td>
</tr>
</table>

## Atrakcje

### Attractions


Nazwy atrakcji i klucze im przypisane przechowywane są w słowniku Attractions

<b>
- attractionID: klucz główny, autoinkrementowane pole typu int, identyfikuje daną atrakcję
- attraction_name: pole typu varchar, zawiera nazwę atrakcji
</b>

CREATE TABLE [dbo].[Attractions] (
    [attractionID]    INT           NOT NULL,
    [attraction_name] VARCHAR (100) NOT NULL,
    CONSTRAINT [PK_Attractions] PRIMARY KEY CLUSTERED ([attractionID] ASC),
    CONSTRAINT [Attraction_attraction] CHECK (NOT [attraction_name] like '%[^a-zA-Z ]%'),
    CONSTRAINT [Attraction_pk] UNIQUE NONCLUSTERED ([attraction_name] ASC)
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>attractionID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>attraction_name</td>
<td></td>
<td><code>NOT [attraction_name] like '%[^a-zA-Z ]%'</code></td>
<td>yes</td>
</tr>
</table>

### Tour Attractions


Więcej informacji o atrakcjach czyli do jakiego touru są przypisane (poprzez klucz obcy-tourID), ile maksymalnie uczestników może wziąć udział w danej atrakcji oraz ile kosztuje przechowywane są w tabeli Tour attractions.

**tour_attractionID: klucz główny
tourID: klucz obcy łączący się z tabelą tour
attractionID: klucz obcy łączący się ze słownikiem attraction
max_participants: maksymalna liczba uczestników
base_price: cena bazowa
price_per_partcipants: cena za osobę**

CREATE TABLE [dbo].[TourAttractions] (
    [tour_attractionID]     INT   NOT NULL,
    [tourID]                INT   NOT NULL,
    [attractionID]          INT   NOT NULL,
    [max_participants]      INT   NOT NULL,
    [price_per_participant] MONEY NOT NULL,
    CONSTRAINT [PK_Tour Attactions] PRIMARY KEY CLUSTERED ([tour_attractionID] ASC),
    CONSTRAINT [TourAttractions_max_participants] CHECK (NOT [max_participants] like '%[^0-9]%'),
    CONSTRAINT [TourAttractions_price_per_participant ] CHECK (NOT [price_per_participant] like '%[^0-9.]%'),
    CONSTRAINT [attractionID_fk] FOREIGN KEY ([attractionID]) REFERENCES [dbo].[Attractions] ([attractionID]),
    CONSTRAINT [TourID_fk] FOREIGN KEY ([tourID]) REFERENCES [dbo].[Tours] ([tourID])
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>tour_attractionID</td>
<td>PK</td>
<td><code></code></td>
<td>yes</td>
</tr>
<tr>
<td>attractionID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>tourID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>max_participants</td>
<td></td>
<td><code>[max_participants] like '%[^0-9]%'</code></td>
<td></td>
</tr>
<tr>
<td>price_per_participant</td>
<td></td>
<td><code>[price_per_participant] like '%[^0-9.]%'</code></td>
<td></td>
</tr>
</table>

### Reserved Attractions


Informacje o tym kto zarezerwował jaką atrakcję przechowywane są w tabeli Reserved Attractions. Tabela zawiera dwa klucze obce: tour_attractionID oraz participantID.

**tour_attractoinID: klucz główny i obcy łączący się z tabelą Tour Attractions
participantID:klucz główny i obcy łączący się z tabelą participants**

CREATE TABLE [dbo].[ReservedAttractions] (
    [tour_attractionID] INT NOT NULL,
    [participantID]     INT NOT NULL,
    CONSTRAINT [PK_ReservedAttraction] PRIMARY KEY CLUSTERED ([tour_attractionID] ASC, [participantID] ASC),
    CONSTRAINT [participantID_fk] FOREIGN KEY ([participantID]) REFERENCES [dbo].[Participants] ([participantID]),
    CONSTRAINT [tour_attractionID_fk] FOREIGN KEY ([tour_attractionID]) REFERENCES [dbo].[TourAttractions] ([tour_attractionID])
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>participantID</td>
<td>PK/FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>tour_attractionID</td>
<td>PK/FK</td>
<td><code></code></td>
<td></td>
</tr>
</table>

### Payments


Tabela przechowuje informację o płatności za rezerwację oraz datę jej uiszczenia.

**paymentID: klucz główny do indeksowania płatności
reservationID: klucz obcy określający jakiej rezerwacji dotyczy płatność
amount_paid: wartość typu money określający kwotę wpłaty
payment_date: data płatności typu date**

CREATE TABLE [dbo].[Payments] (
    [paymentID]     INT   NOT NULL,
    [reservationID] INT   NOT NULL,
    [amount_paid]   MONEY NOT NULL,
    [payment_date]  DATE  NOT NULL,
    CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED ([paymentID] ASC),
    CONSTRAINT [Payments_amount_paid] CHECK ([amount_paid]>(0)),
    CONSTRAINT [FK_Payments_Reservation] FOREIGN KEY ([reservationID]) REFERENCES [dbo].[Reservations] ([reservationID])
);

#### Warunki integralności

<table>
<tr>
<th>Pole</th>
<th>Key</th>
<th>Check</th>
<th>Unique</th>
</tr>
<tr>
<td>paymentID</td>
<td>PK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>reservationID</td>
<td>FK</td>
<td><code></code></td>
<td></td>
</tr>
<tr>
<td>amount_paid</td>
<td></td>
<td><code>[amount_paid]>(0)</code></td>
<td></td>
</tr>
<tr>
<td>payment_date</td>
<td></td>
<td><code></code></td>
<td></td>
</tr>
</table>

# Widoki

## Login backend - Dominik Motyl

SELECT hash, salt, lower(concat(names,'.',surname)) AS username, email AS email, phone FROM passwords p
JOIN Employees e ON p.passwordID = e.passwordID
UNION
SELECT hash, salt, username, email_address AS email, phone FROM passwords p
JOIN Accounts a ON p.passwordID = a.passwordID

## Reservation view - Dominik Motyl

SELECT username, email_address AS email, phone, names, surname, address, postal_code, birth_date, company_name, NIP, r.tourID, r.reserved_seats FROM Accounts a
LEFT JOIN dbo.PersonalClients pc ON a.accountID = pc.accountID
LEFT JOIN dbo.CompanyClients cc ON a.accountID = cc.accountID
LEFT JOIN Reservations r ON a.accountID = r.accountID

## Tour view - Dominik Motyl

SELECT t.tourID, email, start_date, end_date, participants_limit, max_participants AS [max_participants_per_attraction], reserved_seats AS [reserved_tour_seats], price, price_per_participant, attraction_name, carrier_name FROM Tours t
JOIN TourAttractions ta ON t.tourID = ta.tourID
JOIN Attractions a ON ta.attractionID = a.attractionID
JOIN Reservations r ON t.tourID = r.tourID
JOIN ReservedAttractions ra ON ta.tour_attractionID = ra.tour_attractionID
JOIN Carriers c ON t.carrierID = c.carrierID
JOIN Employees e ON t.tour_supervisor = e.employeeID

## Website view - Dominik Motyl

SELECT cs.city_name AS [Start],
           ce.city_name AS [End],
           t.participants_limit,
           t.price,
           ca.carrier_name,
           em.email
    FROM Tours t
             JOIN Offers o ON t.offerID = o.offerID
             JOIN Cities cs ON o.starting_cityID = cs.cityID
             JOIN Cities ce ON o.destination_cityID = ce.cityID
             JOIN Carriers ca ON t.carrierID = ca.carrierID
             JOIN Employees em ON t.tour_supervisor = em.employeeID

## Reservation payment details - Michał Zięba

SELECT r.reservationID,
           (isnull(cena_atrakcji, 0))                                    as cena_atrakcji,
           price                                                         as cena_wycieczki,
           (isnull(cena_atrakcji, 0) + price)                            as cena_laczna,
           isnull(P2.amount_paid, 0)                                     as kwota_zaplacona,
           (isnull(amount_paid, 0) - (isnull(cena_atrakcji, 0) + price)) as 'nadplata/niedoplata'
    from Reservations r
             left join
         (Select p.reservationID, SUM(price_per_participant) as cena_atrakcji
          from Participants p
                   join dbo.ReservedAttractions RA on p.participantID = RA.participantID
                   join dbo.TourAttractions TA on RA.tour_attractionID = TA.tour_attractionID
          group by p.reservationID)
             as pom2 on r.reservationID = pom2.reservationID
             join
         (select r.reservationID, (reserved_seats * price) as price
          from Reservations r
                   join dbo.Tours t on r.tourID = t.tourID
          group by r.reservationID, (reserved_seats * price)) as pom on r.reservationID = pom.reservationID
             left join dbo.Payments P2 on r.reservationID = P2.reservationID
    group by r.reservationID, cena_atrakcji, price, (cena_atrakcji + price), amount_paid

## Company Clients Reservation - Mikołaj Kraczek

select P.reservationID as 'participantID', P. names as 'participant_name', 
P.surname as 'participant_surname', R.accountID as 'bookerID',
CC.company_name as 'booker_name', CC.NIP as 'booker_NIP', T.tourID
from CompanyClients CC join dbo.Reservations R on CC.accountID = R.accountID
join Participants P on R.reservationID = P.reservationID
join Tours T on R.tourID = T.tourID

## Personal Clients Reservation - Mikołaj Kraczek

select P.reservationID as 'participantID', P. names as 'participant_name', 
P.surname as 'participant_surname', R.accountID as 'bookerID', 
PC.names as 'booker_name', PC.surname as 'booker_surname', T.tourID
from PersonalClients PC join dbo.Reservations R on PC.accountID = R.accountID
join Participants P on R.reservationID = P.reservationID
join Tours T on R.tourID = T.tourID

## Wyświetlanie danych uczestnika oraz jego atrakcji - Tomasz Leniart

CREATE VIEW [dbo].[ParticipantsAttractionsInReservations] AS
SELECT 
    r.tourID as Tour,
    r.reservationID as Reservation,
    p.participantID as Participant,
    p.names as Names,
    p.surname as Surname,
    p.birth_date as [Birth date],
    ta.tour_attractionID
FROM 
    dbo.Tours t
    JOIN dbo.Reservations r ON t.tourID = r.tourID
    JOIN dbo.Participants p ON r.reservationID = p.reservationID
    JOIN dbo.ReservedAttractions ra ON p.participantID = ra.participantID
    JOIN dbo.TourAttractions ta ON ra.tour_attractionID = ta.tour_attractionID

# Funkcje

## CountParticipant - Mikołaj Kraczek

CREATE FUNCTION CountParticipants(
    @FirstName VARCHAR(30),
    @LastName VARCHAR(30),
    @BirthDate DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @Count INT;

    SELECT @Count = COUNT(*)
    FROM Participants
    WHERE names = @FirstName
    AND surname = @LastName
    AND birth_date = @BirthDate;

    RETURN @Count;
END;

## Dodaje do daty 2 tygodnie - Dominik Motyl

CREATE FUNCTION twoWeeksLater (@d DATETIME)
RETURNS DATETIME
AS
BEGIN
    DECLARE @result DATETIME;
    SET @result = DATEADD(WEEK, 2, @d);
    RETURN @result;
END;

## Wyswietlenie ilosci rezerwacji po ID konta - Michał Zięba

CREATE FUNCTION [dbo].[GetReservationCountByAccount] (@AccountID int)
RETURNS int
AS
BEGIN
    DECLARE @ReservationCount int;
    SELECT @ReservationCount = COUNT(*)
    FROM Reservations
    WHERE accountID = @AccountID;

    RETURN @ReservationCount;
END

## Wyswietlenie sumy wplat po ID konta - Michał Zięba

CREATE FUNCTION [dbo].[GetTotalAmountPaidByAccount] (@AccountID int)
RETURNS money
AS
BEGIN
    DECLARE @TotalAmountPaid money;

    SELECT @TotalAmountPaid = SUM(ISNULL(p.amount_paid, 0))
    FROM Reservations r
    LEFT JOIN Payments p ON r.reservationID = p.reservationID
    WHERE r.accountID = @AccountID;

    RETURN @TotalAmountPaid;
END

## Dla podanego NIP i ReservationID wyświetla ilość członków - Tomasz Leniart

CREATE FUNCTION dbo.GetParticipantCountByNIPAndReservation (
    @NIP VARCHAR(10),
    @reservationID INT
)
RETURNS INT
AS
BEGIN
    DECLARE @accountID INT;
    DECLARE @participantCount INT;

    SELECT @accountID = accountID
    FROM dbo.CompanyClients
    WHERE NIP = @NIP;

    IF EXISTS (
        SELECT 1
        FROM dbo.Reservations
        WHERE reservationID = @reservationID
        AND accountID = @accountID
    )
    BEGIN
        SELECT @participantCount = COUNT(*)
        FROM dbo.Participants
        WHERE reservationID = @reservationID;
    END
    ELSE
    BEGIN
        SET @participantCount = 0;
    END

    RETURN @participantCount;
END;

# Procedury
## Dodawanie rezerwacji - Mikołaj Kraczek

CREATE PROCEDURE AddReservation
    @reservationID INT,
    @accountID INT,
    @tourID INT,
    @reservedSeats INT
AS
BEGIN
    -- Wstawienie nowego rekordu do tabeli Reservations
    INSERT INTO Reservations (reservationID, accountID, tourID, reserved_seats)
    VALUES (@reservationID, @accountID, @tourID, @reservedSeats);

    PRINT 'Reservation added successfully';
END;

## Dodawanie participanta - Mikołaj Kraczek

CREATE PROCEDURE AddParticipant
    @participantID INT,
    @reservationID INT,
    @name VARCHAR(100),
    @surname VARCHAR(100),
    @birth_date DATE
AS
BEGIN
    -- Wstawienie nowego rekordu do tabeli Participants
    INSERT INTO Participants (participantID, reservationID, names, surname, birth_date)
    VALUES (@participantID, @reservationID, @name, @surname, @birth_date);

    PRINT 'Participant added successfully';
END;

## Dodawanie rekordu w tabeli Passwords - Dominik Motyl

CREATE PROCEDURE dbo.AddPasswordRecord
    @passwordID INT,
    @hash VARCHAR(100),
    @salt VARCHAR(100)
AS
BEGIN
    INSERT INTO dbo.Passwords (passwordID, hash, salt)
    VALUES (@passwordID, @hash, @salt);
END;
GO

## Dodawanie rekordu do tabeli Reserved Attraction - Michał Zięba

CREATE PROCEDURE AddReservedAttraction
    @tour_attractionID INT,
    @participantID INT
AS
BEGIN
    -- Wstawienie nowego rekordu do tabeli reserved_attractions
    INSERT INTO reserved_attractions (tour_attraction_ID, participant_ID)
    VALUES (@tourAttractionID, @participantID);

    PRINT 'Reserved attraction added successfully';
END;

## Wyświetlanie tours dla przedziału cenowego - Mikołaj Kraczek

CREATE PROCEDURE GetToursByPriceRange
    @MinPrice MONEY,
    @MaxPrice MONEY
AS
BEGIN
    SELECT tourID, offerID, start_date, end_date, price
    FROM Tours
    WHERE price BETWEEN @MinPrice AND @MaxPrice;
END;

## Wypisuje Atrakcje dla danej rezerwacji - Dominik Motyl

ALTER PROCEDURE printAttraction @reservationID int
AS
SELECT a.attraction_name, count(ra.participantID) as participantsNumber, sum(price_per_participant) as summaryPrice
FROM Participants p
JOIN ReservedAttractions ra ON p.participantID = ra.participantID
JOIN TourAttractions ta ON ra.tour_attractionID = ta.tour_attractionID
JOIN Attractions a ON ta.attractionID = a.attractionID
WHERE p.reservationID = @reservationID GROUP BY attraction_name
GO;

EXEC printAttraction @reservationID = 2

## Wyswietlenie uczestnikow urodzonych w przedziale dat - Michał Zięba

CREATE PROCEDURE [dbo].[GetParticipantsByBirthDate]
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SELECT
        participantID,
        names,
        surname,
        birth_date,
        reservationID
    FROM
        Participants
    WHERE
        birth_date BETWEEN @StartDate AND @EndDate
    ORDER BY
        birth_date;
END;

## Wyświetlanie wycieczek, które dopiero się odbędą dla danego przewodnika - Tomasz Leniart

USE [u_mizieba];
GO

CREATE PROCEDURE GetUpcomingToursByCarrier
    @CarrierName VARCHAR(100)
AS
BEGIN
    -- Get the current date and time
    DECLARE @CurrentDateTime DATETIME;
    SET @CurrentDateTime = GETDATE();

    SELECT
        t.tourID,
        t.offerID,
        t.start_date,
        t.end_date,
        t.participants_limit,
        t.price,
        t.tour_supervisor
    FROM
        dbo.Tours t
    INNER JOIN
        dbo.Carriers c ON t.carrierID = c.carrierID
    WHERE
        c.carrier_name = @CarrierName
        AND t.start_date > @CurrentDateTime;
END;
GO

## Dodawanie rekordu do tabel Accounts i PersonalClient/CompanyClient - Tomasz Leniart

GO
CREATE PROCEDURE AddClientRecord
    @accountID INT,
    @Email VARCHAR(60),
    @Username VARCHAR(40),
    @Phone VARCHAR(19),
    @PasswordID INT,
    @ClientType CHAR(1), -- 'P' for PersonalClient, 'C' for CompanyClient
    @CityID INT = NULL,
    @Names VARCHAR(50) = NULL,
    @Surname VARCHAR(50) = NULL,
    @Address VARCHAR(200) = NULL,
    @PostalCode VARCHAR(6) = NULL,
    @BirthDate DATE = NULL,
    @CompanyName VARCHAR(150) = NULL,
    @NIP VARCHAR(10) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Start transaction
        BEGIN TRANSACTION;

        -- Insert into Accounts table
        INSERT INTO dbo.Accounts (accountID, email_address, username, phone, passwordID)
        VALUES (@accountID, @Email, @Username, @Phone, @PasswordID);

        -- Insert into PersonalClients or CompanyClients based on ClientType
        IF @ClientType = 'P'
        BEGIN
            -- Insert into PersonalClients table
            INSERT INTO dbo.PersonalClients (accountID, cityID, names, surname, address, postal_code, birth_date)
            VALUES (@accountID, @CityID, @Names, @Surname, @Address, @PostalCode, @BirthDate);
        END
        ELSE IF @ClientType = 'C'
        BEGIN
            -- Insert into CompanyClients table
            INSERT INTO dbo.CompanyClients (accountID, company_name, NIP)
            VALUES (@accountID, @CompanyName, @NIP);
        END
        ELSE
        BEGIN
            -- Raise an error if ClientType is not valid
            THROW  5, 'Invalid ClientType. Use "P" for PersonalClient or "C" for CompanyClient.', 1;
        END

        -- Commit transaction
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback transaction if any error occurs
        ROLLBACK TRANSACTION;

        -- Raise the error
        THROW;
    END CATCH;
END
GO

# Triggery

## Próbujemy zapodiegać duplikowniu się haseł - Dominik Motyl

CREATE TRIGGER trg_PreventDuplicatePassword
ON dbo.Passwords
INSTEAD OF INSERT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM dbo.Passwords p
               INNER JOIN inserted i ON p.hash = i.hash)
    BEGIN
        RAISERROR('Duplicate hash value is not allowed.', 16, 1)
        ROLLBACK TRANSACTION
        RETURN
    END
    IF EXISTS (SELECT 1 FROM dbo.Passwords p
               INNER JOIN inserted i ON p.salt = i.salt)
    BEGIN
        RAISERROR('Duplicate salt value is not allowed.', 16, 1)
        ROLLBACK TRANSACTION
        RETURN
    END
    INSERT INTO dbo.Passwords (passwordID, hash, salt)
    SELECT passwordID, hash, salt
    FROM inserted
END

## Sprawdzanie ilości wolnych miejsc - Mikołaj Kraczek

CREATE TRIGGER before_insert_reservations ON Reservations
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @tourID INT;
    DECLARE @reservedSeats INT;
    DECLARE @totalReserved INT;
    DECLARE @maxSeats INT;

    SELECT @tourID = tourID, @reservedSeats = reserved_seats
    FROM INSERTED;
    SELECT @totalReserved = ISNULL(SUM(reserved_seats), 0)
    FROM Reservations
    WHERE tourID = @tourID;
    SELECT @maxSeats = participants_limit
    FROM Tours
    WHERE tourID = @tourID;
    IF @totalReserved + @reservedSeats > @maxSeats
    BEGIN
        RAISERROR ('Not enough seats available for this tour.', 16, 1);
    END
    ELSE
    BEGIN
        INSERT INTO Reservations (reservationID, accountID, tourID, reserved_seats)
        SELECT reservationID, accountID, tourID, reserved_seats
        FROM INSERTED;
    END
END;

## Sprawdzenie przed dodaniem zarezerwowanej atrakcji czy jest miejsce - Michał Zięba

CREATE TRIGGER CheckMaxParticipants
ON ReservedAttractions
INSTEAD OF INSERT
AS
BEGIN
    IF EXISTS (
        SELECT
            RA.tour_attractionID,
            COUNT(*) AS current_participants,
            TA.max_participants
        FROM
            ReservedAttractions RA
            INNER JOIN TourAttractions TA ON RA.tour_attractionID = TA.tour_attractionID
        WHERE
            RA.tour_attractionID IN (SELECT tour_attractionID FROM inserted)
        GROUP BY
            RA.tour_attractionID, TA.max_participants
        HAVING
            COUNT(*) + (SELECT COUNT(*) FROM inserted WHERE tour_attractionID = RA.tour_attractionID) > TA.max_participants
    )
    BEGIN
        RAISERROR('Nie można dodać uczestnika do tej atrakcji - przekroczono maksymalną liczbę miejsc.', 16, 1);
    END
    ELSE
    BEGIN
        INSERT INTO ReservedAttractions (tour_attractionID, participantID)
        SELECT tour_attractionID, participantID FROM inserted;
    END
END;

## Usuwanie w tabelach PersonalClients/CompanyClients rekordu o tym samym id, gdy usunięty jest rekord w tabeli Accounts - Tomasz Leniart

CREATE TRIGGER trg_DeleteAccount
ON dbo.Accounts
AFTER DELETE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM deleted d INNER JOIN dbo.PersonalClients pc ON d.accountID = pc.accountID)
    BEGIN
        DELETE FROM dbo.PersonalClients 
        WHERE accountID IN (SELECT accountID FROM deleted);
    END

    IF EXISTS (SELECT 1 FROM deleted d INNER JOIN dbo.CompanyClients cc ON d.accountID = cc.accountID)
    BEGIN
        DELETE FROM dbo.CompanyClients 
        WHERE accountID IN (SELECT accountID FROM deleted);
    END
END;