-- vytvoření dtabáze
CREATE TABLE airports (code varchar(2) primary key,
		       name varchar(100) );

INSERT INTO airports VALUES ('SF','San Francisco');
INSERT INTO airports VALUES ('LA','Los Angeles');
INSERT INTO airports VALUES ('DA','Dallas');
INSERT INTO airports VALUES ('CH','Chicago');
INSERT INTO airports VALUES ('NY','New YoPostgreSQL rk');

CREATE TABLE flights (
  flight_id varchar(3) primary key,
  start varchar(2) references airports(code),
  dest varchar(2) references airports(code),
  price integer);

INSERT INTO flights VALUES ('f1','SF','LA',50);
INSERT INTO flights VALUES ('f2','LA','SF',50);
INSERT INTO flights VALUES ('f3','SF','CH',275);
INSERT INTO flights VALUES ('f4','CH','SF',275);
INSERT INTO flights VALUES ('f5','SF','DA',300);
INSERT INTO flights VALUES ('f6','DA','SF',300);
INSERT INTO flights VALUES ('f7','CH','DA',100);
INSERT INTO flights VALUES ('f8','DA','CH',100);
INSERT INTO flights VALUES ('f9','CH','NY',250);
INSERT INTO flights VALUES ('f10','NY','CH',250);
INSERT INTO flights VALUES ('f11','NY','DA',225);
INSERT INTO flights VALUES ('f12','DA','NY',225);
INSERT INTO flights VALUES ('f13','DA','LA',200);
INSERT INTO flights VALUES ('f14','LA','DA',200);


-- rekurzivní část dotazu
-- začínáme v San Franciscu končíme v New Yorku
  WITH RECURSIVE trips (dest, path, total_price) AS
      ((SELECT dest, dest, price
            FROM flights
            WHERE "start" = 'SF' )
  UNION ALL
        (SELECT f.dest ,
                t.path || ',' || f.dest,
                t.total_price + f.price
        FROM trips t, flights f
        WHERE t.dest = f."start"))
  SELECT path, total_price
  FROM trips
  WHERE dest = 'NY';

-- musíme ovšem omezit rekuzi, aby vyhodnocení nekončilo chybou
  WITH RECURSIVE trips (dest, path, n_flights, total_price) AS
    (SELECT dest,  "start"||','||dest , 1,  price
         FROM flights
         WHERE "start" = 'SF'
  UNION ALL
     (SELECT f.dest,
             t.path || ',' || f.dest,
             t.n_flights + 1, t.total_price + f.price
      FROM trips t, flights f
      WHERE t.dest = f."start"
            AND f.dest <> 'SF'
            AND f."start" <> 'NY'
            AND t.n_flights < 5
      ))
  SELECT path, total_price
  FROM trips;

-- tři nejlevnější cesty ze San Francisca do New Yorku
  WITH RECURSIVE trips (dest, path, n_flights, total_price) AS
    (SELECT dest,  "start"||','||dest , 1,  price
         FROM flights
         WHERE "start" = 'SF'
  UNION ALL
     (SELECT f.dest,
             t.path || ',' || f.dest,
             t.n_flights + 1, t.total_price + f.price
      FROM trips t, flights f
      WHERE t.dest = f."start"
            AND f.dest <> 'SF'
            AND f."start" <> 'NY'
            AND t.n_flights < 5
      ))
  SELECT path, total_price
  FROM trips
  WHERE dest = 'NY'
  ORDER BY total_price
  LIMIT 3;