CREATE DATABASE hw;
CREATE EXTENSION pgcrypto;

# Statements

В базе данных Airline информация о рейсах самолётов задана в виде таблиц

Flights(
    FlightId integer, 
    FlightTime timestamp, 
    PlaneId integer,
    -- Дополнительные столбцы, при необходимости
)
Seats(
    PlaneId integer,
    SeatNo varchar(4), -- 123A
    -- Дополнительные столбцы, при необходимости
)

Реализуйте запросы к базе данных Airline с применением представлений, хранимых процедур и функций. При необходимости, вы можете создать дополнительные таблицы, представления и хранимые процедуры.

Возможность бронирования должна автоматически отключаться за трое суток до начала рейса. Продажа мест должна автоматически отключаться за три часа до начала рейса. Также должна быть предусмотрена возможность отключения бронирования и продаж вручную.

FreeSeats(FlightId) — список мест, доступных для продажи и для бронирования.
Reserve(UserId, Pass, FlightId, SeatNo) — пытается забронировать место на трое суток начиная с момента бронирования. Возвращает истину, если удалось и ложь — в противном случае.
ExtendReservation(UserId, Pass, FlightId, SeatNo) — пытается продлить бронь места на трое суток начиная с момента продления. Возвращает истину, если удалось и ложь — в противном случае.
BuyFree(FlightId, SeatNo) — пытается купить свободное место. Возвращает истину, если удалось и ложь — в противном случае.
BuyReserved(UserId, Pass, FlightId, SeatNo) — пытается выкупить забронированное место (пользователи должны совпадать). Возвращает истину, если удалось и ложь — в противном случае.
FlightsStatistics(UserId, Pass) — статистика по рейсам: возможность бронирования и покупки, число свободных, забронированных и проданных мест.
FlightStat(UserId, Pass, FlightId) — статистика по рейсу: возможность бронирования и покупки, число свободных, забронированных и проданных мест.
CompressSeats(FlightId) — оптимизирует занятость мест в самолете. В результате оптимизации, в начале самолета должны быть купленные места, затем — забронированные, а в конце — свободные. Примечание: клиенты, которые уже выкупили билеты, также должны быть пересажены.

# 0.1

PostgreSQL 14.5

# 0.2

DROP TABLE IF EXISTS Flights CASCADE;
DROP TABLE IF EXISTS Seats CASCADE;
DROP TABLE IF EXISTS Tickets CASCADE;
DROP TABLE IF EXISTS Users CASCADE;

-- Список рейсов
CREATE TABLE Flights (
       FlightId int GENERATED ALWAYS AS IDENTITY not null,
       FlightTime TIMESTAMP not null,
       PlaneId int not null,

       PRIMARY KEY (FlightId)
);

-- Список мест
CREATE TABLE Seats (
       PlaneId int not null,
       SeatNo VARCHAR(4) not null,

       PRIMARY KEY (PlaneId, SeatNo)
);

-- Описание пользователей: Идентификатор и захещированный пароль
CREATE TABLE Users (
       UserId int GENERATED ALWAYS AS IDENTITY not null,
       Pass TEXT not null,

       PRIMARY KEY (UserId)
);

-- Купленные и забронированные места. Если место забронированно то
-- BookedUntil указано время когда бронь слетает, а также указан
-- пользователь взявший бронь. Если билет на место куплен то Taken =
-- TRUE.
CREATE TABLE Tickets (
       SeatNo VARCHAR(4) not null,
       PlaneId int not null,
       FlightId int not null,
       BookedUntil TIMESTAMP,
       Taken bool not null,
       UserId int,

       PRIMARY KEY (SeatNo, PlaneId, FlightId) DEFERRABLE,

       FOREIGN KEY (FlightId)
       REFERENCES Flights(FlightId),

       FOREIGN KEY (PlaneId, SeatNo)
       REFERENCES Seats(PlaneId, SeatNo),

       FOREIGN KEY (UserId)
       REFERENCES Users(UserId)
);

-- Все места актуальных рейсов
CREATE OR REPLACE VIEW AllSeats AS
SELECT Flights.FlightId, Seats.PlaneId, Seats.SeatNo, Tickets.Taken, Tickets.BookedUntil, Tickets.UserId
FROM Flights
     NATURAL JOIN Seats
     NATURAL LEFT JOIN Tickets
WHERE Flights.FlightTime > NOW();

-- Все не купленные места актуальных рейсов
CREATE OR REPLACE VIEW FreeOrReservedTickets AS
SELECT FlightId, PlaneId, SeatNo, BookedUntil, UserId
FROM AllSeats
WHERE AllSeats.Taken IS NULL
      OR NOT AllSeats.Taken;


-- Все места актуальных рейсов
CREATE OR REPLACE VIEW FreeSeatsView AS
SELECT FlightId, PlaneId, SeatNo, BookedUntil, UserId
FROM FreeOrReservedTickets
WHERE BookedUntil IS NULL
      OR BookedUntil < NOW();

# Fill

INSERT INTO Flights(FlightTime, PlaneId) VALUES
       ('2022-11-26 18:00:00', 0),
       ('2022-12-03 18:00:00', 0),
       ('2022-11-24 10:00:00', 1),
       ('2022-11-26 10:00:00', 1),
       ('2022-11-28 10:00:00', 2);

INSERT INTO Seats(PlaneId, SeatNo) VALUES
       (0, '001A'),
       (0, '001B'),
       (0, '002A'),
       (0, '002B'),
       (0, '003A'),
       (0, '003B'),
       (1, '001A'),
       (1, '001B'),
       (1, '001C'),
       (1, '002A'),
       (1, '002B'),
       (1, '002C'),
       (2, '0001'),
       (2, '0002'),
       (2, '0003'),
       (2, '0004');

INSERT INTO Users(Pass) VALUES
       (CRYPT('qwerty', GEN_SALT('bf'))),
       (CRYPT('123456', GEN_SALT('bf'))),
       (CRYPT('amogus', GEN_SALT('bf')));

INSERT INTO Tickets(SeatNo, PlaneId, FlightId, BookedUntil, Taken, UserId) VALUES
       ('001A', 0, 1, NULL, TRUE, 1),
       ('001B', 0, 1, '2022-11-28 17:00:00', FALSE, 2),
       ('001A', 0, 2, '2022-12-03 17:00:00', FALSE, 3),
       ('002A', 0, 2, NULL, TRUE, NULL),
       ('001B', 0, 2, NULL, FALSE, 2);

# View

SELECT * FROM Flights;

SELECT * FROM Users;

SELECT * FROM Tickets;

# 0.3

# 1

CREATE OR REPLACE FUNCTION FreeSeats(_FlightId int)
RETURNS TABLE (
	SeatNo VARCHAR(4)
)
LANGUAGE plpgsql
AS $$
BEGIN
   RETURN QUERY (
	  SELECT FreeSeatsView.SeatNo
	  FROM FreeSeatsView
	  WHERE FlightId = _FlightId
   );
END;
$$

SELECT * FROM FreeSeats(2);

# 2

-- Проверка что пользователь сущесвует и пароль корректный
CREATE OR REPLACE FUNCTION Authenticate(UserId INT, Pass TEXT)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
	RETURN EXISTS (
	       SELECT *
	       FROM Users
	       WHERE Users.UserId = Authenticate.UserId
		     AND Users.Pass = CRYPT(Authenticate.Pass, Users.Pass)
	);
END;
$$;

-- Проверка что место не забронированно и не куплено
CREATE OR REPLACE FUNCTION IsFreeSeat(_FlightId INT, _SeatNo VARCHAR(40))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
	RETURN _SeatNo IN (
	       SELECT FreeSeatsView.SeatNo
	       FROM FreeSeatsView
	       WHERE FlightId = _FlightId
	);
END;
$$;

SELECT * FROM Authenticate(1, 'qwerty');

SELECT * FROM IsFreeSeat(2, '002A');

CREATE OR REPLACE FUNCTION Reserve(_UserId INT, _Pass TEXT, _FlightId INT, _SeatNo VARCHAR(4))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE _PlaneId INT = (
	SELECT PlaneId
	FROM Flights
	WHERE FlightId = _FlightId
);
DECLARE _FlightTime TIMESTAMP = (
	SELECT FlightTime
	FROM Flights
	WHERE FlightId = _FlightId
);
BEGIN
	IF _PlaneId IS NULL OR _FlightTime IS NULL THEN
	   RETURN FALSE;
	END IF;

	IF NOT Authenticate(_UserId, _Pass) THEN
	   RETURN FALSE;
	END IF;

	IF _FlightTime < NOW() THEN
	   RETURN FALSE;
	END IF;

	IF NOT IsFreeSeat(_FlightId, _SeatNo) THEN
	   RETURN FALSE;
	END IF;

	INSERT INTO Tickets(SeatNo, PlaneId, FlightId, BookedUntil, Taken, UserId) VALUES
	       (_SeatNo, _PlaneId, _FlightId, NOW() + INTERVAL '3' DAY, FALSE, _UserId)
	ON CONFLICT(SeatNo, PlaneId, FlightId) DO UPDATE
	   SET UserId = _UserId,
	       BookedUntil = NOW() + INTERVAL '3' DAY,
	       Taken = FALSE;

	RETURN TRUE;
END;
$$

SELECT * FROM Reserve(3, 'amogus', 5, '0003');

# 3

CREATE OR REPLACE FUNCTION ExtendReservation(_UserId INT, _Pass TEXT, _FlightId INT, _SeatNo VARCHAR(4))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE _PlaneId INT = (
	SELECT PlaneId
	FROM Flights
	WHERE FlightId = _FlightId
);
DECLARE _FlightTime TIMESTAMP = (
	SELECT FlightTime
	FROM Flights
	WHERE FlightId = _FlightId
);
DECLARE _ReservedByUser INT = (
	SELECT UserId
	FROM Tickets
	WHERE PlaneId = _PlaneId
	      AND FlightId = _FlightId
	      AND SeatNo = _SeatNo
);
BEGIN
	IF _PlaneId IS NULL OR _FlightTime IS NULL OR _ReservedByUser IS NULL THEN
	   RETURN FALSE;
	END IF;

	IF NOT Authenticate(_UserId, _Pass) THEN
	   RETURN FALSE;
	END IF;

	IF _UserId != _ReservedByUser THEN
	   RETURN FALSE;
	END IF;

	IF _FlightTime < NOW() THEN
	   RETURN FALSE;
	END IF;

	UPDATE Tickets
	SET BookedUntil = NOW() + INTERVAL '3' DAY
	WHERE PlaneId = _PlaneId
	      AND FlightId = _FlightId
	      AND SeatNo = _SeatNo;

	RETURN TRUE;
END;
$$

SELECT * FROM ExtendReservation(1, 'qwerty', 2, '001B');

# 4

CREATE OR REPLACE FUNCTION BuyFree(_FlightId INT, _SeatNo VARCHAR(4))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE _PlaneId INT = (
	SELECT PlaneId
	FROM Flights
	WHERE FlightId = _FlightId
);
DECLARE _FlightTime TIMESTAMP = (
	SELECT FlightTime
	FROM Flights
	WHERE FlightId = _FlightId
);
BEGIN
	IF _PlaneId IS NULL OR _FlightTime IS NULL THEN
	   RETURN FALSE;
	END IF;

	IF _FlightTime < NOW() THEN
	   RETURN FALSE;
	END IF;

	IF NOT IsFreeSeat(_FlightId, _SeatNo) THEN
	   RETURN FALSE;
	END IF;

	INSERT INTO Tickets(SeatNo, PlaneId, FlightId, BookedUntil, Taken, UserId) VALUES
	       (_SeatNo, _PlaneId, _FlightId, NULL, TRUE, NULL)
	ON CONFLICT(SeatNo, PlaneId, FlightId) DO UPDATE
	   SET UserId = NULL,
	       BookedUntil = NULL,
	       Taken = TRUE;

	RETURN TRUE;
END;
$$

SELECT * FROM BuyFree(2, '002B');

# 5

CREATE OR REPLACE FUNCTION BuyReserved(_UserId INT, _Pass TEXT, _FlightId INT, _SeatNo VARCHAR(4))
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE _PlaneId INT = (
	SELECT PlaneId
	FROM Flights
	WHERE FlightId = _FlightId
);
DECLARE _FlightTime TIMESTAMP = (
	SELECT FlightTime
	FROM Flights
	WHERE FlightId = _FlightId
);
DECLARE _ReservedByUser INT = (
	SELECT UserId
	FROM Tickets
	WHERE PlaneId = _PlaneId
	      AND FlightId = _FlightId
	      AND SeatNo = _SeatNo
);
BEGIN
	IF _PlaneId IS NULL OR _FlightTime IS NULL OR _ReservedByUser IS NULL THEN
	   RETURN FALSE;
	END IF;

	IF NOT Authenticate(_UserId, _Pass) THEN
	   RETURN FALSE;
	END IF;

	IF _UserId != _ReservedByUser THEN
	   RETURN FALSE;
	END IF;

	IF _FlightTime < NOW() THEN
	   RETURN FALSE;
	END IF;

	UPDATE Tickets
	SET BookedUntil = NULL,
	    TAKEN = TRUE
	WHERE PlaneId = _PlaneId
	      AND FlightId = _FlightId
	      AND SeatNo = _SeatNo;

	RETURN TRUE;
END;
$$

SELECT * FROM BuyReserved(2, '123456', 5, '0001');

# 6

-- Статистика по местам которые можно забронировать
CREATE OR REPLACE VIEW StatCanReserve AS
SELECT FlightId, COUNT(*) AS CanReserve
FROM FreeOrReservedTickets
WHERE BookedUntil IS NULL
      OR BookedUntil < NOW()
GROUP BY FlightId;

-- Статистика по местам которые может купить пользователь
CREATE OR REPLACE FUNCTION StatCanBuy(_UserId INT)
RETURNS TABLE (
	FlightId INT,
	CanBuy INT
)
LANGUAGE plpgsql
AS $$
BEGIN
   RETURN QUERY (
	  SELECT FreeOrReservedTickets.FlightId,
		 CAST(COUNT(*) AS INT) AS CanBuy
	  FROM FreeOrReservedTickets
	  WHERE BookedUntil IS NULL
		OR BookedUntil < NOW()
		OR (
		   BookedUntil > NOW()
		   AND UserId = _UserId
		)
	  GROUP BY FreeOrReservedTickets.FlightId
   );
END;
$$;


-- Статистика по свободным местам
CREATE OR REPLACE VIEW StatFree AS
SELECT FlightId, COUNT(*) AS Free
FROM FreeOrReservedTickets
WHERE BookedUntil IS NULL
      OR BookedUntil < NOW()
GROUP BY FlightId;


-- Статистика по забронированным местам
CREATE OR REPLACE VIEW StatReserved AS
SELECT FlightId, COUNT(*) AS Reserved
FROM FreeOrReservedTickets
WHERE BookedUntil > NOW()
GROUP BY FlightId;


-- Статистика по купленным местам
CREATE OR REPLACE VIEW StatSold AS
SELECT FlightId, COUNT(Taken = TRUE) AS Sold
FROM AllSeats
GROUP BY FlightId;

SELECT * FROM AllSeats;

SELECT * FROM StatCanBuy(3);

CREATE OR REPLACE FUNCTION FlightsStatistics(_UserId INT, _Pass TEXT)
RETURNS TABLE (
	CanReserve INT,
	CanBuy INT,
	Free INT,
	Reserved INT,
	Sold INT
)
LANGUAGE plpgsql
AS $$
BEGIN
	IF NOT Authenticate(_UserId, _Pass) THEN
	   RETURN;
	END IF;

	RETURN QUERY (
	 SELECT CAST(SUM(StatCanReserve.CanReserve) AS INT),
		CAST(SUM(StatCanBuy.CanBuy) AS INT),
		CAST(SUM(StatFree.Free) AS INT),
		CAST(SUM(StatReserved.Reserved) AS INT),
		CAST(SUM(StatSold.Sold) AS INT)
	 FROM StatFree
	     NATURAL LEFT JOIN StatReserved
	     NATURAL LEFT JOIN StatSold
	     NATURAL LEFT JOIN StatCanReserve
	     NATURAL LEFT JOIN StatCanBuy(_UserId)
	);
END;
$$

SELECT * FROM FlightsStatistics(3, 'amogus');

# 7

CREATE OR REPLACE FUNCTION FlightStat(_UserId INT, _Pass TEXT, _FlightId INT)
RETURNS TABLE (
	CanReserve INT,
	CanBuy INT,
	Free INT,
	Reserved INT,
	Sold INT
)
LANGUAGE plpgsql
AS $$
BEGIN
	IF NOT Authenticate(_UserId, _Pass) THEN
	   RETURN;
	END IF;

	RETURN QUERY (
	 SELECT CAST(StatCanReserve.CanReserve AS INT),
		CAST(StatCanBuy.CanBuy AS INT),
		CAST(StatFree.Free AS INT),
		CAST(StatReserved.Reserved AS INT),
		CAST(StatSold.Sold AS INT)
	 FROM StatFree
	     NATURAL LEFT JOIN StatReserved
	     NATURAL LEFT JOIN StatSold
	     NATURAL LEFT JOIN StatCanReserve
	     NATURAL LEFT JOIN StatCanBuy(_UserId)
	 WHERE StatFree.FlightId = _FlightId
	);
END;
$$

SELECT * FROM FlightStat(2, '123456', 5);

# 8

CREATE OR REPLACE FUNCTION CompressSeats(_FlightId INT)
RETURNS BOOL
LANGUAGE plpgsql
AS $$
DECLARE _CurSeatNo VARCHAR(4);
DECLARE _SeatCursor CURSOR FOR
	SELECT SeatNo
	FROM Seats
	     NATURAL JOIN Flights
	WHERE FlightId = _FlightId
	ORDER BY SeatNo
	FOR READ ONLY;
DECLARE _TakenCursor CURSOR FOR
	SELECT SeatNo
	FROM Tickets
	WHERE FlightId = _FlightId
	      AND Taken
	FOR UPDATE;
DECLARE _ReservedCursor CURSOR FOR
	SELECT SeatNo
	FROM Tickets
	WHERE FlightId = _FlightId
	      AND NOT Taken
	      AND BookedUntil >= NOW()
      FOR UPDATE;
BEGIN
	SET CONSTRAINTS ALL DEFERRED;

	DELETE FROM Tickets
	WHERE NOT Taken
	      AND (
	       BookedUntil < NOW()
	       OR BookedUntil IS NULL
	      )
	      AND FlightId = _FlightId;

	IF _FlightId NOT IN (SELECT FlightId FROM Flights) THEN
	   RETURN FALSE;
	END IF;

	OPEN _SeatCursor;

	FOR _SeatNo IN _TakenCursor LOOP
	    FETCH NEXT FROM _SeatCursor INTO _CurSeatNo;
	    UPDATE Tickets
	    SET SeatNo = _CurSeatNo
	    WHERE CURRENT OF _TakenCursor; 
	END LOOP;

	FOR _SeatNo IN _ReservedCursor LOOP
	    FETCH NEXT FROM _SeatCursor INTO _CurSeatNo;
	    UPDATE Tickets
	    SET SeatNo = _CurSeatNo
	    WHERE CURRENT OF _ReservedCursor; 
	END LOOP;

	CLOSE _SeatCursor;

	RETURN TRUE;
END;
$$

SELECT * FROM CompressSeats(2);

# HW-10 tests

START TRANSACTION READ ONLY ISOLATION LEVEL READ COMMITTED;
SELECT SeatNo, BookedUntil FROM FreeOrReservedTickets
  WHERE FlightId = 2
  AND (UserId = 1 OR UserId IS NULL);
COMMIT;

# Comments

0.2. Таблицы и представления
0.3. Дополнительные процедуры
FreeSeats
Reserve
ExtendReservation
BuyFree
BuyReserved
FlightsStatistics
FlightsStat
- Дублирования кода подсчёта статистики
CompressSeats
- Дублирования кода подсчёта статистики