from sqlalchemy.orm import Session, joinedload

from app.models.insurance_company import InsuranceCompany
from app.models.policy_scan_keyword import PolicyScanKeyword


class ScanKeywordRepository:
    def __init__(self, db: Session):
        self.db = db

    def list_all(self) -> list[PolicyScanKeyword]:
        return (
            self.db.query(PolicyScanKeyword)
            .options(joinedload(PolicyScanKeyword.insurance_company))
            .join(InsuranceCompany)
            .order_by(InsuranceCompany.name.asc(), PolicyScanKeyword.is_primary.desc(), PolicyScanKeyword.keyword.asc())
            .all()
        )

    def list_active_for_scan(self) -> list[PolicyScanKeyword]:
        return (
            self.db.query(PolicyScanKeyword)
            .join(InsuranceCompany)
            .filter(InsuranceCompany.is_active.is_(True))
            .options(joinedload(PolicyScanKeyword.insurance_company))
            .all()
        )

    def get_by_id(self, keyword_id: int) -> PolicyScanKeyword | None:
        return (
            self.db.query(PolicyScanKeyword)
            .options(joinedload(PolicyScanKeyword.insurance_company))
            .filter(PolicyScanKeyword.id == keyword_id)
            .first()
        )

    def get_primary_for_company(self, company_id: int) -> PolicyScanKeyword | None:
        return (
            self.db.query(PolicyScanKeyword)
            .filter(
                PolicyScanKeyword.insurance_company_id == company_id,
                PolicyScanKeyword.is_primary.is_(True),
            )
            .first()
        )

    def get_by_company_and_keyword(self, company_id: int, keyword: str) -> PolicyScanKeyword | None:
        return (
            self.db.query(PolicyScanKeyword)
            .filter(
                PolicyScanKeyword.insurance_company_id == company_id,
                PolicyScanKeyword.keyword == keyword.strip(),
            )
            .first()
        )

    def delete_for_company(self, company_id: int) -> None:
        self.db.query(PolicyScanKeyword).filter(PolicyScanKeyword.insurance_company_id == company_id).delete()