XFEEDToolBox

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

namespace XFEEDToolBox.Core.Data;

public enum MarketSort { Stable, Newest, BuyDescending, SellDescending, SupplyDescending, DemandDescending }

/// <summary>One serialized writer, short lived connections, atomic whole-station market snapshots.</summary>
public sealed class MarketRepository
{
    private readonly string originalPath;
    public string DatabasePath => MarketCacheMaintenance.ActivePath(originalPath);
    public string VerifiedBackupPath => originalPath + ".verified.db";
    private string connectionString=>new SqliteConnectionStringBuilder { DataSource = DatabasePath, Mode = SqliteOpenMode.ReadWriteCreate, DefaultTimeout = 20 }.ToString();
    private string readOnlyConnectionString=>new SqliteConnectionStringBuilder(connectionString){Mode=SqliteOpenMode.ReadOnly,Pooling=false}.ToString();
    public MarketCacheRecovery? LastRecovery { get; private set; }
    private readonly SemaphoreSlim gate = new(1, 1);
    private DateTimeOffset backupDue = DateTimeOffset.MinValue;
    private static readonly JsonSerializerOptions Json = ToolboxSettings.JsonOptions;
    private sealed record StatisticsCache(string Path,MarketCacheHealth Health,long? Latest,MarketGenerationStamp Stamp,string? IntegrityAnchor=null);
    private StatisticsCache? statisticsCache;
    private StatisticsCache? pendingFullValidation;
    private bool automaticRecovery=true;
    public Func<Station,bool>? PublicDataFilter{get;set;}

    private sealed class ReadLease(SemaphoreSlim semaphore):IDisposable
    {private SemaphoreSlim? current=semaphore;public void Dispose()=>Interlocked.Exchange(ref current,null)?.Release();}
    private IDisposable EnterRead(CancellationToken token=default){gate.Wait(token);return new ReadLease(gate);}

    public MarketCacheUsage GetCacheUsage()
    {using var read=EnterRead();return MarketCacheCleanup.Usage(originalPath);}

    public async Task<MarketCacheClearResult> ClearAsync(CancellationToken token=default)
    {
        await gate.WaitAsync(token);
        try
        {
            ClearIdleConnections();
            var result=MarketCacheCleanup.Clear(originalPath,()=>
            {
                Volatile.Write(ref statisticsCache,null);pendingFullValidation=null;LastRecovery=null;
                InitializeCheapCore();
            },ClearIdleConnections,token);
            if(!result.Succeeded){Volatile.Write(ref statisticsCache,null);pendingFullValidation=null;}
            return result;
        }
        finally{gate.Release();}
    }

    public void InitializeCheap()
    {gate.Wait();try{InitializeCheapCore();}finally{gate.Release();}}

    private void InitializeCheapCore()
    {
        automaticRecovery=false;backupDue=DateTimeOffset.MaxValue;pendingFullValidation=null;Volatile.Write(ref statisticsCache,null);
        try
        {
            var path=DatabasePath;var empty=!File.Exists(path)||new FileInfo(path).Length==0;
            if(empty)InitializeSchema();
            else
            {
                // Inspect only schema metadata. Rebuilding a missing index on a
                // legacy full dump would itself scan the whole database.
                using var connection=OpenReadOnly();using var schema=connection.CreateCommand();
                schema.CommandText="SELECT id,name,system,x,y,z,json FROM stations LIMIT 0; SELECT station,commodity,name,buy,sell,supply,demand,timestamp,source,json FROM quotes LIMIT 0; SELECT station,timestamp,source FROM snapshots LIMIT 0";
                schema.ExecuteNonQuery();
            }
            if(MarketVerifiedGeneration.TryRead(path,out var receipt))
                Volatile.Write(ref statisticsCache,new(path,receipt.Health,receipt.Latest,receipt.Stamp));
            else if(empty)CacheStatistics(path,new(0,0,0,""),null);
        }
        catch(Exception error)when(error is InvalidDataException||error is SqliteException sql&&sql.SqliteErrorCode is 1 or 11 or 14 or 26)
        {throw new InvalidDataException("行情缓存不可用，请在设置中清理行情缓存；本地 Journal 和个人设置已保留。",error);}
    }

    public void RecordVerifiedGeneration(MarketCacheHealth health,long? latestTimestamp,string sha256)
    {
        gate.Wait();try
        {
        var path=DatabasePath;var receipt=MarketVerifiedGeneration.Record(path,health,latestTimestamp,sha256);
        Volatile.Write(ref statisticsCache,new(path,health,latestTimestamp,receipt.Stamp,receipt.Sha256));
        }
        finally{gate.Release();}
    }

    private void InvalidateStatistics(string reason,object? detail=null)
    {
        var previous=Interlocked.Exchange(ref statisticsCache,null);
        if(previous?.IntegrityAnchor is null)return;
        var path=DatabasePath;var captured=MarketVerifiedGeneration.TryCapture(path,out var actual);
        MarketCacheMaintenance.Log(previous.Path,"verified-generation-anchor-lost",new{reason,detail,previousPath=previous.Path,activePath=path,
            expected=previous.Stamp,actual=captured?actual:null});
    }

    private StatisticsCache? CurrentStatisticsCache()
    {
        var value=Volatile.Read(ref statisticsCache);var path=DatabasePath;
        if(value is not null&&value.Path==path&&MarketVerifiedGeneration.Matches(path,value.Stamp))return value;
        if(MarketVerifiedGeneration.TryRead(path,out var proof))
        {value=new(path,proof.Health,proof.Latest,proof.Stamp,proof.Sha256);Volatile.Write(ref statisticsCache,value);return value;}
        InvalidateStatistics("metadata-or-file-identity-changed");
        return null;
    }

    private void CacheStatistics(string path,MarketCacheHealth health,long? latest,string? anchor=null)
    {
        Volatile.Write(ref statisticsCache,MarketVerifiedGeneration.TryCapture(path,out var stamp)?new(path,health,latest,stamp,anchor):null);
    }

    private static long DataVersion(SqliteConnection connection,SqliteTransaction? transaction=null)
    {using var query=connection.CreateCommand();query.Transaction=transaction;query.CommandText="PRAGMA data_version";return(long)query.ExecuteScalar()!;}

    private void CacheAfterWrite(StatisticsCache? previous,SqliteConnection connection,long version,int stationDelta,long quoteDelta,int snapshotDelta)
    {
        var path=Path.GetFullPath(connection.DataSource);
        if(previous is null||previous.Path!=path||DatabasePath!=path)
        {InvalidateStatistics("write-path-or-prior-cache-mismatch",new{previousPath=previous?.Path,nativeConnectionPath=connection.DataSource,connectionPath=path,suppliedPath=new SqliteConnectionStringBuilder(connection.ConnectionString).DataSource});return;}
        using var latest=connection.CreateCommand();latest.CommandText="SELECT max(timestamp) FROM quotes";var value=latest.ExecuteScalar();
        CacheStatistics(path,previous.Health with{Stations=previous.Health.Stations+stationDelta,Quotes=previous.Health.Quotes+quoteDelta,Snapshots=previous.Health.Snapshots+snapshotDelta},value is long time?time:null,previous.IntegrityAnchor);
        var afterVersion=DataVersion(connection);
        if(afterVersion!=version||DatabasePath!=path)InvalidateStatistics("write-data-version-or-active-generation-changed",new{beforeVersion=version,afterVersion,path});
    }

    private void ClearIdleConnections()
    {
        string path;try{path=DatabasePath;}catch(InvalidDataException){path=originalPath;}
        var write=new SqliteConnectionStringBuilder{DataSource=path,Mode=SqliteOpenMode.ReadWriteCreate,DefaultTimeout=20}.ToString();
        // Search owns a distinct read-only pool. Its returned connections may
        // still hold WAL handles even after the SQL reader has been disposed.
        // Clear only our two exact pools; borrowed active readers stay alive.
        foreach(var value in new[]{write,new SqliteConnectionStringBuilder(write){Mode=SqliteOpenMode.ReadOnly}.ToString()})
        {using var pool=new SqliteConnection(value);SqliteConnection.ClearPool(pool);}
    }

    private MarketGenerationStamp? PrepareFullValidation()
    {
        var path=DatabasePath;ClearIdleConnections();
        using(var connection=new SqliteConnection(new SqliteConnectionStringBuilder{DataSource=path,Mode=SqliteOpenMode.ReadWrite,Pooling=false,DefaultTimeout=1}.ToString()))
        {
            connection.Open();using var query=connection.CreateCommand();query.CommandText="PRAGMA wal_checkpoint(TRUNCATE)";
            using var result=query.ExecuteReader();
            if(!result.Read()||result.GetInt64(0)!=0||result.GetInt64(1)>0)return null;
        }
        return MarketVerifiedGeneration.TryCapture(path,out var stamp)&&stamp.Wal is null?stamp:null;
    }

    private bool SealFullValidation()
    {
        var pending=pendingFullValidation;if(pending is null)return false;
        if(DatabasePath!=pending.Path||!MarketVerifiedGeneration.Matches(pending.Path,pending.Stamp))
        {pendingFullValidation=null;return false;}
        try
        {
            ClearIdleConnections();
            var proof=MarketVerifiedGeneration.RecordFresh(pending.Path,pending.Health,pending.Latest,pending.Stamp);
            if(DatabasePath!=pending.Path){pendingFullValidation=null;Volatile.Write(ref statisticsCache,null);return false;}
            pendingFullValidation=null;Volatile.Write(ref statisticsCache,new(pending.Path,proof.Health,proof.Latest,proof.Stamp,proof.Sha256));
            MarketCacheMaintenance.Log(pending.Path,"verified-generation-rebuilt",new{proof.Health,proof.Latest,proof.Sha256,proof.VerifiedAt});return true;
        }
        catch(Exception error)when(error is IOException or UnauthorizedAccessException or InvalidDataException)
        {MarketCacheMaintenance.Log(pending.Path,"verified-generation-rebuild-deferred",error.Message);return false;}
    }

    private bool SealTrustedWrites(int checkpointTimeoutSeconds=20)
    {
        if(!automaticRecovery)return false;
        try
        {
            var path=DatabasePath;StatisticsCache current;MarketGenerationStamp expected;bool checkpointComplete;
            using(var connection=new SqliteConnection(new SqliteConnectionStringBuilder{DataSource=path,Mode=SqliteOpenMode.ReadWriteCreate,Pooling=false,DefaultTimeout=checkpointTimeoutSeconds}.ToString()))
            {
                connection.Open();
                // Establish the connection's foreign-write clock before
                // checking which verified state is eligible for continuation.
                var version=DataVersion(connection);
                var candidate=CurrentStatisticsCache();
                if(candidate?.IntegrityAnchor is null||candidate.Health.Quotes<=0||candidate.Latest is null||candidate.Path!=path)return false;
                current=candidate;
                if(DataVersion(connection)!=version){InvalidateStatistics("seal-data-version-changed");return false;}
                using(var checkpoint=connection.CreateCommand())
                {
                    checkpoint.CommandText="PRAGMA wal_checkpoint(TRUNCATE)";
                    using var result=checkpoint.ExecuteReader();
                    if(!result.Read())return false;
                    checkpointComplete=result.GetInt64(0)==0&&result.GetInt64(1)<=0;
                }
                // A busy checkpoint may still copy some WAL pages into the
                // database. That is our mutation even though WAL cannot yet be
                // truncated; retain its new exact stamp for the later retry.
                if(!MarketVerifiedGeneration.TryCapture(path,out expected)||DataVersion(connection)!=version||DatabasePath!=path)
                {InvalidateStatistics("seal-checkpoint-identity-or-data-version-changed",new{path,version,afterVersion=DataVersion(connection),expectedWal=expected?.Wal});return false;}
                SqliteConnection.ClearPool(connection);
            }
            ClearIdleConnections();
            // A successful checkpoint is still our known write chain even if
            // another open handle temporarily prevents the final file seal.
            // Keep its exact post-checkpoint stamp so a later retry can verify
            // it; any intervening external change invalidates this cache.
            Volatile.Write(ref statisticsCache,new(path,current.Health,current.Latest,expected,current.IntegrityAnchor));
            if(!checkpointComplete||expected.Wal is not null)return false;
            // Only an unbroken chain of verified state + this repository's
            // committed transactions may continue the original integrity
            // anchor. Unknown external changes never acquire a new receipt.
            var proof=MarketVerifiedGeneration.Record(path,current.Health,current.Latest,current.IntegrityAnchor!,true,expected);
            if(DatabasePath!=path){Volatile.Write(ref statisticsCache,null);return false;}
            Volatile.Write(ref statisticsCache,new(path,proof.Health,proof.Latest,proof.Stamp,proof.Sha256));return true;
        }
        catch(Exception error)when(error is IOException or SqliteException or InvalidDataException)
        {MarketCacheMaintenance.Log(DatabasePath,"verified-generation-seal-deferred",error.Message);return false;}
    }

    public async Task<bool> FlushVerifiedGenerationAsync(CancellationToken token=default,bool completeFullValidation=true)
    {
        if(!automaticRecovery)return false;
        await gate.WaitAsync(token);try{return File.Exists(DatabasePath)&&(completeFullValidation&&SealFullValidation()||SealTrustedWrites(1));}finally{gate.Release();}
    }

    public MarketRepository(string file)
    {
        originalPath = Path.GetFullPath(file);
        Directory.CreateDirectory(Path.GetDirectoryName(Path.GetFullPath(file))!);
    }

    private SqliteConnection Open()
    {
        var connection = new SqliteConnection(connectionString);
        try{connection.Open();return connection;}catch{connection.Dispose();throw;}
    }

    private SqliteConnection OpenReadOnly()
    {
        // A query must not own a writable SQLite handle. Closing the last such
        // handle can checkpoint WAL pages after a writer captured its stamp.
        // Close native read handles at the end of the query too: an idle RO
        // pool can otherwise keep WAL alive after the final RW handle closes.
        var connection=new SqliteConnection(readOnlyConnectionString);
        try{connection.Open();return connection;}catch{connection.Dispose();throw;}
    }

    public void Initialize()
    {
        gate.Wait();
        try
        {
            automaticRecovery=true;
            if(MarketVerifiedGeneration.TryRead(DatabasePath,out var receipt))
            {
                using(var connection=new SqliteConnection(new SqliteConnectionStringBuilder{DataSource=DatabasePath,Mode=SqliteOpenMode.ReadOnly,Pooling=false}.ToString()))
                {
                    connection.Open();using var schema=connection.CreateCommand();
                    schema.CommandText="SELECT id,name,system,x,y,z,json FROM stations LIMIT 0; SELECT station,commodity,name,buy,sell,supply,demand,timestamp,source,json FROM quotes LIMIT 0; SELECT station,timestamp,source FROM snapshots LIMIT 0";schema.ExecuteNonQuery();
                }
                if(!MarketVerifiedGeneration.Matches(DatabasePath,receipt.Stamp))throw new InvalidDataException("已验证商品缓存在启动时发生变化。");
                Volatile.Write(ref statisticsCache,new(DatabasePath,receipt.Health,receipt.Latest,receipt.Stamp,receipt.Sha256));
                backupDue=DateTimeOffset.UtcNow.AddMinutes(30);
                MarketCacheMaintenance.Log(DatabasePath,"startup-verified-generation",new{receipt.Health,receipt.Latest,receipt.VerifiedAt,IntegrityAnchorSha256=receipt.Sha256,receipt.TrustedWriteContinuation});
                return;
            }
            try
            {
                InitializeSchema();
                var path=DatabasePath;var full=File.Exists(path+".verified-generation.json");MarketGenerationStamp? expected=null;
                if(full)try{expected=PrepareFullValidation();}
                catch(Exception error)when(error is IOException||error is SqliteException sql&&!MarketCacheMaintenance.IsCorruption(sql))
                {MarketCacheMaintenance.Log(path,"verified-generation-checkpoint-deferred",error.Message);}
                var health = MarketCacheMaintenance.Check(path,full);
                using(var connection=OpenReadOnly())
                {
                    using var latest=connection.CreateCommand();latest.CommandText="SELECT max(timestamp) FROM quotes";var value=latest.ExecuteScalar();
                    CacheStatistics(path,health,value is long time?time:null);
                    if(full&&expected is not null&&health.Stations>0&&health.Quotes>0&&value is long at&&MarketVerifiedGeneration.Matches(path,expected))
                        pendingFullValidation=new(path,health,at,expected);
                }
                if(full)SealFullValidation();
                MarketCacheMaintenance.Log(path, "startup-healthy", health);
            }
            catch (Exception error) when (error is InvalidDataException || error is SqliteException sql && MarketCacheMaintenance.IsCorruption(sql))
            {
                MarketCacheMaintenance.Log(DatabasePath, "startup-corrupt", error.ToString());
                LastRecovery = MarketCacheMaintenance.Restore(DatabasePath, VerifiedBackupPath,originalPath);
                InitializeSchema();
                MarketCacheMaintenance.Log(DatabasePath, "restored", LastRecovery);
            }
            // Preserve a useful known-good snapshot, never an empty freshly initialized cache.
            var current = StatisticsCore();
            if (current.Quotes > 0)
            {
                try { MarketCacheMaintenance.Check(VerifiedBackupPath); }
                catch (Exception error) when (error is InvalidDataException || error is SqliteException sql && (MarketCacheMaintenance.IsCorruption(sql) || sql.SqliteErrorCode == 14))
                { MarketCacheMaintenance.SaveVerifiedBackup(DatabasePath, VerifiedBackupPath); }
                backupDue = DateTimeOffset.UtcNow.AddMinutes(30);
            }
        }
        finally { gate.Release(); }
    }

    private void InitializeSchema()
    {
        using var connection = Open();
        using var cmd = connection.CreateCommand();
        cmd.CommandText = """
            PRAGMA journal_mode=WAL;
            CREATE TABLE IF NOT EXISTS stations(id INTEGER PRIMARY KEY, name TEXT NOT NULL, system TEXT NOT NULL COLLATE NOCASE, x REAL, y REAL, z REAL, json TEXT NOT NULL);
            CREATE INDEX IF NOT EXISTS idx_stations_system ON stations(system);
            CREATE TABLE IF NOT EXISTS snapshots(station INTEGER PRIMARY KEY, timestamp INTEGER NOT NULL, source INTEGER NOT NULL);
            CREATE TABLE IF NOT EXISTS quotes(station INTEGER NOT NULL, commodity TEXT NOT NULL, name TEXT NOT NULL, buy INTEGER NOT NULL, sell INTEGER NOT NULL, supply INTEGER NOT NULL, demand INTEGER NOT NULL, timestamp INTEGER NOT NULL, source INTEGER NOT NULL, json TEXT NOT NULL, PRIMARY KEY(station,commodity));
            CREATE INDEX IF NOT EXISTS idx_quotes_commodity ON quotes(commodity);
            CREATE INDEX IF NOT EXISTS idx_quotes_time ON quotes(timestamp DESC);
            """;
        cmd.ExecuteNonQuery();
    }

    public async Task<MarketCacheRecovery?> RecoverAsync(CancellationToken token = default)
    {
        if(!automaticRecovery)throw new InvalidDataException("行情缓存不可用，请在设置中清理行情缓存。");
        await gate.WaitAsync(token);
        try
        {
            using(var pool=new SqliteConnection(connectionString))SqliteConnection.ClearPool(pool);
            try { MarketCacheMaintenance.Check(DatabasePath, true); return null; }
            catch (Exception error) when (error is InvalidDataException || error is SqliteException sql && MarketCacheMaintenance.IsCorruption(sql))
            {
                LastRecovery = MarketCacheMaintenance.Restore(DatabasePath, VerifiedBackupPath,originalPath);
                MarketCacheMaintenance.Log(DatabasePath, "restored-after-query", LastRecovery);
                return LastRecovery;
            }
        }
        finally { gate.Release(); }
    }

    public async Task SaveVerifiedBackupAsync(CancellationToken token = default)
    {
        await gate.WaitAsync(token);
        try { MarketCacheMaintenance.SaveVerifiedBackup(DatabasePath, VerifiedBackupPath); }
        finally { gate.Release(); }
    }

    public async Task<bool> SaveSnapshotAsync(MarketSnapshot snapshot, CancellationToken token = default)
    {
        try { return await SaveSnapshotCoreAsync(snapshot, token); }
        catch (SqliteException error) when (automaticRecovery&&MarketCacheMaintenance.IsCorruption(error))
        {
            await RecoverAsync(token);
            return await SaveSnapshotCoreAsync(snapshot, token);
        }
    }

    private async Task<bool> SaveSnapshotCoreAsync(MarketSnapshot snapshot, CancellationToken token)
    {
        if (snapshot.Station.MarketId <= 0 || snapshot.Station.Name.Length == 0 || snapshot.Station.SystemName.Length == 0) return false;
        if (snapshot.CollectedAt > DateTimeOffset.UtcNow.AddMinutes(5)) return false;
        if(snapshot.Source!=DataSource.Journal&&PublicDataFilter?.Invoke(snapshot.Station)==false)return false;
        await gate.WaitAsync(token);
        try
        {
            using var connection = Open();
            using var tx = connection.BeginTransaction();
            var cached=CurrentStatisticsCache();var version=DataVersion(connection,tx);
            var newStation=SaveStation(connection, tx, snapshot.Station);
            using var check = connection.CreateCommand();
            check.Transaction = tx;
            check.CommandText = "SELECT timestamp,source FROM snapshots WHERE station=$id";
            check.Parameters.AddWithValue("$id", snapshot.Station.MarketId);
            var oldSnapshot=false;
            using (var reader = check.ExecuteReader())
            {
                if(reader.Read())
                {
                    oldSnapshot=true;
                    if(reader.GetInt64(0)>snapshot.CollectedAt.ToUnixTimeMilliseconds()||reader.GetInt64(0)==snapshot.CollectedAt.ToUnixTimeMilliseconds()&&reader.GetInt32(1)>(int)snapshot.Source)
                    {reader.Close();tx.Commit();CacheAfterWrite(cached,connection,version,newStation?1:0,0,0);return false;}
                }
            }
            using var previousQuotes=connection.CreateCommand();previousQuotes.Transaction=tx;previousQuotes.CommandText="SELECT count(*) FROM quotes WHERE station=$id";previousQuotes.Parameters.AddWithValue("$id",snapshot.Station.MarketId);
            var oldQuoteCount=cached is null?0:(long)previousQuotes.ExecuteScalar()!;long inserted=0;
            using var delete = connection.CreateCommand();
            delete.Transaction = tx;
            delete.CommandText = "DELETE FROM quotes WHERE station=$id";
            delete.Parameters.AddWithValue("$id", snapshot.Station.MarketId);
            delete.ExecuteNonQuery();
            foreach (var q in snapshot.Quotes.DistinctBy(x => x.Commodity))
            {
                token.ThrowIfCancellationRequested();
                if (string.IsNullOrWhiteSpace(q.Commodity) || q.MarketId != snapshot.Station.MarketId) continue;
                using var insert = connection.CreateCommand();
                insert.Transaction = tx;
                insert.CommandText = "INSERT INTO quotes VALUES($id,$key,$name,$buy,$sell,$supply,$demand,$time,$source,$json)";
                insert.Parameters.AddWithValue("$id", q.MarketId);
                insert.Parameters.AddWithValue("$key", q.Commodity);
                insert.Parameters.AddWithValue("$name", q.DisplayName);
                insert.Parameters.AddWithValue("$buy", q.BuyPrice);
                insert.Parameters.AddWithValue("$sell", q.SellPrice);
                insert.Parameters.AddWithValue("$supply", q.Supply);
                insert.Parameters.AddWithValue("$demand", q.Demand);
                insert.Parameters.AddWithValue("$time", snapshot.CollectedAt.ToUnixTimeMilliseconds());
                insert.Parameters.AddWithValue("$source", (int)snapshot.Source);
                insert.Parameters.AddWithValue("$json", JsonSerializer.Serialize(q with { CollectedAt = snapshot.CollectedAt, Source = snapshot.Source }, Json));
                insert.ExecuteNonQuery();
                inserted++;
            }
            using var stamp = connection.CreateCommand();
            stamp.Transaction = tx;
            stamp.CommandText = "INSERT OR REPLACE INTO snapshots VALUES($id,$time,$source)";
            stamp.Parameters.AddWithValue("$id", snapshot.Station.MarketId);
            stamp.Parameters.AddWithValue("$time", snapshot.CollectedAt.ToUnixTimeMilliseconds());
            stamp.Parameters.AddWithValue("$source", (int)snapshot.Source);
            stamp.ExecuteNonQuery();
            tx.Commit();
            CacheAfterWrite(cached,connection,version,newStation?1:0,inserted-oldQuoteCount,oldSnapshot?0:1);
            RefreshBackupIfDue();
            return true;
        }
        finally { SealTrustedWrites();gate.Release(); }
    }

    public async Task SaveStationAsync(Station station, CancellationToken token = default,bool localObservation=false)
    {
        try { await SaveStationCoreAsync(station, token,localObservation); }
        catch (SqliteException error) when (automaticRecovery&&MarketCacheMaintenance.IsCorruption(error))
        {
            await RecoverAsync(token);
            await SaveStationCoreAsync(station, token,localObservation);
        }
    }

    private async Task SaveStationCoreAsync(Station station, CancellationToken token,bool localObservation=false)
    {
        if (station.MarketId <= 0 || station.Name.Length == 0) return;
        if(!localObservation&&PublicDataFilter?.Invoke(station)==false)return;
        await gate.WaitAsync(token);
        try
        {
            using var connection = Open();
            using var tx = connection.BeginTransaction();
            var cached=CurrentStatisticsCache();var version=DataVersion(connection,tx);
            var created=SaveStation(connection, tx, station);
            tx.Commit();
            CacheAfterWrite(cached,connection,version,created?1:0,0,0);
        }
        finally { SealTrustedWrites();gate.Release(); }
    }

    private void RefreshBackupIfDue()
    {
        if(!automaticRecovery)return;
        if (DateTimeOffset.UtcNow < backupDue) return;
        try
        {
            MarketCacheMaintenance.SaveVerifiedBackup(DatabasePath, VerifiedBackupPath);
            backupDue = DateTimeOffset.UtcNow.AddMinutes(30);
        }
        catch (Exception error) when (error is SqliteException or InvalidDataException or IOException)
        {
            backupDue = DateTimeOffset.UtcNow.AddMinutes(1);
            MarketCacheMaintenance.Log(DatabasePath, "backup-failed", error.ToString());
        }
    }

    private static bool SaveStation(SqliteConnection connection, SqliteTransaction tx, Station incoming)
    {
        using var get = connection.CreateCommand();
        get.Transaction = tx;
        get.CommandText = "SELECT json FROM stations WHERE id=$id";
        get.Parameters.AddWithValue("$id", incoming.MarketId);
        var old = get.ExecuteScalar() is string json ? JsonSerializer.Deserialize<Station>(json, Json) : null;
        Station s;
        if (old is null) s = incoming;
        else
        {
            var newer = incoming.UpdatedAt >= old.UpdatedAt;
            // Never attach coordinates to a carrier's new system from its previous location.
            var moved = incoming.SystemName.Length > 0 && !old.SystemName.Equals(incoming.SystemName, StringComparison.OrdinalIgnoreCase);
            if (!newer && moved) return false;
            s = (newer ? incoming : old) with
            {
                Name = incoming.Name.Length > 0 ? incoming.Name : old.Name,
                SystemName = newer && incoming.SystemName.Length > 0 ? 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
            };
        }
        using var cmd = connection.CreateCommand();
        cmd.Transaction = tx;
        cmd.CommandText = "INSERT OR REPLACE INTO stations VALUES($id,$name,$system,$x,$y,$z,$json)";
        cmd.Parameters.AddWithValue("$id", s.MarketId);
        cmd.Parameters.AddWithValue("$name", s.Name);
        cmd.Parameters.AddWithValue("$system", s.SystemName);
        cmd.Parameters.AddWithValue("$x", (object?)s.Position?.X ?? DBNull.Value);
        cmd.Parameters.AddWithValue("$y", (object?)s.Position?.Y ?? DBNull.Value);
        cmd.Parameters.AddWithValue("$z", (object?)s.Position?.Z ?? DBNull.Value);
        cmd.Parameters.AddWithValue("$json", JsonSerializer.Serialize(s, Json));
        cmd.ExecuteNonQuery();
        return old is null;
    }

    public async Task UpdateSystemAsync(string name, Coordinates? position, SecurityLevel security, CancellationToken token = default)
    {
        var stations = GetStations(name).Where(x => x.SystemName.Equals(name, StringComparison.OrdinalIgnoreCase)).ToArray();
        foreach (var s in stations) await SaveStationAsync(s with { Position = position ?? s.Position, Security = security == SecurityLevel.Unknown ? s.Security : security }, token);
    }

    public IReadOnlyList<Station> GetStations(string search = "", int limit = 1000)
    {
        using var read=EnterRead();
        using var c = OpenReadOnly();
        using var cmd = c.CreateCommand();
        cmd.CommandText = "SELECT json FROM stations WHERE name LIKE $q OR system LIKE $q ORDER BY system,name LIMIT $limit";
        cmd.Parameters.AddWithValue("$q", "%" + EscapeLike(search) + "%");
        cmd.Parameters.AddWithValue("$limit", Math.Clamp(limit, 1, 10000));
        using var reader = cmd.ExecuteReader();
        var result = new List<Station>();
        while (reader.Read()) result.Add(JsonSerializer.Deserialize<Station>(reader.GetString(0), Json)!);
        return result;
    }

    public IReadOnlyList<Station> GetStationsNear(Coordinates center,double radius,int limit=1000)
    {
        ValidateSphere(center,radius);
        using var read=EnterRead();using var connection=OpenReadOnly();
        return ReadStationsNear(connection,null,center,radius,Math.Clamp(limit,1,10000),null);
    }

    private static void ValidateSphere(Coordinates center,double radius)
    {
        ArgumentNullException.ThrowIfNull(center);
        if(!center.IsValid)throw new ArgumentException("中心星系缺少有效坐标。",nameof(center));
        if(!double.IsFinite(radius)||radius<=0||!double.IsFinite(radius*radius))
            throw new ArgumentOutOfRangeException(nameof(radius),"范围半径必须是有效的正数。");
        if(!double.IsFinite(center.X-radius)||!double.IsFinite(center.X+radius)||!double.IsFinite(center.Y-radius)||!double.IsFinite(center.Y+radius)
            ||!double.IsFinite(center.Z-radius)||!double.IsFinite(center.Z+radius))
            throw new ArgumentOutOfRangeException(nameof(radius),"中心坐标与范围半径超出有效数值范围。");
    }

    private static List<Station> ReadStationsNear(SqliteConnection connection,SqliteTransaction? transaction,Coordinates center,double radius,int limit,long? originId)
    {
        using var command=connection.CreateCommand();command.Transaction=transaction;
        command.CommandText="""
            SELECT json FROM stations
            WHERE x BETWEEN $x-$r AND $x+$r AND y BETWEEN $y-$r AND $y+$r AND z BETWEEN $z-$r AND $z+$r
            AND ((x-$x)*(x-$x)+(y-$y)*(y-$y)+(z-$z)*(z-$z)) <= $r2
            ORDER BY (id=$origin) DESC,((x-$x)*(x-$x)+(y-$y)*(y-$y)+(z-$z)*(z-$z)),system,name,id LIMIT $limit
            """;
        command.Parameters.AddWithValue("$x",center.X);command.Parameters.AddWithValue("$y",center.Y);command.Parameters.AddWithValue("$z",center.Z);
        command.Parameters.AddWithValue("$r",radius);command.Parameters.AddWithValue("$r2",radius*radius);command.Parameters.AddWithValue("$origin",(object?)originId??DBNull.Value);
        command.Parameters.AddWithValue("$limit",limit);using var reader=command.ExecuteReader();var stations=new List<Station>();
        while(reader.Read())stations.Add(JsonSerializer.Deserialize<Station>(reader.GetString(0),Json)!);
        return stations;
    }

    public Station? GetStation(long id)
    {using var read=EnterRead();return GetStationCore(id);}

    private Station? GetStationCore(long id)
    {
        using var c = OpenReadOnly();
        return ReadStation(c,id);
    }

    private static Station? ReadStation(SqliteConnection c,long id,SqliteTransaction? transaction=null)
    {
        using var cmd = c.CreateCommand();
        cmd.Transaction=transaction;
        cmd.CommandText = "SELECT json FROM stations WHERE id=$id";
        cmd.Parameters.AddWithValue("$id", id);
        return cmd.ExecuteScalar() is string s ? JsonSerializer.Deserialize<Station>(s, Json) : null;
    }

    public IReadOnlyList<MarketRow> Search(string search, long? marketId, int offset = 0, int limit = 150, MarketSort sort = MarketSort.Stable,
        CancellationToken token=default,TimeSpan? budget=null)
    {
        using var read=EnterRead(token);
        token.ThrowIfCancellationRequested();
        var duration=budget??TimeSpan.FromSeconds(5);
        if(duration<=TimeSpan.Zero)throw new ArgumentOutOfRangeException(nameof(budget));
        using var c = OpenReadOnly();
        using var stopped=CancellationTokenSource.CreateLinkedTokenSource(token);stopped.CancelAfter(duration);
        // SqliteCommand.Cancel is a no-op. Interrupt handles running native work;
        // the progress callback also closes cancellation's pre-execution race.
        using var interrupt=stopped.Token.Register(()=>SQLitePCL.raw.sqlite3_interrupt(c.Handle));
        SQLitePCL.raw.sqlite3_progress_handler(c.Handle,1000,_=>stopped.IsCancellationRequested?1:0,null);
        void ThrowIfStopped()
        {
            token.ThrowIfCancellationRequested();
            if(stopped.IsCancellationRequested)throw new TimeoutException("行情查询超时，请限定当前站点或输入更完整的商品名称后重试。");
        }
        try
        {
        using var cmd = c.CreateCommand();
        cmd.CommandText = """
            SELECT s.json,q.json FROM quotes q JOIN stations s ON s.id=q.station
            WHERE ($id IS NULL OR s.id=$id) AND (q.name LIKE $q OR q.commodity LIKE $q OR s.name LIKE $q OR s.system LIKE $q)
            ORDER BY SORT_EXPRESSION,s.system,s.name,q.commodity LIMIT $limit OFFSET $offset
            """;
        var order=sort switch { MarketSort.Newest=>"q.timestamp DESC",MarketSort.BuyDescending=>"q.buy DESC",MarketSort.SellDescending=>"q.sell DESC",MarketSort.SupplyDescending=>"q.supply DESC",MarketSort.DemandDescending=>"q.demand DESC",_=>"s.system,s.name,q.commodity" };
        cmd.CommandText=cmd.CommandText.Replace("SORT_EXPRESSION",order,StringComparison.Ordinal);
        if(marketId is not null)
            cmd.CommandText=cmd.CommandText.Replace("($id IS NULL OR s.id=$id)","q.station=$id",StringComparison.Ordinal);
        else if(search.Length==0&&sort==MarketSort.Stable)
        {
            // At least one row per selected station guarantees enough rows for
            // this page. Include every station tied with the final station's
            // sort key so duplicate station names cannot truncate an earlier
            // commodity. Only this bounded set reaches the quotes join/sort.
            cmd.CommandText="""
                WITH first_stations AS MATERIALIZED (
                    SELECT s.id,s.system,s.name FROM stations s INDEXED BY idx_stations_system
                    WHERE EXISTS(SELECT 1 FROM quotes q WHERE q.station=s.id)
                    ORDER BY s.system,s.name,s.id LIMIT $stationCount
                ), boundary AS MATERIALIZED (
                    SELECT system,name FROM first_stations ORDER BY system DESC,name DESC LIMIT 1
                ), chosen AS MATERIALIZED (
                    SELECT id FROM first_stations
                    UNION SELECT s.id FROM stations s JOIN boundary b ON s.system=b.system AND s.name=b.name
                    WHERE EXISTS(SELECT 1 FROM quotes q WHERE q.station=s.id)
                )
                SELECT s.json,q.json FROM chosen c JOIN stations s ON s.id=c.id JOIN quotes q ON q.station=s.id
                ORDER BY s.system,s.name,q.commodity LIMIT $limit OFFSET $offset
                """;
            cmd.Parameters.AddWithValue("$stationCount",(long)Math.Max(0,offset)+Math.Clamp(limit,1,1000));
        }
        else if(sort==MarketSort.Stable)
        {
            // Preserve the full OR-LIKE predicate, including arbitrary imported
            // display names. Station order permits LIMIT to stop before traversing
            // later systems instead of sorting every matching quote in the galaxy.
            cmd.CommandText=cmd.CommandText.Replace("FROM quotes q JOIN stations s ON s.id=q.station",
                "FROM stations s INDEXED BY idx_stations_system CROSS JOIN quotes q ON q.station=s.id",StringComparison.Ordinal);
        }
        else if(sort==MarketSort.Newest)
        {
            cmd.CommandText=cmd.CommandText.Replace("FROM quotes q JOIN stations s ON s.id=q.station",
                "FROM quotes q INDEXED BY idx_quotes_time CROSS JOIN stations s ON s.id=q.station",StringComparison.Ordinal);
        }
        cmd.Parameters.AddWithValue("$q", "%" + EscapeLike(search) + "%");
        cmd.Parameters.AddWithValue("$id", (object?)marketId ?? DBNull.Value);
        cmd.Parameters.AddWithValue("$offset", Math.Max(0, offset));
        cmd.Parameters.AddWithValue("$limit", Math.Clamp(limit, 1, 1000));
        ThrowIfStopped();
        using var reader = cmd.ExecuteReader();
        var result = new List<MarketRow>();
        while (reader.Read()) result.Add(new(JsonSerializer.Deserialize<Station>(reader.GetString(0), Json)!, JsonSerializer.Deserialize<CommodityQuote>(reader.GetString(1), Json)!));
        ThrowIfStopped();
        return result;
        }
        catch(SqliteException error)when(error.SqliteErrorCode==9&&stopped.IsCancellationRequested)
        {ThrowIfStopped();throw;}
        finally{SQLitePCL.raw.sqlite3_progress_handler(c.Handle,0,null,null);}
    }

    public MarketBook LoadRegion(long originId,double radius,int limit=180,Coordinates? center=null)
    {
        using var read=EnterRead();
        using var c=OpenReadOnly();using var transaction=c.BeginTransaction(deferred:true);
        var origin=ReadStation(c,originId,transaction)??throw new ArgumentException("请选择起始站点。",nameof(originId));
        if(origin.Position is not{IsValid:true})throw new ArgumentException("起始星系缺少有效坐标，请先补全附近星系数据。",nameof(originId));
        var selectedCenter=center??origin.Position;ValidateSphere(selectedCenter,radius);
        if(origin.Position.DistanceTo(selectedCenter)>radius)
            throw new InvalidOperationException("所选起始站点不在中心星系的指定范围内，请选择范围内的起始站点。");
        var maximum=Math.Clamp(limit,1,10000);var stations=ReadStationsNear(c,transaction,selectedCenter,radius,maximum+1,originId);
        var truncated=stations.Count>maximum;stations=stations.Take(maximum).ToList();
        var quotes = new List<CommodityQuote>();
        using var getQuotes = c.CreateCommand();
        getQuotes.Transaction=transaction;
        getQuotes.CommandText = $"SELECT json FROM quotes WHERE station IN ({string.Join(',', stations.Select(x => x.MarketId.ToString(CultureInfo.InvariantCulture)))})";
        using (var reader = getQuotes.ExecuteReader()) while (reader.Read()) quotes.Add(JsonSerializer.Deserialize<CommodityQuote>(reader.GetString(0), Json)!);
        transaction.Commit();
        return new(stations, quotes, truncated);
    }

    public MarketBook LoadPlanningRegion(PlannerRequest request,CancellationToken token=default,TimeSpan? budget=null,IProgress<Planning.TradePlanningProgress>? progress=null)
    {
        request.Validate();Planning.TradeRoutePlanner.RequireKnownShip(request);var duration=budget??TimeSpan.FromSeconds(5);if(duration<=TimeSpan.Zero)throw new ArgumentOutOfRangeException(nameof(budget));
        using var read=EnterRead(token);using var connection=OpenReadOnly();
        using var stopped=CancellationTokenSource.CreateLinkedTokenSource(token);stopped.CancelAfter(duration);
        using var interrupt=stopped.Token.Register(()=>SQLitePCL.raw.sqlite3_interrupt(connection.Handle));
        SQLitePCL.raw.sqlite3_progress_handler(connection.Handle,1000,_=>stopped.IsCancellationRequested?1:0,null);
        void CheckBudget(){token.ThrowIfCancellationRequested();if(stopped.IsCancellationRequested)throw new TimeoutException("周边行情读取超时，尚未完成范围检查；请稍后重试或缩小范围。");}
        try
        {
            CheckBudget();using var transaction=connection.BeginTransaction(deferred:true);
            var origin=ReadStation(connection,request.StartMarketId,transaction);
            var center=request.SearchCenterPosition??request.CurrentPosition??origin?.Position??throw new ArgumentException("搜索中心与真实当前位置均未知。");ValidateSphere(center,request.SearchRadiusLy);
            using var select=connection.CreateCommand();select.Transaction=transaction;
            // Drive from the time index; every fresh compatible station is
            // read, including carriers. There is no station or quote row cap.
            select.CommandText="""
                WITH fresh AS MATERIALIZED(SELECT DISTINCT station FROM quotes INDEXED BY idx_quotes_time WHERE timestamp BETWEEN $oldest AND $future),
                chosen AS(SELECT station FROM fresh UNION SELECT $origin)
                SELECT s.json FROM chosen c JOIN stations s ON s.id=c.station
                WHERE s.x BETWEEN $x-$r AND $x+$r AND s.y BETWEEN $y-$r AND $y+$r AND s.z BETWEEN $z-$r AND $z+$r
                AND ((s.x-$x)*(s.x-$x)+(s.y-$y)*(s.y-$y)+(s.z-$z)*(s.z-$z)) <= $r2
                ORDER BY (s.id=$origin) DESC,((s.x-$x)*(s.x-$x)+(s.y-$y)*(s.y-$y)+(s.z-$z)*(s.z-$z)),s.system,s.name,s.id
                """;
            select.Parameters.AddWithValue("$oldest",request.AsOf.AddMinutes(-request.MaxMarketAgeMinutes).ToUnixTimeMilliseconds());
            select.Parameters.AddWithValue("$future",request.AsOf.AddMinutes(5).ToUnixTimeMilliseconds());select.Parameters.AddWithValue("$origin",request.StartMarketId);
            select.Parameters.AddWithValue("$x",center.X);select.Parameters.AddWithValue("$y",center.Y);select.Parameters.AddWithValue("$z",center.Z);
            select.Parameters.AddWithValue("$r",request.SearchRadiusLy);select.Parameters.AddWithValue("$r2",request.SearchRadiusLy*request.SearchRadiusLy);
            progress?.Report(new("读取全部新鲜行情站点",0,null,"按时间索引和实际范围读取，不截断站数"));var stations=new List<Station>();long processed=0;
            using(var rows=select.ExecuteReader())while(rows.Read())
            {
                CheckBudget();var station=JsonSerializer.Deserialize<Station>(rows.GetString(0),Json)!;
                processed++;if(processed%128==0)progress?.Report(new("读取全部新鲜行情站点",processed,null,$"已核对 {processed} 个范围内站点"));
                if(station.MarketId!=request.StartMarketId&&!Planning.TradeRoutePlanner.IsStationCompatible(station,request,false))continue;
                stations.Add(station);
            }
            progress?.Report(new("读取全部新鲜行情站点",processed,processed,$"站点核对完成，纳入 {stations.Count} 个合规站点"));
            var quotes=new List<CommodityQuote>();progress?.Report(new("读取全部新鲜商品报价",0,null,"读取各站时效内报价，不截断商品行"));
            if(stations.Count>0)
            {
                using var query=connection.CreateCommand();query.Transaction=transaction;
                query.CommandText=$"SELECT json FROM quotes WHERE station IN ({string.Join(',',stations.Select(station=>station.MarketId.ToString(CultureInfo.InvariantCulture)))}) AND timestamp BETWEEN $oldest AND $future";
                query.Parameters.AddWithValue("$oldest",request.AsOf.AddMinutes(-request.MaxMarketAgeMinutes).ToUnixTimeMilliseconds());
                query.Parameters.AddWithValue("$future",request.AsOf.AddMinutes(5).ToUnixTimeMilliseconds());
                using var rows=query.ExecuteReader();while(rows.Read()){CheckBudget();quotes.Add(JsonSerializer.Deserialize<CommodityQuote>(rows.GetString(0),Json)!);if(quotes.Count%256==0)progress?.Report(new("读取全部新鲜商品报价",quotes.Count,null,$"已读取 {quotes.Count} 条新鲜报价"));}
            }
            CheckBudget();progress?.Report(new("读取全部新鲜商品报价",quotes.Count,quotes.Count,"报价读取完成"));transaction.Commit();progress?.Report(new("行情读取完成",stations.Count,stations.Count,$"共 {stations.Count} 个站点、{quotes.Count} 条报价"));return new(stations,quotes,false);
        }
        catch(SqliteException error)when(error.SqliteErrorCode==9&&stopped.IsCancellationRequested){CheckBudget();throw;}
        finally{SQLitePCL.raw.sqlite3_progress_handler(connection.Handle,0,null,null);}
    }

    public (long Stations, long Quotes, DateTimeOffset? Latest) Statistics()
    {
        // Readers must not replace a validated anchor while our transaction or
        // checkpoint is between its before/after file stamps. All shared cache
        // publication uses the same gate as the writer and sealing pipeline.
        gate.Wait();try{return StatisticsCore();}finally{gate.Release();}
    }

    private (long Stations, long Quotes, DateTimeOffset? Latest) StatisticsCore()
    {
        if(CurrentStatisticsCache() is{} cached)return(cached.Health.Stations,cached.Health.Quotes,cached.Latest is{} time?DateTimeOffset.FromUnixTimeMilliseconds(time):null);
        var path=DatabasePath;var captured=MarketVerifiedGeneration.TryCapture(path,out var before);
        using var c = OpenReadOnly(); using var cmd = c.CreateCommand();
        cmd.CommandText = "SELECT (SELECT count(*) FROM stations),(SELECT count(*) FROM quotes),(SELECT max(timestamp) FROM quotes),(SELECT count(*) FROM snapshots),sqlite_version()";
        using var r = cmd.ExecuteReader(); r.Read();
        var stations=r.GetInt64(0);var quotes=r.GetInt64(1);long? latest=r.IsDBNull(2)?null:r.GetInt64(2);
        if(captured&&MarketVerifiedGeneration.Matches(path,before))Volatile.Write(ref statisticsCache,new(path,new(stations,quotes,r.GetInt64(3),r.GetString(4)),latest,before));
        else Volatile.Write(ref statisticsCache,null);
        return(stations,quotes,latest is{} value?DateTimeOffset.FromUnixTimeMilliseconds(value):null);
    }

    private static string EscapeLike(string s) => s.Replace("%", "").Replace("_", "");
}