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


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


def get_by_key(db: Session, key: str) -> Optional[Systemconfiguration]:
    return db.query(Systemconfiguration).filter(Systemconfiguration.key == key).first()


def list_all(
    db: Session,
    limit: int = 20,
    offset: int = 0,
    search: Optional[str] = None,
    data_type: Optional[str] = None,
) -> List[Systemconfiguration]:
    query = db.query(Systemconfiguration)

    if search:
        search_pattern = f"%{search}%"
        query = query.filter(
            (Systemconfiguration.key.ilike(search_pattern)) |
            (Systemconfiguration.description.ilike(search_pattern))
        )

    if data_type:
        query = query.filter(Systemconfiguration.data_type == data_type)

    query = query.order_by(Systemconfiguration.key.asc())
    return query.limit(limit).offset(offset).all()


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


def update(db: Session, entity_id: str, data: dict) -> Optional[Systemconfiguration]:
    config = get_by_id(db, entity_id)
    if not config:
        return None

    for key, value in data.items():
        setattr(config, key, value)

    db.flush()
    return config


def delete(db: Session, entity_id: str) -> bool:
    config = get_by_id(db, entity_id)
    if not config:
        return False

    db.delete(config)
    db.flush()
    return True


def get_with_details(db: Session, entity_id: str) -> Optional[Systemconfiguration]:
    return (
        db.query(Systemconfiguration)
        .options(joinedload(Systemconfiguration.modified_by_user))
        .filter(Systemconfiguration.id == entity_id)
        .first()
    )