using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using FrameworkDAL.DTO.Dashboard;
using FrameworkDAL.Query.Dashboard;
using FrameworkDAL.Query.PortLet;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace FrameworkDAL.CustomCode.Dashboard
{
    public class DashboardDAL : IDashboardDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetDashboard(int DashboardId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var parameters = new { dashboardid = DashboardId };
                var Dashboard = await _QueryExecutor.QuerySingleAsync<DashboardDTO>(
                    LoginDTO, DashboardQB.GET_DASHBOARD, parameters, cancellationToken: ct);
                return JsonConvert.SerializeObject(Dashboard);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveDashboard(DashboardDTO DashboardDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(LoginDTO, DashboardQB.SAVE_DASHBOARD, DashboardDTO, cancellationToken: ct);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateDashboard(DashboardDTO DashboardDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(LoginDTO, DashboardQB.UPDATE_DASHBOARD, DashboardDTO, cancellationToken: ct);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> DeleteDashboard(int DashboardId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var parameters = new { dashboardid = DashboardId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, DashboardQB.DELETE_DASHBOARD, parameters, cancellationToken: ct);

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

        public async Task<string> GetSelectListDashboard(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var Result = await _QueryExecutor.QueryAsync<DashboardPicklistDTO>(
                    LoginDTO, DashboardQB.GET_SELECTLIST_DASHBOARD, null!, cancellationToken: ct);
                return JsonConvert.SerializeObject(Result);
            }
            catch (Exception)
            {
                throw;
            }
        }

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

        public async Task<List<DashboardListWithPagesDTO>> GetDashboardListForUser(int UserId, int RoleId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var parameters = new { userid = UserId, roleid = RoleId };
                var DashboardDict = new Dictionary<int, DashboardListWithPagesDTO>();

                await _QueryExecutor.QueryMultiMapAsync<DashboardListWithPagesDTO, DashboardPageDTO, DashboardListWithPagesDTO>(
                    LoginDTO,
                    DashboardQB.GET_DASHBOARD_LIST_FOR_USER,
                    (parent, child) =>
                    {
                        if (!DashboardDict.TryGetValue(parent.DashboardId, out var dashboard))
                        {
                            dashboard = parent;
                            dashboard.Pages = new List<DashboardPageDTO>();
                            DashboardDict[parent.DashboardId] = dashboard;
                        }

                        // PageId is NOT a positive-sequential ID in this system (IDs run large and
                        // negative) — "> 0" would silently exclude every real row. 0 is Dapper's
                        // default for the LEFT JOIN's unmatched non-nullable int column, so "!= 0"
                        // is the correct "no page" sentinel. Confirmed live: with "> 0" every
                        // dashboard's Pages array came back empty despite MDASHBOARDPAGE having rows.
                        // "child?.PageId != 0" alone is NOT enough: when a dashboard has zero visible
                        // MDASHBOARDPAGE rows, Dapper's multi-map passes an actual null `child` (not a
                        // default-valued DTO) — "null?.PageId" is `null` (int?), and "null != 0" is
                        // true, so the old guard let a literal null entry into Pages. Confirmed live
                        // on a freshly-created Shared dashboard with no pages: Pages came back as
                        // "[null]" instead of "[]".
                        if (child != null && child.PageId != 0 && !dashboard.Pages.Any(p => p.DashboardPageId == child.DashboardPageId))
                            dashboard.Pages.Add(child);

                        return dashboard;
                    },
                    parameters,
                    splitOn: "DashboardPageId"
                );

                // Mirrors the SQL's own ORDER BY (pinned first, then per-user SlNo if set,
                // else the dashboard's own SortOrder) — re-sorting here is necessary because
                // Dictionary.Values does not guarantee the SQL row order is preserved.
                var Result = DashboardDict.Values
                    .Select(d => { d.Pages = d.Pages.OrderBy(p => p.SlNo).ToList(); return d; })
                    .OrderByDescending(d => d.IsPinned)
                    .ThenBy(d => d.UserSlNo ?? d.SortOrder)
                    .ThenBy(d => d.DashboardId)
                    .ToList();

                return Result;
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.GetFetchingMessge}");
                throw new Exception(Error);
            }
        }
    }
}