using System.Runtime.CompilerServices;
using AccountsDAL.DTO.AccountsReports;
using AccountsDAL.Query.AccountReports;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5CommonFunction;
using GB5Shared.QueryExecutor; 
using System.Text;
using System.Text.Json;

namespace AccountsDAL.CustomCode.AccountReports.DayBook
{
    public class DayBookReportDAL : IDayBookReportDAL
    {
        private readonly IQueryExecutor _qe;

        public DayBookReportDAL(IQueryExecutor queryExecutor)
        {
            _qe = queryExecutor;
        }

        public async Task<object> GetDayBooks(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var tx = await _qe.BeginTransactionAsync(loginDTO);
            try
            {
                // ── 1. Parse criteria ─────────────────────────────────────────
                var criteria = ParseCriteria(criteriaDTO, loginDTO);

                // ── 2. Build dynamic filter + Dapper parameters ───────────────
                var (dynamicFilter, parameters) = BuildParameters(criteria, loginDTO);

                // ── 3. If account filter is present, build opening balance temp table
                if (criteria.VoucherDetailAccountIds.Count > 0)
                {
                    string tempSql = DayBookReportsQB.CREATE_OPENING_BALANCE_TEMP
                        .Replace("{DynamicFilter}", string.Empty);   // no extra filter on opening balance

                    var tempParams = new DynamicParameters();
                    tempParams.Add("periodFromDate", criteria.PeriodFromDate);
                    tempParams.Add("periodToDate", criteria.PeriodToDate);
                    tempParams.Add("ouid", loginDTO.WorkOUId);
                    tempParams.Add("voucherDetailAccountIds", criteria.VoucherDetailAccountIds);

                    await _qe.ExecuteAsync(loginDTO, tempSql, tempParams, tx);
                }

                // ── 4. Choose query variant ───────────────────────────────────
                bool isConsolidated = criteria.DayBookType == 0;
                bool withAccount    = criteria.VoucherDetailAccountIds.Count > 0;

                string mainSql  = SelectMainQuery(isConsolidated, withAccount);
                string totalSql = SelectTotalQuery(isConsolidated, withAccount);

                mainSql  = mainSql.Replace("{DynamicFilter}", dynamicFilter);
                totalSql = totalSql.Replace("{DynamicFilter}", dynamicFilter);

                // ── 5. Execute with paging ────────────────────────────────────
                var rows = (await _qe.QueryAsync<AccountBooksDetailDTO>(loginDTO, mainSql, parameters, tx)).ToList();

                // Apply paging manually (consistent with GB4 FirstNumber/MaxResult)
                var pagedRows = (firstNumber > 0 && maxResult > 0)
                    ? rows.Skip(firstNumber - 1).Take(maxResult).ToList()
                    : rows;

                // ── 6. Fetch totals ───────────────────────────────────────────
                var totals = (await _qe.QueryAsync<AccountBooksDetailDTO>(loginDTO, totalSql, parameters, tx)).ToList();

                decimal totalDebit  = totals.FirstOrDefault()?.TotalDebit  ?? 0;
                decimal totalCredit = totals.FirstOrDefault()?.TotalCredit ?? 0;

                foreach (var row in pagedRows)
                {
                    row.TotalDebit  = totalDebit;
                    row.TotalCredit = totalCredit;
                    row.ISAccountSelect = withAccount;
                }

                await _qe.CommitAsync(tx);
                return pagedRows;
            }
            catch
            {
                await _qe.RollbackAsync(tx);
                throw;
            }
        }

        public async IAsyncEnumerable<AccountBooksDetailDTO> GetDayBooksStream(
            int firstNumber, int maxResult,
            CriteriaDTO criteriaDTO, LoginDTO loginDTO,
            [EnumeratorCancellation] CancellationToken ct = default)
        {
            var criteria        = ParseCriteria(criteriaDTO, loginDTO);
            var (filter, param) = BuildParameters(criteria, loginDTO);
            bool isConsolidated = criteria.DayBookType == 0;
            bool withAccount    = criteria.VoucherDetailAccountIds.Count > 0;

            string streamSql = SelectMainQuery(isConsolidated, withAccount)
                .Replace("{DynamicFilter}", filter);

            bool applyPaging = firstNumber > 0 && maxResult > 0;
            int skip = applyPaging ? firstNumber - 1 : 0;
            int take = applyPaging ? maxResult       : int.MaxValue;

            if (withAccount)
            {
                // Temp table is session-scoped; keep transaction open for the duration of the stream
                var tx = await _qe.BeginTransactionAsync(loginDTO);
                bool committed = false;
                try
                {
                    string tempSql = DayBookReportsQB.CREATE_OPENING_BALANCE_TEMP
                        .Replace("{DynamicFilter}", string.Empty);
                    var tempParams = new DynamicParameters();
                    tempParams.Add("periodFromDate",          criteria.PeriodFromDate);
                    tempParams.Add("periodToDate",            criteria.PeriodToDate);
                    tempParams.Add("ouid",                    loginDTO.WorkOUId);
                    tempParams.Add("voucherDetailAccountIds", criteria.VoucherDetailAccountIds);
                    await _qe.ExecuteAsync(loginDTO, tempSql, tempParams, tx);

                    // yield return is allowed in try/finally (no catch clause)
                    int index = 0;
                    await foreach (var row in _qe.QueryStreamAsync<AccountBooksDetailDTO>(
                        loginDTO, streamSql, param, ct, tx))
                    {
                        if (applyPaging)
                        {
                            if (index >= skip + take) break;
                            if (index++ < skip) continue;
                        }
                        else index++;
                        yield return row;
                    }

                    await _qe.CommitAsync(tx);
                    committed = true;
                }
                finally
                {
                    if (!committed) await _qe.RollbackAsync(tx);
                }
            }
            else
            {
                int index = 0;
                await foreach (var row in _qe.QueryStreamAsync<AccountBooksDetailDTO>(
                    loginDTO, streamSql, param, ct))
                {
                    if (applyPaging)
                    {
                        if (index >= skip + take) break;
                        if (index++ < skip) continue;
                    }
                    else index++;
                    yield return row;
                }
            }
        }

        // ── Helpers ───────────────────────────────────────────────────────────

        private static string SelectMainQuery(bool consolidated, bool withAccount) =>
            (consolidated, withAccount) switch
            {
                (false, false) => DayBookReportsQB.GET_DAYBOOK_NOCON_NOACC,
                (false, true)  => DayBookReportsQB.GET_DAYBOOK_NOCON_WITHACC,
                (true,  false) => DayBookReportsQB.GET_DAYBOOK_CON_NOACC,
                (true,  true)  => DayBookReportsQB.GET_DAYBOOK_CON_WITHACC,
            };

        private static string SelectTotalQuery(bool consolidated, bool withAccount) =>
            (consolidated, withAccount) switch
            {
                (false, false) => DayBookReportsQB.GET_DAYBOOK_NOCON_NOACC_TOTAL,
                (false, true)  => DayBookReportsQB.GET_DAYBOOK_NOCON_WITHACC_TOTAL,
                (true,  false) => DayBookReportsQB.GET_DAYBOOK_CON_NOACC_TOTAL,
                (true,  true)  => DayBookReportsQB.GET_DAYBOOK_CON_WITHACC_TOTAL,
            };

        private static (string filter, DynamicParameters parameters) BuildParameters(
            DayBookCriteria criteria, LoginDTO loginDTO)
        {
            var filter = new StringBuilder();
            var p = new DynamicParameters();

            p.Add("periodFromDate",  criteria.PeriodFromDate);
            p.Add("periodToDate",    criteria.PeriodToDate);
            p.Add("ouid",            loginDTO.WorkOUId);
            // -1 is the real "no finance book assigned" sentinel here (confirmed live on GB5DEMO —
            // same finding as AccountReportFilterBuilder's FinanceBookId fix), NOT 0. `> 0` never
            // matches any real FinanceBookId, since this system's surrogate IDs are large negative
            // numbers (e.g. BizTransactionTypeId -1499983521 below) — see feedback_voucherdetail_report_patterns memory.
            p.Add("financeBookId",   criteria.FinanceBookId != -1 ? criteria.FinanceBookId : loginDTO.WorkFinanceBookId);

            if (criteria.VoucherDetailAccountIds.Count > 0)
                p.Add("voucherDetailAccountIds", criteria.VoucherDetailAccountIds);

            // Optional filters (values always bound via parameters — never string interpolated)
            if (criteria.BizTransactionTypeId != 0)
            {
                filter.Append(" AND voucher.BizTransactionTypeId = @bizTransactionTypeId");
                p.Add("bizTransactionTypeId", criteria.BizTransactionTypeId);
            }

            if (criteria.BizTransactionClassId != 0)
            {
                filter.Append(" AND btt.BizTransactionClassId = @bizTransactionClassId");
                p.Add("bizTransactionClassId", criteria.BizTransactionClassId);
            }

            if (criteria.BizTransactionSubClassId != 0)
            {
                filter.Append(" AND btt.BizTransactionSubClassId = @bizTransactionSubClassId");
                p.Add("bizTransactionSubClassId", criteria.BizTransactionSubClassId);
            }

            // FromVoucherId/ToVoucherId are real surrogate VoucherIds (large negative on this system,
            // e.g. -1499999139 above) — `> 0` silently never matched, making this filter permanently
            // dead code regardless of what a caller sent. Fixed to `!= 0`, matching the same class of
            // bug found and fixed in AccountRegister/AccountLedger this session.
            if (criteria.FromVoucherId != 0)
            {
                filter.Append(" AND voucher.VoucherId >= @fromVoucherId");
                p.Add("fromVoucherId", criteria.FromVoucherId);
            }

            if (criteria.ToVoucherId != 0)
            {
                filter.Append(" AND voucher.VoucherId <= @toVoucherId");
                p.Add("toVoucherId", criteria.ToVoucherId);
            }

            // FromAmount/ToAmount are amount MAGNITUDES, not surrogate IDs — VoucherAmount is confirmed
            // live to be always >= 0 in this system (no negative amounts), so `> 0` as the "is this
            // filter set" check is a different, much lower-risk case than the ID fields above: a
            // FromAmount of exactly 0 is a no-op anyway (every real amount already satisfies ">= 0"),
            // and a ToAmount of exactly 0 (deliberately filtering for zero-value vouchers only) is a
            // real but extremely unlikely use case. Confirmed live that VoucherAmount CAN be exactly 0
            // (1 such row on GB5DEMO) — left as `> 0` rather than reworked to a nullable decimal (the
            // AccountRegister/AccountLedger pattern) since that's a larger, separate refactor not
            // required to close the ID-sign bug class this pass targets.
            if (criteria.FromAmount > 0)
            {
                filter.Append(" AND voucher.VoucherAmount >= @fromAmount");
                p.Add("fromAmount", criteria.FromAmount);
            }

            if (criteria.ToAmount > 0)
            {
                filter.Append(" AND voucher.VoucherAmount <= @toAmount");
                p.Add("toAmount", criteria.ToAmount);
            }

            if (criteria.ExcludeProvision)
            {
                filter.Append(" AND voucher.TypeStatus <> 0");   // 0 = provision entry
            }

            if (criteria.InfoClass != 0)
            {
                filter.Append(" AND voucher.InfoClass = @infoClass");
                p.Add("infoClass", criteria.InfoClass);
            }

            return (filter.ToString(), p);
        }

        private static DayBookCriteria ParseCriteria(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            var result = new DayBookCriteria
            {
                FinanceBookId = loginDTO.WorkFinanceBookId
            };

            if (criteriaDTO?.SectionCriteriaList == null)
                return result;

            IGB5CommonFunction fn = new GB5CommonFunction();

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section.AttributesCriteriaList == null) continue;

                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr.FieldValue == null) continue;
                    string field = attr.FieldName?.ToLower() ?? string.Empty;

                    switch (field)
                    {
                        case "periodfromdate":
                            result.PeriodFromDate = ParseEpochToDate(attr.FieldValue, fn);
                            break;

                        case "periodtodate":
                            result.PeriodToDate = ParseEpochToDate(attr.FieldValue, fn);
                            break;

                        case "voucherdetailaccountid":
                            result.VoucherDetailAccountIds = ParseIntList(attr.FieldValue);
                            break;

                        case "daybooktype":
                            result.DayBookType = ParseInt(attr.FieldValue);
                            break;

                        case "biztransactiontypeid":
                            result.BizTransactionTypeId = ParseInt(attr.FieldValue);
                            break;

                        case "biztransactionclassid":
                            result.BizTransactionClassId = ParseInt(attr.FieldValue);
                            break;

                        case "biztransactionsubclassid":
                            result.BizTransactionSubClassId = ParseInt(attr.FieldValue);
                            break;

                        case "fromvoucherid":
                            result.FromVoucherId = ParseInt(attr.FieldValue);
                            break;

                        case "tovoucherid":
                            result.ToVoucherId = ParseInt(attr.FieldValue);
                            break;

                        case "fromamount":
                            result.FromAmount = ParseDecimal(attr.FieldValue);
                            break;

                        case "toamount":
                            result.ToAmount = ParseDecimal(attr.FieldValue);
                            break;

                        case "provision":
                            // 1 = exclude provisions
                            result.ExcludeProvision = ParseInt(attr.FieldValue) == 1;
                            break;

                        case "infoclass":
                            result.InfoClass = ParseInt(attr.FieldValue);
                            break;

                        case "financebookid":
                            // -1 is the real "no book" sentinel (see BuildParameters below) and 0 is
                            // ParseInt's own "couldn't parse" fallback — neither should override the
                            // WorkFinanceBookId default. `> 0` silently discarded any real
                            // (negative-surrogate) FinanceBookId override on top of that.
                            int fbId = ParseInt(attr.FieldValue);
                            if (fbId != 0 && fbId != -1) result.FinanceBookId = fbId;
                            break;
                    }
                }
            }

            return result;
        }

        private static DateTime ParseEpochToDate(object fieldValue, IGB5CommonFunction fn)
        {
            string raw = fieldValue is JsonElement je
                ? (je.ValueKind == JsonValueKind.Number ? je.GetRawText() : je.GetString() ?? string.Empty)
                : fieldValue?.ToString() ?? string.Empty;

            if (long.TryParse(raw, out long epoch))
                return fn.FromEpoch(epoch).GetAwaiter().GetResult();

            if (DateTime.TryParse(raw, null, System.Globalization.DateTimeStyles.None, out DateTime dt))
                return dt;

            throw new FormatException($"Cannot parse '{raw}' as a date or epoch timestamp.");
        }

        private static List<int> ParseIntList(object fieldValue)
        {
            string raw = fieldValue is JsonElement je ? je.ToString() : fieldValue.ToString()!;

            if (string.IsNullOrWhiteSpace(raw)) return new List<int>();

            // Supports single value ("123") or comma-separated ("123,456")
            return raw.Split(',', StringSplitOptions.RemoveEmptyEntries)
                      .Select(s => int.TryParse(s.Trim(), out int v) ? v : 0)
                      .Where(v => v != 0)
                      .ToList();
        }

        private static int ParseInt(object fieldValue)
        {
            string raw = fieldValue is JsonElement je ? je.ToString() : fieldValue.ToString()!;
            return int.TryParse(raw, out int v) ? v : 0;
        }

        private static decimal ParseDecimal(object fieldValue)
        {
            string raw = fieldValue is JsonElement je ? je.ToString() : fieldValue.ToString()!;
            return decimal.TryParse(raw, out decimal v) ? v : 0;
        }

        // ── Internal criteria holder ──────────────────────────────────────────
        private sealed class DayBookCriteria
        {
            public DateTime PeriodFromDate          { get; set; } = DateTime.Today;
            public DateTime PeriodToDate            { get; set; } = DateTime.Today;
            public List<int> VoucherDetailAccountIds { get; set; } = new();
            public int DayBookType                  { get; set; } = 1;   // 1 = non-consolidated
            public int BizTransactionTypeId         { get; set; }
            public int BizTransactionClassId        { get; set; }
            public int BizTransactionSubClassId     { get; set; }
            public int FromVoucherId                { get; set; }
            public int ToVoucherId                  { get; set; }
            public decimal FromAmount               { get; set; }
            public decimal ToAmount                 { get; set; }
            public bool ExcludeProvision            { get; set; }
            public int InfoClass                    { get; set; }
            public int FinanceBookId                { get; set; }
        }
    }
}