using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using GB5Shared.GOP.GBQueryExecutor.Models;
using GB5Shared.GOP.GBQueryExecutor;
using GB5Shared.GOP.GBQueryExecutor.DTOs;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;

namespace GB5Shared.GOP.GBQueryExecutor
{
    // ============================================================
    // GBQueryExecutorBLL — Enforces 6 safety rules on every query.
    //
    // Rule 1 (SELECT-only): Blocked DML/DDL keyword scan on the SQL template.
    //         Runs at ValidateAsync time and is stored with IsValidated=0 in DB (0=Yes/Validated).
    //         Execution methods reject IsValidated=1 queries (1=No/Not validated).
    // Rule 2 (Read-only connection): Infrastructure concern. This BLL uses IQueryExecutor
    //         with useReadUncommitted=false (committed reads). The DB login used by
    //         IQueryExecutor for this module must have SELECT-only grants.
    // Rule 3 (TenantId injection): @TenantId always added to DynamicParameters.
    //         SQL template must include AND TENANTID = @TenantId (checked at validate time).
    // Rule 4 (Parameterized binding): DynamicParameters used exclusively — no interpolation.
    // Rule 5 (Hard row cap): SELECT TOP (@RowCap) ... wraps every template at runtime.
    // Rule 6 (Timeout ceiling): CancellationToken.CancelAfter(HardTimeoutMs) per execution.
    // ============================================================
    public class GBQueryExecutorBLL : IGBQueryExecutor
    {
        private readonly IGBQueryExecutorDAL      _Dal;
        private readonly IQueryExecutor           _QueryExecutor;
        private readonly ILogger<GBQueryExecutorBLL> _Logger;

        // Blocked keywords — full-word matches using \b word boundary.
        private static readonly Regex BlockedKeywordsRegex = new(
            @"\b(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|TRUNCATE|EXEC|EXECUTE|MERGE|GRANT|REVOKE|DENY|OPENROWSET|OPENDATASOURCE|BULK|DBCC)\b",
            RegexOptions.IgnoreCase | RegexOptions.Compiled);

        // Strip SQL comments before keyword scanning.
        private static readonly Regex CommentStripper = new(
            @"/\*.*?\*/|--[^\r\n]*",
            RegexOptions.Singleline | RegexOptions.Compiled);

        // Check that @TenantId appears as a parameterized reference in the SQL.
        private static readonly Regex TenantIdParamCheck = new(
            @"@TenantId\b",
            RegexOptions.IgnoreCase | RegexOptions.Compiled);

        public GBQueryExecutorBLL(
            IGBQueryExecutorDAL      dal,
            IQueryExecutor           queryExecutor,
            ILogger<GBQueryExecutorBLL> logger)
        {
            _Dal           = dal;
            _QueryExecutor = queryExecutor;
            _Logger        = logger;
        }

        // ═══════════════════════════════════════════════════════════
        // VALIDATE
        // Checks all 6 safety rules against the SQL template.
        // Marks IsValidated=1 on success.
        // ═══════════════════════════════════════════════════════════
        public async Task<QueryValidationResult> ValidateAsync(
            string queryCode,
            LoginDTO loginDTO,
            CancellationToken ct = default)
        {
            var errors = new List<string>();

            var query = await _Dal.GetConfiguredQueryByCode(queryCode, loginDTO);
            if (query is null)
            {
                errors.Add($"Configured query '{queryCode}' not found or inactive.");
                return QueryValidationResult.Fail(errors);
            }

            var profile = await _Dal.GetIntentProfileById(query.IntentProfileId, loginDTO);
            if (profile is null)
            {
                errors.Add($"Intent profile id={query.IntentProfileId} not found.");
                return QueryValidationResult.Fail(errors);
            }

            var sql = query.SqlTemplate ?? string.Empty;
            var strippedSql = CommentStripper.Replace(sql, " ").Trim();

            // Rule 1: SELECT-only — blocked DML/DDL keyword scan.
            if (BlockedKeywordsRegex.IsMatch(strippedSql))
            {
                errors.Add("Rule 1: SQL template contains prohibited DML/DDL keywords " +
                           "(INSERT, UPDATE, DELETE, DROP, ALTER, etc.).");
            }

            // Rule 1 continued: first meaningful token must be SELECT or WITH (CTE).
            var firstToken = strippedSql.Split(new[] { ' ', '\t', '\r', '\n' },
                    StringSplitOptions.RemoveEmptyEntries)
                .FirstOrDefault() ?? string.Empty;

            if (!firstToken.Equals("SELECT", StringComparison.OrdinalIgnoreCase) &&
                !firstToken.Equals("WITH", StringComparison.OrdinalIgnoreCase))
            {
                errors.Add($"Rule 1: SQL template must start with SELECT or WITH (for CTEs). " +
                           $"Found: '{firstToken}'.");
            }

            // Rule 3: @TenantId parameter must be present in the template.
            if (!TenantIdParamCheck.IsMatch(strippedSql))
            {
                errors.Add("Rule 3: SQL template must include 'AND TENANTID = @TenantId' " +
                           "in its WHERE clause for tenant isolation.");
            }

            // Rule 5: Warn if template already uses its own TOP — the outer wrapper will be redundant
            // but still safe.  Not an error; just a note.
            if (strippedSql.Contains("SELECT TOP", StringComparison.OrdinalIgnoreCase))
            {
                _Logger.LogWarning(
                    "GBQueryExecutor: query '{QueryCode}' already contains TOP — outer cap wrapper still applied",
                    queryCode);
            }

            if (errors.Count > 0)
            {
                _Logger.LogWarning(
                    "GBQueryExecutor: validation failed for '{QueryCode}' with {ErrorCount} error(s)",
                    queryCode, errors.Count);
                return QueryValidationResult.Fail(errors);
            }

            // Mark validated in DB so execution methods can proceed.
            await _Dal.MarkQueryValidated(query.ConfiguredQueryId, loginDTO, ct);

            _Logger.LogInformation(
                "GBQueryExecutor: query '{QueryCode}' validated successfully (id={Id})",
                queryCode, query.ConfiguredQueryId);

            return QueryValidationResult.Ok();
        }

        // ═══════════════════════════════════════════════════════════
        // EXECUTE SCALAR
        // ═══════════════════════════════════════════════════════════
        public async Task<QueryScalarResult<T>> ExecuteScalarAsync<T>(
            string                      queryCode,
            Dictionary<string, object?> parameters,
            LoginDTO                    loginDTO,
            string?                     callerModule = null,
            CancellationToken           ct           = default)
        {
            var sw = Stopwatch.StartNew();
            GBConfiguredQueryDTO? query = null;
            GBQueryIntentProfileDTO? profile = null;

            try
            {
                (query, profile) = await LoadAndGuard(queryCode, loginDTO);

                using var timeout = CancellationTokenSource.CreateLinkedTokenSource(ct);
                timeout.CancelAfter(profile.HardTimeoutMs);  // Rule 6: timeout ceiling

                var (wrappedSql, dynParams) = BuildSafeQuery(query, profile, parameters, loginDTO);

                var value = await _QueryExecutor.ExecuteScalarAsync<T>(
                    loginDTO, wrappedSql, dynParams,
                    cancellationToken: timeout.Token);

                sw.Stop();
                await LogAsync(query, profile, loginDTO, callerModule, sw.ElapsedMilliseconds, 1, true, null, parameters);

                return new QueryScalarResult<T> { Value = value, DurationMs = sw.ElapsedMilliseconds, IsSuccess = true };
            }
            catch (Exception ex)
            {
                sw.Stop();
                if (query is not null && profile is not null)
                    await LogAsync(query, profile, loginDTO, callerModule, sw.ElapsedMilliseconds, 0, false, ex.Message, parameters);

                _Logger.LogError(ex, "GBQueryExecutor: ExecuteScalarAsync failed for '{QueryCode}'", queryCode);
                return new QueryScalarResult<T> { IsSuccess = false, Error = ex.Message, DurationMs = sw.ElapsedMilliseconds };
            }
        }

        // ═══════════════════════════════════════════════════════════
        // EXECUTE ROW
        // ═══════════════════════════════════════════════════════════
        public async Task<QueryRowResult> ExecuteRowAsync(
            string                      queryCode,
            Dictionary<string, object?> parameters,
            LoginDTO                    loginDTO,
            string?                     callerModule = null,
            CancellationToken           ct           = default)
        {
            var sw = Stopwatch.StartNew();
            GBConfiguredQueryDTO? query = null;
            GBQueryIntentProfileDTO? profile = null;

            try
            {
                (query, profile) = await LoadAndGuard(queryCode, loginDTO);

                using var timeout = CancellationTokenSource.CreateLinkedTokenSource(ct);
                timeout.CancelAfter(profile.HardTimeoutMs);  // Rule 6

                var (wrappedSql, dynParams) = BuildSafeQuery(query, profile, parameters, loginDTO);

                // Use dynamic — Dapper returns DapperRow (which implements IDictionary<string,object>).
                // Avoids ArgumentException: "Invalid type owner for DynamicMethod" that occurs
                // when Dapper tries to IL-emit a deserializer for the IDictionary<> interface type.
                var raw = await _QueryExecutor.QuerySingleAsync<dynamic>(
                    loginDTO, wrappedSql, dynParams);

                sw.Stop();
                bool found = raw is not null;
                await LogAsync(query, profile, loginDTO, callerModule, sw.ElapsedMilliseconds, found ? 1 : 0, true, null, parameters);

                var row = found
                    ? (IReadOnlyDictionary<string, object?>)((IDictionary<string, object>)raw)
                        .ToDictionary(kv => kv.Key, kv => (object?)kv.Value)
                    : new Dictionary<string, object?>();

                return new QueryRowResult { Row = row, Found = found, DurationMs = sw.ElapsedMilliseconds };
            }
            catch (Exception ex)
            {
                sw.Stop();
                if (query is not null && profile is not null)
                    await LogAsync(query, profile, loginDTO, callerModule, sw.ElapsedMilliseconds, 0, false, ex.Message, parameters);

                _Logger.LogError(ex, "GBQueryExecutor: ExecuteRowAsync failed for '{QueryCode}'", queryCode);
                throw;
            }
        }

        // ═══════════════════════════════════════════════════════════
        // EXECUTE DATASET
        // ═══════════════════════════════════════════════════════════
        public async Task<QueryDataResult> ExecuteDatasetAsync(
            string                      queryCode,
            Dictionary<string, object?> parameters,
            LoginDTO                    loginDTO,
            string?                     callerModule = null,
            CancellationToken           ct           = default)
        {
            var sw = Stopwatch.StartNew();
            GBConfiguredQueryDTO? query = null;
            GBQueryIntentProfileDTO? profile = null;

            try
            {
                (query, profile) = await LoadAndGuard(queryCode, loginDTO);

                using var timeout = CancellationTokenSource.CreateLinkedTokenSource(ct);
                timeout.CancelAfter(profile.HardTimeoutMs);  // Rule 6

                var (wrappedSql, dynParams) = BuildSafeQuery(query, profile, parameters, loginDTO);

                // Use dynamic — same reason as ExecuteRowAsync (avoid DynamicMethod interface error).
                var rawRows = await _QueryExecutor.QueryAsync<dynamic>(
                    loginDTO, wrappedSql, dynParams,
                    cancellationToken: timeout.Token);

                sw.Stop();
                var rows = (rawRows ?? []).Select(r =>
                    (IReadOnlyDictionary<string, object?>)((IDictionary<string, object>)r)
                        .ToDictionary(kv => kv.Key, kv => (object?)kv.Value))
                    .ToList();

                bool capReached = rows.Count >= profile.HardRowCap;

                if (rows.Count >= profile.SoftRowCap)
                    _Logger.LogWarning(
                        "GBQueryExecutor: query '{QueryCode}' returned {Rows} rows — soft cap {Soft} reached",
                        queryCode, rows.Count, profile.SoftRowCap);

                await LogAsync(query, profile, loginDTO, callerModule, sw.ElapsedMilliseconds, rows.Count, true, null, parameters);

                return new QueryDataResult
                {
                    Rows         = rows,
                    RowsReturned = rows.Count,
                    IsCapReached = capReached,
                    DurationMs   = sw.ElapsedMilliseconds
                };
            }
            catch (Exception ex)
            {
                sw.Stop();
                if (query is not null && profile is not null)
                    await LogAsync(query, profile, loginDTO, callerModule, sw.ElapsedMilliseconds, 0, false, ex.Message, parameters);

                _Logger.LogError(ex, "GBQueryExecutor: ExecuteDatasetAsync failed for '{QueryCode}'", queryCode);
                throw;
            }
        }

        // ═══════════════════════════════════════════════════════════
        // PRIVATE HELPERS
        // ═══════════════════════════════════════════════════════════

        /// <summary>
        /// Loads the query and its profile, rejects un-validated queries.
        /// </summary>
        private async Task<(GBConfiguredQueryDTO Query, GBQueryIntentProfileDTO Profile)>
            LoadAndGuard(string queryCode, LoginDTO loginDTO)
        {
            var query = await _Dal.GetConfiguredQueryByCode(queryCode, loginDTO)
                ?? throw new InvalidOperationException(
                    $"GBQueryExecutor: configured query '{queryCode}' not found.");

            if (query.IsValidated == 0)
                throw new InvalidOperationException(
                    $"GBQueryExecutor: query '{queryCode}' has not been validated. " +
                    "Call ValidateAsync() first.");

            var profile = await _Dal.GetIntentProfileById(query.IntentProfileId, loginDTO)
                ?? throw new InvalidOperationException(
                    $"GBQueryExecutor: intent profile id={query.IntentProfileId} not found.");

            return (query, profile);
        }

        // Matches every @ParamName reference in SQL (word-boundary safe).
        private static readonly Regex SqlParamRefRegex = new(
            @"@([A-Za-z_][A-Za-z0-9_]*)",
            RegexOptions.Compiled);

        /// <summary>
        /// Builds the safe wrapped SQL and DynamicParameters.
        ///
        /// Rule 3: @TenantId injected from LoginDTO — callers cannot override.
        /// Rule 4: DynamicParameters only — no string interpolation.
        /// Rule 5: SELECT TOP (@RowCap) wraps the user template.
        ///
        /// Parameter filtering: only params whose @Name appears in the wrapped SQL are bound.
        /// This prevents Dapper "Invalid type owner for DynamicMethod" when the caller
        /// supplies many null-valued entity fields that the SQL doesn't reference.
        /// </summary>
        private static (string WrappedSql, DynamicParameters Params) BuildSafeQuery(
            GBConfiguredQueryDTO        query,
            GBQueryIntentProfileDTO     profile,
            Dictionary<string, object?> callerParams,
            LoginDTO                    loginDTO)
        {
            // Rule 5: hard row cap wrapper
            var wrappedSql = $@"SELECT TOP (@RowCap) _gbq.*
FROM   ({query.SqlTemplate}) AS _gbq";

            // Collect all @ParamName tokens actually referenced in the wrapped SQL.
            var referencedInSql = new HashSet<string>(
                SqlParamRefRegex.Matches(wrappedSql).Select(m => m.Groups[1].Value),
                StringComparer.OrdinalIgnoreCase);

            // Rule 4: DynamicParameters — no string interpolation of user values.
            var dp = new DynamicParameters();

            // Rule 5 param — always present.
            dp.Add("RowCap", profile.HardRowCap);

            // Rule 3: inject TenantId from server-side LoginDTO — cannot be overridden.
            dp.Add("TenantId", loginDTO.ClientId);

            // Caller-supplied params — only bind those referenced in the SQL.
            // Skipping unreferenced null params avoids Dapper IL-emission failures
            // when the caller sends many entity fields the SQL doesn't use.
            foreach (var kv in callerParams)
            {
                var key = kv.Key;
                if (key.Equals("TenantId", StringComparison.OrdinalIgnoreCase) ||
                    key.Equals("RowCap",   StringComparison.OrdinalIgnoreCase))
                    continue;  // Safety params already set above — cannot override.

                if (!referencedInSql.Contains(key))
                    continue;  // Not used by this SQL — skip to keep param list minimal.

                dp.Add(key, kv.Value);
            }

            return (wrappedSql, dp);
        }

        private async Task LogAsync(
            GBConfiguredQueryDTO    query,
            GBQueryIntentProfileDTO profile,
            LoginDTO                loginDTO,
            string?                 callerModule,
            long                    durationMs,
            int                     rowsReturned,
            bool                    isSuccess,
            string?                 errorMessage,
            Dictionary<string, object?> parameters)
        {
            // Scrub TenantId from logged parameters (already in the tenant context header).
            var logParams = parameters
                .Where(kv => !kv.Key.Equals("TenantId", StringComparison.OrdinalIgnoreCase))
                .ToDictionary(kv => kv.Key, kv => kv.Value);

            await _Dal.LogExecution(new GBQueryExecutionLogDTO
            {
                ConfiguredQueryId = query.ConfiguredQueryId,
                TenantId          = loginDTO.ClientId,
                QueryCode         = query.QueryCode,
                QueryIntent       = profile.QueryIntent,
                CallerModule      = callerModule,
                DurationMs        = durationMs,
                RowsReturned      = rowsReturned,
                IsSuccess         = isSuccess,
                ErrorMessage      = errorMessage,
                ParametersJson    = System.Text.Json.JsonSerializer.Serialize(logParams),
                CreatedById       = loginDTO.UserId
            }, loginDTO);
        }
    }
}