using EAIAdminDAL.DTO;
using EAIAdminDAL.Query;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace EAIAdminDAL.CustomCode
{
    public class EAIAgentRouteAdminDAL : IEAIAgentRouteAdminDAL
    {
        private readonly IQueryExecutor _qe;
        private readonly AutoNumber _autoNumber;
        private readonly ILogger<EAIAgentRouteAdminDAL> _logger;

        public EAIAgentRouteAdminDAL(IQueryExecutor qe, AutoNumber autoNumber, ILogger<EAIAgentRouteAdminDAL> logger)
        {
            _qe         = qe         ?? throw new ArgumentNullException(nameof(qe));
            _autoNumber = autoNumber ?? throw new ArgumentNullException(nameof(autoNumber));
            _logger     = logger     ?? throw new ArgumentNullException(nameof(logger));
        }

        // NOTE: none of these queries filter by TenantId — MEAIAGENTROUTE is global config, not
        // tenant-scoped data (see EAIAgentRouteAdminQB's doc comment). TenantId is still
        // stamped on insert for audit/ownership only.

        public async Task<IEnumerable<EAIAgentRouteAdminDTO>> GetAgentRouteListAsync(
            string? search, LoginDTO login, CancellationToken ct = default)
        {
            ct.ThrowIfCancellationRequested();

            try
            {
                var searchPattern = string.IsNullOrWhiteSpace(search) ? null : $"%{search}%";

                var result = await _qe.QueryAsync<EAIAgentRouteAdminDTO>(
                    login,
                    EAIAgentRouteAdminQB.GET_AGENTROUTE_LIST,
                    new { SearchPattern = searchPattern },
                    cancellationToken: ct).ConfigureAwait(false);

                return result ?? [];
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning("DAL | GetAgentRouteList | Cancelled | Tenant={TenantId}", login.ClientId);
                throw;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "DAL | GetAgentRouteList | Error | Tenant={TenantId}", login.ClientId);
                throw;
            }
        }

        public async Task<EAIAgentRouteAdminDTO?> GetAgentRouteAsync(
            int agentRouteId, LoginDTO login, CancellationToken ct = default)
        {
            ct.ThrowIfCancellationRequested();

            try
            {
                return await _qe.QuerySingleAsync<EAIAgentRouteAdminDTO>(
                    login,
                    EAIAgentRouteAdminQB.GET_AGENTROUTE,
                    new { AgentRouteId = agentRouteId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning("DAL | GetAgentRoute | Cancelled | AgentRouteId={Id}", agentRouteId);
                throw;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "DAL | GetAgentRoute | Error | AgentRouteId={Id}", agentRouteId);
                throw;
            }
        }

        public async Task<int> InsertAgentRouteAsync(
            EAIAgentRouteAdminDTO dto, LoginDTO login, CancellationToken ct = default)
        {
            ct.ThrowIfCancellationRequested();

            try
            {
                // AGENTROUTEID is a plain INT PK (not IDENTITY) — must be generated here.
                var auto = await _autoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.EAIAGENTROUTE, login)
                    .ConfigureAwait(false);
                var newId = auto.StartNumber;
                var now = DateTime.UtcNow;

                await _qe.ExecuteAsync(
                    login,
                    EAIAgentRouteAdminQB.INSERT_AGENTROUTE,
                    new
                    {
                        AgentRouteId   = newId,
                        // Audit/ownership only — see class-level note; not used as an access filter.
                        TenantId       = login.ClientId,
                        dto.Slug,
                        dto.InitiativeCode,
                        dto.DefaultCapability,
                        dto.Module,
                        dto.Summary,
                        dto.Capabilities,
                        dto.Aliases,
                        dto.AcceptsFile,
                        dto.AcceptsText,
                        dto.RequiresFile,
                        dto.EAIRemoteId,
                        dto.Version,
                        CreatedById    = login.UserId,
                        CreatedOn      = now
                    },
                    cancellationToken: ct).ConfigureAwait(false);

                _logger.LogInformation("DAL | InsertAgentRoute | Completed | NewId={NewId}", newId);
                return newId;
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning("DAL | InsertAgentRoute | Cancelled | Slug={Slug}", dto.Slug);
                throw;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "DAL | InsertAgentRoute | Error | Slug={Slug}", dto.Slug);
                throw;
            }
        }

        public async Task UpdateAgentRouteAsync(
            EAIAgentRouteAdminDTO dto, LoginDTO login, CancellationToken ct = default)
        {
            ct.ThrowIfCancellationRequested();

            try
            {
                await _qe.ExecuteAsync(
                    login,
                    EAIAgentRouteAdminQB.UPDATE_AGENTROUTE,
                    new
                    {
                        dto.AgentRouteId,
                        dto.InitiativeCode,
                        dto.DefaultCapability,
                        dto.Module,
                        dto.Summary,
                        dto.Capabilities,
                        dto.Aliases,
                        dto.AcceptsFile,
                        dto.AcceptsText,
                        dto.RequiresFile,
                        dto.EAIRemoteId,
                        dto.Version,
                        ModifiedById   = login.UserId,
                        ModifiedOn     = DateTime.UtcNow
                    },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning("DAL | UpdateAgentRoute | Cancelled | AgentRouteId={Id}", dto.AgentRouteId);
                throw;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "DAL | UpdateAgentRoute | Error | AgentRouteId={Id}", dto.AgentRouteId);
                throw;
            }
        }

        public async Task UpdateEAIRemoteIdAsync(
            int agentRouteId, long eaiRemoteId, LoginDTO login, CancellationToken ct = default)
        {
            ct.ThrowIfCancellationRequested();

            await _qe.ExecuteAsync(
                login,
                EAIAgentRouteAdminQB.UPDATE_EAIREMOTEID,
                new { AgentRouteId = agentRouteId, EAIRemoteId = eaiRemoteId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task DeleteAgentRouteAsync(
            int agentRouteId, LoginDTO login, CancellationToken ct = default)
        {
            ct.ThrowIfCancellationRequested();

            try
            {
                await _qe.ExecuteAsync(
                    login,
                    EAIAgentRouteAdminQB.DELETE_AGENTROUTE,
                    new
                    {
                        AgentRouteId = agentRouteId,
                        ModifiedById = login.UserId,
                        ModifiedOn   = DateTime.UtcNow
                    },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning("DAL | DeleteAgentRoute | Cancelled | AgentRouteId={Id}", agentRouteId);
                throw;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "DAL | DeleteAgentRoute | Error | AgentRouteId={Id}", agentRouteId);
                throw;
            }
        }

        public async Task<bool> SlugExistsAsync(
            string slug, int excludeAgentRouteId, LoginDTO login, CancellationToken ct = default)
        {
            ct.ThrowIfCancellationRequested();

            var count = await _qe.ExecuteScalarAsync<int>(
                login,
                EAIAgentRouteAdminQB.EXISTS_SLUG,
                new { Slug = slug, AgentRouteId = excludeAgentRouteId },
                cancellationToken: ct).ConfigureAwait(false);

            return count == 1;
        }

        public async Task<string?> ResolveSlugForCapabilityAsync(
            string initiativeCode, string capabilityCode, LoginDTO login, CancellationToken ct = default)
        {
            ct.ThrowIfCancellationRequested();

            var candidates = await _qe.QueryAsync<EAIAgentRouteCandidateDTO>(
                login,
                EAIAgentRouteAdminQB.GET_AGENTROUTES_BY_INITIATIVE,
                new { InitiativeCode = initiativeCode },
                cancellationToken: ct).ConfigureAwait(false);

            var match = (candidates ?? []).FirstOrDefault(c =>
                string.Equals(c.DefaultCapability, capabilityCode, StringComparison.OrdinalIgnoreCase)
                || (c.Capabilities ?? string.Empty)
                    .Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
                    .Any(cap => string.Equals(cap, capabilityCode, StringComparison.OrdinalIgnoreCase)));

            return match?.Slug;
        }
    }
}