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


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 category_name_exists(name: str, exclude_id=None) -> bool:
    query = TagCategory.query.filter(TagCategory.name == name)
    if exclude_id:
        query = query.filter(TagCategory.id != exclude_id)
    return db.session.query(query.exists()).scalar()


def tag_name_exists(category_id: int, name: str) -> bool:
    query = Tag.query.filter(Tag.category_id == category_id, Tag.name == name)
    return db.session.query(query.exists()).scalar()


def _delete_tag_links(tag_ids):
    # کلید خارجی SQLite خاموشه و ON DELETE CASCADE اجرا نمی‌شه؛ اتصال‌ها دستی پاک می‌شن
    # (وگرنه روی تماس‌ها و مشتری‌ها می‌موندن و حتی ممکن بود به تگ جدیدی با همون شناسه وصل بشن)
    if not tag_ids:
        return
    CustomerTag.query.filter(CustomerTag.tag_id.in_(tag_ids)).delete(synchronize_session=False)
    CallTag.query.filter(CallTag.tag_id.in_(tag_ids)).delete(synchronize_session=False)


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:
        _delete_tag_links([t.id for t in cat.tags])
        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:
        _delete_tag_links([tag.id])
        db.session.delete(tag)
        db.session.commit()