"""ساخت داده گزارش مشتریان/فروشنده‌ها و خروجی Excel و PDF — پورت‌شده از core/reports.py دسکتاپ."""
import functools
from collections import Counter

from sqlalchemy import func

from app.extensions import db
from app.models.customer import Customer, Purchase
from app.models.call import Call
from app.models.tag import CustomerTag, CallTag
from app.models.user import User
from app.services.jalali import to_jalali_str

_FONTS_REGISTERED = False


def build_customer_report(cities=None, tag_ids=None, min_weight=None, max_weight=None,
                           date_from=None, date_to=None, time_mode="none", sort="weight_desc"):
    """گزارش مشتری‌ها بر اساس وزن خرید (مبلغ خرید هیچ‌جا ثبت نمی‌شه و همیشه صفر بود).

    بازه‌ی تاریخ (از/تا، هر کدوم به‌تنهایی هم کار می‌کنه) وزن خریدِ داخل بازه رو حساب می‌کنه:
    - بدون حالت زمانی یا «فعال‌ترین‌ها»: فقط مشتری‌هایی که در بازه خرید داشتن (فعال‌ترین‌ها: بیشترین خرید در بازه اول)
    - «مشتری خوابیده»: همه‌ی مشتری‌ها، کمترین خرید در بازه اول (کسایی که اصلاً نخریدن بالای لیست)
    """
    stats = (
        db.session.query(
            Purchase.customer_id.label("customer_id"),
            func.sum(Purchase.weight).label("total_weight"),
            func.count(Purchase.id).label("purchase_count"),
        )
        .group_by(Purchase.customer_id)
        .subquery()
    )
    query = (
        db.session.query(
            Customer,
            func.coalesce(stats.c.total_weight, 0).label("total_weight"),
            func.coalesce(stats.c.purchase_count, 0).label("purchase_count"),
        )
        .outerjoin(stats, stats.c.customer_id == Customer.id)
        .filter(Customer.is_deleted.is_(False))
    )
    if cities:
        query = query.filter(Customer.city.in_(cities))
    if tag_ids:
        tagged_ids = db.session.query(CustomerTag.customer_id).filter(CustomerTag.tag_id.in_(tag_ids))
        query = query.filter(Customer.id.in_(tagged_ids))

    rows = [
        {"customer": customer, "total_weight": float(total_weight or 0), "purchase_count": purchase_count or 0,
         "range_weight": None}
        for customer, total_weight, purchase_count in query.all()
    ]

    has_range = bool(date_from or date_to)
    if has_range:
        range_q = db.session.query(Purchase.customer_id, func.coalesce(func.sum(Purchase.weight), 0))
        if date_from:
            range_q = range_q.filter(Purchase.purchase_date >= date_from)
        if date_to:
            range_q = range_q.filter(Purchase.purchase_date <= date_to)
        range_map = {cid: float(w) for cid, w in range_q.group_by(Purchase.customer_id).all()}
        for r in rows:
            r["range_weight"] = range_map.get(r["customer"].id, 0.0)
        if time_mode != "sleeping":
            rows = [r for r in rows if r["customer"].id in range_map]
        if time_mode == "active":
            sort = "range_desc"
        elif time_mode == "sleeping":
            sort = "range_asc"

    if min_weight is not None:
        rows = [r for r in rows if r["total_weight"] >= min_weight]
    if max_weight is not None:
        rows = [r for r in rows if r["total_weight"] <= max_weight]

    sort = {"amount_desc": "weight_desc", "amount_asc": "weight_asc"}.get(sort, sort)  # آدرس‌های قدیمی
    sort_key_map = {
        "weight_desc": lambda r: -r["total_weight"],
        "weight_asc": lambda r: r["total_weight"],
        "range_asc": lambda r: r["range_weight"] or 0,
        "range_desc": lambda r: -(r["range_weight"] or 0),
        "name": lambda r: (r["customer"].first_name or "", r["customer"].last_name or ""),
    }
    rows.sort(key=sort_key_map.get(sort, sort_key_map["weight_desc"]))
    return rows


def build_salesperson_report(salesperson_ids=None, date_from=None, date_to=None,
                              answered=None, tag_ids=None, sort="date_desc"):
    query = (
        Call.query
        .join(Customer, Call.customer_id == Customer.id)
    )

    if salesperson_ids:
        query = query.filter(Call.salesperson_id.in_(salesperson_ids))

    if date_from:
        query = query.filter(Call.call_date >= date_from)

    if date_to:
        query = query.filter(func.date(Call.call_date) <= date_to)

    if answered is not None:
        query = query.filter(Call.answered.is_(answered))

    # فیلتر بر اساس تگ‌های خود تماس
    if tag_ids:
        query = (
            query
            .join(CallTag, CallTag.call_id == Call.id)
            .filter(CallTag.tag_id.in_(tag_ids))
            .distinct()
        )

    calls = query.order_by(Call.call_date.desc()).all()

    if sort in ("salesperson_calls_desc", "salesperson_calls_asc"):
        counts = Counter(c.salesperson_id for c in calls)
        calls.sort(
            key=lambda c: counts[c.salesperson_id],
            reverse=(sort == "salesperson_calls_desc")
        )

    return calls

@functools.lru_cache(maxsize=1)
def _reshaper():
    import arabic_reshaper

    class _CachedReshaper(arabic_reshaper.ArabicReshaper):
        # arabic_reshaper 3.0.0 به‌خاطر یک باگ (name mangling در hasattr) regex لیگاتورها رو هیچ‌وقت کش نمی‌کنه
        # و برای هر خانه‌ی جدول PDF از اول می‌سازدش؛ خروجی PDF همه‌ی مشتری‌ها حدود ۴۰ ثانیه طول می‌کشید
        @functools.cached_property
        def _ligatures_re(self):
            return super()._ligatures_re

    return _CachedReshaper()


def _persian(text):
    from bidi.algorithm import get_display
    return get_display(_reshaper().reshape(str(text)))


def _register_pdf_fonts():
    global _FONTS_REGISTERED
    if _FONTS_REGISTERED:
        return
    from flask import current_app
    from reportlab.pdfbase import pdfmetrics
    from reportlab.pdfbase.ttfonts import TTFont
    fonts_dir = current_app.static_folder + "/fonts"
    pdfmetrics.registerFont(TTFont("Vazir", f"{fonts_dir}/Vazirmatn-Regular.ttf"))
    pdfmetrics.registerFont(TTFont("Vazir-Bold", f"{fonts_dir}/Vazirmatn-Bold.ttf"))
    _FONTS_REGISTERED = True


def _build_pdf(buffer, title, headers, data_rows):
    from reportlab.lib import colors
    from reportlab.lib.pagesizes import A4, landscape
    from reportlab.lib.styles import ParagraphStyle
    from reportlab.lib.units import cm
    from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer

    _register_pdf_fonts()
    doc = SimpleDocTemplate(buffer, pagesize=landscape(A4),
                             topMargin=1.2 * cm, bottomMargin=1.2 * cm,
                             leftMargin=1 * cm, rightMargin=1 * cm)
    title_style = ParagraphStyle("title", fontName="Vazir-Bold", fontSize=16, alignment=1, textColor=colors.HexColor("#9C7A22"))
    elements = [Paragraph(_persian(title), title_style), Spacer(1, 14)]

    header_row = [_persian(h) for h in reversed(headers)]
    table_data = [header_row]
    for row_vals in data_rows:
        table_data.append([_persian(str(v)) for v in reversed(row_vals)])

    table = Table(table_data, repeatRows=1)
    table.setStyle(TableStyle([
        ("FONTNAME", (0, 0), (-1, -1), "Vazir"),
        ("FONTNAME", (0, 0), (-1, 0), "Vazir-Bold"),
        ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#D4AF37")),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.black),
        ("GRID", (0, 0), (-1, -1), 0.4, colors.grey),
        ("ALIGN", (0, 0), (-1, -1), "CENTER"),
        ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
        ("FONTSIZE", (0, 0), (-1, -1), 8.5),
        ("ROWBACKGROUNDS", (0, 1), (-1, -1), [colors.white, colors.HexColor("#F7F0DC")]),
        ("TOPPADDING", (0, 0), (-1, -1), 5),
        ("BOTTOMPADDING", (0, 0), (-1, -1), 5),
    ]))
    elements.append(table)
    doc.build(elements)


def export_customers_excel(rows, buffer):
    from openpyxl import Workbook
    from openpyxl.styles import Font, Alignment, PatternFill

    wb = Workbook()
    ws = wb.active
    ws.title = "گزارش مشتریان"
    ws.sheet_view.rightToLeft = True

    with_range = any(r.get("range_weight") is not None for r in rows)
    headers = ["نام", "نام خانوادگی", "شهر", "شماره تماس", "وزن خرید (گرم)", "تعداد خرید"]
    if with_range:
        headers.append("خرید در بازه (گرم)")
    headers += ["رتبه", "تاریخ عضویت"]
    ws.append(headers)
    header_fill = PatternFill(start_color="D4AF37", end_color="D4AF37", fill_type="solid")
    for cell in ws[1]:
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center")

    for r in rows:
        c = r["customer"]
        values = [c.first_name, c.last_name, c.city or "-", c.mobile_phone or "-",
                  r["total_weight"], r["purchase_count"]]
        if with_range:
            values.append(r["range_weight"] or 0)
        values += [c.rating or "-", to_jalali_str(c.joined_at)]
        ws.append(values)

    for col in ws.columns:
        max_len = max((len(str(cell.value)) for cell in col if cell.value is not None), default=10)
        ws.column_dimensions[col[0].column_letter].width = max(12, min(30, max_len + 4))

    wb.save(buffer)


def export_salespeople_excel(calls, buffer):
    from openpyxl import Workbook
    from openpyxl.styles import Font, Alignment, PatternFill

    wb = Workbook()
    ws = wb.active
    ws.title = "گزارش فروشنده‌ها"
    ws.sheet_view.rightToLeft = True

    headers = ["تاریخ تماس", "فروشنده", "مشتری", "پاسخ داده شد؟", "نتیجه"]
    ws.append(headers)
    header_fill = PatternFill(start_color="D4AF37", end_color="D4AF37", fill_type="solid")
    for cell in ws[1]:
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center")

    for c in calls:
        ws.append([
            to_jalali_str(c.call_date, with_time=True),
            c.salesperson.full_name if c.salesperson else "-",
            f"{c.customer.first_name} {c.customer.last_name}",
            "بله" if c.answered else ("خیر" if c.answered is False else "-"),
            c.result_text or "-",
        ])

    for col in ws.columns:
        max_len = max((len(str(cell.value)) for cell in col if cell.value is not None), default=10)
        ws.column_dimensions[col[0].column_letter].width = max(12, min(40, max_len + 4))

    wb.save(buffer)


def export_customers_pdf(rows, buffer, title="گزارش مشتریان"):
    with_range = any(r.get("range_weight") is not None for r in rows)
    headers = ["نام", "نام خانوادگی", "شهر", "تماس", "وزن (گرم)", "تعداد خرید"]
    if with_range:
        headers.append("خرید در بازه (گرم)")
    headers.append("رتبه")
    data_rows = []
    for r in rows:
        values = [r["customer"].first_name, r["customer"].last_name, r["customer"].city or "-",
                  r["customer"].mobile_phone or "-", f"{r['total_weight']:,.1f}", r["purchase_count"]]
        if with_range:
            values.append(f"{(r['range_weight'] or 0):,.1f}")
        values.append(r["customer"].rating or "-")
        data_rows.append(values)
    _build_pdf(buffer, title, headers, data_rows)


def export_salespeople_pdf(calls, buffer, title="گزارش فروشنده‌ها"):
    headers = ["تاریخ", "فروشنده", "مشتری", "پاسخ", "نتیجه"]
    data_rows = [
        [to_jalali_str(c.call_date), c.salesperson.full_name if c.salesperson else "-",
         f"{c.customer.first_name} {c.customer.last_name}",
         "بله" if c.answered else ("خیر" if c.answered is False else "-"),
         (c.result_text or "-")[:40]]
        for c in calls
    ]
    _build_pdf(buffer, title, headers, data_rows)