"""Model for abacus schedule.

Besides the POST endpoint, the insertion trigger on the contract_party table
leads to the insertion of a record into the schedule table when
the insertion record is of target_type "contributor".
"""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import BaseModel
from sqlalchemy import Enum, func

from abacus_schedule.constants import constants
from abacus_schedule.models.schedule_attachment import ScheduleAttachment


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

    __tablename__ = 'schedule'
    schedule_id = db.Column(db.Integer, primary_key=True, nullable=False)
    schedule_name = db.Column(db.String(180), nullable=False)
    target_id = db.Column(db.String(180), nullable=False)
    target_type = db.Column(
        Enum(*constants.SCHEDULE_TARGET_TYPE, name='target_type', create_type=False),
        nullable=False,
    )
    schedule_attachment = db.relationship(
        'ScheduleAttachment',
        backref='schedule',
        cascade='all, delete-orphan',
        uselist=True,
    )
    conditions = db.Column(db.JSON, nullable=True)

    @classmethod
    def default_order(cls):
        """Override to customize default ordering."""
        return func.lower(cls.schedule_name)

    @classmethod
    def get_schedules_by_target(
        cls, target_type: str, target_id: str, include_only_schedules: bool = False
    ) -> list:
        """Get all schedules for a given target.

        if "include_only_schedules" is True then query returns only those
        schedule records where both the "schedule" and "schedule_attachment"
        both point to the same entity.
        """
        subquery = (
            cls.query.with_entities(*[cls.schedule_id])
            .join(ScheduleAttachment, ScheduleAttachment.schedule_id == cls.schedule_id)
            .filter(
                cls.target_type == target_type,
                cls.target_id == target_id,
                ScheduleAttachment.target_type == target_type,
                ScheduleAttachment.target_id == target_id,
            )
        )

        if include_only_schedules:
            query = cls.query.filter(
                cls.target_type == target_type,
                cls.target_id == target_id,
                cls.schedule_id.in_(subquery),
            )
        else:
            query = cls.query.filter(
                cls.target_type == target_type,
                cls.target_id == target_id,
                cls.schedule_id.notin_(subquery),
            )
        items = query.order_by(cls.default_order()).all()
        return items