"""
engine/preview.py — template-less file preview for the onboarding
field-mapping wizard (phase R4).

Given a raw file, detect the most plausible header row and return the column
names plus the first few data rows, so an admin can map columns to canonical
fields before any template exists.
"""

import io
import base64

import pandas as pd

_SCAN_ROWS = 30
_SAMPLE_ROWS = 5


def preview_file(content_b64: str, file_format: str) -> dict:
    raw = base64.b64decode(content_b64)

    if file_format == "csv":
        df = pd.read_csv(io.BytesIO(raw))
        header_row = 0
    elif file_format in ("xlsx", "xls"):
        probe = pd.read_excel(io.BytesIO(raw), header=None, nrows=_SCAN_ROWS)
        header_row = _detect_header_row(probe)
        df = pd.read_excel(io.BytesIO(raw), header=header_row)
    else:
        raise ValueError(f"Unsupported file format for preview: '{file_format}'")

    headers = [str(c) for c in df.columns]
    sample = df.head(_SAMPLE_ROWS)
    rows = [
        ["" if pd.isna(v) else str(v) for v in row]
        for row in sample.itertuples(index=False, name=None)
    ]

    return {
        "headers": headers,
        "rows": rows,
        "header_row": header_row,
        "total_columns": len(headers),
        "row_count_sampled": len(rows),
    }


def _detect_header_row(probe: pd.DataFrame) -> int:
    """The header row is the one with the most non-empty, short, mostly
    non-numeric string cells — bank portals prepend titles/metadata above it."""
    best_row, best_score = 0, -1
    for i in range(len(probe)):
        cells = probe.iloc[i].tolist()
        score = 0
        for cell in cells:
            if pd.isna(cell):
                continue
            text = str(cell).strip()
            if not text or len(text) > 60:
                continue
            if _is_number(text):
                score -= 1  # data rows are numeric-heavy
            else:
                score += 1
        if score > best_score:
            best_row, best_score = i, score
    return best_row


def _is_number(text: str) -> bool:
    try:
        float(text.replace(",", ""))
        return True
    except ValueError:
        return False