using GB5Shared.Query.Framework.DBObject;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.DBObject;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Microsoft.VisualBasic;
using Newtonsoft.Json;

namespace GB5Shared.DBObject
{
    public class DBObjectDAL : IDBObjectDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IValidation _Validation;

        public DBObjectDAL(IQueryExecutor queryExecutor, IValidation IValidation)
        {
            _queryExecutor = queryExecutor;
            _Validation = IValidation;
        }

        public async Task<string> GetDBObject(int dbObjectId, LoginDTO loginDTO)
        {
            try
            {
                var sql = DBObjectQB.GET_DBOBJECT;
                var parameters = new { dbobjectid = dbObjectId };

                var dbObjectDict = new Dictionary<int, DBObjectDTO>();

                var result = await _queryExecutor.QueryMultiMapAsync
                    <DBObjectDTO, DBObjectFieldsDTO, DBObjectDTO>(
                    loginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!dbObjectDict.TryGetValue(parent.DBObjectId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.DBObjectFields = new List<DBObjectFieldsDTO>();
                            dbObjectDict[parent.DBObjectId] = existingParent;
                        }

                        if (child != null && child.DBObjectFieldsId != 0)
                        {
                            existingParent.DBObjectFields.Add(child);
                        }

                        return existingParent;
                    },
                    parameters,
                    splitOn: "DBObjectFieldsId"   // ⚠ Must match SQL alias
                );

                var finalResult = dbObjectDict.Values.FirstOrDefault();

                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve DBObject data: {ex.Message}", ex);
            }
        }
        public async Task<string> GetSelectListDBObjectFields(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = DBObjectQB.GET_SELECTLIST_DBOBJECT_FIELDS;
                var Result = await _queryExecutor.QueryAsync<DBObjectPicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveDBObject(DBObjectDTO dto, LoginDTO login)
        {
            var tx = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                await _queryExecutor.ExecuteAsync(login, DBObjectQB.SAVE_DBOBJECT, dto, tx);
                await SaveChildFieldsAsync(dto, login, tx);
                await _queryExecutor.CommitAsync(tx);
                return 1;
            }
            catch (Exception ex)
            {
                await _queryExecutor.RollbackAsync(tx);
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateDBObject(DBObjectDTO dto, LoginDTO login)
        {
            var tx = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                await _queryExecutor.ExecuteAsync(login, DBObjectQB.UPDATE_DBOBJECT, dto, tx);
                await SaveChildFieldsAsync(dto, login, tx);
                await _queryExecutor.CommitAsync(tx);
                return 1;
            }
            catch (Exception ex)
            {
                await _queryExecutor.RollbackAsync(tx);
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(error);
            }
        }

        private async Task SaveChildFieldsAsync(DBObjectDTO dto, LoginDTO login, System.Data.Common.DbTransaction tx)
        {
            await _queryExecutor.ExecuteAsync(login, DBObjectQB.DELETE_DBOBJECTFIELDS_BY_PARENT,
                new { DBObjectId = dto.DBObjectId }, tx);

            if (dto.DBObjectFields?.Count > 0)
            {
                foreach (var f in dto.DBObjectFields)
                    f.DBObjectId = dto.DBObjectId;

                await _queryExecutor.BulkInsertAsync(login, DBObjectQB.SAVE_DBOBJECTFIELDS, dto.DBObjectFields, tx);
            }
        }

        public async Task<string> DeleteDBObject(int DBObjectId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = DBObjectQB.DELETE_DBOBJECT;
                var Parameters = new { dbobjectid = DBObjectId };
                int result = await _queryExecutor.ExecuteAsync(LoginDTO, Sql, Parameters);

                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);
            }
        }
        public async Task<string> GetSelectListDBObject(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = DBObjectQB.GET_SELECTLIST_DBOBJECT;
                var Result = await _queryExecutor.QueryAsync<DBObjectPicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<DBObjectDTO> FindBySourceAndNameAsync(string dbObjectName, byte sourceDbObjectKind, int? sourceClientDbId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var sql = DBObjectQB.GET_DBOBJECT_BY_SOURCE_AND_NAME;
                var parameters = new
                {
                    DBObjectName = dbObjectName,
                    SourceDbObjectKind = sourceDbObjectKind,
                    SourceClientDbId = sourceClientDbId
                };

                return await _queryExecutor.QuerySingleAsync<DBObjectDTO>(login, sql, parameters, cancellationToken: ct);
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}