"""bw_store_sqlalchemy.py — a ready SQLAlchemy store for the BW-Auth accounts kit.

`bw_accounts.py` is storage-pluggable: it owns the permission model + guards +
reporting; a "store" owns only WHERE the rows live. Every SQLAlchemy app was
re-writing the same ~16 methods and the same four tables (levels / members /
instances / grants). This module ships that once, two ways:

  * MANAGED (zero config, the scaffold default) — the store defines its OWN four
    tables (prefixed `bw_`) on your metadata and `ensure_schema()` creates them:

        import bw_accounts, bw_store_sqlalchemy as bwstore
        store = bwstore.managed(Base.metadata, SessionLocal)   # Session factory
        bw_accounts.init(store=store, owner="rian", has_instances=True)

  * MAPPED (bring your own tables) — point the four concepts at EXISTING tables
    and columns (e.g. members→your accounts table, instances→your projects table),
    with per-concept knobs for the semantics real apps need. See `mapped()`.

Framework-agnostic (SQLAlchemy Core only; no Flask/FastAPI). Works on SQLite /
Postgres / MySQL. The `session_factory` is a callable returning a Session; each
method opens a short-lived session (the kit calls at arbitrary points, so a
method must not depend on a request-scoped session being open).

Contract (mirrors bw_accounts' store docstring): usernames arrive normalized;
no method performs authorization (the kit already did); `get_member` returns
None for a member the app considers inactive (deactivation → `can()` false next
request) — set `active_col` and it filters for you.
"""
from sqlalchemy import (JSON, Boolean, Column, Integer, MetaData, String, Table,
                        delete, func, insert, select, update)


class _Spec:
    __slots__ = ()


class LevelsSpec(_Spec):
    """Where levels live. `table` is a SQLAlchemy Table; the *_col names map the
    kit's fields onto its columns. permissions/assignable columns must be JSON
    (store a list)."""
    __slots__ = ("table", "name", "permissions", "assignable")

    def __init__(self, table, name="name", permissions="permissions",
                 assignable="assignable"):
        self.table, self.name = table, name
        self.permissions, self.assignable = permissions, assignable


class MembersSpec(_Spec):
    """Where members live (often an EXISTING accounts table).
      active_col:  if set, get_member/list filter WHERE <active> is true, so a
                   deactivated account reads as not-a-member (deactivation is
                   instant through can()).
      insert:      True (managed) = INSERT a new member row; False (mapped onto an
                   accounts table you already populate at sign-in) = UPDATE the
                   existing row and RAISE if it's absent.
      soft_delete: True = remove_member sets active_col False instead of DELETE
                   (keeps history/attribution); requires active_col.
    """
    __slots__ = ("table", "username", "level", "all_instances", "active_col",
                 "added_by", "insert", "soft_delete")

    def __init__(self, table, username="username", level="level",
                 all_instances="all_instances", active_col=None, added_by="added_by",
                 insert=True, soft_delete=False):
        self.table, self.username, self.level = table, username, level
        self.all_instances, self.active_col = all_instances, active_col
        self.added_by, self.insert, self.soft_delete = added_by, insert, soft_delete


class InstancesSpec(_Spec):
    """Where instances live (often an EXISTING projects/workspaces table).
      id_is_int:  the id column is an integer (the kit uses string ids); reads
                  str() it, writes coerce — a non-numeric id is simply one no row
                  has, so has_instance() safely returns False.
      readonly:   True = add_instance() raises (instances are created by the app's
                  own flow, not the accounts kit).
    """
    __slots__ = ("table", "id", "label", "created_by", "id_is_int", "readonly")

    def __init__(self, table, id="id", label="label", created_by="created_by",
                 id_is_int=False, readonly=False):
        self.table, self.id, self.label = table, id, label
        self.created_by, self.id_is_int, self.readonly = created_by, id_is_int, readonly


class GrantsSpec(_Spec):
    """Where per-instance grants live (often an EXISTING membership table)."""
    __slots__ = ("table", "username", "instance_id", "level", "added_by")

    def __init__(self, table, username="username", instance_id="instance_id",
                 level="level", added_by="added_by"):
        self.table, self.username, self.instance_id = table, username, instance_id
        self.level, self.added_by = level, added_by


class SqlAlchemyStore:
    """The kit store contract implemented over four (Spec) tables via Core."""

    def __init__(self, session_factory, levels, members, instances, grants,
                 owns_tables=False):
        self._sf = session_factory
        self.L, self.M, self.I, self.G = levels, members, instances, grants
        self._owns = owns_tables   # managed mode created the tables -> ensure_schema

    # -- schema (managed mode only) --
    def ensure_schema(self):
        if not self._owns:
            return                 # mapped mode: the app owns migrations
        with self._sf() as db:
            bind = db.get_bind()
            for spec in (self.L, self.M, self.I, self.G):
                spec.table.create(bind=bind, checkfirst=True)

    def _iid(self, spec, instance_id):
        if spec.id_is_int:
            try:
                return int(instance_id)
            except (TypeError, ValueError):
                return -1
        return instance_id

    # -- levels --
    def get_level(self, name):
        L = self.L
        with self._sf() as db:
            row = db.execute(select(L.table.c[L.permissions], L.table.c[L.assignable])
                             .where(L.table.c[L.name] == (name or "").strip())).first()
        if not row:
            return None
        return {"name": (name or "").strip(),
                "permissions": list(row[0] or []), "assignable": list(row[1] or [])}

    def list_levels(self):
        L = self.L
        with self._sf() as db:
            rows = db.execute(select(L.table.c[L.name], L.table.c[L.permissions],
                                     L.table.c[L.assignable])
                              .order_by(L.table.c[L.name])).all()
        return [{"name": r[0], "permissions": list(r[1] or []),
                 "assignable": list(r[2] or [])} for r in rows]

    def add_level(self, name, permissions, assignable):
        L = self.L
        with self._sf() as db:
            db.execute(insert(L.table).values({L.name: name,
                                               L.permissions: list(permissions),
                                               L.assignable: list(assignable)}))
            db.commit()

    def update_level(self, name, permissions, assignable):
        L = self.L
        with self._sf() as db:
            db.execute(update(L.table).where(L.table.c[L.name] == name)
                       .values({L.permissions: list(permissions),
                                L.assignable: list(assignable)}))
            db.commit()

    def delete_level(self, name):
        L = self.L
        with self._sf() as db:
            db.execute(delete(L.table).where(L.table.c[L.name] == name))
            db.commit()

    def count_level_usage(self, name):
        M, G = self.M, self.G
        with self._sf() as db:
            n = db.scalar(select(func.count()).select_from(M.table)
                          .where(M.table.c[M.level] == name)) or 0
            n += db.scalar(select(func.count()).select_from(G.table)
                           .where(G.table.c[G.level] == name)) or 0
        return int(n)

    # -- members --
    def _grants_for(self, db, username):
        G = self.G
        rows = db.execute(select(G.table.c[G.instance_id], G.table.c[G.level])
                          .where(G.table.c[G.username] == username)).all()
        return {str(iid): lvl for iid, lvl in rows}

    def _member_active(self, db, username):
        """The member row (Row) if present AND active (per active_col), else None."""
        M = self.M
        cols = [M.table.c[M.username], M.table.c[M.level], M.table.c[M.all_instances]]
        if M.active_col:
            cols.append(M.table.c[M.active_col])
        row = db.execute(select(*cols).where(M.table.c[M.username] == username)).first()
        if not row:
            return None
        if M.active_col and not row[3]:
            return None                        # deactivated == not a member
        return row

    def get_member(self, username):
        with self._sf() as db:
            row = self._member_active(db, username)
            if not row:
                return None
            grants = self._grants_for(db, username)
        return {"username": row[0], "level": row[1],
                "all_instances": bool(row[2]), "grants": grants}

    def list_members(self):
        M, G = self.M, self.G
        with self._sf() as db:
            q = select(M.table.c[M.username], M.table.c[M.level],
                       M.table.c[M.all_instances])
            if M.active_col:
                q = q.where(M.table.c[M.active_col].is_(True))
            rows = db.execute(q.order_by(M.table.c[M.username])).all()
            grants = {}
            for un, iid, lvl in db.execute(select(
                    G.table.c[G.username], G.table.c[G.instance_id],
                    G.table.c[G.level])).all():
                grants.setdefault(un, {})[str(iid)] = lvl
        return [{"username": r[0], "level": r[1], "all_instances": bool(r[2]),
                 "grants": grants.get(r[0], {})} for r in rows]

    def add_member(self, username, level, all_instances, added_by):
        M = self.M
        with self._sf() as db:
            exists = db.execute(select(M.table.c[M.username])
                                .where(M.table.c[M.username] == username)).first()
            vals = {M.level: level, M.all_instances: bool(all_instances)}
            if M.active_col:
                vals[M.active_col] = True      # (re)adding activates
            if exists:
                db.execute(update(M.table).where(M.table.c[M.username] == username)
                           .values(vals))
            elif M.insert:
                ins = {M.username: username, **vals}
                if M.added_by in M.table.c:
                    ins[M.added_by] = added_by
                db.execute(insert(M.table).values(ins))
            else:
                raise RuntimeError(
                    f"no account row for {username!r}; this app provisions accounts "
                    "elsewhere (members insert=False) — create it first")
            db.commit()

    def remove_member(self, username):
        M, G = self.M, self.G
        with self._sf() as db:
            db.execute(delete(G.table).where(G.table.c[G.username] == username))
            if M.soft_delete and M.active_col:
                db.execute(update(M.table).where(M.table.c[M.username] == username)
                           .values({M.active_col: False}))
            else:
                db.execute(delete(M.table).where(M.table.c[M.username] == username))
            db.commit()

    def set_member_level(self, username, level):
        M = self.M
        with self._sf() as db:
            db.execute(update(M.table).where(M.table.c[M.username] == username)
                       .values({M.level: level}))
            db.commit()

    def set_all_instances(self, username, value):
        M = self.M
        with self._sf() as db:
            db.execute(update(M.table).where(M.table.c[M.username] == username)
                       .values({M.all_instances: bool(value)}))
            db.commit()

    # -- instances --
    def list_instances(self):
        I = self.I
        with self._sf() as db:
            rows = db.execute(select(I.table.c[I.id], I.table.c[I.label])
                              .order_by(I.table.c[I.label])).all()
        return [{"id": str(r[0]), "label": r[1]} for r in rows]

    def has_instance(self, instance_id):
        I = self.I
        with self._sf() as db:
            return db.execute(select(I.table.c[I.id])
                              .where(I.table.c[I.id] == self._iid(I, instance_id))
                              ).first() is not None

    def add_instance(self, instance_id, label, created_by):
        I = self.I
        if I.readonly:
            raise RuntimeError(
                "instances are created by this app's own flow, not the accounts kit "
                "(InstancesSpec readonly=True)")
        with self._sf() as db:
            vals = {I.id: self._iid(I, instance_id), I.label: label}
            if I.created_by in I.table.c:
                vals[I.created_by] = created_by
            db.execute(insert(I.table).values(vals))
            db.commit()

    def set_grant(self, username, instance_id, level):
        G, I = self.G, self.I
        iid = self._iid(I, instance_id)
        with self._sf() as db:
            exists = db.execute(select(G.table.c[G.username]).where(
                (G.table.c[G.username] == username)
                & (G.table.c[G.instance_id] == iid))).first()
            if exists:
                db.execute(update(G.table).where(
                    (G.table.c[G.username] == username)
                    & (G.table.c[G.instance_id] == iid)).values({G.level: level}))
            else:
                vals = {G.username: username, G.instance_id: iid, G.level: level}
                if G.added_by in G.table.c:
                    vals[G.added_by] = username
                db.execute(insert(G.table).values(vals))
            db.commit()

    def remove_grant(self, username, instance_id):
        G, I = self.G, self.I
        iid = self._iid(I, instance_id)
        with self._sf() as db:
            db.execute(delete(G.table).where(
                (G.table.c[G.username] == username)
                & (G.table.c[G.instance_id] == iid)))
            db.commit()


# --------------------------------------------------------------- factories -----

def managed(metadata, session_factory, prefix="bw_"):
    """MANAGED mode: define the kit's own four tables on `metadata` (prefixed) and
    return a store. `ensure_schema()` (called by bw_accounts.init) creates them.
    Pass your declarative Base's `.metadata` so they live in your migrations too,
    or a bare `MetaData()` for a standalone create_all. Zero column mapping — this
    is what the scaffolder wires for a new app."""
    md = metadata if isinstance(metadata, MetaData) else metadata  # Base.metadata is a MetaData
    lv = Table(prefix + "levels", md,
               Column("name", String(64), primary_key=True),
               Column("permissions", JSON, nullable=False),
               Column("assignable", JSON, nullable=False),
               extend_existing=True)
    mem = Table(prefix + "members", md,
                Column("username", String(64), primary_key=True),
                Column("level", String(64), nullable=False),
                Column("all_instances", Boolean, default=False),
                Column("added_by", String(64)),
                extend_existing=True)
    inst = Table(prefix + "instances", md,
                 Column("id", String(64), primary_key=True),
                 Column("label", String(200), nullable=False),
                 Column("created_by", String(64)),
                 extend_existing=True)
    gr = Table(prefix + "grants", md,
               Column("username", String(64), primary_key=True),
               Column("instance_id", String(64), primary_key=True),
               Column("level", String(64), nullable=False),
               extend_existing=True)
    return SqlAlchemyStore(session_factory,
                           LevelsSpec(lv), MembersSpec(mem), InstancesSpec(inst),
                           GrantsSpec(gr), owns_tables=True)


def mapped(session_factory, *, levels, members, instances, grants):
    """MAPPED mode: pass a LevelsSpec / MembersSpec / InstancesSpec / GrantsSpec
    pointing at your EXISTING tables + columns. No schema is created (you own the
    migration). This is how an app that already has accounts/projects tables keeps
    its schema and still gets the kit's model."""
    return SqlAlchemyStore(session_factory, levels, members, instances, grants,
                           owns_tables=False)


# --------------------------------------------------------------- self-check ----
# Run inside an env with SQLAlchemy: `python3 bw_store_sqlalchemy.py`. Exercises
# BOTH modes end-to-end via bw_accounts (imported next to this file).
if __name__ == "__main__":
    import os
    import sys
    sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
    import bw_accounts as bwa
    from sqlalchemy import Table as _T, Column as _C, create_engine
    from sqlalchemy.orm import sessionmaker

    ok = lambda n, c: (print(("  ok  " if c else "  FAIL ") + n),
                       (None if c else os._exit(1)))

    def run(label, store):
        bwa.init(store=store, owner="rian", has_instances=True)
        bwa.create_level("rian", "member", [], [])
        bwa.create_level("rian", "admin",
                         [bwa.PERM_ACCOUNTS_ADD, bwa.PERM_INSTANCES_GRANT,
                          bwa.PERM_INSTANCES_CREATE], ["member"])
        bwa.add_member("rian", "ann", "admin")
        bwa.add_member("rian", "bo", "member")
        if not any(i["id"] == "1" for i in bwa.instances()):
            bwa.create_instance("rian", "1", "Project One")   # managed makes it; mapped pre-seeds it
        bwa.grant_instance("ann", "bo", "1", "member")
        ok(f"[{label}] level round-trips", bwa.level_def("admin")["assignable"] == ["member"])
        ok(f"[{label}] member + per-instance grant",
           bwa.effective_level("bo", "1") == "member" and bwa.effective_level("bo", "2") is None)
        ok(f"[{label}] permission model enforced",
           bwa.can("ann", bwa.PERM_ACCOUNTS_ADD) and not bwa.can("bo", bwa.PERM_ACCOUNTS_ADD))
        ok(f"[{label}] admin can't mint admin",
           "admin" not in bwa.assignable_by("ann"))
        mx = bwa.access_matrix()
        ok(f"[{label}] access_matrix", any(r["username"] == "bo" for r in mx["rows"]))
        bwa.set_member_level("rian", "bo", "admin")
        ok(f"[{label}] set level", bwa.member("bo")["level"] == "admin")
        bwa.remove_member("rian", "bo")
        ok(f"[{label}] remove member", bwa.member("bo") is None)

    # MANAGED — the adapter's own tables on a fresh sqlite
    from sqlalchemy.orm import declarative_base
    Base = declarative_base()
    eng = create_engine("sqlite://")     # in-memory, single connection pool below
    from sqlalchemy import StaticPool
    eng = create_engine("sqlite://", connect_args={"check_same_thread": False},
                        poolclass=StaticPool)
    SL = sessionmaker(eng)
    run("managed", managed(Base.metadata, SL))

    # MAPPED — onto an existing app's schema: a members table with an `active` flag +
    # soft-delete, a domain table with INT ids that instances map onto (readonly),
    # a membership table for grants.
    md = MetaData()
    accounts = _T("app_accounts", md,
                  _C("username", String, primary_key=True), _C("level", String),
                  _C("all_instances", Boolean, default=False),
                  _C("active", Boolean, default=True))
    projects = _T("projects", md, _C("id", Integer, primary_key=True), _C("name", String))
    pmembers = _T("project_members", md,
                  _C("project_id", Integer, primary_key=True),
                  _C("username", String, primary_key=True), _C("level", String))
    lvls = _T("app_levels", md, _C("name", String, primary_key=True),
              _C("permissions", JSON), _C("assignable", JSON))
    eng2 = create_engine("sqlite://", connect_args={"check_same_thread": False},
                         poolclass=StaticPool)
    md.create_all(eng2)
    SL2 = sessionmaker(eng2)
    # the app provisions accounts itself (insert=False): the rows exist but are NOT
    # yet members (active=False) until add_member activates + levels them — mirroring
    # "a known BW account that hasn't been granted access to this app yet".
    with SL2() as db:
        for u in ("ann", "bo"):
            db.execute(insert(accounts).values(username=u, active=False))
        db.execute(insert(projects).values(id=1, name="Project One"))
        db.commit()
    existing_app = mapped(
        SL2,
        levels=LevelsSpec(lvls),
        members=MembersSpec(accounts, active_col="active", insert=False, soft_delete=True),
        instances=InstancesSpec(projects, id="id", label="name", id_is_int=True, readonly=True),
        grants=GrantsSpec(pmembers, instance_id="project_id"))
    run("mapped", existing_app)
    # mapped-specific: soft-delete deactivated rather than deleting the row
    with SL2() as db:
        still = db.execute(select(accounts.c.active).where(accounts.c.username == "bo")).scalar()
    ok("[mapped] remove_member soft-deleted (row kept, active=False)", still is False)
    # mapped-specific: creating an instance is refused (projects made by the app)
    try:
        bwa.create_instance("rian", "9", "Nope"); ok("[mapped] readonly instances refuse create", False)
    except Exception as e:
        ok("[mapped] readonly instances refuse create", "own flow" in str(e))
    print("ALL bw_store_sqlalchemy self-checks PASSED")