"""Db bass class for updating the photoblaster database"""
import time
import sys
from photoblaster.db.imcmd import ImCmd
from photoblaster.config import DB_USER, DB_PASSWORD, DB_HOST,\
    DB_NAME
from sqlalchemy import Column, Integer, LargeBinary, String, \
    create_engine, sql, func, desc
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

_NULL = sql.null()

Base = declarative_base()
metadata = Base.metadata


class Db(object):
    """wrapper for all db methods"""
    def __init__(self):
        engine = create_engine('mysql://{}:{}@{}/{}'.format(
            DB_USER,
            DB_PASSWORD,
            DB_HOST,
            DB_NAME
        ))
        self.Session = sessionmaker(bind=engine)

    def _get(
            self,
            columns=None,
            filter_by={},
            order_by=None,
            offset=0,
            limit=20,
            not_deleted=False
            ):
        session = self.Session()
        results = session.query(ImCmd)
        if not_deleted:
            filter_by['deleted'] = 0
        if filter_by:
            results = results.filter_by(filter_by)
        if order_by is not None:
            results = results.order_by(order_by)
        results = results.offset(offset).limit(limit)
        session.close()
        return [result.serialize(columns=columns) for result in results]

    def get_random(
            self, filter_by={},
            offset=0, limit=20, columns=None
            ):
        return self._get(
            columns=columns, order_by=func.random(),
            offset=offset, limit=limit)

    def get_by_date(
            self,
            filter_by={},
            offset=0, limit=20, columns=None
            ):
        return self._get(
            columns=columns, offset=offset, limit=limit,
            order_by=desc(
                ImCmd.date
            )
        )

    def insert_cmd(self, **kwargs):
        try:
            session = self.Session()
            _entry_data = {
                'date': kwargs.get("date", int(time.time())),
                'remote_addr': kwargs['remote_addr'] or _NULL,
                'name': kwargs['username'] or _NULL,
                'url': kwargs['username'] or _NULL,
                'dir': kwargs['directory'] or _NULL,
                'oldfile': kwargs['oldfile'] or _NULL,
                'newfile': kwargs['newfile'] or _NULL,
                'cmd': kwargs['cmd'] or _NULL,
                'dataobj': kwargs['dataobj'] or _NULL,
                'tag': kwargs['tag'] or _NULL
            }
            session.add(ImCmd(**_entry_data))
            session.commit()
            session.close()
        except Exception as e:
            sys.stderr.write("Unable to commit database entry\n")
            sys.stderr.write(str(e))