"""Formulas from sheet 'Summary' of 'pricing_calculator.xlsx' (pandas target).

Generated by Power Migrate 0.1.0a1. Copied formulas are column, row or
table operations on the sheet's DataFrame; other blocks run cell by cell over
the same frame. ``model.compute`` runs them in dependency order.
"""

from .. import xl
from ..frames import FrameBook, abs_, across, and_, average, averageif, averageifs, ceiling, cells, choose, col, concat, concatenate, count, counta, countblank, countif, countifs, date, datedif, day, div, edate, eomonth, eq, exact, exp, find, floor, fv, ge, gt, hlookup, iferror, ifna, ifs, index, int_, ipmt, irr, isblank, iserr, iserror, islogical, isna, isnumber, istext, le, left, len_, ln, log, log10, lower, lt, match, max_, maxifs, mid, min_, minifs, mod, month, n, ne, networkdays, not_, nper, npv, num, or_, pi, pmt, power, ppmt, product, proper, pv, rate, rept, right, round_, rounddown, roundup, search, shifted, sign, sqrt, substitute, sum_, sumif, sumifs, sumproduct, switch, text, textjoin, trim, trunc, upper, value, vlookup, weekday, where, workday, xlookup, xor, year, yearfrac


def grade_band(book: FrameBook) -> None:
    r"""Grade band

    Summary!B3 (one cell).
    Excel (B3): =_xlfn.IFS(Inputs!B9="A","Prime",Inputs!B9="B","Near prime",Inputs!B9="C","Standard",TRUE,"Sub prime")
    Reads: Inputs!B9
    """
    summary = book['Summary']
    inputs = book['Inputs']
    summary["B3"] = xl.IFS(inputs["B9"] == 'A', 'Prime', inputs["B9"] == 'B', 'Near prime', inputs["B9"] == 'C', 'Standard', True, 'Sub prime')


def product_description(book: FrameBook) -> None:
    r"""Product description

    Summary!B4 (one cell).
    Excel (B4): =_xlfn.SWITCH(Inputs!B10,"HP","Hire purchase","PCP","Personal contract purchase","LP","Lease purchase","Other")
    Reads: Inputs!B10
    """
    summary = book['Summary']
    inputs = book['Inputs']
    summary["B4"] = xl.SWITCH(inputs["B10"], 'HP', 'Hire purchase', 'PCP', 'Personal contract purchase', 'LP', 'Lease purchase', 'Other')


def quote_line(book: FrameBook) -> None:
    r"""Quote line

    Summary!B5 (one cell).
    Excel (B5): =_xlfn.TEXTJOIN(" | ",TRUE,B4,TEXT(Economics!B3,"#,##0.00")&" per month",TermMonths&" months",TEXT(APR,"0.00%")&" APR")
    Reads: Economics!B3, Inputs!B5, RateCard!B12, Summary!B4, name APR, name TermMonths
    """
    summary = book['Summary']
    economics = book['Economics']
    summary["B5"] = xl.TEXTJOIN(' | ', True, summary["B4"], xl.concat(xl.TEXT(economics["B3"], '#,##0.00'), ' per month'), xl.concat(book.name('TermMonths'), ' months'), xl.concat(xl.TEXT(book.name('APR'), '0.00%'), ' APR'))


def payments_above_900(book: FrameBook) -> None:
    r"""Payments above 900

    Summary!B6 (one cell).
    Excel (B6): =COUNTIF(Schedule!D3:D74,">900")
    Reads: Schedule!D3:D74
    """
    summary = book['Summary']
    schedule = book['Schedule']
    summary["B6"] = xl.COUNTIF(schedule["D3:D74"], '>900')


def within_policy(book: FrameBook) -> None:
    r"""Within policy

    Summary!B7 (one cell).
    Excel (B7): =AND(APR<0.12,Inputs!B4>=0.1,TermMonths<=60)
    Reads: Inputs!B4, Inputs!B5, RateCard!B12, name APR, name TermMonths
    """
    summary = book['Summary']
    inputs = book['Inputs']
    summary["B7"] = xl.AND(book.name('APR') < 0.12, inputs["B4"] >= 0.1, book.name('TermMonths') <= 60)


def approval(book: FrameBook) -> None:
    r"""Approval

    Summary!B8 (one cell).
    Excel (B8): =IF(B7,"Auto-approve",IF(Inputs!B9="E","Decline","Refer"))
    Reads: Inputs!B9, Summary!B7
    """
    summary = book['Summary']
    inputs = book['Inputs']
    summary["B8"] = xl.IF(summary["B7"], 'Auto-approve', xl.IF(inputs["B9"] == 'E', 'Decline', 'Refer'))


def days_in_schedule(book: FrameBook) -> None:
    r"""Days in schedule

    Summary!B9 (one cell).
    Excel (B9): =Schedule!B74-Schedule!B2
    Reads: Schedule!B2, Schedule!B74
    """
    summary = book['Summary']
    schedule = book['Schedule']
    summary["B9"] = schedule["B74"] - schedule["B2"]


def average_balance(book: FrameBook) -> None:
    r"""Average balance

    Summary!B10 (one cell).
    Excel (B10): =AVERAGEIF(Schedule!A3:A74,"<="&TermMonths,Schedule!C3:C74)
    Reads: Inputs!B5, Schedule!A3:A74, Schedule!C3:C74, name TermMonths
    """
    summary = book['Summary']
    schedule = book['Schedule']
    summary["B10"] = xl.AVERAGEIF(schedule["A3:A74"], xl.concat('<=', book.name('TermMonths')), schedule["C3:C74"])