"""Report custom model."""

from datetime import datetime
from datetime import timedelta

from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import Enum
from sqlalchemy import Integer
from sqlalchemy import JSON
from sqlalchemy import String
from sqlalchemy import text

from moneyhub.connectors.mysql import db
from moneyhub.constants.constants import NumberFormat
from moneyhub.constants.constants import ReportCustomColumnDimension
from moneyhub.constants.constants import ReportCustomFileType
from moneyhub.constants.constants import ReportCustomRowDimension
from moneyhub.constants.constants import ReportCustomStatus
from moneyhub.constants.constants import RevenueDisplayType
from moneyhub.constants.constants import RevenueType
from moneyhub.constants.constants import SYSTEM_TIMEZONE
from moneyhub.models.mysql_base import BaseModel


class ReportCustom(BaseModel):
    """Report custom model."""

    __tablename__ = 'report_custom'

    report_custom_id = Column(Integer, primary_key=True)
    account_id = Column(Integer, nullable=False)
    contract_id = Column(Integer, nullable=True)
    subaccount_id = Column(Integer, nullable=True)
    statement_period_ids = Column(String(255), nullable=True)
    revenue_type = Column(
        Enum(
            *RevenueType,
            name='revenue_type',
            create_type=False
        ),
        nullable=False
    )
    revenue_display_type = Column(
        Enum(
            *RevenueDisplayType,
            name='revenue_display_type',
            create_type=False
        ),
        nullable=False,
        server_default=RevenueDisplayType.NET.value
    )
    report_custom_status = Column(
        Enum(
            *ReportCustomStatus,
            name='report_custom_status',
            create_type=False
        ),
        nullable=False
    )
    dimension_row = Column(
        Enum(
            *ReportCustomRowDimension,
            name='dimension_row',
            create_type=False
        ),
        nullable=False
    )
    dimension_column = Column(
        Enum(
            *ReportCustomColumnDimension,
            name='dimension_column',
            create_type=False
        ),
        nullable=False
    )
    filters = Column(JSON(none_as_null=True), nullable=True)
    number_format = Column(
        Enum(
            *NumberFormat,
            name='number_format',
            create_type=False
        ),
        nullable=False
    )
    file_type = Column(
        Enum(
            *ReportCustomFileType,
            name='file_type',
            create_type=False
        ),
        nullable=False
    )
    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
        super().__init__(*args, **kwargs)

    @classmethod
    def get_by_account_id(
        cls,
        account_id: int,
        contract_id: int | None,
        subaccount_id: int | None = None,
        is_subaccount: bool = False
    ) -> list:
        """Get custom reports by an account_id.

        Args:
            account_id (int): The id of an account
            contract_id (int): Contract to filter by
            subaccount_id (int): Optional subaccount to filter by
            is_subaccount (bool): Whether to filter by subaccount_id
        Returns:
            list: list of custom reports
        """
        filters = [
            ReportCustom.account_id == account_id,
            ReportCustom.subaccount_id == subaccount_id
        ]
        if contract_id:
            filters.append(cls.contract_id == contract_id)
        if is_subaccount and subaccount_id is not None:
            filters.append(cls.subaccount_id == subaccount_id)

        return cls.query.filter(*filters).order_by(
            cls.report_custom_id.desc()
        ).all()

    @classmethod
    def get_by_statement_period(
        cls,
        statement_period_id: int,
        report_custom_ids: list | None
    ) -> list:
        """Get custom reports by a statement period.

        Args:
            statement_period_id (int): ID of the statement period
            report_custom_ids (list|None): List of IDs to filter by
        Returns:
            list: list of custom reports
        """
        filters = [text(f'FIND_IN_SET( {statement_period_id},statement_period_ids)')]
        if report_custom_ids:
            filters.append(cls.report_custom_id.in_(report_custom_ids))

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

    @classmethod
    def update_custom_report(
            cls, report_custom_id: int, report_data: list) -> dict:
        """Update custom report by a report_custom_id.

        Args:
            report_custom_id (int): The id of an account
            report_data (list): List of properties to update
        Returns:
            dict: Updated custom report
        """
        # TODO:  ownership check for the report
        obj = cls.get_by_id_or_error(report_custom_id)
        obj.update_attributes(**report_data)
        db.session.commit()

        return obj

    @classmethod
    def delete_by_id(cls, report_custom_id: int) -> dict:
        """Load a report by id, then delete it.

        Args:
            report_custom_id (int): The id of the report
        Returns:
            dict: Deleted custom report
        """
        obj = cls.get_by_id_or_error(report_custom_id)
        # TODO:  ownership check for the report
        db.session.delete(obj)
        db.session.commit()

        return obj

    @classmethod
    def get_stuck_in_progress(cls):
        """Get custom report stuck "in progress".

        Returns:
            list: list of custom reports
        """
        STATUS_IN_PROGRESS = ReportCustomStatus.IN_PROGRESS
        one_hour_ago = datetime.utcnow() - timedelta(hours=1)

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