100 lines
3.8 KiB
C#
100 lines
3.8 KiB
C#
using System.Collections.Concurrent;
|
|
using System.Text.RegularExpressions;
|
|
using Npgsql;
|
|
|
|
namespace FinlyticCore.Util;
|
|
|
|
/// <summary>
|
|
/// Resolves the crypto subtitle/ticker (e.g. "BTC", "ETH", "SOL") for Trade Republic internal ISINs starting with 'X'.
|
|
/// </summary>
|
|
public static class CryptoSubtitleResolver
|
|
{
|
|
private static readonly ConcurrentDictionary<string, (string Subtitle, string? Name)> _cache = new();
|
|
|
|
/// <summary>
|
|
/// Checks if an ISIN is a Trade Republic internal crypto ISIN (starts with 'X') and resolves its Subtitle from DB or heuristic.
|
|
/// </summary>
|
|
public static async Task<(string? Subtitle, string? Name)> ResolveCryptoInfoAsync(
|
|
string isin,
|
|
string? defaultConnectionString = null,
|
|
CancellationToken cancellationToken = default)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(isin)) return (null, null);
|
|
|
|
var cleanIsin = isin.Trim().ToUpperInvariant();
|
|
if (!cleanIsin.StartsWith("X", StringComparison.OrdinalIgnoreCase))
|
|
{
|
|
return (null, null);
|
|
}
|
|
|
|
if (_cache.TryGetValue(cleanIsin, out var cached))
|
|
{
|
|
return (cached.Subtitle, cached.Name);
|
|
}
|
|
|
|
// 1. Try querying PostgreSQL database (finlytic_assets)
|
|
if (!string.IsNullOrWhiteSpace(defaultConnectionString))
|
|
{
|
|
try
|
|
{
|
|
var assetsConnStr = Regex.Replace(defaultConnectionString, @"Database=[^;]+", "Database=finlytic_assets", RegexOptions.IgnoreCase);
|
|
await using var conn = new NpgsqlConnection(assetsConnStr);
|
|
await conn.OpenAsync(cancellationToken);
|
|
|
|
await using var cmd = new NpgsqlCommand(
|
|
"SELECT \"Subtitle\", \"SearchSubtitle\", \"Name\" FROM \"TradeRepublicAssets\" " +
|
|
"WHERE \"Isin\" = @isin AND (\"AssetType\" = 'Crypto' OR \"InstrumentCategory\" = 'crypto' OR \"Subtitle\" IS NOT NULL) " +
|
|
"LIMIT 1",
|
|
conn);
|
|
cmd.Parameters.AddWithValue("isin", cleanIsin);
|
|
|
|
await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
|
|
if (await reader.ReadAsync(cancellationToken))
|
|
{
|
|
string? sub = reader.IsDBNull(0) ? null : reader.GetString(0);
|
|
if (string.IsNullOrWhiteSpace(sub) && !reader.IsDBNull(1))
|
|
{
|
|
sub = reader.GetString(1);
|
|
}
|
|
|
|
string? name = reader.IsDBNull(2) ? null : reader.GetString(2);
|
|
|
|
if (!string.IsNullOrWhiteSpace(sub))
|
|
{
|
|
var cleanSub = sub.Trim().ToUpperInvariant();
|
|
_cache[cleanIsin] = (cleanSub, name);
|
|
return (cleanSub, name);
|
|
}
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
// Fall through to heuristic if DB unreachable or different server
|
|
}
|
|
}
|
|
|
|
// 2. Heuristic fallback for Trade Republic internal ISIN patterns (e.g. XF000BTC0017 -> BTC)
|
|
var match = Regex.Match(cleanIsin, @"^X[A-Z0-9]*?000([A-Z0-9]{3,6})\d*$");
|
|
if (match.Success)
|
|
{
|
|
var extracted = match.Groups[1].Value;
|
|
_cache[cleanIsin] = (extracted, null);
|
|
return (extracted, null);
|
|
}
|
|
|
|
return (null, null);
|
|
}
|
|
|
|
/// <summary>
|
|
/// Convenience method returning just the crypto subtitle (e.g. "BTC").
|
|
/// </summary>
|
|
public static async Task<string?> ResolveCryptoSubtitleAsync(
|
|
string isin,
|
|
string? defaultConnectionString = null,
|
|
CancellationToken cancellationToken = default)
|
|
{
|
|
var (sub, _) = await ResolveCryptoInfoAsync(isin, defaultConnectionString, cancellationToken);
|
|
return sub;
|
|
}
|
|
}
|