from datetime import date
from typing import List, Optional, Union, Dict, Any

from fastapi import APIRouter, Depends, HTTPException, Query
from sqlalchemy import or_, func
from sqlalchemy.orm import Session

from app.core.database import get_db
from app.core.dependencies import get_current_company_id
from app.models.inventory import SerialNumber, Product
from app.models.branch import Branch
from app.schemas.serial_number import (
    SerialNumberBulkCreate,
    SerialNumberCreate,
    SerialNumberResponse,
    SerialNumberUpdate,
)

router = APIRouter()


_ALLOWED_STATUSES = {"available", "reserved", "sold", "returned", "void", "in_service"}


def _serialize(sn: SerialNumber) -> dict:
    """Flatten SerialNumber with related names for UI consumption."""
    product = sn.product
    customer = sn.customer
    branch = sn.branch
    return {
        "id": sn.id,
        "serial": sn.serial,
        "product_id": sn.product_id,
        "product_name": product.name if product else None,
        "product_sku": product.sku if product else None,
        "status": sn.status,
        "purchase_id": sn.purchase_id,
        "sale_id": sn.sale_id,
        "invoice_id": sn.invoice_id,
        "receipt_id": sn.receipt_id,
        "customer_id": sn.customer_id,
        "customer_name": customer.name if customer else None,
        "supplier_id": sn.supplier_id,
        "batch_number": sn.batch_number,
        "branch_id": sn.branch_id,
        "branch_name": branch.name if branch else None,
        "warranty_started_at": sn.warranty_started_at,
        "warranty_expires_at": sn.warranty_expires_at,
        "purchase_date": sn.purchase_date,
        "sold_at": sn.sold_at,
        "returned_at": sn.returned_at,
        "location": sn.location,
        "notes": sn.notes,
        "active": bool(sn.active),
        "created_at": sn.created_at,
        "updated_at": sn.updated_at,
    }


@router.get("/", response_model=List[SerialNumberResponse])
async def list_serial_numbers(
    db: Session = Depends(get_db),
    product_id: Optional[str] = Query(None, description="Filter by product"),
    status: Optional[str] = Query(None, description="Filter by status"),
    customer_id: Optional[str] = Query(None, description="Filter by customer"),
    supplier_id: Optional[str] = Query(None, description="Filter by supplier"),
    sale_id: Optional[str] = Query(None, description="Filter by sale"),
    invoice_id: Optional[str] = Query(None, description="Filter by invoice"),
    receipt_id: Optional[str] = Query(None, description="Filter by receipt"),
    batch_number: Optional[str] = Query(None, description="Filter by batch number"),
    branch_id: Optional[str] = Query(None, description="Filter by branch"),
    location: Optional[str] = Query(None, description="Filter by location"),
    search: Optional[str] = Query(None, description="Partial serial search"),
    active: Optional[bool] = Query(None, description="Filter by active flag"),
    sold_from: Optional[date] = Query(None, description="Sold from date (inclusive)"),
    sold_to: Optional[date] = Query(None, description="Sold to date (inclusive)"),
    limit: int = Query(200, le=500, description="Maximum rows to return"),
    offset: int = Query(0, ge=0, description="Skip rows for pagination"),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    query = db.query(SerialNumber).join(Product)

    if company_id and not branch_id:
        branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
        # Include unassigned branch_id so stock created before branch assignment still appears
        query = query.filter(
            or_(
                SerialNumber.branch_id.is_(None),
                SerialNumber.branch_id.in_(branch_ids_sq),
            )
        )

    if product_id:
        query = query.filter(SerialNumber.product_id == product_id)
    if status:
        query = query.filter(SerialNumber.status == status)
    if customer_id:
        query = query.filter(SerialNumber.customer_id == customer_id)
    if supplier_id:
        query = query.filter(SerialNumber.supplier_id == supplier_id)
    if sale_id:
        query = query.filter(SerialNumber.sale_id == sale_id)
    if invoice_id:
        query = query.filter(SerialNumber.invoice_id == invoice_id)
    if receipt_id:
        query = query.filter(SerialNumber.receipt_id == receipt_id)
    if batch_number:
        query = query.filter(SerialNumber.batch_number == batch_number)
    if branch_id:
        query = query.filter(SerialNumber.branch_id == branch_id)
    if location:
        query = query.filter(SerialNumber.location.ilike(f"%{location}%"))
    if active is not None:
        query = query.filter(SerialNumber.active == active)
    if sold_from:
        query = query.filter(SerialNumber.sold_at >= sold_from)
    if sold_to:
        query = query.filter(SerialNumber.sold_at <= sold_to)
    if search:
        like = f"%{search}%"
        query = query.filter(or_(SerialNumber.serial.ilike(like), Product.name.ilike(like), Product.sku.ilike(like)))

    serials = query.order_by(SerialNumber.created_at.desc()).offset(offset).limit(limit).all()

    return [_serialize(sn) for sn in serials]


@router.get("/{serial_id}", response_model=SerialNumberResponse)
async def get_serial_number(serial_id: str, db: Session = Depends(get_db)):
    sn = db.query(SerialNumber).filter(SerialNumber.id == serial_id).first()
    if not sn:
        raise HTTPException(status_code=404, detail="Serial number not found")
    return _serialize(sn)


@router.get("/lookup/{serial}", response_model=SerialNumberResponse)
async def lookup_serial(serial: str, db: Session = Depends(get_db)):
    sn = db.query(SerialNumber).filter(SerialNumber.serial == serial).first()
    if not sn:
        raise HTTPException(status_code=404, detail="Serial number not found")
    return _serialize(sn)


@router.get("/scan/{serial}")
async def scan_serial(serial: str, db: Session = Depends(get_db)):
    """
    Fast POS / sales scan endpoint.  Given a barcode / serial string, returns
    everything the front-end needs to add a serialised product to a cart or
    validate a sale line — including the product's current selling price,
    availability status, and a human-readable reason string when the serial
    cannot be sold.

    HTTP 200 always (even if not available) so the front-end can display a
    specific message without catching exceptions.  Check `can_sell` in the
    response to decide whether to proceed.
    """
    sn = db.query(SerialNumber).filter(SerialNumber.serial == serial).first()
    if not sn:
        return {
            "found": False,
            "serial": serial,
            "can_sell": False,
            "reason": f"Serial number '{serial}' not found in inventory",
        }

    product = sn.product
    can_sell = sn.status == "available" and bool(sn.active)
    reasons = {
        "sold":       "Already sold — this unit is not available",
        "reserved":   "Reserved — cannot be sold until reservation is released",
        "returned":   "Returned — pending inspection before re-sale",
        "void":       "Voided — this unit has been written off",
        "in_service": "In service / under repair",
    }
    reason = None if can_sell else reasons.get(sn.status, f"Status '{sn.status}' prevents sale")

    return {
        "found": True,
        "can_sell": can_sell,
        "reason": reason,
        "serial_id": sn.id,
        "serial": sn.serial,
        "status": sn.status,
        "product_id": sn.product_id,
        "product_name": product.name if product else None,
        "product_sku": product.sku if product else None,
        "selling_price": float(product.selling_price) if product and product.selling_price else 0.0,
        "vat_rate": float(product.vat_rate) if product and hasattr(product, "vat_rate") and product.vat_rate else 0.0,
        "branch_id": sn.branch_id,
        "batch_number": sn.batch_number,
        "purchase_id": sn.purchase_id,
        "warranty_expires_at": sn.warranty_expires_at,
    }


@router.post("/", response_model=List[SerialNumberResponse])
async def create_serial_numbers(
    payload: Union[SerialNumberCreate, SerialNumberBulkCreate],
    db: Session = Depends(get_db),
):
    serials_to_create: List[SerialNumberCreate] = []

    if isinstance(payload, SerialNumberBulkCreate):
        for serial in payload.serials:
            serials_to_create.append(
                SerialNumberCreate(
                    product_id=payload.product_id,
                    serial=serial,
                    status=payload.status,
                    purchase_id=payload.purchase_id,
                    inventory_transaction_id=payload.inventory_transaction_id,
                    warranty_started_at=payload.warranty_started_at,
                    warranty_expires_at=payload.warranty_expires_at,
                    purchase_date=payload.purchase_date,
                    supplier_id=payload.supplier_id,
                    customer_id=payload.customer_id,
                    batch_number=payload.batch_number,
                    branch_id=payload.branch_id,
                    location=payload.location,
                    notes=payload.notes,
                )
            )
    else:
        serials_to_create.append(payload)

    created: List[SerialNumber] = []
    for item in serials_to_create:
        product = db.query(Product).filter(Product.id == item.product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail=f"Product {item.product_id} not found")
        if not product.is_serialized:
            raise HTTPException(status_code=400, detail="Product is not marked as serialized")

        exists = db.query(SerialNumber).filter(SerialNumber.serial == item.serial).first()
        if exists:
            raise HTTPException(status_code=400, detail=f"Serial {item.serial} already exists")

        status_value = item.status or "available"
        if status_value not in _ALLOWED_STATUSES:
            raise HTTPException(status_code=400, detail=f"Invalid status for {item.serial}. Allowed: {', '.join(sorted(_ALLOWED_STATUSES))}")

        sn = SerialNumber(
            serial=item.serial,
            product_id=item.product_id,
            status=status_value,
            purchase_id=item.purchase_id,
            inventory_transaction_id=item.inventory_transaction_id,
            warranty_started_at=item.warranty_started_at,
            warranty_expires_at=item.warranty_expires_at,
            purchase_date=item.purchase_date,
            supplier_id=item.supplier_id,
            customer_id=item.customer_id,
            batch_number=item.batch_number,
            branch_id=item.branch_id,
            location=item.location,
            notes=item.notes,
        )
        db.add(sn)
        created.append(sn)

    db.commit()
    for sn in created:
        db.refresh(sn)

    return [_serialize(sn) for sn in created]


@router.patch("/{serial_id}", response_model=SerialNumberResponse)
async def update_serial_number(serial_id: str, payload: SerialNumberUpdate, db: Session = Depends(get_db)):
    sn = db.query(SerialNumber).filter(SerialNumber.id == serial_id).first()
    if not sn:
        raise HTTPException(status_code=404, detail="Serial number not found")

    if payload.status and payload.status not in _ALLOWED_STATUSES:
        raise HTTPException(status_code=400, detail=f"Invalid status. Allowed: {', '.join(sorted(_ALLOWED_STATUSES))}")

    update_data = payload.model_dump(exclude_unset=True)

    # Apply status-based timestamps
    if "status" in update_data:
        sn.status = update_data.pop("status")
        if sn.status == "sold":
            sn.sold_at = update_data.get("sold_at") or date.today()
        if sn.status == "returned":
            sn.returned_at = update_data.get("returned_at") or date.today()

    for field, value in update_data.items():
        setattr(sn, field, value)

    db.commit()
    db.refresh(sn)
    return _serialize(sn)


@router.get("/reports/sales-history", response_model=List[SerialNumberResponse])
async def get_sales_history(
    db: Session = Depends(get_db),
    customer_id: Optional[str] = Query(None, description="Filter by customer"),
    product_id: Optional[str] = Query(None, description="Filter by product"),
    batch_number: Optional[str] = Query(None, description="Filter by batch"),
    branch_id: Optional[str] = Query(None, description="Filter by branch"),
    sold_from: Optional[date] = Query(None, description="Sold from date"),
    sold_to: Optional[date] = Query(None, description="Sold to date"),
    limit: int = Query(200, le=500),
):
    """Get sales history with customer, date, and product details"""
    query = db.query(SerialNumber).join(Product).filter(SerialNumber.status == "sold")

    if customer_id:
        query = query.filter(SerialNumber.customer_id == customer_id)
    if product_id:
        query = query.filter(SerialNumber.product_id == product_id)
    if batch_number:
        query = query.filter(SerialNumber.batch_number == batch_number)
    if branch_id:
        query = query.filter(SerialNumber.branch_id == branch_id)
    if sold_from:
        query = query.filter(SerialNumber.sold_at >= sold_from)
    if sold_to:
        query = query.filter(SerialNumber.sold_at <= sold_to)

    serials = query.order_by(SerialNumber.sold_at.desc()).limit(limit).all()
    return [_serialize(sn) for sn in serials]


@router.get("/reports/by-batch", response_model=Dict[str, Any])
async def get_serials_by_batch(
    db: Session = Depends(get_db),
    branch_id: Optional[str] = Query(None, description="Filter by branch"),
):
    """Group serial numbers by batch with counts and status breakdown"""
    query = db.query(
        SerialNumber.batch_number,
        SerialNumber.status,
        func.count(SerialNumber.id).label("count")
    ).filter(SerialNumber.batch_number.isnot(None))

    if branch_id:
        query = query.filter(SerialNumber.branch_id == branch_id)

    results = query.group_by(SerialNumber.batch_number, SerialNumber.status).all()

    # Organize by batch
    batches = {}
    for batch_number, status, count in results:
        if batch_number not in batches:
            batches[batch_number] = {"batch_number": batch_number, "total": 0, "by_status": {}}
        batches[batch_number]["by_status"][status] = count
        batches[batch_number]["total"] += count

    return {"batches": list(batches.values())}


@router.get("/reports/by-branch", response_model=Dict[str, Any])
async def get_serials_by_branch(
    db: Session = Depends(get_db),
    status: Optional[str] = Query(None, description="Filter by status"),
):
    """Group serial numbers by branch with counts and status breakdown"""
    query = db.query(
        SerialNumber.branch_id,
        SerialNumber.status,
        func.count(SerialNumber.id).label("count")
    ).filter(SerialNumber.branch_id.isnot(None))

    if status:
        query = query.filter(SerialNumber.status == status)

    results = query.group_by(SerialNumber.branch_id, SerialNumber.status).all()

    # Organize by branch
    branches = {}
    for branch_id, status_val, count in results:
        if branch_id not in branches:
            branches[branch_id] = {"branch_id": branch_id, "total": 0, "by_status": {}}
        branches[branch_id]["by_status"][status_val] = count
        branches[branch_id]["total"] += count

    return {"branches": list(branches.values())}


@router.get("/reports/by-location", response_model=Dict[str, Any])
async def get_serials_by_location(
    db: Session = Depends(get_db),
    branch_id: Optional[str] = Query(None, description="Filter by branch"),
    batch_number: Optional[str] = Query(None, description="Filter by batch"),
):
    """Group serial numbers by location with counts"""
    query = db.query(
        SerialNumber.location,
        SerialNumber.status,
        func.count(SerialNumber.id).label("count")
    ).filter(SerialNumber.location.isnot(None))

    if branch_id:
        query = query.filter(SerialNumber.branch_id == branch_id)
    if batch_number:
        query = query.filter(SerialNumber.batch_number == batch_number)

    results = query.group_by(SerialNumber.location, SerialNumber.status).all()

    # Organize by location
    locations = {}
    for location, status_val, count in results:
        if location not in locations:
            locations[location] = {"location": location, "total": 0, "by_status": {}}
        locations[location]["by_status"][status_val] = count
        locations[location]["total"] += count

    return {"locations": list(locations.values())}
