using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using FrameworkDAL.DTO.MetaReport;
using FrameworkDAL.Query.MetaReport;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace FrameworkDAL.CustomCode.MetaReport
{
    public class MetaReportDAL : IMetaReportDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public MetaReportDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        public async Task<MetaReportDTO?> GetMetaReport(int metaReportId, LoginDTO loginDTO, CancellationToken ct)
            => await _queryExecutor.QuerySingleAsync<MetaReportDTO>(
                loginDTO, MetaReportQB.GET_METAREPORT, new { MetaReportId = metaReportId }, cancellationToken: ct)
                .ConfigureAwait(false);

        public async Task<List<MetaReportListItemDTO>> GetMetaReportList(LoginDTO loginDTO, CancellationToken ct)
            => (await _queryExecutor.QueryAsync<MetaReportListItemDTO>(
                loginDTO, MetaReportQB.GET_METAREPORT_LIST, cancellationToken: ct)
                .ConfigureAwait(false)).ToList();

        public async Task<List<MetaReportVariantDTO>> GetVariantsByMetaReportId(int metaReportId, LoginDTO loginDTO, CancellationToken ct)
            => (await _queryExecutor.QueryAsync<MetaReportVariantDTO>(
                loginDTO, MetaReportQB.GET_VARIANTS_BY_METAREPORTID, new { MetaReportId = metaReportId }, cancellationToken: ct)
                .ConfigureAwait(false)).ToList();

        public async Task<List<MetaReportAspectDTO>> GetAspectsByMetaReportId(int metaReportId, LoginDTO loginDTO, CancellationToken ct)
            => (await _queryExecutor.QueryAsync<MetaReportAspectDTO>(
                loginDTO, MetaReportQB.GET_ASPECTS_BY_METAREPORTID, new { MetaReportId = metaReportId }, cancellationToken: ct)
                .ConfigureAwait(false)).ToList();

        public async Task<List<MetaReportAspectOptionDTO>> GetOptionsByAspectIds(IEnumerable<int> aspectIds, LoginDTO loginDTO, CancellationToken ct)
        {
            var ids = aspectIds.ToList();
            if (ids.Count == 0) return new List<MetaReportAspectOptionDTO>();

            return (await _queryExecutor.QueryAsync<MetaReportAspectOptionDTO>(
                loginDTO, MetaReportQB.GET_OPTIONS_BY_ASPECTIDS, new { AspectIds = ids }, cancellationToken: ct)
                .ConfigureAwait(false)).ToList();
        }

        public async Task<List<MetaReportOptionVariantLinkDTO>> GetApplicableVariantsByOptionIds(IEnumerable<int> optionIds, LoginDTO loginDTO, CancellationToken ct)
        {
            var ids = optionIds.ToList();
            if (ids.Count == 0) return new List<MetaReportOptionVariantLinkDTO>();

            return (await _queryExecutor.QueryAsync<MetaReportOptionVariantLinkDTO>(
                loginDTO, MetaReportQB.GET_APPLICABLEVARIANTS_BY_OPTIONIDS, new { OptionIds = ids }, cancellationToken: ct)
                .ConfigureAwait(false)).ToList();
        }

        /// <summary>Statement order is FK-dependency-safe by construction:
        ///  1. Upsert the MMETAREPORT row itself (everything else FKs to it).
        ///  2. Clear EVERY MMETAREPORTOPTIONVARIANT link touching this MetaReport's options —
        ///     unconditionally, regardless of survival — so no link row can block an option or
        ///     variant delete in steps 3/5, and so step 9's re-insert starts from a clean slate.
        ///  3. Delete options not in the surviving set (scoped to this MetaReport's aspects).
        ///  4. Delete aspects not in the surviving set — safe because step 3 already removed
        ///     every option belonging to any aspect that isn't being resent.
        ///  5. Delete variants not in the surviving set — safe because step 2 already removed
        ///     every link that could have referenced them.
        ///  6-8. Upsert surviving variants/aspects/options, insert new ones (BLL has already
        ///     assigned real ids to every "new" row before calling this).
        ///  9. Re-insert MMETAREPORTOPTIONVARIANT links from each option's final
        ///     ApplicableVariantIds, now that every option/variant id is real.</summary>
        public async Task SaveMetaReportDefinition(MetaReportDTO dto, bool isNewMetaReport, LoginDTO loginDTO, CancellationToken ct)
        {
            var statements = new List<(string sql, object param)>();

            statements.Add(isNewMetaReport
                ? (MetaReportQB.INSERT_METAREPORT, (object)dto)
                : (MetaReportQB.UPDATE_METAREPORT, (object)dto));

            statements.Add((MetaReportQB.DELETE_OPTIONVARIANTS_FOR_METAREPORT, new { dto.MetaReportId }));

            var survivingOptionIds = dto.Aspects.SelectMany(a => a.Options).Where(o => o.OptionId > 0).Select(o => o.OptionId).ToList();
            statements.Add(survivingOptionIds.Count > 0
                ? (MetaReportQB.DELETE_OPTIONS_NOT_IN, new { dto.MetaReportId, SurvivingIds = survivingOptionIds })
                : (MetaReportQB.DELETE_OPTIONS_ALL_FOR_METAREPORT, new { dto.MetaReportId }));

            var survivingAspectIds = dto.Aspects.Where(a => a.AspectId > 0).Select(a => a.AspectId).ToList();
            statements.Add(survivingAspectIds.Count > 0
                ? (MetaReportQB.DELETE_ASPECTS_NOT_IN, new { dto.MetaReportId, SurvivingIds = survivingAspectIds })
                : (MetaReportQB.DELETE_ASPECTS_ALL, new { dto.MetaReportId }));

            var survivingVariantIds = dto.Variants.Where(v => v.VariantId > 0).Select(v => v.VariantId).ToList();
            statements.Add(survivingVariantIds.Count > 0
                ? (MetaReportQB.DELETE_VARIANTS_NOT_IN, new { dto.MetaReportId, SurvivingIds = survivingVariantIds })
                : (MetaReportQB.DELETE_VARIANTS_ALL, new { dto.MetaReportId }));

            foreach (var variant in dto.Variants)
                statements.Add(survivingVariantIds.Contains(variant.VariantId)
                    ? (MetaReportQB.UPDATE_VARIANT, (object)variant)
                    : (MetaReportQB.INSERT_VARIANT, (object)variant));

            foreach (var aspect in dto.Aspects)
            {
                statements.Add(survivingAspectIds.Contains(aspect.AspectId)
                    ? (MetaReportQB.UPDATE_ASPECT, (object)aspect)
                    : (MetaReportQB.INSERT_ASPECT, (object)aspect));

                foreach (var option in aspect.Options)
                    statements.Add(survivingOptionIds.Contains(option.OptionId)
                        ? (MetaReportQB.UPDATE_OPTION, (object)option)
                        : (MetaReportQB.INSERT_OPTION, (object)option));
            }

            foreach (var aspect in dto.Aspects)
                foreach (var option in aspect.Options)
                    foreach (var variantId in option.ApplicableVariantIds)
                        statements.Add((MetaReportQB.INSERT_OPTIONVARIANT, new { option.OptionId, VariantId = variantId }));

            await _queryExecutor.ExecuteInTransactionAsync(loginDTO, statements).ConfigureAwait(false);
        }

        public async Task DeleteMetaReport(int metaReportId, LoginDTO loginDTO, CancellationToken ct)
        {
            var statements = new List<(string sql, object param)>
            {
                (MetaReportQB.DELETE_OPTIONVARIANTS_FOR_METAREPORT, new { MetaReportId = metaReportId }),
                (MetaReportQB.DELETE_OPTIONS_ALL_FOR_METAREPORT, new { MetaReportId = metaReportId }),
                (MetaReportQB.DELETE_ASPECTS_ALL, new { MetaReportId = metaReportId }),
                (MetaReportQB.DELETE_VARIANTS_ALL, new { MetaReportId = metaReportId }),
                (MetaReportQB.DELETE_METAREPORT, new { MetaReportId = metaReportId }),
            };
            await _queryExecutor.ExecuteInTransactionAsync(loginDTO, statements).ConfigureAwait(false);
        }
    }
}