using System.Text.RegularExpressions;
using Dapper;
using GB5Shared.DTO.Framework.SchemaIntrospection;
using GB5Shared.EncryptionHelper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using SwBLL.Vault;
using SwDAL.DTO.ClientDatabase;
using SwDAL.DTO.DbServer;
using SwDAL.Enums;

namespace SwBLL.Provisioning;

/// <summary>
/// SQL Server implementation of ITargetDbExecutor.
/// Credentials always come from DbServerDTO — never Integrated Security.
/// Row cap on SELECT: 5 000 rows. Multi-statement scripts are split on GO separator.
/// </summary>
public class TargetDbExecutor : ITargetDbExecutor
{
    private const int SelectRowCap = 5_000;

    /// <summary>Row cap for <see cref="ExecuteQueryBatchAsync"/> only — a separate, higher cap
    /// than <see cref="SelectRowCap"/> since that path serves full server-assembled report
    /// execution rather than ad-hoc exploratory queries. Does not affect SelectRowCap or any
    /// other existing method.</summary>
    private const int InternalBatchRowCap = 100_000;

    private static readonly Regex _selectOnlyRegex =
        new(@"^\s*(SELECT|WITH)\b", RegexOptions.IgnoreCase | RegexOptions.Multiline);

    private static readonly Regex _nonSelectRegex =
        new(@"\b(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|TRUNCATE|EXEC|EXECUTE|GRANT|REVOKE|DENY)\b",
            RegexOptions.IgnoreCase);

    private readonly IConfiguration _configuration;
    private readonly ISwVaultService _swVaultService;
    private readonly ILogger<TargetDbExecutor> _logger;

    public TargetDbExecutor(
        IConfiguration configuration, ISwVaultService swVaultService, ILogger<TargetDbExecutor> logger)
    {
        _configuration  = configuration;
        _swVaultService = swVaultService;
        _logger         = logger;
    }

    // ── Connection string builder ─────────────────────────────────────────

    public string BuildConnectionString(DbServerDTO server,string databaseName,string? dbUsername = null,string? dbPassword = null)
    {
        var username = string.IsNullOrWhiteSpace(dbUsername)
            ? server.DbUsername
            : dbUsername;

        var password = string.IsNullOrWhiteSpace(dbPassword)
            ? DecryptServerPassword(server)
            : dbPassword;

        if (string.IsNullOrWhiteSpace(username))
        {
            throw new InvalidOperationException(
                "Database username is not configured.");
        }

        if (string.IsNullOrWhiteSpace(password))
        {
            throw new InvalidOperationException(
                "Database password is not configured.");
        }

        var csb = new SqlConnectionStringBuilder
        {
            DataSource = server.Port == 0
                ? server.HostName
                : $"{server.HostName},{server.Port}",

            InitialCatalog = databaseName,

            UserID = username,
            Password = password,

            TrustServerCertificate = true,
            Encrypt = false,
            ConnectTimeout = 30
        };

        return csb.ConnectionString;
    }

    public string BuildAdminConnectionString(DbServerDTO server)
        => BuildConnectionString(server, "master");

    // ── Role-aware connection string builder (contained users) ───────────

    public async Task<string> BuildConnectionStringForRoleAsync(
        DbServerDTO server, ClientDatabaseDTO clientDb, ClientDbLoginRole role, CancellationToken ct)
    {
        var (vaultPath, roleSuffix) = role switch
        {
            ClientDbLoginRole.Dba      => (clientDb.DbaVaultPath, "dba"),
            ClientDbLoginRole.App      => (clientDb.AppVaultPath, "app"),
            ClientDbLoginRole.ReadOnly => (clientDb.ReadOnlyVaultPath, "readonly"),
            _ => throw new ArgumentOutOfRangeException(nameof(role), role, "Unhandled ClientDbLoginRole.")
        };

        if (string.IsNullOrWhiteSpace(vaultPath))
        {
            _logger.LogWarning(
                "No {Role} contained-user credential provisioned for ClientDatabaseId {ClientDbId}; falling back to DbServer admin credential.",
                role, clientDb.ClientDbId);
            return BuildConnectionString(server, clientDb.DatabaseName);
        }

        var password = await _swVaultService.GetSecretAsync(vaultPath, ct).ConfigureAwait(false);
        var username = $"{clientDb.ClientDbCode}_{roleSuffix}";

        return BuildConnectionString(server, clientDb.DatabaseName, username, password);
    }

    private string DecryptServerPassword(DbServerDTO server)
    {
        if (string.IsNullOrWhiteSpace(server.DbPassword))
        {
            throw new InvalidOperationException(
                $"DB password is not configured for DB server '{server.DbServerId}'.");
        }

        string encryptionKey = _configuration["Encryption:DbPasswordKey"]
            ?? throw new InvalidOperationException("DB password encryption key is not configured.");

        try
        {
            return PasswordEncryption.Decrypt(server.DbPassword, encryptionKey);
        }
        catch (Exception ex)
        {
            throw new InvalidOperationException(
                $"Unable to decrypt DB password for DB server '{server.DbServerId}'.", ex);
        }
    }

    // ── DDL / DML execution ───────────────────────────────────────────────

    public async Task<int> ExecuteScriptAsync(string connectionString, string sql, CancellationToken ct)
    {
        // Split on GO batches (case-insensitive, standalone line)
        var batches = Regex.Split(sql, @"^\s*GO\s*$",
                          RegexOptions.Multiline | RegexOptions.IgnoreCase)
                      .Where(b => !string.IsNullOrWhiteSpace(b))
                      .ToList();

        int totalRows = 0;
        await using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync(ct).ConfigureAwait(false);

        foreach (var batch in batches)
        {
            var rows = await conn.ExecuteAsync(new CommandDefinition(batch, cancellationToken: ct))
                .ConfigureAwait(false);
            totalRows += rows;
        }

        return totalRows;
    }

    // ── Parameterized DML ─────────────────────────────────────────────────

    public async Task<int> ExecuteDmlAsync(string connectionString, string sql,
        Dictionary<string, object?> parameters, CancellationToken ct)
    {
        var dynParams = new DynamicParameters();
        foreach (var kv in parameters)
            dynParams.Add(kv.Key, kv.Value);

        await using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync(ct).ConfigureAwait(false);
        return await conn.ExecuteAsync(new CommandDefinition(sql, dynParams, cancellationToken: ct))
            .ConfigureAwait(false);
    }

    // ── SELECT-only query ─────────────────────────────────────────────────

    public async Task<IEnumerable<Dictionary<string, object?>>> ExecuteQueryAsync(
        string connectionString, string sql,
        Dictionary<string, object?> parameters,
        CancellationToken ct)
    {
        EnforceSelectOnly(sql);

        var dynParams = new DynamicParameters();
        foreach (var kv in parameters)
            dynParams.Add(kv.Key, kv.Value);

        await using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync(ct).ConfigureAwait(false);

        var rows = (await conn.QueryAsync(
            new CommandDefinition(sql, dynParams, cancellationToken: ct))
            .ConfigureAwait(false)).ToList();

        if (rows.Count > SelectRowCap)
            throw new InvalidOperationException(
                $"Query returned {rows.Count} rows which exceeds the cap of {SelectRowCap}.");

        return rows.Select(r =>
        {
            var dict = (IDictionary<string, object>)r;
            return dict.ToDictionary(kv => kv.Key, kv => (object?)kv.Value);
        });
    }

    // ── Multi-statement server-assembled batch execution (internal-only) ─────

    public async Task<IEnumerable<Dictionary<string, object?>>> ExecuteQueryBatchAsync(
        string connectionString, string sql,
        Dictionary<string, object?> parameters,
        int? rowCapOverride, CancellationToken ct)
    {
        EnforceSelectOnly(sql);

        var dynParams = new DynamicParameters();
        foreach (var kv in parameters)
            dynParams.Add(kv.Key, kv.Value);

        await using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync(ct).ConfigureAwait(false);

        var rows = (await conn.QueryAsync(
            new CommandDefinition(sql, dynParams, cancellationToken: ct))
            .ConfigureAwait(false)).ToList();

        var effectiveCap = rowCapOverride ?? InternalBatchRowCap;
        if (rows.Count > effectiveCap)
            throw new InvalidOperationException(
                $"Query returned {rows.Count} rows which exceeds the cap of {effectiveCap}.");

        return rows.Select(r =>
        {
            var dict = (IDictionary<string, object>)r;
            return dict.ToDictionary(kv => kv.Key, kv => (object?)kv.Value);
        });
    }

    // ── Schema introspection ──────────────────────────────────────────────

    public async Task<IEnumerable<RemoteDbObjectDTO>> GetServerObjectsAsync(
        string connectionString, CancellationToken ct)
    {
        const string sql = @"
            SELECT TABLE_SCHEMA   AS SchemaName,
                   TABLE_NAME     AS ObjectName,
                   TABLE_TYPE     AS ObjectType
            FROM   INFORMATION_SCHEMA.TABLES
            UNION ALL
            SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE
            FROM   INFORMATION_SCHEMA.ROUTINES
            ORDER BY SchemaName, ObjectType, ObjectName";

        await using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync(ct).ConfigureAwait(false);

        return await conn.QueryAsync<RemoteDbObjectDTO>(
            new CommandDefinition(sql, cancellationToken: ct))
            .ConfigureAwait(false);
    }

    // ── Existence check (used before RESTORE ... REPLACE / CREATE DATABASE) ───────────────

    public async Task<bool> DatabaseExistsAsync(string adminConnectionString,string databaseName,CancellationToken ct)
    {
        await using var connection =new SqlConnection(adminConnectionString);

        await connection.OpenAsync(ct).ConfigureAwait(false);

        const string sql = @"SELECT CASE
                                WHEN EXISTS
                                (
                                    SELECT 1
                                    FROM sys.databases
                                    WHERE name = @DatabaseName
                                )
                                THEN 1
                                ELSE 0
                            END;";

        var result = await connection.ExecuteScalarAsync<int>(
            new CommandDefinition(
                sql,
                new
                {
                    DatabaseName = databaseName
                },
                cancellationToken: ct))
            .ConfigureAwait(false);

        return result == 1;
    }

    // ── Client-DB schema introspection (Analytics Catalog Wizard) ─────────

    public async Task<IEnumerable<SchemaTableColumnDTO>> GetServerColumnsAsync(
        string connectionString, byte dbType, CancellationToken ct)
    {
        switch (dbType)
        {
            case (byte)DbType.SqlServer:
                return await GetSqlServerColumnsAsync(connectionString, ct).ConfigureAwait(false);
            default:
                throw new NotSupportedException(
                    $"Client-DB schema introspection for DbType={dbType} is not yet implemented (planned as a follow-up).");
        }
    }

    public async Task<IEnumerable<SchemaForeignKeyDTO>> GetServerForeignKeysAsync(
        string connectionString, byte dbType, CancellationToken ct)
    {
        switch (dbType)
        {
            case (byte)DbType.SqlServer:
                return await GetSqlServerForeignKeysAsync(connectionString, ct).ConfigureAwait(false);
            default:
                throw new NotSupportedException(
                    $"Client-DB schema introspection for DbType={dbType} is not yet implemented (planned as a follow-up).");
        }
    }

    private async Task<IEnumerable<SchemaTableColumnDTO>> GetSqlServerColumnsAsync(
        string connectionString, CancellationToken ct)
    {
        const string sql = @"
            SELECT t.TABLE_SCHEMA AS SchemaName, t.TABLE_NAME AS TableName,
                   c.COLUMN_NAME AS ColumnName, c.DATA_TYPE AS DataType,
                   c.CHARACTER_MAXIMUM_LENGTH AS MaxLength, c.NUMERIC_PRECISION AS NumericPrecision,
                   c.NUMERIC_SCALE AS NumericScale, c.IS_NULLABLE AS IsNullable,
                   c.ORDINAL_POSITION AS OrdinalPosition,
                   CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey
            FROM INFORMATION_SCHEMA.TABLES t
            JOIN INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
            LEFT JOIN (
                SELECT ku.TABLE_SCHEMA, ku.TABLE_NAME, ku.COLUMN_NAME
                FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
                  ON tc.CONSTRAINT_NAME=ku.CONSTRAINT_NAME AND tc.TABLE_SCHEMA=ku.TABLE_SCHEMA
                WHERE tc.CONSTRAINT_TYPE='PRIMARY KEY'
            ) pk ON pk.TABLE_SCHEMA=t.TABLE_SCHEMA AND pk.TABLE_NAME=t.TABLE_NAME AND pk.COLUMN_NAME=c.COLUMN_NAME
            WHERE t.TABLE_TYPE='BASE TABLE'
            ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME, c.ORDINAL_POSITION";

        await using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync(ct).ConfigureAwait(false);

        return await conn.QueryAsync<SchemaTableColumnDTO>(
            new CommandDefinition(sql, cancellationToken: ct))
            .ConfigureAwait(false);
    }

    private async Task<IEnumerable<SchemaForeignKeyDTO>> GetSqlServerForeignKeysAsync(
        string connectionString, CancellationToken ct)
    {
        const string sql = @"
            SELECT fk.name AS ConstraintName,
                   sch1.name AS FromSchema, tp.name AS FromTable, cp.name AS FromColumn,
                   sch2.name AS ToSchema,   tr.name AS ToTable,   cr.name AS ToColumn
            FROM sys.foreign_keys fk
            JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
            JOIN sys.tables  tp  ON tp.object_id = fkc.parent_object_id
            JOIN sys.schemas sch1 ON sch1.schema_id = tp.schema_id
            JOIN sys.columns cp  ON cp.object_id = fkc.parent_object_id AND cp.column_id = fkc.parent_column_id
            JOIN sys.tables  tr  ON tr.object_id = fkc.referenced_object_id
            JOIN sys.schemas sch2 ON sch2.schema_id = tr.schema_id
            JOIN sys.columns cr  ON cr.object_id = fkc.referenced_object_id AND cr.column_id = fkc.referenced_column_id
            ORDER BY tp.name, fk.name, fkc.constraint_column_id";

        await using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync(ct).ConfigureAwait(false);

        return await conn.QueryAsync<SchemaForeignKeyDTO>(
            new CommandDefinition(sql, cancellationToken: ct))
            .ConfigureAwait(false);
    }

    // ── Helpers ───────────────────────────────────────────────────────────

    private static void EnforceSelectOnly(string sql)
    {
        var trimmed = sql.TrimStart();

        if (!_selectOnlyRegex.IsMatch(trimmed))
            throw new InvalidOperationException("Only SELECT queries are allowed.");

        if (_nonSelectRegex.IsMatch(trimmed))
            throw new InvalidOperationException(
                "Query contains non-SELECT statements which are not permitted.");
    }
}