from sqlmodel import Session, func, select

from app.models import FieldInputEvent, ProjectStatus, SavedProject
from app.schemas import StatsSummary


def compute_stats_summary(session: Session) -> StatsSummary:
    total_projects = session.exec(select(func.count(SavedProject.id))).one()
    drafts = session.exec(select(func.count(SavedProject.id)).where(SavedProject.status == ProjectStatus.DRAFT)).one()
    simulations = session.exec(select(func.count(SavedProject.id)).where(SavedProject.status == ProjectStatus.SIMULATION)).one()
    saved_projects = session.exec(select(func.count(SavedProject.id)).where(SavedProject.status == ProjectStatus.PROJECT_SAVED)).one()
    archived = session.exec(select(func.count(SavedProject.id)).where(SavedProject.status == ProjectStatus.ARCHIVED)).one()
    total_input_events = session.exec(select(func.count(FieldInputEvent.id))).one()
    top_error_row = session.exec(
        select(FieldInputEvent.field_name, func.count(FieldInputEvent.id))
        .where(FieldInputEvent.event_type == "error")
        .group_by(FieldInputEvent.field_name)
        .order_by(func.count(FieldInputEvent.id).desc())
    ).first()
    return StatsSummary(
        total_projects=total_projects or 0,
        drafts=drafts or 0,
        simulations=simulations or 0,
        saved_projects=saved_projects or 0,
        archived=archived or 0,
        total_input_events=total_input_events or 0,
        top_error_field=top_error_row[0] if top_error_row else None,
    )