using System.Data;
using System.Globalization;
using System.Security.Cryptography;
using MBN_STOCK_WEBVIEW.Core.Playout.Scenes;
using MBN_STOCK_WEBVIEW.Infrastructure;
using MMoneyCoderSharp.Data;

public sealed record NamedManualRestoreReadOnlyAuditSummary(
    int CandidateCount,
    int FreshMaterializedCount,
    int BlockedCount,
    LegacyManualOperatorDataRuntimeState RuntimeState,
    bool OperatorFilesStable,
    IReadOnlyDictionary<LegacyNamedManualRestoreKind, int> KindCounts,
    IReadOnlyDictionary<string, int> BlockReasons);

/// <summary>
/// Production-data audit for the manual subset of PLAY_LIST. Every database
/// command is a SELECT through an IDataQueryExecutor. Operator files are opened
/// only through their production read paths; the before/after snapshot proves
/// that no file, marker, or intent entry was created or changed.
/// </summary>
public static class NamedManualRestoreReadOnlyAudit
{
    private const string QueryName = "AUDIT_NAMED_MANUAL_RESTORE_ROWS";
    private const int MaximumRows = 20_000;
    private const int BrowserStockSearchLimit = 200;

    private const string Sql = """
        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
            JOIN PLAY_LIST p ON p.PG_CODE = d.DC_CODE
            ORDER BY TRIM(d.DC_CODE), TO_NUMBER(p.ITEM_INDEX)
        )
        WHERE ROWNUM <= :row_limit
        """;

    public static async Task<NamedManualRestoreReadOnlyAuditSummary> RunAsync(
        IDataQueryExecutor executor,
        string trustedDirectory,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(executor);
        if (string.IsNullOrWhiteSpace(trustedDirectory) ||
            !Path.IsPathFullyQualified(trustedDirectory))
        {
            throw InvalidBoundary("trusted directory composition");
        }

        cancellationToken.ThrowIfCancellationRequested();
        var candidates = await LoadCandidatesAsync(executor, cancellationToken)
            .ConfigureAwait(false);
        var runtime = LegacyManualOperatorDataRuntimeGuard.Inspect(trustedDirectory);
        if (!runtime.IsReady)
        {
            throw InvalidBoundary("trusted runtime state");
        }

        var before = SnapshotDirectory(trustedDirectory);
        var results = new List<RestoreResult>(candidates.Count);
        var financialService = new LegacyManualFinancialScreenService(executor);
        var stockSearchService = new LegacyStockSearchService(executor);
        var netSellSource = new S5025TrustedManualFileDataSource(trustedDirectory);
        TrustedSourceEvidence trustedSources;
        using (var viSource = new ViTrustedManualFileStore(trustedDirectory))
        {
            trustedSources = await ReadTrustedSourcesAsync(
                    netSellSource,
                    viSource,
                    cancellationToken)
                .ConfigureAwait(false);
            foreach (var candidate in candidates)
            {
                cancellationToken.ThrowIfCancellationRequested();
                results.Add(candidate.Kind switch
                {
                    LegacyNamedManualRestoreKind.Financial =>
                        await ResolveFinancialAsync(
                                candidate,
                                financialService,
                                stockSearchService,
                                cancellationToken)
                            .ConfigureAwait(false),
                    LegacyNamedManualRestoreKind.NetSell =>
                        await ResolveNetSellAsync(candidate, netSellSource, cancellationToken)
                            .ConfigureAwait(false),
                    LegacyNamedManualRestoreKind.Vi =>
                        await ResolveViAsync(candidate, viSource, cancellationToken)
                            .ConfigureAwait(false),
                    _ => throw InvalidBoundary("manual candidate kind")
                });
            }
        }

        var after = SnapshotDirectory(trustedDirectory);
        var filesStable = before.Matches(after);
        if (!filesStable)
        {
            throw InvalidBoundary("operator file immutability");
        }

        var kindCounts = candidates
            .GroupBy(static candidate => candidate.Kind)
            .ToDictionary(static group => group.Key, static group => group.Count());
        var screenCounts = candidates
            .Where(static candidate =>
                candidate.Kind == LegacyNamedManualRestoreKind.Financial)
            .GroupBy(static candidate => candidate.FinancialScreen!.Value)
            .OrderBy(static group => group.Key)
            .Select(group =>
                $"{group.Key}={group.Count().ToString(CultureInfo.InvariantCulture)}");
        var audienceCounts = candidates
            .Where(static candidate => candidate.Kind == LegacyNamedManualRestoreKind.NetSell)
            .GroupBy(static candidate => candidate.Audience!.Value)
            .OrderBy(static group => group.Key)
            .Select(group =>
                $"{group.Key}={group.Count().ToString(CultureInfo.InvariantCulture)}");
        var viRows = candidates
            .Where(static candidate => candidate.Kind == LegacyNamedManualRestoreKind.Vi)
            .ToArray();
        var financialOutcomes = candidates
            .Select((candidate, index) => (Candidate: candidate, Result: results[index]))
            .Where(static pair =>
                pair.Candidate.Kind == LegacyNamedManualRestoreKind.Financial)
            .GroupBy(static pair => pair.Candidate.FinancialScreen!.Value)
            .OrderBy(static group => group.Key)
            .Select(group =>
            {
                var freshCount = group.Count(static pair => pair.Result.IsFresh);
                var blocked = group
                    .Where(static pair => !pair.Result.IsFresh)
                    .GroupBy(static pair => pair.Result.Reason, StringComparer.Ordinal)
                    .OrderBy(static reason => reason.Key, StringComparer.Ordinal)
                    .Select(reason =>
                        $"{reason.Key}:{reason.Count().ToString(CultureInfo.InvariantCulture)}");
                return $"{group.Key}=fresh:{freshCount.ToString(CultureInfo.InvariantCulture)}" +
                    (freshCount == group.Count()
                        ? string.Empty
                        : $"/blocked:{string.Join('+', blocked)}");
            });
        var blockReasons = results
            .Where(static result => !result.IsFresh)
            .GroupBy(static result => result.Reason, StringComparer.Ordinal)
            .ToDictionary(static group => group.Key, static group => group.Count(), StringComparer.Ordinal);
        var fresh = results.Count(static result => result.IsFresh);

        Console.WriteLine(
            "PLIST_MANUAL_GRAMMAR_AUDIT: " +
            $"candidates={candidates.Count.ToString(CultureInfo.InvariantCulture)} " +
            $"financial={kindCounts.GetValueOrDefault(LegacyNamedManualRestoreKind.Financial).ToString(CultureInfo.InvariantCulture)} " +
            $"netSell={kindCounts.GetValueOrDefault(LegacyNamedManualRestoreKind.NetSell).ToString(CultureInfo.InvariantCulture)} " +
            $"vi={kindCounts.GetValueOrDefault(LegacyNamedManualRestoreKind.Vi).ToString(CultureInfo.InvariantCulture)} " +
            $"historical={candidates.Count(static candidate => candidate.IsHistorical).ToString(CultureInfo.InvariantCulture)} " +
            $"canonical={candidates.Count(static candidate => !candidate.IsHistorical).ToString(CultureInfo.InvariantCulture)}");
        Console.WriteLine("PLIST_MANUAL_FINANCIAL_SCREENS: " + string.Join(',', screenCounts));
        Console.WriteLine(
            "PLIST_MANUAL_FINANCIAL_OUTCOMES: " + string.Join(',', financialOutcomes));
        Console.WriteLine("PLIST_MANUAL_NET_SELL_AUDIENCES: " + string.Join(',', audienceCounts));
        Console.WriteLine(
            "PLIST_MANUAL_VI_GRAMMAR: " +
            $"historical={viRows.Count(static candidate => candidate.IsHistorical).ToString(CultureInfo.InvariantCulture)} " +
            $"versioned={viRows.Count(static candidate => !candidate.IsHistorical).ToString(CultureInfo.InvariantCulture)}");
        Console.WriteLine(
            "PLIST_MANUAL_TRUSTED_SOURCES: " +
            $"netSellAudiencesReady={trustedSources.NetSellAudiencesReady.ToString(CultureInfo.InvariantCulture)} " +
            $"viSnapshotReady={trustedSources.ViSnapshotReady.ToString().ToLowerInvariant()} " +
            $"viItems={trustedSources.ViItemCount.ToString(CultureInfo.InvariantCulture)} " +
            $"viPages={trustedSources.ViPageCount.ToString(CultureInfo.InvariantCulture)} " +
            "openedHandle=true");
        Console.WriteLine(
            "PLIST_MANUAL_FRESH_PROOF: " +
            $"freshMaterialized={fresh.ToString(CultureInfo.InvariantCulture)} " +
            $"restoreBlocked={(results.Count - fresh).ToString(CultureInfo.InvariantCulture)} " +
            "graphE=double-read-row-version+fresh-stock-identity " +
            "netSell=opened-handle-production-parser " +
            "vi=opened-handle-version+identity+page-check " +
            "sceneDataAndAssetsNotAsserted=true");
        Console.WriteLine(
            "PLIST_MANUAL_BLOCK_REASONS: " +
            (blockReasons.Count == 0
                ? "none"
                : string.Join(
                    ',',
                    blockReasons.OrderBy(static pair => pair.Key, StringComparer.Ordinal)
                        .Select(pair =>
                            $"{pair.Key}={pair.Value.ToString(CultureInfo.InvariantCulture)}"))));
        Console.WriteLine(
            "PLIST_MANUAL_LOCAL_READ_AUDIT: " +
            $"runtimeState={runtime.State} " +
            $"entries={before.Entries.Count.ToString(CultureInfo.InvariantCulture)} " +
            "filesStable=true writesAttempted=false markerOrIntentCreated=false");

        return new NamedManualRestoreReadOnlyAuditSummary(
            candidates.Count,
            fresh,
            results.Count - fresh,
            runtime.State,
            filesStable,
            kindCounts,
            blockReasons);
    }

    public static string DefaultTrustedDirectory()
    {
        var localAppData = Environment.GetFolderPath(
            Environment.SpecialFolder.LocalApplicationData,
            Environment.SpecialFolderOption.DoNotVerify);
        if (string.IsNullOrWhiteSpace(localAppData))
        {
            throw InvalidBoundary("local application-data root");
        }

        return Path.GetFullPath(Path.Combine(
            localAppData,
            "MBN_STOCK_WEBVIEW",
            "OperatorData"));
    }

    private static async Task<IReadOnlyList<LegacyNamedManualRestoreCandidate>> LoadCandidatesAsync(
        IDataQueryExecutor executor,
        CancellationToken cancellationToken)
    {
        var spec = new DataQuerySpec(
            Sql,
            [new DataQueryParameter("row_limit", MaximumRows + 1, DbType.Int32)]);
        spec.ValidateFor(DataSourceKind.Oracle);
        var table = await executor.ExecuteAsync(
                DataSourceKind.Oracle,
                QueryName,
                spec,
                cancellationToken)
            .ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        var columns = new[] { "DC_CODE", "DC_TITLE", "LIST_TEXT", "ITEM_INDEX" };
        if (table is null ||
            table.Columns.Count != columns.Length ||
            table.Rows.Count is < 1 or > MaximumRows)
        {
            throw InvalidBoundary("PList query schema or row bound");
        }

        for (var index = 0; index < columns.Length; index++)
        {
            if (table.Columns[index].ColumnName != columns[index] ||
                table.Columns[index].DataType != typeof(string))
            {
                throw InvalidBoundary("PList query schema");
            }
        }

        var candidates = new List<LegacyNamedManualRestoreCandidate>();
        var routedManualCount = 0;
        foreach (DataRow dataRow in table.Rows)
        {
            var parsed = LegacyPListReadAuditParser.Parse(
                dataRow[0] as string,
                dataRow[1] as string,
                dataRow[3] as string,
                dataRow[2] as string);
            if (!parsed.IsSuccess)
            {
                throw InvalidBoundary("PList row parse");
            }

            var row = parsed.Row!;
            var route = LegacyPListReadAuditEvidence.ClassifyRoute(row);
            if (route == LegacyPListAuditRoute.ManualFreshMaterialization)
            {
                routedManualCount++;
            }

            var candidate = LegacyNamedManualRestoreClassifier.Classify(row);
            if (candidate is not null)
            {
                candidates.Add(candidate);
            }
            else if (route == LegacyPListAuditRoute.ManualFreshMaterialization)
            {
                throw InvalidBoundary("manual route closed grammar");
            }
        }

        if (candidates.Count != routedManualCount)
        {
            throw InvalidBoundary("manual route/classifier agreement");
        }

        return candidates.AsReadOnly();
    }

    private static async Task<RestoreResult> ResolveFinancialAsync(
        LegacyNamedManualRestoreCandidate candidate,
        LegacyManualFinancialScreenService financialService,
        LegacyStockSearchService stockSearchService,
        CancellationToken cancellationToken)
    {
        try
        {
            var identity = new ManualFinancialIdentity(
                candidate.FinancialScreen!.Value,
                candidate.StockName!);
            var firstRead = await financialService.GetAsync(identity, cancellationToken)
                .ConfigureAwait(false);
            var query = LegacyNamedManualFreshProof.FinancialStockSearchQuery(candidate);
            var stockSearch = await stockSearchService.SearchAsync(
                    query,
                    BrowserStockSearchLimit,
                    cancellationToken)
                .ConfigureAwait(false);
            var secondRead = await financialService.GetAsync(identity, cancellationToken)
                .ConfigureAwait(false);
            var failure = LegacyNamedManualFreshProof.EvaluateFinancial(
                candidate,
                firstRead,
                secondRead,
                stockSearch);
            return RestoreResult.From(failure);
        }
        catch (ManualFinancialNotFoundException)
        {
            return RestoreResult.Blocked("GRAPH_E_NOT_FOUND");
        }
        catch (ManualFinancialAmbiguousIdentityException)
        {
            return RestoreResult.Blocked("GRAPH_E_AMBIGUOUS");
        }
        catch (ManualFinancialDataException)
        {
            return RestoreResult.Blocked("GRAPH_E_DATA_INVALID");
        }
        catch (StockSearchDataException)
        {
            return RestoreResult.Blocked("STOCK_MASTER_DATA_INVALID");
        }
        catch (ArgumentException)
        {
            return RestoreResult.Blocked("GRAPH_E_IDENTITY_INVALID");
        }
    }

    private static async Task<RestoreResult> ResolveNetSellAsync(
        LegacyNamedManualRestoreCandidate candidate,
        S5025TrustedManualFileDataSource source,
        CancellationToken cancellationToken)
    {
        try
        {
            var rows = await source.ReadAsync(candidate.Audience!.Value, cancellationToken)
                .ConfigureAwait(false);
            return RestoreResult.From(
                LegacyNamedManualFreshProof.EvaluateNetSell(candidate, rows));
        }
        catch (S5025TrustedManualDataException)
        {
            return RestoreResult.Blocked("NET_SELL_TRUSTED_READ_INVALID");
        }
    }

    private static async Task<TrustedSourceEvidence> ReadTrustedSourcesAsync(
        S5025TrustedManualFileDataSource netSellSource,
        ViTrustedManualFileStore viSource,
        CancellationToken cancellationToken)
    {
        var audiencesReady = 0;
        foreach (var audience in Enum.GetValues<S5025ManualAudience>())
        {
            var rows = await netSellSource.ReadAsync(audience, cancellationToken)
                .ConfigureAwait(false);
            if (rows.Count != 5)
            {
                throw InvalidBoundary("net-sell production parser");
            }

            audiencesReady++;
        }

        var vi = await viSource.ReadStockNameSnapshotAsync(cancellationToken)
            .ConfigureAwait(false);
        if (!TrustedViManualReference.IsRowVersion(vi.RowVersion) ||
            vi.StockNames.Count is < 1 or > TrustedViManualReference.MaximumItemCount)
        {
            throw InvalidBoundary("VI production parser");
        }

        var canonical = new LegacySceneSelection(
            "PAGED_VI",
            TrustedViManualReference.SubjectSentinel,
            "INPUT_ORDER",
            "CURRENT",
            vi.RowVersion);
        var secondRead = await TrustedViManualReference.ResolveAsync(
                canonical,
                pageIndexZeroBased: 0,
                viSource,
                cancellationToken)
            .ConfigureAwait(false);
        if (!secondRead.StockNames.SequenceEqual(vi.StockNames, StringComparer.Ordinal))
        {
            throw InvalidBoundary("VI stable second read");
        }

        return new TrustedSourceEvidence(
            audiencesReady,
            ViSnapshotReady: true,
            vi.StockNames.Count,
            checked((vi.StockNames.Count + 4) / 5));
    }

    private static async Task<RestoreResult> ResolveViAsync(
        LegacyNamedManualRestoreCandidate candidate,
        ViTrustedManualFileStore source,
        CancellationToken cancellationToken)
    {
        try
        {
            var snapshot = await source.ReadStockNameSnapshotAsync(cancellationToken)
                .ConfigureAwait(false);
            var failure = LegacyNamedManualFreshProof.EvaluateVi(candidate, snapshot);
            if (failure != LegacyNamedManualFreshProofFailure.None)
            {
                return RestoreResult.From(failure);
            }

            var canonical = new LegacySceneSelection(
                "PAGED_VI",
                TrustedViManualReference.SubjectSentinel,
                "INPUT_ORDER",
                "CURRENT",
                snapshot.RowVersion);
            var resolved = await TrustedViManualReference.ResolveAsync(
                    canonical,
                    pageIndexZeroBased: 0,
                    source,
                    cancellationToken)
                .ConfigureAwait(false);
            return resolved.PageNumberOneBased == 1 &&
                   resolved.StockNames.SequenceEqual(
                       snapshot.StockNames,
                       StringComparer.Ordinal)
                ? RestoreResult.Fresh()
                : RestoreResult.Blocked("VI_SECOND_READ_CHANGED");
        }
        catch (ViTrustedManualDataException)
        {
            return RestoreResult.Blocked("VI_TRUSTED_READ_INVALID");
        }
        catch (LegacySceneDataException)
        {
            return RestoreResult.Blocked("VI_SECOND_READ_CHANGED");
        }
    }

    private static DirectorySnapshot SnapshotDirectory(string trustedDirectory)
    {
        try
        {
            var directory = new DirectoryInfo(trustedDirectory);
            if (!directory.Exists ||
                (directory.Attributes & (FileAttributes.ReparsePoint | FileAttributes.Directory)) !=
                FileAttributes.Directory)
            {
                throw InvalidBoundary("operator directory");
            }

            var entries = new List<FileSnapshot>();
            foreach (var path in Directory.EnumerateFileSystemEntries(trustedDirectory)
                         .OrderBy(static value => Path.GetFileName(value), StringComparer.Ordinal))
            {
                var attributes = File.GetAttributes(path);
                if ((attributes & (FileAttributes.Directory | FileAttributes.ReparsePoint)) != 0)
                {
                    throw InvalidBoundary("operator directory entry");
                }

                var before = new FileInfo(path);
                byte[] digest;
                long length;
                using (var stream = new FileStream(
                           path,
                           FileMode.Open,
                           FileAccess.Read,
                           FileShare.Read,
                           bufferSize: 4_096,
                           FileOptions.SequentialScan))
                {
                    length = stream.Length;
                    digest = SHA256.HashData(stream);
                    if (stream.Length != length)
                    {
                        throw InvalidBoundary("operator file stable read");
                    }
                }

                var after = new FileInfo(path);
                if (after.Length != length ||
                    before.LastWriteTimeUtc != after.LastWriteTimeUtc ||
                    before.CreationTimeUtc != after.CreationTimeUtc)
                {
                    throw InvalidBoundary("operator file stable read");
                }

                entries.Add(new FileSnapshot(
                    Path.GetFileName(path),
                    length,
                    after.CreationTimeUtc,
                    after.LastWriteTimeUtc,
                    Convert.ToHexString(digest)));
            }

            directory.Refresh();
            return new DirectorySnapshot(directory.LastWriteTimeUtc, entries.AsReadOnly());
        }
        catch (InvalidDataException)
        {
            throw;
        }
        catch (Exception exception) when (exception is
            IOException or
            UnauthorizedAccessException or
            ArgumentException or
            NotSupportedException)
        {
            throw InvalidBoundary("operator file snapshot");
        }
    }

    private static InvalidDataException InvalidBoundary(string detail) =>
        new($"The read-only named manual restore audit failed its {detail} boundary. " +
            "No source values were printed.");

    private sealed record RestoreResult(bool IsFresh, string Reason)
    {
        public static RestoreResult Fresh() => new(true, "NONE");

        public static RestoreResult Blocked(string reason) => new(false, reason);

        public static RestoreResult From(LegacyNamedManualFreshProofFailure failure) =>
            failure == LegacyNamedManualFreshProofFailure.None
                ? Fresh()
                : Blocked(failure.ToString().ToUpperInvariant());
    }

    private sealed record TrustedSourceEvidence(
        int NetSellAudiencesReady,
        bool ViSnapshotReady,
        int ViItemCount,
        int ViPageCount);

    private sealed record FileSnapshot(
        string Name,
        long Length,
        DateTime CreationTimeUtc,
        DateTime LastWriteTimeUtc,
        string Sha256);

    private sealed record DirectorySnapshot(
        DateTime LastWriteTimeUtc,
        IReadOnlyList<FileSnapshot> Entries)
    {
        public bool Matches(DirectorySnapshot other) =>
            other is not null &&
            LastWriteTimeUtc == other.LastWriteTimeUtc &&
            Entries.SequenceEqual(other.Entries);
    }
}
