// Copyright 2026 OfficeCLI (https://OfficeCLI.AI)
// SPDX-License-Identifier: Apache-2.0

using System.Globalization;

namespace OfficeCli.Core;

// W3b — bond / coupon / securities analysis functions (the date-driven
// fixed-income family). Every one rests on two shared pieces: the day-count
// basis engine (DayCountDiff / YearFracBasis, bases 0–4) and the coupon-date
// schedule (CouponPcd / CouponNcd stepped back from maturity). PRICE/YIELD-style
// roots reuse the shared SolveRoot. Values are verified against real
// Excel — the day-count edge rules (30/360 end-of-month, actual/actual) are
// where engines diverge, so each function is checked rather than trusted.
internal partial class FormulaEvaluator
{
    // ---- argument coercion ----

    private static DateTime Serial(List<object> args, int i)
        => DateTime.FromOADate(i < args.Count && args[i] is FormulaResult r ? r.AsNumber() : 0);

    private static double SecNum(List<object> args, int i, double def = 0)
        => i < args.Count && args[i] is FormulaResult r && !r.IsBlank ? r.AsNumber() : def;

    private static int SecBasis(List<object> args, int i)
    {
        int b = (int)SecNum(args, i, 0);
        return b;
    }

    // ---- day-count basis engine (bases 0–4) ----

    private static bool IsLastDayOfMonth(DateTime d) => d.Day == DateTime.DaysInMonth(d.Year, d.Month);
    private static bool IsLastFeb(DateTime d) => d.Month == 2 && IsLastDayOfMonth(d);

    // US (NASD) 30/360 — Excel basis 0.
    private static int Days360Us(DateTime d1, DateTime d2)
    {
        int dd1 = d1.Day, dd2 = d2.Day;
        if (IsLastFeb(d1) && IsLastFeb(d2)) dd2 = 30;
        if (IsLastFeb(d1)) dd1 = 30;
        if (dd2 == 31 && dd1 >= 30) dd2 = 30;
        if (dd1 == 31) dd1 = 30;
        return (d2.Year - d1.Year) * 360 + (d2.Month - d1.Month) * 30 + (dd2 - dd1);
    }

    // European 30/360 — Excel basis 4.
    private static int Days360Eu(DateTime d1, DateTime d2)
    {
        int dd1 = Math.Min(d1.Day, 30), dd2 = Math.Min(d2.Day, 30);
        return (d2.Year - d1.Year) * 360 + (d2.Month - d1.Month) * 30 + (dd2 - dd1);
    }

    // Day count between two dates under a basis.
    private static double DayCountDiff(DateTime d1, DateTime d2, int basis) => basis switch
    {
        0 => Days360Us(d1, d2),
        4 => Days360Eu(d1, d2),
        _ => (d2 - d1).Days,   // actual (bases 1,2,3)
    };

    private static double DaysInYearBasis(int basis) => basis == 3 ? 365.0 : 360.0;

    // Year fraction between two dates under a basis (YEARFRAC + ACCRINT engine).
    private static double YearFracBasis(DateTime start, DateTime end, int basis)
    {
        if (start == end) return 0.0;
        bool neg = start > end;
        if (neg) (start, end) = (end, start);
        double frac = basis switch
        {
            0 => Days360Us(start, end) / 360.0,
            4 => Days360Eu(start, end) / 360.0,
            2 => (end - start).Days / 360.0,
            3 => (end - start).Days / 365.0,
            _ => ActualActualYearFrac(start, end),   // basis 1
        };
        return neg ? -frac : frac;
    }

    // Basis-1 actual/actual day count (OOXML §18.17.4.2 / ODF 4.11.7.7): actual
    // days over a year length chosen by where the interval sits. A span longer
    // than one year (or crossing the year boundary "forward") uses the average
    // days-per-year of the calendar years it touches; a span of at most one year
    // uses 366 iff a 29-Feb falls inside it (inclusive), else 365.
    private static double ActualActualYearFrac(DateTime start, DateTime end)
    {
        int y1 = start.Year, m1 = start.Month, d1 = start.Day;
        int y2 = end.Year, m2 = end.Month, d2 = end.Day;
        double dayDiff = (end - start).Days;
        bool yearDifferent = y1 != y2;
        double denom;
        if (yearDifferent && (y2 != y1 + 1 || m1 < m2 || (m1 == m2 && d1 < d2)))
        {
            int dayCount = 0;
            for (int i = y1; i <= y2; i++) dayCount += DateTime.IsLeapYear(i) ? 366 : 365;
            denom = (double)dayCount / (y2 - y1 + 1);
        }
        else if (!yearDifferent && DateTime.IsLeapYear(y1))
        {
            denom = 366;
        }
        else if (yearDifferent &&
                 ((DateTime.IsLeapYear(y1) && (m1 < 2 || (m1 == 2 && d1 <= 29))) ||
                  (DateTime.IsLeapYear(y2) && (m2 > 2 || (m2 == 2 && d2 == 29)))))
        {
            denom = 366;
        }
        else
        {
            denom = 365;
        }
        return dayDiff / denom;
    }

    // ---- coupon schedule ----

    // Coupon date <= settlement (previous coupon date), generated by stepping the
    // maturity date back by whole periods with end-of-month preservation.
    private static DateTime CouponPcd(DateTime settle, DateTime maturity, int freq)
    {
        int step = 12 / freq;
        bool eom = IsLastDayOfMonth(maturity);
        DateTime d = maturity;
        int k = 0;
        while (d > settle) { k++; d = ShiftMonthsEom(maturity, -k * step, eom); }
        return d;
    }

    // Coupon date > settlement (next coupon date).
    private static DateTime CouponNcd(DateTime settle, DateTime maturity, int freq)
    {
        int step = 12 / freq;
        bool eom = IsLastDayOfMonth(maturity);
        DateTime d = maturity;
        int k = 0;
        while (d > settle) { k++; d = ShiftMonthsEom(maturity, -k * step, eom); }
        // d is now the last coupon <= settle; one step forward is the NCD.
        return ShiftMonthsEom(maturity, -(k - 1) * step, eom);
    }

    private static DateTime ShiftMonthsEom(DateTime anchor, int months, bool eom)
    {
        int total = (anchor.Year * 12 + (anchor.Month - 1)) + months;
        int y = total / 12, m = total % 12 + 1;
        int dim = DateTime.DaysInMonth(y, m);
        int day = eom ? dim : Math.Min(anchor.Day, dim);
        return new DateTime(y, m, day);
    }

    private static int CouponNumber(DateTime settle, DateTime maturity, int freq)
    {
        int step = 12 / freq;
        bool eom = IsLastDayOfMonth(maturity);
        int count = 0;
        DateTime d = maturity;
        int k = 0;
        while (d > settle) { count++; k++; d = ShiftMonthsEom(maturity, -k * step, eom); }
        return count;
    }

    // COUPDAYS — days in the coupon period containing settlement.
    private FormulaResult? EvalCoupDays(List<object> args)
    {
        if (!CoupArgs(args, out var s, out var m, out var f, out var b)) return FormulaResult.Error("#NUM!");
        double days = b == 1
            ? (CouponNcd(s, m, f) - CouponPcd(s, m, f)).Days
            : DaysInYearBasis(b) / f;
        return FR(days);
    }

    private FormulaResult? EvalCoupDayBs(List<object> args)
    {
        if (!CoupArgs(args, out var s, out var m, out var f, out var b)) return FormulaResult.Error("#NUM!");
        return FR(DayCountDiff(CouponPcd(s, m, f), s, b));
    }

    private FormulaResult? EvalCoupDaysNc(List<object> args)
    {
        if (!CoupArgs(args, out var s, out var m, out var f, out var b)) return FormulaResult.Error("#NUM!");
        // 30/360 bases derive NC from the period less the elapsed part; actual
        // bases count the real days settlement→next coupon.
        if (b is 0 or 4)
            return FR(DaysInYearBasis(b) / f - DayCountDiff(CouponPcd(s, m, f), s, b));
        return FR(DayCountDiff(s, CouponNcd(s, m, f), b));
    }

    private FormulaResult? EvalCoupNcd(List<object> args)
    {
        if (!CoupArgs(args, out var s, out var m, out var f, out _)) return FormulaResult.Error("#NUM!");
        return FR(CouponNcd(s, m, f).ToOADate());
    }

    private FormulaResult? EvalCoupPcd(List<object> args)
    {
        if (!CoupArgs(args, out var s, out var m, out var f, out _)) return FormulaResult.Error("#NUM!");
        return FR(CouponPcd(s, m, f).ToOADate());
    }

    private FormulaResult? EvalCoupNum(List<object> args)
    {
        if (!CoupArgs(args, out var s, out var m, out var f, out _)) return FormulaResult.Error("#NUM!");
        return FR(CouponNumber(s, m, f));
    }

    private static bool CoupArgs(List<object> args, out DateTime settle, out DateTime maturity, out int freq, out int basis)
    {
        settle = Serial(args, 0); maturity = Serial(args, 1);
        freq = (int)SecNum(args, 2, 0); basis = SecBasis(args, 3);
        return (freq is 1 or 2 or 4) && basis is >= 0 and <= 4 && settle < maturity;
    }

    // ---- discount / interest securities ----

    // ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc])
    private FormulaResult? EvalAccrInt(List<object> args)
    {
        if (args.Count < 6) return FormulaResult.Error("#VALUE!");
        var issue = Serial(args, 0);
        var settle = Serial(args, 2);
        double rate = SecNum(args, 3), par = SecNum(args, 4, 1000);
        int freq = (int)SecNum(args, 5, 1), basis = SecBasis(args, 6);
        if (rate <= 0 || par <= 0 || freq is not (1 or 2 or 4) || settle <= issue)
            return FormulaResult.Error("#NUM!");
        // From-issue accrual (the common default): par * rate * yearfrac(issue, settlement).
        return FR(par * rate * YearFracBasis(issue, settle, basis));
    }

    // ACCRINTM(issue, settlement, rate, par, [basis])
    private FormulaResult? EvalAccrIntM(List<object> args)
    {
        if (args.Count < 3) return FormulaResult.Error("#VALUE!");
        var issue = Serial(args, 0); var settle = Serial(args, 1);
        double rate = SecNum(args, 2), par = SecNum(args, 3, 1000);
        int basis = SecBasis(args, 4);
        if (rate <= 0 || par <= 0 || settle <= issue) return FormulaResult.Error("#NUM!");
        return FR(par * rate * YearFracBasis(issue, settle, basis));
    }

    // DISC(settlement, maturity, pr, redemption, [basis])
    private FormulaResult? EvalDisc(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double pr = SecNum(args, 2), red = SecNum(args, 3);
        int basis = SecBasis(args, 4);
        if (pr <= 0 || red <= 0 || s >= m) return FormulaResult.Error("#NUM!");
        double dfrac = YearFracBasis(s, m, basis);
        return FR((red - pr) / red / dfrac);
    }

    // INTRATE(settlement, maturity, investment, redemption, [basis])
    private FormulaResult? EvalIntRate(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double inv = SecNum(args, 2), red = SecNum(args, 3);
        int basis = SecBasis(args, 4);
        if (inv <= 0 || red <= 0 || s >= m) return FormulaResult.Error("#NUM!");
        return FR((red - inv) / inv / YearFracBasis(s, m, basis));
    }

    // RECEIVED(settlement, maturity, investment, discount, [basis])
    private FormulaResult? EvalReceived(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double inv = SecNum(args, 2), disc = SecNum(args, 3);
        int basis = SecBasis(args, 4);
        if (inv <= 0 || disc <= 0 || s >= m) return FormulaResult.Error("#NUM!");
        double dfrac = YearFracBasis(s, m, basis);
        double denom = 1 - disc * dfrac;
        if (denom <= 0) return FormulaResult.Error("#NUM!");
        return FR(inv / denom);
    }

    // PRICEDISC(settlement, maturity, discount, redemption, [basis])
    private FormulaResult? EvalPriceDisc(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double disc = SecNum(args, 2), red = SecNum(args, 3);
        int basis = SecBasis(args, 4);
        if (disc <= 0 || red <= 0 || s >= m) return FormulaResult.Error("#NUM!");
        return FR(red - disc * red * YearFracBasis(s, m, basis));
    }

    // YIELDDISC(settlement, maturity, pr, redemption, [basis])
    private FormulaResult? EvalYieldDisc(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double pr = SecNum(args, 2), red = SecNum(args, 3);
        int basis = SecBasis(args, 4);
        if (pr <= 0 || red <= 0 || s >= m) return FormulaResult.Error("#NUM!");
        return FR((red - pr) / pr / YearFracBasis(s, m, basis));
    }

    // PRICEMAT(settlement, maturity, issue, rate, yld, [basis])
    private FormulaResult? EvalPriceMat(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1); var issue = Serial(args, 2);
        double rate = SecNum(args, 3), yld = SecNum(args, 4);
        int basis = SecBasis(args, 5);
        if (rate < 0 || yld < 0 || s >= m) return FormulaResult.Error("#NUM!");
        double dim = YearFracBasis(issue, m, basis);   // issue→maturity
        double dis = YearFracBasis(issue, s, basis);   // issue→settlement
        double dsm = YearFracBasis(s, m, basis);       // settlement→maturity
        double num = 100 + dim * rate * 100;
        double den = 1 + dsm * yld;
        return FR(num / den - dis * rate * 100);
    }

    // YIELDMAT(settlement, maturity, issue, rate, pr, [basis])
    private FormulaResult? EvalYieldMat(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1); var issue = Serial(args, 2);
        double rate = SecNum(args, 3), pr = SecNum(args, 4);
        int basis = SecBasis(args, 5);
        if (rate < 0 || pr <= 0 || s >= m) return FormulaResult.Error("#NUM!");
        double dim = YearFracBasis(issue, m, basis);
        double dis = YearFracBasis(issue, s, basis);
        double dsm = YearFracBasis(s, m, basis);
        double a = 100 + dim * rate * 100;
        double b = pr + dis * rate * 100;
        return FR((a - b) / b / dsm);
    }

    // ---- T-bill family (always actual/360) ----

    private FormulaResult? EvalTBillEq(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double disc = SecNum(args, 2);
        if (disc <= 0 || s >= m) return FormulaResult.Error("#NUM!");
        double dsm = (m - s).Days;
        if (dsm > 365) return FormulaResult.Error("#NUM!");
        return FR(365 * disc / (360 - disc * dsm));
    }

    private FormulaResult? EvalTBillPrice(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double disc = SecNum(args, 2);
        if (disc <= 0 || s >= m) return FormulaResult.Error("#NUM!");
        double dsm = (m - s).Days;
        if (dsm > 365) return FormulaResult.Error("#NUM!");
        return FR(100 * (1 - disc * dsm / 360));
    }

    private FormulaResult? EvalTBillYield(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double pr = SecNum(args, 2);
        if (pr <= 0 || s >= m) return FormulaResult.Error("#NUM!");
        double dsm = (m - s).Days;
        if (dsm > 365) return FormulaResult.Error("#NUM!");
        return FR((100 - pr) / pr * (360 / dsm));
    }

    // ---- coupon bond PRICE / YIELD / DURATION ----

    // PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
    private FormulaResult? EvalPrice(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double rate = SecNum(args, 2), yld = SecNum(args, 3), red = SecNum(args, 4);
        int freq = (int)SecNum(args, 5, 0), basis = SecBasis(args, 6);
        if (rate < 0 || yld < 0 || red <= 0 || freq is not (1 or 2 or 4) || s >= m)
            return FormulaResult.Error("#NUM!");
        return FR(BondPrice(s, m, rate, yld, red, freq, basis));
    }

    // YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]) — root of PRICE−pr.
    private FormulaResult? EvalYield(List<object> args)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double rate = SecNum(args, 2), pr = SecNum(args, 3), red = SecNum(args, 4);
        int freq = (int)SecNum(args, 5, 0), basis = SecBasis(args, 6);
        if (rate < 0 || pr <= 0 || red <= 0 || freq is not (1 or 2 or 4) || s >= m)
            return FormulaResult.Error("#NUM!");
        var root = SolveRoot(y => BondPrice(s, m, rate, y, red, freq, basis) - pr, rate > 0 ? rate : 0.05);
        return root is { } r ? FR(r) : FormulaResult.Error("#NUM!");
    }

    // Standard coupon-bond price per 100 face, with fractional first period.
    private static double BondPrice(DateTime s, DateTime m, double rate, double yld, double red, int freq, int basis)
    {
        int n = CouponNumber(s, m, freq);
        double e = basis == 1 ? (CouponNcd(s, m, freq) - CouponPcd(s, m, freq)).Days : DaysInYearBasis(basis) / freq;
        double dsc = basis == 1 ? (CouponNcd(s, m, freq) - s).Days : e - DayCountDiff(CouponPcd(s, m, freq), s, basis);
        double a = DayCountDiff(CouponPcd(s, m, freq), s, basis);
        double t = dsc / e;
        double coupon = 100.0 * rate / freq;
        double yf = yld / freq;

        double price = red / Math.Pow(1 + yf, n - 1 + t);
        for (int k = 1; k <= n; k++)
            price += coupon / Math.Pow(1 + yf, k - 1 + t);
        price -= coupon * (a / e);   // less accrued interest
        return price;
    }

    // DURATION / MDURATION(settlement, maturity, coupon, yld, frequency, [basis])
    private FormulaResult? EvalDuration(List<object> args, bool modified)
    {
        var s = Serial(args, 0); var m = Serial(args, 1);
        double coupon = SecNum(args, 2), yld = SecNum(args, 3);
        int freq = (int)SecNum(args, 4, 0), basis = SecBasis(args, 5);
        if (coupon < 0 || yld < 0 || freq is not (1 or 2 or 4) || s >= m)
            return FormulaResult.Error("#NUM!");
        double dur = MacaulayDuration(s, m, coupon, yld, freq, basis);
        if (modified) dur /= 1 + yld / freq;
        return FR(dur);
    }

    private static double MacaulayDuration(DateTime s, DateTime m, double coupon, double yld, int freq, int basis)
    {
        int n = CouponNumber(s, m, freq);
        double e = basis == 1 ? (CouponNcd(s, m, freq) - CouponPcd(s, m, freq)).Days : DaysInYearBasis(basis) / freq;
        double dsc = basis == 1 ? (CouponNcd(s, m, freq) - s).Days : e - DayCountDiff(CouponPcd(s, m, freq), s, basis);
        double t0 = dsc / e;
        double yf = yld / freq;
        double cpn = 100.0 * coupon / freq;

        double wPv = 0, pv = 0;
        for (int k = 1; k <= n; k++)
        {
            double tk = (k - 1 + t0);                 // periods to cashflow
            double cf = cpn + (k == n ? 100.0 : 0.0); // redemption at par with final coupon
            double d = cf / Math.Pow(1 + yf, tk);
            pv += d;
            wPv += d * (tk / freq);                   // weight in years
        }
        return pv == 0 ? 0 : wPv / pv;
    }
}
