from collections import defaultdict
from datetime import datetime, timezone
import csv
from difflib import SequenceMatcher
import io
import re
import unicodedata

from fastapi import APIRouter, Body, Depends, HTTPException, Query, Response, status
from fastapi.responses import StreamingResponse
from sqlalchemy import case, select
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session, selectinload

from app.api.deps import (
    get_db,
    get_db_from_query_token,
    require_management_query_token,
    require_management_token,
    require_query_token,
    require_token,
)
from app.core.config import get_settings
from app.core.tenancy import AuthContext, authenticate_login
from app.models.order_batch import OrderBatch
from app.models.order_item import OrderItem
from app.models.product import Product
from app.models.seasonal_goal import SeasonalGoal
from app.models.shared_note import SharedNote
from app.models.suspended_order import SuspendedOrder
from app.schemas.common import (
    GoalUpsert,
    LoginRequest,
    OrderCreateRequest,
    OrderDeleteRequest,
    OrderUpdateRequest,
    ProductUpsert,
    SharedNoteCreate,
    SupplierCatalogPreviewRequest,
    SupplierCatalogRow,
)


router = APIRouter()
protected = APIRouter(dependencies=[Depends(require_management_token)])


def _utc_year_bounds(year: int) -> tuple[datetime, datetime]:
    start = datetime(year, 1, 1, tzinfo=timezone.utc)
    end = datetime(year + 1, 1, 1, tzinfo=timezone.utc)
    return start, end


def _format_iso(value: datetime | None) -> str | None:
    if value is None:
        return None
    if value.tzinfo is None:
        value = value.replace(tzinfo=timezone.utc)
    return value.isoformat()


def _serialize_product(product: Product) -> dict[str, object]:
    return {
        "id": product.id,
        "product_name": product.product_name,
        "lot_code": product.lot_code,
        "supplier_name": product.supplier_name,
        "product_code": product.product_code,
        "final_price_vat": product.final_price_vat,
        "vat_rate": product.vat_rate,
        "weight_kg": product.weight_kg,
        "unit_price_per_kg": product.unit_price_per_kg,
        "category": product.category,
        "notes": product.notes,
        "units_per_pack": product.units_per_pack,
        "liters_per_unit": product.liters_per_unit,
    }


def _estimate_line_total(
    quantity: int,
    final_price_vat: float | None,
    *,
    weight_kg: float | None = None,
    unit_price_per_kg: float | None = None,
    vat_rate: float | None = None,
) -> float | None:
    if final_price_vat is not None and final_price_vat > 0:
        return round(float(quantity) * float(final_price_vat), 2)
    if unit_price_per_kg is None or unit_price_per_kg <= 0:
        return None
    if weight_kg is None or weight_kg <= 0:
        return None
    unit_total = float(unit_price_per_kg) * float(weight_kg)
    if vat_rate is not None and vat_rate > 0:
        unit_total *= 1 + (float(vat_rate) / 100)
    return round(float(quantity) * unit_total, 2)


def _resolved_item_line_total(item: OrderItem) -> float | None:
    if item.estimated_line_total is not None and item.estimated_line_total > 0:
        return round(float(item.estimated_line_total), 2)
    if item.final_price_vat_snapshot is not None and item.final_price_vat_snapshot > 0:
        return _estimate_line_total(item.quantity, item.final_price_vat_snapshot)
    product = item.product
    if product is None:
        return None
    return _estimate_line_total(
        item.quantity,
        product.final_price_vat,
        weight_kg=product.weight_kg,
        unit_price_per_kg=product.unit_price_per_kg,
        vat_rate=product.vat_rate,
    )


def _serialize_item(item: OrderItem) -> dict[str, object]:
    resolved_line_total = _resolved_item_line_total(item)
    return {
        "id": item.id,
        "product_id": item.product_id,
        "product_name": item.product_name,
        "lot_code": item.lot_code,
        "supplier_name": item.supplier_name,
        "quantity": item.quantity,
        "final_price_vat_snapshot": item.final_price_vat_snapshot,
        "estimated_line_total": resolved_line_total,
        "units_per_pack": item.units_per_pack,
        "liters_per_unit": item.liters_per_unit,
    }


def _serialize_batch(batch: OrderBatch) -> dict[str, object]:
    serialized_items = [_serialize_item(item) for item in batch.items]
    resolved_batch_total = batch.total_estimated_amount
    if resolved_batch_total is None or resolved_batch_total <= 0:
        line_totals = [float(item["estimated_line_total"]) for item in serialized_items if item.get("estimated_line_total") is not None]
        resolved_batch_total = round(sum(line_totals), 2) if line_totals else None
    return {
        "id": batch.id,
        "staff": batch.staff,
        "total_estimated_amount": resolved_batch_total,
        "confirmed_at": _format_iso(batch.confirmed_at),
        "items": serialized_items,
    }


def _extract_liters_from_text(value: str) -> float | None:
    normalized = (value or "").lower().replace(",", ".")
    for pattern, multiplier in (
        (r"(\d+(?:\.\d+)?)\s*(?:lt|l)\b", 1.0),
        (r"(\d+(?:\.\d+)?)\s*cl\b", 0.01),
        (r"(\d+(?:\.\d+)?)\s*ml\b", 0.001),
    ):
        match = re.search(pattern, normalized)
        if not match:
            continue
        try:
            amount = float(match.group(1))
        except ValueError:
            continue
        liters = amount * multiplier
        if liters > 0:
            return liters
    return None


def _resolve_liters_per_unit(product_name: str, lot_code: str, liters_per_unit: float | None) -> float | None:
    if liters_per_unit is not None and liters_per_unit > 0:
        return liters_per_unit
    for source in (lot_code, product_name):
        estimated = _extract_liters_from_text(source or "")
        if estimated is not None:
            return estimated
    return None


def _login_response(context: AuthContext) -> dict[str, object]:
    return {
        "success": True,
        "token": context.token,
        "staff": context.default_staff,
        "tenant_name": context.tenant_name,
        "tenant_slug": context.tenant_slug,
        "user_name": context.user_name or context.username or context.user_email,
    }


def _load_batches_for_year(year: int, db: Session) -> list[OrderBatch]:
    start, end = _utc_year_bounds(year)
    stmt = (
        select(OrderBatch)
        .where(OrderBatch.confirmed_at >= start, OrderBatch.confirmed_at < end)
        .options(selectinload(OrderBatch.items).selectinload(OrderItem.product))
        .order_by(OrderBatch.confirmed_at.desc(), OrderBatch.id.desc())
    )
    return list(db.scalars(stmt))


def _all_years(db: Session) -> list[int]:
    years = {
        (batch.confirmed_at.replace(tzinfo=timezone.utc) if batch.confirmed_at.tzinfo is None else batch.confirmed_at).year
        for batch in db.scalars(select(OrderBatch))
    }
    years.update(db.scalars(select(SeasonalGoal.year)))
    if not years:
        years.add(datetime.now(timezone.utc).year)
    return sorted(years, reverse=True)


def _normalize_staff(staff: str | None, fallback: str | None = None) -> str:
    value = (staff or fallback or get_settings().default_staff).strip().lower()
    return value or get_settings().default_staff


def _normalize_suspended_payload(payload: dict[str, int]) -> dict[str, int]:
    normalized: dict[str, int] = {}
    for key, raw_value in payload.items():
        try:
            quantity = int(raw_value)
        except (TypeError, ValueError):
            continue
        if quantity > 0:
            normalized[str(key)] = quantity
    return normalized


def _normalize_note_text(value: str) -> str:
    text = value.strip()
    if not text:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Il testo della nota e' obbligatorio")
    return text


def _normalize_product_identity(entry: ProductUpsert) -> tuple[str, str, str]:
    return (
        entry.product_name.strip(),
        entry.lot_code.strip(),
        entry.supplier_name.strip(),
    )


def _dedupe_product_payload(payload: list[ProductUpsert]) -> list[ProductUpsert]:
    deduped: dict[tuple[str, str, str], ProductUpsert] = {}
    for entry in payload:
        deduped[_normalize_product_identity(entry)] = entry
    return list(deduped.values())


def _apply_product_metadata(product: Product, entry: ProductUpsert) -> None:
    optional_fields = {
        "product_code": entry.product_code,
        "final_price_vat": entry.final_price_vat,
        "vat_rate": entry.vat_rate,
        "weight_kg": entry.weight_kg,
        "unit_price_per_kg": entry.unit_price_per_kg,
        "category": entry.category,
        "notes": entry.notes,
        "units_per_pack": entry.units_per_pack,
        "liters_per_unit": entry.liters_per_unit,
    }
    for field_name, value in optional_fields.items():
        if field_name in entry.model_fields_set:
            setattr(product, field_name, value)


def _normalize_catalog_text(value: str | None) -> str:
    normalized = unicodedata.normalize("NFKD", str(value or "")).encode("ascii", "ignore").decode("ascii")
    lowered = normalized.lower().replace("’", "'")

    def convert_centiliters(match: re.Match[str]) -> str:
        raw_number = match.group(1).replace(",", ".")
        try:
            liters = float(raw_number) / 100.0
        except ValueError:
            return match.group(0)
        rendered = f"{liters:.2f}".rstrip("0").rstrip(".")
        return f"{rendered}l"

    lowered = re.sub(r"(\d+(?:[.,]\d+)?)\s*cl\b", convert_centiliters, lowered)
    lowered = re.sub(r"[^a-z0-9]+", " ", lowered)
    return " ".join(lowered.split())


def _normalize_catalog_lot_code(value: str | None) -> str:
    normalized = _normalize_catalog_text(value).replace(" ", "")
    if not normalized:
        return ""
    if normalized in {"bt", "bottiglia", "bottiglie"} or normalized.startswith("bottigli"):
        return "bt"
    if normalized in {"ct", "cartone", "cartoni", "cassa", "casse"} or normalized.startswith("carton") or normalized.startswith("cass"):
        return "ct"
    if normalized in {"pz", "pezzo", "pezzi"} or normalized.startswith("pezz"):
        return "pz"
    return normalized


def _catalog_name_tokens(value: str | None) -> set[str]:
    return {token for token in _normalize_catalog_text(value).split(" ") if token}


def _catalog_name_similarity(left: str | None, right: str | None) -> tuple[float, float]:
    left_normalized = _normalize_catalog_text(left)
    right_normalized = _normalize_catalog_text(right)
    if not left_normalized or not right_normalized:
        return 0.0, 0.0
    if left_normalized == right_normalized:
        return 1.0, 1.0

    left_tokens = _catalog_name_tokens(left)
    right_tokens = _catalog_name_tokens(right)
    token_overlap = len(left_tokens & right_tokens) / max(len(left_tokens), len(right_tokens), 1)
    sequence_score = SequenceMatcher(None, left_normalized, right_normalized).ratio()
    score = min(1.0, (sequence_score * 0.68) + (token_overlap * 0.32))
    return score, token_overlap


def _catalog_match_label(match_type: str) -> str:
    labels = {
        "exact_code": "codice",
        "exact_identity": "identita esatta",
        "exact_name": "nome esatto",
        "strong_name": "nome forte",
        "new": "nuovo",
        "ambiguous": "ambiguo",
        "skipped": "saltato",
    }
    return labels.get(match_type, match_type)


def _product_upsert_payload_from_preview(
    row: SupplierCatalogRow,
    *,
    matched_product: Product | None,
    supplier_name: str | None,
    lot_code: str | None,
) -> dict[str, object]:
    product_name = matched_product.product_name if matched_product is not None else row.source_name.strip()
    resolved_supplier = (supplier_name or (matched_product.supplier_name if matched_product is not None else None) or "").strip()
    resolved_lot = (lot_code or (matched_product.lot_code if matched_product is not None else None) or "").strip()

    payload: dict[str, object] = {
        "product_name": product_name,
        "supplier_name": resolved_supplier,
        "lot_code": resolved_lot,
    }

    optional_fields = {
        "product_code": row.product_code if row.product_code is not None else (matched_product.product_code if matched_product is not None else None),
        "final_price_vat": row.final_price_vat,
        "vat_rate": row.vat_rate,
        "weight_kg": row.weight_kg,
        "unit_price_per_kg": row.unit_price_per_kg,
        "category": row.category,
        "notes": row.notes,
        "units_per_pack": row.units_per_pack,
        "liters_per_unit": row.liters_per_unit,
    }
    for field_name, value in optional_fields.items():
        if value is not None:
            payload[field_name] = value

    return payload


def _preview_supplier_catalog_rows(
    request: SupplierCatalogPreviewRequest,
    products: list[Product],
) -> dict[str, object]:
    products_by_code: defaultdict[str, list[Product]] = defaultdict(list)
    products_by_identity: defaultdict[tuple[str, str, str], list[Product]] = defaultdict(list)
    products_by_supplier: defaultdict[str, list[Product]] = defaultdict(list)
    products_by_name_and_lot: defaultdict[tuple[str, str], list[Product]] = defaultdict(list)

    for product in products:
        normalized_name = _normalize_catalog_text(product.product_name)
        normalized_supplier = _normalize_catalog_text(product.supplier_name)
        normalized_lot = _normalize_catalog_lot_code(product.lot_code)
        normalized_code = _normalize_catalog_text(product.product_code)

        if normalized_code:
            products_by_code[normalized_code].append(product)
        if normalized_name and normalized_supplier and normalized_lot:
            products_by_identity[(normalized_name, normalized_lot, normalized_supplier)].append(product)
        if normalized_supplier:
            products_by_supplier[normalized_supplier].append(product)
        if normalized_name and normalized_lot:
            products_by_name_and_lot[(normalized_name, normalized_lot)].append(product)

    preview_rows: list[dict[str, object]] = []
    summary = {
        "total_rows": 0,
        "importable_rows": 0,
        "matched_rows": 0,
        "matched_exact_code": 0,
        "matched_exact_identity": 0,
        "matched_exact_name": 0,
        "matched_strong_name": 0,
        "new_rows": 0,
        "ambiguous_rows": 0,
        "skipped_rows": 0,
    }

    default_supplier_name = (request.supplier_name or "").strip()
    default_lot_code = (request.default_lot_code or "").strip()

    for index, row in enumerate(request.rows, start=1):
        summary["total_rows"] += 1
        source_name = row.source_name.strip()
        source_supplier = (row.source_supplier_name or default_supplier_name or "").strip()
        source_lot = (row.source_lot_code or default_lot_code or "").strip()
        normalized_name = _normalize_catalog_text(source_name)
        normalized_supplier = _normalize_catalog_text(source_supplier)
        normalized_lot = _normalize_catalog_lot_code(source_lot)
        normalized_code = _normalize_catalog_text(row.product_code)

        warnings: list[str] = []
        matched_product: Product | None = None
        match_type = "skipped"
        confidence = 0.0
        importable = False

        if not source_name:
            warnings.append("Nome prodotto mancante.")
        if not source_supplier:
            warnings.append("Fornitore mancante: imposta una colonna fornitore o un fornitore di default.")
        if not source_lot:
            warnings.append("Lotto mancante: imposta una colonna lotto o un lotto di default.")

        if normalized_code:
            code_candidates = products_by_code.get(normalized_code, [])
            if normalized_supplier:
                supplier_filtered = [
                    candidate
                    for candidate in code_candidates
                    if _normalize_catalog_text(candidate.supplier_name) == normalized_supplier
                ]
                if supplier_filtered:
                    code_candidates = supplier_filtered
            if len(code_candidates) == 1:
                matched_product = code_candidates[0]
                match_type = "exact_code"
                confidence = 1.0
            elif len(code_candidates) > 1:
                warnings.append("Codice prodotto presente ma associato a piu righe del catalogo locale.")
                match_type = "ambiguous"

        if matched_product is None and normalized_name and normalized_supplier and normalized_lot:
            identity_candidates = products_by_identity.get((normalized_name, normalized_lot, normalized_supplier), [])
            if len(identity_candidates) == 1:
                matched_product = identity_candidates[0]
                match_type = "exact_identity"
                confidence = 0.99
            elif len(identity_candidates) > 1:
                warnings.append("Prodotto gia presente piu volte con la stessa identita locale.")
                match_type = "ambiguous"

        if matched_product is None and normalized_name and normalized_supplier:
            supplier_candidates = products_by_supplier.get(normalized_supplier, [])
            if normalized_lot:
                lot_candidates = [
                    candidate for candidate in supplier_candidates if _normalize_catalog_lot_code(candidate.lot_code) == normalized_lot
                ]
            else:
                lot_candidates = supplier_candidates

            exact_name_candidates = [
                candidate for candidate in lot_candidates if _normalize_catalog_text(candidate.product_name) == normalized_name
            ]
            if len(exact_name_candidates) == 1:
                matched_product = exact_name_candidates[0]
                match_type = "exact_name"
                confidence = 0.97
            elif len(exact_name_candidates) > 1:
                warnings.append("Nome prodotto coincidente su piu varianti locali: specifica meglio il lotto.")
                match_type = "ambiguous"
            elif lot_candidates:
                scored_candidates: list[tuple[float, float, Product]] = []
                for candidate in lot_candidates:
                    score, overlap = _catalog_name_similarity(source_name, candidate.product_name)
                    if normalized_lot and _normalize_catalog_lot_code(candidate.lot_code) == normalized_lot:
                        score = min(1.0, score + 0.02)
                    scored_candidates.append((score, overlap, candidate))
                scored_candidates.sort(key=lambda item: item[0], reverse=True)
                best_score, best_overlap, best_candidate = scored_candidates[0]
                second_score = scored_candidates[1][0] if len(scored_candidates) > 1 else 0.0
                if best_score >= 0.965 or (best_score >= 0.93 and best_overlap >= 0.84 and (best_score - second_score) >= 0.05):
                    matched_product = best_candidate
                    match_type = "strong_name"
                    confidence = round(best_score, 4)
                elif best_score >= 0.86:
                    warnings.append(
                        f"Riconoscimento non abbastanza sicuro: proposta {best_candidate.product_name} ({int(round(best_score * 100))}%)."
                    )
                    match_type = "ambiguous"

        if matched_product is None and match_type != "ambiguous" and normalized_name and normalized_lot:
            fallback_candidates = products_by_name_and_lot.get((normalized_name, normalized_lot), [])
            if len(fallback_candidates) == 1:
                matched_product = fallback_candidates[0]
                match_type = "exact_name"
                confidence = 0.94

        if matched_product is not None:
            source_supplier = matched_product.supplier_name
            source_lot = matched_product.lot_code
            importable = True
            summary["matched_rows"] += 1
            summary[f"matched_{match_type}"] += 1
        elif match_type == "ambiguous":
            summary["ambiguous_rows"] += 1
        elif normalized_name and source_supplier and source_lot and request.create_missing:
            match_type = "new"
            confidence = 0.0
            importable = True
            summary["new_rows"] += 1
        else:
            match_type = "skipped"
            summary["skipped_rows"] += 1

        if importable:
            summary["importable_rows"] += 1

        proposed_product = (
            _product_upsert_payload_from_preview(
                row,
                matched_product=matched_product,
                supplier_name=source_supplier,
                lot_code=source_lot,
            )
            if importable
            else None
        )

        preview_rows.append(
            {
                "row_index": index,
                "source_name": source_name,
                "source_supplier_name": row.source_supplier_name,
                "source_lot_code": row.source_lot_code,
                "source_product_code": row.product_code,
                "match_type": match_type,
                "match_label": _catalog_match_label(match_type),
                "confidence": round(confidence, 4),
                "importable": importable,
                "warnings": warnings,
                "matched_product": _serialize_product(matched_product) if matched_product is not None else None,
                "proposed_product": proposed_product,
            }
        )

    return {
        "ok": True,
        "summary": summary,
        "rows": preview_rows,
    }


def _goal_matches(item: OrderItem, goal: SeasonalGoal, *, secondary: bool = False) -> bool:
    product_match = goal.secondary_product_match if secondary else goal.product_match
    if secondary and not product_match:
        return False
    product_terms = [part.strip(" .,-") for part in re.split(r"\s*\|\s*|\s*,\s*|\s*;\s*", product_match or "") if part.strip(" .,-")]
    if product_terms:
        product_name = item.product_name.lower()
        if not any(term.lower() in product_name for term in product_terms):
            return False
    if goal.supplier_match and goal.supplier_match.lower() not in item.supplier_name.lower():
        return False
    return True


def _goal_progress(goal: SeasonalGoal, batches: list[OrderBatch]) -> dict[str, object]:
    if goal.goal_type == "note":
        return {
            "id": goal.id,
            "year": goal.year,
            "name": goal.name,
            "type": "note",
            "description": goal.description or "",
            "product_match": goal.product_match,
            "secondary_product_match": goal.secondary_product_match,
            "supplier_match": goal.supplier_match,
            "target": goal.target,
            "secondary_target": goal.secondary_target,
            "unit": goal.unit_label,
            "bonus": goal.bonus_label,
        }

    if goal.goal_type == "liters_dual":
        primary = 0.0
        secondary = 0.0
        for batch in batches:
            for item in batch.items:
                resolved_liters = _resolve_liters_per_unit(
                    item.product_name,
                    item.lot_code,
                    item.liters_per_unit if item.liters_per_unit is not None else (item.product.liters_per_unit if item.product is not None else None),
                )
                if resolved_liters is None:
                    continue
                units_per_pack = item.units_per_pack if item.units_per_pack is not None else (item.product.units_per_pack if item.product is not None else None)
                liters = resolved_liters * item.quantity * (float(units_per_pack) if units_per_pack and units_per_pack > 0 else 1.0)
                if _goal_matches(item, goal):
                    primary += liters
                if _goal_matches(item, goal, secondary=True):
                    secondary += liters
        return {
            "id": goal.id,
            "year": goal.year,
            "name": goal.name,
            "type": "liters_dual",
            "target_grey": goal.target or 0,
            "target_patron": goal.secondary_target or 0,
            "progress_grey": round(primary, 2),
            "progress_patron": round(secondary, 2),
            "bonus": goal.bonus_label,
            "description": goal.description or "",
            "product_match": goal.product_match,
            "secondary_product_match": goal.secondary_product_match,
            "supplier_match": goal.supplier_match,
            "target": goal.target,
            "secondary_target": goal.secondary_target,
            "unit": goal.unit_label or "L",
        }

    progress = 0.0
    for batch in batches:
        for item in batch.items:
            if not _goal_matches(item, goal):
                continue
            if goal.goal_type == "liters":
                resolved_liters = _resolve_liters_per_unit(
                    item.product_name,
                    item.lot_code,
                    item.liters_per_unit if item.liters_per_unit is not None else (item.product.liters_per_unit if item.product is not None else None),
                )
                if resolved_liters is None:
                    continue
                units_per_pack = item.units_per_pack if item.units_per_pack is not None else (item.product.units_per_pack if item.product is not None else None)
                progress += resolved_liters * item.quantity * (float(units_per_pack) if units_per_pack and units_per_pack > 0 else 1.0)
            else:
                progress += item.quantity

    return {
        "id": goal.id,
        "year": goal.year,
        "name": goal.name,
        "type": goal.goal_type,
        "target": goal.target or 0,
        "progress": round(progress, 2),
        "unit": goal.unit_label or ("L" if goal.goal_type == "liters" else "articoli"),
        "bonus": goal.bonus_label,
        "description": goal.description or "",
        "product_match": goal.product_match,
        "secondary_product_match": goal.secondary_product_match,
        "supplier_match": goal.supplier_match,
        "secondary_target": goal.secondary_target,
    }


@router.get("/health")
def health_check() -> dict[str, str]:
    return {"status": "ok", "service": "ordini-backend"}


@router.post("/api/login")
def login(payload: LoginRequest) -> dict[str, object]:
    try:
        context = authenticate_login(
            identifier=payload.identifier,
            password=payload.password,
            session_token=payload.session_token,
        )
    except ValueError:
        return {"success": False}
    if context.role == "staff" and "ordini" not in context.permissions:
        return {"success": False, "detail": "Questo account non puo accedere a Ordini"}
    return _login_response(context)


@protected.get("/api/session")
def session_info(auth: AuthContext = Depends(require_management_token)) -> dict[str, object]:
    return {
        "authenticated": True,
        "token": auth.token,
        "staff": auth.default_staff,
        "tenant_name": auth.tenant_name,
        "tenant_slug": auth.tenant_slug,
        "user_name": auth.user_name or auth.username or auth.user_email,
        "source": auth.source,
    }


@protected.get("/api/prodotti")
def list_products(db: Session = Depends(get_db)) -> list[dict[str, object]]:
    stmt = select(Product).where(Product.active.is_(True)).order_by(Product.supplier_name, Product.product_name, Product.lot_code)
    return [_serialize_product(product) for product in db.scalars(stmt)]


@protected.post("/api/prodotti/catalogo/preview")
def preview_supplier_catalog(payload: SupplierCatalogPreviewRequest, db: Session = Depends(get_db)) -> dict[str, object]:
    if not payload.rows:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Nessuna riga catalogo ricevuta")

    products = list(
        db.scalars(
            select(Product).where(Product.active.is_(True)).order_by(Product.supplier_name, Product.product_name, Product.lot_code)
        )
    )
    return _preview_supplier_catalog_rows(payload, products)


@protected.put("/api/prodotti/{product_id}")
def update_product(product_id: int, payload: ProductUpsert, db: Session = Depends(get_db)) -> dict[str, object]:
    product = db.get(Product, product_id)
    if product is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Prodotto non trovato")

    product_name, lot_code, supplier_name = _normalize_product_identity(payload)
    conflicting_product = db.scalar(
        select(Product).where(
            Product.id != product_id,
            Product.product_name == product_name,
            Product.lot_code == lot_code,
            Product.supplier_name == supplier_name,
        )
    )
    if conflicting_product is not None:
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Esiste gia un altro prodotto con lo stesso nome, lotto e fornitore.",
        )

    product.product_name = product_name
    product.lot_code = lot_code
    product.supplier_name = supplier_name
    _apply_product_metadata(product, payload)
    product.active = True

    try:
        db.commit()
    except IntegrityError as exc:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Aggiornamento prodotto in conflitto: controlla nome, lotto e fornitore.",
        ) from exc

    db.refresh(product)
    return {"ok": True, "product": _serialize_product(product)}


@protected.delete("/api/prodotti/{product_id}")
def delete_product(product_id: int, db: Session = Depends(get_db)) -> dict[str, object]:
    product = db.get(Product, product_id)
    if product is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Prodotto non trovato")

    product.active = False

    try:
        db.commit()
    except IntegrityError as exc:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Eliminazione prodotto non riuscita.",
        ) from exc

    return {"ok": True, "product_id": product_id}


@protected.post("/api/prodotti")
def upsert_products(payload: list[ProductUpsert], db: Session = Depends(get_db)) -> dict[str, object]:
    if not payload:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Nessun prodotto ricevuto")

    normalized_payload = _dedupe_product_payload(payload)
    created = 0
    updated = 0
    for entry in normalized_payload:
        product_name, lot_code, supplier_name = _normalize_product_identity(entry)
        existing = db.scalar(
            select(Product).where(
                Product.product_name == product_name,
                Product.lot_code == lot_code,
                Product.supplier_name == supplier_name,
            )
        )
        if existing is None:
            product = Product(
                product_name=product_name,
                lot_code=lot_code,
                supplier_name=supplier_name,
                active=True,
            )
            _apply_product_metadata(product, entry)
            db.add(product)
            created += 1
            continue

        _apply_product_metadata(existing, entry)
        existing.active = True
        updated += 1

    duplicate_rows = len(payload) - len(normalized_payload)
    try:
        db.commit()
    except IntegrityError as exc:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Import prodotti in conflitto: controlla se il file contiene righe duplicate o se lo stesso prodotto e' stato creato in parallelo.",
        ) from exc
    return {"ok": True, "created": created, "updated": updated, "duplicate_rows": duplicate_rows}


@protected.get("/api/ordini")
def list_orders(year: int | None = Query(default=None), db: Session = Depends(get_db)) -> list[dict[str, object]]:
    target_year = year or datetime.now(timezone.utc).year
    return [_serialize_batch(batch) for batch in _load_batches_for_year(target_year, db)]


@protected.get("/api/ordini/years")
def list_order_years(db: Session = Depends(get_db)) -> dict[str, object]:
    return {"years": _all_years(db)}


@protected.post("/api/ordini")
def create_order(
    payload: OrderCreateRequest,
    auth: AuthContext = Depends(require_token),
    db: Session = Depends(get_db),
) -> dict[str, object]:
    if not payload.items:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Nessun prodotto selezionato")

    product_ids = [item.product_id for item in payload.items]
    products = {
        product.id: product
        for product in db.scalars(select(Product).where(Product.id.in_(product_ids), Product.active.is_(True)))
    }
    missing = [item.product_id for item in payload.items if item.product_id not in products]
    if missing:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"Prodotti non trovati: {missing}")

    batch_total_estimated_amount = 0.0
    has_priced_lines = False
    batch = OrderBatch(staff=_normalize_staff(payload.staff, auth.default_staff))
    db.add(batch)
    db.flush()

    for entry in payload.items:
        product = products[entry.product_id]
        line_total = _estimate_line_total(
            entry.quantity,
            product.final_price_vat,
            weight_kg=product.weight_kg,
            unit_price_per_kg=product.unit_price_per_kg,
            vat_rate=product.vat_rate,
        )
        if line_total is not None:
            has_priced_lines = True
            batch_total_estimated_amount += line_total
        db.add(
            OrderItem(
                batch_id=batch.id,
                product_id=product.id,
                product_name=product.product_name,
                lot_code=product.lot_code,
                supplier_name=product.supplier_name,
                quantity=entry.quantity,
                final_price_vat_snapshot=product.final_price_vat,
                estimated_line_total=line_total,
                units_per_pack=product.units_per_pack,
                liters_per_unit=_resolve_liters_per_unit(product.product_name, product.lot_code, product.liters_per_unit),
            )
        )

    batch.total_estimated_amount = round(batch_total_estimated_amount, 2) if has_priced_lines else None

    db.commit()
    db.refresh(batch)
    loaded_batch = db.scalar(select(OrderBatch).where(OrderBatch.id == batch.id).options(selectinload(OrderBatch.items)))
    if loaded_batch is None:
        raise HTTPException(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail="Ordine salvato ma non ricaricabile")
    return {"ok": True, "batch": _serialize_batch(loaded_batch)}


@protected.put("/api/ordini")
def update_order(
    payload: OrderUpdateRequest,
    auth: AuthContext = Depends(require_token),
    db: Session = Depends(get_db),
) -> dict[str, object]:
    if not payload.items:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Nessun prodotto selezionato")

    batch = db.scalar(select(OrderBatch).where(OrderBatch.id == payload.batch_id).options(selectinload(OrderBatch.items)))
    if batch is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Ordine non trovato")

    product_ids = [item.product_id for item in payload.items]
    products = {
        product.id: product
        for product in db.scalars(select(Product).where(Product.id.in_(product_ids), Product.active.is_(True)))
    }
    missing = [item.product_id for item in payload.items if item.product_id not in products]
    if missing:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"Prodotti non trovati: {missing}")

    batch.staff = _normalize_staff(payload.staff, batch.staff or auth.default_staff)
    batch_total_estimated_amount = 0.0
    has_priced_lines = False

    batch.items.clear()
    db.flush()

    for entry in payload.items:
        product = products[entry.product_id]
        line_total = _estimate_line_total(
            entry.quantity,
            product.final_price_vat,
            weight_kg=product.weight_kg,
            unit_price_per_kg=product.unit_price_per_kg,
            vat_rate=product.vat_rate,
        )
        if line_total is not None:
            has_priced_lines = True
            batch_total_estimated_amount += line_total
        db.add(
            OrderItem(
                batch_id=batch.id,
                product_id=product.id,
                product_name=product.product_name,
                lot_code=product.lot_code,
                supplier_name=product.supplier_name,
                quantity=entry.quantity,
                final_price_vat_snapshot=product.final_price_vat,
                estimated_line_total=line_total,
                units_per_pack=product.units_per_pack,
                liters_per_unit=_resolve_liters_per_unit(product.product_name, product.lot_code, product.liters_per_unit),
            )
        )

    batch.total_estimated_amount = round(batch_total_estimated_amount, 2) if has_priced_lines else None

    db.commit()
    loaded_batch = db.scalar(select(OrderBatch).where(OrderBatch.id == batch.id).options(selectinload(OrderBatch.items)))
    if loaded_batch is None:
        raise HTTPException(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail="Ordine aggiornato ma non ricaricabile")
    return {"ok": True, "batch": _serialize_batch(loaded_batch)}


@protected.delete("/api/ordini")
def delete_order(payload: OrderDeleteRequest, db: Session = Depends(get_db)) -> dict[str, object]:
    batch: OrderBatch | None = None
    if payload.batch_id is not None:
        batch = db.scalar(select(OrderBatch).where(OrderBatch.id == payload.batch_id).options(selectinload(OrderBatch.items)))
    elif payload.timestamp:
        try:
            target = datetime.fromisoformat(payload.timestamp.replace("Z", "+00:00"))
        except ValueError as exc:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Timestamp ordine non valido") from exc
        batch = db.scalar(select(OrderBatch).where(OrderBatch.confirmed_at == target).options(selectinload(OrderBatch.items)))
    else:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Specifica batch_id o timestamp")

    if batch is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Ordine non trovato")

    removed_items = [_serialize_item(item) for item in batch.items]
    db.delete(batch)
    db.commit()
    return {"ok": True, "removed": removed_items}


@protected.get("/api/ordini/ultimo")
def latest_order(db: Session = Depends(get_db)) -> dict[str, object]:
    stmt = select(OrderBatch).options(selectinload(OrderBatch.items)).order_by(OrderBatch.confirmed_at.desc(), OrderBatch.id.desc())
    batch = db.scalar(stmt)
    if batch is None:
        return {"batch": None}
    return {"batch": _serialize_batch(batch)}


@protected.get("/api/ordini/confronto")
def compare_orders(year: int | None = Query(default=None), db: Session = Depends(get_db)) -> dict[str, object]:
    target_year = year or datetime.now(timezone.utc).year
    current_batches = _load_batches_for_year(target_year, db)
    previous_year = target_year - 1
    previous_batches = _load_batches_for_year(previous_year, db)

    current_totals: dict[str, int] = defaultdict(int)
    previous_totals: dict[str, int] = defaultdict(int)

    for batch in current_batches:
        for item in batch.items:
            current_totals[item.product_name] += item.quantity

    for batch in previous_batches:
        for item in batch.items:
            previous_totals[item.product_name] += item.quantity

    product_names = sorted(set(current_totals) | set(previous_totals), key=lambda name: (-current_totals.get(name, 0), name))
    items = []
    for product_name in product_names:
        current_quantity = current_totals.get(product_name, 0)
        previous_quantity = previous_totals.get(product_name, 0)
        delta = current_quantity - previous_quantity
        delta_percent = None
        if previous_quantity:
            delta_percent = round((delta / previous_quantity) * 100, 1)
        elif current_quantity:
            delta_percent = None
        items.append(
            {
                "product_name": product_name,
                "current_quantity": current_quantity,
                "previous_quantity": previous_quantity,
                "delta": delta,
                "delta_percent": delta_percent,
            }
        )

    return {
        "year": target_year,
        "previous_year": previous_year,
        "current_total": sum(current_totals.values()),
        "previous_total": sum(previous_totals.values()),
        "items": items,
    }


@protected.get("/api/ordine_sospeso")
def get_suspended_order(
    staff: str | None = Query(default=None),
    auth: AuthContext = Depends(require_token),
    db: Session = Depends(get_db),
) -> dict[str, int]:
    suspended = db.scalar(select(SuspendedOrder).where(SuspendedOrder.staff == _normalize_staff(staff, auth.default_staff)))
    return suspended.payload if suspended else {}


@protected.post("/api/ordine_sospeso")
def save_suspended_order(
    payload: dict[str, int] = Body(...),
    staff: str | None = Query(default=None),
    auth: AuthContext = Depends(require_management_token),
    db: Session = Depends(get_db),
) -> dict[str, object]:
    normalized = _normalize_suspended_payload(payload)
    if not normalized:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Nessun prodotto da sospendere")

    target_staff = _normalize_staff(staff, auth.default_staff)
    suspended = db.scalar(select(SuspendedOrder).where(SuspendedOrder.staff == target_staff))
    if suspended is None:
        suspended = SuspendedOrder(staff=target_staff, payload=normalized)
        db.add(suspended)
    else:
        suspended.payload = normalized
    db.commit()
    return {"ok": True}


@protected.delete("/api/ordine_sospeso")
def delete_suspended_order(
    staff: str | None = Query(default=None),
    auth: AuthContext = Depends(require_management_token),
    db: Session = Depends(get_db),
) -> Response:
    suspended = db.scalar(select(SuspendedOrder).where(SuspendedOrder.staff == _normalize_staff(staff, auth.default_staff)))
    if suspended is not None:
        db.delete(suspended)
        db.commit()
    return Response(status_code=status.HTTP_204_NO_CONTENT)


@protected.get("/api/note")
def list_notes(db: Session = Depends(get_db)) -> list[dict[str, object]]:
    stmt = select(SharedNote).order_by(SharedNote.created_at.asc(), SharedNote.id.asc())
    return [
        {
            "id": note.id,
            "author": note.author,
            "text": note.text,
            "timestamp": _format_iso(note.created_at),
        }
        for note in db.scalars(stmt)
    ]


@protected.post("/api/note")
def create_note(
    payload: SharedNoteCreate,
    auth: AuthContext = Depends(require_management_token),
    db: Session = Depends(get_db),
) -> dict[str, object]:
    note = SharedNote(author=_normalize_staff(None, auth.default_staff), text=_normalize_note_text(payload.text))
    db.add(note)
    db.commit()
    db.refresh(note)
    return {
        "note": {
            "id": note.id,
            "author": note.author,
            "text": note.text,
            "timestamp": _format_iso(note.created_at),
        }
    }


@protected.delete("/api/note/{note_id}")
def delete_note(note_id: int, db: Session = Depends(get_db)) -> Response:
    note = db.get(SharedNote, note_id)
    if note is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Nota non trovata")
    db.delete(note)
    db.commit()
    return Response(status_code=status.HTTP_204_NO_CONTENT)


@protected.put("/api/obiettivi/{goal_id}")
def update_goal(goal_id: int, payload: GoalUpsert, db: Session = Depends(get_db)) -> dict[str, object]:
    goal = db.get(SeasonalGoal, goal_id)
    if goal is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Obiettivo non trovato")

    goal_type = str(payload.goal_type or "").strip()
    if goal_type not in {"quantity", "liters", "liters_dual", "note"}:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Tipo obiettivo non valido")

    goal.name = payload.name.strip()
    goal.goal_type = goal_type
    goal.year = payload.year or goal.year
    goal.description = payload.description
    goal.product_match = payload.product_match
    goal.secondary_product_match = payload.secondary_product_match
    goal.supplier_match = payload.supplier_match
    goal.target = payload.target
    goal.secondary_target = payload.secondary_target
    goal.unit_label = payload.unit_label
    goal.bonus_label = payload.bonus_label

    if goal.goal_type == "note":
        goal.product_match = None
        goal.secondary_product_match = None
        goal.supplier_match = None
        goal.target = None
        goal.secondary_target = None
        goal.unit_label = None
        if not goal.description:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Per una nota serve una descrizione.")
    elif goal.goal_type == "liters_dual":
        if not goal.product_match or not goal.secondary_product_match:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Per un doppio target servono prodotto principale e secondo prodotto.",
            )
        if goal.target is None or goal.secondary_target is None:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Per un doppio target servono target principale e target secondario.",
            )
        goal.unit_label = goal.unit_label or "L"
    else:
        if goal.target is None:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Per questo obiettivo serve un target numerico.")
        if not (goal.product_match or goal.supplier_match):
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Per questo obiettivo indica almeno prodotto o fornitore.",
            )
        goal.secondary_product_match = None
        goal.secondary_target = None
        goal.unit_label = goal.unit_label or ("L" if goal.goal_type == "liters" else "articoli")

    db.commit()
    db.refresh(goal)
    batches = _load_batches_for_year(goal.year, db)
    return {"ok": True, "goal": _goal_progress(goal, batches)}


@protected.get("/api/obiettivi")
def list_goals(year: int | None = Query(default=None), db: Session = Depends(get_db)) -> dict[str, object]:
    target_year = year or datetime.now(timezone.utc).year
    stmt = (
        select(SeasonalGoal)
        .where(SeasonalGoal.year == target_year)
        .order_by(case((SeasonalGoal.goal_type == "note", 1), else_=0).asc(), SeasonalGoal.id.asc())
    )
    goals = list(db.scalars(stmt))
    batches = _load_batches_for_year(target_year, db)
    return {
        "year": target_year,
        "available": _all_years(db),
        "goals": [_goal_progress(goal, batches) for goal in goals],
    }


@router.get("/storico_ordini.csv")
def export_orders_csv(
    year: int | None = Query(default=None),
    _: AuthContext = Depends(require_management_query_token),
    db: Session = Depends(get_db_from_query_token),
) -> StreamingResponse:
    target_year = year or datetime.now(timezone.utc).year
    batches = _load_batches_for_year(target_year, db)
    buffer = io.StringIO()
    writer = csv.writer(buffer)
    writer.writerow(["Data", "Prodotto", "LOTTO", "FORNITORE", "Quantita"])
    for batch in batches:
        timestamp = _format_iso(batch.confirmed_at) or ""
        for item in batch.items:
            writer.writerow([timestamp, item.product_name, item.lot_code, item.supplier_name, item.quantity])
    buffer.seek(0)
    headers = {"Content-Disposition": f'attachment; filename="storico_ordini_{target_year}.csv"'}
    return StreamingResponse(iter([buffer.getvalue()]), media_type="text/csv; charset=utf-8", headers=headers)


router.include_router(protected)
