Some rework on the ServiceViewer (which will be repurposed for live multi-GTFS serving)

8 files changed, 76 insertions, 35 deletions

diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Feed.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Feed.cs
new file mode 100644
index 0000000..065250b
--- /dev/null
+++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/Feed.cs
@@ -0,0 +1,21 @@
+using System.ComponentModel.DataAnnotations;
+using System.ComponentModel.DataAnnotations.Schema;
+
+namespace Costasdev.ServiceViewer.Data.Gtfs;
+
+[Table("feeds")]
+public class Feed
+{
+    /// <summary>
+    /// Auto-incrementing ID value for each feed, to identify it and its version
+    /// </summary>
+    [Key]
+    public int Id { get; set; }
+
+    [MaxLength(32)] public string ShortName { get; set; }
+    [MaxLength(32)] public string LongName { get; set; }
+    [MaxLength(255)] public string DownloadUrl { get; set; }
+    [MaxLength(32)] public string Etag { get; set; }
+
+    public DateTime InsertedAt { get; set; }
+}
diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsAgency.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsAgency.cs
index 999adb8..89b5518 100644
--- a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsAgency.cs
+++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsAgency.cs
@@ -1,9 +1,11 @@
 using System.ComponentModel.DataAnnotations;
 using System.ComponentModel.DataAnnotations.Schema;
+using Microsoft.EntityFrameworkCore;
 
 namespace Costasdev.ServiceViewer.Data.Gtfs;
 
-[Table("agencies")]
+[Table("gtfs_agencies")]
+[PrimaryKey(nameof(Id), nameof(FeedId))]
 public class GtfsAgency
 {
     [Key]
@@ -11,6 +13,9 @@ public class GtfsAgency
     [MaxLength(255)]
     public required string Id { get; set; }
 
+    [Column("feed_id")] public int FeedId { get; set; }
+    [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; }
+
     [Column("agency_name")]
     [MaxLength(255)]
     public string Name { get; set; } = string.Empty;
diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendar.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendar.cs
index 56f3f85..cfb144c 100644
--- a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendar.cs
+++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendar.cs
@@ -1,9 +1,11 @@
 using System.ComponentModel.DataAnnotations;
 using System.ComponentModel.DataAnnotations.Schema;
+using Microsoft.EntityFrameworkCore;
 
 namespace Costasdev.ServiceViewer.Data.Gtfs;
 
-[Table("calendar")]
+[Table("gtfs_calendar")]
+[PrimaryKey(nameof(ServiceId), nameof(FeedId))]
 public class GtfsCalendar
 {
     [Key]
@@ -11,6 +13,9 @@ public class GtfsCalendar
     [MaxLength(32)]
     public string ServiceId { get; set; } = null!;
 
+    [Column("feed_id")] public int FeedId { get; set; }
+    [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; }
+
     [Column("monday")]
     public bool Monday { get; set; }
 
diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs
index 977fb74..1543ef5 100644
--- a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs
+++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs
@@ -5,8 +5,8 @@ using Microsoft.EntityFrameworkCore;
 
 namespace Costasdev.ServiceViewer.Data.Gtfs;
 
-[Table("calendar_dates")]
-[PrimaryKey(nameof(ServiceId), nameof(Date))]
+[Table("gtfs_calendar_dates")]
+[PrimaryKey(nameof(ServiceId), nameof(Date), nameof(FeedId))]
 public class GtfsCalendarDate
 {
     [Column("service_id")]
@@ -16,6 +16,9 @@ public class GtfsCalendarDate
     [Column("date")]
     public required DateTime Date { get; set; }
 
+    [Column("feed_id")] public int FeedId { get; set; }
+    [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; }
+
     [Column("exception_type")]
     public required ExceptionType ExceptionType { get; set; }
 }
diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsRoute.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsRoute.cs
index 261e183..c34353c 100644
--- a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsRoute.cs
+++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsRoute.cs
@@ -1,26 +1,30 @@
 using System.ComponentModel.DataAnnotations;
 using System.ComponentModel.DataAnnotations.Schema;
 using Costasdev.ServiceViewer.Data.Gtfs.Enums;
+using Microsoft.EntityFrameworkCore;
 
 namespace Costasdev.ServiceViewer.Data.Gtfs;
 
-[Table("routes")]
+[Table("gtfs_routes")]
+[PrimaryKey(nameof(Id), nameof(FeedId))]
 public class GtfsRoute
 {
-    [Key]
     [Column("route_id")]
     [MaxLength(255)]
     public required string Id { get; set; }
 
     public string SafeId => Id.Replace(" ", "_").Replace("-", "_");
 
+    [Column("feed_id")]public int FeedId { get; set; }
+    [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; }
+
     [Column("agency_id")]
-    [ForeignKey(nameof(GtfsAgency))]
+    [ForeignKey(nameof(Agency))]
     [MaxLength(255)]
     public required string AgencyId { get; set; }
 
     [ForeignKey(nameof(AgencyId))]
-    public GtfsAgency GtfsAgency { get; set; } = null!;
+    public GtfsAgency Agency { get; set; } = null!;
 
     /// <summary>
     /// Short name of a route. Often a short, abstract identifier (e.g., "32", "100X", "Green")
diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStop.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStop.cs
index 20900d7..f604c5f 100644
--- a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStop.cs
+++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStop.cs
@@ -1,17 +1,22 @@
 using System.ComponentModel.DataAnnotations;
 using System.ComponentModel.DataAnnotations.Schema;
 using Costasdev.ServiceViewer.Data.Gtfs.Enums;
+using Microsoft.EntityFrameworkCore;
+using NetTopologySuite.Geometries;
 
 namespace Costasdev.ServiceViewer.Data.Gtfs;
 
-[Table("stops")]
+[Table("gtfs_stops")]
+[PrimaryKey(nameof(Id), nameof(FeedId))]
 public class GtfsStop
 {
-    [Key]
     [Column("stop_id")]
     [MaxLength(32)]
     public required string Id { get; set; }
 
+    [Column("feed_id")]public int FeedId { get; set; }
+    [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; }
+
     [Column("stop_code")]
     [MaxLength(32)]
     public string Code { get; set; } = string.Empty;
@@ -24,11 +29,8 @@ public class GtfsStop
     [MaxLength(255)]
     public string? Description { get; set; }
 
-    [Column("stop_lat")]
-    public double Latitude { get; set; }
-
-    [Column("stop_lon")]
-    public double Longitude { get; set; }
+    [Column("stop_pos")]
+    public Point? Position { get; set; }
 
     [Column("stop_url")]
     [MaxLength(255)]
diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStopTime.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStopTime.cs
index 07b6732..9599947 100644
--- a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStopTime.cs
+++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsStopTime.cs
@@ -1,11 +1,12 @@
 using System.ComponentModel.DataAnnotations;
 using System.ComponentModel.DataAnnotations.Schema;
+using Costasdev.ServiceViewer.Data.Extensions;
 using Microsoft.EntityFrameworkCore;
 
 namespace Costasdev.ServiceViewer.Data.Gtfs;
 
-[Table("stop_times")]
-[PrimaryKey(nameof(TripId), nameof(StopSequence))]
+[Table("gtfs_stop_times")]
+[PrimaryKey(nameof(TripId), nameof(StopSequence), nameof(FeedId))]
 public class GtfsStopTime
 {
     [Column("trip_id")]
@@ -13,13 +14,16 @@ public class GtfsStopTime
     [MaxLength(32)]
     public string TripId { get; set; } = null!;
 
+    [Column("feed_id")]public int FeedId { get; set; }
+    [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; }
+
     [ForeignKey(nameof(TripId))] public GtfsTrip GtfsTrip { get; set; } = null!;
 
-    [Column("arrival_time")] public string ArrivalTime { get; set; }
-    public TimeOnly ArrivalTimeOnly => TimeOnly.Parse(ArrivalTime);
+    [Column("arrival_time")] public string Arrival { get; set; }
+    public TimeSpan ArrivalTime => TimeSpan.FromGtfsTime(Arrival);
 
-    [Column("departure_time")] public string DepartureTime { get; set; }
-    public TimeOnly DepartureTimeOnly => TimeOnly.Parse(DepartureTime);
+    [Column("departure_time")] public string Departure { get; set; }
+    public TimeSpan DepartureTime => TimeSpan.FromGtfsTime(Departure);
 
     [Column("stop_id")]
     [ForeignKey(nameof(GtfsStop))]
diff --git a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsTrip.cs b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsTrip.cs
index d68cbdd..3614120 100644
--- a/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsTrip.cs
+++ b/src/Costasdev.Busurbano.ServiceViewer/Data/Gtfs/GtfsTrip.cs
@@ -1,28 +1,27 @@
 using System.ComponentModel.DataAnnotations;
 using System.ComponentModel.DataAnnotations.Schema;
 using Costasdev.ServiceViewer.Data.Gtfs.Enums;
+using Microsoft.EntityFrameworkCore;
 
 namespace Costasdev.ServiceViewer.Data.Gtfs;
 
-[Table("trips")]
+[Table("gtfs_trips")]
+[PrimaryKey(nameof(Id), nameof(FeedId))]
 public class GtfsTrip
 {
-    [Key]
-    [Column("trip_id")]
-    [MaxLength(32)]
-    public string TripId { get; set; } = null!;
+    [Column("trip_id")] [MaxLength(32)] public string Id { get; set; } = null!;
+
+    [Column("feed_id")] public int FeedId { get; set; }
+    [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; }
 
     [Column("route_id")]
     [MaxLength(32)]
     [ForeignKey(nameof(Route))]
     public string RouteId { get; set; } = null!;
 
-    [ForeignKey(nameof(RouteId))]
-    public GtfsRoute Route { get; set; } = null!;
+    [ForeignKey(nameof(RouteId))] public GtfsRoute Route { get; set; } = null!;
 
-    [Column("service_id")]
-    [MaxLength(32)]
-    public string ServiceId { get; set; } = null!;
+    [Column("service_id")] [MaxLength(32)] public string ServiceId { get; set; } = null!;
 
     [Column("trip_headsign")]
     [MaxLength(255)]
@@ -32,8 +31,7 @@ public class GtfsTrip
     [MaxLength(255)]
     public string? TripShortName { get; set; }
 
-    [Column("direction_id")]
-    public DirectionId DirectionId { get; set; } = DirectionId.Outbound;
+    [Column("direction_id")] public DirectionId DirectionId { get; set; } = DirectionId.Outbound;
 
     /// <summary>
     /// Identifies the block to which the trip belongs. A block consists of a single trip or many
@@ -55,6 +53,5 @@ public class GtfsTrip
     [Column("trip_wheelchair_accessible")]
     public TripWheelchairAccessible TripWheelchairAccessible { get; set; } = TripWheelchairAccessible.Empty;
 
-    [Column("trip_bikes_allowed")]
-    public TripBikesAllowed TripBikesAllowed { get; set; } = TripBikesAllowed.Empty;
+    [Column("trip_bikes_allowed")] public TripBikesAllowed TripBikesAllowed { get; set; } = TripBikesAllowed.Empty;
 }