oeffimaster/lib/transport_helper.dart

import 'package:sqflite/sqflite.dart';
// import './state_helper.dart';
// import './db_helper.dart';

class DbHelper {
  static late Database db;
  static String dbName = "";

  // DbHelper({
  //   required this.db,
  //   required this.dbName,
  // });

  // remnant from when DB was not static
  // factory DbHelper._openDb(Database database, String dbName) => DbHelper(
  //   db: database,
  //   dbName: dbName,
  // );

  static void initDb(String name) async {
    // var db = await openDatabase('/var/db/oeffimaster/transport_db.db');
    var databasesPath = await getDatabasesPath();
    var path = databasesPath + name;
    // var newDb = await openDatabase(path);
    // var dbHelper = DbHelper._openDb(newDb, dbName);
    db = await openDatabase(path);
    dbName = name;

    // TODO: Make sure the directory exists

    // TODO: future optimisation: batch transactions

    Journey.initJourneyTable();
    Journey.initJourneySegmentsTable();
    Segment.initSegmentTable();
    Station.initStationTable();
    Station.initStationLinesTable();
    Line.initLineTable();
    Vehicle.initVehicleTable();
    Departure.initDepartureTable();
  }
}

/// this class covers two database tables: "journey" and "routesegments"
/// while journey will for now only hold an id, routesegments will store the
/// m-n/ 1-n relations between journeys and segments - the segments contain the most
/// relevant info, while routesegments stores which journey the segments belong to
/// and in which order they make up a journey
class Journey {
  static const String tableName = "journey";
  // TODO: add last_viewed, last_modified and such
  int? id;
  String? startStation;
  String? endStation;
  List<Segment> segments = <Segment>[];
  Duration duration = Duration(minutes: 0);

  Journey({
    this.id,
    this.startStation,
    this.endStation,
    required this.segments,
    required this.duration,
  });

  /// toMap for Journey but it's just journey ids for now
  Map<String, Object?> _toMapJourney() {
    return {"id": id, "startstation": startStation, "endstation": endStation};
  }

  /// toMap for routesegments relational table
  List<Map<String, Object?>> _toMapJourneySegments() {
    //asMap() apparently enumerates
    List<Map<String, Object?>> tmp = [];
    for (final (segmentIdx, segment) in segments.indexed) {
      tmp.add({
        "journey": id,
        "segment": segment.id,
        "segmentorder": segmentIdx,
      });
    }
    return tmp;
  }

  factory Journey.empty(Station start) {
    return Journey(
      startStation: start.name,
      segments: List.empty(),
      duration: Duration(minutes: 0),
    );
  }

  factory Journey.fromMap(
    Map<String, Object?> journeyMaps,
    List<Segment> segmentMaps,
  ) {
    return Journey(
      id: journeyMaps["id"] as int,
      startStation: journeyMaps["startstation"] as String?,
      endStation: journeyMaps["endstation"] as String?,
      segments: segmentMaps,
      duration: Duration(minutes: 0),
    );
  }

  // from trip API request
  factory Journey.fromJson(Map<String, dynamic> json) {
    Journey newJourney = Journey(
      segments: json["LegList"]["Leg"].map((leg) => Segment.fromJson(leg)),
      duration:
          json["LegList"]["Leg"][-1]["Origin"]["time"] -
          json["LegList"]["Leg"][0]["Origin"]["time"],
    );
    newJourney._dbInsert();
    newJourney.sync();
    return newJourney;
  }

  static Future<Journey> fromSegment(Segment startSeg) async {
    Journey newJourney = Journey(
      segments: [startSeg],
      duration: startSeg.end.arrivalTime.difference(
        startSeg.start.departureTime,
      ),
    );
    newJourney.id = await newJourney._dbInsert();
    newJourney._dbInsertLastJourneySegment();
    newJourney.sync();
    return newJourney;
  }

  /// recalculate some technically redundant data for fast and easy access
  void sync() async {
    if (segments.isNotEmpty) {
      startStation = segments.first.start.stationName;
      endStation = segments.last.end.stationName;
      duration = segments.first.start.departureTime.difference(
        segments.last.end.arrivalTime,
      );
    } else {
      // if the journey has no segments yet, at least the startStation should remain,
      // otherwise delete the thing
      // having a startStation makes the first step faster at least
      // no need to put a mostly empty journey into the db though
      endStation = null;
      duration = Duration(minutes: 0);
    }
    _dbUpdate();
  }

  void addSegment(Segment newSeg) {
    segments.add(newSeg);
    _dbInsertLastJourneySegment();
  }

  void removeLastSegment() {
    _dbDeleteLastJourneySegment();
    segments.removeLast();
  }

  void removeSegmentsTillEnd(int idx) {
    segments.removeRange(idx, segments.length - 1);
    _dbDeleteTrailingJourneySegments(idx);
  }

  void _removeSegment(int idx) {
    segments.removeAt(idx);
  }

  void replaceSegment(int idx, Segment replacement) {
    if ((idx < segments.length - 1 &&
            replacement._fitsBetween(segments[idx - 1], segments[idx + 1])) ||
        replacement.start.departureTime.isAfter(
          segments[idx - 1].end.arrivalTime,
        )) {
      segments[idx] = replacement;
      _dbUpdateJourneySegments();
    } else if (idx >= segments.length) {
      addSegment(replacement);
    } else {
      throw "startTime for Segment does not fit"; //TODO: can do this better and maybe we should allow this
    }
  }

  static void initJourneyTable() async {
    Database database = DbHelper.db;
    database.execute("""CREATE TABLE IF NOT EXISTS '$tableName'(
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    startstation  TEXT,
    endstation    TEXT
    );
    """);
  }

  static void initJourneySegmentsTable() async {
    Database database = DbHelper.db;
    String segment = Segment.tableName;
    database.execute("""CREATE TABLE IF NOT EXISTS routesegments(
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    journey         INTEGER,
    segment       INTEGER,
    segmentorder  INTEGER, -- TODO: implement sorting
    FOREIGN KEY('$tableName') REFERENCES '$tableName'(id),
    FOREIGN KEY(segment) REFERENCES '$segment'(id)
    );
    """);
  }

  static Future<Journey> dbGet(int id) async {
    Database database = DbHelper.db;
    Future<List<Segment>> routeSegments = _dbGetJourneySegments(id);
    var result = await database.query(
      tableName,
      where: "id = ?",
      whereArgs: [id],
    );
    assert(result.length < 2, "found more than 1 journey for id $id");
    Journey journey = Journey.fromMap(result.first, await routeSegments);
    return journey;
  }

  /// gets routesegments and hands them to the Journey.fromMap factory, works async
  static Future<Journey> fromMapWrapper(Map<String, Object?> journeyMap) async {
    Future<List<Segment>> segs = _dbGetJourneySegments(journeyMap["id"] as int);
    Journey res = Journey.fromMap(journeyMap, await segs);
    res.sync();
    return res;
  }

  // TODO: could make this a generator and use batched queries, probably
  // query all journeys
  // _dbGetJourneySegments -> give list of segments for each journey
  // map all journeys
  static Future<List<Journey>> dbGetAll({int num = 40, int offset = 0}) async {
    //final batch = DbHelper.db.batch; // later
    Database database = DbHelper.db;
    var tmp = await database.query(tableName, limit: num, offset: offset);
    Future<List<Journey>> journeys = Future.wait(
      tmp.map((jour) => fromMapWrapper(jour)),
    );
    return journeys;
  }

  static Future<List<Segment>> _dbGetJourneySegments(
    int journeyId,
  ) async {
    Database database = DbHelper.db;
    String segment = Segment.tableName;
    var tmp = await database.rawQuery("""
        SELECT * FROM '$segment'
        JOIN routesegments ON routesegments.segment = '$segment'.id
        WHERE routesegments.journey = '$journeyId'
        ORDER BY routesegments.segmentorder ASC;
        """);

    return Future.wait(
      tmp.map(
        (x) => Segment.dbGet(x["segment"] as int),
      ),
    );
  }

  Future<int> _dbInsert() async {
    Database database = DbHelper.db;
    Future<int> id_tmp = database.insert(
      tableName,
      _toMapJourney(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
    id = await id_tmp;
    return id_tmp;
  }

  static void dbAppendJourneySegment(int journeyId, int segmentid) {
    Database database = DbHelper.db;
    // generate segmentorder to be 0 if no segment exists for journey, otherwise
    // take highest segmentorder+1 for given journey
    database.rawInsert(
      """INSERT INTO routesegments(journey, segment, segmentorder) VALUES
      ($journeyId, $segmentid, CASE WHEN EXISTS (
        SELECT segmentorder FROM routesegments r WHERE r.journey=$journeyId) THEN (
        SELECT segmentorder AS so FROM routesegments r WHERE r.journey=$journeyId
          ORDER BY so DESC LIMIT 1) +1 ELSE 0 END);
    """,
    );
  }

  void _dbInsertLastJourneySegment() async {
    Database database = DbHelper.db;
    var lastJourneySegment = _toMapJourneySegments().last;
    database.insert("routesegments", lastJourneySegment);
  }

  void _dbInsertJourneySegments() async {
    Database database = DbHelper.db;
    var routeSegments = _toMapJourneySegments();
    for (var i = 0; i < routeSegments.length; i++) {
      database.insert(
        "routesegments",
        routeSegments[i], //TODO: does this work?
      );
    }
  }

  void _dbUpdate() async {
    Database database = DbHelper.db;
    id = await database.update(
      tableName,
      _toMapJourney(),
      where: "id = ?",
      whereArgs: [id],
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  void _dbUpdateJourneySegments() async {
    Database database = DbHelper.db;
    var routeSegments = _toMapJourneySegments();
    for (var i = 0; i < routeSegments.length; i++) {
      database.update(
        "routesegments",
        routeSegments[i],
        where: "journey = ? AND segmentoder = ?",
        whereArgs: [id, i],
      );
    }
  }

  void dbDelete() async {
    Database database = DbHelper.db;
    int result = await database.delete(
      tableName,
      where: "id = ?",
      whereArgs: [id],
    );
    assert(result == 0);
  }

  void _dbDeleteJourneySegment(int idx) {
    Database database = DbHelper.db;
    database.delete(
      "routesegments",
      where: "journey = ? AND segmentorder = ?",
      whereArgs: [id, idx], // TODO: test the index
    );
  }

  void _dbDeleteLastJourneySegment() {
    Database database = DbHelper.db;
    database.delete(
      "routesegments",
      where: "journey = ? AND segmentorder = ?",
      whereArgs: [id, segments.length - 1], // TODO: test the index
    );
  }

  void _dbDeleteTrailingJourneySegments(int position) {
    Database database = DbHelper.db;
    if (position <= segments.length) {
      database.delete(
        "routesegments",
        where: "journey = ? AND segmentorder >= ?",
        whereArgs: [id, position],
      );
    }
  }

  // TODO: delete leading segments and decrement following segments
}

class Segment {
  static const String tableName = "segment";

  // TODO: add attribute for departures on the way?
  int? id;
  String ref;
  Departure start;
  Departure end;
  // Station startPoint;
  // Station endPoint;
  // DateTime startTime;
  // DateTime endTime;
  Vehicle vehicle;

  Segment({
    this.id, // might throw errors - needs testing, I guess
    required this.ref,
    required this.start,
    required this.end,
    required this.vehicle,
  });

  Map<String, Object?> toMap() {
    return {
      "ref": ref,
      "startid": start.id,
      "endid": end.id,
      "vehicleid": vehicle.vehicleid,
    };
  }

  factory Segment.fromMap(Map<String, Object?> map) {
    return Segment(
      id: map["id"] as int,
      ref: map["ref"] as String,
      start: Departure.fromMap(
        map["start"] as Map<String, Object?>,
      ),
      end: Departure.fromMap(
        map["end"] as Map<String, Object?>,
      ),
      vehicle: Vehicle.fromMap(map["vehicle"] as Map<String, Object?>),
    );
  }

  factory Segment.fromJson(Map<String, dynamic> json) {
    // adding departures to db only here instead of within fromJson to only
    // store used departures
    // we need to store them in the db though to get an id from the db :P
    Departure start = Departure.fromJson(json["Origin"]);
    Departure end = Departure.fromJson(json["Destination"]);
    start.dbInsert();
    end.dbInsert();
    Future.delayed(Duration(milliseconds: 30));

    Segment tmp = Segment(
      ref: json["JourneyDetail"]["ref"],
      start: start,
      end: end,
      vehicle: Vehicle.fromJson(json["Product"]),
    );
    tmp.dbInsert();
    return tmp;
  }

  static Future<Segment> fromDepartures(Departure start, Departure end) async {
    start.dbInsert();
    end.dbInsert();
    Segment tmp = Segment(
      ref: start.ref,
      start: start,
      end: end,
      vehicle: await Vehicle.dbGet(start.vehicleId),
    );
    tmp.dbInsert();
    return tmp;
  }

  bool _fitsBetween(Segment prev, Segment next) {
    return start.departureTime.isAfter(prev.end.arrivalTime) &&
        start.arrivalTime.isBefore(next.end.departureTime);
  }

  bool _sameEndpoints(Segment other) {
    return start.stationId == other.start.stationId &&
        end.stationId == other.end.stationId;
  }

  static void initSegmentTable() async {
    Database database = DbHelper.db;
    String station = Station.tableName;
    String vehicle = Vehicle.tableName;
    String departure = Departure.tableName;
    database.execute("""
    CREATE TABLE IF NOT EXISTS '$tableName'(
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    ref         TEXT,
    startid     INTEGER,
    endid       INTEGER,
    vehicleid   TEXT,
    FOREIGN KEY(startid) REFERENCES '$departure'(stationid),
    FOREIGN KEY(endid) REFERENCES '$departure'(stationid),
    FOREIGN KEY(vehicleid) REFERENCES '$vehicle'(vehicleid)
    );
    """);
  }

  //TODO: support batches
  static Future<Segment> dbGet(int id) async {
    Database database = DbHelper.db;
    String vehicle = Vehicle.tableName;
    String departure = Departure.tableName;
    var tmp = await database.rawQuery(
      """SELECT s.id, s.ref, s.startid, s.endid, s.vehicleid,
    start.station as start_stationid,
    start.stationname as start_stationname, start.arrivaltime as start_arrivaltime,
    start.departuretime as start_departuretime, start.direction as direction,
    end.station as end_stationid, end.stationname as end_stationname,
    end.arrivaltime as end_arrivaltime, end.departuretime as end_departuretime,
    v.name as vehiclename, v.line as vehicleline
    FROM $tableName s
    JOIN $departure start ON s.startid = start.id
    JOIN $departure end ON s.endid = end.id
    JOIN $vehicle v ON s.vehicleid = v.vehicleid
    where s.id = $id;
    """,
    );
    // since we are querying for the primary key, this list should never be > 1
    assert(tmp.length < 2, "found more than one segment for id $id");
    // var seg = tmp.map((x) => Segment.fromMap(x));
    // return seg.first;

    Map<String, Object?> seg = tmp.first;
    Map<String, Object> startMap = {
      "id": seg["startid"] as int,
      "ref": seg["ref"] as String,
      "direction": seg["direction"] as String,
      "vehicle": seg["vehicleid"] as String,
      "vehiclename": seg["vehiclename"] as String,
      "station": seg["start_stationid"] as String,
      "stationname": seg["start_stationname"] as String,
      "arrivaltime": seg["start_arrivaltime"] as String,
      "departuretime": seg["start_departuretime"] as String,
    };
    Map<String, Object> endMap = {
      "id": seg["endid"] as int,
      "ref": seg["ref"] as String,
      "direction": seg["direction"] as String,
      "vehicle": seg["vehicleid"] as String,
      "vehiclename": seg["vehiclename"] as String,
      "station": seg["end_stationid"] as String,
      "stationname": seg["end_stationname"] as String,
      "arrivaltime": seg["end_arrivaltime"] as String,
      "departuretime": seg["end_departuretime"] as String,
    };
    Map<String, String> vehicleMap = {
      "vehicleid": seg["vehicleid"] as String,
      "name": seg["vehiclename"] as String,
      "line": seg["vehicleline"] as String,
    };
    Map<String, Object?> fullMap = {
      "id": seg["id"] as int,
      "ref": seg["ref"] as String,
      "start": startMap,
      "end": endMap,
      "vehicle": vehicleMap,
    };
    return Segment.fromMap(fullMap);
  }

  static Future<List<Segment>> dbGetAll() async {
    Database database = DbHelper.db;
    var seglist = await database.query(tableName);
    return seglist.map((seg) => Segment.fromMap(seg)).toList();
  }

  void dbInsert() async {
    Database database = DbHelper.db;
    id = await database.insert(
      tableName,
      toMap(),
      conflictAlgorithm: ConflictAlgorithm.ignore,
    );
  }

  void dbUpdate() async {
    Database database = DbHelper.db;
    id = await database.update(
      tableName,
      toMap(),
      where: "id =?",
      whereArgs: [id],
    );
  }

  void dbDelete() {
    Database database = DbHelper.db;
    database.delete(
      tableName,
      where: "id = ?",
      whereArgs: [id],
    );
  }
}

class Station {
  static const String tableName = "station";

  String id;
  String name;
  List<Line> transportLines = List.empty(); // could be normalized away

  Station({
    required this.id,
    required this.name,
    required this.transportLines,
  });

  Map<String, Object?> _toMap() {
    return {"stationid": id, "name": name};
  }

  List<Map<String, Object?>> _toMapLines() {
    return transportLines
        .map(
          (line) => {"stationid": id, "lineid": line},
        )
        .toList();
  }

  factory Station.fromMap(
    Map<String, Object?> map, {
    List<Line>? stationLines,
  }) {
    return Station(
      id: map["stationid"] as String,
      name: map["name"] as String,
      transportLines: stationLines ?? List.empty(),
    );
  }

  factory Station.fromJson(Map<String, dynamic> json) => Station(
    id: json["id"] as String,
    name: json["name"] as String,
    transportLines:
        json.containsKey("productAtStop") && json["productAtStop"] != Null
        ? json["productAtStop"]
              .map(
                (jsonLine) => //jsonLine != Null
                    /*?*/ Line.fromJson(jsonLine! as Map<String, dynamic>),
                //: Null,
              )
              .toList()
              .cast<Line>()
        : [],
  );

  static void initStationTable() async {
    Database database = DbHelper.db;
    database.execute("""CREATE TABLE IF NOT EXISTS station(
      stationid TEXT PRIMARY KEY,
      name      TEXT
    );
    """);
  }

  static void initStationLinesTable() async {
    Database database = DbHelper.db;
    database.execute("""CREATE TABLE IF NOT EXISTS stationlines(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      stationid TEXT,
      lineid TEXT,
      FOREIGN KEY (stationid) REFERENCES station(stationid),
      FOREIGN KEY (lineid) REFERENCES line(lineid)
    );
    """);
  }

  static Future<Station> dbGet(String stationId) async {
    Database database = DbHelper.db;
    var stationLines = _dbGetStationLines(stationId);
    var stationMap = await database.query(
      tableName,
      where: "stationid = ?",
      whereArgs: [stationId],
    );
    var station = Station.fromMap(stationMap.first);
    station.transportLines = await stationLines;
    return station;
  }

  static Future<List<Line>> _dbGetStationLines(String stationId) async {
    Database database = DbHelper.db;
    String line = Line.tableName;
    var lines = await database.rawQuery("""
        SELECT * FROM '$line'
        JOIN stationlines sl ON sl.lineid = line.lineid
        WHERE sl.stationid = '$stationId'
        ORDER BY line.lineid ASC;
        """);
    return lines.map((x) => Line.fromMap(x)).toList();
  }

  void dbInsert() async {
    Database database = DbHelper.db;
    database.insert(
      tableName,
      _toMap(),
      conflictAlgorithm: ConflictAlgorithm.ignore,
    );
  }

  void _dbInsertStationLines() async {
    Database database = DbHelper.db;
    var stationLines = _toMapLines();
    for (var i = 0; i < stationLines.length; i++) {
      database.insert("stationlines", stationLines[i]);
    }
  }

  void dbUpdate() async {
    Database database = DbHelper.db;
    database.update(tableName, _toMap(), where: "id = ?", whereArgs: [id]);
  }

  void dbUpdateStationLines() async {
    Database database = DbHelper.db;
    var stationLines = _toMapLines();
    for (var i = 0; i < stationLines.length; i++) {
      database.update(
        "stationlines",
        stationLines[i],
        where: "stationid = ? AND lineid = ?",
        whereArgs: [id, stationLines[i]["lineid"]],
      );
    }
  }

  void dbDelete() async {
    Database database = DbHelper.db;
    database.delete(tableName, where: "stationid = ?", whereArgs: [id]);
  }

  /// remove all Lines connected to a Station - should be used together with some kind of reparsing.
  void _dbDeleteStationLines() async {
    Database database = DbHelper.db;
    database.delete("stationlines", where: "stationid = ?", whereArgs: [id]);
  }
}

class Vehicle {
  static const String tableName = "vehicle";

  String vehicleid;
  String name;
  String vehicleLineId;

  Vehicle({
    required this.vehicleid,
    required this.name,
    required this.vehicleLineId,
  });

  Map<String, Object?> _toMap() {
    return {
      "vehicleid": vehicleid,
      "name": name,
      "line": vehicleLineId,
    };
  }

  factory Vehicle.fromMap(
    Map<String, Object?> map,
  ) {
    return Vehicle(
      vehicleid: map["vehicleid"] as String,
      name: map["name"] as String,
      vehicleLineId: map["line"] as String,
    );
  }

  // we can use the "product" class from within any JSON response
  // (within hafas API, on the appropriate level)
  factory Vehicle.fromJson(Map<String, dynamic> json) => Vehicle(
    vehicleid: json["num"],
    name: json["name"],
    vehicleLineId: Line.fromJson(json).lineId,
  );

  static void initVehicleTable() async {
    Database database = DbHelper.db;
    String line = Line.tableName;
    database.execute("""CREATE TABLE IF NOT EXISTS '$tableName'(
      vehicleid TEXT PRIMARY KEY,
      name      TEXT,
      line      TEXT,
      FOREIGN KEY(line) REFERENCES '$line'(lineid)
    );
    """);
  }

  static Future<Vehicle> dbGet(String vehicleId) async {
    Database database = DbHelper.db;
    var vehicleMap = await database.query(
      tableName,
      where: "vehicleid = ?",
      whereArgs: [vehicleId],
    );
    return Vehicle.fromMap(vehicleMap.first);
  }

  void dbInsert() async {
    Database database = DbHelper.db;
    database.insert(
      tableName,
      _toMap(),
      conflictAlgorithm: ConflictAlgorithm.ignore,
    );
  }

  void dbUpdate() async {
    Database database = DbHelper.db;
    database.update(
      tableName,
      _toMap(),
      where: "vehicleid=?",
      whereArgs: [vehicleid],
    );
  }

  void dbDelete() async {
    Database database = DbHelper.db;
    database.delete(tableName, where: "id = ?", whereArgs: [vehicleid]);
  }
}

class Line {
  static const String tableName = "line";

  String lineId;
  String? destination;
  String? direction;
  VehicleType mode;
  String name;
  Color? fgColor;
  Color? bgColor;

  Line({
    required this.lineId,
    this.destination,
    this.direction,
    required this.name,
    required this.mode,
    this.fgColor,
    this.bgColor,
  });

  Map<String, Object?> _toMap() {
    return {
      "lineid": lineId,
      "destination": destination,
      "direction": direction,
      "mode": mode.toString(),
      "name": name,
      "fgcolor": fgColor.toString(),
      "bgcolor": bgColor.toString(),
    };
  }

  factory Line.fromMap(Map<String, Object?> map) {
    return Line(
      lineId: map["lineId"] as String,
      destination: map["destination"] as String,
      direction: map["direction"] as String,
      // Source - https://stackoverflow.com/a/44060511
      // Posted by Collin Jackson, modified by community. See post 'Timeline' for change history
      // Retrieved 2026-07-29, License - CC BY-SA 4.k
      mode: VehicleType.values.firstWhere(
        (e) => e.toString() == 'VehicleType.${map["mode"] as String}',
      ),
      name: map["name"] as String,
      fgColor: map["fgcolor"] as Color,
      bgColor: map["bgcolor"] as Color,
    );
  }

  // TODO: EXPLAIN!!!!!!!!!!!!
  static const _vehicleTypeAsCatCode = [
    VehicleType.metro,
    VehicleType.subway,
    VehicleType.PLACEHOLDER,
    VehicleType.bus,
    VehicleType.PLACEHOLDER,
    VehicleType.PLACEHOLDER,
    VehicleType.regionalTrain,
  ];

  factory Line.fromJson(Map<String, dynamic> json) {
    Line line = Line(
      lineId: json["lineId"] ?? 'NO ID FOUND',
      name: json["name"] as String,
      mode: _vehicleTypeAsCatCode[int.parse(json["catCode"] as String)],
      fgColor:
          Color.fromJson(json["icon"]["foregroundColor"]) as Color? ??
          Color(r: 255, g: 255, b: 255), // fallback
      bgColor:
          Color.fromJson(json["icon"]["backgroundColor"]) as Color? ??
          Color(r: 50, g: 50, b: 50), // fallback
    );
    line.dbInsert();
    return line;
  }
  static void initLineTable() async {
    Database database = DbHelper.db;
    database.execute("""CREATE TABLE IF NOT EXISTS '$tableName'(
      lineid      TEXT PRIMARY KEY,
      destination TEXT,
      direction   TEXT,
      mode        TEXT,
      name        TEXT,
      fgcolor     TEXT,
      bgcolor     TEXT
    );
    """);
  }

  static Future<Line> dbGet(String lineId) async {
    Database database = DbHelper.db;
    var tmp = await database.query(
      tableName,
      where: "lineid = ?",
      whereArgs: [lineId],
    );
    return Line.fromMap(tmp.first);
  }

  void dbInsert() async {
    Database database = DbHelper.db;
    database.insert(
      tableName,
      _toMap(),
      conflictAlgorithm: ConflictAlgorithm.ignore,
    );
  }

  void dbUpdate() async {
    Database database = DbHelper.db;
    database.update(
      tableName,
      _toMap(),
      where: "lineid = ?",
      whereArgs: [lineId],
    );
  }

  void dbDelete() async {
    Database database = DbHelper.db;
    database.delete(tableName, where: "lineid = ?", whereArgs: [lineId]);
  }
}

class Departure {
  static const String tableName = "departure";

  int? id;
  String ref;
  String direction;
  String vehicleId;
  String vehicleName;
  String stationId;
  String stationName;
  DateTime arrivalTime;
  DateTime departureTime;

  Departure({
    this.id,
    required this.ref,
    required this.direction,
    required this.vehicleId,
    required this.vehicleName,
    required this.stationId,
    required this.stationName,
    required this.arrivalTime,
    required this.departureTime,
  });

  Map<String, Object?> _toMap() {
    return {
      "ref": ref,
      "direction": direction,
      "vehicle": vehicleId,
      "vehiclename": vehicleName,
      "station": stationId,
      "stationname": stationName,
      "arrivaltime": arrivalTime.toString(),
      "departuretime": departureTime.toString(),
    };
  }

  factory Departure.fromMap(Map<String, Object?> map) {
    return Departure(
      id: map["id"] as int,
      ref: map["ref"] as String,
      direction: map["direction"] as String,
      vehicleId: map["vehicle"] as String,
      vehicleName: map["vehiclename"] as String,
      stationId: map["station"] as String,
      stationName: map["stationname"] as String,
      arrivalTime: DateTime.parse(map["arrivaltime"] as String),
      departureTime: DateTime.parse(map["departuretime"] as String),
    );
  }

  // this function is built to construct from different json sources
  // for our hafas api, that means requests to both /departureBoard and /tripDetail
  // sources from departureBoard were the default, but with some adaptations,
  // tripDetail is a working source too, with the following workarounds:
  //
  // tripDetail has stops as list in a sublevel, whereas "ref", and "Product"
  // are in the top Level.
  // stop/station infos on the other hand just use different keys
  // Therefore this function uses some catches to serve both cases and needs to
  // be served a somewhat expanded json Map, as seen in
  // ApiHander._expandDeparturesJson, which copies the topLevel fields into the
  // repeated "Stop" entries that this function takes as json param
  factory Departure.fromJson(Map<String, dynamic> json) {
    Vehicle vehicle = Vehicle.fromJson(json["Product"][0]);
    vehicle.dbInsert();
    Station station = Station(
      id: json["stopid"] ?? json["id"],
      name: json["stop"] ?? json["name"],
      transportLines: [],
    );
    station.dbInsert();

    // sometimes no arrivalTime exists, e.g. on the first station in a list
    // and sometimes no departureTime, as for the last station...
    // we might in these cases not need to ask for them, but I like a little
    // redundancy more than handling null values
    DateTime? arrDateTime;
    String? arrivalDate = json["arrDate"];
    String? arrivalTime = json["arrTime"];
    if (arrivalDate != null && arrivalTime != null) {
      arrDateTime = DateTime.parse('${arrivalDate}T$arrivalTime');
    }

    DateTime? depDateTime;
    String? departureDate = json["depDate"] ?? json["date"];
    String? departureTime = json["depTime"] ?? json["time"];
    if (departureDate != null && departureTime != null) {
      depDateTime = DateTime.parse('${departureDate}T$departureTime');
    }

    return Departure(
      // asking for tripDetail version first because
      // ["JourneyDetailRef"]["ref"] cannot be caught if ["JourneyDetailRef"]
      // is non-existent
      ref: json["ref"] ?? json["JourneyDetailRef"]["ref"] as String,
      direction:
          json["direction"] ??
          json["Directions"]["Direction"][0]["value"], // this is not healthy
      vehicleId: vehicle.vehicleid,
      vehicleName: vehicle.name,
      stationId: station.id,
      stationName: station.name,
      arrivalTime:
          arrDateTime ??
          depDateTime as DateTime, // one of these has to be in the json
      departureTime:
          depDateTime ??
          arrDateTime as DateTime, // one of these has to be in the json
    );
  }

  static void initDepartureTable() async {
    Database database = DbHelper.db;
    String vehicle = Vehicle.tableName;
    String station = Station.tableName;
    database.execute("""CREATE TABLE IF NOT EXISTS '$tableName'(
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      ref             TEXT,
      direction       TEXT,
      vehicle         TEXT,
      vehiclename     TEXT,
      station         TEXT,
      stationname     TEXT,
      arrivaltime     TEXT,
      departuretime   TEXT,
      FOREIGN KEY(vehicle) REFERENCES '$vehicle'(vehicleid),
      FOREIGN KEY(station) REFERENCES '$station'(stationid)
     );
     """);
  }

  static Future<List<Departure>> dbGetByVehicle(
    String vehicleId, {
    DateTime? intervalStart,
  }) async {
    Database database = DbHelper.db;
    intervalStart ??= DateTime.now(); // assign if null
    int limit = 50;
    List results = await database.query(
      tableName,
      where: "vehicle= ? AND departuretime > ?",
      whereArgs: [
        vehicleId,
        intervalStart,
      ],
      orderBy: "departuretime ASC",
      limit: limit,
    );
    return results.map((dep) => Departure.fromMap(dep)).toList();
  }

  static Future<List<Departure>> dbGetByStation(
    String stationId, {
    DateTime? intervalStart,
  }) async {
    Database database = DbHelper.db;
    intervalStart ??= DateTime.now(); // assign if null
    int limit = 50;
    List results = await database.query(
      tableName,
      where: "station = ? AND departuretime > ?",
      whereArgs: [stationId, intervalStart],
      orderBy: "departuretime",
      limit: limit,
    );
    return results.map((dep) => Departure.fromMap(dep)).toList();
  }

  static Future<Departure> dbGet(int id) async {
    Database database = DbHelper.db;
    var depMap = await database.query(
      tableName,
      where: "id = ?",
      whereArgs: [id],
    );
    return Departure.fromMap(depMap.first);
  }

  void dbInsert() async {
    Database database = DbHelper.db;
    id = await database.insert(
      tableName,
      _toMap(),
      conflictAlgorithm: ConflictAlgorithm.ignore,
    );
  }

  void dbUpdate() async {
    Database database = DbHelper.db;
    id = await database.update(
      tableName,
      _toMap(),
      where: "id = ?",
      whereArgs: [id],
    );
  }

  void dbDelete() async {
    Database database = DbHelper.db;
    database.delete(tableName, where: "id = ?", whereArgs: [id]);
  }
}

class Color {
  int r;
  int g;
  int b;

  Color({
    required this.r,
    required this.g,
    required this.b,
  });

  factory Color.fromStringRGBComma(String input) {
    List<String> split = input.split(",");
    if (split.length == 3) {
      return Color(
        r: int.parse(split[0]),
        g: int.parse(split[1]),
        b: int.parse(split[2]),
      );
    }
    return Color(r: 128, g: 128, b: 128);
  }

  factory Color.fromJson(Map<String, dynamic> json) => Color(
    r: json["r"],
    g: json["g"],
    b: json["b"],
  );

  @override
  String toString() {
    return '$r,$g,$b';
  }
}

enum VehicleType {
  bus,
  tram,
  metro,
  subway,
  regionalTrain,
  PLACEHOLDER,
}