import sys
import logging
from sqlalchemy import create_engine, Column, Integer, String, Boolean
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.exc import IntegrityError
from sqlalchemy_utils import PasswordType, PhoneNumberType

# logger configurations should be done while application is starting up
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)
output = logging.StreamHandler(sys.stdout)
output.setLevel(logging.DEBUG)
logger.addHandler(output)

# comes from application config
DEBUG = True
MYSQL_URI = 'mysql://user:password@localhost:3306/span'
MYSQL_ENGINE_CONFIG = {
    'echo': DEBUG,
    'echo_pool': DEBUG,
    'pool_size': 100,
    'encoding': 'utf-8',
    'pool_timeout': 20,
    'pool_recycle': 3200
}

# create connection engine
engine = create_engine(MYSQL_URI, **MYSQL_ENGINE_CONFIG)


Session = sessionmaker(bind=engine)

Base = declarative_base()


# Describe models
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(255), unique=True, nullable=False)
    password = Column(
        PasswordType(
            schemes=('pbkdf2_sha512', )  # hash algo can be changed, passlib is required to be installed
        ),
        nullable=False
    )
    telephone = Column(PhoneNumberType(), nullable=False)
    is_active = Column(Boolean(), default=True, nullable=False)


# create model's tables
Base.metadata.create_all(bind=engine)

# -------------------

session = Session()

password = '1Sf123a#sqa'

user = User(username='Dmytro', password=password, telephone='+380999245702')
try:
    session.add(user)
    session.flush()
    session.commit()
except IntegrityError as ex:
    session.rollback()
    logger.error('User already exists (username=%s). Continue...', user.username)

# filtering
user = session.query(User).filter(User.username == 'Dmytro').first()
if user:
    logger.info('Username: %s', user.username)
    logger.info('Is password correct: %s', user.password == password)

session.close()


# Heh.. it's more then 15 LOC, but it's fully operational