"""Statement Attachment model."""

from datetime import datetime
from datetime import timedelta

from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import Enum
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import JSON
from sqlalchemy import literal_column
from sqlalchemy import String
from sqlalchemy import table

from moneyhub.connectors.mysql import db
from moneyhub.constants.constants import NumberFormat
from moneyhub.constants.constants import StatementAttachmentFailureReason
from moneyhub.constants.constants import StatementAttachmentFileType
from moneyhub.constants.constants import StatementAttachmentStatus
from moneyhub.constants.constants import StatementAttachmentType
from moneyhub.constants.constants import SYSTEM_TIMEZONE
from moneyhub.models import Account
from moneyhub.models import AccountPaymentTerm
from moneyhub.models.mysql_base import BaseModel


class StatementAttachment(BaseModel):
    """Statement attachments model."""

    __tablename__ = 'statement_attachment'

    statement_attachment_id = Column(Integer, primary_key=True)
    account_id = Column(Integer, nullable=False)
    subaccount_id = Column(Integer, nullable=True)
    contract_id = Column(Integer, nullable=True)
    statement_period_id = Column(Integer, nullable=False)
    statement_period_ids = Column(String(255), nullable=True)
    statement_attachment_status = Column(
        Enum(
            *StatementAttachmentStatus,
            name='statement_attachment_status',
            create_type=False
        ),
        nullable=False
    )
    failure_reason = Column(
        Enum(
            *StatementAttachmentFailureReason,
            name='failure_reason',
            create_type=False
        ),
        nullable=True
    )
    filters = Column(JSON(none_as_null=True), nullable=True)
    file_type = Column(
        Enum(
            *StatementAttachmentFileType,
            name='file_type',
            create_type=False
        ),
        nullable=False
    )
    number_format = Column(
        Enum(
            *NumberFormat,
            name='number_format',
            create_type=False
        ),
        nullable=False,
        default=NumberFormat.US
    )
    statement_attachment_type = Column(
        Enum(
            *StatementAttachmentType,
            name='statement_attachment_type',
            create_type=False
        ),
        nullable=False
    )
    invoice_number = Column(String(180), nullable=True)
    file_location = Column(String(255), nullable=True)
    created_at = Column(DateTime, nullable=False)
    created_by = Column(String(255), nullable=False, server_default='')

    def __init__(self, *args, **kwargs):
        """Initialize model with default values."""
        date_now = datetime.now(SYSTEM_TIMEZONE)
        self.created_at = date_now
        kwargs.setdefault('number_format', NumberFormat.US)
        super().__init__(*args, **kwargs)

    @classmethod
    def delete_by_id(cls, statement_attachment_id: int):
        """Delete an attachment based on its ID.

        Args:
            statement_attachment_id (int): ID of the attachment to delete.
        Returns:
            dict: Deleted attachment.
        """
        obj = cls.get_by_id_or_error(statement_attachment_id)
        db.session.delete(obj)
        db.session.commit()

        return obj

    @classmethod
    def get_by_statement_period(
        cls,
        statement_period_id: int,
        account_id: int | None = None,
        contract_id: int | None = None,
        statuses: list[str] | None = None,
        types: list[str] | None = None,
        subaccount_id: int | None = None,
    ) -> list:
        """Get statement attachments by statement period and other filters.

        Args:
            statement_period_id (int): The id of statement period
            account_id (int): Optional account to filter by
            contract_id (int): Optional contract to filter by
            statuses (list): Optional list of statuses to filter by
            types (list): Optional list of types to filter by
            subaccount_id (int): Optional id of the subaccount
        Returns:
            list: list of statement attachments
        """
        filters = [
            (StatementAttachment.statement_period_id == statement_period_id)
        ]

        if account_id is not None:
            filters.append(StatementAttachment.account_id == account_id)
            filters.append(StatementAttachment.subaccount_id == subaccount_id)
        if contract_id:
            filters.append(StatementAttachment.contract_id == contract_id)
        if statuses:
            filters.append(StatementAttachment.statement_attachment_status.in_(statuses))
        if types:
            filters.append(StatementAttachment.statement_attachment_type.in_(types))

        return cls.query.filter(*filters).all()

    @classmethod
    def get_by_account_id_and_statement_periods(
            cls,
            account_id: int,
            contract_id: int | None = None,
            statement_period_ids: list | None = None,
            subaccount_id: int | None = None,
    ) -> list:
        """Get statement attachments by statement periods, account and contract.

        Args:
            statement_period_ids (list): The ids of the statement periods
            account_id (int):  account to filter by
            contract_id (int):  Optional contract to filter by
            subaccount_id (int): Optional id of the subaccount
        Returns:
            list: list of statement attachments
        """
        filters = [
            StatementAttachment.account_id == account_id,
            StatementAttachment.subaccount_id == subaccount_id
        ]

        if contract_id:
            filters.append(StatementAttachment.contract_id == contract_id)
        if statement_period_ids:
            filters.append(StatementAttachment.statement_period_id.in_(statement_period_ids))

        return cls.query.filter(*filters).all()

    @classmethod
    def get_latest_account_self_billing_invoice_number(
        cls,
        year: int,
        account_ids: list[int]
    ) -> list:
        """Get the latest self-billing invoice number per account for a given year.

        Args:
            year (int): Year to fetch invoice numbers for.
            account_ids (list): Account IDs to filter by.
        Returns:
            list: Account ID and accompanying invoice numbers
        """
        filters = [
            cls.statement_attachment_type == StatementAttachmentType.SELF_BILLING_INVOICE,
            cls.invoice_number.regexp_match(f'^(\\d+)_{year}_(\\d+)_(\\d+)$')
        ]
        if account_ids:
            filters.append(cls.account_id.in_(account_ids))

        return cls.query.with_entities(
            cls.account_id,
            func.max(cls.invoice_number).label('invoice_number'),
        ).filter(
            *filters
        ).group_by(
            cls.account_id,
        ).all()

    @classmethod
    def get_latest_statement_attachments_invoices(cls, current_year: int) -> list:
        """Get latest statement attachment by current year for each attachment type and sap_id.

        Args:
            current_year (int): The year the attachment was generated.
        Returns:
            list: a list of the latest statement attachments.
        """
        subquery = cls.query.with_entities(
            func.max(StatementAttachment.invoice_number).label('invoice_number')
        ).filter(
            func.year(cls.created_at) == current_year
        ).group_by(
            StatementAttachment.statement_attachment_type,
            func.substring_index(
                func.substring_index(StatementAttachment.invoice_number, '_', 2),
                ',', -1
            )
        ).subquery()

        return cls.query.with_entities(
            cls.statement_attachment_type,
            cls.invoice_number,
            cls.created_at,
            func.substring_index(
                func.substring_index(cls.invoice_number, '_', 1),
                ',', -1
            ).label('sap_id')
        ).join(
            subquery, cls.invoice_number == subquery.c.invoice_number
        ).all()

    @classmethod
    def get_invoices_by_payment_entity(
        cls,
        payment_entity_id: int,
        statement_period_id: int,
    ):
        """GET invoice statement attachments for a specified payment entity id and statement period.

        Args:
            payment_entity_id (int): The id of a payment entity
            statement_period_id (int): the id of a statement period
        Returns:
            list: list of invoice statements attachments
        """
        DISTRIBUTION_FEE_INVOICE = StatementAttachmentType.DISTRIBUTION_FEE_INVOICE
        SELF_BILLING_INVOICE = StatementAttachmentType.SELF_BILLING_INVOICE

        filter_condition = \
            [literal_column('account_payment_term.payment_entity_id') == payment_entity_id]

        return cls.query.with_entities(
            cls.statement_attachment_id,
            cls.account_id,
            literal_column('account.account_name').label('account_name'),
            cls.statement_period_id,
            cls.contract_id,
            cls.statement_attachment_status,
            cls.failure_reason,
            cls.statement_attachment_type,
            cls.invoice_number,
            cls.file_location,
            cls.file_type,
            cls.number_format,
            cls.created_at,
            cls.created_by
        )\
            .join(
                Account,
                Account.account_id == cls.account_id
        )\
            .join(
                AccountPaymentTerm,
                AccountPaymentTerm.account_id == cls.account_id
        )\
            .filter(
                cls.statement_period_id == statement_period_id,
                cls.statement_attachment_type.in_([DISTRIBUTION_FEE_INVOICE, SELF_BILLING_INVOICE]),
                *filter_condition
        ).all()

    @classmethod
    def get_failed_invoices_by_payment_entity(
            cls, payment_entity_id: int, statement_period_id: int):
        """GET failed invoice statement attachments for a payment entity and statement period.

        Args:
            payment_entity_id (int): The id of a payment entity
            statement_period_id (int): the id of a statement period
        Returns:
            list: list of invoice statements attachments that failed
        """
        DISTRIBUTION_FEE_INVOICE = StatementAttachmentType.DISTRIBUTION_FEE_INVOICE
        SELF_BILLING_INVOICE = StatementAttachmentType.SELF_BILLING_INVOICE
        STATUS_ERROR = StatementAttachmentStatus.ERROR

        filter_condition = \
            [literal_column('account_payment_term.payment_entity_id') == payment_entity_id]

        return cls.query \
            .join(
                table('account_payment_term'),
                literal_column('account_payment_term.account_id') == cls.account_id
            ) \
            .filter(
                cls.statement_period_id == statement_period_id,
                cls.statement_attachment_type.in_([DISTRIBUTION_FEE_INVOICE, SELF_BILLING_INVOICE]),
                cls.statement_attachment_status == STATUS_ERROR,
                *filter_condition
            ).all()

    @classmethod
    def get_stuck_in_progress(cls):
        """Get statement attachments stuck "in progress".

        Returns:
            list: list of statement attachments
        """
        STATUS_IN_PROGRESS = StatementAttachmentStatus.IN_PROGRESS
        one_hour_ago = datetime.utcnow() - timedelta(hours=1)

        return cls.query \
            .filter(
                cls.statement_attachment_status == STATUS_IN_PROGRESS,
                cls.created_at < one_hour_ago
            ).all()

    @classmethod
    def exists(
        cls,
        account_id: int | None = None,
        statement_period_id: int | None = None,
        file_location: str | None = None,
        number_format: str | None = None,
        contract_id: int | None = None,
        attachment_type: StatementAttachmentType | None = None,
        subaccount_id: int | None = None,
        statement_period_ids: str | None = None,
        statement_attachment_id: int | None = None,
    ) -> bool:
        """Check if a statement attachment already exists.

        Args:
            account_id (int): the label id
            contract_id (int): the id of the contract
            statement_period_id (int): the statement period
            file_location (str): the file
            attachment_type (StatementAttachmentType): the statement attachment type to check
            subaccount_id (int): Optional id of the subaccount
            statement_period_ids (list): Optional list of statement period ids
        Return: bool
        """
        # always pass subaccount_id as a filter - see WAR-2983 bug
        filters = [cls.subaccount_id == subaccount_id]

        if account_id:
            filters.append(cls.account_id == account_id)

        if statement_period_id:
            filters.append(cls.statement_period_id == statement_period_id)

        if file_location:
            filters.append(cls.file_location == file_location)

        if number_format:
            filters.append(cls.number_format == number_format)

        if attachment_type:
            filters.append(cls.statement_attachment_type == attachment_type)

        if contract_id:
            filters.append(cls.contract_id == contract_id)

        if statement_period_ids:
            filters.append(cls.statement_period_ids == statement_period_ids)

        if statement_attachment_id:
            filters.append(cls.statement_attachment_id == statement_attachment_id)

        return bool(cls.query.filter(*filters).first())