"""ReferencePaymentEntity Model.

It is a READ ONLY model for accessing data.
The real business logic lives in the ows-abacus-contract repository.
"""

import typing
from dataclasses import dataclass
from datetime import datetime

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'),
                ]
            )
            .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'),
                )
            )
            .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)