using System.Data;
using System.Globalization;
using MBN_STOCK_WEBVIEW.Core.Playout.Scenes;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

/// <summary>
/// Component integration of the typed service and its read/transaction seams.
/// The store is in-memory: no production Oracle row is changed by this test.
/// </summary>
public sealed class LegacyNamedPlaylistPersistenceIntegrationTests
{
    [Fact]
    public async Task NamedPlaylistLifecycle_PreservesTitleAndVisibleItemOrder()
    {
        var store = new InMemoryOracleContractStore();
        var service = new LegacyNamedPlaylistPersistenceService(store, store);

        var firstCode = await service.SuggestNextProgramCodeAsync();
        await service.CreateDefinitionAsync(firstCode, "Morning");
        var secondCode = await service.SuggestNextProgramCodeAsync();
        await service.CreateDefinitionAsync(secondCode, "Afternoon");

        var definitions = await service.ListAsync();
        Assert.Equal(
            ["Afternoon", "Morning"],
            definitions.Items.Select(static item => item.Title));

        var alpha = StoredItem(0, "Alpha", "000001");
        var beta = StoredItem(1, "Beta", "000002");
        await service.ReplaceItemsAsync(firstCode, [alpha, beta]);

        var initial = await service.LoadAsync(firstCode);
        Assert.Equal(
            ["Alpha", "Beta"],
            initial.Items.Select(static item => item.Selection.Subject));

        await service.ReplaceItemsAsync(
            firstCode,
            [beta with { ItemIndex = 0 }, alpha with { ItemIndex = 1 }]);

        var reordered = await service.LoadAsync(firstCode);
        Assert.Equal(
            ["Beta", "Alpha"],
            reordered.Items.Select(static item => item.Selection.Subject));
        Assert.Equal([0, 1], reordered.Items.Select(static item => item.ItemIndex));

        await service.DeleteAsync(firstCode);
        await Assert.ThrowsAsync<NamedPlaylistNotFoundException>(
            () => service.LoadAsync(firstCode));
        var remaining = await service.ListAsync();
        Assert.Equal(secondCode, Assert.Single(remaining.Items).ProgramCode);
    }

    private static NamedPlaylistStoredItem StoredItem(
        int index,
        string subject,
        string dataCode) =>
        new(
            index,
            true,
            new LegacySceneSelection("KOSPI", subject, "1열판기본", "현재가", dataCode),
            new NamedPlaylistPageState(1, 1));

    private sealed class InMemoryOracleContractStore
        : IDataQueryExecutor, INamedPlaylistMutationExecutor
    {
        private Dictionary<string, string> _definitions = new(StringComparer.Ordinal);
        private Dictionary<string, SortedDictionary<int, string>> _items = new(StringComparer.Ordinal);

        public DataTable Execute(DataSourceKind source, string tableName, string query) =>
            throw new InvalidOperationException("String SQL fallback is forbidden.");

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            string query,
            CancellationToken cancellationToken = default) =>
            throw new InvalidOperationException("String SQL fallback is forbidden.");

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            DataQuerySpec query,
            CancellationToken cancellationToken = default)
        {
            cancellationToken.ThrowIfCancellationRequested();
            Assert.Equal(DataSourceKind.Oracle, source);
            query.ValidateFor(source);
            var table = tableName switch
            {
                LegacyNamedPlaylistPersistenceService.ListQueryName => List(query),
                LegacyNamedPlaylistPersistenceService.NextCodeQueryName => NextCode(),
                LegacyNamedPlaylistPersistenceService.LoadQueryName => Load(query),
                _ => throw new InvalidOperationException("Unexpected query contract.")
            };
            return Task.FromResult(table);
        }

        public Task<NamedPlaylistDbTransactionResult> ExecuteTransactionAsync(
            DataSourceKind source,
            NamedPlaylistDbTransaction transaction,
            CancellationToken cancellationToken = default)
        {
            cancellationToken.ThrowIfCancellationRequested();
            Assert.Equal(DataSourceKind.Oracle, source);

            var definitions = new Dictionary<string, string>(_definitions, StringComparer.Ordinal);
            var items = _items.ToDictionary(
                static pair => pair.Key,
                static pair => new SortedDictionary<int, string>(pair.Value),
                StringComparer.Ordinal);
            var affected = new List<int>(transaction.Commands.Count);
            foreach (var command in transaction.Commands)
            {
                switch (command.Kind)
                {
                    case NamedPlaylistDbCommandKind.InsertDefinition:
                    {
                        var code = StringValue(command, "program_code");
                        var title = StringValue(command, "program_title");
                        if (!definitions.TryAdd(code, title))
                        {
                            throw new NamedPlaylistMutationException(
                                "Duplicate definition; transaction rolled back.",
                                outcomeUnknown: false);
                        }

                        affected.Add(1);
                        break;
                    }

                    case NamedPlaylistDbCommandKind.DeleteItems:
                    {
                        var code = StringValue(command, "program_code");
                        var count = items.TryGetValue(code, out var existing) ? existing.Count : 0;
                        items.Remove(code);
                        affected.Add(count);
                        break;
                    }

                    case NamedPlaylistDbCommandKind.InsertItem:
                    {
                        var code = StringValue(command, "program_code");
                        if (!definitions.ContainsKey(code))
                        {
                            throw new NamedPlaylistMutationException(
                                "Missing definition; transaction rolled back.",
                                outcomeUnknown: false);
                        }

                        var listText = StringValue(command, "list_text");
                        var index = IntValue(command, "item_index");
                        if (!items.TryGetValue(code, out var playlistItems))
                        {
                            playlistItems = new SortedDictionary<int, string>();
                            items.Add(code, playlistItems);
                        }

                        playlistItems.Add(index, listText);
                        affected.Add(1);
                        break;
                    }

                    case NamedPlaylistDbCommandKind.DeleteDefinition:
                    {
                        var code = StringValue(command, "program_code");
                        affected.Add(definitions.Remove(code) ? 1 : 0);
                        break;
                    }

                    default:
                        throw new InvalidOperationException("Unexpected mutation contract.");
                }
            }

            _definitions = definitions;
            _items = items;
            return Task.FromResult(new NamedPlaylistDbTransactionResult(
                transaction.OperationId,
                affected,
                DateTimeOffset.UtcNow));
        }

        private DataTable List(DataQuerySpec query)
        {
            var rowLimit = Convert.ToInt32(
                Parameter(query.Parameters, "row_limit").Value,
                CultureInfo.InvariantCulture);
            var table = new DataTable();
            table.Columns.Add("DC_CODE", typeof(string));
            table.Columns.Add("DC_TITLE", typeof(string));
            foreach (var pair in _definitions
                         .OrderBy(static pair => pair.Value, StringComparer.Ordinal)
                         .ThenBy(static pair => pair.Key, StringComparer.Ordinal)
                         .Take(rowLimit))
            {
                table.Rows.Add(pair.Key, pair.Value);
            }

            return table;
        }

        private DataTable NextCode()
        {
            var next = _definitions.Count == 0
                ? 1
                : _definitions.Keys.Max(static code =>
                    int.Parse(code, NumberStyles.None, CultureInfo.InvariantCulture)) + 1;
            var table = new DataTable();
            table.Columns.Add("DC_CODE", typeof(string));
            table.Rows.Add(next.ToString("D8", CultureInfo.InvariantCulture));
            return table;
        }

        private DataTable Load(DataQuerySpec query)
        {
            var code = Convert.ToString(
                Parameter(query.Parameters, "program_code").Value,
                CultureInfo.InvariantCulture)!;
            var rowLimit = Convert.ToInt32(
                Parameter(query.Parameters, "row_limit").Value,
                CultureInfo.InvariantCulture);
            var table = new DataTable();
            table.Columns.Add("DC_CODE", typeof(string));
            table.Columns.Add("DC_TITLE", typeof(string));
            table.Columns.Add("LIST_TEXT", typeof(string));
            table.Columns.Add("ITEM_INDEX", typeof(string));
            if (!_definitions.TryGetValue(code, out var title))
            {
                return table;
            }

            if (!_items.TryGetValue(code, out var playlistItems) || playlistItems.Count == 0)
            {
                table.Rows.Add(code, title, DBNull.Value, DBNull.Value);
                return table;
            }

            foreach (var item in playlistItems.Take(rowLimit))
            {
                table.Rows.Add(
                    code,
                    title,
                    item.Value,
                    item.Key.ToString(CultureInfo.InvariantCulture));
            }

            return table;
        }

        private static string StringValue(NamedPlaylistDbCommand command, string name) =>
            Assert.IsType<string>(CommandParameter(command, name).Value);

        private static int IntValue(NamedPlaylistDbCommand command, string name) =>
            Assert.IsType<int>(CommandParameter(command, name).Value);

        private static NamedPlaylistDbParameter CommandParameter(
            NamedPlaylistDbCommand command,
            string name) =>
            Assert.Single(command.Parameters, parameter =>
                string.Equals(parameter.Name, name, StringComparison.Ordinal));

        private static DataQueryParameter Parameter(
            IReadOnlyList<DataQueryParameter> parameters,
            string name) =>
            Assert.Single(parameters, parameter =>
                string.Equals(parameter.Name, name, StringComparison.Ordinal));
    }
}
