#nullable enable

using System.Collections.ObjectModel;
using System.Data;
using System.Globalization;
using MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

namespace MMoneyCoderSharp.Data;

/// <summary>
/// The four manual financial editors opened by the original Form/GraphE.cs.
/// This closed enum is also the only route by which an INPUT_* table can be
/// selected; callers can never supply a table name.
/// </summary>
public enum ManualFinancialScreenKind
{
    RevenueComposition,
    GrowthMetrics,
    Sales,
    OperatingProfit
}

public sealed record ManualFinancialIdentity(
    ManualFinancialScreenKind Screen,
    string StockName);

public abstract record ManualFinancialRecord(ManualFinancialIdentity Identity);

/// <summary>
/// INPUT_PIE stores one populated slice as label_percentage. PercentageText is
/// retained because s5076 displays the legacy text while Percentage drives its
/// circle angle. An absent slice is stored as an empty column, not "_".
/// </summary>
public sealed record ManualRevenueSlice(
    string Label,
    string PercentageText,
    double Percentage);

public sealed record ManualRevenueCompositionRecord(
    ManualFinancialIdentity Identity,
    string BaseDate,
    IReadOnlyList<ManualRevenueSlice?> Slices)
    : ManualFinancialRecord(Identity);

public sealed record ManualGrowthSeriesValues(
    double? First,
    double? Second,
    double? Third,
    double? Fourth)
{
    public IReadOnlyList<double?> ToArray() => [First, Second, Third, Fourth];
}

public sealed record ManualGrowthPeriodLabels(
    string First,
    string Second,
    string Third,
    string Fourth)
{
    public IReadOnlyList<string> ToArray() => [First, Second, Third, Fourth];
}

/// <summary>
/// Series order is the original GraphE order: sales growth, operating-profit
/// growth, net-asset growth and net-income growth. Each GUSUNG_N and BASE_DATE
/// contains exactly four underscore-delimited tokens.
/// </summary>
public sealed record ManualGrowthMetricsRecord(
    ManualFinancialIdentity Identity,
    ManualGrowthSeriesValues SalesGrowth,
    ManualGrowthSeriesValues OperatingProfitGrowth,
    ManualGrowthSeriesValues NetAssetGrowth,
    ManualGrowthSeriesValues NetIncomeGrowth,
    ManualGrowthPeriodLabels Periods)
    : ManualFinancialRecord(Identity);

public sealed record ManualQuarterValue(string Quarter, int Value);

public sealed record ManualSalesRecord(
    ManualFinancialIdentity Identity,
    IReadOnlyList<ManualQuarterValue> Quarters)
    : ManualFinancialRecord(Identity);

public sealed record ManualOperatingProfitRecord(
    ManualFinancialIdentity Identity,
    IReadOnlyList<ManualQuarterValue> Quarters)
    : ManualFinancialRecord(Identity);

/// <summary>
/// ROW_VERSION is calculated by Oracle with STANDARD_HASH over every stored
/// column. It is used for optimistic update/delete checks without exposing ROWID
/// or rebuilding a WHERE clause from operator text.
/// </summary>
public sealed record ManualFinancialSnapshot(
    ManualFinancialRecord Record,
    string RowVersion);

/// <summary>
/// The exact legacy INPUT_* strings, before the strict scene DTO parser is
/// applied. GraphE edited these strings first and the scene parsed them later.
/// Keeping this representation separate lets historical rows remain editable
/// without weakening the strict record used by playout.
/// </summary>
public sealed record ManualFinancialRawRecord(
    ManualFinancialIdentity Identity,
    IReadOnlyList<string> StorageValues);

/// <summary>
/// Process-local Oracle row identity. The native locator intentionally has no
/// public accessor and ToString never reveals it, so only Core can bind it back
/// to a fixed ROWID query. Browser callers receive an unrelated randomized
/// result id from LegacyApplication.
/// </summary>
public sealed class ManualFinancialRawRowHandle
{
    internal ManualFinancialRawRowHandle(
        ManualFinancialScreenKind screen,
        string locator)
    {
        Screen = screen;
        Locator = locator;
    }

    internal ManualFinancialScreenKind Screen { get; }

    internal string Locator { get; }

    public override string ToString() => "Opaque manual-financial row handle";
}

public sealed record ManualFinancialRawSnapshot(
    ManualFinancialRawRecord Record,
    string RowVersion,
    ManualFinancialRawRowHandle Handle,
    ManualFinancialSnapshot? StrictSnapshot)
{
    public bool IsPlayoutReady => StrictSnapshot is not null;
}

public sealed record ManualFinancialSearchResult(
    ManualFinancialScreenKind Screen,
    string Query,
    DateTimeOffset RetrievedAt,
    IReadOnlyList<ManualFinancialSnapshot> Items,
    bool IsTruncated)
{
    /// <summary>
    /// Rows whose legacy compound fields could not be parsed during this list
    /// read. Their safe names may remain visible for parity, while an exact
    /// GetAsync stays strict and never materializes an invalid row for editing
    /// or playout.
    /// </summary>
    public int RejectedItemCount { get; init; }

    /// <summary>
    /// Safe STOCK_NAME identities recovered from rows whose remaining legacy
    /// compound fields are unreadable. The original GraphE listed these names
    /// first and parsed the selected row later; callers may show them, but must
    /// keep exact GetAsync strict before editing or playout.
    /// </summary>
    public IReadOnlyList<string> UnreadableStockNames { get; init; } =
        Array.Empty<string>();

    /// <summary>
    /// Complete raw rows returned by the current Core implementation. Older
    /// fakes/adapters may leave this empty and RawItemsComplete false.
    /// </summary>
    public IReadOnlyList<ManualFinancialRawSnapshot> RawItems { get; init; } =
        Array.Empty<ManualFinancialRawSnapshot>();

    public bool RawItemsComplete { get; init; }
}

public sealed record ManualFinancialScreenContract(
    ManualFinancialScreenKind Screen,
    string TableName,
    string BuilderKey,
    string CutCode,
    string OriginalGraphicType,
    string CanonicalGraphicType,
    IReadOnlyList<string> StorageColumns,
    string CompoundStorageShape);

public sealed record ManualFinancialCutSelection(
    string BuilderKey,
    string CutCode,
    LegacySceneSelection Selection,
    int CurrentPage,
    int TotalPages);

/// <summary>
/// Opaque result of resolving one stock name to one exact master row. GraphE's
/// INPUT_* schema has no stock code, so a duplicated master name cannot safely
/// create a cut and is rejected before this binding can be created.
/// </summary>
public sealed class VerifiedManualFinancialStock
{
    internal VerifiedManualFinancialStock(StockSearchItem item)
    {
        Market = item.Market;
        Source = item.Source;
        Name = item.Name;
        Code = item.Code;
    }

    public StockMarket Market { get; }

    public DataSourceKind Source { get; }

    public string Name { get; }

    public string Code { get; }

    public override string ToString() => "Verified manual-financial stock";
}

/// <summary>
/// Documents and creates the exact one-page GraphE playlist contract. The stock
/// code is used to prove an unambiguous master selection, but DataCode remains
/// empty because the original GraphE playlist row did not populate hidden column
/// six and the four scene loaders query INPUT_* by STOCK_NAME.
/// </summary>
public static class ManualFinancialCutContracts
{
    private static readonly IReadOnlyDictionary<ManualFinancialScreenKind, ManualFinancialScreenContract>
        Contracts = new ReadOnlyDictionary<ManualFinancialScreenKind, ManualFinancialScreenContract>(
            new Dictionary<ManualFinancialScreenKind, ManualFinancialScreenContract>
            {
                [ManualFinancialScreenKind.RevenueComposition] = Contract(
                    ManualFinancialScreenKind.RevenueComposition,
                    "INPUT_PIE",
                    "s5076",
                    "5076",
                    "주요매출 구성",
                    "REVENUE_COMPOSITION",
                    ["STOCK_NAME", "BASE_DATE", "GUSUNG_1", "GUSUNG_2", "GUSUNG_3", "GUSUNG_4", "GUSUNG_5"],
                    "BASE_DATE + five label_percentage columns"),
                [ManualFinancialScreenKind.GrowthMetrics] = Contract(
                    ManualFinancialScreenKind.GrowthMetrics,
                    "INPUT_GROW",
                    "s5079",
                    "5079",
                    "성장성 지표",
                    "GROWTH_METRICS",
                    ["STOCK_NAME", "GUSUNG_1", "GUSUNG_2", "GUSUNG_3", "GUSUNG_4", "BASE_DATE"],
                    "four value_value_value_value series + four period labels in BASE_DATE"),
                [ManualFinancialScreenKind.Sales] = Contract(
                    ManualFinancialScreenKind.Sales,
                    "INPUT_SELL",
                    "s5080",
                    "5080",
                    "매출액",
                    "SALES",
                    ["STOCK_NAME", "GUSUNG_1", "GUSUNG_2", "GUSUNG_3", "GUSUNG_4", "GUSUNG_5", "GUSUNG_6"],
                    "six quarter_integer columns"),
                [ManualFinancialScreenKind.OperatingProfit] = Contract(
                    ManualFinancialScreenKind.OperatingProfit,
                    "INPUT_PROFIT",
                    "s5081",
                    "5081",
                    "영업이익",
                    "OPERATING_PROFIT",
                    ["STOCK_NAME", "GUSUNG_1", "GUSUNG_2", "GUSUNG_3", "GUSUNG_4", "GUSUNG_5", "GUSUNG_6"],
                    "six quarter_integer columns")
            });

    public static IReadOnlyCollection<ManualFinancialScreenContract> All =>
        Contracts.Values.ToArray();

    public static ManualFinancialScreenContract Get(ManualFinancialScreenKind screen) =>
        Contracts.TryGetValue(screen, out var contract)
            ? contract
            : throw new ArgumentOutOfRangeException(nameof(screen));

    public static VerifiedManualFinancialStock VerifyStock(
        ManualFinancialIdentity identity,
        IReadOnlyList<StockSearchItem> candidates,
        StockMarket selectedMarket,
        string selectedCode)
    {
        var validatedIdentity = ManualFinancialValidation.Identity(identity, nameof(identity));
        ArgumentNullException.ThrowIfNull(candidates);
        selectedCode = ManualFinancialValidation.StockCode(selectedCode, nameof(selectedCode));

        var sameName = new List<StockSearchItem>();
        var exactIdentities = new HashSet<(StockMarket Market, DataSourceKind Source, string Code)>();
        foreach (var candidate in candidates)
        {
            if (candidate is null)
            {
                throw new ArgumentException("A stock candidate collection is invalid.", nameof(candidates));
            }

            var name = ManualFinancialValidation.StockName(candidate.Name, nameof(candidates));
            var code = ManualFinancialValidation.StockCode(candidate.Code, nameof(candidates));
            ValidateProvider(candidate.Market, candidate.Source, nameof(candidates));
            if (!exactIdentities.Add((candidate.Market, candidate.Source, code)))
            {
                throw new ArgumentException("A stock candidate collection contains a duplicate identity.", nameof(candidates));
            }

            if (string.Equals(name, validatedIdentity.StockName, StringComparison.Ordinal))
            {
                sameName.Add(candidate with { Name = name, Code = code });
            }
        }

        // INPUT_* has STOCK_NAME only. Even two legitimate market masters with the
        // same name are ambiguous for later scene playback and must fail closed.
        if (sameName.Count != 1 ||
            sameName[0].Market != selectedMarket ||
            !string.Equals(sameName[0].Code, selectedCode, StringComparison.Ordinal))
        {
            throw new ArgumentException(
                "The manual-financial stock name does not resolve to one exact master identity.",
                nameof(candidates));
        }

        return new VerifiedManualFinancialStock(sameName[0]);
    }

    public static ManualFinancialCutSelection CreateSelection(
        ManualFinancialSnapshot snapshot,
        VerifiedManualFinancialStock stock)
    {
        var validated = ManualFinancialValidation.Snapshot(snapshot, nameof(snapshot));
        return CreateSelection(validated.Record.Identity, stock);
    }

    /// <summary>
    /// Creates the legacy one-page schedule identity without claiming that the
    /// current compound DB strings are scene-ready. PREPARE still performs its
    /// fresh strict INPUT_* read before any engine command is dispatched.
    /// </summary>
    public static ManualFinancialCutSelection CreateSelection(
        ManualFinancialIdentity identity,
        VerifiedManualFinancialStock stock)
    {
        var validated = ManualFinancialValidation.Identity(identity, nameof(identity));
        ArgumentNullException.ThrowIfNull(stock);
        ValidateProvider(stock.Market, stock.Source, nameof(stock));
        _ = ManualFinancialValidation.StockCode(stock.Code, nameof(stock));
        if (!string.Equals(
                validated.StockName,
                stock.Name,
                StringComparison.Ordinal))
        {
            throw new ArgumentException(
                "The verified stock does not match the manual-financial record.",
                nameof(stock));
        }

        var contract = Get(validated.Screen);
        var groupCode = stock.Market switch
        {
            StockMarket.Kospi => "KOSPI",
            StockMarket.Kosdaq => "KOSDAQ",
            StockMarket.NxtKospi => "NXT_KOSPI",
            StockMarket.NxtKosdaq => "NXT_KOSDAQ",
            _ => throw new ArgumentOutOfRangeException(nameof(stock))
        };
        return new ManualFinancialCutSelection(
            contract.BuilderKey,
            contract.CutCode,
            new LegacySceneSelection(
                groupCode,
                validated.StockName,
                contract.CanonicalGraphicType,
                string.Empty,
                string.Empty),
            1,
            1);
    }

    private static ManualFinancialScreenContract Contract(
        ManualFinancialScreenKind screen,
        string tableName,
        string builderKey,
        string cutCode,
        string originalGraphicType,
        string canonicalGraphicType,
        string[] storageColumns,
        string compoundStorageShape) =>
        new(
            screen,
            tableName,
            builderKey,
            cutCode,
            originalGraphicType,
            canonicalGraphicType,
            Array.AsReadOnly(storageColumns),
            compoundStorageShape);

    private static void ValidateProvider(
        StockMarket market,
        DataSourceKind source,
        string parameterName)
    {
        var expected = market switch
        {
            StockMarket.Kospi or StockMarket.Kosdaq => DataSourceKind.Oracle,
            StockMarket.NxtKospi or StockMarket.NxtKosdaq => DataSourceKind.MariaDb,
            _ => throw new ArgumentOutOfRangeException(parameterName)
        };
        if (source != expected)
        {
            throw new ArgumentException("The stock market/provider identity is invalid.", parameterName);
        }
    }
}

public enum ManualFinancialMutationKind
{
    Create,
    Update,
    DeleteOne,
    DeleteAll
}

public enum ManualFinancialDbCommandKind
{
    Insert,
    Update,
    DeleteOne,
    DeleteAll
}

public enum ManualFinancialAffectedRowsRule
{
    ExactlyOne,
    AnyNonNegative
}

public sealed class ManualFinancialDbParameter
{
    internal ManualFinancialDbParameter(string name, object? value, DbType dbType)
    {
        if (!DataQueryParameter.IsValidName(name) || !Enum.IsDefined(dbType))
        {
            throw new ArgumentException("A manual-financial database parameter is invalid.");
        }

        Name = name;
        Value = value;
        DbType = dbType;
    }

    public string Name { get; }

    public object? Value { get; }

    public DbType DbType { get; }

    public override string ToString() => "Manual-financial database parameter";
}

/// <summary>
/// A command from the fixed INPUT_PIE/GROW/SELL/PROFIT mutation set. SQL and
/// table identifiers are never caller supplied; every operator value is bound.
/// </summary>
public sealed class ManualFinancialDbCommand
{
    internal ManualFinancialDbCommand(
        ManualFinancialDbCommandKind kind,
        string sql,
        IEnumerable<ManualFinancialDbParameter> parameters,
        ManualFinancialAffectedRowsRule affectedRowsRule)
    {
        Kind = kind;
        Sql = sql;
        Parameters = Array.AsReadOnly(parameters.ToArray());
        AffectedRowsRule = affectedRowsRule;
    }

    public ManualFinancialDbCommandKind Kind { get; }

    public string Sql { get; }

    public IReadOnlyList<ManualFinancialDbParameter> Parameters { get; }

    public ManualFinancialAffectedRowsRule AffectedRowsRule { get; }

    public override string ToString() => $"Manual-financial {Kind} command";
}

/// <summary>
/// One write operation. An executor must open one Oracle transaction, execute
/// ExclusiveTableLockSql once, execute Commands once in order, validate every
/// affected-row rule before commit, and never retry after timeout/ambiguity.
/// </summary>
public sealed class ManualFinancialDbTransaction
{
    internal ManualFinancialDbTransaction(
        Guid operationId,
        ManualFinancialMutationKind kind,
        ManualFinancialScreenKind screen,
        string tableName,
        string exclusiveTableLockSql,
        IEnumerable<ManualFinancialDbCommand> commands)
    {
        OperationId = operationId;
        Kind = kind;
        Screen = screen;
        TableName = tableName;
        ExclusiveTableLockSql = exclusiveTableLockSql;
        Commands = Array.AsReadOnly(commands.ToArray());
    }

    public Guid OperationId { get; }

    public ManualFinancialMutationKind Kind { get; }

    public ManualFinancialScreenKind Screen { get; }

    public string TableName { get; }

    public string ExclusiveTableLockSql { get; }

    public bool RequiresExclusiveTableLock => true;

    public IReadOnlyList<ManualFinancialDbCommand> Commands { get; }

    public override string ToString() => $"Manual-financial {Kind} transaction";
}

public sealed record ManualFinancialDbTransactionResult(
    Guid OperationId,
    IReadOnlyList<int> AffectedRows,
    DateTimeOffset CommittedAt);

public sealed record ManualFinancialMutationReceipt(
    Guid OperationId,
    ManualFinancialMutationKind Kind,
    ManualFinancialScreenKind Screen,
    string StockName,
    DateTimeOffset CommittedAt,
    int AffectedRows);

/// <summary>
/// Infrastructure seam for state-changing Oracle work. The executor must report
/// known rollback with OutcomeUnknown=false and an indeterminate commit with
/// OutcomeUnknown=true. It must never retry this transaction automatically.
/// </summary>
public interface IManualFinancialMutationExecutor
{
    Task<ManualFinancialDbTransactionResult> ExecuteTransactionAsync(
        DataSourceKind source,
        ManualFinancialDbTransaction transaction,
        CancellationToken cancellationToken = default);
}

public interface IManualFinancialScreenService
{
    bool CanMutate { get; }

    Task<ManualFinancialSearchResult> SearchAsync(
        ManualFinancialScreenKind screen,
        string query = "",
        int maximumResults = LegacyManualFinancialScreenService.DefaultMaximumResults,
        CancellationToken cancellationToken = default);

    Task<ManualFinancialSnapshot> GetAsync(
        ManualFinancialIdentity identity,
        CancellationToken cancellationToken = default);

    Task<ManualFinancialMutationReceipt> CreateAsync(
        ManualFinancialRecord record,
        CancellationToken cancellationToken = default);

    Task<ManualFinancialMutationReceipt> UpdateAsync(
        ManualFinancialSnapshot expected,
        ManualFinancialRecord replacement,
        CancellationToken cancellationToken = default);

    Task<ManualFinancialMutationReceipt> DeleteAsync(
        ManualFinancialSnapshot expected,
        CancellationToken cancellationToken = default);

    Task<ManualFinancialMutationReceipt> DeleteAllAsync(
        ManualFinancialScreenKind screen,
        CancellationToken cancellationToken = default);
}

/// <summary>
/// Compatibility seam for GraphE's row-level raw editor. The opaque handle is
/// accepted only from a Core-produced snapshot; callers cannot provide a table
/// name or native locator string.
/// </summary>
public interface IManualFinancialRawScreenService
{
    Task<ManualFinancialRawSnapshot> GetRawAsync(
        ManualFinancialRawSnapshot listedRow,
        CancellationToken cancellationToken = default);

    Task<ManualFinancialMutationReceipt> UpdateRawAsync(
        ManualFinancialRawSnapshot expected,
        ManualFinancialRawRecord replacement,
        CancellationToken cancellationToken = default);

    Task<ManualFinancialMutationReceipt> DeleteRawAsync(
        ManualFinancialRawSnapshot expected,
        CancellationToken cancellationToken = default);
}

public class ManualFinancialDataException : Exception
{
    public ManualFinancialDataException(string message)
        : base(message)
    {
    }
}

public sealed class ManualFinancialNotFoundException : ManualFinancialDataException
{
    public ManualFinancialNotFoundException(ManualFinancialIdentity identity)
        : base($"A {identity.Screen} record for {identity.StockName} does not exist.")
    {
    }
}

public sealed class ManualFinancialAmbiguousIdentityException : ManualFinancialDataException
{
    public ManualFinancialAmbiguousIdentityException(
        ManualFinancialScreenKind screen,
        string stockName)
        : base($"{screen} contains an ambiguous STOCK_NAME identity for {stockName}.")
    {
    }
}

public class ManualFinancialMutationException : Exception
{
    public ManualFinancialMutationException(
        string message,
        bool outcomeUnknown,
        Exception? innerException = null)
        : base(message, innerException)
    {
        OutcomeUnknown = outcomeUnknown;
    }

    public bool OutcomeUnknown { get; }
}

public enum ManualFinancialMutationRejectionReason
{
    DuplicateIdentity,
    NotFoundOrChanged,
    AmbiguousIdentity
}

/// <summary>
/// A safe-to-correct rejection after the executor has rolled the transaction
/// back. The caller may ask the operator to reload/edit, but must not retry the
/// same command automatically.
/// </summary>
public sealed class ManualFinancialMutationRejectedException : ManualFinancialMutationException
{
    public ManualFinancialMutationRejectedException(
        ManualFinancialMutationRejectionReason reason,
        string message,
        Exception? innerException = null)
        : base(message, outcomeUnknown: false, innerException)
    {
        Reason = reason;
    }

    public ManualFinancialMutationRejectionReason Reason { get; }
}

/// <summary>
/// Typed migration of Form/GraphE.cs. Reads reproduce its list, substring search
/// and exact-row selection over Oracle. Writes preserve the INPUT_* underscore
/// representation, but replace concatenated SQL with fixed bound commands,
/// exclusive-table identity checks and optimistic ROW_VERSION checks.
/// </summary>
public sealed class LegacyManualFinancialScreenService :
    IManualFinancialScreenService,
    IManualFinancialRawScreenService
{
    public const int DefaultMaximumResults = 200;
    // The current legacy INPUT_* tables contain up to 2,349 rows and MainForm
    // displayed the complete result without paging. Keep a finite safety bound,
    // but place it above the verified development data instead of silently
    // truncating the original operator list at 200 or 1,000 rows.
    public const int MaximumResults = 5_000;
    public const int MaximumQueryLength = 64;

    private readonly IDataQueryExecutor _queryExecutor;
    private readonly IManualFinancialMutationExecutor? _mutationExecutor;

    public LegacyManualFinancialScreenService(IDataQueryExecutor queryExecutor)
        : this(queryExecutor, null)
    {
    }

    public LegacyManualFinancialScreenService(
        IDataQueryExecutor queryExecutor,
        IManualFinancialMutationExecutor? mutationExecutor)
    {
        _queryExecutor = queryExecutor ?? throw new ArgumentNullException(nameof(queryExecutor));
        _mutationExecutor = mutationExecutor;
    }

    public bool CanMutate => _mutationExecutor is not null;

    public async Task<ManualFinancialSearchResult> SearchAsync(
        ManualFinancialScreenKind screen,
        string query = "",
        int maximumResults = DefaultMaximumResults,
        CancellationToken cancellationToken = default)
    {
        var profile = ManualFinancialSqlProfiles.Get(screen);
        var normalizedQuery = ManualFinancialValidation.Query(query, nameof(query));
        if (maximumResults is < 1 or > MaximumResults)
        {
            throw new ArgumentOutOfRangeException(
                nameof(maximumResults),
                $"The requested result limit must be between 1 and {MaximumResults}.");
        }

        cancellationToken.ThrowIfCancellationRequested();
        var isInitialSearch =
            HangulSearchMatcher.IsInitialConsonantQuery(normalizedQuery);
        var candidateLimit = isInitialSearch
            ? MaximumResults
            : maximumResults;
        var rowLimit = checked(candidateLimit + 1);
        var spec = new DataQuerySpec(
            profile.SearchSql,
            [
                new DataQueryParameter(
                    "search_empty",
                    normalizedQuery.Length == 0 || isInitialSearch ? 1 : 0,
                    DbType.Int32),
                new DataQueryParameter(
                    "search_term",
                    isInitialSearch
                        ? string.Empty
                        : EscapeLikePattern(normalizedQuery.ToUpperInvariant()),
                    DbType.String),
                new DataQueryParameter("row_limit", rowLimit, DbType.Int32)
            ]);
        spec.ValidateFor(DataSourceKind.Oracle);
        var table = await _queryExecutor.ExecuteAsync(
            DataSourceKind.Oracle,
            profile.SearchQueryName,
            spec,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        if (isInitialSearch)
        {
            // Validate every row in the finite candidate window before filtering.
            // Then parse the matching rows again with the caller's visible limit
            // so Items, RawItems and rejected-row diagnostics stay correlated.
            _ = ParseRows(
                table,
                profile,
                rowLimit,
                candidateLimit,
                tolerateInvalidRows: true,
                out _,
                out _,
                out _);
            var matchingTable = table.Clone();
            foreach (var row in table.Rows
                         .Cast<DataRow>()
                         .Take(candidateLimit))
            {
                try
                {
                    var stockName =
                        ManualFinancialValidation.SearchStockName(row);
                    if (HangulSearchMatcher.IsMatch(stockName, normalizedQuery))
                    {
                        matchingTable.ImportRow(row);
                    }
                }
                catch (ArgumentException)
                {
                    // An unsafe or absent name cannot match a safe initial query.
                }
            }

            var matchingSnapshots = ParseRows(
                matchingTable,
                profile,
                candidateLimit,
                maximumResults,
                tolerateInvalidRows: true,
                out var matchingRejectedItemCount,
                out var matchingUnreadableStockNames,
                out var matchingRawItems);

            return new ManualFinancialSearchResult(
                screen,
                normalizedQuery,
                DateTimeOffset.Now,
                matchingSnapshots,
                table.Rows.Count == rowLimit ||
                    matchingTable.Rows.Count > maximumResults)
            {
                RejectedItemCount = matchingRejectedItemCount,
                UnreadableStockNames = matchingUnreadableStockNames,
                RawItems = matchingRawItems,
                RawItemsComplete = true
            };
        }

        var snapshots = ParseRows(
            table,
            profile,
            rowLimit,
            candidateLimit,
            tolerateInvalidRows: true,
            out var rejectedItemCount,
            out var unreadableStockNames,
            out var rawItems);
        return new ManualFinancialSearchResult(
            screen,
            normalizedQuery,
            DateTimeOffset.Now,
            snapshots.Take(maximumResults).ToArray(),
            // The database may have stopped at rowLimit before an invalid row
            // was isolated. Preserve that boundary so callers never assume the
            // valid subset is complete when more source rows may exist.
            table.Rows.Count > maximumResults)
        {
            RejectedItemCount = rejectedItemCount,
            UnreadableStockNames = unreadableStockNames,
            RawItems = rawItems,
            RawItemsComplete = true
        };
    }

    public async Task<ManualFinancialSnapshot> GetAsync(
        ManualFinancialIdentity identity,
        CancellationToken cancellationToken = default)
    {
        var validatedIdentity = ManualFinancialValidation.Identity(identity, nameof(identity));
        var profile = ManualFinancialSqlProfiles.Get(validatedIdentity.Screen);
        cancellationToken.ThrowIfCancellationRequested();
        var rowLimit = checked(MaximumResults + 1);
        var spec = new DataQuerySpec(
            profile.GetSql,
            [
                new DataQueryParameter(
                    "stock_name",
                    validatedIdentity.StockName,
                    DbType.String),
                new DataQueryParameter("row_limit", rowLimit, DbType.Int32)
            ]);
        spec.ValidateFor(DataSourceKind.Oracle);
        var table = await _queryExecutor.ExecuteAsync(
            DataSourceKind.Oracle,
            profile.GetQueryName,
            spec,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();
        var rows = ParseRows(
            table,
            profile,
            maximumRows: MaximumResults,
            resultLimit: MaximumResults,
            tolerateInvalidRows: false,
            out _,
            out _,
            out _);
        if (rows.Count == 0)
        {
            throw new ManualFinancialNotFoundException(validatedIdentity);
        }

        if (rows.Any(row => row.Record.Identity != validatedIdentity))
        {
            throw InvalidData(profile, "record identity");
        }

        var first = rows[0];
        if (rows.Skip(1).All(row => EquivalentExactSnapshot(first, row)))
        {
            return first;
        }

        throw new ManualFinancialAmbiguousIdentityException(
            validatedIdentity.Screen,
            validatedIdentity.StockName);
    }

    public async Task<ManualFinancialRawSnapshot> GetRawAsync(
        ManualFinancialRawSnapshot listedRow,
        CancellationToken cancellationToken = default)
    {
        var listed = ManualFinancialValidation.RawSnapshot(listedRow, nameof(listedRow));
        var profile = ManualFinancialSqlProfiles.Get(listed.Record.Identity.Screen);
        cancellationToken.ThrowIfCancellationRequested();
        var spec = new DataQuerySpec(
            profile.RawGetSql,
            [
                new DataQueryParameter(
                    "row_handle",
                    listed.Handle.Locator,
                    DbType.String),
                new DataQueryParameter(
                    "stock_name",
                    listed.Record.Identity.StockName,
                    DbType.String)
            ]);
        spec.ValidateFor(DataSourceKind.Oracle);
        var table = await _queryExecutor.ExecuteAsync(
            DataSourceKind.Oracle,
            profile.RawGetQueryName,
            spec,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();
        _ = ParseRows(
            table,
            profile,
            maximumRows: 2,
            resultLimit: 2,
            tolerateInvalidRows: true,
            out _,
            out _,
            out var rows);
        if (rows.Count == 0)
        {
            throw new ManualFinancialNotFoundException(listed.Record.Identity);
        }

        if (rows.Count != 1)
        {
            throw new ManualFinancialAmbiguousIdentityException(
                listed.Record.Identity.Screen,
                listed.Record.Identity.StockName);
        }

        var result = rows[0];
        if (result.Record.Identity != listed.Record.Identity ||
            !string.Equals(
                result.Handle.Locator,
                listed.Handle.Locator,
                StringComparison.Ordinal))
        {
            throw InvalidData(profile, "raw row correlation");
        }

        return result;
    }

    public Task<ManualFinancialMutationReceipt> CreateAsync(
        ManualFinancialRecord record,
        CancellationToken cancellationToken = default)
    {
        var validated = ManualFinancialValidation.Record(record, nameof(record));
        var profile = ManualFinancialSqlProfiles.Get(validated.Identity.Screen);
        var fields = ManualFinancialValidation.StorageValues(validated);
        var parameters = CreateValueParameters(validated.Identity.StockName, fields);
        parameters.Add(StringParameter("unique_stock_name", validated.Identity.StockName));
        return ExecuteMutationAsync(
            profile,
            ManualFinancialMutationKind.Create,
            new ManualFinancialDbCommand(
                ManualFinancialDbCommandKind.Insert,
                profile.InsertSql,
                parameters,
                ManualFinancialAffectedRowsRule.ExactlyOne),
            validated.Identity.StockName,
            cancellationToken);
    }

    public Task<ManualFinancialMutationReceipt> UpdateAsync(
        ManualFinancialSnapshot expected,
        ManualFinancialRecord replacement,
        CancellationToken cancellationToken = default)
    {
        var validatedExpected = ManualFinancialValidation.Snapshot(expected, nameof(expected));
        var validatedReplacement = ManualFinancialValidation.Record(replacement, nameof(replacement));
        if (validatedExpected.Record.Identity != validatedReplacement.Identity)
        {
            throw new ArgumentException(
                "A GraphE update cannot rename a stock or change its screen.",
                nameof(replacement));
        }

        var profile = ManualFinancialSqlProfiles.Get(validatedReplacement.Identity.Screen);
        var fields = ManualFinancialValidation.StorageValues(validatedReplacement);
        var parameters = CreateFieldParameters(fields);
        parameters.Add(StringParameter("identity_stock_name", validatedReplacement.Identity.StockName));
        parameters.Add(StringParameter("expected_row_version", validatedExpected.RowVersion));
        parameters.Add(StringParameter("unique_stock_name", validatedReplacement.Identity.StockName));
        return ExecuteMutationAsync(
            profile,
            ManualFinancialMutationKind.Update,
            new ManualFinancialDbCommand(
                ManualFinancialDbCommandKind.Update,
                profile.UpdateSql,
                parameters,
                ManualFinancialAffectedRowsRule.ExactlyOne),
            validatedReplacement.Identity.StockName,
            cancellationToken);
    }

    public Task<ManualFinancialMutationReceipt> UpdateRawAsync(
        ManualFinancialRawSnapshot expected,
        ManualFinancialRawRecord replacement,
        CancellationToken cancellationToken = default)
    {
        var validatedExpected = ManualFinancialValidation.RawSnapshot(
            expected,
            nameof(expected));
        var validatedReplacement = ManualFinancialValidation.RawRecord(
            replacement,
            nameof(replacement));
        if (validatedExpected.Record.Identity != validatedReplacement.Identity)
        {
            throw new ArgumentException(
                "A raw GraphE update cannot rename a stock or change its screen.",
                nameof(replacement));
        }

        var profile = ManualFinancialSqlProfiles.Get(validatedReplacement.Identity.Screen);
        var parameters = CreateFieldParameters(validatedReplacement.StorageValues);
        parameters.Add(StringParameter("row_handle", validatedExpected.Handle.Locator));
        parameters.Add(StringParameter(
            "identity_stock_name",
            validatedReplacement.Identity.StockName));
        parameters.Add(StringParameter(
            "expected_row_version",
            validatedExpected.RowVersion));
        return ExecuteMutationAsync(
            profile,
            ManualFinancialMutationKind.Update,
            new ManualFinancialDbCommand(
                ManualFinancialDbCommandKind.Update,
                profile.RawUpdateSql,
                parameters,
                ManualFinancialAffectedRowsRule.ExactlyOne),
            validatedReplacement.Identity.StockName,
            cancellationToken);
    }

    public Task<ManualFinancialMutationReceipt> DeleteAsync(
        ManualFinancialSnapshot expected,
        CancellationToken cancellationToken = default)
    {
        var validated = ManualFinancialValidation.Snapshot(expected, nameof(expected));
        var profile = ManualFinancialSqlProfiles.Get(validated.Record.Identity.Screen);
        return ExecuteMutationAsync(
            profile,
            ManualFinancialMutationKind.DeleteOne,
            new ManualFinancialDbCommand(
                ManualFinancialDbCommandKind.DeleteOne,
                profile.DeleteOneSql,
                [
                    StringParameter("identity_stock_name", validated.Record.Identity.StockName),
                    StringParameter("expected_row_version", validated.RowVersion),
                    StringParameter("unique_stock_name", validated.Record.Identity.StockName)
                ],
                ManualFinancialAffectedRowsRule.ExactlyOne),
            validated.Record.Identity.StockName,
            cancellationToken);
    }

    public Task<ManualFinancialMutationReceipt> DeleteRawAsync(
        ManualFinancialRawSnapshot expected,
        CancellationToken cancellationToken = default)
    {
        var validated = ManualFinancialValidation.RawSnapshot(expected, nameof(expected));
        var profile = ManualFinancialSqlProfiles.Get(validated.Record.Identity.Screen);
        return ExecuteMutationAsync(
            profile,
            ManualFinancialMutationKind.DeleteOne,
            new ManualFinancialDbCommand(
                ManualFinancialDbCommandKind.DeleteOne,
                profile.RawDeleteOneSql,
                [
                    StringParameter("row_handle", validated.Handle.Locator),
                    StringParameter(
                        "identity_stock_name",
                        validated.Record.Identity.StockName),
                    StringParameter("expected_row_version", validated.RowVersion)
                ],
                ManualFinancialAffectedRowsRule.ExactlyOne),
            validated.Record.Identity.StockName,
            cancellationToken);
    }

    public Task<ManualFinancialMutationReceipt> DeleteAllAsync(
        ManualFinancialScreenKind screen,
        CancellationToken cancellationToken = default)
    {
        var profile = ManualFinancialSqlProfiles.Get(screen);
        return ExecuteMutationAsync(
            profile,
            ManualFinancialMutationKind.DeleteAll,
            new ManualFinancialDbCommand(
                ManualFinancialDbCommandKind.DeleteAll,
                profile.DeleteAllSql,
                [],
                ManualFinancialAffectedRowsRule.AnyNonNegative),
            string.Empty,
            cancellationToken);
    }

    private async Task<ManualFinancialMutationReceipt> ExecuteMutationAsync(
        ManualFinancialSqlProfile profile,
        ManualFinancialMutationKind kind,
        ManualFinancialDbCommand command,
        string stockName,
        CancellationToken cancellationToken)
    {
        var executor = _mutationExecutor ?? throw new InvalidOperationException(
            "Manual-financial screens are configured for read-only access.");
        cancellationToken.ThrowIfCancellationRequested();
        var transaction = new ManualFinancialDbTransaction(
            Guid.NewGuid(),
            kind,
            profile.Screen,
            profile.TableName,
            profile.ExclusiveTableLockSql,
            [command]);

        ManualFinancialDbTransactionResult result;
        try
        {
            result = await executor.ExecuteTransactionAsync(
                DataSourceKind.Oracle,
                transaction,
                cancellationToken).ConfigureAwait(false);
        }
        catch (ManualFinancialMutationException)
        {
            throw;
        }
        catch (Exception exception)
        {
            // A cancellation/timeout after Oracle received a mutation is not proof
            // of rollback. Never turn it into an automatic retry opportunity.
            throw new ManualFinancialMutationException(
                $"The {kind} transaction outcome is unknown; do not retry automatically.",
                outcomeUnknown: true,
                exception);
        }

        if (result is null ||
            result.OperationId != transaction.OperationId ||
            result.AffectedRows is null ||
            result.AffectedRows.Count != 1 ||
            !Matches(command.AffectedRowsRule, result.AffectedRows[0]))
        {
            throw new ManualFinancialMutationException(
                $"The {kind} transaction returned an ambiguous commit result; do not retry automatically.",
                outcomeUnknown: true);
        }

        return new ManualFinancialMutationReceipt(
            result.OperationId,
            kind,
            profile.Screen,
            stockName,
            result.CommittedAt,
            result.AffectedRows[0]);
    }

    private static bool Matches(ManualFinancialAffectedRowsRule rule, int affectedRows) =>
        rule switch
        {
            ManualFinancialAffectedRowsRule.ExactlyOne => affectedRows == 1,
            ManualFinancialAffectedRowsRule.AnyNonNegative => affectedRows >= 0,
            _ => false
        };

    private static bool EquivalentExactSnapshot(
        ManualFinancialSnapshot left,
        ManualFinancialSnapshot right) =>
        left.Record.Identity == right.Record.Identity &&
        string.Equals(left.RowVersion, right.RowVersion, StringComparison.Ordinal) &&
        ManualFinancialValidation.StorageValues(left.Record).SequenceEqual(
            ManualFinancialValidation.StorageValues(right.Record),
            StringComparer.Ordinal);

    private static IReadOnlyList<ManualFinancialSnapshot> ParseRows(
        DataTable? table,
        ManualFinancialSqlProfile profile,
        int maximumRows,
        int resultLimit,
        bool tolerateInvalidRows,
        out int rejectedItemCount,
        out IReadOnlyList<string> unreadableStockNames,
        out IReadOnlyList<ManualFinancialRawSnapshot> rawItems)
    {
        rejectedItemCount = 0;
        var unreadableNames = new List<string>();
        var rawRows = new List<ManualFinancialRawSnapshot>();
        unreadableStockNames = Array.Empty<string>();
        rawItems = Array.Empty<ManualFinancialRawSnapshot>();
        if (table is null || table.Rows.Count > maximumRows ||
            resultLimit is < 1 || resultLimit > maximumRows ||
            table.Columns.Count != profile.ResultColumns.Count)
        {
            throw InvalidData(profile, "schema or row bound");
        }

        for (var index = 0; index < profile.ResultColumns.Count; index++)
        {
            var column = table.Columns[index];
            if (!string.Equals(
                    column.ColumnName,
                    profile.ResultColumns[index],
                    StringComparison.Ordinal) ||
                column.DataType != typeof(string))
            {
                throw InvalidData(profile, "schema");
            }
        }

        var visibleRowCount = Math.Min(table.Rows.Count, resultLimit);
        var result = new List<ManualFinancialSnapshot>(visibleRowCount);
        for (var index = 0; index < visibleRowCount; index++)
        {
            var row = table.Rows[index];
            ManualFinancialRawSnapshot raw;
            try
            {
                raw = ManualFinancialValidation.ParseRaw(profile.Screen, row);
            }
            catch (ArgumentException exception)
            {
                if (tolerateInvalidRows)
                {
                    rejectedItemCount++;
                    try
                    {
                        unreadableNames.Add(
                            ManualFinancialValidation.SearchStockName(row));
                    }
                    catch (ArgumentException)
                    {
                        // The count remains visible, but an unsafe or absent name
                        // cannot become an operator-selectable identity.
                    }
                    continue;
                }

                throw new ManualFinancialDataException(
                    $"Manual-financial data from {profile.SearchQueryName} is invalid: {exception.Message}");
            }

            try
            {
                var strict = ManualFinancialValidation.Parse(profile.Screen, row);
                raw = raw with { StrictSnapshot = strict };
                result.Add(strict);
            }
            catch (ArgumentException exception)
            {
                if (!tolerateInvalidRows)
                {
                    throw new ManualFinancialDataException(
                        $"Manual-financial data from {profile.SearchQueryName} is invalid: {exception.Message}");
                }

                rejectedItemCount++;
                unreadableNames.Add(raw.Record.Identity.StockName);
            }

            rawRows.Add(raw);
        }

        unreadableStockNames = Array.AsReadOnly(unreadableNames.ToArray());
        rawItems = Array.AsReadOnly(rawRows.ToArray());
        return result;
    }

    private static List<ManualFinancialDbParameter> CreateValueParameters(
        string stockName,
        IReadOnlyList<string> fields)
    {
        var parameters = new List<ManualFinancialDbParameter>(fields.Count + 2)
        {
            StringParameter("stock_name", stockName)
        };
        parameters.AddRange(CreateFieldParameters(fields));
        return parameters;
    }

    private static List<ManualFinancialDbParameter> CreateFieldParameters(
        IReadOnlyList<string> fields)
    {
        var parameters = new List<ManualFinancialDbParameter>(fields.Count);
        for (var index = 0; index < fields.Count; index++)
        {
            parameters.Add(StringParameter(
                "field_" + (index + 1).ToString(CultureInfo.InvariantCulture),
                fields[index]));
        }

        return parameters;
    }

    private static ManualFinancialDbParameter StringParameter(string name, string value) =>
        new(name, value, DbType.String);

    private static string EscapeLikePattern(string value) => value
        .Replace("!", "!!", StringComparison.Ordinal)
        .Replace("%", "!%", StringComparison.Ordinal)
        .Replace("_", "!_", StringComparison.Ordinal);

    private static ManualFinancialDataException InvalidData(
        ManualFinancialSqlProfile profile,
        string detail) =>
        new($"Manual-financial data from {profile.SearchQueryName} contains an invalid {detail}.");
}

internal static class ManualFinancialValidation
{
    private const int MaximumStockNameLength = 200;
    private const int MaximumStockCodeLength = 32;
    private const int MaximumTextLength = 200;
    private const int MaximumNumberTextLength = 64;
    private const double MaximumAbsoluteFloatingValue = 1_000_000_000_000d;

    internal static ManualFinancialIdentity Identity(
        ManualFinancialIdentity? identity,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(identity, parameterName);
        _ = ManualFinancialCutContracts.Get(identity.Screen);
        return new ManualFinancialIdentity(
            identity.Screen,
            StockName(identity.StockName, parameterName));
    }

    internal static string StockName(string value, string parameterName) =>
        Text(value, MaximumStockNameLength, allowEmpty: false, allowUnderscore: true, parameterName);

    internal static string SearchStockName(DataRow row)
    {
        ArgumentNullException.ThrowIfNull(row);
        return StockName(RequiredString(row, "STOCK_NAME"), nameof(row));
    }

    internal static string StockCode(string value, string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value, parameterName);
        if (value.Length is < 1 or > MaximumStockCodeLength ||
            value.Any(static character => !char.IsAsciiLetterOrDigit(character)))
        {
            throw new ArgumentException("A stock code is invalid.", parameterName);
        }

        return value;
    }

    internal static string Query(string value, string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value, parameterName);
        if (!IsSafe(value))
        {
            throw new ArgumentException("A manual-financial query is invalid.", parameterName);
        }

        var normalized = value.Trim();
        if (normalized.Length > LegacyManualFinancialScreenService.MaximumQueryLength ||
            !IsSafe(normalized))
        {
            throw new ArgumentException("A manual-financial query is invalid.", parameterName);
        }

        return normalized;
    }

    internal static ManualFinancialSnapshot Snapshot(
        ManualFinancialSnapshot? snapshot,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(snapshot, parameterName);
        var record = Record(snapshot.Record, parameterName);
        var rowVersion = RowVersion(snapshot.RowVersion, parameterName);
        return new ManualFinancialSnapshot(record, rowVersion);
    }

    internal static ManualFinancialRawRecord RawRecord(
        ManualFinancialRawRecord? record,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(record, parameterName);
        var identity = Identity(record.Identity, parameterName);
        ArgumentNullException.ThrowIfNull(record.StorageValues, parameterName);
        var expectedCount = ManualFinancialCutContracts.Get(identity.Screen)
            .StorageColumns.Count - 1;
        if (record.StorageValues.Count != expectedCount)
        {
            throw new ArgumentException(
                "A raw manual-financial record has an invalid field count.",
                parameterName);
        }

        var values = new string[expectedCount];
        for (var index = 0; index < values.Length; index++)
        {
            var value = record.StorageValues[index] ?? throw new ArgumentException(
                "A raw manual-financial field cannot be null.",
                parameterName);
            var maximumLength = RawStorageMaximumLength(identity.Screen, index);
            if (value.Length > maximumLength ||
                value.Any(static character =>
                    char.IsSurrogate(character) || character == '\uFFFD'))
            {
                throw new ArgumentException(
                    "A raw manual-financial field is invalid.",
                    parameterName);
            }

            values[index] = value;
        }

        return new ManualFinancialRawRecord(
            identity,
            Array.AsReadOnly(values));
    }

    internal static ManualFinancialRawSnapshot RawSnapshot(
        ManualFinancialRawSnapshot? snapshot,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(snapshot, parameterName);
        var record = RawRecord(snapshot.Record, parameterName);
        var rowVersion = RowVersion(snapshot.RowVersion, parameterName);
        ArgumentNullException.ThrowIfNull(snapshot.Handle, parameterName);
        if (snapshot.Handle.Screen != record.Identity.Screen)
        {
            throw new ArgumentException(
                "A raw manual-financial handle does not match its screen.",
                parameterName);
        }

        _ = RawRowLocator(snapshot.Handle.Locator, parameterName);
        ManualFinancialSnapshot? strict = null;
        if (snapshot.StrictSnapshot is not null)
        {
            strict = Snapshot(snapshot.StrictSnapshot, parameterName);
            if (strict.Record.Identity != record.Identity ||
                !string.Equals(strict.RowVersion, rowVersion, StringComparison.Ordinal))
            {
                throw new ArgumentException(
                    "A raw manual-financial strict projection is not correlated.",
                    parameterName);
            }
        }

        return new ManualFinancialRawSnapshot(record, rowVersion, snapshot.Handle, strict);
    }

    internal static ManualFinancialRecord Record(
        ManualFinancialRecord? record,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(record, parameterName);
        var identity = Identity(record.Identity, parameterName);
        return record switch
        {
            ManualRevenueCompositionRecord revenue
                when identity.Screen == ManualFinancialScreenKind.RevenueComposition =>
                Revenue(identity, revenue, parameterName),
            ManualGrowthMetricsRecord growth
                when identity.Screen == ManualFinancialScreenKind.GrowthMetrics =>
                Growth(identity, growth, parameterName),
            ManualSalesRecord sales
                when identity.Screen == ManualFinancialScreenKind.Sales =>
                new ManualSalesRecord(
                    identity,
                    Quarters(sales.Quarters, parameterName)),
            ManualOperatingProfitRecord profit
                when identity.Screen == ManualFinancialScreenKind.OperatingProfit =>
                new ManualOperatingProfitRecord(
                    identity,
                    Quarters(profit.Quarters, parameterName)),
            _ => throw new ArgumentException(
                "A manual-financial DTO does not match its screen.",
                parameterName)
        };
    }

    internal static IReadOnlyList<string> StorageValues(ManualFinancialRecord record)
    {
        var validated = Record(record, nameof(record));
        return validated switch
        {
            ManualRevenueCompositionRecord revenue =>
            [revenue.BaseDate, .. revenue.Slices.Select(SerializeSlice)],
            ManualGrowthMetricsRecord growth =>
            [
                SerializeGrowth(growth.SalesGrowth),
                SerializeGrowth(growth.OperatingProfitGrowth),
                SerializeGrowth(growth.NetAssetGrowth),
                SerializeGrowth(growth.NetIncomeGrowth),
                string.Join('_', growth.Periods.ToArray())
            ],
            ManualSalesRecord sales => sales.Quarters.Select(SerializeQuarter).ToArray(),
            ManualOperatingProfitRecord profit => profit.Quarters.Select(SerializeQuarter).ToArray(),
            _ => throw new ArgumentOutOfRangeException(nameof(record))
        };
    }

    internal static ManualFinancialSnapshot Parse(
        ManualFinancialScreenKind screen,
        DataRow row)
    {
        var stockName = StockName(RequiredString(row, "STOCK_NAME"), nameof(row));
        var identity = new ManualFinancialIdentity(screen, stockName);
        ManualFinancialRecord record = screen switch
        {
            ManualFinancialScreenKind.RevenueComposition =>
                new ManualRevenueCompositionRecord(
                    identity,
                    Text(
                        OptionalOracleString(row, "BASE_DATE"),
                        MaximumTextLength,
                        allowEmpty: true,
                        allowUnderscore: true,
                        nameof(row)),
                    Enumerable.Range(1, 5)
                        .Select(index => ParseSlice(
                            OptionalOracleString(row, "GUSUNG_" + index)))
                        .ToArray()),
            ManualFinancialScreenKind.GrowthMetrics => ParseGrowth(identity, row),
            ManualFinancialScreenKind.Sales =>
                new ManualSalesRecord(identity, ParseQuarters(row, allowThousands: false)),
            ManualFinancialScreenKind.OperatingProfit =>
                new ManualOperatingProfitRecord(identity, ParseQuarters(row, allowThousands: true)),
            _ => throw new ArgumentOutOfRangeException(nameof(screen))
        };
        return Snapshot(
            new ManualFinancialSnapshot(record, RequiredString(row, "ROW_VERSION")),
            nameof(row));
    }

    internal static ManualFinancialRawSnapshot ParseRaw(
        ManualFinancialScreenKind screen,
        DataRow row)
    {
        ArgumentNullException.ThrowIfNull(row);
        var identity = Identity(
            new ManualFinancialIdentity(screen, RequiredString(row, "STOCK_NAME")),
            nameof(row));
        var columns = ManualFinancialCutContracts.Get(screen).StorageColumns;
        var values = columns.Skip(1)
            .Select(column => OptionalOracleString(row, column))
            .ToArray();
        var record = RawRecord(
            new ManualFinancialRawRecord(identity, values),
            nameof(row));
        var rowVersion = RowVersion(RequiredString(row, "ROW_VERSION"), nameof(row));
        var locator = RawRowLocator(RequiredString(row, "ROW_HANDLE"), nameof(row));
        return new ManualFinancialRawSnapshot(
            record,
            rowVersion,
            new ManualFinancialRawRowHandle(screen, locator),
            StrictSnapshot: null);
    }

    private static ManualRevenueCompositionRecord Revenue(
        ManualFinancialIdentity identity,
        ManualRevenueCompositionRecord record,
        string parameterName)
    {
        if (record.Slices is null || record.Slices.Count != 5)
        {
            throw new ArgumentException("Revenue composition requires exactly five slices.", parameterName);
        }

        var slices = new ManualRevenueSlice?[5];
        for (var index = 0; index < slices.Length; index++)
        {
            var slice = record.Slices[index];
            if (slice is null)
            {
                slices[index] = null;
                continue;
            }

            var label = LegacyStoredText(
                slice.Label,
                MaximumTextLength,
                allowEmpty: false,
                parameterName);
            var percentageText = LegacyStoredText(
                slice.PercentageText,
                MaximumNumberTextLength,
                allowEmpty: false,
                parameterName);
            var parsed = percentageText == "-"
                ? 0d
                : ParseFinite(percentageText, allowThousands: true, parameterName);
            Finite(slice.Percentage, parameterName);
            if (!parsed.Equals(slice.Percentage))
            {
                throw new ArgumentException(
                    "A revenue percentage text/value pair is inconsistent.",
                    parameterName);
            }

            slices[index] = new ManualRevenueSlice(label, percentageText, parsed);
        }

        return new ManualRevenueCompositionRecord(
            identity,
            LegacyStoredText(
                record.BaseDate,
                MaximumTextLength,
                allowEmpty: true,
                parameterName),
            slices);
    }

    private static ManualGrowthMetricsRecord Growth(
        ManualFinancialIdentity identity,
        ManualGrowthMetricsRecord record,
        string parameterName) =>
        new(
            identity,
            GrowthValues(record.SalesGrowth, parameterName),
            GrowthValues(record.OperatingProfitGrowth, parameterName),
            GrowthValues(record.NetAssetGrowth, parameterName),
            GrowthValues(record.NetIncomeGrowth, parameterName),
            Periods(record.Periods, parameterName));

    private static ManualGrowthSeriesValues GrowthValues(
        ManualGrowthSeriesValues? values,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(values, parameterName);
        foreach (var value in values.ToArray())
        {
            if (value.HasValue)
            {
                Finite(value.Value, parameterName);
            }
        }

        return values;
    }

    private static ManualGrowthPeriodLabels Periods(
        ManualGrowthPeriodLabels? periods,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(periods, parameterName);
        var values = periods.ToArray()
            .Select(value => LegacyStoredText(
                value,
                MaximumTextLength,
                allowEmpty: true,
                parameterName))
            .ToArray();
        return new ManualGrowthPeriodLabels(values[0], values[1], values[2], values[3]);
    }

    private static IReadOnlyList<ManualQuarterValue> Quarters(
        IReadOnlyList<ManualQuarterValue>? quarters,
        string parameterName)
    {
        if (quarters is null || quarters.Count != 6 || quarters.Any(static value => value is null))
        {
            throw new ArgumentException("Quarter data requires exactly six values.", parameterName);
        }

        return quarters.Select(value => new ManualQuarterValue(
                LegacyStoredText(
                    value.Quarter,
                    MaximumTextLength,
                    allowEmpty: true,
                    parameterName),
                value.Value))
            .ToArray();
    }

    private static ManualGrowthMetricsRecord ParseGrowth(
        ManualFinancialIdentity identity,
        DataRow row)
    {
        var series = Enumerable.Range(1, 4)
            .Select(index => ParseGrowthValues(RequiredString(row, "GUSUNG_" + index)))
            .ToArray();
        var periods = RequiredString(row, "BASE_DATE").Split('_', StringSplitOptions.None);
        if (periods.Length != 4)
        {
            throw new ArgumentException("Growth period storage is invalid.", nameof(row));
        }

        return new ManualGrowthMetricsRecord(
            identity,
            series[0],
            series[1],
            series[2],
            series[3],
            Periods(
                new ManualGrowthPeriodLabels(periods[0], periods[1], periods[2], periods[3]),
                nameof(row)));
    }

    private static ManualGrowthSeriesValues ParseGrowthValues(string value)
    {
        var tokens = value.Split('_', StringSplitOptions.None);
        if (tokens.Length != 4)
        {
            throw new ArgumentException("Growth-series storage is invalid.", nameof(value));
        }

        var values = tokens.Select(token => token.Length == 0
                ? (double?)null
                : ParseFinite(token, allowThousands: true, nameof(value)))
            .ToArray();
        return new ManualGrowthSeriesValues(values[0], values[1], values[2], values[3]);
    }

    private static IReadOnlyList<ManualQuarterValue> ParseQuarters(
        DataRow row,
        bool allowThousands)
    {
        var values = new ManualQuarterValue[6];
        for (var index = 0; index < values.Length; index++)
        {
            var stored = RequiredString(row, "GUSUNG_" + (index + 1));
            var tokens = stored.Split('_', StringSplitOptions.None);
            if (tokens.Length < 2)
            {
                throw new ArgumentException("Quarter storage is invalid.", nameof(row));
            }

            var number = 0;
            var styles = NumberStyles.Integer |
                (allowThousands ? NumberStyles.AllowThousands : NumberStyles.None);
            if (tokens[1].Length != 0 &&
                !int.TryParse(tokens[1], styles, CultureInfo.InvariantCulture, out number))
            {
                throw new ArgumentException("Quarter value storage is invalid.", nameof(row));
            }

            values[index] = new ManualQuarterValue(
                LegacyStoredText(
                    LegacyManualFinancialStorageCompatibility.NormalizeQuarterLabel(tokens[0]),
                    MaximumTextLength,
                    allowEmpty: true,
                    nameof(row)),
                number);
        }

        return values;
    }

    private static ManualRevenueSlice? ParseSlice(string value)
    {
        if (value.Length == 0)
        {
            return null;
        }

        var tokens = value.Split('_', StringSplitOptions.None);
        if (tokens.Length < 2)
        {
            throw new ArgumentException("Revenue-slice storage is invalid.", nameof(value));
        }

        // GraphE always saved an empty editor slot as "_". Both its editor and
        // s5076 inspected only the first two tokens and skipped the slot whenever
        // the label token was empty. Treat that representation as an absent slice
        // instead of rejecting a row that the original program could load and play.
        if (tokens[0].Length == 0)
        {
            return null;
        }

        var percentageText = tokens[1] is "" or "-" ? tokens[1] : LegacyStoredText(
            tokens[1],
            MaximumNumberTextLength,
            allowEmpty: false,
            nameof(value));
        var number = percentageText is "" or "-"
            ? 0d
            : ParseFinite(percentageText, allowThousands: true, nameof(value));
        return new ManualRevenueSlice(
            LegacyStoredText(
                tokens[0],
                MaximumTextLength,
                allowEmpty: false,
                nameof(value)),
            percentageText.Length == 0 ? "0" : percentageText,
            number);
    }

    private static string SerializeSlice(ManualRevenueSlice? slice) =>
        slice is null ? "_" : slice.Label + "_" + slice.PercentageText;

    private static string SerializeGrowth(ManualGrowthSeriesValues values) =>
        string.Join('_', values.ToArray().Select(value => value.HasValue
            ? value.Value.ToString("G17", CultureInfo.InvariantCulture)
            : string.Empty));

    private static string SerializeQuarter(ManualQuarterValue value) =>
        value.Quarter + "_" + value.Value.ToString(CultureInfo.InvariantCulture);

    private static string RequiredString(DataRow row, string columnName)
    {
        if (!row.Table.Columns.Contains(columnName) || row[columnName] is not string value)
        {
            throw new ArgumentException("A manual-financial database value is invalid.", nameof(row));
        }

        return value;
    }

    private static string OptionalOracleString(DataRow row, string columnName)
    {
        if (!row.Table.Columns.Contains(columnName))
        {
            throw new ArgumentException("A manual-financial database value is invalid.", nameof(row));
        }

        return row[columnName] switch
        {
            string value => value,
            DBNull => string.Empty,
            _ => throw new ArgumentException(
                "A manual-financial database value is invalid.",
                nameof(row))
        };
    }

    private static string RowVersion(string value, string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value, parameterName);
        if (value.Length != 64 ||
            value.Any(static character =>
                !char.IsAsciiDigit(character) && character is not (>= 'A' and <= 'F')))
        {
            throw new ArgumentException("A manual-financial row version is invalid.", parameterName);
        }

        return value;
    }

    private static string RawRowLocator(string value, string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value, parameterName);
        if (value.Length is < 1 or > 128 ||
            value.Any(static character =>
                !char.IsAsciiLetterOrDigit(character) && character is not '+' and not '/'))
        {
            throw new ArgumentException(
                "A raw manual-financial row handle is invalid.",
                parameterName);
        }

        return value;
    }

    private static int RawStorageMaximumLength(
        ManualFinancialScreenKind screen,
        int index) => screen switch
    {
        ManualFinancialScreenKind.RevenueComposition when index == 0 => 30,
        ManualFinancialScreenKind.GrowthMetrics when index == 4 => 40,
        ManualFinancialScreenKind.RevenueComposition or
            ManualFinancialScreenKind.GrowthMetrics or
            ManualFinancialScreenKind.Sales or
            ManualFinancialScreenKind.OperatingProfit => 50,
        _ => throw new ArgumentOutOfRangeException(nameof(screen))
    };

    private static string Text(
        string value,
        int maximumLength,
        bool allowEmpty,
        bool allowUnderscore,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value, parameterName);
        if ((!allowEmpty && value.Length == 0) ||
            value.Length > maximumLength ||
            value != value.Trim() ||
            !IsSafe(value) ||
            value.Contains('^') ||
            (!allowUnderscore && value.Contains('_')))
        {
            throw new ArgumentException("A manual-financial text value is invalid.", parameterName);
        }

        return value;
    }

    private static string LegacyStoredText(
        string value,
        int maximumLength,
        bool allowEmpty,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value, parameterName);
        if ((!allowEmpty && value.Length == 0) ||
            value.Length > maximumLength ||
            !IsSafe(value))
        {
            throw new ArgumentException(
                "A legacy manual-financial text value is invalid.",
                parameterName);
        }

        return value;
    }

    private static double ParseFinite(
        string value,
        bool allowThousands,
        string parameterName)
    {
        var styles = NumberStyles.Float |
            (allowThousands ? NumberStyles.AllowThousands : NumberStyles.None);
        if (!double.TryParse(value, styles, CultureInfo.InvariantCulture, out var parsed))
        {
            throw new ArgumentException("A manual-financial number is invalid.", parameterName);
        }

        Finite(parsed, parameterName);
        return parsed;
    }

    private static void Finite(double value, string parameterName)
    {
        if (!double.IsFinite(value) || Math.Abs(value) > MaximumAbsoluteFloatingValue)
        {
            throw new ArgumentException("A manual-financial number is outside the safe range.", parameterName);
        }
    }

    private static bool IsSafe(string value) =>
        !value.Any(static character =>
            char.IsControl(character) ||
            char.IsSurrogate(character) ||
            character == '\uFFFD' ||
            CharUnicodeInfo.GetUnicodeCategory(character) is
                UnicodeCategory.Format or
                UnicodeCategory.LineSeparator or
                UnicodeCategory.ParagraphSeparator);
}

internal sealed record ManualFinancialSqlProfile(
    ManualFinancialScreenKind Screen,
    string TableName,
    string SearchQueryName,
    string GetQueryName,
    string RawGetQueryName,
    string SearchSql,
    string GetSql,
    string RawGetSql,
    string ExclusiveTableLockSql,
    string InsertSql,
    string UpdateSql,
    string RawUpdateSql,
    string DeleteOneSql,
    string RawDeleteOneSql,
    string DeleteAllSql,
    IReadOnlyList<string> ResultColumns);

internal static class ManualFinancialSqlProfiles
{
    private const string RowVersionExpressionMarker = "__ROW_VERSION_EXPRESSION__";

    private static readonly string[] PieColumns =
        ["STOCK_NAME", "BASE_DATE", "GUSUNG_1", "GUSUNG_2", "GUSUNG_3", "GUSUNG_4", "GUSUNG_5", "ROW_VERSION", "ROW_HANDLE"];
    private static readonly string[] GrowColumns =
        ["STOCK_NAME", "GUSUNG_1", "GUSUNG_2", "GUSUNG_3", "GUSUNG_4", "BASE_DATE", "ROW_VERSION", "ROW_HANDLE"];
    private static readonly string[] SixValueColumns =
        ["STOCK_NAME", "GUSUNG_1", "GUSUNG_2", "GUSUNG_3", "GUSUNG_4", "GUSUNG_5", "GUSUNG_6", "ROW_VERSION", "ROW_HANDLE"];

    private static readonly string PieRowVersionExpression =
        CreateRowVersionExpression(PieColumns[..^2]);
    private static readonly string GrowRowVersionExpression =
        CreateRowVersionExpression(GrowColumns[..^2]);
    private static readonly string SellRowVersionExpression =
        CreateRowVersionExpression(SixValueColumns[..^2]);
    private static readonly string ProfitRowVersionExpression =
        CreateRowVersionExpression(SixValueColumns[..^2]);

    private static readonly IReadOnlyDictionary<ManualFinancialScreenKind, ManualFinancialSqlProfile>
        Profiles = new ReadOnlyDictionary<ManualFinancialScreenKind, ManualFinancialSqlProfile>(
            new Dictionary<ManualFinancialScreenKind, ManualFinancialSqlProfile>
            {
                [ManualFinancialScreenKind.RevenueComposition] = new(
                    ManualFinancialScreenKind.RevenueComposition,
                    "INPUT_PIE",
                    "MANUAL_FINANCIAL_PIE_SEARCH",
                    "MANUAL_FINANCIAL_PIE_GET",
                    "MANUAL_FINANCIAL_PIE_RAW_GET",
                    WithRowVersion(PieSearchSql, PieRowVersionExpression),
                    WithRowVersion(PieGetSql, PieRowVersionExpression),
                    WithRowVersion(PieRawGetSql, PieRowVersionExpression),
                    "LOCK TABLE INPUT_PIE IN EXCLUSIVE MODE",
                    PieInsertSql,
                    WithRowVersion(PieUpdateSql, PieRowVersionExpression),
                    WithRowVersion(PieRawUpdateSql, PieRowVersionExpression),
                    WithRowVersion(PieDeleteOneSql, PieRowVersionExpression),
                    WithRowVersion(PieRawDeleteOneSql, PieRowVersionExpression),
                    "DELETE FROM INPUT_PIE",
                    Array.AsReadOnly(PieColumns)),
                [ManualFinancialScreenKind.GrowthMetrics] = new(
                    ManualFinancialScreenKind.GrowthMetrics,
                    "INPUT_GROW",
                    "MANUAL_FINANCIAL_GROW_SEARCH",
                    "MANUAL_FINANCIAL_GROW_GET",
                    "MANUAL_FINANCIAL_GROW_RAW_GET",
                    WithRowVersion(GrowSearchSql, GrowRowVersionExpression),
                    WithRowVersion(GrowGetSql, GrowRowVersionExpression),
                    WithRowVersion(GrowRawGetSql, GrowRowVersionExpression),
                    "LOCK TABLE INPUT_GROW IN EXCLUSIVE MODE",
                    GrowInsertSql,
                    WithRowVersion(GrowUpdateSql, GrowRowVersionExpression),
                    WithRowVersion(GrowRawUpdateSql, GrowRowVersionExpression),
                    WithRowVersion(GrowDeleteOneSql, GrowRowVersionExpression),
                    WithRowVersion(GrowRawDeleteOneSql, GrowRowVersionExpression),
                    "DELETE FROM INPUT_GROW",
                    Array.AsReadOnly(GrowColumns)),
                [ManualFinancialScreenKind.Sales] = new(
                    ManualFinancialScreenKind.Sales,
                    "INPUT_SELL",
                    "MANUAL_FINANCIAL_SELL_SEARCH",
                    "MANUAL_FINANCIAL_SELL_GET",
                    "MANUAL_FINANCIAL_SELL_RAW_GET",
                    WithRowVersion(SellSearchSql, SellRowVersionExpression),
                    WithRowVersion(SellGetSql, SellRowVersionExpression),
                    WithRowVersion(SellRawGetSql, SellRowVersionExpression),
                    "LOCK TABLE INPUT_SELL IN EXCLUSIVE MODE",
                    SellInsertSql,
                    WithRowVersion(SellUpdateSql, SellRowVersionExpression),
                    WithRowVersion(SellRawUpdateSql, SellRowVersionExpression),
                    WithRowVersion(SellDeleteOneSql, SellRowVersionExpression),
                    WithRowVersion(SellRawDeleteOneSql, SellRowVersionExpression),
                    "DELETE FROM INPUT_SELL",
                    Array.AsReadOnly(SixValueColumns)),
                [ManualFinancialScreenKind.OperatingProfit] = new(
                    ManualFinancialScreenKind.OperatingProfit,
                    "INPUT_PROFIT",
                    "MANUAL_FINANCIAL_PROFIT_SEARCH",
                    "MANUAL_FINANCIAL_PROFIT_GET",
                    "MANUAL_FINANCIAL_PROFIT_RAW_GET",
                    WithRowVersion(ProfitSearchSql, ProfitRowVersionExpression),
                    WithRowVersion(ProfitGetSql, ProfitRowVersionExpression),
                    WithRowVersion(ProfitRawGetSql, ProfitRowVersionExpression),
                    "LOCK TABLE INPUT_PROFIT IN EXCLUSIVE MODE",
                    ProfitInsertSql,
                    WithRowVersion(ProfitUpdateSql, ProfitRowVersionExpression),
                    WithRowVersion(ProfitRawUpdateSql, ProfitRowVersionExpression),
                    WithRowVersion(ProfitDeleteOneSql, ProfitRowVersionExpression),
                    WithRowVersion(ProfitRawDeleteOneSql, ProfitRowVersionExpression),
                    "DELETE FROM INPUT_PROFIT",
                    Array.AsReadOnly(SixValueColumns))
            });

    internal static ManualFinancialSqlProfile Get(ManualFinancialScreenKind screen) =>
        Profiles.TryGetValue(screen, out var profile)
            ? profile
            : throw new ArgumentOutOfRangeException(nameof(screen));

    private static string CreateRowVersionExpression(IReadOnlyList<string> columns)
    {
        if (columns.Count == 0 || columns.Any(static column =>
                !DataQueryParameter.IsValidName(column)))
        {
            throw new InvalidOperationException(
                "A manual-financial row-version column set is invalid.");
        }

        // Oracle treats an empty VARCHAR2 as NULL. Each NULL therefore gets a
        // one-character N tag, while each non-null value gets a V tag followed
        // by a fixed ten-digit character length and the exact value. This code
        // is prefix-decodable even when values contain NUL, CHR(31), digits or
        // text that previously acted as a structural delimiter.
        var encodedColumns = columns.Select(static column =>
            $"CASE WHEN {column} IS NULL THEN 'N' " +
            $"ELSE 'V' || LPAD(TO_CHAR(LENGTH({column}), " +
            $"'FM9999999990'), 10, '0') || {column} END");
        return "RAWTOHEX(STANDARD_HASH(" +
            string.Join(" || ", encodedColumns) +
            ", 'SHA256'))";
    }

    private static string WithRowVersion(string sqlTemplate, string expression)
    {
        ArgumentException.ThrowIfNullOrWhiteSpace(sqlTemplate);
        ArgumentException.ThrowIfNullOrWhiteSpace(expression);
        var first = sqlTemplate.IndexOf(
            RowVersionExpressionMarker,
            StringComparison.Ordinal);
        if (first < 0 || sqlTemplate.IndexOf(
                RowVersionExpressionMarker,
                first + RowVersionExpressionMarker.Length,
                StringComparison.Ordinal) >= 0)
        {
            throw new InvalidOperationException(
                "A manual-financial SQL template must contain one row-version marker.");
        }

        return sqlTemplate.Replace(
            RowVersionExpressionMarker,
            expression,
            StringComparison.Ordinal);
    }

    private const string PieSearchSql =
        """
        SELECT STOCK_NAME, BASE_DATE, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, ROW_VERSION, ROW_HANDLE
        FROM (
            SELECT STOCK_NAME, BASE_DATE, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5,
                   __ROW_VERSION_EXPRESSION__ ROW_VERSION,
                   ROWIDTOCHAR(ROWID) ROW_HANDLE
            FROM INPUT_PIE
            WHERE (:search_empty = 1 OR
                   UPPER(STOCK_NAME) LIKE '%' || :search_term || '%' ESCAPE '!')
            ORDER BY UPPER(STOCK_NAME), STOCK_NAME
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string PieGetSql =
        """
        SELECT STOCK_NAME, BASE_DATE, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, ROW_VERSION, ROW_HANDLE
        FROM (
            SELECT STOCK_NAME, BASE_DATE, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5,
                   __ROW_VERSION_EXPRESSION__ ROW_VERSION,
                   ROWIDTOCHAR(ROWID) ROW_HANDLE
            FROM INPUT_PIE
            WHERE STOCK_NAME = :stock_name
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string PieRawGetSql =
        """
        SELECT STOCK_NAME, BASE_DATE, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5,
               __ROW_VERSION_EXPRESSION__ ROW_VERSION,
               ROWIDTOCHAR(ROWID) ROW_HANDLE
        FROM INPUT_PIE
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :stock_name
        """;

    private const string PieInsertSql =
        """
        INSERT INTO INPUT_PIE(
            STOCK_NAME, BASE_DATE, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5)
        SELECT :stock_name, :field_1, :field_2, :field_3, :field_4, :field_5, :field_6
        FROM DUAL
        WHERE (SELECT COUNT(*) FROM INPUT_PIE WHERE STOCK_NAME = :unique_stock_name) = 0
        """;

    private const string PieUpdateSql =
        """
        UPDATE INPUT_PIE
        SET BASE_DATE = :field_1,
            GUSUNG_1 = :field_2,
            GUSUNG_2 = :field_3,
            GUSUNG_3 = :field_4,
            GUSUNG_4 = :field_5,
            GUSUNG_5 = :field_6
        WHERE STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
          AND (SELECT COUNT(*) FROM INPUT_PIE WHERE STOCK_NAME = :unique_stock_name) = 1
        """;

    private const string PieRawUpdateSql =
        """
        UPDATE INPUT_PIE
        SET BASE_DATE = :field_1,
            GUSUNG_1 = :field_2,
            GUSUNG_2 = :field_3,
            GUSUNG_3 = :field_4,
            GUSUNG_4 = :field_5,
            GUSUNG_5 = :field_6
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
        """;

    private const string PieDeleteOneSql =
        """
        DELETE FROM INPUT_PIE
        WHERE STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
          AND (SELECT COUNT(*) FROM INPUT_PIE WHERE STOCK_NAME = :unique_stock_name) = 1
        """;

    private const string PieRawDeleteOneSql =
        """
        DELETE FROM INPUT_PIE
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
        """;

    private const string GrowSearchSql =
        """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, BASE_DATE, ROW_VERSION, ROW_HANDLE
        FROM (
            SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, BASE_DATE,
                   __ROW_VERSION_EXPRESSION__ ROW_VERSION,
                   ROWIDTOCHAR(ROWID) ROW_HANDLE
            FROM INPUT_GROW
            WHERE (:search_empty = 1 OR
                   UPPER(STOCK_NAME) LIKE '%' || :search_term || '%' ESCAPE '!')
            ORDER BY UPPER(STOCK_NAME), STOCK_NAME
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string GrowGetSql =
        """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, BASE_DATE, ROW_VERSION, ROW_HANDLE
        FROM (
            SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, BASE_DATE,
                   __ROW_VERSION_EXPRESSION__ ROW_VERSION,
                   ROWIDTOCHAR(ROWID) ROW_HANDLE
            FROM INPUT_GROW
            WHERE STOCK_NAME = :stock_name
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string GrowRawGetSql =
        """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, BASE_DATE,
               __ROW_VERSION_EXPRESSION__ ROW_VERSION,
               ROWIDTOCHAR(ROWID) ROW_HANDLE
        FROM INPUT_GROW
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :stock_name
        """;

    private const string GrowInsertSql =
        """
        INSERT INTO INPUT_GROW(
            STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, BASE_DATE)
        SELECT :stock_name, :field_1, :field_2, :field_3, :field_4, :field_5
        FROM DUAL
        WHERE (SELECT COUNT(*) FROM INPUT_GROW WHERE STOCK_NAME = :unique_stock_name) = 0
        """;

    private const string GrowUpdateSql =
        """
        UPDATE INPUT_GROW
        SET GUSUNG_1 = :field_1,
            GUSUNG_2 = :field_2,
            GUSUNG_3 = :field_3,
            GUSUNG_4 = :field_4,
            BASE_DATE = :field_5
        WHERE STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
          AND (SELECT COUNT(*) FROM INPUT_GROW WHERE STOCK_NAME = :unique_stock_name) = 1
        """;

    private const string GrowRawUpdateSql =
        """
        UPDATE INPUT_GROW
        SET GUSUNG_1 = :field_1,
            GUSUNG_2 = :field_2,
            GUSUNG_3 = :field_3,
            GUSUNG_4 = :field_4,
            BASE_DATE = :field_5
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
        """;

    private const string GrowDeleteOneSql =
        """
        DELETE FROM INPUT_GROW
        WHERE STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
          AND (SELECT COUNT(*) FROM INPUT_GROW WHERE STOCK_NAME = :unique_stock_name) = 1
        """;

    private const string GrowRawDeleteOneSql =
        """
        DELETE FROM INPUT_GROW
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
        """;

    private const string SellSearchSql =
        """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6, ROW_VERSION, ROW_HANDLE
        FROM (
            SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6,
                   __ROW_VERSION_EXPRESSION__ ROW_VERSION,
                   ROWIDTOCHAR(ROWID) ROW_HANDLE
            FROM INPUT_SELL
            WHERE (:search_empty = 1 OR
                   UPPER(STOCK_NAME) LIKE '%' || :search_term || '%' ESCAPE '!')
            ORDER BY UPPER(STOCK_NAME), STOCK_NAME
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string SellGetSql =
        """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6, ROW_VERSION, ROW_HANDLE
        FROM (
            SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6,
                   __ROW_VERSION_EXPRESSION__ ROW_VERSION,
                   ROWIDTOCHAR(ROWID) ROW_HANDLE
            FROM INPUT_SELL
            WHERE STOCK_NAME = :stock_name
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string SellRawGetSql =
        """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6,
               __ROW_VERSION_EXPRESSION__ ROW_VERSION,
               ROWIDTOCHAR(ROWID) ROW_HANDLE
        FROM INPUT_SELL
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :stock_name
        """;

    private const string SellInsertSql =
        """
        INSERT INTO INPUT_SELL(
            STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6)
        SELECT :stock_name, :field_1, :field_2, :field_3, :field_4, :field_5, :field_6
        FROM DUAL
        WHERE (SELECT COUNT(*) FROM INPUT_SELL WHERE STOCK_NAME = :unique_stock_name) = 0
        """;

    private const string SellUpdateSql =
        """
        UPDATE INPUT_SELL
        SET GUSUNG_1 = :field_1,
            GUSUNG_2 = :field_2,
            GUSUNG_3 = :field_3,
            GUSUNG_4 = :field_4,
            GUSUNG_5 = :field_5,
            GUSUNG_6 = :field_6
        WHERE STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
          AND (SELECT COUNT(*) FROM INPUT_SELL WHERE STOCK_NAME = :unique_stock_name) = 1
        """;

    private const string SellRawUpdateSql =
        """
        UPDATE INPUT_SELL
        SET GUSUNG_1 = :field_1,
            GUSUNG_2 = :field_2,
            GUSUNG_3 = :field_3,
            GUSUNG_4 = :field_4,
            GUSUNG_5 = :field_5,
            GUSUNG_6 = :field_6
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
        """;

    private const string SellDeleteOneSql =
        """
        DELETE FROM INPUT_SELL
        WHERE STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
          AND (SELECT COUNT(*) FROM INPUT_SELL WHERE STOCK_NAME = :unique_stock_name) = 1
        """;

    private const string SellRawDeleteOneSql =
        """
        DELETE FROM INPUT_SELL
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
        """;

    private const string ProfitSearchSql =
        """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6, ROW_VERSION, ROW_HANDLE
        FROM (
            SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6,
                   __ROW_VERSION_EXPRESSION__ ROW_VERSION,
                   ROWIDTOCHAR(ROWID) ROW_HANDLE
            FROM INPUT_PROFIT
            WHERE (:search_empty = 1 OR
                   UPPER(STOCK_NAME) LIKE '%' || :search_term || '%' ESCAPE '!')
            ORDER BY UPPER(STOCK_NAME), STOCK_NAME
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string ProfitGetSql =
        """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6, ROW_VERSION, ROW_HANDLE
        FROM (
            SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6,
                   __ROW_VERSION_EXPRESSION__ ROW_VERSION,
                   ROWIDTOCHAR(ROWID) ROW_HANDLE
            FROM INPUT_PROFIT
            WHERE STOCK_NAME = :stock_name
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string ProfitRawGetSql =
        """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6,
               __ROW_VERSION_EXPRESSION__ ROW_VERSION,
               ROWIDTOCHAR(ROWID) ROW_HANDLE
        FROM INPUT_PROFIT
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :stock_name
        """;

    private const string ProfitInsertSql =
        """
        INSERT INTO INPUT_PROFIT(
            STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6)
        SELECT :stock_name, :field_1, :field_2, :field_3, :field_4, :field_5, :field_6
        FROM DUAL
        WHERE (SELECT COUNT(*) FROM INPUT_PROFIT WHERE STOCK_NAME = :unique_stock_name) = 0
        """;

    private const string ProfitUpdateSql =
        """
        UPDATE INPUT_PROFIT
        SET GUSUNG_1 = :field_1,
            GUSUNG_2 = :field_2,
            GUSUNG_3 = :field_3,
            GUSUNG_4 = :field_4,
            GUSUNG_5 = :field_5,
            GUSUNG_6 = :field_6
        WHERE STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
          AND (SELECT COUNT(*) FROM INPUT_PROFIT WHERE STOCK_NAME = :unique_stock_name) = 1
        """;

    private const string ProfitRawUpdateSql =
        """
        UPDATE INPUT_PROFIT
        SET GUSUNG_1 = :field_1,
            GUSUNG_2 = :field_2,
            GUSUNG_3 = :field_3,
            GUSUNG_4 = :field_4,
            GUSUNG_5 = :field_5,
            GUSUNG_6 = :field_6
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
        """;

    private const string ProfitDeleteOneSql =
        """
        DELETE FROM INPUT_PROFIT
        WHERE STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
          AND (SELECT COUNT(*) FROM INPUT_PROFIT WHERE STOCK_NAME = :unique_stock_name) = 1
        """;

    private const string ProfitRawDeleteOneSql =
        """
        DELETE FROM INPUT_PROFIT
        WHERE ROWID = CHARTOROWID(:row_handle)
          AND STOCK_NAME = :identity_stock_name
          AND __ROW_VERSION_EXPRESSION__ = :expected_row_version
        """;
}
