import logging
import pytz

from collections import OrderedDict
from datetime import datetime

from sqlalchemy import or_, and_
from sqlalchemy.sql import text
from sqlalchemy.ext.hybrid import hybrid_property

from buyercall.lib.util_sqlalchemy import ResourceMixin
from buyercall.extensions import db

log = logging.getLogger(__name__)

DEFAULT_TIMEZONE = 'US/Central'  # TODO: Define settings in a central location


class AgentSchedule(ResourceMixin, db.Model):
    __tablename__ = 'agent_schedules'
    id = db.Column(db.Integer, primary_key=True)

    agent_id = db.Column(db.Integer, db.ForeignKey('agents.id'), nullable=False)

    is_active = db.Column(db.Boolean, nullable=False)

    day = db.Column(db.Integer, index=True, nullable=False)

    available_from = db.Column(
        db.String(8), nullable=False, server_default='08:00:00'
    )

    available_to = db.Column(
        db.String(8), nullable=False, server_default='17:00:00'
    )

    partnership_account_id = db.Column(
        db.Integer,
        db.ForeignKey(
            'partnership_accounts.id',
            onupdate='CASCADE',
            ondelete='CASCADE'),
        index=True, nullable=False
    )

    def __init__(self, day, available_from, available_to, is_active, partnership_account_id, agent_id):
        self.day = day
        self.available_from = available_from
        self.available_to = available_to
        self.is_active = is_active
        self.partnership_account_id = partnership_account_id
        self.agent_id = agent_id

    @classmethod
    def create(cls, *days):
        """
        Return whether or not the agent schedule was created successfully.

        :return: bool
        """
        for day in days:
            db.session.add(AgentSchedule(**day))
        db.session.commit()

        return True

    @classmethod
    def update(cls, schedule_id, available_from, available_to, is_active, partnership_account_id):
        db.session.query(AgentSchedule).filter_by(id=schedule_id).update(
            {"is_active": is_active,
             "available_from": available_from,
             "available_to": available_to,
             "partnership_account_id": partnership_account_id
             })
        db.session.commit()

    @classmethod
    def api_update(cls, schedule_id, available_from, available_to, is_active, partnership_account_id):
        try:

            schedule = AgentSchedule.query.filter(
                and_(AgentSchedule.id == schedule_id,
                     AgentSchedule.partnership_account_id == partnership_account_id
                     )
            ).first()

            if schedule is not None:
                if available_from is not None:
                    schedule.available_from = available_from

                if available_to is not None:
                    schedule.available_to = available_to

                if is_active is not None:
                    schedule.is_active = is_active

                db.session.commit()

                return True
            else:
                return False

        except Exception as e:
            log.error('Error updating agent schedule. Error: {}'.format(e.message))
            return False

    @classmethod
    def deactivate(cls, agent_id, partnership_account_id):
        agent_found = db.session.query(Agent).filter(
            and_(Agent.id == agent_id, Agent.partnership_account_id == partnership_account_id)
        ).first()

        if agent_found is not None:
            agent_schedules = db.session.query(AgentSchedule).filter(AgentSchedule.agent_id == agent_found.id).all()

            for agent_schedule in agent_schedules:
                agent_schedule.is_active = False
            db.session.commit()

            return True
        else:
            return False


agent_groups_table = db.Table(
    'agent_groups',
    db.Column('group_id', db.Integer, db.ForeignKey('agents.id'), primary_key=True),
    db.Column('agent_id', db.Integer, db.ForeignKey('agents.id'), primary_key=True),
)


class Agent(ResourceMixin, db.Model):
    DEPARTMENT = OrderedDict([
        ('none', 'None'),
        ('sales', 'Sales'),
        ('internet sales', 'Internet Sales'),
        ('bdc', 'BDC'),
        ('service', 'Service'),
        ('management', 'Management'),
        ('client relationship', 'Client Relationship'),
        ('finance', 'Finance'),
        ('other', 'Other')
        ])

    __tablename__ = 'agents'
    id = db.Column(db.Integer, primary_key=True)

    # Relationships.
    leads = db.relationship('Lead', back_populates='agent')

    schedules = db.relationship('AgentSchedule', backref='agent', cascade='all, delete-orphan', lazy='dynamic', order_by='asc(AgentSchedule.day)')

    user_id = db.Column(db.Integer, db.ForeignKey('users.id',
                                                  onupdate='CASCADE',
                                                  ondelete='CASCADE'),
                        index=True, nullable=True)

    partnership_account_id = db.Column(db.Integer, db.ForeignKey('partnership_accounts.id',
                                                                 onupdate='CASCADE',
                                                                 ondelete='CASCADE'),
                                       index=True, nullable=False)

    firstname = db.Column(db.String(128), index=True, nullable=False,
                          server_default='')

    lastname = db.Column(db.String(128), nullable=False, server_default='')

    title = db.Column(db.String(128))

    email = db.Column(db.String(254), nullable=False, server_default='')

    phonenumber = db.Column(db.String(20), nullable=False, server_default='')

    mobile = db.Column(db.String(20), nullable=False, server_default='')

    extension = db.Column(db.Integer, nullable=True, server_default=None)

    department = db.Column(db.String(128))

    description = db.Column(db.String(), nullable=True, server_default='')

    is_group = db.Column(
        db.Boolean, nullable=False, default=False, server_default='f')

    deactivated_on = db.Column(db.DateTime(), nullable=True)

    is_deactivated = db.Column(db.Boolean(), nullable=False,
                               server_default='0', default=False)

    agents = db.relationship(
        'Agent',
        secondary=agent_groups_table,
        primaryjoin=(id == agent_groups_table.c.group_id),
        secondaryjoin=(id == agent_groups_table.c.agent_id),
        backref='groups',
    )

    timezone = db.Column(
        db.String(), nullable=False, server_default=DEFAULT_TIMEZONE,
        default=DEFAULT_TIMEZONE
    )

    contact_count = db.Column(db.Integer, nullable=True, server_default=None)

    all_hours = db.Column(db.Boolean)

    def __init__(self, *args, **kwargs):

        super(Agent, self).__init__(*args, **kwargs)

    @classmethod
    def update(cls, partnership_account_id, title, firstname, lastname, extension, phonenumber,
               mobile, email, department, all_hours, timezone):
        """
        Update the agent's details.

        :return: bool
        """
        agent = Agent.query.filter(and_(Agent.id == id, Agent.partnership_account_id == partnership_account_id)).first()

        if agent is not None:
            get_department = Agent.DEPARTMENT[department]
            agent.firstname = firstname
            agent.lastname = lastname
            agent.title = title
            agent.email = email
            agent.phonenumber = phonenumber
            agent.mobile = mobile
            agent.extension = extension
            agent.all_hours = all_hours

            if get_department is not None:
                agent.department = get_department

            if timezone is not None:
                agent.timezone = timezone

            db.session.commit()

            return True
        else:
            return False

    @classmethod
    def api_update(cls, partnership_account_id, agent_id, title, firstname, lastname, phonenumber,
                   mobile, extension, email, department, description, all_hours):
        """
        Update the agent's details.

        :return: bool
        """
        agent = Agent.query.filter(and_(
            Agent.id == agent_id, Agent.partnership_account_id == partnership_account_id)
        ).first()

        if agent is not None:
            agent.department = department
            agent.description = description
            agent.firstname = firstname
            agent.lastname = lastname
            agent.title = title
            agent.email = email
            agent.phonenumber = phonenumber
            agent.mobile = mobile
            agent.extension = extension
            agent.all_hours = all_hours

            db.session.commit()

            return True
        else:
            return False

    @classmethod
    def api_update_v2(cls, partnership_account_id, agent_id, title, firstname, lastname, phonenumber,
                      mobilenumber, email, department, description, timezone, all_hours):
        """
        Update the agent's details.

        :return: bool
        """
        agent = Agent.query.filter(
            and_(Agent.id == agent_id, Agent.partnership_account_id == partnership_account_id)
        ).first()

        if agent is not None:
            if title is not None:
                agent.title = title

            if firstname is not None:
                agent.firstname = firstname

            if lastname is not None:
                agent.lastname = lastname

            if department is not None:
                agent.department = department

            if description is not None:
                agent.description = description

            if timezone is not None:
                agent.timezone = timezone

            if phonenumber is not None:
                agent.phonenumber = phonenumber

            if mobilenumber is not None:
                agent.mobile = mobilenumber

            if email is not None:
                agent.email = email

            if all_hours is not None:
                agent.all_hours = all_hours

            db.session.commit()

            return True
        else:
            return False

    @classmethod
    def search(cls, query):

        """
        Search a resource by 1 or more fields.

        :param query: Search query
        :type query: str
        :return: SQLAlchemy filter
        """
        if not query:
            return text('')

        search_query = '%{0}%'.format(query)

        return text(
            or_(Agent.email.ilike(search_query),
                Agent.firstname.ilike(search_query),
                Agent.lastname.ilike(search_query),
                Agent.extension.ilike(search_query),
                Agent.department.ilike(search_query)
                )
        )

    @classmethod
    def create(cls, params):
        """
        Return whether or not the agent was created successfully.

        :return: bool
        """
        agent_params = params

        agent = Agent(**agent_params)

        db.session.add(agent)
        db.session.flush()

        agent_id = agent.id

        db.session.commit()

        return agent_id

    @classmethod
    def deactivate(cls, id, partnership_account_id):
        """
        Deactivate agent. The is_active flag will be set to false.

        :return: bool
        """
        agent = Agent.query.filter(and_(Agent.id == id, Agent.partnership_account_id == partnership_account_id)).first()

        if agent is not None:
            agent.all_hours = False
            agent.is_deactivated = True
            agent.deactivated_on = datetime.now()
            db.session.commit()

            return AgentSchedule.deactivate(agent.id, agent.partnership_account_id)
        else:
            return False

    @classmethod
    def reverse_agent_id_lookup(cls, partnership_account_id, agent_name):
        """
        Lookup and return agent id based on agent full name comparison.

        :return: integer
        """
        agent = cls.query.filter(and_(cls.full_name == agent_name,
                                      cls.partnership_account_id == partnership_account_id,
                                      cls.is_deactivated.is_(False))).first()
        if agent:
            agent_id = agent.id
        else:
            agent_id = None
        return agent_id

    @property
    def created_datetime(self):

        """ Return the date/time this agent was created
        """
        return self.created_on.strftime('%Y-%m-%d %H:%M:%S')

    @property
    def updated_datetime(self):

        """ Return the date/time this agent was updated
        """
        return self.updated_on.strftime('%Y-%m-%d %H:%M:%S')

    @property
    def deactivated_on_datetime(self):

        """ Return the date/time this agent was deactivated on.
        """
        if self.deactivated_on is not None and self.deactivated_on is not '':
            return self.deactivated_on.strftime('%Y-%m-%d %H:%M:%S')
        else:
            return ''

    @property
    def available_now(self):
        if self.is_group:
            return any(x.available_now for x in self.agents)

        timezone = pytz.timezone(DEFAULT_TIMEZONE)
        tz_name = (self.timezone or DEFAULT_TIMEZONE)
        try:
            timezone = pytz.timezone(tz_name)
        except pytz.UnknownTimeZoneError:
            log.error("Invalid timezone: '%s'" % tz_name)

        date = datetime.now(pytz.utc)
        local_date = timezone.normalize(date.astimezone(timezone))
        weekday = (local_date.weekday() + 1) % 7

        # Check if the agent/widget is available on that weekday
        # if not self.schedule[weekday]:
        #    return False
        # Check the time
        # time = local_date.strftime('%H:%M:%S')

        # if time < self.available_from or self.available_to < time:
        #    return False

        foundresult = False
        time = local_date.strftime('%H:%M:%S')

        if self.schedules.count() == 0 or self.all_hours:
            foundresult = True
        else:
            for var_day in self.schedules:
                if var_day.day == weekday and var_day.is_active:
                    if var_day.available_from < time < var_day.available_to:
                        foundresult = True
                        break

        return foundresult

    @hybrid_property
    def app_number(self):
        from buyercall.blueprints.mobile.models import Endpoint
        from buyercall.blueprints.phonenumbers.models import Phone
        sip_endpoint = Endpoint.query.filter(Endpoint.agent_id == self.id).first()
        if sip_endpoint:
            number = Phone.query.filter(Phone.id == sip_endpoint.inbound_id).first()
            app_phone_number = number.phonenumber.replace("+1", "")
            return app_phone_number
        else:
            return ''

    @hybrid_property
    def full_name(self):
        return '{} {}'.format(self.firstname, self.lastname)

    @full_name.expression
    def full_name(cls):
        return db.func.concat(cls.firstname, ' ', cls.lastname)

    @hybrid_property
    def contact_leads(self):
        from buyercall.blueprints.contacts.models import Contact
        leads = Contact.query\
            .filter(and_(Contact.partnership_account_id == self.partnership_account_id,
                         Contact.agent_assigned == self.full_name)).all()
        return leads

    @hybrid_property
    def outbound_calls(self):
        from buyercall.blueprints.leads.models import Lead
        calls = Lead.query \
            .filter(and_(Lead.partnership_account_id == self.partnership_account_id,
                         Lead.agent_id == self.id)).all()
        return calls

    @hybrid_property
    def outbound_messages(self):
        from buyercall.blueprints.sms.models import Message
        msgs = Message.query \
            .filter(and_(Message.partnership_account_id == self.partnership_account_id,
                         Message.agent_id == self.id)).all()
        return msgs

    @full_name.setter
    def full_name(self, value):
        if ' ' in value:
            self.firstname, self.lastname = value.split(' ', 1)
        else:
            self.firstname, self.lastname = value, ''

    @full_name.expression
    def full_name(cls):
        return db.func.concat(cls.firstname, ' ', cls.lastname)

    @hybrid_property
    def type(self):
        return 'Agent' if not self.is_group else 'Group'

    @type.expression
    def type(cls):
        return db.case([(cls.is_group == 't', 'Group')], else_='Agent')