using System;
using System.Collections.Generic;
using System.Linq;
using AccountsDAL.DTO.AccountsReports;

namespace AccountsDAL.CustomCode.AccountReports
{
    // Caller-supplied sort order for AccountRegister/AccountRegisterSummary — most GB4.7 reports left
    // sort order to a downstream printing program; since these are being built fresh in GB5, sort is
    // exposed as a first-class option instead. Field names are matched against a small allow-list of
    // real SQL expressions per report — never interpolate a caller-supplied string directly into
    // ORDER BY. Defaults match GB4.7's own hardcoded order, plus a Slno tiebreaker GB4.7 didn't have.
    public static class AccountReportSortBuilder
    {
        private static readonly Dictionary<string, string> DetailSortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["VoucherDate"] = "V.VOUCHERDATE",
            ["VoucherNumber"] = "V.VOUCHERNUMBER",
            ["VoucherAmount"] = "V.VOUCHERAMOUNT",
            ["BizTransactionTypeCode"] = "BTT.BIZTRANSACTIONTYPECODE",
            ["VoucherAccountCode"] = "ACC.ACCOUNTCODE",
            ["VoucherAccountName"] = "ACC.ACCOUNTNAME",
            ["Debit"] = "CASE WHEN VD.DETAILTYPE = 0 THEN VD.VOUCHERAMOUNT ELSE 0 END",
            ["Credit"] = "CASE WHEN VD.DETAILTYPE = 1 THEN VD.VOUCHERAMOUNT ELSE 0 END",
        };

        // GB4.7's own default: ORDER BY A.voucherdate, t.BIZTRANSACTIONTYPECODE, a.VoucherNumber
        private const string DetailDefaultOrderBy = "V.VOUCHERDATE, BTT.BIZTRANSACTIONTYPECODE, V.VOUCHERNUMBER, VD.SLNO";

        public static string BuildDetailOrderBy(List<AccountReportSortOptionDTO> sortBy)
        {
            var mapped = MapFields(sortBy, DetailSortFields);
            if (mapped.Count == 0) return DetailDefaultOrderBy;
            mapped.Add("VD.SLNO"); // stable tiebreaker, always present regardless of caller's sort choice
            return string.Join(", ", mapped);
        }

        private static readonly Dictionary<string, string> SummarySortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["AccountGroupName"] = "AG.ACCOUNTGROUPNAME",
            ["ControlAccountName"] = "CTRL.ACCOUNTNAME",
            ["AccountCode"] = "ACC.ACCOUNTCODE",
            ["AccountName"] = "ACC.ACCOUNTNAME",
            ["Debit"] = "SUM(CASE WHEN VD.DETAILTYPE = 0 THEN VD.VOUCHERAMOUNT ELSE 0 END)",
            ["Credit"] = "SUM(CASE WHEN VD.DETAILTYPE = 1 THEN VD.VOUCHERAMOUNT ELSE 0 END)",
        };

        // GB4.7's own default: ORDER BY accountgroup.ACCOUNTGROUPNAME, controlaccount.ACCOUNTNAME, account.ACCOUNTNAME
        private const string SummaryDefaultOrderBy = "AG.ACCOUNTGROUPNAME, CTRL.ACCOUNTNAME, ACC.ACCOUNTNAME";

        public static string BuildSummaryOrderBy(List<AccountReportSortOptionDTO> sortBy)
        {
            var mapped = MapFields(sortBy, SummarySortFields);
            return mapped.Count == 0 ? SummaryDefaultOrderBy : string.Join(", ", mapped);
        }

        private static readonly Dictionary<string, string> LedgerSortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["VoucherAccountCode"] = "ACC.ACCOUNTCODE",
            ["VoucherAccountName"] = "ACC.ACCOUNTNAME",
            ["VoucherDate"] = "V.VOUCHERDATE",
            ["VoucherNumber"] = "V.VOUCHERNUMBER",
            ["Debit"] = "CASE WHEN VD.DETAILTYPE = 0 THEN VD.VOUCHERAMOUNT ELSE 0 END",
            ["Credit"] = "CASE WHEN VD.DETAILTYPE = 1 THEN VD.VOUCHERAMOUNT ELSE 0 END",
        };

        // A ledger is inherently chronological per account — default groups by account first (so the
        // running balance reads naturally top-to-bottom within each account), then by date/Slno.
        private const string LedgerDefaultOrderBy = "ACC.ACCOUNTCODE, V.VOUCHERDATE, VD.SLNO";

        public static string BuildLedgerOrderBy(List<AccountReportSortOptionDTO> sortBy)
        {
            var mapped = MapFields(sortBy, LedgerSortFields);
            if (mapped.Count == 0) return LedgerDefaultOrderBy;
            mapped.Add("VD.SLNO");
            return string.Join(", ", mapped);
        }

        private static readonly Dictionary<string, string> LedgerSummarySortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["AccountGroupName"] = "AG.ACCOUNTGROUPNAME",
            ["ControlAccountName"] = "CTRL.ACCOUNTNAME",
            ["AccountCode"] = "ACC.ACCOUNTCODE",
            ["AccountName"] = "ACC.ACCOUNTNAME",
            ["OpeningBalance"] = "OpeningBalance",
            ["ClosingBalance"] = "ClosingBalance",
        };

        private const string LedgerSummaryDefaultOrderBy = "AG.ACCOUNTGROUPNAME, CTRL.ACCOUNTNAME, ACC.ACCOUNTNAME";

        public static string BuildLedgerSummaryOrderBy(List<AccountReportSortOptionDTO> sortBy)
        {
            var mapped = MapFields(sortBy, LedgerSummarySortFields);
            return mapped.Count == 0 ? LedgerSummaryDefaultOrderBy : string.Join(", ", mapped);
        }

        private static readonly Dictionary<string, string> OutstandingDetailSortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["AccountCode"] = "ACC.ACCOUNTCODE",
            ["AccountName"] = "ACC.ACCOUNTNAME",
            ["DetailDate"] = "VBILL.DETAILDATE",
            ["DueDate"] = "VBILL.DUEDATE",
            ["Balance"] = "PB.BALANCE",
        };

        private const string OutstandingDetailDefaultOrderBy = "ACC.ACCOUNTCODE, VBILL.DETAILDATE";

        public static string BuildOutstandingDetailOrderBy(List<AccountReportSortOptionDTO> sortBy)
        {
            var mapped = MapFields(sortBy, OutstandingDetailSortFields);
            return mapped.Count == 0 ? OutstandingDetailDefaultOrderBy : string.Join(", ", mapped);
        }

        private static readonly Dictionary<string, string> OutstandingSummarySortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["Receivable"] = "Receivable",
            ["Payable"] = "Payable",
            ["Balance"] = "Balance",
        };

        public static string BuildOutstandingSummaryOrderBy(List<AccountReportSortOptionDTO> sortBy, string groupByColumns)
        {
            var mapped = MapFields(sortBy, OutstandingSummarySortFields);
            return mapped.Count == 0 ? groupByColumns : string.Join(", ", mapped);
        }

        private static readonly Dictionary<string, string> AgeingDetailSortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["AccountCode"] = "ACC.ACCOUNTCODE",
            ["DetailDate"] = "VBILL.DETAILDATE",
        };

        private const string AgeingDetailDefaultOrderBy = "ACC.ACCOUNTCODE, VBILL.DETAILDATE";

        public static string BuildAgeingDetailOrderBy(List<AccountReportSortOptionDTO> sortBy)
        {
            var mapped = MapFields(sortBy, AgeingDetailSortFields);
            return mapped.Count == 0 ? AgeingDetailDefaultOrderBy : string.Join(", ", mapped);
        }

        public static string BuildAgeingSummaryOrderBy(List<AccountReportSortOptionDTO> sortBy, string groupByColumns)
            => BuildOutstandingSummaryOrderBy(sortBy, groupByColumns);

        private static readonly Dictionary<string, string> SettlementSortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["AccountName"] = "C.ACCOUNTNAME",
            ["AccountCode"] = "C.ACCOUNTCODE",
            ["VoucherDate"] = "A.VOUCHERDATE",
            ["VoucherNumber"] = "A.VOUCHERNUMBER",
        };

        // GB4.7's own default: ORDER BY c.accountname, a.voucherdate, a.vouchernumber
        private const string SettlementDefaultOrderBy = "C.ACCOUNTNAME, A.VOUCHERDATE, A.VOUCHERNUMBER";

        public static string BuildSettlementOrderBy(List<AccountReportSortOptionDTO> sortBy)
        {
            var mapped = MapFields(sortBy, SettlementSortFields);
            return mapped.Count == 0 ? SettlementDefaultOrderBy : string.Join(", ", mapped);
        }

        private static readonly Dictionary<string, string> CollectionProjectionSortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["AccountCode"] = "ACC.ACCOUNTCODE",
            ["RoutingName"] = "RT.ROUTENAME",
            ["DetailDate"] = "VBILL.DETAILDATE",
            ["DueDate"] = "VBILL.DUEDATE",
        };

        // GB4.7's own default: ORDER BY accountcode, routingname
        private const string CollectionProjectionDefaultOrderBy = "ACC.ACCOUNTCODE, RT.ROUTENAME";

        public static string BuildCollectionProjectionOrderBy(List<AccountReportSortOptionDTO> sortBy)
        {
            var mapped = MapFields(sortBy, CollectionProjectionSortFields);
            return mapped.Count == 0 ? CollectionProjectionDefaultOrderBy : string.Join(", ", mapped);
        }

        private static readonly Dictionary<string, string> TrialBalanceSortFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["AccountCode"] = "ACC.ACCOUNTCODE",
            ["AccountGroupName"] = "AG.ACCOUNTGROUPNAME",
            ["AccountScheduleName"] = "AGS.ACCOUNTSCHEDULENAME",
            ["Debit"] = "Debit",
            ["Credit"] = "Credit",
        };

        // Default order depends on GroupType (Account/AccountGroup/AccountSchedule each have a
        // different, non-overlapping SELECT/GROUP BY column set) — caller passes its own default.
        public static string BuildTrialBalanceOrderBy(List<AccountReportSortOptionDTO> sortBy, string defaultOrderBy)
        {
            var mapped = MapFields(sortBy, TrialBalanceSortFields);
            return mapped.Count == 0 ? defaultOrderBy : string.Join(", ", mapped);
        }

        private static List<string> MapFields(List<AccountReportSortOptionDTO> sortBy, Dictionary<string, string> allowList)
        {
            var result = new List<string>();
            if (sortBy == null) return result;

            foreach (var option in sortBy)
            {
                if (option?.Field == null) continue;
                if (!allowList.TryGetValue(option.Field, out var column)) continue; // unrecognized field: silently skipped, never interpolated
                result.Add(column + (option.Descending ? " DESC" : " ASC"));
            }
            return result;
        }
    }
}