-- Počet čísel vlaků
SELECT COUNT(*) FROM (SELECT DISTINCT tripid FROM tripdetails) AS i;

-- Počet jízd
SELECT COUNT(*) FROM tripdetails;

CREATE TEMPORARY TABLE final_delays AS (
    SELECT DISTINCT ON (tripid, tripstartdate)
        tripid, tripstartdate, arrivedat - shouldarriveat AS delay
    FROM stophistory AS sh
    WHERE arrivedat IS NOT NULL
    ORDER BY tripid, tripstartdate, tripstopindex DESC);

-- Součet zpoždění (pro náskok jen otočit nerovnost)
SELECT EXTRACT(epoch FROM SUM(delay))/60
FROM final_delays WHERE delay > '0';

-- Průměrné zpoždění
SELECT EXTRACT(epoch FROM AVG(delay))
FROM final_delays;

-- Medián zpoždění
SELECT EXTRACT(epoch FROM percentile_cont(0.5) within group (order by delay))
FROM final_delays;

CREATE TEMPORARY TABLE final_delays_longdistance AS (
SELECT * FROM final_delays WHERE tripid ~ '-CZTRAINT-(EC|IC|Ex|SC|rj|LE|RJ)-.*');

SELECT COUNT(DISTINCT tripid) FROM final_delays_longdistance;
SELECT COUNT(*) FROM final_delays_longdistance;

SELECT EXTRACT(epoch FROM SUM(delay))/60
FROM final_delays_longdistance WHERE delay > '0';

SELECT EXTRACT(epoch FROM AVG(delay))
FROM final_delays_longdistance;

SELECT EXTRACT(epoch FROM percentile_cont(0.5) within group (order by delay))
FROM final_delays_longdistance;

SELECT CONCAT('[', shortname, '](https://rt.jr.ggu.cz/Trip/', tripid, '/', tripstartdate ,')'), tripstartdate, name, delay
FROM (
    SELECT DISTINCT ON (i.tripid, i.tripstartdate) td.shortname,  i.tripstartdate, s.name, i.delay, i.tripid
    FROM (
        SELECT GREATEST(COALESCE(departedat - shoulddepartat, '0'), COALESCE(arrivedat - shoulddepartat, '0')) AS delay, *
        FROM stophistory AS sh
        ORDER BY delay DESC
        LIMIT 1000) AS i
    LEFT JOIN stops AS s ON s.id = i.stopid AND s.validdaterange @> i.tripstartdate
    LEFT JOIN tripdetails AS td ON td.tripid = i.tripid AND td.tripstartdate = i.tripstartdate
    ORDER BY i.tripid, i.tripstartdate, delay DESC) AS i2
WHERE (SELECT arrivedat
       FROM stophistory AS sh2
       WHERE sh2.tripid = i2.tripid AND sh2.tripstartdate = i2.tripstartdate
       ORDER BY tripstopindex DESC LIMIT 1) IS NOT NULL
  AND (SELECT departedat - shoulddepartat
       FROM stophistory AS sh2
       WHERE sh2.tripid = i2.tripid AND sh2.tripstartdate = i2.tripstartdate
       AND tripstopindex = 1) < '5 hours'
ORDER BY delay DESC;

CREATE TEMPORARY TABLE stophistory_large_change AS
SELECT * FROM
    (SELECT
        tripid, tripstartdate, tripstopindex,
        shouldarriveat - LAG(shouldarriveat, 1) OVER w AS sarr_change,
        arrivedat - LAG(arrivedat, 1) OVER w AS arr_change,
        shoulddepartat - LAG(shoulddepartat, 1) OVER w AS sdep_change,
        departedat - LAG(departedat, 1) OVER w AS dep_change
    FROM stophistory
    WINDOW w AS (PARTITION BY tripid, tripstartdate ORDER BY tripstopindex ASC)) AS i
WHERE COALESCE(sarr_change > '12 hours' OR sarr_change < '-12 hours', FALSE)
   OR COALESCE(arr_change > '12 hours' OR arr_change < '-12 hours', FALSE)
   OR COALESCE(sdep_change > '12 hours' OR sdep_change < '-12 hours', FALSE)
   OR COALESCE(dep_change > '12 hours' OR dep_change < '-12 hours', FALSE);

CREATE TEMPORARY TABLE stophistory_large_delay AS
SELECT * FROM
    (SELECT
        tripid, tripstartdate, tripstopindex,
        arrivedat - shouldarriveat AS arrdelay,
        departedat - shoulddepartat AS depdelay
     FROM stophistory) AS i
WHERE COALESCE(arrdelay > '20 hours' OR arrdelay < '-1 hour', FALSE)
   OR COALESCE(depdelay > '20 hours' OR depdelay < '-1 hour', FALSE);

DELETE FROM stophistory AS sh
WHERE EXISTS (SELECT FROM stophistory_large_change AS shc
              WHERE shc.tripid = sh.tripid
                  AND shc.tripstartdate = sh.tripstartdate)
   OR EXISTS (SELECT FROM stophistory_large_delay AS shd
                 WHERE shd.tripid = sh.tripid
                   AND shd.tripstartdate = sh.tripstartdate);