from fastapi import APIRouter, HTTPException, Request, Depends, Query
from sqlalchemy.orm import Session
from sqlalchemy import func, desc
from typing import Dict, Any, List, Optional
import logging
from datetime import date, datetime

from src.smart_inventory.apps.inventory.models import DemandForecast
from src.smart_inventory.core.demand_forecaster import CompanyDemandForecaster
from src.smart_inventory.core.prediction_pipeline import forecast_series
from . import schemas, controller

try:
    from src.utils.db import get_db
except ImportError:
    from utils.db import get_db

# Import Celery tasks
try:
    from src.smart_inventory.tasks.daily_sales_task import compute_daily_sales
    from src.smart_inventory.tasks.service_level_task import compute_service_level_daily
    daily_sales_celery_available = True
    service_level_celery_available = True
except ImportError as e:
    logger_temp = logging.getLogger(__name__)
    logger_temp.warning(f"Could not import Celery tasks: {e}")
    compute_daily_sales = None
    compute_service_level_daily = None
    daily_sales_celery_available = False
    service_level_celery_available = False

# Import inventory snapshot task
try:
    from src.smart_inventory.tasks.inventory_snapshot_task import compute_inventory_snapshot
    snapshot_celery_available = True
except ImportError as e:
    logger_temp = logging.getLogger(__name__)
    logger_temp.warning(f"Could not import inventory snapshot task: {e}")
    compute_inventory_snapshot = None
    snapshot_celery_available = False

# Import inventory planning task
try:
    from src.smart_inventory.tasks.inventory_planning_task import compute_inventory_planning_snapshot
    inventory_planning_celery_available = True
except ImportError as e:
    logger_temp = logging.getLogger(__name__)
    logger_temp.warning(f"Could not import inventory planning task: {e}")
    compute_inventory_planning_snapshot = None
    inventory_planning_celery_available = False

# Import demand forecast training task
try:
    from src.smart_inventory.tasks.demand_forecast_task import train_demand_forecast_model
    train_demand_forecast_celery_available = True
except ImportError as e:
    logger_temp = logging.getLogger(__name__)
    logger_temp.warning(f"Could not import demand forecast training task: {e}")
    train_demand_forecast_model = None
    train_demand_forecast_celery_available = False

logger = logging.getLogger(__name__)

router = APIRouter()


@router.post("/webhook/movement")
async def inventory_movement(request: Request) -> Dict[str, Any]:
    """
    Webhook endpoint to receive order data (purchase orders or sales orders)
    
    Handles different types of inventory movements based on the event_type field:
    
    Expected data format for purchase orders:
    {
        "event_type": "purchase_order_received",
        "timestamp": "2025-11-25T21:13:08+05:30",
        "purchase_order": {...},
        "purchase_order_lines": [...],
        "inventory_movements": [...]
    }
    
    Expected data format for sales orders:
    {
        "event_type": "sales_order_completed",
        "timestamp": "2025-11-25T21:13:08+05:30",
        "sales_order": {...},
        "sales_order_lines": [...],
        "inventory_movements": [...]
    }
    """
    try:
        # Parse the incoming JSON data
        data = await request.json()
        
        event_type = data.get('event_type', 'unknown')
        
        logger.info(f"Received order webhook at {datetime.now().isoformat()}")
        logger.info(f"Event type: {event_type}")
        
        # Determine order type and log accordingly
        if event_type == "purchase_order_received":
            order_id = data.get('purchase_order', {}).get('id', 'N/A')
            logger.info(f"Purchase Order ID: {order_id}")
            # todo: add logic to process purchase order data (save to db, update inventory)
        elif event_type == "sales_order_completed":
            order_id = data.get('sales_order', {}).get('id', 'N/A')
            logger.info(f"Sales Order ID: {order_id}")
            # todo: add logic to process sales order data (save to db, update inventory)
        else:
            logger.warning(f"Unknown event type: {event_type}")
        
        # Return the received data
        return {
            "status": "success",
            "message": f"Order data received successfully (event: {event_type})",
            "received_at": datetime.now().isoformat(),
            "event_type": event_type,
            "data": data
        }
        
    except Exception as e:
        logger.error(f"Error processing order webhook: {str(e)}")
        raise HTTPException(
            status_code=400,
            detail=f"Error processing order data: {str(e)}"
        )


# Sales Orders endpoints
@router.get("/sales-orders", response_model=List[schemas.SalesOrderOut])
def get_sales_orders(
    skip: int = Query(0, ge=0, description="Number of sales orders to skip"),
    limit: int = Query(100, ge=1, le=1000, description="Number of sales orders to return"),
    db: Session = Depends(get_db)
):
    """Get sales orders with pagination"""
    try:
        sales_orders = controller.get_sales_orders(db, skip=skip, limit=limit)
        return sales_orders
    except Exception as e:
        logger.error(f"Error fetching sales orders: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching sales orders")


# Sales Order Lines endpoints
@router.get("/sales-order-lines", response_model=List[schemas.SalesOrderLineOut])
def get_sales_order_lines(
    skip: int = Query(0, ge=0, description="Number of sales order lines to skip"),
    limit: int = Query(100, ge=1, le=1000, description="Number of sales order lines to return"),
    db: Session = Depends(get_db)
):
    """Get sales order lines with pagination"""
    try:
        sales_order_lines = controller.get_sales_order_lines(db, skip=skip, limit=limit)
        return sales_order_lines
    except Exception as e:
        logger.error(f"Error fetching sales order lines: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching sales order lines")


# Inventory Batches endpoints
@router.get("/inventory-batches", response_model=List[schemas.InventoryBatchOut])
def get_inventory_batches(
    skip: int = Query(0, ge=0, description="Number of inventory batches to skip"),
    limit: int = Query(100, ge=1, le=1000, description="Number of inventory batches to return"),
    db: Session = Depends(get_db)
):
    """Get inventory batches with pagination"""
    try:
        inventory_batches = controller.get_inventory_batches(db, skip=skip, limit=limit)
        return inventory_batches
    except Exception as e:
        logger.error(f"Error fetching inventory batches: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching inventory batches")


# Inventory Movements endpoints
@router.get("/inventory-movements", response_model=List[schemas.InventoryMovementOut])
def get_inventory_movements(
    skip: int = Query(0, ge=0, description="Number of inventory movements to skip"),
    limit: int = Query(100, ge=1, le=1000, description="Number of inventory movements to return"),
    db: Session = Depends(get_db)
):
    """Get inventory movements with pagination"""
    try:
        inventory_movements = controller.get_inventory_movements(db, skip=skip, limit=limit)
        return inventory_movements
    except Exception as e:
        logger.error(f"Error fetching inventory movements: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching inventory movements")


# Purchase Orders endpoints
@router.get("/purchase-orders", response_model=List[schemas.PurchaseOrderOut])
def get_purchase_orders(
    skip: int = Query(0, ge=0, description="Number of purchase orders to skip"),
    limit: int = Query(100, ge=1, le=1000, description="Number of purchase orders to return"),
    db: Session = Depends(get_db)
):
    """Get purchase orders with pagination"""
    try:
        purchase_orders = controller.get_purchase_orders(db, skip=skip, limit=limit)
        return purchase_orders
    except Exception as e:
        logger.error(f"Error fetching purchase orders: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching purchase orders")


# Purchase Order Lines endpoints
@router.get("/purchase-order-lines", response_model=List[schemas.PurchaseOrderLineOut])
def get_purchase_order_lines(
    skip: int = Query(0, ge=0, description="Number of purchase order lines to skip"),
    limit: int = Query(100, ge=1, le=1000, description="Number of purchase order lines to return"),
    db: Session = Depends(get_db)
):
    """Get purchase order lines with pagination"""
    try:
        purchase_order_lines = controller.get_purchase_order_lines(db, skip=skip, limit=limit)
        return purchase_order_lines
    except Exception as e:
        logger.error(f"Error fetching purchase order lines: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching purchase order lines")


# Daily Sales endpoints
@router.get("/analytics/daily-sales", response_model=schemas.PaginatedDailySalesResponse)
def get_daily_sales(
    page: int = Query(1, ge=1, description="Page number"),
    pagelimit: int = Query(100, ge=1, description="Number of items per page"),
    date: str = Query(None, description="Date in YYYY-MM-DD format to get daily sales for specific date"),
    company_id: int = Query(None, description="Filter by company ID"),
    db: Session = Depends(get_db)
):
    """Get daily sales records with pagination. If date is provided, returns sales for that specific date. If company_id is provided, filters by company."""
    try:
        result = controller.get_daily_sales(db, date=date, company_id=company_id, page=page, perpage=pagelimit)
        return {
            "data": result["data"],
            "page": result["page"],
            "perpage": result["perpage"],
            "total": result["total"]
        }
    except Exception as e:
        logger.error(f"Error fetching daily sales: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching daily sales")


@router.post("/celery/compute-daily-sales")
def trigger_compute_daily_sales():
    """Trigger daily sales computation using Celery task"""
    if not daily_sales_celery_available:
        raise HTTPException(
            status_code=503,
            detail="Celery not available. Daily sales computation service is currently unavailable."
        )
    
    try:
        # Trigger the celery task
        task = compute_daily_sales.delay()
        
        return {
            "task_id": task.id,
            "status": "started",
            "message": "Daily sales computation task started"
        }
        
    except Exception as e:
        logger.error(f"Error starting daily sales computation task: {str(e)}")
        raise HTTPException(status_code=500, detail="Error starting daily sales computation task")


@router.post("/celery/compute-service-level")
def trigger_compute_service_level(
    target_date: str = Query(None, description="Target date in YYYY-MM-DD format. If not provided, uses today's date")
):
    """Trigger service level computation using Celery task"""
    if not service_level_celery_available:
        raise HTTPException(
            status_code=503,
            detail="Celery not available. Service level computation service is currently unavailable."
        )
    
    try:
        # Validate date format if provided
        if target_date:
            from datetime import datetime
            try:
                datetime.strptime(target_date, "%Y-%m-%d")
            except ValueError:
                raise HTTPException(status_code=400, detail="Invalid date format. Use YYYY-MM-DD")
        
        # Trigger the celery task
        task = compute_service_level_daily.delay(target_date)
        
        date_msg = f" for {target_date}" if target_date else " for today"
        
        return {
            "task_id": task.id,
            "status": "started",
            "message": f"Service level computation task started{date_msg}",
            "target_date": target_date
        }
        
    except Exception as e:
        logger.error(f"Error starting service level computation task: {str(e)}")
        raise HTTPException(status_code=500, detail="Error starting service level computation task")


@router.post("/celery/compute-inventory-snapshot")
def trigger_compute_inventory_snapshot(
    target_date: str = Query(None, description="Target date in YYYY-MM-DD format. If not provided, uses yesterday's date")
):
    """Trigger daily inventory snapshot computation using Celery task"""
    if not snapshot_celery_available:
        raise HTTPException(
            status_code=503,
            detail="Celery not available. Inventory snapshot computation service is currently unavailable."
        )
    
    try:
        # Validate date format if provided
        if target_date:
            from datetime import datetime
            try:
                datetime.strptime(target_date, "%Y-%m-%d")
            except ValueError:
                raise HTTPException(status_code=400, detail="Invalid date format. Use YYYY-MM-DD")
        
        # Trigger the celery task
        task = compute_inventory_snapshot.delay(target_date)
        
        date_msg = f" for {target_date}" if target_date else " for yesterday"
        
        return {
            "task_id": task.id,
            "status": "started",
            "message": f"Inventory snapshot computation task started{date_msg}",
            "target_date": target_date
        }
        
    except Exception as e:
        logger.error(f"Error starting inventory snapshot computation task: {str(e)}")
        raise HTTPException(status_code=500, detail="Error starting inventory snapshot computation task")


@router.get("/inventory-snapshots", response_model=schemas.PaginatedInventorySnapshotResponse)
def get_inventory_snapshots(
    page: int = Query(1, ge=1, description="Page number"),
    perpage: int = Query(100, ge=1, description="Number of items per page"),
    date: str = Query(None, description="Date in YYYY-MM-DD format to get snapshots for specific date"),
    db: Session = Depends(get_db)
):
    """Get inventory snapshot records with pagination. If date is provided, returns snapshots for that specific date."""
    try:
        result = controller.get_inventory_snapshots(db, date=date, page=page, perpage=perpage)
        return {
            "data": result["data"],
            "page": result["page"],
            "perpage": result["perpage"],
            "total": result["total"]
        }
    except Exception as e:
        logger.error(f"Error fetching inventory snapshots: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching inventory snapshots")


@router.get("/testing/inventory-planning-snapshots")
def get_inventory_planning_snapshots(
    page: int = Query(1, ge=1, description="Page number"),
    perpage: int = Query(100, ge=1, le=1000, description="Number of items per page"),
    date: str = Query(None, description="Date in YYYY-MM-DD format to get snapshots for specific date"),
    company_id: int = Query(None, description="Filter by company ID"),
    location_id: int = Query(None, description="Filter by location ID"),
    product_id: int = Query(None, description="Filter by product ID"),
    db: Session = Depends(get_db)
):
    """Get inventory planning snapshot records with pagination and filters. If date is provided, returns snapshots for that specific date."""
    try:
        result = controller.get_inventory_planning_snapshots(
            db, 
            date=date, 
            page=page, 
            perpage=perpage,
            company_id=company_id,
            location_id=location_id,
            product_id=product_id
        )
        return {
            "data": result["data"],
            "page": result["page"],
            "perpage": result["perpage"],
            "total": result["total"]
        }
    except Exception as e:
        logger.error(f"Error fetching inventory planning snapshots: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching inventory planning snapshots")



# Service Level endpoints
@router.get("/testing/service-level", response_model=schemas.PaginatedServiceLevelResponse)
def get_service_level(
    page: int = Query(..., ge=1, description="Page number"),
    perpage: int = Query(..., ge=1, le=100, description="Number of items per page"),
    date: str = Query(None, description="Date in YYYY-MM-DD format to get service level for specific date"),
    db: Session = Depends(get_db)
):
    """Get service level records with pagination. If date is provided, returns service level for that specific date."""
    try:
        result = controller.get_service_level(db, date=date, page=page, perpage=perpage)
        return {
            "data": result["data"],
            "page": result["page"],
            "perpage": result["perpage"],
            "total": result["total"]
        }
    except Exception as e:
        logger.error(f"Error fetching service level: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching service level")


# Analytics endpoints
@router.get("/analytics/service-level-vs-demand", response_model=schemas.ServiceLevelVsDemandResponse, tags=["Smart Inventory - Frontend"])
def get_service_level_vs_demand(
    company_id: int = Query(..., description="Company ID (required)"),
    location_id: int = Query(None, description="Filter by location ID (optional)"),
    product_id: int = Query(None, description="Filter by product ID (optional)"),
    month_from: str = Query(None, description="Start month in YYYY-MM format (optional, defaults to 11 months ago)"),
    month_to: str = Query(None, description="End month in YYYY-MM format (optional, defaults to current month)"),
    db: Session = Depends(get_db)
):
    """
    Get month-wise service level vs demand analytics.
    
    Returns aggregated monthly data:
    - demand_qty: Total demand quantity for the month (integer)
    - fulfilled_qty: Total fulfilled quantity for the month (integer)
    - lost_sales_qty: Total lost sales quantity for the month (integer)
    - service_level: fulfilled_qty / demand_qty (0.0 - 1.0, or 1.0 if no demand)
    
    Date Range:
    - month_from: Start month (YYYY-MM). Defaults to 11 months ago if not provided
    - month_to: End month (YYYY-MM). Defaults to current month if not provided
    - If neither provided, returns last 12 months of data
    
    Filters:
    - company_id: Required - filters data for specific company
    - location_id: Optional - if provided, only data for that location is included
    - product_id: Optional - if provided, only data for that product is included
    - If both location_id and product_id are omitted, aggregates across all locations and products
    """
    try:
        result_data = controller.get_analytics_service_level_vs_demand(
            db=db,
            company_id=company_id,
            location_id=location_id,
            product_id=product_id,
            month_from=month_from,
            month_to=month_to
        )
        
        return {
            "success": True,
            "data": result_data,
            "message": "Service level vs demand analytics retrieved successfully"
        }
        
    except ValueError as e:
        logger.error(f"Validation error for service level vs demand analytics: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Validation error",
            error_code="VALIDATION_ERROR",
            details={"error": str(e)}
        )
    except Exception as e:
        logger.error(f"Error fetching service level vs demand analytics: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Error fetching service level analytics",
            error_code="DATABASE_ERROR",
            details={"error": str(e)}
        )


# SLOW MOVER ENDPOINTS
@router.post("/celery/compute-slow-movers")
def trigger_compute_slow_movers(
    target_date: str = Query(None, description="Target date in YYYY-MM-DD format. If not provided, uses today's date")
):
    """Trigger slow mover computation using Celery task"""
    try:
        from src.smart_inventory.tasks.slow_movers_task import compute_slow_movers_90d
    except ImportError:
        raise HTTPException(
            status_code=503,
            detail="Celery task not available."
        )

    try:
        # Validate date format if provided
        if target_date:
            from datetime import datetime
            try:
                datetime.strptime(target_date, "%Y-%m-%d")
            except ValueError:
                raise HTTPException(status_code=400, detail="Invalid date format. Use YYYY-MM-DD")
        
        # Trigger the celery task
        task = compute_slow_movers_90d.delay(target_date)
        
        date_msg = f" for {target_date}" if target_date else " for today"
        
        return {
            "task_id": task.id,
            "status": "started",
            "message": f"Slow mover computation task started{date_msg}",
            "target_date": target_date
        }
        
    except Exception as e:
        logger.error(f"Error starting slow mover computation task: {str(e)}")
        raise HTTPException(status_code=500, detail="Error starting slow mover computation task")


@router.get("/slow-movers", response_model=schemas.SlowMoverResponse, tags=["Smart Inventory - Frontend"])
def get_slow_movers(
    page: int = Query(1, ge=1, description="Page number"),
    perpage: int = Query(10, ge=1, description="Number of items per page"),
    date: str = Query(None, description="Date in YYYY-MM-DD format. If not provided, defaults to today's date"),
    company_id: int = Query(None, description="Filter by company ID"),
    location_id: int = Query(None, description="Filter by location ID"),
    product_id: int = Query(None, description="Filter by product ID"),
    is_slow_mover: bool = Query(None, description="Filter by is_slow_mover flag"),
    db: Session = Depends(get_db)
):
    """Get slow mover snapshots with pagination and filters. If date is not provided, returns data for today's date."""
    try:
        result = controller.get_slow_movers(
            db, 
            page=page, 
            perpage=perpage, 
            date=date, 
            company_id=company_id, 
            location_id=location_id, 
            product_id=product_id,
            is_slow_mover=is_slow_mover
        )
        
        return {
            "success": True,
            "data": result,
            "message": "Slow movers retrieved successfully"
        }
        
    except HTTPException:
        # Re-raise HTTP exceptions as they are
        raise
    except ValueError as e:
        logger.error(f"Invalid data format for slow movers: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Invalid data format",
            error_code="INVALID_DATA",
            details={"error": str(e)}
        )
    except Exception as e:
        logger.error(f"Error fetching slow movers: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Error fetching slow movers",
            error_code="DATABASE_ERROR",
            details={"error": str(e)}
        )


@router.get("/planning")
def get_inventory_planning(
    page: int = Query(1, ge=1, description="Page number"),
    perpage: int = Query(100, ge=1, description="Number of items per page"),
    company_id: int = Query(None, description="Filter by company ID"),
    location_id: int = Query(None, description="Filter by location ID"),
    product_id: int = Query(None, description="Filter by product ID"),
    status: str = Query(None, description="Filter by status (out_of_stock, low_stock, in_stock, slow_mover, dead_stock)"),
    db: Session = Depends(get_db)
):
    """
    Get inventory planning data: stock levels, reorder points, and status.
    """
    try:
        result = controller.get_inventory_planning(
            db, 
            page=page, 
            perpage=perpage, 
            company_id=company_id, 
            location_id=location_id, 
            product_id=product_id, 
            status=status
        )
        return result
    except Exception as e:
        logger.error(f"Error fetching inventory planning: {str(e)}")
        raise HTTPException(status_code=500, detail="Error fetching inventory planning")


@router.post("/celery/compute-inventory-planning")
def trigger_compute_inventory_planning(
    snapshot_date: str = Query(None, description="Snapshot date in YYYY-MM-DD format. If not provided, uses today's date")
):
    """Trigger inventory planning snapshot computation using Celery task"""
    if not inventory_planning_celery_available:
        raise HTTPException(
            status_code=503,
            detail="Celery not available. Inventory planning computation service is currently unavailable."
        )
    
    try:
        # Validate date format if provided
        if snapshot_date:
            from datetime import datetime
            try:
                datetime.strptime(snapshot_date, "%Y-%m-%d")
            except ValueError:
                raise HTTPException(status_code=400, detail="Invalid date format. Use YYYY-MM-DD")
        
        # Trigger the celery task
        task = compute_inventory_planning_snapshot.delay(snapshot_date)
        
        date_msg = f" for {snapshot_date}" if snapshot_date else " for today"
        
        return {
            "task_id": task.id,
            "status": "started",
            "message": f"Inventory planning snapshot computation task started{date_msg}",
            "snapshot_date": snapshot_date
        }
        
    except Exception as e:
        logger.error(f"Error starting inventory planning computation task: {str(e)}")
        raise HTTPException(status_code=500, detail="Error starting inventory planning computation task")


@router.post("/celery/train-demand-forecast", response_model=schemas.TrainDemandForecastResponse)
def trigger_train_demand_forecast(
    company_id: int = Query(..., description="Company ID to train the model for")
):
    """Trigger demand forecast model training using Celery task"""
    if not train_demand_forecast_celery_available:
        raise HTTPException(
            status_code=503,
            detail="Celery not available. Demand forecast training service is currently unavailable."
        )
    
    try:
        # Trigger the celery task
        task = train_demand_forecast_model.delay(company_id)
        
        return {
            "task_id": task.id,
            "status": "started",
            "message": f"Demand forecast model training task started for company {company_id}",
            "company_id": company_id
        }
        
    except Exception as e:
        logger.error(f"Error starting demand forecast training task: {str(e)}")
        raise HTTPException(status_code=500, detail="Error starting demand forecast training task")


@router.post("/predict", response_model=schemas.ForecastResponse)
def forecast_and_store(
    payload: schemas.ForecastRequest,
    db: Session = Depends(get_db),
):
    """
    Generate and store demand forecast predictions.
    Automatically deletes existing forecasts for the same date range to avoid duplicates.
    """
    try:
        # Calculate horizon_days from the date range
        horizon_days = (payload.end_date - payload.start_date).days + 1
        
        # Generate predictions
        df_preds = forecast_series(
            company_id=payload.company_id,
            location_id=payload.location_id,
            product_id=payload.product_id,
            horizon_days=horizon_days,
        )
    except FileNotFoundError:
        logger.error(f"Model not found for company_id={payload.company_id}")
        raise HTTPException(
            status_code=400,
            detail=f"No model found for company_id={payload.company_id}. Train it first.",
        )
    except Exception as e:
        logger.error(f"Error generating forecast: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Error generating forecast",
            error_code="FORECAST_GENERATION_ERROR",
            details={"error": str(e)}
        )

    # Save forecasts to database (controller handles deletion of old forecasts)
    try:
        saved_count = controller.save_demand_forecast(
            db=db,
            company_id=payload.company_id,
            location_id=payload.location_id,
            product_id=payload.product_id,
            df_predictions=df_preds,
            model_version="v1"
        )
        
        logger.info(f"Successfully saved {saved_count} forecast records")
    except ValueError as e:
        logger.error(f"Validation error saving forecasts: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Invalid forecast data",
            error_code="VALIDATION_ERROR",
            details={"error": str(e)}
        )
    except Exception as e:
        logger.error(f"Error saving forecasts to database: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Error saving forecasts to database",
            error_code="DATABASE_ERROR",
            details={"error": str(e)}
        )

    # Build response
    return schemas.ForecastResponse(
        company_id=payload.company_id,
        location_id=payload.location_id,
        product_id=payload.product_id,
        forecasts=[
            schemas.ForecastResponseItem(
                date=row["date"].date(),
                forecast_qty=float(row["forecast_qty"])
            )
            for _, row in df_preds.iterrows()
        ],
    )


@router.get("/analytics/overview", response_model=schemas.AnalyticsOverviewResponse,  tags=["Smart Inventory - Frontend"])
def get_analytics_overview(
    company_id: int = Query(..., description="Company ID"),
    location_id: int = Query(None, description="Location ID (optional)"),
    product_id: int = Query(None, description="Product ID (optional)"),
    date: str = Query(None, description="Date in YYYY-MM-DD format to get slow movers for specific date if no date is provided, uses today's date"),
    db: Session = Depends(get_db),
):
    try:
        # Use today's date if no date is provided
        if not date:
            from datetime import date as date_class
            date = date_class.today().strftime("%Y-%m-%d")
        
        result = controller.get_analytics_overview(
            db, 
            date=date, 
            company_id=company_id, 
            location_id=location_id, 
            product_id=product_id,
        )
        
        return {
            "success": True,
            "data": result,
            "message": "Analytics overview retrieved successfully"
        }
        
    except Exception as e:
        logger.error(f"Error fetching analytics overview: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Error fetching analytics overview",
            error_code="DATABASE_ERROR",
            details={"error": str(e)}
        )


@router.get("/analytics/kpi-summary", response_model=schemas.KPISummaryResponse, tags=["Smart Inventory - Frontend"])
def get_kpi_summary(
    company_id: int = Query(..., description="Company ID"),
    location_id: int = Query(None, description="Location ID (optional)"),
    date: str = Query(None, description="Date in YYYY-MM-DD format (optional, defaults to latest available data)"),
    db: Session = Depends(get_db),
):
    """
    Get KPI summary for frontend dashboard cards.
    
    Returns aggregated metrics:
    - avg_service_level: Average service level (0.0 - 1.0)
    - stockouts_count: Number of products currently out of stock
    - overstock_count: Number of overstocked products
    - understock_count: Number of understocked products  
    - slow_mover_count: Number of slow-moving products
    - total_products_tracked: Total number of products being tracked
    - avg_days_on_hand: Average days of inventory on hand (stock turns indicator)
    """
    try:
        result = controller.get_kpi_summary(
            db=db,
            company_id=company_id,
            date=date,
            location_id=location_id,
        )
        
        return {
            "success": True,
            "data": result,
            "message": "KPI summary retrieved successfully"
        }
        
    except ValueError as e:
        logger.error(f"Validation error for KPI summary: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Validation error",
            error_code="VALIDATION_ERROR",
            details={"error": str(e)}
        )
    except Exception as e:
        logger.error(f"Error fetching KPI summary: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Error fetching KPI summary",
            error_code="DATABASE_ERROR",
            details={"error": str(e)}
        )
        

@router.get("/analytics/stock-vs-demand", response_model=schemas.StockVsDemandResponse, tags=["Smart Inventory - Frontend"])
def get_analytics_stock_vs_demand(
    company_id: int = Query(..., description="Company ID (required)"),
    location_id: int = Query(None, description="Filter by location ID (optional, aggregates all locations if not provided)"),
    product_id: int = Query(None, description="Filter by product ID (optional, aggregates all products if not provided)"),
    month_from: str = Query(None, description="Start month in YYYY-MM format (optional, defaults to 11 months ago)"),
    month_to: str = Query(None, description="End month in YYYY-MM format (optional, defaults to current month)"),
    db: Session = Depends(get_db)
):
    """Get month-wise stock vs demand analysis.
    
    Returns aggregated monthly data:
    - avg_daily_demand: Average daily demand across all dates in the month (integer)
    - available_stock: Average available stock across all dates in the month (integer)
    
    Date Range:
    - month_from: Start month (YYYY-MM). Defaults to 11 months ago if not provided
    - month_to: End month (YYYY-MM). Defaults to current month if not provided
    - If neither provided, returns last 12 months of data
    
    Filters:
    - company_id: Required - filters data for specific company
    - location_id: Optional - if provided, only data for that location is included
    - product_id: Optional - if provided, only data for that product is included
    - If both location_id and product_id are omitted, aggregates across all locations and products
    """
    try:
        result = controller.get_analytics_stock_vs_demand(
            db, 
            company_id=company_id, 
            location_id=location_id, 
            product_id=product_id,
            month_from=month_from,
            month_to=month_to
        )
        
        return {
            "success": True,
            "data": result,
            "message": "Stock vs demand analysis retrieved successfully"
        }
        
    except HTTPException:
        # Re-raise HTTP exceptions as they are
        raise
    except ValueError as e:
        logger.error(f"Invalid data format for stock vs demand analysis: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Invalid data format",
            error_code="INVALID_DATA",
            details={"error": str(e)}
        )
    except Exception as e:
        logger.error(f"Error fetching stock vs demand analysis: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Error fetching stock vs demand analysis",
            error_code="DATABASE_ERROR",
            details={"error": str(e)}
        )


@router.get("/analytics/fastest-moving", response_model=schemas.FastestMovingResponse, tags=["Smart Inventory - Frontend"])
def get_fastest_moving_products(
    company_id: int = Query(..., description="Company ID (required)"),
    location_id: Optional[int] = Query(None, description="Filter by location ID (optional)"),
    category_id: Optional[int] = Query(None, description="Filter by category ID (optional)"),
    limit: int = Query(10, ge=1, le=100, description="Number of products to return (default 10, max 100)"),
    db: Session = Depends(get_db)
):
    """Get fastest moving products by ads_30d (highest sales velocity)."""
    try:
        result = controller.get_fastest_moving_products(
            db=db,
            company_id=company_id,
            location_id=location_id,
            category_id=category_id,
            limit=limit
        )
        return result
        
    except ValueError as ve:
        raise HTTPException(status_code=400, detail=str(ve))
    except Exception as e:
        logger.error(f"Error fetching fastest moving products: {str(e)}")
        raise HTTPException(
            status_code=500,
            detail=f"Error fetching fastest moving products: {str(e)}"
        )


@router.get("/analytics/most-stagnant", response_model=schemas.MostStagnantResponse, tags=["Smart Inventory - Frontend"])
def get_most_stagnant_products_endpoint(
    company_id: int = Query(..., description="Company ID (required)"),
    location_id: Optional[int] = Query(None, description="Filter by location ID (optional)"),
    category_id: Optional[int] = Query(None, description="Filter by category ID (optional)"),
    limit: int = Query(10, ge=1, le=100, description="Number of products to return (default 10, max 100)"),
    db: Session = Depends(get_db)
):
    """Get most stagnant products by ads_90d (lowest sales velocity) with excess stock calculations."""
    try:
        result = controller.get_most_stagnant_products(
            db=db,
            company_id=company_id,
            location_id=location_id,
            category_id=category_id,
            limit=limit
        )
        # FastAPI will automatically transform the dict to MostStagnantResponse schema
        return result
        
    except ValueError as ve:
        raise HTTPException(status_code=400, detail=str(ve))
    except Exception as e:
        logger.error(f"Error fetching most stagnant products: {str(e)}")
        raise HTTPException(
            status_code=500,
            detail=f"Error fetching most stagnant products: {str(e)}"
        )


@router.get("/analytics/most-urgent", response_model=schemas.MostUrgentResponse, tags=["Smart Inventory - Frontend"])
def get_most_urgent_products_endpoint(
    company_id: int = Query(..., description="Company ID (required)"),
    location_id: Optional[int] = Query(None, description="Filter by location ID (optional)"),
    category_id: Optional[int] = Query(None, description="Filter by category ID (optional)"),
    limit: int = Query(10, ge=1, le=100, description="Number of products to return (default 10, max 100)"),
    db: Session = Depends(get_db)
):
    """Get most urgent order requirements based on urgency score (days of cover vs lead time)."""
    try:
        result = controller.get_most_urgent_products(
            db=db,
            company_id=company_id,
            location_id=location_id,
            category_id=category_id,
            limit=limit
        )
        return result
        
    except ValueError as ve:
        raise HTTPException(status_code=400, detail=str(ve))
    except Exception as e:
        logger.error(f"Error fetching most urgent products: {str(e)}")
        raise HTTPException(
            status_code=500,
            detail=f"Error fetching most urgent products: {str(e)}"
        )


# @router.get("/demand-forecasts", response_model=schemas.PaginatedDemandForecastResponse, tags=["Smart Inventory - Frontend"])
# def get_demand_forecasts_endpoint(
#     company_id: int = Query(..., description="Company ID (required)"),
#     location_id: int = Query(None, description="Filter by location ID"),
#     product_id: int = Query(None, description="Filter by product ID"),
#     date_from: str = Query(None, description="Start date for target_date (YYYY-MM-DD format)"),
#     date_to: str = Query(None, description="End date for target_date (YYYY-MM-DD format)"),
#     page: int = Query(1, ge=1, description="Page number"),
#     perpage: int = Query(100, ge=1, le=1000, description="Number of items per page"),
#     db: Session = Depends(get_db)
# ):
#     """
#     Get demand forecasts with pagination and filters.
    
#     This endpoint retrieves demand forecast data created by the /predict endpoint.
#     You can filter by company, location, product, and date range.
    
#     Args:
#         company_id: Company ID (required)
#         location_id: Filter by location ID (optional)
#         product_id: Filter by product ID (optional)
#         date_from: Start date for target_date in YYYY-MM-DD format (optional)
#         date_to: End date for target_date in YYYY-MM-DD format (optional)
#         page: Page number (default: 1)
#         perpage: Items per page (default: 100, max: 1000)
    
#     Returns:
#         Paginated demand forecast data with product and location names
#     """
#     try:
#         result = controller.get_demand_forecasts(
#             db=db,
#             company_id=company_id,
#             location_id=location_id,
#             product_id=product_id,
#             date_from=date_from,
#             date_to=date_to,
#             page=page,
#             perpage=perpage
#         )
#         return result
        
#     except ValueError as e:
#         logger.error(f"Validation error for demand forecasts: {str(e)}")
#         raise HTTPException(status_code=400, detail=str(e))
#     except Exception as e:
#         logger.error(f"Error fetching demand forecasts: {str(e)}")
#         import traceback
#         logger.error(f"Traceback: {traceback.format_exc()}")
#         raise HTTPException(status_code=500, detail="Error fetching demand forecasts")


@router.get("", response_model=schemas.InventoryResponse, tags=["Smart Inventory - Frontend"])
def get_inventory_details(
    page: int = Query(1, ge=1, description="Page number"),
    perpage: int = Query(10, ge=1, description="Number of items per page"),
    company_id: int = Query(None, description="Filter by company ID"),
    location_id: int = Query(None, description="Filter by location ID"),
    product_id: int = Query(None, description="Filter by product ID"),
    status: str = Query(None, description="Filter by status (in_stock, low_stock, out_of_stock, slow_mover, dead_stock)"),
    db: Session = Depends(get_db)
):
    """Get comprehensive inventory details with joins across multiple tables."""
    try:
        result = controller.get_inventory_details(
            db=db,
            page=page,
            perpage=perpage,
            company_id=company_id,
            location_id=location_id,
            product_id=product_id,
            status_filter=status
        )
        
        # Ensure result is not None
        if result is None:
            logger.warning("Controller returned None result")
            result = {
                "items": [],
                "page": page,
                "perpage": perpage,
                "total": 0
            }
        
        return {
            "success": True,
            "data": result,
            "message": "Inventory details retrieved successfully"
        }
        
    except ValueError as e:
        logger.error(f"Validation error for inventory details: {str(e)}")
        return schemas.ErrorResponse(
            success=False,
            message="Validation error",
            error_code="VALIDATION_ERROR",
            details={"error": str(e)}
        )
    except Exception as e:
        logger.error(f"Error fetching inventory details: {str(e)}")
        import traceback
        logger.error(f"Traceback: {traceback.format_exc()}")
        return schemas.ErrorResponse(
            success=False,
            message="Error fetching inventory details",
            error_code="DATABASE_ERROR",
            details={"error": str(e)}
        )