CREATE DATABASE Airport

CREATE TABLE Planes(
                    [Id] INT PRIMARY KEY IDENTITY(1,1)  ,
                    [Name] VARCHAR(30) NOT NULL,
                    [Seats]INT NOT NULL ,
                    [Range] INT NOT NULL
                    )

CREATE TABLE Flights(
                    [Id] INT PRIMARY KEY IDENTITY (1,1),
                    [DepartureTime] DATETIME ,
                    [ArrivalTime] DATETIME,
                    [Origin] VARCHAR(50) NOT NULL ,
                    [Destination] VARCHAR(50) NOT NULL,
                    [PlaneId] INT FOREIGN KEY REFERENCES Planes([Id])
                    )


CREATE TABLE Passengers(
                    [Id] INT PRIMARY KEY IDENTITY (1,1),
                    [FirstName] VARCHAR(30) NOT NULL,
                    [LastName] VARCHAR(30) NOT NULL,
                    [Age] INT NOT NULL,
                    [Address] VARCHAR(30) NOT NULL ,
                    [PassportId] VARCHAR(11) NOT NULL
                    )


CREATE TABLE LuggageTypes(
                    [Id] INT PRIMARY KEY IDENTITY (1,1),
                    [Type] VARCHAR(30) NOT NULL,
                    )


CREATE TABLE Luggages(
                    [Id] INT PRIMARY KEY IDENTITY (1,1),
                    [LuggageTypeId] INT FOREIGN KEY REFERENCES LuggageTypes([Id]) NOT NULL ,
                    [PassengerId] INT FOREIGN KEY REFERENCES Passengers([Id]) NOT NULL,
                    )


CREATE TABLE Tickets(
                    [Id] INT PRIMARY KEY IDENTITY (1,1),
                    [PassengerId] INT FOREIGN KEY REFERENCES Passengers([Id]) NOT NULL,
                    [FlightId] INT FOREIGN KEY REFERENCES Flights([Id]) NOT NULL,
                    [LuggageId] INT FOREIGN KEY REFERENCES Luggages([Id]) NOT NULL,
                    [Price] DECIMAL(15,2)NOT NULL
                    )




INSERT INTO Planes([Name] , [Seats], [Range])
                VALUES
                ('Airbus 336', 112, 5132),
                ('Airbus 330', 432, 5325),
                ('Boeing 369', 231, 2355),
                ('Stelt 297', 254, 2143),
                ('Boeing 338', 165, 5111),
                ('Airbus 558', 387, 1342),
                ('Boeing 128', 345, 5541)

INSERT INTO LuggageTypes([Type])
                VALUES
                ('Crossbody Bag'),
                ('School Backpack'),
                ('Shoulder Bag')


UPDATE t
SET t.[Price] = t.[Price] * 1.13
FROM Tickets AS t
JOIN Flights AS f ON f.[Id] = t.[FlightId]
WHERE f.[Destination] = 'Carlsbad'

--DELETE
DELETE FROM Tickets
WHERE FlightId = (SELECT TOP(1) Id FROM Flights WHERE Destination = 'Ayn Halagim')
  DELETE FROM Flights
WHERE Destination = 'Ayn Halagim'


SELECT [Origin], [Destination]
FROM Flights
ORDER BY [Origin] ASC , [Destination] DESC


SELECT [Id], [Name], [Seats], [Range]
FROM Planes
WHERE [Name] LIKE '%tr%'
ORDER BY [Id] ASC , [Name] ASC , [Seats] ASC , [Range] ASC


SELECT FlightId , SUM(Price) AS [Price]
FROM Tickets
GROUP BY FlightId
ORDER BY SUM([Price]) DESC , [FlightId] ASC


SELECT TOP(10) p.[FirstName], p.[LastName], t.[Price]
FROM Tickets AS t
JOIN Passengers AS p ON p.[Id] = t.[PassengerId]
ORDER BY t.[Price] DESC , p.[FirstName] ASC , p.[LastName] ASC


SELECT lt.[Type] , COUNT(lt.Id) AS MostUsedLuggage
FROM Luggages AS l
JOIN LuggageTypes AS lt ON lt.[Id] = l.[LuggageTypeId]
GROUP BY lt.[Type]
ORDER BY MostUsedLuggage DESC, lt.[Type]

SELECT p.FirstName + ' ' + p.LastName AS [Full Name],
           f.Origin,
           f.Destination
      FROM Passengers AS p
      JOIN Tickets AS t ON t.PassengerId = p.Id
      JOIN Flights AS f ON f.Id = t.FlightId
    ORDER BY [Full Name], f.Origin, f.Destination


    SELECT p.FirstName, p.LastName, p.Age
      FROM Passengers AS p
 LEFT JOIN Tickets AS t ON t.PassengerId = p.Id
     WHERE t.Id IS NULL
  ORDER BY p.Age DESC, p.FirstName, p.LastName


 SELECT p.PassportId, p.Address
     FROM Passengers AS p
LEFT JOIN Luggages AS l ON l.PassengerId = p.Id
    WHERE l.Id IS NULL
 ORDER BY p.PassportId, p.Address


 SELECT p.FirstName, p.LastName, COUNT(t.Id) AS TotalTrips
     FROM Passengers AS p
LEFT JOIN Tickets AS t ON t.PassengerId = p.Id
 GROUP BY p.FirstName, p.LastName
 ORDER BY TotalTrips DESC, p.FirstName, p.LastName


 SELECT p.FirstName + ' ' + p.LastName AS [Full Name],
          pl.Name AS [Plane Name],
          f.Origin + ' - ' + f.Destination AS [Trip],
          lt.Type As [Luggage Type]
     FROM Passengers AS p
     JOIN Tickets AS t ON t.PassengerId = p.Id
     JOIN Flights AS f ON f.Id = t.FlightId
     JOIN Planes AS pl ON pl.Id = f.PlaneId
     JOIN Luggages AS l ON l.Id = t.LuggageId
     JOIN LuggageTypes AS lt ON lt.Id = l.LuggageTypeId
ORDER BY [Full Name], Name,Origin, Destination, Type



SELECT k.FirstName, k.LastName, k.Destination, k.Price
  FROM (
    SELECT p.FirstName, p.LastName, f.Destination, t.Price,
           DENSE_RANK() OVER(PARTITION BY p.FirstName, p.LastName ORDER BY t.Price DESC) As PriceRank
      FROM Passengers AS p
      JOIN Tickets AS t ON t.PassengerId = p.Id
      JOIN Flights AS f ON f.Id = t.FlightId
  ) AS k
  WHERE k.PriceRank = 1
  ORDER BY k.Price DESC, k.FirstName, k.LastName, k.Destination


  SELECT f.Destination, COUNT(t.Id) AS [Count]
     FROM Flights AS f
LEFT JOIN Tickets AS t ON t.FlightId = f.Id
 GROUP BY f.Destination
 ORDER BY [Count] DESC, f.Destination


   SELECT p.Name, p.Seats, COUNT(t.Id) AS PassengersCount
     FROM Planes AS p
LEFT JOIN Flights AS f ON f.PlaneId = p.Id
LEFT JOIN Tickets AS t ON t.FlightId = f.Id
 GROUP BY p.Name, p.Seats
 ORDER BY PassengersCount DESC, p.Name, p.Seats


 CREATE FUNCTION udf_CalculateTickets(@origin varchar(50), @destination varchar(50), @peopleCount int)
RETURNS VARCHAR(100)
AS
BEGIN

IF (@peopleCount <= 0)
BEGIN
    RETURN 'Invalid people count!'
END

DECLARE @tripId INT = (SELECT f.Id FROM Flights AS f
                                              JOIN Tickets AS t ON t.FlightId = f.Id
                                              WHERE Destination = @destination AND Origin = @origin)
IF (@tripId IS NULL)
BEGIN
    RETURN 'Invalid flight!'
END

DECLARE @ticketPrice DECIMAL(15,2) = (SELECT t.Price FROM Flights AS f
                                              JOIN Tickets AS t ON t.FlightId = f.Id
                                              WHERE Destination = @destination AND Origin = @origin)

DECLARE @totalPrice DECIMAL(15, 2) = @ticketPrice * @peoplecount;

RETURN 'Total price ' + CAST(@totalPrice as VARCHAR(30));
END



CREATE PROC usp_CancelFlights
AS
UPDATE Flights
SET DepartureTime = NULL, ArrivalTime = NULL
WHERE ArrivalTime > DepartureTime



CREATE TABLE DeletedPlanes
(
    Id INT,
    Name VARCHAR(30),
    Seats INT,
    Range INT
)

CREATE TRIGGER tr_DeletedPlanes ON Planes
AFTER DELETE AS
  INSERT INTO DeletedPlanes (Id, Name, Seats, Range)
      (SELECT Id, Name, Seats, Range FROM deleted)