"""
Parse CSV/XLSX for import_data. Maps column aliases to canonical fields per plan §3.3.
"""
import io
import logging
from typing import Any

import pandas as pd

logger = logging.getLogger(__name__)

# Aliases: first key is canonical name used in DB/API, values are accepted headers
LEAD_ALIASES = {"full_name": ["full_name", "name"], "note": ["note", "notes"]}
CUSTOMER_ALIASES = {
    "full_name": ["full_name", "name"],
    "customer_type": ["customer_type", "type"],
    "notes": ["notes", "note"],
}
CONTACT_ALIASES = {"full_name": ["full_name", "name"]}
LINE_ALIASES = {"line_no": ["line_no", "line"]}


def _normalize_headers(df: pd.DataFrame, alias_map: dict) -> pd.DataFrame:
    """Rename columns to canonical names using alias_map."""
    out = df.copy()
    for canonical, aliases in alias_map.items():
        for a in aliases:
            if a in out.columns and canonical not in out.columns:
                out = out.rename(columns={a: canonical})
                break
    return out


def parse_leads_file(content: bytes, filename: str) -> list[dict[str, Any]]:
    """Parse CSV or XLSX into list of lead dicts. Keys: full_name, email, phone, company, title, note."""
    df = _read_file(content, filename)
    df = _normalize_headers(df, LEAD_ALIASES)
    return df.replace({pd.NA: None}).to_dict("records")


def parse_customers_file(content: bytes, filename: str) -> list[dict[str, Any]]:
    """Parse CSV or XLSX into list of customer dicts. Keys: full_name, title, email, phone, company, customer_type, notes."""
    df = _read_file(content, filename)
    df = _normalize_headers(df, CUSTOMER_ALIASES)
    return df.replace({pd.NA: None}).to_dict("records")


def parse_contacts_file(content: bytes, filename: str) -> list[dict[str, Any]]:
    """Parse CSV or XLSX into list of contact dicts. Keys: full_name, email, phone, company, title."""
    df = _read_file(content, filename)
    df = _normalize_headers(df, CONTACT_ALIASES)
    return df.replace({pd.NA: None}).to_dict("records")


def _read_file(content: bytes, filename: str) -> pd.DataFrame:
    """Read CSV or XLSX into DataFrame."""
    name = (filename or "").lower()
    if name.endswith(".xlsx") or name.endswith(".xls"):
        return pd.read_excel(io.BytesIO(content), engine="openpyxl")
    return pd.read_csv(io.BytesIO(content), encoding="utf-8-sig")


def parse_quotations_file(content: bytes, filename: str) -> list[dict[str, Any]]:
    """
    Parse CSV or XLSX into list of quotation dicts.
    Each item: { quotation_number, customer, amount, status?, issue_date?, valid_until?, line_items: [ {line_no, product, product_sku?, qty, unit_price, line_amount?, category?} ] }
    Expects either one row per quotation with embedded line cols (e.g. line1_product, line1_qty...)
    or separate sheets/tables. For simplicity we support: one row per quotation, line columns named
    line_1_product, line_1_qty, ... or product_1, qty_1, ... or a single "line_items" JSON column.
    Minimal format: quotation_number, customer, amount per row; lines can be empty.
    """
    df = _read_file(content, filename)
    df = df.replace({pd.NA: None})
    rows = []
    for _, r in df.iterrows():
        rec = {
            "quotation_number": str(r.get("quotation_number") or "").strip() or None,
            "customer": (r.get("customer") or "") if pd.notna(r.get("customer")) else "",
            "amount": float(r.get("amount") or 0),
            "status": (str(r.get("status") or "pending").strip().lower()),
            "issue_date": r.get("issue_date"),
            "valid_until": r.get("valid_until"),
        }
        lines = []
        # detect line columns: line_1_product, line_1_qty, ... or product, qty, line_no in repeating blocks
        for k in list(r.keys()):
            if k is None:
                continue
            sk = str(k).lower()
            if sk in ("product", "product_sku", "qty", "unit_price", "line_amount", "category", "line", "line_no"):
                idx = 0
                line = {"line_no": len(lines) + 1, "product": r.get("product"), "product_sku": r.get("product_sku"),
                        "qty": float(r.get("qty") or 0), "unit_price": float(r.get("unit_price") or 0),
                        "line_amount": float(r.get("line_amount") or 0), "category": r.get("category")}
                if line["product"] or line["qty"] or line["unit_price"]:
                    lines.append(line)
                break
        # try block columns line_1_*, line_2_*, ...
        i = 1
        while True:
            p = r.get(f"line_{i}_product") or r.get(f"product_{i}")
            if p is None and i > 1:
                break
            qty = float(r.get(f"line_{i}_qty") or r.get(f"qty_{i}") or 0)
            up = float(r.get(f"line_{i}_unit_price") or r.get(f"unit_price_{i}") or 0)
            la = float(r.get(f"line_{i}_line_amount") or r.get(f"line_amount_{i}") or (qty * up))
            lines.append({
                "line_no": i,
                "product": p,
                "product_sku": r.get(f"line_{i}_product_sku") or r.get(f"product_sku_{i}"),
                "qty": qty,
                "unit_price": up,
                "line_amount": la,
                "category": r.get(f"line_{i}_category") or r.get(f"category_{i}"),
            })
            i += 1
        rec["line_items"] = lines if lines else []
        rows.append(rec)
    return rows


def parse_billings_file(content: bytes, filename: str) -> list[dict[str, Any]]:
    """Like quotations but with invoice_number, invoice_date, due_date, status (pending|paid|overdue|cancelled)."""
    df = _read_file(content, filename)
    df = df.replace({pd.NA: None})
    rows = []
    for _, r in df.iterrows():
        rec = {
            "invoice_number": str(r.get("invoice_number") or r.get("invoice_number") or "").strip(),
            "customer": r.get("customer") or "",
            "amount": float(r.get("amount") or 0),
            "status": (r.get("status") or "pending").lower(),
            "invoice_date": r.get("invoice_date"),
            "due_date": r.get("due_date"),
        }
        lines = []
        i = 1
        while True:
            p = r.get(f"line_{i}_product") or r.get(f"product_{i}")
            if p is None and i > 1:
                break
            qty = float(r.get(f"line_{i}_qty") or r.get(f"qty_{i}") or 0)
            up = float(r.get(f"line_{i}_unit_price") or r.get(f"unit_price_{i}") or 0)
            la = float(r.get(f"line_{i}_line_amount") or r.get(f"line_amount_{i}") or (qty * up))
            lines.append({
                "line_no": i,
                "product": p,
                "product_sku": r.get(f"line_{i}_product_sku") or r.get(f"product_sku_{i}"),
                "qty": qty,
                "unit_price": up,
                "line_amount": la,
                "category": r.get(f"line_{i}_category") or r.get(f"category_{i}"),
            })
            i += 1
        rec["line_items"] = lines
        rows.append(rec)
    return rows