from datetime import datetime, timedelta, timezone

from sqlalchemy import func, select
from sqlalchemy.orm import Session

from app.db.models import AnalysisTask, Child, Report, TrainingPlan, TrainingRecord, Video


def _tenant_child_ids_subquery(tenant_id: int):
    return select(Child.id).where(Child.tenant_id == tenant_id).scalar_subquery()


def get_dashboard_metrics(db: Session, tenant_id: int | None = None) -> dict:
    since = datetime.now(timezone.utc) - timedelta(days=30)

    child_q = select(func.count()).select_from(Child).where(Child.status == "active")
    video_q = select(func.count()).select_from(Video)
    report_q = select(func.count()).select_from(Report).where(Report.status == "published")
    plan_q = select(func.count()).select_from(TrainingPlan).where(TrainingPlan.status == "active")
    new_child_q = select(func.count()).select_from(Child).where(Child.created_at >= since)
    succeeded_q = select(func.count()).select_from(AnalysisTask).where(
        AnalysisTask.status == "SUCCEEDED"
    )
    reassessment_q = select(func.count()).select_from(AnalysisTask).where(
        AnalysisTask.task_type == "reassessment", AnalysisTask.status == "SUCCEEDED"
    )
    pending_review_q = select(func.count()).select_from(Report).where(Report.reviewed_by.is_(None))
    pending_training_q = select(func.count()).select_from(TrainingRecord).where(
        TrainingRecord.score.is_not(None)
    )

    if tenant_id:
        tenant_children = _tenant_child_ids_subquery(tenant_id)
        child_q = child_q.where(Child.tenant_id == tenant_id)
        new_child_q = new_child_q.where(Child.tenant_id == tenant_id)
        video_q = video_q.where(Video.child_id.in_(tenant_children))
        report_q = report_q.where(Report.child_id.in_(tenant_children))
        plan_q = plan_q.where(TrainingPlan.child_id.in_(tenant_children))
        succeeded_q = succeeded_q.where(AnalysisTask.child_id.in_(tenant_children))
        reassessment_q = reassessment_q.where(AnalysisTask.child_id.in_(tenant_children))
        pending_review_q = pending_review_q.where(Report.child_id.in_(tenant_children))
        pending_training_q = pending_training_q.where(TrainingRecord.child_id.in_(tenant_children))

    new_children = db.scalar(new_child_q) or 0
    uploaded_videos = db.scalar(video_q) or 0
    completed_reports = db.scalar(report_q) or 0
    active_plans = db.scalar(plan_q) or 0
    succeeded_tasks = db.scalar(succeeded_q) or 0
    reassessment_done = db.scalar(reassessment_q) or 0

    conversion_rate = round(completed_reports / max(succeeded_tasks, 1), 2)
    reassessment_rate = round(reassessment_done / max(active_plans, 1), 2)

    pending_review = db.scalar(pending_review_q) or 0
    pending_training = db.scalar(pending_training_q) or 0

    return {
        "newChildren": new_children,
        "uploadedVideos": uploaded_videos,
        "completedReports": completed_reports,
        "activePlans": active_plans,
        "conversionRate": min(conversion_rate, 1.0),
        "reassessmentCompletionRate": min(reassessment_rate, 1.0),
        "pendingReviewReports": pending_review,
        "pendingTrainingReview": pending_training,
    }