"""ReferencePaymentEntity Model.

It is a READ ONLY model for accessing data.
The real business logic lives in the ows-abacus-contract repository.
"""
from dataclasses import dataclass
from datetime import datetime
import typing

from abacus_common_logic.connectors.database import db
from sqlalchemy import func, outerjoin, select, text
from sqlalchemy.sql import literal_column, table


@dataclass
class ReferencePaymentEntity:
    """ReferencePaymentEntity Model."""

    reference_payment_entity_id: int
    payment_entity_name: str
    country_of_tax_reporting: str
    created_by: str
    created_at: datetime
    last_modified_by: str
    last_modified: datetime

    @classmethod
    def get_knr_payment_entities(cls):
        """Get KNR payment entities."""
        query = select([literal_column('rpe.*')]) \
            .where(text(
                'rpe.payment_entity_name like "%KNR%"'
            )).select_from(table('reference_payment_entity').alias('rpe'))

        return db.session.execute(query).fetchall()

    @classmethod
    def get_payment_entity_payoneer_programs(cls, limit, offset) -> tuple:
        """Get all payment entity payoneer programs.

        Arg:
            limit (int): pagination limit
            offset (int): pagination offset
        Returns:
            A tuple containing items and total count
        """
        query = cls.query_get_payment_entity_payoneer_programs()
        items = db.session.execute(
            query.limit(limit).offset(offset)
        ).fetchall()
        total_count = db.session.execute(query).rowcount
        return items, total_count

    @staticmethod
    def query_get_payment_entity_payoneer_programs():
        """Build a query to get payment entity payoneer programs."""
        return select([
            literal_column('pepp.payoneer_program_id').label('payoneer_program_id'),
            literal_column('rpe.reference_payment_entity_id').label('reference_payment_entity_id'),  # noqa: E501
            func.group_concat(
                literal_column('rat.agreement_type').distinct()
                .op('ORDER BY')(literal_column('rat.agreement_type'))
            ).label('agreement_type'),
            func.group_concat(
                literal_column('rat.reference_agreement_type_id').distinct()
                .op('ORDER BY')(literal_column('rat.reference_agreement_type_id'))
            ).label('reference_agreement_type_id'),
            func.group_concat(
                literal_column('pepp.payment_currency').distinct()
                .op('ORDER BY')(literal_column('pepp.payment_currency'))
            ).label('payment_currency'),
            literal_column('rpp.reference_payment_type_id').label('reference_payment_type_id'),  # noqa: E501
            literal_column('rpt.payment_service').label('payment_service'),
        ]).select_from(outerjoin(
            table('reference_payment_entity').alias('rpe'),
            table('payment_entity_payoneer_program').alias('pepp'),
            text('rpe.reference_payment_entity_id=pepp.reference_payment_entity_id')
        ).outerjoin(
            table('reference_agreement_type').alias('rat'),
            text('rat.reference_agreement_type_id=pepp.agreement_type_id')
        ).outerjoin(
            table('reference_payoneer_program').alias('rpp'),
            text('rpp.payoneer_program_id=pepp.payoneer_program_id')
        ).outerjoin(
            table('reference_payment_type').alias('rpt'),
            text('rpt.reference_payment_type_id=rpp.reference_payment_type_id')
        )).group_by(
            literal_column('rpe.reference_payment_entity_id'),
            literal_column('pepp.payoneer_program_id')
        ).order_by(
            literal_column('rpe.reference_payment_entity_id')
        )

    @classmethod
    def get_by_id(
        cls, reference_payment_entity_id: int
    ) -> typing.Optional['ReferencePaymentEntity']:
        """Get payment entity by ID."""
        query = (
            select(
                # list all fields here in explicit way to ensure
                # nothing will brake with DB PR adding more columns
                [
                    literal_column('reference_payment_entity_id'),
                    literal_column('payment_entity_name'),
                    literal_column('country_of_tax_reporting'),
                    literal_column('created_by'),
                    literal_column('created_at'),
                    literal_column('last_modified_by'),
                    literal_column('last_modified'),
                ]
            )
            .where(text('reference_payment_entity_id = :entity_id'))
            .select_from(table('reference_payment_entity'))
        )

        result = db.session.execute(
            query, dict(entity_id=reference_payment_entity_id)).fetchone()

        if result:
            return cls(**result)