"""Show Family Model.

This model represents Show Family
"""

import sqlalchemy
from sqlalchemy import ForeignKey
from sqlalchemy.dialects.mysql import INTEGER
from sqlalchemy.sql import func

from podcast.connectors import mysql
from podcast.constants import error
from podcast.utils import api_utils
from podcast.utils import exc


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

    __tablename__ = 'show_family'

    id = sqlalchemy.Column(INTEGER(unsigned=True), primary_key=True, autoincrement=True)  # noqa
    title = sqlalchemy.Column(sqlalchemy.VARCHAR(255))
    network_id = sqlalchemy.Column(
        INTEGER(unsigned=True),
        ForeignKey('network.id'),
        nullable=False
    )
    created_by = sqlalchemy.Column(sqlalchemy.Integer)
    updated_by = sqlalchemy.Column(sqlalchemy.Integer)
    created_date = sqlalchemy.Column(sqlalchemy.DateTime, default=func.now())
    updated_date = sqlalchemy.Column(
        sqlalchemy.DateTime, default=func.now(), onupdate=func.now())
    is_deleted = sqlalchemy.Column(
        sqlalchemy.Boolean, nullable=False, default=False)

    def to_dict(self):
        """Return the object as dictionary."""
        return dict(
            id=self.id,
            title=self.title,
            network_id=self.network_id,
            created_by=self.created_by,
            updated_by=self.updated_by,
            created_date=self.created_date,
            updated_date=self.updated_date,
            is_deleted=self.is_deleted
        )


def get_show_family_by_id(show_family_id):
    """Return a show_family by id.

    Args:
        show_family_id (int): The show_family unique identifier.

    Returns:
        dict: containing the show family.
    """
    with mysql.pod_db_session(read_only=True) as session:
        show_family = session.query(ShowFamily).filter(
            ShowFamily.id == show_family_id,
            ShowFamily.is_deleted.isnot(True)
        ).first()

        if not show_family:
            raise exc.OwsError.not_found(error.ERROR_MESSAGE_SHOW_FAMILY_NOT_FOUND)

        return show_family.to_dict()


def create_show_family(data, session=None):
    """Create a show_family.

    Args:
        data (dict): the data from which to create a show family.

    Returns:
        dict: containing the created show family.
    """
    show_family = ShowFamily(**data)
    if session:
        session.add(show_family)
        session.flush()
    else:
        with mysql.pod_db_session() as session:
            session.add(show_family)
    return show_family.to_dict()


def get_show_family_ids_by_network_ids(network_ids):
    """Return show family ids by network_ids.

    Args:
        network_ids (List of int): List of networks ids.

    Returns:
        List of int: List of show family ids.
    """
    with mysql.pod_db_session(read_only=True) as session:
        show_families = session.query(ShowFamily.id).filter(
            ShowFamily.network_id.in_(network_ids),
            ShowFamily.is_deleted.isnot(True)
        )
        return [show_family.id for show_family in show_families]


def get_show_families(limit=0, offset=0, show_family_ids=None):
    """Return all the show families by show_family_ids.

    If show_family_ids then filters by show_family_ids else query none.
    Ordered by show_family title and paginated by limit and offset.

    Args:
        limit (int): how many show families to retrieve.
        offset (int): the offset (for pagination).
        show_family_ids (list of int): ids to fetch show families.

    Returns:
        dict: containing the paginated show families.
    """
    with mysql.pod_db_session(read_only=True) as session:
        query = session.query(ShowFamily).filter(ShowFamily.is_deleted.isnot(True))

        if show_family_ids:
            query = query.filter(ShowFamily.id.in_(show_family_ids))
        else:
            query = query.filter(None)

        query = query.order_by(ShowFamily.title)
        limited_query = query.offset(offset)

        if limit != 0:
            limited_query = limited_query.limit(limit).offset(offset)

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

        return {
            'items': items,
            'pagination': {
                'total_records': total_records
            }
        }


def delete_show_family(show_family_id, session):
    """Delete a show family.

    Args:
        show_family_id (int): the show family id.
        session (sqlalchemy.session.Session): live connection to database
    Returns:
        dict: containing the soft deleted show_family dict.
    """
    show_family = session.query(ShowFamily).get(show_family_id)

    if not show_family:
        raise exc.OwsError.not_found(error.ERROR_SHOW_FAMILY_NOT_FOUND)

    show_family.updated_by = api_utils.get_user_id()
    show_family.is_deleted = True
    return show_family.to_dict()


def update_show_family(show_family_id, data, session):
    """Update a show family.

    Args:
        show_family_id (int): the show family id.
        data (dict): containing the attributes to be updated.
        session (sqlalchemy.session.Session): live connection to database
    Returns:
        dict: containing the updated show_family dict.
    """
    query = session.query(ShowFamily).filter(ShowFamily.id == show_family_id)
    show_family = query.first()

    if not show_family:
        raise exc.OwsError.not_found(error.ERROR_SHOW_FAMILY_NOT_FOUND)

    data['updated_by'] = api_utils.get_user_id()
    query.update(data)

    result = query.first()

    return result.to_dict()