"""routes_uploads.py — ONE tabular-file preview endpoint (wave 21, item 11 / C5, ruling R10).

    POST /api/v1/uploads/tabular   multipart `file` = .xlsx | .csv, <= 5 MB
      -> {"columns": [str], "rows": int, "values": {col: [str, ...]}, "truncated": bool}

The grid's "Select from file..." dialog uploads a spreadsheet, offers its columns, and matches
the chosen column's VALUES against a grid column client-side (exact match after trim+casefold;
the matches become the grid selection, which feeds the existing bulk actions). This route's
whole job is honest parsing:

  * FIRST sheet only (xlsx). Silently unioning sheets would "find" rows the user can see are
    not on the sheet they meant.
  * Every cell STRINGIFIED + trimmed. Matching is string matching by contract, so the wire
    carries strings, never spreadsheet cell types.
  * <= 20,000 data rows, and the cap is DISCLOSED (`truncated: true`), never silent
    ([[no-unverifiable-aggregates]] — a silently clipped file would report "6 not found"
    against a tail that was never read).
  * Fully blank rows are padding, not data — skipped without counting.
  * The PASTE path never reaches the server at all.

⛔ openpyxl is imported INSIDE the handler, deliberately. `verify_no_streamlit` boots the whole
API with openpyxl blocked at sys.meta_path — the API must serve every non-upload route without
it — and the manifest half of that gate now asserts the package IS declared in both
requirements files ([[pin-deps-space-rebuilds]]): absent there, the first upload on a fresh
container is a 500.
"""
import csv
import io

from fastapi import APIRouter, Depends, File, UploadFile

from deps import Session, err, require_session

router = APIRouter(prefix="/api/v1")

MAX_BYTES = 5 * 1024 * 1024
MAX_ROWS = 20_000


def _cell(v):
    return "" if v is None else str(v).strip()


def _headers(row):
    """Stringified, blank-filled, DEDUPED header names — two 'SKU' columns must not silently
    merge their values into one list."""
    out, seen = [], {}
    for i, cell in enumerate(row):
        name = _cell(cell) or f"Column {i + 1}"
        n = seen.get(name, 0)
        seen[name] = n + 1
        out.append(name if n == 0 else f"{name} ({n + 1})")
    return out


@router.post("/uploads/tabular")
async def tabular_preview(file: UploadFile = File(...),
                          session: Session = Depends(require_session)):
    name = (file.filename or "").lower()
    data = await file.read()
    if len(data) > MAX_BYTES:
        raise err(400, "too_large",
                  "that file is over 5 MB — export just the column you need and retry")
    if name.endswith(".csv"):
        text = data.decode("utf-8-sig", errors="replace")
        rows = iter(csv.reader(io.StringIO(text)))
    elif name.endswith(".xlsx"):
        try:
            import openpyxl                    # lazy BY CONTRACT — see the module docstring
            wb = openpyxl.load_workbook(io.BytesIO(data), read_only=True, data_only=True)
            ws = wb.worksheets[0]
        except Exception:
            raise err(400, "bad_file", "that workbook could not be read — is it a real .xlsx?")
        rows = ws.iter_rows(values_only=True)
    else:
        raise err(400, "bad_type",
                  "upload a .xlsx or .csv file (or paste the values into the dialog instead)")

    try:
        first = next(iter(rows))
    except StopIteration:
        raise err(400, "empty_file", "that file has no rows")
    except Exception:
        raise err(400, "bad_file", "that file could not be read")
    columns = _headers(list(first))
    values = {c: [] for c in columns}
    count, truncated = 0, False
    for row in rows:
        cells = list(row)
        if not any(_cell(c) for c in cells):
            continue
        if count >= MAX_ROWS:
            truncated = True
            break
        count += 1
        for i, col in enumerate(columns):
            values[col].append(_cell(cells[i]) if i < len(cells) else "")
    return {"columns": columns, "rows": count, "values": values, "truncated": truncated}