-- MySQL

create database if not exists avia;
use avia;
drop table if exists test;
drop function if exists is_hot;

delimiter //
create function is_hot(a date, b date) returns int
deterministic
begin
    declare d int;
    set d := datediff(a, b);
    return if(d > 60 or d < 3, 15, 0);
end
//

create table test (
    Passage int check(Passage > 0 and Passage < 100000),
    Title varchar(40),
    DepartureDate date,
    DepartureTime time,
    ArrivalDate date,
    ArrivalTime time,
    Seats bool,
    Places varchar(50),
    Passenger int check(Passenger > 0 and Passenger < 100000),
    PassName varchar(40),
    PurchaseDate date,
    Passport varchar(20),
    PassportDate date,
    Region varchar(40),
    Till int check(Till > 0 and Till < 100000),
    Ticket bigint check(Ticket > 0 and Ticket < 10000000000),
    Chair int check(Chair > 0 and Chair < 1000),
    Class int check(Class > 0 and Class < 4),
    -- Price
    Registrar varchar(15),
    Airliner bigint check(Airliner > 0 and Airliner < 10000000000),
    AirlinerName varchar(15),
    AirlinerYear int check(AirlinerYear > 1000 and AirlinerYear < 9999),
    AirlinerPicture mediumblob,
    Amount int check(Amount >= 0 and Amount < 10000)
);

insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class, Amount) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 3, 17);
insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 2);
insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2017-06-01', '00:00:00', '2017-03-01', 1, 3);
insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 2);
insert into test (DepartureDate, DepartureTime, PurchaseDate, Airliner, Class) values ('2015-06-01', '00:00:00', '2015-05-01', 1, 1);

select Passage, Title, DepartureDate, DepartureTime,
ArrivalDate, ArrivalTime, Seats, Places, Passenger,
PassName, PurchaseDate, Passport, PassportDate,
Region, Till, Ticket, Chair, Class, ((4 - Class) * 4000 - ((4 - Class) * 40) * is_hot(PurchaseDate, DepartureDate)) as price,
Registrar, Airliner, AirlinerName, AirlinerYear,
(select count(*)
    from test as testp
    where Airliner = testpar.Airliner and (testp.DepartureDate < testpar.DepartureDate or testp.DepartureDate = testpar.DepartureDate and  testp.DepartureTime < testpar.DepartureTime)
    order by Airliner, DepartureDate, DepartureTime
) + (select min(Amount)
    from test as testc
    where testc.Airliner = testpar.Airliner
    order by Airliner, DepartureDate, DepartureTime
) as Amount
from test as testpar
order by Airliner, DepartureDate, DepartureTime;