"""Account creation, the login upsert, and level assignment.

The upsert here is the one piece of this app that must not be "simplified": see
the COLUMN-SCOPED comment on `upsert_from_bw_identity`.
"""

from sqlalchemy import func, select
from sqlalchemy.dialects.postgresql import insert
from sqlalchemy.orm import Session

from app.constants import DEFAULT_LEVEL, LEVEL_ADMIN
from app.models.account import Account


def get(db: Session, username: str) -> Account | None:
    return db.get(Account, username.lower())


def list_all(db: Session) -> list[Account]:
    return list(db.scalars(select(Account).order_by(Account.username)))


def upsert_from_bw_identity(db: Session, identity: dict, initial_level: str = DEFAULT_LEVEL) -> Account:
    """Insert-or-update an account from a verified BW identity, then return it.

    COLUMN-SCOPED: on conflict this updates ONLY the BW-derived columns
    (email/first/last/last_login). App-local columns (level, active, and any
    profile field added later) are deliberately absent, so signing in can never
    wipe something the app or an admin set.

    `level` is SEEDED only when it is currently empty — on the first INSERT and on
    a pre-provisioned row an admin created before the person ever signed in.
    COALESCE(NULLIF(...)) makes that a seed rather than an overwrite: once a level
    holds a real value, every later login preserves it.

    Do NOT rewrite this as INSERT ... ON CONFLICT DO UPDATE SET (whole row) or as
    a delete-then-insert; either form silently discards the app-local columns.
    """
    username = identity["username"].lower()
    stmt = insert(Account).values(
        username=username,
        email=identity.get("email", "") or "",
        first=identity.get("first", "") or "",
        last=identity.get("last", "") or "",
        level=initial_level,
        last_login=func.now(),
    )
    stmt = stmt.on_conflict_do_update(
        index_elements=[Account.username],
        set_={
            "email": stmt.excluded.email,
            "first": stmt.excluded.first,
            "last": stmt.excluded.last,
            "last_login": func.now(),
            "level": func.coalesce(func.nullif(Account.level, ""), stmt.excluded.level),
        },
    )
    db.execute(stmt)
    db.commit()
    return db.get(Account, username)


def provision(db: Session, identity: dict, level: str, added_by: str) -> Account:
    """Create (or fill in) an account for someone an admin is granting access to.

    Called with a profile fetched from BW, so the row carries a real name from the
    moment it exists — the admin screens have something to show before that person
    has ever signed in. An existing row keeps its level; changing one is an
    explicit `set_level` call, never a side effect of re-adding someone.
    """
    existing = get(db, identity["username"])
    if existing is not None:
        return existing
    account = Account(
        username=identity["username"].lower(),
        email=identity.get("email", "") or "",
        first=identity.get("first", "") or "",
        last=identity.get("last", "") or "",
        level=level,
        active=True,
    )
    db.add(account)
    db.commit()
    db.refresh(account)
    return account


def set_level(db: Session, account: Account, level: str) -> Account:
    account.level = level
    db.commit()
    db.refresh(account)
    return account


def set_all_instances(db: Session, account: Account, value: bool) -> Account:
    """Whether this account's app-wide level reaches every project without an
    explicit per-project grant. This is what makes someone staff in practice."""
    account.all_instances = bool(value)
    db.commit()
    db.refresh(account)
    return account


def set_active(db: Session, account: Account, active: bool) -> Account:
    account.active = active
    db.commit()
    db.refresh(account)
    return account


def seed_initial_admins(db: Session, usernames: list[str]) -> list[str]:
    """Give the bootstrap admins their level at startup.

    Seeds an EMPTY level only. It never promotes an account that already holds
    one, so a deliberate demotion is not undone by the next restart, and removing
    a name from the env var does not strip anyone's access.
    Returns the usernames actually seeded.
    """
    seeded: list[str] = []
    for username in usernames:
        account = get(db, username)
        if account is None:
            db.add(Account(username=username.lower(), level=LEVEL_ADMIN,
                           all_instances=True, active=True))
            seeded.append(username)
        elif not account.level:
            account.level = LEVEL_ADMIN
            account.all_instances = True
            seeded.append(username)
    if seeded:
        db.commit()
    return seeded