"""The Studio's built-in Home dashboard (see app/schemas/home.py) — one round trip that summarizes the tenant for the signed-in internal user, section by section only where their roles already grant "view".""" import uuid from datetime import datetime, timedelta, timezone from zoneinfo import ZoneInfo, ZoneInfoNotFoundError from fastapi import APIRouter, Depends, HTTPException from sqlalchemy import and_, distinct, func, or_, select from sqlalchemy.ext.asyncio import AsyncSession from app.core.database import get_db from app.core.permissions import deny_vendor, resolve_action_matrix from app.core.security import AuthContext, get_auth_context from app.crud import record_sql from app.models.audit import AuditLogEntry from app.models.automation import Task from app.models.identity import Tenant, User, UserRole, Vendor from app.models.master import Master, MasterRecord from app.models.navigation import Dashboard, DashboardRole, Page from app.models.notification import Notification from app.models.scheduling import ScheduledJob from app.models.workflow import Form, FormSubmission, Workflow, WorkflowInstance from app.schemas.home import ( HomeActivity, HomeBuild, HomeBuildCount, HomeDashboardLink, HomeDayCount, HomeFormCount, HomeInstance, HomeMyWork, HomeNotification, HomeNotifications, HomePeople, HomeSubmission, HomeSubmissions, HomeSummary, HomeWorkflows, ) router = APIRouter(prefix="/home", tags=["home"]) RECENT = 6 TREND_DAYS = 14 async def _count(db: AsyncSession, stmt) -> int: return int((await db.execute(stmt)).scalar_one() or 0) async def _current_names(db: AsyncSession, model, id_col, tenant_id: uuid.UUID, ids: set[uuid.UUID]) -> dict[uuid.UUID, str]: if not ids: return {} rows = await db.execute(select(id_col, model.name).where(model.tenant_id == tenant_id, model.is_current.is_(True), id_col.in_(ids))) return {row[0]: row[1] for row in rows} async def _instances(db: AsyncSession, tenant_id: uuid.UUID, rows: list[WorkflowInstance], now: datetime) -> list[HomeInstance]: names = await _current_names(db, Workflow, Workflow.workflow_id, tenant_id, {r.workflow_id for r in rows}) return [ HomeInstance( id=r.id, workflow_id=r.workflow_id, workflow_name=names.get(r.workflow_id, "Workflow"), current_step=r.current_step, status=r.status, started_at=r.started_at, sla_due_at=r.sla_due_at, overdue=r.status == "in_progress" and r.sla_due_at is not None and r.sla_paused_at is None and r.sla_due_at < now, ) for r in rows ] async def _versioned_counts(db: AsyncSession, model, id_col, tenant_id: uuid.UUID) -> HomeBuildCount: """A Form/Workflow counts as published when any version is — a draft new version of a live one doesn't take it offline.""" total = await _count(db, select(func.count(distinct(id_col))).where(model.tenant_id == tenant_id)) published = await _count(db, select(func.count(distinct(id_col))).where(model.tenant_id == tenant_id, model.status == "published")) return HomeBuildCount(published=published, draft=total - published) async def _status_counts(db: AsyncSession, model, tenant_id: uuid.UUID) -> HomeBuildCount: rows = dict((await db.execute(select(model.status, func.count()).where(model.tenant_id == tenant_id).group_by(model.status))).all()) return HomeBuildCount(published=int(rows.get("published", 0)), draft=int(rows.get("draft", 0))) @router.get("/summary", response_model=HomeSummary) async def home_summary( tz: str = "UTC", ctx: AuthContext = Depends(get_auth_context), db: AsyncSession = Depends(get_db), _: AuthContext = Depends(deny_vendor()), ) -> HomeSummary: """`tz` (an IANA name, e.g. "Asia/Kolkata") sets where each day of the submissions trend starts.""" try: zone = ZoneInfo(tz) except (ZoneInfoNotFoundError, ValueError): raise HTTPException(status_code=422, detail=f"Unknown time zone '{tz}'") from None tid = ctx.tenant_id now = datetime.now(timezone.utc) tenant = await db.get(Tenant, tid) user = await db.get(User, ctx.user_id) if ctx.user_id else None matrix, is_admin = await resolve_action_matrix(db, tid, ctx.user_id) def can(module: str) -> bool: return is_admin or "view" in matrix.get(module, []) # ---- Personal: notifications and the workflows I started ------------- visible = and_(Notification.tenant_id == tid, or_(Notification.recipient_user_id.is_(None), Notification.recipient_user_id == ctx.user_id)) unread = await _count(db, select(func.count()).select_from(Notification).where(visible, Notification.read.is_(False))) recent_notes = (await db.execute(select(Notification).where(visible).order_by(Notification.created_at.desc()).limit(RECENT))).scalars().all() notifications = HomeNotifications( unread=unread, recent=[HomeNotification(id=n.id, title=n.title, message=n.message, read=n.read, created_at=n.created_at) for n in recent_notes], ) mine = and_(WorkflowInstance.tenant_id == tid, WorkflowInstance.started_by == ctx.user_id) my_open = await _count(db, select(func.count()).select_from(WorkflowInstance).where(mine, WorkflowInstance.status == "in_progress")) my_recent = (await db.execute(select(WorkflowInstance).where(mine).order_by(WorkflowInstance.started_at.desc()).limit(RECENT))).scalars().all() my_work = HomeMyWork(in_progress=my_open, recent=await _instances(db, tid, list(my_recent), now)) since_30 = now - timedelta(days=30) # ---- Workflows ---------------------------------------------------------- workflows = None if can("workflows"): wi = WorkflowInstance in_tenant = wi.tenant_id == tid recent = (await db.execute(select(wi).where(in_tenant).order_by(wi.started_at.desc()).limit(RECENT))).scalars().all() workflows = HomeWorkflows( in_progress=await _count(db, select(func.count()).select_from(wi).where(in_tenant, wi.status == "in_progress")), overdue=await _count( db, select(func.count()).select_from(wi).where(in_tenant, wi.status == "in_progress", wi.sla_paused_at.is_(None), wi.sla_due_at < now), ), escalated=await _count(db, select(func.count()).select_from(wi).where(in_tenant, wi.status == "in_progress", wi.has_escalation.is_(True))), completed_30d=await _count(db, select(func.count()).select_from(wi).where(in_tenant, wi.status == "completed", wi.completed_at >= since_30)), cancelled_30d=await _count( db, select(func.count()).select_from(wi).where(in_tenant, wi.status == "cancelled", func.coalesce(wi.completed_at, wi.started_at) >= since_30) ), started_30d=await _count(db, select(func.count()).select_from(wi).where(in_tenant, wi.started_at >= since_30)), recent=await _instances(db, tid, list(recent), now), ) # ---- Form submissions --------------------------------------------------- submissions = None if can("forms"): fs = FormSubmission live = and_(fs.tenant_id == tid, fs.is_deleted.is_(False)) today = now.astimezone(zone).date() first_day = today - timedelta(days=TREND_DAYS - 1) local_day = func.date(func.timezone(tz, fs.submitted_at)) by_day = dict( # submitted_at bounds what's read (idx_form_submissions_tenant_time); a day either side covers any time zone. (await db.execute(select(local_day, func.count()).where(live, fs.submitted_at >= now - timedelta(days=TREND_DAYS + 1), local_day >= first_day).group_by(local_day))).all() ) daily = [HomeDayCount(day=first_day + timedelta(days=i), count=int(by_day.get(first_day + timedelta(days=i), 0))) for i in range(TREND_DAYS)] top = ( await db.execute( select(fs.form_id, func.count().label("n")).where(live, fs.submitted_at >= since_30).group_by(fs.form_id).order_by(func.count().desc()).limit(5) ) ).all() recent = (await db.execute(select(fs).where(live).order_by(fs.submitted_at.desc()).limit(RECENT))).scalars().all() form_names = await _current_names(db, Form, Form.form_id, tid, {r[0] for r in top} | {s.form_id for s in recent}) submitter_ids = {s.submitted_by for s in recent if s.submitted_by} submitters = dict((await db.execute(select(User.id, User.name).where(User.id.in_(submitter_ids)))).all()) if submitter_ids else {} total, total_capped = await record_sql.capped_count(db, fs, [live]) submissions = HomeSubmissions( total=total, total_capped=total_capped, last_7d=await _count(db, select(func.count()).select_from(fs).where(live, fs.submitted_at >= now - timedelta(days=7))), prev_7d=await _count( db, select(func.count()).select_from(fs).where(live, fs.submitted_at >= now - timedelta(days=14), fs.submitted_at < now - timedelta(days=7)) ), daily=daily, top_forms=[HomeFormCount(form_id=fid, name=form_names.get(fid, "Form"), count=int(n)) for fid, n in top], recent=[ HomeSubmission( id=s.id, form_id=s.form_id, form_name=form_names.get(s.form_id, "Form"), submitted_at=s.submitted_at, submitted_by_name=submitters.get(s.submitted_by), from_portal=s.vendor_id is not None, ) for s in recent ], ) # ---- What's been built ------------------------------------------------- build = HomeBuild() if can("forms"): build.forms = await _versioned_counts(db, Form, Form.form_id, tid) if can("workflows"): build.workflows = await _versioned_counts(db, Workflow, Workflow.workflow_id, tid) if can("pages"): build.pages = await _status_counts(db, Page, tid) if can("tasks"): build.tasks = await _status_counts(db, Task, tid) build.scheduled_jobs_active = await _count(db, select(func.count()).select_from(ScheduledJob).where(ScheduledJob.tenant_id == tid, ScheduledJob.status == "active")) if can("masters"): build.datasources = await _count(db, select(func.count()).select_from(Master).where(Master.tenant_id == tid)) if is_admin: # row-scoped Datasources would make a tenant-wide count misleading for others build.datasource_records, build.datasource_records_capped = await record_sql.capped_count( db, MasterRecord, [MasterRecord.tenant_id == tid, MasterRecord.is_deleted.is_(False)] ) if can("dashboard"): build.dashboards = await _count(db, select(func.count()).select_from(Dashboard).where(Dashboard.tenant_id == tid)) # ---- People --------------------------------------------------------------- people = None if can("users_roles"): in_tenant = User.tenant_id == tid people = HomePeople( internal_users=await _count(db, select(func.count()).select_from(User).where(in_tenant, User.vendor_id.is_(None))), portal_users=await _count(db, select(func.count()).select_from(User).where(in_tenant, User.vendor_id.is_not(None))), vendors=await _count(db, select(func.count()).select_from(Vendor).where(Vendor.tenant_id == tid)), signed_in_7d=await _count(db, select(func.count()).select_from(User).where(in_tenant, User.last_login_at >= now - timedelta(days=7))), inactive=await _count(db, select(func.count()).select_from(User).where(in_tenant, User.status != "active")), ) # ---- Recent activity (the audit log — Settings "view", like GET /audit) -- activity = None if can("settings"): rows = ( await db.execute( select(AuditLogEntry, User.name) .outerjoin(User, User.id == AuditLogEntry.actor_id) .where(AuditLogEntry.tenant_id == tid) .order_by(AuditLogEntry.occurred_at.desc()) .limit(8) ) ).all() activity = [HomeActivity(action=e.action, entity_type=e.entity_type, actor_name=name, occurred_at=e.occurred_at) for e, name in rows] # ---- Dashboards this user can open (all of them for an admin) ------------ dashboards = None if can("dashboard"): stmt = select(Dashboard.id, Dashboard.name).where(Dashboard.tenant_id == tid) if not is_admin: stmt = ( stmt.join(DashboardRole, DashboardRole.dashboard_id == Dashboard.id) .join(UserRole, UserRole.role_id == DashboardRole.role_id) .where(UserRole.user_id == ctx.user_id) .distinct() ) dashboards = [HomeDashboardLink(id=i, name=n) for i, n in (await db.execute(stmt.order_by(Dashboard.name).limit(8))).all()] return HomeSummary( user_name=user.name if user else "", tenant_name=tenant.name if tenant else "", is_admin=is_admin, generated_at=now, notifications=notifications, my_work=my_work, workflows=workflows, submissions=submissions, build=build, people=people, activity=activity, dashboards=dashboards, )