import calendar
import re
from datetime import date, datetime, timedelta
from decimal import Decimal
from typing import Any

from sqlalchemy import and_, or_
from sqlalchemy.orm import Session

from app.core.models import (
    Customer,
    Expense,
    GstReturn,
    Invoice,
    InvoiceStatus,
    Payment,
    Product,
    Reminder,
    Subscription,
)


SUPPORTED_QUERY_TYPES = {
    "revenue",
    "stats",
    "invoice",
    "invoices",
    "customer",
    "customers",
    "product",
    "products",
    "expense",
    "expenses",
    "subscription",
    "subscriptions",
    "reminder",
    "reminders",
    "gst",
    "gst_summary",
}


def build_query_from_message(message: str) -> dict[str, Any] | None:
    """Build safe read-only query intent from common English or Chinese wording."""
    text = str(message or "").strip()
    if not text:
        return None

    normalized = text.lower()
    language = "zh" if _contains_chinese(text) else "en"
    revenue_terms = (
        "revenue",
        "income",
        "sales",
        "turnover",
        "cash received",
        "paid invoice",
        "收入",
        "营收",
        "营业额",
        "销售额",
        "收款",
        "已付款发票",
    )
    if not any(term in normalized for term in revenue_terms):
        return None

    filters: dict[str, Any] = {}
    today = date.today()
    month_range = _month_range_from_message(text, normalized)
    if month_range:
        filters["date_from"] = month_range[0].isoformat()
        filters["date_to"] = month_range[1].isoformat()
    elif any(term in normalized for term in ("今年", "本年", "this year", "this year's", "year to date", "ytd")):
        filters["date_from"] = today.replace(month=1, day=1).isoformat()
        filters["date_to"] = today.isoformat()
    elif any(term in normalized for term in ("本月", "这个月", "this month")):
        filters["date_range"] = "this_month"
    elif any(term in normalized for term in ("上月", "上个月", "last month")):
        filters["date_range"] = "last_month"
    elif any(term in normalized for term in ("最近30天", "近30天", "last 30 days", "recent month")):
        filters["date_range"] = "recent_month"

    return {
        "action": "query",
        "query_type": "revenue",
        "language": language,
        "filters": filters,
    }


def execute_ai_query(db: Session, query_data: dict[str, Any], tenant_id: int) -> dict[str, Any]:
    """Execute an AI-requested query against real ERP data."""
    query_type = str(query_data.get("query_type") or "").strip().lower()
    language = _query_language(query_data)
    if query_type not in SUPPORTED_QUERY_TYPES:
        if language == "zh":
            return {
                "reply": "我暂时还不能执行这个查询。目前可以查询收入、发票、客户、支出、订阅、提醒和 GST 汇总。",
                "action": "error",
            }
        return {
            "reply": (
                "I cannot run that query yet. I can currently query revenue, invoices, "
                "customers, expenses, subscriptions, reminders, and GST summaries."
            ),
            "action": "error",
        }

    filters = query_data.get("filters") if isinstance(query_data.get("filters"), dict) else {}
    date_range = _resolve_date_range(filters)
    if query_type in {"revenue", "stats"} and not date_range:
        if language == "zh":
            return {
                "reply": "请提供日期范围，或直接说“今年”“本月”“上月”或“最近30天”。",
                "action": "clarify",
            }
        return {
            "reply": 'Please provide a date range or say "this month", "last month", or "recent month."',
            "action": "clarify",
        }

    if query_type in {"revenue", "stats"}:
        return _query_revenue(db, date_range[0], date_range[1], language=language, tenant_id=tenant_id)
    if query_type in {"invoice", "invoices"}:
        return _query_invoices(db, filters, date_range, tenant_id=tenant_id)
    if query_type in {"customer", "customers"}:
        return _query_customers(db, filters, tenant_id=tenant_id)
    if query_type in {"product", "products"}:
        return _query_products(db, filters, tenant_id=tenant_id)
    if query_type in {"expense", "expenses"}:
        return _query_expenses(db, filters, date_range, tenant_id=tenant_id)
    if query_type in {"subscription", "subscriptions"}:
        return _query_subscriptions(db, filters, date_range, tenant_id=tenant_id)
    if query_type in {"reminder", "reminders"}:
        return _query_reminders(db, filters, tenant_id=tenant_id)
    if query_type in {"gst", "gst_summary"}:
        return _query_gst_summary(db, filters, date_range, tenant_id=tenant_id)

    return _unsupported_query()


def _unsupported_query() -> dict[str, Any]:
    return {
        "reply": (
            "I cannot run that query yet. I can currently query revenue, invoices, "
            "customers, expenses, subscriptions, reminders, and GST summaries."
        ),
        "action": "error",
    }


def _contains_chinese(text: str) -> bool:
    return any("\u4e00" <= char <= "\u9fff" for char in text)


def _month_range_from_message(text: str, normalized: str) -> tuple[date, date] | None:
    year = date.today().year
    numeric_match = re.search(r"(?<!\d)(1[0-2]|0?[1-9])\s*(?:月|月份)", text)
    if numeric_match:
        return _month_bounds(year, int(numeric_match.group(1)))

    chinese_months = {
        "一月": 1,
        "二月": 2,
        "三月": 3,
        "四月": 4,
        "五月": 5,
        "六月": 6,
        "七月": 7,
        "八月": 8,
        "九月": 9,
        "十月": 10,
        "十一月": 11,
        "十二月": 12,
    }
    for name, month in chinese_months.items():
        if name in text:
            return _month_bounds(year, month)

    english_months = {
        "january": 1,
        "jan": 1,
        "february": 2,
        "feb": 2,
        "march": 3,
        "mar": 3,
        "april": 4,
        "apr": 4,
        "may": 5,
        "june": 6,
        "jun": 6,
        "july": 7,
        "jul": 7,
        "august": 8,
        "aug": 8,
        "september": 9,
        "sept": 9,
        "sep": 9,
        "october": 10,
        "oct": 10,
        "november": 11,
        "nov": 11,
        "december": 12,
        "dec": 12,
    }
    for name, month in english_months.items():
        if re.search(rf"\b{name}\b", normalized):
            return _month_bounds(year, month)

    return None


def _month_bounds(year: int, month: int) -> tuple[date, date]:
    return date(year, month, 1), date(year, month, calendar.monthrange(year, month)[1])


def _query_language(query_data: dict[str, Any]) -> str:
    language = str(query_data.get("language") or "").strip().lower()
    return "zh" if language.startswith("zh") else "en"


def _resolve_date_range(filters: dict[str, Any]) -> tuple[date, date] | None:
    date_from = _parse_date(filters.get("date_from"))
    date_to = _parse_date(filters.get("date_to"))
    if date_from and date_to:
        return date_from, date_to

    range_name = str(filters.get("date_range") or filters.get("period") or "").strip().lower()
    today = date.today()
    if range_name in {"recent_month", "recent month", "last_30_days", "last 30 days"}:
        return today - timedelta(days=30), today
    if range_name in {"this_month", "this month"}:
        return today.replace(day=1), today
    if range_name in {"last_month", "last month"}:
        first_this_month = today.replace(day=1)
        last_previous_month = first_this_month - timedelta(days=1)
        return last_previous_month.replace(day=1), last_previous_month
    if range_name in {"this_year", "this year", "year_to_date", "year to date", "ytd", "今年", "本年"}:
        return today.replace(month=1, day=1), today

    return None


def _parse_date(value: Any) -> date | None:
    if isinstance(value, date) and not isinstance(value, datetime):
        return value
    if isinstance(value, datetime):
        return value.date()
    if not value:
        return None
    try:
        return datetime.strptime(str(value), "%Y-%m-%d").date()
    except ValueError:
        return None


def _query_revenue(db: Session, date_from: date, date_to: date, tenant_id: int, language: str = "en") -> dict[str, Any]:
    payments = (
        db.query(Payment)
        .filter(Payment.tenant_id == tenant_id)
        .join(Invoice, Payment.invoice_id == Invoice.id)
        .filter(
            or_(
                and_(Payment.received_date >= date_from, Payment.received_date <= date_to),
                and_(
                    Payment.received_date.is_(None),
                    Payment.paid_at >= datetime.combine(date_from, datetime.min.time()),
                    Payment.paid_at < datetime.combine(date_to + timedelta(days=1), datetime.min.time()),
                ),
            )
        )
        .order_by(Payment.received_date.asc(), Payment.paid_at.asc(), Payment.id.asc())
        .all()
    )

    paid_invoices = (
        db.query(Invoice)
        .filter(
            Invoice.tenant_id == tenant_id,
            Invoice.status == InvoiceStatus.paid,
            Invoice.invoice_date >= date_from,
            Invoice.invoice_date <= date_to,
        )
        .order_by(Invoice.invoice_date.asc(), Invoice.invoice_number.asc())
        .all()
    )

    cash_rows = [_payment_row(db, payment) for payment in payments]
    invoice_rows = [_invoice_row(db, invoice) for invoice in paid_invoices]
    cash_total = sum((Decimal(str(row["amount"])) for row in cash_rows), Decimal("0"))
    paid_invoice_total = sum((Decimal(str(row["total_amount"])) for row in invoice_rows), Decimal("0"))

    result = {
        "period": {
            "date_from": date_from.isoformat(),
            "date_to": date_to.isoformat(),
        },
        "cash_received": {
            "total": float(cash_total),
            "currency": _currency_for_rows(cash_rows) or "NZD",
            "payment_count": len(cash_rows),
            "rows": cash_rows,
        },
        "paid_invoices": {
            "total": float(paid_invoice_total),
            "currency": _currency_for_rows(invoice_rows) or "NZD",
            "invoice_count": len(invoice_rows),
            "rows": invoice_rows,
        },
    }

    return {
        "reply": _format_revenue_reply(result, language=language),
        "action": "query_result",
        "source": "database",
        "tool_name": "query_revenue",
        "result": result,
    }


def _query_invoices(db: Session, filters: dict[str, Any], date_range: tuple[date, date] | None, tenant_id: int) -> dict[str, Any]:
    query = db.query(Invoice).filter(Invoice.tenant_id == tenant_id)
    status = str(filters.get("status") or "").strip()
    invoice_number = str(filters.get("invoice_number") or "").strip()
    customer_name = str(filters.get("customer_name") or filters.get("customer") or "").strip()
    if status:
        query = query.filter(Invoice.status == status)
    if invoice_number:
        query = query.filter(Invoice.invoice_number.ilike(f"%{invoice_number}%"))
    if customer_name:
        query = query.join(Customer, Invoice.customer_id == Customer.id).filter(Customer.name.ilike(f"%{customer_name}%"))
    if date_range:
        query = query.filter(Invoice.invoice_date >= date_range[0], Invoice.invoice_date <= date_range[1])
    rows = [_invoice_row(db, invoice) for invoice in query.order_by(Invoice.invoice_date.desc(), Invoice.id.desc()).limit(20).all()]
    return _simple_query_result("query_invoices", "Invoice results", rows)


def _query_customers(db: Session, filters: dict[str, Any], tenant_id: int) -> dict[str, Any]:
    query = db.query(Customer).filter(Customer.tenant_id == tenant_id)
    keyword = str(filters.get("keyword") or filters.get("name") or filters.get("customer") or "").strip()
    status = str(filters.get("status") or "").strip()
    if keyword:
        query = query.filter(
            or_(
                Customer.name.ilike(f"%{keyword}%"),
                Customer.company_name.ilike(f"%{keyword}%"),
                Customer.email.ilike(f"%{keyword}%"),
            )
        )
    if status:
        query = query.filter(Customer.status == status)
    rows = [_customer_row(customer) for customer in query.order_by(Customer.name.asc()).limit(20).all()]
    return _simple_query_result("query_customers", "Customer results", rows)


def _query_products(db: Session, filters: dict[str, Any], tenant_id: int) -> dict[str, Any]:
    query = db.query(Product).filter(Product.tenant_id == tenant_id)
    keyword = str(filters.get("keyword") or filters.get("name") or filters.get("product") or "").strip()
    status = str(filters.get("status") or "").strip()
    product_type = str(filters.get("product_type") or filters.get("type") or "").strip()
    if keyword:
        query = query.filter(Product.name.ilike(f"%{keyword}%"))
    if status:
        query = query.filter(Product.status == status)
    if product_type:
        query = query.filter(Product.product_type == product_type)
    rows = [_product_row(product) for product in query.order_by(Product.name.asc()).limit(20).all()]
    return _simple_query_result("query_products", "Product results", rows)


def _query_expenses(db: Session, filters: dict[str, Any], date_range: tuple[date, date] | None, tenant_id: int) -> dict[str, Any]:
    query = db.query(Expense).filter(Expense.tenant_id == tenant_id)
    status = str(filters.get("status") or "").strip()
    vendor = str(filters.get("vendor") or filters.get("vendor_name") or "").strip()
    category = str(filters.get("category") or "").strip()
    if status:
        query = query.filter(Expense.status == status)
    if vendor:
        query = query.filter(Expense.vendor_name.ilike(f"%{vendor}%"))
    if category:
        query = query.filter(Expense.category == category)
    if date_range:
        query = query.filter(Expense.expense_date >= date_range[0], Expense.expense_date <= date_range[1])
    rows = [_expense_row(expense) for expense in query.order_by(Expense.expense_date.desc(), Expense.id.desc()).limit(20).all()]
    return _simple_query_result("query_expenses", "Expense results", rows)


def _query_subscriptions(db: Session, filters: dict[str, Any], date_range: tuple[date, date] | None, tenant_id: int) -> dict[str, Any]:
    query = db.query(Subscription).filter(Subscription.tenant_id == tenant_id)
    status = str(filters.get("status") or "").strip()
    customer_name = str(filters.get("customer") or filters.get("customer_name") or "").strip()
    if status:
        query = query.filter(Subscription.status == status)
    if customer_name:
        query = query.join(Customer, Subscription.customer_id == Customer.id).filter(Customer.name.ilike(f"%{customer_name}%"))
    if date_range:
        query = query.filter(or_(Subscription.end_date.is_(None), and_(Subscription.end_date >= date_range[0], Subscription.end_date <= date_range[1])))
    rows = [_subscription_row(db, sub) for sub in query.order_by(Subscription.end_date.asc(), Subscription.id.desc()).limit(20).all()]
    return _simple_query_result("query_subscriptions", "Subscription results", rows)


def _query_reminders(db: Session, filters: dict[str, Any], tenant_id: int) -> dict[str, Any]:
    query = db.query(Reminder).filter(Reminder.tenant_id == tenant_id)
    status = str(filters.get("status") or "").strip()
    reminder_type = str(filters.get("reminder_type") or filters.get("type") or "").strip()
    if status:
        query = query.filter(Reminder.status == status)
    if reminder_type:
        query = query.filter(Reminder.reminder_type == reminder_type)
    rows = [_reminder_row(reminder) for reminder in query.order_by(Reminder.id.desc()).limit(20).all()]
    return _simple_query_result("query_reminders", "Reminder results", rows)


def _query_gst_summary(db: Session, filters: dict[str, Any], date_range: tuple[date, date] | None, tenant_id: int) -> dict[str, Any]:
    query = db.query(GstReturn).filter(GstReturn.tenant_id == tenant_id)
    status = str(filters.get("status") or "").strip()
    if status:
        query = query.filter(GstReturn.status == status)
    if date_range:
        query = query.filter(GstReturn.period_start <= date_range[1], GstReturn.period_end >= date_range[0])
    rows = [_gst_return_row(gst_return) for gst_return in query.order_by(GstReturn.period_start.desc()).limit(20).all()]
    return _simple_query_result("query_gst_summary", "GST summary results", rows)


def _payment_row(db: Session, payment: Payment) -> dict[str, Any]:
    invoice = db.get(Invoice, payment.invoice_id)
    customer = db.get(Customer, invoice.customer_id) if invoice else None
    payment_date = payment.received_date or (payment.paid_at.date() if payment.paid_at else None)
    return {
        "invoice_number": invoice.invoice_number if invoice else "",
        "customer": customer.name if customer else "",
        "received_date": payment_date.isoformat() if payment_date else "",
        "amount": float(payment.amount or 0),
        "currency": payment.currency or (invoice.currency if invoice else "NZD"),
        "method": payment.method or "",
        "reference": payment.reference or "",
    }


def _invoice_row(db: Session, invoice: Invoice) -> dict[str, Any]:
    customer = db.get(Customer, invoice.customer_id)
    return {
        "invoice_number": invoice.invoice_number,
        "customer": customer.name if customer else "",
        "invoice_date": invoice.invoice_date.isoformat() if invoice.invoice_date else "",
        "total_amount": float(invoice.total_amount or 0),
        "currency": invoice.currency or "NZD",
        "status": invoice.status.value if hasattr(invoice.status, "value") else str(invoice.status),
    }


def _customer_row(customer: Customer) -> dict[str, Any]:
    return {
        "id": customer.id,
        "name": customer.name,
        "company_name": customer.company_name or "",
        "email": customer.email or "",
        "customer_type": customer.customer_type.value if hasattr(customer.customer_type, "value") else str(customer.customer_type),
        "status": customer.status.value if hasattr(customer.status, "value") else str(customer.status),
    }


def _expense_row(expense: Expense) -> dict[str, Any]:
    return {
        "id": expense.id,
        "vendor_name": expense.vendor_name,
        "expense_date": expense.expense_date.isoformat() if expense.expense_date else "",
        "category": expense.category,
        "amount_gross": float(expense.amount_gross or 0),
        "gst_amount": float(expense.gst_amount or 0),
        "currency": expense.currency or "NZD",
        "status": expense.status or "",
        "source": expense.source or "",
    }


def _product_row(product: Product) -> dict[str, Any]:
    return {
        "id": product.id,
        "name": product.name,
        "product_type": product.product_type.value if hasattr(product.product_type, "value") else str(product.product_type),
        "unit_price": float(product.unit_price or 0),
        "tax_rate": float(product.tax_rate or 0),
        "unit": product.unit or "",
        "status": product.status.value if hasattr(product.status, "value") else str(product.status),
    }


def _subscription_row(db: Session, sub: Subscription) -> dict[str, Any]:
    customer = db.get(Customer, sub.customer_id)
    product = db.get(Product, sub.product_id)
    return {
        "id": sub.id,
        "customer": customer.name if customer else "",
        "product": product.name if product else "",
        "start_date": sub.start_date.isoformat() if sub.start_date else "",
        "end_date": sub.end_date.isoformat() if sub.end_date else "",
        "status": sub.status.value if hasattr(sub.status, "value") else str(sub.status),
        "billing_cycle": sub.billing_cycle.value if hasattr(sub.billing_cycle, "value") else str(sub.billing_cycle),
        "auto_renew": bool(sub.auto_renew),
        "next_invoice_date": sub.next_invoice_date.isoformat() if sub.next_invoice_date else "",
    }


def _reminder_row(reminder: Reminder) -> dict[str, Any]:
    return {
        "id": reminder.id,
        "reminder_type": reminder.reminder_type.value if hasattr(reminder.reminder_type, "value") else str(reminder.reminder_type),
        "trigger_days": reminder.trigger_days,
        "send_email": bool(reminder.send_email),
        "send_telegram": bool(reminder.send_telegram),
        "status": reminder.status.value if hasattr(reminder.status, "value") else str(reminder.status),
    }


def _gst_return_row(gst_return: GstReturn) -> dict[str, Any]:
    return {
        "id": gst_return.id,
        "period_start": gst_return.period_start.isoformat() if gst_return.period_start else "",
        "period_end": gst_return.period_end.isoformat() if gst_return.period_end else "",
        "status": gst_return.status or "",
        "gst_output": float(gst_return.gst_output or 0),
        "gst_input": float(gst_return.gst_input or 0),
        "gst_payable": float(gst_return.gst_payable or 0),
        "total_sales_income": float(gst_return.total_sales_income or 0),
        "total_purchases_expenses": float(gst_return.total_purchases_expenses or 0),
    }


def _currency_for_rows(rows: list[dict[str, Any]]) -> str | None:
    currencies = {str(row.get("currency") or "").strip() for row in rows if row.get("currency")}
    return currencies.pop() if len(currencies) == 1 else None


def _format_money(currency: str | None, amount: float) -> str:
    return f"{currency or 'NZD'} {amount:,.2f}"


def _simple_query_result(tool_name: str, title: str, rows: list[dict[str, Any]]) -> dict[str, Any]:
    result = {"count": len(rows), "rows": rows}
    lines = [
        "The following figures come from database queries, not AI-generated data.",
        "",
        f"{title}: {len(rows)}",
    ]
    for row in rows:
        lines.append("- " + " | ".join(f"{key}: {value}" for key, value in row.items() if value not in ("", None)))
    if not rows:
        lines.append("- No matching records.")
    return {
        "reply": "\n".join(lines),
        "action": "query_result",
        "source": "database",
        "tool_name": tool_name,
        "result": result,
    }


def _format_revenue_reply(result: dict[str, Any], language: str = "en") -> str:
    period = result["period"]
    cash = result["cash_received"]
    invoices = result["paid_invoices"]
    if language == "zh":
        lines = [
            "以下数据来自数据库查询，不是 AI 生成的数据。",
            "",
            f"期间：{period['date_from']} 至 {period['date_to']}",
            "",
            "实际收款：",
            f"- 合计：{_format_money(cash['currency'], cash['total'])}",
            f"- 收款笔数：{cash['payment_count']}",
            "",
            "已付款发票合计：",
            f"- 合计：{_format_money(invoices['currency'], invoices['total'])}",
            f"- 发票数量：{invoices['invoice_count']}",
            "",
            "两者可能不同：实际收款按收款日期统计；已付款发票合计按当前标记为 paid 的发票日期统计。",
            "",
            "实际收款明细：",
        ]

        if cash["rows"]:
            for row in cash["rows"]:
                lines.append(
                    f"- {row['invoice_number']} | {row['customer']} | {row['received_date']} | "
                    f"{_format_money(row['currency'], row['amount'])} | {row['method']} | {row['reference'] or '-'}"
                )
        else:
            lines.append("- 没有匹配的收款记录。")

        lines.extend(["", "已付款发票明细："])
        if invoices["rows"]:
            for row in invoices["rows"]:
                lines.append(
                    f"- {row['invoice_number']} | {row['customer']} | {row['invoice_date']} | "
                    f"{_format_money(row['currency'], row['total_amount'])} | {row['status']}"
                )
        else:
            lines.append("- 没有匹配的已付款发票。")

        return "\n".join(lines)

    lines = [
        "The following figures come from database queries, not AI-generated data.",
        "",
        f"Period: {period['date_from']} to {period['date_to']}",
        "",
        "Cash received:",
        f"- Total: {_format_money(cash['currency'], cash['total'])}",
        f"- Payment count: {cash['payment_count']}",
        "",
        "Paid invoice totals:",
        f"- Total: {_format_money(invoices['currency'], invoices['total'])}",
        f"- Invoice count: {invoices['invoice_count']}",
        "",
        "Why they may differ: cash received uses payment received dates. Paid invoice totals use invoice dates for invoices currently marked paid.",
        "",
        "Cash received details:",
    ]

    if cash["rows"]:
        for row in cash["rows"]:
            lines.append(
                f"- {row['invoice_number']} | {row['customer']} | {row['received_date']} | "
                f"{_format_money(row['currency'], row['amount'])} | {row['method']} | {row['reference'] or '-'}"
            )
    else:
        lines.append("- No matching payments.")

    lines.extend(["", "Paid invoice details:"])
    if invoices["rows"]:
        for row in invoices["rows"]:
            lines.append(
                f"- {row['invoice_number']} | {row['customer']} | {row['invoice_date']} | "
                f"{_format_money(row['currency'], row['total_amount'])} | {row['status']}"
            )
    else:
        lines.append("- No matching paid invoices.")

    return "\n".join(lines)