"""کوئری‌های تگ — پورت‌شده از core/repositories/tag_repo.py دسکتاپ."""
from app.extensions import db
from app.models.tag import TagCategory, Tag, CustomerTag


def all_tags_grouped():
    cats = TagCategory.query.order_by(TagCategory.sort_order, TagCategory.id).all()
    return [{"category": c, "tags": c.tags} for c in cats]


def get_customer_tag_ids(customer_id: int) -> set:
    rows = CustomerTag.query.filter_by(customer_id=customer_id).all()
    return {r.tag_id for r in rows}


def get_customer_tags_grouped(customer_id: int):
    rows = (
        db.session.query(Tag.name, TagCategory.name.label("category_name"), TagCategory.sort_order, Tag.sort_order)
        .join(CustomerTag, CustomerTag.tag_id == Tag.id)
        .join(TagCategory, TagCategory.id == Tag.category_id)
        .filter(CustomerTag.customer_id == customer_id)
        .order_by(TagCategory.sort_order, Tag.sort_order)
        .all()
    )
    grouped = {}
    for name, category_name, _cat_order, _tag_order in rows:
        grouped.setdefault(category_name, []).append(name)
    return grouped


def set_customer_tags(customer_id: int, tag_ids: list, assigned_by=None):
    CustomerTag.query.filter_by(customer_id=customer_id).delete()
    for tag_id in tag_ids:
        db.session.add(CustomerTag(customer_id=customer_id, tag_id=tag_id, assigned_by=assigned_by))
    db.session.commit()


def add_category(name: str) -> TagCategory:
    max_order = db.session.query(db.func.coalesce(db.func.max(TagCategory.sort_order), -1)).scalar()
    cat = TagCategory(name=name, sort_order=max_order + 1)
    db.session.add(cat)
    db.session.commit()
    return cat


def rename_category(category_id: int, name: str):
    cat = db.session.get(TagCategory, category_id)
    if cat:
        cat.name = name
        db.session.commit()


def delete_category(category_id: int):
    cat = db.session.get(TagCategory, category_id)
    if cat:
        db.session.delete(cat)
        db.session.commit()


def add_tag(category_id: int, name: str) -> Tag:
    max_order = db.session.query(db.func.coalesce(db.func.max(Tag.sort_order), -1)).filter(
        Tag.category_id == category_id
    ).scalar()
    tag = Tag(category_id=category_id, name=name, sort_order=max_order + 1)
    db.session.add(tag)
    db.session.commit()
    return tag


def delete_tag(tag_id: int):
    tag = db.session.get(Tag, tag_id)
    if tag:
        db.session.delete(tag)
        db.session.commit()