"""
CA Automator — resource management: workload visibility and an allocation
*suggestion* engine. Per the design this was scoped against: "the manager
must retain control... never make critical professional assignments
invisibly" — nothing here writes an assignment. suggest_candidates() only
ever returns a ranked list; a human still has to call POST
/api/engagements/<id>/members (or assign a task) to act on it.

Workload is computed on the fly from engagement_member.allocated_hours and
recent time_entry rows, not stored — same "capture once, reuse everywhere"
convention as the client-directory roll-up (see app.py's client_directory),
so this can never drift from the data it's derived from.
"""
from datetime import datetime, timedelta, timezone

import db


def workload_report(firm_id):
    """One row per active employee: capacity, hours allocated to still-open
    engagements, hours actually logged in the last 7 days, and a utilization
    % (allocated / capacity) a manager can scan for who's overloaded versus
    who has room."""
    employees = db.query(
        "SELECT au.id user_id, au.full_name, jp.name job_position_name,"
        " COALESCE(ep.weekly_capacity_hours, 40) weekly_capacity_hours"
        " FROM app_user au"
        " LEFT JOIN employee_profile ep ON ep.user_id = au.id"
        " LEFT JOIN job_position jp ON jp.id = ep.job_position_id"
        " WHERE au.firm_id = ? AND au.is_active = 1 ORDER BY au.full_name", (firm_id,))
    allocated = {r["user_id"]: r["hrs"] or 0 for r in db.query(
        "SELECT em.user_id, SUM(em.allocated_hours) hrs FROM engagement_member em"
        " JOIN engagement e ON e.id = em.engagement_id JOIN client c ON c.id = e.client_id"
        " WHERE c.firm_id = ? AND e.status != 'closed' GROUP BY em.user_id", (firm_id,))}
    # A boundary computed in Python and bound as a plain parameter, not
    # SQLite's date('now', '-7 days') inline — that modifier-argument form
    # isn't one of the two literal strings db._translate_sql() rewrites for
    # MySQL, so it would reach MySQL unmodified and fail outright. Same
    # pattern as app.py's bootstrap() 60-day deadline horizon.
    week_ago = (datetime.now(timezone.utc) - timedelta(days=7)).strftime("%Y-%m-%d")
    logged = {r["user_id"]: r["hrs"] or 0 for r in db.query(
        "SELECT te.user_id, SUM(te.hours) hrs FROM time_entry te"
        " JOIN engagement e ON e.id = te.engagement_id JOIN client c ON c.id = e.client_id"
        " WHERE c.firm_id = ? AND te.work_date >= ? GROUP BY te.user_id", (firm_id, week_ago))}
    out = []
    for e in employees:
        cap = e["weekly_capacity_hours"] or 40
        alloc = allocated.get(e["user_id"], 0)
        out.append({
            "user_id": e["user_id"], "full_name": e["full_name"], "job_position_name": e["job_position_name"],
            "weekly_capacity_hours": cap, "allocated_hours": alloc,
            "logged_hours_last_7_days": logged.get(e["user_id"], 0),
            "utilization_pct": round(alloc / cap * 100, 1) if cap else None,
        })
    return out


def suggest_candidates(firm_id, job_position_id=None, skill_id=None, limit=10):
    """Ranked candidate list for a piece of work — never an assignment.
    Scoring is intentionally simple and legible (a manager should be able to
    see WHY someone was suggested, not trust a black box):
      +3  has the requested skill (proficiency added as a fractional bonus, 0.2-1.0)
      +2  holds the requested position
      +   availability bonus: up to +3 for being furthest under capacity,
          scaled linearly so 0% allocated scores +3 and >=100% scores +0
    """
    employees = db.query(
        "SELECT au.id user_id, au.full_name, ep.job_position_id, jp.name job_position_name,"
        " COALESCE(ep.weekly_capacity_hours, 40) weekly_capacity_hours"
        " FROM app_user au"
        " LEFT JOIN employee_profile ep ON ep.user_id = au.id"
        " LEFT JOIN job_position jp ON jp.id = ep.job_position_id"
        " WHERE au.firm_id = ? AND au.is_active = 1", (firm_id,))
    allocated = {r["user_id"]: r["hrs"] or 0 for r in db.query(
        "SELECT em.user_id, SUM(em.allocated_hours) hrs FROM engagement_member em"
        " JOIN engagement e ON e.id = em.engagement_id JOIN client c ON c.id = e.client_id"
        " WHERE c.firm_id = ? AND e.status != 'closed' GROUP BY em.user_id", (firm_id,))}
    skill_rows = {}
    if skill_id:
        skill_rows = {r["user_id"]: r["proficiency"] for r in db.query(
            "SELECT user_id, proficiency FROM employee_skill WHERE skill_id = ?", (skill_id,))}

    out = []
    for e in employees:
        cap = e["weekly_capacity_hours"] or 40
        alloc = allocated.get(e["user_id"], 0)
        utilization = min(alloc / cap, 1.0) if cap else 1.0
        availability_bonus = (1 - utilization) * 3

        skill_score = (3 + skill_rows[e["user_id"]] / 5) if e["user_id"] in skill_rows else 0
        matches_position = bool(job_position_id) and e["job_position_id"] == job_position_id
        position_score = 2 if matches_position else 0

        score = round(skill_score + position_score + availability_bonus, 2)
        out.append({
            "user_id": e["user_id"], "full_name": e["full_name"], "job_position_name": e["job_position_name"],
            "current_utilization_pct": round(utilization * 100, 1),
            "matches_requested_skill": bool(e["user_id"] in skill_rows) if skill_id else None,
            "matches_requested_position": matches_position if job_position_id else None,
            "score": score,
        })
    out.sort(key=lambda c: c["score"], reverse=True)
    return out[:limit]
