"""Network Model.

This model represents a Network
"""

import sqlalchemy

from podcast import config
from podcast.connectors import mysql
from podcast.constants import api
from podcast.constants import error
from podcast.utils.exc import OwsError


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

    __tablename__ = 'network'

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)  # noqa
    name = sqlalchemy.Column(sqlalchemy.VARCHAR(255))
    is_sony = sqlalchemy.Column(sqlalchemy.Boolean, nullable=False, default=False)
    megaphone_id = sqlalchemy.Column(sqlalchemy.VARCHAR(50))

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


def get_networks():
    """Get a list of networks.

    Returns:
        list: a list of available networks.
    """
    with mysql.pod_db_session(read_only=True) as session:
        query = session.query(Network)

        rows = query.all()
        items = [row.to_dict() for row in rows]

        return {'items': items}


def create_network(name, megaphone_id, is_sony):
    """Create a network."""
    with mysql.pod_db_session() as session:
        network = Network(name=name, megaphone_id=megaphone_id, is_sony=is_sony)
        session.add(network)
        session.flush()
        return network.to_dict()


def delete_network(megaphone_id):
    """Delete a network."""
    with mysql.pod_db_session() as session:
        session.query(Network).filter(Network.megaphone_id == megaphone_id).delete()


def get_networks_by_ids(network_ids):
    """Get a network.

    Returns:
        list: a list of available networks.
    """
    with mysql.pod_db_session(read_only=True) as session:
        rows = session.query(Network).filter(
            Network.id.in_(network_ids),
        ).all()
        items = [row.to_dict() for row in rows]
        return {'items': items}


def get_networks_show_and_episode_count(network_ids, podcast_ids):
    """Get the show and episode count for network ids."""
    with mysql.pod_db_session(read_only=True) as session:
        sql = """
        SELECT n.id, (
            SELECT count(*) from podcast
            where network_id = n.id and is_deleted = 0 and podcast.id in :podcast_ids
        ) as num_shows,
        COUNT(e.id) from network as n
        JOIN podcast as p on p.network_id = n.id and p.is_deleted = 0
        JOIN episode as e on e.podcast_id = p.id and e.is_deleted = 0 AND e.draft = 0
        WHERE n.id in :network_ids
        AND p.id in :podcast_ids
        GROUP BY n.id;
        """
        if config.IS_TEST_ENV:
            string_ids = ','.join([str(network_id) for network_id in network_ids])
            sql = sql.replace(':network_ids', '({})'.format(string_ids))
            string_pod_ids = ','.join([str(podcast_id) for podcast_id in podcast_ids])
            sql = sql.replace(':podcast_ids', '({})'.format(string_pod_ids))

        results = session.execute(sql, {
            'network_ids': network_ids, 'podcast_ids': podcast_ids}).fetchall()
        network_id_to_counts = {}
        for result in results:
            network_id_to_counts[result[0]] = (result[1], result[2])
        return network_id_to_counts


def get_megaphone_url(network_id):
    """Get a the Megaphone API URL by network id.

    Returns:
        string: API URL.
    """
    with mysql.pod_db_session(read_only=True) as session:
        network = session.query(Network).get(network_id)
        if not network:
            raise OwsError.not_found(error.ERROR_NETWORK_NOT_FOUND)
        return api.MEGAPHONE_API_BASE_URL.format(network.megaphone_id)


def search_networks(name, network_ids):
    """Return all the networks where the names match the search query.

    Args:
        name: search text to query the networks on.
        networks_ids: network ids user has access too
    Returns:
        list: list of networks
    """
    with mysql.pod_db_session(read_only=True) as session:
        query = session.query(Network)
        query = query.filter(
            Network.id.in_(network_ids),
            Network.name.ilike(f'%{name}%', escape='/')
        )
        query = query.order_by(Network.name)

        rows = query.all()

        return [row.to_dict() for row in rows]