from typing import Any
from typing import Iterator

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import Query
from sqlalchemy.orm import Session
from sqlalchemy.orm import sessionmaker

from redbeanpython.bean.bean import Bean
from redbeanpython.bean.bean import Id
from redbeanpython.bean.beans import Beans
from redbeanpython.config import Config
from redbeanpython.const import ID
from redbeanpython.errors import FrozenError
from redbeanpython.errors import IncorrectValueError
from redbeanpython.errors import NotExistsError
from redbeanpython.model.model import Model
from redbeanpython.structure.db_structure import DbStructure


class RedBean:
    def __init__(
        self,
        dsn: str | bool = None,
        *,
        frozen: bool = None,
        directory: str = None,
        namespace: str = None,
    ):
        """
        RedBeanPython works with PostgreSQL, MySQL/MariaDB and SQLite.

        Setup via ENV
        A typical pattern for setting up a RedBeanPython connection is setting the environment variable REDBEAN_DB_DSN.

        F.e. for PostgreSQL:
        > REDBEAN_DB_DSN=postgresql+psycopg://user:pass@host/dbname
        If REDBEAN_DB_DSN is set, it will be used as DSN for database connection just by calling:

        > RedBean()

        Test database

        To connect to an SQLite testing database, use:
        > RedBean(dsn=False)
        It will create SQLite test database redbean.sqlite in the system's temporary directory and connect to it.

        Remember that, in most operating systems, this database will be deleted after the system reboots.

        Real database connection

        PostgreSQL (recommended)
        PostgreSQL is the recommended database for production use. RedBeanPython performs best with it.

        DSN (consistent with SQLAlchemy):
        > RedBean('postgresql+psycopg://user:pass@host/dbname')

        MySQL/MariaDB
        MySQL (because of how it supports TEXT type) performs worse than PostgreSQL
        but is still a good choice for production use.

        DSN (consistent with SQLAlchemy):
        > RedBean('mysql+pymysql://user:pass@host/dbname')

        SQLite
        SQLite is a good choice for system/cli applications but is not recommended for web applications.

        DSN (consistent with SQLAlchemy):
        > RedBean('sqlite:///path/to/database.db')

        Configuration

        As the idea of RedBeanPython is to be as zero-config ORM, by default, no configuration is needed.

        But you can do it if you want to change the default directory/namespace
        and adjust it to your needs and project structure.

        Directory and namespace configuration
        By default, RedBeanPython will create models in the redbean directory in the app's main directory
        and use the corresponding namespace.

        But it can be changed by calling RedBean() with directory and namespace parameters.

        > RedBean(
        >    "postgresql+psycopg://...",
        >    directory='my_module/my_redbean',
        >    namespace='my_module.my_redbean'
        > )

        or by setting REDBEAN_DIRECTORY and REDBEAN_NAMESPACE environment variables.

        >REDBEAN_DIRECTORY=my_module/my_redbean
        >REDBEAN_NAMESPACE=my_module.my_redbean

        In the above example, models will be created in my_module/my_redbean directory
        and will be available as my_module.my_redbean namespace.
        """
        self.config = Config(dsn, frozen, directory, namespace)
        self.engine = create_engine(self.config.dsn)
        self.session_maker = sessionmaker(bind=self.engine)

        self._db_structure = DbStructure(self.engine, self.config)

    def store(self, bean: Bean) -> Id:
        """
        Use redbean.store(bean) to store the bean in the database.

        > user = Bean('user')
        > user.name = "Adam"
        > user.age = 42
        > redbean.store(user)

        By default, id is an autogenerated UUID4 string.

        Optionally id can be overridden.

        > user = Bean('user')
        > user.id = "my_own_id_1234"
        > user.name = "Adam"
        > user.age = 42
        > redbean.store(user)

        If a user with a given id already exists in the database, it will be updated.

        store() operation is idempotent.
        No exception will be thrown if a bean with a given ID already exists in the database.
        """
        if not self._db_structure.is_table_up_to_date(bean):
            if self.config.frozen:
                raise FrozenError("RedBean is frozen. Can not modify models.")
            self._db_structure.update_structure(bean)
        session = self.session_maker()
        try:
            model = self._load(session, bean.bean_type, bean.id)
            for k, v in model.as_dict().items():
                if k != ID:
                    setattr(model, k, None)
            for k, v in dict(bean).items():
                if k != ID:
                    setattr(model, k, v)
            session.flush()
        except (sqlalchemy.orm.exc.NoResultFound, sqlalchemy.exc.NoResultFound):
            session.add(self._db_structure.get_model(bean))
        session.commit()
        session.close()

        return bean.id

    def load(self, bean_type: str, bean_id: Id | str, *, throw_on_empty=False) -> Bean:
        """
        Load/Read

        > user = redbean.load('user', user_id)
        If a bean with a given id does not exist in the database, it will be created by default.

        Optionally, it may be called with throw_on_empty=True
        to throw NotExistsError if a bean with a given id does not exist in the database.

        > from redbeanpython import NotExistsError
        > try:
        >    user = redbean.load('user', user_id, throw_on_empty=True)
        > except NotExistsError:
        >    ...
        """
        session = self.session_maker()

        default_properties = self._db_structure.get_default_properties(bean_type)
        try:
            result = self._load(session, bean_type, bean_id)
            return Bean(bean_type, data=default_properties | result.as_dict())
        except sqlalchemy.exc.NoResultFound:
            if throw_on_empty:
                raise NotExistsError(f"Bean {bean_type} with id {bean_id} does not exist")
            return Bean(bean_type, data=default_properties | {"id": bean_id})
        finally:
            session.close()

    def count(self, bean_type: str, *, query: str = None, params: dict[str, Any] = None) -> int:
        """
        Counting

        Counting can be done by query and parameters with syntax consistent with SQLAlchemy.

        > count: int = redbean.count(
        >     'user',
        >     query="age > :age and active = :active",
        >     params={"age": 21, "active": True}
        > )

        > count: int = redbean.count('user')

        Count can be safely run against not-existing (at that moment) bean types (tables).

        > redbean.count('not_existing_yet_in_database')

        Alternative syntax

        If you do not want to use a text query with parameters, you can use SQLAlchemy. Hybrid mode.

        > with redbean.session_maker() as session:
        >     query = session.query(User)
        >     query = query.filter(User.age > 21)
        >     query = query.filter(User.active == True)
        >     count: int = query.count()
        """
        if not self._db_structure.have_table(bean_type):
            return 0

        session = self.session_maker()
        try:
            q = self._parametrize_query(
                session=session,
                bean_type=bean_type,
                params=params,
                query=query,
            )
            return q.count()
        finally:
            session.close()

    def find(
        self,
        bean_type: str,
        *,
        query: str = None,
        params: dict[str, Any] = None,
        order: str = None,
        limit: int = None,
        offset: int = None,
    ) -> Iterator[Bean]:
        """
        Finding

        The finding can be done by query and parameters with the syntax consistent with SQLAlchemy.

        > result: Iterator[Bean] = redbean.find(
        >     'user',
        >     query="age > :age and active = :active",
        >     params={"age": 21, "active": True},
        >     order="age desc",
        >     limit=10,
        >     offset=20,
        > )

        > result: Iterator[Bean] = redbean.find(
        >     'user',
        >     query="age > :age and active = :active",
        >     params={"age": 21, "active": True},
        > )

        > result: Iterator[Bean] = redbean.find('user')

        Find can be safely run against not existing (at that moment) bean types (tables).

        > print(list(redbean.find('not_existing_yet_in_database')))

        Alternative syntax

        If you do not want to use a text query with parameters, you can use SQLAlchemy.
        (Hybrid mode). The main difference is that the result object is list[Model], not Iterator[Bean].

        > with redbean.session_maker() as session:
        >     query = session.query(User)
        >     query = query.filter(User.age > 21)
        >     query = query.filter(User.active == True)
        >     query = query.order_by(User.age.desc())
        >     query = query.limit(10)
        >     query = query.offset(20)
        >     result: list[User] = query.all()
        >     for user in result:
        >         print(user.as_dict())
        """
        if not self._db_structure.have_table(bean_type):
            return iter([])

        session = self.session_maker()
        try:
            q = self._parametrize_query(
                session=session,
                bean_type=bean_type,
                params=params,
                query=query,
                order=order,
                limit=limit,
                offset=offset,
            )
            yield from [Bean(bean_type, data=model.as_dict()) for model in q.all()]
        finally:
            session.close()

    def exists(self, bean_type: str, *, bean_id: Id | str) -> bool:
        """
        Checking the existence of a bean
        Existence can be checked with the exists method.

        > is_exists: bool = redbean.exists('user', bean_id=user_id)

        It can be safely run against not-existing (at that moment) bean types (tables).
        F.e.:
        > print(redbean.exists('not_existing_yet_in_database', bean_id="some_id"))
        """
        try:
            self.load(bean_type, bean_id=bean_id, throw_on_empty=True)
        except NotExistsError:
            return False
        return True

    def delete(self, *, bean: Bean = None, bean_type: str = None, bean_id: Id | str = None) -> None:
        """
        Delete

        We can delete loaded bean:
        > user = redbean.load('user', user_id)
        > redbean.delete(bean=user)

        or we can delete without loading bean via id and type:

        > redbean.delete(bean_type='user', bean_id=user_id)

        delete() operation is idempotent.
        No exception will be thrown if a bean with the given ID does not exist in the database.
        """
        session = self.session_maker()
        if all([bean, bean_type, bean_id]):
            raise IncorrectValueError("Only one of bean or (bean_type and bean_id) must be provided")
        if not bean and not all([bean_type, bean_id]):
            raise IncorrectValueError("Bean or (bean_type and bean_id) must be provided")
        if bean:
            bean_type = bean.bean_type
            bean_id = bean.id
        try:
            model = self._load(session, bean_type, bean_id)
        except sqlalchemy.orm.exc.NoResultFound:
            session.close()
            return

        session.delete(model)
        session.commit()
        session.close()

    def store_many(self, beans: list[Bean]) -> None:
        """
        Store  many

        > redbean.store_many(users)

        store_many is a syntactic sugar only. It will not store records in bulk.
        It will be enough for most situations,
        but if you need to speed up storing many records at once, use Bulk operations

        Bulk operations

        Bulk operations are designed to decrease the number of requests sent to databases.

        With many records to store or update, you should consider using bulk operations.

        Bulk operations are out of the scope of RedBeanPython by design, as if we have such use cases,
        hybrid mode and SQLAlchemy should be used directly in such places.

        Bulk store

        A bulk store is used to store many beans at once (with single request).

        > from sqlalchemy import insert
        > with redbean.session_maker() as session:
        >     session.execute(
        >          insert(User),
        >          [
        >              {"id": "a", "name": "Adam", "age": 42},
        >              {"id": "i", "name": "Ivona", "age": 42},
        >              {"id": "h", "name": "Hanna", "age": 10},
        >              {"id": "e", "name": "Eva", "age": 7},
        >          ],
        >     )
        >     session.commit()

        Bulk update

        A bulk update is used to update many beans at once (with a single request).

        > from sqlalchemy import update
        > with redbean.session_maker() as session:
        >     session.execute(
        >          update(User),
        >          [
        >              {"id": "a", "name": "Adam", "age": 43},
        >              {"id": "i", "name": "Ivona", "age": 43},
        >              {"id": "h", "name": "Hanna", "age": 11},
        >              {"id": "e", "name": "Eva", "age": 8},
        >          ],
        >     )
        >     session.commit()
        """
        for bean in beans:
            self.store(bean)

    def load_many(self, bean_type: str, beans_ids: list[Id | str], *, throw_on_empty=False) -> Iterator[Bean]:
        """
        Load many

        To load many beans at once, use load_many method of the redbean object

        > users = redbean.load_many('user', [user_id_1, user_id_2, user_id_3])

        > users = redbean.load_many(
        >     'user',
        >     [user_id_1, user_id_2, user_id_3],
        >     throw_on_empty=True,
        > )

        load_many is a syntactic sugar only. It will not load records in bulk.
        It will be enough for most situations,
        but if you need to speed up loading many records at once, use Bulk operations.
        """
        session = self.session_maker()
        try:
            for bean_id in beans_ids:
                yield self.load(bean_type, bean_id, throw_on_empty=throw_on_empty)
        finally:
            session.close()

    @staticmethod
    def dispense(bean_type: str, *, data: dict = None) -> Bean:
        """
        Beans can be created just by passing their type to the Bean class.

        > user = Bean('user')

        And their property can be added just as an object property:
        > user.name = "Adam"
        > user.age = 42

        or via a dictionary-like interface

        > user['name'] = "Adam"
        > user['age'] = 42

        An alternative way to create beans is to use the dispense method on engine object

        > user = redbean.dispense('user')
        > user['name'] = "Adam"
        > user['age'] = 42
        """
        return Bean(bean_type, data=data)

    @staticmethod
    def dispense_many(bean_type: str, *, count: int = None, data: list[dict] = None) -> Iterator[Bean]:
        """
        Create many

        Use Beans() syntactic sugar notation to dispense many beans at once.

        > users = Beans('user', count=10) #

        > users = Beans('user', data=[{"name": "Adam"}, {"name": "Eve"}]) #

        An alternative syntax is to use dispense_many method

        > users = redbean.dispense_many('user', count=10) #

        > users = redbean.dispense_many('user', data=[{"name": "Adam"}, {"name": "Eve"}]) #
        """
        return Beans(bean_type, count=count, data=data)

    def _load(self, session: Session, bean_type: str, id_: str) -> Model:
        if not self._db_structure.have_table(bean_type):
            raise sqlalchemy.orm.exc.NoResultFound()

        return session.query(self._db_structure.get_model_class(bean_type)).filter_by(id=id_).one()

    def _parametrize_query(
        self,
        session: Session,
        bean_type: str,
        params: dict[str, Any] = None,
        query: str = None,
        order: str = None,
        limit: int = None,
        offset: int = None,
    ) -> Query[Model]:
        params = params or {}
        q = session.query(self._db_structure.get_model_class(bean_type))
        if query:
            q = q.filter(sqlalchemy.text(query))
            q = q.params(**params)
        if order:
            q = q.order_by(sqlalchemy.text(order))
        if limit:
            q = q.limit(limit)
        if offset:
            q = q.offset(offset)

        return q
