"""Model for template_details table in salessheets database."""
from oto import response
from sqlalchemy import Column
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import TIMESTAMP
from sqlalchemy.exc import SQLAlchemyError

from salessheets.connectors import mysql
from salessheets.connectors.sentry import sentry_capture_exception
from salessheets.constants import errors


class TemplateDetailsSalesRegion(mysql.BaseModel):
    """Model for template_details_sales_region table."""

    __tablename__ = 'template_details_sales_region'

    tdsr_id = Column(
        'id', Integer, primary_key=True, autoincrement=True)
    template_details_id = Column(Integer(), nullable=False)
    region_id = Column(Integer(), nullable=False)
    timestamp_created = Column(TIMESTAMP, nullable=False)

    def as_dict(self):
        """Return row as dict.

        Returns:
            dict: Dictionary representation of row.
        """
        template_detail_dict = {
            'id': self.tdsr_id,
            'template_details_id': self.template_details_id,
            'region_id': self.region_id,
            'timestamp_created': self.timestamp_created
        }
        return template_detail_dict


class TemplateDetail(mysql.BaseModel):
    """Class representing the salessheets localized template detail."""

    __tablename__ = 'template_details'

    localized_template_type_id = Column(
        'id', Integer, primary_key=True, autoincrement=True)
    name = Column(String(127), unique=True)
    display_name = Column(String(1000))
    store_id = Column(Integer)
    email = Column(String(1000), nullable=True)
    street = Column(String(1000), nullable=True)
    apartment = Column(String(1000), nullable=True)
    city = Column(String(1000), nullable=True)
    state = Column(String(1000), nullable=True)
    country = Column(String(1000), nullable=True)
    postal_code = Column(String(1000), nullable=True)
    telephone = Column(String(1000), nullable=True)

    def as_dict(self):
        """Return object as dict.

        Returns:
            dict: Dictionary representation of object
        """
        template_detail_dict = {
            'localized_template_type_id': self.localized_template_type_id,
            'name': self.name,
            'display_name': self.display_name,
            'store_id': self.store_id,
            'email': self.email,
            'street': self.street,
            'apartment': self.apartment,
            'city': self.city,
            'state': self.state,
            'country': self.country,
            'postal_code': self.postal_code,
            'telephone': self.telephone
        }
        return template_detail_dict


def get_by_template_type_id(localized_template_type_id):
    """Get sales sheets template details from db by localized_template_type_id.

    Args:
        localized_template_type_id (int): id of template.

    Returns:
        response.Response: .message with TemplateDetail.to_dict() on success,
            .errors on failure.
    """
    try:
        with mysql.salessheets_history_session_scope() as session:
            template_detail = (
                session.query(
                    TemplateDetail,
                    (func.group_concat(TemplateDetailsSalesRegion.region_id))
                    .label('regions'))
                .join(
                    TemplateDetailsSalesRegion,
                    (TemplateDetail.localized_template_type_id ==
                     TemplateDetailsSalesRegion.template_details_id))
                .filter(
                    (TemplateDetail.localized_template_type_id ==
                     localized_template_type_id))
                .group_by(TemplateDetail.localized_template_type_id)
            ).first()
        if not template_detail:
            return response.create_not_found_response(
                errors.ERROR_MESSAGE_TEMPLATE_TYPE_DOES_NOT_EXIST.format(
                    id=localized_template_type_id))
    except SQLAlchemyError as e:
        if sentry_capture_exception:
            sentry_capture_exception()
        return response.create_fatal_response(e.args)

    template_info = template_detail[0].as_dict()
    template_info['regions'] = [
        int(r) for r in template_detail[1].split(',')]

    return response.Response(template_info)