"""Sheet parser tests — label/blank/zero skipping, header month match, trimming."""

from __future__ import annotations

from datetime import date

from app.matching.sheet_parser import parse_planned_lines
from tests.conftest import build_workbook


def test_keeps_valid_rows_and_reads_value_column(tmp_path):
    path = build_workbook(
        tmp_path,
        [
            (2, "Baskit (Duke)", 480000, 40000),
            (3, "Rio Africa Portal", 600000, 50000),
        ],
    )
    lines = parse_planned_lines(path, 2026, 10)
    assert [l.client_name for l in lines] == ["Baskit (Duke)", "Rio Africa Portal"]
    assert lines[0].planned_amount == 40000
    assert lines[0].project_value == 480000
    assert lines[0].source_row == 2


def test_skips_labels_blanks_and_zero_amounts(tmp_path):
    path = build_workbook(
        tmp_path,
        [
            (2, "Real Project", 120000, 10000),
            (3, "SLA Total for each month", None, 123456),  # known label
            (4, "Grand Total", None, 1500000),  # known label
            (5, "Total Projects", None, 999),  # starts with "Total"
            (6, "target", None, 900000),  # case-insensitive label
            (7, None, None, 7777),  # blank name
            (8, "Dormant Project", 90000, 0),  # zero month amount
            (9, "Blank Month Project", 90000, None),  # blank month amount
            (10, "Kept Project", 50000, 5000),
        ],
    )
    lines = parse_planned_lines(path, 2026, 10)
    assert [l.client_name for l in lines] == ["Real Project", "Kept Project"]


def test_trims_and_collapses_internal_newlines(tmp_path):
    path = build_workbook(
        tmp_path,
        [(2, "  Summit HR Portal\n(phase 2)  ", 264000, 22000)],
    )
    lines = parse_planned_lines(path, 2026, 10)
    assert lines[0].client_name == "Summit HR Portal (phase 2)"


def test_locates_month_by_header_not_index(tmp_path):
    # Request September -> must pick the Sept column, not a fixed offset.
    path = build_workbook(
        tmp_path,
        [(2, "Proj", 120000, 10000)],
    )
    # Oct amount is 10000; Sept/Nov cells are blank in build_workbook, so asking
    # for Oct returns the value and asking for a blank month returns nothing.
    oct_lines = parse_planned_lines(path, 2026, 10)
    assert oct_lines[0].planned_amount == 10000
    sept_lines = parse_planned_lines(path, 2026, 9)
    assert sept_lines == []  # Sept column is blank for this row -> skipped


def test_accepts_text_month_headers(tmp_path):
    path = build_workbook(
        tmp_path,
        [(2, "Proj", 120000, 10000)],
        months=[date(2026, 9, 1), date(2026, 10, 1), date(2026, 11, 1)],
    )
    # Overwrite the Oct header with text form "Oct-26".
    from openpyxl import load_workbook

    wb = load_workbook(path)
    ws = wb["projects"]
    ws.cell(row=1, column=8, value="Oct-26")  # column H == Oct
    wb.save(path)

    lines = parse_planned_lines(path, 2026, 10)
    assert lines and lines[0].planned_amount == 10000


def test_ignores_rows_beyond_138(tmp_path):
    path = build_workbook(
        tmp_path,
        [
            (2, "In Scope", 120000, 10000),
            (140, "EDI Client Alpha", None, 88888),  # out of scope tail
        ],
    )
    lines = parse_planned_lines(path, 2026, 10)
    assert [l.client_name for l in lines] == ["In Scope"]