"""services/hub — the Hub v0 cross-tenant read surface (super-admin ONLY).

The seed of the future hub: five read-only "what do we have?" tables that
show EVERY tenant's data at a glance (Projects, Organizations, People,
Tenants, Accounts). Structure only — no money is read, so there are no
``can_see_income`` concerns.

CRITICAL SECURITY CONSTRAINT
----------------------------
These reads are DELIBERATELY cross-tenant: unlike every other list in the
app they do NOT apply ``authz.scope()`` / the tenant filter — they return
rows from ALL tenants. That makes them a **super-admin-only** surface. A
cross-tenant leak to a partner (rob/danielle/adi) would be a serious
breach, so ``require_super_admin`` gates EVERY function here and runs
BEFORE any query touches the database:

  * a non-super-admin  -> 403 FORBIDDEN, zero rows.
  * a super admin who is currently VIEWING-AS anyone (even another super
    admin) -> 403 FORBIDDEN. Under view-as ``actor.is_super_admin`` is the
    TARGET's flag and ``actor.is_viewing_as`` is True, so the ``is_super
    && !viewing_as`` idiom (the same one scope()/policy()/comments() use)
    closes both holes.

The hub paths are also tenant-exempt (``deps._TENANT_EXEMPT_PREFIXES``) so
a cross-tenant read never trips the NO_TENANT gate — hub data is not
tenant-scoped, and a super admin with zero tenants must still be able to
read it.

Every query is a single grouped statement (subquery counts, not N+1).
"""

from __future__ import annotations

import json
import uuid

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

from app.models import (
    ClientProfile,
    Engagement,
    InvoiceLine,
    Organization,
    Party,
    PartyAffiliation,
    Project,
    Tenant,
    TenantVendor,
    TimeEntry,
    User,
)
from app.services.errors import ServiceError
from app.services.tenancy import effective_books_owner


def require_super_admin(actor) -> None:
    """THE hub gate. Cross-tenant reads (no scope()) are super-admin only;
    a non-super-admin — or a super admin currently VIEWING-AS — is refused
    with 403 FORBIDDEN before any row is read."""
    is_super = getattr(actor, "is_super_admin", False)
    viewing_as = getattr(actor, "is_viewing_as", False)
    if not (is_super and not viewing_as):
        raise ServiceError(
            403,
            "FORBIDDEN",
            "The hub is available to super admins only.",
            "These views span every tenant's data, so they are restricted to "
            "the server owner and are unavailable while viewing as another user.",
        )


def _sid(value: uuid.UUID | None) -> str | None:
    return str(value) if value is not None else None


def projects(session: Session, actor) -> dict:
    """Every project across every tenant, with its operator/client/owning
    tenant and two derived counts. ``worker_count`` = distinct workers who
    logged time on it; ``invoice_count`` = distinct invoices reached
    through its time entries' invoice lines (the ``invoice_line_id`` lock).
    Ordered by tenant then project name.

    Deliberately excludes ``income``/``billout_adjustment_*`` — Hub v0 is
    money-free by design (structure only, no ``can_see_income`` gate to
    reason about). Flag it if that line should move."""
    require_super_admin(actor)

    operator = aliased(Party)
    client = aliased(Party)
    tenant_party = aliased(Party)

    workers = (
        select(
            TimeEntry.project_id.label("project_id"),
            func.count(distinct(TimeEntry.worker_party_id)).label("worker_count"),
        )
        .where(TimeEntry.project_id.is_not(None))
        .group_by(TimeEntry.project_id)
        .subquery()
    )
    invoices = (
        select(
            TimeEntry.project_id.label("project_id"),
            func.count(distinct(InvoiceLine.invoice_id)).label("invoice_count"),
        )
        .join(InvoiceLine, InvoiceLine.id == TimeEntry.invoice_line_id)
        .where(TimeEntry.project_id.is_not(None))
        .group_by(TimeEntry.project_id)
        .subquery()
    )

    rows = session.execute(
        select(
            Project.id,
            Project.name,
            Project.status,
            operator.name.label("operator"),
            client.name.label("client"),
            tenant_party.name.label("tenant"),
            Project.notes,
            Project.created_at,
            Project.updated_at,
            func.coalesce(workers.c.worker_count, 0),
            func.coalesce(invoices.c.invoice_count, 0),
        )
        .join(Tenant, Tenant.id == Project.tenant_id)
        .join(tenant_party, tenant_party.id == Tenant.party_id)
        .join(operator, operator.id == Project.operator_party_id, isouter=True)
        .join(client, client.id == Project.client_party_id, isouter=True)
        .join(workers, workers.c.project_id == Project.id, isouter=True)
        .join(invoices, invoices.c.project_id == Project.id, isouter=True)
        .order_by(func.lower(tenant_party.name), func.lower(Project.name))
    ).all()

    return {
        "rows": [
            {
                "id": _sid(pid),
                "name": name,
                "operator": operator_name,
                "client": client_name,
                "tenant": tenant_name,
                "status": status,
                "notes": notes,
                "created_at": created_at.isoformat(),
                "updated_at": updated_at.isoformat(),
                "worker_count": int(worker_count),
                "invoice_count": int(invoice_count),
            }
            for (
                pid,
                name,
                status,
                operator_name,
                client_name,
                tenant_name,
                notes,
                created_at,
                updated_at,
                worker_count,
                invoice_count,
            ) in rows
        ]
    }


def organizations(session: Session, actor) -> dict:
    """Every organization party — every ``parties`` (kind='org') column plus
    its 1:1 ``organizations`` detail row (LEFT JOIN, not INNER: an org party
    with no detail row would be an invariant violation, and this audit
    surface should show that as nulls rather than silently drop the row).
    ``is_tenant`` = it backs a tenant; ``project_count`` = projects where it
    is the client; ``member_count`` = distinct people with an active
    affiliation to it. Ordered by name."""
    require_super_admin(actor)

    project_counts = (
        select(
            Project.client_party_id.label("party_id"),
            func.count(Project.id).label("n"),
        )
        .group_by(Project.client_party_id)
        .subquery()
    )
    member_counts = (
        select(
            PartyAffiliation.org_party_id.label("party_id"),
            func.count(distinct(PartyAffiliation.person_party_id)).label("n"),
        )
        .where(PartyAffiliation.ended_on.is_(None))
        .group_by(PartyAffiliation.org_party_id)
        .subquery()
    )

    rows = session.execute(
        select(
            Party.id,
            Party.name,
            Party.slug,
            Party.email,
            Party.phone,
            Party.address,
            Party.notes,
            Party.is_active,
            Party.created_at,
            Party.updated_at,
            Organization.legal_name,
            Organization.currency,
            Organization.drive_folder_url,
            Organization.mosiah_folder,
            Tenant.id,  # NULL => not a tenant
            func.coalesce(project_counts.c.n, 0),
            func.coalesce(member_counts.c.n, 0),
        )
        .join(Organization, Organization.party_id == Party.id, isouter=True)
        .join(Tenant, Tenant.party_id == Party.id, isouter=True)
        .join(project_counts, project_counts.c.party_id == Party.id, isouter=True)
        .join(member_counts, member_counts.c.party_id == Party.id, isouter=True)
        .where(Party.kind == "org")
        .order_by(func.lower(Party.name))
    ).all()

    return {
        "rows": [
            {
                "id": _sid(pid),
                "name": name,
                "slug": slug,
                "email": email,
                "phone": phone,
                "address": address,
                "notes": notes,
                "is_active": bool(is_active),
                "created_at": created_at.isoformat(),
                "updated_at": updated_at.isoformat(),
                "legal_name": legal_name,
                "currency": currency,
                "drive_folder_url": drive_folder_url,
                "mosiah_folder": mosiah_folder,
                "is_tenant": tenant_id is not None,
                "project_count": int(project_count),
                "member_count": int(member_count),
            }
            for (
                pid,
                name,
                slug,
                email,
                phone,
                address,
                notes,
                is_active,
                created_at,
                updated_at,
                legal_name,
                currency,
                drive_folder_url,
                mosiah_folder,
                tenant_id,
                project_count,
                member_count,
            ) in rows
        ]
    }


def organization_detail(session: Session, actor, party_id: uuid.UUID) -> dict:
    """Everything tenant-scoped hung off ONE org party — for the hub's
    expand-a-row drill-down. An org's identity (``organizations()`` above)
    is tenant-agnostic, but its COMMERCIAL relationships are per-tenant:
    ``client_profiles`` (it's a client of a tenant — terms/commission/
    currency/credit-limit, one row per tenant it's a client of; the same
    org spread across N tenants shows up here as N rows, which is exactly
    the multi-tenant-duplication signal this view exists to surface) and
    ``tenant_vendors`` (it's a listed vendor of a tenant). Both ordered by
    tenant name."""
    require_super_admin(actor)

    tenant_party = aliased(Party)
    commission_party = aliased(Party)
    profile_rows = session.execute(
        select(
            tenant_party.name,
            tenant_party.slug,
            ClientProfile.currency,
            ClientProfile.terms,
            ClientProfile.invoice_timing,
            ClientProfile.default_commission_pct,
            commission_party.name,
            ClientProfile.credit_limit,
            ClientProfile.contacts,
            ClientProfile.archived,
            ClientProfile.notes,
            ClientProfile.created_at,
            ClientProfile.updated_at,
        )
        .join(Tenant, Tenant.id == ClientProfile.tenant_id)
        .join(tenant_party, tenant_party.id == Tenant.party_id)
        .join(
            commission_party,
            commission_party.id == ClientProfile.commission_party_id,
            isouter=True,
        )
        .where(ClientProfile.client_party_id == party_id)
        .order_by(func.lower(tenant_party.name))
    ).all()

    tenant_party2 = aliased(Party)
    paid_by_party = aliased(Party)
    vendor_rows = session.execute(
        select(
            tenant_party2.name,
            tenant_party2.slug,
            TenantVendor.default_category,
            paid_by_party.name,
        )
        .join(Tenant, Tenant.id == TenantVendor.tenant_id)
        .join(tenant_party2, tenant_party2.id == Tenant.party_id)
        .join(
            paid_by_party,
            paid_by_party.id == TenantVendor.default_paid_by_party_id,
            isouter=True,
        )
        .where(TenantVendor.vendor_party_id == party_id)
        .order_by(func.lower(tenant_party2.name))
    ).all()

    return {
        "client_profiles": [
            {
                "tenant": tenant_name,
                "tenant_slug": tenant_slug,
                "currency": currency,
                "terms": terms,
                "invoice_timing": invoice_timing,
                "default_commission_pct": (
                    float(pct) if pct is not None else None
                ),
                "commission_party": commission_name,
                "credit_limit": (
                    float(credit_limit) if credit_limit is not None else None
                ),
                "contacts": contacts,
                "archived": bool(archived),
                "notes": notes,
                "created_at": created_at.isoformat(),
                "updated_at": updated_at.isoformat(),
            }
            for (
                tenant_name,
                tenant_slug,
                currency,
                terms,
                invoice_timing,
                pct,
                commission_name,
                credit_limit,
                contacts,
                archived,
                notes,
                created_at,
                updated_at,
            ) in profile_rows
        ],
        "tenant_vendors": [
            {
                "tenant": tenant_name,
                "tenant_slug": tenant_slug,
                "default_category": default_category,
                "default_paid_by": paid_by_name,
            }
            for tenant_name, tenant_slug, default_category, paid_by_name in vendor_rows
        ],
    }


def people(session: Session, actor) -> dict:
    """Every person party — every ``parties`` (kind='person') column.
    ``has_account`` = a users row links here; ``affiliation_count`` = active
    affiliations; ``is_worker`` = holds an active engagement (as party_b).
    (The ``people`` detail table itself has no fields of its own yet beyond
    its own created_at/updated_at — everything person-specific still lives
    on the shared party row.) Ordered by name."""
    require_super_admin(actor)

    affiliation_counts = (
        select(
            PartyAffiliation.person_party_id.label("party_id"),
            func.count(PartyAffiliation.id).label("n"),
        )
        .where(PartyAffiliation.ended_on.is_(None))
        .group_by(PartyAffiliation.person_party_id)
        .subquery()
    )
    workers = (
        select(distinct(Engagement.party_b_id).label("party_id"))
        .where(Engagement.ended_on.is_(None))
        .subquery()
    )

    rows = session.execute(
        select(
            Party.id,
            Party.name,
            Party.slug,
            Party.email,
            Party.phone,
            Party.address,
            Party.notes,
            Party.is_active,
            Party.created_at,
            Party.updated_at,
            User.id,  # NULL => no account
            func.coalesce(affiliation_counts.c.n, 0),
            workers.c.party_id,  # NULL => not a worker
        )
        .join(User, User.person_party_id == Party.id, isouter=True)
        .join(affiliation_counts, affiliation_counts.c.party_id == Party.id, isouter=True)
        .join(workers, workers.c.party_id == Party.id, isouter=True)
        .where(Party.kind == "person")
        .order_by(func.lower(Party.name))
    ).all()

    return {
        "rows": [
            {
                "id": _sid(pid),
                "name": name,
                "slug": slug,
                "email": email,
                "phone": phone,
                "address": address,
                "notes": notes,
                "is_active": bool(is_active),
                "created_at": created_at.isoformat(),
                "updated_at": updated_at.isoformat(),
                "has_account": user_id is not None,
                "affiliation_count": int(affiliation_count),
                "is_worker": worker_party_id is not None,
            }
            for (
                pid,
                name,
                slug,
                email,
                phone,
                address,
                notes,
                is_active,
                created_at,
                updated_at,
                user_id,
                affiliation_count,
                worker_party_id,
            ) in rows
        ]
    }


def tenants(session: Session, actor) -> dict:
    """Every tenant (a set of books) — its ``tenants`` row plus its backing
    party's identity fields. ``books_owner`` is the effective ownership
    (app | sheet | None, derived from ``settings``); ``settings`` itself is
    also exposed raw (JSON) since it's the one tenant-only field and may
    carry other keys (e.g. legacy ``parallel_run``) worth seeing directly.
    ``member_count`` = distinct people with an active engagement in it;
    ``project_count`` = its projects. Ordered by org name."""
    require_super_admin(actor)

    person = aliased(Party)
    member_counts = (
        select(
            Engagement.tenant_id.label("tenant_id"),
            func.count(distinct(Engagement.party_b_id)).label("n"),
        )
        .join(person, person.id == Engagement.party_b_id)
        .where(Engagement.ended_on.is_(None), person.kind == "person")
        .group_by(Engagement.tenant_id)
        .subquery()
    )
    project_counts = (
        select(
            Project.tenant_id.label("tenant_id"),
            func.count(Project.id).label("n"),
        )
        .group_by(Project.tenant_id)
        .subquery()
    )

    rows = session.execute(
        select(
            Tenant.id,
            Party.name,
            Party.slug,
            Party.email,
            Party.phone,
            Party.address,
            Party.notes,
            Party.is_active,
            Party.created_at,
            Party.updated_at,
            Tenant.settings,
            func.coalesce(member_counts.c.n, 0),
            func.coalesce(project_counts.c.n, 0),
        )
        .join(Party, Party.id == Tenant.party_id)
        .join(member_counts, member_counts.c.tenant_id == Tenant.id, isouter=True)
        .join(project_counts, project_counts.c.tenant_id == Tenant.id, isouter=True)
        .order_by(func.lower(Party.name))
    ).all()

    return {
        "rows": [
            {
                "id": _sid(tenant_id),
                "name": name,
                "slug": slug,
                "email": email,
                "phone": phone,
                "address": address,
                "notes": notes,
                "is_active": bool(is_active),
                "created_at": created_at.isoformat(),
                "updated_at": updated_at.isoformat(),
                "books_owner": effective_books_owner(settings),
                "settings": json.dumps(settings or {}, sort_keys=True),
                "member_count": int(member_count),
                "project_count": int(project_count),
            }
            for (
                tenant_id,
                name,
                slug,
                email,
                phone,
                address,
                notes,
                is_active,
                created_at,
                updated_at,
                settings,
                member_count,
                project_count,
            ) in rows
        ]
    }


def accounts(session: Session, actor) -> dict:
    """Every app login (users row) — every ``users`` column. ``person_name``
    = the linked person party's name (or null); ``tenant_memberships`` = the
    slugs of tenants the person holds an active engagement in. Ordered by
    username."""
    require_super_admin(actor)

    # One grouped pass: person_party_id -> {tenant slugs with an active
    # engagement}, so the per-account membership list is a dict lookup
    # (never a query per row).
    tenant_party = aliased(Party)
    membership_rows = session.execute(
        select(Engagement.party_b_id, tenant_party.slug)
        .join(Tenant, Tenant.id == Engagement.tenant_id)
        .join(tenant_party, tenant_party.id == Tenant.party_id)
        .where(Engagement.ended_on.is_(None))
    ).all()
    memberships: dict[uuid.UUID, set[str]] = {}
    for person_party_id, slug in membership_rows:
        memberships.setdefault(person_party_id, set()).add(slug)

    person = aliased(Party)
    rows = session.execute(
        select(
            User.id,
            User.idauth_username,
            User.email,
            User.is_super_admin,
            User.person_party_id,
            User.preferences,
            User.created_at,
            person.name,
        )
        .join(person, person.id == User.person_party_id, isouter=True)
        .order_by(func.lower(User.idauth_username))
    ).all()

    return {
        "rows": [
            {
                "id": _sid(user_id),
                "idauth_username": username,
                "email": email,
                "person_id": _sid(person_party_id),
                "person_name": person_name,
                "is_super_admin": bool(is_super_admin),
                "preferences": json.dumps(preferences or {}, sort_keys=True),
                "created_at": created_at.isoformat(),
                "tenant_memberships": sorted(memberships.get(person_party_id, set())),
            }
            for (
                user_id,
                username,
                email,
                is_super_admin,
                person_party_id,
                preferences,
                created_at,
                person_name,
            ) in rows
        ]
    }