SELECT
    kwi.id_kwi     AS kwi_id,
    krs.id_krs     AS krs_id,
    krs.id_krn     AS krn_id,
    kwi.numer_lini AS line,
    kwi.nr_brygady AS brigade,
    kwi.typ_dnia   AS dayType,
    krs.godz_rozp  AS GodzinaOdjazdu,
    directionInfo.*

FROM kursowki AS kwi
    LEFT JOIN brygada AS brg ON brg.id_kwi = kwi.id_kwi
    LEFT JOIN kursy   AS krs ON krs.id_krs = brg.id_krs
    LEFT JOIN (
        SELECT
          direction.id_krn                                      AS krn_id,
          ifnull(directionDescr.opis_tabl, direction.opis_tabl) AS Kierunek,
          ifnull(directionDescr.id_pikt, direction.nr_ibis)     AS Picto,
          LINES.typ_linii                                       AS LineType_DayNigth,
          ifnull(directionDescr.id_typ_tr, direction.id_typ_tr) AS IdTypTrasy,
          direction.trasa                                       AS KodTrasy,
          CASE WHEN directionDescr.opis_tabl IS NULL
               THEN 0 ELSE 1 END                                AS fromKierunekOpisy,
          ifnull(directionDescr.id_prz_kon, 0)                  AS movementThroughValidTo,
          CASE WHEN directionDescr.nowa_trasa IS NULL THEN 0
               WHEN directionDescr.nowa_trasa = 'Y' THEN 1
               ELSE 0 END                                       AS flagNewRoute,
          CASE WHEN directionDescr.opis_tabl IS NOT NULL THEN 0
               WHEN direction.skrocony = 'Y' THEN 1
               ELSE 0 END                                       AS flagShortenedRoute

        FROM kierunek AS direction
          LEFT JOIN linie AS LINES ON direction.id_lin = LINES.id_lin
          LEFT JOIN (
              SELECT * FROM (
                  SELECT kierunek_opisy.*
                  FROM kierunek_opisy INNER JOIN pojazdy_zaw_grup
                      ON id_grupy_p != 0 AND id_grupy_p = id_grupy AND nb = '803'
                  UNION
                  SELECT * FROM kierunek_opisy WHERE id_grupy_p = 0 OR id_grupy_p IS NULL)
              WHERE aktywny = 'Y'
                  AND ((wazny_od <= DATE('now') AND wazny_do >= DATE('now'))
                    OR (wazny_do IS NULL AND wazny_od <= DATE('now'))
                    OR (wazny_od IS NULL AND wazny_do >= DATE('now'))
                    OR (wazny_do IS NULL AND wazny_od IS NULL))) AS directionDescr
              ON TRIM(directionDescr.trasa) = TRIM(direction.trasa)
             AND TRIM(directionDescr.linia) = TRIM(direction.linia)) AS directionInfo
    ON directionInfo.krn_id = krs.id_krn

WHERE
      (kwi.wazna_od IS NOT NULL AND DATE('now') >= kwi.wazna_od OR kwi.wazna_od IS NULL)
  AND (kwi.wazna_do IS NOT NULL AND DATE('now') <= kwi.wazna_do OR kwi.wazna_do IS NULL)
  AND kwi.numer_lini   = 'N64'
  AND kwi.sm_brygady = '2'
  AND kwi.typ_dnia   = 'NS'
  AND (CAST(line AS INTEGER) = 0 OR CAST(line AS INTEGER) > 100) -- skip all lines from 1..100
--  AND KodTrasy like "%TO-CPD-C%"

ORDER BY line AND brigade AND dayType AND brg.lp_kursu