#nullable enable

using System.Data;
using System.Globalization;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
using System.Text;
using Microsoft.Win32.SafeHandles;

namespace MMoneyCoderSharp.Data;

public enum LegacyComparisonImportFailure
{
    SourceUnavailable,
    SourceInvalid,
    IdentityNotFound,
    IdentityAmbiguous,
    DatabaseDataInvalid
}

public sealed class LegacyComparisonImportException : Exception
{
    public LegacyComparisonImportException(
        LegacyComparisonImportFailure failure,
        string message,
        Exception? innerException = null)
        : base(message, innerException)
    {
        Failure = failure;
    }

    public LegacyComparisonImportFailure Failure { get; }
}

public enum LegacyComparisonImportTargetKind
{
    MarketTarget,
    KrxStock,
    NxtStock,
    WorldStock
}

public sealed record LegacyComparisonImportTarget(
    LegacyComparisonImportTargetKind Kind,
    string? MarketTarget = null,
    string? Market = null,
    string? StockName = null,
    string? StockCode = null,
    string? InputName = null,
    string? Symbol = null,
    string? Nation = null);

public sealed record LegacyComparisonImportPair(
    int RowNumber,
    LegacyComparisonImportTarget First,
    LegacyComparisonImportTarget Second);

public sealed record LegacyComparisonImportResult(
    string SourceSha256,
    int SourceRowCount,
    DateTimeOffset RetrievedAt,
    IReadOnlyList<LegacyComparisonImportPair> Pairs);

public sealed record LegacyComparisonSourceSnapshot(
    byte[] Bytes,
    string Sha256);

public interface ILegacyComparisonFileSource
{
    Task<LegacyComparisonSourceSnapshot> ReadAsync(
        CancellationToken cancellationToken = default);
}

public interface ILegacyComparisonPairImportService
{
    Task<LegacyComparisonImportResult> ImportAsync(
        CancellationToken cancellationToken = default);
}

/// <summary>
/// Reads only the original application's fixed comparison-pair file. The Web
/// request never supplies a path. The file is opened read-only, bounded, and
/// rejected when the fixed path traverses a reparse point.
/// </summary>
public sealed class TrustedLegacyComparisonFileSource : ILegacyComparisonFileSource
{
    public const int MaximumFileBytes = 128 * 1024;
    public const string FixedRelativePath =
        @"MBN_STOCK_N\MBN_STOCK_N\bin\Debug\Data\종목비교.dat";

    private readonly string _repositoriesRoot;
    private readonly string _sourcePath;

    public TrustedLegacyComparisonFileSource(string repositoriesRoot)
    {
        if (string.IsNullOrWhiteSpace(repositoriesRoot) ||
            !Path.IsPathFullyQualified(repositoriesRoot))
        {
            throw new ArgumentException(
                "An absolute repositories root is required.",
                nameof(repositoriesRoot));
        }

        _repositoriesRoot = Path.TrimEndingDirectorySeparator(
            Path.GetFullPath(repositoriesRoot));
        _sourcePath = Path.GetFullPath(
            Path.Combine(_repositoriesRoot, FixedRelativePath));

        var prefix = _repositoriesRoot + Path.DirectorySeparatorChar;
        if (!_sourcePath.StartsWith(prefix, StringComparison.OrdinalIgnoreCase))
        {
            throw new ArgumentException(
                "The fixed comparison source path escapes the repositories root.",
                nameof(repositoriesRoot));
        }
    }

    public static TrustedLegacyComparisonFileSource CreateDefault()
    {
        var profile = Environment.GetFolderPath(
            Environment.SpecialFolder.UserProfile,
            Environment.SpecialFolderOption.DoNotVerify);
        if (string.IsNullOrWhiteSpace(profile))
        {
            throw new LegacyComparisonImportException(
                LegacyComparisonImportFailure.SourceUnavailable,
                "The user profile path is unavailable.");
        }

        return new TrustedLegacyComparisonFileSource(
            Path.Combine(profile, "source", "repos"));
    }

    public string SourcePath => _sourcePath;

    public async Task<LegacyComparisonSourceSnapshot> ReadAsync(
        CancellationToken cancellationToken = default)
    {
        cancellationToken.ThrowIfCancellationRequested();

        try
        {
            var first = await ReadCandidateAsync(cancellationToken).ConfigureAwait(false);
            // MainForm.WriteListFile closes and reopens the file for every row.
            // Do not block that writer; instead require two identical, quiescent
            // snapshots before parsing so a valid-looking prefix is never copied.
            await Task.Delay(TimeSpan.FromMilliseconds(150), cancellationToken)
                .ConfigureAwait(false);
            var second = await ReadCandidateAsync(cancellationToken).ConfigureAwait(false);
            if (first.LastWriteTimeUtc != second.LastWriteTimeUtc ||
                !first.Bytes.AsSpan().SequenceEqual(second.Bytes))
            {
                throw SourceUnavailable();
            }

            return new LegacyComparisonSourceSnapshot(
                second.Bytes,
                Convert.ToHexString(SHA256.HashData(second.Bytes)));
        }
        catch (OperationCanceledException)
        {
            throw;
        }
        catch (LegacyComparisonImportException)
        {
            throw;
        }
        catch (Exception exception) when (
            exception is IOException or UnauthorizedAccessException or
                System.Security.SecurityException or NotSupportedException)
        {
            throw SourceUnavailable(exception);
        }
    }

    private async Task<SourceCandidate> ReadCandidateAsync(
        CancellationToken cancellationToken)
    {
        ValidateFixedPath();
        var before = new FileInfo(_sourcePath);
        if (!before.Exists || before.Length is <= 0 or > MaximumFileBytes)
        {
            throw SourceUnavailable();
        }

        byte[] bytes;
        await using (var stream = new FileStream(
            _sourcePath,
            FileMode.Open,
            FileAccess.Read,
            FileShare.Read,
            bufferSize: 4096,
            FileOptions.Asynchronous | FileOptions.SequentialScan))
        {
            if (stream.Length != before.Length ||
                stream.Length is <= 0 or > MaximumFileBytes)
            {
                throw SourceUnavailable();
            }

            EnsureOpenedFileIsTrusted(stream.SafeFileHandle);

            bytes = new byte[checked((int)stream.Length)];
            await stream.ReadExactlyAsync(bytes, cancellationToken).ConfigureAwait(false);
        }

        cancellationToken.ThrowIfCancellationRequested();
        ValidateFixedPath();
        var after = new FileInfo(_sourcePath);
        if (!after.Exists || after.Length != bytes.Length ||
            after.LastWriteTimeUtc != before.LastWriteTimeUtc)
        {
            throw SourceUnavailable();
        }

        return new SourceCandidate(bytes, after.LastWriteTimeUtc);
    }

    private void ValidateFixedPath()
    {
        var file = new FileInfo(_sourcePath);
        if (file.Exists && (file.Attributes & FileAttributes.ReparsePoint) != 0)
        {
            throw new LegacyComparisonImportException(
                LegacyComparisonImportFailure.SourceUnavailable,
                "The fixed comparison source path contains a reparse point.");
        }

        DirectoryInfo? current = file.Directory;
        while (current is not null)
        {
            if (current.Exists &&
                (current.Attributes & FileAttributes.ReparsePoint) != 0)
            {
                throw new LegacyComparisonImportException(
                    LegacyComparisonImportFailure.SourceUnavailable,
                    "The fixed comparison source path contains a reparse point.");
            }

            var fullName = Path.TrimEndingDirectorySeparator(
                Path.GetFullPath(current.FullName));
            if (string.Equals(
                    fullName,
                    _repositoriesRoot,
                    StringComparison.OrdinalIgnoreCase))
            {
                return;
            }

            current = current.Parent;
        }

        throw SourceUnavailable();
    }

    private static LegacyComparisonImportException SourceUnavailable(
        Exception? innerException = null) =>
        new(
            LegacyComparisonImportFailure.SourceUnavailable,
            "The fixed legacy comparison source is unavailable.",
            innerException);

    private void EnsureOpenedFileIsTrusted(SafeFileHandle handle)
    {
        if (!OperatingSystem.IsWindows() ||
            !GetFileInformationByHandle(handle, out var information) ||
            (information.FileAttributes &
             (FileAttributes.Directory | FileAttributes.ReparsePoint)) != 0 ||
            information.NumberOfLinks != 1)
        {
            throw SourceUnavailable();
        }

        var length = ((long)information.FileSizeHigh << 32) | information.FileSizeLow;
        if (length is <= 0 or > MaximumFileBytes ||
            !string.Equals(
                GetFinalPath(handle),
                Path.GetFullPath(_sourcePath),
                StringComparison.OrdinalIgnoreCase))
        {
            throw SourceUnavailable();
        }
    }

    private static string GetFinalPath(SafeFileHandle handle)
    {
        var capacity = 512;
        while (capacity <= 32_768)
        {
            var buffer = new StringBuilder(capacity);
            var length = GetFinalPathNameByHandleW(
                handle,
                buffer,
                (uint)buffer.Capacity,
                0);
            if (length == 0)
            {
                throw SourceUnavailable();
            }

            if (length < buffer.Capacity)
            {
                var path = buffer.ToString();
                if (path.StartsWith("\\\\?\\UNC\\", StringComparison.OrdinalIgnoreCase))
                {
                    path = "\\\\" + path[8..];
                }
                else if (path.StartsWith("\\\\?\\", StringComparison.Ordinal))
                {
                    path = path[4..];
                }

                return Path.GetFullPath(path);
            }

            capacity = checked((int)length + 1);
        }

        throw SourceUnavailable();
    }

    [DllImport("kernel32.dll", SetLastError = true)]
    [return: MarshalAs(UnmanagedType.Bool)]
    private static extern bool GetFileInformationByHandle(
        SafeFileHandle file,
        out ByHandleFileInformation information);

    [DllImport("kernel32.dll", CharSet = CharSet.Unicode, SetLastError = true)]
    private static extern uint GetFinalPathNameByHandleW(
        SafeFileHandle file,
        StringBuilder path,
        uint pathLength,
        uint flags);

    [StructLayout(LayoutKind.Sequential)]
    private struct ByHandleFileInformation
    {
        public FileAttributes FileAttributes;
        public FileTime CreationTime;
        public FileTime LastAccessTime;
        public FileTime LastWriteTime;
        public uint VolumeSerialNumber;
        public uint FileSizeHigh;
        public uint FileSizeLow;
        public uint NumberOfLinks;
        public uint FileIndexHigh;
        public uint FileIndexLow;
    }

    [StructLayout(LayoutKind.Sequential)]
    private struct FileTime
    {
        public uint Low;
        public uint High;
    }

    private sealed record SourceCandidate(byte[] Bytes, DateTime LastWriteTimeUtc);
}

public sealed record LegacyComparisonFileTarget(string Name, string Market);

public sealed record LegacyComparisonFileRow(
    int RowNumber,
    LegacyComparisonFileTarget First,
    LegacyComparisonFileTarget Second);

/// <summary>
/// Strict decoder for MainForm.WriteListFile's active UC3 shape: CP949,
/// eight FarPoint columns plus the trailing delimiter (exactly nine fields).
/// </summary>
public static class LegacyComparisonFileParser
{
    public const int MaximumRows = 500;
    public const int ExactFieldCount = 9;
    public const int MaximumTargetNameLength = 200;

    private static readonly HashSet<string> Markets = new(StringComparer.Ordinal)
    {
        string.Empty,
        "코스피",
        "코스닥",
        "코스피_NXT",
        "코스닥_NXT"
    };

    public static IReadOnlyList<LegacyComparisonFileRow> Parse(byte[] bytes)
    {
        ArgumentNullException.ThrowIfNull(bytes);
        if (bytes.Length is <= 0 or > TrustedLegacyComparisonFileSource.MaximumFileBytes)
        {
            throw InvalidSource();
        }

        Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
        var cp949 = Encoding.GetEncoding(
            949,
            EncoderFallback.ExceptionFallback,
            DecoderFallback.ExceptionFallback);

        string text;
        try
        {
            text = cp949.GetString(bytes);
            if (!cp949.GetBytes(text).AsSpan().SequenceEqual(bytes))
            {
                throw InvalidSource();
            }
        }
        catch (DecoderFallbackException exception)
        {
            throw InvalidSource(exception);
        }
        catch (EncoderFallbackException exception)
        {
            throw InvalidSource(exception);
        }

        var lines = text.Split('\n');
        if (lines.Length > 0 && lines[^1].Length == 0)
        {
            lines = lines[..^1];
        }

        if (lines.Length is <= 0 or > MaximumRows)
        {
            throw InvalidSource();
        }

        var rows = new List<LegacyComparisonFileRow>(lines.Length);
        for (var index = 0; index < lines.Length; index++)
        {
            var line = lines[index];
            if (line.EndsWith('\r'))
            {
                line = line[..^1];
            }

            if (line.Length == 0 || line.Contains('\r'))
            {
                throw InvalidSource();
            }

            var fields = line.Split('^');
            if (fields.Length != ExactFieldCount ||
                !string.Equals(
                    fields[0],
                    (index + 1).ToString(CultureInfo.InvariantCulture),
                    StringComparison.Ordinal) ||
                fields.Skip(4).Any(static value => value.Length != 0) ||
                !Markets.Contains(fields[2]) ||
                !Markets.Contains(fields[3]))
            {
                throw InvalidSource();
            }

            var names = fields[1].Split(',');
            if (names.Length != 2 ||
                !IsCanonicalTargetName(names[0]) ||
                !IsCanonicalTargetName(names[1]))
            {
                throw InvalidSource();
            }

            ValidateMarketAndName(names[0], fields[2]);
            ValidateMarketAndName(names[1], fields[3]);

            rows.Add(new LegacyComparisonFileRow(
                index + 1,
                new LegacyComparisonFileTarget(names[0], fields[2]),
                new LegacyComparisonFileTarget(names[1], fields[3])));
        }

        return rows.AsReadOnly();
    }

    private static bool IsCanonicalTargetName(string value) =>
        value.Length is > 0 and <= MaximumTargetNameLength &&
        string.Equals(value, value.Trim(), StringComparison.Ordinal) &&
        !value.Contains('^') &&
        !value.Contains(',') &&
        IsSafeText(value);

    private static void ValidateMarketAndName(string name, string market)
    {
        var isNxtName = name.EndsWith("(NXT)", StringComparison.Ordinal);
        var isNxtMarket = market.EndsWith("_NXT", StringComparison.Ordinal);
        if ((isNxtName && !isNxtMarket) || (!isNxtName && isNxtMarket))
        {
            throw InvalidSource();
        }
    }

    private static bool IsSafeText(string value)
    {
        foreach (var character in value)
        {
            var category = CharUnicodeInfo.GetUnicodeCategory(character);
            if (char.IsControl(character) || char.IsSurrogate(character) ||
                character == '\uFFFD' ||
                category is UnicodeCategory.Format or
                    UnicodeCategory.LineSeparator or
                    UnicodeCategory.ParagraphSeparator)
            {
                return false;
            }
        }

        return true;
    }

    private static LegacyComparisonImportException InvalidSource(
        Exception? innerException = null) =>
        new(
            LegacyComparisonImportFailure.SourceInvalid,
            "The legacy comparison source does not match the strict CP949 nine-field format.",
            innerException);
}

public sealed class LegacyComparisonPairImportService : ILegacyComparisonPairImportService
{
    private const string StockNameColumn = "STOCK_NAME";
    private const string StockCodeColumn = "STOCK_CODE";
    private const string WorldInputNameColumn = "F_INPUT_NAME";
    private const string WorldSymbolColumn = "F_SYMB";
    private const string WorldNationColumn = "F_NATC";

    private static readonly IReadOnlyDictionary<string, string> MarketTargets =
        new Dictionary<string, string>(StringComparer.Ordinal)
        {
            ["코스피 지수"] = "Kospi",
            ["코스닥 지수"] = "Kosdaq",
            ["코스피200 지수"] = "Kospi200",
            ["선물 지수"] = "Futures",
            ["KRX100지수"] = "Krx100",
            ["환율-원달러"] = "WonDollar",
            ["환율-원엔"] = "WonYen",
            ["환율-원위엔"] = "WonYuan",
            ["환율-원유로"] = "WonEuro",
            ["해외지수-다우"] = "Dow",
            ["해외지수-나스닥"] = "Nasdaq",
            ["해외지수-S&P"] = "Sp500",
            ["해외지수-독일"] = "GermanyDax",
            ["해외지수-영국"] = "UnitedKingdomFtse",
            ["해외지수-프랑스"] = "FranceCac",
            ["해외지수-일본"] = "Nikkei",
            ["해외지수-홍콩"] = "HangSeng",
            ["해외지수-대만"] = "TaiwanWeighted",
            ["해외지수-싱가포르"] = "SingaporeStraitsTimes",
            ["해외지수-태국"] = "ThailandSet",
            ["해외지수-필리핀"] = "PhilippinesComposite",
            ["해외지수-말레이시아"] = "MalaysiaKlse",
            ["해외지수-인도네시아"] = "IndonesiaComposite",
            ["해외지수-중국"] = "ShanghaiComposite"
        };

    private static readonly IReadOnlyDictionary<string, StockIdentityProfile> StockProfiles =
        new Dictionary<string, StockIdentityProfile>(StringComparer.Ordinal)
        {
            ["코스피"] = new(
                DataSourceKind.Oracle,
                "LEGACY_COMPARISON_IMPORT_KOSPI",
                "kospi",
                LegacyComparisonImportTargetKind.KrxStock,
                """
                SELECT STOCK_NAME, STOCK_CODE
                FROM (
                    SELECT DISTINCT F_STOCK_WANNAME AS STOCK_NAME, F_STOCK_CODE AS STOCK_CODE
                    FROM T_STOCK
                    WHERE F_MKT_HALT = 'N'
                      AND F_STOCK_WANNAME = :stock_name
                    ORDER BY F_STOCK_CODE
                )
                WHERE ROWNUM <= 2
                """),
            ["코스닥"] = new(
                DataSourceKind.Oracle,
                "LEGACY_COMPARISON_IMPORT_KOSDAQ",
                "kosdaq",
                LegacyComparisonImportTargetKind.KrxStock,
                """
                SELECT STOCK_NAME, STOCK_CODE
                FROM (
                    SELECT DISTINCT F_STOCK_WANNAME AS STOCK_NAME, F_STOCK_CODE AS STOCK_CODE
                    FROM T_KOSDAQ_STOCK
                    WHERE F_MKT_HALT = 'N'
                      AND F_STOCK_WANNAME = :stock_name
                    ORDER BY F_STOCK_CODE
                )
                WHERE ROWNUM <= 2
                """),
            ["코스피_NXT"] = new(
                DataSourceKind.MariaDb,
                "LEGACY_COMPARISON_IMPORT_NXT_KOSPI",
                "kospi",
                LegacyComparisonImportTargetKind.NxtStock,
                """
                SELECT DISTINCT a.F_STOCK_NAME AS STOCK_NAME, a.F_STOCK_CODE AS STOCK_CODE
                FROM N_STOCK a
                INNER JOIN N_ONLINE b ON b.F_STOCK_CODE = a.F_STOCK_CODE
                WHERE a.F_STOP_GUBUN = 'N'
                  AND a.F_STOCK_NAME = @stock_name
                ORDER BY a.F_STOCK_CODE
                LIMIT 2
                """),
            ["코스닥_NXT"] = new(
                DataSourceKind.MariaDb,
                "LEGACY_COMPARISON_IMPORT_NXT_KOSDAQ",
                "kosdaq",
                LegacyComparisonImportTargetKind.NxtStock,
                """
                SELECT DISTINCT a.F_STOCK_NAME AS STOCK_NAME, a.F_STOCK_CODE AS STOCK_CODE
                FROM N_KOSDAQ_STOCK a
                INNER JOIN N_KOSDAQ_ONLINE b ON b.F_STOCK_CODE = a.F_STOCK_CODE
                WHERE a.F_STOP_GUBUN = 'N'
                  AND a.F_STOCK_NAME = @stock_name
                ORDER BY a.F_STOCK_CODE
                LIMIT 2
                """)
        };

    private const string WorldIdentitySql = """
        SELECT F_INPUT_NAME, F_SYMB, F_NATC
        FROM (
            SELECT F_INPUT_NAME, F_SYMB, F_NATC
            FROM T_WORLD_IX_EQ_MASTER
            WHERE F_FDTC = '1'
              AND F_NATC IN ('US', 'TW')
              AND F_INPUT_NAME = :input_name
            ORDER BY F_SYMB, F_NATC
        )
        WHERE ROWNUM <= 2
        """;

    private readonly IDataQueryExecutor _executor;
    private readonly ILegacyComparisonFileSource _source;

    public LegacyComparisonPairImportService(
        IDataQueryExecutor executor,
        ILegacyComparisonFileSource source)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
        _source = source ?? throw new ArgumentNullException(nameof(source));
    }

    public async Task<LegacyComparisonImportResult> ImportAsync(
        CancellationToken cancellationToken = default)
    {
        cancellationToken.ThrowIfCancellationRequested();
        var snapshot = await _source.ReadAsync(cancellationToken).ConfigureAwait(false);
        if (snapshot is null || snapshot.Bytes is null ||
            snapshot.Bytes.Length is <= 0 or > TrustedLegacyComparisonFileSource.MaximumFileBytes ||
            string.IsNullOrEmpty(snapshot.Sha256) ||
            snapshot.Sha256.Length != 64 ||
            !snapshot.Sha256.All(static value =>
                value is >= '0' and <= '9' or >= 'A' and <= 'F') ||
            !CryptographicOperations.FixedTimeEquals(
                Convert.FromHexString(snapshot.Sha256),
                SHA256.HashData(snapshot.Bytes)))
        {
            throw new LegacyComparisonImportException(
                LegacyComparisonImportFailure.SourceInvalid,
                "The legacy comparison source digest is invalid.");
        }

        var rows = LegacyComparisonFileParser.Parse(snapshot.Bytes);
        cancellationToken.ThrowIfCancellationRequested();

        var cache = new Dictionary<string, LegacyComparisonImportTarget>(StringComparer.Ordinal);
        var pairs = new List<LegacyComparisonImportPair>(rows.Count);
        foreach (var row in rows)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var first = await ResolveAsync(row.First, cache, cancellationToken)
                .ConfigureAwait(false);
            var second = await ResolveAsync(row.Second, cache, cancellationToken)
                .ConfigureAwait(false);
            pairs.Add(new LegacyComparisonImportPair(row.RowNumber, first, second));
        }

        return new LegacyComparisonImportResult(
            snapshot.Sha256,
            rows.Count,
            DateTimeOffset.Now,
            pairs.AsReadOnly());
    }

    private async Task<LegacyComparisonImportTarget> ResolveAsync(
        LegacyComparisonFileTarget raw,
        IDictionary<string, LegacyComparisonImportTarget> cache,
        CancellationToken cancellationToken)
    {
        var key = raw.Market + "\u001f" + raw.Name;
        if (cache.TryGetValue(key, out var existing))
        {
            return existing;
        }

        LegacyComparisonImportTarget target;
        if (raw.Market.Length == 0)
        {
            var worldStock = await TryResolveWorldStockAsync(raw.Name, cancellationToken)
                .ConfigureAwait(false);
            if (MarketTargets.TryGetValue(raw.Name, out var marketTarget))
            {
                // The legacy file persists no source-kind column. A world stock
                // whose input name equals one of the fixed selector labels makes
                // that row inherently ambiguous, so never guess the fixed target.
                if (worldStock is not null)
                {
                    throw IdentityAmbiguous();
                }

                target = new LegacyComparisonImportTarget(
                    LegacyComparisonImportTargetKind.MarketTarget,
                    MarketTarget: marketTarget);
            }
            else
            {
                target = worldStock ?? throw IdentityNotFound();
            }
        }
        else if (StockProfiles.TryGetValue(raw.Market, out var profile))
        {
            target = await ResolveDomesticStockAsync(raw.Name, profile, cancellationToken)
                .ConfigureAwait(false);
        }
        else
        {
            throw new LegacyComparisonImportException(
                LegacyComparisonImportFailure.SourceInvalid,
                "The legacy comparison source contains an unsupported market label.");
        }

        cache.Add(key, target);
        return target;
    }

    private async Task<LegacyComparisonImportTarget> ResolveDomesticStockAsync(
        string sourceName,
        StockIdentityProfile profile,
        CancellationToken cancellationToken)
    {
        var lookupName = profile.Kind == LegacyComparisonImportTargetKind.NxtStock
            ? sourceName[..^"(NXT)".Length]
            : sourceName;
        var marker = profile.Source == DataSourceKind.Oracle ? ':' : '@';
        var spec = new DataQuerySpec(
            profile.Sql,
            [new DataQueryParameter("stock_name", lookupName, DbType.String)]);
        spec.ValidateFor(profile.Source);
        if (!profile.Sql.Contains(marker + "stock_name", StringComparison.Ordinal))
        {
            throw new InvalidOperationException("The stock identity query marker is invalid.");
        }

        var table = await _executor.ExecuteAsync(
                profile.Source,
                profile.QueryName,
                spec,
                cancellationToken)
            .ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        ValidateStockTable(table, lookupName);
        if (table.Rows.Count == 0)
        {
            throw IdentityNotFound();
        }

        if (table.Rows.Count != 1)
        {
            throw IdentityAmbiguous();
        }

        var stockName = (string)table.Rows[0][0];
        var stockCode = (string)table.Rows[0][1];
        return new LegacyComparisonImportTarget(
            profile.Kind,
            Market: profile.Market,
            StockName: stockName,
            StockCode: stockCode);
    }

    private async Task<LegacyComparisonImportTarget?> TryResolveWorldStockAsync(
        string inputName,
        CancellationToken cancellationToken)
    {
        var spec = new DataQuerySpec(
            WorldIdentitySql,
            [new DataQueryParameter("input_name", inputName, DbType.String)]);
        spec.ValidateFor(DataSourceKind.Oracle);
        var table = await _executor.ExecuteAsync(
                DataSourceKind.Oracle,
                "LEGACY_COMPARISON_IMPORT_WORLD_STOCK",
                spec,
                cancellationToken)
            .ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        ValidateWorldTable(table, inputName);
        if (table.Rows.Count == 0)
        {
            return null;
        }

        if (table.Rows.Count != 1)
        {
            throw IdentityAmbiguous();
        }

        return new LegacyComparisonImportTarget(
            LegacyComparisonImportTargetKind.WorldStock,
            InputName: (string)table.Rows[0][0],
            Symbol: (string)table.Rows[0][1],
            Nation: (string)table.Rows[0][2]);
    }

    private static void ValidateStockTable(DataTable? table, string expectedName)
    {
        if (table is null || table.Columns.Count != 2 || table.Rows.Count > 2 ||
            !HasExactStringColumn(table.Columns[0], StockNameColumn) ||
            !HasExactStringColumn(table.Columns[1], StockCodeColumn))
        {
            throw InvalidDatabaseData();
        }

        var codes = new HashSet<string>(StringComparer.Ordinal);
        foreach (DataRow row in table.Rows)
        {
            if (row[0] is not string name || row[1] is not string code ||
                !string.Equals(name, expectedName, StringComparison.Ordinal) ||
                !IsCanonicalDatabaseValue(name, LegacyComparisonFileParser.MaximumTargetNameLength) ||
                !IsCanonicalAsciiIdentity(code, 32) ||
                !codes.Add(code))
            {
                throw InvalidDatabaseData();
            }
        }
    }

    private static void ValidateWorldTable(DataTable? table, string expectedName)
    {
        if (table is null || table.Columns.Count != 3 || table.Rows.Count > 2 ||
            !HasExactStringColumn(table.Columns[0], WorldInputNameColumn) ||
            !HasExactStringColumn(table.Columns[1], WorldSymbolColumn) ||
            !HasExactStringColumn(table.Columns[2], WorldNationColumn))
        {
            throw InvalidDatabaseData();
        }

        var identities = new HashSet<string>(StringComparer.Ordinal);
        foreach (DataRow row in table.Rows)
        {
            if (row[0] is not string inputName || row[1] is not string symbol ||
                row[2] is not string nation ||
                !string.Equals(inputName, expectedName, StringComparison.Ordinal) ||
                !IsCanonicalDatabaseValue(inputName, LegacyComparisonFileParser.MaximumTargetNameLength) ||
                !IsCanonicalWorldSymbol(symbol) ||
                nation is not ("US" or "TW") ||
                !identities.Add(symbol + "\u001f" + nation))
            {
                throw InvalidDatabaseData();
            }
        }
    }

    private static bool HasExactStringColumn(DataColumn column, string name) =>
        string.Equals(column.ColumnName, name, StringComparison.Ordinal) &&
        column.DataType == typeof(string);

    private static bool IsCanonicalDatabaseValue(string value, int maximumLength) =>
        value.Length is > 0 && value.Length <= maximumLength &&
        string.Equals(value, value.Trim(), StringComparison.Ordinal) &&
        IsSafeText(value);

    private static bool IsCanonicalAsciiIdentity(string value, int maximumLength) =>
        value.Length is > 0 && value.Length <= maximumLength &&
        value.All(static character =>
            char.IsAsciiLetterOrDigit(character) || character is '.' or '_' or '-');

    private static bool IsCanonicalWorldSymbol(string value) =>
        value.Length is > 0 and <= 64 &&
        value.All(static character =>
            char.IsAsciiLetterOrDigit(character) || character is '@' or '.' or '_' or ':' or '-');

    private static bool IsSafeText(string value)
    {
        foreach (var character in value)
        {
            var category = CharUnicodeInfo.GetUnicodeCategory(character);
            if (char.IsControl(character) || char.IsSurrogate(character) ||
                character == '\uFFFD' ||
                category is UnicodeCategory.Format or
                    UnicodeCategory.LineSeparator or
                    UnicodeCategory.ParagraphSeparator)
            {
                return false;
            }
        }

        return true;
    }

    private static LegacyComparisonImportException IdentityNotFound() =>
        new(
            LegacyComparisonImportFailure.IdentityNotFound,
            "A legacy comparison identity is not present in the current master data.");

    private static LegacyComparisonImportException IdentityAmbiguous() =>
        new(
            LegacyComparisonImportFailure.IdentityAmbiguous,
            "A legacy comparison identity is ambiguous in the current master data.");

    private static LegacyComparisonImportException InvalidDatabaseData() =>
        new(
            LegacyComparisonImportFailure.DatabaseDataInvalid,
            "A comparison identity query returned an invalid result.");

    private sealed record StockIdentityProfile(
        DataSourceKind Source,
        string QueryName,
        string Market,
        LegacyComparisonImportTargetKind Kind,
        string Sql);
}
