--
-- SAFE ZONE
--

-- dodaj usera (bez karty)
insert into Users(Name, Surname, PersonalId) values ('Jan', 'Kowalski', '<numer dokumentu>');

-- pokaż userów
select * from Users;

-- pokaż karty
select * from TransportCards
  join CardTypeDictionaries CTD on TransportCards.CardType = CTD.CardTypeId
;

-- pokaż userów i ich karty
select User_Id, Name, Surname, PersonalId, IsActive, Description from Users
  inner join TransportCards TC on Users.Id = TC.User_Id
  inner join CardTypeDictionaries CTD on TC.CardType = CTD.CardTypeId
;

-- pokaż typy biletów
select TPD.Description as "Time", DTD.Description as "Discount", ZD.Description as "Zone"
from TicketTypes
  inner join TicketPeriodDictionaries TPD on TicketTypes.TicketPeriod = TPD.TicketPeriodId
  inner join ZoneDictionaries ZD on TicketTypes.Zone = ZD.ZoneId
  inner join DiscountTypeDictionaries DTD on TicketTypes.DiscountType = DTD.DiscountTypeId
;

-- pokaż transakcje
select * from Transactions order by Id;

-- pokaż ilość biletów w każdej transakcji
select
       Transaction_Id as "Transaction ID",
       count(Transaction_Id) as "Number of tickets"
from Tickets
group by Transaction_Id;

-- pokaż bilety
select * from Tickets;

-- pokaz bilety papierowe
select Tickets.Id,
       IssuedPrice as "Issued price (in PLN)",
       TPD.Description as "Time",
       ZD.Description as "Zone",
       DPD.Description as "Discount type",
       T.Date as "Transaction date"
from Tickets
inner join Transactions T on Tickets.Transaction_Id = T.Id
inner join TicketTypes TT on Tickets.TicketType_Id = TT.Id
inner join ZoneDictionaries ZD on TT.Zone = ZD.ZoneId
inner join TicketPeriodDictionaries TPD on TPD.TicketPeriodId = TT.TicketPeriod
inner join DiscountTypeDictionaries DPD on DPD.DiscountTypeId = TT.DiscountType
where Card_Id is null
order by Tickets.Id
;

-- pokaz bilety elektroniczne
select Tickets.Id,
       IssuedPrice as "Issued price (in PLN)",
       TPD.Description as "Time",
       ZD.Description as "Zone",
       DPD.Description as "Discount type",
       ValidFromDate as "From",
       ValidToDate as "To"
from Tickets
inner join TicketTypes TT on Tickets.TicketType_Id = TT.Id
inner join ZoneDictionaries ZD on TT.Zone = ZD.ZoneId
inner join TicketPeriodDictionaries TPD on TPD.TicketPeriodId = TT.TicketPeriod
inner join DiscountTypeDictionaries DPD on DPD.DiscountTypeId = TT.DiscountType
where Card_Id is not null
order by Card_Id
;

-- pokaż transakcje i wszystkie bilety
select Tickets.Id as "Ticket ID",
       T.Id as "Transaction ID",
       TPD.Description as "Ticket period",
       ZD.Description as " Zone ",
       DTD.Description as "Discount type"
from Tickets
  inner join Transactions T on T.Id = Tickets.Transaction_Id
  inner join TicketTypes TT on Tickets.TicketType_Id = TT.Id
  inner join TicketPeriodDictionaries TPD on TT.TicketPeriod = TPD.TicketPeriodId
  inner join ZoneDictionaries ZD on TT.Zone = ZD.ZoneId
  inner join DiscountTypeDictionaries DTD on TT.DiscountType = DTD.DiscountTypeId
where TPD.Description in ('Month', 'Three months')
order by T.Id, TPD.Description
;

-- ile biletów łącznie
select
  count(*) as "Total number of tickets",
  (select count(*) from Tickets where Card_Id is not null) as "Number of card tickets",
  (select count(*) from Tickets where Card_Id is null) as "Number of paper tickets"
from Tickets
;

-- ile userów łącznie
select count(*) as "Number of users" from Users;

-- status bazy
select
  (select count(*) from Users) as "Number of users",
  (select count(*) from TransportCards) as "Number of cards",
  (select count(*) from Tickets) as "Total number of tickets",
  (select count(*) from Tickets where Card_Id is not null) as "Number of card tickets",
  (select count(*) from Tickets where Card_Id is null) as "Number of paper tickets",
  (select count(*) from Transactions) as "Number of transactions"
;

--
-- DANGER ZONE
--

-- usuń wszystkie bilety elektroniczne
delete from Tickets where Card_Id is not null;

-- zaoraj tabelki
begin transaction;
  delete from Tickets;
  delete from TransportCards;
  delete from Users;
  delete from TicketTypes;
  delete from Transactions;
commit;