Spaces:

arthikrangan
/

text-to-sql-agent-api

Sleeping

text-to-sql-agent-api / app /api /insights.py

arthi.kasturirangan@informa.com

package paths

f6375d6 over 1 year ago

9.18 kB

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))

	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))