"""WorksheetAdjustmentDetail model."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from abacus_common_logic.utils.dates import current_timestamp
from abacus_common_logic.utils.users import get_flask_user_id
from sqlalchemy import Enum

from abacus_worksheet.constants.constants import DISTRIBUTION_TYPES
from abacus_worksheet.utils.deletion_status import predicates


class WorksheetAdjustmentDetail(BaseModel):
    """WorksheetAdjustmentDetail model."""

    __tablename__ = 'worksheet_adjustment_detail'

    worksheet_adjustment_detail_id = db.Column(db.Integer, primary_key=True)
    statement_period_adjustment_file_id = db.Column(db.Integer, nullable=False)
    worksheet_adjustment_id = db.Column(
        db.Integer,
        db.ForeignKey('worksheet_adjustment.worksheet_adjustment_id'),
        nullable=False,
    )
    account_id = db.Column(db.Integer, nullable=False)
    contract_id = db.Column(db.Integer, nullable=True)
    activity_statement_period_id = db.Column(db.Integer, nullable=False)
    apply_to_statement_period_id = db.Column(db.Integer, nullable=False)
    reference_adjustment_type_id = db.Column(db.Integer, nullable=False)
    apply_to_flowthrough_payment = db.Column(db.Boolean, nullable=True)
    currency_code = db.Column(db.String(3), nullable=False)
    amount = db.Column(db.Numeric(20, 2), nullable=False)
    upc = db.Column(db.String(20), nullable=False)
    distribution_type = db.Column(
        Enum(*DISTRIBUTION_TYPES, name='distribution_type', create_type=False),
        default=DISTRIBUTION_TYPES.DIGITAL,
        nullable=False,
    )
    note = db.Column(db.String(180), nullable=True)
    internal_note = db.Column(db.String(180), nullable=True)
    deleted_at = db.Column(db.DateTime, nullable=True)
    deleted_by = db.Column(db.String(180), nullable=True)

    worksheet_adjustment = db.relationship(
        'WorksheetAdjustment', backref='worksheet_adjustment_detail', uselist=False
    )

    @classmethod
    def get_by_worksheet_adjustment_id(
        cls, worksheet_adjustment_id: int, limit: int, offset: int
    ):
        """Get a list of worksheet adjustment details by worksheet_adjustment_id.

        Args:
            worksheet_adjustment_id (int): id of the worksheet_adjustment
            limit (int): pagination limit
            offset (int): pagination offset

        Returns:
            A tuple of result items and total count.
        """
        query = cls.query.filter(
            cls.worksheet_adjustment_id == worksheet_adjustment_id,
            cls.deleted_by.is_(None),
            cls.deleted_at.is_(None),
        )

        items = query.limit(limit).offset(offset).all()
        total_count = query.count()

        return items, total_count

    @classmethod
    def soft_delete_worksheet_adjustment_details(
        cls, statement_period_adjustment_file_id: int
    ):
        """Soft delete worksheet adjustment details by statement_period_adjustment_file_id.

        Args:
        statement_period_adjustment_file_id (int): id of the statement_period_adjustment_file
        """
        db.session.query(WorksheetAdjustmentDetail).filter(
            WorksheetAdjustmentDetail.statement_period_adjustment_file_id
            == statement_period_adjustment_file_id,
            WorksheetAdjustmentDetail.deleted_at.is_(None),
        ).update(
            {
                WorksheetAdjustmentDetail.deleted_at: current_timestamp(),
                WorksheetAdjustmentDetail.deleted_by: get_flask_user_id(),
            }
        )

    @classmethod
    def soft_delete_by_adjustment_ids(cls, worksheet_adjustment_ids):
        """Soft-delete live details whose parent is in the id list."""
        db.session.query(cls).filter(
            cls.worksheet_adjustment_id.in_(worksheet_adjustment_ids),
            cls.deleted_at.is_(None),
        ).update(
            {
                cls.deleted_at: current_timestamp(),
                cls.deleted_by: get_flask_user_id(),
            },
            synchronize_session=False,
        )

    @classmethod
    def restore_by_adjustment_ids(cls, worksheet_adjustment_ids):
        """Restore soft-deleted details whose parent is in the id list."""
        db.session.query(cls).filter(
            cls.worksheet_adjustment_id.in_(worksheet_adjustment_ids),
            cls.deleted_at.isnot(None),
        ).update(
            {cls.deleted_at: None, cls.deleted_by: None},
            synchronize_session=False,
        )

    @staticmethod
    def _query_to_get_worksheet_adjustment_detail(
        is_deleted: bool | None = False,
    ):
        """Query to get worksheet adjustment detail, filtered by is_deleted."""
        from abacus_worksheet.models.worksheet_adjustment import WorksheetAdjustment

        return (
            db.session.query(WorksheetAdjustmentDetail)
            .join(
                WorksheetAdjustment,
                WorksheetAdjustment.worksheet_adjustment_id
                == WorksheetAdjustmentDetail.worksheet_adjustment_id,
            )
            .filter(*predicates(WorksheetAdjustmentDetail, is_deleted))
        )