#nullable enable

using System.Data;

namespace MMoneyCoderSharp.Data;

public sealed record OperatorCatalogSchemaValidationResult(
    DateTimeOffset ValidatedAt,
    IReadOnlyList<DataSourceKind> ValidatedSources);

public interface IOperatorCatalogSchemaValidationService
{
    Task<OperatorCatalogSchemaValidationResult> ValidateAsync(
        CancellationToken cancellationToken = default);
}

/// <summary>
/// Read-only schema preflight for the four original CRUD tables. Each query is
/// deliberately constrained by WHERE 1 = 0: providers parse and describe all
/// referenced tables/columns, but no catalog row is read or changed.
/// </summary>
public sealed class LegacyOperatorCatalogSchemaValidationService :
    IOperatorCatalogSchemaValidationService
{
    internal const string OracleQueryName = "OPERATOR_CATALOG_SCHEMA_ORACLE";
    internal const string MariaDbQueryName = "OPERATOR_CATALOG_SCHEMA_MARIADB";

    internal const string OracleSql = """
        SELECT l.SB_CODE THEME_CODE,
               l.SB_TITLE THEME_TITLE,
               l.SB_MARKET THEME_MARKET,
               i.SB_M_CODE ITEM_THEME_CODE,
               i.SB_I_CODE ITEM_CODE,
               i.SB_I_NAME ITEM_NAME,
               i.SB_I_INDEX ITEM_INDEX,
               e.EP_CODE EXPERT_CODE,
               e.EP_NAME EXPERT_NAME,
               r.RC_CODE RECOMMEND_EXPERT_CODE,
               r.RC_STOCK_CODE STOCK_CODE,
               r.RC_STOCK_NAME STOCK_NAME,
               r.RC_BUY_AMOUNT BUY_AMOUNT,
               r.PLAYINDEX PLAY_INDEX
        FROM SB_LIST l
        LEFT JOIN SB_ITEM i ON 1 = 0
        LEFT JOIN EXPERT_LIST e ON 1 = 0
        LEFT JOIN RECOMMEND_LIST r ON 1 = 0
        WHERE 1 = 0
        """;

    internal const string MariaDbSql = """
        SELECT l.SB_CODE THEME_CODE,
               l.SB_TITLE THEME_TITLE,
               l.SB_MARKET THEME_MARKET,
               i.SB_M_CODE ITEM_THEME_CODE,
               i.SB_I_CODE ITEM_CODE,
               i.SB_I_NAME ITEM_NAME,
               i.SB_I_INDEX ITEM_INDEX
        FROM SB_LIST l
        LEFT JOIN SB_ITEM i ON 1 = 0
        WHERE 1 = 0
        """;

    private static readonly string[] OracleColumns =
    [
        "THEME_CODE",
        "THEME_TITLE",
        "THEME_MARKET",
        "ITEM_THEME_CODE",
        "ITEM_CODE",
        "ITEM_NAME",
        "ITEM_INDEX",
        "EXPERT_CODE",
        "EXPERT_NAME",
        "RECOMMEND_EXPERT_CODE",
        "STOCK_CODE",
        "STOCK_NAME",
        "BUY_AMOUNT",
        "PLAY_INDEX"
    ];

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

    private readonly IDataQueryExecutor _executor;

    public LegacyOperatorCatalogSchemaValidationService(IDataQueryExecutor executor)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
    }

    public async Task<OperatorCatalogSchemaValidationResult> ValidateAsync(
        CancellationToken cancellationToken = default)
    {
        cancellationToken.ThrowIfCancellationRequested();
        await ValidateSourceAsync(
            DataSourceKind.Oracle,
            OracleQueryName,
            OracleSql,
            OracleColumns,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();
        await ValidateSourceAsync(
            DataSourceKind.MariaDb,
            MariaDbQueryName,
            MariaDbSql,
            MariaDbColumns,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        return new OperatorCatalogSchemaValidationResult(
            DateTimeOffset.Now,
            [DataSourceKind.Oracle, DataSourceKind.MariaDb]);
    }

    private async Task ValidateSourceAsync(
        DataSourceKind source,
        string queryName,
        string sql,
        IReadOnlyList<string> expectedColumns,
        CancellationToken cancellationToken)
    {
        var spec = new DataQuerySpec(sql);
        spec.ValidateFor(source);
        var table = await _executor.ExecuteAsync(
            source,
            queryName,
            spec,
            cancellationToken).ConfigureAwait(false);

        if (table is null || table.Rows.Count != 0 ||
            table.Columns.Count != expectedColumns.Count)
        {
            throw new OperatorCatalogSchemaException(
                source,
                "The read-only schema preflight returned an unexpected shape.");
        }

        for (var index = 0; index < expectedColumns.Count; index++)
        {
            if (!string.Equals(
                    table.Columns[index].ColumnName,
                    expectedColumns[index],
                    StringComparison.Ordinal))
            {
                throw new OperatorCatalogSchemaException(
                    source,
                    "The read-only schema preflight returned an unexpected column.");
            }
        }
    }
}

public sealed class OperatorCatalogSchemaException : Exception
{
    public OperatorCatalogSchemaException(DataSourceKind source, string message)
        : base($"{source} operator-catalog schema validation failed. {message}")
    {
        DataSource = source;
    }

    public DataSourceKind DataSource { get; }
}
