from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import Calculationsettings, Loancalculation, Amortizationentry


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


def get_by_settings_id(db: Session, settings_id: str) -> Optional[Calculationsettings]:
    return db.query(Calculationsettings).filter(Calculationsettings.settings_id == settings_id).first()


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Calculationsettings]:
    q = db.query(Calculationsettings)
    if filters.get("settings_id") is not None:
        q = q.filter(Calculationsettings.settings_id == filters["settings_id"])
    return q.order_by(Calculationsettings.created_at.desc()).limit(limit).offset(offset).all()


def count_all(db: Session, **filters) -> int:
    q = db.query(Calculationsettings.id)
    if filters.get("settings_id") is not None:
        q = q.filter(Calculationsettings.settings_id == filters["settings_id"])
    return q.count()


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


def update(db: Session, entity_id: str, data: dict) -> Optional[Calculationsettings]:
    obj = get_by_id(db, entity_id)
    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 = get_by_id(db, entity_id)
    if not obj:
        return False
    db.delete(obj)
    db.flush()
    return True


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


def get_calculation_by_calculation_id(db: Session, calculation_id: str) -> Optional[Loancalculation]:
    return db.query(Loancalculation).filter(Loancalculation.calculation_id == calculation_id).first()


def list_calculations(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Loancalculation]:
    q = db.query(Loancalculation)
    if filters.get("calculation_id") is not None:
        q = q.filter(Loancalculation.calculation_id == filters["calculation_id"])
    if filters.get("settings_id") is not None:
        q = q.filter(Loancalculation.settings_id == filters["settings_id"])
    return q.order_by(Loancalculation.created_at.desc()).limit(limit).offset(offset).all()


def count_calculations(db: Session, **filters) -> int:
    q = db.query(Loancalculation.id)
    if filters.get("calculation_id") is not None:
        q = q.filter(Loancalculation.calculation_id == filters["calculation_id"])
    if filters.get("settings_id") is not None:
        q = q.filter(Loancalculation.settings_id == filters["settings_id"])
    return q.count()


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


def update_calculation(db: Session, entity_id: str, data: dict) -> Optional[Loancalculation]:
    obj = get_calculation_by_id(db, entity_id)
    if not obj:
        return None
    for key, value in data.items():
        setattr(obj, key, value)
    db.flush()
    return obj


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


def get_calculation_with_details(db: Session, entity_id: str) -> Optional[Loancalculation]:
    return (
        db.query(Loancalculation)
        .options(
            joinedload(Loancalculation.settings),
            joinedload(Loancalculation.amortization_entries),
        )
        .filter(Loancalculation.id == entity_id)
        .first()
    )


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


def get_entry_by_entry_id(db: Session, entry_id: str) -> Optional[Amortizationentry]:
    return db.query(Amortizationentry).filter(Amortizationentry.entry_id == entry_id).first()


def list_entries(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Amortizationentry]:
    q = db.query(Amortizationentry)
    if filters.get("calculation_id") is not None:
        q = q.filter(Amortizationentry.calculation_id == filters["calculation_id"])
    if filters.get("entry_id") is not None:
        q = q.filter(Amortizationentry.entry_id == filters["entry_id"])
    return q.order_by(Amortizationentry.payment_number).limit(limit).offset(offset).all()


def count_entries(db: Session, **filters) -> int:
    q = db.query(Amortizationentry.id)
    if filters.get("calculation_id") is not None:
        q = q.filter(Amortizationentry.calculation_id == filters["calculation_id"])
    if filters.get("entry_id") is not None:
        q = q.filter(Amortizationentry.entry_id == filters["entry_id"])
    return q.count()


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


def update_entry(db: Session, entity_id: str, data: dict) -> Optional[Amortizationentry]:
    obj = get_entry_by_id(db, entity_id)
    if not obj:
        return None
    for key, value in data.items():
        setattr(obj, key, value)
    db.flush()
    return obj


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