"""
Manufacturing Variance Analysis & Batch Tracking Endpoints

New REST endpoints for enterprise manufacturing:
- Variance analysis
- Batch/lot tracking & search
- Quality management
"""

from typing import List, Optional, Dict, Any
from fastapi import APIRouter, Depends, HTTPException, Query, status
from pydantic import BaseModel
from sqlalchemy.orm import Session
from sqlalchemy import func, desc, and_
from datetime import datetime, date, timedelta
import uuid

from app.core.database import get_db
from app.core.dependencies import get_current_company_id
from app.models.branch import Branch


# ============ SCHEMAS ============

class VarianceAnalysisResponse(BaseModel):
    batch_number: str
    product_name: str
    standard_cost: float
    actual_cost: float
    variance_amount: float
    variance_percentage: float
    variance_type: str  # favorable/unfavorable
    material_variance: float
    labor_variance: float
    overhead_variance: float
    status: str
    date: datetime

    class Config:
        from_attributes = True


class BatchSearchResponse(BaseModel):
    batch_number: str
    product_name: str
    batch_size: float
    total_cost: float
    quality_status: str
    lot_start_date: Optional[datetime]
    lot_end_date: Optional[datetime]
    status: str
    created_at: datetime

    class Config:
        from_attributes = True


class QualityCheckRequest(BaseModel):
    manufacturing_cost_id: int
    status: str  # pass/fail/conditional
    notes: Optional[str] = None
    checked_by_id: Optional[str] = None


class StandardCostSetRequest(BaseModel):
    product_id: str
    standard_material_cost: float
    standard_labor_cost: float
    standard_overhead_cost: float
    effective_date: date


# ============ ENDPOINTS ============

router = APIRouter(tags=["Manufacturing - Enterprise"])


def _variance_to_response(v, product_name: str) -> VarianceAnalysisResponse:
    return VarianceAnalysisResponse(
        batch_number=v.batch_number,
        product_name=product_name,
        standard_cost=float(v.standard_cost or 0),
        actual_cost=float(v.actual_cost or 0),
        variance_amount=float(v.variance_amount or 0),
        variance_percentage=float(v.variance_percentage or 0),
        variance_type=v.variance_type or "neutral",
        material_variance=float(v.material_variance or 0),
        labor_variance=float(v.labor_variance or 0),
        overhead_variance=float(v.overhead_variance or 0),
        status="recorded",
        date=v.created_at or datetime.utcnow(),
    )


def _batch_to_response(mc, product_name: str) -> BatchSearchResponse:
    return BatchSearchResponse(
        batch_number=mc.batch_number or "",
        product_name=product_name,
        batch_size=float(mc.batch_size or 0),
        total_cost=float(mc.total_cost or 0),
        quality_status=mc.quality_check_status or "pending",
        lot_start_date=mc.lot_start_date,
        lot_end_date=mc.lot_end_date,
        status=mc.status or "draft",
        created_at=mc.date or mc.lot_start_date or datetime.utcnow(),
    )


@router.get("/variance-analysis", response_model=List[VarianceAnalysisResponse])
async def get_variance_analysis(
    start_date: Optional[date] = Query(None),
    end_date: Optional[date] = Query(None),
    product_id: Optional[str] = Query(None),
    variance_type: Optional[str] = Query(None),  # favorable/unfavorable
    min_variance: float = Query(0, description="Minimum variance percentage to display"),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Get variance analysis (actual vs standard costs).

    Filters:
    - min_variance: Show only variances above this % (e.g., 5 for 5%)
    - variance_type: 'favorable' or 'unfavorable'
    - product_id: Filter by specific product
    """
    from app.models.cost_accounting import ManufacturingVariance
    from app.models.inventory import Product

    query = db.query(ManufacturingVariance).join(Product)
    if company_id and not product_id:
        _bids = db.query(Branch.id).filter(Branch.company_id == company_id)
        _pids = db.query(Product.id).filter(Product.branch_id.in_(_bids))
        query = query.filter(ManufacturingVariance.product_id.in_(_pids))

    if start_date:
        query = query.filter(ManufacturingVariance.created_at >= start_date)
    if end_date:
        query = query.filter(ManufacturingVariance.created_at <= end_date)
    if product_id:
        query = query.filter(ManufacturingVariance.product_id == product_id)
    if variance_type:
        query = query.filter(ManufacturingVariance.variance_type == variance_type)
    if min_variance > 0:
        query = query.filter(ManufacturingVariance.variance_percentage >= min_variance)

    rows = (
        query.order_by(desc(ManufacturingVariance.variance_percentage))
        .add_columns(Product.name.label("product_name"))
        .all()
    )
    return [_variance_to_response(v, pname or "") for v, pname in rows]


@router.get("/variance-analysis/summary", response_model=Dict[str, Any])
async def get_variance_summary(
    start_date: Optional[date] = Query(None),
    end_date: Optional[date] = Query(None),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Get variance summary statistics for dashboard.

    Returns:
    - Total variance (favorable/unfavorable)
    - Average variance %
    - Top 5 unfavorable variances
    - Trend over time
    """
    from app.models.cost_accounting import ManufacturingVariance
    from app.models.inventory import Product

    query = db.query(ManufacturingVariance)

    if company_id:
        _bids = db.query(Branch.id).filter(Branch.company_id == company_id)
        _pids = db.query(Product.id).filter(Product.branch_id.in_(_bids))
        query = query.filter(ManufacturingVariance.product_id.in_(_pids))
    if start_date:
        query = query.filter(ManufacturingVariance.created_at >= start_date)
    if end_date:
        query = query.filter(ManufacturingVariance.created_at <= end_date)

    all_variances = query.all()

    favorable_total = sum(v.variance_amount for v in all_variances if v.variance_type == "favorable")
    unfavorable_total = sum(v.variance_amount for v in all_variances if v.variance_type == "unfavorable")
    avg_variance = sum(abs(v.variance_percentage) for v in all_variances) / len(all_variances) if all_variances else 0

    unfavorable_list = sorted(
        [v for v in all_variances if v.variance_type == "unfavorable"],
        key=lambda x: abs(x.variance_percentage),
        reverse=True
    )[:5]

    return {
        "total_records": len(all_variances),
        "favorable_variance_total": favorable_total,
        "unfavorable_variance_total": unfavorable_total,
        "net_variance": favorable_total - unfavorable_total,
        "average_variance_percent": avg_variance,
        "top_unfavorable": [
            {
                "batch": v.batch_number,
                "product_id": v.product_id,
                "variance": v.variance_percentage,
                "amount": v.variance_amount
            }
            for v in unfavorable_list
        ]
    }


@router.get("/batches", response_model=List[BatchSearchResponse])
async def search_batches(
    batch_number: Optional[str] = Query(None),
    product_id: Optional[str] = Query(None),
    quality_status: Optional[str] = Query(None),
    start_date: Optional[date] = Query(None),
    end_date: Optional[date] = Query(None),
    limit: int = Query(100, le=1000),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Search manufacturing batches.

    Useful for lot traceability, regulatory compliance, and recalls.
    Perfect for FDA, GMP, ISO 9001 audits.
    """
    from app.models.cost_accounting import ManufacturingCost
    from app.models.inventory import Product

    query = db.query(ManufacturingCost).join(Product).filter(ManufacturingCost.batch_number.isnot(None))

    if company_id and not product_id:
        _bids = db.query(Branch.id).filter(Branch.company_id == company_id)
        _pids = db.query(Product.id).filter(Product.branch_id.in_(_bids))
        query = query.filter(ManufacturingCost.product_id.in_(_pids))
    if batch_number:
        query = query.filter(ManufacturingCost.batch_number.ilike(f"%{batch_number}%"))
    if product_id:
        query = query.filter(ManufacturingCost.product_id == product_id)
    if quality_status:
        query = query.filter(ManufacturingCost.quality_check_status == quality_status)
    if start_date:
        query = query.filter(ManufacturingCost.lot_start_date >= start_date)
    if end_date:
        query = query.filter(ManufacturingCost.lot_end_date <= end_date)

    rows = (
        query.add_columns(Product.name.label("product_name"))
        .order_by(desc(ManufacturingCost.date))
        .limit(limit)
        .all()
    )
    return [_batch_to_response(mc, pname or "") for mc, pname in rows]


@router.post("/batches/quality-check", status_code=201)
async def submit_quality_check(
    request: QualityCheckRequest,
    db: Session = Depends(get_db)
):
    """
    Submit quality check results for a manufacturing batch.

    Prevents production completion until QC passes.
    Creates audit trail for compliance.
    """
    from app.models.cost_accounting import ManufacturingCost

    manufacturing = db.query(ManufacturingCost).filter(
        ManufacturingCost.id == request.manufacturing_cost_id
    ).first()

    if not manufacturing:
        raise HTTPException(status_code=404, detail="Manufacturing record not found")

    manufacturing.quality_check_status = request.status
    manufacturing.quality_notes = request.notes
    manufacturing.quality_check_date = datetime.utcnow()
    manufacturing.quality_checked_by = request.checked_by_id

    # If QC fails, prevent production completion
    if request.status == "fail":
        manufacturing.status = "draft"  # Reset to draft for review

    db.commit()
    db.refresh(manufacturing)

    return {
        "id": manufacturing.id,
        "batch_number": manufacturing.batch_number,
        "quality_status": manufacturing.quality_check_status,
        "message": "Quality check recorded. Batch rejected - requires rework" if request.status == "fail" else "Quality check passed"
    }


@router.post("/standard-costs", status_code=201)
async def set_standard_cost(
    request: StandardCostSetRequest,
    db: Session = Depends(get_db)
):
    """
    Set standard costs for variance analysis.

    Use for standard costing method. When actual costs differ >threshold,
    system flags for investigation.
    """
    from app.models.cost_accounting import ManufacturingCostStandard

    # Check if standard already exists for this date
    existing = db.query(ManufacturingCostStandard).filter(
        and_(
            ManufacturingCostStandard.product_id == request.product_id,
            ManufacturingCostStandard.effective_date == request.effective_date
        )
    ).first()

    if existing:
        existing.standard_material_cost = request.standard_material_cost
        existing.standard_labor_cost = request.standard_labor_cost
        existing.standard_overhead_cost = request.standard_overhead_cost
        existing.updated_at = datetime.utcnow()
    else:
        existing = ManufacturingCostStandard(
            id=str(uuid.uuid4()),
            product_id=request.product_id,
            standard_material_cost=request.standard_material_cost,
            standard_labor_cost=request.standard_labor_cost,
            standard_overhead_cost=request.standard_overhead_cost,
            effective_date=request.effective_date
        )
        db.add(existing)

    db.commit()
    db.refresh(existing)

    return {
        "id": existing.id,
        "product_id": existing.product_id,
        "total_standard_cost": (
            request.standard_material_cost +
            request.standard_labor_cost +
            request.standard_overhead_cost
        ),
        "effective_date": existing.effective_date
    }


@router.get("/standard-costs/{product_id}", response_model=Dict[str, Any])
async def get_standard_costs(
    product_id: str,
    db: Session = Depends(get_db)
):
    """
    Get current standard costs for a product.

    Returns latest effective standard if multiple dates exist.
    """
    from app.models.cost_accounting import ManufacturingCostStandard

    # Get most recent effective standard
    standard = db.query(ManufacturingCostStandard).filter(
        and_(
            ManufacturingCostStandard.product_id == product_id,
            ManufacturingCostStandard.effective_date <= date.today()
        )
    ).order_by(desc(ManufacturingCostStandard.effective_date)).first()

    if not standard:
        raise HTTPException(status_code=404, detail="No standard costs defined for this product")

    return {
        "product_id": standard.product_id,
        "standard_material_cost": standard.standard_material_cost,
        "standard_labor_cost": standard.standard_labor_cost,
        "standard_overhead_cost": standard.standard_overhead_cost,
        "total_standard_cost": (
            standard.standard_material_cost +
            standard.standard_labor_cost +
            standard.standard_overhead_cost
        ),
        "effective_date": standard.effective_date
    }


@router.get("/batch-traceability/{batch_number}", response_model=Dict[str, Any])
async def get_batch_traceability(
    batch_number: str,
    db: Session = Depends(get_db)
):
    """
    Get complete traceability for a batch (for recalls/compliance).

    Returns:
    - Batch details
    - Consumed raw materials (with supplier info)
    - Quality checks performed
    - Variance analysis
    - Current inventory location
    """
    from app.models.cost_accounting import ManufacturingCost, ManufacturingVariance
    from app.models.inventory import Product

    manufacturing = db.query(ManufacturingCost).filter(
        ManufacturingCost.batch_number == batch_number
    ).first()

    if not manufacturing:
        raise HTTPException(status_code=404, detail="Batch not found")

    variance = db.query(ManufacturingVariance).filter(
        ManufacturingVariance.batch_number == batch_number
    ).first()

    product = db.query(Product).filter(Product.id == manufacturing.product_id).first()

    return {
        "batch_number": batch_number,
        "product": product.name if product else "Unknown",
        "batch_size": manufacturing.batch_size,
        "lot_start_date": manufacturing.lot_start_date,
        "lot_end_date": manufacturing.lot_end_date,
        "total_cost": manufacturing.total_cost,
        "unit_cost": manufacturing.unit_cost,
        "quality_status": manufacturing.quality_check_status,
        "quality_notes": manufacturing.quality_notes,
        "quality_checked_date": manufacturing.quality_check_date,
        "status": manufacturing.status,
        "variance": {
            "variance_amount": variance.variance_amount if variance else 0,
            "variance_percentage": variance.variance_percentage if variance else 0,
            "variance_type": variance.variance_type if variance else "neutral"
        } if variance else None,
        "created_at": manufacturing.date
    }
