"""Scouting project and membership operations."""

import re

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

from app.constants import DEFAULT_LEVEL
from app.models.account import Account
from app.models.project import Project, ProjectMember

_SLUG_STRIP = re.compile(r"[^a-z0-9]+")


def slugify(name: str) -> str:
    return _SLUG_STRIP.sub("-", name.strip().lower()).strip("-")[:80] or "project"


def unique_slug(db: Session, name: str) -> str:
    """A slug that is free, disambiguated with a counter rather than a random
    suffix so the URL a client is sent stays readable."""
    base = slugify(name)
    candidate, n = base, 2
    while db.scalar(select(Project.id).where(Project.slug == candidate)) is not None:
        candidate = f"{base}-{n}"[:80]
        n += 1
    return candidate


def create(
    db: Session,
    *,
    name: str,
    client_name: str,
    brief: str,
    status: str,
    created_by: str,
    client_website: str = "",
) -> Project:
    project = Project(
        slug=unique_slug(db, name),
        name=name.strip(),
        client_name=client_name.strip(),
        client_website=client_website.strip(),
        brief=brief.strip(),
        status=status,
        created_by=created_by,
    )
    db.add(project)
    db.commit()
    db.refresh(project)
    return project


def update(db: Session, project: Project, **fields) -> Project:
    for key, value in fields.items():
        if value is not None:
            setattr(project, key, value.strip() if isinstance(value, str) else value)
    db.commit()
    db.refresh(project)
    return project


def members(db: Session, project_id: int) -> list[tuple[ProjectMember, Account]]:
    """Membership rows joined to their accounts, so a caller can render a name
    without a second query per member."""
    stmt = (
        select(ProjectMember, Account)
        .join(Account, Account.username == ProjectMember.username)
        .where(ProjectMember.project_id == project_id)
        .order_by(Account.username)
    )
    return list(db.execute(stmt).all())


def add_member(
    db: Session, project_id: int, username: str, added_by: str, level: str = DEFAULT_LEVEL
) -> ProjectMember:
    """Idempotent: adding someone already on the project updates their level
    rather than failing, so a double-click cannot break the admin's flow.

    A grant never LOWERS someone whose app-wide level already applies to every
    project. Without this, adding an admin to a project as a member writes a
    `reviewer` grant that overrides their `all_instances` standing and quietly
    strips their ability to manage that one project. Scoping someone down on a
    single project is still possible — it just has to be the explicit act of
    setting their level, not a side effect of adding them.
    """
    account = db.get(Account, username)
    if account is not None and account.all_instances:
        level = account.level
    existing = db.get(ProjectMember, (project_id, username))
    if existing is not None:
        if level and existing.level != level:
            existing.level = level
            db.commit()
            db.refresh(existing)
        return existing
    member = ProjectMember(
        project_id=project_id, username=username, added_by=added_by, level=level
    )
    db.add(member)
    db.commit()
    db.refresh(member)
    return member


def set_member_level(
    db: Session, project_id: int, username: str, level: str
) -> ProjectMember | None:
    """Change one person's level on one project. Returns None when they are not
    a member, which the router turns into a 404."""
    member = db.get(ProjectMember, (project_id, username))
    if member is None:
        return None
    member.level = level
    db.commit()
    db.refresh(member)
    return member


def remove_member(db: Session, project_id: int, username: str) -> bool:
    member = db.get(ProjectMember, (project_id, username))
    if member is None:
        return False
    db.delete(member)
    db.commit()
    return True


def delete_project(db: Session, project: Project) -> list[str]:
    """Delete a project, its rows (FK cascade), and its files on disk.

    Returns the usernames who were members, so the caller can re-report their
    access centrally after the project is gone.
    """
    import shutil

    from app.services import storage

    former_members = list(
        db.scalars(
            select(ProjectMember.username).where(ProjectMember.project_id == project.id)
        )
    )
    project_dir = storage.data_root() / "projects" / str(project.id)
    db.delete(project)
    db.commit()
    shutil.rmtree(project_dir, ignore_errors=True)
    return former_members


def member_counts(db: Session, project_ids: list[int]) -> dict[int, int]:
    """One query for the list screen instead of one per row."""
    if not project_ids:
        return {}
    rows = db.execute(
        select(ProjectMember.project_id, func.count())
        .where(ProjectMember.project_id.in_(project_ids))
        .group_by(ProjectMember.project_id)
    ).all()
    return {project_id: count for project_id, count in rows}