crushdb/backend/storage.py

import sqlite3
import uuid
import time
import hashlib
import hmac
import os

DB_PATH = os.environ.get("WHO2_DB_PATH", "instance/who2.sqlite")

RIGHTS = {
    "view_private": "Can view private articles",
    "manage_persons": "Can manage persons",
    "manage_articles": "Can manage articles",
    "manage_groups": "Can manage groups",
    "manage_accounts": "Can manage accounts",
}


ADMIN_RIGHT = "manage_accounts"


def _grant_all_rights(conn, account_id: str) -> None:
    for right_name in RIGHTS:
        conn.execute(
            "INSERT OR IGNORE INTO account_rights (account_id, right_name) VALUES (?,?)",
            (account_id, right_name),
        )


def bootstrap_admin(account_id: str) -> None:
    """Grant all rights to an account so it can manage the system.

    In this model an account has no rights by default, so bootstrap grants a starting
    set so the first admin can then grant rights to others.
    """
    conn = _conn()
    try:
        _grant_all_rights(conn, account_id)
        conn.commit()
    finally:
        conn.close()


def is_admin(account: dict | None) -> bool:
    return has_right(account, ADMIN_RIGHT)


def ensure_admin() -> None:
    """Guarantee that at least one account holds the admin right.

    WHO2_ADMIN_EMAIL promotes that account when it exists. Otherwise the oldest
    account is promoted, so an existing database gets exactly one admin.
    """
    admin_email = (os.environ.get("WHO2_ADMIN_EMAIL") or "").strip()
    conn = _conn()
    try:
        if admin_email:
            row = conn.execute(
                "SELECT id FROM accounts WHERE lower(email) = lower(?)", (admin_email,)
            ).fetchone()
            if row:
                _grant_all_rights(conn, row["id"])
                conn.commit()
                return
        existing = conn.execute(
            "SELECT account_id FROM account_rights WHERE right_name = ? LIMIT 1",
            (ADMIN_RIGHT,),
        ).fetchone()
        if existing:
            return
        # rowid breaks ties between accounts created in the same second, so the
        # earliest-inserted account is reliably the one that gets promoted.
        oldest = conn.execute("SELECT id FROM accounts ORDER BY created_at, rowid LIMIT 1").fetchone()
        if oldest:
            _grant_all_rights(conn, oldest["id"])
            conn.commit()
    finally:
        conn.close()


ARTICLE_TYPES = [
    "contact_details",
    "notes",
    "profile_text",
    "documents",
    "links",
    "phone_numbers",
    "addresses",
    "social_profiles",
]


def _now():
    return int(time.time())


def _conn():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    return conn


def _hash_password(password: str, salt: str) -> str:
    key = (salt + password).encode("utf-8")
    return hashlib.sha256(key).hexdigest()


def init_db():
    conn = _conn()
    try:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS accounts (
                id TEXT PRIMARY KEY,
                email TEXT NOT NULL UNIQUE,
                password_hash TEXT NOT NULL,
                password_salt TEXT NOT NULL,
                created_at INTEGER NOT NULL
            );

            CREATE TABLE IF NOT EXISTS persons (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL DEFAULT '',
                created_at INTEGER NOT NULL
            );

            CREATE TABLE IF NOT EXISTS person_accounts (
                person_id TEXT NOT NULL,
                account_id TEXT NOT NULL,
                PRIMARY KEY (person_id, account_id),
                FOREIGN KEY (person_id) REFERENCES persons(id),
                FOREIGN KEY (account_id) REFERENCES accounts(id)
            );

            CREATE TABLE IF NOT EXISTS articles (
                id TEXT PRIMARY KEY,
                person_id TEXT NOT NULL,
                article_type TEXT NOT NULL CHECK(article_type IN (
                    'contact_details','notes','profile_text','documents',
                    'links','phone_numbers','addresses','social_profiles'
                )),
                title TEXT NOT NULL DEFAULT '',
                content TEXT NOT NULL DEFAULT '',
                visibility TEXT NOT NULL DEFAULT 'private'
                    CHECK(visibility IN ('private','group','public')),
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                FOREIGN KEY (person_id) REFERENCES persons(id)
            );

            CREATE TABLE IF NOT EXISTS groups (
                id TEXT PRIMARY KEY,
                person_id TEXT NOT NULL,
                name TEXT NOT NULL DEFAULT '',
                created_at INTEGER NOT NULL,
                FOREIGN KEY (person_id) REFERENCES persons(id)
            );

            CREATE TABLE IF NOT EXISTS group_members (
                group_id TEXT NOT NULL,
                person_id TEXT NOT NULL,
                PRIMARY KEY (group_id, person_id),
                FOREIGN KEY (group_id) REFERENCES groups(id),
                FOREIGN KEY (person_id) REFERENCES persons(id)
            );

            CREATE TABLE IF NOT EXISTS account_rights (
                account_id TEXT NOT NULL,
                right_name TEXT NOT NULL,
                PRIMARY KEY (account_id, right_name),
                FOREIGN KEY (account_id) REFERENCES accounts(id)
            );

            CREATE TABLE IF NOT EXISTS article_group_access (
                article_id TEXT NOT NULL,
                group_id TEXT NOT NULL,
                PRIMARY KEY (article_id, group_id),
                FOREIGN KEY (article_id) REFERENCES articles(id),
                FOREIGN KEY (group_id) REFERENCES groups(id)
            );

            CREATE INDEX IF NOT EXISTS idx_articles_person ON articles(person_id);
            CREATE INDEX IF NOT EXISTS idx_group_members_group ON group_members(group_id);
            CREATE INDEX IF NOT EXISTS idx_article_group_access_article ON article_group_access(article_id);
            """
        )
        conn.commit()
    finally:
        conn.close()
    ensure_admin()


def create_account(email: str, password: str) -> dict:
    if not email or "@" not in email:
        raise ValueError("Invalid email address")
    if not password:
        raise ValueError("Password required")
    salt = uuid.uuid4().hex
    password_hash = _hash_password(password, salt)
    account_id = uuid.uuid4().hex
    conn = _conn()
    try:
        is_first = conn.execute("SELECT COUNT(*) AS c FROM accounts").fetchone()["c"] == 0
        conn.execute(
            "INSERT INTO accounts (id, email, password_hash, password_salt, created_at) VALUES (?,?,?,?,?)",
            (account_id, email, password_hash, salt, _now()),
        )
        if is_first:
            # The very first account becomes the bootstrap admin.
            _grant_all_rights(conn, account_id)
        conn.commit()
    except sqlite3.IntegrityError as e:
        raise ValueError("An account with this email already exists") from e
    finally:
        conn.close()
    return get_account_by_id(account_id)


def authenticate(email: str, password: str) -> dict | None:
    conn = _conn()
    try:
        row = conn.execute(
            "SELECT id, email, password_hash, password_salt FROM accounts WHERE email = ?",
            (email,),
        ).fetchone()
        if not row:
            return None
        expected = _hash_password(password, row["password_salt"])
        if not hmac.compare_digest(expected, row["password_hash"]):
            return None
        rights = [
            r["right_name"] for r in conn.execute(
                "SELECT right_name FROM account_rights WHERE account_id = ?",
                (row["id"],),
            ).fetchall()
        ]
        person = conn.execute(
            "SELECT person_id FROM person_accounts WHERE account_id = ? ORDER BY person_id LIMIT 1",
            (row["id"],),
        ).fetchone()
        return {
            "id": row["id"],
            "email": row["email"],
            "rights": rights,
            "person_id": person["person_id"] if person else None,
            "is_admin": ADMIN_RIGHT in rights,
        }
    finally:
        conn.close()


def has_right(account: dict | None, right: str) -> bool:
    if account is None:
        return False
    if right in account.get("rights", []):
        return True
    if "id" in account:
        conn = _conn()
        try:
            row = conn.execute(
                "SELECT 1 FROM account_rights WHERE account_id = ? AND right_name = ?",
                (account["id"], right),
            ).fetchone()
            return row is not None
        finally:
            conn.close()
    return False


def create_person(account: dict | None, name: str = "") -> dict:
    """Create a person. Admins only; persons are never auto-linked to the creator."""
    if account is None or not has_right(account, "manage_persons"):
        raise PermissionError("Only admins can create persons")
    if name is None:
        name = ""
    person_id = uuid.uuid4().hex
    conn = _conn()
    try:
        conn.execute(
            "INSERT INTO persons (id, name, created_at) VALUES (?,?,?)",
            (person_id, name, _now()),
        )
        conn.commit()
        return {"id": person_id, "name": name}
    finally:
        conn.close()


def _find_account_by_email(email: str) -> dict | None:
    conn = _conn()
    try:
        row = conn.execute(
            "SELECT id, email FROM accounts WHERE lower(email) = lower(?)", (email,)
        ).fetchone()
        if not row:
            return None
        return {"id": row["id"], "email": row["email"]}
    finally:
        conn.close()


def link_account_to_person(account: dict | None, person_id: str, account_id: str) -> dict | None:
    """Link an account (email) to a person. Admins only.

    The specification allows several accounts per person but only one person per
    account, so an account already linked elsewhere is rejected.
    """
    if account is None or not has_right(account, "manage_persons"):
        raise PermissionError("Only admins can link accounts to persons")
    conn = _conn()
    try:
        linked = conn.execute(
            "SELECT person_id FROM person_accounts WHERE account_id = ?", (account_id,)
        ).fetchall()
        for row in linked:
            if row["person_id"] != person_id:
                raise ValueError("This email is already linked to another person")
        conn.execute(
            "INSERT OR IGNORE INTO person_accounts (person_id, account_id) VALUES (?,?)",
            (person_id, account_id),
        )
        conn.commit()
        return get_person(person_id)
    finally:
        conn.close()


def create_person_with_account(account: dict | None, name: str = "",
                               email: str | None = None, password: str | None = None) -> dict:
    """Admin flow: create a person and optionally give them an email address.

    If the email has no account yet, an account is created for it (a password is
    then required). Otherwise the existing account is linked to the new person.
    """
    if account is None or not has_right(account, "manage_persons"):
        raise PermissionError("Only admins can create persons")
    person = create_person(account, name)
    email = (email or "").strip()
    if not email:
        return get_person(person["id"])
    target = _find_account_by_email(email)
    if target is None:
        if not password:
            raise ValueError("A password is required to create the account for " + email)
        target = create_account(email, password)
    link_account_to_person(account, person["id"], target["id"])
    return get_person(person["id"])


def list_persons(account: dict | None) -> list[dict]:
    show_emails = is_admin(account)
    conn = _conn()
    try:
        rows = conn.execute("SELECT id, name, created_at FROM persons ORDER BY name, id").fetchall()
        out = []
        for r in rows:
            emails = []
            if show_emails:
                emails = [
                    a["email"] for a in conn.execute(
                        "SELECT a.email FROM accounts a JOIN person_accounts pa ON pa.account_id = a.id WHERE pa.person_id = ?",
                        (r["id"],),
                    ).fetchall()
                ]
            out.append({
                "id": r["id"],
                "name": r["name"],
                "emails": sorted(set(emails)),
                "created_at": r["created_at"],
            })
        return out
    finally:
        conn.close()


def get_person(person_id: str) -> dict | None:
    conn = _conn()
    try:
        row = conn.execute("SELECT id, name, created_at FROM persons WHERE id = ?", (person_id,)).fetchone()
        if not row:
            return None
        person_accounts = [
            a["email"] for a in conn.execute(
                "SELECT a.email FROM accounts a JOIN person_accounts pa ON pa.account_id = a.id WHERE pa.person_id = ?",
                (person_id,),
            ).fetchall()
        ]
        groups = [
            {"id": g["id"], "name": g["name"]}
            for g in conn.execute("SELECT id, name FROM groups WHERE person_id = ?", (person_id,)).fetchall()
        ]
        return {
            "id": row["id"],
            "name": row["name"],
            "emails": sorted(set(person_accounts)),
            "groups": groups,
            "created_at": row["created_at"],
        }
    finally:
        conn.close()


def update_person(account: dict | None, person_id: str, name: str | None = None) -> dict | None:
    person = get_person(person_id)
    if person is None:
        return None
    conn = _conn()
    try:
        if name is not None:
            conn.execute("UPDATE persons SET name = ? WHERE id = ?", (name if name is not None else "", person_id))
        conn.commit()
        return get_person(person_id)
    finally:
        conn.close()


def delete_person(account: dict | None, person_id: str) -> bool:
    person = get_person(person_id)
    if person is None:
        return False
    conn = _conn()
    try:
        conn.execute("DELETE FROM articles WHERE person_id = ?", (person_id,))
        conn.execute("DELETE FROM article_group_access WHERE article_id IN (SELECT id FROM articles WHERE person_id = ?)", (person_id,))
        conn.execute("DELETE FROM group_members WHERE group_id IN (SELECT id FROM groups WHERE person_id = ?)", (person_id,))
        conn.execute("DELETE FROM groups WHERE person_id = ?", (person_id,))
        conn.execute("DELETE FROM person_accounts WHERE person_id = ?", (person_id,))
        conn.execute("DELETE FROM persons WHERE id = ?", (person_id,))
        conn.commit()
        return True
    finally:
        conn.close()


def create_article(account: dict, person_id: str, article_type: str, title: str, content: str,
                   visibility: str = "private", group_ids: list[str] | None = None) -> dict:
    if article_type not in ARTICLE_TYPES:
        raise ValueError("Invalid article type")
    if visibility not in ("private", "group", "public"):
        raise ValueError("Invalid visibility")
    if account is None:
        raise PermissionError("Not authenticated")
    if not has_right(account, "manage_articles"):
        if article_type != "notes":
            raise PermissionError("Normal users can only add notes")
        if person_id != account.get("person_id"):
            raise PermissionError("You can only add notes to your own person")
    article_id = uuid.uuid4().hex
    now = _now()
    conn = _conn()
    try:
        conn.execute(
            "INSERT INTO articles (id, person_id, article_type, title, content, visibility, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?)",
            (article_id, person_id, article_type, title, content, visibility, now, now),
        )
        if group_ids:
            for gid in group_ids:
                conn.execute("INSERT INTO article_group_access (article_id, group_id) VALUES (?,?)", (article_id, gid))
        conn.commit()
        return get_article(article_id)
    finally:
        conn.close()


def get_article(article_id: str) -> dict | None:
    conn = _conn()
    try:
        row = conn.execute("SELECT * FROM articles WHERE id = ?", (article_id,)).fetchone()
        if not row:
            return None
        allowed_groups = [
            g["id"] for g in conn.execute(
                "SELECT g.id FROM groups g JOIN article_group_access aga ON aga.group_id = g.id WHERE aga.article_id = ?",
                (article_id,),
            ).fetchall()
        ]
        return {
            "id": row["id"],
            "person_id": row["person_id"],
            "article_type": row["article_type"],
            "title": row["title"],
            "content": row["content"],
            "visibility": row["visibility"],
            "created_at": row["created_at"],
            "updated_at": row["updated_at"],
            "allowed_groups": allowed_groups,
        }
    finally:
        conn.close()


def list_articles(account: dict | None, person_id: str | None = None) -> list[dict]:
    conn = _conn()
    try:
        query = "SELECT a.* FROM articles a"
        params: list = []
        if person_id:
            query += " WHERE a.person_id = ?"
            params.append(person_id)
        query += " ORDER BY a.updated_at DESC, a.created_at DESC"
        rows = conn.execute(query, params).fetchall()
        out = []
        for r in rows:
            article = {
                "id": r["id"],
                "person_id": r["person_id"],
                "article_type": r["article_type"],
                "title": r["title"],
                "visibility": r["visibility"],
                "created_at": r["created_at"],
                "updated_at": r["updated_at"],
            }
            allowed_groups = [
                g["id"] for g in conn.execute(
                    "SELECT g.id FROM groups g JOIN article_group_access aga ON aga.group_id = g.id WHERE aga.article_id = ?",
                    (r["id"],),
                ).fetchall()
            ]
            article["allowed_groups"] = allowed_groups
            if _can_read_article(account, article):
                article["content"] = r["content"]
            else:
                article["content"] = ""
            out.append(article)
        return out
    finally:
        conn.close()


def _can_read_article(account: dict | None, article: dict) -> bool:
    if article["visibility"] == "public":
        return True
    if account is None:
        return False
    # You always see the articles of your own person.
    if article["person_id"] == account.get("person_id"):
        return True
    if article["visibility"] == "group":
        for gid in article.get("allowed_groups", []):
            if _account_in_group(account, gid):
                return True
        return False
    if article["visibility"] == "private":
        return has_right(account, "view_private") and article["person_id"] == account.get("person_id")
    return False


def _account_in_group(account: dict, group_id: str) -> bool:
    conn = _conn()
    try:
        row = conn.execute(
            "SELECT 1 FROM group_members gm JOIN person_accounts pa ON pa.person_id = gm.person_id WHERE gm.group_id = ? AND pa.account_id = ?",
            (group_id, account["id"]),
        ).fetchone()
        return row is not None
    finally:
        conn.close()


def update_article(account: dict, article_id: str, title: str | None = None,
                   content: str | None = None, visibility: str | None = None,
                   group_ids: list[str] | None = None) -> dict | None:
    a = get_article(article_id)
    if a is None:
        return None
    if a["person_id"] != account.get("person_id") and not has_right(account, "manage_articles"):
        raise PermissionError("Not allowed to edit this article")
    conn = _conn()
    try:
        if title is not None:
            conn.execute("UPDATE articles SET title = ? WHERE id = ?", (title, article_id))
        if content is not None:
            conn.execute("UPDATE articles SET content = ? WHERE id = ?", (content, article_id))
        if visibility is not None:
            conn.execute("UPDATE articles SET visibility = ? WHERE id = ?", (visibility, article_id))
            conn.execute("DELETE FROM article_group_access WHERE article_id = ?", (article_id,))
        if group_ids is not None:
            conn.execute("DELETE FROM article_group_access WHERE article_id = ?", (article_id,))
            for gid in group_ids:
                conn.execute("INSERT INTO article_group_access (article_id, group_id) VALUES (?,?)", (article_id, gid))
        conn.execute("UPDATE articles SET updated_at = ? WHERE id = ?", (_now(), article_id))
        conn.commit()
        return get_article(article_id)
    finally:
        conn.close()


def delete_article(account: dict, article_id: str) -> bool:
    a = get_article(article_id)
    if a is None:
        return False
    if a["person_id"] != account.get("person_id") and not has_right(account, "manage_articles"):
        raise PermissionError("Not allowed to delete this article")
    conn = _conn()
    try:
        conn.execute("DELETE FROM article_group_access WHERE article_id = ?", (article_id,))
        conn.execute("DELETE FROM articles WHERE id = ?", (article_id,))
        conn.commit()
        return True
    finally:
        conn.close()


def create_group(account: dict, person_id: str, name: str = "") -> dict:
    if not has_right(account, "manage_groups") and account.get("person_id") != person_id:
        raise PermissionError("Not allowed to create groups for this person")
    group_id = uuid.uuid4().hex
    conn = _conn()
    try:
        conn.execute("INSERT INTO groups (id, person_id, name, created_at) VALUES (?,?,?,?)",
                     (group_id, person_id, name, _now()))
        conn.commit()
        return {"id": group_id, "person_id": person_id, "name": name}
    finally:
        conn.close()


def list_groups(account: dict | None, person_id: str | None = None) -> list[dict]:
    conn = _conn()
    try:
        query = "SELECT id, person_id, name, created_at FROM groups"
        params: list = []
        if person_id:
            query += " WHERE person_id = ?"
            params.append(person_id)
        query += " ORDER BY name, id"
        rows = conn.execute(query, params).fetchall()
        visible = _visible_group_ids(conn, account)
        out = []
        for r in rows:
            if visible is not None and r["id"] not in visible:
                continue
            members = get_group_members(r["id"])
            out.append({
                "id": r["id"],
                "person_id": r["person_id"],
                "name": r["name"],
                "created_at": r["created_at"],
                "members": members,
            })
        return out
    finally:
        conn.close()


def _visible_group_ids(conn, account: dict | None):
    """Group ids the account may see, or None for unrestricted (admins)."""
    if account is None:
        return set()
    if has_right(account, "manage_groups"):
        return None
    person_id = account.get("person_id")
    if not person_id:
        return set()
    ids = {row["id"] for row in conn.execute("SELECT id FROM groups WHERE person_id = ?", (person_id,)).fetchall()}
    ids |= {row["group_id"] for row in conn.execute("SELECT group_id FROM group_members WHERE person_id = ?", (person_id,)).fetchall()}
    return ids


def group_is_visible_to(account: dict | None, group_id: str) -> bool:
    conn = _conn()
    try:
        visible = _visible_group_ids(conn, account)
        return visible is None or group_id in visible
    finally:
        conn.close()


def get_group(group_id: str) -> dict | None:
    conn = _conn()
    try:
        row = conn.execute("SELECT id, person_id, name, created_at FROM groups WHERE id = ?", (group_id,)).fetchone()
        if not row:
            return None
        return {
            "id": row["id"],
            "person_id": row["person_id"],
            "name": row["name"],
            "created_at": row["created_at"],
            "members": get_group_members(group_id),
        }
    finally:
        conn.close()


def get_group_members(group_id: str) -> list[dict]:
    conn = _conn()
    try:
        rows = conn.execute(
            "SELECT p.id, p.name FROM persons p JOIN group_members gm ON gm.person_id = p.id WHERE gm.group_id = ?",
            (group_id,),
        ).fetchall()
        return [{"id": r["id"], "name": r["name"]} for r in rows]
    finally:
        conn.close()


def add_group_member(account: dict, group_id: str, person_id: str) -> dict | None:
    group = get_group(group_id)
    if group is None:
        return None
    if account is None:
        raise PermissionError("Not allowed to manage this group")
    allowed = has_right(account, "manage_groups") or group["person_id"] == account.get("person_id")
    if not allowed and account.get("person_id"):
        conn = _conn()
        try:
            allowed = conn.execute(
                "SELECT 1 FROM group_members WHERE group_id = ? AND person_id = ?",
                (group_id, account["person_id"]),
            ).fetchone() is not None
        finally:
            conn.close()
    if not allowed:
        raise PermissionError("You can only invite people to groups you own or belong to")
    person = get_person(person_id)
    if person is None:
        raise ValueError("Person not found")
    conn = _conn()
    try:
        conn.execute("INSERT OR IGNORE INTO group_members (group_id, person_id) VALUES (?,?)", (group_id, person_id))
        conn.commit()
        return get_group(group_id)
    finally:
        conn.close()


def remove_group_member(account: dict, group_id: str, person_id: str) -> dict | None:
    group = get_group(group_id)
    if group is None:
        return None
    if group["person_id"] != account.get("person_id") and not has_right(account, "manage_groups"):
        raise PermissionError("Not allowed to manage this group")
    conn = _conn()
    try:
        conn.execute("DELETE FROM group_members WHERE group_id = ? AND person_id = ?", (group_id, person_id))
        conn.commit()
        return get_group(group_id)
    finally:
        conn.close()


def update_group(account: dict, group_id: str, name: str | None = None) -> dict | None:
    group = get_group(group_id)
    if group is None:
        return None
    if group["person_id"] != account.get("person_id") and not has_right(account, "manage_groups"):
        raise PermissionError("Not allowed to manage this group")
    conn = _conn()
    try:
        if name is not None:
            conn.execute("UPDATE groups SET name = ? WHERE id = ?", (name, group_id))
        conn.commit()
        return get_group(group_id)
    finally:
        conn.close()


def delete_group(account: dict, group_id: str) -> bool:
    group = get_group(group_id)
    if group is None:
        return False
    if group["person_id"] != account.get("person_id") and not has_right(account, "manage_groups"):
        raise PermissionError("Not allowed to manage this group")
    conn = _conn()
    try:
        conn.execute("DELETE FROM article_group_access WHERE group_id = ?", (group_id,))
        conn.execute("DELETE FROM group_members WHERE group_id = ?", (group_id,))
        conn.execute("DELETE FROM groups WHERE id = ?", (group_id,))
        conn.commit()
        return True
    finally:
        conn.close()


def grant_right(account: dict, target_account_id: str, right_name: str):
    if not has_right(account, "manage_accounts"):
        raise PermissionError("Not allowed to manage account rights")
    if right_name not in RIGHTS:
        raise ValueError("Unknown right")
    conn = _conn()
    try:
        conn.execute("INSERT OR IGNORE INTO account_rights (account_id, right_name) VALUES (?,?)",
                     (target_account_id, right_name))
        conn.commit()
    finally:
        conn.close()


def revoke_right(account: dict, target_account_id: str, right_name: str):
    if not has_right(account, "manage_accounts"):
        raise PermissionError("Not allowed to manage account rights")
    conn = _conn()
    try:
        conn.execute("DELETE FROM account_rights WHERE account_id = ? AND right_name = ?",
                     (target_account_id, right_name))
        conn.commit()
    finally:
        conn.close()


def list_rights(account: dict | None, account_id: str | None = None) -> list[dict]:
    if account is None or not has_right(account, "manage_accounts"):
        raise PermissionError("Only admins can view account rights")
    conn = _conn()
    try:
        query = "SELECT a.id, a.email, ar.right_name FROM accounts a LEFT JOIN account_rights ar ON ar.account_id = a.id"
        params: list = []
        if account_id:
            query += " WHERE a.id = ?"
            params.append(account_id)
        query += " ORDER BY a.email, a.id, ar.right_name"
        rows = conn.execute(query, params).fetchall()
        out: dict = {}
        for r in rows:
            key = r["id"]
            if key not in out:
                out[key] = {"id": r["id"], "email": r["email"], "rights": []}
            if r["right_name"]:
                out[key]["rights"].append(r["right_name"])
        return list(out.values())
    finally:
        conn.close()


def attach_email_to_person(account: dict, person_id: str, email: str) -> dict | None:
    if account is None or not has_right(account, "manage_persons"):
        raise PermissionError("Only admins can change a person's emails")
    target = _find_account_by_email(email)
    if not target:
        raise ValueError("No account exists for " + email)
    return link_account_to_person(account, person_id, target["id"])


def detach_email_from_person(account: dict, person_id: str, email: str) -> dict | None:
    if account is None or not has_right(account, "manage_persons"):
        raise PermissionError("Only admins can change a person's emails")
    conn = _conn()
    try:
        target = conn.execute(
            "SELECT id FROM accounts WHERE lower(email) = lower(?)", (email,)
        ).fetchone()
        if not target:
            raise ValueError("No account exists for " + email)
        conn.execute("DELETE FROM person_accounts WHERE person_id = ? AND account_id = ?",
                     (person_id, target["id"]))
        conn.commit()
        return get_person(person_id)
    finally:
        conn.close()


def get_account_by_id(account_id: str) -> dict | None:
    conn = _conn()
    try:
        row = conn.execute("SELECT id, email FROM accounts WHERE id = ?", (account_id,)).fetchone()
        if not row:
            return None
        rights = [
            r["right_name"] for r in conn.execute(
                "SELECT right_name FROM account_rights WHERE account_id = ?",
                (account_id,),
            ).fetchall()
        ]
        person = conn.execute(
            "SELECT person_id FROM person_accounts WHERE account_id = ? ORDER BY person_id LIMIT 1",
            (account_id,),
        ).fetchone()
        return {
            "id": row["id"],
            "email": row["email"],
            "rights": rights,
            "person_id": person["person_id"] if person else None,
            "is_admin": ADMIN_RIGHT in rights,
        }
    finally:
        conn.close()