import datetime
from sqlalchemy import (Column, desc, String, SmallInteger, DateTime)
from sqlalchemy.dialects.mysql.base import (_StringType, TIMESTAMP)
from sqlalchemy.orm import relationship
from api.connectors import mysql
from api.models.message import Message


class Thread(mysql.OrchardZenBaseModel):
    __tablename__ = 'threads'
    id = Column(SmallInteger, primary_key=True)
    status_id = Column(SmallInteger, default=1)
    priority = Column(SmallInteger, default=2)
    date_created = Column(TIMESTAMP, nullable=False)
    subject_line = Column(_StringType)
    messages = relationship('Message')


def find_by_id(session, thread_id):
    return find_one(session, id=thread_id)


def find_one(session, **kwargs):
    return session.query(Thread).filter_by(**kwargs).first()


def list(session, **kwargs):
    query = session.query(Thread).join(
        Thread.messages, isouter=True).order_by(desc(Thread.date_created))
    if 'min_date' in kwargs:
        query = query.filter(Thread.date_created >= kwargs.get('min_date'))
    if 'max_date' in kwargs:
        # note: your max-date had better be in the YYYY-MM-DD format, sucka
        midnight = datetime.datetime.strptime(kwargs.get('max_date'), '%Y-%m-%d')
        next_midnight = midnight + datetime.timedelta(days=1)
        query = query.filter(Thread.date_created < next_midnight)
    if 'priority' in kwargs:
        query = query.filter(Thread.priority == kwargs.get('priority'))
    if 'status_id' in kwargs:
        query = query.filter(Thread.status_id == kwargs.get('status_id'))
    if 'q' in kwargs:
        searchTerm = "%" + kwargs.get('q') + "%"
        query = query.filter(Thread.subject_line.like(searchTerm) |
            Message.message.like(searchTerm) |
            Message.msg_from.like(searchTerm) |
            Message.msg_to.like(searchTerm))
    return query.all()