using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Text.Json;
using System.Text.RegularExpressions;
using Dapper;

namespace GB5Shared.Telemetry.Database;

/// <summary>
/// Single, shared factory for every DB-operation span raised anywhere in GB5 — Dapper via
/// <c>IQueryExecutor</c>, or any raw <c>SqlConnection</c>/<c>NpgsqlConnection</c> call site.
/// Every span carries three tags, built purely dynamically from the SQL text and the bound
/// parameter object at hand — nothing here is specific to any query, table, or module:
/// <list type="bullet">
///   <item><c>db.statement</c> — the original parameterised SQL text.</item>
///   <item><c>db.params</c> — every bound parameter name → resolved value, as JSON.</item>
///   <item><c>db.statement.runnable</c> — the same SQL with every <c>@param</c> token inlined
///   as its literal value, ready to paste into SSMS/psql and run as-is.</item>
/// </list>
/// Nothing is persisted to any table — these are OpenTelemetry span tags only, visible in
/// Zipkin/Jaeger/Tempo for the lifetime of the trace.
/// </summary>
public static class DatabaseActivityHelper
{
    private static readonly JsonSerializerOptions ParamJsonOptions = new() { WriteIndented = false };

    private static readonly Regex ParamTokenPattern =
        new(@"@(?<name>[A-Za-z_][A-Za-z0-9_]*)", RegexOptions.Compiled);

    private static readonly Regex LineCommentPattern =
        new(@"--([^\r\n]*)", RegexOptions.Compiled);

    /// <summary>
    /// Opens a client-side database span and stamps it with the full, dynamic query detail
    /// (statement, parameters, runnable statement) — the one call every GB5 data-access path
    /// (QueryExecutor or raw ADO.NET) should make around a command execution.
    /// </summary>
    /// <param name="sql">Full SQL text (or stored-procedure name) about to execute.</param>
    /// <param name="databaseName">Target database / tenant schema name, if known.</param>
    /// <param name="parameters">The bound parameter object — anonymous type, POCO, DynamicParameters, or dictionary.</param>
    /// <param name="maxStatementLength">0 (default) = never truncate. Positive value caps very long statements.</param>
    /// <param name="dbSystem">Database engine identifier: <c>"mssql"</c> or <c>"postgresql"</c>.</param>
    /// <param name="caller">Calling member name — set automatically via <see cref="System.Runtime.CompilerServices.CallerMemberNameAttribute"/> at call sites that want it.</param>
    public static Activity? StartDbActivity(
        string  sql,
        string? databaseName        = null,
        object? parameters          = null,
        int     maxStatementLength  = 0,
        string  dbSystem            = "mssql",
        string? caller              = null)
    {
        var op = DetectDbOp(sql);

        // Include the calling DAL/BLL method in the span name itself (not just the db.caller
        // tag) — "db select (AnalysisDAL.DynamicOutput)" reads clearly on its own, whereas a
        // bare "db select" is meaningless if this span ever ends up representing a trace in a
        // viewer's list (e.g. Zipkin can label a long-running trace's row using whichever span
        // happened to close first, well before the enclosing endpoint span does).
        var spanName = string.IsNullOrEmpty(caller)
            ? $"db {op.ToLowerInvariant()}"
            : $"db {op.ToLowerInvariant()} ({caller})";
        var activity = GB5ActivitySources.Database.StartActivity(spanName, ActivityKind.Client);
        if (activity is null) return null;

        activity.SetTag("db.system",    dbSystem);
        activity.SetTag("db.operation", op);

        activity.SetTag("db.statement",
            maxStatementLength > 0 && sql?.Length > maxStatementLength
                ? string.Concat(sql.AsSpan(0, maxStatementLength), " ...")
                : sql ?? string.Empty);

        var paramsJson = BuildParametersJson(parameters);
        if (!string.IsNullOrEmpty(paramsJson))
            activity.SetTag("db.params", paramsJson);

        var runnableSql = BuildRunnableStatement(sql, parameters);
        if (!string.IsNullOrEmpty(runnableSql))
            activity.SetTag("db.statement.runnable",
                maxStatementLength > 0 && runnableSql.Length > maxStatementLength
                    ? string.Concat(runnableSql.AsSpan(0, maxStatementLength), " ...")
                    : runnableSql);

        if (!string.IsNullOrEmpty(databaseName)) activity.SetTag("db.name",   databaseName);
        if (!string.IsNullOrEmpty(caller))       activity.SetTag("db.caller", caller);

        return activity;
    }

    /// <summary>
    /// Marks a database span as failed and records exception details as span attributes/events.
    /// </summary>
    public static void RecordDbError(Activity? activity, Exception ex)
    {
        if (activity is null) return;

        activity.AddException(ex);
        activity.SetTag("db.error.type",    ex.GetType().Name);
        activity.SetTag("db.error.message", ex.Message);
        activity.SetStatus(ActivityStatusCode.Error, ex.Message);
    }

    // ── Dynamic parameter extraction / inlining ──────────────────────────────────────────

    /// <summary>
    /// Serializes every bound parameter (name → resolved value) to a single JSON tag.
    /// Works generically for Dapper anonymous objects, POCOs, <see cref="DynamicParameters"/>,
    /// and plain dictionaries — no per-query or per-field hardcoding anywhere.
    /// </summary>
    public static string BuildParametersJson(object? parameters)
    {
        var raw = ExtractParameterValues(parameters);
        if (raw.Count == 0) return string.Empty;

        var display = raw.ToDictionary(kv => kv.Key, kv => NormalizeParamValue(kv.Value), StringComparer.OrdinalIgnoreCase);

        try
        {
            return JsonSerializer.Serialize(display, ParamJsonOptions);
        }
        catch
        {
            var safe = display.ToDictionary(kv => kv.Key, kv => (object?)(kv.Value?.ToString() ?? "null"));
            return JsonSerializer.Serialize(safe, ParamJsonOptions);
        }
    }

    /// <summary>
    /// Inlines every bound <c>@paramName</c> token in <paramref name="sql"/> with its actual
    /// value as a literal SQL Server expression — the resulting text can be pasted directly
    /// into SSMS/psql and executed as-is. Works generically for any query/param shape;
    /// unresolvable tokens (T-SQL system variables like <c>@@ROWCOUNT</c>, or a name with no
    /// bound value) are left untouched rather than guessed at.
    /// </summary>
    public static string? BuildRunnableStatement(string? sql, object? parameters)
    {
        if (string.IsNullOrEmpty(sql)) return null;

        var raw = ExtractParameterValues(parameters);
        if (raw.Count == 0) return null;

        var inlined = ParamTokenPattern.Replace(sql, m =>
        {
            var name = m.Groups["name"].Value;
            return raw.TryGetValue(name, out var value)
                ? ToSqlLiteral(value)
                : m.Value;
        });

        // Trace viewers (Zipkin's web UI, a browser "copy", a chat client) routinely render a
        // tag's \r\n as plain whitespace and collapse it onto one line. A "--" line comment
        // only terminates at a real newline — once collapsed, it silently swallows every token
        // after it. Block comments don't have that failure mode, so every "--" comment is
        // rewritten to "/* ... */" here — the query stays valid whether or not the viewer
        // preserves line breaks.
        return LineCommentPattern.Replace(inlined, m => $"/*{m.Groups[1].Value} */");
    }

    /// <summary>
    /// Renders a bound parameter value as a literal SQL Server expression — the inverse of
    /// parameter binding. TVP DataTables (which need a DECLARE/INSERT preamble, not a single
    /// literal) are left as their original @token so the runnable statement never becomes
    /// silently wrong.
    /// </summary>
    public static string ToSqlLiteral(object? value)
    {
        switch (value)
        {
            case null:
            case DBNull:
                return "NULL";
            case bool b:
                return b ? "1" : "0";
            case byte or sbyte or short or ushort or int or uint or long or ulong:
                return Convert.ToString(value, CultureInfo.InvariantCulture)!;
            case float or double or decimal:
                return Convert.ToString(value, CultureInfo.InvariantCulture)!;
            case DateTime dt:
                return $"'{dt:yyyy-MM-dd HH:mm:ss.fff}'";
            case DateTimeOffset dto:
                return $"'{dto:yyyy-MM-dd HH:mm:ss.fff zzz}'";
            case DateOnly d:
                return $"'{d:yyyy-MM-dd}'";
            case TimeOnly t:
                return $"'{t:HH:mm:ss.fff}'";
            case Guid g:
                return $"'{g}'";
            case byte[] bytes:
                return bytes.Length == 0 ? "0x" : "0x" + Convert.ToHexString(bytes);
            case DataTable:
                return "@__TVP_NOT_INLINED__";
            default:
                return "'" + value.ToString()!.Replace("'", "''") + "'";
        }
    }

    public static Dictionary<string, object?> ExtractParameterValues(object? parameters)
    {
        var result = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);

        switch (parameters)
        {
            case null:
                break;

            case DynamicParameters dp:
                foreach (var name in dp.ParameterNames)
                {
                    object? value;
                    try { value = dp.Get<object?>(name); }
                    catch { value = "(unreadable)"; }
                    result[name] = value;
                }
                break;

            case IEnumerable<KeyValuePair<string, object>> kvSeq:
                foreach (var kv in kvSeq)
                    result[kv.Key] = kv.Value;
                break;

            case IDictionary legacyDict:
                foreach (DictionaryEntry entry in legacyDict)
                    result[entry.Key?.ToString() ?? string.Empty] = entry.Value;
                break;

            case string:
                // A bare string (e.g. a stored-procedure name passed as "parameters") is not
                // a parameter bag — nothing to bind.
                break;

            default:
                foreach (var prop in parameters.GetType()
                             .GetProperties(BindingFlags.Public | BindingFlags.Instance))
                {
                    if (prop.GetIndexParameters().Length > 0) continue;
                    object? value;
                    try { value = prop.GetValue(parameters); }
                    catch { value = "(unreadable)"; }
                    result[prop.Name] = value;
                }
                break;
        }

        return result;
    }

    /// <summary>
    /// Keeps genuinely huge/binary payloads (TVP tables, byte arrays) out of the <c>db.params</c>
    /// JSON as a compact summary instead of dumping raw bytes/rows — everything else (every
    /// scalar, string, date, decimal, guid parameter) is captured verbatim and in full.
    /// </summary>
    public static object? NormalizeParamValue(object? value)
    {
        switch (value)
        {
            case null:
            case DBNull:
                return null;
            case DataTable dt:
                return $"<TVP DataTable: {dt.Rows.Count} row(s)>";
            case byte[] bytes:
                return $"<binary: {bytes.Length} byte(s)>";
            default:
                return value;
        }
    }

    public static string DetectDbOp(string? sql)
    {
        if (string.IsNullOrWhiteSpace(sql)) return "EXECUTE";
        var s = sql.AsSpan().TrimStart();
        if (s.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase)
         || s.StartsWith("SET TRANSACTION", StringComparison.OrdinalIgnoreCase)) return "SELECT";
        if (s.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase)) return "INSERT";
        if (s.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase)) return "UPDATE";
        if (s.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase)) return "DELETE";
        if (s.StartsWith("MERGE",  StringComparison.OrdinalIgnoreCase)) return "MERGE";
        return "EXECUTE";
    }
}