"""کوئری‌های صفحه تسک — پورت‌شده از core/repositories/task_repo.py دسکتاپ."""
from sqlalchemy import func, and_

from app.extensions import db
from app.models.customer import Customer, Purchase
from app.models.call import Call
from app.models.tag import Tag, CustomerTag
from app.services.jalali import days_ago


def _latest_call_subq():
    latest = (
        db.session.query(Call.customer_id.label("customer_id"), func.max(Call.call_date).label("max_date"))
        .group_by(Call.customer_id)
        .subquery()
    )
    return (
        db.session.query(Call.customer_id.label("customer_id"), Call.next_action.label("next_action"))
        .join(latest, and_(latest.c.customer_id == Call.customer_id, latest.c.max_date == Call.call_date))
        .subquery()
    )


def _stats_query():
    stats = (
        db.session.query(
            Purchase.customer_id.label("customer_id"),
            func.sum(Purchase.amount).label("total_amount"),
            func.sum(Purchase.weight).label("total_weight"),
            func.max(Purchase.purchase_date).label("last_purchase_date"),
        )
        .group_by(Purchase.customer_id)
        .subquery()
    )
    calls = (
        db.session.query(Call.customer_id.label("customer_id"), func.max(Call.call_date).label("last_call_date"))
        .group_by(Call.customer_id)
        .subquery()
    )
    query = (
        db.session.query(
            Customer,
            func.coalesce(stats.c.total_amount, 0).label("total_amount"),
            func.coalesce(stats.c.total_weight, 0).label("total_weight"),
            stats.c.last_purchase_date,
            calls.c.last_call_date,
        )
        .outerjoin(stats, stats.c.customer_id == Customer.id)
        .outerjoin(calls, calls.c.customer_id == Customer.id)
        .filter(Customer.is_deleted.is_(False))
    )
    return query, stats, calls


def _finalize(rows):
    results = []
    for customer, total_amount, total_weight, last_purchase_date, last_call_date in rows:
        customer.computed_total_amount = total_amount or 0
        customer.computed_total_weight = total_weight or 0
        customer.computed_last_purchase_date = last_purchase_date
        customer.computed_last_call_date = last_call_date
        results.append(customer)
    return results


def _by_next_action(action_key):
    query, _stats, _calls = _stats_query()
    latest = _latest_call_subq()
    query = query.filter(Customer.id.in_(
        db.session.query(latest.c.customer_id).filter(latest.c.next_action == action_key)
    )).order_by(Customer.first_name)
    return _finalize(query.all())


def call_today():
    return _by_next_action("call_today")


def call_next_week():
    return _by_next_action("call_next_week")


def follow_up_week():
    return _by_next_action("follow_up_week")


def vip_inactive(days=14):
    cutoff = days_ago(days)
    query, stats, _calls = _stats_query()
    vip_ids = (
        db.session.query(CustomerTag.customer_id)
        .join(Tag, Tag.id == CustomerTag.tag_id)
        .filter(Tag.name == "VIP")
    )
    query = query.filter(
        Customer.id.in_(vip_ids),
        db.or_(stats.c.last_purchase_date.is_(None), stats.c.last_purchase_date < cutoff),
    ).order_by(Customer.first_name)
    return _finalize(query.all())


def sleeping(days=60):
    cutoff = days_ago(days)
    query, stats, _calls = _stats_query()
    sleeping_ids = (
        db.session.query(CustomerTag.customer_id)
        .join(Tag, Tag.id == CustomerTag.tag_id)
        .filter(Tag.name == "خواب")
    )
    query = query.filter(
        db.or_(
            Customer.id.in_(sleeping_ids),
            db.and_(stats.c.last_purchase_date.isnot(None), stats.c.last_purchase_date < cutoff),
        )
    ).order_by(Customer.first_name)
    return _finalize(query.all())


def not_presented():
    query, _stats, _calls = _stats_query()
    called_ids = db.session.query(Call.customer_id).distinct()
    query = query.filter(~Customer.id.in_(called_ids)).order_by(Customer.joined_at.desc())
    return _finalize(query.all())


CATEGORY_FUNCS = {
    "call_today": call_today,
    "vip_inactive": vip_inactive,
    "sleeping": sleeping,
    "not_presented": not_presented,
    "call_next_week": call_next_week,
    "follow_up_week": follow_up_week,
}


def get_category_customers(key):
    fn = CATEGORY_FUNCS.get(key)
    return fn() if fn else []


def get_category_counts():
    return {key: len(fn()) for key, fn in CATEGORY_FUNCS.items()}