import csv
import json
import logging
import uuid

from contextlib import closing
from datetime import date, datetime
from io import StringIO
from os import path

from flask import (
    Blueprint,
    request,
    flash,
    current_app as app,
    url_for,
    jsonify,
    redirect,
    make_response,
    render_template)
from flask_login import login_required, current_user
from sqlalchemy import extract, or_, and_, desc, func, text
from sqlalchemy.orm import contains_eager

from buyercall.blueprints.billing.decorators import subscription_required
from buyercall.blueprints.user.decorators import role_required
from buyercall.blueprints.filters import format_phone_number
from buyercall.extensions import csrf, db
from buyercall.blueprints.sms.models import Message
from buyercall.blueprints.contacts.models import Contact

sms = Blueprint('sms', __name__, template_folder='templates')
log = logging.getLogger(__name__)

MAX_FILE_SIZE = 4 * 1024 * 1024


def days_between(d1, d2):
    d1 = datetime.strptime(d1, "%m%d%Y")
    d2 = datetime.strptime(d2, "%m%d%Y")
    return abs((d2 - d1).days)


def getboolean(param):
    if param == 'true' or param == 'True':
        return True
    else:
        return False


@sms.route('/sms')
@login_required
@role_required('admin', 'agent')
def sms_leads():
    partnership_account_id = current_user.partnership_account_id
    is_admin_in_group = current_user.is_admin_user_with_groups
    viewing_partnership_account = current_user.is_viewing_partnership

    # Check if being viewed by super partner
    if is_admin_in_group and viewing_partnership_account:
        partnership_account_id = current_user.get_user_viewing_partnership_account_id
    elif not viewing_partnership_account and is_admin_in_group:
        return redirect(url_for('partnership.company_accounts'))

    total_messages = Message.query.filter(Message.partnership_account_id == partnership_account_id).count()
    return render_template('sms/sms.jinja2',
                           total_messages=total_messages)


# Display lead details and Add notes to a lead on edit page
@sms.route('/sms/edit/<int:id>', methods=['GET', 'POST'])
@login_required
def lead_edit(id):
    partnership_account_id = current_user.partnership_account_id
    is_admin_in_group = current_user.is_admin_user_with_groups
    viewing_partnership_account = current_user.is_viewing_partnership

    # Check if being viewed by super partner
    if is_admin_in_group and viewing_partnership_account:
        partnership_account_id = current_user.get_user_viewing_partnership_account_id
    elif not viewing_partnership_account and is_admin_in_group:
        return redirect(url_for('partnership.company_accounts'))

    message = Message.query.outerjoin(Message.contact)\
        .outerjoin(Message.inbound).options(
        contains_eager(Message.contact).load_only('firstname', 'lastname'),
        contains_eager(Message.inbound).load_only('id', 'friendly_name'),
    ).filter(and_(Message.id == id, Message.partnership_account_id == partnership_account_id)).first()

    multiple_media = None
    if message:
        if message.media_url and message.media_url[:1] == '[':
            bracket_message_list = str(message.media_url).replace("[", "").replace("]", "").replace(" ", "")
            bracket_message_list_split = bracket_message_list.split(",")
            multiple_media = bracket_message_list_split
        elif message.media_url and message.media_url[:1] == '{':
            replace_single_message_str = str(message.media_url).replace("{", "").replace("}", "").replace(" ", "")
            media_links = replace_single_message_str.split(",")
            new_media_link_list = []
            for i in media_links:
                media_key = (i.split('/', 3)[-1]).split('?')[0].replace('%20', ' ')
                bucket = app.config['MMS_BUCKET']
                from buyercall.lib.util_boto3_s3 import generate_presigned_aws_url
                i = generate_presigned_aws_url(media_key, bucket)
                new_media_link_list.append(i)
            multiple_media = new_media_link_list
    else:
        flash('Message with ID {} not found.'.format(id))
        return redirect(url_for('sms.sms_leads'))

    contact_id = message.contact_id
    message_total_list = None
    message_total_count = 0

    if contact_id is not None:
        messages = Message.query.filter(
            Message.partnership_account_id == partnership_account_id,
            Message.contact_id == contact_id
        )
        message_total_count = messages.count()
        message_total_raw_list = messages.order_by(text('messages.created_on desc')).limit(100).all()
        message_total_list = []
        for msg in message_total_raw_list:
            if msg.media_url and msg.media_url[:1] == '[':
                bracket_msg_list = str(msg.media_url).replace("[", "").replace("]", "").replace(" ", "")
                bracket_msg_list_split = bracket_msg_list.split(",")
                msg.media_url = bracket_msg_list_split
                message_total_list.append(msg)
            elif msg.media_url and msg.media_url[:1] == '{':
                replace_msg_str = str(msg.media_url).replace("{", "").replace("}", "").replace(" ", "")
                media_str_links = replace_msg_str.split(",")
                msg.media_url = media_str_links
                message_total_list.append(msg)
            elif msg.media_url and msg.media_url[:1] not in ['[', '{']:
                single_link = str(msg.media_url).split(",")
                single_link_list = []
                for s in single_link:
                    single_link_list.append(s)
                msg.media_url = single_link_list
                message_total_list.append(msg)
            else:
                message_total_list.append(msg)
                
    return render_template('sms/edit.jinja2',
                           message_info=message,
                           multiple_media=multiple_media,
                           message_log_count=message_total_count,
                           message_log=message_total_list
                           )


@sms.route('/outbound/message/<int:inbound_id>', methods=['POST'])
@csrf.exempt
def send_text_message(inbound_id, to, text_, media, agent_id=None):
    """ This function is used to send text message. First it will check which provider the phonenumber is and then
        decide on which celery text message sending function to use.
    """
    log.info('the media on send text message is: {}'.format(media))

    # Get information on inbound phone number
    from ..phonenumbers.models import Phone
    inbound = Phone.query.filter(Phone.id == inbound_id).first()

    # Check to see if the to number is an existing contact and if so check if the
    # contact unsubscribed in which case no message should be sent
    if inbound:
        contact = Contact.query.filter(and_(Contact.phonenumber_1 == format_phone_number(to),
                                            Contact.partnership_account_id == inbound.partnership_account_id)).first()
        log.info('The to number after formatting looks like: {}'. format(format_phone_number(to)))
        if contact:
            log.info('A message is being sent to contact id: {} using inbound id: {}'.format(contact.id, inbound_id))
            if contact.is_unsubscribe:
                log.info('The contact unsubscribed. The message was not sent to contact id: {}'.format(contact.id))
                return ''
    try:
        if inbound:
            agent_list = []

            if agent_id and agent_id > 0:
                agent_list.append(agent_id)
            else:
                if current_user.is_authenticated:
                    from buyercall.blueprints.agents.models import Agent
                    agent = Agent\
                        .query\
                        .filter(Agent.user_id == current_user.id,
                                Agent.partnership_account_id == current_user.partnership_account_id)\
                        .first()
                    if agent:
                        agent_list.append(agent.id)

            if inbound.provider == 'twilio':
                if media:
                    # Send a mms using function in tw celery task
                    from ..sms.tw_sms_tasks import tw_send_mms
                    tw_send_mms(inbound_id, to, text_, media, agent_list)
                else:
                    # Send a sms using function in tw celery task
                    from ..sms.tw_sms_tasks import tw_send_sms
                    tw_send_sms(inbound_id, to, text_, agent_list)
            elif inbound.provider == 'bandwidth':
                from ..sms.bw_sms_tasks import bw_send_sms
                if media:
                    # Send a mms using function in bw celery task
                    bw_send_sms.apply_async(args=[inbound_id, to, text_, agent_list, media], countdown=1)
                else:
                    # Send a sms using function in bw celery task
                    bw_send_sms.apply_async(args=[inbound_id, to, text_, agent_list], countdown=1)
            else:
                log.info('The phone number has no provider')
        else:
            log.info('There is no phone number in the db matching the inbound id')
    except Exception as e:
        log.error('No data was returned with the inbound id when trying to send a text message')


@sms.route('/inbound/sms-attachment', methods=['POST'])
@login_required
@subscription_required
@role_required('admin', 'partner', 'sysadmin', 'limitsysadmin')
def upload_sms_attachment():
    def allowed_file(file_ext):
        return file_ext in ['.jpeg', '.jpg', '.png', '.gif', '.wav', '.mp4',
                            '.mpeg', '.bmp', '.csv', '.pdf', '.mp3']

    file = request.files['file']
    file_ext = path.splitext(file.filename.lower())[1]
    if not file:
        return make_response('Error uploading file.', 400)

    if not allowed_file(file_ext):
        return make_response('File extension not allowed: ' + file_ext, 400)

    if file.content_length and file.content_length > MAX_FILE_SIZE:
        return make_response('File too large.', 400)

    filename = str(uuid.uuid4())
    file_path = path.join(app.config['UPLOAD_FOLDER'], filename)
    file.save(file_path)

    partnership_account_id = current_user.partnership_account_id
    viewing_partnership_account = current_user.is_viewing_partnership

    # Check if being viewed by super partner
    if viewing_partnership_account:
        partnership_account_id = current_user.get_user_viewing_partnership_account_id

    if path.getsize(file_path) > MAX_FILE_SIZE:
        return make_response('File too large.', 400)

    from buyercall.blueprints.partnership.models import PartnershipAccount
    partner = PartnershipAccount.query.filter(PartnershipAccount.id == partnership_account_id).first()
    partner_name = partner.name

    from .tasks import upload_mms_image
    image_url = upload_mms_image(partner_name, partnership_account_id, file.filename, file_path)
    log.info('The mms url is {}'.format(image_url))

    return jsonify(filename=file.filename, image_url=image_url)


# return data for the filter options
@sms.route('/sms/filteroptions', methods=['GET'])
@csrf.exempt
@login_required
def filteroptions():
    status_result = {}
    source_result = {}
    partnership_account_id = current_user.partnership_account_id
    viewing_partnership_account = current_user.is_viewing_partnership

    # Check if being viewed by super partner
    if viewing_partnership_account:
        partnership_account_id = current_user.get_user_viewing_partnership_account_id

    filter_by = (Message.partnership_account_id == partnership_account_id)

    # retrieve the inbound friendly names from phone numbers
    source_data = Message.query\
        .outerjoin(Message.inbound)\
        .options(contains_eager(Message.inbound))\
        .with_entities('phonenumbers.id', 'phonenumbers.friendly_name', 'phonenumbers.phonenumber')\
        .filter(filter_by)\
        .distinct()

    # retrieve the status from the message table
    status_data = db.session.query(Message.status)\
        .filter(filter_by).distinct()\
        .order_by(Message.status)

    # populate result with the phonenumbers
    for i in source_data:
        if i is not None and i is not '':
            if i[0] is not None and i[2] is not None:
                id = str(i[0])
                name = str(i[2])
                friendly_name = str(i[1])
                source_result[id] = name + ' (' + friendly_name + ')'

    # populate result with the statuses
    for i in status_data:
        if i is not None and i is not '':
            if i[0] is not None:
                id = str(i[0])
                name = str(i[0])
                status_result[id] = name

    return jsonify(
        status_data=status_result,
        source_data=source_result
    )


# return data into jquery datatables
@sms.route('/sms/data')
@csrf.exempt
@login_required
def data():
    """Return server side data."""
    search = request.args.get('search[value]', '')
    order = int(request.args.get('order[0][column]', '-1'))
    direction = request.args.get('order[0][dir]', 'asc')
    offset = int(request.args.get('start', 0))
    limit = int(request.args.get('length', 99))
    date_from = str(request.args.get('df'))
    date_to = str(request.args.get('dt'))

    message_direction = str(request.args.get('dn'))
    message_source = str(request.args.get('so'))
    message_status = str(request.args.get('st'))
    message_type = str(request.args.get('tp'))

    date_difference = days_between(date_from, date_to)

    # filter initiation section
    filter_by_type = text('')
    filter_by_direction = text('')
    filter_by_message_status = text('')
    filter_by_message_source = text('')

    converted_dateFrom = datetime.strptime(date_from, "%m%d%Y").date()
    converted_dateTo = datetime.strptime(date_to, "%m%d%Y").date()

    partnership_account_id = current_user.partnership_account_id
    viewing_partnership_account = current_user.is_viewing_partnership

    # Check if being viewed by super partner
    if viewing_partnership_account:
        partnership_account_id = current_user.get_user_viewing_partnership_account_id

    filter_by_date = and_(func.date(Message.created_on) >= converted_dateFrom,
                          func.date(Message.created_on) <= converted_dateTo)
    filter_by = and_(Message.partnership_account_id == partnership_account_id,
                     or_(current_user.role == 'admin', current_user.role == 'agent'))

    columns_search = [
        Message.id, Message.type, Message.from_, Message.to,
        Message.direction, Message.source, Message.status,
        Contact.firstname, Message.contact_id, Contact.lastname
    ]
    columns_window = [
        Message.id, Message.type, Message.from_, Message.to, Message.body_text,
        Message.created_on, Message.direction, Message.source, Message.status,
        Contact.firstname, Message.contact_id, Contact.lastname
    ]

    # Message status filter section
    if message_status != 'null' and message_status and message_status is not '---':
        filter_by_message_status = (text("messages.status = '{}'".format(message_status)))

    # Message direction filter section
    if message_direction != 'null' and message_direction and message_direction is not '---':
        if message_direction == 'in':
            filter_by_direction = (text("(messages.direction = 'in' or messages.direction = 'inbound')"))
        elif message_direction == 'out':
            filter_by_direction = (text("(messages.direction = 'out' or messages.direction = 'outbound')"))

    # Message type filter section
    if message_type != 'null' and message_type and message_type is not '---':
        filter_by_type = (func.lower(Message.type) == func.lower(message_type))

    # Message source filter section
    if message_source != 'null' and message_source and message_source is not '---':
        filter_by_message_source = (Message.inbound_id == message_source)

    # define data columns for the counts
    columns_total = [
        Message.id
    ]

    total = Message.query.outerjoin(Message.contact)\
        .outerjoin(Message.inbound).options(
        contains_eager(Message.contact).load_only('firstname', 'lastname'),
        contains_eager(Message.inbound).load_only('id', 'friendly_name'),
    ).filter(filter_by)\
        .filter(filter_by_date)\
        .filter(filter_by_type)\
        .filter(filter_by_message_status)\
        .filter(filter_by_message_source)\
        .filter(filter_by_direction)

    filtered = total

    if search:
        total_list = total.with_entities(*columns_search).all()
        searched_result_list = [x for x in total_list
                                if x.firstname.lower() + ' ' + x.lastname.lower() == search.lower()
                                or x.firstname.lower() == search.lower()
                                or x.lastname.lower() == search.lower()
                                or x.to.replace("+1", "").replace("(", "").replace(")", "").replace("-", "")
                                .replace(" ", "") == search.replace("+1", "").replace("(", "").replace(")", "")
                                .replace("-", "").replace(" ", "")
                                or x.from_.replace("+1", "").replace("(", "").replace(")", "").replace("-", "")
                                .replace(" ", "") == search.replace("+1", "").replace("(", "").replace(")", "")
                                .replace("-", "").replace(" ", "")
                                or x.type.lower() == search.lower()
                                or x.status.lower() == search.lower()
                                or x.source.lower() == search.lower()
                                or x.direction.lower() == search.lower()
                                ]
        searched_result_ids = [x.id for x in searched_result_list]
        filtered = Message.query.outerjoin(Message.contact)\
            .outerjoin(Message.inbound).options(
            contains_eager(Message.contact).load_only('firstname', 'lastname'),
            contains_eager(Message.inbound).load_only('id', 'friendly_name'),)\
            .filter(Message.id.in_(searched_result_ids))

    sorted_ = filtered.with_entities(*columns_window)

    if order in range(len(columns_window)):
        order_pred = columns_window[order]
        if direction == 'desc':
            order_pred = desc(order_pred)
        sorted_ = sorted_.order_by(order_pred)
    sorted_ = sorted_.offset(offset).limit(limit)

    data = [
        {i: row[i] for i in range(len(row))
         } for row in sorted_.all()
        ]
    # set specific columns for the total count
    filtered_total = filtered.with_entities(*columns_total)

    message_id_list = list()
    for m in filtered_total.all():
        message_id_list.append(m[0])

    total_msg_leads = len(message_id_list)

    from buyercall.lib.supervisor_manager import current_supervisor_user
    for lp_data in data:
        if lp_data[4] is not None:
            try:
                if current_supervisor_user.role in ('limitsysadmin', 'partner'):
                    lp_data[4] = 'Restricted'
            except AttributeError:
                pass

    # Get selectors
    parent_type_filter = extract('day', func.date(Message.created_on))
    child_type_filter = extract('month', func.date(Message.created_on))
    year_selector = False
    order_by_year = 'parent'

    # Check filter type days/month/year
    if date_difference <= 31:
        parent_type_filter = extract('day', func.date(Message.created_on))
        child_type_filter = extract('month', func.date(Message.created_on))
    elif 32 <= date_difference <= 366:
        parent_type_filter = extract('month', func.date(Message.created_on))
        child_type_filter = extract('year', func.date(Message.created_on))

        if converted_dateFrom.year < converted_dateTo.year:
            order_by_year = 'child'
    elif date_difference > 366:
        parent_type_filter = extract('year', func.date(Message.created_on))
        child_type_filter = extract('year', func.date(Message.created_on))
        year_selector = True

    # Total calls data and values for Line Graph for the given date/time range
    labels = db.session.query(
        parent_type_filter.label('parent'),
        child_type_filter.label('child'),
        func.count(Message.created_on).label('total'))\
        .filter(filter_by_date)\
        .filter(filter_by) \
        .filter(Message.id.in_(message_id_list))\
        .group_by('parent', 'child')\
        .order_by(order_by_year)

    filter_data_points = list()
    filter_labels = list()
    filter_date = list()
    for label in labels:
        split_filter_date = str(label.parent).split('.')
        split_filter_child = str(label.child).split('.')

        if label.parent not in filter_date:
            filter_date.append(label.parent)
        if year_selector:
            filter_labels.append(split_filter_date[0])
        else:
            filter_labels.append(split_filter_date[0] + '/' + split_filter_child[0])
        filter_data_points.append(label.total)

    # Total messages data and values for Line Graph
    labels_totals = labels.filter(filter_by)

    labels_total_date = list()
    labels_total_value = list()
    for row in labels_totals:
        labels_total_date.append(row.parent)
        labels_total_value.append(row.total)

    total_data_points = list()
    complete_point = 0
    for day in filter_date:
        if day in labels_total_date:
            if complete_point < (len(labels_total_value)):
                total_data_points.append(labels_total_value[complete_point])
                complete_point = complete_point + 1
        else:
            total_data_points.append(0)

    # Inbound messages data and values for Line Graph
    labels_in = labels.filter(or_(Message.direction == 'in', Message.direction == 'inbound'))

    labels_in_date = list()
    labels_in_value = list()
    for row in labels_in:
        labels_in_date.append(row.parent)
        labels_in_value.append(row.total)

    in_data_points = list()
    in_point = 0
    for day in filter_date:
        if day in labels_in_date:
            if in_point < (len(labels_in_value)):
                in_data_points.append(labels_in_value[in_point])
                in_point = in_point + 1
        else:
            in_data_points.append(0)

    # Outbound messages data and values for Line Graph
    labels_out = labels_totals.filter(or_(Message.direction == 'out', Message.direction == 'outbound'))

    labels_out_date = list()
    labels_out_value = list()
    for row in labels_out:
        labels_out_date.append(row.parent)
        labels_out_value.append(row.total)

    out_data_points = list()
    out_point = 0
    for day in filter_date:
        if day in labels_out_date:
            if out_point < (len(labels_out_value)):
                out_data_points.append(labels_out_value[out_point])
                out_point = out_point + 1
        else:
            out_data_points.append(0)

    all_messages = list()
    for i in total_data_points:
        all_messages.append(i)

    inbound_messages = list()
    for i in in_data_points:
        inbound_messages.append(i)

    outbound_messages = list()
    for i in out_data_points:
        outbound_messages.append(str(i))

    points = list()
    for i in filter_labels:
        points.append(str(i))

    # Side bar data
    # Total message count
    total_messages = filtered.count()

    # Unique call count
    total_unique_messages = filtered\
        .filter(or_(filter_by))\
        .distinct(Message.from_).count()

    # Total Inbound Messages
    total_inbound_messages = filtered.filter(or_(Message.direction == 'in', Message.direction == 'inbound')).count()

    # Total Inbound Percentage
    if total_messages is None or total_messages == 0:
        total_inbound_percentage = 0
    else:
        total_inbound_percentage = int((total_inbound_messages * 100) / total_messages)

    # Total Outbound Messages
    total_outbound_messages = filtered.filter(or_(Message.direction == 'out', Message.direction == 'outbound')).count()

    # Total Outbound Percentage
    if total_messages is None or total_messages == 0:
        total_outbound_percentage = 0
    else:
        total_outbound_percentage = int((total_outbound_messages * 100) / total_messages)

    return jsonify(
        draw=request.args['draw'],
        recordsFiltered=total_msg_leads,
        recordsTotal=total_msg_leads,
        data=data,
        messages=total_messages,
        unique_messages=total_unique_messages,
        inbound_messages=total_inbound_messages,
        inbound_percentage=total_inbound_percentage,
        outbound_messages=total_outbound_messages,
        outbound_percentage=total_outbound_percentage,
        graphTotal=all_messages,
        graphInbound=inbound_messages,
        graphOutbound=outbound_messages,
        graphPoints=points
    )


@sms.route('/sms/csv', methods=['GET'])
@login_required
def data_csv():
    """Return server side data."""
    date_from = str(request.args.get('df'))
    date_to = str(request.args.get('dt'))

    message_direction = str(request.args.get('dn'))
    message_source = str(request.args.get('so'))
    message_status = str(request.args.get('st'))
    message_type = str(request.args.get('tp'))

    # filter initiation section
    filter_by_type = text('')
    filter_by_direction = text('')
    filter_by_message_status = text('')
    filter_by_message_source = text('')

    converted_dateFrom = datetime.strptime(date_from, "%m%d%Y").date()
    converted_dateTo = datetime.strptime(date_to, "%m%d%Y").date()

    partnership_account_id = current_user.partnership_account_id
    viewing_partnership_account = current_user.is_viewing_partnership

    # Check if being viewed by super partner
    if viewing_partnership_account:
        partnership_account_id = current_user.get_user_viewing_partnership_account_id

    filter_by_date = and_(func.date(Message.created_on) >= converted_dateFrom,
                          func.date(Message.created_on) <= converted_dateTo)
    filter_by = and_(Message.partnership_account_id == partnership_account_id, current_user.role == 'admin')

    search = request.args.get('search[value]', '')
    header = [
        'No', 'Created On', 'Type',
        'To', 'From', 'Message',
        'Media URL', 'Status', 'Direction',
        'Lead', 'Lead Source', 'Agent'
    ]

    # Message status filter section
    if message_status != 'null' and message_status and message_status is not '---':
        filter_by_message_status = text("messages.status = '{}'".format(message_status))

    # Message direction filter section
    if message_direction != 'null' and message_direction and message_direction is not '---':
        filter_by_direction = text("messages.direction = '{}}'".format(message_direction))

    # Message type filter section
    if message_type != 'null' and message_type and message_type is not '---':
        filter_by_type = (func.lower(Message.type) == func.lower(message_type))

    # Message source filter section
    if message_source != 'null' and message_source and message_source is not '---':
        filter_by_message_source = (Message.inbound_id == message_source)

    total = Message.query.outerjoin(Message.contact)\
        .outerjoin(Message.inbound).options(
        contains_eager(Message.contact).load_only('firstname', 'lastname'),
        contains_eager(Message.inbound).load_only('id', 'friendly_name'),
    ).filter(filter_by)\
        .filter(filter_by_date)\
        .filter(filter_by_type)\
        .filter(filter_by_message_status)\
        .filter(filter_by_message_source)\
        .filter(filter_by_direction)

    filtered = total

    columns_search = [
        Message.id, Message.type, Message.from_, Message.to,
        Message.direction, Message.source, Message.status,
        Contact.firstname, Message.contact_id, Contact.lastname
    ]

    if search:
        total_list = total.with_entities(*columns_search).all()
        searched_result_list = [x for x in total_list
                                if x.firstname.lower() + ' ' + x.lastname.lower() == search.lower()
                                or x.firstname.lower() == search.lower()
                                or x.lastname.lower() == search.lower()
                                or x.to.replace("+1", "").replace("(", "").replace(")", "").replace("-", "")
                                .replace(" ", "") == search.replace("+1", "").replace("(", "").replace(")", "")
                                .replace("-", "").replace(" ", "")
                                or x.from_.replace("+1", "").replace("(", "").replace(")", "").replace("-", "")
                                .replace(" ", "") == search.replace("+1", "").replace("(", "").replace(")", "")
                                .replace("-", "").replace(" ", "")
                                or x.type.lower() == search.lower()
                                or x.status.lower() == search.lower()
                                or x.source.lower() == search.lower()
                                or x.direction.lower() == search.lower()
                                ]
        searched_result_ids = [x.id for x in searched_result_list]
        filtered = Message.query.outerjoin(Message.contact) \
            .outerjoin(Message.inbound).options(
            contains_eager(Message.contact).load_only('firstname', 'lastname'),
            contains_eager(Message.inbound).load_only('id', 'friendly_name'), ) \
            .filter(Message.id.in_(searched_result_ids))

    query = filtered.order_by(Message.id)

    # Build the CSV
    row_no = 0
    with closing(StringIO()) as out:
        writer = csv.writer(out)
        writer.writerow(header)
        for msg in query.all():
            row_no += 1
            csv_row = [
                row_no, msg.created_on, msg.type,
                msg.to, msg.from_, msg.body_text,
                msg.media_url, msg.status, msg.direction,
                (msg.contact and msg.contact.name) or (msg.contact and msg.contact.phonenumber_1) or '',
                msg.inbound and msg.inbound.friendly_name or '',
                msg.message_agent
            ]
            writer.writerow([x for x in csv_row])

        filename = 'Buyercall Messages - {}.csv'.format(
            date.today().strftime('%Y-%m-%d')
        )

        resp = make_response(out.getvalue())
        resp.headers['Content-Type'] = 'text/csv'
        resp.headers['Content-Disposition'] = \
            'attachment; filename="{}"'.format(filename)
        return resp


def format_date(date_string):
    """ Format a local date for consumption by Excel
    """
    if not date_string:
        return ''
    return date_string.strftime('%Y-%m-%d %H:%M:%S')