app/api/insights.py

from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy import desc, distinct, func
from sqlalchemy.orm import Session

from app.db.database import get_db
from app.db.models import (
    Actor,
    Address,
    Category,
    City,
    Country,
    Customer,
    Film,
    Inventory,
    Payment,
    Rental,
    Store,
)

router = APIRouter()


@router.get("/insights")
async def get_insights(db: Session = Depends(get_db)):
    try:
        # TODO: Implement insights generation logic
        return {"status": "success", "insights": []}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/insights/top-films")
async def get_top_films(limit: int = 10, db: Session = Depends(get_db)):
    try:
        # Get top films by rental count
        top_films = (
            db.query(
                Film.title,
                func.count(Rental.rental_id).label("rental_count"),
                Film.rental_rate,
                func.sum(Payment.amount).label("total_revenue"),
            )
            .join(Rental, Film.film_id == Rental.inventory_id)
            .join(Payment, Rental.rental_id == Payment.rental_id)
            .group_by(Film.film_id)
            .order_by(desc("rental_count"))
            .limit(limit)
            .all()
        )

        return {
            "status": "success",
            "data": [
                {
                    "title": film.title,
                    "rental_count": film.rental_count,
                    "rental_rate": float(film.rental_rate),
                    "total_revenue": float(film.total_revenue),
                }
                for film in top_films
            ],
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/insights/category-performance")
async def get_category_performance(db: Session = Depends(get_db)):
    try:
        # Get performance metrics by category
        category_stats = (
            db.query(
                Category.name,
                func.count(Film.film_id).label("film_count"),
                func.avg(Film.rental_rate).label("avg_rental_rate"),
                func.sum(Payment.amount).label("total_revenue"),
            )
            .join(Film, Category.category_id == Film.film_id)
            .join(Rental, Film.film_id == Rental.inventory_id)
            .join(Payment, Rental.rental_id == Payment.rental_id)
            .group_by(Category.category_id)
            .all()
        )

        return {
            "status": "success",
            "data": [
                {
                    "category": cat.name,
                    "film_count": cat.film_count,
                    "avg_rental_rate": float(cat.avg_rental_rate),
                    "total_revenue": float(cat.total_revenue),
                }
                for cat in category_stats
            ],
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/insights/customer-activity")
async def get_customer_activity(limit: int = 10, db: Session = Depends(get_db)):
    try:
        # Get most active customers
        active_customers = (
            db.query(
                Customer.first_name,
                Customer.last_name,
                func.count(Rental.rental_id).label("rental_count"),
                func.sum(Payment.amount).label("total_spent"),
            )
            .join(Rental, Customer.customer_id == Rental.customer_id)
            .join(Payment, Rental.rental_id == Payment.rental_id)
            .group_by(Customer.customer_id)
            .order_by(desc("total_spent"))
            .limit(limit)
            .all()
        )

        return {
            "status": "success",
            "data": [
                {
                    "customer_name": f"{cust.first_name} {cust.last_name}",
                    "rental_count": cust.rental_count,
                    "total_spent": float(cust.total_spent),
                }
                for cust in active_customers
            ],
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/insights/store-performance")
async def get_store_performance(db: Session = Depends(get_db)):
    try:
        # Get store performance metrics
        store_stats = (
            db.query(
                Store.store_id,
                func.count(Rental.rental_id).label("rental_count"),
                func.sum(Payment.amount).label("total_revenue"),
                func.avg(Payment.amount).label("avg_transaction"),
            )
            .join(Rental, Store.store_id == Rental.staff_id)
            .join(Payment, Rental.rental_id == Payment.rental_id)
            .group_by(Store.store_id)
            .all()
        )

        return {
            "status": "success",
            "data": [
                {
                    "store_id": store.store_id,
                    "rental_count": store.rental_count,
                    "total_revenue": float(store.total_revenue),
                    "avg_transaction": float(store.avg_transaction),
                }
                for store in store_stats
            ],
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/insights/actor-popularity")
async def get_actor_popularity(limit: int = 10, db: Session = Depends(get_db)):
    try:
        # Get most popular actors based on film rentals
        popular_actors = (
            db.query(
                Actor.first_name,
                Actor.last_name,
                func.count(Rental.rental_id).label("rental_count"),
                func.sum(Payment.amount).label("total_revenue"),
            )
            .join(Film, Actor.actor_id == Film.film_id)
            .join(Rental, Film.film_id == Rental.inventory_id)
            .join(Payment, Rental.rental_id == Payment.rental_id)
            .group_by(Actor.actor_id)
            .order_by(desc("rental_count"))
            .limit(limit)
            .all()
        )

        return {
            "status": "success",
            "data": [
                {
                    "actor_name": f"{actor.first_name} {actor.last_name}",
                    "rental_count": actor.rental_count,
                    "total_revenue": float(actor.total_revenue),
                }
                for actor in popular_actors
            ],
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/insights/sales-overview")
async def get_sales_overview(db: Session = Depends(get_db)):
    try:
        # Get monthly sales data for the past year
        sales_data = (
            db.query(
                func.strftime("%Y-%m", Payment.payment_date).label("date"),
                func.sum(Payment.amount).label("Sales"),
                func.sum(Payment.amount * 0.7).label("Profit"),  # Assuming 70% profit margin
                func.sum(Payment.amount * 0.3).label("Expenses"),  # Assuming 30% expenses
                func.count(distinct(Rental.customer_id)).label("Customers"),
            )
            .join(Rental, Payment.rental_id == Rental.rental_id)
            .group_by(func.strftime("%Y-%m", Payment.payment_date))
            .order_by(func.strftime("%Y-%m", Payment.payment_date))
            .limit(12)
            .all()
        )

        return {
            "status": "success",
            "data": [
                {
                    "date": sale.date,
                    "Sales": float(sale.Sales),
                    "Profit": float(sale.Profit),
                    "Expenses": float(sale.Expenses),
                    "Customers": sale.Customers,
                }
                for sale in sales_data
            ],
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/insights/regional-sales")
async def get_regional_sales(db: Session = Depends(get_db)):
    try:
        # Get sales data by country
        regional_data = (
            db.query(
                Country.country.label("region"),
                func.sum(Payment.amount).label("sales"),
                func.count(distinct(Rental.customer_id)).label("marketShare"),
            )
            .join(City, Country.country_id == City.country_id)
            .join(Address, City.city_id == Address.city_id)
            .join(Store, Address.address_id == Store.address_id)
            .join(Inventory, Store.store_id == Inventory.store_id)
            .join(Rental, Inventory.inventory_id == Rental.inventory_id)
            .join(Payment, Rental.rental_id == Payment.rental_id)
            .group_by(Country.country)
            .order_by(func.sum(Payment.amount).desc())
            .all()
        )

        return {
            "status": "success",
            "data": [
                {"region": region.region, "sales": float(region.sales), "marketShare": region.marketShare}
                for region in regional_data
            ],
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))