using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using Newtonsoft.Json;
using PayRollDAL.DTO;
using PayRollDAL.DTO.Job;
using PayRollDAL.Query.Job;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.Job
{
    public class JobDAL : IJobDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public JobDAL(IQueryExecutor IQueryExecutor)
        {
            _QueryExecutor = IQueryExecutor;
        }

        // ── Picklist for grid ─────────────────────────────────────────────

        public async Task<string> GetSelectListJob(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string jobNumberFilter = GetCriteriaStringValue(CriteriaDTO, "JobNumber");
                var jobStatusAttr = GetCriteriaAttribute(CriteriaDTO, "JobStatus");

                var parameters = new DynamicParameters();
                string filterClause = "";

                if (!string.IsNullOrWhiteSpace(jobNumberFilter))
                {
                    filterClause += " AND JOB.JOBNUMBER LIKE @jobnumber";
                    parameters.Add("jobnumber", $"%{jobNumberFilter}%");
                }

                if (jobStatusAttr?.FieldValue != null)
                {
                    string jobStatusFilter = jobStatusAttr.FieldValue is JsonElement je
                        ? (je.ValueKind == JsonValueKind.String ? je.GetString() : je.ToString())
                        : jobStatusAttr.FieldValue.ToString();

                    if (!string.IsNullOrWhiteSpace(jobStatusFilter))
                    {
                        if (jobStatusAttr.OperationType == CriteriaDTO.OperationType.In
                            || jobStatusAttr.OperationType == CriteriaDTO.OperationType.NotIn)
                        {
                            var statusValues = jobStatusFilter
                                .Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
                                .Select(int.Parse)
                                .ToArray();

                            string op = jobStatusAttr.OperationType == CriteriaDTO.OperationType.NotIn
                                ? "NOT IN" : "IN";
                            filterClause += $" AND JOB.JOBSTATUS {op} @jobstatus";
                            parameters.Add("jobstatus", statusValues);
                        }
                        else
                        {
                            filterClause += " AND JOB.JOBSTATUS = @jobstatus";
                            parameters.Add("jobstatus", int.Parse(jobStatusFilter.Trim()));
                        }
                    }
                }

                if (IsCount)
                {
                    string countSql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => JobQB.GET_SELECTLIST_JOB_COUNT_SQL,
                        DBType.PostGre => JobQB.GET_SELECTLIST_JOB_COUNT_PG,
                        _ => throw new Exception("Unsupported database type")
                    };
                    countSql = countSql.Replace("/*FILTER*/", filterClause);

                    int count = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql, parameters)
                        .ConfigureAwait(false);
                    return count.ToString();
                }
                else
                {
                    string sql = LoginDTO.DatabaseType switch
                    {
                        DBType.SQL => JobQB.GET_SELECTLIST_JOB_SQL,
                        DBType.PostGre => JobQB.GET_SELECTLIST_JOB_PG,
                        _ => throw new Exception("Unsupported database type")
                    };
                    sql = sql.Replace("/*FILTER*/", filterClause);

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

                    var result = await _QueryExecutor.QueryAsync<JobPicklistDTO>(LoginDTO, sql, parameters)
                        .ConfigureAwait(false);
                    return JsonConvert.SerializeObject(result);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error in GetSelectListJob", ex);
            }
        }

        // ── Get single header (all FK joins) ────────────────────────────────

        public async Task<JobDTO> GetJob(int JobId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LoginDTO.DatabaseType switch
                {
                    DBType.SQL => JobQB.GET_JOB_SQL,
                    DBType.PostGre => JobQB.GET_JOB_PG,
                    _ => throw new Exception("Unsupported database type")
                };

                var parameters = new { jobid = JobId };
                return await _QueryExecutor.QuerySingleAsync<JobDTO>(LoginDTO, sql, parameters).ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Insert / Update (transaction provided by BLL) ────────────────────

        public async Task<int> SaveJob(JobDTO JobDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            return await _QueryExecutor.ExecuteAsync(
                LoginDTO, JobQB.SAVE_JOB, JobDTO, DbTransaction).ConfigureAwait(false);
        }

        public async Task<int> UpdateJob(JobDTO JobDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            return await _QueryExecutor.ExecuteAsync(
                LoginDTO, JobQB.UPDATE_JOB, JobDTO, DbTransaction).ConfigureAwait(false);
        }

        // ── Delete ────────────────────────────────────────────────────────

        public async Task<string> DeleteJob(int JobId, LoginDTO LoginDTO)
        {
            int result = await _QueryExecutor.ExecuteAsync(
                LoginDTO, JobQB.DELETE_JOB, new { jobid = JobId }).ConfigureAwait(false);

            return result > 0 ? SuccessResponse.DeleteSuccessMessage : ErrorResponse.DeleteNotFoundMessage;
        }

        // ── Financial period check (mirrors TimeSlipDAL.GetPeriodAsync) ─────

        public async Task<PeriodDTO?> GetPeriodAsync(int PeriodId, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            var result = await _QueryExecutor.QueryAsync<PeriodDTO>(
                LoginDTO,
                JobQB.GET_PERIOD,
                new { PeriodId },
                DbTransaction
            ).ConfigureAwait(false);

            return result.FirstOrDefault();
        }

        // ── "Must be an employee" / reporting-chain lookup ───────────────────

        public async Task<JobEmployeeLookupDTO?> GetEmployeeForJobAsync(int EmployeeId, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            var result = await _QueryExecutor.QueryAsync<JobEmployeeLookupDTO>(
                LoginDTO,
                JobQB.GET_EMPLOYEE_FOR_JOB,
                new { EmployeeId },
                DbTransaction
            ).ConfigureAwait(false);

            return result.FirstOrDefault();
        }

        // ── List grid ─────────────────────────────────────────────────────

        public async Task<string> GetJobList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql = JobQB.GET_JOB_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                int ouId = GetCriteriaValue(CriteriaDTO, "OUId", -1);
                int departmentId = GetCriteriaValue(CriteriaDTO, "DepartmentId", -1);

                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals,
                    OUId = ouId == -1 ? (int?)null : ouId,
                    DepartmentId = departmentId == -1 ? (int?)null : departmentId
                };

                var list = (await _QueryExecutor.QueryAsync<JobDTO>(LoginDTO, sql, parameter)
                                                 .ConfigureAwait(false)).ToList();
                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Criteria helpers ──────────────────────────────────────────────

        private static string? ExtractLikeSearchText(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return null;
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr?.OperationType == CriteriaDTO.OperationType.Like && attr.FieldValue != null)
                    {
                        var text = attr.FieldValue.ToString();
                        if (!string.IsNullOrWhiteSpace(text)) return text;
                    }
                }
            }
            return null;
        }

        private static (int? EqualTo, int? NotEqualTo) ExtractStatusFilter(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return (null, null);
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr == null || !string.Equals(attr.FieldName, "Status", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (attr.FieldValue == null || !int.TryParse(attr.FieldValue.ToString(), out int value))
                        continue;

                    if (attr.OperationType == CriteriaDTO.OperationType.Equal)
                        return (value, null);
                    if (attr.OperationType == CriteriaDTO.OperationType.NotEqual)
                        return (null, value);
                }
            }
            return (null, null);
        }

        private int GetCriteriaValue(CriteriaDTO criteriaDTO, string fieldName, int defaultValue)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

                if (attr?.FieldValue is JsonElement je)
                    return je.ValueKind == JsonValueKind.Number ? je.GetInt32()
                         : int.TryParse(je.GetString(), out int p) ? p : defaultValue;

                return attr != null ? Convert.ToInt32(attr.FieldValue) : defaultValue;
            }
            catch { return defaultValue; }
        }

        private static AttributesCriteriaDTO GetCriteriaAttribute(CriteriaDTO criteriaDTO, string fieldName)
        {
            return criteriaDTO?.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList)
                .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));
        }

        private string GetCriteriaStringValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            try
            {
                var attr = criteriaDTO?.SectionCriteriaList?
                    .SelectMany(s => s.AttributesCriteriaList)
                    .FirstOrDefault(a => string.Equals(a.FieldName, fieldName, StringComparison.OrdinalIgnoreCase));

                if (attr?.FieldValue is JsonElement je)
                    return je.ValueKind == JsonValueKind.String ? je.GetString() : je.ToString();

                return attr?.FieldValue?.ToString();
            }
            catch { return null; }
        }

        // GB4 parity: EmployeeDAL.ApplyJOBDetailReport's CriteriaWithQueryField map.
        private static readonly (string FieldName, string Column)[] JobDetailReportCriteriaFields =
        {
            ("DepartmentId", "J.DEPARTMENTID"),
            ("DesignationId", "J.DESIGNATIONID"),
            ("OUId", "J.FOROUID"),
            ("BIZtransactionTypeId", "J.BIZTRANSACTIONTYPEID"),
            ("JobId", "J.JOBID"),
            ("PartyId", "J.PARTYID"),
            ("PartyBranchId", "J.PARTYBRANCHID"),
            ("AllocationId", "J.ALLOCATIONID"),
            ("EmploymentType", "J.EMPLOYMENTTYPE"),
            ("Experience", "J.EXPERIENCE"),
            ("JOBFunction", "J.JOBFUNCTION"),
            ("ReportingToDesignationId", "J.REPORTINGTODESIGNATIONID"),
            ("HiringManagerId", "J.HIRINGMANAGERID"),
            ("HRInchargeId", "J.HRINCHARGEID"),
            ("ReportingToEmployeeId", "J.REPORTINGTOEMPLOYEEID"),
            ("SkillSetId", "J.SKILLSETID"),
            ("SelectionProcessId", "J.SELECTIONPROCESSID"),
            ("JOBStatus", "J.JOBSTATUS"),
            ("JOBForType", "J.JOBFORTYPE")
        };

        public async Task<(List<JOBDetailReportDTO> Items, int Total)> GetJOBDetailReport(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var whereClauses = new List<string>();
            var parameters = new Dapper.DynamicParameters();

            void ApplyCriteria(CriteriaDTO criteria)
            {
                if (criteria == null) return;

                foreach (var (fieldName, column) in JobDetailReportCriteriaFields)
                {
                    int value = GetCriteriaValue(criteria, fieldName, -1);
                    if (value == -1) continue;

                    string paramName = $"Crit_{fieldName}";
                    whereClauses.Add($"{column} = @{paramName}");
                    parameters.Add(paramName, value);
                }

                string periodFromDate = GetCriteriaStringValue(criteria, "PeriodFromDate");
                string periodToDate = GetCriteriaStringValue(criteria, "PeriodToDate");
                if (!string.IsNullOrWhiteSpace(periodFromDate) && !string.IsNullOrWhiteSpace(periodToDate))
                {
                    var fromDt = DateTimeOffset.FromUnixTimeSeconds(Convert.ToInt64(periodFromDate)).UtcDateTime;
                    var toDt = DateTimeOffset.FromUnixTimeSeconds(Convert.ToInt64(periodToDate)).UtcDateTime;
                    whereClauses.Add("J.JOBDATE >= @Crit_PeriodFromDate AND J.JOBDATE <= @Crit_PeriodToDate");
                    parameters.Add("Crit_PeriodFromDate", fromDt);
                    parameters.Add("Crit_PeriodToDate", toDt);
                }
            }

            ApplyCriteria(criteriaDTO);
            ApplyCriteria(loginDTO.UserCriteriaDTO);

            string extraWhere = whereClauses.Count > 0
                ? " AND " + string.Join(" AND ", whereClauses)
                : string.Empty;

            string listSql = JobQB.GET_JOB_DETAIL_REPORT.Replace(":replacefields", extraWhere)
                + " ORDER BY J.JOBID OFFSET @Offset ROWS FETCH NEXT @MaxResult ROWS ONLY";
            string countSql = JobQB.GET_JOB_DETAIL_REPORT_COUNT.Replace(":replacefields", extraWhere);

            int offset = firstNumber <= 1 ? 0 : firstNumber - 1;
            parameters.Add("Offset", offset);
            parameters.Add("MaxResult", maxResult > 0 ? maxResult : 50);

            var listTask = _QueryExecutor.QueryAsync<JOBDetailReportDTO>(loginDTO, listSql, parameters, cancellationToken: ct);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, countSql, parameters, cancellationToken: ct);
            await Task.WhenAll(listTask, countTask).ConfigureAwait(false);

            return (listTask.Result.ToList(), countTask.Result);
        }
    }
}