"""Run controller contract model."""

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


class RunControllerContract(BaseModel):
    """Run controller contract model."""

    __tablename__ = 'run_controller_contract'

    run_controller_contract_id = db.Column(db.Integer, primary_key=True)
    run_controller_id = db.Column(
        db.Integer, db.ForeignKey('run_controller.run_controller_id')
    )
    contract_id = db.Column(db.Integer, db.ForeignKey('contract.contract_id'))

    @classmethod
    def get_by_contract_id(cls, contract_id):
        """Get all rows filtered by contract_id."""
        return cls.query.filter(cls.contract_id == contract_id)

    @classmethod
    def get_by_contract_ids(cls, contract_ids):
        """Get all rows filtered by contract_ids."""
        return cls.query.filter(cls.contract_id.in_(contract_ids)).all()

    @classmethod
    def get_by_run_controller_id(cls, run_controller_id):
        """Get all rows filtered by run_controller_id."""
        return cls.query.filter(cls.run_controller_id == run_controller_id)

    @staticmethod
    def get_by_account_id(account_id, contract_type=None):
        """Get run controller contracts by account_id."""
        select_conditions = [
            literal_column('rcc.contract_id') == literal_column('ac.contract_id'),
            literal_column('rcc.run_controller_id')
            == literal_column('rc.run_controller_id'),
            text('ac.account_id = :account_id'),
        ]

        if contract_type:
            select_conditions.append(text(f'rc.contract_type = "{contract_type}"'))

        query = (
            select(
                [
                    literal_column('rcc.run_controller_id').label('run_controller_id'),
                    literal_column('rc.run_controller_name').label(
                        'run_controller_name'
                    ),
                    literal_column('rc.contract_type').label('contract_type'),
                    func.count('rcc.contract_id').label('contract_count'),
                ]
            )
            .where(and_(*select_conditions))
            .select_from(table('run_controller_contract').alias('rcc'))
            .select_from(table('run_controller').alias('rc'))
            .select_from(table('account_contract').alias('ac'))
            .group_by(text('rc.run_controller_id'))
        )

        params = {'account_id': account_id}
        return db.session.execute(query, params).fetchall()