"""Formulas from sheet 'Costs' of 'fpna_month_end.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 month_index(book: FrameBook) -> None:
    r"""Month index

    Costs!C2:M2 (11 columns, copied across).
    Excel (C2): =B2+1
    Pattern (R1C1): =RC[-1]+1
    Reads: Costs!B2
    """
    costs = book['Costs']
    for c in range(3, 14):
        costs[2, c] = costs[2, c - 1] + 1


def headcount(book: FrameBook) -> None:
    r"""Headcount

    Costs!B3:M3 (12 columns, copied across).
    Excel (B3): =ROUND(HeadcountStart*(1+GrowthRate)^(B2-1),0)
    Pattern (R1C1): =ROUND(HeadcountStart*(1+GrowthRate)^(R[-1]C-1),0)
    Reads: Assumptions!B5, Assumptions!B8, Costs!B2, name GrowthRate, name HeadcountStart
    """
    df = book.frame('Costs')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[3, cols] = round_((num(book.name_table('HeadcountStart')) * power((1 + num(book.name_table('GrowthRate'))), (num(across(df, 2, cols)) - 1))), 0)


def salary_cost(book: FrameBook) -> None:
    r"""Salary cost

    Costs!B4:M4 (12 columns, copied across).
    Excel (B4): =B3*AvgSalary/12
    Pattern (R1C1): =R[-1]C*AvgSalary/12
    Reads: Assumptions!B9, Costs!B3, name AvgSalary
    """
    df = book.frame('Costs')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[4, cols] = div((num(across(df, 3, cols)) * num(book.name_table('AvgSalary'))), 12)


def rent(book: FrameBook) -> None:
    r"""Rent

    Costs!B5:M5 (12 columns, copied across).
    Excel (B5): =MonthlyRent
    Pattern (R1C1): =MonthlyRent
    Reads: Assumptions!B10, name MonthlyRent
    """
    df = book.frame('Costs')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[5, cols] = book.name_table('MonthlyRent')


def total_costs(book: FrameBook) -> None:
    r"""Total costs

    Costs!B6:M6 (12 columns, copied across).
    Excel (B6): =SUM(B4:B5)
    Pattern (R1C1): =SUM(R[-2]C:R[-1]C)
    Reads: Costs!B4:B5
    """
    costs = book['Costs']
    for c in range(2, 14):
        costs[6, c] = xl.SUM(costs.rng(4, c, 5, c))