#nullable enable

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

namespace MMoneyCoderSharp.Data;

/// <summary>
/// The page value stored in the sixth caret-delimited field of PLAY_LIST.LIST_TEXT.
/// The original application displays pages as one-based values such as 1/4.
/// Historical rows can contain 1/0 or totals above the current 20-page playout
/// bound. They are preserved for compatibility but are never authoritative for
/// scene paging; scene data must calculate the current page plan again.
/// </summary>
public sealed record NamedPlaylistPageState
{
    public NamedPlaylistPageState(int currentPage, int totalPages)
    {
        if (totalPages is < 0 or > LegacyNamedPlaylistPersistenceService.MaximumStoredPageCount ||
            currentPage < 1 ||
            totalPages == 0 && currentPage != 1 ||
            totalPages > 0 && currentPage > totalPages)
        {
            throw new ArgumentOutOfRangeException(
                nameof(currentPage),
                "A stored named-playlist page value is invalid.");
        }

        CurrentPage = currentPage;
        TotalPages = totalPages;
    }

    public int CurrentPage { get; }

    public int TotalPages { get; }

    /// <summary>
    /// True only when the stored display value is compatible with the current
    /// maximum-20-page scene contract. False requires a fresh DB page plan before
    /// PREPARE and must not be treated as a playable page count.
    /// </summary>
    public bool IsWithinPlayoutBounds =>
        TotalPages is >= 1 and <= LegacyNamedPlaylistPersistenceService.MaximumPageCount &&
        CurrentPage <= TotalPages;

    public override string ToString() =>
        string.Create(
            CultureInfo.InvariantCulture,
            $"{CurrentPage}/{TotalPages}");
}

/// <summary>
/// A typed representation of one original FarPoint playlist row. ItemIndex is
/// always zero-based and contiguous. Selection maps exactly to original columns
/// 1, 2, 3, 4 and hidden column 6; Page maps to column 5.
/// </summary>
public sealed record NamedPlaylistStoredItem(
    int ItemIndex,
    bool IsEnabled,
    LegacySceneSelection Selection,
    NamedPlaylistPageState? Page);

public sealed record NamedPlaylistSummary(string ProgramCode, string Title);

public sealed record NamedPlaylistListResult(
    DateTimeOffset RetrievedAt,
    IReadOnlyList<NamedPlaylistSummary> Items,
    bool IsTruncated);

public sealed record NamedPlaylistDocument(
    NamedPlaylistSummary Definition,
    DateTimeOffset RetrievedAt,
    IReadOnlyList<NamedPlaylistStoredItem> Items);

public enum NamedPlaylistMutationKind
{
    CreateDefinition,
    ReplaceItems,
    DeletePlaylist
}

public enum NamedPlaylistDbCommandKind
{
    InsertDefinition,
    DeleteItems,
    InsertItem,
    DeleteDefinition
}

/// <summary>
/// One provider-neutral bind value for a fixed named-playlist mutation command.
/// Values are deliberately not included in ToString so diagnostics cannot
/// accidentally disclose operator-entered content.
/// </summary>
public sealed class NamedPlaylistDbParameter
{
    internal NamedPlaylistDbParameter(string name, object? value, DbType dbType)
    {
        if (!DataQueryParameter.IsValidName(name) || !Enum.IsDefined(dbType))
        {
            throw new ArgumentException("A named-playlist 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() => "Named-playlist database parameter";
}

/// <summary>
/// A command from the closed Oracle DC_LIST/PLAY_LIST command set. Callers
/// cannot construct arbitrary SQL; the persistence service creates commands
/// from fixed statements and bound values only.
/// </summary>
public sealed class NamedPlaylistDbCommand
{
    internal NamedPlaylistDbCommand(
        NamedPlaylistDbCommandKind kind,
        string sql,
        IEnumerable<NamedPlaylistDbParameter> parameters)
    {
        Kind = kind;
        Sql = sql;
        Parameters = Array.AsReadOnly(parameters.ToArray());
    }

    public NamedPlaylistDbCommandKind Kind { get; }

    public string Sql { get; }

    public IReadOnlyList<NamedPlaylistDbParameter> Parameters { get; }

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

/// <summary>
/// An ordered command batch that must be executed once on one Oracle connection
/// and committed as one transaction. An executor must never retry a transaction
/// after a timeout or an otherwise ambiguous result.
/// </summary>
public sealed class NamedPlaylistDbTransaction
{
    internal NamedPlaylistDbTransaction(
        Guid operationId,
        NamedPlaylistMutationKind kind,
        string programCode,
        IEnumerable<NamedPlaylistDbCommand> commands)
    {
        OperationId = operationId;
        Kind = kind;
        ProgramCode = programCode;
        Commands = Array.AsReadOnly(commands.ToArray());
    }

    public Guid OperationId { get; }

    public NamedPlaylistMutationKind Kind { get; }

    public string ProgramCode { get; }

    public IReadOnlyList<NamedPlaylistDbCommand> Commands { get; }

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

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

/// <summary>
/// Infrastructure seam for state-changing Oracle work. Returning means the
/// transaction committed. A known rollback or an ambiguous outcome must be
/// reported with NamedPlaylistMutationException and the appropriate flag.
/// </summary>
public interface INamedPlaylistMutationExecutor
{
    Task<NamedPlaylistDbTransactionResult> ExecuteTransactionAsync(
        DataSourceKind source,
        NamedPlaylistDbTransaction transaction,
        CancellationToken cancellationToken = default);
}

public interface INamedPlaylistPersistenceService
{
    bool CanMutate { get; }

    Task<NamedPlaylistListResult> ListAsync(
        int maximumResults = LegacyNamedPlaylistPersistenceService.DefaultMaximumDefinitions,
        CancellationToken cancellationToken = default);

    Task<string> SuggestNextProgramCodeAsync(
        CancellationToken cancellationToken = default);

    Task<NamedPlaylistDocument> LoadAsync(
        string programCode,
        CancellationToken cancellationToken = default);

    Task CreateDefinitionAsync(
        string programCode,
        string title,
        CancellationToken cancellationToken = default);

    Task ReplaceItemsAsync(
        string programCode,
        IReadOnlyList<NamedPlaylistStoredItem> items,
        CancellationToken cancellationToken = default);

    Task DeleteAsync(
        string programCode,
        CancellationToken cancellationToken = default);
}

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

public sealed class NamedPlaylistNotFoundException : NamedPlaylistDataException
{
    public NamedPlaylistNotFoundException(string programCode)
        : base($"Named playlist {programCode} does not exist.")
    {
    }
}

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

    public bool OutcomeUnknown { get; }
}

/// <summary>
/// Typed migration of AList/PList. Reads retain the original DC_LIST title sort,
/// PLAY_LIST item-index sort and seven-field LIST_TEXT representation. Writes
/// preserve that representation but make replace/delete atomic and bind every
/// operator value instead of concatenating SQL.
/// </summary>
public sealed class LegacyNamedPlaylistPersistenceService : INamedPlaylistPersistenceService
{
    public const int DefaultMaximumDefinitions = 200;
    public const int MaximumDefinitions = 1_000;
    public const int MaximumItems = 1_000;
    public const int MaximumPageCount = 20;
    public const int MaximumStoredPageCount = 9_999;

    private const int MaximumTitleLength = 128;
    private const int MaximumSelectionFieldLength = 256;
    private const int MaximumListTextLength = 4_000;
    private const int MaximumSubjectFieldLength = MaximumListTextLength;

    internal const string ListQueryName = "NAMED_PLAYLIST_LIST";
    internal const string NextCodeQueryName = "NAMED_PLAYLIST_NEXT_CODE";
    internal const string LoadQueryName = "NAMED_PLAYLIST_LOAD";

    internal const string ListSql =
        """
        SELECT DC_CODE, DC_TITLE
        FROM (
            SELECT TRIM(DC_CODE) DC_CODE,
                   DC_TITLE
            FROM DC_LIST
            ORDER BY DC_TITLE, DC_CODE
        )
        WHERE ROWNUM <= :row_limit
        """;

    internal const string NextCodeSql =
        """
        SELECT LPAD(
                   TO_CHAR(NVL(MAX(TO_NUMBER(TRIM(DC_CODE))), 0) + 1),
                   8,
                   '0') DC_CODE
        FROM DC_LIST
        """;

    internal const string LoadSql =
        """
        SELECT DC_CODE, DC_TITLE, LIST_TEXT, ITEM_INDEX
        FROM (
            SELECT TRIM(d.DC_CODE) DC_CODE,
                   d.DC_TITLE DC_TITLE,
                   p.LIST_TEXT LIST_TEXT,
                   TO_CHAR(TO_NUMBER(p.ITEM_INDEX)) ITEM_INDEX
            FROM DC_LIST d
            LEFT JOIN PLAY_LIST p
              ON p.PG_CODE = d.DC_CODE
            WHERE d.DC_CODE = :program_code
            ORDER BY TO_NUMBER(p.ITEM_INDEX)
        )
        WHERE ROWNUM <= :row_limit
        """;

    internal const string InsertDefinitionSql =
        "INSERT INTO DC_LIST(DC_CODE, DC_TITLE) VALUES (:program_code, :program_title)";

    internal const string DeleteItemsSql =
        "DELETE FROM PLAY_LIST WHERE PG_CODE = :program_code";

    internal const string InsertItemSql =
        """
        INSERT INTO PLAY_LIST(
            PG_CODE,
            LIST_TEXT,
            ITEM_NAME,
            ITEM_CODE,
            ITEM_DETAIL,
            ITEM_TYPE,
            ITEM_ECUT,
            ITEM_ESTOCK,
            ITEM_COUNT,
            ITEM_INPUT,
            ITEM_INDEX)
        VALUES (
            :program_code,
            :list_text,
            :item_name,
            :item_code,
            :item_detail,
            :item_type,
            :item_ecut,
            :item_estock,
            :item_count,
            :item_input,
            :item_index)
        """;

    internal const string DeleteDefinitionSql =
        "DELETE FROM DC_LIST WHERE DC_CODE = :program_code";

    private static readonly string[] ListColumns = ["DC_CODE", "DC_TITLE"];
    private static readonly string[] NextCodeColumns = ["DC_CODE"];
    private static readonly string[] LoadColumns =
        ["DC_CODE", "DC_TITLE", "LIST_TEXT", "ITEM_INDEX"];

    private readonly IDataQueryExecutor _queryExecutor;
    private readonly INamedPlaylistMutationExecutor? _mutationExecutor;

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

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

    public bool CanMutate => _mutationExecutor is not null;

    public async Task<NamedPlaylistListResult> ListAsync(
        int maximumResults = DefaultMaximumDefinitions,
        CancellationToken cancellationToken = default)
    {
        ValidateLimit(maximumResults, MaximumDefinitions, nameof(maximumResults));
        cancellationToken.ThrowIfCancellationRequested();

        var rowLimit = checked(maximumResults + 1);
        var spec = new DataQuerySpec(
            ListSql,
            [new DataQueryParameter("row_limit", rowLimit, DbType.Int32)]);
        spec.ValidateFor(DataSourceKind.Oracle);
        var table = await _queryExecutor.ExecuteAsync(
            DataSourceKind.Oracle,
            ListQueryName,
            spec,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        ValidateExactStringSchema(table, ListColumns, rowLimit, ListQueryName);
        var definitions = new List<NamedPlaylistSummary>(table.Rows.Count);
        var codes = new HashSet<string>(StringComparer.Ordinal);
        foreach (DataRow row in table.Rows)
        {
            var code = ReadProgramCode(row, 0, ListQueryName);
            var title = ReadCanonicalString(
                row,
                1,
                MaximumTitleLength,
                allowEmpty: false,
                allowCaret: true,
                ListQueryName);
            if (!codes.Add(code))
            {
                throw InvalidData(ListQueryName, "duplicate program code");
            }

            definitions.Add(new NamedPlaylistSummary(code, title));
        }

        return new NamedPlaylistListResult(
            DateTimeOffset.Now,
            definitions.Take(maximumResults).ToArray(),
            definitions.Count > maximumResults);
    }

    public async Task<string> SuggestNextProgramCodeAsync(
        CancellationToken cancellationToken = default)
    {
        cancellationToken.ThrowIfCancellationRequested();
        var spec = new DataQuerySpec(NextCodeSql);
        spec.ValidateFor(DataSourceKind.Oracle);
        var table = await _queryExecutor.ExecuteAsync(
            DataSourceKind.Oracle,
            NextCodeQueryName,
            spec,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        ValidateExactStringSchema(table, NextCodeColumns, 1, NextCodeQueryName);
        if (table.Rows.Count != 1)
        {
            throw InvalidData(NextCodeQueryName, "result cardinality");
        }

        return ReadProgramCode(table.Rows[0], 0, NextCodeQueryName);
    }

    public async Task<NamedPlaylistDocument> LoadAsync(
        string programCode,
        CancellationToken cancellationToken = default)
    {
        var code = ValidateProgramCode(programCode, nameof(programCode));
        cancellationToken.ThrowIfCancellationRequested();

        var rowLimit = MaximumItems + 1;
        var spec = new DataQuerySpec(
            LoadSql,
            [
                new DataQueryParameter("program_code", code, DbType.String),
                new DataQueryParameter("row_limit", rowLimit, DbType.Int32)
            ]);
        spec.ValidateFor(DataSourceKind.Oracle);
        var table = await _queryExecutor.ExecuteAsync(
            DataSourceKind.Oracle,
            LoadQueryName,
            spec,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        ValidateExactStringSchema(table, LoadColumns, rowLimit, LoadQueryName);
        if (table.Rows.Count == 0)
        {
            throw new NamedPlaylistNotFoundException(code);
        }

        if (table.Rows.Count > MaximumItems)
        {
            throw InvalidData(LoadQueryName, "item count");
        }

        NamedPlaylistSummary? definition = null;
        var items = new List<NamedPlaylistStoredItem>(table.Rows.Count);
        var sawEmptySentinel = false;
        foreach (DataRow row in table.Rows)
        {
            var rowCode = ReadProgramCode(row, 0, LoadQueryName);
            var title = ReadCanonicalString(
                row,
                1,
                MaximumTitleLength,
                allowEmpty: false,
                allowCaret: true,
                LoadQueryName);
            if (!string.Equals(code, rowCode, StringComparison.Ordinal))
            {
                throw InvalidData(LoadQueryName, "program identity");
            }

            var currentDefinition = new NamedPlaylistSummary(rowCode, title);
            if (definition is null)
            {
                definition = currentDefinition;
            }
            else if (definition != currentDefinition)
            {
                throw InvalidData(LoadQueryName, "program definition");
            }

            var listTextValue = row[2];
            var itemIndexValue = row[3];
            if (listTextValue is DBNull && itemIndexValue is DBNull)
            {
                if (table.Rows.Count != 1)
                {
                    throw InvalidData(LoadQueryName, "empty-playlist sentinel");
                }

                sawEmptySentinel = true;
                continue;
            }

            if (listTextValue is not string listText || itemIndexValue is not string itemIndexText)
            {
                throw InvalidData(LoadQueryName, "partial item row");
            }

            if (listText.Length is < 1 or > MaximumListTextLength ||
                !int.TryParse(
                    itemIndexText,
                    NumberStyles.None,
                    CultureInfo.InvariantCulture,
                    out var itemIndex) ||
                itemIndex < 0 ||
                itemIndex >= MaximumItems ||
                !string.Equals(
                    itemIndexText,
                    itemIndex.ToString(CultureInfo.InvariantCulture),
                    StringComparison.Ordinal))
            {
                throw InvalidData(LoadQueryName, "item index or text");
            }

            items.Add(ParseItem(itemIndex, listText, LoadQueryName));
        }

        if (sawEmptySentinel)
        {
            items.Clear();
        }

        for (var index = 0; index < items.Count; index++)
        {
            if (items[index].ItemIndex != index)
            {
                throw InvalidData(LoadQueryName, "non-contiguous item order");
            }
        }

        return new NamedPlaylistDocument(
            definition!,
            DateTimeOffset.Now,
            items.ToArray());
    }

    public Task CreateDefinitionAsync(
        string programCode,
        string title,
        CancellationToken cancellationToken = default)
    {
        var code = ValidateProgramCode(programCode, nameof(programCode));
        var canonicalTitle = ValidateText(
            title,
            MaximumTitleLength,
            allowEmpty: false,
            allowCaret: true,
            nameof(title));
        var transaction = new NamedPlaylistDbTransaction(
            Guid.NewGuid(),
            NamedPlaylistMutationKind.CreateDefinition,
            code,
            [
                Command(
                    NamedPlaylistDbCommandKind.InsertDefinition,
                    InsertDefinitionSql,
                    StringParameter("program_code", code),
                    StringParameter("program_title", canonicalTitle))
            ]);
        return ExecuteMutationAsync(transaction, cancellationToken);
    }

    public Task ReplaceItemsAsync(
        string programCode,
        IReadOnlyList<NamedPlaylistStoredItem> items,
        CancellationToken cancellationToken = default)
    {
        var code = ValidateProgramCode(programCode, nameof(programCode));
        ArgumentNullException.ThrowIfNull(items);
        if (items.Count > MaximumItems)
        {
            throw new ArgumentOutOfRangeException(
                nameof(items),
                $"A named playlist can contain at most {MaximumItems} items.");
        }

        var commands = new List<NamedPlaylistDbCommand>(items.Count + 1)
        {
            Command(
                NamedPlaylistDbCommandKind.DeleteItems,
                DeleteItemsSql,
                StringParameter("program_code", code))
        };

        for (var index = 0; index < items.Count; index++)
        {
            var item = ValidateItem(items[index], index, nameof(items));
            var listText = SerializeItem(item);
            commands.Add(Command(
                NamedPlaylistDbCommandKind.InsertItem,
                InsertItemSql,
                StringParameter("program_code", code),
                StringParameter("list_text", listText),
                NullStringParameter("item_name"),
                NullStringParameter("item_code"),
                NullStringParameter("item_detail"),
                NullStringParameter("item_type"),
                NullStringParameter("item_ecut"),
                NullStringParameter("item_estock"),
                NullStringParameter("item_count"),
                NullStringParameter("item_input"),
                new NamedPlaylistDbParameter("item_index", index, DbType.Int32)));
        }

        var transaction = new NamedPlaylistDbTransaction(
            Guid.NewGuid(),
            NamedPlaylistMutationKind.ReplaceItems,
            code,
            commands);
        return ExecuteMutationAsync(transaction, cancellationToken);
    }

    public Task DeleteAsync(
        string programCode,
        CancellationToken cancellationToken = default)
    {
        var code = ValidateProgramCode(programCode, nameof(programCode));
        // The original PList deletes DC_LIST only and leaves orphaned PLAY_LIST
        // rows. The migrated contract removes children first in the same transaction.
        var transaction = new NamedPlaylistDbTransaction(
            Guid.NewGuid(),
            NamedPlaylistMutationKind.DeletePlaylist,
            code,
            [
                Command(
                    NamedPlaylistDbCommandKind.DeleteItems,
                    DeleteItemsSql,
                    StringParameter("program_code", code)),
                Command(
                    NamedPlaylistDbCommandKind.DeleteDefinition,
                    DeleteDefinitionSql,
                    StringParameter("program_code", code))
            ]);
        return ExecuteMutationAsync(transaction, cancellationToken);
    }

    internal static string SerializeItem(NamedPlaylistStoredItem item)
    {
        ArgumentNullException.ThrowIfNull(item);
        ArgumentNullException.ThrowIfNull(item.Selection);
        return string.Join(
            '^',
            item.IsEnabled ? "1" : "0",
            item.Selection.GroupCode,
            item.Selection.Subject,
            item.Selection.GraphicType,
            item.Selection.Subtype,
            item.Page?.ToString() ?? string.Empty,
            item.Selection.DataCode);
    }

    private async Task ExecuteMutationAsync(
        NamedPlaylistDbTransaction transaction,
        CancellationToken cancellationToken)
    {
        var executor = _mutationExecutor ?? throw new InvalidOperationException(
            "Named-playlist persistence is configured for read-only access.");
        cancellationToken.ThrowIfCancellationRequested();

        NamedPlaylistDbTransactionResult result;
        try
        {
            result = await executor.ExecuteTransactionAsync(
                DataSourceKind.Oracle,
                transaction,
                cancellationToken).ConfigureAwait(false);
        }
        catch (NamedPlaylistMutationException)
        {
            throw;
        }
        catch (Exception exception)
        {
            // The executor did not provide a known-rollback indication. Treat the
            // outcome conservatively and never invite an automatic retry.
            throw new NamedPlaylistMutationException(
                $"The {transaction.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 != transaction.Commands.Count ||
            result.AffectedRows.Any(static count => count < 0))
        {
            throw new NamedPlaylistMutationException(
                $"The {transaction.Kind} transaction returned an ambiguous commit result; do not retry automatically.",
                outcomeUnknown: true);
        }

        for (var index = 0; index < transaction.Commands.Count; index++)
        {
            var command = transaction.Commands[index];
            var affectedRows = result.AffectedRows[index];
            if (command.Kind is (NamedPlaylistDbCommandKind.InsertDefinition or
                    NamedPlaylistDbCommandKind.InsertItem) &&
                affectedRows != 1)
            {
                throw new NamedPlaylistMutationException(
                    $"The {transaction.Kind} transaction returned an ambiguous inserted-row count; do not retry automatically.",
                    outcomeUnknown: true);
            }

            if (command.Kind == NamedPlaylistDbCommandKind.DeleteDefinition && affectedRows != 1)
            {
                if (affectedRows == 0)
                {
                    throw new NamedPlaylistNotFoundException(transaction.ProgramCode);
                }

                throw new NamedPlaylistMutationException(
                    $"The {transaction.Kind} transaction returned an ambiguous deleted-row count; do not retry automatically.",
                    outcomeUnknown: true);
            }
        }
    }

    private static NamedPlaylistStoredItem ParseItem(
        int itemIndex,
        string listText,
        string queryName)
    {
        if (!IsSafeText(listText))
        {
            throw InvalidData(queryName, "LIST_TEXT value");
        }

        var fields = listText.Split('^');
        if (fields.Length != 7 || fields[0] is not ("0" or "1"))
        {
            throw InvalidData(queryName, "LIST_TEXT shape");
        }

        var selection = new LegacySceneSelection(
            ValidateLoadedField(fields[1], MaximumSelectionFieldLength, queryName),
            ValidateLoadedField(fields[2], MaximumSubjectFieldLength, queryName),
            ValidateLoadedField(fields[3], MaximumSelectionFieldLength, queryName),
            ValidateLoadedField(fields[4], MaximumSelectionFieldLength, queryName),
            ValidateLoadedField(fields[6], MaximumSelectionFieldLength, queryName));
        var page = ParsePage(fields[5], queryName);
        return new NamedPlaylistStoredItem(itemIndex, fields[0] == "1", selection, page);
    }

    private static NamedPlaylistPageState? ParsePage(string value, string queryName)
    {
        // PList.data_load removes ASCII spaces from the page column only.
        var canonical = value.Replace(" ", string.Empty, StringComparison.Ordinal);
        if (canonical.Length == 0)
        {
            return null;
        }

        var separator = canonical.IndexOf('/');
        if (separator < 1 || separator != canonical.LastIndexOf('/') ||
            !int.TryParse(
                canonical.AsSpan(0, separator),
                NumberStyles.None,
                CultureInfo.InvariantCulture,
                out var currentPage) ||
            !int.TryParse(
                canonical.AsSpan(separator + 1),
                NumberStyles.None,
                CultureInfo.InvariantCulture,
                out var totalPages))
        {
            throw InvalidData(queryName, "page value");
        }

        try
        {
            return new NamedPlaylistPageState(currentPage, totalPages);
        }
        catch (ArgumentOutOfRangeException exception)
        {
            throw new NamedPlaylistDataException(
                $"Named-playlist data from {queryName} contains an invalid page value: {exception.Message}");
        }
    }

    private static NamedPlaylistStoredItem ValidateItem(
        NamedPlaylistStoredItem? item,
        int expectedIndex,
        string parameterName)
    {
        if (item is null || item.ItemIndex != expectedIndex || item.Selection is null)
        {
            throw new ArgumentException(
                "Named-playlist items must have contiguous zero-based indexes.",
                parameterName);
        }

        var selection = item.Selection;
        var groupCode = ValidateText(
            selection.GroupCode,
            MaximumSelectionFieldLength,
            allowEmpty: true,
            allowCaret: false,
            parameterName);
        var dataCode = ValidateText(
            selection.DataCode,
            MaximumSelectionFieldLength,
            allowEmpty: true,
            allowCaret: false,
            parameterName);
        var subject = CanPreserveClosedThemeSubject(
                groupCode,
                selection.Subject,
                dataCode)
            ? selection.Subject
            : ValidateText(
                selection.Subject,
                MaximumSubjectFieldLength,
                allowEmpty: true,
                allowCaret: false,
                parameterName);
        var canonical = new LegacySceneSelection(
            groupCode,
            subject,
            ValidateText(
                selection.GraphicType,
                MaximumSelectionFieldLength,
                allowEmpty: true,
                allowCaret: false,
                parameterName),
            ValidateText(
                selection.Subtype,
                MaximumSelectionFieldLength,
                allowEmpty: true,
                allowCaret: false,
                parameterName),
            dataCode);
        var validated = item with { Selection = canonical };
        if (SerializeItem(validated).Length > MaximumListTextLength)
        {
            throw new ArgumentException(
                $"A serialized named-playlist item must contain at most {MaximumListTextLength} characters.",
                parameterName);
        }

        return validated;
    }

    private static string ValidateLoadedField(
        string value,
        int maximumLength,
        string queryName)
    {
        if (value.Length > maximumLength || !IsSafeText(value))
        {
            throw InvalidData(queryName, "selection field");
        }

        return value;
    }

    private static string ValidateProgramCode(string value, string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value);
        if (value.Length != 8 || value.Any(static character => !char.IsAsciiDigit(character)))
        {
            throw new ArgumentException(
                "A named-playlist program code must contain exactly eight digits.",
                parameterName);
        }

        return value;
    }

    private static string ValidateText(
        string value,
        int maximumLength,
        bool allowEmpty,
        bool allowCaret,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value);
        if ((!allowEmpty && value.Length == 0) ||
            value.Length > maximumLength ||
            !string.Equals(value, value.Trim(), StringComparison.Ordinal) ||
            !IsSafeText(value) ||
            (!allowCaret && value.Contains('^')))
        {
            throw new ArgumentException(
                "A named-playlist text value is invalid.",
                parameterName);
        }

        return value;
    }

    private static bool CanPreserveClosedThemeSubject(
        string groupCode,
        string subject,
        string dataCode)
    {
        if (dataCode.Length is < 3 or > 8 ||
            dataCode.Any(character => !char.IsAsciiDigit(character)))
        {
            return false;
        }

        var isNxt = groupCode is "테마_NXT" or "PAGED_NXT_THEME";
        if (!isNxt && groupCode is not ("테마" or "PAGED_THEME"))
        {
            return false;
        }

        const string suffix = "(NXT)";
        var exactTitle = isNxt && subject.EndsWith(suffix, StringComparison.Ordinal)
            ? subject[..^suffix.Length]
            : isNxt ? string.Empty : subject;
        return exactTitle.Length is >= 1 and <= 128 &&
            !string.IsNullOrWhiteSpace(exactTitle) &&
            !subject.Contains('^') &&
            IsSafeText(subject);
    }

    private static bool IsSafeText(string value) =>
        !value.Any(static character =>
            char.IsControl(character) ||
            char.IsSurrogate(character) ||
            character == '\uFFFD');

    private static string ReadProgramCode(DataRow row, int ordinal, string queryName)
    {
        if (row[ordinal] is not string value)
        {
            throw InvalidData(queryName, "program code");
        }

        try
        {
            return ValidateProgramCode(value, nameof(value));
        }
        catch (ArgumentException)
        {
            throw InvalidData(queryName, "program code");
        }
    }

    private static string ReadCanonicalString(
        DataRow row,
        int ordinal,
        int maximumLength,
        bool allowEmpty,
        bool allowCaret,
        string queryName)
    {
        if (row[ordinal] is not string value)
        {
            throw InvalidData(queryName, "text value");
        }

        try
        {
            return ValidateText(
                value,
                maximumLength,
                allowEmpty,
                allowCaret,
                nameof(value));
        }
        catch (ArgumentException)
        {
            throw InvalidData(queryName, "text value");
        }
    }

    private static void ValidateExactStringSchema(
        DataTable? table,
        IReadOnlyList<string> columns,
        int rowLimit,
        string queryName)
    {
        if (table is null || table.Columns.Count != columns.Count || table.Rows.Count > rowLimit)
        {
            throw InvalidData(queryName, "schema or row bound");
        }

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

    private static void ValidateLimit(int value, int maximum, string parameterName)
    {
        if (value is < 1 || value > maximum)
        {
            throw new ArgumentOutOfRangeException(
                parameterName,
                value,
                $"The requested limit must be between 1 and {maximum}.");
        }
    }

    private static NamedPlaylistDbCommand Command(
        NamedPlaylistDbCommandKind kind,
        string sql,
        params NamedPlaylistDbParameter[] parameters) =>
        new(kind, sql, parameters);

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

    private static NamedPlaylistDbParameter NullStringParameter(string name) =>
        new(name, null, DbType.String);

    private static NamedPlaylistDataException InvalidData(string queryName, string detail) =>
        new($"Named-playlist data from {queryName} contains an invalid {detail}.");
}
