"""Create MySQL sqlalchemy db instance."""    # pylint: disable=R0801,R0903
from datetime import datetime

from sqlalchemy import event
from sqlalchemy.ext.declarative import declarative_base

from moneyhub.config import Config
from moneyhub.connectors.database_connector import Database

db = Database(
    Config.DATABASE_URL,
    Config.DATABASE_ENGINE_ARGUMENTS,
    Config.DATABASE_SESSION_ARGUMENTS
)


class ORMClass:
    """SQLAlchemy Object Relational Mapper for user-defined MySQL Python classes."""

    query = db.scoped_session.query_property()


Base = declarative_base(cls=ORMClass)


def find_in_set(value: int | str, lst: str):
    """Find in set function SQLite replacement."""
    if not lst:
        return False
    return str(value) in lst


def year(value: datetime):
    """Year function SQLite replacement."""
    return value.year


# Register the custom SQL function
@event.listens_for(db.engine, 'connect')
def register_sqlite_functions(dbapi_connection, connection_record):
    """Register user defined functions for SQLite compatibility."""
    if db.engine.name == 'sqlite':
        dbapi_connection.create_function('FIND_IN_SET', 2, find_in_set)
        dbapi_connection.create_function('year', 1, year)