from uuid import UUID

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

from app.core.permissions import Role
from app.models.branch import Branch
from app.models.membership import Membership, MembershipBranch
from app.models.user import User
from app.services.audit import record_audit


class StaffError(ValueError):
    pass


def staff_for_business(db: Session, business_id: UUID) -> list[tuple[Membership, User]]:
    return list(db.execute(select(Membership, User).join(User, User.id == Membership.user_id).where(Membership.business_id == business_id).order_by(Membership.created_at)).all())


def membership_for_business(db: Session, business_id: UUID, membership_id: UUID, lock: bool = False) -> Membership:
    stmt = select(Membership).where(Membership.id == membership_id, Membership.business_id == business_id)
    if lock:
        stmt = stmt.with_for_update()
    membership = db.scalar(stmt)
    if membership is None:
        raise LookupError("Staff membership not found.")
    return membership


def _active_ceo_count(db: Session, business_id: UUID) -> int:
    return db.scalar(select(func.count()).select_from(Membership).where(Membership.business_id == business_id, Membership.is_active.is_(True), Membership.role == Role.CEO.value)) or 0


def _protect_last_ceo(db: Session, membership: Membership, new_role: Role | None = None, active: bool | None = None):
    removes_ceo = membership.role == Role.CEO.value and ((new_role is not None and new_role != Role.CEO) or active is False)
    if removes_ceo and _active_ceo_count(db, membership.business_id) <= 1:
        raise StaffError("The final active CEO cannot be demoted or deactivated.")


def _validate_branches(db: Session, business_id: UUID, branch_ids: list[UUID]):
    if len(branch_ids) != len(set(branch_ids)):
        raise StaffError("Branch IDs must not contain duplicates.")
    valid = set(db.scalars(select(Branch.id).where(Branch.business_id == business_id, Branch.is_active.is_(True))))
    if not set(branch_ids).issubset(valid):
        raise StaffError("One or more branches do not belong to this business.")


def set_branch_access(db: Session, membership: Membership, all_branches: bool, branch_ids: list[UUID], actor_user_id: UUID) -> Membership:
    _validate_branches(db, membership.business_id, branch_ids)
    previous_all_branches = membership.all_branches
    previous_branch_ids = set(db.scalars(select(MembershipBranch.branch_id).where(MembershipBranch.membership_id == membership.id)))
    membership.all_branches = all_branches
    db.query(MembershipBranch).filter(MembershipBranch.membership_id == membership.id).delete(synchronize_session=False)
    if not all_branches:
        db.add_all([MembershipBranch(membership_id=membership.id, branch_id=branch_id) for branch_id in branch_ids])
    new_branch_ids = set() if all_branches else set(branch_ids)
    record_audit(db, membership.business_id, 'STAFF_BRANCH_ACCESS_CHANGED', 'membership', membership.id, actor_user_id, None, {
        'staff_member_id': str(membership.id), 'previous_all_branches': previous_all_branches,
        'new_all_branches': all_branches, 'added_branch_ids': [str(branch_id) for branch_id in new_branch_ids - previous_branch_ids],
        'removed_branch_ids': [str(branch_id) for branch_id in previous_branch_ids - new_branch_ids],
    })
    db.commit(); db.refresh(membership)
    return membership


def add_staff(db: Session, business_id: UUID, email: str, role: Role, all_branches: bool, branch_ids: list[UUID], actor_user_id: UUID) -> Membership:
    user = db.scalar(select(User).where(User.email == email.lower()))
    if user is None:
        raise LookupError("No registered user was found for this email.")
    if db.scalar(select(Membership.id).where(Membership.business_id == business_id, Membership.user_id == user.id)) is not None:
        raise StaffError("This user is already a staff member of this business.")
    _validate_branches(db, business_id, branch_ids)
    membership = Membership(business_id=business_id, user_id=user.id, role=role.value, all_branches=all_branches, is_active=True)
    db.add(membership); db.flush()
    if not all_branches:
        db.add_all([MembershipBranch(membership_id=membership.id, branch_id=branch_id) for branch_id in branch_ids])
    record_audit(db, business_id, 'STAFF_ADDED', 'membership', membership.id, actor_user_id, None, {
        'staff_member_id': str(membership.id), 'user_id': str(user.id), 'role': role.value,
        'all_branches': all_branches, 'branch_ids': [str(branch_id) for branch_id in branch_ids],
    })
    db.commit(); db.refresh(membership)
    return membership