from __future__ import annotations

import asyncio
import json
import logging

from fastapi import APIRouter, Depends, HTTPException, Query
from pydantic import BaseModel
from sqlalchemy import func, select, desc, text
from sqlalchemy.ext.asyncio import AsyncSession

from app.database import AsyncSessionLocal

from app.api.deps import get_db, require_permission
from app.core.services.plugin_helper import require_plugin
from app.plugins.cin7_import.client import Cin7Client, Cin7ApiError
from app.plugins.cin7_import.models import Cin7SyncLog, Cin7SyncState
from app.plugins.cin7_import.schemas import (
    Cin7ConnectionStatus,
    Cin7SyncLogOut,
    Cin7SyncLogPage,
    Cin7SyncResult,
)
from app.plugins.cin7_import.services import Cin7ImportService

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

router = APIRouter(prefix="/cin7-import", tags=["Cin7 数据同步"])


# ── 辅助函数 ──────────────────────────────────────────────────────

async def _load_config(db: AsyncSession, tenant_id: int) -> dict:
    row = await db.execute(
        text("SELECT config FROM plugin_configs WHERE tenant_id=:tid AND plugin_name='cin7_import' AND is_active=1 LIMIT 1"),
        {"tid": tenant_id},
    )
    r = row.fetchone()
    if not r:
        return {}
    cfg = r[0]
    if isinstance(cfg, str):
        cfg = json.loads(cfg)
    return cfg if isinstance(cfg, dict) else {}


async def _save_config(db: AsyncSession, tenant_id: int, patch: dict) -> None:
    """Merge patch into existing plugin config, upsert plugin_configs row."""
    existing = await _load_config(db, tenant_id)
    merged = {**existing, **patch}
    merged_str = json.dumps(merged)

    row = await db.execute(
        text("SELECT id FROM plugin_configs WHERE tenant_id=:tid AND plugin_name='cin7_import' LIMIT 1"),
        {"tid": tenant_id},
    )
    r = row.fetchone()
    if r:
        await db.execute(
            text("UPDATE plugin_configs SET config=:cfg, is_active=1 WHERE id=:id"),
            {"cfg": merged_str, "id": r[0]},
        )
    else:
        await db.execute(
            text("INSERT INTO plugin_configs (tenant_id, plugin_name, config, is_active) VALUES (:tid, 'cin7_import', :cfg, 1)"),
            {"tid": tenant_id, "cfg": merged_str},
        )
    await db.commit()


def _build_client(cfg: dict) -> Cin7Client:
    username = cfg.get("api_username")
    password = cfg.get("api_password")
    if not username or not password:
        raise HTTPException(status_code=400, detail="请先填写 Cin7 API Username 和 Password")
    return Cin7Client(username, password, cfg.get("base_url"))


def _cin7_error_to_http(e: Cin7ApiError) -> HTTPException:
    msg = str(e)
    if "429" in msg:
        return HTTPException(status_code=429, detail="Cin7 API 频率限制（3次/秒，60次/分钟），请稍后重试")
    if "401" in msg or "403" in msg:
        return HTTPException(status_code=400, detail="Cin7 API 认证失败，请检查 API 凭证")
    return HTTPException(status_code=502, detail=f"Cin7 API 错误：{msg[:200]}")


def _bool(cfg: dict, key: str, default: bool = True) -> bool:
    val = cfg.get(key)
    if val is None:
        return default
    if isinstance(val, bool):
        return val
    return str(val).lower() == "true"


def _parse_level_mapping(cfg: dict) -> dict:
    raw = cfg.get("level_mapping")
    if not raw:
        return {}
    if isinstance(raw, dict):
        return raw
    if isinstance(raw, str):
        try:
            return json.loads(raw)
        except Exception:
            return {}
    return {}


def _build_sync_options(cfg: dict) -> dict:
    return {
        "sync_price": _bool(cfg, "sync_price", True),
        "auto_delist_zero_stock": _bool(cfg, "auto_delist_zero_stock", True),
        "sync_status": _bool(cfg, "sync_status", True),
        "publish_field": (cfg.get("publish_field") or "").strip(),
        "publish_field_value": (cfg.get("publish_field_value") or "").strip(),
        "price_col_default": (cfg.get("price_col_default") or "").strip(),
        "price_col_market": (cfg.get("price_col_market") or "").strip(),
        "price_col_cost": (cfg.get("price_col_cost") or "").strip(),
        "level_mapping": _parse_level_mapping(cfg),
    }


# ── 路由 ──────────────────────────────────────────────────────────

@router.get("/status", response_model=Cin7ConnectionStatus, summary="获取 Cin7 连接状态")
async def get_status(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    cfg = await _load_config(db, tid)

    username = cfg.get("api_username")
    if not username:
        return Cin7ConnectionStatus(connected=False)

    states = {}
    for resource in ("products", "categories", "brands", "contacts"):
        result = await db.execute(
            select(Cin7SyncState.last_success_at).where(
                Cin7SyncState.tenant_id == tid,
                Cin7SyncState.resource == resource,
            )
        )
        states[f"{resource}_last_sync"] = result.scalar_one_or_none()

    return Cin7ConnectionStatus(
        connected=True,
        api_username=username,
        **states,
    )


class Cin7CredentialsIn(BaseModel):
    api_username: str
    api_password: str


@router.put("/credentials", summary="保存 Cin7 API 凭证")
async def save_credentials(
    body: Cin7CredentialsIn,
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    await _save_config(db, tid, {
        "api_username": body.api_username.strip(),
        "api_password": body.api_password.strip(),
    })
    return {"ok": True}


@router.post("/test-connection", summary="测试 Cin7 API 连接")
async def test_connection(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    cfg = await _load_config(db, tid)
    client = _build_client(cfg)
    try:
        ok = await client.test_connection()
    except Cin7ApiError as e:
        raise _cin7_error_to_http(e)
    if not ok:
        raise HTTPException(status_code=400, detail="Cin7 API 连接失败，请检查凭据")
    return {"ok": True, "message": "连接成功"}


@router.post("/sync/all", summary="全量同步（分类+品牌+商品+客户）")
async def sync_all(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    await require_plugin("cin7_import", db, tid)
    cfg = await _load_config(db, tid)
    client = _build_client(cfg)
    opts = _build_sync_options(cfg)
    asyncio.ensure_future(_bg_sync(tid, client, opts, "all"))
    return {"ok": True, "message": "全量同步已开始"}


@router.post("/sync/categories", summary="同步分类")
async def sync_categories(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    await require_plugin("cin7_import", db, tid)
    cfg = await _load_config(db, tid)
    client = _build_client(cfg)
    opts = _build_sync_options(cfg)
    asyncio.ensure_future(_bg_sync(tid, client, opts, "categories"))
    return {"ok": True, "message": "分类同步已开始"}


@router.post("/sync/brands", summary="同步品牌")
async def sync_brands(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    await require_plugin("cin7_import", db, tid)
    cfg = await _load_config(db, tid)
    client = _build_client(cfg)
    opts = _build_sync_options(cfg)
    asyncio.ensure_future(_bg_sync(tid, client, opts, "brands"))
    return {"ok": True, "message": "品牌同步已开始"}


@router.post("/sync/products", summary="同步商品")
async def sync_products(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    await require_plugin("cin7_import", db, tid)
    cfg = await _load_config(db, tid)
    client = _build_client(cfg)
    opts = _build_sync_options(cfg)
    asyncio.ensure_future(_bg_sync(tid, client, opts, "products"))
    return {"ok": True, "message": "商品同步已开始"}


@router.post("/sync/contacts", summary="同步客户")
async def sync_contacts(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    await require_plugin("cin7_import", db, tid)
    cfg = await _load_config(db, tid)
    client = _build_client(cfg)
    opts = _build_sync_options(cfg)
    asyncio.ensure_future(_bg_sync(tid, client, opts, "contacts"))
    return {"ok": True, "message": "客户同步已开始"}

async def _bg_sync(tid: int, client, opts: dict, resource: str):
    try:
        async with AsyncSessionLocal() as bg_db:
            svc = Cin7ImportService(bg_db, tid, client, sync_options=opts)
            if resource == "all":
                await svc.sync_all(mode="manual")
            elif resource == "categories":
                await svc.sync_categories(mode="manual")
            elif resource == "brands":
                await svc.sync_brands(mode="manual")
            elif resource == "products":
                await svc.sync_products(mode="manual")
            elif resource == "contacts":
                await svc.sync_contacts(mode="manual")
    except Exception as e:
        logger.error("[cin7_import] background sync %s failed: %s", resource, e, exc_info=True)


@router.get("/cin7-price-columns", summary="从 Cin7 实时拉取价格列")
async def get_cin7_price_columns(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    cfg = await _load_config(db, tid)
    client = _build_client(cfg)

    try:
        price_col_keys: set[str] = set()
        products = await client.get_products()
        for sp in products[:50]:
            pc = sp.get("priceColumns") or {}
            for k, v in pc.items():
                if v is not None:
                    price_col_keys.add(k)
            if sp.get("vipPrice"):
                price_col_keys.add("vipPrice")
            for opt in (sp.get("productOptions") or [])[:5]:
                opc = opt.get("priceColumns") or {}
                for k, v in opc.items():
                    if v is not None:
                        price_col_keys.add(k)

        customer_price_columns: set[str] = set()
        contacts = await client.get_contacts(contact_type="Customer")
        for c in contacts:
            pc = (c.get("priceColumn") or "").strip()
            if pc:
                customer_price_columns.add(pc)
                price_col_keys.add(pc)

        sorted_keys = sorted(price_col_keys)
        return {
            "price_columns": [
                {"key": k, "used_by_contacts": k in customer_price_columns}
                for k in sorted_keys
            ],
            "total_products_sampled": min(len(products), 50),
            "total_contacts": len(contacts),
        }
    except Cin7ApiError as e:
        raise _cin7_error_to_http(e)


@router.get("/local-member-levels", summary="获取本地会员等级列表")
async def get_local_member_levels(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    from app.core.models.member import MemberLevel
    tid = user.tenant_id
    result = await db.execute(
        select(MemberLevel.id, MemberLevel.name, MemberLevel.code, MemberLevel.rank)
        .where(MemberLevel.tenant_id == tid, MemberLevel.is_active == 1)
        .order_by(MemberLevel.rank)
    )
    return [{"id": r.id, "name": r.name, "code": r.code, "rank": r.rank} for r in result.all()]


@router.get("/cin7-product-fields", summary="返回第一个商品的所有原始字段（调试用）")
async def get_cin7_product_fields(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    cfg = await _load_config(db, tid)
    client = _build_client(cfg)
    try:
        products = await client._get_all_pages("v1/Products", rows=1)
    except Cin7ApiError as e:
        raise _cin7_error_to_http(e)
    if not products:
        return {"fields": []}
    sp = products[0]
    return {
        "fields": [
            {"key": k, "type": type(v).__name__, "value": str(v)[:120]}
            for k, v in sp.items()
        ]
    }


@router.get("/cin7-boolean-fields", summary="从 Cin7 扫描商品顶层布尔字段")
async def get_cin7_boolean_fields(
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    cfg = await _load_config(db, tid)
    client = _build_client(cfg)

    try:
        products = await client.get_products()
    except Cin7ApiError as e:
        raise _cin7_error_to_http(e)
    BOOL_LIKE = {True, False, 0, 1}
    bool_field_values: dict[str, set] = {}
    bool_true_count: dict[str, int] = {}
    str_field_values: dict[str, set] = {}

    for sp in products[:100]:
        for key, val in sp.items():
            if isinstance(val, (dict, list)):
                continue
            if isinstance(val, str):
                if val:
                    str_field_values.setdefault(key, set()).add(val)
                continue
            if val not in BOOL_LIKE:
                bool_field_values[key] = {"INVALID"}
                continue
            if key not in bool_field_values:
                bool_field_values[key] = set()
                bool_true_count[key] = 0
            bool_field_values[key].add(val)
            if val:
                bool_true_count[key] += 1

    sampled = min(len(products), 100)
    bool_fields = [
        {"key": k, "field_type": "bool", "true_count": bool_true_count.get(k, 0),
         "total_sampled": sampled, "values": None}
        for k, vs in bool_field_values.items()
        if "INVALID" not in vs and bool_true_count.get(k, 0) > 0
    ]
    str_fields = [
        {"key": k, "field_type": "string", "true_count": 0,
         "total_sampled": sampled, "values": sorted(vs)}
        for k, vs in str_field_values.items()
        if 1 <= len(vs) <= 10
    ]
    return {"bool_fields": sorted(bool_fields + str_fields, key=lambda x: x["key"])}


@router.get("/logs", response_model=Cin7SyncLogPage, summary="获取同步日志（分页）")
async def get_logs(
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    db: AsyncSession = Depends(get_db),
    user=Depends(require_permission("plugins.configure")),
):
    tid = user.tenant_id
    base = select(Cin7SyncLog).where(Cin7SyncLog.tenant_id == tid)
    total_result = await db.execute(select(func.count()).select_from(base.subquery()))
    total = total_result.scalar() or 0
    result = await db.execute(
        base.order_by(desc(Cin7SyncLog.created_at))
        .offset((page - 1) * page_size)
        .limit(page_size)
    )
    return Cin7SyncLogPage(
        items=[Cin7SyncLogOut.model_validate(r) for r in result.scalars().all()],
        total=total,
        page=page,
        page_size=page_size,
    )