using GB5Shared.DTO.Framework.Login;
using SwBLL.ClientDatabase;
using SwBLL.DbServer;
using SwBLL.Provisioning;
using SwDAL.DTO.DmlScript;
using SwDAL.Enums;
using System.Text.Json;

namespace SwBLL.DmlScript;

/// <summary>
/// Executes parameterized DML (INSERT / UPDATE / DELETE) against a client's target database.
///
/// Security rules:
///  - Column names from RowValues/WhereValues are ALWAYS bracket-quoted: [{col.Replace("]","")}]
///  - Values are ALWAYS bound as Dapper parameters — never concatenated into SQL.
///  - Never injects another module's DAL — uses IDbServerBLL and IClientDatabaseBLL.
/// </summary>
public class DmlScriptBLL : IDmlScriptBLL
{
    private readonly IClientDatabaseBLL _clientDatabaseBLL;
    private readonly IDbServerBLL       _dbServerBLL;
    private readonly ITargetDbExecutor  _targetDbExecutor;

    public DmlScriptBLL(
        IClientDatabaseBLL clientDatabaseBLL,
        IDbServerBLL dbServerBLL,
        ITargetDbExecutor targetDbExecutor)
    {
        _clientDatabaseBLL = clientDatabaseBLL;
        _dbServerBLL       = dbServerBLL;
        _targetDbExecutor  = targetDbExecutor;
    }

    public async Task<DmlExecutionResultDTO> ExecuteDml(
        DmlExecutionRequestDTO request, LoginDTO loginDTO, CancellationToken ct)
    {
        if (request == null)             throw new ArgumentNullException(nameof(request));
        if (string.IsNullOrWhiteSpace(request.TableName))
            throw new ArgumentException("TableName is required.", nameof(request));

        var clientDb = await _clientDatabaseBLL.GetById(request.ClientDatabaseId, loginDTO, ct)
            ?? throw new InvalidOperationException($"Client database {request.ClientDatabaseId} not found.");

        var server = await _dbServerBLL.GetByIdWithCredentials(clientDb.DbServerId, loginDTO, ct)
            ?? throw new InvalidOperationException($"DB server {clientDb.DbServerId} not found.");

        // Data-only change, no schema rights needed — uses the least-privilege _app contained user.
        string connStr     = await _targetDbExecutor
            .BuildConnectionStringForRoleAsync(server, clientDb, ClientDbLoginRole.App, ct)
            .ConfigureAwait(false);
        string qualTable   = $"[{EscapeBracket(request.SchemaName)}].[{EscapeBracket(request.TableName)}]";
        var    (sql, parm) = BuildDml(request, qualTable);

        int rows = await _targetDbExecutor.ExecuteDmlAsync(connStr, sql, parm, ct).ConfigureAwait(false);

        return new DmlExecutionResultDTO
        {
            RowsAffected = rows,
            Success      = rows > 0,
            Message      = rows > 0 ? $"{rows} row(s) affected." : "No rows affected."
        };
    }

    // ── SQL builder ───────────────────────────────────────────────────────

    private static (string Sql, Dictionary<string, object?> Params) BuildDml(
        DmlExecutionRequestDTO req, string qualTable)
    {
        var p = new Dictionary<string, object?>();

        return req.Operation switch
        {
            DmlOperation.Insert => BuildInsert(req, qualTable, p),
            DmlOperation.Update => BuildUpdate(req, qualTable, p),
            DmlOperation.Delete => BuildDelete(req, qualTable, p),
            _                   => throw new ArgumentOutOfRangeException(nameof(req.Operation))
        };
    }

    private static (string, Dictionary<string, object?>) BuildInsert(
        DmlExecutionRequestDTO req, string qualTable, Dictionary<string, object?> p)
    {
        if (req.RowValues.Count == 0)
            throw new ArgumentException("RowValues must not be empty for INSERT.");

        var cols = new List<string>();
        var vals = new List<string>();
        int idx  = 0;

        foreach (var kv in req.RowValues)
        {
            string paramName = $"@p{idx++}";
            cols.Add($"[{EscapeBracket(kv.Key)}]");
            vals.Add(paramName);
            p[paramName] = NormalizeValue(kv.Value);
        }

        string sql = $"INSERT INTO {qualTable} ({string.Join(", ", cols)}) VALUES ({string.Join(", ", vals)})";
        return (sql, p);
    }

    private static (string, Dictionary<string, object?>) BuildUpdate(
        DmlExecutionRequestDTO req, string qualTable, Dictionary<string, object?> p)
    {
        if (req.RowValues.Count == 0)
            throw new ArgumentException("RowValues must not be empty for UPDATE.");
        if (req.WhereValues.Count == 0)
            throw new ArgumentException("WhereValues must not be empty for UPDATE — unbounded update rejected.");

        var setClauses   = new List<string>();
        var whereClauses = new List<string>();
        int idx          = 0;

        foreach (var kv in req.RowValues)
        {
            string paramName = $"@p{idx++}";
            setClauses.Add($"[{EscapeBracket(kv.Key)}] = {paramName}");
            p[paramName] = kv.Value;
        }

        foreach (var kv in req.WhereValues)
        {
            string paramName = $"@w{idx++}";
            whereClauses.Add($"[{EscapeBracket(kv.Key)}] = {paramName}");
            p[paramName] = kv.Value;
        }

        string sql = $"UPDATE {qualTable} SET {string.Join(", ", setClauses)} WHERE {string.Join(" AND ", whereClauses)}";
        return (sql, p);
    }

    private static (string, Dictionary<string, object?>) BuildDelete(
        DmlExecutionRequestDTO req, string qualTable, Dictionary<string, object?> p)
    {
        if (req.WhereValues.Count == 0)
            throw new ArgumentException("WhereValues must not be empty for DELETE — unbounded delete rejected.");

        var whereClauses = new List<string>();
        int idx          = 0;

        foreach (var kv in req.WhereValues)
        {
            string paramName = $"@w{idx++}";
            whereClauses.Add($"[{EscapeBracket(kv.Key)}] = {paramName}");
            p[paramName] = kv.Value;
        }

        string sql = $"DELETE FROM {qualTable} WHERE {string.Join(" AND ", whereClauses)}";
        return (sql, p);
    }

    /// <summary>Strips bracket characters to prevent bracket-injection in column names.</summary>
    private static string EscapeBracket(string name) => name.Replace("[", "").Replace("]", "");

    private static object? NormalizeValue(object? value)
    {
        if (value is JsonElement je)
        {
            switch (je.ValueKind)
            {
                case JsonValueKind.String:
                    return je.GetString();

                case JsonValueKind.Number:
                    if (je.TryGetInt32(out int intVal))
                        return intVal;

                    if (je.TryGetInt64(out long longVal))
                        return longVal;

                    if (je.TryGetDecimal(out decimal decVal))
                        return decVal;

                    return je.GetDouble();

                case JsonValueKind.True:
                    return true;

                case JsonValueKind.False:
                    return false;

                case JsonValueKind.Null:
                    return null;

                default:
                    return je.ToString();
            }
        }

        return value;
    }
}