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

namespace PayRollDAL.CustomeCode.TDSContact
{
    public class TDSContactDAL : ITDSContactDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public TDSContactDAL(IQueryExecutor QueryExecutor, IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }

        public async Task<string> GetSelectListTDSContact(int FirstNumber,int MaxResult,CriteriaDTO CriteriaDTO,LoginDTO LoginDTO,bool IsCount = false)
        {
            try
            {
                string SQL = string.Empty;

                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        SQL = TDSContactQB.GET_SELECTLIST_TDSCONTACT;
                        break;
                    case DBType.PostGre:
                        SQL = TDSContactQB.GET_SELECTLIST_TDSCONTACT_PG;
                        break;
                    case DBType.Oracle:
                        SQL = TDSContactQB.GET_SELECTLIST_TDSCONTACT_ORACLE;
                        break;
                    case DBType.MySQL:
                        SQL = TDSContactQB.GET_SELECTLIST_TDSCONTACT_MYSQL;
                        break;
                    default:
                        throw new Exception("Unsupported database type");
                }

                // 👉 Simple where builder
                string where = BuildWhere(CriteriaDTO, LoginDTO.DatabaseType);

                SQL = SQL.Replace("/**where**/", where);

                var Parameters = new
                {
                    firstnumber = FirstNumber,
                    maxresult = MaxResult
                };

                var Result = await _QueryExecutor.QueryAsync<TDSContactPicklistDTO>(LoginDTO,SQL,Parameters);

                return JsonConvert.SerializeObject(Result);
            }
            catch
            {
                throw;
            }
        }
        private string BuildWhere(CriteriaDTO criteria, byte dbType)
        {
            if (criteria?.SectionCriteriaList == null)
                return "";

            // PostgreSQL LIKE is case-sensitive; use ILIKE to match SQL Server behaviour
            string likeOp = dbType == DBType.PostGre ? "ILIKE" : "LIKE";

            string where = "";

            foreach (var section in criteria.SectionCriteriaList)
            {
                foreach (var item in section.AttributesCriteriaList)
                {
                    if (item.FieldValue == null || item.FieldValue.ToString() == "")
                        continue;

                    if (item.FieldName == "ContactType")
                        where += $" AND TC.CONTACTTYPE = {item.FieldValue}";

                    if (item.FieldName == "EmployeeId")
                        where += $" AND TC.EMPLOYEEID = {item.FieldValue}";

                    if (item.FieldName == "PAN")
                        where += $" AND TC.PAN {likeOp} '%{item.FieldValue}%'";

                    if (item.FieldName == "Name")
                        where += $" AND TC.NAME {likeOp} '%{item.FieldValue}%'";
                }
            }

            return where;
        }


        public async Task<string> GetTDSContact(int TDSContactId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = TDSContactQB.GET_TDSCONTACT;
                var parameters = new { TDScontactid = TDSContactId };
                TDSContactDTO TDSContactDTOs = await _QueryExecutor.QuerySingleAsync<TDSContactDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(TDSContactDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }


        public async Task<int> SaveTDSContact(TDSContactDTO TDSContactDTO, LoginDTO LoginDTO)
        {
            try
            {

                const string Sql = TDSContactQB.SAVE_TDSCONTACT;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, TDSContactDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateTDSContact(TDSContactDTO TDSContactDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = TDSContactQB.UPDATE_TDSCONTACT;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, TDSContactDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }


        public async Task<string> DeleteTDSContact(int TDSContactId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = TDSContactQB.DELETE_TDSCONTACT;
                var parameter = new { TDScontactid = TDSContactId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameter);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }
    }
}