using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using PayRollDAL.DTO.Declaration;
using PayRollDAL.Query.Declaration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.AccessControl;
using System.Text;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;
namespace PayRollDAL.CustomeCode.Declaration
{
    public class DeclarationDAL : IDeclarationDAL
    {
        private readonly IQueryExecutor _Queryexecutor;
        public DeclarationDAL(IQueryExecutor IQueryExecutor)
        {
            _Queryexecutor = IQueryExecutor;
        }
        public async Task<object> GetDeclaration(int DeclarationId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = "";
                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        sql = DeclarationQB.GET_DECLARATION;
                        break;
                    case DBType.PostGre:
                        sql = DeclarationQB.GET_DECLARATION_PG;
                        break;
                    case DBType.Oracle:
                        sql = DeclarationQB.GET_DECLARATION_ORACLE;
                        break;
                    case DBType.MySQL:
                        sql = DeclarationQB.GET_DECLARATION_MYSQL;
                        break;
                    default:
                        throw new Exception("Unsupported database type");
                }
                var parameters = new { declarationid = DeclarationId };
                var DeclarationDict = new Dictionary<int, DeclarationDTO>();
                var result = await _Queryexecutor.QueryMultiMapAsync<DeclarationDTO, DeclarationDetailDTO, DeclarationDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!DeclarationDict.TryGetValue(parent.DeclarationId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.DeclarationDetailArray = new List<DeclarationDetailDTO>();
                            DeclarationDict[parent.DeclarationId] = existingParent;
                        }
                        if (child != null && child.DeclarationId != 0)
                        {
                            existingParent.DeclarationDetailArray.Add(child);
                        }
                        return existingParent;
                    },
                    parameters,
                    splitOn: "DeclarationDetailId"
                );
                var finalResult = DeclarationDict.Values.FirstOrDefault();
                return finalResult;
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve Declaration data: {ex.Message}", ex);
            }
        }
        public async Task<int> SaveDeclaration(DeclarationDTO declarationDTO, LoginDTO loginDTO)
        {
            try
            {
                string declarationSql, detailSql, countSql;

                switch (loginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        declarationSql = DeclarationQB.SAVE_DECLARATION_SQL;
                        detailSql = DeclarationQB.SAVE_DECLARATIONDETAIL_SQL;
                        countSql = DeclarationQB.GET_DECLARATION_COUNT_SQL;
                        break;

                    case DBType.PostGre:
                        declarationSql = DeclarationQB.SAVE_DECLARATION_PG;
                        detailSql = DeclarationQB.SAVE_DECLARATIONDETAIL_PG;
                        countSql = DeclarationQB.GET_DECLARATION_COUNT_PG;
                        break;

                    default:
                        throw new NotSupportedException($"Database type '{loginDTO.DatabaseType}' is not supported for saving declarations.");
                }

                int existingCount = await _Queryexecutor.ExecuteScalarAsync<int>(
                    loginDTO, countSql,
                    new
                    {
                        declarationDTO.EmployeeId,
                        declarationDTO.PeriodId,
                        declarationDTO.DeclarationPlanNumber,
                        declarationDTO.DeclarationId
                    });

                if (existingCount > 0)
                    throw new MethodNotAllowedException(
                        $"A declaration already exists for Employee ID {declarationDTO.EmployeeId} in Period {declarationDTO.PeriodId} with Plan Number {declarationDTO.DeclarationPlanNumber}. Please edit the existing record instead of creating a new one.");

                var statements = new List<(string sql, object param)>();

                statements.Add((declarationSql, declarationDTO));

                foreach (var d in declarationDTO.DeclarationDetailArray)
                {
                    statements.Add((
    detailSql,
    new
    {
        d.DeclarationDetailId,
        d.DeclarationId,
        d.DeclarationDetailSlno,
        d.DeclarationDetailType,
        d.DeclarationDetailTypeSlno,
        d.DeclarationDetailParticulars,
        d.DeclarationDetailPrinciple,
        d.DeclarationDetailInterest,
        TdsContactId = d.TDSContactId,
        d.DeclarationDetailDetailRemarks,
        d.DeclarationDetailDetailStatus,
        d.DeclarationDetailFeedback,
        d.DeclarationDetailApprovedAmount,
        d.DeclarationDetailFromPeriod,
        d.DeclarationDetailToPeriod,
        d.DeclarationDetailRentReceived,
        d.DeclarationDetailLocalTax,
        d.DeclarationDetailStandardDeduction,
        d.DeclarationDetailNetIncome,
        TdsSectionDetailId = d.TDSSectionDetailId,
        d.DeclarationDetailIsMetro,
        d.DeclarationDetailDeclaredAmount,
        d.ApproverId,

        DeclarationDetailApprovedOn =
            d.DeclarationDetailApprovedOn ?? DateTime.UtcNow
    }
));
                }

                await _Queryexecutor.ExecuteInTransactionAsync(loginDTO, statements);
                return declarationDTO.DeclarationId;
            }
            catch (MethodNotAllowedException)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Unable to save the declaration for Employee ID {declarationDTO.EmployeeId}. " +
                    $"Please check your inputs and try again. Technical detail: {ex.Message}", ex);
            }
        }

        public async Task<int> UpdateDeclaration(DeclarationDTO declarationDTO, LoginDTO loginDTO)
        {
            try
            {
                string parentSql, insertDetailSql, updateDetailSql;
                switch (loginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        parentSql = DeclarationQB.UPDATE_DECLARATION_SQL;
                        insertDetailSql = DeclarationQB.SAVE_DECLARATIONDETAIL_SQL;
                        updateDetailSql = DeclarationQB.UPDATE_DECLARATIONDETAIL_SQL;
                        break;
                    case DBType.PostGre:
                        parentSql = DeclarationQB.UPDATE_DECLARATION_PG;
                        insertDetailSql = DeclarationQB.SAVE_DECLARATIONDETAIL_PG;
                        updateDetailSql = DeclarationQB.UPDATE_DECLARATIONDETAIL_PG;
                        break;
                    default:
                        throw new NotSupportedException($"Database type '{loginDTO.DatabaseType}' is not supported for updating declarations.");
                }

                var statements = new List<(string sql, object param)>();

                // Update parent
                statements.Add((parentSql, declarationDTO));

                // Get existing detail IDs for this declaration
                string getExistingIdsSql = loginDTO.DatabaseType == DBType.SQL
                    ? "SELECT DECLARATIONDETAILID FROM TDECLARATIONDETAIL WHERE DECLARATIONID = @DeclarationId"
                    : "SELECT declarationdetailid FROM tdeclarationdetail WHERE declarationid = @DeclarationId";

                var existingIds = await _Queryexecutor.QueryAsync<int>(
                    loginDTO,
                    getExistingIdsSql,
                    new { DeclarationId = declarationDTO.DeclarationId });

                var existingIdSet = new HashSet<int>(existingIds);

                // Process children
                foreach (var d in declarationDTO.DeclarationDetailArray)
                {
                    bool isNewRecord = !existingIdSet.Contains(d.DeclarationDetailId);

                    if (isNewRecord)
                    {
                        statements.Add((
                            insertDetailSql,
                            new
                            {
                                d.DeclarationDetailId,
                                d.DeclarationId,
                                d.DeclarationDetailSlno,
                                d.DeclarationDetailType,
                                d.DeclarationDetailTypeSlno,
                                d.DeclarationDetailParticulars,
                                d.DeclarationDetailPrinciple,
                                d.DeclarationDetailInterest,
                                TdsContactId = d.TDSContactId,
                                d.DeclarationDetailDetailRemarks,
                                d.DeclarationDetailDetailStatus,
                                d.DeclarationDetailFeedback,
                                d.DeclarationDetailApprovedAmount,
                                d.DeclarationDetailFromPeriod,
                                d.DeclarationDetailToPeriod,
                                d.DeclarationDetailRentReceived,
                                d.DeclarationDetailLocalTax,
                                d.DeclarationDetailStandardDeduction,
                                d.DeclarationDetailNetIncome,
                                TdsSectionDetailId = d.TDSSectionDetailId,
                                d.DeclarationDetailIsMetro,
                                d.DeclarationDetailDeclaredAmount,
                                d.ApproverId,
                                DeclarationDetailApprovedOn =
    d.DeclarationDetailApprovedOn ?? DateTime.UtcNow
                            }
                        ));
                    }
                    else
                    {
                        statements.Add((updateDetailSql, new
                        {
                            d.DeclarationDetailId,
                            d.DeclarationDetailSlno,
                            d.DeclarationDetailType,
                            d.DeclarationDetailTypeSlno,
                            d.DeclarationDetailParticulars,
                            d.DeclarationDetailPrinciple,
                            d.DeclarationDetailInterest,
                            TdsContactId = d.TDSContactId,
                            d.DeclarationDetailDetailRemarks,
                            d.DeclarationDetailDetailStatus,
                            d.DeclarationDetailFeedback,
                            d.DeclarationDetailApprovedAmount,
                            d.DeclarationDetailFromPeriod,
                            d.DeclarationDetailToPeriod,
                            d.DeclarationDetailRentReceived,
                            d.DeclarationDetailLocalTax,
                            d.DeclarationDetailStandardDeduction,
                            d.DeclarationDetailNetIncome,
                            TdsSectionDetailId = d.TDSSectionDetailId,
                            d.DeclarationDetailIsMetro,
                            d.DeclarationDetailDeclaredAmount,
                            d.ApproverId,
                            DeclarationDetailApprovedOn =
    d.DeclarationDetailApprovedOn ?? DateTime.UtcNow
                        }));
                    }
                }

                await _Queryexecutor.ExecuteInTransactionAsync(loginDTO, statements);
                return declarationDTO.DeclarationId;
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Unable to update Declaration ID {declarationDTO.DeclarationId}. " +
                    $"Please try again or contact support if the issue persists. Technical detail: {ex.Message}", ex);
            }
        }


        public async Task<int> DeleteDeclarationDetail(int declarationId, LoginDTO loginDTO)
        {
            try
            {
                string sql;
                switch (loginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        sql = DeclarationQB.DELETE_DECLARATIONDETAIL_SQL;
                        break;
                    case DBType.PostGre:
                        sql = DeclarationQB.DELETE_DECLARATIONDETAIL_PG;
                        break;
                    case DBType.Oracle:
                        sql = DeclarationQB.DELETE_DECLARATIONDETAIL_ORACLE;
                        break;
                    case DBType.MySQL:
                        sql = DeclarationQB.DELETE_DECLARATIONDETAIL_MYSQL;
                        break;
                    default:
                        throw new Exception("Unsupported database type");
                }

                return await _Queryexecutor.ExecuteAsync(
                    loginDTO,
                    sql,
                    new { DeclarationId = declarationId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Error while deleting Declaration detail for ID {declarationId}",
                    ex
                );
            }
        }
        public async Task<int> DeleteDeclaration(int declarationId, LoginDTO loginDTO)
        {
            try
            {
                string sql;
                switch (loginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        sql = DeclarationQB.DELETE_DECLARATION_SQL;
                        break;
                    case DBType.PostGre:
                        sql = DeclarationQB.DELETE_DECLARATION_PG;
                        break;
                    case DBType.Oracle:
                        sql = DeclarationQB.DELETE_DECLARATION_ORACLE;
                        break;
                    case DBType.MySQL:
                        sql = DeclarationQB.DELETE_DECLARATION_MYSQL;
                        break;
                    default:
                        throw new Exception("Unsupported database type");
                }

                return await _Queryexecutor.ExecuteAsync(loginDTO, sql, new { DeclarationId = declarationId });
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Error while deleting Declaration with ID {declarationId}",
                    ex
                );
            }
        }
        public async Task<string> DeleteDetailDeclaration(int DeclarationDetailId, LoginDTO LoginDTO)
        {
            try
            {
                string sql;

                switch (LoginDTO.DatabaseType)
                {
                    case DBType.SQL:
                        sql = DeclarationQB.DELETEDECLARATIONDETAIL_SQL;
                        break;

                    case DBType.PostGre:
                        sql = DeclarationQB.DELETEDECLARATIONDETAIL_PG;
                        break;

                    case DBType.Oracle:
                        sql = DeclarationQB.DELETEDECLARATIONDETAIL_ORACLE;
                        break;

                    case DBType.MySQL:
                        sql = DeclarationQB.DELETEDECLARATIONDETAIL_MYSQL;
                        break;

                    default:
                        throw new Exception("Unsupported database type");
                }

                var parameters = new { DeclarationDetailId = DeclarationDetailId };
                int result = await _Queryexecutor.ExecuteAsync(LoginDTO, sql, parameters);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListDeclarationView(int first, int max, CriteriaDTO criteria, LoginDTO login)
        {
            try
            {
                string sql = login.DatabaseType == DBTYPE.POSTGRESQL
                    ? DeclarationQB.GET_SELECTLIST_DECLARATIONVIEW_PG
                    : DeclarationQB.GET_SELECTLIST_DECLARATIONVIEW_SQL;

                string criteriaClause = BuildCriteriaClause(criteria, login.DatabaseType, login.WorkPeriodId);
                sql = sql.Replace("{CRITERIA_PLACEHOLDER}", criteriaClause);

                var param = new { firstnumber = first, maxresult = max };

                var declarationLookup = new Dictionary<int, DeclarationDTO>();

                await _Queryexecutor.QueryMultiMapAsync<DeclarationDTO, DeclarationDetailDTO, DeclarationDTO>(
                    login,
                    sql,
                    (declaration, detail) =>
                    {
                        if (!declarationLookup.TryGetValue(declaration.DeclarationId, out var currentDeclaration))
                        {
                            currentDeclaration = declaration;
                            currentDeclaration.DeclarationDetailArray = new List<DeclarationDetailDTO>();
                            declarationLookup.Add(currentDeclaration.DeclarationId, currentDeclaration);
                        }

                        if (detail != null && detail.DeclarationDetailId != 0 &&
                            !currentDeclaration.DeclarationDetailArray.Any(d => d.DeclarationDetailId == detail.DeclarationDetailId))
                        {
                            currentDeclaration.DeclarationDetailArray.Add(detail);
                        }

                        return currentDeclaration;
                    },
                    param,
                    splitOn: "DeclarationDetailId"
                );

                var list = declarationLookup.Values.ToList();

                // If no declaration record found for this employee, return the employee row so
                // the UI can still display a blank declaration form for them.
                string empId = GetEmployeeIdFromCriteria(criteria);
                if (!string.IsNullOrEmpty(empId) && list.Count == 0)
                {
                    string fallbackSql = login.DatabaseType == DBTYPE.POSTGRESQL
                        ? DeclarationQB.GET_DECLARATION_FROM_EMPLOYEE_PG
                        : DeclarationQB.GET_DECLARATION_FROM_EMPLOYEE_SQL;

                    fallbackSql = string.Format(fallbackSql, login.WorkPeriodId);
                    fallbackSql = ApplyNewEmployeeCriteria(fallbackSql, empId, login.DatabaseType);

                    var fallbackList = await _Queryexecutor.QueryAsync<DeclarationDTO>(login, fallbackSql, null);
                    if (fallbackList != null)
                    {
                        list = fallbackList.ToList();
                        foreach (var item in list)
                            item.DeclarationDetailArray = new List<DeclarationDetailDTO>();
                    }
                }

                return JsonConvert.SerializeObject(list);
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Unable to load the declaration list. Please refresh and try again. " +
                    $"Technical detail: {ex.Message}", ex);
            }
        }
        private string BuildCriteriaClause(CriteriaDTO criteria, byte dbType, int periodId)
        {
            StringBuilder sb = new StringBuilder();

            if (dbType == DBTYPE.POSTGRESQL)
                sb.Append($" AND decl.periodid = {periodId}");
            else
                sb.Append($" AND DECL.PERIODID = {periodId}");

            if (criteria?.SectionCriteriaList == null ||
                !criteria.SectionCriteriaList.Any() ||
                criteria.SectionCriteriaList[0].AttributesCriteriaList == null)
                return sb.ToString();

            Dictionary<string, string> fieldMap = dbType == DBTYPE.POSTGRESQL
                ? new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
                {
            { "EmployeeId", "decl.employeeid" },
            { "Status", "decl.lockstatus" },
            { "dateofjoining", "emp.dateofjoining" }
                }
                : new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
                {
            { "EmployeeId", "DECL.EMPLOYEEID" },
            { "Status", "DECL.LOCKSTATUS" },
            { "dateofjoining", "EMP.DATEOFJOINING" }
                };

            foreach (var attr in criteria.SectionCriteriaList[0].AttributesCriteriaList)
            {
                if (!fieldMap.TryGetValue(attr.FieldName, out var column))
                    continue;

                string value = Convert.ToString(attr.FieldValue);
                if (string.IsNullOrWhiteSpace(value))
                    continue;

                if (attr.FieldName.Equals("dateofjoining", StringComparison.OrdinalIgnoreCase))
                {
                    string fmt = dbType == DBTYPE.POSTGRESQL ? "yyyy-MM-dd" : "yyyy/MM/dd";
                    string date = DateTimeOffset.FromUnixTimeMilliseconds(Convert.ToInt64(value))
                                                .ToString(fmt);
                    sb.Append($" AND {column} = '{date}'");
                    continue;
                }

                sb.Append(" AND ");

                switch (attr.OperationType)
                {
                    case CriteriaDTO.OperationType.Equal:
                        sb.Append($"{column} = {value}");
                        break;

                    case CriteriaDTO.OperationType.In:
                        sb.Append($"{column} IN ({value})");
                        break;

                    case CriteriaDTO.OperationType.NotIn:
                        sb.Append($"{column} NOT IN ({value})");
                        break;

                    case CriteriaDTO.OperationType.NotEqual:
                        sb.Append($"{column} <> {value}");
                        break;

                    case CriteriaDTO.OperationType.Like:
                        if (dbType == DBTYPE.POSTGRESQL)
                            sb.Append($"{column} ILIKE '%{value}%'");
                        else
                            sb.Append($"{column} LIKE '%{value}%'");
                        break;

                    default:
                        sb.Append($"{column} = {value}");
                        break;
                }
            }

            return sb.ToString();
        }
        private string GetEmployeeIdFromCriteria(CriteriaDTO criteria)
        {
            if (criteria?.SectionCriteriaList?.Any() != true)
                return string.Empty;

            var empIdCriteria = criteria.SectionCriteriaList[0].AttributesCriteriaList
                ?.FirstOrDefault(c => c.FieldName.Equals("employeeid", StringComparison.OrdinalIgnoreCase));

            return empIdCriteria != null ? Convert.ToString(empIdCriteria.FieldValue) : string.Empty;
        }
        private string ApplyNewEmployeeCriteria(string sql, string empId, byte dbType)
        {
            sql += dbType == DBTYPE.POSTGRESQL
                ? $" AND emp.employeeid = {empId}"
                : $" AND EMP.EMPLOYEEID = {empId}";
            return sql;
        }

        public async Task<bool> DeclarationBulkUpdate(
        List<DeclarationDTO> declarationList,
        LoginDTO loginDTO)
        {
            if (declarationList == null || declarationList.Count == 0)
                return true;

            try
            {
                string sql = DeclarationQB.DECLARATIONBULKUPDATE;

                foreach (var item in declarationList)
                {
                    var parameters = new Dictionary<string, object>
                    {
                        { "@LockStatus", item.DeclarationLockStatus },
                        { "@DeclarationId", item.DeclarationId }
                    };

                    await _Queryexecutor.ExecuteAsync(loginDTO, sql, parameters);
                }

                return true;
            }
            catch
            {
                throw;
            }
        }

        public async Task<bool> DeclarationDetailBulkUpdate(
            List<DeclarationDetailDTO> declarationDetailList,
            LoginDTO loginDTO,
            CancellationToken ct)
        {
            if (declarationDetailList == null || declarationDetailList.Count == 0)
                return true;

            string sql = loginDTO.DatabaseType == DBType.PostGre
                ? DeclarationQB.DECLARATIONDETAILBULKUPDATE_PG
                : DeclarationQB.DECLARATIONDETAILBULKUPDATE_SQL;

            var Trans = await _Queryexecutor.BeginTransactionAsync(loginDTO);
            try
            {
                foreach (var item in declarationDetailList)
                {
                    await _Queryexecutor.ExecuteAsync(
                        loginDTO,
                        sql,
                        new
                        {
                            item.DeclarationDetailDetailStatus,
                            item.DeclarationDetailFeedback,
                            item.DeclarationDetailApprovedAmount,
                            ApproverId = loginDTO.UserId,
                            item.DeclarationDetailId
                        },
                        Trans,
                        ct);
                }

                await _Queryexecutor.CommitAsync(Trans);
                return true;
            }
            catch
            {
                await _Queryexecutor.RollbackAsync(Trans);
                throw;
            }
        }
    }
}