"""کوئری‌های مشتری — پورت‌شده از core/repositories/customer_repo.py دسکتاپ (SQLAlchemy روی Postgres/SQLite)."""
from sqlalchemy import func, or_

from app.extensions import db
from app.models.customer import Customer, Purchase
from app.models.call import Call

STATUS_CHOICES = [("new", "جدید"), ("active", "فعال"), ("inactive", "غیرفعال"), ("sleeping", "خواب")]
GENDER_CHOICES = [("male", "آقا"), ("female", "خانم")]
HOW_FOUND_CHOICES = ["مشتری قدیمی", "معرفی دوستان", "اینستاگرام", "تابلو مغازه", "سایر"]

SORT_OPTIONS = [
    ("joined_desc", "جدیدترین عضویت"),
    ("amount_desc", "بیشترین خرید"),
    ("amount_asc", "کمترین خرید"),
    ("name", "نام (الفبا)"),
]


def _purchase_stats_subq():
    return (
        db.session.query(
            Purchase.customer_id.label("customer_id"),
            func.sum(Purchase.amount).label("total_amount"),
            func.sum(Purchase.weight).label("total_weight"),
            func.count(Purchase.id).label("purchase_count"),
            func.max(Purchase.purchase_date).label("last_purchase_date"),
        )
        .group_by(Purchase.customer_id)
        .subquery()
    )


def _call_stats_subq():
    return (
        db.session.query(
            Call.customer_id.label("customer_id"),
            func.max(Call.call_date).label("last_call_date"),
            func.min(Call.call_date).label("first_call_date"),
        )
        .group_by(Call.customer_id)
        .subquery()
    )


def list_customers(search="", city=None, status=None, rating=None, sort="joined_desc", limit=1000):
    stats = _purchase_stats_subq()
    calls = _call_stats_subq()
    total_amount_expr = func.coalesce(stats.c.total_amount, 0)

    query = (
        db.session.query(
            Customer,
            total_amount_expr.label("total_amount"),
            func.coalesce(stats.c.total_weight, 0).label("total_weight"),
            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))
    )

    if search:
        like = f"%{search}%"
        query = query.filter(or_(
            Customer.first_name.ilike(like), Customer.last_name.ilike(like),
            Customer.mobile_phone.ilike(like), Customer.landline_phone.ilike(like),
            Customer.customer_code.ilike(like),
        ))
    if city:
        query = query.filter(Customer.city == city)
    if status:
        query = query.filter(Customer.status == status)
    if rating:
        query = query.filter(Customer.rating == rating)

    order_map = {
        "joined_desc": Customer.joined_at.desc(),
        "amount_desc": total_amount_expr.desc(),
        "amount_asc": total_amount_expr.asc(),
        "name": Customer.first_name.asc(),
    }
    query = query.order_by(order_map.get(sort, Customer.joined_at.desc())).limit(limit)

    results = []
    for customer, total_amount, total_weight, last_call_date in query.all():
        customer.computed_total_amount = total_amount or 0
        customer.computed_total_weight = total_weight or 0
        customer.computed_last_call_date = last_call_date
        results.append(customer)
    return results


def get_customer_with_stats(customer_id: int):
    stats = _purchase_stats_subq()
    calls = _call_stats_subq()
    row = (
        db.session.query(
            Customer,
            func.coalesce(stats.c.total_amount, 0).label("total_amount"),
            func.coalesce(stats.c.total_weight, 0).label("total_weight"),
            calls.c.last_call_date,
            calls.c.first_call_date,
        )
        .outerjoin(stats, stats.c.customer_id == Customer.id)
        .outerjoin(calls, calls.c.customer_id == Customer.id)
        .filter(Customer.id == customer_id)
        .first()
    )
    if not row:
        return None
    customer, total_amount, total_weight, last_call_date, first_call_date = row
    customer.computed_total_amount = total_amount or 0
    customer.computed_total_weight = total_weight or 0
    customer.computed_last_call_date = last_call_date
    customer.computed_first_call_date = first_call_date
    return customer


def search_by_name(q: str, limit=30):
    like = f"%{q}%"
    return (
        Customer.query.filter(
            Customer.is_deleted.is_(False),
            or_(
                Customer.first_name.ilike(like),
                Customer.last_name.ilike(like),
                (Customer.first_name + " " + Customer.last_name).ilike(like),
            ),
        )
        .order_by(Customer.first_name)
        .limit(limit)
        .all()
    )


def distinct_cities():
    rows = (
        db.session.query(Customer.city)
        .filter(Customer.is_deleted.is_(False), Customer.city.isnot(None), Customer.city != "")
        .distinct()
        .order_by(Customer.city)
        .all()
    )
    return [r[0] for r in rows]


def next_customer_code():
    count = Customer.query.count()
    return f"C{count + 1001}"