账号已经停用，为什么邮箱还是不能重新注册？

"""Unique among active rows, while historical rows keep immutable IDs."""
from contextlib import closing
import sqlite3


def main():
    checks = 0

    def check(condition, message):
        nonlocal checks
        if not condition:
            raise AssertionError(message)
        checks += 1

    with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
        db.execute("CREATE TABLE ordinary(id INTEGER PRIMARY KEY, tenant TEXT NOT NULL, email TEXT NOT NULL, deleted_at TEXT, UNIQUE(tenant,email))")
        db.execute("INSERT INTO ordinary VALUES(1, 'north', 'reader@example.test', '2026-10-01')")
        try:
            db.execute("INSERT INTO ordinary VALUES(2, 'north', 'reader@example.test', NULL)")
        except sqlite3.IntegrityError:
            ordinary_rejected = True
        else:
            ordinary_rejected = False
        check(ordinary_rejected, "full unique index includes historical rows")

        db.execute("CREATE TABLE accounts(id INTEGER PRIMARY KEY, tenant TEXT NOT NULL, email TEXT NOT NULL, deleted_at TEXT)")
        db.execute("CREATE UNIQUE INDEX one_active_email ON accounts(tenant,email) WHERE deleted_at IS NULL")
        db.execute("INSERT INTO accounts VALUES(1, 'north', 'reader@example.test', '2026-10-01')")
        db.execute("INSERT INTO accounts VALUES(2, 'north', 'reader@example.test', NULL)")
        db.execute("INSERT INTO accounts VALUES(3, 'south', 'reader@example.test', NULL)")
        db.execute("INSERT INTO accounts VALUES(4, 'north', 'reader@example.test', '2026-09-01')")
        check(db.execute("SELECT count(*) FROM accounts").fetchone()[0] == 4, "history and separate tenant accepted")

        def rejected(sql, parameters=()):
            try:
                db.execute(sql, parameters)
            except sqlite3.IntegrityError:
                return True
            return False

        check(rejected("INSERT INTO accounts VALUES(5, 'north', 'reader@example.test', NULL)"), "second active row rejected")
        check(rejected("UPDATE accounts SET deleted_at=NULL WHERE id=1"), "restoring old ID conflicts with active ID 2")
        check(db.execute("SELECT deleted_at FROM accounts WHERE id=1").fetchone()[0] == "2026-10-01", "failed restore must leave old row inactive")
        check(rejected("INSERT INTO accounts VALUES(6, 'north', NULL, NULL)"), "required email is a separate constraint")

        # The two state changes belong to one explicit business transaction.
        db.execute("BEGIN")
        try:
            db.execute("UPDATE accounts SET deleted_at='2026-10-17' WHERE id=2")
            db.execute("UPDATE accounts SET deleted_at=NULL WHERE id=1")
            db.execute("COMMIT")
        except Exception:
            db.execute("ROLLBACK")
            raise
        active = db.execute("SELECT id,tenant FROM accounts WHERE deleted_at IS NULL ORDER BY id").fetchall()
        check(active == [(1, "north"), (3, "south")], "explicit handover keeps one active per tenant")
        check(db.execute("SELECT count(*) FROM accounts").fetchone()[0] == 4, "history has not been deleted")
        print("ordinary_unique_reuse=rejected")
        print("partial_unique=two_historical_plus_one_active_in_north; one_active_in_south")
        print("second_active=rejected; direct_restore=rejected; null_email=rejected")
        print(f"after_explicit_handover={active}; total_rows=4")
    print(f"PASS checks={checks}")


if __name__ == "__main__":
    main()