from __future__ import annotations
from datetime import date
from typing import Optional
from fastapi import APIRouter, Depends, Query
from sqlalchemy.orm import Session
from sqlalchemy import func
from app.database import get_db
from app.models.plant import Plant, PlantCategory, PlantVariety
from app.models.production import ProductionBatch, GerminationRecord
from app.models.inventory import InventoryItem
from app.models.supplier import Supplier
from app.models.customer import Customer
from app.models.sale import Sale, SaleItem
from app.models.purchase import PurchaseOrder, PurchaseItem
from app.models.finance import Expense, Income
from app.models.employee import Employee
from app.services.auth import get_current_user

router = APIRouter(prefix="/reports", tags=["reports"])


@router.get("/inventory")
def inventory_report(
    category: Optional[str] = None,
    db: Session = Depends(get_db),
    _=Depends(get_current_user),
):
    q = db.query(InventoryItem)
    if category:
        q = q.filter(InventoryItem.category == category)
    items = q.all()

    total_items = len(items)
    total_value = sum(i.quantity * i.cost_price for i in items)
    low_stock = [i for i in items if i.quantity <= i.min_quantity]
    categories = db.query(InventoryItem.category, func.count(InventoryItem.id)).group_by(InventoryItem.category).all()

    return {
        "total_items": total_items,
        "total_value": round(total_value, 2),
        "low_stock_count": len(low_stock),
        "low_stock_items": [{"id": i.id, "code": i.code, "name": i.name, "quantity": i.quantity, "min_quantity": i.min_quantity} for i in low_stock],
        "by_category": [{"category": c[0], "count": c[1]} for c in categories],
    }


@router.get("/production")
def production_report(
    db: Session = Depends(get_db),
    _=Depends(get_current_user),
):
    total_batches = db.query(func.count(ProductionBatch.id)).scalar() or 0
    active_batches = db.query(func.count(ProductionBatch.id)).filter(
        ProductionBatch.status.notin_(["completed", "sold"])
    ).scalar() or 0
    ready_batches = db.query(func.count(ProductionBatch.id)).filter(
        ProductionBatch.status == "ready"
    ).scalar() or 0

    avg_germination = db.query(func.avg(GerminationRecord.success_rate)).scalar() or 0

    status_breakdown = db.query(
        ProductionBatch.status, func.count(ProductionBatch.id)
    ).group_by(ProductionBatch.status).all()

    return {
        "total_batches": total_batches,
        "active_batches": active_batches,
        "ready_batches": ready_batches,
        "average_germination_rate": round(avg_germination, 2),
        "by_status": [{"status": s[0], "count": s[1]} for s in status_breakdown],
    }


@router.get("/sales")
def sales_report(
    from_date: Optional[date] = Query(None),
    to_date: Optional[date] = Query(None),
    db: Session = Depends(get_db),
    _=Depends(get_current_user),
):
    q = db.query(Sale)
    if from_date:
        q = q.filter(Sale.date >= from_date)
    if to_date:
        q = q.filter(Sale.date <= to_date)
    sales = q.all()

    total_sales = len(sales)
    total_revenue = sum(s.total for s in sales)
    total_paid = sum(s.paid for s in sales)
    total_due = sum(s.due for s in sales)

    top_plants = db.query(
        Plant.name, func.sum(SaleItem.quantity).label("total_sold"), func.sum(SaleItem.total).label("total_revenue")
    ).join(SaleItem, SaleItem.plant_id == Plant.id).join(Sale, Sale.id == SaleItem.sale_id)
    if from_date:
        top_plants = top_plants.filter(Sale.date >= from_date)
    if to_date:
        top_plants = top_plants.filter(Sale.date <= to_date)
    top_plants = top_plants.group_by(Plant.name).order_by(func.sum(SaleItem.quantity).desc()).limit(10).all()

    return {
        "total_sales": total_sales,
        "total_revenue": round(total_revenue, 2),
        "total_paid": round(total_paid, 2),
        "total_due": round(total_due, 2),
        "top_plants": [{"name": p[0], "quantity_sold": p[1], "revenue": round(p[2], 2)} for p in top_plants],
    }


@router.get("/purchases")
def purchases_report(
    from_date: Optional[date] = Query(None),
    to_date: Optional[date] = Query(None),
    db: Session = Depends(get_db),
    _=Depends(get_current_user),
):
    q = db.query(PurchaseOrder)
    if from_date:
        q = q.filter(PurchaseOrder.order_date >= from_date)
    if to_date:
        q = q.filter(PurchaseOrder.order_date <= to_date)
    orders = q.all()

    total_orders = len(orders)
    total_value = sum(o.total for o in orders)
    status_breakdown = {}
    for o in orders:
        status_breakdown[o.status] = status_breakdown.get(o.status, 0) + 1

    return {
        "total_orders": total_orders,
        "total_value": round(total_value, 2),
        "by_status": [{"status": k, "count": v} for k, v in status_breakdown.items()],
    }


@router.get("/profit-loss")
def profit_loss_report(
    from_date: Optional[date] = Query(None),
    to_date: Optional[date] = Query(None),
    db: Session = Depends(get_db),
    _=Depends(get_current_user),
):
    sales_q = db.query(func.coalesce(func.sum(Sale.total), 0))
    income_q = db.query(func.coalesce(func.sum(Income.amount), 0))
    expense_q = db.query(func.coalesce(func.sum(Expense.amount), 0))

    if from_date:
        sales_q = sales_q.filter(Sale.date >= from_date)
        income_q = income_q.filter(Income.date >= from_date)
        expense_q = expense_q.filter(Expense.date >= from_date)
    if to_date:
        sales_q = sales_q.filter(Sale.date <= to_date)
        income_q = income_q.filter(Income.date <= to_date)
        expense_q = expense_q.filter(Expense.date <= to_date)

    sales_total = sales_q.scalar()
    income_total = income_q.scalar()
    expense_total = expense_q.scalar()
    profit = (sales_total + income_total) - expense_total

    return {
        "sales_revenue": round(sales_total, 2),
        "other_income": round(income_total, 2),
        "total_expenses": round(expense_total, 2),
        "net_profit": round(profit, 2),
    }


@router.get("/customers")
def customers_report(db: Session = Depends(get_db), _=Depends(get_current_user)):
    total = db.query(func.count(Customer.id)).scalar() or 0
    active = db.query(func.count(Customer.id)).filter(Customer.is_active == True).scalar() or 0
    total_outstanding = db.query(func.coalesce(func.sum(Customer.outstanding), 0)).scalar()
    total_purchases = db.query(func.coalesce(func.sum(Customer.total_purchases), 0)).scalar()

    by_type = db.query(Customer.customer_type, func.count(Customer.id)).group_by(Customer.customer_type).all()

    return {
        "total_customers": total,
        "active_customers": active,
        "total_outstanding": round(total_outstanding, 2),
        "total_purchases_value": round(total_purchases, 2),
        "by_type": [{"type": t[0], "count": t[1]} for t in by_type],
    }


@router.get("/suppliers")
def suppliers_report(db: Session = Depends(get_db), _=Depends(get_current_user)):
    total = db.query(func.count(Supplier.id)).scalar() or 0
    active = db.query(func.count(Supplier.id)).filter(Supplier.is_active == True).scalar() or 0
    total_balance = db.query(func.coalesce(func.sum(Supplier.balance), 0)).scalar()

    return {
        "total_suppliers": total,
        "active_suppliers": active,
        "total_outstanding": round(total_balance, 2),
    }