using AdminDAL.DTO.Department;
using AdminDAL.Query.Department;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AdminDAL.CustomCode.Department
{
    public class DepartmentDAL : IDepartmentDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly ILogger<DepartmentDAL> _logger;

        public DepartmentDAL(IQueryExecutor IQueryExecutor, ILogger<DepartmentDAL> logger)
        {
            _QueryExecutor = IQueryExecutor;
            _logger = logger;
        }

        public async Task<string> GetDepartment(int DepartmentId, LoginDTO LoginDTO)
        {
            try
            {              
                string sql = DepartmentQB.GET_DEPARTMENT;
                var parameters = new { departmentid = DepartmentId };
                DepartmentDTO DepartmentDTOs = await _QueryExecutor.QuerySingleAsync<DepartmentDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(DepartmentDTOs);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListDepartment(LoginDTO LoginDTO, CriteriaDTO CriteriaDTO = null!)
        {
            try
            {
                string code = "", name = "";
                int OUId = 0;
                int DepartmentId = 0;
                if (CriteriaDTO.SectionCriteriaList != null)
                {
                    for (int i = 0; i < CriteriaDTO.SectionCriteriaList.Count; i++)
                    {
                        for (int j = 0; j < CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList.Count; j++)
                        {
                            if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[j].FieldName.ToLower() == "code")
                            {
                                code = Convert.ToString(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[j].FieldValue);
                            }
                            else if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[j].FieldName.ToLower() == "name")
                            {
                                name = Convert.ToString(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[j].FieldValue);
                            }
                            else if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[j].FieldName.ToLower() == "ouid")
                            {
                                OUId = Convert.ToInt32(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[j].FieldValue);
                            }
                            else if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[j].FieldName.ToLower() == "departmentid")
                            {
                                DepartmentId = Convert.ToInt32(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[j].FieldValue);
                            }
                        }
                    }
                }
                string Sql = DepartmentQB.GetSelectList_Department;
                if (code != "" && name != "")
                {
                    Sql = Sql + " AND (md.DEPARTMENTCODE like '%" + code + "%' or md.DEPARTMENTNAME like '%" + name + "%')" + "\r\n";
                }
                if (DepartmentId != 0)
                {
                    Sql = Sql + " AND (md.DEPARTMENTID IN (" + DepartmentId + ")" + "\r\n";
                }
                if (LoginDTO.DatabaseType == 0) //Sql
                {
                    if (OUId != 0)
                    {
                        Sql = Sql + "  and (" + OUId + " = -1 or  isnull(len(ltrim(rtrim(md.applicableouids))), 0) < 4 or md.applicableouids like '%' + convert(varchar, " + OUId + ") + '%' )" + "\r\n";
                    }
                }                
                var result = await _QueryExecutor.QueryAsync<DepartmentPicklistDTO>(LoginDTO, Sql, null!);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "GetSelectListDepartment failed");
                throw;
            }
        }

        public async Task SaveDepartment(DepartmentDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, DepartmentQB.SAVE_DEPARTMENT, dto);
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateDepartment(DepartmentDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, DepartmentQB.UPDATE_DEPARTMENT, dto);
            }
            catch (Exception) { throw; }
        }

        public async Task<string> DeleteDepartment(int departmentId, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, DepartmentQB.DELETE_DEPARTMENT, new { DepartmentId = departmentId });
                return GB5Shared.Resource.Response.SuccessResponse.DeleteSuccessMessage;
            }
            catch (Exception) { throw; }
        }
    }
}