"""Formulas from sheet 'Dashboard' of 'fpna_month_end.xlsx'.

Generated by Power Migrate 0.1.0a1 (no-AI mode). One function per
logic block, in the order they appear on the sheet; ``model.compute``
runs them in dependency order.
"""

from .. import xl
from ..xl import Book


def total_net_revenue(book: Book) -> None:
    r"""Total net revenue

    Dashboard!B3 (one cell).
    Excel (B3): =SUM(Revenue!B12:M12)
    Reads: Revenue!B12:M12
    """
    dashboard = book['Dashboard']
    revenue = book['Revenue']
    dashboard["B3"] = xl.SUM(revenue["B12:M12"])


def total_net_result(book: Book) -> None:
    r"""Total net result

    Dashboard!B4 (one cell).
    Excel (B4): =SUM('P&L'!B8:M8)+Scratch!B2
    Reads: Scratch!B2, 'P&L'!B8:M8
    """
    dashboard = book['Dashboard']
    p_l = book['P&L']
    scratch = book['Scratch']
    dashboard["B4"] = xl.SUM(p_l["B8:M8"]) + scratch["B2"]


def best_month(book: Book) -> None:
    r"""Best month

    Dashboard!B5 (one cell).
    Excel (B5): =INDEX('P&L'!B2:M2,MATCH(MAX('P&L'!B8:M8),'P&L'!B8:M8,0))
    Reads: 'P&L'!B2:M2, 'P&L'!B8:M8
    """
    dashboard = book['Dashboard']
    p_l = book['P&L']
    dashboard["B5"] = xl.INDEX(p_l["B2:M2"], xl.MATCH(xl.MAX(p_l["B8:M8"]), p_l["B8:M8"], 0))


def worst_month(book: Book) -> None:
    r"""Worst month

    Dashboard!B6 (one cell).
    Excel (B6): =INDEX('P&L'!B2:M2,MATCH(MIN('P&L'!B8:M8),'P&L'!B8:M8,0))
    Reads: 'P&L'!B2:M2, 'P&L'!B8:M8
    """
    dashboard = book['Dashboard']
    p_l = book['P&L']
    dashboard["B6"] = xl.INDEX(p_l["B2:M2"], xl.MATCH(xl.MIN(p_l["B8:M8"]), p_l["B8:M8"], 0))


def loss_making_months(book: Book) -> None:
    r"""Loss-making months

    Dashboard!B7 (one cell).
    Excel (B7): =COUNTIF('P&L'!B8:M8,"<0")
    Reads: 'P&L'!B8:M8
    """
    dashboard = book['Dashboard']
    p_l = book['P&L']
    dashboard["B7"] = xl.COUNTIF(p_l["B8:M8"], '<0')


def average_margin_profitable_months(book: Book) -> None:
    r"""Average margin (profitable months)

    Dashboard!B8 (one cell).
    Excel (B8): =AVERAGEIF('P&L'!B6:M6,">0")
    Reads: 'P&L'!B6:M6
    """
    dashboard = book['Dashboard']
    p_l = book['P&L']
    dashboard["B8"] = xl.AVERAGEIF(p_l["B6:M6"], '>0')


def average_headcount(book: Book) -> None:
    r"""Average headcount

    Dashboard!B9 (one cell).
    Excel (B9): =AVERAGE(Costs!B3:M3)
    Reads: Costs!B3:M3
    """
    dashboard = book['Dashboard']
    costs = book['Costs']
    dashboard["B9"] = xl.AVERAGE(costs["B3:M3"])


def orders_with_unknown_product(book: Book) -> None:
    r"""Orders with unknown product

    Dashboard!B10 (one cell).
    Excel (B10): =COUNTIF(Orders!H:H,"#N/A")
    Reads: Orders!H1:H301
    """
    dashboard = book['Dashboard']
    orders = book['Orders']
    dashboard["B10"] = xl.COUNTIF(orders["H1:H301"], '#N/A')


def headline(book: Book) -> None:
    r"""Headline

    Dashboard!B12 (one cell).
    Excel (B12): =CONCATENATE("Net result ",TEXT(B4,"#,##0")," GBP across ",COUNTA(Revenue!B2:M2)," months")
    Reads: Dashboard!B4, Revenue!B2:M2
    """
    dashboard = book['Dashboard']
    revenue = book['Revenue']
    dashboard["B12"] = xl.CONCATENATE('Net result ', xl.TEXT(dashboard["B4"], '#,##0'), ' GBP across ', xl.COUNTA(revenue["B2:M2"]), ' months')