using System.Text.Json;
using AnalyticsDAL.DTO.AnalysisQuery;
using AnalyticsDAL.Query.AnalysisQuery;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;
using System.Data.Common;

namespace AnalyticsDAL.CustomCode.AnalysisQuery
{
    public class AnalysisQueryDAL : IAnalysisQueryDAL
    {
        private readonly IQueryExecutor          _queryExecutor;
        private readonly ILogger<AnalysisQueryDAL> _logger;

        public AnalysisQueryDAL(IQueryExecutor queryExecutor, ILogger<AnalysisQueryDAL> logger)
        {
            _queryExecutor = queryExecutor;
            _logger        = logger;
        }

        public async Task<string> GetAnalysisQuery(int analysisQueryId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var header = await _queryExecutor.QuerySingleAsync<AnalysisQueryDTO>(
                    login,
                    AnalysisQueryQB.GET_ANALYSISQUERY_DETAIL,
                    new { analysisqueryid = analysisQueryId, TenantId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);

                var fields = (await _queryExecutor.QueryAsync<AnalysisQueryFieldsDTO>(
                    login,
                    AnalysisQueryQB.GET_ANALYSISQUERY_FIELDS_DETAIL,
                    new { analysisqueryid = analysisQueryId, TenantId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false)).ToList();

                return JsonSerializer.Serialize(new { Header = header, Fields = fields });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "GetAnalysisQuery failed for AnalysisQueryId {Id}", analysisQueryId);
                throw;
            }
        }

        public async Task<string> GetSelectListAnalysisQuery(int analysisId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var list = await _queryExecutor.QueryAsync<AnalysisQueryPicklistDTO>(
                    login,
                    AnalysisQueryQB.GET_SELECTLIST_ANALYSISQUERY,
                    new { analysisid = analysisId, TenantId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);

                return JsonSerializer.Serialize(list);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "GetSelectListAnalysisQuery failed for AnalysisId {Id}", analysisId);
                throw;
            }
        }

        public async Task<string> GetAnalysisFieldsForDesigner(int analysisId, int analysisQueryId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var fields = await _queryExecutor.QueryAsync<AnalysisQueryFieldsDTO>(
                    login,
                    AnalysisQueryQB.GET_ANALYSISFIELDS_FOR_DESIGNER,
                    new { analysisid = analysisId, AnalysisQueryId = analysisQueryId },
                    cancellationToken: ct).ConfigureAwait(false);

                return JsonSerializer.Serialize(fields);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "GetAnalysisFieldsForDesigner failed for AnalysisId {Id}", analysisId);
                throw;
            }
        }

        public async Task<string> SaveAnalysisQuery(AnalysisQueryDTO dto, LoginDTO login, bool isNew, CancellationToken ct, DbTransaction tx = null)
        {
            // When an external transaction is supplied, the caller owns its lifecycle
            // (begin/commit/rollback/dispose) — we only participate in it. When none is
            // supplied, preserve this method's original self-managed transaction behavior
            // exactly (begin here, commit/rollback/dispose here) for existing callers.
            bool ownsTransaction = tx == null;
            DbTransaction activeTx = tx ?? await _queryExecutor.BeginTransactionAsync(login);

            try
            {
                if (isNew)
                {
                    // INSERT
                    await _queryExecutor.ExecuteAsync(
                        login,
                        AnalysisQueryQB.SAVE_ANALYSISQUERY,
                        new
                        {
                            dto.AnalysisQueryId,
                            dto.AnalysisId,
                            dto.AnalysisQueryQueryName,
                            dto.AnalysisQueryQueryType,
                            AnalysisQueryQuery = dto.AnalysisQueryQuery ?? "",
                            dto.AnalysisQueryVersion,
                            dto.AnalysisQueryStatus,
                            dto.AnalysisQuerySourceType,
                            dto.AnalysisQuerySortOrder,
                            AnalysisQueryCreatedById = login.UserId,
                            AnalysisQueryModifiedById = login.UserId,
                            AnalysisQueryOrderBy = dto.AnalysisQueryOrderBy ?? "",
                            AnalysisQueryTenantId = login.ClientId,
                            dto.AnalysisQueryWorkspaceId
                        },
                        activeTx,
                        ct);
                }
                else
                {
                    // UPDATE
                    await _queryExecutor.ExecuteAsync(
                        login,
                        AnalysisQueryQB.UPDATE_ANALYSISQUERY,
                        new
                        {
                            dto.AnalysisQueryId,
                            dto.AnalysisQueryQueryName,
                            dto.AnalysisQueryQueryType,
                            AnalysisQueryQuery = dto.AnalysisQueryQuery ?? "",
                            AnalysisQueryOrderBy = dto.AnalysisQueryOrderBy ?? "",
                            dto.AnalysisQueryVersion,
                            dto.AnalysisQuerySortOrder,
                            dto.AnalysisQueryStatus,
                            AnalysisQueryModifiedById = login.UserId
                        },
                        activeTx,
                        ct);

                    await _queryExecutor.ExecuteAsync(
                        login,
                        AnalysisQueryQB.DELETE_ANALYSISQUERY_FIELDS,
                        new { analysisqueryid = dto.AnalysisQueryId },
                        activeTx,
                        ct);
                }

                if (dto.AnalysisQueryFieldDTOs != null)
                {
                    var fieldParams = dto.AnalysisQueryFieldDTOs
                        .Select((field, i) => new
                        {
                            field.AnalysisQueryFieldsId,
                            AnalysisQueryId = dto.AnalysisQueryId,
                            AnalysisQueryFieldsSlNo = (short)(i + 1),
                            field.AnalysisQueryFieldsDisplaySlNo,
                            field.AnalysisQueryFieldsDisplayType,
                            AnalysisQueryFieldsFilterCondition = field.AnalysisQueryFieldsFilterCondition ?? "",
                            field.AnalysisQueryFieldsAnalysisFieldId,
                            field.AnalysisQueryFieldsIsFilter,
                            field.CriteriaAttributeId,
                            AnalysisQueryFieldsDisplayName = field.AnalysisQueryFieldsDisplayName ?? "",
                            field.AnalysisQueryFieldsAggregationType,
                            field.AnalysisQueryFieldsPeriodtype
                        })
                        .ToList();

                    await _queryExecutor.BulkInsertAsync(login, AnalysisQueryQB.SAVE_ANALYSISQUERYFIELD, fieldParams, activeTx)
                        .ConfigureAwait(false);
                }

                if (ownsTransaction)
                {
                    await activeTx.CommitAsync(ct);
                }

                return dto.AnalysisQueryId.ToString();
            }
            catch
            {
                if (ownsTransaction)
                {
                    await activeTx.RollbackAsync(ct);
                }
                throw;
            }
            finally
            {
                if (ownsTransaction)
                {
                    await activeTx.DisposeAsync();
                }
            }
        }

        public async Task<string> DeleteAnalysisQuery(int analysisQueryId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                await _queryExecutor.ExecuteAsync(
                    login,
                    AnalysisQueryQB.DELETE_ANALYSISQUERY,
                    new { AnalysisQueryId = analysisQueryId, ModifiedById = login.UserId, TenantId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);

                return analysisQueryId.ToString();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "DeleteAnalysisQuery failed for AnalysisQueryId {Id}", analysisQueryId);
                throw;
            }
        }

        public async Task<AnalysisQueryTypeInfoDTO?> GetAnalysisQueryTypeInfo(int analysisQueryId, LoginDTO login, CancellationToken ct)
        {
            return await _queryExecutor.QuerySingleAsync<AnalysisQueryTypeInfoDTO>(
                login,
                AnalysisQueryQB.GET_ANALYSISQUERY_TYPE_AND_ANALYSISID,
                new { AnalysisQueryId = analysisQueryId },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task<List<AnalysisQueryFieldPickerDTO>> GetBIDimensionsForQuery(int analysisQueryId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<AnalysisQueryFieldPickerDTO>(
                login,
                AnalysisQueryQB.GET_BI_DIMENSIONS_FOR_QUERY,
                new { AnalysisQueryId = analysisQueryId },
                cancellationToken: ct).ConfigureAwait(false);

            return rows.ToList();
        }

        public async Task<List<AnalysisQueryFieldPickerDTO>> GetBIMeasuresForQuery(int analysisQueryId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<AnalysisQueryFieldPickerDTO>(
                login,
                AnalysisQueryQB.GET_BI_MEASURES_FOR_QUERY,
                new { AnalysisQueryId = analysisQueryId },
                cancellationToken: ct).ConfigureAwait(false);

            return rows.ToList();
        }
    }
}