"""Ledger accounting run balance model."""

from typing import Tuple

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from abacus_common_logic.utils.features import is_feature_enabled
from flask import g
from sqlalchemy import and_, func, or_, select, text
from sqlalchemy.sql import literal_column, table

from ledger.constants.constants import (
    ACCOUNTING_RUN_STATUSES,
    VAT_APPLICABLE_COUNTRIES,
    VAT_CATEGORIES_OLD,
)
from ledger.models.reference_payment_entity import ReferencePaymentEntity


class LedgerAccountingRunBalance(BaseModel):
    """Ledger accounting run balance model."""

    __tablename__ = 'ledger_accounting_run_balance'
    ledger_accounting_run_balance_id = db.Column(db.Integer, primary_key=True)
    abacus_event_id = db.Column(db.Integer, nullable=False)
    accounting_run_id = db.Column(db.Integer, nullable=False)
    adjusted_net_revenue = db.Column(db.Numeric(20, 2), nullable=False)
    contract_id = db.Column(db.Integer, nullable=False)
    distribution_fee = db.Column(db.Numeric(20, 2), nullable=True)
    currency_code = db.Column(db.String(3), nullable=False)
    mechanical_deduction_admin_fee_total = db.Column(db.Numeric(20, 2), nullable=True)
    mechanical_deduction_total = db.Column(db.Numeric(20, 2), nullable=True)
    total_gross_revenue_amount = db.Column(db.Numeric(20, 2), nullable=True)
    total_net_revenue_amount = db.Column(db.Numeric(20, 2), nullable=True)

    @classmethod
    def find_by_accounting_run_id(
        cls, accounting_run_id: int, limit: int, offset: int
    ) -> Tuple[dict, int]:
        """Get ledger_accounting_run_balance list by accounting_run_id.

        Includes joins to account and contract tables to get account_name and
        contract_name, respectively, for the run summary export.

        Args:
            accounting_run_id (int): id of the parent accounting run
            limit (int): length of query result; the size of the page
            offset (int): number of rows to skip before returning result; the page num

        Returns:
            a tuple of result items and total count
        """
        query = (
            select(
                [
                    literal_column('larb.*'),
                    literal_column('a.account_id'),
                    literal_column('a.account_name'),
                    literal_column('c.contract_name'),
                ]
            )
            .where(
                and_(
                    literal_column('larb.contract_id')
                    == literal_column('ac.contract_id'),
                    literal_column('ac.account_id') == literal_column('a.account_id'),
                    literal_column('ac.contract_id') == literal_column('c.contract_id'),
                    literal_column('larb.accounting_run_id') == accounting_run_id,
                )
            )
            .order_by(literal_column('a.account_name').asc())
            .select_from(table('ledger_accounting_run_balance').alias('larb'))
            .select_from(table('account_contract').alias('ac'))
            .select_from(table('account').alias('a'))
            .select_from(table('contract').alias('c'))
            .distinct()
        )

        items = db.session.execute(query.limit(limit).offset(offset)).fetchall()
        total_count = db.session.execute(query).rowcount

        return items, total_count

    @classmethod
    def find_contract_count_by_accounting_run_ids(
        cls, accounting_run_ids: list[int]
    ) -> list:
        """Get ledger_accounting_run_balance contract_count by accounting_run_ids."""
        # Base filtered query
        ar = table('accounting_run').alias('ar')
        larb = table('ledger_accounting_run_balance').alias('larb')
        ac = table('account_contract').alias('ac')
        c = table('contract').alias('c')

        query = (
            select(
                [
                    literal_column('ar.accounting_run_id').label('accounting_run_id'),
                    func.count(literal_column('c.contract_id')).label('contract_count'),
                ]
            )
            .select_from(
                ar.outerjoin(
                    larb,
                    literal_column('larb.accounting_run_id')
                    == literal_column('ar.accounting_run_id'),
                )
                .outerjoin(
                    ac,
                    literal_column('ac.contract_id')
                    == literal_column('larb.contract_id'),
                )
                .outerjoin(
                    c,
                    literal_column('c.contract_id') == literal_column('ac.contract_id'),
                )
            )
            .where(literal_column('ar.accounting_run_id').in_(accounting_run_ids))
            .group_by(literal_column('ar.accounting_run_id'))
        )

        # Maintain same order as in accounting_run_ids, required for dataloader
        query = query.order_by(
            func.field(literal_column('ar.accounting_run_id'), *accounting_run_ids)
        )

        result = db.session.execute(query)
        keys = result.keys()
        rows = [dict(zip(keys, row)) for row in result.fetchall()]
        return rows

    @classmethod
    def get_by_accounting_period_and_vat_category(
        cls, accounting_period_id, vat_category, limit, offset
    ):
        """Get ledger_accounting_run_balance list by accounting period and vat category.

        Args:
            accounting_period_id (int): id of the parent accounting period
            vat_category (str): one of 'vat_applied' or 'vat_exempt'
            limit (int): number of list items to return; the size of a page
            offset (int): number of items to skip before returning results; the page num
        """
        query = (
            cls._query_by_period_id_and_vat_category(accounting_period_id, vat_category)
            .offset(offset)
            .limit(limit)
        )

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

    @classmethod
    def get_by_accounting_period_and_vat_category_count(
        cls, accounting_period_id, vat_category
    ):
        """Get total count of ledger_accounting_run_balance list.

        Query by accounting period and vat category.

        Args:
            accounting_period_id (int): id of the parent accounting period
            vat_category (str): one of 'vat_applied' or 'vat_exempt'
        """
        query = cls._query_by_period_id_and_vat_category(
            accounting_period_id, vat_category
        )

        return db.session.execute(query).rowcount

    # ************ #
    #   QUERIES:
    # ************ #

    @staticmethod
    def _query_by_period_id_and_vat_category(accounting_period_id, vat_category):
        """Build a query to get ledger_accounting_run_balance records.

        When vat_category is 'vat_exempt', return records that:
            - have "is_vat_exempt" value True
            OR
            - do NOT have "AWAL-UK" reference_payment_entity
            OR
            -  DO have "AWAL-UK" reference_payment_entity AND have a CTR that is NOT GBR

        When vat_category is 'vat_applied', return records that:
            - have "is_vat_exempt" value False
            - have "AWAL-UK" reference_payment_entity AND have a CTR of GBR

        Args:
            accounting_period_id (int): id of the parent accounting period
            vat_category (str): one of 'vat_applied' or 'vat_exempt'
        """
        filter_condition = ''
        awal_uk_payment_entity = ReferencePaymentEntity.get_payment_entity_by_name(
            'AWAL-UK'
        )
        awal_uk_payment_entity_id = awal_uk_payment_entity.reference_payment_entity_id
        if vat_category == VAT_CATEGORIES_OLD.VAT_EXEMPT:
            filter_condition = [
                or_(
                    literal_column('ati.is_vat_exempt').is_(True),
                    literal_column('apt.payment_entity_id')
                    != awal_uk_payment_entity_id,
                    and_(
                        literal_column('apt.payment_entity_id')
                        == awal_uk_payment_entity_id,
                        literal_column('ati.country_of_tax_residence')
                        != VAT_APPLICABLE_COUNTRIES.GBR,
                    ),
                )
            ]
        elif vat_category == VAT_CATEGORIES_OLD.VAT_APPLIED:
            filter_condition = [
                and_(
                    literal_column('ati.is_vat_exempt').is_(False),
                    literal_column('apt.payment_entity_id')
                    == awal_uk_payment_entity_id,
                    literal_column('ati.country_of_tax_residence')
                    == VAT_APPLICABLE_COUNTRIES.GBR,
                )
            ]
        run_balance_query = (
            select(
                [
                    literal_column('ati.is_vat_exempt').label('is_vat_exempt'),
                    literal_column('apt.payment_entity_id').label('payment_entity_id'),
                    literal_column('ati.country_of_tax_residence').label(
                        'country_of_tax_residence'
                    ),
                    text('larb.*'),
                ]
            )
            .where(
                and_(
                    literal_column('larb.accounting_run_id')
                    == literal_column('ar.accounting_run_id'),
                    literal_column('ac.contract_id')
                    == literal_column('larb.contract_id'),
                    literal_column('larb.contract_id')
                    == literal_column('c.contract_id'),
                    literal_column('apt.account_id') == literal_column('ac.account_id'),
                    literal_column('apt.account_id')
                    == literal_column('ati.account_id'),
                    literal_column('ar.accounting_period_id') == accounting_period_id,
                    literal_column('ar.run_status')
                    == ACCOUNTING_RUN_STATUSES.COMMITTED,
                    *filter_condition,
                )
            )
            .select_from(table('ledger_accounting_run_balance').alias('larb'))
            .select_from(table('accounting_run').alias('ar'))
            .select_from(table('account_contract').alias('ac'))
            .select_from(table('account_payment_term').alias('apt'))
            .select_from(table('account_tax_info').alias('ati'))
            .select_from(table('contract').alias('c'))
        )

        return run_balance_query