"""Create snowflake sqlalchemy db instance."""    # pylint: disable=R0801,R0903

from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives import serialization
from sqlalchemy.ext.declarative import declarative_base

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


engine_arguments = Config.DATABASE_ENGINE_ARGUMENTS.copy()

if Config.ENVIRONMENT != Config.TEST_ENVIRONMENT:
    engine_arguments['pool_pre_ping'] = False
    engine_arguments['pool_recycle'] = 28800  # throw away sessions after 8 hours
    # keeps sessions alive in snowflake, avoiding to reconnect and reauthenticate for each query
    engine_arguments['connect_args']['client_session_keep_alive'] = True
    engine_arguments['connect_args']['autocommit'] = True  # avoids implicit transactions
    engine_arguments['pool_reset_on_return'] = None  # avoids rollbacks

SNOWFLAKE_PRIVATE_KEY = Config.SNOWFLAKE_PRIVATE_KEY
SNOWFLAKE_PRIVATE_KEY_PATH = Config.SNOWFLAKE_PRIVATE_KEY_PATH

if (SNOWFLAKE_PRIVATE_KEY or SNOWFLAKE_PRIVATE_KEY_PATH)\
        and Config.ENVIRONMENT != Config.TEST_ENVIRONMENT:
    PASSPHRASE = (
        bytes(Config.SNOWFLAKE_KEY_PASSPHRASE, 'utf8')
        if Config.SNOWFLAKE_KEY_PASSPHRASE
        else b''
    )

    if SNOWFLAKE_PRIVATE_KEY:
        private_key_bytes = bytes(SNOWFLAKE_PRIVATE_KEY, 'utf8')
    elif SNOWFLAKE_PRIVATE_KEY_PATH:
        with open(SNOWFLAKE_PRIVATE_KEY_PATH, 'rb') as key:
            private_key_bytes = key.read()

    p_key = serialization.load_pem_private_key(
        private_key_bytes,
        password=PASSPHRASE,
        backend=default_backend(),
    )

    pkb = p_key.private_bytes(
        encoding=serialization.Encoding.DER,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption(),
    )

    engine_arguments['connect_args']['private_key'] = pkb

db = Database(
    Config.SNOWFLAKE_URL,
    engine_arguments,
    Config.DATABASE_SESSION_ARGUMENTS
)


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

    query = db.scoped_session.query_property()


Base = declarative_base(cls=ORMClass)