import logging as log

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import select, func
from sqlalchemy.dialects import postgresql as pg

from buyercall.lib.util_sqlalchemy import ResourceMixin
from buyercall.extensions import db
from buyercall.lib.util_crypto import EncryptedValue
import json


class Message(ResourceMixin, db.Model):
    __tablename__ = 'messages'

    id = db.Column(db.Integer, primary_key=True)
    # Identifies message as a sms or mms
    type = db.Column(db.String(32), nullable=False, server_default='')
    # Identify the provider for the message. Either Twilio or Bandwidth
    provider = db.Column(db.String(32), nullable=False, server_default='', index=True)
    # The provider id for the message
    provider_message_id = db.Column(db.String(64), nullable=False, server_default='')
    # The date the provider created the message
    provider_message_date = db.Column(db.String(64), nullable=False, server_default='')
    # The number receiving the message
    to = db.Column(EncryptedValue(64), index=True, nullable=False, server_default='')
    # The number sending the message
    from_ = db.Column(EncryptedValue(64), index=True, nullable=False, server_default='')
    # The body text of the message
    body_text = db.Column(EncryptedValue(5120), nullable=False, server_default='')
    # The number of media files for the message
    num_media_files = db.Column(db.String(10), nullable=True)
    # The media url for any media attached to image
    media_url = db.Column(EncryptedValue(512), nullable=True)
    # The status of the message
    status = db.Column(db.String(64), nullable=False, server_default='')
    # Message delivery type
    delivery_type = db.Column(db.String(64), nullable=True)
    # Message delivery code
    delivery_code = db.Column(db.Integer, nullable=True)
    # Message delivery code decription
    delivery_description = db.Column(db.String(256), nullable=True)
    # Message direction (in = inbound, out = outbound)
    direction = db.Column(db.String(64), nullable=False, server_default='')
    # The inbound phonenumber the message is linked to
    inbound_id = db.Column(db.Integer, db.ForeignKey(
        'phonenumbers.id', name='message_inbound_fkey', onupdate='CASCADE', ondelete='SET NULL'), index=True,
                           nullable=False)
    # The partnership account id associated with the message
    partnership_account_id = db.Column(db.Integer, db.ForeignKey(
        'partnership_accounts.id', name='message_partnership_account_fkey', onupdate='CASCADE', ondelete='CASCADE'),
                                       index=True, nullable=False)
    # The contact id associated with the message
    contact_id = db.Column(db.Integer, db.ForeignKey(
        'contacts.id', name='message_contact_fkey', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False)
    # The originating phone number if operational number is used
    originating_number = db.Column(EncryptedValue(64), index=True, nullable=False,
                                   server_default='')
    # The agent ID linked to outbound message OR agent ID(s) linked to inbound message.
    agent_id = db.Column(pg.JSON, nullable=False, server_default='[]')

    @hybrid_property
    def message_agent(self):
        from buyercall.blueprints.agents.models import Agent
        agent_list = []
        result = ''

        if self.agent_id:
            agents = Agent\
                .query\
                .filter(Agent.id.in_(self.agent_id), Agent.partnership_account_id == self.partnership_account_id)\
                .all()

            for agent in agents:
                agent_list.append(agent.full_name)

            result = ', '.join(agent_list)

        return result

    @hybrid_property
    def message_source(self):
        return (
            self.inbound and self.inbound.friendly_name
        ) or ''

    @message_source.expression
    def message_source(cls):
        from ..phonenumbers.models import Phone
        from buyercall.blueprints.mobile.models import Endpoint
        from buyercall.blueprints.agents.models import Agent
        endpoint = Endpoint.query.filter(
            Endpoint.inbound_id == cls.inbound_id
        ).first()
        if endpoint and endpoint.agent_id:
            return select([Agent.full_name.label("name")]).where(
                Agent.id == endpoint.agent_id
            ).limit(1).label("phone")
        return select([Phone.friendly_name.label("name")]).where(
            Phone.id == cls.inbound_id
        ).limit(1).label("phone")

    @hybrid_property
    def source(self):
        return (
                       self.inbound and self.inbound.friendly_name
               ) or ''

    @source.expression
    def source(cls):
        from ..phonenumbers.models import Phone

        return Phone.friendly_name

    @hybrid_property
    def source_id(self):
        return (
            self.inbound and str(self.inbound_id)
        ) or ''

    @property
    def interaction_time(self):

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

    @property
    def updated_time(self):

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


class MessageReporting(ResourceMixin, db.Model):
    __tablename__ = 'messages_reporting'

    id = db.Column(db.Integer, primary_key=True)
    type = db.Column(db.String(32), nullable=True)
    provider = db.Column(db.String(32), nullable=True)
    provider_message_id = db.Column(db.String(64), nullable=True)
    num_media_files = db.Column(db.String(10), nullable=True)
    status = db.Column(db.String(64), nullable=True)
    delivery_type = db.Column(db.String(64), nullable=True)
    delivery_code = db.Column(db.Integer, nullable=True)
    delivery_description = db.Column(db.String(256), nullable=True)
    direction = db.Column(db.String(64), nullable=True)
    inbound_id = db.Column(db.Integer, db.ForeignKey(
        'phonenumbers.id', name='message_reporting_inbound_id_fkey'), index=True,
                           nullable=False)
    partnership_account_id = db.Column(db.Integer, db.ForeignKey(
        'partnership_accounts.id', name='message_reporting_partnership_account_fkey'),
                                       index=True, nullable=False)
    contact_id = db.Column(db.Integer, db.ForeignKey(
        'contacts.id', name='message_reporting_contact_id_fkey'), index=True, nullable=True)
    agent_id = db.Column(pg.JSON, nullable=True)