#nullable enable

using System.Data;
using System.Globalization;
using System.Text.RegularExpressions;

namespace MMoneyCoderSharp.Data;

/// <summary>
/// A closed pair of current and persisted NXT theme codes used only by the
/// read-only restore diagnostic. Titles and stock identities never cross this
/// boundary.
/// </summary>
public sealed record LegacyNxtThemeJoinAuditIdentity(
    string CurrentThemeCode,
    string StoredThemeCode);

/// <summary>
/// Aggregate evidence for one current theme code. No theme or stock identifier
/// is returned, so callers can safely log only cardinalities.
/// </summary>
public sealed record LegacyNxtThemeJoinAuditEvidence(
    int InputIndex,
    int RawCurrentItemCount,
    int RawStoredItemCount,
    int CurrentOriginalJoinCount,
    int CurrentNonStoppedJoinCount,
    int CurrentLiveJoinCount,
    int StoredOriginalJoinCount,
    int StoredNonStoppedJoinCount,
    int StoredLiveJoinCount,
    int CurrentExactJoinCount,
    int CurrentStripBothJoinCount,
    int CurrentRightSixJoinCount,
    int CurrentValidItemCodeCount,
    int CurrentKospiMasterJoinCount,
    int CurrentKosdaqMasterJoinCount,
    int CurrentKospiOnlineJoinCount,
    int CurrentKosdaqOnlineJoinCount);

public sealed record LegacyNxtThemeJoinAuditResult(
    DateTimeOffset RetrievedAt,
    IReadOnlyList<LegacyNxtThemeJoinAuditEvidence> Themes);

/// <summary>
/// Executes only bounded aggregate MariaDB reads. The original s5074, s5077,
/// and s5088 NXT loaders all use SUBSTRING(SB_I_CODE, 2, 12) = F_STOCK_CODE;
/// the other joins are diagnostic evidence only and are never used to resolve
/// or play a scene.
/// </summary>
public sealed partial class LegacyNxtThemeJoinAuditService
{
    public const int MaximumThemes = 100;
    public const string QueryName = "LEGACY_NXT_THEME_JOIN_AUDIT";

    private static readonly string[] Columns =
    [
        "RAW_CURRENT_ITEM_COUNT",
        "RAW_STORED_ITEM_COUNT",
        "CURRENT_ORIGINAL_JOIN_COUNT",
        "CURRENT_NON_STOPPED_JOIN_COUNT",
        "CURRENT_LIVE_JOIN_COUNT",
        "STORED_ORIGINAL_JOIN_COUNT",
        "STORED_NON_STOPPED_JOIN_COUNT",
        "STORED_LIVE_JOIN_COUNT",
        "CURRENT_EXACT_JOIN_COUNT",
        "CURRENT_STRIP_BOTH_JOIN_COUNT",
        "CURRENT_RIGHT_SIX_JOIN_COUNT",
        "CURRENT_VALID_ITEM_CODE_COUNT",
        "CURRENT_KOSPI_MASTER_JOIN_COUNT",
        "CURRENT_KOSDAQ_MASTER_JOIN_COUNT",
        "CURRENT_KOSPI_ONLINE_JOIN_COUNT",
        "CURRENT_KOSDAQ_ONLINE_JOIN_COUNT"
    ];

    private const string Sql = """
        SELECT
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE) AS CHAR)
            RAW_CURRENT_ITEM_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                WHERE BINARY i.SB_M_CODE = BINARY p.STORED_THEME_CODE) AS CHAR)
            RAW_STORED_ITEM_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                JOIN v_all_stock q
                  ON SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE) AS CHAR)
            CURRENT_ORIGINAL_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                JOIN v_all_stock q
                  ON SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE
                  AND q.F_STOP_GUBUN = 'N') AS CHAR)
            CURRENT_NON_STOPPED_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                JOIN v_all_stock q
                  ON SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE
                  AND q.F_STOP_GUBUN = 'N'
                  AND q.F_CURR_PRICE != 0) AS CHAR)
            CURRENT_LIVE_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                JOIN v_all_stock q
                  ON SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                WHERE BINARY i.SB_M_CODE = BINARY p.STORED_THEME_CODE) AS CHAR)
            STORED_ORIGINAL_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                JOIN v_all_stock q
                  ON SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                WHERE BINARY i.SB_M_CODE = BINARY p.STORED_THEME_CODE
                  AND q.F_STOP_GUBUN = 'N') AS CHAR)
            STORED_NON_STOPPED_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                JOIN v_all_stock q
                  ON SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                WHERE BINARY i.SB_M_CODE = BINARY p.STORED_THEME_CODE
                  AND q.F_STOP_GUBUN = 'N'
                  AND q.F_CURR_PRICE != 0) AS CHAR)
            STORED_LIVE_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                JOIN v_all_stock q
                  ON i.SB_I_CODE = q.F_STOCK_CODE
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE) AS CHAR)
            CURRENT_EXACT_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                JOIN v_all_stock q
                  ON SUBSTRING(i.SB_I_CODE, 2, 12) =
                     SUBSTRING(q.F_STOCK_CODE, 2, 12)
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE) AS CHAR)
            CURRENT_STRIP_BOTH_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                JOIN v_all_stock q
                  ON RIGHT(i.SB_I_CODE, 6) = RIGHT(q.F_STOCK_CODE, 6)
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE) AS CHAR)
            CURRENT_RIGHT_SIX_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE
                  AND i.SB_I_CODE REGEXP BINARY '^[XT][A-Za-z0-9]{1,12}$') AS CHAR)
            CURRENT_VALID_ITEM_CODE_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE
                  AND EXISTS (
                    SELECT 1
                    FROM N_STOCK q
                    WHERE SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                  )) AS CHAR)
            CURRENT_KOSPI_MASTER_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE
                  AND EXISTS (
                    SELECT 1
                    FROM N_KOSDAQ_STOCK q
                    WHERE SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                  )) AS CHAR)
            CURRENT_KOSDAQ_MASTER_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE
                  AND EXISTS (
                    SELECT 1
                    FROM N_ONLINE q
                    WHERE SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                  )) AS CHAR)
            CURRENT_KOSPI_ONLINE_JOIN_COUNT,
          CAST((SELECT COUNT(*)
                FROM SB_ITEM i
                WHERE BINARY i.SB_M_CODE = BINARY p.CURRENT_THEME_CODE
                  AND EXISTS (
                    SELECT 1
                    FROM N_KOSDAQ_ONLINE q
                    WHERE SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                  )) AS CHAR)
            CURRENT_KOSDAQ_ONLINE_JOIN_COUNT
        FROM (
          SELECT @current_theme_code CURRENT_THEME_CODE,
                 @stored_theme_code STORED_THEME_CODE
        ) p
        """;

    private readonly IDataQueryExecutor _executor;
    private readonly TimeProvider _timeProvider;

    public LegacyNxtThemeJoinAuditService(IDataQueryExecutor executor)
        : this(executor, TimeProvider.System)
    {
    }

    internal LegacyNxtThemeJoinAuditService(
        IDataQueryExecutor executor,
        TimeProvider timeProvider)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
        _timeProvider = timeProvider ?? throw new ArgumentNullException(nameof(timeProvider));
    }

    public async Task<LegacyNxtThemeJoinAuditResult> AuditAsync(
        IReadOnlyList<LegacyNxtThemeJoinAuditIdentity> identities,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(identities);
        if (identities.Count is < 1 or > MaximumThemes)
        {
            throw new ArgumentOutOfRangeException(
                nameof(identities),
                $"A join audit must contain 1 through {MaximumThemes} themes.");
        }

        var currentCodes = new HashSet<string>(StringComparer.Ordinal);
        foreach (var identity in identities)
        {
            var current = identity?.CurrentThemeCode;
            var stored = identity?.StoredThemeCode;
            if (!ThemeCodePattern().IsMatch(current ?? string.Empty) ||
                !ThemeCodePattern().IsMatch(stored ?? string.Empty) ||
                !currentCodes.Add(current!))
            {
                throw new ArgumentException(
                    "NXT join audit identities must have unique canonical current codes.",
                    nameof(identities));
            }
        }

        var evidence = new List<LegacyNxtThemeJoinAuditEvidence>(identities.Count);
        for (var index = 0; index < identities.Count; index++)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var identity = identities[index];
            var spec = new DataQuerySpec(
                Sql,
                [
                    new DataQueryParameter(
                        "current_theme_code",
                        identity.CurrentThemeCode,
                        DbType.String),
                    new DataQueryParameter(
                        "stored_theme_code",
                        identity.StoredThemeCode,
                        DbType.String)
                ]);
            spec.ValidateFor(DataSourceKind.MariaDb);
            var table = await _executor.ExecuteAsync(
                    DataSourceKind.MariaDb,
                    QueryName,
                    spec,
                    cancellationToken)
                .ConfigureAwait(false);
            cancellationToken.ThrowIfCancellationRequested();
            evidence.Add(MapEvidence(table, index));
        }

        return new LegacyNxtThemeJoinAuditResult(
            _timeProvider.GetLocalNow(),
            evidence.AsReadOnly());
    }

    private static LegacyNxtThemeJoinAuditEvidence MapEvidence(
        DataTable? table,
        int inputIndex)
    {
        if (table is null || table.Columns.Count != Columns.Length || table.Rows.Count != 1)
        {
            throw InvalidData();
        }

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

        var counts = table.Rows[0].ItemArray.Select(ReadCount).ToArray();
        if (counts[2] > counts[0] || counts[3] > counts[2] || counts[4] > counts[3] ||
            counts[5] > counts[1] || counts[6] > counts[5] || counts[7] > counts[6] ||
            counts[8] > counts[0] || counts[9] > counts[0] || counts[10] > counts[0] ||
            counts[11] > counts[0] || counts[12] > counts[0] || counts[13] > counts[0] ||
            counts[14] > counts[0] || counts[15] > counts[0] ||
            counts[12] + counts[13] > counts[0] ||
            counts[14] + counts[15] > counts[0])
        {
            throw InvalidData();
        }

        return new LegacyNxtThemeJoinAuditEvidence(
            inputIndex,
            counts[0],
            counts[1],
            counts[2],
            counts[3],
            counts[4],
            counts[5],
            counts[6],
            counts[7],
            counts[8],
            counts[9],
            counts[10],
            counts[11],
            counts[12],
            counts[13],
            counts[14],
            counts[15]);
    }

    private static int ReadCount(object? value)
    {
        if (value is not string text ||
            !int.TryParse(text, NumberStyles.None, CultureInfo.InvariantCulture, out var count) ||
            count is < 0 or > 1_000_000 ||
            !string.Equals(text, count.ToString(CultureInfo.InvariantCulture), StringComparison.Ordinal))
        {
            throw InvalidData();
        }

        return count;
    }

    private static InvalidDataException InvalidData() =>
        new("The NXT theme join audit returned invalid aggregate data.");

    [GeneratedRegex("^[0-9]{3,8}$", RegexOptions.CultureInvariant)]
    private static partial Regex ThemeCodePattern();
}
