"""Category Model.

This model represents a Category
"""


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

from podcast.connectors import mysql


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

    __tablename__ = 'category'

    id = sqlalchemy.Column(INTEGER(unsigned=True), primary_key=True, autoincrement=True)  # noqa
    parent_id = sqlalchemy.Column(
        INTEGER,
        ForeignKey('category.id'),
        nullable=True
    )
    name = sqlalchemy.Column(sqlalchemy.VARCHAR(255), nullable=False)
    active = sqlalchemy.Column(sqlalchemy.Boolean, nullable=False)

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


def get_categories():
    """Return all the categories for a podcast.

    Returns:
        response.Response: containing the categories.
    """
    with mysql.pod_db_session(read_only=True) as session:
        query = session.query(Category).filter(
            Category.active.is_(True)
        )
        items = [row.to_dict() for row in query]

        return {'items': items}


def get_categories_by_ids(ids):
    """Return all the categories for a podcast.

    Args:
        ids (list): List of category ids
    Returns:
        response.Response: containing the categories
        ordered by the list of the ids.
    """
    with mysql.pod_db_session(read_only=True) as session:
        filters = [(Category.id.in_(ids))]
        query = session.query(Category).filter(*filters)
        items = [row.to_dict() for row in query]

        sorted_items = [item['name'] for item in sorted(
            items, key=lambda item: ids.index(item['id']))]

        return {'items': sorted_items}