"""Ledger Accounting Run VAT model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from sqlalchemy import and_, or_, select, text
from sqlalchemy.sql import literal_column, table

from ledger.constants.constants import VAT_CATEGORIES_OLD

GET_ACCOUNTING_RUN_VAT_OVERVIEW = """
    SELECT
        COUNT(vat.contract_id) AS contract_count,
        'vat_exempt' AS vat_category,
        NULL AS country_of_tax_residence
    FROM ledger_accounting_run_vat AS vat
        INNER JOIN accounting_run AS ar ON vat.accounting_run_id = ar.accounting_run_id
    WHERE ar.accounting_period_id = {accounting_period_id}
        AND (
          vat.exempt_reason IS NOT NULL
          OR (
            vat.gross_revenue IS NULL
            AND vat.net_revenue IS NULL
            AND vat.distribution_fee IS NULL
            AND vat.gross_vat IS NULL
            AND vat.distribution_vat IS NULL
            AND vat.adjusted_net_revenue IS NULL
            )
          )
    HAVING contract_count > 0

    UNION

    SELECT
        COUNT(vat.contract_id) AS contract_count,
        'vat_applied' AS vat_category,
        vat.country_of_tax_residence
    FROM ledger_accounting_run_vat AS vat
        INNER JOIN accounting_run AS ar ON vat.accounting_run_id = ar.accounting_run_id
    WHERE ar.accounting_period_id = {accounting_period_id}
        AND (
          vat.exempt_reason IS NULL
          AND (
            vat.gross_revenue IS NOT NULL
            OR vat.net_revenue IS NOT NULL
            OR vat.distribution_fee IS NOT NULL
            OR vat.gross_vat IS NOT NULL
            OR vat.distribution_vat IS NOT NULL
            OR vat.adjusted_net_revenue IS NOT NULL
            )
          )

    GROUP BY vat_category, country_of_tax_residence
"""


class LedgerAccountingRunVat(BaseModel):
    """Ledger accounting run vat model."""

    __tablename__ = 'ledger_accounting_run_vat'
    ledger_accounting_run_vat_id = db.Column(db.Integer, primary_key=True)
    accounting_run_id = db.Column(db.Integer, nullable=False)
    abacus_event_id = db.Column(db.Integer, nullable=False)
    contract_id = db.Column(db.Integer, nullable=False)
    currency_code = db.Column(db.String(3), nullable=False)
    country_of_tax_residence = db.Column(db.String(3), nullable=False)
    gross_revenue = db.Column(db.Numeric(20, 2), nullable=True)
    net_revenue = db.Column(db.Numeric(20, 2), nullable=True)
    distribution_fee = db.Column(db.Numeric(20, 2), nullable=True)
    gross_vat_rate = db.Column(db.Numeric(5, 2), nullable=True)
    distribution_vat_rate = db.Column(db.Numeric(5, 2), nullable=True)
    gross_vat = db.Column(db.Numeric(20, 2), nullable=True)
    distribution_vat = db.Column(db.Numeric(20, 2), nullable=True)
    adjusted_net_revenue = db.Column(db.Numeric(20, 2), nullable=True)
    exempt_reason = db.Column(db.String(180), nullable=True)

    @classmethod
    def get_ledger_accounting_run_vat_overview(cls, accounting_period_id):
        """Get ledger_accounting_run_vat list by accounting_period_id."""
        sql_result = db.session.execute(
            GET_ACCOUNTING_RUN_VAT_OVERVIEW.format(
                accounting_period_id=accounting_period_id
            )
        ).fetchall()

        return sorted(
            sql_result,
            key=lambda result: result.country_of_tax_residence or '',
            reverse=True,
        )

    @classmethod
    def get_by_accounting_period_id(cls, accounting_period_id):
        """Get ledger_accounting_run_vat list by accounting_period_id."""
        query = (
            select([literal_column('run.accounting_run_id').label('accounting_run_id')])
            .where(text('run.accounting_period_id = :accounting_period_id'))
            .select_from(table('accounting_run').alias('run'))
        )
        params = {'accounting_period_id': accounting_period_id}
        result = db.session.execute(query, params).fetchall()
        accounting_run_ids = [obj.accounting_run_id for obj in result]
        return cls.query.filter(cls.accounting_run_id.in_(accounting_run_ids)).all()

    @property
    def vat_category(self):
        """Get VAT category."""
        if not any(
            [
                self.gross_revenue,
                self.net_revenue,
                self.distribution_fee,
                self.gross_vat,
                self.distribution_vat,
                self.adjusted_net_revenue,
            ]
        ):
            return VAT_CATEGORIES_OLD.VAT_EXEMPT
        return VAT_CATEGORIES_OLD.VAT_APPLIED

    @staticmethod
    def _query_by_period_id_and_vat_category(vat_category):
        """Build a query to get ledger_accounting_run_vat list by period id and vat category.

        Includes a join to 'accounting_run' to returns vat list by accounting period id.

        Returns a list of ledger_accounting_run_vat
        """
        filter_condition = ''
        if vat_category == VAT_CATEGORIES_OLD.VAT_EXEMPT:
            filter_condition = [
                or_(
                    literal_column('lcrv.exempt_reason').isnot(None),
                    and_(
                        literal_column('lcrv.gross_revenue').is_(None),
                        literal_column('lcrv.net_revenue').is_(None),
                        literal_column('lcrv.distribution_fee').is_(None),
                        literal_column('lcrv.gross_vat').is_(None),
                        literal_column('lcrv.distribution_vat').is_(None),
                        literal_column('lcrv.adjusted_net_revenue').is_(None),
                    ),
                )
            ]
        elif vat_category == VAT_CATEGORIES_OLD.VAT_APPLIED:
            filter_condition = [
                text('lcrv.country_of_tax_residence = :country_of_tax_residence'),
                literal_column('lcrv.exempt_reason').is_(None),
                or_(
                    literal_column('lcrv.gross_revenue').isnot(None),
                    literal_column('lcrv.net_revenue').isnot(None),
                    literal_column('lcrv.distribution_fee').isnot(None),
                    literal_column('lcrv.gross_vat').isnot(None),
                    literal_column('lcrv.distribution_vat').isnot(None),
                    literal_column('lcrv.adjusted_net_revenue').isnot(None),
                ),
            ]

        vat_list_query = (
            select(
                [
                    literal_column('ar.accounting_period_id').label(
                        'accounting_period_id'
                    ),
                    literal_column('a.account_id').label('account_id'),
                    literal_column('a.account_name').label('account_name'),
                    literal_column('c.contract_name').label('contract_name'),
                    text('lcrv.*'),
                ]
            )
            .where(
                and_(
                    literal_column('ar.accounting_run_id')
                    == literal_column('lcrv.accounting_run_id'),
                    literal_column('c.contract_id')
                    == literal_column('lcrv.contract_id'),
                    literal_column('c.contract_id') == literal_column('ac.contract_id'),
                    literal_column('ac.account_id') == literal_column('a.account_id'),
                    text('ar.accounting_period_id = :accounting_period_id'),
                    *filter_condition,
                )
            )
            .order_by(literal_column('a.account_name').asc())
            .select_from(table('ledger_accounting_run_vat').alias('lcrv'))
            .select_from(table('accounting_run').alias('ar'))
            .select_from(table('contract').alias('c'))
            .select_from(table('account_contract').alias('ac'))
            .select_from(table('account').alias('a'))
        )

        return vat_list_query

    @classmethod
    def get_ledger_vat_list(
        cls, accounting_period_id, vat_category, country_code, limit, offset
    ):
        """Get list of ledger_accounting_run_vat for an accounting period by vat category and country code."""
        query = (
            cls._query_by_period_id_and_vat_category(vat_category)
            .offset(offset)
            .limit(limit)
            .distinct()
        )

        return db.session.execute(
            query,
            {
                'accounting_period_id': accounting_period_id,
                'country_of_tax_residence': country_code,
            },
        ).fetchall()

    @classmethod
    def get_ledger_vat_list_count(
        cls, accounting_period_id, vat_category, country_code
    ):
        """Get total count of ledger_accounting_run_vat for an accounting period by vat category and country code."""
        query = cls._query_by_period_id_and_vat_category(vat_category).distinct()

        return db.session.execute(
            query,
            {
                'accounting_period_id': accounting_period_id,
                'country_of_tax_residence': country_code,
            },
        ).rowcount