"""Order Model."""

from pymysql.converters import escape_string
import sqlalchemy

from podcast.connectors import mysql
from podcast.logic import user as user_logic
from podcast.models import ad_action as ad_action_model
from podcast.models import episode as episode_model
from podcast.models import network as network_model
from podcast.models import order as order_model
from podcast.models import podcast as podcast_model


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

    __tablename__ = 'campaign_advertisement'

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)  # noqa
    name = sqlalchemy.Column(sqlalchemy.VARCHAR(255))
    megaphone_id = sqlalchemy.Column(sqlalchemy.VARCHAR(255))
    insertion_point = sqlalchemy.Column(sqlalchemy.Enum(*['pre', 'mid', 'post']))
    pos = sqlalchemy.Column(sqlalchemy.VARCHAR(255))
    audio_file = sqlalchemy.Column(sqlalchemy.VARCHAR(1000))
    campaign_order_megaphone_id = sqlalchemy.Column(sqlalchemy.VARCHAR(255))

    def to_dict(self):
        """Return the object as dictionary."""
        return dict(
            id=self.megaphone_id,
            name=self.name,
            insertion_point=self.insertion_point,
            pos=self.pos,
            audio_file=self.audio_file,
            campaign_order_megaphone_id=self.campaign_order_megaphone_id
        )


def create_or_update_advertisements(advertisements, order_id):
    """Create many advertisements.

    Args:
        advertisements (list of dicts): the advertisements to create or update.
    """
    with mysql.pod_db_session() as session:
        _create_advertisements(session, advertisements)
        _delete_old_advertisements(session, advertisements, order_id)


def _delete_old_advertisements(session, advertisements, order_id):
    advertisement_ids = [advertisement['id'] for advertisement in advertisements]
    bad_advertisements = session.query(Advertisement).filter(
        Advertisement.campaign_order_megaphone_id == order_id,
        Advertisement.megaphone_id.notin_(advertisement_ids)
    ).all()
    advertisement_ids = [ad.to_dict()['id'] for ad in bad_advertisements]

    ad_actions = ad_action_model.get_ad_actions_by_advertisement_ids(
        advertisement_ids)['items']
    ad_action_ids = [ad['advertisement_id'] for ad in ad_actions]

    ads_to_delete = [ad_id for ad_id in advertisement_ids if ad_id not in ad_action_ids]
    if ads_to_delete:
        session.query(Advertisement).filter(
            Advertisement.megaphone_id.in_(ads_to_delete)).delete(synchronize_session=False)


def _create_advertisements(session, advertisements):
    rows = []
    for advertisement in advertisements:
        pos = advertisement['pos']
        rows.append("('{}', '{}', {}, '{}', '{}', '{}')".format(
            escape_string(advertisement['id']),
            escape_string(advertisement['insertion_point']),
            int(pos) if pos else 'null',
            escape_string(advertisement['audio_file']),
            advertisement['order_id'],
            escape_string(advertisement['name'])
        ))
    if len(rows) == 0:
        return
    session.execute("""
    INSERT INTO campaign_advertisement (
      megaphone_id, insertion_point, pos, audio_file, campaign_order_megaphone_id, name
    ) VALUES
    {}
    ON DUPLICATE KEY UPDATE insertion_point=VALUES(insertion_point),
    pos=VALUES(pos), audio_file=VALUES(audio_file), campaign_order_megaphone_id=VALUES(campaign_order_megaphone_id),
    name=VALUES(name);
    """.format(','.join(rows)))
    session.commit()


def get_advertisements_by_megaphone_ids(ids):
    """Get advertisements by megaphone ids.

    Args:
        ids (list of strings): list of megaphone ids
    Returns:
        list (dict): advertisements
    """
    with mysql.pod_db_session(read_only=True) as session:
        network_ids = user_logic.network_ids_for_current_user()

        # If they don't have access at the network level, you can just check show level access specifically
        podcast_ids = user_logic.specific_podcast_ids_for_current_user()
        episode_ids = episode_model.get_episode_ids_by_podcast_ids(
            podcast_ids) if podcast_ids else []

        query = session.query(Advertisement, order_model.Order) \
            .filter((Advertisement.megaphone_id.in_(ids))) \
            .filter(Advertisement.campaign_order_megaphone_id == order_model.Order.megaphone_id) \
            .filter(sqlalchemy.or_(
                order_model.Order.networks.any(network_model.Network.id.in_(network_ids)),
                order_model.Order.podcasts.any(podcast_model.Podcast.id.in_(podcast_ids)),
                order_model.Order.episodes.any(episode_model.Episode.id.in_(episode_ids))
            )).all()
        return {'items': [row[0].to_dict() for row in query]}


def get_advertisements_by_order_ids(ids):
    """Get advertisements by order ids."""
    with mysql.pod_db_session(read_only=True) as session:
        advertisements = session.query(Advertisement).filter((Advertisement.campaign_order_megaphone_id.in_(ids))).all()
        return {'items': [advertisement.to_dict() for advertisement in advertisements]}