using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class LegacyThemeCatalogPersistenceServiceTests
{
    [Fact]
    public async Task ReadStoredAsync_KrxReadsEveryStoredItemWithoutTheActiveStockJoin()
    {
        var table = StoredThemeTable();
        table.Rows.Add(" Legacy theme ", "00000420", "KRX", "Active", "P005930", "0");
        table.Rows.Add(" Legacy theme ", "00000420", "KRX", "Cross X", "X999998", "7");
        table.Rows.Add(" Legacy theme ", "00000420", "KRX", "Cross T", "T999999", "7");
        var query = new CatalogRecordingQueryExecutor(_ => table);
        var service = new LegacyThemeCatalogPersistenceService(query);
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Krx,
            ThemeSession.NotApplicable,
            "00000420",
            " Legacy theme ");

        var result = await service.ReadStoredAsync(identity);

        Assert.Equal(identity, result.Identity);
        Assert.False(result.IsTruncated);
        Assert.Equal(
            ["P005930", "X999998", "T999999"],
            result.Items.Select(static item => item.ItemCode));
        Assert.Equal([0, 7, 7], result.Items.Select(static item => item.InputIndex));
        var call = Assert.Single(query.Calls);
        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal(LegacyThemeCatalogPersistenceService.StoredKrxQueryName, call.TableName);
        Assert.Contains("LEFT JOIN SB_ITEM", call.Spec.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("v_all_stock", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("F_MKT_HALT", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Equal("00000420", Parameter(call.Spec, "theme_code").Value);
        Assert.Equal(" Legacy theme ", Parameter(call.Spec, "theme_title").Value);
        Assert.Equal(241, Parameter(call.Spec, "row_limit").Value);
    }

    [Fact]
    public async Task ReadStoredAsync_NxtPreservesNativeSuffixAndRowsMissingFromActivePreview()
    {
        var table = StoredThemeTable();
        table.Rows.Add("Native(NXT)", "00000008", "NXT", "Legacy KRX", "P000001", "3");
        var query = new CatalogRecordingQueryExecutor(_ => table);
        var service = new LegacyThemeCatalogPersistenceService(query);
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Nxt,
            ThemeSession.AfterMarket,
            "00000008",
            "Native(NXT)");

        var result = await service.ReadStoredAsync(identity);

        Assert.Equal("Legacy KRX", Assert.Single(result.Items).ItemName);
        var call = Assert.Single(query.Calls);
        Assert.Equal(DataSourceKind.MariaDb, call.Source);
        Assert.Equal(LegacyThemeCatalogPersistenceService.StoredNxtQueryName, call.TableName);
        Assert.Contains("LEFT JOIN SB_ITEM", call.Spec.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("N_STOCK", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("F_STOP_GUBUN", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.All(call.Spec.Parameters, parameter =>
            Assert.Contains("@" + parameter.Name, call.Spec.Sql, StringComparison.Ordinal));
    }

    [Fact]
    public async Task ReadStoredAsync_RepresentsAnEmptyStoredThemeWithoutConfusingItWithMissingIdentity()
    {
        var table = StoredThemeTable();
        table.Rows.Add("Empty", "00000009", "KRX", DBNull.Value, DBNull.Value, DBNull.Value);
        var service = new LegacyThemeCatalogPersistenceService(
            new CatalogRecordingQueryExecutor(_ => table));

        var result = await service.ReadStoredAsync(new ThemeSelectionIdentity(
            ThemeMarket.Krx,
            ThemeSession.NotApplicable,
            "00000009",
            "Empty"));

        Assert.Empty(result.Items);
        Assert.False(result.IsTruncated);
    }

    [Theory]
    [InlineData(ThemeMarket.Krx, DataSourceKind.Oracle, 0, false)]
    [InlineData(ThemeMarket.Krx, DataSourceKind.Oracle, 2, true)]
    [InlineData(ThemeMarket.Nxt, DataSourceKind.MariaDb, 0, false)]
    [InlineData(ThemeMarket.Nxt, DataSourceKind.MariaDb, 2, true)]
    public async Task ThemeNameExistsAsync_UsesOneParameterizedExactReadOnTheSelectedMarket(
        ThemeMarket market,
        DataSourceKind expectedSource,
        long count,
        bool expected)
    {
        var query = new CatalogRecordingQueryExecutor(_ => ThemeCountTable(count));
        var service = new LegacyThemeCatalogPersistenceService(query);
        var title = new string('가', 128);

        var exists = await service.ThemeNameExistsAsync(market, title);

        Assert.Equal(expected, exists);
        var call = Assert.Single(query.Calls);
        Assert.Equal(expectedSource, call.Source);
        Assert.Equal(
            market == ThemeMarket.Krx
                ? LegacyThemeCatalogPersistenceService.NameExistsKrxQueryName
                : LegacyThemeCatalogPersistenceService.NameExistsNxtQueryName,
            call.TableName);
        Assert.Contains("COUNT(*) THEME_COUNT", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("s.SB_TITLE = ", call.Spec.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain(title, call.Spec.Sql, StringComparison.Ordinal);
        var parameter = Assert.Single(call.Spec.Parameters);
        Assert.Equal("theme_title", parameter.Name);
        Assert.Equal(title, parameter.Value);
        Assert.Equal(DbType.String, parameter.DbType);
    }

    [Fact]
    public async Task SuggestNextCodeAsync_UsesReadOnlyProviderSpecificQueries()
    {
        var query = new CatalogRecordingQueryExecutor(call => call.TableName switch
        {
            LegacyThemeCatalogPersistenceService.NextKrxCodeQueryName =>
                OperatorCatalogTestTables.SingleString("THEME_CODE", "00000042"),
            LegacyThemeCatalogPersistenceService.NextNxtCodeQueryName =>
                OperatorCatalogTestTables.SingleString("THEME_CODE", "00000009"),
            _ => throw new InvalidOperationException(call.TableName)
        });
        var service = new LegacyThemeCatalogPersistenceService(query);

        Assert.Equal("00000042", await service.SuggestNextCodeAsync(ThemeMarket.Krx));
        Assert.Equal("00000009", await service.SuggestNextCodeAsync(ThemeMarket.Nxt));

        Assert.False(service.CanMutate);
        Assert.Collection(
            query.Calls,
            call =>
            {
                Assert.Equal(DataSourceKind.Oracle, call.Source);
                Assert.Contains("TO_NUMBER(TRIM(SB_CODE))", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Contains("'^[0-9]{3,8}$'", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Contains("FROM SB_LIST", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Empty(call.Spec.Parameters);
            },
            call =>
            {
                Assert.Equal(DataSourceKind.MariaDb, call.Source);
                Assert.Contains("CAST(TRIM(SB_CODE) AS UNSIGNED)", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Contains("REGEXP BINARY '^[0-9]{3,8}$'", call.Spec.Sql, StringComparison.Ordinal);
                Assert.DoesNotContain("INVALID", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Empty(call.Spec.Parameters);
            });
    }

    [Fact]
    public async Task SuggestNextCodeAsync_RejectsExhaustedCodeRange()
    {
        var service = new LegacyThemeCatalogPersistenceService(
            new CatalogRecordingQueryExecutor(_ =>
                OperatorCatalogTestTables.SingleString("THEME_CODE", "EXHAUSTED")));

        await Assert.ThrowsAsync<ThemeSelectionDataException>(() =>
            service.SuggestNextCodeAsync(ThemeMarket.Krx));
    }

    [Fact]
    public async Task CreateAsync_KrxBindsParentThenOrderedChildrenInOneTransaction()
    {
        var query = UnusedQuery();
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(query, mutation);
        using var cancellation = new CancellationTokenSource();
        var definition = new ThemeCatalogDefinition(
            ThemeMarket.Krx,
            "00000042",
            "AI'테마",
            [
                new ThemeCatalogItem(0, "P005930", "삼성전자"),
                new ThemeCatalogItem(1, "D035720", "카카오")
            ]);

        var receipt = await service.CreateAsync(definition, cancellation.Token);

        Assert.True(service.CanMutate);
        Assert.Equal(OperatorCatalogMutationKind.CreateTheme, receipt.Kind);
        var call = Assert.Single(mutation.Calls);
        Assert.Equal(cancellation.Token, call.Token);
        var transaction = call.Transaction;
        Assert.Equal(DataSourceKind.Oracle, transaction.Source);
        Assert.Equal("00000042", transaction.IdentityCode);
        Assert.NotEqual(Guid.Empty, transaction.OperationId);
        Assert.Equal(
            [
                OperatorCatalogDbCommandKind.InsertTheme,
                OperatorCatalogDbCommandKind.InsertThemeItem,
                OperatorCatalogDbCommandKind.InsertThemeItem
            ],
            transaction.Commands.Select(static command => command.Kind));
        Assert.All(transaction.Commands, command =>
            Assert.DoesNotContain("AI'테마", command.Sql, StringComparison.Ordinal));

        var parent = transaction.Commands[0];
        Assert.Contains("NOT EXISTS", parent.Sql, StringComparison.Ordinal);
        Assert.Equal(OperatorCatalogAffectedRowsRule.ExactlyOne, parent.AffectedRowsRule);
        Assert.Equal("AI'테마", Parameter(parent, "theme_title").Value);
        Assert.Equal("AI'테마", Parameter(parent, "conflict_title").Value);
        Assert.Equal(DbType.String, Parameter(parent, "theme_title").DbType);

        Assert.Equal("P005930", Parameter(transaction.Commands[1], "item_code").Value);
        Assert.Equal(0, Parameter(transaction.Commands[1], "item_index").Value);
        Assert.Equal("D035720", Parameter(transaction.Commands[2], "item_code").Value);
        Assert.Equal(1, Parameter(transaction.Commands[2], "item_index").Value);
    }

    [Fact]
    public async Task CreateAsync_NxtUsesMariaMarkerAndRawStoredTitle()
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);
        var definition = new ThemeCatalogDefinition(
            ThemeMarket.Nxt,
            "00000007",
            "로봇",
            [new ThemeCatalogItem(0, "X005930", "삼성전자")]);

        await service.CreateAsync(definition);

        var transaction = Assert.Single(mutation.Calls).Transaction;
        Assert.Equal(DataSourceKind.MariaDb, transaction.Source);
        Assert.All(transaction.Commands, command =>
        {
            Assert.DoesNotContain(':', command.Sql);
            Assert.All(command.Parameters, parameter =>
                Assert.Contains($"@{parameter.Name}", command.Sql, StringComparison.OrdinalIgnoreCase));
        });
        Assert.Contains("'NXT'", transaction.Commands[0].Sql, StringComparison.Ordinal);

        await Assert.ThrowsAsync<ArgumentException>(() => service.CreateAsync(
            definition with { ThemeTitle = "로봇(NXT)" }));
        Assert.Single(mutation.Calls);
    }

    [Fact]
    public async Task ReplaceAsync_ChecksCodeAndOriginalTitleBeforeReplacingChildren()
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Krx,
            ThemeSession.NotApplicable,
            "00000042",
            "기존 테마");

        await service.ReplaceAsync(
            identity,
            "새 테마",
            [new ThemeCatalogItem(0, "P005930", "삼성전자")]);

        var transaction = Assert.Single(mutation.Calls).Transaction;
        Assert.Equal(OperatorCatalogMutationKind.ReplaceTheme, transaction.Kind);
        Assert.Equal(
            [
                OperatorCatalogDbCommandKind.UpdateTheme,
                OperatorCatalogDbCommandKind.DeleteThemeItems,
                OperatorCatalogDbCommandKind.InsertThemeItem
            ],
            transaction.Commands.Select(static command => command.Kind));
        var update = transaction.Commands[0];
        Assert.Equal("기존 테마", Parameter(update, "expected_title").Value);
        Assert.Equal("새 테마", Parameter(update, "new_title").Value);
        Assert.DoesNotContain(update.Parameters, parameter => parameter.Name == "duplicate_title");
        Assert.DoesNotContain("NOT EXISTS", update.Sql, StringComparison.Ordinal);
        Assert.Equal(
            OperatorCatalogAffectedRowsRule.ExactlyOne,
            update.AffectedRowsRule);
        Assert.Equal(
            OperatorCatalogAffectedRowsRule.AnyNonNegative,
            transaction.Commands[1].AffectedRowsRule);
    }

    [Theory]
    [InlineData(ThemeMarket.Krx, ThemeSession.NotApplicable, "X005930")]
    [InlineData(ThemeMarket.Nxt, ThemeSession.PreMarket, "P005930")]
    public async Task ReplaceAsync_PreservesClosedLegacyCrossMarketPrefixes(
        ThemeMarket market,
        ThemeSession session,
        string itemCode)
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);
        var identity = new ThemeSelectionIdentity(market, session, "00000042", "Legacy");

        await service.ReplaceAsync(
            identity,
            "Legacy",
            [new ThemeCatalogItem(0, itemCode, "Stored")]);

        var transaction = Assert.Single(mutation.Calls).Transaction;
        Assert.Equal(itemCode, Parameter(transaction.Commands[2], "item_code").Value);
    }

    [Theory]
    [InlineData(ThemeMarket.Krx, ThemeSession.NotApplicable, " Stored edge ")]
    [InlineData(ThemeMarket.Nxt, ThemeSession.AfterMarket, "Native(NXT)")]
    public async Task ReplaceAsync_PreservesAnUnchangedExactLegacyStoredTitle(
        ThemeMarket market,
        ThemeSession session,
        string storedTitle)
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);
        var identity = new ThemeSelectionIdentity(market, session, "00000042", storedTitle);

        await service.ReplaceAsync(identity, storedTitle, []);

        var update = Assert.Single(mutation.Calls).Transaction.Commands[0];
        Assert.Equal(storedTitle, Parameter(update, "expected_title").Value);
        Assert.Equal(storedTitle, Parameter(update, "new_title").Value);
    }

    [Theory]
    [InlineData("123")]
    [InlineData("0123")]
    [InlineData("000123")]
    [InlineData("00000123")]
    public async Task ExistingIdentity_PreservesLegacyThreeThroughEightDigitCode(string code)
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Krx,
            ThemeSession.NotApplicable,
            code,
            "기존 테마");

        await service.ReplaceAsync(identity, "수정 테마", []);

        var transaction = Assert.Single(mutation.Calls).Transaction;
        Assert.Equal(code, transaction.IdentityCode);
        Assert.Equal(code, Parameter(transaction.Commands[0], "theme_code").Value);
    }

    [Theory]
    [InlineData("12")]
    [InlineData("123456789")]
    [InlineData("12A")]
    public async Task ExistingIdentity_RejectsCodeOutsideClosedLegacyRange(string code)
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Krx,
            ThemeSession.NotApplicable,
            code,
            "기존 테마");

        await Assert.ThrowsAsync<ArgumentException>(() =>
            service.DeleteAsync(identity));

        Assert.Empty(mutation.Calls);
    }

    [Fact]
    public async Task CreateStillRequiresNewEightDigitCode()
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);

        await Assert.ThrowsAsync<ArgumentException>(() => service.CreateAsync(
            new ThemeCatalogDefinition(ThemeMarket.Krx, "123", "신규 테마", [])));

        Assert.Empty(mutation.Calls);
    }

    [Fact]
    public async Task DeleteAsync_DeletesChildrenBeforeIdentityBoundParent()
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Nxt,
            ThemeSession.AfterMarket,
            "00000008",
            "반도체");

        await service.DeleteAsync(identity);

        var transaction = Assert.Single(mutation.Calls).Transaction;
        Assert.Equal(DataSourceKind.MariaDb, transaction.Source);
        Assert.Equal(
            [
                OperatorCatalogDbCommandKind.DeleteThemeItems,
                OperatorCatalogDbCommandKind.DeleteTheme
            ],
            transaction.Commands.Select(static command => command.Kind));
        Assert.Equal("반도체", Parameter(transaction.Commands[1], "expected_title").Value);
        Assert.Equal(
            OperatorCatalogAffectedRowsRule.ExactlyOne,
            transaction.Commands[1].AffectedRowsRule);
    }

    [Fact]
    public async Task Mutations_AllowRepeatedItemsButRejectInvalidOrderOrMarketBeforeExecutor()
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);
        var baseDefinition = new ThemeCatalogDefinition(
            ThemeMarket.Krx,
            "00000001",
            "테마",
            []);

        await service.CreateAsync(
            baseDefinition with
            {
                Items =
                [
                    new ThemeCatalogItem(0, "P005930", "삼성전자"),
                    new ThemeCatalogItem(1, "P005930", "삼성전자")
                ]
            });
        Assert.Equal(3, Assert.Single(mutation.Calls).Transaction.Commands.Count);
        mutation.Calls.Clear();
        await Assert.ThrowsAsync<ArgumentException>(() => service.CreateAsync(
            baseDefinition with
            {
                Items = [new ThemeCatalogItem(1, "P005930", "삼성전자")]
            }));
        await Assert.ThrowsAsync<ArgumentException>(() => service.CreateAsync(
            baseDefinition with
            {
                Items = [new ThemeCatalogItem(0, "X005930", "삼성전자")]
            }));
        await Assert.ThrowsAsync<ArgumentException>(() => service.CreateAsync(
            baseDefinition with
            {
                Items = [new ThemeCatalogItem(0, "P005930", " Stored edge ")]
            }));

        Assert.Empty(mutation.Calls);
    }

    [Fact]
    public async Task Mutations_FailClosedForReadOnlyCancellationAndUnknownExecutorOutcome()
    {
        var definition = new ThemeCatalogDefinition(
            ThemeMarket.Krx,
            "00000001",
            "테마",
            []);
        var readOnly = new LegacyThemeCatalogPersistenceService(UnusedQuery());
        await Assert.ThrowsAsync<InvalidOperationException>(() => readOnly.CreateAsync(definition));

        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);
        using var cancellation = new CancellationTokenSource();
        cancellation.Cancel();
        await Assert.ThrowsAnyAsync<OperationCanceledException>(() =>
            service.CreateAsync(definition, cancellation.Token));
        Assert.Empty(mutation.Calls);

        mutation.Exception = new TimeoutException("provider detail");
        var exception = await Assert.ThrowsAsync<OperatorCatalogMutationException>(() =>
            service.CreateAsync(definition));
        Assert.True(exception.OutcomeUnknown);
        Assert.Contains("do not retry automatically", exception.Message, StringComparison.Ordinal);
    }

    [Fact]
    public async Task CreateAsync_TreatsMismatchedCommittedReceiptAsOutcomeUnknown()
    {
        var mutation = new CatalogRecordingMutationExecutor(transaction =>
            new OperatorCatalogDbTransactionResult(
                Guid.NewGuid(),
                [1],
                DateTimeOffset.UtcNow));
        var service = new LegacyThemeCatalogPersistenceService(UnusedQuery(), mutation);

        var exception = await Assert.ThrowsAsync<OperatorCatalogMutationException>(() =>
            service.CreateAsync(new ThemeCatalogDefinition(
                ThemeMarket.Krx,
                "00000001",
                "테마",
                [])));

        Assert.True(exception.OutcomeUnknown);
    }

    private static CatalogRecordingQueryExecutor UnusedQuery() =>
        new(_ => throw new InvalidOperationException("No read query was expected."));

    private static DataTable ThemeCountTable(long count)
    {
        var table = new DataTable();
        table.Columns.Add("THEME_COUNT", typeof(long));
        table.Rows.Add(count);
        return table;
    }

    private static OperatorCatalogDbParameter Parameter(
        OperatorCatalogDbCommand command,
        string name) =>
        Assert.Single(command.Parameters, parameter =>
            string.Equals(parameter.Name, name, StringComparison.Ordinal));

    private static DataQueryParameter Parameter(DataQuerySpec query, string name) =>
        Assert.Single(query.Parameters, parameter =>
            string.Equals(parameter.Name, name, StringComparison.Ordinal));

    private static DataTable StoredThemeTable()
    {
        var table = new DataTable();
        table.Columns.Add("THEME_TITLE", typeof(string));
        table.Columns.Add("THEME_CODE", typeof(string));
        table.Columns.Add("THEME_MARKET", typeof(string));
        table.Columns.Add("ITEM_NAME", typeof(string));
        table.Columns.Add("ITEM_CODE", typeof(string));
        table.Columns.Add("ITEM_INDEX", typeof(string));
        return table;
    }
}
