using System.Collections.Concurrent;
using System.Data;
using System.Text.RegularExpressions;
using BoardApp.Config;
using Microsoft.Data.SqlClient;

namespace BoardApp.Services;

public sealed class SqlDataSourceService
{
    private static readonly Regex NamedParamRegex = new(@"(?<![@])@([a-zA-Z_][a-zA-Z0-9_]*)", RegexOptions.Compiled);
    private readonly BoardConfigLoader _cfg;

    private readonly ConcurrentDictionary<string, string[]> _sqlParamCache = new(StringComparer.Ordinal);
    private SqlConnection? _conn;
    private readonly object _lock = new();

    public SqlDataSourceService(BoardConfigLoader cfg)
    {
        _cfg = cfg;
    }

    public static string[] ExtractNamedSqlParams(string sqlText)
    {
        var seen = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (Match m in NamedParamRegex.Matches(sqlText ?? ""))
        {
            if (m.Groups.Count > 1)
            {
                seen.Add(m.Groups[1].Value);
            }
        }
        return seen.ToArray();
    }

    private SqlConnection GetOrCreateConnection()
    {
        lock (_lock)
        {
            if (_conn is { State: ConnectionState.Open }) return _conn;

            var c = _cfg.LoadSqlServerConfig();
            var csb = new SqlConnectionStringBuilder
            {
                DataSource = c.Port.HasValue ? $"{c.Server},{c.Port.Value}" : c.Server,
                InitialCatalog = c.Database,
                UserID = c.User,
                Password = c.Password,
                Encrypt = c.Options?.Encrypt ?? false,
                TrustServerCertificate = c.Options?.TrustServerCertificate ?? true
            };

            _conn?.Dispose();
            _conn = new SqlConnection(csb.ConnectionString);
            _conn.Open();
            return _conn;
        }
    }

    private static void BindInput(SqlCommand cmd, string name, object? value)
    {
        if (value is null) throw new InvalidOperationException($"缺少 SQL 参数: @{name}");

        var p = cmd.Parameters.Add("@" + name, SqlDbType.NVarChar);

        if (value is DateTime dt)
        {
            if (string.Equals(name, "startDate", StringComparison.OrdinalIgnoreCase) ||
                string.Equals(name, "endDate", StringComparison.OrdinalIgnoreCase))
            {
                p.SqlDbType = SqlDbType.Date;
                p.Value = dt.Date;
            }
            else
            {
                p.SqlDbType = SqlDbType.DateTime2;
                p.Value = dt;
            }
            return;
        }

        if (value is long l)
        {
            p.SqlDbType = SqlDbType.BigInt;
            p.Value = l;
            return;
        }

        if (value is int i)
        {
            p.SqlDbType = SqlDbType.Int;
            p.Value = i;
            return;
        }

        if (value is double d)
        {
            p.SqlDbType = SqlDbType.Float;
            p.Value = d;
            return;
        }

        if (value is bool b)
        {
            p.SqlDbType = SqlDbType.Bit;
            p.Value = b;
            return;
        }

        p.SqlDbType = SqlDbType.NVarChar;
        p.Value = value.ToString() ?? "";
    }

    public async Task<List<Dictionary<string, object?>>> ExecuteDataSourceById(string id, Dictionary<string, object?> @params)
    {
        var cfg = _cfg.LoadDataSrcConfig();
        var src = cfg.Sources.FirstOrDefault(s => string.Equals(s.Id, id, StringComparison.Ordinal));
        if (src is null) throw new InvalidOperationException($"datasrc 未定义 id: {id}");

        var paramNames = _sqlParamCache.GetOrAdd(src.Sql, ExtractNamedSqlParams);

        var conn = GetOrCreateConnection();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = src.Sql;

        foreach (var name in paramNames)
        {
            @params.TryGetValue(name, out var value);
            BindInput(cmd, name, value);
        }

        var rows = new List<Dictionary<string, object?>>();
        using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            var row = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
            for (var i = 0; i < reader.FieldCount; i++)
            {
                row[reader.GetName(i)] = reader.IsDBNull(i) ? null : reader.GetValue(i);
            }
            rows.Add(row);
        }
        return rows;
    }

    public static Dictionary<string, object?> CoerceParamsFromRequest(Dictionary<string, string?> raw)
    {
        var o = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
        foreach (var (k, v) in raw)
        {
            if (string.IsNullOrWhiteSpace(k)) continue;
            if (string.IsNullOrWhiteSpace(v)) continue;
            var s = v.Trim();

            if (k.EndsWith("date", StringComparison.OrdinalIgnoreCase) ||
                string.Equals(k, "startDate", StringComparison.OrdinalIgnoreCase) ||
                string.Equals(k, "endDate", StringComparison.OrdinalIgnoreCase))
            {
                if (DateTime.TryParse(s, out var dt))
                {
                    o[k] = dt;
                    continue;
                }
            }

            if (Regex.IsMatch(s, @"^-?\d+$"))
            {
                if (int.TryParse(s, out var i)) { o[k] = i; continue; }
                if (long.TryParse(s, out var l)) { o[k] = l; continue; }
            }

            if (double.TryParse(s, out var d))
            {
                o[k] = d;
                continue;
            }

            if (string.Equals(s, "true", StringComparison.OrdinalIgnoreCase) ||
                string.Equals(s, "false", StringComparison.OrdinalIgnoreCase))
            {
                o[k] = string.Equals(s, "true", StringComparison.OrdinalIgnoreCase);
                continue;
            }

            o[k] = s;
        }
        return o;
    }
}