from sqlalchemy.orm import Session, joinedload
from typing import Optional, List
from .models import Gradingservice, Gradedcard


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


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Gradingservice]:
    query = db.query(Gradingservice)
    
    if "name" in filters and filters["name"]:
        query = query.filter(Gradingservice.name.ilike(f"%{filters['name']}%"))
    
    if "abbreviation" in filters and filters["abbreviation"]:
        query = query.filter(Gradingservice.abbreviation.ilike(f"%{filters['abbreviation']}%"))
    
    return query.order_by(Gradingservice.name).limit(limit).offset(offset).all()


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


def update(db: Session, entity_id: str, data: dict) -> Optional[Gradingservice]:
    grading_service = get_by_id(db, entity_id)
    if not grading_service:
        return None
    
    for key, value in data.items():
        setattr(grading_service, key, value)
    
    db.flush()
    return grading_service


def delete(db: Session, entity_id: str) -> bool:
    grading_service = get_by_id(db, entity_id)
    if not grading_service:
        return False
    
    db.delete(grading_service)
    db.flush()
    return True


def get_by_name(db: Session, name: str) -> Optional[Gradingservice]:
    return db.query(Gradingservice).filter(Gradingservice.name == name).first()


def get_by_abbreviation(db: Session, abbreviation: str) -> Optional[Gradingservice]:
    return db.query(Gradingservice).filter(Gradingservice.abbreviation == abbreviation).first()


# Gradedcard repository functions
def get_gradedcard_by_id(db: Session, entity_id: str) -> Optional[Gradedcard]:
    return db.query(Gradedcard).filter(Gradedcard.id == entity_id).first()


def list_gradedcards(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Gradedcard]:
    query = db.query(Gradedcard)
    
    if "listing_id" in filters and filters["listing_id"]:
        query = query.filter(Gradedcard.listing_id == filters["listing_id"])
    
    if "grading_service_id" in filters and filters["grading_service_id"]:
        query = query.filter(Gradedcard.grading_service_id == filters["grading_service_id"])
    
    if "grade" in filters and filters["grade"]:
        query = query.filter(Gradedcard.grade == filters["grade"])
    
    if "certification_number" in filters and filters["certification_number"]:
        query = query.filter(Gradedcard.certification_number == filters["certification_number"])
    
    return query.order_by(Gradedcard.created_at.desc()).limit(limit).offset(offset).all()


def create_gradedcard(db: Session, data: dict) -> Gradedcard:
    graded_card = Gradedcard(**data)
    db.add(graded_card)
    db.flush()
    return graded_card


def update_gradedcard(db: Session, entity_id: str, data: dict) -> Optional[Gradedcard]:
    graded_card = get_gradedcard_by_id(db, entity_id)
    if not graded_card:
        return None
    
    for key, value in data.items():
        setattr(graded_card, key, value)
    
    db.flush()
    return graded_card


def delete_gradedcard(db: Session, entity_id: str) -> bool:
    graded_card = get_gradedcard_by_id(db, entity_id)
    if not graded_card:
        return False
    
    db.delete(graded_card)
    db.flush()
    return True


def get_gradedcard_by_listing_id(db: Session, listing_id: str) -> Optional[Gradedcard]:
    return db.query(Gradedcard).filter(Gradedcard.listing_id == listing_id).first()


def get_gradedcard_by_certification_number(db: Session, certification_number: str) -> Optional[Gradedcard]:
    return db.query(Gradedcard).filter(Gradedcard.certification_number == certification_number).first()


def get_gradedcard_with_details(db: Session, entity_id: str) -> Optional[Gradedcard]:
    return (
        db.query(Gradedcard)
        .options(
            joinedload(Gradedcard.grading_service)
        )
        .filter(Gradedcard.id == entity_id)
        .first()
    )