from __future__ import annotations

from fastapi import APIRouter, Form, Request
from fastapi.responses import HTMLResponse, RedirectResponse
from sqlalchemy import select

from app.core.db.common import CommonSessionLocal
from app.core.security.csrf import get_or_create_csrf_token, validate_csrf
from app.core.security.session_auth import get_current_user
from app.core.templating import templates
from app.modules.clients.constants import CLIENT_TYPES
from app.modules.clients.models import Client
from app.modules.consultants.service import (
    CONSULTANT_MANAGED_CLIENT_STAGES,
    CONSULTANT_MANAGED_CLIENT_STATUSES,
    CONSULTANT_RELATIONSHIP_TYPES,
    CONSULTANT_TYPES,
    CONSULTANT_BILLING_CYCLES,
    CONSULTANT_ONBOARDING_STATUSES,
    CONSULTANT_CLIENT_CONVERSION_STATUSES,
    CONSULTANT_SERVICE_REQUEST_PRIORITIES,
    CONSULTANT_SERVICE_REQUEST_STATUSES,
    CONSULTANT_SUBSCRIPTION_STATUSES,
    CONSULTANT_WORKSPACE_PLANS,
    CONSULTANT_WORKSPACE_TYPES,
    add_consultant_task_reply,
    approve_managed_client_conversion,
    consultant_can_add_managed_client,
    consultant_dashboard_payload,
    consultant_linked_clients_page,
    create_consultant_service_request,
    create_or_update_consultant,
    create_or_update_managed_client,
    ensure_consultant_login_user,
    ensure_consultant_workspace,
    get_consultant,
    get_consultant_by_user,
    get_consultant_managed_client,
    get_client_conversion_request,
    get_consultant_service_request,
    get_consultant_visible_communication,
    get_workspace_by_consultant,
    link_client_to_consultant,
    list_all_consultant_service_requests,
    list_client_links_for_consultant,
    list_consultant_conversion_requests,
    list_clients_available_for_link,
    list_consultant_requestable_services,
    list_consultant_role_users,
    list_consultant_managed_clients,
    list_consultant_service_requests,
    list_consultant_task_timeline,
    list_consultant_visible_communications,
    list_consultants,
    mark_conversion_under_review,
    parse_optional_date,
    reject_managed_client_conversion,
    request_managed_client_conversion,
    set_link_active,
    update_consultant_own_profile,
    update_consultant_service_request_status,
    update_consultant_workspace,
)
from app.modules.consultants.portal_service import (
    get_consultant_assignment_detail,
    get_consultant_engagement_progress,
    update_consultant_assignment,
    get_consultant_document_centre,
    get_consultant_work_board,
)
from app.modules.core.rbac.deps import get_user_permissions, get_user_roles
from app.modules.core.rbac.permission_guard import require_permission
from app.modules.core.iam.profile_service import save_user_profile_photo, update_user_public_profile

router = APIRouter(prefix="/consultants", tags=["consultants-ui"])
portal_router = APIRouter(prefix="/consultant", tags=["consultant-portal-ui"])


def _base_ctx(request: Request, user, db, **ctx):
    base = {
        "request": request,
        "current_user": user,
        "current_user_roles": get_user_roles(db, user.id),
        "current_user_permissions": get_user_permissions(db, user.id),
        "csrf_token": get_or_create_csrf_token(request),
        "consultant_types": CONSULTANT_TYPES,
        "relationship_types": CONSULTANT_RELATIONSHIP_TYPES,
        "managed_client_statuses": CONSULTANT_MANAGED_CLIENT_STATUSES,
        "managed_client_stages": CONSULTANT_MANAGED_CLIENT_STAGES,
        "client_types": CLIENT_TYPES,
        "workspace_types": CONSULTANT_WORKSPACE_TYPES,
        "workspace_plans": CONSULTANT_WORKSPACE_PLANS,
        "subscription_statuses": CONSULTANT_SUBSCRIPTION_STATUSES,
        "billing_cycles": CONSULTANT_BILLING_CYCLES,
        "onboarding_statuses": CONSULTANT_ONBOARDING_STATUSES,
        "service_request_statuses": CONSULTANT_SERVICE_REQUEST_STATUSES,
        "service_request_priorities": CONSULTANT_SERVICE_REQUEST_PRIORITIES,
        "conversion_statuses": CONSULTANT_CLIENT_CONVERSION_STATUSES,
    }
    base.update(ctx)
    return base


def _render(request: Request, template_name: str, db, user, **ctx):
    return templates.TemplateResponse(template_name, _base_ctx(request, user, db, **ctx))


def _redirect_denied():
    from app.core.http_responses import ui_access_denied
    return ui_access_denied()


def _has_perm(db, user, code: str) -> bool:
    try:
        require_permission(db, user, code)
        return True
    except Exception:
        return False


def _role_names(db, user) -> set[str]:
    return set(get_user_roles(db, user.id))


def _is_system_admin(db, user) -> bool:
    return "System Admin" in _role_names(db, user)


def _is_firm_admin(db, user) -> bool:
    return "Firm Admin" in _role_names(db, user)


def _is_partner(db, user) -> bool:
    return "Partner" in _role_names(db, user)


def _is_consultant(db, user) -> bool:
    return "Consultant" in _role_names(db, user)


def _active_tenant_id(request: Request, user) -> int:
    return int(
        request.session.get("active_tenant_id")
        or request.session.get("selected_tenant_id")
        or request.session.get("tenant_id")
        or user.tenant_id
    )


def _active_branch_id(request: Request, user, db) -> int | None:
    value = request.session.get("active_branch_id")
    if value in (None, "", 0, "0"):
        if _has_perm(db, user, "consultants.cross_branch") or _has_perm(db, user, "clients.cross_branch"):
            return None
        return int(getattr(user, "branch_id", 0) or 0) or None
    return int(value)


def _form_bool(value) -> bool:
    return value in ("1", "true", "True", "on", "yes")


def _can_manage_profiles(db, user) -> bool:
    return _has_perm(db, user, "consultants.manage")


def _can_link_clients(db, user) -> bool:
    return _has_perm(db, user, "consultants.link_clients")


def _can_manage_conversions(db, user) -> bool:
    return _has_perm(db, user, "consultants.conversions.manage")


def _partner_filter_user_id(db, user) -> int | None:
    return int(user.id) if _is_partner(db, user) and not _is_firm_admin(db, user) else None


def _build_payload(request: Request, *, tenant_id: int):
    form = request._form
    user_id_raw = form.get("user_id")
    branch_raw = form.get("branch_id")
    return {
        "tenant_id": tenant_id,
        "branch_id": int(branch_raw) if branch_raw not in (None, "", "None") else None,
        "user_id": int(user_id_raw) if user_id_raw not in (None, "", "None") else None,
        "consultant_type": form.get("consultant_type") or "external_consultant",
        "firm_name": form.get("firm_name"),
        "contact_person": (form.get("contact_person") or "").strip(),
        "email": form.get("email"),
        "mobile": form.get("mobile"),
        "specialisation": form.get("specialisation"),
        "gstin": form.get("gstin"),
        "pan": form.get("pan"),
        "address": form.get("address"),
        "status": form.get("status") or "active",
        "onboarding_status": form.get("onboarding_status") or "active",
        "is_platform_partner": _form_bool(form.get("is_platform_partner")),
        "is_franchise_partner": _form_bool(form.get("is_franchise_partner")),
        "is_saas_customer": _form_bool(form.get("is_saas_customer")),
        "is_active": _form_bool(form.get("is_active")),
        "remarks": form.get("remarks"),
        "create_login_user": _form_bool(form.get("create_login_user")),
        "invite_login_user": _form_bool(form.get("invite_login_user")),
        "login_email": form.get("login_email"),
        "temporary_password": form.get("temporary_password"),
    }


def _validate_payload(payload: dict) -> list[str]:
    errors: list[str] = []
    if not payload.get("contact_person"):
        errors.append("Contact person/name is required.")
    if not payload.get("email") and not payload.get("mobile"):
        errors.append("Enter at least email or mobile for consultant communication.")
    if payload.get("create_login_user") and payload.get("user_id"):
        errors.append("Choose either an existing linked user or create a new login user, not both.")
    if payload.get("create_login_user") and not (payload.get("login_email") or payload.get("email")):
        errors.append("Login email is required for consultant login creation.")
    if payload.get("create_login_user") and not payload.get("invite_login_user") and not (payload.get("temporary_password") or "").strip():
        errors.append("Enter a temporary password or choose invite link for consultant login.")
    if payload.get("temporary_password") and len((payload.get("temporary_password") or "").strip()) < 8:
        errors.append("Temporary password must be at least 8 characters.")
    return errors


def _build_own_profile_payload(request: Request) -> dict:
    form = request._form
    return {
        "firm_name": form.get("firm_name"),
        "contact_person": (form.get("contact_person") or "").strip(),
        "email": form.get("email"),
        "mobile": form.get("mobile"),
        "specialisation": form.get("specialisation"),
        "gstin": form.get("gstin"),
        "pan": form.get("pan"),
        "address": form.get("address"),
    }


def _validate_own_profile_payload(payload: dict) -> list[str]:
    errors: list[str] = []
    if not payload.get("contact_person"):
        errors.append("Contact person/name is required.")
    if not payload.get("email") and not payload.get("mobile"):
        errors.append("Enter at least email or mobile.")
    return errors


def _maybe_create_consultant_login(db, *, payload: dict, actor_user, branch_id: int | None) -> tuple[int | None, str | None]:
    if not payload.get("create_login_user"):
        return payload.get("user_id"), None
    login_user, invite_url = ensure_consultant_login_user(
        db,
        tenant_id=int(payload["tenant_id"]),
        branch_id=payload.get("branch_id") or branch_id or getattr(actor_user, "branch_id", None),
        email=(payload.get("login_email") or payload.get("email") or "").strip(),
        full_name=payload.get("contact_person") or payload.get("firm_name") or "Consultant",
        password=payload.get("temporary_password"),
        invite_user=bool(payload.get("invite_login_user")),
    )
    return int(login_user.id), invite_url


def _get_own_consultant_or_dashboard(request: Request, db, user):
    tenant_id = int(getattr(user, "tenant_id", 0) or 0)
    consultant = get_consultant_by_user(db, tenant_id=tenant_id, user_id=user.id)
    if consultant:
        return consultant
    return None


def _build_managed_client_payload(request: Request) -> dict:
    form = request._form
    return {
        "client_code": form.get("client_code"),
        "client_name": (form.get("client_name") or "").strip(),
        "trade_name": form.get("trade_name"),
        "client_type": form.get("client_type") or "Other",
        "pan": form.get("pan"),
        "gstin": form.get("gstin"),
        "tan": form.get("tan"),
        "contact_person_name": form.get("contact_person_name"),
        "mobile": form.get("mobile"),
        "email": form.get("email"),
        "address_line_1": form.get("address_line_1"),
        "address_line_2": form.get("address_line_2"),
        "city": form.get("city"),
        "state": form.get("state"),
        "pincode": form.get("pincode"),
        "country": form.get("country") or "India",
        "service_interest": form.get("service_interest"),
        "relationship_stage": form.get("relationship_stage") or "managed",
        "status": form.get("status") or "active",
        "is_active": _form_bool(form.get("is_active")),
        "notes": form.get("notes"),
    }


def _validate_managed_client_payload(payload: dict) -> list[str]:
    errors: list[str] = []
    if not payload.get("client_name"):
        errors.append("Client name is required.")
    if not payload.get("mobile") and not payload.get("email"):
        errors.append("Enter at least mobile or email for the managed client.")
    return errors



def _build_workspace_payload(request: Request) -> dict:
    form = request._form
    return {
        "workspace_name": (form.get("workspace_name") or "").strip(),
        "workspace_type": form.get("workspace_type") or "consultant_saas",
        "plan_code": form.get("plan_code") or "starter",
        "billing_cycle": form.get("billing_cycle") or "manual",
        "subscription_status": form.get("subscription_status") or "trial",
        "subscription_start_date": parse_optional_date(form.get("subscription_start_date")),
        "subscription_end_date": parse_optional_date(form.get("subscription_end_date")),
        "max_managed_clients": form.get("max_managed_clients") or 25,
        "max_user_accounts": form.get("max_user_accounts") or 1,
        "allow_client_portal": _form_bool(form.get("allow_client_portal")),
        "allow_firm_referrals": _form_bool(form.get("allow_firm_referrals")),
        "allow_service_marketplace": _form_bool(form.get("allow_service_marketplace")),
        "is_active": _form_bool(form.get("is_active")),
        "remarks": form.get("remarks"),
    }


def _validate_workspace_payload(payload: dict) -> list[str]:
    errors: list[str] = []
    if not payload.get("workspace_name"):
        errors.append("Workspace name is required.")
    try:
        if int(payload.get("max_managed_clients") or 0) < 0:
            errors.append("Managed client limit cannot be negative.")
    except (TypeError, ValueError):
        errors.append("Managed client limit must be a number.")
    try:
        if int(payload.get("max_user_accounts") or 0) < 1:
            errors.append("User account limit must be at least 1.")
    except (TypeError, ValueError):
        errors.append("User account limit must be a number.")
    start = payload.get("subscription_start_date")
    end = payload.get("subscription_end_date")
    if start and end and end < start:
        errors.append("Subscription end date cannot be earlier than start date.")
    return errors


def _build_workspace_self_payload(request: Request) -> dict:
    form = request._form
    return {
        "workspace_name": (form.get("workspace_name") or "").strip(),
        "remarks": form.get("remarks"),
    }


def _validate_workspace_self_payload(payload: dict) -> list[str]:
    errors: list[str] = []
    if not payload.get("workspace_name"):
        errors.append("Workspace name is required.")
    return errors


@router.get("")
def consultants_list(request: Request, q: str = "", include_inactive: bool = False):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not _has_perm(db, user, "consultants.view"):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        rows = list_consultants(db, tenant_id=tenant_id, branch_id=branch_id, q=q, include_inactive=include_inactive)
        return _render(
            request,
            "modules/consultants/templates/consultants/list.html",
            db,
            user,
            title="Consultants",
            rows=rows,
            q=q,
            include_inactive=include_inactive,
            can_manage=_can_manage_profiles(db, user),
            can_link=_can_link_clients(db, user),
        )
    finally:
        db.close()


@router.get("/new")
def consultant_new_page(request: Request):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not _can_manage_profiles(db, user):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        users = list_consultant_role_users(db, tenant_id=tenant_id, branch_id=branch_id)
        return _render(
            request,
            "modules/consultants/templates/consultants/form.html",
            db,
            user,
            title="Add Consultant",
            consultant=None,
            consultant_users=users,
            errors=[],
        )
    finally:
        db.close()


@router.post("/new")
async def consultant_new_submit(request: Request, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not _can_manage_profiles(db, user):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        payload = _build_payload(request, tenant_id=tenant_id)
        if branch_id and payload.get("branch_id") not in (None, branch_id):
            return _redirect_denied()
        errors = _validate_payload(payload)
        if errors:
            users = list_consultant_role_users(db, tenant_id=tenant_id, branch_id=branch_id)
            return _render(
                request,
                "modules/consultants/templates/consultants/form.html",
                db,
                user,
                title="Add Consultant",
                consultant=payload,
                consultant_users=users,
                errors=errors,
            )
        try:
            linked_user_id, invite_url = _maybe_create_consultant_login(db, payload=payload, actor_user=user, branch_id=branch_id)
            payload["user_id"] = linked_user_id
            if invite_url:
                payload["onboarding_status"] = "invited"
            elif linked_user_id:
                payload["onboarding_status"] = payload.get("onboarding_status") or "active"
            consultant = create_or_update_consultant(db, payload=payload, user_id=user.id)
            db.commit()
            db.refresh(consultant)
        except Exception as exc:
            db.rollback()
            users = list_consultant_role_users(db, tenant_id=tenant_id, branch_id=branch_id)
            return _render(
                request,
                "modules/consultants/templates/consultants/form.html",
                db,
                user,
                title="Add Consultant",
                consultant=payload,
                consultant_users=users,
                errors=[str(exc)],
            )
        if invite_url:
            return _render(
                request,
                "modules/consultants/templates/consultants/consultant_invite_link.html",
                db,
                user,
                title="Consultant Invite Link",
                consultant=consultant,
                invite_url=invite_url,
                invited_user=consultant.user,
            )
        return RedirectResponse(url=f"/consultants/{consultant.id}", status_code=303)
    finally:
        db.close()


@router.get("/service-requests")
def internal_consultant_service_requests_list(request: Request, status: str = ""):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.view") and _has_perm(db, user, "consultants.service_requests.manage")):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        rows = list_all_consultant_service_requests(db, tenant_id=tenant_id, branch_id=branch_id, status=status)
        return _render(
            request,
            "modules/consultants/templates/consultants/internal_service_requests_list.html",
            db,
            user,
            title="Consultant Service Requests",
            rows=rows,
            status=status,
        )
    finally:
        db.close()


@router.get("/service-requests/{request_id}")
def internal_consultant_service_request_detail(request: Request, request_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.view") and _has_perm(db, user, "consultants.service_requests.manage")):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        row = get_consultant_service_request(db, tenant_id=tenant_id, request_id=request_id)
        if not row:
            return RedirectResponse(url="/consultants/service-requests", status_code=303)
        return _render(
            request,
            "modules/consultants/templates/consultants/service_request_detail.html",
            db,
            user,
            title="Consultant Service Request",
            consultant=row.consultant,
            request_row=row,
            internal_view=True,
        )
    finally:
        db.close()


@router.post("/service-requests/{request_id}/status")
def internal_consultant_service_request_status(
    request: Request,
    request_id: int,
    status: str = Form(...),
    firm_response: str = Form(""),
    csrf_token: str = Form(...),
):
    validate_csrf(request, csrf_token)
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.view") and _has_perm(db, user, "consultants.service_requests.manage")):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        row = get_consultant_service_request(db, tenant_id=tenant_id, request_id=request_id)
        if not row:
            return RedirectResponse(url="/consultants/service-requests", status_code=303)
        update_consultant_service_request_status(db, request=row, status=status, firm_response=firm_response, user_id=user.id)
        db.commit()
        return RedirectResponse(url=f"/consultants/service-requests/{request_id}", status_code=303)
    finally:
        db.close()




@router.get("/conversion-requests")
def internal_consultant_conversion_requests_list(request: Request, status: str = ""):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.view") and _can_manage_conversions(db, user)):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        rows = list_consultant_conversion_requests(db, tenant_id=tenant_id, branch_id=branch_id, status=status)
        return _render(
            request,
            "modules/consultants/templates/consultants/internal_conversion_requests_list.html",
            db,
            user,
            title="Consultant Client Conversion Requests",
            rows=rows,
            status=status,
        )
    finally:
        db.close()


@router.get("/conversion-requests/{managed_client_id}")
def internal_consultant_conversion_request_detail(request: Request, managed_client_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.view") and _can_manage_conversions(db, user)):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        row = get_client_conversion_request(db, tenant_id=tenant_id, managed_client_id=managed_client_id, branch_id=branch_id)
        if not row:
            return RedirectResponse(url="/consultants/conversion-requests", status_code=303)
        return _render(
            request,
            "modules/consultants/templates/consultants/conversion_request_detail.html",
            db,
            user,
            title="Conversion Request",
            managed_client=row,
            consultant=row.consultant,
            errors=[],
        )
    finally:
        db.close()


@router.post("/conversion-requests/{managed_client_id}/review")
def internal_consultant_conversion_review(
    request: Request,
    managed_client_id: int,
    action: str = Form(...),
    client_code: str = Form(""),
    partner_user_id: str = Form(""),
    firm_notes: str = Form(""),
    csrf_token: str = Form(...),
):
    validate_csrf(request, csrf_token)
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.view") and _can_manage_conversions(db, user)):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        row = get_client_conversion_request(db, tenant_id=tenant_id, managed_client_id=managed_client_id, branch_id=branch_id)
        if not row:
            return RedirectResponse(url="/consultants/conversion-requests", status_code=303)
        try:
            if action == "under_review":
                mark_conversion_under_review(db, managed_client=row, user_id=user.id, firm_notes=firm_notes)
            elif action == "reject":
                reject_managed_client_conversion(db, managed_client=row, user_id=user.id, firm_notes=firm_notes)
            elif action == "approve":
                partner_id = int(partner_user_id) if str(partner_user_id).strip() else None
                firm_client = approve_managed_client_conversion(
                    db,
                    managed_client=row,
                    user_id=user.id,
                    partner_user_id=partner_id,
                    client_code=client_code,
                    firm_notes=firm_notes,
                )
                db.commit()
                return RedirectResponse(url=f"/clients/{firm_client.id}", status_code=303)
            else:
                raise ValueError("Invalid review action.")
            db.commit()
            return RedirectResponse(url=f"/consultants/conversion-requests/{managed_client_id}", status_code=303)
        except Exception as exc:
            db.rollback()
            return _render(
                request,
                "modules/consultants/templates/consultants/conversion_request_detail.html",
                db,
                user,
                title="Conversion Request",
                managed_client=row,
                consultant=row.consultant,
                errors=[str(exc)],
            )
    finally:
        db.close()


@router.get("/{consultant_id}")
def consultant_detail(request: Request, consultant_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not _has_perm(db, user, "consultants.view"):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        consultant = get_consultant(db, tenant_id=tenant_id, consultant_id=consultant_id, branch_id=branch_id)
        if not consultant:
            return RedirectResponse(url="/consultants", status_code=303)
        links = list_client_links_for_consultant(db, consultant_id=consultant.id)
        clients = list_clients_available_for_link(
            db,
            tenant_id=tenant_id,
            branch_id=branch_id,
            partner_user_id=_partner_filter_user_id(db, user),
        )
        return _render(
            request,
            "modules/consultants/templates/consultants/detail.html",
            db,
            user,
            title="Consultant Detail",
            consultant=consultant,
            links=links,
            clients=clients,
            can_manage=_can_manage_profiles(db, user),
            can_link=_can_link_clients(db, user),
        )
    finally:
        db.close()


@router.get("/{consultant_id}/edit")
def consultant_edit_page(request: Request, consultant_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not _can_manage_profiles(db, user):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        consultant = get_consultant(db, tenant_id=tenant_id, consultant_id=consultant_id, branch_id=branch_id)
        if not consultant:
            return RedirectResponse(url="/consultants", status_code=303)
        users = list_consultant_role_users(db, tenant_id=tenant_id, branch_id=branch_id)
        return _render(
            request,
            "modules/consultants/templates/consultants/form.html",
            db,
            user,
            title="Edit Consultant",
            consultant=consultant,
            consultant_users=users,
            errors=[],
        )
    finally:
        db.close()


@router.post("/{consultant_id}/edit")
async def consultant_edit_submit(request: Request, consultant_id: int, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not _can_manage_profiles(db, user):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        consultant = get_consultant(db, tenant_id=tenant_id, consultant_id=consultant_id, branch_id=branch_id)
        if not consultant:
            return RedirectResponse(url="/consultants", status_code=303)
        payload = _build_payload(request, tenant_id=tenant_id)
        if branch_id and payload.get("branch_id") not in (None, branch_id):
            return _redirect_denied()
        errors = _validate_payload(payload)
        if errors:
            users = list_consultant_role_users(db, tenant_id=tenant_id, branch_id=branch_id)
            return _render(
                request,
                "modules/consultants/templates/consultants/form.html",
                db,
                user,
                title="Edit Consultant",
                consultant=consultant,
                consultant_users=users,
                errors=errors,
            )
        invite_url = None
        try:
            linked_user_id, invite_url = _maybe_create_consultant_login(db, payload=payload, actor_user=user, branch_id=branch_id)
            payload["user_id"] = linked_user_id
            if invite_url:
                payload["onboarding_status"] = "invited"
            create_or_update_consultant(db, payload=payload, user_id=user.id, consultant=consultant)
            db.commit()
        except Exception as exc:
            db.rollback()
            users = list_consultant_role_users(db, tenant_id=tenant_id, branch_id=branch_id)
            return _render(
                request,
                "modules/consultants/templates/consultants/form.html",
                db,
                user,
                title="Edit Consultant",
                consultant=consultant,
                consultant_users=users,
                errors=[str(exc)],
            )
        if invite_url:
            return _render(
                request,
                "modules/consultants/templates/consultants/consultant_invite_link.html",
                db,
                user,
                title="Consultant Invite Link",
                consultant=consultant,
                invite_url=invite_url,
                invited_user=consultant.user,
            )
        return RedirectResponse(url=f"/consultants/{consultant.id}", status_code=303)
    finally:
        db.close()


@router.post("/{consultant_id}/links")
def consultant_link_submit(
    request: Request,
    consultant_id: int,
    client_id: int = Form(...),
    relationship_type: str = Form("accounts_consultant"),
    is_primary: str | None = Form(None),
    can_view_client: str | None = Form(None),
    can_view_services: str | None = Form(None),
    can_view_due_dates: str | None = Form(None),
    can_view_communications: str | None = Form(None),
    remarks: str = Form(""),
    csrf_token: str = Form(...),
):
    validate_csrf(request, csrf_token)
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not _can_link_clients(db, user):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        branch_id = _active_branch_id(request, user, db)
        consultant = get_consultant(db, tenant_id=tenant_id, consultant_id=consultant_id, branch_id=branch_id)
        if not consultant:
            return RedirectResponse(url="/consultants", status_code=303)
        client_query = select(Client).where(Client.id == client_id, Client.tenant_id == tenant_id, Client.is_archived.is_(False))
        if branch_id:
            client_query = client_query.where(Client.branch_id == branch_id)
        partner_id = _partner_filter_user_id(db, user)
        if partner_id:
            client_query = client_query.where(Client.partner_id == partner_id)
        client = db.execute(client_query).scalar_one_or_none()
        if not client:
            return _redirect_denied()
        link_client_to_consultant(
            db,
            tenant_id=tenant_id,
            consultant_id=consultant.id,
            client=client,
            relationship_type=relationship_type,
            is_primary=_form_bool(is_primary),
            can_view_client=can_view_client is not None,
            can_view_services=can_view_services is not None,
            can_view_due_dates=can_view_due_dates is not None,
            can_view_communications=can_view_communications is not None,
            remarks=remarks,
            user_id=user.id,
        )
        db.commit()
        return RedirectResponse(url=f"/consultants/{consultant.id}", status_code=303)
    finally:
        db.close()


@router.post("/links/{link_id}/toggle")
def consultant_link_toggle(request: Request, link_id: int, active: str = Form("0"), csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not _can_link_clients(db, user):
            return _redirect_denied()
        tenant_id = _active_tenant_id(request, user)
        link = set_link_active(db, tenant_id=tenant_id, link_id=link_id, active=_form_bool(active), user_id=user.id)
        if not link:
            return RedirectResponse(url="/consultants", status_code=303)
        db.commit()
        return RedirectResponse(url=f"/consultants/{link.consultant_id}", status_code=303)
    finally:
        db.close()



@portal_router.get("/managed-clients")
def consultant_managed_clients_list(request: Request, q: str = "", status: str = "", include_inactive: bool = False):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        rows = list_consultant_managed_clients(
            db,
            tenant_id=consultant.tenant_id,
            consultant_id=consultant.id,
            q=q,
            status=status,
            include_inactive=include_inactive,
        )
        can_add_managed_client, workspace_summary = consultant_can_add_managed_client(db, consultant=consultant)
        return _render(
            request,
            "modules/consultants/templates/consultants/managed_clients_list.html",
            db,
            user,
            title="My Managed Clients",
            consultant=consultant,
            rows=rows,
            q=q,
            status=status,
            include_inactive=include_inactive,
            can_add_managed_client=can_add_managed_client,
            workspace_summary=workspace_summary,
        )
    finally:
        db.close()


@portal_router.get("/managed-clients/new")
def consultant_managed_client_new_page(request: Request):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        can_add_managed_client, workspace_summary = consultant_can_add_managed_client(db, consultant=consultant)
        return _render(
            request,
            "modules/consultants/templates/consultants/managed_client_form.html",
            db,
            user,
            title="Add Managed Client",
            consultant=consultant,
            managed_client=None,
            errors=[] if can_add_managed_client else [
                f"Managed client limit reached ({workspace_summary.get('managed_clients_used')}/{workspace_summary.get('managed_clients_limit')})."
            ],
            can_add_managed_client=can_add_managed_client,
            workspace_summary=workspace_summary,
        )
    finally:
        db.close()


@portal_router.post("/managed-clients/new")
async def consultant_managed_client_new_submit(request: Request, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        payload = _build_managed_client_payload(request)
        errors = _validate_managed_client_payload(payload)
        can_add_managed_client, workspace_summary = consultant_can_add_managed_client(db, consultant=consultant)
        if not can_add_managed_client:
            errors.append(
                f"Managed client limit reached ({workspace_summary.get('managed_clients_used')}/{workspace_summary.get('managed_clients_limit')})."
            )
        if errors:
            return _render(
                request,
                "modules/consultants/templates/consultants/managed_client_form.html",
                db,
                user,
                title="Add Managed Client",
                consultant=consultant,
                managed_client=payload,
                errors=errors,
                can_add_managed_client=can_add_managed_client,
                workspace_summary=workspace_summary,
            )
        try:
            row = create_or_update_managed_client(db, consultant=consultant, payload=payload, user_id=user.id)
            db.commit()
            db.refresh(row)
        except Exception as exc:
            db.rollback()
            return _render(
                request,
                "modules/consultants/templates/consultants/managed_client_form.html",
                db,
                user,
                title="Add Managed Client",
                consultant=consultant,
                managed_client=payload,
                errors=[str(exc)],
                can_add_managed_client=can_add_managed_client,
                workspace_summary=workspace_summary,
            )
        return RedirectResponse(url=f"/consultant/managed-clients/{row.id}", status_code=303)
    finally:
        db.close()


@portal_router.get("/managed-clients/{managed_client_id}")
def consultant_managed_client_detail(request: Request, managed_client_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        row = get_consultant_managed_client(
            db,
            tenant_id=consultant.tenant_id,
            consultant_id=consultant.id,
            managed_client_id=managed_client_id,
        )
        if not row:
            return RedirectResponse(url="/consultant/managed-clients", status_code=303)
        return _render(
            request,
            "modules/consultants/templates/consultants/managed_client_detail.html",
            db,
            user,
            title="Managed Client Detail",
            consultant=consultant,
            managed_client=row,
            can_request_conversion=(not row.linked_firm_client_id and row.conversion_status not in {"requested", "under_review", "approved"}),
        )
    finally:
        db.close()




@portal_router.post("/managed-clients/{managed_client_id}/request-conversion")
def consultant_managed_client_request_conversion(
    request: Request,
    managed_client_id: int,
    conversion_notes: str = Form(""),
    csrf_token: str = Form(...),
):
    validate_csrf(request, csrf_token)
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        row = get_consultant_managed_client(
            db,
            tenant_id=consultant.tenant_id,
            consultant_id=consultant.id,
            managed_client_id=managed_client_id,
        )
        if not row:
            return RedirectResponse(url="/consultant/managed-clients", status_code=303)
        try:
            request_managed_client_conversion(
                db,
                consultant=consultant,
                managed_client=row,
                notes=conversion_notes,
                user_id=user.id,
            )
            db.commit()
        except Exception:
            db.rollback()
        return RedirectResponse(url=f"/consultant/managed-clients/{managed_client_id}", status_code=303)
    finally:
        db.close()


@portal_router.get("/managed-clients/{managed_client_id}/edit")
def consultant_managed_client_edit_page(request: Request, managed_client_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        row = get_consultant_managed_client(
            db,
            tenant_id=consultant.tenant_id,
            consultant_id=consultant.id,
            managed_client_id=managed_client_id,
        )
        if not row:
            return RedirectResponse(url="/consultant/managed-clients", status_code=303)
        return _render(
            request,
            "modules/consultants/templates/consultants/managed_client_form.html",
            db,
            user,
            title="Edit Managed Client",
            consultant=consultant,
            managed_client=row,
            errors=[],
        )
    finally:
        db.close()


@portal_router.post("/managed-clients/{managed_client_id}/edit")
async def consultant_managed_client_edit_submit(request: Request, managed_client_id: int, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        row = get_consultant_managed_client(
            db,
            tenant_id=consultant.tenant_id,
            consultant_id=consultant.id,
            managed_client_id=managed_client_id,
        )
        if not row:
            return RedirectResponse(url="/consultant/managed-clients", status_code=303)
        payload = _build_managed_client_payload(request)
        errors = _validate_managed_client_payload(payload)
        if errors:
            return _render(
                request,
                "modules/consultants/templates/consultants/managed_client_form.html",
                db,
                user,
                title="Edit Managed Client",
                consultant=consultant,
                managed_client=row,
                errors=errors,
            )
        create_or_update_managed_client(db, consultant=consultant, payload=payload, user_id=user.id, managed_client=row)
        db.commit()
        return RedirectResponse(url=f"/consultant/managed-clients/{row.id}", status_code=303)
    finally:
        db.close()


@portal_router.get("/workspace")
def consultant_workspace_page(request: Request):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        workspace = ensure_consultant_workspace(db, consultant=consultant, user_id=user.id)
        db.commit()
        db.refresh(workspace)
        return _render(
            request,
            "modules/consultants/templates/consultants/workspace_detail.html",
            db,
            user,
            title="Consultant Workspace",
            consultant=consultant,
            workspace=workspace,
        )
    finally:
        db.close()


@portal_router.get("/workspace/edit")
def consultant_workspace_edit_page(request: Request):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.workspace.manage") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        workspace = ensure_consultant_workspace(db, consultant=consultant, user_id=user.id)
        db.commit()
        db.refresh(workspace)
        return _render(
            request,
            "modules/consultants/templates/consultants/workspace_form.html",
            db,
            user,
            title="Edit Consultant Workspace",
            consultant=consultant,
            workspace=workspace,
            errors=[],
        )
    finally:
        db.close()


@portal_router.post("/workspace/edit")
async def consultant_workspace_edit_submit(request: Request, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.workspace.manage") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        workspace = ensure_consultant_workspace(db, consultant=consultant, user_id=user.id)
        payload = _build_workspace_self_payload(request)
        errors = _validate_workspace_self_payload(payload)
        if errors:
            return _render(
                request,
                "modules/consultants/templates/consultants/workspace_form.html",
                db,
                user,
                title="Edit Consultant Workspace",
                consultant=consultant,
                workspace={**workspace.__dict__, **payload},
                errors=errors,
            )
        update_consultant_workspace(db, workspace=workspace, payload=payload, user_id=user.id, admin_mode=False)
        db.commit()
        return RedirectResponse(url="/consultant/workspace", status_code=303)
    finally:
        db.close()


@portal_router.get("/profile")
def consultant_profile_page(request: Request):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        return _render(
            request,
            "modules/consultants/templates/consultants/profile_form.html",
            db,
            user,
            title="My Consultant Profile",
            consultant=consultant,
            errors=[],
        )
    finally:
        db.close()


@portal_router.post("/profile")
async def consultant_profile_submit(request: Request, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        payload = _build_own_profile_payload(request)
        errors = _validate_own_profile_payload(payload)
        if errors:
            return _render(
                request,
                "modules/consultants/templates/consultants/profile_form.html",
                db,
                user,
                title="My Consultant Profile",
                consultant={**consultant.__dict__, **payload},
                errors=errors,
            )
        form = request._form
        profile_photo_path = await save_user_profile_photo(user, form.get("profile_photo"))
        update_user_public_profile(
            db,
            user,
            qualification=form.get("qualification"),
            designation=form.get("public_designation") or "Consultant",
            mobile=payload.get("mobile"),
            bio=form.get("bio"),
            profile_photo_path=profile_photo_path,
        )
        update_consultant_own_profile(db, consultant=consultant, payload=payload, user_id=user.id)
        db.commit()
        return RedirectResponse(url="/consultant/dashboard", status_code=303)
    finally:
        db.close()




CONSULTANT_PORTAL_TABS = {
    "overview": "modules/consultants/templates/consultants/portal_partials/overview.html",
    "assigned-work": "modules/consultants/templates/consultants/portal_partials/assigned_work.html",
    "clients": "modules/consultants/templates/consultants/portal_partials/clients.html",
    "documents": "modules/consultants/templates/consultants/portal_partials/documents.html",
    "clarifications": "modules/consultants/templates/consultants/portal_partials/clarifications.html",
    "requests": "modules/consultants/templates/consultants/portal_partials/service_requests.html",
    "reports": "modules/consultants/templates/consultants/portal_partials/reports.html",
}


def _consultant_empty_payload() -> dict:
    return {
        "links": [],
        "active_links": [],
        "linked_clients_page": {
            "rows": [],
            "groups": [],
            "meta": {"total": 0, "page": 1, "per_page": 25, "pages": 1},
            "filters": {"q": "", "client_group_id": None, "status": "active", "sort_by": "client_name", "sort_order": "asc"},
        },
        "comments": [],
        "recent_firm_messages": [],
        "recent_consultant_replies": [],
        "managed_clients": [],
        "workspace": None,
        "workspace_alerts": [],
        "service_requests": [],
        "open_service_requests": [],
        "pending_service_requests": [],
        "conversion_requests": [],
        "pending_conversions": [],
        "due_items": [],
        "overdue_items": [],
        "workspace_summary": {
            "workspace": None,
            "managed_clients_used": 0,
            "managed_clients_limit": None,
            "managed_clients_remaining": None,
            "usage_percent": 0,
        },
        "work_board": {"columns": {}, "column_options": [], "total_tasks": 0, "service_requests": []},
        "document_centre": {"engagement_documents": [], "permanent_documents": [], "total": 0},
        "stats": {
            "linked_clients": 0,
            "communication_enabled_clients": 0,
            "due_date_enabled_clients": 0,
            "pending_clarifications": 0,
            "consultant_replies": 0,
            "upcoming_due": 0,
            "overdue": 0,
            "open_service_requests": 0,
            "pending_service_requests": 0,
            "pending_conversions": 0,
            "converted_clients": 0,
            "managed_clients": 0,
            "prospects": 0,
            "referred_to_firm": 0,
        },
    }


def _build_consultant_portal_payload(
    db,
    consultant,
    *,
    linked_q: str = "",
    linked_group_id: int | None = None,
    linked_status: str = "active",
    linked_page: int = 1,
    linked_per_page: int = 25,
    linked_sort_by: str = "client_name",
    linked_sort_order: str = "asc",
) -> dict:
    payload = consultant_dashboard_payload(db, consultant=consultant)
    payload["linked_clients_page"] = consultant_linked_clients_page(
        db,
        consultant=consultant,
        q=linked_q,
        client_group_id=linked_group_id,
        status=linked_status,
        page=linked_page,
        per_page=linked_per_page,
        sort_by=linked_sort_by,
        sort_order=linked_sort_order,
    )
    try:
        payload["work_board"] = get_consultant_work_board(db, consultant=consultant)
    except Exception:
        payload["work_board"] = {"columns": {}, "column_options": [], "total_tasks": 0, "service_requests": []}
    try:
        payload["document_centre"] = get_consultant_document_centre(db, consultant=consultant)
    except Exception:
        payload["document_centre"] = {"engagement_documents": [], "permanent_documents": [], "total": 0}
    return payload



@portal_router.get("/dashboard")
def consultant_dashboard(
    request: Request,
    tab: str = "overview",
    linked_q: str = "",
    linked_group_id: int | None = None,
    linked_status: str = "active",
    linked_page: int = 1,
    linked_per_page: int = 25,
    linked_sort_by: str = "client_name",
    linked_sort_order: str = "asc",
):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        tenant_id = int(getattr(user, "tenant_id", 0) or 0)
        consultant = get_consultant_by_user(db, tenant_id=tenant_id, user_id=user.id)
        active_tab = tab if tab in CONSULTANT_PORTAL_TABS else "overview"
        payload = _build_consultant_portal_payload(
            db,
            consultant,
            linked_q=linked_q,
            linked_group_id=linked_group_id,
            linked_status=linked_status,
            linked_page=linked_page,
            linked_per_page=linked_per_page,
            linked_sort_by=linked_sort_by,
            linked_sort_order=linked_sort_order,
        ) if consultant else _consultant_empty_payload()
        return _render(
            request,
            "modules/consultants/templates/consultants/portal_dashboard.html",
            db,
            user,
            title="Consultant Portal",
            consultant=consultant,
            payload=payload,
            active_tab=active_tab,
        )
    finally:
        db.close()


@portal_router.get("/dashboard/tab/{tab_name}")
def consultant_dashboard_tab(
    request: Request,
    tab_name: str,
    linked_q: str = "",
    linked_group_id: int | None = None,
    linked_status: str = "active",
    linked_page: int = 1,
    linked_per_page: int = 25,
    linked_sort_by: str = "client_name",
    linked_sort_order: str = "asc",
):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        tenant_id = int(getattr(user, "tenant_id", 0) or 0)
        consultant = get_consultant_by_user(db, tenant_id=tenant_id, user_id=user.id)
        active_tab = tab_name if tab_name in CONSULTANT_PORTAL_TABS else "overview"
        payload = _build_consultant_portal_payload(
            db,
            consultant,
            linked_q=linked_q,
            linked_group_id=linked_group_id,
            linked_status=linked_status,
            linked_page=linked_page,
            linked_per_page=linked_per_page,
            linked_sort_by=linked_sort_by,
            linked_sort_order=linked_sort_order,
        ) if consultant else _consultant_empty_payload()
        return _render(
            request,
            CONSULTANT_PORTAL_TABS[active_tab],
            db,
            user,
            title="Consultant Portal",
            consultant=consultant,
            payload=payload,
            active_tab=active_tab,
        )
    finally:
        db.close()


@portal_router.get("/communications")
def consultant_communications_list(request: Request, q: str = ""):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        rows = list_consultant_visible_communications(db, consultant=consultant, q=q)
        return _render(
            request,
            "modules/consultants/templates/consultants/communications_list.html",
            db,
            user,
            title="Consultant Communications",
            consultant=consultant,
            rows=rows,
            q=q,
        )
    finally:
        db.close()


@portal_router.get("/communications/{comment_id}")
def consultant_communication_detail(request: Request, comment_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        row = get_consultant_visible_communication(db, consultant=consultant, comment_id=comment_id)
        if not row:
            return RedirectResponse(url="/consultant/communications", status_code=303)
        comment, task, subscription, client, catalogue = row
        timeline = list_consultant_task_timeline(db, consultant=consultant, task_id=task.id)
        return _render(
            request,
            "modules/consultants/templates/consultants/communication_detail.html",
            db,
            user,
            title="Communication Detail",
            consultant=consultant,
            comment=comment,
            task=task,
            subscription=subscription,
            client=client,
            catalogue=catalogue,
            timeline=timeline,
            errors=[],
        )
    finally:
        db.close()


@portal_router.post("/communications/{comment_id}/reply")
async def consultant_communication_reply(request: Request, comment_id: int, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    form = await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        row = get_consultant_visible_communication(db, consultant=consultant, comment_id=comment_id)
        if not row:
            return RedirectResponse(url="/consultant/communications", status_code=303)
        comment, task, subscription, client, catalogue = row
        message = (form.get("message") or "").strip()
        errors = [] if message else ["Reply message is required."]
        if not errors:
            reply = add_consultant_task_reply(db, consultant=consultant, task=task, message=message, user_id=user.id)
            if not reply:
                errors.append("Reply could not be saved. The task may be locked or access is restricted.")
        if errors:
            timeline = list_consultant_task_timeline(db, consultant=consultant, task_id=task.id)
            return _render(
                request,
                "modules/consultants/templates/consultants/communication_detail.html",
                db,
                user,
                title="Communication Detail",
                consultant=consultant,
                comment=comment,
                task=task,
                subscription=subscription,
                client=client,
                catalogue=catalogue,
                timeline=timeline,
                errors=errors,
            )
        db.commit()
        return RedirectResponse(url=f"/consultant/communications/{comment_id}", status_code=303)
    finally:
        db.close()


@portal_router.get("/service-requests")
def consultant_service_requests_list(request: Request, status: str = ""):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        rows = list_consultant_service_requests(db, consultant=consultant, status=status)
        return _render(
            request,
            "modules/consultants/templates/consultants/service_requests_list.html",
            db,
            user,
            title="Service Requests",
            consultant=consultant,
            rows=rows,
            status=status,
        )
    finally:
        db.close()


@portal_router.get("/service-requests/new")
def consultant_service_request_new_page(request: Request):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        managed_clients = list_consultant_managed_clients(db, tenant_id=consultant.tenant_id, consultant_id=consultant.id)
        firm_links = list_client_links_for_consultant(db, consultant_id=consultant.id)
        services = list_consultant_requestable_services(db)
        return _render(
            request,
            "modules/consultants/templates/consultants/service_request_form.html",
            db,
            user,
            title="New Service Request",
            consultant=consultant,
            request_row=None,
            managed_clients=managed_clients,
            firm_links=[link for link in firm_links if link.is_active and link.can_view_client],
            services=services,
            errors=[],
        )
    finally:
        db.close()


@portal_router.post("/service-requests/new")
async def consultant_service_request_new_submit(request: Request, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        payload = _build_service_request_payload(request)
        errors = _validate_service_request_payload(payload)
        if errors:
            managed_clients = list_consultant_managed_clients(db, tenant_id=consultant.tenant_id, consultant_id=consultant.id)
            firm_links = list_client_links_for_consultant(db, consultant_id=consultant.id)
            services = list_consultant_requestable_services(db)
            return _render(
                request,
                "modules/consultants/templates/consultants/service_request_form.html",
                db,
                user,
                title="New Service Request",
                consultant=consultant,
                request_row=payload,
                managed_clients=managed_clients,
                firm_links=[link for link in firm_links if link.is_active and link.can_view_client],
                services=services,
                errors=errors,
            )
        try:
            row = create_consultant_service_request(db, consultant=consultant, payload=payload, user_id=user.id)
            db.commit()
            db.refresh(row)
        except Exception as exc:
            db.rollback()
            managed_clients = list_consultant_managed_clients(db, tenant_id=consultant.tenant_id, consultant_id=consultant.id)
            firm_links = list_client_links_for_consultant(db, consultant_id=consultant.id)
            services = list_consultant_requestable_services(db)
            return _render(
                request,
                "modules/consultants/templates/consultants/service_request_form.html",
                db,
                user,
                title="New Service Request",
                consultant=consultant,
                request_row=payload,
                managed_clients=managed_clients,
                firm_links=[link for link in firm_links if link.is_active and link.can_view_client],
                services=services,
                errors=[str(exc)],
            )
        return RedirectResponse(url=f"/consultant/service-requests/{row.id}", status_code=303)
    finally:
        db.close()


@portal_router.get("/service-requests/{request_id}")
def consultant_service_request_detail(request: Request, request_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        row = get_consultant_service_request(db, tenant_id=consultant.tenant_id, request_id=request_id, consultant_id=consultant.id)
        if not row:
            return RedirectResponse(url="/consultant/service-requests", status_code=303)
        return _render(
            request,
            "modules/consultants/templates/consultants/service_request_detail.html",
            db,
            user,
            title="Service Request Detail",
            consultant=consultant,
            request_row=row,
            internal_view=False,
        )
    finally:
        db.close()


@portal_router.get("/work")
def consultant_work_board(request: Request, q: str = "", status: str = ""):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        board = get_consultant_work_board(db, consultant=consultant, q=q, status=status)
        return _render(
            request,
            "modules/consultants/templates/consultants/work_board.html",
            db,
            user,
            title="My Consultant Work",
            consultant=consultant,
            board=board,
            q=q,
            status=status,
        )
    finally:
        db.close()


@portal_router.get("/engagements/{subscription_id}", response_class=HTMLResponse)
def consultant_engagement_progress(request: Request, subscription_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        progress = get_consultant_engagement_progress(db, consultant=consultant, subscription_id=subscription_id)
        if not progress:
            return RedirectResponse(url="/consultant/work", status_code=303)
        return _render(
            request,
            "modules/consultants/templates/consultants/engagement_progress.html",
            db,
            user,
            title="Engagement Progress",
            consultant=consultant,
            **progress,
        )
    finally:
        db.close()


@portal_router.get("/assignments/{task_id}")
def consultant_assignment_detail(request: Request, task_id: int):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        detail = get_consultant_assignment_detail(db, consultant=consultant, task_id=task_id)
        if not detail:
            return RedirectResponse(url="/consultant/work", status_code=303)
        return _render(
            request,
            "modules/consultants/templates/consultants/assignment_detail.html",
            db,
            user,
            title="Consultant Work Detail",
            consultant=consultant,
            **detail,
            errors=[],
        )
    finally:
        db.close()


@portal_router.post("/assignments/{task_id}/reply")
async def consultant_assignment_reply(request: Request, task_id: int, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    form = await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        detail = get_consultant_assignment_detail(db, consultant=consultant, task_id=task_id)
        if not detail:
            return RedirectResponse(url="/consultant/work", status_code=303)
        message = (form.get("message") or "").strip()
        errors = [] if message else ["Reply/submission note is required."]
        if not errors:
            reply = add_consultant_task_reply(db, consultant=consultant, task=detail["task"], message=message, user_id=user.id)
            if not reply:
                errors.append("Reply could not be saved. The task may be locked or access is restricted.")
        if errors:
            refreshed = get_consultant_assignment_detail(db, consultant=consultant, task_id=task_id) or detail
            return _render(
                request,
                "modules/consultants/templates/consultants/assignment_detail.html",
                db,
                user,
                title="Consultant Work Detail",
                consultant=consultant,
                **refreshed,
                errors=errors,
            )
        db.commit()
        return RedirectResponse(url=f"/consultant/assignments/{task_id}", status_code=303)
    finally:
        db.close()


@portal_router.get("/documents")
def consultant_documents_page(request: Request, q: str = ""):
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        docs = get_consultant_document_centre(db, consultant=consultant, q=q)
        return _render(
            request,
            "modules/consultants/templates/consultants/documents.html",
            db,
            user,
            title="Consultant Documents",
            consultant=consultant,
            docs=docs,
            q=q,
        )
    finally:
        db.close()


@portal_router.post("/assignments/{task_id}/execution")
async def consultant_assignment_execution(request: Request, task_id: int, csrf_token: str = Form(...)):
    validate_csrf(request, csrf_token)
    form = await request.form()
    db = CommonSessionLocal()
    try:
        user = get_current_user(request, db=db)
        if not user:
            return RedirectResponse(url="/login", status_code=303)
        if not (_has_perm(db, user, "consultants.portal.view") or _is_consultant(db, user)):
            return _redirect_denied()
        consultant = _get_own_consultant_or_dashboard(request, db, user)
        if not consultant:
            return RedirectResponse(url="/consultant/dashboard", status_code=303)
        detail = get_consultant_assignment_detail(db, consultant=consultant, task_id=task_id)
        if not detail:
            return RedirectResponse(url="/consultant/work", status_code=303)
        ok, error = update_consultant_assignment(db, consultant=consultant, task=detail["task"], action=str(form.get("action") or ""), note=str(form.get("note") or ""), user_id=user.id)
        if not ok:
            refreshed = get_consultant_assignment_detail(db, consultant=consultant, task_id=task_id) or detail
            return _render(request, "modules/consultants/templates/consultants/assignment_detail.html", db, user, title="Consultant Work Detail", consultant=consultant, **refreshed, errors=[error or "Action could not be completed."])
        db.commit()
        return RedirectResponse(url=f"/consultant/assignments/{task_id}?execution=updated", status_code=303)
    except Exception:
        db.rollback(); raise
    finally:
        db.close()
