from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import Patient, MedicalRecord


def get_by_id(db: Session, entity_id: str) -> Optional[Patient]:
    return db.query(Patient).filter(Patient.id == entity_id).first()


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Patient]:
    q = db.query(Patient)
    search = filters.get("search")
    if search:
        search_term = f"%{search}%"
        q = q.filter(
            (Patient.first_name.ilike(search_term)) |
            (Patient.last_name.ilike(search_term)) |
            (Patient.patient_number.ilike(search_term)) |
            (Patient.email.ilike(search_term))
        )
    return q.order_by(Patient.created_at.desc()).limit(limit).offset(offset).all()


def count_all(db: Session, **filters) -> int:
    q = db.query(Patient.id)
    search = filters.get("search")
    if search:
        search_term = f"%{search}%"
        q = q.filter(
            (Patient.first_name.ilike(search_term)) |
            (Patient.last_name.ilike(search_term)) |
            (Patient.patient_number.ilike(search_term)) |
            (Patient.email.ilike(search_term))
        )
    return q.count()


def create(db: Session, data: dict) -> Patient:
    obj = Patient(**data)
    db.add(obj)
    db.flush()
    return obj


def update(db: Session, entity_id: str, data: dict) -> Optional[Patient]:
    obj = db.query(Patient).filter(Patient.id == entity_id).first()
    if not obj:
        return None
    for key, value in data.items():
        setattr(obj, key, value)
    db.flush()
    return obj


def delete(db: Session, entity_id: str) -> bool:
    obj = db.query(Patient).filter(Patient.id == entity_id).first()
    if not obj:
        return False
    db.delete(obj)
    db.flush()
    return True


def get_with_details(db: Session, entity_id: str) -> Optional[Patient]:
    return (
        db.query(Patient)
        .options(
            joinedload(Patient.insurance_provider),
            joinedload(Patient.medical_record),
            joinedload(Patient.appointments)
                .joinedload("doctor"),
            joinedload(Patient.appointments)
                .joinedload("department"),
        )
        .filter(Patient.id == entity_id)
        .first()
    )


def get_medicalrecord_by_patient_id(db: Session, patient_id: str) -> Optional[MedicalRecord]:
    return db.query(MedicalRecord).filter(MedicalRecord.patient_id == patient_id).first()