using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using GB5Shared.CriteriaHandler;
using GB5Shared.GOP.GBQueryExecutor.DTOs;
using GB5Shared.GOP.GBQueryExecutor;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;

namespace GB5Shared.GOP.GBQueryExecutor
{
    public class GBQueryExecutorDAL : IGBQueryExecutorDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation    _Validation;

        public GBQueryExecutorDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _QueryExecutor = queryExecutor;
            _Validation    = validation;
        }

        public async Task<GBConfiguredQueryDTO?> GetConfiguredQueryByCode(string queryCode, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<GBConfiguredQueryDTO>(
                    loginDTO,
                    GBQueryExecutorQB.GET_CONFIGURED_QUERY_BY_CODE,
                    new { TenantId = loginDTO.ClientId, QueryCode = queryCode });
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<GBConfiguredQueryDTO?> GetConfiguredQueryById(int configuredQueryId, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<GBConfiguredQueryDTO>(
                    loginDTO,
                    GBQueryExecutorQB.GET_CONFIGURED_QUERY_BY_ID,
                    new { TenantId = loginDTO.ClientId, ConfiguredQueryId = configuredQueryId });
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<GBQueryIntentProfileDTO?> GetIntentProfileById(int intentProfileId, LoginDTO loginDTO)
        {
            try
            {
                // Intent profiles are global/seeded — no TENANTID filter.
                // Use static connection name since this table has no tenant isolation.
                return await _QueryExecutor.QuerySingleAsync<GBQueryIntentProfileDTO>(
                    loginDTO,
                    GBQueryExecutorQB.GET_INTENT_PROFILE_BY_ID,
                    new { IntentProfileId = intentProfileId });
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task<GBQueryIntentProfileDTO?> GetIntentProfileByCode(string intentCode, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<GBQueryIntentProfileDTO>(
                    loginDTO,
                    GBQueryExecutorQB.GET_INTENT_PROFILE_BY_CODE,
                    new { IntentCode = intentCode });
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task MarkQueryValidated(int configuredQueryId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    GBQueryExecutorQB.MARK_QUERY_VALIDATED,
                    new { TenantId = loginDTO.ClientId, ConfiguredQueryId = configuredQueryId, ModifiedById = loginDTO.UserId },
                    cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        public async Task LogExecution(GBQueryExecutionLogDTO logDto, LoginDTO loginDTO)
        {
            try
            {
                // Fire-and-forget is intentional here: log failures must not fail the caller.
                // Best-effort: swallow exceptions after logging server-side.
                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    GBQueryExecutorQB.LOG_EXECUTION,
                    logDto);
            }
            catch
            {
                // Swallow — execution log is audit-only and must not fail the calling query.
            }
        }

        // ── Management CRUD ───────────────────────────────────────────

        public async Task<int> SaveConfiguredQuery(
            GBConfiguredQueryDTO dto, LoginDTO loginDTO, DbTransaction? tx = null, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId = loginDTO.ClientId;
                return await _QueryExecutor.ExecuteIdentityAsync(
                    loginDTO,
                    GBQueryExecutorQB.SAVE_CONFIGURED_QUERY,
                    new
                    {
                        dto.QueryCode,
                        dto.QueryName,
                        dto.SqlTemplate,
                        dto.ParameterJson,
                        dto.ResultMappingJson,
                        dto.IntentProfileId,
                        dto.TenantId,
                        SourceType   = 5,
                        CreatedById  = loginDTO.UserId,
                        ModifiedById = loginDTO.UserId
                    },
                    tx);
            }
            catch (Exception ex)
            {
                throw new Exception(
                    await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task UpdateConfiguredQuery(
            GBConfiguredQueryDTO dto, LoginDTO loginDTO, DbTransaction? tx = null, CancellationToken ct = default)
        {
            try
            {
                dto.TenantId = loginDTO.ClientId;
                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    GBQueryExecutorQB.UPDATE_CONFIGURED_QUERY,
                    new
                    {
                        dto.ConfiguredQueryId,
                        dto.QueryName,
                        dto.SqlTemplate,
                        dto.ParameterJson,
                        dto.ResultMappingJson,
                        dto.IntentProfileId,
                        dto.TenantId,
                        ModifiedById = loginDTO.UserId
                    },
                    tx,
                    ct);
            }
            catch (Exception ex)
            {
                throw new Exception(
                    await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task DeleteConfiguredQuery(
            int configuredQueryId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    GBQueryExecutorQB.DELETE_CONFIGURED_QUERY,
                    new { TenantId = loginDTO.ClientId, ConfiguredQueryId = configuredQueryId, ModifiedById = loginDTO.UserId },
                    cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage));
            }
        }

        public async Task<IEnumerable<GBConfiguredQueryDTO>> GetConfiguredQueryList(
            LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<GBConfiguredQueryDTO>(
                    loginDTO,
                    GBQueryExecutorQB.GET_CONFIGURED_QUERY_LIST,
                    new { TenantId = loginDTO.ClientId },
                    cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(
                    await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        // Whitelists which criteria FieldName values may filter this picklist and maps
        // each to its real MGBQUERYINTENTPROFILE column. Doubles as a security control:
        // CriteriaBuilder interpolates FieldName directly into the SQL column reference
        // (not parameterized), so any FieldName not listed here is dropped first.
        private static readonly Dictionary<string, string> _intentProfileFilterableFields =
            new(StringComparer.OrdinalIgnoreCase)
            {
                ["IntentCode"]  = "INTENTCODE",
                ["QueryIntent"] = "QUERYINTENT",
                ["Status"]      = "STATUS",
            };

        public async Task<IEnumerable<GBQueryIntentProfileDTO>> GetSelectListIntentProfiles(
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                ApplyIntentProfileFieldNameWhitelist(criteriaDTO);

                var parameters = new DynamicParameters();
                string baseSql = GBQueryExecutorQB.GET_INTENT_PROFILE_SELECT_LIST;
                var (whereClause, _) = CriteriaBuilder.Build(criteriaDTO, parameters, baseSql);
                string sql = baseSql.Replace("/*CRITERIA*/", whereClause);

                return await _QueryExecutor.QueryAsync<GBQueryIntentProfileDTO>(
                    loginDTO, sql, parameters, cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        // Drops any AttributesCriteriaList entry whose FieldName isn't a known
        // filterable column, then rewrites the surviving entries' FieldName to
        // the real column name CriteriaBuilder should emit.
        private static void ApplyIntentProfileFieldNameWhitelist(CriteriaDTO? criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;

                section.AttributesCriteriaList = section.AttributesCriteriaList
                    .Where(attr => attr?.FieldName != null && _intentProfileFilterableFields.ContainsKey(attr.FieldName))
                    .ToList();

                foreach (var attr in section.AttributesCriteriaList)
                    attr.FieldName = _intentProfileFilterableFields[attr.FieldName];
            }
        }

        // Whitelists which criteria FieldName values may filter this picklist and maps
        // each to its real MGBCONFIGUREDQUERY/MGBQUERYINTENTPROFILE column. Doubles as a
        // security control: CriteriaBuilder interpolates FieldName directly into the SQL
        // column reference (not parameterized), so any FieldName not listed here is dropped.
        private static readonly Dictionary<string, string> _configuredQueryFilterableFields =
            new(StringComparer.OrdinalIgnoreCase)
            {
                ["QueryCode"]       = "QUERYCODE",
                ["QueryName"]       = "QUERYNAME",
                ["IntentProfileId"] = "INTENTPROFILEID",
                ["IntentCode"]      = "INTENTCODE",
                ["Status"]          = "STATUS",
            };

        public async Task<IEnumerable<GBConfiguredQueryDTO>> GetSelectListConfiguredQuery(
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                ApplyConfiguredQueryFieldNameWhitelist(criteriaDTO);

                var parameters = new DynamicParameters();
                parameters.Add("TenantId", loginDTO.ClientId);

                string baseSql = GBQueryExecutorQB.GET_CONFIGURED_QUERY_SELECT_LIST;
                var (whereClause, _) = CriteriaBuilder.Build(criteriaDTO, parameters, baseSql);
                string sql = baseSql.Replace("/*CRITERIA*/", whereClause);

                return await _QueryExecutor.QueryAsync<GBConfiguredQueryDTO>(
                    loginDTO, sql, parameters, cancellationToken: ct);
            }
            catch (Exception ex)
            {
                throw new Exception(await _Validation.HandleException(ex, ErrorResponse.GetFetchingMessge));
            }
        }

        // Drops any AttributesCriteriaList entry whose FieldName isn't a known
        // filterable column, then rewrites the surviving entries' FieldName to
        // the real column name CriteriaBuilder should emit.
        private static void ApplyConfiguredQueryFieldNameWhitelist(CriteriaDTO? criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;

                section.AttributesCriteriaList = section.AttributesCriteriaList
                    .Where(attr => attr?.FieldName != null && _configuredQueryFilterableFields.ContainsKey(attr.FieldName))
                    .ToList();

                foreach (var attr in section.AttributesCriteriaList)
                    attr.FieldName = _configuredQueryFilterableFields[attr.FieldName];
            }
        }
    }
}