"""Order Model."""

from pymysql.converters import escape_string
import sqlalchemy
from sqlalchemy.orm import relationship

from podcast.connectors import mysql
from podcast.models import advertisement as advertisement_model
from podcast.models import campaign as campaign_model
from podcast.models import episode as episode_model
from podcast.models import network as network_model
from podcast.models import podcast as podcast_model
from podcast.utils.exc import OwsError


order_network_table = sqlalchemy.Table(
    'campaign_order_network', mysql.BaseModel.metadata,
    sqlalchemy.Column('campaign_order_id', sqlalchemy.Integer, sqlalchemy.ForeignKey('campaign_order.id')),
    sqlalchemy.Column('network_id', sqlalchemy.Integer, sqlalchemy.ForeignKey('network.id')),
)

order_episode_table = sqlalchemy.Table(
    'campaign_order_episodes', mysql.BaseModel.metadata,
    sqlalchemy.Column('campaign_order_id', sqlalchemy.VARCHAR(255),
                      sqlalchemy.ForeignKey('campaign_order.megaphone_id')),
    sqlalchemy.Column('episode_id', sqlalchemy.Integer, sqlalchemy.ForeignKey('episode.id')),
)


order_podcast_table = sqlalchemy.Table(
    'campaign_order_podcasts', mysql.BaseModel.metadata,
    sqlalchemy.Column('campaign_order_id', sqlalchemy.VARCHAR(255),
                      sqlalchemy.ForeignKey('campaign_order.megaphone_id')),
    sqlalchemy.Column('podcast_id', sqlalchemy.Integer, sqlalchemy.ForeignKey('podcast.id')),
)


class Order(mysql.BaseModel):
    """Order model."""

    __tablename__ = 'campaign_order'

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)  # noqa
    megaphone_id = sqlalchemy.Column(sqlalchemy.VARCHAR(255))
    name = sqlalchemy.Column(sqlalchemy.VARCHAR(255))
    start_at = sqlalchemy.Column(sqlalchemy.DateTime)
    end_at = sqlalchemy.Column(sqlalchemy.DateTime)
    pos = sqlalchemy.Column(sqlalchemy.Integer)
    enabled = sqlalchemy.Column(sqlalchemy.Boolean, nullable=False, default=True)
    campaign_megaphone_id = sqlalchemy.Column(sqlalchemy.VARCHAR(255))
    campaign = relationship(
        'Campaign',
        foreign_keys=[campaign_megaphone_id],
        primaryjoin=campaign_megaphone_id == campaign_model.Campaign.megaphone_id
    )
    advertisements = relationship(
        'Advertisement',
        foreign_keys=[advertisement_model.Advertisement.campaign_order_megaphone_id],
        primaryjoin=megaphone_id == advertisement_model.Advertisement.campaign_order_megaphone_id
    )
    networks = relationship('Network', secondary=order_network_table, lazy='joined')
    episodes = relationship('Episode', secondary=order_episode_table, lazy='select')
    podcasts = relationship('Podcast', secondary=order_podcast_table, lazy='select')
    is_deleted = sqlalchemy.Column(sqlalchemy.Boolean, nullable=False, default=False)

    def to_dict(self, full_dict=False):
        """Return the object as dictionary."""
        order_dict = dict(
            id=self.megaphone_id,
            db_id=self.id,
            name=self.name,
            start_at=self.start_at,
            end_at=self.end_at,
            position=self.pos,
            enabled=self.enabled,
            network_ids=[network.to_dict()['id'] for network in self.networks],
            episode_ids=[episode.to_dict()['id'] for episode in self.episodes],
            podcast_ids=[podcast.to_dict()['id'] for podcast in self.podcasts],
            campaign_id=self.campaign_megaphone_id,
            is_deleted=self.is_deleted
        )
        if full_dict:
            order_dict['campaign'] = self.campaign.to_dict()
            order_dict['advertisements'] = [ad.to_dict() for ad in self.advertisements]
        return order_dict


def get_order_by_megaphone_id(mp_id):
    """Get order by megaphone id."""
    with mysql.pod_db_session(read_only=True) as session:
        order = session.query(Order).filter(Order.megaphone_id == mp_id).first()
        if not order:
            raise OwsError.not_found()
        return order.to_dict()


def get_orders_by_campaign_id(campaign_mp_id):
    """Get orders by campaign megaphone id that are not soft deleted."""
    with mysql.pod_db_session(read_only=True) as session:
        orders = session.query(Order).filter(
            Order.campaign_megaphone_id == campaign_mp_id,
            Order.is_deleted.isnot(True)
        ).all()
        return [result.to_dict() for result in orders]


def get_orders_by_episode_ids(ids, podcast_ids):
    """Get order by megaphone id."""
    with mysql.pod_db_session(read_only=True) as session:
        orders = session.query(Order).filter(
            Order.is_deleted.isnot(True),
            sqlalchemy.or_(
                Order.episodes.any(episode_model.Episode.id.in_(ids)),
                Order.podcasts.any(podcast_model.Podcast.id.in_(podcast_ids))
            )
        )
        # return campaign and advertisements as they are always required by the query
        results = [result.to_dict(full_dict=True) for result in orders.all()]
        return {'items': results}


def get_orders_by_megaphone_ids(megaphone_ids, network_ids, podcast_ids, episode_ids):
    """Get orders by megaphone ids and network ids or podcast ids or episode ids.

    Args:
        megaphone_ids (list of ints): list of megaphone ids
        network_ids (list of ints): list of network ids
        podcast_ids (list of ints): list of podcast ids
        episode_ids (list of ints): list of episode ids
    Returns:
        list (dict): orders
    """
    with mysql.pod_db_session(read_only=True) as session:
        orders = session.query(Order).filter((Order.megaphone_id.in_(megaphone_ids))) \
            .filter(sqlalchemy.or_(
                Order.networks.any(network_model.Network.id.in_(network_ids)),
                Order.podcasts.any(podcast_model.Podcast.id.in_(podcast_ids)),
                Order.episodes.any(episode_model.Episode.id.in_(episode_ids))
            )).all()
        return [order.to_dict() for order in orders]


def delete_orders_by_megaphone_ids(mp_ids):
    """Soft delete orders by megaphone ids."""
    with mysql.pod_db_session() as session:
        orders = session.query(Order).filter(Order.megaphone_id.in_(mp_ids)).all()
        for order in orders:
            order.is_deleted = True
        return [order.to_dict() for order in orders]


def create_or_update_orders(orders, campaign_id):
    """Create many orders.

    Args:
        orders (list of dicts): the orders to create or update.
    """
    with mysql.pod_db_session() as session:
        _create_orders(session, orders)
        mp_id_to_id = _get_mp_id_to_id(session, orders)
        _create_orders_to_network(session, orders, mp_id_to_id)

        campaign_orders = session.query(Order).filter(
            Order.campaign_megaphone_id == campaign_id).all()
        order_ids = [order.to_dict()['id'] for order in campaign_orders]

        _create_orders_to_episode(session, orders, order_ids)
        _create_orders_to_podcast(session, orders, order_ids)


def _create_orders(session, orders):
    rows = []
    for order in orders:
        rows.append("('{}', '{}', '{}', '{}', {}, {}, {}, '{}')".format(
            escape_string(order['id']),
            escape_string(str(order.get('name', ''))),
            order['start_at'].replace(tzinfo=None),
            order['end_at'].replace(tzinfo=None),
            int(order['pos']),
            int(order['priority']),
            order['enabled'],
            order['campaign_id'],
        ))
    if len(rows) == 0:
        return

    session.execute("""
    INSERT INTO campaign_order (
      megaphone_id, name, start_at, end_at, pos, priority, enabled, campaign_megaphone_id
    ) VALUES
    {}
    ON DUPLICATE KEY UPDATE name=VALUES(name), start_at=VALUES(start_at), end_at=VALUES(end_at),
    pos=VALUES(pos), priority=VALUES(priority),
    enabled=VALUES(enabled), campaign_megaphone_id=VALUES(campaign_megaphone_id);
    """.format(','.join(rows)))
    session.commit()


def _get_mp_id_to_id(session, orders):
    mp_ids = []
    for order in orders:
        mp_ids.append(order['id'])

    results = session.query(Order.id, Order.megaphone_id).filter(Order.megaphone_id.in_(mp_ids)).all()

    return {result[1]: result[0] for result in results}


def _create_orders_to_network(session, orders, mp_id_to_id):
    order_networks = []
    for order in orders:
        order_id = mp_id_to_id[order['id']]
        for network_id in order['network_ids']:
            order_networks.append('({}, {})'.format(order_id, network_id))

    if len(order_networks) == 0:
        return

    session.execute("""
    INSERT INTO campaign_order_network (
      campaign_order_id, network_id
    ) VALUES
    {}
    ON DUPLICATE KEY UPDATE campaign_order_id=VALUES(campaign_order_id);
    """.format(','.join(order_networks)))


def _create_orders_to_episode(session, orders, order_ids):
    episode_mp_ids = _order_target_ids(orders, 'episode')
    episodes = episode_model.get_episodes_by_megaphone_ids(episode_mp_ids)['items']

    values = []
    for order in orders:
        for target in order['targets']:
            if target['type'] == 'episode':
                for episode in episodes:
                    if target['id'] == episode['megaphone_id']:
                        values.append("('{}', {})".format(order['id'], episode['id']))

    if len(values) == 0:
        return

    session.execute(order_episode_table.delete().where(
        order_episode_table.c.campaign_order_id.in_(order_ids)))
    session.execute("""
    INSERT INTO campaign_order_episodes (
      campaign_order_id, episode_id
    ) VALUES
    {};
    """.format(','.join(values)))


def _create_orders_to_podcast(session, orders, order_ids):
    podcast_mp_ids = _order_target_ids(orders, 'podcast')
    podcasts = podcast_model.get_podcasts_by_megaphone_ids(podcast_mp_ids)['items']

    values = []
    for order in orders:
        for target in order['targets']:
            if target['type'] == 'podcast':
                for podcast in podcasts:
                    if target['id'] == podcast['megaphone_id']:
                        values.append("('{}', {})".format(order['id'], podcast['id']))

    if len(values) == 0:
        return

    session.execute(order_podcast_table.delete().where(
        order_podcast_table.c.campaign_order_id.in_(order_ids)))
    session.execute("""
    INSERT INTO campaign_order_podcasts (
      campaign_order_id, podcast_id
    ) VALUES
    {};
    """.format(','.join(values)))


def _order_target_ids(orders, podcast_or_episode):
    ids = []
    for order in orders:
        for target in order['targets']:
            if target['type'] == podcast_or_episode:
                ids.append(target['id'])
    return ids