from sqlalchemy import select
from app.extensions import db
from app.models.category import Category
from app.models.transaction import Transaction


def get_all_active():
    """All active categories: system first, then alphabetical."""
    return (Category.query
            .filter_by(is_active=True)
            .order_by(Category.is_system.desc(), Category.name)
            .all())


def create_custom(name: str) -> Category:
    """Add a new custom category. Raises ValueError on validation failure."""
    name = name.strip()
    if not name:
        raise ValueError("Category name is required.")
    if len(name) > 50:
        raise ValueError("Category name must be 50 characters or less.")
    if Category.query.filter_by(name=name).first():
        raise ValueError(f"A category named '{name}' already exists.")
    cat = Category(name=name, is_system=False, is_active=True)
    db.session.add(cat)
    return cat


def rename(category: Category, new_name: str) -> None:
    """Rename a category. Raises ValueError on validation failure."""
    new_name = new_name.strip()
    if not new_name:
        raise ValueError("Category name is required.")
    if len(new_name) > 50:
        raise ValueError("Name must be 50 characters or less.")
    conflict = Category.query.filter_by(name=new_name).first()
    if conflict and conflict.id != category.id:
        raise ValueError(f"A category named '{new_name}' already exists.")
    category.name = new_name


def soft_delete(category: Category) -> None:
    """Soft-delete a custom category. Raises ValueError if system or in use."""
    if category.is_system:
        raise ValueError("System categories cannot be deleted.")
    has_txns = db.session.execute(
        select(Transaction.id).where(Transaction.category_id == category.id).limit(1)
    ).first() is not None
    if has_txns:
        raise ValueError("Category in use — cannot be deleted.")
    category.is_active = False