using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AdminDAL.DTO.Branch;
using AdminDAL.DTO.Division;
using AdminDAL.DTO.OrganizationUnit;
using AdminDAL.DTO.Period;
using AdminDAL.DTO.UserAccessRights;
using AdminDAL.Query.Branch;
using AdminDAL.Query.Division;
using AdminDAL.Query.Period;
using AdminDAL.Query.UserAccessRights;
using Dapper;
using GB5Shared.CriteriaHandler;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using static GB5Shared.DTO.Framework.Enum.FrameworkEnumDTO;

namespace AdminDAL.CustomCode.UserAccessRights
{
    public class UserAccessRightsDAL : IUserAccessRightsDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;
        public UserAccessRightsDAL(IQueryExecutor IQueryExecutor, IValidation IValidation)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = IValidation;
        }
        public async Task<string> GetUserAccessRightsOnUser(int UserId, LoginDTO LoginDTO)
        {
            try
            {
                //Get User Access Rights
                string sql = UserAccessRightsQB.GET_USERACCESSACCESS_BASED_ON_USERID;
                var parameters = new { userid = UserId };

                List<UserAccessRightsDTO> userAccessRights =
                    (await _QueryExecutor
                        .QueryAsync<UserAccessRightsDTO>(LoginDTO, sql, parameters))
                    .ToList();

                if (userAccessRights.Count == 0)
                    return JsonConvert.SerializeObject(userAccessRights);

                //Get Latest Period (based on DB type)
                string periodSql;

                if (LoginDTO.DatabaseType == 2)
                {
                    periodSql = UserAccessRightsQB.GET_PERIOD_CHECK_POSTGRESQL;
                }
                else
                {
                    periodSql = UserAccessRightsQB.GET_PERIOD_CHECK;
                }

                List<PeriodPicklistDTO> periods =
                    (await _QueryExecutor
                        .QueryAsync<PeriodPicklistDTO>(LoginDTO, periodSql))
                    .ToList();

                if (periods.Count == 0)
                    return JsonConvert.SerializeObject(userAccessRights);

                int latestPeriodId = periods[0].Id;

                //Get User Work Period
                string userWorkSql = UserAccessRightsQB.GET_USERWORKPERIOD;

                int workPeriodId =
                    await _QueryExecutor
                        .QuerySingleAsync<int>(LoginDTO, userWorkSql, parameters);

                for (int i = 0; i < userAccessRights.Count; i++)
                {
                    if (userAccessRights[i].UserAccessRightsNoOfPeriod == 1)
                    {
                        if (workPeriodId != latestPeriodId)
                        {
                            userAccessRights[i].MySettingRequired = 0;
                        }
                        else
                        {
                            userAccessRights[i].MySettingRequired = 1;
                        }
                    }
                }
                return JsonConvert.SerializeObject(userAccessRights);
            }
            catch
            {
                throw;
            }
        }

        public async Task<string> GetUserAccessRights(int UserAccessRightsId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = UserAccessRightsQB.GET_USERACCESSRIGHTS;
                var parameters = new { useraccessrightsid = UserAccessRightsId };
                UserAccessRightsDTO UserAccessRightsDTO = await _QueryExecutor.QuerySingleAsync<UserAccessRightsDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(UserAccessRightsDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveUserAccessRights(UserAccessRightsDTO UserAccessRightsDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = UserAccessRightsQB.SAVE_USERACCESSRIGHTS;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, UserAccessRightsDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateUserAccessRights(UserAccessRightsDTO UserAccessRightsDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = UserAccessRightsQB.UPDATE_USERACCESSRIGHTS;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, UserAccessRightsDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> DeleteUserAccessRights(int UserAccessRightsId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = UserAccessRightsQB.DELETE_USERACCESSRIGHTS;
                var Parameters = new { useraccessrightsid = UserAccessRightsId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, Parameters);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> DeleteUserAccessRightsOnUser(int UserId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = UserAccessRightsQB.DELETE_USERACCESSACCESS_BASED_ON_USERID;
                var Parameters = new { userid = UserId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, Parameters);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> GetSelectListPeriod(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = PeriodQB.GET_SELECTLIST_PERIOD;
                var Result = await _QueryExecutor.QueryAsync<PeriodPicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListOU(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Id = string.Empty;
                string Code = string.Empty;
                string Name = string.Empty;
                foreach (var section in CriteriaDTO.SectionCriteriaList)
                {
                    foreach (var attribute in section.AttributesCriteriaList)
                    {
                        switch (attribute.FieldName?.ToLower())
                        {
                            case "id":
                                Id = Convert.ToString(attribute.FieldValue) ?? "";
                                break;
                            case "code":
                                Code = Convert.ToString(attribute.FieldValue) ?? "";
                                break;
                            case "name":
                                Name = Convert.ToString(attribute.FieldValue) ?? "";
                                break;
                        }
                    }
                }

                string SQL = UserAccessRightsQB.GET_SELECTLIST_OU;

                DynamicParameters parameters = new();
                parameters.Add("@UserId", LoginDTO.UserId);
                if (!string.IsNullOrWhiteSpace(Id))
                {
                    SQL += " AND CAST(OU.OUID AS VARCHAR(20)) LIKE @Id ";
                    parameters.Add("@Id", $"%{Id}%");
                }
                if (!string.IsNullOrWhiteSpace(Code) || !string.IsNullOrWhiteSpace(Name))
                {
                    SQL += @" AND (
                    OU.ORGANIZATIONUNITCODE LIKE @Code
                    OR OU.ORGANIZATIONUNITNAME LIKE @Name
                    )";
                    parameters.Add("@Code", $"%{Code}%");
                    parameters.Add("@Name", $"%{Name}%");
                }

                var Result = await _QueryExecutor.QueryAsync<OrganizationUnitPicklistDTO>(LoginDTO, SQL, parameters);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Maps the legacy GB4 Criteria.svc/List FieldNames to the real
        // MUSERACCESSRIGHTS/joined-table columns — also doubles as a whitelist
        // since CriteriaBuilder interpolates FieldName directly into the SQL
        // column reference (not parameterized).
        private static readonly Dictionary<string, string> _filterableFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["UserAccessRights.Id"] = "USERACCESSRIGHTSID",
            ["User.Id"] = "USERID",
            ["OU.Id"] = "OUID",
            ["OrganizationGroup.Id"] = "OUGROUPID",
            ["UserAccessRights.Type"] = "TYPE",
        };

        private static void ApplyFieldNameWhitelist(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 && _filterableFields.ContainsKey(attr.FieldName))
                    .ToList();

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

        public async Task<string> GetUserAccessRightsList(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                ApplyFieldNameWhitelist(CriteriaDTO);

                var parameters = new DynamicParameters();
                int offset = FirstNumber > 1 ? FirstNumber - 1 : 0;
                parameters.Add("Offset", offset);
                parameters.Add("MaxResult", MaxResult);

                string baseSql = UserAccessRightsQB.GET_USERACCESSRIGHTS_LIST;
                var (whereClause, _) = CriteriaBuilder.Build(CriteriaDTO, parameters, baseSql);
                string sql = baseSql.Replace("/*CRITERIA*/", whereClause);

                var result = await _QueryExecutor.QueryAsync<UserAccessRightsDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}