using Dapper;
using FrameworkDAL.DTO.Menu;
using FrameworkDAL.DTO.ReportViewer;
using FrameworkDAL.DTO.ReportViewFields;
using FrameworkDAL.Query.ReportViewer;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using System.Text;
using System.Text.Json;

namespace FrameworkDAL.CustomCode.ReportViewer
{
    public class ReportViewDAL : IReportViewDAL
    {
        private readonly IQueryExecutor _qe;

        public ReportViewDAL(IQueryExecutor queryExecutor)
        {
            _qe = queryExecutor;
        }

        // ── Existing picklist methods (CT wired through) ──────────────────────

        public async Task<string> GetSelectListReportView(CriteriaDTO criteriaDTO,LoginDTO loginDTO,CancellationToken ct = default)
        {
            try
            {
                int reportId = -1;

                // Extract ReportId from CriteriaDTO
                if (criteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in criteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (string.Equals(attr.FieldName, "ReportId", StringComparison.OrdinalIgnoreCase))
                            {
                                if (attr.FieldValue is JsonElement jsonElement)
                                {
                                    if (jsonElement.ValueKind == JsonValueKind.Number)
                                    {
                                        reportId = jsonElement.GetInt32();
                                    }
                                    else if (jsonElement.ValueKind == JsonValueKind.String)
                                    {
                                        reportId = Convert.ToInt32(jsonElement.GetString());
                                    }
                                }
                                else
                                {
                                    reportId = Convert.ToInt32(attr.FieldValue);
                                }

                                break;
                            }
                        }
                    }
                }

                string sql = ReportViewQB.GET_SELECTLIST_REPORTVIEW;
                var parameters = new Dictionary<string, object>();

                if (reportId != -1)
                {
                    sql += " WHERE REPORTID = @ReportId";
                    parameters.Add("@ReportId", reportId);
                }

                var result = await _qe.QueryAsync<ReportViewPicklistDTO>(loginDTO,sql,parameters,cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }

        public async Task<string> GetSelectListReportViewandFields(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            var criteria = ParseCriteria(CriteriaDTO, LoginDTO);
            var (filter, parameters) = BuildParameters(criteria, LoginDTO);

            parameters.Add("firstnumber", FirstNumber);
            parameters.Add("maxresult",   MaxResult);

            string sql = ReportViewQB.GET_SELECTLIST_REPORTVIEW_WITH_FIELD + filter;

            var views = await _qe.QueryAsync<ReportViewPicklistDTO>(
                LoginDTO, sql, parameters, cancellationToken: ct).ConfigureAwait(false);
            var picklist = views.ToList();

            if (picklist.Count > 0 && criteria.ReportViewId.Count > 0)
            {
                string idList   = string.Join(",", criteria.ReportViewId);
                string fieldSQL = ReportViewQB.GET_SELECTLIST_REPORTVIEWFIELDS
                                              .Replace(":reportviewid", idList);

                var fields = await _qe.QueryAsync<ReportViewFieldsDTO>(
                    LoginDTO, fieldSQL, null!, cancellationToken: ct).ConfigureAwait(false);
                var fieldList = fields.ToList();

                for (int i = 0; i < picklist.Count; i++)
                {
                    int viewId = picklist[i].Id;
                    picklist[i].ReportViewFieldsArray = fieldList
                        .Where(f => f.ReportViewId == viewId)
                        .ToList();
                }
            }

            return JsonConvert.SerializeObject(picklist);
        }

        // ── Single record ─────────────────────────────────────────────────────

        public async Task<string> GetReportView(int reportViewId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _qe.QuerySingleAsync<ReportViewDTO>(
                    login, ReportViewQB.GET_REPORTVIEW,
                    new { ReportViewId = reportViewId },
                    cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetReportViewLite(int reportViewId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _qe.QuerySingleAsync<ReportViewLiteDTO>(
                    login, ReportViewQB.GET_REPORTVIEW_LITE,
                    new { ReportViewId = reportViewId },
                    cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Lists ─────────────────────────────────────────────────────────────

        public async Task<string> GetReportViewList(int reportId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _qe.QueryAsync<ReportViewDTO>(
                    login, ReportViewQB.GET_REPORTVIEW_LIST,
                    new { ReportId = reportId },
                    cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetReportViewPaged(int reportId, int offset, int pageSize, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _qe.QueryAsync<ReportViewDTO>(
                    login, ReportViewQB.GET_REPORTVIEW_PAGED,
                    new { ReportId = reportId, Offset = offset, PageSize = pageSize },
                    cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetReportViewByMenu(int reportId, int menuId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _qe.QueryAsync<ReportViewDTO>(
                    login, ReportViewQB.GET_REPORTVIEW_BY_MENU,
                    new { ReportId = reportId, MenuId = menuId },
                    cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Fields ────────────────────────────────────────────────────────────

        public async Task<string> GetReportViewFieldsForView(int reportViewId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _qe.QueryAsync<ReportViewFieldsDTO>(
                    login, ReportViewQB.GET_REPORTVIEW_FIELDS_FOR_VIEW,
                    new { ReportViewId = reportViewId },
                    cancellationToken: ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Mutations ─────────────────────────────────────────────────────────

        public async Task<int> SaveReportView(ReportViewDTO dto, LoginDTO login, CancellationToken ct, bool isNew)
        {
            try
            {
                string sql = isNew ? ReportViewQB.INSERT_REPORTVIEW : ReportViewQB.UPDATE_REPORTVIEW;
                await _qe.ExecuteAsync(login, sql, dto, cancellationToken: ct).ConfigureAwait(false);
                return dto.ReportViewId;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task SaveReportViewWithFields(ReportViewDTO dto, LoginDTO login, CancellationToken ct, bool isNew)
        {
            var tx = await _qe.BeginTransactionAsync(login).ConfigureAwait(false);
            try
            {
                // 1. INSERT or UPDATE the view header
                string viewSql = isNew ? ReportViewQB.INSERT_REPORTVIEW : ReportViewQB.UPDATE_REPORTVIEW;
                await _qe.ExecuteAsync(login, viewSql, dto, tx).ConfigureAwait(false);

                // 2. If this is the default view, clear the flag on all siblings first
                if (dto.ReportViewIsDefaultView == 1)
                {
                    await _qe.ExecuteAsync(login, ReportViewQB.RESET_DEFAULT_VIEW,
                        new { dto.ReportId, dto.ReportViewId }, tx).ConfigureAwait(false);
                }

                // 3. Delete existing field rows, then insert fresh
                await _qe.ExecuteAsync(login, ReportViewQB.DELETE_VIEW_FIELDS,
                    new { dto.ReportViewId }, tx).ConfigureAwait(false);

                foreach (var field in dto.ReportViewFieldsDTOs)
                {
                    field.ReportViewId = dto.ReportViewId;
                    field.ReportId     = dto.ReportId;
                    await _qe.ExecuteAsync(login, ReportViewQB.INSERT_REPORTVIEWFIELDS, field, tx)
                        .ConfigureAwait(false);
                }

                await _qe.CommitAsync(tx).ConfigureAwait(false);
            }
            catch (Exception)
            {
                await _qe.RollbackAsync(tx).ConfigureAwait(false);
                throw;
            }
        }

        public async Task DeleteReportView(int reportViewId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                await _qe.ExecuteAsync(login, ReportViewQB.SOFT_DELETE_REPORTVIEW,
                    new
                    {
                        ReportViewId = reportViewId,
                        ModifiedById = login.UserId,
                        ModifiedOn   = DateTime.UtcNow
                    },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task HardDeleteReportView(int reportViewId, LoginDTO login, CancellationToken ct)
        {
            var tx = await _qe.BeginTransactionAsync(login).ConfigureAwait(false);
            try
            {
                await _qe.ExecuteAsync(login, ReportViewQB.DELETE_VIEW_FIELDS,
                    new { ReportViewId = reportViewId }, tx).ConfigureAwait(false);

                await _qe.ExecuteAsync(login, ReportViewQB.HARD_DELETE_REPORTVIEW,
                    new { ReportViewId = reportViewId }, tx).ConfigureAwait(false);

                await _qe.CommitAsync(tx).ConfigureAwait(false);
            }
            catch (Exception)
            {
                await _qe.RollbackAsync(tx).ConfigureAwait(false);
                throw;
            }
        }

        // ── Internal: criteria parsing helpers (unchanged from original) ──────

        private sealed class ReportViewandViewFieldsCriteria
        {
            public List<int> ReportViewId      { get; set; } = new();
            public string    Name              { get; set; } = "";
            public List<int> ApplicableMenuIds { get; set; } = new();
            public int       ReportId          { get; set; }
        }

        private static ReportViewandViewFieldsCriteria ParseCriteria(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            var result = new ReportViewandViewFieldsCriteria();
            if (criteriaDTO?.SectionCriteriaList == null) return result;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr?.FieldValue == null) continue;
                    string field = attr.FieldName?.ToLower() ?? string.Empty;
                    string value = Convert.ToString(attr.FieldValue) ?? "";

                    switch (field)
                    {
                        case "reportviewid":
                            result.ReportViewId = ParseIntList(attr.FieldValue);
                            break;
                        case "name":
                            result.Name = value;
                            break;
                        case "applicablemenuid":
                            result.ApplicableMenuIds = ParseIntList(attr.FieldValue);
                            break;
                        case "reportid":
                            result.ReportId = ParseInt(attr.FieldValue);
                            break;
                    }
                }
            }

            return result;
        }

        private static List<int> ParseIntList(object fieldValue)
        {
            string raw = fieldValue is JsonElement je ? je.ToString() : fieldValue.ToString()!;
            if (string.IsNullOrWhiteSpace(raw)) return new List<int>();
            return raw.Split(',', StringSplitOptions.RemoveEmptyEntries)
                      .Select(s => int.TryParse(s.Trim(), out int v) ? v : 0)
                      .Where(v => v != 0)
                      .ToList();
        }

        private static int ParseInt(object fieldValue)
        {
            string raw = fieldValue is JsonElement je ? je.ToString() : fieldValue.ToString()!;
            return int.TryParse(raw, out int v) ? v : 0;
        }

        private static (string filter, DynamicParameters parameters) BuildParameters(
            ReportViewandViewFieldsCriteria criteria, LoginDTO loginDTO)
        {
            var filter = new StringBuilder();
            var p      = new DynamicParameters();

            p.Add("ouid", loginDTO.WorkOUId);

            if (criteria.ReportViewId.Count > 0)
            {
                filter.Append(" AND a.reportviewid IN @reportviewid");
                p.Add("reportviewid", criteria.ReportViewId);
            }
            if (!string.IsNullOrWhiteSpace(criteria.Name))
            {
                filter.Append(" AND a.reportviewname LIKE @name");
                p.Add("name", $"%{criteria.Name}%");
            }
            if (criteria.ApplicableMenuIds.Count > 0)
            {
                filter.Append(" AND a.applicablemenuid IN @applicablemenuid");
                p.Add("applicablemenuid", criteria.ApplicableMenuIds);
            }
            if (criteria.ReportId > 0)
            {
                filter.Append(" AND a.reportid = @reportid");
                p.Add("reportid", criteria.ReportId);
            }

            return (filter.ToString(), p);
        }

        public async Task<string> GetSelectListReportVsFormats(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            var attrs = criteriaDTO.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList ?? [])
                .ToList() ?? [];


            int reportId = ParseInt(
                attrs.FirstOrDefault(a => a.FieldName == "Report.Id")?.FieldValue);


            int applicableMenuId = ParseInt(
                attrs.FirstOrDefault(a => a.FieldName == "ApplicableMenuIds")?.FieldValue);


            var param = new
            {
                reportid = reportId,
                applicablemenuid = applicableMenuId
            };


            var result = await _qe.QueryAsync<MenuReportVsFormatsDTO>(
                loginDTO,
                ReportViewQB.GET_SELECTLIST_REPORTVSFORMATS,
                param);


            return JsonConvert.SerializeObject(result);
        }
    }
}