"""The merged table as an Excel workbook: a sheet per statement, indentation from the breakdown tree."""
import io

from openpyxl import load_workbook

from app.ocr.services import xlsx_export
from tests.test_ocr import ocr  # noqa: F401  (fixture)


def _row(sid, field, values, comps=None, stmt="BALANCE_SHEET", agg="LINE_ITEM", unit="BDT", restated=None, scope="STANDALONE"):
    return {"series_id": sid, "field": field, "statement_type": stmt, "scope": scope, "unit": unit,
            "aggregation_level": agg, "values": {str(y): v for y, v in values.items()},
            "breakdown": {"components": [{"series_id": c, "sign": 1} for c in comps]} if comps else None,
            "restatement_variances": restated or {}}


ROWS = [
    _row(1, "Net Asset Value Per Share", {2024: 13.6}, unit="BDT/share"),
    _row(2, "Total Assets", {2023: 100, 2024: 120}, comps=[3, 4], agg="GRAND_TOTAL",
         restated={"2024": {"original_value": 118.0, "restated_value": 120.0}}),
    _row(3, "Non-current Assets", {2023: 60, 2024: 70}, comps=[5, 6], agg="SUBTOTAL"),
    _row(4, "Current Assets", {2023: 40, 2024: 50}, agg="SUBTOTAL"),
    _row(5, "Property, Plant and Equipment", {2023: 50, 2024: 60}),
    _row(6, "Intangible Assets", {2024: 10}),
    _row(7, "Total Owner's Equity and Liabilities", {2023: 100, 2024: 120}, comps=[8, 9], agg="GRAND_TOTAL"),
    _row(8, "Total Liabilities", {2023: 30, 2024: 40}, comps=[9], agg="SUBTOTAL"),
    _row(9, "Non-current Liabilities", {2023: 30, 2024: 40}),
    _row(20, "Revenue", {2024: 5}, stmt="INCOME_STATEMENT"),
    _row(30, "Share of notes", {2024: 1}, stmt="NOTES_AND_FOOTNOTES"),
]


def _load(rows=ROWS):
    return load_workbook(io.BytesIO(xlsx_export.build_merged_workbook(rows)))


def test_one_sheet_per_statement_and_none_for_missing_ones():
    wb = _load()
    assert wb.sheetnames == ["Balance Sheet", "Income Statement", "Notes & Others"]


def test_tree_order_and_indent_follow_the_breakdowns():
    ws = _load()["Balance Sheet"]
    lines = [(ws.cell(row=r, column=1).value, ws.cell(row=r, column=1).alignment.indent) for r in range(3, ws.max_row + 1)]
    assert lines == [
        ("Net Asset Value Per Share", 0),
        ("Total Assets", 0), ("Non-current Assets", 1), ("Property, Plant and Equipment", 2),
        ("Intangible Assets", 2), ("Current Assets", 1),
        ("Total Owner's Equity and Liabilities", 0), ("Total Liabilities", 1), ("Non-current Liabilities", 2),
    ]
    assert ws.cell(row=2, column=1).value == "Standalone (9)"


def test_numbers_are_numbers_headings_are_bold_and_restated_cells_are_marked():
    ws = _load()["Balance Sheet"]
    assert [c.value for c in ws[1]] == ["Field / Line Item", "Unit", "2023", "2024"]
    assert ws["D3"].value == 13.6 and ws["D3"].number_format == "#,##0.00;-#,##0.00"      # per-share keeps decimals
    assert ws["A4"].font.bold and not ws["A6"].font.bold
    assert ws["C4"].value == 100 and ws["C4"].number_format == "#,##0;-#,##0"
    assert ws["D4"].fill.fgColor.rgb.endswith("FFF3C4") and "118" in ws["D4"].comment.text
    assert ws["C3"].value is None                                                         # no value stays an empty cell


def test_download_endpoint(ocr):
    import json
    from app.ocr import database
    from tests.test_ocr import KEY
    database.upsert_company("XLSXCO", "Xlsx Co", "Misc")
    r = ocr.get("/ocr/company/XLSXCO/download/merged.xlsx", headers=KEY)
    assert r.status_code == 403                                                           # no merge yet
    with database.get_connection() as conn:
        conn.execute("INSERT INTO company_results (instrument_code, merged_rows_json, updated_at) VALUES (?, ?, ?)",
                     ("XLSXCO", json.dumps(ROWS), "2026-11-11T00:00:00"))
        conn.commit()
    r = ocr.get("/ocr/company/XLSXCO/download/merged.xlsx", headers=KEY)
    assert r.status_code == 200 and "spreadsheetml" in r.headers["content-type"]
    assert "XLSXCO_financial_data_merged.xlsx" in r.headers["content-disposition"]
    assert load_workbook(io.BytesIO(r.content)).sheetnames[0] == "Balance Sheet"


RAW_COLS = [
    {"key": "2021-22__2021", "group_header": "FY 2021-22", "header": "2021", "restated": False},
    {"key": "2021-22__2022", "group_header": "FY 2021-22", "header": "2022", "restated": False},
    {"key": "2022-23__2022@2022-07-01", "group_header": "FY 2022-23", "header": "2022-07-01", "restated": True},
]
RAW_ROWS = [
    {"field": "Total Assets", "statement_type": "BALANCE_SHEET", "scope": "STANDALONE", "unit": "BDT",
     "values": {"2021-22__2021": 1.0, "2021-22__2022": 2.0, "2022-23__2022@2022-07-01": 3.0}},
    {"field": "Net Asset Value Per Share", "statement_type": "BALANCE_SHEET", "scope": "STANDALONE", "unit": "BDT/share",
     "values": {"2021-22__2022": 13.98}},
    {"field": "Revenue", "statement_type": "INCOME_STATEMENT", "scope": "CONSOLIDATED", "unit": "BDT", "values": {}},
]


def test_raw_workbook_has_report_group_headers_and_numeric_cells():
    wb = load_workbook(io.BytesIO(xlsx_export.build_raw_workbook(RAW_ROWS, RAW_COLS)))
    assert wb.sheetnames == ["Balance Sheet", "Income Statement"]
    ws = wb["Balance Sheet"]
    assert [c.value for c in ws[1]][:6] == ["Field / Raw Line Item", "Scope", "Unit", "FY 2021-22", None, "FY 2022-23"]
    assert [c.value for c in ws[2]][3:] == ["2021", "2022", "2022-07-01"]
    assert any(str(m) == "D1:E1" for m in ws.merged_cells.ranges)             # the report spans its columns
    assert [ws.cell(row=3, column=c).value for c in (1, 2, 3, 4, 5, 6)] == ["Total Assets", "Standalone", "BDT", 1.0, 2.0, 3.0]
    assert ws["E4"].value == 13.98 and ws["E4"].number_format == "#,##0.00;-#,##0.00" and ws["D4"].value is None
    assert ws.freeze_panes == "D3"


def test_raw_download_endpoint(ocr):
    from tests.test_ocr import KEY
    assert ocr.get("/ocr/company/NOSUCHCO/download/raw.xlsx", headers=KEY).status_code == 404


# ---- the standard workbook (Balance Sheet only; the merged workbook above is a different, unchanged export)
def _std(rows=ROWS):
    return load_workbook(io.BytesIO(xlsx_export.build_standard_workbook(rows)))


def _by_name(ws):
    return {ws.cell(row=r, column=1).value: r for r in range(2, ws.max_row + 1)}


def test_standard_balance_sheet_layout():
    wb = _std()
    assert wb.sheetnames == ["Balance Sheet", "Income Statement", "Notes & Others"]
    ws = wb["Balance Sheet"]
    assert [c.value for c in ws[1]] == ["Line Item", "Unit", "2023", "2024"]
    names = [(ws.cell(row=r, column=1).value, ws.cell(row=r, column=1).alignment.indent) for r in range(2, ws.max_row + 1)]
    assert names == [
        ("ASSETS", 0),
        ("Non-current Assets:", 0), ("Property, Plant and Equipment", 1), ("Intangible Assets", 1), ("Total Non-current Assets", 0),
        ("As calculated", 1), ("Check (calculated - reported)", 1), (None, 0),
        ("Current Assets", 0),                                        # a total with no parts is a plain line
        ("Total Assets", 0), ("As calculated", 1), ("Check (calculated - reported)", 1), (None, 0),
        ("OWNER'S EQUITY AND LIABILITIES", 0), ("Liabilities:", 0), ("Non-current Liabilities", 1), ("Total Liabilities", 0),
        ("As calculated", 1), ("Check (calculated - reported)", 1), (None, 0),
        ("Total Owner's Equity and Liabilities", 0), ("As calculated", 1),
        ("Check (calculated - reported)", 1), (None, 0),
        ("Check: Total Assets - Total Owner's Equity and Liabilities (0 when it balances; up to 5 units = rounding)", 0), (None, 0),
        ("PER SHARE DATA", 0), ("Net Asset Value Per Share", 1),
    ]


def test_standard_totals_show_the_printed_value_with_the_calculated_formulas_and_a_check_under_them():
    ws = _std()["Balance Sheet"]
    r = _by_name(ws)
    ppe, tot = r["Property, Plant and Equipment"], r["Total Non-current Assets"]
    calc, chk = tot + 1, tot + 2                                                                                     # "As calculated", then the check
    assert ws.cell(row=calc, column=1).value == "As calculated" and ws.cell(row=chk, column=1).value == "Check (calculated - reported)"
    assert ws.cell(row=ppe, column=3).value == 50 and ws.cell(row=ppe, column=3).font.color.rgb.endswith("0000FF")    # input line: blue
    assert ws.cell(row=tot, column=3).value == 60 and ws.cell(row=tot, column=4).value == 70                          # the printed figures are the main value
    assert ws.cell(row=tot, column=1).font.bold and ws.cell(row=tot, column=3).fill.fgColor.rgb.endswith("EDEFF2")
    assert ws.cell(row=calc, column=3).value == f"=SUM(C{ppe},C{r['Intangible Assets']})"                            # calculated from its lines
    assert ws.cell(row=calc, column=4).value == f"=SUM(D{ppe},D{r['Intangible Assets']})"
    assert ws.cell(row=calc, column=3).fill.fgColor.rgb.endswith("EDEFF2")                                           # grey italic
    assert ws.cell(row=chk, column=3).value == f"=C{calc}-C{tot}" and ws.cell(row=chk, column=4).value == f"=D{calc}-D{tot}"
    # a total made of lines and totals is a SUM; a total made only of calculated totals adds them ("A+B", as in the reference workbook);
    # the parts of a total are the main (printed) cells of its parts; the assets / equity-and-liabilities check subtracts the printed totals
    a, e = r["Total Assets"], r["Total Owner's Equity and Liabilities"]
    assert ws.cell(row=a + 1, column=3).value == f"=SUM(C{tot},C{r['Current Assets']})"
    assert ws.cell(row=e + 1, column=3).value == f"=C{r['Total Liabilities']}"
    last = r["Check: Total Assets - Total Owner's Equity and Liabilities (0 when it balances; up to 5 units = rounding)"]
    assert ws.cell(row=last, column=3).value == f"=C{a}-C{e}" and ws.cell(row=last, column=4).value == f"=D{a}-D{e}"
    assert ws.cell(row=a, column=4).fill.fgColor.rgb.endswith("FFF3C4")                                              # restated cell kept on the total
    assert ws.cell(row=ppe, column=3).number_format == '#,##0;-#,##0;"-"'
    nav = r["Net Asset Value Per Share"]
    assert ws.cell(row=nav, column=4).value == 13.6 and ws.cell(row=nav, column=4).number_format == '#,##0.00;-#,##0.00;"-"'


def test_a_minus_part_is_subtracted_and_a_non_sum_total_keeps_its_value():
    rows = [
        _row(1, "Total Assets", {2024: 70}, comps=[2, 3], agg="GRAND_TOTAL"),
        _row(2, "Cash", {2024: 100}), _row(3, "Less: Provision", {2024: 30}),
        _row(4, "Total Ratio Line", {2024: 2}, comps=[5, 6], agg="SUBTOTAL"), _row(5, "Profit", {2024: 4}), _row(6, "Shares", {2024: 2}),
    ]
    rows[0]["breakdown"]["components"][1]["sign"] = -1
    rows[2]["breakdown"] = None
    rows[3]["breakdown"]["operation"] = "RATIO"
    ws = _std(rows)["Balance Sheet"]
    r = _by_name(ws)
    assert ws.cell(row=r["Total Assets"], column=3).value == 70                                                       # the printed total
    assert ws.cell(row=r["Total Assets"] + 1, column=3).value == f"=C{r['Cash']}-ABS(C{r['Less: Provision']})"
    assert ws.cell(row=r["Total Ratio Line"], column=3).value == 2 and ws.cell(row=r["Total Ratio Line"] + 1, column=1).value != "As calculated"


def test_standard_workbook_one_sheet_per_statement_and_scope_and_the_merged_workbook_is_unchanged():
    assert _std().sheetnames == ["Balance Sheet", "Income Statement", "Notes & Others"]
    both = ROWS + [_row(50, "Total Assets", {2024: 9}, scope="CONSOLIDATED", agg="GRAND_TOTAL"),
                   _row(51, "Equity", {2024: 1}, stmt="CHANGES_IN_EQUITY", scope="CONSOLIDATED"),
                   _row(52, "Equity", {2024: 1}, stmt="CHANGES_IN_EQUITY")]
    assert _std(both).sheetnames == ["Balance Sheet - Consolidated", "Balance Sheet - Standalone", "Income Statement",
                                     "Changes in Equity - Cons.", "Changes in Equity - Standalone", "Notes & Others"]
    assert _std([]).sheetnames == ["Balance Sheet"]
    assert _load().sheetnames == ["Balance Sheet", "Income Statement", "Notes & Others"]


IS_ROWS = [
    _row(1, "Net Profit", {2024: 25}, comps=[2, 5], stmt="INCOME_STATEMENT", agg="GRAND_TOTAL"),
    _row(2, "Gross Profit", {2024: 40}, comps=[3, 4], stmt="INCOME_STATEMENT", agg="SUBTOTAL"),
    _row(3, "Revenue", {2024: 100}, stmt="INCOME_STATEMENT"),
    _row(4, "Cost of Sales", {2024: 60}, stmt="INCOME_STATEMENT"),
    _row(5, "Expenses", {2024: 15}, stmt="INCOME_STATEMENT"),
    _row(6, "Earnings Per Share", {2024: 1.25}, stmt="INCOME_STATEMENT", unit="BDT/share"),
    _row(7, "Dividend proposed", {2024: 3}, stmt="INCOME_STATEMENT"),
]
for _r, _minus in ((IS_ROWS[0], 1), (IS_ROWS[1], 1)):
    _r["breakdown"]["components"][_minus]["sign"] = -1


def test_other_statements_read_like_the_printed_statement():
    ws = _std(IS_ROWS)["Income Statement"]
    names = [(ws.cell(row=r, column=1).value, ws.cell(row=r, column=1).alignment.indent) for r in range(2, ws.max_row + 1)]
    assert names == [
        ("Revenue", 1), ("Cost of Sales", 1),                       # a group's own lines first, indented
        ("Gross Profit", 0), ("As calculated", 1), ("Check (calculated - reported)", 1), (None, 0),
        ("Expenses", 1),
        ("Net Profit", 0), ("As calculated", 1), ("Check (calculated - reported)", 1), (None, 0),
        ("Dividend proposed", 0),                                   # a line in no total follows; no banners, no "Total" prefix
        ("PER SHARE DATA", 0), ("Earnings Per Share", 1),
    ]
    r = _by_name(ws)
    assert ws.cell(row=r["Gross Profit"], column=3).value == 40 and ws.cell(row=r["Net Profit"], column=3).value == 25
    assert ws.cell(row=r["Gross Profit"] + 1, column=3).value == f"=C{r['Revenue']}-ABS(C{r['Cost of Sales']})"
    assert ws.cell(row=r["Net Profit"] + 1, column=3).value == f"=C{r['Gross Profit']}-ABS(C{r['Expenses']})"
    assert ws.cell(row=r["Net Profit"] + 2, column=3).value == f"=C{r['Net Profit'] + 1}-C{r['Net Profit']}"
    assert ws.cell(row=r["Revenue"], column=3).font.color.rgb.endswith("0000FF") and ws.cell(row=r["Net Profit"], column=1).font.bold
    assert not any(str(n).startswith("Check: Total") for n, _ in names)                    # the assets check is the Balance Sheet's only


def test_notes_and_others_have_a_banner_per_statement_type():
    rows = [_row(1, "Note total", {2024: 3}, comps=[2, 3], stmt="NOTES_AND_FOOTNOTES", agg="SUBTOTAL"),
            _row(2, "Note part A", {2024: 1}, stmt="NOTES_AND_FOOTNOTES"), _row(3, "Note part B", {2024: 2}, stmt="NOTES_AND_FOOTNOTES"),
            _row(4, "Odd line", {2024: 7}, stmt="UNKNOWN")]
    ws = _std(rows)["Notes & Others"]
    names = [ws.cell(row=r, column=1).value for r in range(2, ws.max_row + 1)]
    assert names == ["NOTES AND FOOTNOTES", "Note part A", "Note part B", "Note total", "As calculated",
                     "Check (calculated - reported)", None, "UNKNOWN", "Odd line"]
    r = _by_name(ws)
    assert ws.cell(row=r["Note total"] + 1, column=3).value == f"=SUM(C{r['Note part A']},C{r['Note part B']})"


def test_standard_download_endpoint(ocr):
    import json
    from app.ocr import database
    from tests.test_ocr import KEY
    database.upsert_company("XLSXCO2", "Xlsx Co 2", "Misc")
    assert ocr.get("/ocr/company/XLSXCO2/download/standard.xlsx", headers=KEY).status_code == 403
    with database.get_connection() as conn:
        conn.execute("INSERT INTO company_results (instrument_code, merged_rows_json, updated_at) VALUES (?, ?, ?)",
                     ("XLSXCO2", json.dumps(ROWS), "2026-11-11T00:00:00"))
        conn.commit()
    r = ocr.get("/ocr/company/XLSXCO2/download/standard.xlsx", headers=KEY)
    assert r.status_code == 200 and "XLSXCO2_financial_data_standard.xlsx" in r.headers["content-disposition"]
    assert load_workbook(io.BytesIO(r.content)).sheetnames == ["Balance Sheet", "Income Statement", "Notes & Others"]


# ---------------------------------------------------------------- the Standard tab (the same layout as data)
def _rows_by_name(sheet):
    return {r["name"]: r for r in sheet["rows"] if r["name"]}


def _under(sheet, total_name):
    """The "As calculated" and the check row right under a total of a layout sheet."""
    rows = sheet["rows"]
    i = next(k for k, x in enumerate(rows) if x["kind"] == "total" and x["name"] == total_name)
    assert (rows[i + 1]["kind"], rows[i + 1]["name"]) == ("calculated", "As calculated")
    assert (rows[i + 2]["kind"], rows[i + 2]["name"]) == ("check", "Check (calculated - reported)")
    return rows[i + 1], rows[i + 2]


def test_standard_layout_has_the_calculated_totals_and_the_checks():
    import json
    layout = xlsx_export.standard_layout(ROWS)
    json.dumps(layout)                                                   # plain data
    assert layout["years"] == [2023, 2024] and [s["name"] for s in layout["sheets"]] == ["Balance Sheet", "Income Statement", "Notes & Others"]
    sheet = layout["sheets"][0]
    assert (sheet["statement"], sheet["scope"], sheet["style"]) == ("Balance Sheet", "STANDALONE", "balance")
    r = _rows_by_name(sheet)
    assert r["ASSETS"]["kind"] == "banner" and r["Non-current Assets:"]["kind"] == "header" and r["Property, Plant and Equipment"]["kind"] == "line"
    nca = r["Total Non-current Assets"]
    assert (nca["kind"], nca["calculated"], nca["depth"]) == ("total", True, 0)
    assert nca["values"] == {"2023": 60.0, "2024": 70.0}                      # the printed total is the main value (2023: only PP&E printed, it adds to 50)
    assert nca["parts"] == [{"series_id": 5, "sign": 1}, {"series_id": 6, "sign": 1}] and nca["calculated_years"] == [2023, 2024]
    assert nca["calculated_only"] == [] and nca["reported_only"] == []
    calc_row, chk = _under(sheet, "Total Non-current Assets")
    assert calc_row["values"] == {"2023": 50.0, "2024": 70.0} and calc_row["of"] == 3
    assert chk["values"] == {"2023": -10.0, "2024": 0.0} and chk["ok"] == {"2023": False, "2024": True} and chk["of"] == 3
    assert r["Current Assets"]["kind"] == "line" and r["Total Assets"]["restated"] == {"2024": 118.0}
    bc = r["Check: Total Assets - Total Owner's Equity and Liabilities (0 when it balances; up to 5 units = rounding)"]
    assert bc["kind"] == "balance_check" and bc["refs"] == {"assets": 2, "funding": 7}
    assert bc["values"] == {"2023": 0.0, "2024": 0.0} and bc["ok"] == {"2023": True, "2024": True}      # the printed totals balance
    assert xlsx_export.layout_checks(layout) == {"total": 10, "off": 3}
    assert [x["kind"] for x in sheet["rows"] if x["kind"] == "gap"] and sheet["rows"][-2]["name"] == "PER SHARE DATA"


def test_standard_layout_rows_match_the_standard_workbook_line_by_line():
    layout = xlsx_export.standard_layout(ROWS)
    wb = _std(ROWS)
    assert wb.sheetnames == [s["name"] for s in layout["sheets"]]
    for ws, sheet in zip(wb.worksheets, layout["sheets"]):
        assert [(x["name"] or None) for x in sheet["rows"]] == [ws.cell(row=r, column=1).value for r in range(2, ws.max_row + 1)]
        assert [x["depth"] for x in sheet["rows"] if x["kind"] != "gap"] == [
            ws.cell(row=r, column=1).alignment.indent for r in range(2, ws.max_row + 1) if ws.cell(row=r, column=1).value]



def test_a_year_with_parts_but_no_printed_total_is_calculated_only():
    rows = [_row(1, "Total Assets", {2023: 100}, comps=[2, 3], agg="GRAND_TOTAL"),
            _row(2, "Cash", {2023: 60, 2024: 70}), _row(3, "Bank", {2023: 40, 2024: 30})]
    r = _rows_by_name(xlsx_export.standard_layout(rows)["sheets"][0])
    sheet0 = xlsx_export.standard_layout(rows)["sheets"][0]
    assert r["Total Assets"]["values"] == {"2023": 100.0, "2024": 100.0}          # 2024: no report prints a total, the calculated figure stands in
    assert r["Total Assets"]["calculated_years"] == [2023, 2024] and r["Total Assets"]["calculated_only"] == [2024]
    calc_row, chk = _under(sheet0, "Total Assets")
    assert calc_row["values"] == {"2023": 100.0, "2024": 100.0}
    assert chk["values"] == {"2023": 0.0, "2024": None} and chk["ok"] == {"2023": True}          # nothing to check against in 2024
    assert xlsx_export.layout_checks(xlsx_export.standard_layout(rows)) == {"total": 1, "off": 0}
    ws = _std(rows)["Balance Sheet"]                                     # in the file the calculated-only cell is blue (a formula to "As calculated"), the others grey
    r = _by_name(ws)["Total Assets"]
    assert ws.cell(row=r, column=3).fill.fgColor.rgb.endswith("EDEFF2") and ws.cell(row=r, column=3).value == 100
    assert ws.cell(row=r, column=4).fill.fgColor.rgb.endswith("BDD7EE") and "no total for this year" in ws.cell(row=r, column=4).comment.text
    assert ws.cell(row=r, column=4).value == f"=D{r + 1}"


def test_a_year_where_no_part_has_a_figure_shows_the_printed_total():
    # 2023: only the total is printed (the older report has no breakdown); 2024: parts and total. 2022: nothing at all.
    rows = [_row(1, "Total Assets", {2023: 100, 2024: 100}, comps=[2, 3], agg="GRAND_TOTAL", restated={"2023": {"original_value": 90.0}, "2022": {"original_value": 5.0}}),
            _row(2, "Cash", {2024: 60}), _row(3, "Bank", {2024: 40}),
            _row(4, "Total Liabilities", {2023: 80, 2024: 80}, comps=[5], agg="GRAND_TOTAL"), _row(5, "Loans", {2024: 80})]
    rows[0]["values"]["2022"] = None
    layout = xlsx_export.standard_layout(rows, years=[2022, 2023, 2024])
    r = _rows_by_name(layout["sheets"][0])
    ta = r["Total Assets"]
    assert ta["values"] == {"2022": None, "2023": 100.0, "2024": 100.0}          # 2023 is the printed total, no longer empty
    assert ta["reported_only"] == [2023] and ta["calculated_years"] == [2024] and ta["calculated_only"] == []
    calc_row, chk = _under(layout["sheets"][0], "Total Assets")
    assert calc_row["values"] == {"2022": None, "2023": None, "2024": 100.0}      # nothing to add up in 2023
    assert chk["values"] == {"2022": None, "2023": None, "2024": 0.0} and chk["ok"] == {"2024": True}
    # only 2024 has parts: 2 total checks (2 totals x 1 year) ok; the 2 off are the balance check (100 vs 80, 2023 and 2024)
    assert xlsx_export.layout_checks(layout) == {"total": 4, "off": 2}
    assert ta["restated"] == {"2023": 90.0}                                        # not 2022: that cell has no figure
    ws = _std(rows)["Balance Sheet"]                                              # in the file: the printed total, nothing to add up in 2023
    n = _by_name(ws)["Total Assets"]
    assert ws.cell(row=n, column=3).value is None and ws.cell(row=n, column=4).value == 100
    assert ws.cell(row=n + 1, column=4).value is None and str(ws.cell(row=n + 1, column=5).value).startswith("=")
    assert ws.cell(row=n, column=4).fill.fgColor.rgb.endswith("FFF3C4") or ws.cell(row=n, column=4).fill.fgColor.rgb.endswith("EDEFF2")


def _with_last_reports(row, last):
    """The components of `row` with the report that last listed each part: {series_id: "2024-25"}."""
    for c in row["breakdown"]["components"]:
        if c["series_id"] in last:
            c["last_report"] = last[c["series_id"]]
    return row


def _tie_rows(nca_last="2024-25", assets_last="2018-19"):
    """The CROWNCEMNT shape: Total Assets (first in the rows) and Total Non-Current Assets both list Intangible Assets (4) and
    Investment in Associates (5) and both have 3 parts: a tie. An old report printed the two outside the non-current subtotal."""
    assets = _with_last_reports(_row(1, "Total Assets", {2024: 100}, comps=[2, 4, 5], agg="GRAND_TOTAL"), {4: assets_last, 5: assets_last})
    nca = _with_last_reports(_row(2, "Total Non-Current Assets", {2024: 100}, comps=[3, 4, 5], agg="SUBTOTAL"), {4: nca_last, 5: nca_last})
    return [assets, nca, _row(3, "Property", {2024: 80}), _row(4, "Intangible Assets", {2024: 12}), _row(5, "Investment in Associates", {2024: 8})]


def test_a_tie_between_two_totals_goes_to_the_one_that_lists_the_line_in_the_newest_report():
    sheet = xlsx_export.standard_layout(_tie_rows())["sheets"][0]
    r = _rows_by_name(sheet)
    nca = r["Total Non-Current Assets"]
    assert [p["series_id"] for p in nca["parts"]] == [3, 4, 5]                      # the two lines are under the subtotal that lists them now ...
    assert nca["values"] == {"2024": 100.0}
    assert _under(sheet, "Total Non-Current Assets")[1]["values"] == {"2024": 0.0}
    assert [p["series_id"] for p in r["Total Assets"]["parts"]] == [2]               # ... and not under Total Assets
    assert r["Total Assets"]["values"] == {"2024": 100.0}
    assert _under(sheet, "Total Assets")[1]["values"] == {"2024": 0.0}
    names = [x["name"] for x in xlsx_export.standard_layout(_tie_rows())["sheets"][0]["rows"] if x["kind"] in ("line", "total")]
    assert names.index("Intangible Assets") < names.index("Total Non-Current Assets")   # printed above its total, as a part of it


def test_a_tie_with_the_same_or_no_last_report_still_goes_by_row_order():
    same = _tie_rows(nca_last="2024-25", assets_last="2024-25")
    none = _tie_rows()
    for r in none[:2]:
        for c in r["breakdown"]["components"]:
            c.pop("last_report", None)
    for rows in (same, none):
        t = _rows_by_name(xlsx_export.standard_layout(rows)["sheets"][0])
        assert [p["series_id"] for p in t["Total Assets"]["parts"]] == [2, 4, 5]     # the earlier row keeps the lines, as before
        assert [p["series_id"] for p in t["Total Non-Current Assets"]["parts"]] == [3]


def test_the_newest_report_wins_even_over_a_total_with_fewer_parts():
    # After a re-merge Total Assets has FEWER parts (4) than Total Non-Current Assets (5) but lists the lines only in an old report.
    assets = _with_last_reports(_row(1, "Total Assets", {2024: 150}, comps=[2, 4, 5, 6], agg="GRAND_TOTAL"), {4: "2018-19", 5: "2018-19"})
    nca = _with_last_reports(_row(2, "Total Non-Current Assets", {2024: 100}, comps=[3, 4, 5, 7, 8], agg="SUBTOTAL"), {4: "2024-25", 5: "2024-25"})
    rows = [assets, nca, _row(3, "Property", {2024: 70}), _row(4, "Intangible Assets", {2024: 12}), _row(5, "Investment in Associates", {2024: 8}),
            _row(6, "Cash", {2024: 50}), _row(7, "Goodwill", {2024: 6}), _row(8, "Deferred Tax Asset", {2024: 4})]
    sheet = xlsx_export.standard_layout(rows)["sheets"][0]
    t = _rows_by_name(sheet)
    assert [p["series_id"] for p in t["Total Non-Current Assets"]["parts"]] == [3, 4, 5, 7, 8]
    assert [p["series_id"] for p in t["Total Assets"]["parts"]] == [2, 6]
    assert _under(sheet, "Total Non-Current Assets")[1]["values"] == {"2024": 0.0}
    assert _under(sheet, "Total Assets")[1]["values"] == {"2024": 0.0}


def test_the_same_newest_report_falls_back_to_the_fewest_parts():
    # "Total Liabilities" and "Total Equity and Liabilities" both list Loans in the same report: the smaller total keeps the line.
    funding = _with_last_reports(_row(1, "Total Equity and Liabilities", {2024: 150}, comps=[2, 3, 4, 5], agg="GRAND_TOTAL"), {5: "2024-25"})
    liabilities = _with_last_reports(_row(2, "Total Liabilities", {2024: 90}, comps=[5, 6], agg="SUBTOTAL"), {5: "2024-25"})
    rows = [funding, liabilities, _row(3, "Share Capital", {2024: 40}), _row(4, "Retained Earnings", {2024: 20}),
            _row(5, "Loans", {2024: 60}), _row(6, "Payables", {2024: 30})]
    t = _rows_by_name(xlsx_export.standard_layout(rows)["sheets"][0])
    assert [p["series_id"] for p in t["Total Liabilities"]["parts"]] == [5, 6]
    assert 5 not in [p["series_id"] for p in t["Total Equity and Liabilities"]["parts"]]


def test_a_total_adding_up_a_fallback_total_is_not_short():
    rows = [_row(1, "Total Assets", {2023: 150, 2024: 150}, comps=[2, 4], agg="GRAND_TOTAL"),
            _row(2, "Total Non-current Assets", {2023: 100, 2024: 100}, comps=[3], agg="SUBTOTAL"),
            _row(3, "Property", {2024: 100}), _row(4, "Cash", {2023: 50, 2024: 50})]
    sheet = xlsx_export.standard_layout(rows)["sheets"][0]
    r = _rows_by_name(sheet)
    assert r["Total Non-current Assets"]["reported_only"] == [2023]
    assert r["Total Assets"]["values"] == {"2023": 150.0, "2024": 150.0}
    calc_row, chk = _under(sheet, "Total Assets")
    assert calc_row["values"] == {"2023": 150.0, "2024": 150.0}                    # 2023: 100 (the child's printed total) + 50
    assert chk["values"] == {"2023": 0.0, "2024": 0.0}


def test_a_name_never_starts_with_a_lower_case_letter_in_the_standard_layout():
    rows = [_row(1, "total equity", {2024: 30}, comps=[2, 3], agg="GRAND_TOTAL"),
            _row(2, "shareholders' equity", {2024: 10}), _row(3, "distribution commission", {2024: 20})]
    names = [x["name"] for x in xlsx_export.standard_layout(rows)["sheets"][0]["rows"] if x["name"]]
    # only the first letter of the first word changes: the rest of every name is as merged
    assert {"Shareholders' equity", "Distribution commission", "Total equity"} <= set(names)
    assert not any(n[0].islower() for n in names)
    ws = _std(rows)["Balance Sheet"]
    assert "Shareholders' equity" in [ws.cell(row=r, column=1).value for r in range(2, ws.max_row + 1)]


def test_standard_layout_minus_parts_non_sum_totals_and_a_users_choice():
    rows = [
        _row(1, "Total Assets", {2024: 70}, comps=[2, 3], agg="GRAND_TOTAL"),
        _row(2, "Cash", {2024: 100}), _row(3, "Less: Provision", {2024: -30}),
        _row(4, "Total Ratio Line", {2024: 2}, comps=[5, 6], agg="SUBTOTAL"), _row(5, "Profit", {2024: 4}), _row(6, "Shares", {2024: 2}),
    ]
    rows[0]["breakdown"]["components"][1]["sign"] = -1
    rows[3]["breakdown"]["operation"] = "RATIO"
    rows[1]["overrides"] = {"2024": {"set_by": "Bob", "origin": "USER_ADDED"}}
    r = _rows_by_name(xlsx_export.standard_layout(rows)["sheets"][0])
    assert r["Total Assets"]["values"] == {"2024": 70.0}                       # the printed total
    assert _under(xlsx_export.standard_layout(rows)["sheets"][0], "Total Assets")[0]["values"] == {"2024": 70.0}   # 100 - |-30|: a deduction counts whichever way it is printed
    assert r["Total Assets"]["parts"][1] == {"series_id": 3, "sign": -1}
    assert r["Total Ratio Line"]["calculated"] is False and r["Total Ratio Line"]["values"] == {"2024": 2.0}
    rows_ = xlsx_export.standard_layout(rows)["sheets"][0]["rows"]
    i = next(k for k, x in enumerate(rows_) if x["name"] == "Total Ratio Line")
    assert all(x["kind"] != "calculated" for x in rows_[i + 1:i + 2])                                     # no "As calculated" row for a non-sum total
    assert r["Cash"]["chosen"] == {"2024": {"set_by": "Bob", "origin": "USER_ADDED"}}


def test_standard_layout_of_a_flow_statement_and_the_per_share_tolerance():
    layout = xlsx_export.standard_layout(IS_ROWS)
    (sheet,) = layout["sheets"]
    assert sheet["style"] == "flow"
    r = _rows_by_name(sheet)
    assert r["Gross Profit"]["values"] == {"2024": 40.0} and r["Net Profit"]["values"] == {"2024": 25.0}      # the printed figures (100-60, then 40-15)
    assert _under(sheet, "Net Profit")[0]["values"] == {"2024": 25.0}
    net_check = _under(sheet, "Net Profit")[1]
    assert net_check["tolerance"] == 5.0 and net_check["ok"] == {"2024": True}
    assert r["Earnings Per Share"]["kind"] == "line" and sheet["rows"][-1]["name"] == "Earnings Per Share"


def test_standard_results_endpoint(ocr, monkeypatch):
    import json
    from app.ocr import database
    from tests.test_ocr import KEY
    database.upsert_company("STDTAB", "Std Tab", "Misc")
    assert ocr.get("/ocr/company/STDTAB/results/standard", headers=KEY).status_code == 403          # locked until a merge has run
    with database.get_connection() as conn:
        conn.execute("INSERT INTO company_results (instrument_code, merged_rows_json, updated_at) VALUES (?, ?, ?)",
                     ("STDTAB", json.dumps(ROWS), "2026-11-11T00:00:00"))
        conn.commit()
    r = ocr.get("/ocr/company/STDTAB/results/standard", headers=KEY)
    assert r.status_code == 200
    body = r.json()
    assert body["instrument_code"] == "STDTAB" and body["years"] == [2023, 2024] and body["merged_stale"] is False
    assert [s["name"] for s in body["sheets"]] == ["Balance Sheet", "Income Statement", "Notes & Others"]
    assert body["checks"] == {"total": 10, "off": 3} and body["updated_at"].startswith("2026-11-11")
    # a row a user switched off is left out, as in the downloads
    monkeypatch.setattr(database, "get_inactive_series_ids", lambda code: {6})
    body = ocr.get("/ocr/company/STDTAB/results/standard", headers=KEY).json()
    assert "Intangible Assets" not in {x["name"] for x in body["sheets"][0]["rows"]}


def _with_by_year(row, by_year):
    row["breakdown"] = {**row["breakdown"], "by_year": by_year}
    return row


def test_a_year_is_added_up_from_the_breakdown_its_own_report_printed():
    """The union of the parts (Cash, Bank, Other) is what 2024 printed; the 2023 report printed only Cash + Bank + a line in no series
    (printed 5) + a line whose series is not on the sheet (printed 7). Each year is calculated from its own list; both checks are 0."""
    total = _row(1, "Total Assets", {2023: 112, 2024: 130}, comps=[2, 3, 4], agg="GRAND_TOTAL")
    _with_by_year(total, {
        "2023": {"report": "2023-24", "status": "MATCH", "components": [
            {"series_id": 2, "sign": 1, "value": 60}, {"series_id": 3, "sign": 1, "value": 40},
            {"series_id": None, "sign": 1, "value": 5, "field": "Printed only"}, {"series_id": 99, "sign": 1, "value": 7}]},
        "2024": {"report": "2024-25", "status": "MATCH", "components": [
            {"series_id": 2, "sign": 1, "value": 70}, {"series_id": 3, "sign": 1, "value": 30}, {"series_id": 4, "sign": 1, "value": 30}]}})
    rows = [total, _row(2, "Cash", {2023: 60, 2024: 70}), _row(3, "Bank", {2023: 40, 2024: 30}), _row(4, "Other", {2024: 30})]
    sheet = xlsx_export.standard_layout(rows)["sheets"][0]
    r = _rows_by_name(sheet)["Total Assets"]
    assert [p["series_id"] for p in r["parts"]] == [2, 3, 4]                                  # the union (the tree) is unchanged
    assert [p["series_id"] for p in r["parts_by_year"]["2023"]] == [2, 3, None, 99]            # the year's own printed list
    assert r["parts_by_year"]["2023"][2]["field"] == "Printed only"
    calc_row, chk = _under(sheet, "Total Assets")
    assert calc_row["values"] == {"2023": 112.0, "2024": 130.0}
    assert chk["values"] == {"2023": 0.0, "2024": 0.0} and chk["ok"] == {"2023": True, "2024": True}
    ws = _std(rows)["Balance Sheet"]                                                           # the formulas follow the same lists
    n = _by_name(ws)["Total Assets"]
    assert ws.cell(row=n + 1, column=3).value == f"=C{_by_name(ws)['Cash']}+C{_by_name(ws)['Bank']}+(5)+(7)"
    assert ws.cell(row=n + 1, column=4).value == f"=SUM(D{_by_name(ws)['Cash']},D{_by_name(ws)['Bank']},D{_by_name(ws)['Other']})"


def test_a_printed_year_whose_report_stored_no_breakdown_is_not_checked():
    """2023 is printed but its report stored no breakdown: no calculation, no check (nothing to compare with); 2024 has one."""
    total = _row(1, "Total Assets", {2023: 100, 2024: 100}, comps=[2, 3], agg="GRAND_TOTAL")
    _with_by_year(total, {"2024": {"report": "2024-25", "status": "MATCH", "components": [{"series_id": 2, "sign": 1, "value": 60}, {"series_id": 3, "sign": 1, "value": 40}]}})
    rows = [total, _row(2, "Cash", {2023: 60, 2024: 60}), _row(3, "Bank", {2023: 40, 2024: 40})]
    sheet = xlsx_export.standard_layout(rows)["sheets"][0]
    r = _rows_by_name(sheet)["Total Assets"]
    assert list(r["parts_by_year"]) == ["2024"] and r["values"] == {"2023": 100.0, "2024": 100.0}
    assert r["calculated_years"] == [2024] and r["reported_only"] == [2023]
    calc_row, chk = _under(sheet, "Total Assets")
    assert calc_row["values"] == {"2023": None, "2024": 100.0}
    assert chk["values"] == {"2023": None, "2024": 0.0} and chk["ok"] == {"2024": True}          # no check for 2023 at all
    assert xlsx_export.layout_checks(xlsx_export.standard_layout(rows)) == {"total": 1, "off": 0}
    ws = _std(rows)["Balance Sheet"]
    n = _by_name(ws)["Total Assets"]
    assert ws.cell(row=n + 1, column=3).value is None and ws.cell(row=n + 2, column=3).value is None      # blank in the file too
    assert ws.cell(row=n + 2, column=4).value is not None


def test_a_stored_breakdown_that_does_not_add_up_keeps_its_check_and_a_total_with_none_has_no_rows():
    mism = _row(1, "Total Assets", {2023: 100}, comps=[2, 3], agg="GRAND_TOTAL")
    _with_by_year(mism, {"2023": {"report": "2023-24", "status": "MISMATCH"}})           # stored, but does not add up: the union is checked
    rows = [mism, _row(2, "Cash", {2023: 60}), _row(3, "Bank", {2023: 30})]
    sheet = xlsx_export.standard_layout(rows)["sheets"][0]
    assert _under(sheet, "Total Assets")[1]["values"] == {"2023": -10.0}
    assert xlsx_export.layout_checks(xlsx_export.standard_layout(rows)) == {"total": 1, "off": 1}

    none = _with_by_year(_row(1, "Total Assets", {2023: 100, 2024: 100}, comps=[2, 3], agg="GRAND_TOTAL"), {})      # no year has a breakdown
    rows = [none, _row(2, "Cash", {2023: 60, 2024: 60}), _row(3, "Bank", {2023: 40, 2024: 40})]
    layout = xlsx_export.standard_layout(rows)
    kinds = [x["kind"] for x in layout["sheets"][0]["rows"]]
    assert "calculated" not in kinds and "check" not in kinds
    assert _rows_by_name(layout["sheets"][0])["Total Assets"]["calculated"] is False
    assert xlsx_export.layout_checks(layout) == {"total": 0, "off": 0}


def test_a_year_no_report_prints_a_total_for_is_still_calculated_when_the_row_has_per_year_breakdowns():
    total = _row(1, "Total Assets", {2023: 100}, comps=[2, 3], agg="GRAND_TOTAL")
    _with_by_year(total, {"2023": {"report": "2023-24", "status": "MATCH", "components": [{"series_id": 2, "sign": 1, "value": 60}, {"series_id": 3, "sign": 1, "value": 40}]}})
    rows = [total, _row(2, "Cash", {2023: 60, 2024: 70}), _row(3, "Bank", {2023: 40, 2024: 30})]
    r = _rows_by_name(xlsx_export.standard_layout(rows)["sheets"][0])["Total Assets"]
    assert r["calculated_only"] == [2024] and r["values"]["2024"] == 100.0


def test_the_balance_check_never_takes_a_per_share_total_as_the_assets_total():
    """BSRMLTD: "Net Asset Value Per Share" has parts and says "asset"; the check must still use Total Assets / Total Liabilities."""
    rows = [_row(1, "Net Asset Value Per Share", {2024: 13.6}, comps=[2], unit="BDT/share"), _row(2, "Equity Per Share Part", {2024: 13.6}, unit="BDT/share"),
            _row(3, "Total Assets", {2024: 100}, comps=[4, 5], agg="GRAND_TOTAL"), _row(4, "Cash", {2024: 60}), _row(5, "Bank", {2024: 40}),
            _row(6, "Total Equity and Liabilities", {2024: 100}, comps=[7, 8], agg="GRAND_TOTAL"), _row(7, "Equity", {2024: 70}), _row(8, "Loans", {2024: 30})]
    sheet = xlsx_export.standard_layout(rows)["sheets"][0]
    (bal,) = [r for r in sheet["rows"] if r["kind"] == "balance_check"]
    assert bal["refs"] == {"assets": 3, "funding": 6} and bal["values"] == {"2024": 0.0} and bal["ok"] == {"2024": True}


def test_a_check_of_a_year_whose_printed_breakdown_does_not_add_up_carries_the_reason():
    total = _row(1, "Total Assets", {2023: 100, 2024: 100}, comps=[2, 3], agg="GRAND_TOTAL")
    _with_by_year(total, {"2023": {"report": "2023-24", "status": "MISMATCH"},
                          "2024": {"report": "2024-25", "status": "MATCH", "components": [{"series_id": 2, "sign": 1, "value": 60}, {"series_id": 3, "sign": 1, "value": 40}]}})
    rows = [total, _row(2, "Cash", {2023: 60, 2024: 60}), _row(3, "Bank", {2023: 30, 2024: 40})]
    sheet = xlsx_export.standard_layout(rows)["sheets"][0]
    chk = _under(sheet, "Total Assets")[1]
    assert set(chk["notes"]) == {"2023"} and "2023-24" in chk["notes"]["2023"] and "does not add up" in chk["notes"]["2023"]
    assert chk["ok"] == {"2023": False, "2024": True}
    ws = _std(rows)["Balance Sheet"]
    n = _by_name(ws)["Total Assets"]
    assert "does not add up" in ws.cell(row=n + 2, column=3).comment.text and ws.cell(row=n + 2, column=4).comment is None


def test_a_difference_of_up_to_five_units_is_rounding_and_more_is_not():
    def check_of(diff):
        rows = [_row(1, "Total Assets", {2023: 100}, comps=[2, 3], agg="GRAND_TOTAL"), _row(2, "Cash", {2023: 60}), _row(3, "Bank", {2023: 40 + diff})]
        chk = _under(xlsx_export.standard_layout(rows)["sheets"][0], "Total Assets")[1]
        return chk["tolerance"], chk["ok"]["2023"]
    assert check_of(5) == (5.0, True) and check_of(-5) == (5.0, True)
    assert check_of(5.5) == (5.0, False) and check_of(6) == (5.0, False)
