using FrameworkDAL.DTO.DrillDown;
using FrameworkDAL.DTO.Menu;
using FrameworkDAL.DTO.Page;
using FrameworkDAL.DTO.Portlet;
using FrameworkDAL.DTO.WebService;
using FrameworkDAL.Query.PortLet;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Microsoft.VisualBasic; 
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Threading.Tasks;
using static System.Collections.Specialized.BitVector32;

namespace FrameworkDAL.CustomCode.PortLet
{
    public class PortletDAL : IPortletDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public PortletDAL(IQueryExecutor QueryExecutor, IValidation IValidation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = IValidation;
        }

        public async Task<string> GetPortlet(int PortletId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = PortletQB.GET_PORTLET;
                var parameters = new { portletid = PortletId };
                PortletDTO PortletDTOs = await _QueryExecutor.QuerySingleAsync<PortletDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(PortletDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

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

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

        public async Task<string> DeletePortlet(int PortletId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = PortletQB.DELETE_PORTLET;
                var Parameters = new { portletid = PortletId };
                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> GetSelectListPortlet(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = PortletQB.GET_SELECTLIST_PORTLET;
                var Result = await _QueryExecutor.QueryAsync<PortletPicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> CriteriaConfigExists(int CriteriaConfigId, LoginDTO LoginDTO)
        {
            try
            {
                var count = await _QueryExecutor.ExecuteScalarAsync<int>(
                    LoginDTO, PortletQB.CHECK_CRITERIACONFIG_ACTIVE, new { criteriaconfigid = CriteriaConfigId });
                return count > 0;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetPortletListWithCriteria(int UserId, int PageId, LoginDTO LoginDTO, int DashboardId = -1)
        {
            try
            {
                string sql;
                if (UserId != -1)
                {
                    // 1️⃣ User-specific query
                    sql = PortletQB.GET_USER_PORTLETLIST_WITH_CRITERIA;
                }
                else
                {
                    // 2️⃣ Role-based query (fallback)
                    sql = PortletQB.GET_ROLE_PORTLETLIST_WITH_CRITERIA;
                }
                sql = sql.Replace(":baseuri", LoginDTO.BaseUri);
                sql = sql.Replace(":partybranchid", LoginDTO.WorkPartyBranchId.ToString());
                var parameters = new { userid = UserId, pageid = PageId };

                var PortletListDict = new Dictionary<int, PortletListWithReportCriteriaDTO>();

                await _QueryExecutor.QueryMultiMapAsync<PortletListWithReportCriteriaDTO, ReportCriteriaDTO, PortletReportVsFieldsDTO, PortletConfigDTO, MenuDetailDTO, DrillDownListDTO, WebServiceSettingDTO, PortletListWithReportCriteriaDTO>(
                    LoginDTO,
                    sql,
                    (parent, reportcriteria, reportvsfields, criteriaconfig, menudetail, drilldown, webservicesetting) =>
                    {
                        if (!PortletListDict.TryGetValue(parent.UserVsPortletId, out var existing))
                        {
                            existing = parent;

                            existing.ReportCriteriaArray = new List<ReportCriteriaDTO>();
                            existing.ReportVsFieldsArray = new List<PortletReportVsFieldsDTO>();
                            existing.CriteriaConfigArray = new List<PortletConfigDTO>();
                            existing.MenuDetailArray = new List<MenuDetailDTO>();
                            existing.DrillDownArray = new List<DrillDownListDTO>();
                            existing.WebServiceSettingArray = new List<WebServiceSettingDTO>();

                            PortletListDict[parent.UserVsPortletId] = existing;
                        }

                        if (reportcriteria != null && reportcriteria.WebServiceCriteriaId > 0 &&
                        !existing.ReportCriteriaArray.Any(x => x.WebServiceCriteriaId == reportcriteria.WebServiceCriteriaId))
                            existing.ReportCriteriaArray.Add(reportcriteria);

                        if (reportvsfields != null && reportvsfields.ReportVsFieldsId > 0 &&
                        !existing.ReportVsFieldsArray.Any(x => x.ReportVsFieldsId == reportvsfields.ReportVsFieldsId))
                            existing.ReportVsFieldsArray.Add(reportvsfields);

                        if (criteriaconfig != null && criteriaconfig.UserVsPortletCriteriaConfigId > 0 &&
                        !existing.CriteriaConfigArray.Any(x => x.UserVsPortletCriteriaConfigId == criteriaconfig.UserVsPortletCriteriaConfigId))
                            existing.CriteriaConfigArray.Add(criteriaconfig);

                        if (menudetail != null && menudetail.MenuId > 0 &&
                        !existing.MenuDetailArray.Any(x => x.MenuId == menudetail.MenuId))
                            existing.MenuDetailArray.Add(menudetail);

                        if (drilldown != null && drilldown.DrillDownId > 0 &&
                        !existing.DrillDownArray.Any(x => x.DrillDownId == drilldown.DrillDownId))
                            existing.DrillDownArray.Add(drilldown);

                        if (webservicesetting != null && webservicesetting.WebServiceSettingId > 0 &&
                        !existing.WebServiceSettingArray.Any(x => x.WebServiceSettingId == webservicesetting.WebServiceSettingId))
                            existing.WebServiceSettingArray.Add(webservicesetting);

                        return existing;
                    },
                    parameters,
                    splitOn: "WebServiceCriteriaId,ReportVsFieldsId,UserVsPortletCriteriaConfigId,MenuId,DrillDownId,WebServiceSettingId"
                );

                var ConfigDict = new Dictionary<int, PortletConfigDTO>();

                await _QueryExecutor.QueryMultiMapAsync<PortletConfigDTO, PortletConfigSectionDTO, PortletConfigAttributeDTO, PortletConfigDTO>(
                    LoginDTO,
                    sql,
                    (parent, configsection, configattribute) =>
                    {
                        if (!ConfigDict.TryGetValue(parent.UserVsPortletCriteriaConfigId, out var cfg))
                        {
                            cfg = parent;
                            cfg.ConfigSection = new List<PortletConfigSectionDTO>();
                            ConfigDict[parent.UserVsPortletCriteriaConfigId] = cfg;
                        }

                        if (configsection?.CriteriaConfigSectionId > 0)
                        {
                            var sec = cfg.ConfigSection
                                .FirstOrDefault(x => x.CriteriaConfigSectionId == configsection.CriteriaConfigSectionId);

                            if (sec == null)
                            {
                                sec = configsection;
                                sec.ConfigAttribute = new List<PortletConfigAttributeDTO>();
                                cfg.ConfigSection.Add(sec);
                            }

                            if (configattribute?.CriteriaConfigAttributeId > 0 &&
                                !sec.ConfigAttribute.Any(x => x.CriteriaConfigAttributeId == configattribute.CriteriaConfigAttributeId))
                            {
                                sec.ConfigAttribute.Add(configattribute);
                            }
                        }

                        return cfg;
                    },
                    parameters,
                    splitOn: "CriteriaConfigSectionId,CriteriaConfigAttributeId"
                );

                foreach (var portlet in PortletListDict.Values)
                {
                    foreach (var cfg in portlet.CriteriaConfigArray)
                    {
                        if (ConfigDict.TryGetValue(cfg.UserVsPortletCriteriaConfigId, out var fullCfg))
                        {
                            cfg.ConfigSection = fullCfg.ConfigSection;
                        }
                    }
                }

                var DrillDict = new Dictionary<int, DrillDownListDTO>();

                await _QueryExecutor.QueryMultiMapAsync<DrillDownListDTO, DrillDownDetailListDTO, DrillDownListDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!DrillDict.TryGetValue(parent.DrillDownId, out var d))
                        {
                            d = parent;
                            d.DrilldownDetail = new();
                            DrillDict[parent.DrillDownId] = d;
                        }

                        if (child?.DrillDownDetailId > 0 &&
                            !d.DrilldownDetail.Any(x => x.DrillDownDetailId == child.DrillDownDetailId))
                            d.DrilldownDetail.Add(child);

                        return d;
                    },
                    parameters,
                    splitOn: "DrillDownDetailId"
                );

                foreach (var portlet in PortletListDict.Values)
                {
                    foreach (var drill in portlet.DrillDownArray)
                    {
                        if (DrillDict.TryGetValue(drill.DrillDownId, out var full))
                            drill.DrilldownDetail = full.DrilldownDetail;
                    }
                }

                var WebserviceDict = new Dictionary<int, WebServiceSettingDTO>();

                await _QueryExecutor.QueryMultiMapAsync<WebServiceSettingDTO, WebServiceSettingDetailDTO, WebServiceSettingDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!WebserviceDict.TryGetValue(parent.WebServiceSettingId, out var d))
                        {
                            d = parent;
                            d.WebServiceSettingDetailArray = new();
                            WebserviceDict[parent.WebServiceSettingId] = d;
                        }

                        if (child?.WebServiceSettingDetailId > 0 &&
                            !d.WebServiceSettingDetailArray.Any(x => x.WebServiceSettingDetailId == child.WebServiceSettingDetailId))
                            d.WebServiceSettingDetailArray.Add(child);

                        return d;
                    },
                    parameters,
                    splitOn: "WebServiceSettingDetailId"
                );

                foreach (var portlet in PortletListDict.Values)
                {
                    foreach (var ws in portlet.WebServiceSettingArray)
                    {
                        if (WebserviceDict.TryGetValue(ws.WebServiceSettingId, out var full))
                            ws.WebServiceSettingDetailArray = full.WebServiceSettingDetailArray;
                    }
                }

                // ── Phase 3: Page-level / Dashboard-level filter tiers ──────────
                // Resolved via two small, separate queries (not joined into the mega-query
                // above) — see PortletQB.GET_CRITERIA_CONFIG_TREE_BY_ID's comment for why.
                if (PortletListDict.Count > 0)
                {
                    var anyPortlet = PortletListDict.Values.GetEnumerator();
                    anyPortlet.MoveNext();
                    var pageDefaultCriteriaConfigId = anyPortlet.Current.PageDefaultCriteriaConfigId;
                    var pageOverrideDashboardCriteria = anyPortlet.Current.PageOverrideDashboardCriteria;

                    // Sentinel for "unset" here is -1 (this migration's own DF_..._DEFAULT -1
                    // default), NOT 0 — and real CriteriaConfigId/DashboardId values in this
                    // system run large-negative, so "> 0" would never match a real id at all.
                    // Confirmed live on GB5DEMO: with "> 0" here, the whole Page/Dashboard tier
                    // silently never ran for any real DashboardId.
                    if (pageDefaultCriteriaConfigId != -1)
                    {
                        var pageConfigList = await GetCriteriaConfigTree(pageDefaultCriteriaConfigId, LoginDTO);
                        foreach (var portlet in PortletListDict.Values)
                            if (portlet.PortletOverridePageCriteria == 0)
                                portlet.PageCriteriaConfigArray = pageConfigList;
                    }

                    if (DashboardId != -1)
                    {
                        var dashboardDefaultCriteriaConfigId = await _QueryExecutor.QuerySingleAsync<int>(
                            LoginDTO, PortletQB.GET_DASHBOARD_DEFAULT_CRITERIA_CONFIG_ID, new { dashboardid = DashboardId });

                        if (dashboardDefaultCriteriaConfigId != -1)
                        {
                            var dashboardConfigList = await GetCriteriaConfigTree(dashboardDefaultCriteriaConfigId, LoginDTO);
                            foreach (var portlet in PortletListDict.Values)
                                if (pageOverrideDashboardCriteria == 0)
                                    portlet.DashboardCriteriaConfigArray = dashboardConfigList;
                        }
                    }
                }

                return JsonConvert.SerializeObject(PortletListDict.Values);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve Portlet List with Criteria data: {ex.Message}", ex);
            }
        }

        // Resolves a single CriteriaConfigId's full Section/Attribute tree — same
        // shape as the per-portlet CC/CCS/CCA multimap inside GetPortletListWithCriteria,
        // reused for both the Page tier and the Dashboard tier (see PortletQB comment).
        private async Task<List<PortletConfigDTO>> GetCriteriaConfigTree(int criteriaConfigId, LoginDTO LoginDTO)
        {
            var configDict = new Dictionary<int, PortletConfigDTO>();

            await _QueryExecutor.QueryMultiMapAsync<PortletConfigDTO, PortletConfigSectionDTO, PortletConfigAttributeDTO, PortletConfigDTO>(
                LoginDTO,
                PortletQB.GET_CRITERIA_CONFIG_TREE_BY_ID,
                (parent, configsection, configattribute) =>
                {
                    if (!configDict.TryGetValue(parent.UserVsPortletCriteriaConfigId, out var cfg))
                    {
                        cfg = parent;
                        cfg.ConfigSection = new List<PortletConfigSectionDTO>();
                        configDict[parent.UserVsPortletCriteriaConfigId] = cfg;
                    }

                    // IDs in this system run large-negative (e.g. TCRITERIACONFIGSECTIONID like
                    // -2147483646), never positive — "> 0" would silently exclude every real row
                    // and only Dapper's own LEFT-JOIN-no-match default (0) reads as "absent".
                    // Confirmed live on GB5DEMO: with "> 0" here, PageCriteriaConfigArray/
                    // DashboardCriteriaConfigArray came back with a header row but empty
                    // ConfigSection/ConfigAttribute despite real section/attribute rows existing.
                    // "configsection?.CriteriaConfigSectionId != 0" alone is NOT enough: when a
                    // criteria config has zero sections, Dapper's multi-map passes an actual null
                    // `configsection` (not a default-valued DTO) — "null?.CriteriaConfigSectionId"
                    // is `null` (int?), and "null != 0" is true, so this would have thrown a
                    // NullReferenceException at "sec.ConfigAttribute = ...". Confirmed live via the
                    // matching bug in DashboardDAL.GetDashboardListForUser (a dashboard with zero
                    // pages produced a literal "null" in the Pages array) — same root cause here,
                    // caught before it could crash a live call.
                    if (configsection != null && configsection.CriteriaConfigSectionId != 0)
                    {
                        var sec = cfg.ConfigSection
                            .FirstOrDefault(x => x.CriteriaConfigSectionId == configsection.CriteriaConfigSectionId);

                        if (sec == null)
                        {
                            sec = configsection;
                            sec.ConfigAttribute = new List<PortletConfigAttributeDTO>();
                            cfg.ConfigSection.Add(sec);
                        }

                        if (configattribute != null && configattribute.CriteriaConfigAttributeId != 0 &&
                            !sec.ConfigAttribute.Any(x => x.CriteriaConfigAttributeId == configattribute.CriteriaConfigAttributeId))
                        {
                            sec.ConfigAttribute.Add(configattribute);
                        }
                    }

                    return cfg;
                },
                new { criteriaconfigid = criteriaConfigId },
                splitOn: "CriteriaConfigSectionId,CriteriaConfigAttributeId"
            );

            return configDict.Values.ToList();
        }

        public async Task<string> GetPageWithMenuPageList(int RoleId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql;
                if (LoginDTO.DatabaseType == 2) // PostgreSQL
                    sql = PortletQB.GET_PAGE_WITH_MENU_PAGE_LIST_POSTGRE;
                else
                    sql = PortletQB.GET_PAGE_WITH_MENU_PAGE_LIST;

                var parameters = new { roleid = RoleId };
                var Result = await _QueryExecutor.QueryAsync<PageWithMenuPageDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

        public async Task<string> GetPageList(int UserId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = PortletQB.GET_PAGELIST;
                var parameters = new { userid = UserId };
                var Result = await _QueryExecutor.QueryAsync<PageListDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

        public async Task<string> GetPortletList(int UserId, int PageId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = PortletQB.GET_PORTLETLIST;
                var parameters = new { userid = UserId, pageid = PageId };
                var Result = await _QueryExecutor.QueryAsync<PortletListDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

        public async Task<string> GetPortletListForUser(int UserId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = PortletQB.GET_PORTLETLIST_FOR_USER;
                var parameters = new { userid = UserId };
                var Result = await _QueryExecutor.QueryAsync<PortletListDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }
    }
}