"""Worksheet Correction model."""

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

from abacus_worksheet.constants import constants


class WorksheetCorrection(BaseModel):
    """Worksheet Correction model."""

    __tablename__ = 'worksheet_correction'

    worksheet_correction_id = db.Column(db.Integer, primary_key=True)
    account_id = db.Column(db.Integer, nullable=False)
    contract_id = db.Column(db.Integer, nullable=True)
    statement_period_id = db.Column(db.Integer, nullable=False)
    correction_statement_period_id = db.Column(db.Integer, nullable=False)
    correction_type = db.Column(
        Enum(*constants.CORRECTION_TYPES, name='correction_type', create_type=False),
        nullable=False,
    )
    currency_code = db.Column(db.String(3), nullable=False)
    gross_revenue = db.Column(db.Numeric(38, 18), nullable=False)
    distribution_fee = db.Column(db.Numeric(38, 18), nullable=False)
    mechanical_deduction_total = db.Column(db.Numeric(38, 18), nullable=True)
    mechanical_deduction_admin_fee_total = db.Column(db.Numeric(38, 18), nullable=True)
    net_revenue = db.Column(db.Numeric(38, 18), nullable=False)
    note = db.Column(db.String(255), nullable=True)
    deleted_at = db.Column(db.DateTime, nullable=True)
    deleted_by = db.Column(db.String(180), nullable=True)

    @classmethod
    def get_unapplied_worksheet_corrections(
        cls, statement_period_id: int, correction_type: str, limit: int, offset: int
    ):
        """Get a list of unapplied worksheet_correction's by statement period id and correction_type.

        Get records that are not deleted and do not exist in ledger_correction table.

        Args:
            statement_period_id (int): id of the statement period
            correction_type (str): either royalty_reversal or royalty_correction
            limit (int): pagination limit
            offset (int): pagination offset
        Returns:
            A tuple of result items and total count.
        """
        query = (
            select([literal_column('wc.*')])
            .where(
                and_(
                    literal_column('wc.statement_period_id') == statement_period_id,
                    literal_column('wc.correction_type') == correction_type,
                    literal_column('wc.deleted_at').is_(None),
                    literal_column('wc.deleted_by').is_(None),
                    literal_column('lc.worksheet_correction_id').is_(None),
                )
            )
            .order_by(literal_column('wc.worksheet_correction_id'))
            .select_from(
                outerjoin(
                    table('worksheet_correction').alias('wc'),
                    table('ledger_correction').alias('lc'),
                    text('lc.worksheet_correction_id = wc.worksheet_correction_id'),
                )
            )
        )

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

        return items, total_count