"""sqlite
"""
from typing import List
import logging
import sqlite3

from pasee.storage_interface import StorageBackend

logger = logging.getLogger(__name__)


class DemoSqliteStorage(StorageBackend):
    """Exposing a simple backend that fetch authorizations from a dictionary."""

    def __init__(self, options: dict, **kwargs) -> None:
        super().__init__(options, **kwargs)
        self.file = options["file"]
        self.connection = None

    async def __aenter__(self):
        self.connection = sqlite3.connect(self.file)
        cursor = self.connection.cursor()

        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS users(
                name TEXT PRIMARY KEY,
                is_banned BOOLEAN DEFAULT FALSE

            );
            """
        )
        cursor.execute("CREATE TABLE IF NOT EXISTS groups(name TEXT PRIMARY KEY);")
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS user_in_group(
                id INTEGER PRIMARY KEY,
                user TEXT,
                group_name TEXT
            );
            """
        )

        cursor.execute(
            """
            CREATE UNIQUE INDEX IF NOT EXISTS group_name_index
            on groups (name);
            """
        )

    async def __aexit__(self, exc_type, exc_value, traceback):
        self.connection.close()

    async def get_authorizations_for_user(self, user: str) -> List[str]:
        """Claim list of groups an user belongs to

        We suppose db schema to be created like this:
        $>CREATE TABLE users(name TEXT PRIMARY KEY);
        $>CREATE TABLE groups(name TEXT PRIMARY KEY);
        $>CREATE TABLE user_in_group (
            id INTEGER PRIMARY KEY,
            user TEXT,
            group_name TEXT
        );"
        """
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()

        results = cursor.execute(
            """
            SELECT groups.name
            FROM groups
            JOIN user_in_group
                ON groups.name = user_in_group.group_name
            WHERE
                user_in_group.user = :user
            ORDER BY name ASC
            """,
            {"user": user},
        )
        return [elem[0] for elem in results]

    async def create_group(self, group_name):
        """Staff member adds group method"""
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()

        cursor.execute(
            "INSERT INTO groups (name) VALUES (:group_name)", {"group_name": group_name}
        )
        self.connection.commit()

    async def get_groups(self, last_element: str = "") -> List[str]:
        """Get groups paginated by group name in alphabetical order
        List of groups is returned by page of 20
        last_element is the last know element returned in previous page
        So passing the last element to this function will retrieve the next page
        """
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        results = cursor.execute(
            """
            SELECT name
            FROM groups
            WHERE name > :last_element
            ORDER BY name ASC
            LIMIT 20
            """,
            {"last_element": last_element},
        )
        groups = [group[0] for group in results]
        cursor.close()
        return groups

    async def get_groups_of_user(self, user: str, last_element: str = "") -> List[str]:
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")

        cursor = self.connection.cursor()
        results = cursor.execute(
            """
            SELECT groups.name
            FROM groups
            JOIN user_in_group
                ON groups.name = user_in_group.group_name
            WHERE
                user_in_group.user = :user
                AND groups.name > :last_element
            ORDER BY groups.name ASC
            LIMIT 20
            """,
            {"user": user, "last_element": last_element},
        )
        groups = [group[0] for group in results]
        cursor.close()
        return groups

    async def delete_group(self, group: str):
        """Delete group"""
        await self.delete_members_in_group(group)
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        cursor.execute("DELETE FROM groups WHERE name = :group", {"group": group})
        self.connection.commit()

    async def get_users(self, last_element: str = ""):
        """Get users"""
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        results = cursor.execute(
            "SELECT * FROM users WHERE name > :name ORDER BY name ASC LIMIT 50",
            {"name": last_element},
        )
        return [elem[0] for elem in results]

    async def get_user(self, username: str = ""):
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")

        cursor = self.connection.cursor()
        result = cursor.execute(
            """
            SELECT name, is_banned
            FROM users
            WHERE name = :username
            """,
            {"username": username},
        ).fetchone()
        if not result:
            return None
        return {"username": result[0], "is_banned": result[1]}

    async def get_members_of_group(self, group: str) -> List[str]:
        """Get members of group"""
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()

        query_result = cursor.execute(
            """
            SELECT user
            FROM user_in_group
            WHERE group_name = :group
        """,
            {"group": group},
        )
        members = [member[0] for member in query_result]
        return members

    async def group_exists(self, group: str) -> bool:
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        result = cursor.execute(
            "SELECT 1 FROM groups WHERE name = :group", {"group": group}
        ).fetchone()

        return bool(result)

    async def create_user(self, username):
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        cursor.execute(
            "INSERT INTO users(name) VALUES(:username)", {"username": username}
        )
        self.connection.commit()

    async def delete_user(self, username):
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        cursor.execute(
            "DELETE FROM user_in_group WHERE user = :username", {"username": username}
        )
        cursor.execute(
            "DELETE FROM users WHERE name = :username", {"username": username}
        )
        self.connection.commit()

    async def user_exists(self, user: str) -> bool:
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        result = cursor.execute(
            "SELECT 1 FROM users WHERE name = :user", {"user": user}
        ).fetchone()
        return bool(result)

    async def is_user_in_group(self, user: str, group: str) -> bool:
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        result = cursor.execute(
            """
                SELECT 1
                FROM user_in_group
                WHERE
                    user = :user
                AND group_name = :group
            """,
            {"user": user, "group": group},
        ).fetchone()
        return bool(result)

    async def add_member_to_group(self, member, group):
        """Staff adds member to group"""
        cursor = self.connection.cursor()
        cursor.execute(
            """
            INSERT INTO user_in_group(
                user, group_name
            ) VALUES (
                :user, :group
            )
            """,
            {"user": member, "group": group},
        )
        self.connection.commit()

    async def delete_member_in_group(self, member, group):
        """Delete member in group"""
        cursor = self.connection.cursor()
        cursor.execute(
            """
            DELETE FROM user_in_group
            WHERE
                user = :user
            AND group_name = :group
            """,
            {"user": member, "group": group},
        )
        self.connection.commit()

    async def delete_members_in_group(self, group):
        """Delete all members of group"""
        if self.connection is None:
            return

        cursor = self.connection.cursor()
        cursor.execute(
            """
            DELETE FROM user_in_group
            WHERE group_name = :group
            """,
            {"group": group},
        )
        self.connection.commit()

    async def ban_user(self, username: str, ban: bool = True):
        """Ban user"""
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")

        cursor = self.connection.cursor()
        cursor.execute(
            "UPDATE users SET is_banned = :ban WHERE name = :username",
            {"username": username, "ban": ban},
        )

"""sqlite
"""
from typing import List
import logging
import sqlite3

from pasee.storage_interface import StorageBackend

logger = logging.getLogger(__name__)


class DemoSqliteStorage(StorageBackend):
    """Exposing a simple backend that fetch authorizations from a dictionary."""

    def __init__(self, options: dict, **kwargs) -> None:
        super().__init__(options, **kwargs)
        self.file = options["file"]
        self.connection = None

    async def __aenter__(self):
        self.connection = sqlite3.connect(self.file)
        cursor = self.connection.cursor()

        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS users(
                name TEXT PRIMARY KEY,
                is_banned BOOLEAN DEFAULT FALSE

            );
            """
        )
        cursor.execute("CREATE TABLE IF NOT EXISTS groups(name TEXT PRIMARY KEY);")
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS user_in_group(
                id INTEGER PRIMARY KEY,
                user TEXT,
                group_name TEXT
            );
            """
        )

        cursor.execute(
            """
            CREATE UNIQUE INDEX IF NOT EXISTS group_name_index
            on groups (name);
            """
        )

    async def __aexit__(self, exc_type, exc_value, traceback):
        self.connection.close()

    async def get_authorizations_for_user(self, user: str) -> List[str]:
        """Claim list of groups an user belongs to

        We suppose db schema to be created like this:
        $>CREATE TABLE users(name TEXT PRIMARY KEY);
        $>CREATE TABLE groups(name TEXT PRIMARY KEY);
        $>CREATE TABLE user_in_group (
            id INTEGER PRIMARY KEY,
            user TEXT,
            group_name TEXT
        );"
        """
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()

        results = cursor.execute(
            """
            SELECT groups.name
            FROM groups
            JOIN user_in_group
                ON groups.name = user_in_group.group_name
            WHERE
                user_in_group.user = :user
            ORDER BY name ASC
            """,
            {"user": user},
        )
        return [elem[0] for elem in results]

    async def create_group(self, group_name):
        """Staff member adds group method"""
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()

        cursor.execute(
            "INSERT INTO groups (name) VALUES (:group_name)", {"group_name": group_name}
        )
        self.connection.commit()

    async def get_groups(self, last_element: str = "") -> List[str]:
        """Get groups paginated by group name in alphabetical order
        List of groups is returned by page of 20
        last_element is the last know element returned in previous page
        So passing the last element to this function will retrieve the next page
        """
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        results = cursor.execute(
            """
            SELECT name
            FROM groups
            WHERE name > :last_element
            ORDER BY name ASC
            LIMIT 20
            """,
            {"last_element": last_element},
        )
        groups = [group[0] for group in results]
        cursor.close()
        return groups

    async def get_groups_of_user(self, user: str, last_element: str = "") -> List[str]:
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")

        cursor = self.connection.cursor()
        results = cursor.execute(
            """
            SELECT groups.name
            FROM groups
            JOIN user_in_group
                ON groups.name = user_in_group.group_name
            WHERE
                user_in_group.user = :user
                AND groups.name > :last_element
            ORDER BY groups.name ASC
            LIMIT 20
            """,
            {"user": user, "last_element": last_element},
        )
        groups = [group[0] for group in results]
        cursor.close()
        return groups

    async def delete_group(self, group: str):
        """Delete group"""
        await self.delete_members_in_group(group)
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        cursor.execute("DELETE FROM groups WHERE name = :group", {"group": group})
        self.connection.commit()

    async def get_users(self, last_element: str = ""):
        """Get users"""
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        results = cursor.execute(
            "SELECT * FROM users WHERE name > :name ORDER BY name ASC LIMIT 50",
            {"name": last_element},
        )
        return [elem[0] for elem in results]

    async def get_user(self, username: str = ""):
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")

        cursor = self.connection.cursor()
        result = cursor.execute(
            """
            SELECT name, is_banned
            FROM users
            WHERE name = :username
            """,
            {"username": username},
        ).fetchone()
        if not result:
            return None
        return {"username": result[0], "is_banned": result[1]}

    async def get_members_of_group(self, group: str) -> List[str]:
        """Get members of group"""
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()

        query_result = cursor.execute(
            """
            SELECT user
            FROM user_in_group
            WHERE group_name = :group
        """,
            {"group": group},
        )
        members = [member[0] for member in query_result]
        return members

    async def group_exists(self, group: str) -> bool:
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        result = cursor.execute(
            "SELECT 1 FROM groups WHERE name = :group", {"group": group}
        ).fetchone()

        return bool(result)

    async def create_user(self, username):
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        cursor.execute(
            "INSERT INTO users(name) VALUES(:username)", {"username": username}
        )
        self.connection.commit()

    async def delete_user(self, username):
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        cursor.execute(
            "DELETE FROM user_in_group WHERE user = :username", {"username": username}
        )
        cursor.execute(
            "DELETE FROM users WHERE name = :username", {"username": username}
        )
        self.connection.commit()

    async def user_exists(self, user: str) -> bool:
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        result = cursor.execute(
            "SELECT 1 FROM users WHERE name = :user", {"user": user}
        ).fetchone()
        return bool(result)

    async def is_user_in_group(self, user: str, group: str) -> bool:
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")
        cursor = self.connection.cursor()
        result = cursor.execute(
            """
                SELECT 1
                FROM user_in_group
                WHERE
                    user = :user
                AND group_name = :group
            """,
            {"user": user, "group": group},
        ).fetchone()
        return bool(result)

    async def add_member_to_group(self, member, group):
        """Staff adds member to group"""
        cursor = self.connection.cursor()
        cursor.execute(
            """
            INSERT INTO user_in_group(
                user, group_name
            ) VALUES (
                :user, :group
            )
            """,
            {"user": member, "group": group},
        )
        self.connection.commit()

    async def delete_member_in_group(self, member, group):
        """Delete member in group"""
        cursor = self.connection.cursor()
        cursor.execute(
            """
            DELETE FROM user_in_group
            WHERE
                user = :user
            AND group_name = :group
            """,
            {"user": member, "group": group},
        )
        self.connection.commit()

    async def delete_members_in_group(self, group):
        """Delete all members of group"""
        if self.connection is None:
            return

        cursor = self.connection.cursor()
        cursor.execute(
            """
            DELETE FROM user_in_group
            WHERE group_name = :group
            """,
            {"group": group},
        )
        self.connection.commit()

    async def ban_user(self, username: str, ban: bool = True):
        """Ban user"""
        if self.connection is None:
            raise RuntimeError("This class should be used in a context manager.")

        cursor = self.connection.cursor()
        cursor.execute(
            "UPDATE users SET is_banned = :ban WHERE name = :username",
            {"username": username, "ban": ban},
        )

sqlite Exposing a simple backend that fetch authorizations from a dictionary. CREATE TABLE IF NOT EXISTS users(                 name TEXT PRIMARY KEY,                 is_banned BOOLEAN DEFAULT FALSE              ); CREATE TABLE IF NOT EXISTS user_in_group(                 id INTEGER PRIMARY KEY,                 user TEXT,                 group_name TEXT             ); CREATE UNIQUE INDEX IF NOT EXISTS group_name_index             on groups (name); Claim list of groups an user belongs to          We suppose db schema to be created like this:         $>CREATE TABLE users(name TEXT PRIMARY KEY);         $>CREATE TABLE groups(name TEXT PRIMARY KEY);         $>CREATE TABLE user_in_group (             id INTEGER PRIMARY KEY,             user TEXT,             group_name TEXT         );" SELECT groups.name             FROM groups             JOIN user_in_group                 ON groups.name = user_in_group.group_name             WHERE                 user_in_group.user = :user             ORDER BY name ASC Staff member adds group method Get groups paginated by group name in alphabetical order         List of groups is returned by page of 20         last_element is the last know element returned in previous page         So passing the last element to this function will retrieve the next page SELECT name             FROM groups             WHERE name > :last_element             ORDER BY name ASC             LIMIT 20 SELECT groups.name             FROM groups             JOIN user_in_group                 ON groups.name = user_in_group.group_name             WHERE                 user_in_group.user = :user                 AND groups.name > :last_element             ORDER BY groups.name ASC             LIMIT 20 Delete group Get users SELECT name, is_banned             FROM users             WHERE name = :username Get members of group SELECT user             FROM user_in_group             WHERE group_name = :group SELECT 1                 FROM user_in_group                 WHERE                     user = :user                 AND group_name = :group Staff adds member to group INSERT INTO user_in_group(                 user, group_name             ) VALUES (                 :user, :group             ) Delete member in group DELETE FROM user_in_group             WHERE                 user = :user             AND group_name = :group Delete all members of group DELETE FROM user_in_group             WHERE group_name = :group Ban user