using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using MMDAL.DTO.MTR;
using MMDAL.Query.MTR;
using Newtonsoft.Json;

namespace MMDAL.CustomCode.MTR
{
    public class MtrDAL : IMtrDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public MtrDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        // ══════════════════════════════════════════════════════════════════
        // GET — full report: header + lots + sources + result + preference
        // ══════════════════════════════════════════════════════════════════

        public async Task<MtrReportDTO?> GetMtr(int mtrId, LoginDTO loginDTO, CancellationToken ct)
        {
            var param = new { MtrId = mtrId, TenantId = loginDTO.ClientId };

            // 1. Header — includes PartyName and ItemName
            var header = await _QueryExecutor.QuerySingleAsync<MtrReportDTO>(
                loginDTO, MtrQB.GET_MTR, param).ConfigureAwait(false);

            if (header == null)
                return null;

            // 2. Lots — joined to THEAT for HeatNumber and HeatDate
            var lots = (await _QueryExecutor.QueryAsync<MtrLotDTO>(
                loginDTO, MtrQB.GET_MTR_LOTS, param).ConfigureAwait(false)).ToList();

            // 3. Sources — single query for all lots; assembled below
            var sources = (await _QueryExecutor.QueryAsync<MtrSourceDTO>(
                loginDTO, MtrQB.GET_MTR_SOURCES_BY_MTR, param).ConfigureAwait(false)).ToList();

            // Assemble sources into their parent lots
            var sourcesByLot = sources.ToLookup(s => s.MtrLotId);
            foreach (var lot in lots)
                lot.Sources = sourcesByLot[lot.MtrLotId].ToList();

            header.Lots = lots;


            // 4. Section 5 — signatories
            header.Result = (await _QueryExecutor.QueryAsync<MtrResultDTO>(
                loginDTO, MtrQB.GET_MTR_RESULT, param).ConfigureAwait(false)).ToList();

            // 5. Section 4 — client preference
            var prefParam = new { header.PartyId, header.ItemId, TenantId = loginDTO.ClientId };
            header.ClientPreference = (await _QueryExecutor.QueryAsync<MtrClientPreferenceDTO>(
                loginDTO, MtrQB.GET_MTR_CLIENT_PREF, prefParam).ConfigureAwait(false)).ToList();

            return header;
        }

        // ══════════════════════════════════════════════════════════════════
        // LIST — paginated summary grid
        // ══════════════════════════════════════════════════════════════════

        public async Task<PagedResult<MtrListDTO>> GetMtrList(
            string? searchText, int partyId, int itemId, byte reportStatus,
            int pageOffset, int pageSize,
            LoginDTO loginDTO, CancellationToken ct)
        {
            var param = new
            {
                SearchText   = string.IsNullOrWhiteSpace(searchText) ? null : searchText,
                PartyId      = partyId,
                ItemId       = itemId,
                ReportStatus = reportStatus,
                PageOffset   = pageOffset,
                PageSize     = pageSize,
                TenantId     = loginDTO.ClientId
            };

            var rows  = await _QueryExecutor.QueryAsync<MtrListDTO>(
                loginDTO, MtrQB.GET_MTR_LIST, param).ConfigureAwait(false);
            int total = await _QueryExecutor.ExecuteScalarAsync<int>(
                loginDTO, MtrQB.GET_MTR_LIST_COUNT, param).ConfigureAwait(false);

            return new PagedResult<MtrListDTO> { Items = rows, TotalCount = total };
        }

        // ══════════════════════════════════════════════════════════════════
        // SELECT-LIST — lightweight picklist for dropdowns
        // ══════════════════════════════════════════════════════════════════

        public async Task<string> GetSelectListMtr(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            var param  = new { TenantId = loginDTO.ClientId };
            var result = await _QueryExecutor.QueryAsync<MtrPicklistDTO>(
                loginDTO, MtrQB.GET_SELECTLIST_MTR, param).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // ══════════════════════════════════════════════════════════════════
        // SAVE — insert header → lots → sources → result → preference
        // Transaction is opened and committed by BLL.
        // ══════════════════════════════════════════════════════════════════

        public async Task<Result<string>> SaveMtr(
            MtrReportDTO mtrDTO, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            var now          = DateTime.UtcNow;
            var createdById  = loginDTO.UserId;
            var tenantId     = loginDTO.ClientId;

            // 1. Header
            int rows = await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.SAVE_MTR, new
            {
                mtrDTO.MtrId,
                mtrDTO.MtrNo,
                mtrDTO.MtrDate,
                mtrDTO.DocumentId,
                mtrDTO.DocumentDetailId,
                mtrDTO.IndentId,
                mtrDTO.PartyId,
                mtrDTO.PartyBranchId,
                mtrDTO.PoNumber,
                mtrDTO.PoDate,
                mtrDTO.ReferenceNumber,
                mtrDTO.ReferenceDate,
                mtrDTO.ItemId,
                mtrDTO.SkuId,
                mtrDTO.TotalQty,
                mtrDTO.UomId,
                mtrDTO.MtrTypeId,
                mtrDTO.OverallResult,
                mtrDTO.Remarks,
                mtrDTO.ReportStatus,
                mtrDTO.ParentMtrId,
                mtrDTO.ParentMtrNo,
                mtrDTO.MaterialSpec,
                mtrDTO.IssuedById,
                mtrDTO.IssuedOn,
                CreatedById  = createdById,
                CreatedOn    = now,
                ModifiedById = createdById,
                ModifiedOn   = now,
                TenantId     = tenantId
            }, tx).ConfigureAwait(false);

            if (rows <= 0)
                return Result<string>.Failure("Failed to save MTR header.");

            // 2. Lots and sources
            await InsertLotsAndSourcesAsync(mtrDTO.Lots, createdById, now, tenantId, loginDTO, tx)
                .ConfigureAwait(false);

            // 3. Result / signatories (optional — may not be submitted yet)
            var resultRow = mtrDTO.Result.FirstOrDefault();
            if (resultRow != null)
                await InsertResultAsync(resultRow, mtrDTO.MtrId, createdById, now, tenantId, loginDTO, tx)
                    .ConfigureAwait(false);

            // 4. Client preference (optional — upsert: insert or update)
            var prefRow = mtrDTO.ClientPreference.FirstOrDefault();
            if (prefRow != null)
                await UpsertClientPrefAsync(prefRow, createdById, now, tenantId, loginDTO, tx)
                    .ConfigureAwait(false);

            return Result<string>.Success("MTR saved successfully.");
        }

        // ══════════════════════════════════════════════════════════════════
        // UPDATE — update header, replace children, upsert result/preference
        // ══════════════════════════════════════════════════════════════════

        public async Task<Result<string>> UpdateMtr(
            MtrReportDTO mtrDTO, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            var now          = DateTime.UtcNow;
            var modifiedById = loginDTO.UserId;
            var tenantId     = loginDTO.ClientId;
            var baseParam    = new { mtrDTO.MtrId, TenantId = tenantId };

            // 1. Header
            int rows = await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.UPDATE_MTR, new
            {
                mtrDTO.MtrId,
                mtrDTO.MtrNo,
                mtrDTO.MtrDate,
                mtrDTO.DocumentId,
                mtrDTO.DocumentDetailId,
                mtrDTO.IndentId,
                mtrDTO.PartyId,
                mtrDTO.PartyBranchId,
                mtrDTO.PoNumber,
                mtrDTO.PoDate,
                mtrDTO.ReferenceNumber,
                mtrDTO.ReferenceDate,
                mtrDTO.ItemId,
                mtrDTO.SkuId,
                mtrDTO.TotalQty,
                mtrDTO.UomId,
                mtrDTO.MtrTypeId,
                mtrDTO.OverallResult,
                mtrDTO.Remarks,
                mtrDTO.ReportStatus,
                mtrDTO.ParentMtrId,
                mtrDTO.ParentMtrNo,
                mtrDTO.MaterialSpec,
                mtrDTO.IssuedById,
                mtrDTO.IssuedOn,
                ModifiedById = modifiedById,
                ModifiedOn   = now,
                TenantId     = tenantId
            }, tx).ConfigureAwait(false);

            if (rows <= 0)
                return Result<string>.Failure("MTR not found or no changes made.");

            // 2. Replace child records — delete sources first (FK constraint)
            await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.DELETE_SOURCES_BY_MTR, baseParam, tx)
                .ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.DELETE_LOTS_BY_MTR, baseParam, tx)
                .ConfigureAwait(false);

            await InsertLotsAndSourcesAsync(mtrDTO.Lots, modifiedById, now, tenantId, loginDTO, tx)
                .ConfigureAwait(false);

            // 3. Result — upsert (update if exists, insert if not)
            var updResultRow = mtrDTO.Result.FirstOrDefault();
            if (updResultRow != null)
            {
                var existing = await _QueryExecutor.QuerySingleAsync<MtrResultDTO>(
                    loginDTO, MtrQB.GET_MTR_RESULT,
                    new { mtrDTO.MtrId, TenantId = tenantId }).ConfigureAwait(false);

                if (existing != null)
                    await UpdateResultAsync(updResultRow, mtrDTO.MtrId, modifiedById, now, tenantId, loginDTO, tx)
                        .ConfigureAwait(false);
                else
                    await InsertResultAsync(updResultRow, mtrDTO.MtrId, modifiedById, now, tenantId, loginDTO, tx)
                        .ConfigureAwait(false);
            }

            // 4. Client preference — always upsert
            var updPrefRow = mtrDTO.ClientPreference.FirstOrDefault();
            if (updPrefRow != null)
                await UpsertClientPrefAsync(updPrefRow, modifiedById, now, tenantId, loginDTO, tx)
                    .ConfigureAwait(false);

            return Result<string>.Success("MTR updated successfully.");
        }

        // ══════════════════════════════════════════════════════════════════
        // DELETE — cascade: sources → lots → result → header
        // Client preference is intentionally NOT deleted (it is a standing
        // preference for the party, independent of individual MTRs).
        // ══════════════════════════════════════════════════════════════════

        public async Task<Result<string>> DeleteMtr(
            int mtrId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            var param = new { MtrId = mtrId, TenantId = loginDTO.ClientId };

            await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.DELETE_SOURCES_BY_MTR, param, tx)
                .ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.DELETE_LOTS_BY_MTR, param, tx)
                .ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.DELETE_MTR_RESULT, param, tx)
                .ConfigureAwait(false);

            int rows = await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.DELETE_MTR, param, tx)
                .ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("MTR deleted successfully.")
                : Result<string>.Failure("MTR not found or already deleted.");
        }

        // ══════════════════════════════════════════════════════════════════
        // Private helpers
        // ══════════════════════════════════════════════════════════════════

        private async Task InsertLotsAndSourcesAsync(
            List<MtrLotDTO> lots,
            int userId, DateTime now, int tenantId,
            LoginDTO loginDTO, DbTransaction tx)
        {
            foreach (var lot in lots)
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.SAVE_LOT, new
                {
                    lot.MtrLotId,
                    lot.MtrId,
                    lot.SlNo,
                    lot.HeatId,
                    lot.LotId,
                    lot.LotReference,
                    lot.Quantity,
                    lot.UomId,
                    lot.MaterialSpec,
                    lot.LotResult,
                    CreatedById  = userId,
                    CreatedOn    = now,
                    ModifiedById = userId,
                    ModifiedOn   = now,
                    TenantId     = tenantId
                }, tx).ConfigureAwait(false);

                foreach (var src in lot.Sources)
                {
                    await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.SAVE_SOURCE, new
                    {
                        src.MtrSourceId,
                        src.MtrLotId,
                        src.SourceTypeId,
                        src.SourceRecordId,
                        src.SummaryText,
                        src.DisplayOrder,
                        src.IncludeInPdf,
                        src.DetailLevel,
                        CreatedById  = userId,
                        CreatedOn    = now,
                        ModifiedById = userId,
                        ModifiedOn   = now,
                        TenantId     = tenantId
                    }, tx).ConfigureAwait(false);
                }
            }
        }

        private async Task InsertResultAsync(
            MtrResultDTO result, int mtrId,
            int userId, DateTime now, int tenantId,
            LoginDTO loginDTO, DbTransaction tx)
        {
            result.MtrId = mtrId;
            await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.SAVE_MTR_RESULT, new
            {
                result.MtrResultId,
                result.MtrId,
                result.PreparedById,
                result.PreparedOn,
                result.ReviewedById,
                result.ReviewedOn,
                result.ApprovedById,
                result.ApprovedOn,
                result.OverallResult,
                result.Remarks,
                CreatedById  = userId,
                CreatedOn    = now,
                ModifiedById = userId,
                ModifiedOn   = now,
                TenantId     = tenantId
            }, tx).ConfigureAwait(false);
        }

        private async Task UpdateResultAsync(
            MtrResultDTO result, int mtrId,
            int userId, DateTime now, int tenantId,
            LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.UPDATE_MTR_RESULT, new
            {
                MtrId        = mtrId,
                result.PreparedById,
                result.PreparedOn,
                result.ReviewedById,
                result.ReviewedOn,
                result.ApprovedById,
                result.ApprovedOn,
                result.OverallResult,
                result.Remarks,
                ModifiedById = userId,
                ModifiedOn   = now,
                TenantId     = tenantId
            }, tx).ConfigureAwait(false);
        }

        private async Task UpsertClientPrefAsync(
            MtrClientPreferenceDTO pref,
            int userId, DateTime now, int tenantId,
            LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, MtrQB.UPSERT_MTR_CLIENT_PREF, new
            {
                pref.ClientPreferenceId,
                pref.PartyId,
                pref.ItemId,
                pref.MtrFormatId,
                pref.PreferredMtrTypeId,
                pref.ReportLanguage,
                pref.ShowChemical,
                pref.ShowMechanical,
                pref.ShowDimensional,
                pref.ShowNdt,
                pref.ShowVisual,
                pref.ShowSignatureBlock,
                pref.NdtDetailLevel,
                pref.ChemColumns,
                pref.MechColumns,
                pref.ShowLotHeatDetail,
                pref.RequireCustomerSignoff,
                pref.CustomFooterText,
                pref.MtrNoNumberingPattern,
                CreatedById  = userId,
                CreatedOn    = now,
                ModifiedById = userId,
                ModifiedOn   = now,
                TenantId     = tenantId
            }, tx).ConfigureAwait(false);
        }
    }
}