"""角色 CRUD（sys_role / sys_role_menu / sys_role_data_scope_detail）"""
from __future__ import annotations

import math
from datetime import datetime
from typing import Any, Dict, List, Optional, Tuple

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

from app.core.utils import generate_uuid
from app.crud.child_row_sync import sync_child_rows, sync_link_rows
from app.services.permission_administration import menu_ids_with_ancestors, validate_tenant_references
from app.models.models import (
    SysPost,
    SysPostRole,
    SysRole,
    SysRoleDataScopeDetail,
    SysRoleMenu,
    SysUser,
    SysUserPost,
    SysUserRole,
)


# ---------------------------------------------------------------------------
# helpers
# ---------------------------------------------------------------------------


def _user_display_name(nick_name: Optional[str], user_no: Optional[str]) -> str:
    n = (nick_name or "").strip()
    if n:
        return n
    return (user_no or "").strip()


def _append_unique(out: Dict[str, List[str]], key: str, value: str) -> None:
    if not key or not value:
        return
    bucket = out.setdefault(key, [])
    if value not in bucket:
        bucket.append(value)


def _bound_user_names_by_role_ids(
    db: Session, role_ids: List[str], tenant_no: str
) -> Dict[str, List[str]]:
    """批量查询有效拥有角色的用户显示名（直接绑定 ∪ 启用职位继承）。"""
    out: Dict[str, List[str]] = {}
    ids = [rid for rid in (role_ids or []) if rid]
    tenant = (tenant_no or "").strip()
    if not ids or not tenant:
        return out

    direct_rows = (
        db.query(SysUserRole.role_id, SysUser.nick_name, SysUser.user_no)
        .join(
            SysUser,
            and_(SysUser.id == SysUserRole.user_id, SysUser.del_flag == 0),
        )
        .join(
            SysRole,
            and_(
                SysRole.id == SysUserRole.role_id,
                SysRole.del_flag == 0,
                SysRole.status == "1",
                SysRole.tenant_no == tenant,
            ),
        )
        .filter(
            and_(
                SysUserRole.role_id.in_(ids),
                SysUserRole.tenant_no == tenant,
                SysUserRole.del_flag == 0,
            )
        )
        .order_by(SysUser.nick_name.asc(), SysUser.user_no.asc())
        .all()
    )
    for role_id, nick_name, user_no in direct_rows:
        _append_unique(out, role_id, _user_display_name(nick_name, user_no))

    post_rows = (
        db.query(SysPostRole.role_id, SysUser.nick_name, SysUser.user_no)
        .join(
            SysPost,
            and_(
                SysPost.id == SysPostRole.post_id,
                SysPost.del_flag == 0,
                SysPost.status == "1",
                SysPost.tenant_no == tenant,
            ),
        )
        .join(
            SysUserPost,
            and_(
                SysUserPost.post_id == SysPost.id,
                SysUserPost.del_flag == 0,
                SysUserPost.tenant_no == tenant,
            ),
        )
        .join(
            SysUser,
            and_(SysUser.id == SysUserPost.user_id, SysUser.del_flag == 0),
        )
        .join(
            SysRole,
            and_(
                SysRole.id == SysPostRole.role_id,
                SysRole.del_flag == 0,
                SysRole.status == "1",
                SysRole.tenant_no == tenant,
            ),
        )
        .filter(
            and_(
                SysPostRole.role_id.in_(ids),
                SysPostRole.tenant_no == tenant,
                SysPostRole.del_flag == 0,
            )
        )
        .order_by(SysUser.nick_name.asc(), SysUser.user_no.asc())
        .all()
    )
    for role_id, nick_name, user_no in post_rows:
        _append_unique(out, role_id, _user_display_name(nick_name, user_no))

    return out


def _bound_post_names_by_role_ids(
    db: Session, role_ids: List[str], tenant_no: str
) -> Dict[str, List[str]]:
    """批量查询角色关联的启用职位名称。"""
    out: Dict[str, List[str]] = {}
    ids = [rid for rid in (role_ids or []) if rid]
    tenant = (tenant_no or "").strip()
    if not ids or not tenant:
        return out

    rows = (
        db.query(SysPostRole.role_id, SysPost.post_name)
        .join(
            SysPost,
            and_(
                SysPost.id == SysPostRole.post_id,
                SysPost.del_flag == 0,
                SysPost.status == "1",
                SysPost.tenant_no == tenant,
            ),
        )
        .filter(
            and_(
                SysPostRole.role_id.in_(ids),
                SysPostRole.tenant_no == tenant,
                SysPostRole.del_flag == 0,
            )
        )
        .order_by(SysPost.post_name.asc())
        .all()
    )
    for role_id, post_name in rows:
        name = (post_name or "").strip()
        _append_unique(out, role_id, name)

    return out


def _row_to_dict(
    r: SysRole,
    *,
    bound_user_names: Optional[List[str]] = None,
    bound_post_names: Optional[List[str]] = None,
) -> Dict[str, Any]:
    return {
        "id": r.id,
        "roleName": r.role_name,
        "dataScope": r.data_scope or "1",
        "status": r.status or "1",
        "tenantNo": r.tenant_no or "",
        "orderNum": r.order_num if r.order_num is not None else 0,
        "remark": r.remark or "",
        "boundUserNames": list(bound_user_names or []),
        "boundPostNames": list(bound_post_names or []),
        "createTime": r.create_time.strftime("%Y-%m-%d %H:%M:%S") if r.create_time else "",
        "updateTime": r.update_time.strftime("%Y-%m-%d %H:%M:%S") if r.update_time else "",
    }


def total_pages(total: int, page_size: int) -> int:
    if total <= 0:
        return 0
    return int(math.ceil(total / float(page_size)))


# ---------------------------------------------------------------------------
# list / get
# ---------------------------------------------------------------------------


def list_roles(
    db: Session,
    *,
    page: int = 1,
    page_size: int = 10,
    keyword: Optional[str] = None,
    tenant_no: Optional[str] = None,
    user_info=None,
) -> Tuple[List[Dict[str, Any]], int]:
    def _filtered():
        q0 = db.query(SysRole).filter(SysRole.del_flag == 0)
        if tenant_no:
            q0 = q0.filter(SysRole.tenant_no == tenant_no)
        kw = (keyword or "").strip()
        if kw:
            like = f"%{kw}%"
            q0 = q0.filter(SysRole.role_name.like(like))
        if user_info:
            from app.core.data_scope import apply_data_scope
            q0 = apply_data_scope(q0, SysRole, user_info, db, tenant_no)
        return q0

    total = _filtered().order_by(None).with_entities(func.count(SysRole.id)).scalar() or 0
    page = max(1, page)
    page_size = min(max(1, page_size), 100)
    rows = (
        _filtered()
        .order_by(SysRole.create_time.desc())
        .offset((page - 1) * page_size)
        .limit(page_size)
        .all()
    )
    role_ids = [x.id for x in rows]
    tenant = tenant_no or ""
    user_name_map = _bound_user_names_by_role_ids(db, role_ids, tenant)
    post_name_map = _bound_post_names_by_role_ids(db, role_ids, tenant)
    return [
        _row_to_dict(
            x,
            bound_user_names=user_name_map.get(x.id, []),
            bound_post_names=post_name_map.get(x.id, []),
        )
        for x in rows
    ], int(total)


def get_role(db: Session, role_id: str) -> Optional[SysRole]:
    return (
        db.query(SysRole)
        .filter(and_(SysRole.id == role_id, SysRole.del_flag == 0))
        .first()
    )


def role_name_exists(db: Session, name: str, tenant_no: str, exclude_id: Optional[str] = None) -> bool:
    q = db.query(SysRole).filter(
        and_(SysRole.role_name == name.strip(), SysRole.tenant_no == tenant_no, SysRole.del_flag == 0)
    )
    if exclude_id:
        q = q.filter(SysRole.id != exclude_id)
    return q.first() is not None


# ---------------------------------------------------------------------------
# create / update / delete
# ---------------------------------------------------------------------------


def create_role(
    db: Session,
    *,
    role_name: str,
    data_scope: str = "4",
    status: str = "1",
    tenant_no: str = "zkxw",
    order_num: Optional[int] = None,
    remark: Optional[str] = None,
    user_id: Optional[str] = None,
) -> SysRole:
    rn = (role_name or "").strip()
    if not rn:
        raise ValueError("角色名称不能为空")
    if role_name_exists(db, rn, tenant_no):
        raise ValueError("角色名称已存在")
    obj = SysRole(
        id=generate_uuid(),
        role_name=rn,
        data_scope=data_scope,
        status=status,
        tenant_no=tenant_no,
        order_num=order_num if order_num is not None else 0,
        remark=(remark or "").strip() or None,
        create_by=user_id,
        create_dept_id="",
        create_time=datetime.now(),
        update_by=user_id,
        update_time=datetime.now(),
        del_flag=0,
    )
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


def update_role(
    db: Session,
    role_id: str,
    *,
    role_name: Optional[str] = None,
    data_scope: Optional[str] = None,
    status: Optional[str] = None,
    order_num: Optional[int] = None,
    remark: Optional[str] = None,
    user_id: Optional[str] = None,
) -> SysRole:
    obj = get_role(db, role_id)
    if not obj:
        raise ValueError("角色不存在")
    if role_name is not None:
        rn = role_name.strip()
        if not rn:
            raise ValueError("角色名称不能为空")
        if rn != obj.role_name and role_name_exists(db, rn, obj.tenant_no, exclude_id=role_id):
            raise ValueError("角色名称已存在")
        obj.role_name = rn
    if data_scope is not None:
        obj.data_scope = data_scope
    if status is not None:
        obj.status = status
    if order_num is not None:
        obj.order_num = order_num
    if remark is not None:
        obj.remark = (remark or "").strip() or None
    obj.update_by = user_id
    obj.update_time = datetime.now()
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


def soft_delete_role(db: Session, role_id: str) -> None:
    obj = get_role(db, role_id)
    if not obj:
        raise ValueError("角色不存在")
    obj.del_flag = 1
    obj.update_time = datetime.now()
    db.add(obj)
    # cascade soft-delete role_menu and role_data_scope_detail
    db.query(SysRoleMenu).filter(
        and_(SysRoleMenu.role_id == role_id, SysRoleMenu.del_flag == 0)
    ).update({"del_flag": 1, "update_time": datetime.now()})
    db.query(SysRoleDataScopeDetail).filter(
        and_(SysRoleDataScopeDetail.role_id == role_id, SysRoleDataScopeDetail.del_flag == 0)
    ).update({"del_flag": 1, "update_time": datetime.now()})
    db.commit()


# ---------------------------------------------------------------------------
# role-menu
# ---------------------------------------------------------------------------


def get_role_menu_ids(db: Session, role_id: str) -> List[str]:
    rows = (
        db.query(SysRoleMenu.menu_id)
        .filter(and_(SysRoleMenu.role_id == role_id, SysRoleMenu.del_flag == 0))
        .all()
    )
    return [r.menu_id for r in rows]


def update_role_menus(
    db: Session,
    role_id: str,
    menu_ids: List[str],
    tenant_no: str,
    user_id: Optional[str] = None,
) -> None:
    validate_tenant_references(db, SysRole, [role_id], tenant_no)
    menu_ids = menu_ids_with_ancestors(db, menu_ids, tenant_no)
    sync_link_rows(
        db,
        SysRoleMenu,
        parent_attr="role_id",
        parent_id=role_id,
        link_attr="menu_id",
        link_ids=menu_ids,
        tenant_no=tenant_no,
        user_id=user_id,
        duplicate_message="菜单重复",
        foreign_message="菜单关联不属于当前角色",
    )
    db.commit()


# ---------------------------------------------------------------------------
# role data-scope
# ---------------------------------------------------------------------------


def get_role_data_scope(db: Session, role_id: str) -> List[Dict[str, str]]:
    rows = (
        db.query(SysRoleDataScopeDetail)
        .filter(and_(SysRoleDataScopeDetail.role_id == role_id, SysRoleDataScopeDetail.del_flag == 0))
        .all()
    )
    return [{"scopeType": r.scope_type, "scopeValue": r.scope_value} for r in rows]


def update_role_data_scope(
    db: Session,
    role_id: str,
    data_scope: str,
    scope_details: Optional[List[Dict[str, str]]] = None,
    tenant_no: str = "zkxw",
    user_id: Optional[str] = None,
) -> None:
    obj = get_role(db, role_id)
    if not obj:
        raise ValueError("角色不存在")
    obj.data_scope = data_scope
    obj.update_by = user_id
    obj.update_time = datetime.now()
    db.add(obj)

    now = datetime.now()
    details = scope_details if data_scope == "5" and scope_details else []

    def build_scope(row_id):
        return SysRoleDataScopeDetail(
            id=row_id,
            role_id=role_id,
            scope_type="",
            scope_value="",
            tenant_no=tenant_no,
            create_by=user_id,
            create_dept_id="",
            create_time=now,
        )

    def apply_scope(row, detail, _idx, _now):
        row.scope_type = detail.get("scopeType", "dept")
        row.scope_value = detail.get("scopeValue", "")
        row.order_num = 0

    sync_child_rows(
        db,
        SysRoleDataScopeDetail,
        parent_attr="role_id",
        parent_id=role_id,
        tenant_no=tenant_no,
        items=details,
        user_id=user_id,
        now=now,
        build_row=build_scope,
        apply_fields=apply_scope,
        natural_key=lambda detail: f"{detail.get('scopeType', 'dept')}:{detail.get('scopeValue', '')}",
        row_natural_key=lambda row: f"{row.scope_type}:{row.scope_value}",
        duplicate_message="数据权限重复",
        foreign_message="数据权限不属于当前角色",
        reuse_deleted=True,
    )
    db.commit()