#nullable enable

using System.Data;
using System.Globalization;

namespace MMoneyCoderSharp.Data;

public sealed partial class LegacyThemeCatalogPersistenceService
{
    internal const string StoredKrxQueryName = "THEME_STORED_ITEMS_KRX";
    internal const string StoredNxtQueryName = "THEME_STORED_ITEMS_NXT";

    private static readonly string[] StoredColumns =
    [
        "THEME_TITLE",
        "THEME_CODE",
        "THEME_MARKET",
        "ITEM_NAME",
        "ITEM_CODE",
        "ITEM_INDEX"
    ];

    private const string StoredKrxSql = """
        SELECT THEME_TITLE, THEME_CODE, THEME_MARKET,
               ITEM_NAME, ITEM_CODE, ITEM_INDEX
        FROM (
            SELECT l.SB_TITLE THEME_TITLE,
                   l.SB_CODE THEME_CODE,
                   l.SB_MARKET THEME_MARKET,
                   i.SB_I_NAME ITEM_NAME,
                   i.SB_I_CODE ITEM_CODE,
                   TO_CHAR(i.SB_I_INDEX, 'FM999999990') ITEM_INDEX
            FROM SB_LIST l
            LEFT JOIN SB_ITEM i ON i.SB_M_CODE = l.SB_CODE
            WHERE l.SB_CODE = :theme_code
              AND l.SB_TITLE = :theme_title
              AND l.SB_MARKET = 'KRX'
              AND (SELECT COUNT(*)
                   FROM SB_LIST d
                   WHERE d.SB_MARKET = l.SB_MARKET
                     AND d.SB_CODE = l.SB_CODE) = 1
            ORDER BY i.SB_I_INDEX, i.SB_I_CODE, i.SB_I_NAME
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string StoredNxtSql = """
        SELECT l.SB_TITLE THEME_TITLE,
               l.SB_CODE THEME_CODE,
               l.SB_MARKET THEME_MARKET,
               i.SB_I_NAME ITEM_NAME,
               i.SB_I_CODE ITEM_CODE,
               CAST(i.SB_I_INDEX AS CHAR) ITEM_INDEX
        FROM SB_LIST l
        LEFT JOIN SB_ITEM i ON BINARY i.SB_M_CODE = BINARY l.SB_CODE
        WHERE BINARY l.SB_CODE = BINARY @theme_code
          AND BINARY l.SB_TITLE = BINARY @theme_title
          AND l.SB_MARKET = 'NXT'
          AND (SELECT COUNT(*)
               FROM SB_LIST d
               WHERE d.SB_MARKET = l.SB_MARKET
                 AND BINARY d.SB_CODE = BINARY l.SB_CODE) = 1
        ORDER BY i.SB_I_INDEX, i.SB_I_CODE, i.SB_I_NAME
        LIMIT @row_limit
        """;

    public async Task<ThemeCatalogStoredResult> ReadStoredAsync(
        ThemeSelectionIdentity identity,
        CancellationToken cancellationToken = default)
    {
        var expected = ValidateIdentity(identity, nameof(identity));
        cancellationToken.ThrowIfCancellationRequested();

        var source = expected.Market == ThemeMarket.Krx
            ? DataSourceKind.Oracle
            : DataSourceKind.MariaDb;
        var queryName = expected.Market == ThemeMarket.Krx
            ? StoredKrxQueryName
            : StoredNxtQueryName;
        var rowLimit = checked(MaximumItems + 1);
        var query = new DataQuerySpec(
            expected.Market == ThemeMarket.Krx ? StoredKrxSql : StoredNxtSql,
            [
                new DataQueryParameter("theme_code", expected.ThemeCode, DbType.String),
                new DataQueryParameter("theme_title", expected.ThemeTitle, DbType.String),
                new DataQueryParameter("row_limit", rowLimit, DbType.Int32)
            ]);
        query.ValidateFor(source);
        var table = await _queryExecutor.ExecuteAsync(
            source,
            queryName,
            query,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        var items = MapStoredRows(table, expected, rowLimit, queryName);
        return new ThemeCatalogStoredResult(
            expected,
            DateTimeOffset.Now,
            items.Take(MaximumItems).ToArray(),
            items.Count > MaximumItems);
    }

    private static IReadOnlyList<ThemeCatalogItem> MapStoredRows(
        DataTable? table,
        ThemeSelectionIdentity expected,
        int rowLimit,
        string queryName)
    {
        ValidateStoredSchema(table, rowLimit, queryName);
        if (table!.Rows.Count == 0)
        {
            throw InvalidStoredData(queryName, "missing selected theme");
        }

        var result = new List<ThemeCatalogItem>(table.Rows.Count);
        var sawEmptySentinel = false;
        foreach (DataRow row in table.Rows)
        {
            if (row[0] is not string title || row[1] is not string code ||
                row[2] is not string market ||
                !string.Equals(title, expected.ThemeTitle, StringComparison.Ordinal) ||
                !string.Equals(code, expected.ThemeCode, StringComparison.Ordinal) ||
                !string.Equals(
                    market,
                    expected.Market == ThemeMarket.Krx ? "KRX" : "NXT",
                    StringComparison.Ordinal))
            {
                throw InvalidStoredData(queryName, "selected theme identity");
            }

            var nameValue = row[3];
            var codeValue = row[4];
            var indexValue = row[5];
            if (nameValue is DBNull && codeValue is DBNull && indexValue is DBNull)
            {
                if (table.Rows.Count != 1)
                {
                    throw InvalidStoredData(queryName, "empty item sentinel");
                }

                sawEmptySentinel = true;
                continue;
            }

            if (nameValue is not string itemName || codeValue is not string itemCode ||
                indexValue is not string rawIndex ||
                itemName.Length is < 1 or > 200 || string.IsNullOrWhiteSpace(itemName) ||
                !IsSafeText(itemName) ||
                itemCode.Length is < 2 or > 13 ||
                !string.Equals(itemCode, itemCode.Trim(), StringComparison.Ordinal) ||
                !IsSafeText(itemCode) ||
                !IsClosedStoredItemCode(itemCode) ||
                !int.TryParse(
                    rawIndex,
                    NumberStyles.None,
                    CultureInfo.InvariantCulture,
                    out var inputIndex) ||
                inputIndex is < 0 or > 9_999 ||
                !string.Equals(
                    rawIndex,
                    inputIndex.ToString(CultureInfo.InvariantCulture),
                    StringComparison.Ordinal))
            {
                throw InvalidStoredData(queryName, "stored item");
            }

            result.Add(new ThemeCatalogItem(inputIndex, itemCode, itemName));
        }

        if (sawEmptySentinel)
        {
            return Array.Empty<ThemeCatalogItem>();
        }

        for (var index = 1; index < result.Count; index++)
        {
            if (result[index - 1].InputIndex > result[index].InputIndex)
            {
                throw InvalidStoredData(queryName, "stored item order");
            }
        }

        return result;
    }

    private static void ValidateStoredSchema(
        DataTable? table,
        int rowLimit,
        string queryName)
    {
        if (table is null || table.Columns.Count != StoredColumns.Length ||
            table.Rows.Count > rowLimit)
        {
            throw InvalidStoredData(queryName, "schema or row bound");
        }

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

    private static bool IsClosedStoredItemCode(string value) =>
        KrxItemCodePattern().IsMatch(value) || NxtItemCodePattern().IsMatch(value);

    private static ThemeSelectionDataException InvalidStoredData(
        string queryName,
        string detail) =>
        new($"Stored ThemeA data from {queryName} contains an invalid {detail}.");
}
