using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using PayRollDAL.DTO.Applicant;
using PayRollDAL.Query.Applicant;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.Applicant
{
    public class ApplicantDAL : IApplicantDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IValidation _validation;

        public ApplicantDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _queryExecutor = queryExecutor;
            _validation = validation;
        }

        public async Task<IList<ApplicantPicklistDTO>> GetSelectListApplicant(
            int firstNumber,
            int maxResult,
            int type,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool isCount = false)
        {
            try
            {
                var (whereFragment, criteriaParameters) = ApplyApplicantSelectListCriteria(criteriaDTO, type);

                string baseSql = type == 0
                    ? (isCount ? ApplicantQB.GET_APPLICANT_SELECTLIST_TRANSACTION_COUNT : ApplicantQB.GET_APPLICANT_SELECTLIST_TRANSACTION)
                    : (isCount ? ApplicantQB.GET_APPLICANT_SELECTLIST_MASTER_COUNT : ApplicantQB.GET_APPLICANT_SELECTLIST_MASTER);

                string sql = baseSql.Replace("/*CRITERIA*/", whereFragment);

                if (!isCount)
                {
                    var parameters = new Dictionary<string, object>(criteriaParameters)
                    {
                        { "FirstNumber", firstNumber },
                        { "MaxResult", maxResult }
                    };

                    var result = await _queryExecutor.QueryAsync<ApplicantPicklistDTO>(loginDTO, sql, parameters);
                    return result.ToList();
                }
                else
                {
                    var totalCount = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, criteriaParameters);
                    return new List<ApplicantPicklistDTO> { new ApplicantPicklistDTO { TotalCount = totalCount } };
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static (string WhereFragment, Dictionary<string, object> Parameters) ApplyApplicantSelectListCriteria(CriteriaDTO criteriaDTO, int type)
        {
            var parameters = new Dictionary<string, object>();
            var whereFragment = new System.Text.StringBuilder();

            if (criteriaDTO?.SectionCriteriaList == null)
                return (whereFragment.ToString(), parameters);

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (string.IsNullOrWhiteSpace(attr.FieldName) || attr.FieldValue == null) continue;

                    switch (attr.FieldName.ToLowerInvariant())
                    {
                        case "applicantid":
                            whereFragment.Append(" AND app.APPLICANTID = @ApplicantId");
                            parameters["ApplicantId"] = attr.FieldValue;
                            break;
                        case "applicantstatus":
                            whereFragment.Append(" AND app.APPLICANTSTATUS = @ApplicantStatus");
                            parameters["ApplicantStatus"] = attr.FieldValue;
                            break;
                        case "jobid":
                            if (type == 0)
                            {
                                whereFragment.Append(" AND app.JOBID IN (SELECT value FROM STRING_SPLIT(@JobId, ','))");
                                parameters["JobId"] = attr.FieldValue?.ToString();
                            }
                            break;
                        case "tosearch":
                            whereFragment.Append(" AND (app.APPLICANTNAME LIKE @ToSearch OR app.MAILID LIKE @ToSearch OR app.TOTALEXPERIENCE LIKE @ToSearch OR app.RELAVENTEXPERIENCE LIKE @ToSearch OR dsource.GCMCODE LIKE @ToSearch OR dsource.GCMNAME LIKE @ToSearch OR app.MOBILE LIKE @ToSearch OR app.ALTERNATEMOBILE LIKE @ToSearch)");
                            parameters["ToSearch"] = $"%{attr.FieldValue}%";
                            break;
                        case "firstname":
                            whereFragment.Append(" AND (app.APPLICANTNAME LIKE @FirstName OR app.MAILID LIKE @FirstName)");
                            parameters["FirstName"] = $"%{attr.FieldValue}%";
                            break;
                    }
                }
            }

            return (whereFragment.ToString(), parameters);
        }
    }
}