# backend/app/plugins/advanced_stats/details.py
"""明细下钻：订单级与商品行级。

输出包含客户姓名、邮箱、电话与完整收货地址，属于 PII。
调用方必须已通过 adv_stats.detail 权限校验。
"""
from __future__ import annotations

from sqlalchemy import Select, and_, func, select

from app.core.models.customer import Customer
from app.core.models.order import Order, OrderItem
from app.core.models.product import Product
from app.plugins.advanced_stats.query import (
    apply_filters,
    base_conditions,
    needs_customer_join,
)
from app.plugins.advanced_stats.schemas import ReportQuery


def build_order_details(q: ReportQuery, tenant_id: int) -> Select:
    """订单级明细：一行一单，含客户与地址快照。"""
    stmt = (
        select(
            Order.id.label("order_id"),
            Order.order_no,
            Order.created_at,
            Order.paid_at,
            Order.status,
            Customer.name.label("customer_name"),
            Customer.email.label("customer_email"),
            Customer.phone.label("customer_phone"),
            Customer.member_level_id,
            Order.shipping_address,
            Order.billing_address,
            Order.subtotal,
            Order.discount_total,
            Order.shipping_total,
            Order.tax_total,
            Order.grand_total,
            func.coalesce(Order.display_currency, Order.currency).label("currency"),
            Order.shipping_method_id,
            Order.carrier,
            Order.tracking_no,
            Order.note,
        )
        .join(Customer, Customer.id == Order.customer_id)
        .where(and_(*base_conditions(q, tenant_id)))
    )
    return apply_filters(stmt, q, joined_customer=True).order_by(Order.id.desc())


def build_item_details(q: ReportQuery, tenant_id: int) -> Select:
    """商品行级明细：一行一个订单行。"""
    stmt = (
        select(
            OrderItem.id.label("item_id"),
            Order.id.label("order_id"),
            Order.order_no,
            Order.created_at,
            Order.status,
            Customer.name.label("customer_name"),
            Customer.email.label("customer_email"),
            OrderItem.product_id,
            OrderItem.variant_id,
            OrderItem.product_snapshot,
            Product.sku,
            Product.category_id,
            Product.brand_id,
            OrderItem.quantity,
            OrderItem.unit_price,
            OrderItem.total_price,
            OrderItem.tax_rate,
            OrderItem.tax_amount,
        )
        .join(Order, Order.id == OrderItem.order_id)
        .join(Customer, Customer.id == Order.customer_id)
        .outerjoin(Product, Product.id == OrderItem.product_id)
        .where(and_(*base_conditions(q, tenant_id)), OrderItem.tenant_id == tenant_id)
    )
    if q.product_keyword:
        kw = f"%{q.product_keyword}%"
        stmt = stmt.where((Product.name.like(kw)) | (Product.sku.like(kw)))
    if q.category_ids:
        stmt = stmt.where(Product.category_id.in_(q.category_ids))
    if q.brand_ids:
        stmt = stmt.where(Product.brand_id.in_(q.brand_ids))
    if q.product_ids:
        stmt = stmt.where(OrderItem.product_id.in_(q.product_ids))
    return apply_filters(stmt, q, joined_customer=True).order_by(OrderItem.id.desc())