"""租户数据备份与恢复服务

导出指定租户全部数据为 JSON 结构；从 JSON 恢复数据到指定租户。
"""
import logging
from datetime import datetime, date
from decimal import Decimal
from typing import Any

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

logger = logging.getLogger("uvicorn.error")

# 按依赖顺序排列的备份表（先导出无外键依赖的，再导出有依赖的）
# 恢复时按此顺序插入；清除时反序删除
BACKUP_TABLES: list[dict[str, Any]] = [
    # ── 配置类 ──
    {"table": "tenant_settings", "fk_col": "tenant_id"},
    {"table": "theme_settings", "fk_col": "tenant_id"},
    {"table": "plugin_configs", "fk_col": "tenant_id"},
    {"table": "navigation_menus", "fk_col": "tenant_id"},
    {"table": "store_banners", "fk_col": "tenant_id"},
    {"table": "pages", "fk_col": "tenant_id"},
    {"table": "articles", "fk_col": "tenant_id"},
    {"table": "seo_redirects", "fk_col": "tenant_id"},
    {"table": "email_templates", "fk_col": "tenant_id"},
    {"table": "currencies", "fk_col": "tenant_id"},
    # ── 权限 ──
    {"table": "admin_roles", "fk_col": "tenant_id"},
    {"table": "admin_role_permissions", "fk_col": "tenant_id"},
    {"table": "admin_user_roles", "fk_col": "tenant_id"},
    {"table": "admin_user_permission_overrides", "fk_col": "tenant_id"},
    # ── 用户/员工 ──
    {"table": "users", "fk_col": "tenant_id"},
    # ── 会员 ──
    {"table": "member_levels", "fk_col": "tenant_id"},
    # ── 客户 ──
    {"table": "customers", "fk_col": "tenant_id"},
    {"table": "customer_addresses", "fk_col": "tenant_id"},
    {"table": "customer_wallets", "fk_col": "tenant_id"},
    {"table": "wallet_transactions", "fk_col": "tenant_id"},
    {"table": "member_points_ledger", "fk_col": "tenant_id"},
    # ── 商品 ──
    {"table": "brands", "fk_col": "tenant_id"},
    {"table": "categories", "fk_col": "tenant_id"},
    {"table": "products", "fk_col": "tenant_id"},
    {"table": "product_images", "fk_col": "tenant_id"},
    {"table": "product_variants", "fk_col": "tenant_id"},
    {"table": "product_tier_prices", "fk_col": "tenant_id"},
    {"table": "product_categories", "fk_col": None, "join": "products"},
    {"table": "product_reviews", "fk_col": "tenant_id"},
    # ── 支付 ──
    {"table": "payment_gateways", "fk_col": "tenant_id"},
    # ── 物流 ──
    {"table": "shipping_carriers", "fk_col": "tenant_id"},
    {"table": "shipping_zones", "fk_col": "tenant_id"},
    {"table": "shipping_methods", "fk_col": "tenant_id"},
    {"table": "shipping_rate_tiers", "fk_col": "tenant_id"},
    {"table": "shipping_surcharges", "fk_col": "tenant_id"},
    {"table": "carrier_packing_rules", "fk_col": "tenant_id"},
    # ── 折扣 ──
    {"table": "discounts", "fk_col": "tenant_id"},
    {"table": "discount_tiers", "fk_col": "tenant_id"},
    {"table": "discount_usage_logs", "fk_col": "tenant_id"},
    # ── 订单 ──
    {"table": "orders", "fk_col": "tenant_id"},
    {"table": "order_items", "fk_col": "tenant_id"},
    {"table": "payments", "fk_col": "tenant_id"},
    {"table": "order_delivery_logs", "fk_col": "tenant_id"},
    {"table": "refund_requests", "fk_col": "tenant_id"},
    # ── 日志 ──
    {"table": "email_logs", "fk_col": "tenant_id"},
]


def _serialize_value(v: Any) -> Any:
    if v is None:
        return None
    if isinstance(v, datetime):
        return v.isoformat()
    if isinstance(v, date):
        return v.isoformat()
    if isinstance(v, Decimal):
        return str(v)
    if isinstance(v, bytes):
        return v.decode("utf-8", errors="replace")
    return v


async def export_tenant_data(db: AsyncSession, tenant_id: int) -> dict:
    """导出指定租户的全部数据，返回 dict（可序列化为 JSON）"""
    backup: dict[str, Any] = {
        "version": "1.0",
        "exported_at": datetime.utcnow().isoformat(),
        "tenant_id": tenant_id,
        "tables": {},
    }

    for spec in BACKUP_TABLES:
        table = spec["table"]
        fk_col = spec.get("fk_col")

        if fk_col:
            sql = f"SELECT * FROM `{table}` WHERE `{fk_col}` = :tid"
        elif spec.get("join"):
            parent = spec["join"]
            sql = (
                f"SELECT t.* FROM `{table}` t "
                f"JOIN `{parent}` p ON p.id = t.product_id "
                f"WHERE p.tenant_id = :tid"
            )
        else:
            continue

        try:
            result = await db.execute(text(sql), {"tid": tenant_id})
        except Exception as e:
            if "doesn't exist" in str(e) or "1146" in str(e):
                logger.info("Backup: table %s does not exist, skipping", table)
                continue
            raise

        columns = list(result.keys())
        rows = []
        for row in result.fetchall():
            rows.append({col: _serialize_value(row[i]) for i, col in enumerate(columns)})

        backup["tables"][table] = {
            "columns": columns,
            "count": len(rows),
            "rows": rows,
        }
        logger.info("Backup tenant=%s table=%s rows=%d", tenant_id, table, len(rows))

    return backup


async def get_backup_stats(db: AsyncSession, tenant_id: int) -> dict:
    """快速统计租户数据量（不导出完整数据）"""
    stats: dict[str, int] = {}
    total = 0
    for spec in BACKUP_TABLES:
        table = spec["table"]
        fk_col = spec.get("fk_col")

        if fk_col:
            sql = f"SELECT COUNT(*) FROM `{table}` WHERE `{fk_col}` = :tid"
        elif spec.get("join"):
            parent = spec["join"]
            sql = (
                f"SELECT COUNT(*) FROM `{table}` t "
                f"JOIN `{parent}` p ON p.id = t.product_id "
                f"WHERE p.tenant_id = :tid"
            )
        else:
            continue

        try:
            cnt = (await db.execute(text(sql), {"tid": tenant_id})).scalar() or 0
        except Exception as e:
            if "doesn't exist" in str(e) or "1146" in str(e):
                logger.info("Backup stats: table %s does not exist, skipping", table)
                continue
            raise
        if cnt > 0:
            stats[table] = cnt
            total += cnt

    return {"tenant_id": tenant_id, "tables": stats, "total_rows": total}


async def restore_tenant_data(
    db: AsyncSession,
    tenant_id: int,
    backup_data: dict,
    clear_existing: bool = True,
) -> dict:
    """从备份 JSON 恢复租户数据

    Args:
        clear_existing: 恢复前是否清除该租户的现有数据（默认 True）
    Returns:
        恢复统计信息
    """
    tables_data: dict = backup_data.get("tables", {})
    restored: dict[str, int] = {}
    skipped: list[str] = []

    # 临时关闭外键检查
    await db.execute(text("SET FOREIGN_KEY_CHECKS = 0"))

    try:
        if clear_existing:
            # 反序删除现有数据
            for spec in reversed(BACKUP_TABLES):
                table = spec["table"]
                fk_col = spec.get("fk_col")
                try:
                    if fk_col:
                        await db.execute(
                            text(f"DELETE FROM `{table}` WHERE `{fk_col}` = :tid"),
                            {"tid": tenant_id},
                        )
                    elif spec.get("join"):
                        parent = spec["join"]
                        await db.execute(text(
                            f"DELETE t FROM `{table}` t "
                            f"JOIN `{parent}` p ON p.id = t.product_id "
                            f"WHERE p.tenant_id = :tid"
                        ), {"tid": tenant_id})
                    logger.info("Restore: cleared table=%s for tenant=%s", table, tenant_id)
                except Exception as e:
                    if "doesn't exist" in str(e) or "1146" in str(e):
                        continue
                    raise

        # 按顺序插入数据
        for spec in BACKUP_TABLES:
            table = spec["table"]
            fk_col = spec.get("fk_col")
            tdata = tables_data.get(table)
            if not tdata or not tdata.get("rows"):
                skipped.append(table)
                continue

            rows = tdata["rows"]
            columns = tdata.get("columns") or list(rows[0].keys())

            # 对于有 tenant_id 列的表，覆盖为目标 tenant_id
            override_tid = fk_col == "tenant_id" and "tenant_id" in columns

            try:
                for row in rows:
                    if override_tid:
                        row["tenant_id"] = tenant_id

                    # 去掉 id 列（让数据库自增分配新 ID）
                    row_data = {k: v for k, v in row.items() if k != "id"}
                    col_names = list(row_data.keys())
                    placeholders = ", ".join(f":{c}" for c in col_names)
                    col_list = ", ".join(f"`{c}`" for c in col_names)

                    await db.execute(
                        text(f"INSERT INTO `{table}` ({col_list}) VALUES ({placeholders})"),
                        row_data,
                    )

                restored[table] = len(rows)
                logger.info("Restore: inserted table=%s rows=%d for tenant=%s", table, len(rows), tenant_id)
            except Exception as e:
                if "doesn't exist" in str(e) or "1146" in str(e):
                    skipped.append(table)
                    continue
                raise

    finally:
        await db.execute(text("SET FOREIGN_KEY_CHECKS = 1"))

    await db.commit()

    return {
        "tenant_id": tenant_id,
        "restored": restored,
        "skipped": skipped,
        "total_rows": sum(restored.values()),
    }