using System.Diagnostics;
using System.IO.Compression;
using System.Security.Cryptography;
using System.Text.Json;
using Microsoft.Data.Sqlite;
using XFEEDToolBox.Core.Data;
using XFEEDToolBox.Core.Models;

if(args.Length is <4 or >5||args[0] is not("import" or "activate" or "activate-move" or "reference" or "reopen" or "seal"))throw new ArgumentException("import|reference <official.json.gz> <new.db> <report.json> | reopen <db> <first-integrity-receipt.json> <reopen-report.json> | seal <activated.db> <activation.json> <startup-report.json> | activate|activate-move <verified.db> <original.db> <activation-report.json> <completed-import-report.json>");
var source=Path.GetFullPath(args[1]);var destination=Path.GetFullPath(args[2]);var report=Path.GetFullPath(args[3]);
Directory.CreateDirectory(Path.GetDirectoryName(report)!);
var timer=Stopwatch.StartNew();var json=new JsonSerializerOptions(ToolboxSettings.JsonOptions){WriteIndented=false};
void Report(object value){var temporary=report+".tmp";File.WriteAllText(temporary,JsonSerializer.Serialize(value,new JsonSerializerOptions{WriteIndented=true}));File.Move(temporary,report,true);}
SqliteConnection Open(string file,SqliteOpenMode mode){var connection=new SqliteConnection(new SqliteConnectionStringBuilder{DataSource=file,Mode=mode,Pooling=false,DefaultTimeout=60}.ToString());connection.Open();return connection;}
object[] Health(SqliteConnection connection)
{
    using var command=connection.CreateCommand();command.CommandText="PRAGMA integrity_check";
    using(var reader=command.ExecuteReader()){while(reader.Read())if(reader.GetString(0)!="ok")throw new InvalidDataException(reader.GetString(0));}
    command.CommandText="SELECT (SELECT count(*) FROM stations),(SELECT count(*) FROM snapshots),sqlite_version()";
    long stationCount,snapshotCount;string version;
    using(var rows=command.ExecuteReader()){rows.Read();stationCount=rows.GetInt64(0);snapshotCount=rows.GetInt64(1);version=rows.GetString(2);}
    command.CommandText="SELECT count(*),max(q.timestamp),coalesce(sum(q.source!=2),0),coalesce(sum(s.station IS NULL OR s.timestamp!=q.timestamp),0) FROM quotes q LEFT JOIN snapshots s ON q.station=s.station";
    using var quotes=command.ExecuteReader();quotes.Read();
    if(stationCount==0||quotes.GetInt64(0)==0||quotes.GetInt64(2)!=0||quotes.GetInt64(3)!=0)throw new InvalidDataException("Incomplete imported cache or invalid provenance");
    return [stationCount,quotes.GetInt64(0),snapshotCount,quotes.GetInt64(1),quotes.GetInt64(2),quotes.GetInt64(3),version];
}
object[] Summary(SqliteConnection connection)
{
    using var command=connection.CreateCommand();
    command.CommandText="SELECT (SELECT count(*) FROM stations),(SELECT count(*) FROM quotes),(SELECT count(*) FROM snapshots),(SELECT max(timestamp) FROM quotes),sqlite_version()";
    using var reader=command.ExecuteReader();reader.Read();var values=new object[reader.FieldCount];reader.GetValues(values);return values;
}
string ExclusiveSha256(string path)
{
    using var file=new FileStream(path,FileMode.Open,FileAccess.Read,FileShare.None,4*1024*1024,FileOptions.SequentialScan);
    return Convert.ToHexStringLower(SHA256.HashData(file));
}
if(args[0]=="seal")
{
    using var activation=JsonDocument.Parse(File.ReadAllText(destination));var accepted=activation.RootElement;
    var sourceInfo=new FileInfo(source);
    if(accepted.GetProperty("status").GetString()!="activated"||!Path.GetFullPath(accepted.GetProperty("generation").GetString()!).Equals(source,StringComparison.OrdinalIgnoreCase)
        ||accepted.GetProperty("sourceSha256").GetString()!=accepted.GetProperty("generationSha256").GetString()
        ||sourceInfo.Length!=accepted.GetProperty("sourceBytes").GetInt64()||sourceInfo.LastWriteTimeUtc!=accepted.GetProperty("sourceModified").GetDateTime().ToUniversalTime())
        throw new InvalidDataException("Generation differs from verified activation evidence");
    var summary=accepted.GetProperty("reopenedSummary");
    var health=new MarketCacheHealth(summary[0].GetInt64(),summary[1].GetInt64(),summary[2].GetInt64(),summary[4].GetString()!);
    new MarketRepository(source).RecordVerifiedGeneration(health,summary[3].GetInt64(),accepted.GetProperty("generationSha256").GetString()!);
    var probes=new List<object>();
    for(var attempt=1;attempt<=2;attempt++)
    {
        SqliteConnection.ClearAllPools();var startupRepository=new MarketRepository(accepted.GetProperty("original").GetString()!);var watch=Stopwatch.StartNew();startupRepository.Initialize();var initializeMs=watch.Elapsed.TotalMilliseconds;
        watch.Restart();var stats=startupRepository.Statistics();for(var sample=0;sample<9;sample++)startupRepository.Statistics();var statisticsMs=watch.Elapsed.TotalMilliseconds;
        if(stats.Stations!=health.Stations||stats.Quotes!=health.Quotes||stats.Latest?.ToUnixTimeMilliseconds()!=summary[3].GetInt64()||startupRepository.DatabasePath!=source)
            throw new InvalidDataException("Actual startup did not reopen the verified application generation");
        probes.Add(new{attempt,initializeMs,tenStatisticsMs=statisticsMs,startupRepository.DatabasePath,stats.Stations,stats.Quotes,stats.Latest});
    }
    Report(new{status="sealed-and-started",at=DateTimeOffset.UtcNow,source,activationReceipt=destination,proof=source+".verified-generation.json",health,probes,sourceBytes=sourceInfo.Length,elapsedSeconds=timer.Elapsed.TotalSeconds});
    Console.WriteLine(JsonSerializer.Serialize(new{status="sealed-and-started",probes}));return;
}
if(args[0]=="reopen")
{
    using var first=JsonDocument.Parse(File.ReadAllText(destination));var receipt=first.RootElement;
    var workspace=Path.GetFullPath(Path.Combine(Environment.CurrentDirectory,"artifacts"))+Path.DirectorySeparatorChar;
    if(!source.StartsWith(workspace,StringComparison.OrdinalIgnoreCase)||receipt.GetProperty("status").GetString()!="verified"
        ||!Path.GetFullPath(receipt.GetProperty("destination").GetString()!).Equals(source,StringComparison.OrdinalIgnoreCase))
        throw new InvalidDataException("Independent reopen requires this workspace's completed first integrity receipt");
    var before=new FileInfo(source);var sourceBytes=before.Length;var modified=before.LastWriteTimeUtc;
    if(File.Exists(source+"-wal")&&new FileInfo(source+"-wal").Length!=0)throw new IOException("Independent import still has WAL content");
    using(var exclusive=File.OpenHandle(source,FileMode.Open,FileAccess.Read,FileShare.None)){}
    var health=receipt.GetProperty("health").EnumerateArray().ToArray();
    if(health.Length!=7||health[4].GetInt64()!=0||health[5].GetInt64()!=0)throw new InvalidDataException("First full integrity/provenance pass did not succeed");
    object[] actual;long appliedCache,appliedMmap;string quickCheck;
    using(var input=Open(source,SqliteOpenMode.ReadOnly))
    {
        using var configure=input.CreateCommand();configure.CommandText="PRAGMA query_only=ON; PRAGMA cache_size=-524288;";configure.ExecuteNonQuery();
        configure.CommandText="PRAGMA cache_size";appliedCache=(long)configure.ExecuteScalar()!;
        configure.CommandText="PRAGMA mmap_size=2147483648";appliedMmap=(long)configure.ExecuteScalar()!;
        Report(new{status="reopening",pid=Environment.ProcessId,source,firstIntegrityReceipt=destination,connectionMode="ReadOnly",cacheKiB=appliedCache,mmapBytes=appliedMmap,at=DateTimeOffset.UtcNow});
        Console.WriteLine($"Read-only reopen: cache={appliedCache}KiB mmap={appliedMmap}bytes");
        configure.CommandText="PRAGMA quick_check(1)";quickCheck=(string)configure.ExecuteScalar()!;
        if(quickCheck!="ok")throw new InvalidDataException("Reopened cache failed quick_check: "+quickCheck);
        actual=Summary(input);
    }
    var expected=new[]{health[0],health[1],health[2],health[3],health[6]};
    if(expected.Where((value,index)=>value.ToString()!=Convert.ToString(actual[index],System.Globalization.CultureInfo.InvariantCulture)).Any()
        ||new FileInfo(source).Length!=sourceBytes||File.GetLastWriteTimeUtc(source)!=modified)
        throw new InvalidDataException("Reopened cache differs from completed full integrity/provenance pass");
    Report(new{status="verified",at=DateTimeOffset.UtcNow,source=receipt.GetProperty("source").GetString(),destination=source,
        compressedBytes=receipt.GetProperty("compressedBytes").GetInt64(),health=health.Select(value=>value.ValueKind==JsonValueKind.Number?(object)value.GetInt64():value.GetString()!).ToArray(),
        firstFullIntegrityReceipt=destination,verification="independent-read-only-reopen",connectionMode="ReadOnly",cacheKiB=appliedCache,mmapBytes=appliedMmap,quickCheck,reopenedSummary=actual,
        sourceBytes,sourceModified=modified,sourceUnchanged=true,elapsedSeconds=timer.Elapsed.TotalSeconds});
    Console.WriteLine("Read-only reopen verified: "+source);return;
}
if(args[0].StartsWith("activate",StringComparison.Ordinal))
{
    // Only a completely built standalone SQLite file is eligible. No production file is overwritten.
    if(args.Length!=5)throw new ArgumentException("Activation requires the final verified import report");
    using var importReport=JsonDocument.Parse(File.ReadAllText(Path.GetFullPath(args[4])));
    var imported=importReport.RootElement;
    if(imported.GetProperty("status").GetString()!="verified"||!Path.GetFullPath(imported.GetProperty("destination").GetString()!).Equals(source,StringComparison.OrdinalIgnoreCase))
        throw new InvalidDataException("Activation receipt does not identify this completed import");
    var sourceDump=Path.GetFullPath(imported.GetProperty("source").GetString()!);
    if(!File.Exists(sourceDump)||new FileInfo(sourceDump).Length!=imported.GetProperty("compressedBytes").GetInt64())
        throw new InvalidDataException("Completed original source file does not match the import receipt");
    if(source.Equals(destination,StringComparison.OrdinalIgnoreCase))throw new ArgumentException("Source and original path must differ");
    var sourceInfo=new FileInfo(source);var sourceBytes=sourceInfo.Length;var sourceModified=sourceInfo.LastWriteTimeUtc;
    if(sourceModified>imported.GetProperty("at").GetDateTimeOffset().UtcDateTime||File.Exists(source+"-wal")&&new FileInfo(source+"-wal").Length!=0)
        throw new IOException("Completed import was modified or still has WAL content");
    using(var exclusive=File.OpenHandle(source,FileMode.Open,FileAccess.ReadWrite,FileShare.None)){}
    object[] expected;
    using(var input=Open(source,SqliteOpenMode.ReadWrite))
    {
        using var checkpoint=input.CreateCommand();checkpoint.CommandText="PRAGMA wal_checkpoint(TRUNCATE)";
        using(var result=checkpoint.ExecuteReader())
        {if(!result.Read()||result.GetInt64(0)!=0||result.GetInt64(1)>0)throw new IOException("Import source WAL checkpoint is still busy; do not move its main file");}
        expected=Summary(input);
    }
    var completeHealth=imported.GetProperty("health").EnumerateArray().ToArray();
    if(completeHealth.Length!=7||completeHealth[4].GetInt64()!=0||completeHealth[5].GetInt64()!=0)
        throw new InvalidDataException("Completed integrity/provenance receipt is invalid");
    var receipt=new[]{completeHealth[0],completeHealth[1],completeHealth[2],completeHealth[3],completeHealth[6]};
    if(receipt.Length!=expected.Length||receipt.Where((value,index)=>value.ToString()!=Convert.ToString(expected[index],System.Globalization.CultureInfo.InvariantCulture)).Any())
        throw new InvalidDataException("Database health differs from the completed import receipt");
    Console.WriteLine("Hashing the closed, fully verified import before activation");
    var sourceSha256=ExclusiveSha256(source);
    if(new FileInfo(source).Length!=sourceBytes||File.GetLastWriteTimeUtc(source)!=sourceModified)
        throw new IOException("Completed import changed while validating activation");
    var previous=File.Exists(destination+".active")?File.ReadAllText(destination+".active").Trim():Path.GetFileName(destination);
    var generation=destination+".recovered-download-"+DateTimeOffset.UtcNow.ToString("yyyyMMdd-HHmmss")+"-"+Guid.NewGuid().ToString("N")[..8]+".db";
    var move=args[0]=="activate-move";
    if(move)
    {
        var workspace=Path.GetFullPath(Path.Combine(Environment.CurrentDirectory,"artifacts"))+Path.DirectorySeparatorChar;
        var applicationData=Path.GetFullPath(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData),"XFEstudio","XFEEDToolBox"))+Path.DirectorySeparatorChar;
        if(!source.StartsWith(workspace,StringComparison.OrdinalIgnoreCase)||!generation.StartsWith(applicationData,StringComparison.OrdinalIgnoreCase)
            ||!Path.GetPathRoot(source)!.Equals(Path.GetPathRoot(generation),StringComparison.OrdinalIgnoreCase))
            throw new InvalidOperationException("Move requires a validated workspace artifact and application-data generation on the same volume");
        Console.WriteLine(JsonSerializer.Serialize(new{operation="move-verified-generation",source,generation,sourceBytes=new FileInfo(source).Length}));
        File.Move(source,generation,false);
    }
    else File.Copy(source,generation,false);
    object[] health;using(var check=Open(generation,SqliteOpenMode.ReadOnly))health=Summary(check);
    if(!health.SequenceEqual(expected))throw new InvalidDataException("Published generation differs from the completed import source");
    Console.WriteLine("Hashing the reopened generation before publishing the active marker");
    var generationSha256=ExclusiveSha256(generation);
    if(generationSha256!=sourceSha256||new FileInfo(generation).Length!=sourceBytes||File.GetLastWriteTimeUtc(generation)!=sourceModified)
        throw new InvalidDataException("Generation bytes changed during activation");
    new MarketRepository(generation).RecordVerifiedGeneration(new(Convert.ToInt64(health[0]),Convert.ToInt64(health[1]),Convert.ToInt64(health[2]),(string)health[4]),Convert.ToInt64(health[3]),generationSha256);
    var marker=destination+".active";var temporary=marker+"."+Guid.NewGuid().ToString("N")+".tmp";
    using(var markerStream=new FileStream(temporary,FileMode.CreateNew,FileAccess.Write,FileShare.Read,4096,FileOptions.WriteThrough))
    {markerStream.Write(System.Text.Encoding.UTF8.GetBytes(Path.GetFileName(generation)));markerStream.Flush(true);}
    File.Move(temporary,marker,true);
    Report(new{status="activated",at=DateTimeOffset.UtcNow,source,sourceMoved=move,sourceDump,completedImportReport=Path.GetFullPath(args[4]),original=destination,previous,generation,sourceBytes,sourceModified,sourceSha256,generationSha256,reopenedSummary=health,integrityVerifiedByCompletedImport=true,startupProof=generation+".verified-generation.json",elapsedSeconds=timer.Elapsed.TotalSeconds});
    Console.WriteLine(generation);return;
}
if(File.Exists(destination)||File.Exists(destination+"-wal")||File.Exists(destination+"-shm"))throw new IOException("Import requires a new isolated destination");
Directory.CreateDirectory(Path.GetDirectoryName(destination)!);
var repository=new MarketRepository(destination);repository.Initialize();
if(args[0]=="reference")
{
    using var http=new HttpClient();var result=await new SpanshImporter(repository,http).ImportFileAsync(source);
    SqliteConnection.ClearAllPools();using var check=Open(destination,SqliteOpenMode.ReadOnly);var health=Health(check);
    Report(new{status="reference-verified",source,destination,result,health,elapsedSeconds=timer.Elapsed.TotalSeconds});return;
}
long systems=0,stations=0,snapshots=0,quotes=0,invalidStations=0,futureSnapshots=0,olderSnapshots=0;
DateTimeOffset? newest=null;var now=DateTimeOffset.UtcNow;var lastReport=TimeSpan.Zero;
object? verifiedReport=null;
await using var file=File.OpenRead(source);
await using Stream stream=source.EndsWith(".gz",StringComparison.OrdinalIgnoreCase)?new GZipStream(file,CompressionMode.Decompress,true):file;
using(var connection=Open(destination,SqliteOpenMode.ReadWrite))
{
    using(var settings=connection.CreateCommand())
    {settings.CommandText="PRAGMA synchronous=NORMAL; PRAGMA cache_size=-131072; DROP INDEX idx_quotes_commodity; DROP INDEX idx_quotes_time;";settings.ExecuteNonQuery();}
    SqliteTransaction transaction=connection.BeginTransaction();
    var commands=new List<SqliteCommand>();
    SqliteCommand Prepare(string sql,params string[] names)
    {var command=connection.CreateCommand();command.Transaction=transaction;command.CommandText=sql;foreach(var name in names)command.Parameters.AddWithValue(name,DBNull.Value);command.Prepare();commands.Add(command);return command;}
    var selectStation=Prepare("SELECT json FROM stations WHERE id=$id","$id");
    var saveStation=Prepare("INSERT OR REPLACE INTO stations VALUES($id,$name,$system,$x,$y,$z,$json)","$id","$name","$system","$x","$y","$z","$json");
    var selectSnapshot=Prepare("SELECT timestamp FROM snapshots WHERE station=$id","$id");
    var deleteQuotes=Prepare("DELETE FROM quotes WHERE station=$id","$id");
    var saveQuote=Prepare("INSERT INTO quotes VALUES($id,$key,$name,$buy,$sell,$supply,$demand,$time,$source,$json)","$id","$key","$name","$buy","$sell","$supply","$demand","$time","$source","$json");
    var saveSnapshot=Prepare("INSERT OR REPLACE INTO snapshots VALUES($id,$time,$source)","$id","$time","$source");
    void Put(SqliteCommand command,params object?[] values){for(var index=0;index<values.Length;index++)command.Parameters[index].Value=values[index]??DBNull.Value;}
    try
    {
        await foreach(var system in JsonSerializer.DeserializeAsyncEnumerable<JsonElement>(stream))
        {
            var name=system.Text("name");var position=system.Position("coords");var security=JsonFields.Security(system.Text("security"));
            foreach(var item in system.Get("stations").Items().Concat(system.Get("bodies").Items().SelectMany(body=>body.Get("stations").Items())).DistinctBy(item=>item.Long("id")))
            {
                var market=item.Get("market");var time=market.Time("updateTime")??item.Time("updateTime")??system.Time("date");
                var type=item.Text("type");var pads=item.Get("landingPads");
                var incoming=new Station{MarketId=item.Long("id"),Name=item.Text("name"),SystemName=name,Position=position,Security=security,
                    DistanceToArrivalLs=item.Number("distanceToArrival"),Type=type,
                    MaxLandingPad=pads.Long("large")>0?LandingPad.Large:pads.Long("medium")>0?LandingPad.Medium:pads.Long("small")>0?LandingPad.Small:LandingPad.Unknown,
                    IsPlanetary=type.Contains("Planetary",StringComparison.OrdinalIgnoreCase)||type.Contains("Settlement",StringComparison.OrdinalIgnoreCase),IsFleetCarrier=type.Contains("Carrier",StringComparison.OrdinalIgnoreCase),
                    RequiresPermit=system.Flag("permitRequired"),IsRestricted=item.Text("dockingAccess") is "friends" or "squadron" or "none",UpdatedAt=time??DateTimeOffset.MinValue};
                if(incoming.MarketId<=0||incoming.Name.Length==0||name.Length==0){invalidStations++;continue;}
                Put(selectStation,incoming.MarketId);var old=selectStation.ExecuteScalar() is string text?JsonSerializer.Deserialize<Station>(text,json):null;
                var station=incoming;
                if(old!=null)
                {
                    var newer=incoming.UpdatedAt>=old.UpdatedAt;var moved=!incoming.SystemName.Equals(old.SystemName,StringComparison.OrdinalIgnoreCase);
                    if(!newer&&moved)station=old;
                    else station=(newer?incoming:old) with{Name=incoming.Name,SystemName=newer?incoming.SystemName:old.SystemName,Position=newer&&moved?incoming.Position:incoming.Position??old.Position,
                        DistanceToArrivalLs=incoming.DistanceToArrivalLs??old.DistanceToArrivalLs,Security=incoming.Security!=SecurityLevel.Unknown?incoming.Security:old.Security,
                        MaxLandingPad=incoming.MaxLandingPad!=LandingPad.Unknown?incoming.MaxLandingPad:old.MaxLandingPad,Type=incoming.Type.Length>0?incoming.Type:old.Type,
                        IsFleetCarrier=incoming.IsFleetCarrier||old.IsFleetCarrier,IsPlanetary=incoming.IsPlanetary||old.IsPlanetary,RequiresPermit=incoming.RequiresPermit||old.RequiresPermit,IsRestricted=incoming.IsRestricted||old.IsRestricted};
                }
                Put(saveStation,station.MarketId,station.Name,station.SystemName,station.Position?.X,station.Position?.Y,station.Position?.Z,JsonSerializer.Serialize(station,json));saveStation.ExecuteNonQuery();stations++;
                if(time==null||market.Get("commodities").ValueKind!=JsonValueKind.Array)continue;
                if(time>now.AddMinutes(5)){futureSnapshots++;continue;}
                Put(selectSnapshot,incoming.MarketId);if(selectSnapshot.ExecuteScalar() is long previous&&previous>time.Value.ToUnixTimeMilliseconds()){olderSnapshots++;continue;}
                Put(deleteQuotes,incoming.MarketId);deleteQuotes.ExecuteNonQuery();
                var prohibited=item.Get("prohibitedCommodities").Items().Concat(market.Get("prohibitedCommodities").Items()).Where(value=>value.ValueKind==JsonValueKind.String).Select(value=>CommodityCatalog.Key(value.GetString()!)).ToHashSet();
                foreach(var quote in market.Get("commodities").Items().Select(value=>MarketParser.ParseQuote(value,incoming.MarketId,time.Value,DataSource.Spansh) with{Category=value.Text("category")}).Select(value=>value with{Illegal=value.Illegal||prohibited.Contains(value.Commodity)}).DistinctBy(value=>value.Commodity))
                {
                    if(string.IsNullOrWhiteSpace(quote.Commodity))continue;
                    Put(saveQuote,quote.MarketId,quote.Commodity,quote.DisplayName,quote.BuyPrice,quote.SellPrice,quote.Supply,quote.Demand,time.Value.ToUnixTimeMilliseconds(),(int)DataSource.Spansh,JsonSerializer.Serialize(quote,json));saveQuote.ExecuteNonQuery();quotes++;
                }
                Put(saveSnapshot,incoming.MarketId,time.Value.ToUnixTimeMilliseconds(),(int)DataSource.Spansh);saveSnapshot.ExecuteNonQuery();snapshots++;
                if(newest==null||time>newest)newest=time;
            }
            systems++;
            if(systems%100==0){transaction.Commit();transaction.Dispose();transaction=connection.BeginTransaction();foreach(var command in commands)command.Transaction=transaction;}
            if(timer.Elapsed-lastReport>TimeSpan.FromSeconds(5))
            {lastReport=timer.Elapsed;Report(new{status="importing",pid=Environment.ProcessId,source,destination,systems,stations,snapshots,quotes,newest,compressedBytesRead=file.Position,compressedBytes=file.Length,elapsedSeconds=timer.Elapsed.TotalSeconds});Console.WriteLine($"systems={systems} stations={stations} quotes={quotes} compressed={file.Position}/{file.Length}");}
        }
        transaction.Commit();transaction.Dispose();foreach(var command in commands){command.Transaction=null;command.Dispose();}
        Report(new{status="indexing",pid=Environment.ProcessId,source,destination,systems,stations,snapshots,quotes,compressedBytesRead=file.Position,compressedBytes=file.Length,elapsedSeconds=timer.Elapsed.TotalSeconds});
        using var indexes=connection.CreateCommand();indexes.CommandText="CREATE INDEX idx_quotes_commodity ON quotes(commodity); CREATE INDEX idx_quotes_time ON quotes(timestamp DESC); ANALYZE; PRAGMA wal_checkpoint(TRUNCATE);";indexes.ExecuteNonQuery();
        Report(new{status="validating",pid=Environment.ProcessId,source,destination,systems,stations,snapshots,quotes,elapsedSeconds=timer.Elapsed.TotalSeconds});
        var health=Health(connection);
        verifiedReport=new{status="verified",at=DateTimeOffset.UtcNow,source,destination,systems,stationRecords=stations,snapshotRecords=snapshots,quoteRecords=quotes,invalidStations,futureSnapshots,olderSnapshots,newest,health,compressedBytes=file.Length,elapsedSeconds=timer.Elapsed.TotalSeconds};
    }
    catch(Exception error)
    {transaction.Dispose();Report(new{status="failed",source,destination,systems,stations,snapshots,quotes,error=error.ToString(),elapsedSeconds=timer.Elapsed.TotalSeconds});throw;}
}
SqliteConnection.ClearAllPools();
Report(new{status="validating-reopen",pid=Environment.ProcessId,source,destination,systems,stations,snapshots,quotes,elapsedSeconds=timer.Elapsed.TotalSeconds});
try{using var reopened=Open(destination,SqliteOpenMode.ReadOnly);Health(reopened);Report(verifiedReport!);}
catch(Exception error){Report(new{status="failed-reopen",source,destination,error=error.ToString()});throw;}
Console.WriteLine("Complete and verified: "+destination);