using System.Text.RegularExpressions;
using Microsoft.Data.SqlClient;

namespace SwOfflineApply;

/// <summary>
/// Applies a decrypted Offline Rollout Package's scripts against a local SQL Server, and writes
/// its own local, append-only execution log (a plain JSON-lines file next to the package) —
/// proof of what ran and when, entirely local, with no dependency on reporting back to central.
/// GO-splitting logic mirrors SwBLL.Provisioning.TargetDbExecutor.ExecuteScriptAsync's own
/// regex — same convention, independently implemented per this tool's zero-GB5-dependency rule.
/// </summary>
public static class LocalScriptApplier
{
    public record ScriptResult(string? ObjectName, bool Success, int RowsAffected, string? Error);

    public static async Task<List<ScriptResult>> ApplyAsync(
        OfflinePackageBundle bundle, string connectionString, string logFilePath, CancellationToken ct)
    {
        var results = new List<ScriptResult>();
        await using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync(ct).ConfigureAwait(false);

        foreach (var script in bundle.Scripts.OrderBy(s => s.Sequence))
        {
            var batches = Regex.Split(script.SqlScript, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase)
                .Where(b => !string.IsNullOrWhiteSpace(b))
                .ToList();

            int totalRows = 0;
            try
            {
                foreach (var batch in batches)
                {
                    await using var cmd = new SqlCommand(batch, conn) { CommandTimeout = 300 };
                    totalRows += await cmd.ExecuteNonQueryAsync(ct).ConfigureAwait(false);
                }
                results.Add(new ScriptResult(script.ObjectName, true, totalRows, null));
            }
            catch (Exception ex)
            {
                results.Add(new ScriptResult(script.ObjectName, false, totalRows, ex.Message));
                // One script's failure doesn't abort the rest of the batch — matches this
                // platform's own established posture (ChangeRequestBLL.Execute's per-script
                // try/catch), so a partial, honestly-reported apply beats an all-or-nothing one.
            }
        }

        await WriteLocalLogAsync(bundle, results, logFilePath, ct).ConfigureAwait(false);
        return results;
    }

    private static async Task WriteLocalLogAsync(
        OfflinePackageBundle bundle, List<ScriptResult> results, string logFilePath, CancellationToken ct)
    {
        var lines = results.Select(r =>
            $"{{\"AppliedOn\":\"{DateTime.UtcNow:O}\",\"PackageId\":{bundle.PackageId}," +
            $"\"PackageCode\":\"{bundle.PackageCode}\",\"ObjectName\":\"{r.ObjectName}\"," +
            $"\"Success\":{r.Success.ToString().ToLowerInvariant()},\"RowsAffected\":{r.RowsAffected}," +
            $"\"Error\":{(r.Error is null ? "null" : $"\"{r.Error.Replace("\"", "'")}\"")}}}");

        await File.AppendAllLinesAsync(logFilePath, lines, ct).ConfigureAwait(false);
    }
}