#nullable enable

using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class LegacyManualFinancialScreenServiceTests
{
    private const string Version = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA";

    public static TheoryData<ManualFinancialScreenKind, string, string, string, string> Screens => new()
    {
        { ManualFinancialScreenKind.RevenueComposition, "INPUT_PIE", "s5076", "5076", "REVENUE_COMPOSITION" },
        { ManualFinancialScreenKind.GrowthMetrics, "INPUT_GROW", "s5079", "5079", "GROWTH_METRICS" },
        { ManualFinancialScreenKind.Sales, "INPUT_SELL", "s5080", "5080", "SALES" },
        { ManualFinancialScreenKind.OperatingProfit, "INPUT_PROFIT", "s5081", "5081", "OPERATING_PROFIT" }
    };

    [Theory]
    [MemberData(nameof(Screens))]
    public void Contracts_pin_the_original_table_builder_cut_and_one_page_selection(
        ManualFinancialScreenKind screen,
        string table,
        string builder,
        string cut,
        string graphic)
    {
        var contract = ManualFinancialCutContracts.Get(screen);

        Assert.Equal(table, contract.TableName);
        Assert.Equal(builder, contract.BuilderKey);
        Assert.Equal(cut, contract.CutCode);
        Assert.Equal(graphic, contract.CanonicalGraphicType);
        Assert.StartsWith("INPUT_", contract.TableName, StringComparison.Ordinal);
        Assert.Contains("_", contract.CompoundStorageShape, StringComparison.Ordinal);
    }

    [Theory]
    [MemberData(nameof(Screens))]
    public async Task Search_uses_one_parameterized_Oracle_profile_and_maps_the_closed_DTO(
        ManualFinancialScreenKind screen,
        string tableName,
        string builder,
        string cut,
        string graphic)
    {
        var executor = new RecordingQueryExecutor(Table(screen, "삼성전자"));
        var service = new LegacyManualFinancialScreenService(executor);

        var result = await service.SearchAsync(screen, "  삼성  ", 25);

        Assert.Equal(screen, result.Screen);
        Assert.Equal("삼성", result.Query);
        Assert.Single(result.Items);
        Assert.Equal(screen, result.Items[0].Record.Identity.Screen);
        Assert.Equal(builder, ManualFinancialCutContracts.Get(screen).BuilderKey);
        Assert.Equal(cut, ManualFinancialCutContracts.Get(screen).CutCode);
        Assert.Equal(graphic, ManualFinancialCutContracts.Get(screen).CanonicalGraphicType);
        Assert.Equal("삼성전자", result.Items[0].Record.Identity.StockName);
        Assert.Equal(Version, result.Items[0].RowVersion);
        Assert.False(result.IsTruncated);
        Assert.Equal(DataSourceKind.Oracle, executor.Source);
        Assert.Equal("MANUAL_FINANCIAL_" + QueryToken(screen) + "_SEARCH", executor.TableName);
        Assert.NotNull(executor.Spec);
        Assert.Contains("FROM " + tableName, executor.Spec!.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("삼성", executor.Spec.Sql, StringComparison.Ordinal);
        Assert.Equal(["search_empty", "search_term", "row_limit"],
            executor.Spec.Parameters.Select(parameter => parameter.Name));
        Assert.Equal(0, executor.Spec.Parameters[0].Value);
        Assert.Equal("삼성", executor.Spec.Parameters[1].Value);
        Assert.Equal(26, executor.Spec.Parameters[2].Value);
    }

    [Fact]
    public async Task Blank_list_and_literal_substring_search_are_bound_and_escaped()
    {
        var blankExecutor = new RecordingQueryExecutor(Table(
            ManualFinancialScreenKind.Sales,
            "A%_!"));
        await new LegacyManualFinancialScreenService(blankExecutor)
            .SearchAsync(ManualFinancialScreenKind.Sales, "   ");
        Assert.Equal(1, blankExecutor.Spec!.Parameters[0].Value);
        Assert.Equal(string.Empty, blankExecutor.Spec.Parameters[1].Value);

        var escapedExecutor = new RecordingQueryExecutor(Table(
            ManualFinancialScreenKind.Sales,
            "A%_!"));
        await new LegacyManualFinancialScreenService(escapedExecutor)
            .SearchAsync(ManualFinancialScreenKind.Sales, "a%_!");
        Assert.Equal("A!%!_!!", escapedExecutor.Spec!.Parameters[1].Value);
        Assert.Contains("ESCAPE '!'", escapedExecutor.Spec.Sql, StringComparison.Ordinal);
    }

    [Fact]
    public async Task Initial_consonant_search_filters_the_bounded_full_candidate_list()
    {
        var table = EmptyTable(ManualFinancialScreenKind.Sales);
        AddQuarterRow(table, "가나다");
        AddQuarterRow(table, "삼성전자");
        AddQuarterRow(table, "현대차");
        var executor = new RecordingQueryExecutor(table);
        var service = new LegacyManualFinancialScreenService(executor);

        var result = await service.SearchAsync(
            ManualFinancialScreenKind.Sales,
            " ㅅㅅㅈ ",
            maximumResults: 1);

        Assert.Equal("ㅅㅅㅈ", result.Query);
        Assert.Equal(
            "삼성전자",
            Assert.Single(result.Items).Record.Identity.StockName);
        Assert.Equal(
            "삼성전자",
            Assert.Single(result.RawItems).Record.Identity.StockName);
        Assert.False(result.IsTruncated);
        Assert.Equal(1, executor.Spec!.Parameters[0].Value);
        Assert.Equal(string.Empty, executor.Spec.Parameters[1].Value);
        Assert.Equal(
            LegacyManualFinancialScreenService.MaximumResults + 1,
            executor.Spec.Parameters[2].Value);
    }

    [Theory]
    [MemberData(nameof(Screens))]
    public void Row_version_SQL_is_one_shared_null_tagged_length_prefixed_contract(
        ManualFinancialScreenKind screen,
        string table,
        string builder,
        string cut,
        string graphic)
    {
        _ = (table, builder, cut, graphic);
        var profile = ManualFinancialSqlProfiles.Get(screen);
        var sqlShapes = new[]
        {
            profile.SearchSql,
            profile.GetSql,
            profile.RawGetSql,
            profile.UpdateSql,
            profile.RawUpdateSql,
            profile.DeleteOneSql,
            profile.RawDeleteOneSql
        };
        var expressions = sqlShapes.Select(ExtractRowVersionExpression).ToArray();

        Assert.Single(expressions.Distinct(StringComparer.Ordinal));
        var expression = expressions[0];
        Assert.DoesNotContain("NVL(", expression, StringComparison.Ordinal);
        Assert.DoesNotContain("CHR(0)", expression, StringComparison.Ordinal);
        Assert.DoesNotContain("CHR(31)", expression, StringComparison.Ordinal);
        Assert.Equal(
            ManualFinancialCutContracts.Get(screen).StorageColumns.Count,
            CountOccurrences(expression, "CASE WHEN "));
        foreach (var column in ManualFinancialCutContracts.Get(screen).StorageColumns)
        {
            Assert.Contains(
                $"CASE WHEN {column} IS NULL THEN 'N' ELSE 'V' || " +
                $"LPAD(TO_CHAR(LENGTH({column}), 'FM9999999990'), 10, '0') || " +
                $"{column} END",
                expression,
                StringComparison.Ordinal);
        }
        Assert.All(sqlShapes, sql =>
        {
            Assert.DoesNotContain("__ROW_VERSION_EXPRESSION__", sql, StringComparison.Ordinal);
            Assert.Equal(1, CountOccurrences(sql, "RAWTOHEX(STANDARD_HASH("));
        });
    }

    [Fact]
    public void Row_version_encoding_breaks_old_NUL_and_CHR31_structural_collisions()
    {
        string?[] nullValue = [null, "X"];
        string?[] literalNul = ["\0", "X"];
        Assert.Equal(OldDelimitedEncoding(nullValue), OldDelimitedEncoding(literalNul));
        AssertDistinctConcurrencyTokens(nullValue, literalNul);

        string?[] delimiterInFirst = ["A\u001fB", "C"];
        string?[] delimiterInSecond = ["A", "B\u001fC"];
        Assert.Equal(
            OldDelimitedEncoding(delimiterInFirst),
            OldDelimitedEncoding(delimiterInSecond));
        AssertDistinctConcurrencyTokens(delimiterInFirst, delimiterInSecond);

        // Oracle folds an empty VARCHAR2 to NULL, so those two representations
        // intentionally remain equivalent while literal NUL remains distinct.
        Assert.Equal(
            NewLengthPrefixedEncoding([null]),
            NewLengthPrefixedEncoding([string.Empty]));
        Assert.NotEqual(
            NewLengthPrefixedEncoding([null]),
            NewLengthPrefixedEncoding(["\0"]));
    }

    [Fact]
    public async Task Revenue_storage_maps_five_label_percentage_fields_and_empty_slice()
    {
        var table = EmptyTable(ManualFinancialScreenKind.RevenueComposition);
        table.Rows.Add("A", "2026-06", "반도체_50.0", "서비스_-", "", "기타_25", "해외_25", Version, RowHandle(table));

        var result = await new LegacyManualFinancialScreenService(new RecordingQueryExecutor(table))
            .SearchAsync(ManualFinancialScreenKind.RevenueComposition);

        var record = Assert.IsType<ManualRevenueCompositionRecord>(result.Items[0].Record);
        Assert.Equal("2026-06", record.BaseDate);
        Assert.Equal(5, record.Slices.Count);
        Assert.Equal(new ManualRevenueSlice("반도체", "50.0", 50d), record.Slices[0]);
        Assert.Equal(new ManualRevenueSlice("서비스", "-", 0d), record.Slices[1]);
        Assert.Null(record.Slices[2]);
    }

    [Fact]
    public async Task Revenue_storage_maps_Oracle_empty_strings_returned_as_null()
    {
        var table = EmptyTable(ManualFinancialScreenKind.RevenueComposition);
        table.Rows.Add("A", DBNull.Value, "SMOKE_100", DBNull.Value, DBNull.Value,
            DBNull.Value, DBNull.Value, Version, RowHandle(table));

        var result = await new LegacyManualFinancialScreenService(new RecordingQueryExecutor(table))
            .SearchAsync(ManualFinancialScreenKind.RevenueComposition);

        var record = Assert.IsType<ManualRevenueCompositionRecord>(result.Items[0].Record);
        Assert.Equal(string.Empty, record.BaseDate);
        Assert.Equal(new ManualRevenueSlice("SMOKE", "100", 100d), record.Slices[0]);
        Assert.All(record.Slices.Skip(1), Assert.Null);
    }

    [Fact]
    public async Task Revenue_storage_treats_legacy_underscore_slots_as_absent()
    {
        var table = EmptyTable(ManualFinancialScreenKind.RevenueComposition);
        table.Rows.Add("A", "2026-06", "DOMESTIC_100", "_", "_ignored", "", "_",
            Version, RowHandle(table));

        var result = await new LegacyManualFinancialScreenService(
                new RecordingQueryExecutor(table))
            .SearchAsync(ManualFinancialScreenKind.RevenueComposition);

        var record = Assert.IsType<ManualRevenueCompositionRecord>(
            Assert.Single(result.Items).Record);
        Assert.Equal(new ManualRevenueSlice("DOMESTIC", "100", 100d), record.Slices[0]);
        Assert.All(record.Slices.Skip(1), Assert.Null);
        Assert.Equal(0, result.RejectedItemCount);
        Assert.True(Assert.Single(result.RawItems).IsPlayoutReady);
    }

    [Fact]
    public async Task Growth_storage_maps_four_named_series_and_four_periods()
    {
        var result = await new LegacyManualFinancialScreenService(
                new RecordingQueryExecutor(Table(ManualFinancialScreenKind.GrowthMetrics, "A")))
            .SearchAsync(ManualFinancialScreenKind.GrowthMetrics);

        var record = Assert.IsType<ManualGrowthMetricsRecord>(result.Items[0].Record);
        Assert.Equal([1d, 2d, 3d, 4d], record.SalesGrowth.ToArray());
        Assert.Equal([5d, 6d, null, 8d], record.OperatingProfitGrowth.ToArray());
        Assert.Equal(["1Q", "2Q", "3Q", "4Q"], record.Periods.ToArray());
    }

    [Theory]
    [InlineData(ManualFinancialScreenKind.Sales)]
    [InlineData(ManualFinancialScreenKind.OperatingProfit)]
    public async Task Six_quarter_storage_maps_signed_integer_values(ManualFinancialScreenKind screen)
    {
        var result = await new LegacyManualFinancialScreenService(
                new RecordingQueryExecutor(Table(screen, "A")))
            .SearchAsync(screen);

        var quarters = result.Items[0].Record switch
        {
            ManualSalesRecord sales => sales.Quarters,
            ManualOperatingProfitRecord profit => profit.Quarters,
            _ => throw new InvalidOperationException()
        };
        Assert.Equal(6, quarters.Count);
        Assert.Equal("1Q", quarters[0].Quarter);
        Assert.Equal(10, quarters[0].Value);
        Assert.Equal(-20, quarters[1].Value);
    }

    [Theory]
    [InlineData(ManualFinancialScreenKind.Sales)]
    [InlineData(ManualFinancialScreenKind.OperatingProfit)]
    public async Task Six_quarter_storage_keeps_legacy_empty_labels_and_ignores_extra_tokens(
        ManualFinancialScreenKind screen)
    {
        var table = Table(screen, "A");
        table.Rows[0]["GUSUNG_1"] = "_";
        table.Rows[0]["GUSUNG_2"] = "_20_legacy-extra";

        var result = await new LegacyManualFinancialScreenService(
                new RecordingQueryExecutor(table))
            .SearchAsync(screen);

        var quarters = Assert.Single(result.Items).Record switch
        {
            ManualSalesRecord sales => sales.Quarters,
            ManualOperatingProfitRecord profit => profit.Quarters,
            _ => throw new InvalidOperationException()
        };
        Assert.Equal(string.Empty, quarters[0].Quarter);
        Assert.Equal(0, quarters[0].Value);
        Assert.Equal(string.Empty, quarters[1].Quarter);
        Assert.Equal(20, quarters[1].Value);
        Assert.Equal(0, result.RejectedItemCount);
    }

    [Fact]
    public async Task Six_quarter_storage_preservesRawTabAndNormalizesOnlyTrailingLabelTab()
    {
        var table = Table(ManualFinancialScreenKind.Sales, "Alpha");
        table.Rows[0]["GUSUNG_2"] = "2023/12\t_159";
        table.Rows[0]["GUSUNG_3"] = "3Q_\t30\t";

        var result = await new LegacyManualFinancialScreenService(
                new RecordingQueryExecutor(table))
            .SearchAsync(ManualFinancialScreenKind.Sales);

        var strict = Assert.IsType<ManualSalesRecord>(Assert.Single(result.Items).Record);
        Assert.Equal("2023/12", strict.Quarters[1].Quarter);
        Assert.Equal(159, strict.Quarters[1].Value);
        Assert.Equal(30, strict.Quarters[2].Value);
        Assert.Equal("2023/12\t_159", Assert.Single(result.RawItems).Record.StorageValues[1]);
        Assert.Equal("3Q_\t30\t", Assert.Single(result.RawItems).Record.StorageValues[2]);

        foreach (var invalid in new[] { "2023\t/12_159", "2023/12_\t", "2023/12_15\t9" })
        {
            var rejected = table.Copy();
            rejected.Rows[0]["GUSUNG_2"] = invalid;
            var rejectedResult = await new LegacyManualFinancialScreenService(
                    new RecordingQueryExecutor(rejected))
                .SearchAsync(ManualFinancialScreenKind.Sales);
            Assert.Empty(rejectedResult.Items);
            Assert.False(Assert.Single(rejectedResult.RawItems).IsPlayoutReady);
        }
    }

    [Fact]
    public async Task Search_preserves_duplicate_names_for_row_level_exact_validation()
    {
        var table = EmptyTable(ManualFinancialScreenKind.Sales);
        AddQuarterRow(table, "Alpha");
        AddQuarterRow(table, "ALPHA", "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB");

        var result = await new LegacyManualFinancialScreenService(
                new RecordingQueryExecutor(table))
            .SearchAsync(ManualFinancialScreenKind.Sales);

        Assert.Equal(["Alpha", "ALPHA"],
            result.Items.Select(item => item.Record.Identity.StockName));
    }

    [Fact]
    public async Task Exact_get_bindsNameAndRejectsNonEquivalentDuplicateRows()
    {
        var identity = new ManualFinancialIdentity(ManualFinancialScreenKind.Sales, "Alpha");

        var missing = new RecordingQueryExecutor(EmptyTable(ManualFinancialScreenKind.Sales));
        await Assert.ThrowsAsync<ManualFinancialNotFoundException>(() =>
            new LegacyManualFinancialScreenService(missing).GetAsync(identity));
        Assert.Collection(
            missing.Spec!.Parameters,
            parameter =>
            {
                Assert.Equal("stock_name", parameter.Name);
                Assert.Equal("Alpha", parameter.Value);
            },
            parameter =>
            {
                Assert.Equal("row_limit", parameter.Name);
                Assert.Equal(LegacyManualFinancialScreenService.MaximumResults + 1, parameter.Value);
            });
        Assert.DoesNotContain("Alpha", missing.Spec.Sql, StringComparison.Ordinal);

        var duplicate = EmptyTable(ManualFinancialScreenKind.Sales);
        AddQuarterRow(duplicate, "Alpha");
        AddQuarterRow(duplicate, "Alpha", "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB");
        await Assert.ThrowsAsync<ManualFinancialAmbiguousIdentityException>(() =>
            new LegacyManualFinancialScreenService(new RecordingQueryExecutor(duplicate))
                .GetAsync(identity));
    }

    [Fact]
    public async Task Exact_get_collapsesEquivalentPayloadAndVersionButRejectsAnyConflict()
    {
        var identity = new ManualFinancialIdentity(ManualFinancialScreenKind.Sales, "Alpha");
        var equivalent = EmptyTable(ManualFinancialScreenKind.Sales);
        AddQuarterRow(equivalent, "Alpha");
        AddQuarterRow(equivalent, "Alpha");

        var snapshot = await new LegacyManualFinancialScreenService(
                new RecordingQueryExecutor(equivalent))
            .GetAsync(identity);
        Assert.Equal(identity, snapshot.Record.Identity);
        Assert.Equal(Version, snapshot.RowVersion);

        var conflictingPayload = equivalent.Copy();
        conflictingPayload.Rows[1]["GUSUNG_6"] = "6Q_61";
        await Assert.ThrowsAsync<ManualFinancialAmbiguousIdentityException>(() =>
            new LegacyManualFinancialScreenService(new RecordingQueryExecutor(conflictingPayload))
                .GetAsync(identity));

        var thirdRowConflict = equivalent.Copy();
        AddQuarterRow(thirdRowConflict, "Alpha");
        thirdRowConflict.Rows[2]["GUSUNG_1"] = "1Q_11";
        await Assert.ThrowsAsync<ManualFinancialAmbiguousIdentityException>(() =>
            new LegacyManualFinancialScreenService(new RecordingQueryExecutor(thirdRowConflict))
                .GetAsync(identity));
    }

    [Fact]
    public async Task Read_schema_fails_closed()
    {
        var table = Table(ManualFinancialScreenKind.Sales, "A");
        table.Columns[0].ColumnName = "stock_name";

        await Assert.ThrowsAsync<ManualFinancialDataException>(() =>
            new LegacyManualFinancialScreenService(new RecordingQueryExecutor(table))
                .SearchAsync(ManualFinancialScreenKind.Sales));
    }

    [Fact]
    public async Task Search_isolates_malformed_rows_and_returns_every_valid_row()
    {
        var table = EmptyTable(ManualFinancialScreenKind.Sales);
        AddQuarterRow(table, "Alpha");
        AddQuarterRow(table, "Broken");
        table.Rows[1]["GUSUNG_3"] = "not-compound-storage";
        AddQuarterRow(
            table,
            "BadVersion",
            "not-a-row-version");
        AddQuarterRow(
            table,
            "Omega",
            "BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB");

        var result = await new LegacyManualFinancialScreenService(
                new RecordingQueryExecutor(table))
            .SearchAsync(ManualFinancialScreenKind.Sales);

        Assert.Equal(["Alpha", "Omega"],
            result.Items.Select(item => item.Record.Identity.StockName));
        Assert.Equal(2, result.RejectedItemCount);
        Assert.Equal(["Broken", "BadVersion"], result.UnreadableStockNames);
        Assert.False(result.IsTruncated);
    }

    [Fact]
    public async Task Search_preserves_source_truncation_when_a_boundary_row_is_rejected()
    {
        var table = EmptyTable(ManualFinancialScreenKind.Sales);
        AddQuarterRow(table, "Alpha");
        AddQuarterRow(table, "Broken");
        table.Rows[1]["GUSUNG_3"] = "not-compound-storage";
        AddQuarterRow(table, "Omega");

        var result = await new LegacyManualFinancialScreenService(
                new RecordingQueryExecutor(table))
            .SearchAsync(ManualFinancialScreenKind.Sales, maximumResults: 2);

        Assert.Equal(["Alpha"],
            result.Items.Select(item => item.Record.Identity.StockName));
        Assert.Equal(1, result.RejectedItemCount);
        Assert.Equal(["Broken"], result.UnreadableStockNames);
        Assert.True(result.IsTruncated);
    }

    [Fact]
    public async Task Exact_get_keeps_malformed_selected_row_strictly_blocked()
    {
        var table = EmptyTable(ManualFinancialScreenKind.Sales);
        AddQuarterRow(table, "Broken");
        table.Rows[0]["GUSUNG_3"] = "not-compound-storage";
        var service = new LegacyManualFinancialScreenService(
            new RecordingQueryExecutor(table));

        await Assert.ThrowsAsync<ManualFinancialDataException>(() => service.GetAsync(
                new ManualFinancialIdentity(ManualFinancialScreenKind.Sales, "Broken")));
    }

    [Fact]
    public async Task Raw_search_keeps_malformed_legacy_strings_and_redacts_native_handle()
    {
        var table = EmptyTable(ManualFinancialScreenKind.Sales);
        AddQuarterRow(table, "Broken");
        table.Rows[0]["GUSUNG_1"] = "_10";
        table.Rows[0]["GUSUNG_2"] = " 2Q _ 20 ";
        table.Rows[0]["GUSUNG_3"] = "missing-delimiter";
        var service = new LegacyManualFinancialScreenService(
            new RecordingQueryExecutor(table));

        var result = await service.SearchAsync(ManualFinancialScreenKind.Sales);

        var raw = Assert.Single(result.RawItems);
        Assert.True(result.RawItemsComplete);
        Assert.Null(raw.StrictSnapshot);
        Assert.Equal("_10", raw.Record.StorageValues[0]);
        Assert.Equal(" 2Q _ 20 ", raw.Record.StorageValues[1]);
        Assert.Equal("Opaque manual-financial row handle", raw.Handle.ToString());
        Assert.DoesNotContain("AAAAAAAA", raw.Handle.ToString(), StringComparison.Ordinal);
    }

    [Fact]
    public async Task Raw_get_update_and_delete_use_only_opaque_row_handle_and_bound_values()
    {
        var table = EmptyTable(ManualFinancialScreenKind.Sales);
        AddQuarterRow(table, "Broken");
        table.Rows[0]["GUSUNG_1"] = "_10";
        var mutation = new RecordingMutationExecutor();
        var service = new LegacyManualFinancialScreenService(
            new RecordingQueryExecutor(table),
            mutation);
        var listed = Assert.Single((await service.SearchAsync(
            ManualFinancialScreenKind.Sales)).RawItems);

        var fresh = await service.GetRawAsync(listed);
        Assert.Equal(listed.Record.StorageValues, fresh.Record.StorageValues);
        var replacementValues = fresh.Record.StorageValues.ToArray();
        replacementValues[0] = "  raw_\u0001value ";
        var replacement = new ManualFinancialRawRecord(
            fresh.Record.Identity,
            replacementValues);

        var updated = await service.UpdateRawAsync(fresh, replacement);

        Assert.Equal(ManualFinancialMutationKind.Update, updated.Kind);
        var update = Assert.Single(mutation.Transaction!.Commands);
        Assert.Contains("ROWID = CHARTOROWID(:row_handle)", update.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("  raw_\u0001value ", update.Sql, StringComparison.Ordinal);
        Assert.Equal("  raw_\u0001value ", update.Parameters[0].Value);
        Assert.Equal("row_handle", update.Parameters[6].Name);

        var deleted = await service.DeleteRawAsync(fresh);
        Assert.Equal(ManualFinancialMutationKind.DeleteOne, deleted.Kind);
        var delete = Assert.Single(mutation.Transaction!.Commands);
        Assert.Contains("ROWID = CHARTOROWID(:row_handle)", delete.Sql, StringComparison.Ordinal);
        Assert.Equal(["row_handle", "identity_stock_name", "expected_row_version"],
            delete.Parameters.Select(parameter => parameter.Name));
    }

    [Theory]
    [MemberData(nameof(Screens))]
    public async Task Create_uses_bound_values_one_locked_transaction_and_exactly_one_row(
        ManualFinancialScreenKind screen,
        string table,
        string builder,
        string cut,
        string graphic)
    {
        var mutation = new RecordingMutationExecutor();
        var service = new LegacyManualFinancialScreenService(
            new RecordingQueryExecutor(EmptyTable(screen)),
            mutation);
        var record = Record(screen, "Operator ' quoted");

        var receipt = await service.CreateAsync(record);

        Assert.Equal(1, mutation.CallCount);
        Assert.Equal(ManualFinancialMutationKind.Create, receipt.Kind);
        Assert.Equal(screen, receipt.Screen);
        Assert.Equal("Operator ' quoted", receipt.StockName);
        Assert.Equal(1, receipt.AffectedRows);
        Assert.NotEqual(Guid.Empty, receipt.OperationId);
        Assert.Equal(DataSourceKind.Oracle, mutation.Source);
        var transaction = Assert.IsType<ManualFinancialDbTransaction>(mutation.Transaction);
        Assert.Equal(screen, transaction.Screen);
        Assert.Equal(builder, ManualFinancialCutContracts.Get(screen).BuilderKey);
        Assert.Equal(cut, ManualFinancialCutContracts.Get(screen).CutCode);
        Assert.Equal(graphic, ManualFinancialCutContracts.Get(screen).CanonicalGraphicType);
        Assert.Equal(table, transaction.TableName);
        Assert.Equal(ManualFinancialMutationKind.Create, transaction.Kind);
        Assert.True(transaction.RequiresExclusiveTableLock);
        Assert.Equal("LOCK TABLE " + table + " IN EXCLUSIVE MODE", transaction.ExclusiveTableLockSql);
        var command = Assert.Single(transaction.Commands);
        Assert.Equal(ManualFinancialDbCommandKind.Insert, command.Kind);
        Assert.Equal(ManualFinancialAffectedRowsRule.ExactlyOne, command.AffectedRowsRule);
        Assert.Contains("INSERT INTO " + table, command.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("Operator ' quoted", command.Sql, StringComparison.Ordinal);
        Assert.Equal("Operator ' quoted", command.Parameters.Single(p => p.Name == "stock_name").Value);
        Assert.Equal("Operator ' quoted", command.Parameters.Single(p => p.Name == "unique_stock_name").Value);
        Assert.All(command.Parameters, parameter => Assert.Equal(DbType.String, parameter.DbType));
    }

    [Fact]
    public async Task Update_preserves_identity_binds_row_version_and_serializes_loader_shapes()
    {
        var mutation = new RecordingMutationExecutor();
        var service = MutableService(ManualFinancialScreenKind.GrowthMetrics, mutation);
        var original = Snapshot(Record(ManualFinancialScreenKind.GrowthMetrics, "A"));
        var replacement = new ManualGrowthMetricsRecord(
            original.Record.Identity,
            new ManualGrowthSeriesValues(1, null, 3.5, 4),
            new ManualGrowthSeriesValues(5, 6, 7, 8),
            new ManualGrowthSeriesValues(9, 10, 11, 12),
            new ManualGrowthSeriesValues(13, 14, 15, 16),
            new ManualGrowthPeriodLabels("1Q", "2Q", "3Q", "4Q"));

        await service.UpdateAsync(original, replacement);

        var transaction = Assert.IsType<ManualFinancialDbTransaction>(mutation.Transaction);
        var command = Assert.Single(transaction.Commands);
        Assert.Equal(ManualFinancialDbCommandKind.Update, command.Kind);
        Assert.Equal(Version, command.Parameters.Single(p => p.Name == "expected_row_version").Value);
        Assert.Equal("1__3.5_4", command.Parameters.Single(p => p.Name == "field_1").Value);
        Assert.Equal("1Q_2Q_3Q_4Q", command.Parameters.Single(p => p.Name == "field_5").Value);
        Assert.Contains("STANDARD_HASH", command.Sql, StringComparison.Ordinal);
        Assert.Contains("COUNT(*)", command.Sql, StringComparison.Ordinal);
    }

    [Fact]
    public async Task Update_rejects_rename_or_screen_change_before_executor()
    {
        var mutation = new RecordingMutationExecutor();
        var service = MutableService(ManualFinancialScreenKind.Sales, mutation);
        var original = Snapshot(Record(ManualFinancialScreenKind.Sales, "A"));

        await Assert.ThrowsAsync<ArgumentException>(() =>
            service.UpdateAsync(original, Record(ManualFinancialScreenKind.Sales, "B")));
        await Assert.ThrowsAsync<ArgumentException>(() =>
            service.UpdateAsync(original, Record(ManualFinancialScreenKind.OperatingProfit, "A")));
        Assert.Equal(0, mutation.CallCount);
    }

    [Fact]
    public async Task Delete_one_binds_identity_and_version_while_delete_all_has_no_values()
    {
        var mutation = new RecordingMutationExecutor();
        var service = MutableService(ManualFinancialScreenKind.OperatingProfit, mutation);
        var snapshot = Snapshot(Record(ManualFinancialScreenKind.OperatingProfit, "A"));

        await service.DeleteAsync(snapshot);
        var deleteOne = Assert.Single(mutation.Transaction!.Commands);
        Assert.Equal(ManualFinancialDbCommandKind.DeleteOne, deleteOne.Kind);
        Assert.Equal(["identity_stock_name", "expected_row_version", "unique_stock_name"],
            deleteOne.Parameters.Select(parameter => parameter.Name));
        Assert.Contains("STANDARD_HASH", deleteOne.Sql, StringComparison.Ordinal);

        await service.DeleteAllAsync(ManualFinancialScreenKind.OperatingProfit);
        var deleteAll = Assert.Single(mutation.Transaction!.Commands);
        Assert.Equal(ManualFinancialDbCommandKind.DeleteAll, deleteAll.Kind);
        Assert.Equal(ManualFinancialAffectedRowsRule.AnyNonNegative, deleteAll.AffectedRowsRule);
        Assert.Empty(deleteAll.Parameters);
        Assert.Equal("DELETE FROM INPUT_PROFIT", deleteAll.Sql);
    }

    [Fact]
    public async Task Read_only_service_blocks_every_mutation_before_database_work()
    {
        var service = new LegacyManualFinancialScreenService(
            new RecordingQueryExecutor(EmptyTable(ManualFinancialScreenKind.Sales)));
        Assert.False(service.CanMutate);
        var record = Record(ManualFinancialScreenKind.Sales, "A");
        var snapshot = Snapshot(record);

        await Assert.ThrowsAsync<InvalidOperationException>(() => service.CreateAsync(record));
        await Assert.ThrowsAsync<InvalidOperationException>(() => service.UpdateAsync(snapshot, record));
        await Assert.ThrowsAsync<InvalidOperationException>(() => service.DeleteAsync(snapshot));
        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            service.DeleteAllAsync(ManualFinancialScreenKind.Sales));
    }

    [Fact]
    public async Task Unknown_outcome_is_not_retried_and_known_rollback_is_preserved()
    {
        var unknown = new RecordingMutationExecutor { Exception = new TimeoutException("late") };
        var service = MutableService(ManualFinancialScreenKind.Sales, unknown);
        var exception = await Assert.ThrowsAsync<ManualFinancialMutationException>(() =>
            service.CreateAsync(Record(ManualFinancialScreenKind.Sales, "A")));
        Assert.True(exception.OutcomeUnknown);
        Assert.Equal(1, unknown.CallCount);

        var rejectedException = new ManualFinancialMutationRejectedException(
            ManualFinancialMutationRejectionReason.DuplicateIdentity,
            "rolled back");
        var rejected = new RecordingMutationExecutor { Exception = rejectedException };
        var rejectedService = MutableService(ManualFinancialScreenKind.Sales, rejected);
        var actual = await Assert.ThrowsAsync<ManualFinancialMutationRejectedException>(() =>
            rejectedService.CreateAsync(Record(ManualFinancialScreenKind.Sales, "A")));
        Assert.Same(rejectedException, actual);
        Assert.False(actual.OutcomeUnknown);
        Assert.Equal(1, rejected.CallCount);
    }

    [Theory]
    [InlineData(-1)]
    [InlineData(0)]
    [InlineData(2)]
    public async Task Ambiguous_committed_row_count_is_OutcomeUnknown_and_never_retried(int affectedRows)
    {
        var mutation = new RecordingMutationExecutor { AffectedRows = affectedRows };
        var service = MutableService(ManualFinancialScreenKind.Sales, mutation);

        var exception = await Assert.ThrowsAsync<ManualFinancialMutationException>(() =>
            service.CreateAsync(Record(ManualFinancialScreenKind.Sales, "A")));

        Assert.True(exception.OutcomeUnknown);
        Assert.Equal(1, mutation.CallCount);
    }

    [Fact]
    public void Verified_stock_rejects_duplicate_names_duplicate_rows_or_wrong_provider()
    {
        var identity = new ManualFinancialIdentity(ManualFinancialScreenKind.Sales, "Alpha");
        var selected = new StockSearchItem(StockMarket.Kospi, DataSourceKind.Oracle, "Alpha", "000001");

        Assert.Throws<ArgumentException>(() => ManualFinancialCutContracts.VerifyStock(
            identity,
            [selected, new(StockMarket.Kosdaq, DataSourceKind.Oracle, "Alpha", "000002")],
            StockMarket.Kospi,
            "000001"));
        Assert.Throws<ArgumentException>(() => ManualFinancialCutContracts.VerifyStock(
            identity,
            [selected, selected],
            StockMarket.Kospi,
            "000001"));
        Assert.Throws<ArgumentException>(() => ManualFinancialCutContracts.VerifyStock(
            identity,
            [new(StockMarket.Kospi, DataSourceKind.MariaDb, "Alpha", "000001")],
            StockMarket.Kospi,
            "000001"));
    }

    [Theory]
    [MemberData(nameof(Screens))]
    public void Verified_stock_creates_the_exact_s5076_s5079_s5080_s5081_contract(
        ManualFinancialScreenKind screen,
        string _,
        string builder,
        string cut,
        string graphic)
    {
        var record = Record(screen, "Alpha");
        var snapshot = Snapshot(record);
        var candidate = new StockSearchItem(
            StockMarket.Kosdaq,
            DataSourceKind.Oracle,
            "Alpha",
            "000001");
        var stock = ManualFinancialCutContracts.VerifyStock(
            record.Identity,
            [candidate],
            StockMarket.Kosdaq,
            "000001");

        var selection = ManualFinancialCutContracts.CreateSelection(snapshot, stock);

        Assert.Equal(builder, selection.BuilderKey);
        Assert.Equal(cut, selection.CutCode);
        Assert.Equal(1, selection.CurrentPage);
        Assert.Equal(1, selection.TotalPages);
        Assert.Equal("KOSDAQ", selection.Selection.GroupCode);
        Assert.Equal("Alpha", selection.Selection.Subject);
        Assert.Equal(graphic, selection.Selection.GraphicType);
        Assert.Equal(string.Empty, selection.Selection.Subtype);
        Assert.Equal(string.Empty, selection.Selection.DataCode);
    }

    [Fact]
    public async Task Invalid_closed_DTO_shapes_fail_before_a_mutation()
    {
        var mutation = new RecordingMutationExecutor();
        var service = MutableService(ManualFinancialScreenKind.RevenueComposition, mutation);
        var identity = new ManualFinancialIdentity(
            ManualFinancialScreenKind.RevenueComposition,
            "A");
        var wrongCount = new ManualRevenueCompositionRecord(
            identity,
            "2026",
            [new("A", "50", 50)]);
        var inconsistent = new ManualRevenueCompositionRecord(
            identity,
            "2026",
            [new("A", "50", 49), null, null, null, null]);
        var wrongType = new ManualSalesRecord(identity, SixQuarters());

        await Assert.ThrowsAsync<ArgumentException>(() => service.CreateAsync(wrongCount));
        await Assert.ThrowsAsync<ArgumentException>(() => service.CreateAsync(inconsistent));
        await Assert.ThrowsAsync<ArgumentException>(() => service.CreateAsync(wrongType));
        Assert.Equal(0, mutation.CallCount);
    }

    private static LegacyManualFinancialScreenService MutableService(
        ManualFinancialScreenKind screen,
        RecordingMutationExecutor mutation) =>
        new(new RecordingQueryExecutor(EmptyTable(screen)), mutation);

    private static ManualFinancialSnapshot Snapshot(ManualFinancialRecord record) =>
        new(record, Version);

    private static ManualFinancialRecord Record(
        ManualFinancialScreenKind screen,
        string stockName)
    {
        var identity = new ManualFinancialIdentity(screen, stockName);
        return screen switch
        {
            ManualFinancialScreenKind.RevenueComposition =>
                new ManualRevenueCompositionRecord(
                    identity,
                    "2026-06",
                    [
                        new("반도체", "50", 50),
                        new("서비스", "25", 25),
                        new("해외", "25", 25),
                        null,
                        null
                    ]),
            ManualFinancialScreenKind.GrowthMetrics =>
                new ManualGrowthMetricsRecord(
                    identity,
                    new(1, 2, 3, 4),
                    new(5, 6, null, 8),
                    new(9, 10, 11, 12),
                    new(13, 14, 15, 16),
                    new("1Q", "2Q", "3Q", "4Q")),
            ManualFinancialScreenKind.Sales => new ManualSalesRecord(identity, SixQuarters()),
            ManualFinancialScreenKind.OperatingProfit =>
                new ManualOperatingProfitRecord(identity, SixQuarters()),
            _ => throw new ArgumentOutOfRangeException(nameof(screen))
        };
    }

    private static IReadOnlyList<ManualQuarterValue> SixQuarters() =>
    [
        new("1Q", 10),
        new("2Q", -20),
        new("3Q", 30),
        new("4Q", 40),
        new("5Q", 50),
        new("6Q", 60)
    ];

    private static DataTable Table(ManualFinancialScreenKind screen, string stockName)
    {
        var table = EmptyTable(screen);
        switch (screen)
        {
            case ManualFinancialScreenKind.RevenueComposition:
                table.Rows.Add(stockName, "2026-06", "반도체_50", "서비스_25", "해외_25", "", "", Version, RowHandle(table));
                break;
            case ManualFinancialScreenKind.GrowthMetrics:
                table.Rows.Add(stockName, "1_2_3_4", "5_6__8", "9_10_11_12", "13_14_15_16", "1Q_2Q_3Q_4Q", Version, RowHandle(table));
                break;
            case ManualFinancialScreenKind.Sales:
            case ManualFinancialScreenKind.OperatingProfit:
                AddQuarterRow(table, stockName);
                break;
            default:
                throw new ArgumentOutOfRangeException(nameof(screen));
        }

        return table;
    }

    private static DataTable EmptyTable(ManualFinancialScreenKind screen)
    {
        var table = new DataTable();
        foreach (var column in ManualFinancialCutContracts.Get(screen).StorageColumns)
        {
            table.Columns.Add(column, typeof(string));
        }
        table.Columns.Add("ROW_VERSION", typeof(string));
        table.Columns.Add("ROW_HANDLE", typeof(string));
        return table;
    }

    private static void AddQuarterRow(
        DataTable table,
        string stockName,
        string version = Version) =>
        table.Rows.Add(
            stockName,
            "1Q_10",
            "2Q_-20",
            "3Q_30",
            "4Q_40",
            "5Q_50",
            "6Q_60",
            version,
            RowHandle(table));

    private static string RowHandle(DataTable table) =>
        "AAAAAAAAAAAAAAAA" + (table.Rows.Count + 1).ToString("D2");

    private static string ExtractRowVersionExpression(string sql)
    {
        const string startToken = "RAWTOHEX(STANDARD_HASH(";
        const string endToken = ", 'SHA256'))";
        var start = sql.IndexOf(startToken, StringComparison.Ordinal);
        var end = start < 0
            ? -1
            : sql.IndexOf(endToken, start, StringComparison.Ordinal);
        Assert.True(start >= 0 && end > start, "A row-version expression is missing.");
        return sql.Substring(start, end - start + endToken.Length);
    }

    private static int CountOccurrences(string value, string token)
    {
        var count = 0;
        for (var index = 0;
             (index = value.IndexOf(token, index, StringComparison.Ordinal)) >= 0;
             index += token.Length)
        {
            count++;
        }

        return count;
    }

    private static string OldDelimitedEncoding(IEnumerable<string?> values) =>
        string.Join('\u001f', values.Select(value => string.IsNullOrEmpty(value)
            ? "\0"
            : value));

    private static string NewLengthPrefixedEncoding(IEnumerable<string?> values) =>
        string.Concat(values.Select(value => string.IsNullOrEmpty(value)
            ? "N"
            : "V" + value.Length.ToString(
                "D10",
                System.Globalization.CultureInfo.InvariantCulture) + value));

    private static void AssertDistinctConcurrencyTokens(
        IEnumerable<string?> left,
        IEnumerable<string?> right)
    {
        var leftEncoding = NewLengthPrefixedEncoding(left);
        var rightEncoding = NewLengthPrefixedEncoding(right);
        Assert.NotEqual(leftEncoding, rightEncoding);
        Assert.NotEqual(
            HashConcurrencyEncoding(leftEncoding),
            HashConcurrencyEncoding(rightEncoding));
    }

    private static string HashConcurrencyEncoding(string value) =>
        Convert.ToHexString(System.Security.Cryptography.SHA256.HashData(
            System.Text.Encoding.UTF8.GetBytes(value)));

    private static string QueryToken(ManualFinancialScreenKind screen) => screen switch
    {
        ManualFinancialScreenKind.RevenueComposition => "PIE",
        ManualFinancialScreenKind.GrowthMetrics => "GROW",
        ManualFinancialScreenKind.Sales => "SELL",
        ManualFinancialScreenKind.OperatingProfit => "PROFIT",
        _ => throw new ArgumentOutOfRangeException(nameof(screen))
    };

    private sealed class RecordingQueryExecutor : IDataQueryExecutor
    {
        private readonly DataTable _table;

        public RecordingQueryExecutor(DataTable table)
        {
            _table = table;
        }

        public DataSourceKind? Source { get; private set; }

        public string? TableName { get; private set; }

        public DataQuerySpec? Spec { get; private set; }

        public DataTable Execute(DataSourceKind source, string tableName, string query) =>
            throw new NotSupportedException();

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            string query,
            CancellationToken cancellationToken = default) =>
            throw new NotSupportedException();

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            DataQuerySpec query,
            CancellationToken cancellationToken = default)
        {
            cancellationToken.ThrowIfCancellationRequested();
            Source = source;
            TableName = tableName;
            Spec = query;
            return Task.FromResult(_table);
        }
    }

    private sealed class RecordingMutationExecutor : IManualFinancialMutationExecutor
    {
        public int CallCount { get; private set; }

        public int AffectedRows { get; init; } = 1;

        public Exception? Exception { get; init; }

        public DataSourceKind? Source { get; private set; }

        public ManualFinancialDbTransaction? Transaction { get; private set; }

        public Task<ManualFinancialDbTransactionResult> ExecuteTransactionAsync(
            DataSourceKind source,
            ManualFinancialDbTransaction transaction,
            CancellationToken cancellationToken = default)
        {
            CallCount++;
            Source = source;
            Transaction = transaction;
            if (Exception is not null)
            {
                return Task.FromException<ManualFinancialDbTransactionResult>(Exception);
            }

            return Task.FromResult(new ManualFinancialDbTransactionResult(
                transaction.OperationId,
                [AffectedRows],
                DateTimeOffset.Now));
        }
    }
}
