XFEEDToolBox

using System.Diagnostics;
using System.Globalization;
using System.Text;
using System.Text.Json;
using Microsoft.Data.Sqlite;
using XFEEDToolBox.Core.Models;

if(args.Length is not(6 or 7))throw new ArgumentException("database report.json originSystem ox oy oz [radiusLy=50]");
var database=Path.GetFullPath(args[0]);var report=Path.GetFullPath(args[1]);var originSystem=args[2];
double Number(string text)=>double.TryParse(text,NumberStyles.Float,CultureInfo.InvariantCulture,out var value)&&double.IsFinite(value)?value:throw new ArgumentException("Coordinates and radius must be finite invariant-culture numbers");
var origin=new Coordinates(Number(args[3]),Number(args[4]),Number(args[5]));var radius=args.Length==7?Number(args[6]):50;
if(string.IsNullOrWhiteSpace(originSystem)||radius<=0||radius>10000)throw new ArgumentException("Origin system and radius in (0,10000] required");
if(!File.Exists(database))throw new FileNotFoundException("A completed existing database is required",database);
if(report.Equals(database,StringComparison.OrdinalIgnoreCase))throw new ArgumentException("Report cannot replace the database");
Directory.CreateDirectory(Path.GetDirectoryName(report)!);
var asOf=DateTimeOffset.UtcNow;var cutoff=asOf.AddHours(-6);var timer=Stopwatch.StartNew();
var json=ToolboxSettings.JsonOptions;var costs=new Dictionary<string,double>();
void WriteReport(object value)
{var temporary=report+".tmp";File.WriteAllText(temporary,JsonSerializer.Serialize(value,new JsonSerializerOptions{WriteIndented=true}));File.Move(temporary,report,true);}
DateTimeOffset? At(object? value)=>value is null or DBNull?null:DateTimeOffset.FromUnixTimeMilliseconds(Convert.ToInt64(value,CultureInfo.InvariantCulture));
string Source(long source)=>Enum.IsDefined(typeof(DataSource),(int)source)?((DataSource)source).ToString():"Unknown("+source+")";
var initial=new FileInfo(database);var initialBytes=initial.Length;var initialWrite=initial.LastWriteTimeUtc;
using var connection=new SqliteConnection(new SqliteConnectionStringBuilder{DataSource=database,Mode=SqliteOpenMode.ReadOnly,Pooling=false,DefaultTimeout=60}.ToString());
connection.Open();using var transaction=connection.BeginTransaction(deferred:true);
SqliteCommand Command(string sql)
{
    var command=connection.CreateCommand();command.Transaction=transaction;command.CommandText=sql;
    command.Parameters.AddWithValue("$from",cutoff.ToUnixTimeMilliseconds());command.Parameters.AddWithValue("$to",asOf.ToUnixTimeMilliseconds());
    command.Parameters.AddWithValue("$ox",origin.X);command.Parameters.AddWithValue("$oy",origin.Y);command.Parameters.AddWithValue("$oz",origin.Z);
    command.Parameters.AddWithValue("$radius",radius);command.Parameters.AddWithValue("$radius2",radius*radius);command.Parameters.AddWithValue("$origin",originSystem);
    return command;
}
T Timed<T>(string name,Func<T> action){var watch=Stopwatch.StartNew();try{return action();}finally{costs[name]=watch.Elapsed.TotalMilliseconds;}}
long Count(string name,string sql)=>Timed(name,()=>{using var command=Command(sql);return (long)command.ExecuteScalar()!;});
var columns="""
    SELECT s.id,s.name,s.system,s.x,s.y,s.z,s.json,p.timestamp,p.source,
      (SELECT count(*) FROM quotes q WHERE q.station=s.id),
      (SELECT count(*) FROM quotes q WHERE q.station=s.id AND q.timestamp BETWEEN $from AND $to)
    FROM stations s LEFT JOIN snapshots p ON p.station=s.id
    """;
List<StationCoverage> Stations(string name,string where)=>Timed(name,()=>
{
    using var command=Command(columns+" WHERE "+where+" ORDER BY s.system,s.name,s.id");using var reader=command.ExecuteReader();var rows=new List<StationCoverage>();
    while(reader.Read())
    {
        var station=JsonSerializer.Deserialize<Station>(reader.GetString(6),json)??throw new InvalidDataException("Missing station JSON");
        if(station.MarketId!=reader.GetInt64(0))throw new InvalidDataException("Station identity differs between SQLite and JSON");
        Coordinates? position=reader.IsDBNull(3)||reader.IsDBNull(4)||reader.IsDBNull(5)?null:new(reader.GetDouble(3),reader.GetDouble(4),reader.GetDouble(5));
        rows.Add(new(station,position?.DistanceTo(origin),At(reader.IsDBNull(7)?null:reader.GetInt64(7)),reader.IsDBNull(8)?null:Source(reader.GetInt64(8)),reader.GetInt64(9),reader.GetInt64(10)));
    }
    return rows;
});
try
{
    var version=Timed("sqliteVersion",()=>{using var command=Command("SELECT sqlite_version()");return (string)command.ExecuteScalar()!;});
    var stations=Count("totalStations","SELECT count(*) FROM stations");
    var quotes=Count("totalQuotes","SELECT count(*) FROM quotes");
    var snapshots=Count("totalSnapshots","SELECT count(*) FROM snapshots");
    var coordinates=Count("stationsWithCoordinates","SELECT count(*) FROM stations WHERE x IS NOT NULL AND y IS NOT NULL AND z IS NOT NULL");
    var oldest=Timed("oldestQuote",()=>{using var command=Command("SELECT min(timestamp) FROM quotes");return At(command.ExecuteScalar());});
    var newest=Timed("newestQuote",()=>{using var command=Command("SELECT max(timestamp) FROM quotes");return At(command.ExecuteScalar());});
    var freshQuotes=Count("globalFreshQuotes","SELECT count(*) FROM quotes WHERE timestamp BETWEEN $from AND $to");
    var freshMarkets=Count("globalFreshQuotedMarkets","SELECT count(*) FROM snapshots s WHERE s.timestamp BETWEEN $from AND $to AND EXISTS(SELECT 1 FROM quotes q WHERE q.station=s.station AND q.timestamp BETWEEN $from AND $to)");
    var futureQuotes=Count("futureQuotes","SELECT count(*) FROM quotes WHERE timestamp>$to");
    var sources=Timed("snapshotSources",()=>
    {
        using var command=Command("SELECT source,count(*),min(timestamp),max(timestamp) FROM snapshots GROUP BY source ORDER BY source");using var reader=command.ExecuteReader();var values=new List<object>();
        while(reader.Read())values.Add(new{source=Source(reader.GetInt64(0)),sourceId=reader.GetInt64(0),markets=reader.GetInt64(1),oldest=At(reader.GetValue(2)),newest=At(reader.GetValue(3))});
        return values;
    });
    var region=Stations("regionalStations","""
        s.x BETWEEN $ox-$radius AND $ox+$radius AND s.y BETWEEN $oy-$radius AND $oy+$radius AND s.z BETWEEN $oz-$radius AND $oz+$radius
        AND (s.x-$ox)*(s.x-$ox)+(s.y-$oy)*(s.y-$oy)+(s.z-$oz)*(s.z-$oz)<=$radius2
        """);
    var exact=Stations("exactOriginStations","s.system=$origin COLLATE NOCASE");
    object Summary(IReadOnlyList<StationCoverage> rows)=>new{stations=rows.Count,quotedMarkets=rows.Count(row=>row.Quotes>0),quotes=rows.Sum(row=>row.Quotes),freshQuotedMarkets=rows.Count(row=>row.FreshQuotes>0),freshQuotes=rows.Sum(row=>row.FreshQuotes),standardLargeUsableStations=rows.Count(row=>row.StandardLargeUsable),standardLargeFreshQuotedMarkets=rows.Count(row=>row.StandardLargeUsable&&row.FreshQuotes>0),standardLargeFreshQuotes=rows.Where(row=>row.StandardLargeUsable).Sum(row=>row.FreshQuotes),newestSnapshot=rows.MaxBy(row=>row.SnapshotAt)?.SnapshotAt};
    var fresh=region.Where(row=>row.FreshQuotes>0).OrderByDescending(row=>row.SnapshotAt).ThenBy(row=>row.DistanceLy).ThenBy(row=>row.Station.MarketId).ToArray();
    var sample=fresh.Take(25).Select(row=>new{row.Station.MarketId,row.Station.Name,row.Station.SystemName,row.DistanceLy,row.SnapshotAt,row.Source,row.Quotes,row.FreshQuotes,row.StandardLargeUsable,row.Station.Type,pad=row.Station.MaxLandingPad.ToString(),row.Station.IsFleetCarrier,row.Station.IsPlanetary,row.Station.IsRestricted,row.Station.RequiresPermit}).ToArray();
    var quoteSamples=Timed("realQuoteSamples",()=>
    {
        var result=new List<object>();
        foreach(var station in fresh.Where(row=>row.StandardLargeUsable).Concat(fresh).DistinctBy(row=>row.Station.MarketId).Take(5))
        {
            using var command=Command("SELECT commodity,name,buy,sell,supply,demand,timestamp,source FROM quotes WHERE station=$station AND timestamp BETWEEN $from AND $to ORDER BY commodity LIMIT 5");
            command.Parameters.AddWithValue("$station",station.Station.MarketId);using var reader=command.ExecuteReader();
            while(reader.Read())result.Add(new{marketId=station.Station.MarketId,system=station.Station.SystemName,station=station.Station.Name,commodity=reader.GetString(0),name=reader.GetString(1),buy=reader.GetInt64(2),sell=reader.GetInt64(3),supply=reader.GetInt64(4),demand=reader.GetInt64(5),collectedAt=At(reader.GetInt64(6)),source=Source(reader.GetInt64(7))});
        }
        return result;
    });
    var csv=Path.ChangeExtension(report,"fresh-stations.csv");
    if(csv.Equals(database,StringComparison.OrdinalIgnoreCase))throw new ArgumentException("CSV cannot replace database");
    string Cell(object? value)=>"\""+(value switch{null=>"",double number=>number.ToString("R",CultureInfo.InvariantCulture),DateTimeOffset at=>at.ToString("O",CultureInfo.InvariantCulture),IFormattable item=>item.ToString(null,CultureInfo.InvariantCulture),_=>value.ToString()})!.Replace("\"","\"\"")+"\"";
    var text=new StringBuilder("marketId,system,station,distanceLy,snapshotAt,source,quotes,freshQuotes,standardLargeUsable,type,pad,isFleetCarrier,isPlanetary,isRestricted,requiresPermit\r\n");
    foreach(var row in fresh)text.AppendLine(string.Join(',',new object?[]{row.Station.MarketId,row.Station.SystemName,row.Station.Name,row.DistanceLy,row.SnapshotAt,row.Source,row.Quotes,row.FreshQuotes,row.StandardLargeUsable,row.Station.Type,row.Station.MaxLandingPad,row.Station.IsFleetCarrier,row.Station.IsPlanetary,row.Station.IsRestricted,row.Station.RequiresPermit}.Select(Cell)));
    File.WriteAllText(csv,text.ToString(),new UTF8Encoding(false));
    transaction.Commit();
    var after=new FileInfo(database);
    WriteReport(new{status="complete",database,bytes=initialBytes,lastWriteUtc=initialWrite,bytesAfter=after.Length,lastWriteUtcAfter=after.LastWriteTimeUtc,sqliteVersion=version,connectionMode="ReadOnly",pooling=false,asOf,window=new{hours=6,from=cutoff,to=asOf,excludesFuture=true},totals=new{stations,stationsWithCoordinates=coordinates,quotes,snapshots,oldestQuote=oldest,newestQuote=newest},globalSixHours=new{quotes=freshQuotes,quotedMarkets=freshMarkets},futureQuotes,snapshotSources=sources,origin=new{system=originSystem,position=origin,radiusLy=radius},regional=Summary(region),exactOrigin=Summary(exact),standardLargePolicy=new{pad="Large",fleetCarriers=false,planetary=false,restricted=false,requiresPermit=false},freshStationSample=sample,realQuoteSamples=quoteSamples,freshStationsCsv=csv,queryMilliseconds=costs,elapsedSeconds=timer.Elapsed.TotalSeconds});
    Console.WriteLine(JsonSerializer.Serialize(new{status="complete",stations,quotes,snapshots,freshQuotes,freshMarkets,regionalFreshQuotes=region.Sum(row=>row.FreshQuotes),regionalFreshMarkets=fresh.Length,report,csv,elapsedSeconds=timer.Elapsed.TotalSeconds}));
}
catch(Exception error){WriteReport(new{status="failed",database,asOf,error=error.ToString(),queryMilliseconds=costs,elapsedSeconds=timer.Elapsed.TotalSeconds});throw;}

sealed record StationCoverage(Station Station,double? DistanceLy,DateTimeOffset? SnapshotAt,string? Source,long Quotes,long FreshQuotes)
{
    public bool StandardLargeUsable=>Station.MaxLandingPad==LandingPad.Large&&!Station.IsFleetCarrier&&!Station.IsPlanetary&&!Station.IsRestricted&&!Station.RequiresPermit;
}