using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using FrameworkDAL.DTO.Workflow;
using FrameworkDAL.Query.WorkFlow;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.WorkFlow;
using GB5Shared.QueryExecutor;
using GB5Shared.WorkFlow.WorkFlowEngine;
using Newtonsoft.Json;
using static GB5Shared.DTO.WorkFlow.ContractsDTO;
using WorkflowDTO = FrameworkDAL.DTO.Workflow.WorkflowDTO;

namespace FrameworkDAL.CustomCode.WorkFlow
{
    public class WorkFlowDAL : IWorkFlowDAL
    {
        private readonly IWorkFlowEngine  _engine;
        private readonly IQueryExecutor   _qe;

        public WorkFlowDAL(IWorkFlowEngine engine, IQueryExecutor queryExecutor)
        {
            _engine = engine;
            _qe     = queryExecutor;
        }

        // ── Applicability check ───────────────────────────────────────────────

        public async Task<WorkflowPreCheckResult> CheckApplicability(
            WorkflowCheckContext context,
            LoginDTO loginDTO)
        {
            var tx = await _qe.BeginTransactionAsync(loginDTO);
            try
            {
                var result = await _engine.CheckWorkFlowApplicability(context, loginDTO, tx);
                await _qe.CommitAsync(tx);
                return result;
            }
            catch
            {
                await _qe.RollbackAsync(tx);
                throw;
            }
        }

        // ── Start workflow ────────────────────────────────────────────────────

        public async Task<WorkflowStartResult> StartWorkflow(
            WorkflowStartRequest request,
            LoginDTO loginDTO)
        {
            var tx = await _qe.BeginTransactionAsync(loginDTO);
            try
            {
                var result = await _engine.StartWorkflowAsync(request, loginDTO, tx);
                await _qe.CommitAsync(tx);
                return result;
            }
            catch
            {
                await _qe.RollbackAsync(tx);
                throw;
            }
        }

        // ── Action handling ───────────────────────────────────────────────────

        public async Task<WorkflowActionResult> Actions(
            WorkFlowActionDTO workFlowActionDTO,
            LoginDTO loginDTO)
        {
            var tx = await _qe.BeginTransactionAsync(loginDTO);
            try
            {
                var contexts = workFlowActionDTO.Items.Select(i => new WorkflowActionContext
                {
                    TaskId  = i.TaskId,
                    UserId  = loginDTO.UserId,
                    Action  = i.Action,
                    Comment = i.Comment,
                    Facts   = i.Facts
                });

                var result = await _engine.HandleActionsAsync(contexts, loginDTO, tx);
                await _qe.CommitAsync(tx);
                return result;
            }
            catch
            {
                await _qe.RollbackAsync(tx);
                throw;
            }
        }

        public async Task<string?> GetEntityNameAsync(int entityId, LoginDTO loginDTO)
        {
            return await _qe.QuerySingleAsync<string?>(
                loginDTO, GB5Shared.Query.WorkFlow.WorkFlowQB.GetEntityNameByEntityId,
                new { entityid = entityId });
        }

        // ── Auto-approve ─────────────────────────────────────────────────────

        public async Task<IReadOnlyList<OverdueAutoApproveTaskDTO>> GetOverdueAutoApproveTasks(
            LoginDTO loginDTO)
        {
            var tx = await _qe.BeginTransactionAsync(loginDTO);
            try
            {
                var result = await _engine.GetOverdueAutoApproveTasksAsync(loginDTO, tx);

                await _qe.CommitAsync(tx);
                return result;
            }
            catch
            {
                await _qe.RollbackAsync(tx);
                throw;
            }
        }

        // ── Approval list ─────────────────────────────────────────────────────

        public async Task<List<WorkflowApprovalListDTO>> WorkflowApprovalList(
            int workflowType,
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO)
        {
            var tx = await _qe.BeginTransactionAsync(loginDTO);
            try
            {
                List<WorkflowApprovalListDTO> result;

                // 1 = Request To Me (assigned to logged-in user)
                // other = Request By Me (submitted by logged-in user)
                if (workflowType == 0)
                    result = await GetRequestToMe(firstNumber, maxResult, criteriaDTO, loginDTO, tx);
                else
                    result = await GetRequestByMe(firstNumber, maxResult, criteriaDTO, loginDTO, tx);

                await _qe.CommitAsync(tx);
                return result;
            }
            catch
            {
                await _qe.RollbackAsync(tx);
                throw;
            }
        }

        private async Task<List<WorkflowApprovalListDTO>> GetRequestToMe(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            System.Data.Common.DbTransaction tx)
        {
            var (entityId, status, fromDate, toDate) = ExtractCriteria(criteriaDTO);
            var param = new { UserId = loginDTO.UserId, EntityId = entityId, Status = status, FromDate = fromDate, ToDate = toDate, FirstNumber = firstNumber, MaxResult = maxResult };
            return (await _qe.QueryAsync<WorkflowApprovalListDTO>(
                loginDTO, WorkFlowQB.GetWorkflowApprovalList, param, tx)).ToList();
        }

        private async Task<List<WorkflowApprovalListDTO>> GetRequestByMe(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            System.Data.Common.DbTransaction tx)
        {
            var (entityId, status, fromDate, toDate) = ExtractCriteria(criteriaDTO);
            var param = new { UserId = loginDTO.UserId, EntityId = entityId, Status = status, FromDate = fromDate, ToDate = toDate, FirstNumber = firstNumber, MaxResult = maxResult };
            return (await _qe.QueryAsync<WorkflowApprovalListDTO>(
                loginDTO, WorkFlowQB.RequestByMe, param, tx)).ToList();
        }

        // ── History ───────────────────────────────────────────────────────────

        public async Task<List<WorkflowHistoryListDTO>> WorkflowHistory(
            int workflowType,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO)
        {
            var tx = await _qe.BeginTransactionAsync(loginDTO);
            try
            {
                var (entityId, _, fromDate, toDate) = ExtractCriteria(criteriaDTO);

                var param = new
                {
                    userid   = loginDTO.UserId,
                    ouid     = loginDTO.WorkOUId,
                    entityid = entityId,
                    fromdate = fromDate,
                    todate   = toDate
                };

                // 0 = Request To Me (actions I took as approver), other = Request By Me (instances I submitted)
                string sql = workflowType == 0 ? WorkFlowQB.WorkflowHistory : WorkFlowQB.WorkflowHistoryByMe;

                var result = (await _qe.QueryAsync<WorkflowHistoryListDTO>(
                    loginDTO, sql, param, tx)).ToList();

                await _qe.CommitAsync(tx);
                return result;
            }
            catch
            {
                await _qe.RollbackAsync(tx);
                throw;
            }
        }

        // ── Status ────────────────────────────────────────────────────────────

        public async Task<List<WorkflowStatusDTO>> WorkflowStatus(
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO)
        {
            var tx = await _qe.BeginTransactionAsync(loginDTO);
            try
            {
                var (entityId, _, fromDate, toDate) = ExtractCriteria(criteriaDTO);

                var param = new
                {
                    entityid = entityId,
                    userid   = loginDTO.UserId,
                    fromdate = fromDate,
                    todate   = toDate
                };

                var result = (await _qe.QueryAsync<WorkflowStatusDTO>(
                    loginDTO, WorkFlowQB.GetWorkflowStatus, param, tx)).ToList();

                await _qe.CommitAsync(tx);
                return result;
            }
            catch
            {
                await _qe.RollbackAsync(tx);
                throw;
            }
        }

        // ── Current approval status (real-time) ───────────────────────────────

        public async Task<List<WorkflowStatusDTO>> CurrentApprovalStatus(
            int entityId,
            int objectId,
            LoginDTO loginDTO)
        {
            var tx = await _qe.BeginTransactionAsync(loginDTO);
            try
            {
                var param = new
                {
                    entityid = entityId,
                    objectid = objectId,
                    userid   = loginDTO.UserId
                };

                var result = (await _qe.QueryAsync<WorkflowStatusDTO>(
                    loginDTO, WorkFlowQB.GetCurrentApprovalStatus, param, tx)).ToList();

                await _qe.CommitAsync(tx);
                return result;
            }
            catch
            {
                await _qe.RollbackAsync(tx);
                throw;
            }
        }

        // ── Workflow CRUD ─────────────────────────────────────────────────────

        public async Task<string> GetWorkflow(int WorkflowId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = WorkFlowQB.GET_WORKFLOW;
                var parameters = new { workflowid = WorkflowId };

                var workflowDict = new Dictionary<int, WorkflowDTO>();

                await _qe.QueryMultiMapAsync<WorkflowDTO, WorkflowDetailDTO, WorkflowDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!workflowDict.TryGetValue(parent.WorkflowId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.WorkflowDetailArray = new List<WorkflowDetailDTO>();
                            workflowDict[parent.WorkflowId] = existingParent;
                        }

                        if (child != null && child.WorkflowDetailId != 0)
                        {
                            existingParent.WorkflowDetailArray.Add(child);
                        }

                        return existingParent;
                    },
                    parameters,
                    splitOn: "WORKFLOWDETAILID"
                );

                var finalResult = workflowDict.Values.FirstOrDefault();
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve Workflow: {ex.Message}", ex);
            }
        }

        public async Task<int> SaveWorkflow(WorkflowDTO workflowDTO, LoginDTO login)
        {
            int newId = await _qe.ExecuteScalarAsync<int>(
                login,
                WorkFlowQB.SAVE_WORKFLOW,
                workflowDTO);

            workflowDTO.WorkflowId = newId;

            if (workflowDTO.WorkflowDetailArray?.Count > 0)
            {
                var statements = new List<(string sql, object param)>();

                foreach (var d in workflowDTO.WorkflowDetailArray)
                {
                    d.WorkflowDetailWorkflowId = newId;
                    statements.Add((WorkFlowQB.SAVE_WORKFLOW_DETAILS, d));
                }

                await _qe.ExecuteInTransactionAsync(login, statements);
            }

            return newId;
        }

        public async Task<int> UpdateWorkflow(WorkflowDTO workflowDTO, LoginDTO login)
        {
            var statements = new List<(string sql, object param)>();

            statements.Add((WorkFlowQB.UPDATE_WORKFLOW, (object)workflowDTO));

            foreach (var d in workflowDTO.WorkflowDetailArray)
            {
                d.WorkflowDetailWorkflowId = workflowDTO.WorkflowId;

                if (d.WorkflowDetailId == 0)
                    statements.Add((WorkFlowQB.SAVE_WORKFLOW_DETAILS, (object)d));
                else
                    statements.Add((WorkFlowQB.UPDATE_WORKFLOW_DETAILS, (object)d));
            }

            await _qe.ExecuteInTransactionAsync(login, statements);

            return workflowDTO.WorkflowId;
        }

        public async Task<int> DeleteWorkflowDetail(int WorkflowId, LoginDTO LoginDTO)
        {
            try
            {
                return await _qe.ExecuteAsync(
                    LoginDTO,
                    WorkFlowQB.DELETE_WORKFLOW_DETAILS,
                    new { Workflowid = WorkflowId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting Workflow detail for ID {WorkflowId}", ex);
            }
        }

        public async Task<int> DeleteWorkflow(int WorkflowId, LoginDTO LoginDTO)
        {
            try
            {
                return await _qe.ExecuteAsync(
                    LoginDTO,
                    WorkFlowQB.DELETE_WORKFLOW,
                    new { Workflowid = WorkflowId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting Workflow with ID {WorkflowId}", ex);
            }
        }

        public async Task<IEnumerable<WorkflowDTO>> GetSelectListWorkflow(
    CriteriaDTO criteriaDTO,
    LoginDTO loginDTO)
        {
            try
            {
                var workflowDict = new Dictionary<long, WorkflowDTO>();

                await _qe.QueryMultiMapAsync<WorkflowDTO, WorkflowDetailDTO, WorkflowDTO>(
                    loginDTO,
                    WorkFlowQB.GET_SELECT_LIST_WORKFLOW,
                    (workflow, detail) =>
                    {
                        if (!workflowDict.TryGetValue(workflow.WorkflowId, out var currentWorkflow))
                        {
                            currentWorkflow = workflow;
                            currentWorkflow.WorkflowDetailArray = new List<WorkflowDetailDTO>();
                            workflowDict.Add(currentWorkflow.WorkflowId, currentWorkflow);
                        }

                        if (detail != null && detail.WorkflowDetailId > 0)
                        {
                            if (!currentWorkflow.WorkflowDetailArray.Any(x =>
                                x.WorkflowDetailId == detail.WorkflowDetailId))
                            {
                                currentWorkflow.WorkflowDetailArray.Add(detail);
                            }
                        }

                        return currentWorkflow;
                    },
                    null,
                    splitOn: "WorkflowDetailId"
                );

                return workflowDict.Values.ToList();
            }
            catch
            {
                throw;
            }
        }

        // ── Monitor dashboard ─────────────────────────────────────────────────

        public async Task<WorkflowMonitorSummaryDTO> GetWorkflowMonitor(
            int entityId, DateTime? dateFrom, DateTime? dateTo,
            int page, int pageSize,
            LoginDTO loginDTO)
        {
            var param = new
            {
                TenantId  = loginDTO.ClientId,
                EntityId  = entityId,
                DateFrom  = dateFrom,
                DateTo    = ToDayEndExclusiveBound(dateTo?.Date),
                Offset    = (page - 1) * pageSize,
                PageSize  = pageSize
            };

            var counts = await _qe.QuerySingleAsync<WorkflowMonitorCountsDTO>(
                loginDTO, GB5Shared.Query.WorkFlow.WorkFlowQB.GetMonitorCounts, param);

            var instances = (await _qe.QueryAsync<WorkflowMonitorInstanceDTO>(
                loginDTO, GB5Shared.Query.WorkFlow.WorkFlowQB.GetMonitorInstances, param)).ToList();

            return new WorkflowMonitorSummaryDTO
            {
                PendingCount  = counts?.PendingCount  ?? 0,
                ApprovedCount = counts?.ApprovedCount ?? 0,
                RejectedCount = counts?.RejectedCount ?? 0,
                ReturnedCount = counts?.ReturnedCount ?? 0,
                OverdueCount  = counts?.OverdueCount  ?? 0,
                TotalCount    = counts?.TotalCount    ?? 0,
                Instances     = instances
            };
        }

        // ── Helpers ───────────────────────────────────────────────────────────

        private static (long? entityId, int? status, DateTime? fromDate, DateTime? toDate)
            ExtractCriteria(CriteriaDTO criteria)
        {
            return (
                GetCriteriaValue<long?>(criteria, "EntityId"),
                GetCriteriaValue<int?>(criteria, "Status"),
                GetCriteriaValue<DateTime?>(criteria, "FromDate")?.Date,
                ToDayEndExclusiveBound(GetCriteriaValue<DateTime?>(criteria, "ToDate")?.Date)
            );
        }

        // SQL Server 'datetime' tops out at 9999-12-31 23:59:59.997, so a plain
        // date.AddDays(1) overflows when callers send the "no upper bound" sentinel
        // ToDate = 9999-12-31. Clamp instead of adding a day in that case.
        private static readonly DateTime SqlDateTimeMax = new(9999, 12, 31, 23, 59, 59, 997);

        private static DateTime? ToDayEndExclusiveBound(DateTime? date)
        {
            if (date is null)
                return null;

            return date.Value.Date >= new DateTime(9999, 12, 31)
                ? SqlDateTimeMax
                : date.Value.Date.AddDays(1);
        }

        private static T GetCriteriaValue<T>(CriteriaDTO criteria, string fieldName)
        {
            if (criteria?.SectionCriteriaList == null)
                return default!;

            foreach (var section in criteria.SectionCriteriaList)
            {
                if (section.AttributesCriteriaList == null)
                    continue;

                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (!string.Equals(attr.FieldName, fieldName, StringComparison.OrdinalIgnoreCase))
                        continue;

                    if (attr.FieldValue == null)
                        return default!;

                    string raw = attr.FieldValue is System.Text.Json.JsonElement je
                        ? je.ToString()
                        : attr.FieldValue.ToString()!;

                    if (string.IsNullOrWhiteSpace(raw))
                        return default!;

                    return (T)Convert.ChangeType(
                        raw, Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T));
                }
            }

            return default!;
        }
    }
}