from fastapi import APIRouter, Depends, HTTPException, Query
from sqlalchemy import select, func, delete
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.api.deps import get_db, get_superadmin_user
from app.core.models.user import User
from app.core.models.tenant import Tenant
from app.core.models.product import Product
from app.core.models.product_template import (
    ProductTemplate, TemplateImage, TemplateVariant, TemplateSource,
    TemplateCollection, template_collection_items, tenant_visible_collections,
)
from app.schemas.product_template import (
    TemplateCreate, TemplateUpdate, TemplateListOut, TemplateDetailOut,
    CollectionCreate, CollectionUpdate, CollectionOut, CollectionBrief,
    TemplateSourceOut, DuplicateSkuOut, TenantCollectionAssign,
)

router = APIRouter(
    prefix="/superadmin/templates",
    tags=["超管-商品模板"],
    dependencies=[Depends(get_superadmin_user)],
)


# ══════════════════════════════════════════════════════════════
#  Template CRUD
# ══════════════════════════════════════════════════════════════

@router.get("/", response_model=list[TemplateListOut])
async def list_templates(
    db: AsyncSession = Depends(get_db),
    status: str | None = None,
    collection_id: int | None = None,
    keyword: str | None = None,
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
):
    q = select(ProductTemplate).options(
        selectinload(ProductTemplate.images),
        selectinload(ProductTemplate.collections),
        selectinload(ProductTemplate.sources),
    )
    if status:
        q = q.where(ProductTemplate.status == status)
    if collection_id:
        q = q.join(template_collection_items).where(
            template_collection_items.c.collection_id == collection_id
        )
    if keyword:
        like = f"%{keyword}%"
        q = q.where(ProductTemplate.name.ilike(like) | ProductTemplate.sku.ilike(like))
    q = q.order_by(ProductTemplate.created_at.desc())
    q = q.offset((page - 1) * page_size).limit(page_size)
    result = await db.execute(q)
    templates = result.scalars().unique().all()

    import_counts = {}
    if templates:
        tids = [t.id for t in templates]
        cnt_q = (
            select(Product.source_template_id, func.count())
            .where(Product.source_template_id.in_(tids))
            .group_by(Product.source_template_id)
        )
        cnt_result = await db.execute(cnt_q)
        import_counts = dict(cnt_result.all())

    out = []
    for t in templates:
        primary = next((img for img in t.images if img.is_primary), None)
        cover = primary.url if primary else (t.images[0].url if t.images else None)
        out.append(TemplateListOut(
            id=t.id, sku=t.sku, name=t.name, name_en=t.name_en,
            base_price=float(t.base_price) if t.base_price else None,
            status=t.status, cover_url=cover,
            source_count=len(t.sources),
            import_count=import_counts.get(t.id, 0),
            collections=[CollectionBrief.model_validate(c) for c in t.collections],
            created_at=t.created_at,
        ))
    return out


@router.get("/count")
async def template_counts(db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        select(ProductTemplate.status, func.count()).group_by(ProductTemplate.status)
    )
    counts = dict(result.all())
    return {
        "total": sum(counts.values()),
        "pending": counts.get("pending", 0),
        "approved": counts.get("approved", 0),
        "rejected": counts.get("rejected", 0),
    }


@router.get("/{template_id}", response_model=TemplateDetailOut)
async def get_template(template_id: int, db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        select(ProductTemplate)
        .where(ProductTemplate.id == template_id)
        .options(
            selectinload(ProductTemplate.images),
            selectinload(ProductTemplate.variants),
            selectinload(ProductTemplate.sources),
            selectinload(ProductTemplate.collections),
        )
    )
    tpl = result.scalar_one_or_none()
    if not tpl:
        raise HTTPException(404, "模板不存在")

    sources_out = []
    if tpl.sources:
        tenant_ids = [s.tenant_id for s in tpl.sources]
        t_result = await db.execute(select(Tenant.id, Tenant.name).where(Tenant.id.in_(tenant_ids)))
        tenant_map = dict(t_result.all())
        for s in tpl.sources:
            sources_out.append(TemplateSourceOut(
                id=s.id, tenant_id=s.tenant_id, product_id=s.product_id,
                tenant_name=tenant_map.get(s.tenant_id), created_at=s.created_at,
            ))

    return TemplateDetailOut(
        **{c.name: getattr(tpl, c.name) for c in ProductTemplate.__table__.columns},
        images=tpl.images, variants=tpl.variants,
        sources=sources_out,
        collections=[CollectionBrief.model_validate(c) for c in tpl.collections],
    )


@router.post("/", response_model=TemplateDetailOut, status_code=201)
async def create_template(body: TemplateCreate, db: AsyncSession = Depends(get_db)):
    existing = await db.execute(
        select(ProductTemplate.id).where(ProductTemplate.sku == body.sku)
    )
    if existing.scalar_one_or_none():
        raise HTTPException(409, f"SKU {body.sku} 已存在")

    tpl = ProductTemplate(
        **body.model_dump(exclude={"images", "variants", "collection_ids"}),
    )
    db.add(tpl)
    await db.flush()

    for img in body.images:
        db.add(TemplateImage(template_id=tpl.id, **img.model_dump()))
    for var in body.variants:
        db.add(TemplateVariant(template_id=tpl.id, **var.model_dump()))

    if body.collection_ids:
        for cid in body.collection_ids:
            await db.execute(template_collection_items.insert().values(
                template_id=tpl.id, collection_id=cid
            ))

    await db.commit()
    return await get_template(tpl.id, db)


@router.put("/{template_id}", response_model=TemplateDetailOut)
async def update_template(
    template_id: int, body: TemplateUpdate, db: AsyncSession = Depends(get_db),
):
    result = await db.execute(
        select(ProductTemplate).where(ProductTemplate.id == template_id)
    )
    tpl = result.scalar_one_or_none()
    if not tpl:
        raise HTTPException(404, "模板不存在")

    data = body.model_dump(exclude_unset=True, exclude={"images", "variants", "collection_ids"})
    for k, v in data.items():
        setattr(tpl, k, v)

    if body.images is not None:
        await db.execute(delete(TemplateImage).where(TemplateImage.template_id == template_id))
        for img in body.images:
            db.add(TemplateImage(template_id=template_id, **img.model_dump()))

    if body.variants is not None:
        await db.execute(delete(TemplateVariant).where(TemplateVariant.template_id == template_id))
        for var in body.variants:
            db.add(TemplateVariant(template_id=template_id, **var.model_dump()))

    if body.collection_ids is not None:
        await db.execute(
            delete(template_collection_items).where(template_collection_items.c.template_id == template_id)
        )
        for cid in body.collection_ids:
            await db.execute(template_collection_items.insert().values(
                template_id=template_id, collection_id=cid,
            ))

    await db.commit()
    return await get_template(template_id, db)


@router.delete("/{template_id}", status_code=204)
async def delete_template(template_id: int, db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        select(ProductTemplate).where(ProductTemplate.id == template_id)
    )
    tpl = result.scalar_one_or_none()
    if not tpl:
        raise HTTPException(404, "模板不存在")
    await db.delete(tpl)
    await db.commit()


@router.patch("/{template_id}/status")
async def update_template_status(
    template_id: int,
    status: str = Query(..., regex="^(pending|approved|rejected)$"),
    db: AsyncSession = Depends(get_db),
):
    result = await db.execute(
        select(ProductTemplate).where(ProductTemplate.id == template_id)
    )
    tpl = result.scalar_one_or_none()
    if not tpl:
        raise HTTPException(404, "模板不存在")
    tpl.status = status
    await db.commit()
    return {"id": tpl.id, "status": tpl.status}


# ══════════════════════════════════════════════════════════════
#  Duplicate SKU management
# ══════════════════════════════════════════════════════════════

@router.get("/duplicates/list", response_model=list[DuplicateSkuOut])
async def list_duplicate_skus(db: AsyncSession = Depends(get_db)):
    sub = (
        select(TemplateSource.template_id)
        .group_by(TemplateSource.template_id)
        .having(func.count(TemplateSource.tenant_id.distinct()) > 1)
    )
    result = await db.execute(
        select(ProductTemplate)
        .options(selectinload(ProductTemplate.sources))
        .where(ProductTemplate.id.in_(sub))
    )
    templates = result.scalars().all()

    tenant_ids = set()
    for t in templates:
        for s in t.sources:
            tenant_ids.add(s.tenant_id)
    t_result = await db.execute(select(Tenant.id, Tenant.name).where(Tenant.id.in_(tenant_ids)))
    tenant_map = dict(t_result.all())

    return [
        DuplicateSkuOut(
            sku=t.sku, template_id=t.id, template_name=t.name,
            sources=[
                TemplateSourceOut(
                    id=s.id, tenant_id=s.tenant_id, product_id=s.product_id,
                    tenant_name=tenant_map.get(s.tenant_id), created_at=s.created_at,
                ) for s in t.sources
            ],
        ) for t in templates
    ]


# ══════════════════════════════════════════════════════════════
#  Collection CRUD
# ══════════════════════════════════════════════════════════════

@router.get("/collections/", response_model=list[CollectionOut])
async def list_collections(db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        select(TemplateCollection).order_by(TemplateCollection.sort_order)
    )
    collections = result.scalars().all()
    out = []
    for c in collections:
        tpl_cnt = await db.execute(
            select(func.count()).select_from(template_collection_items)
            .where(template_collection_items.c.collection_id == c.id)
        )
        ten_cnt = await db.execute(
            select(func.count()).select_from(tenant_visible_collections)
            .where(tenant_visible_collections.c.collection_id == c.id)
        )
        out.append(CollectionOut(
            id=c.id, name=c.name, name_en=c.name_en,
            description=c.description, sort_order=c.sort_order,
            template_count=tpl_cnt.scalar() or 0,
            tenant_count=ten_cnt.scalar() or 0,
            created_at=c.created_at,
        ))
    return out


@router.post("/collections/", response_model=CollectionOut, status_code=201)
async def create_collection(body: CollectionCreate, db: AsyncSession = Depends(get_db)):
    col = TemplateCollection(**body.model_dump())
    db.add(col)
    await db.commit()
    await db.refresh(col)
    return CollectionOut(
        id=col.id, name=col.name, name_en=col.name_en,
        description=col.description, sort_order=col.sort_order,
        template_count=0, tenant_count=0, created_at=col.created_at,
    )


# ══════════════════════════════════════════════════════════════
#  Tenant ↔ Collection Assignment  (must be before {collection_id} routes)
# ══════════════════════════════════════════════════════════════

@router.get("/collections/tenant/{tenant_id}", response_model=list[CollectionBrief])
async def get_tenant_collections(tenant_id: int, db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        select(TemplateCollection)
        .join(tenant_visible_collections)
        .where(tenant_visible_collections.c.tenant_id == tenant_id)
        .order_by(TemplateCollection.sort_order)
    )
    return [CollectionBrief.model_validate(c) for c in result.scalars().all()]


@router.put("/collections/tenant-assign")
async def assign_tenant_collections(
    body: TenantCollectionAssign, db: AsyncSession = Depends(get_db),
):
    await db.execute(
        delete(tenant_visible_collections).where(
            tenant_visible_collections.c.tenant_id == body.tenant_id
        )
    )
    for cid in body.collection_ids:
        await db.execute(tenant_visible_collections.insert().values(
            tenant_id=body.tenant_id, collection_id=cid,
        ))
    await db.commit()
    return {"tenant_id": body.tenant_id, "collection_ids": body.collection_ids}


async def _collection_out(col: TemplateCollection, db: AsyncSession) -> CollectionOut:
    tpl_cnt = await db.execute(
        select(func.count()).select_from(template_collection_items)
        .where(template_collection_items.c.collection_id == col.id)
    )
    ten_cnt = await db.execute(
        select(func.count()).select_from(tenant_visible_collections)
        .where(tenant_visible_collections.c.collection_id == col.id)
    )
    return CollectionOut(
        id=col.id, name=col.name, name_en=col.name_en,
        description=col.description, sort_order=col.sort_order,
        template_count=tpl_cnt.scalar() or 0,
        tenant_count=ten_cnt.scalar() or 0,
        created_at=col.created_at,
    )


@router.put("/collections/{collection_id}", response_model=CollectionOut)
async def update_collection(
    collection_id: int, body: CollectionUpdate, db: AsyncSession = Depends(get_db),
):
    result = await db.execute(
        select(TemplateCollection).where(TemplateCollection.id == collection_id)
    )
    col = result.scalar_one_or_none()
    if not col:
        raise HTTPException(404, "集合不存在")
    for k, v in body.model_dump(exclude_unset=True).items():
        setattr(col, k, v)
    await db.commit()
    await db.refresh(col)
    return await _collection_out(col, db)


@router.delete("/collections/{collection_id}", status_code=204)
async def delete_collection(collection_id: int, db: AsyncSession = Depends(get_db)):
    result = await db.execute(
        select(TemplateCollection).where(TemplateCollection.id == collection_id)
    )
    col = result.scalar_one_or_none()
    if not col:
        raise HTTPException(404, "集合不存在")
    await db.delete(col)
    await db.commit()