using Microsoft.Data.Sqlite;
using XFEExtension.NetCore.XUnit.Attributes;
using XFEEDToolBox.Core.Data;
using XFEEDToolBox.Core.Models;
using System.Security.Cryptography;
using XFEEDToolBox.Core.Services;

namespace XFEEDToolBox.Test;

[TestFixture]
[Isolated] // These fixtures clear process-wide SQLite pools and alter physical DB files.
public class MarketRecoveryTests
{
    private static void SealGeneration(MarketRepository repository)
    {
        SqliteConnection.ClearAllPools();MarketCacheHealth health;long latest;
        using(var connection=Open(repository.DatabasePath))
        {
            using var query=connection.CreateCommand();query.CommandText="PRAGMA wal_checkpoint(TRUNCATE)";query.ExecuteNonQuery();
            query.CommandText="PRAGMA integrity_check(1)";Check.Equal("ok",(string)query.ExecuteScalar()!);
            query.CommandText="SELECT (SELECT count(*) FROM stations),(SELECT count(*) FROM quotes),(SELECT count(*) FROM snapshots),(SELECT max(timestamp) FROM quotes),sqlite_version()";
            using var rows=query.ExecuteReader();rows.Read();health=new(rows.GetInt64(0),rows.GetInt64(1),rows.GetInt64(2),rows.GetString(4));latest=rows.GetInt64(3);
        }
        using var file=File.OpenRead(repository.DatabasePath);var hash=Convert.ToHexStringLower(SHA256.HashData(file));file.Dispose();
        repository.RecordVerifiedGeneration(health,latest,hash);
    }
    private static SqliteConnection Open(string path)
    {
        var connection = new SqliteConnection(new SqliteConnectionStringBuilder { DataSource = path, Pooling = false }.ToString());
        connection.Open(); return connection;
    }

    private static void DamageQuotePage(string path)
    {
        long root, size;
        SqliteConnection.ClearAllPools();
        using (var connection = Open(path))
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "PRAGMA wal_checkpoint(TRUNCATE)"; command.ExecuteNonQuery();
            command.CommandText = "SELECT rootpage FROM sqlite_schema WHERE name='quotes'"; root = (long)command.ExecuteScalar()!;
            command.CommandText = "PRAGMA page_size"; size = (long)command.ExecuteScalar()!;
        }
        using var file = File.Open(path, FileMode.Open, FileAccess.Write, FileShare.None);
        file.Position = (root - 1) * size;
        file.Write(new byte[size]);
        file.Flush(true);
    }

    [Test] public async Task StartupRestoresRealCorruptedTableAndPreservesDamagedOriginal()
    {
        using var temp = new TemporaryDirectory(); var repository = temp.Repository();
        var at = DateTimeOffset.UtcNow.AddMinutes(-2);
        for (var i = 1; i <= 4; i++) await repository.SaveSnapshotAsync(new(Check.Station(i), [Check.Quote(i, "water", 12, 10), Check.Quote(i, "food", 20, 18)], at, DataSource.Eddn));
        await repository.SaveVerifiedBackupAsync();
        DamageQuotePage(repository.DatabasePath);
        Check.Throws<SqliteException>(() => repository.Search("", null));
        var restarted = new MarketRepository(repository.DatabasePath); restarted.Initialize();
        Check.That(restarted.LastRecovery is not null);
        Check.Equal(8L, restarted.Statistics().Quotes); Check.Equal(4L, restarted.Statistics().Stations);
        Check.Equal(8, restarted.Search("", null).Count);
        Check.That(File.Exists(restarted.LastRecovery!.OriginalBackup));
        using var damaged = Open(restarted.LastRecovery.OriginalBackup); using var check = damaged.CreateCommand();
        check.CommandText = "PRAGMA quick_check(1)"; Check.That((string?)check.ExecuteScalar() != "ok");
        await restarted.SaveSnapshotAsync(new(Check.Station(1), [Check.Quote(1, "water", 13, 11)], at.AddMinutes(1), DataSource.Journal));
        SqliteConnection.ClearAllPools();
        var twice = new MarketRepository(repository.DatabasePath); twice.Initialize();
        Check.That(twice.LastRecovery is null); Check.Equal(7L, twice.Statistics().Quotes);
        Check.Equal(13L, twice.Search("water", 1).Single().Quote.BuyPrice);
    }

    [Test] public async Task FailedIncomingWriteRestoresThenRetriesTheSameSnapshot()
    {
        using var temp = new TemporaryDirectory(); var repository = temp.Repository(); var at = DateTimeOffset.UtcNow.AddMinutes(-2);
        await repository.SaveSnapshotAsync(new(Check.Station(1), [Check.Quote(1, "water", 10, 9)], at, DataSource.Eddn));
        await repository.SaveVerifiedBackupAsync(); DamageQuotePage(repository.DatabasePath);
        Check.That(await repository.SaveSnapshotAsync(new(Check.Station(1), [Check.Quote(1, "water", 12, 11)], at.AddMinutes(1), DataSource.Journal)));
        Check.Equal(12L, repository.Search("water", 1).Single().Quote.BuyPrice);
        Check.That(repository.LastRecovery is not null);
    }

    [Test] public async Task InvalidBackupCannotReplaceTheCorruptCacheWithAnEmptyDatabase()
    {
        using var temp = new TemporaryDirectory(); var repository = temp.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1), [Check.Quote(1, "water", 10, 9)], DateTimeOffset.UtcNow, DataSource.Journal));
        SqliteConnection.ClearAllPools();
        File.WriteAllBytes(repository.VerifiedBackupPath, new byte[4096]);
        DamageQuotePage(repository.DatabasePath);
        try { new MarketRepository(repository.DatabasePath).Initialize(); throw new Exception("Invalid recovery was accepted"); }
        catch (SqliteException) { }
        Check.Throws<SqliteException>(() => repository.Search("", null));
        Check.Equal(0, Directory.GetFiles(temp.Path, "*.corrupt-*.db").Length);
    }

    [Test] public async Task ConcurrentReadersAndTwoRepositoriesKeepWholeStationSnapshotsAcrossReopen()
    {
        using var temp = new TemporaryDirectory(); var first = temp.Repository(); var second = new MarketRepository(first.DatabasePath);
        var at = DateTimeOffset.UtcNow.AddMinutes(-5);
        var readers = Enumerable.Range(0, 3).Select(_ => Task.Run(() => { for (var n = 0; n < 40; n++) { first.Statistics(); first.Search("", null); first.GetStations(); } })).ToArray();
        var writers = Enumerable.Range(0, 2).Select(writer => Task.Run(async () =>
        {
            var repository = writer == 0 ? first : second;
            for (var n = 0; n < 40; n++) await repository.SaveSnapshotAsync(new(Check.Station(writer + 1), Enumerable.Range(1, 12).Select(i => Check.Quote(writer + 1, "commodity" + i, n + 1, 5)).ToArray(), at.AddSeconds(n), DataSource.Eddn));
        })).ToArray();
        await Task.WhenAll(readers.Concat(writers)); await first.SaveVerifiedBackupAsync(); SqliteConnection.ClearAllPools();
        var restarted = new MarketRepository(first.DatabasePath); restarted.Initialize();
        Check.Equal(24L, restarted.Statistics().Quotes); Check.Equal(2L, restarted.Statistics().Stations);
        foreach (var sort in Enum.GetValues<MarketSort>()) Check.Equal(24, restarted.Search("", null, sort: sort).Count);
        Check.That(restarted.Search("", null).All(x => x.Quote.BuyPrice == 40));
    }

    [Test] public async Task EmptyMarketCannotOverwriteAUsefulVerifiedBackup()
    {
        using var temp=new TemporaryDirectory();var repository=temp.Repository();var at=DateTimeOffset.UtcNow.AddMinutes(-2);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],at,DataSource.Eddn));
        await repository.SaveVerifiedBackupAsync();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[],at.AddMinutes(1),DataSource.Journal));
        try{await repository.SaveVerifiedBackupAsync();throw new Exception("Empty backup was accepted");}catch(InvalidDataException){}
        using var backup=Open(repository.VerifiedBackupPath);using var query=backup.CreateCommand();
        query.CommandText="SELECT count(*) FROM quotes";Check.Equal(1L,(long)query.ExecuteScalar()!);
    }
    [Test]public async Task TruncatedDatabaseRestoresThroughANewGenerationWithoutChangingOriginalBytes()
    {
        using var temporary=new TemporaryDirectory();var original=temporary.File("markets.db");var repository=new MarketRepository(original);repository.Initialize();
        var at=DateTimeOffset.UtcNow.AddMinutes(-2);
        await repository.SaveSnapshotAsync(new(Check.Station(1),Enumerable.Range(0,100).Select(index=>Check.Quote(1,"commodity"+index,10,9)).ToArray(),at,DataSource.Eddn));
        await repository.SaveVerifiedBackupAsync();SqliteConnection.ClearAllPools();
        using(var file=new FileStream(original,FileMode.Open,FileAccess.Write,FileShare.None)){file.SetLength(4096);file.Flush(true);}
        var damaged=File.ReadAllBytes(original);
        var restarted=new MarketRepository(original);restarted.Initialize();
        Check.That(restarted.LastRecovery is{OriginalPreservedInPlace:true});Check.That(restarted.DatabasePath!=original);
        Check.Equal(original,restarted.LastRecovery!.OriginalBackup);Check.That(File.ReadAllBytes(original).SequenceEqual(damaged));
        Check.Equal(100L,restarted.Statistics().Quotes);Check.Equal(100L,repository.Statistics().Quotes);
        Check.That(File.Exists(original+".active"));Check.Equal(0,Directory.EnumerateFiles(temporary.Path,"*.corrupt-*.db").Count());
        SqliteConnection.ClearAllPools();var reopened=new MarketRepository(original);reopened.Initialize();
        Check.That(reopened.LastRecovery is null);Check.Equal(restarted.DatabasePath,reopened.DatabasePath);Check.Equal(100L,reopened.Statistics().Quotes);
        await reopened.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",15,14)],at.AddMinutes(1),DataSource.Journal));
        Check.Equal(1L,repository.Statistics().Quotes);Check.That(File.ReadAllBytes(original).SequenceEqual(damaged));
    }
    [Test]public void ActiveMarkerCannotRedirectTheCacheOutsideItsOwnedGeneration()
    {
        using var temporary=new TemporaryDirectory();var original=temporary.File("markets.db");
        File.WriteAllText(original+".active","../another.db");
        Check.Throws<InvalidDataException>(()=>_ = new MarketRepository(original).DatabasePath);
        File.WriteAllText(original+".active","markets.db.recovered-missing.db");
        Check.Throws<InvalidDataException>(()=>_ = new MarketRepository(original).DatabasePath);
    }
    [Test]public async Task InsufficientBackupSpacePreservesTheExistingVerifiedFile()
    {
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));
        await repository.SaveVerifiedBackupAsync();SqliteConnection.ClearAllPools();var before=File.ReadAllBytes(repository.VerifiedBackupPath);
        var maintenance=typeof(MarketRepository).Assembly.GetType("XFEEDToolBox.Core.Data.MarketCacheMaintenance")!;
        var backup=maintenance.GetMethod("SaveVerifiedBackup",System.Reflection.BindingFlags.Static|System.Reflection.BindingFlags.NonPublic)!;
        var sourceBytes=new FileInfo(repository.DatabasePath).Length+(File.Exists(repository.DatabasePath+"-wal")?new FileInfo(repository.DatabasePath+"-wal").Length:0);
        foreach(var available in new[]{0L,sourceBytes+256L*1024*1024+1})
        {
            try{backup.Invoke(null,[repository.DatabasePath,repository.VerifiedBackupPath,(Func<long>)(()=>available)]);throw new Exception("Insufficient backup space was accepted");}
            catch(System.Reflection.TargetInvocationException error)when(error.InnerException is IOException){}
        }
        Check.That(File.ReadAllBytes(repository.VerifiedBackupPath).SequenceEqual(before));Check.Equal(1L,repository.Statistics().Quotes);
    }
    [Test]public async Task SealedGenerationStartsWithoutScanningOrReplacingTheOlderBackup()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9),Check.Quote(1,"food",15,14)],DateTimeOffset.UtcNow,DataSource.Journal));
        SealGeneration(repository);File.WriteAllBytes(repository.VerifiedBackupPath,new byte[4096]);
        var restarted=new MarketRepository(repository.DatabasePath);restarted.Initialize();
        Check.Equal(2L,restarted.Statistics().Quotes);Check.Equal(4096L,new FileInfo(repository.VerifiedBackupPath).Length);
        Check.That(File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl").Contains("startup-verified-generation"));
    }
    [Test]public async Task SealedGenerationRejectsPageDamageEvenWhenTheOriginalModifiedTimeIsRestored()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));
        await repository.SaveVerifiedBackupAsync();SealGeneration(repository);var modified=File.GetLastWriteTimeUtc(repository.DatabasePath);
        DamageQuotePage(repository.DatabasePath);File.SetLastWriteTimeUtc(repository.DatabasePath,modified);
        var restarted=new MarketRepository(repository.DatabasePath);restarted.Initialize();
        Check.That(restarted.LastRecovery is not null);Check.Equal(1L,restarted.Statistics().Quotes);
    }
    [Test]public async Task AReplacedPhysicalDatabaseCannotReuseAnExistingGenerationReceipt()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));
        SealGeneration(repository);var modified=File.GetLastWriteTimeUtc(repository.DatabasePath);var creation=File.GetCreationTimeUtc(repository.DatabasePath);
        var parked=repository.DatabasePath+".parked";SqliteConnection.ClearAllPools();File.Move(repository.DatabasePath,parked);File.Copy(parked,repository.DatabasePath);
        File.SetCreationTimeUtc(repository.DatabasePath,creation);File.SetLastWriteTimeUtc(repository.DatabasePath,modified);
        File.WriteAllBytes(repository.VerifiedBackupPath,new byte[4096]);
        var restarted=new MarketRepository(repository.DatabasePath);restarted.Initialize();
        Check.Equal(1L,restarted.Statistics().Quotes);Check.That(new FileInfo(repository.VerifiedBackupPath).Length>4096);
    }
    [Test]public async Task CachedStatisticsTrackReplacementEmptySnapshotsAndForeignWriters()
    {
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();var at=DateTimeOffset.UtcNow.AddMinutes(-3);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9),Check.Quote(1,"food",15,14)],at,DataSource.Journal));
        Check.Equal(2L,repository.Statistics().Quotes);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",11,10)],at.AddMinutes(1),DataSource.Journal));
        Check.Equal(1L,repository.Statistics().Quotes);
        var other=new MarketRepository(repository.DatabasePath);await other.SaveSnapshotAsync(new(Check.Station(2),[Check.Quote(2,"food",15,14)],at.AddMinutes(1),DataSource.Journal));
        Check.Equal(2L,repository.Statistics().Quotes);Check.Equal(2L,repository.Statistics().Stations);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[],at.AddMinutes(2),DataSource.Journal));
        Check.Equal(1L,repository.Statistics().Quotes);Check.Equal(at.AddMinutes(1).ToUnixTimeMilliseconds(),repository.Statistics().Latest!.Value.ToUnixTimeMilliseconds());
    }
    [Test]public async Task TrustedWritesAndNormalCloseKeepTheNextStartupFast()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();var at=DateTimeOffset.UtcNow.AddMinutes(-2);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],at,DataSource.Journal));SealGeneration(repository);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",12,11),Check.Quote(1,"food",20,19)],at.AddMinutes(1),DataSource.Journal));
        Check.That(await repository.FlushVerifiedGenerationAsync(),File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl"));SqliteConnection.ClearAllPools();
        File.WriteAllBytes(repository.VerifiedBackupPath,new byte[4096]);
        var restarted=new MarketRepository(repository.DatabasePath);restarted.Initialize();
        Check.Equal(2L,restarted.Statistics().Quotes);Check.Equal(4096L,new FileInfo(repository.VerifiedBackupPath).Length);
        Check.That(File.ReadAllText(repository.DatabasePath+".verified-generation.json").Contains("\"TrustedWriteContinuation\": true"));
    }
    [Test]public async Task UnknownExternalWritesCannotBeSealedAsTrustedContinuation()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));SealGeneration(repository);
        using(var connection=Open(repository.DatabasePath)){using var query=connection.CreateCommand();query.CommandText="UPDATE quotes SET buy=999 WHERE station=1";query.ExecuteNonQuery();}
        Check.That(!await repository.FlushVerifiedGenerationAsync());
        Check.That(!File.ReadAllText(repository.DatabasePath+".verified-generation.json").Contains("\"TrustedWriteContinuation\": true"));
    }
    [Test]public async Task AWriteBetweenCheckpointAndReceiptPublicationCannotGainAContinuation()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));SealGeneration(repository);
        var proofPath=repository.DatabasePath+".verified-generation.json";var before=File.ReadAllBytes(proofPath);
        using var document=System.Text.Json.JsonDocument.Parse(before);var root=document.RootElement;
        var health=System.Text.Json.JsonSerializer.Deserialize<MarketCacheHealth>(root.GetProperty("Health"))!;
        var latest=root.GetProperty("Latest").GetInt64();var hash=root.GetProperty("Sha256").GetString()!;
        var type=typeof(MarketRepository).Assembly.GetType("XFEEDToolBox.Core.Data.MarketVerifiedGeneration")!;
        var capture=type.GetMethod("TryCapture",System.Reflection.BindingFlags.Static|System.Reflection.BindingFlags.NonPublic)!;
        object?[] arguments=[repository.DatabasePath,null];Check.That((bool)capture.Invoke(null,arguments)!);var expected=arguments[1];
        using(var connection=Open(repository.DatabasePath))
        {using var query=connection.CreateCommand();query.CommandText="UPDATE quotes SET buy=999 WHERE station=1; PRAGMA wal_checkpoint(TRUNCATE)";query.ExecuteNonQuery();}
        var record=type.GetMethod("Record",System.Reflection.BindingFlags.Static|System.Reflection.BindingFlags.NonPublic)!;
        try{record.Invoke(null,[repository.DatabasePath,health,latest,hash,true,expected]);throw new Exception("Changed checkpoint received a trusted continuation");}
        catch(System.Reflection.TargetInvocationException error)when(error.InnerException is IOException){}
        Check.That(File.ReadAllBytes(proofPath).SequenceEqual(before));Check.That(!await repository.FlushVerifiedGenerationAsync());
    }
    [Test]public async Task AnOpenForeignWritableHandlePreventsReceiptPublication()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));SealGeneration(repository);
        var proofPath=repository.DatabasePath+".verified-generation.json";var before=File.ReadAllBytes(proofPath);
        using var document=System.Text.Json.JsonDocument.Parse(before);var root=document.RootElement;
        var health=System.Text.Json.JsonSerializer.Deserialize<MarketCacheHealth>(root.GetProperty("Health"))!;
        using(var handle=File.OpenHandle(repository.DatabasePath,FileMode.Open,FileAccess.ReadWrite,FileShare.ReadWrite|FileShare.Delete))
            Check.Throws<IOException>(()=>repository.RecordVerifiedGeneration(health,root.GetProperty("Latest").GetInt64(),root.GetProperty("Sha256").GetString()!));
        Check.That(File.ReadAllBytes(proofPath).SequenceEqual(before));
    }
    [Test]public async Task BoundedStablePagesPreserveGlobalOrderIncludingDuplicateStationNames()
    {
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();var at=DateTimeOffset.UtcNow.AddMinutes(-1);
        var definitions=new[]{(1L,"0 Empty","First",Array.Empty<string>()),(2L,"Alpha","Duplicate",new[]{"zinc","gold"}),(3L,"Alpha","Duplicate",new[]{"apple","titanium"}),(4L,"Alpha","Later",new[]{"silver"}),(5L,"alpha","Later",new[]{"uranium"}),(6L,"Beta","Last",new[]{"food","water"}),(7L,"Delta","Empty",Array.Empty<string>())};
        foreach(var(id,system,name,goods)in definitions)
            await repository.SaveSnapshotAsync(new(Check.Station(id) with{SystemName=system,Name=name},goods.Select((key,index)=>Check.Quote(id,key,20-index,10)).ToArray(),at,DataSource.Journal));
        var expected=new List<string>();using(var connection=Open(repository.DatabasePath))
        {using var query=connection.CreateCommand();query.CommandText="SELECT s.id,q.commodity FROM quotes q JOIN stations s ON s.id=q.station ORDER BY s.system,s.name,q.commodity";using var reader=query.ExecuteReader();while(reader.Read())expected.Add(reader.GetInt64(0)+":"+reader.GetString(1));}
        foreach(var limit in new[]{1,2,3,150})foreach(var offset in Enumerable.Range(0,expected.Count+2))
        {var actual=repository.Search("",null,offset,limit).Select(row=>row.Station.MarketId+":"+row.Quote.Commodity);Check.Equal(string.Join('|',expected.Skip(offset).Take(limit)),string.Join('|',actual));}
        Check.Equal("zinc",repository.Search("",2,sort:MarketSort.BuyDescending).First().Quote.Commodity);
        Check.Equal(1,repository.Search("app",3).Count);Check.Equal(0,repository.Search("app",2).Count);
    }
    [Test]public async Task AHealthyChangedGenerationGetsANewShaReceiptAndItsNextLaunchIsFast()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));SealGeneration(repository);
        var proof=repository.DatabasePath+".verified-generation.json";var old=File.ReadAllBytes(proof);
        using(var changed=Open(repository.DatabasePath)){using var query=changed.CreateCommand();query.CommandText="UPDATE quotes SET buy=99; PRAGMA wal_checkpoint(TRUNCATE)";query.ExecuteNonQuery();}
        var reopened=new MarketRepository(repository.DatabasePath);reopened.Initialize();SqliteConnection.ClearAllPools();
        using(var receipt=System.Text.Json.JsonDocument.Parse(File.ReadAllText(proof)))
        {
            Check.That(!receipt.RootElement.GetProperty("TrustedWriteContinuation").GetBoolean());
            Check.Equal(Convert.ToHexStringLower(SHA256.HashData(File.ReadAllBytes(repository.DatabasePath))),receipt.RootElement.GetProperty("Sha256").GetString()!);
        }
        Check.That(!File.ReadAllBytes(proof).SequenceEqual(old));
        File.WriteAllBytes(repository.VerifiedBackupPath,new byte[4096]);
        var twice=new MarketRepository(repository.DatabasePath);twice.Initialize();
        Check.Equal(1L,twice.Statistics().Quotes);Check.Equal(4096L,new FileInfo(repository.VerifiedBackupPath).Length);
        Check.That(File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl").Contains("verified-generation-rebuilt"));
    }
    [Test]public async Task FullValidationCanSealAfterABlockingHandleClosesWithoutAnotherFullScan()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));SealGeneration(repository);
        using(var changed=Open(repository.DatabasePath)){using var query=changed.CreateCommand();query.CommandText="UPDATE quotes SET buy=98; PRAGMA wal_checkpoint(TRUNCATE)";query.ExecuteNonQuery();}
        var proof=repository.DatabasePath+".verified-generation.json";var old=File.ReadAllBytes(proof);var reopened=new MarketRepository(repository.DatabasePath);
        using(var blocker=File.OpenHandle(repository.DatabasePath,FileMode.Open,FileAccess.ReadWrite,FileShare.ReadWrite|FileShare.Delete))
        {reopened.Initialize();Check.That(File.ReadAllBytes(proof).SequenceEqual(old));Check.That(!await reopened.FlushVerifiedGenerationAsync());}
        Check.That(await reopened.FlushVerifiedGenerationAsync(),File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl"));
        using var receipt=System.Text.Json.JsonDocument.Parse(File.ReadAllText(proof));
        Check.That(!receipt.RootElement.GetProperty("TrustedWriteContinuation").GetBoolean());
        Check.Equal(Convert.ToHexStringLower(SHA256.HashData(File.ReadAllBytes(repository.DatabasePath))),receipt.RootElement.GetProperty("Sha256").GetString()!);
    }
    [Test]public async Task AForeignWriteAfterFullValidationCannotAcquireThePendingReceipt()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));SealGeneration(repository);
        using(var changed=Open(repository.DatabasePath)){using var query=changed.CreateCommand();query.CommandText="UPDATE quotes SET buy=97; PRAGMA wal_checkpoint(TRUNCATE)";query.ExecuteNonQuery();}
        var proof=repository.DatabasePath+".verified-generation.json";var old=File.ReadAllBytes(proof);var reopened=new MarketRepository(repository.DatabasePath);
        using(var blocker=File.OpenHandle(repository.DatabasePath,FileMode.Open,FileAccess.ReadWrite,FileShare.ReadWrite|FileShare.Delete))
        {
            reopened.Initialize();
            using var foreign=Open(repository.DatabasePath);using var query=foreign.CreateCommand();query.CommandText="UPDATE quotes SET buy=96; PRAGMA wal_checkpoint(TRUNCATE)";query.ExecuteNonQuery();
        }
        Check.That(!await reopened.FlushVerifiedGenerationAsync());Check.That(File.ReadAllBytes(proof).SequenceEqual(old));
    }
    [Test]public async Task RuntimeCloseRetriesTheActualTrustedWriteChainAfterAReaderReleasesIt()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var runtime=new ToolboxRuntime(temporary.Path);await runtime.InitializeAsync(offline:true,validateMarketCache:true);
        var at=DateTimeOffset.UtcNow.AddMinutes(-2);await runtime.Markets.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],at,DataSource.Journal));SealGeneration(runtime.Markets);
        var blocker=File.OpenHandle(runtime.Markets.DatabasePath,FileMode.Open,FileAccess.ReadWrite,FileShare.ReadWrite|FileShare.Delete);
        try
        {
            await runtime.Markets.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",12,11),Check.Quote(1,"food",20,19)],at.AddMinutes(1),DataSource.Journal));
            var release=Task.Run(async()=>{await Task.Delay(120);blocker.Dispose();});
            await runtime.DisposeAsync();await release;
        }
        finally{blocker.Dispose();}
        using var receipt=System.Text.Json.JsonDocument.Parse(File.ReadAllText(runtime.Markets.DatabasePath+".verified-generation.json"));
        Check.That(receipt.RootElement.GetProperty("TrustedWriteContinuation").GetBoolean());Check.Equal(2L,receipt.RootElement.GetProperty("Health").GetProperty("Quotes").GetInt64());
        File.WriteAllBytes(runtime.Markets.VerifiedBackupPath,new byte[4096]);var reopened=new MarketRepository(runtime.Markets.DatabasePath);reopened.Initialize();
        Check.Equal(2L,reopened.Statistics().Quotes);Check.Equal(4096L,new FileInfo(runtime.Markets.VerifiedBackupPath).Length);
    }
    [Test]public async Task RuntimeExitDoesNotStartTheDeferredFullFileHash()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));SealGeneration(repository);
        using(var changed=Open(repository.DatabasePath)){using var query=changed.CreateCommand();query.CommandText="UPDATE quotes SET buy=95; PRAGMA wal_checkpoint(TRUNCATE)";query.ExecuteNonQuery();}
        var proof=repository.DatabasePath+".verified-generation.json";var old=File.ReadAllBytes(proof);var runtime=new ToolboxRuntime(temporary.Path);
        using(var blocker=File.OpenHandle(repository.DatabasePath,FileMode.Open,FileAccess.ReadWrite,FileShare.ReadWrite|FileShare.Delete))
            await runtime.InitializeAsync(offline:true,validateMarketCache:true);
        // The blocker has gone. A normal explicit flush can now hash and seal,
        // but closing the application must only retry an existing trusted chain.
        await runtime.DisposeAsync();Check.That(File.ReadAllBytes(proof).SequenceEqual(old));
        Check.That(File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl").Contains("verified-generation-close-unsealed"));
        Check.That(await runtime.Markets.FlushVerifiedGenerationAsync(),"The deferred full validation should remain available for an explicit flush.");
        Check.That(!File.ReadAllBytes(proof).SequenceEqual(old));
    }
    [Test]public async Task LargerIntegrityPagerCacheIsLocalReadOnlyAndStillRejectsDamagedPages()
    {
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),Enumerable.Range(0,100).Select(index=>Check.Quote(1,"commodity"+index,10,9)).ToArray(),DateTimeOffset.UtcNow,DataSource.Journal));
        SqliteConnection.ClearAllPools();using(var checkpoint=Open(repository.DatabasePath)){using var query=checkpoint.CreateCommand();query.CommandText="PRAGMA wal_checkpoint(TRUNCATE)";query.ExecuteNonQuery();}
        var maintenance=typeof(MarketRepository).Assembly.GetType("XFEEDToolBox.Core.Data.MarketCacheMaintenance")!;
        var fullCheck=maintenance.GetMethods(System.Reflection.BindingFlags.Static|System.Reflection.BindingFlags.NonPublic)
            .Single(method=>method.Name=="Check"&&method.GetParameters()[0].ParameterType==typeof(SqliteConnection));
        foreach(var corrupt in new[]{false,true})
        {
            if(corrupt)DamageQuotePage(repository.DatabasePath);
            var before=Convert.ToHexStringLower(SHA256.HashData(File.ReadAllBytes(repository.DatabasePath)));
            using(var readOnly=new SqliteConnection(new SqliteConnectionStringBuilder{DataSource=repository.DatabasePath,Mode=SqliteOpenMode.ReadOnly,Pooling=false}.ToString()))
            {
                readOnly.Open();using var cache=readOnly.CreateCommand();cache.CommandText="PRAGMA cache_size=-8192";cache.ExecuteNonQuery();
                var rejected=false;
                try{var health=(MarketCacheHealth)fullCheck.Invoke(null,[readOnly,true,null])!;Check.Equal(100L,health.Quotes);}
                catch(System.Reflection.TargetInvocationException error)when(error.InnerException is SqliteException or InvalidDataException){rejected=true;}
                Check.Equal(corrupt,rejected);cache.CommandText="PRAGMA cache_size";Check.Equal(-8192L,(long)cache.ExecuteScalar()!);
            }
            Check.Equal(before,Convert.ToHexStringLower(SHA256.HashData(File.ReadAllBytes(repository.DatabasePath))));
        }
        Check.That(File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl").Contains("\"appliedCacheKiB\":-131072"));
    }
    [Test]public async Task RuntimeCloseDuringBlockedMarketInitializationDoesNotWaitOrStartSourcesLater()
    {
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();SqliteConnection.ClearAllPools();
        var settings=new ToolboxSettings{JournalDirectory=temporary.Path,ConnectEddnOnStart=false};settings.Save(temporary.File("settings.json"));
        var runtime=new ToolboxRuntime(temporary.Path);using var blocker=Open(repository.DatabasePath);using var command=blocker.CreateCommand();
        command.CommandText="PRAGMA journal_mode=DELETE; BEGIN EXCLUSIVE";command.ExecuteNonQuery();
        var initialization=runtime.InitializeAsync(validateMarketCache:true);
        try
        {
            var gate=(SemaphoreSlim)typeof(MarketRepository).GetField("gate",System.Reflection.BindingFlags.Instance|System.Reflection.BindingFlags.NonPublic)!.GetValue(runtime.Markets)!;
            var until=DateTimeOffset.UtcNow.AddSeconds(2);while(gate.CurrentCount!=0&&DateTimeOffset.UtcNow<until)await Task.Delay(10);
            Check.Equal(0,gate.CurrentCount);Check.That(!initialization.IsCompleted,"The actual SQLite lock must hold market initialization in flight.");
            await runtime.DisposeAsync().AsTask().WaitAsync(TimeSpan.FromSeconds(2));
            Check.That(!initialization.IsCompleted,"Closing must not wait for the blocked market initializer.");
        }
        finally{command.CommandText="ROLLBACK";command.ExecuteNonQuery();}
        await initialization.WaitAsync(TimeSpan.FromSeconds(3));
        Check.That(!File.Exists(temporary.File("game-events.db")),"The late initializer must not create or start the event/writer source chain after disposal.");
        Check.That(runtime.Eddn is null);Check.Equal(0L,runtime.Journal.EventsRead);
        Check.That(!runtime.Activity.Any(entry=>entry.Kind=="系统"&&entry.Message.Contains("Journal 监听已启动")));
        Check.That(File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl").Contains("Market initialization has not completed"));
    }
    [Test]public async Task ActualSearchIdlePoolDoesNotBlockTheNextTrustedStationWriteSeal()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();var at=DateTimeOffset.UtcNow.AddMinutes(-1);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],at,DataSource.Journal));SealGeneration(repository);
        Check.Equal(1,repository.Search("",1).Count);
        await repository.SaveStationAsync(Check.Station(1) with{Name="Updated station",UpdatedAt=at.AddSeconds(1)});
        Check.That(await repository.FlushVerifiedGenerationAsync(),File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl"));
        using(var receipt=System.Text.Json.JsonDocument.Parse(File.ReadAllText(repository.DatabasePath+".verified-generation.json")))
            Check.That(receipt.RootElement.GetProperty("TrustedWriteContinuation").GetBoolean());
        File.WriteAllBytes(repository.VerifiedBackupPath,new byte[4096]);var restarted=new MarketRepository(repository.DatabasePath);restarted.Initialize();
        Check.Equal(1L,restarted.Statistics().Quotes);Check.Equal(4096L,new FileInfo(repository.VerifiedBackupPath).Length);
    }
    [Test]public async Task ActiveSearchPoolReaderStaysUsableAndDefersSealingUntilReleased()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();var at=DateTimeOffset.UtcNow.AddMinutes(-1);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9),Check.Quote(1,"food",20,19)],at,DataSource.Journal));SealGeneration(repository);
        Check.Equal(2,repository.Search("",1).Count);var proof=repository.DatabasePath+".verified-generation.json";var before=File.ReadAllBytes(proof);
        var readOnlyString=new SqliteConnectionStringBuilder{DataSource=repository.DatabasePath,Mode=SqliteOpenMode.ReadOnly,Pooling=false,DefaultTimeout=20}.ToString();
        using(var active=new SqliteConnection(readOnlyString))
        {
            active.Open();
            // Hold a real pre-write snapshot, rather than merely opening a
            // connection that has not yet acquired a WAL read mark.
            using var query=active.CreateCommand();query.CommandText="SELECT commodity FROM quotes ORDER BY commodity";using var reader=query.ExecuteReader();Check.That(reader.Read());
            await repository.SaveStationAsync(Check.Station(1) with{Name="Active reader fixture",UpdatedAt=at.AddSeconds(1)});
            Check.That(!await repository.FlushVerifiedGenerationAsync());Check.That(File.ReadAllBytes(proof).SequenceEqual(before));
            Check.That(reader.Read(),"The active reader must remain usable after its pool's idle connections are cleared.");
        }
        Check.That(await repository.FlushVerifiedGenerationAsync(),File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl"));
    }
    [Test]public async Task ClearingAnIdleSearchPoolNeverTrustsAnInterveningForeignSqlWrite()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],DateTimeOffset.UtcNow,DataSource.Journal));SealGeneration(repository);
        Check.Equal(1,repository.Search("",1).Count);var proof=repository.DatabasePath+".verified-generation.json";var before=File.ReadAllBytes(proof);
        using(var foreign=Open(repository.DatabasePath)){using var write=foreign.CreateCommand();write.CommandText="UPDATE quotes SET buy=777";write.ExecuteNonQuery();}
        Check.That(!await repository.FlushVerifiedGenerationAsync());Check.That(File.ReadAllBytes(proof).SequenceEqual(before));
    }
    [Test]public async Task PublicStatisticsReadersCannotEraseTheConcurrentTrustedWriteChain()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();var at=DateTimeOffset.UtcNow.AddMinutes(-5);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],at,DataSource.Journal));SealGeneration(repository);
        using var done=new CancellationTokenSource();var observed=0;
        var readers=Enumerable.Range(0,3).Select(_=>Task.Run(()=>
        {
            while(!done.IsCancellationRequested)
            {var statistics=repository.Statistics();Check.That(statistics.Stations>=1&&statistics.Quotes>=1);Interlocked.Increment(ref observed);}
        })).ToArray();
        try
        {
            for(var index=0;index<80;index++)
            {
                await repository.SaveStationAsync(Check.Station(1) with{Name="Concurrent "+index,UpdatedAt=at.AddSeconds(index+1)});
                await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",index+11,9)],at.AddSeconds(index+1),DataSource.Journal));
            }
        }
        finally{done.Cancel();await Task.WhenAll(readers);}
        Check.That(observed>0);Check.That(await repository.FlushVerifiedGenerationAsync(),File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl"));
        File.WriteAllBytes(repository.VerifiedBackupPath,new byte[4096]);var reopened=new MarketRepository(repository.DatabasePath);reopened.Initialize();
        Check.Equal(1L,reopened.Statistics().Quotes);Check.Equal(4096L,new FileInfo(repository.VerifiedBackupPath).Length);
    }
    [Test]public async Task AnExtendedUnicodeProfilePathKeepsTheSamePhysicalTrustedGeneration()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var folder=Path.Combine(temporary.Path,"用户 profile with spaces",string.Join('-',Enumerable.Repeat("long-profile-segment",5)));
        Directory.CreateDirectory(folder);var path=Path.Combine(folder,"markets.db");var extended="\\\\?\\"+Path.GetFullPath(path);
        var seed=new MarketRepository(path);seed.Initialize();var at=DateTimeOffset.UtcNow.AddMinutes(-1);
        await seed.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],at,DataSource.Journal));SealGeneration(seed);
        var repository=new MarketRepository(extended);repository.Initialize();
        Check.Equal(1,repository.Search("",1).Count);repository.Statistics();
        await repository.SaveStationAsync(Check.Station(1) with{Name="Unicode profile station",UpdatedAt=at.AddSeconds(1)});
        Check.That(await repository.FlushVerifiedGenerationAsync(),File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl"));
        Check.Equal(1L,new MarketRepository(path).Statistics().Quotes);
    }
    [Test]public async Task AReturnedWritableQueryHandleCannotLeaveAStampCapturedBeforeItsClose()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();var at=DateTimeOffset.UtcNow.AddMinutes(-2);
        await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",10,9)],at,DataSource.Eddn));SealGeneration(repository);
        Check.Equal(1,repository.Search("",1).Count);
        var pooled=new SqliteConnectionStringBuilder{DataSource=repository.DatabasePath,Mode=SqliteOpenMode.ReadWriteCreate,DefaultTimeout=20}.ToString();
        using(var readerConnection=new SqliteConnection(pooled))
        {
            readerConnection.Open();using(var query=readerConnection.CreateCommand()){query.CommandText="SELECT name FROM stations";query.ExecuteScalar();}
            await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",11,9)],at.AddSeconds(1),DataSource.Eddn));
        }
        // Closing a borrowed pool connection physically closes it after the
        // writer's ClearPool, publishing the DB's delayed NTFS write time.
        Check.Equal(1L,repository.Statistics().Quotes);
        Check.That(await repository.FlushVerifiedGenerationAsync(),File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl"));
    }
    [Test]public async Task EddnWritesWithRealPlanningQueriesKeepTheAnchorAfterAllQueryHandlesClose()
    {
        if(!OperatingSystem.IsWindows())return;
        using var temporary=new TemporaryDirectory();var repository=temporary.Repository();var at=DateTimeOffset.UtcNow.AddMinutes(-2);
        for(var id=1;id<=20;id++)await repository.SaveSnapshotAsync(new(Check.Station(id),Enumerable.Range(0,1000).Select(n=>Check.Quote(id,"commodity"+n,10,9)).ToArray(),at,DataSource.Eddn));
        SealGeneration(repository);using var done=new CancellationTokenSource();var observed=0;
        var readers=Enumerable.Range(0,3).Select(index=>Task.Run(()=>
        {
            while(!done.IsCancellationRequested)
            {
                if(index==0){repository.GetStation(1);repository.GetStations();}
                else if(index==1)repository.LoadRegion(1,1000);
                else repository.Search("",1);
                Interlocked.Increment(ref observed);
            }
        })).ToArray();
        try
        {
            for(var n=0;n<80;n++)
            {
                await repository.SaveSnapshotAsync(new(Check.Station(1),[Check.Quote(1,"water",n+11,9)],at.AddSeconds(n+1),DataSource.Eddn));
                repository.Statistics();
            }
        }
        finally{done.Cancel();await Task.WhenAll(readers);}
        Check.That(observed>0);repository.Statistics();
        Check.That(await repository.FlushVerifiedGenerationAsync(),File.ReadAllText(repository.DatabasePath+".diagnostics.jsonl"));
    }
}