using Amazon.S3.Model;
using DataBase.Entities.Models;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using System.Diagnostics.CodeAnalysis;
using System.Text.Json;
using User.Insight.Tracker.DataContracts;
using User.Insight.Tracker.DataContracts.Requests;
using User.Insight.Tracker.DataContracts.Response;

namespace User.Insight.Tracker.DataProviders
{
    [ExcludeFromCodeCoverage]
    public class CommonServiceProvider : ICommonServiceProvider
    {
        private readonly ComputraceqaContext _dbContext;
        private readonly ILogger _logger;
        private readonly IWebHostEnvironment _env;
        public CommonServiceProvider(ILogger<CommonServiceProvider> logger, ComputraceqaContext dbContext, IWebHostEnvironment env)
        {
            _logger = logger;
            _dbContext = dbContext;
            _env = env;
        }
        [ExcludeFromCodeCoverage]
        public List<AuditDetails> GetAudit(SearchRequest<AuditRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetAudit()");

            var filter = filters.Filter;

            var emailParam = new MySqlParameter("@p_Email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var searchCodeParam = new MySqlParameter("@p_SearchText", !string.IsNullOrEmpty(filters.SearchText) ? filters.SearchText : DBNull.Value);
            var startedOnParam = new MySqlParameter("@p_StartDate", (filter != null && filter.StartDate.HasValue) ? filter.StartDate : DBNull.Value);
            var endedOnParam = new MySqlParameter("@p_EndDate", (filter != null && filter.EndDate.HasValue) ? filter.EndDate : DBNull.Value);
            var pageParam = new MySqlParameter("@p_PageNumber", filters.PageNumber != 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_PageSize", filters.PageSize != 0 ? filters.PageSize : 10);
            var ignorePagingParam = new MySqlParameter("@p_IgnorePaging", filters.IgnorePaging);
            var sortTypeCodeParam = new MySqlParameter("@p_SortOrder", !string.IsNullOrEmpty(filters.SortOrder) ? filters.SortOrder : DBNull.Value);
            var sortColumnCodeParam = new MySqlParameter("@p_SortColumn", !string.IsNullOrEmpty(filters.SortColumn) ? filters.SortColumn : DBNull.Value);
            var results = _dbContext.AuditDetails
                .FromSqlRaw("CALL GetAuditLogs(@p_SearchText,@p_PageSize, @p_PageNumber, @p_IgnorePaging, @p_StartDate, @p_EndDate, @p_SortOrder, @p_SortColumn , @p_Email)",
                   searchCodeParam, pageSizeParam, pageParam, ignorePagingParam, startedOnParam, endedOnParam, sortTypeCodeParam, sortColumnCodeParam, emailParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<DeleteRole> DeleteRoles(SearchRequest<DeleteRoleRequest> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetAudit()");

            var filter = filters.Filter;

            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);
            var roleIdParam = new MySqlParameter("@p_role_id_to_delete", (filter != null && filter.RoleId > 0) ? filter.RoleId : (object)DBNull.Value);
            var repRoleIdParam = new MySqlParameter("@p_replacement_role_id", (filter != null && filter.RepRoleId > 0) ? filter.RepRoleId : (object)DBNull.Value);

            var results = _dbContext.DeleteRole
                .FromSqlRaw("CALL DeleteRoleWithReassignment(@p_role_id_to_delete,@p_replacement_role_id, @p_email)",
                   roleIdParam, repRoleIdParam, emailParam)
                .ToList();

            return results;
        }


        [ExcludeFromCodeCoverage]
        public List<SmartSearch> GetSmartSearch(JwtTokenDetail userDetails, string EmpCode)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetSmartSearch()");
            var emailParam = new MySqlParameter("@p_emp_code", !string.IsNullOrEmpty(EmpCode) ? EmpCode : DBNull.Value);

            var results = _dbContext.SmartSearch
                .FromSqlRaw("CALL GetEmployeeDetails(@p_emp_code)", emailParam)
                .ToList();

            return results;
        }


        [ExcludeFromCodeCoverage]
        public List<GlobalConfiguration> GetGlobalConfiguration(
    SearchRequest<GlobalConfigurationRequest> filters,
    JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GetGlobalConfiguration()");

            var filter = filters.Filter;

            var sortColumnParam = new MySqlParameter("@p_sort_column", !string.IsNullOrEmpty(filters.SortColumn) ? filters.SortColumn : DBNull.Value);
            var sortDirectionParam = new MySqlParameter("@p_sort_direction", !string.IsNullOrEmpty(filter.SortDirection) ? filter.SortDirection : DBNull.Value);
            var pageNumberParam = new MySqlParameter("@p_page_number", filters.PageNumber > 0 ? filters.PageNumber : 1);
            var pageSizeParam = new MySqlParameter("@p_page_size", filters.PageSize > 0 ? filters.PageSize : 10);
            var filterColumnParam = new MySqlParameter("@p_filter_column", !string.IsNullOrEmpty(filter.FilterColumn) ? filter.FilterColumn : DBNull.Value);
            var filterValueParam = new MySqlParameter("@p_filter_value", !string.IsNullOrEmpty(filter.FilterValue) ? filter.FilterValue : DBNull.Value);
            var ignorePagingParam = new MySqlParameter("@p_ignore_paging", filters.IgnorePaging);
            var emailParam = new MySqlParameter("@p_email", !string.IsNullOrEmpty(userDetails.Email) ? userDetails.Email : DBNull.Value);


            // Map environment name to profile value
            object profileValue;
            var envName = _env.EnvironmentName;
            if (!string.IsNullOrEmpty(envName))
            {
                if (envName.Equals("Staging", StringComparison.OrdinalIgnoreCase))
                {
                    profileValue = "dev";
                }
                else if (envName.Equals("Pre-Production", StringComparison.OrdinalIgnoreCase))
                {
                    profileValue = "qa";
                }
                else if (envName.Equals("Production", StringComparison.OrdinalIgnoreCase))
                {
                    profileValue = "prod";
                }
                else if (envName.Equals("UAT", StringComparison.OrdinalIgnoreCase))
                {
                    profileValue = "uat";
                }
                else
                {
                    profileValue = envName;
                }
            }
            else
            {
                profileValue = DBNull.Value;
            }

            var profileParam = new MySqlParameter("@p_profile", MySqlDbType.VarChar)
            {
                Value = profileValue
            };

            var results = _dbContext.GlobalConfiguration
                .FromSqlRaw("CALL GetApplicationPropertiesByStatus(@p_sort_column, @p_sort_direction, @p_page_number, @p_page_size, @p_filter_column, @p_filter_value, @p_email, @p_ignore_paging, @p_profile)",
                    sortColumnParam, sortDirectionParam, pageNumberParam, pageSizeParam,
                    filterColumnParam, filterValueParam, emailParam, ignorePagingParam, profileParam)
                .ToList();

            return results;
        }

        [ExcludeFromCodeCoverage]
        public List<GlobalConfigurationUpdatePropertiesStatusDB> GlobalPropertiesStatus(SearchRequest<GlobalConfigurationUpdatePropertiesStatus> filters, JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GlobalPropertiesStatus()");

            var filter = filters.Filter;

            var idParam = new MySqlParameter("@p_id", filter.Id != 0 ? filter.Id : DBNull.Value);
            var actionParam = new MySqlParameter("@p_action", !string.IsNullOrEmpty(filter.Action) ? filter.Action : DBNull.Value);

            var results = _dbContext.GlobalConfigurationUpdatePropertiesStatusDB
                .FromSqlRaw("CALL UpdateApplicationPropertiesStatus(@p_id, @p_action)",
                    idParam, actionParam)
                .ToList();

            return results;
        }


        [ExcludeFromCodeCoverage]
        public List<GlobalConfigurationUpdatePropertiesValueDB> GlobalPropertiesValue(
    SearchRequest<GlobalConfigurationUpdatePropertiesRequest> filters,
    JwtTokenDetail userDetails)
        {
            _logger.LogInformation("UserInsightTrackerProvider.GlobalPropertiesValue()");

            // Map the incoming Properties list to match MySQL expected JSON keys
            var propertyList = filters.Filter.Properties
                .Select(p => new
                {
                    id = p.Id,                                       // must be lowercase "id"
                    value = p.Value?.ToString() ?? string.Empty       // must be lowercase "value", convert booleans to string
                })
                .ToList();

            // Serialize to JSON
            var jsonValue = JsonSerializer.Serialize(propertyList);

            // Create MySQL parameter for JSON
            var jsonParam = new MySqlParameter("@p_id", MySqlDbType.JSON)
            {
                Value = jsonValue
            };

            // Map environment to profile string
            object profileValue;
            var envName = _env.EnvironmentName;
            if (!string.IsNullOrEmpty(envName))
            {
                if (envName.Equals("Staging", StringComparison.OrdinalIgnoreCase))
                {
                    profileValue = "dev";
                }
                else if (envName.Equals("Pre-Production", StringComparison.OrdinalIgnoreCase))
                {
                    profileValue = "qa";
                }
                else if (envName.Equals("Production", StringComparison.OrdinalIgnoreCase))
                {
                    profileValue = "prod";
                }
                else if (envName.Equals("UAT", StringComparison.OrdinalIgnoreCase))
                {
                    profileValue = "uat";
                }
                else
                {
                    profileValue = envName;
                }
            }
            else
            {
                profileValue = DBNull.Value;
            }

            var profileParam = new MySqlParameter("@p_profile", MySqlDbType.VarChar)
            {
                Value = profileValue
            };

            // Call stored procedure
            var results = _dbContext.Set<GlobalConfigurationUpdatePropertiesValueDB>()
                .FromSqlRaw("CALL UpdateApplicationPropertiesValue(@p_id,@p_profile)", jsonParam, profileParam)
                .ToList();

            return results;
        }
        [ExcludeFromCodeCoverage]
        public async Task<bool> HasStafflineUsersAsync()
        {
            return await _dbContext.StafflineUsers.AnyAsync();
        }
        [ExcludeFromCodeCoverage]
        public async Task UpsertStafflineUsersAsync(List<EmployeeData> users)
        {
            if (users == null || users.Count == 0)
                return;

            // Use HashSet for faster lookups if you already have data in memory
            var emailIds = users.Select(u => u.EmailId).ToHashSet();

            var existingUsers = await _dbContext.StafflineUsers
                .Where(x => emailIds.Contains(x.EmailId))
                .ToDictionaryAsync(x => x.EmailId, StringComparer.OrdinalIgnoreCase);

            var utcNow = DateTime.UtcNow;
            var newUsers = new List<StafflineUser>(users.Count - existingUsers.Count);

            // Parallel processing for change detection if needed
            foreach (var item in users)
            {
                if (existingUsers.TryGetValue(item.EmailId, out var existing))
                {
                    // Only update if changed (prevents unnecessary database updates)
                    if (existing.EmployeeId != item.EmployeeId ||
                        existing.Country != item.EmployeeDetailCountry ||
                        existing.State != item.EmployeeDetailState ||
                        existing.City != item.EmployeeDetailCity ||
                        existing.Status != item.EmployeeStatus ||
                        existing.StafflineUserFlag != item.StafflineUserFlag)
                    {
                        existing.EmployeeId = item.EmployeeId;
                        existing.Country = item.EmployeeDetailCountry;
                        existing.State = item.EmployeeDetailState;
                        existing.City = item.EmployeeDetailCity;
                        existing.Status = item.EmployeeStatus;
                        existing.StafflineUserFlag = item.StafflineUserFlag;
                        existing.UpdatedOn = utcNow;

                        // Mark as modified explicitly
                        _dbContext.Entry(existing).State = EntityState.Modified;
                    }
                }
                else
                {
                    newUsers.Add(new StafflineUser
                    {
                        EmailId = item.EmailId,
                        EmployeeId = item.EmployeeId,
                        Country = item.EmployeeDetailCountry,
                        State = item.EmployeeDetailState,
                        City = item.EmployeeDetailCity,
                        Status = item.EmployeeStatus,
                        StafflineUserFlag = item.StafflineUserFlag,
                        SyncedOn = utcNow,
                        UpdatedOn = utcNow
                    });
                }
            }

            // Bulk add new users
            if (newUsers.Count > 0)
            {
                await _dbContext.StafflineUsers.AddRangeAsync(newUsers);
            }

            // Single save operation
            await _dbContext.SaveChangesAsync();
        }
    }
}
