using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FrameworkDAL.DTO.RoleVsMenu;
using FrameworkDAL.Query.RoleVsMenu;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.AutoNumber;
using GB5Shared.DTO.Framework.Criteria;
using FrameworkDAL.CustomCode.RoleVsMenu;
using System.Runtime.InteropServices;
using GB5Shared.QueryExecutor;
using GB5Shared.GenerateAutoNumber;
using Newtonsoft.Json;
using GB5Shared.Resource.Response;

namespace FrameworkBLL.RoleVsMenu
{
    /// <summary>
    /// RoleVsMenuBLL
    /// </summary>
    public class RoleVsMenuBLL : IRoleVsMenuBLL
    {
        private readonly IRoleVsMenuDAL _RoleVsMenuDAL;
        private readonly AutoNumber _autonumber;
        private readonly IQueryExecutor _queryExecutor;
        /// <summary>
        /// RoleVsMenuBLL
        /// </summary>
        /// <param name="RoleVsMenuDAL"></param>
        /// <param name="AutoNumber"></param>
        /// <param name="QueryExecutor"></param>
        public RoleVsMenuBLL(IRoleVsMenuDAL RoleVsMenuDAL , AutoNumber AutoNumber , IQueryExecutor QueryExecutor)
        {
            _RoleVsMenuDAL = RoleVsMenuDAL;
            _autonumber = AutoNumber;
            _queryExecutor = QueryExecutor;
        }

        /// <summary>
        /// Save
        /// </summary>
        /// <param name="ModuleId"></param>
        /// <param name="PageId"></param>
        /// <param name="RoleVsMenuDTOs"></param>
        /// <param name="LoginDTO"></param>
        /// <returns></returns>
        /// <summary>
        /// Diff-based save: reconciles the incoming list against MROLEVSMENU by the
        /// (RoleId, MenuId) natural key — rows whose MenuId already exists in scope are
        /// UPDATEd in place (preserving ROLEVSMENUID/CREATEDON), rows no longer present are
        /// deleted, and only genuinely new (RoleId, MenuId) pairs get a freshly-issued
        /// ROLEVSMENUID. Replaces the previous delete-everything-then-reinsert-everything
        /// behavior (which also built its DELETE SQL via unparameterized string
        /// concatenation and ran the delete(s)/insert as separate, non-transactional calls) —
        /// this now runs as one parameterized transaction via ExecuteInTransactionAsync.
        /// </summary>
        public async Task<string> Save(int ModuleId, int PageId, List<RoleVsMenuDTO> RoleVsMenuDTOs, LoginDTO LoginDTO)
        {
            try
            {
                if (RoleVsMenuDTOs == null || RoleVsMenuDTOs.Count == 0)
                    return SuccessResponse.SaveSuccessMessage;

                int RoleId = RoleVsMenuDTOs[0].RoleVsMenuRoleId;
                var incomingMenuIds = RoleVsMenuDTOs.Select(d => d.RoleVsMenuMenuId).Distinct().ToList();

                List<RoleVsMenuDTO> existingRows = PageId == -1
                    ? await _RoleVsMenuDAL.GetExistingForModule(RoleId, ModuleId, LoginDTO)
                    : await _RoleVsMenuDAL.GetExistingForMenuIds(RoleId, incomingMenuIds, LoginDTO);

                var existingByMenuId = existingRows.ToDictionary(r => r.RoleVsMenuMenuId, r => r.RoleVsMenuId);

                var statements = new List<(string sql, object param)>();

                // Rows that used to be in this scope but are no longer in the incoming list.
                var toDeleteIds = existingRows
                    .Where(r => !incomingMenuIds.Contains(r.RoleVsMenuMenuId))
                    .Select(r => r.RoleVsMenuId)
                    .ToList();
                if (toDeleteIds.Count > 0)
                    statements.Add((RoleVsMenuQB.DELETE_ROLEVSMENU_BY_IDS, (object)new { Ids = toDeleteIds }));

                int newCount = RoleVsMenuDTOs.Count(d => !existingByMenuId.ContainsKey(d.RoleVsMenuMenuId));
                int nextNewId = 0;
                if (newCount > 0)
                {
                    AutoNumberDTO autoNumberDTO = await _autonumber.GetNumberAsync(newCount, "ROLEVSMENU", LoginDTO);
                    nextNewId = autoNumberDTO.StartNumber;
                }
                int firstNewId = nextNewId;

                foreach (var dto in RoleVsMenuDTOs)
                {
                    dto.RoleVsMenuRoleId = RoleId;
                    if (existingByMenuId.TryGetValue(dto.RoleVsMenuMenuId, out var existingId))
                    {
                        dto.RoleVsMenuId = existingId;
                        statements.Add((RoleVsMenuQB.UPDATE_ROLEVSMENU, (object)dto));
                    }
                    else
                    {
                        dto.RoleVsMenuId = nextNewId++;
                        statements.Add((RoleVsMenuQB.SAVE_ROLEVSMENU, (object)dto));
                    }
                }

                await _queryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

                int savedId = newCount > 0 ? firstNewId : (existingRows.Count > 0 ? existingRows[0].RoleVsMenuId : RoleId);
                return $"{SuccessResponse.SaveSuccessMessage} {savedId}";
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// GetRoleVsMenu
        /// </summary>
        /// <param name="RoleId"></param>
        /// <param name="ModuleId"></param>
        /// <param name="LoginDTO"></param>
        /// <returns></returns>
        public async Task<string> GetRoleVsMenu(int RoleId, int ModuleId, LoginDTO LoginDTO)
        {
            try
            {
                string Json = "";

                List<RoleVsMenuDTO> RoleVsMenuDTOs =  await _RoleVsMenuDAL.GetRoleVsMenu(RoleId, ModuleId, LoginDTO);
                if (RoleVsMenuDTOs.Count == 1)
                {
                    Json =JsonConvert.SerializeObject(RoleVsMenuDTOs[0]);
                }
                else
                {
                    Json = JsonConvert.SerializeObject(RoleVsMenuDTOs);
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<RoleVsMenuDTO>> GetRoleVsMenuBasedOnRoleId(int RoleId, LoginDTO LoginDTO)
        {
            try
            {
                return (await _queryExecutor.QueryAsync<RoleVsMenuDTO>(LoginDTO, RoleVsMenuQB.GET_ROLE_VS_MENU_BY_ROLE_ID, new { RoleId })).ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<FrameworkDAL.DTO.Menu.RoleVsMenuReportDTO>> GetRoleVsMenuDetail(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                int offset = FirstNumber == -1 ? 0 : FirstNumber;
                int fetch  = MaxResult  == -1 ? int.MaxValue : MaxResult;
                var filters = ExtractRoleVsMenuFilters(CriteriaDTO);
                return (await _queryExecutor.QueryAsync<FrameworkDAL.DTO.Menu.RoleVsMenuReportDTO>(LoginDTO, RoleVsMenuQB.GET_ROLE_VS_MENU_DETAIL,
                    new
                    {
                        SearchKeyword = CriteriaDTO?.SearchKeyword,
                        filters.MenuId,
                        filters.RoleId,
                        filters.ModuleId,
                        filters.ParentMenuId,
                        FirstNumber = offset,
                        MaxResult   = fetch
                    })).ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<FrameworkDAL.DTO.Menu.RoleVsMenuReportDTO>> GetRoleVsMenuReportDetail(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                int offset = FirstNumber == -1 ? 0 : FirstNumber;
                int fetch  = MaxResult  == -1 ? int.MaxValue : MaxResult;
                var filters = ExtractRoleVsMenuFilters(CriteriaDTO);
                return (await _queryExecutor.QueryAsync<FrameworkDAL.DTO.Menu.RoleVsMenuReportDTO>(LoginDTO, RoleVsMenuQB.GET_ROLE_VS_MENU_REPORT_DETAIL,
                    new
                    {
                        SearchKeyword = CriteriaDTO?.SearchKeyword,
                        filters.MenuId,
                        filters.RoleId,
                        filters.ModuleId,
                        filters.ParentMenuId,
                        FirstNumber = offset,
                        MaxResult   = fetch
                    })).ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static (int? MenuId, int? RoleId, int? ModuleId, int? ParentMenuId) ExtractRoleVsMenuFilters(CriteriaDTO CriteriaDTO)
        {
            int? menuId = null, roleId = null, moduleId = null, parentMenuId = null;
            if (CriteriaDTO?.SectionCriteriaList == null) return (menuId, roleId, moduleId, parentMenuId);
            foreach (var section in CriteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr?.FieldName == null || attr.FieldValue == null) continue;
                    if (int.TryParse(attr.FieldValue.ToString(), out int val) && val != -1)
                    {
                        switch (attr.FieldName.ToLower())
                        {
                            case "menuid":       menuId       = val; break;
                            case "roleid":       roleId       = val; break;
                            case "moduleid":     moduleId     = val; break;
                            case "parentmenuid": parentMenuId = val; break;
                        }
                    }
                }
            }
            return (menuId, roleId, moduleId, parentMenuId);
        }

        public async Task<List<FrameworkDAL.DTO.Menu.RoleVsMenuForMenuDTO>> GetRoleVsMenuForMenu(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                int menuId = -1, moduleId = -1;
                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        if (section?.AttributesCriteriaList == null) continue;
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (attr?.FieldName == null || attr.FieldValue == null) continue;
                            if (int.TryParse(attr.FieldValue.ToString(), out int val))
                            {
                                switch (attr.FieldName.ToLower())
                                {
                                    case "menuid":   menuId   = val; break;
                                    case "moduleid": moduleId = val; break;
                                }
                            }
                        }
                    }
                }
                return (await _queryExecutor.QueryAsync<FrameworkDAL.DTO.Menu.RoleVsMenuForMenuDTO>(LoginDTO, RoleVsMenuQB.GET_ROLE_VS_MENU_FOR_MENU,
                    new { MenuId = menuId, ModuleId = moduleId })).ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> SaveOnlyRoleMenu(List<RoleVsMenuDTO> RoleVsMenuDTOs, LoginDTO LoginDTO)
        {
            try
            {
                if (RoleVsMenuDTOs == null || RoleVsMenuDTOs.Count == 0)
                    return $"{SuccessResponse.SaveSuccessMessage} 0";

                AutoNumberDTO autoNumberDTO = await _autonumber.GetNumberAsync(RoleVsMenuDTOs.Count, "ROLEVSMENU", LoginDTO);
                int nextId = autoNumberDTO.StartNumber;

                var statements = new List<(string sql, object param)>();
                foreach (var dto in RoleVsMenuDTOs)
                {
                    dto.RoleVsMenuId = nextId++;
                    statements.Add((RoleVsMenuQB.SAVE_ROLEVSMENU, (object)dto));
                }

                await _queryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

                return $"{SuccessResponse.SaveSuccessMessage} {autoNumberDTO.StartNumber}";
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}