import io
import csv
import re
from typing import List, Optional
from datetime import datetime
from fastapi import APIRouter, Depends, HTTPException, Query, UploadFile, File, Response
from fastapi.responses import StreamingResponse
from sqlalchemy.orm import Session
from sqlalchemy import or_, desc
from app.database import get_db
from app.models.admin import AdminUser
from app.models.category import Category
from app.models.product import Product, ProductImage, ProductSpecification
from app.models.enquiry import CustomerEnquiry, EnquiryItem
from app.models.setting import SystemSetting
from app.schemas.admin import (
    AdminProductCreateUpdate,
    AdminProductResponse,
    AdminCategoryCreateUpdate,
    AdminCategoryResponse,
    AdminEnquiryResponse,
    AdminEnquiryItemResponse,
    AdminEnquiryStatusUpdate,
    AdminDashboardStats,
    AdminSettingUpdate,
    AdminSpecificationBase,
    AdminImageBase
)
from app.services.auth_service import get_current_admin
from app.services.image_service import save_uploaded_image, remove_uploaded_file

router = APIRouter(prefix="/api/admin", tags=["Admin Operations"], dependencies=[Depends(get_current_admin)])

def slugify(text: str) -> str:
    text = text.lower().strip()
    text = re.sub(r'[^\w\s-]', '', text)
    text = re.sub(r'[\s_-]+', '-', text)
    return re.sub(r'^-+|-+$', '', text)

# Helper to map product model to AdminProductResponse
def map_admin_product(product: Product) -> AdminProductResponse:
    specs = [
        AdminSpecificationBase(
            id=s.id,
            size_name=s.size_name,
            dimensions=s.dimensions,
            gauge=s.gauge,
            material_grade=s.material_grade,
            packing_unit=s.packing_unit,
            display_order=s.display_order
        ) for s in product.specifications
    ]
    imgs = [
        AdminImageBase(
            id=i.id,
            image_url=i.image_url,
            alt_text=i.alt_text,
            is_primary=i.is_primary,
            display_order=i.display_order
        ) for i in product.images
    ]
    return AdminProductResponse(
        id=product.id,
        category_id=product.category_id,
        category_name=product.category.name if product.category else None,
        name=product.name,
        slug=product.slug,
        description=product.description,
        features=product.features,
        meta_title=product.meta_title,
        meta_description=product.meta_description,
        is_active=product.is_active,
        is_featured=product.is_featured,
        display_order=product.display_order,
        private_mrp=product.private_mrp,
        private_cost_price=product.private_cost_price,
        private_notes=product.private_notes,
        created_at=product.created_at,
        updated_at=product.updated_at,
        specifications=specs,
        images=imgs
    )

# Helper to map enquiry model to AdminEnquiryResponse
def map_admin_enquiry(enq: CustomerEnquiry) -> AdminEnquiryResponse:
    items = [
        AdminEnquiryItemResponse(
            id=i.id,
            product_id=i.product_id,
            product_name=i.product_name,
            product_slug=i.product_slug,
            selected_specification=i.selected_specification,
            quantity=i.quantity,
            product_image_url=i.product_image_url
        ) for i in enq.items
    ]
    return AdminEnquiryResponse(
        id=enq.id,
        enquiry_number=enq.enquiry_number,
        customer_name=enq.customer_name,
        shop_name=enq.shop_name,
        mobile_whatsapp=enq.mobile_whatsapp,
        city=enq.city,
        message=enq.message,
        status=enq.status,
        admin_notes=enq.admin_notes,
        total_items=enq.total_items,
        created_at=enq.created_at,
        updated_at=enq.updated_at,
        items=items
    )

# ================= DASHBOARD & STATS =================
@router.get("/dashboard/stats", response_model=AdminDashboardStats)
async def get_dashboard_stats(db: Session = Depends(get_db)):
    new_count = db.query(CustomerEnquiry).filter(CustomerEnquiry.status == "New").count()
    total_enquiries = db.query(CustomerEnquiry).count()
    total_products = db.query(Product).count()
    active_products = db.query(Product).filter(Product.is_active == True).count()
    total_categories = db.query(Category).count()
    
    recent_enqs = (
        db.query(CustomerEnquiry)
        .order_by(CustomerEnquiry.created_at.desc())
        .limit(10)
        .all()
    )

    return AdminDashboardStats(
        new_enquiries_count=new_count,
        total_enquiries=total_enquiries,
        total_products=total_products,
        total_categories=total_categories,
        active_products=active_products,
        recent_enquiries=[map_admin_enquiry(e) for e in recent_enqs]
    )

# ================= CUSTOMER ENQUIRIES =================
@router.get("/enquiries", response_model=List[AdminEnquiryResponse])
async def list_enquiries(
    status_filter: Optional[str] = Query(None, alias="status"),
    city: Optional[str] = Query(None),
    search: Optional[str] = Query(None),
    limit: int = Query(50, ge=1, le=200),
    offset: int = Query(0, ge=0),
    db: Session = Depends(get_db)
):
    query = db.query(CustomerEnquiry)

    if status_filter and status_filter.strip() and status_filter != "All":
        query = query.filter(CustomerEnquiry.status == status_filter.strip())

    if city and city.strip():
        query = query.filter(CustomerEnquiry.city.ilike(f"%{city.strip()}%"))

    if search and search.strip():
        term = f"%{search.strip()}%"
        query = query.filter(
            or_(
                CustomerEnquiry.customer_name.ilike(term),
                CustomerEnquiry.shop_name.ilike(term),
                CustomerEnquiry.mobile_whatsapp.ilike(term),
                CustomerEnquiry.enquiry_number.ilike(term),
                CustomerEnquiry.items.any(EnquiryItem.product_name.ilike(term))
            )
        )

    enquiries = (
        query
        .order_by(CustomerEnquiry.created_at.desc())
        .offset(offset)
        .limit(limit)
        .all()
    )

    return [map_admin_enquiry(e) for e in enquiries]


@router.get("/enquiries/{enquiry_id}", response_model=AdminEnquiryResponse)
async def get_enquiry_detail(enquiry_id: int, db: Session = Depends(get_db)):
    enquiry = db.query(CustomerEnquiry).filter(CustomerEnquiry.id == enquiry_id).first()
    if not enquiry:
        raise HTTPException(status_code=404, detail="Enquiry not found")
    return map_admin_enquiry(enquiry)


@router.put("/enquiries/{enquiry_id}/status", response_model=AdminEnquiryResponse)
async def update_enquiry_status(
    enquiry_id: int,
    payload: AdminEnquiryStatusUpdate,
    db: Session = Depends(get_db)
):
    enquiry = db.query(CustomerEnquiry).filter(CustomerEnquiry.id == enquiry_id).first()
    if not enquiry:
        raise HTTPException(status_code=404, detail="Enquiry not found")

    enquiry.status = payload.status
    if payload.admin_notes is not None:
        enquiry.admin_notes = payload.admin_notes
    enquiry.updated_at = datetime.utcnow()
    db.commit()
    db.refresh(enquiry)
    return map_admin_enquiry(enquiry)


@router.delete("/enquiries/{enquiry_id}")
async def delete_enquiry(enquiry_id: int, db: Session = Depends(get_db)):
    enquiry = db.query(CustomerEnquiry).filter(CustomerEnquiry.id == enquiry_id).first()
    if not enquiry:
        raise HTTPException(status_code=404, detail="Enquiry not found")
    db.delete(enquiry)
    db.commit()
    return {"message": f"Enquiry #{enquiry.enquiry_number} deleted successfully"}


@router.get("/enquiries-export/csv")
async def export_enquiries_csv(db: Session = Depends(get_db)):
    enquiries = db.query(CustomerEnquiry).order_by(CustomerEnquiry.created_at.desc()).all()

    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow([
        "Enquiry No",
        "Date & Time",
        "Customer Name",
        "Shop / Business",
        "Mobile / WhatsApp",
        "City",
        "Status",
        "Product Details & Specs",
        "Total Quantity / Units",
        "Customer Message",
        "Admin Notes"
    ])

    for enq in enquiries:
        prods_text = " | ".join([
            f"{it.product_name} [{it.selected_specification or 'Standard'}] (Qty: {it.quantity})"
            for it in enq.items
        ])
        writer.writerow([
            enq.enquiry_number,
            enq.created_at.strftime("%Y-%m-%d %H:%M:%S") if enq.created_at else "",
            enq.customer_name,
            enq.shop_name or "",
            enq.mobile_whatsapp,
            enq.city,
            enq.status,
            prods_text,
            enq.total_items,
            enq.message or "",
            enq.admin_notes or ""
        ])

    output.seek(0)
    filename = f"nathji_enquiries_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}.csv"
    return StreamingResponse(
        iter([output.getvalue()]),
        media_type="text/csv",
        headers={"Content-Disposition": f"attachment; filename={filename}"}
    )

# ================= PRODUCTS MANAGEMENT =================
@router.get("/products", response_model=List[AdminProductResponse])
async def list_admin_products(
    category_id: Optional[int] = Query(None),
    search: Optional[str] = Query(None),
    db: Session = Depends(get_db)
):
    query = db.query(Product)
    if category_id:
        query = query.filter(Product.category_id == category_id)
    if search and search.strip():
        term = f"%{search.strip()}%"
        query = query.filter(or_(Product.name.ilike(term), Product.description.ilike(term)))
    
    products = query.order_by(Product.display_order.asc(), Product.id.desc()).all()
    return [map_admin_product(p) for p in products]


@router.get("/products/{product_id}", response_model=AdminProductResponse)
async def get_admin_product(product_id: int, db: Session = Depends(get_db)):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")
    return map_admin_product(product)


@router.post("/products", response_model=AdminProductResponse)
async def create_product(payload: AdminProductCreateUpdate, db: Session = Depends(get_db)):
    slug = payload.slug.strip() if payload.slug else slugify(payload.name)
    # Ensure slug uniqueness
    base_slug = slug
    counter = 1
    while db.query(Product).filter(Product.slug == slug).first():
        slug = f"{base_slug}-{counter}"
        counter += 1

    product = Product(
        category_id=payload.category_id,
        name=payload.name.strip(),
        slug=slug,
        description=payload.description,
        features=payload.features,
        meta_title=payload.meta_title or payload.name,
        meta_description=payload.meta_description,
        is_active=payload.is_active,
        is_featured=payload.is_featured,
        display_order=payload.display_order,
        private_mrp=payload.private_mrp,
        private_cost_price=payload.private_cost_price,
        private_notes=payload.private_notes
    )
    db.add(product)
    db.flush()

    # Add Specifications
    for idx, sp in enumerate(payload.specifications):
        db_spec = ProductSpecification(
            product_id=product.id,
            size_name=sp.size_name,
            dimensions=sp.dimensions,
            gauge=sp.gauge,
            material_grade=sp.material_grade,
            packing_unit=sp.packing_unit,
            display_order=sp.display_order or idx
        )
        db.add(db_spec)

    # Add Images if provided via URLs
    for idx, im in enumerate(payload.images):
        db_img = ProductImage(
            product_id=product.id,
            image_url=im.image_url,
            alt_text=im.alt_text or product.name,
            is_primary=im.is_primary or (idx == 0),
            display_order=im.display_order or idx
        )
        db.add(db_img)

    db.commit()
    db.refresh(product)
    return map_admin_product(product)


@router.put("/products/{product_id}", response_model=AdminProductResponse)
async def update_product(
    product_id: int,
    payload: AdminProductCreateUpdate,
    db: Session = Depends(get_db)
):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")

    product.category_id = payload.category_id
    product.name = payload.name.strip()
    if payload.slug and payload.slug != product.slug:
        # Check uniqueness
        if db.query(Product).filter(Product.slug == payload.slug, Product.id != product_id).first():
            raise HTTPException(status_code=400, detail="Slug already taken by another product")
        product.slug = payload.slug
    
    product.description = payload.description
    product.features = payload.features
    product.meta_title = payload.meta_title
    product.meta_description = payload.meta_description
    product.is_active = payload.is_active
    product.is_featured = payload.is_featured
    product.display_order = payload.display_order
    product.private_mrp = payload.private_mrp
    product.private_cost_price = payload.private_cost_price
    product.private_notes = payload.private_notes

    # Replace specifications
    db.query(ProductSpecification).filter(ProductSpecification.product_id == product_id).delete()
    for idx, sp in enumerate(payload.specifications):
        db_spec = ProductSpecification(
            product_id=product.id,
            size_name=sp.size_name,
            dimensions=sp.dimensions,
            gauge=sp.gauge,
            material_grade=sp.material_grade,
            packing_unit=sp.packing_unit,
            display_order=sp.display_order or idx
        )
        db.add(db_spec)

    db.commit()
    db.refresh(product)
    return map_admin_product(product)


@router.delete("/products/{product_id}")
async def delete_product(product_id: int, db: Session = Depends(get_db)):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")
    
    # Remove associated image files
    for img in product.images:
        remove_uploaded_file(img.image_url)

    db.delete(product)
    db.commit()
    return {"message": f"Product '{product.name}' deleted successfully"}


@router.post("/products/{product_id}/upload-images")
async def upload_product_images(
    product_id: int,
    files: List[UploadFile] = File(...),
    db: Session = Depends(get_db)
):
    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")

    has_primary = any(img.is_primary for img in product.images)
    uploaded = []

    for idx, file in enumerate(files):
        img_url = save_uploaded_image(file, folder="products")
        is_primary = False if has_primary else (idx == 0)
        p_img = ProductImage(
            product_id=product.id,
            image_url=img_url,
            alt_text=product.name,
            is_primary=is_primary,
            display_order=len(product.images) + idx
        )
        db.add(p_img)
        uploaded.append(img_url)

    db.commit()
    return {"message": f"Successfully uploaded {len(uploaded)} image(s)", "images": uploaded}


@router.delete("/products/images/{image_id}")
async def delete_product_image(image_id: int, db: Session = Depends(get_db)):
    image = db.query(ProductImage).filter(ProductImage.id == image_id).first()
    if not image:
        raise HTTPException(status_code=404, detail="Image not found")
    
    remove_uploaded_file(image.image_url)
    product_id = image.product_id
    was_primary = image.is_primary

    db.delete(image)
    db.commit()

    # If it was primary, assign primary to next available image
    if was_primary:
        next_img = db.query(ProductImage).filter(ProductImage.product_id == product_id).first()
        if next_img:
            next_img.is_primary = True
            db.commit()

    return {"message": "Image deleted successfully"}


@router.put("/products/images/{image_id}/primary")
async def set_primary_image(image_id: int, db: Session = Depends(get_db)):
    image = db.query(ProductImage).filter(ProductImage.id == image_id).first()
    if not image:
        raise HTTPException(status_code=404, detail="Image not found")

    # Set all other images for this product as not primary
    db.query(ProductImage).filter(ProductImage.product_id == image.product_id).update({"is_primary": False})
    image.is_primary = True
    db.commit()
    return {"message": "Primary image updated successfully"}

# ================= CATEGORIES MANAGEMENT =================
@router.get("/categories", response_model=List[AdminCategoryResponse])
async def list_admin_categories(db: Session = Depends(get_db)):
    categories = db.query(Category).order_by(Category.display_order.asc(), Category.name.asc()).all()
    res = []
    for cat in categories:
        count = db.query(Product).filter(Product.category_id == cat.id).count()
        res.append(
            AdminCategoryResponse(
                id=cat.id,
                name=cat.name,
                slug=cat.slug,
                description=cat.description,
                image_url=cat.image_url,
                icon_name=cat.icon_name,
                display_order=cat.display_order,
                is_active=cat.is_active,
                product_count=count,
                created_at=cat.created_at
            )
        )
    return res


@router.post("/categories", response_model=AdminCategoryResponse)
async def create_category(payload: AdminCategoryCreateUpdate, db: Session = Depends(get_db)):
    slug = payload.slug.strip() if payload.slug else slugify(payload.name)
    if db.query(Category).filter(Category.slug == slug).first():
        raise HTTPException(status_code=400, detail="Category slug already exists")

    cat = Category(
        name=payload.name.strip(),
        slug=slug,
        description=payload.description,
        image_url=payload.image_url,
        icon_name=payload.icon_name,
        display_order=payload.display_order,
        is_active=payload.is_active
    )
    db.add(cat)
    db.commit()
    db.refresh(cat)
    return AdminCategoryResponse(
        id=cat.id,
        name=cat.name,
        slug=cat.slug,
        description=cat.description,
        image_url=cat.image_url,
        icon_name=cat.icon_name,
        display_order=cat.display_order,
        is_active=cat.is_active,
        product_count=0,
        created_at=cat.created_at
    )


@router.put("/categories/{category_id}", response_model=AdminCategoryResponse)
async def update_category(
    category_id: int,
    payload: AdminCategoryCreateUpdate,
    db: Session = Depends(get_db)
):
    cat = db.query(Category).filter(Category.id == category_id).first()
    if not cat:
        raise HTTPException(status_code=404, detail="Category not found")

    cat.name = payload.name.strip()
    if payload.slug and payload.slug != cat.slug:
        if db.query(Category).filter(Category.slug == payload.slug, Category.id != category_id).first():
            raise HTTPException(status_code=400, detail="Category slug already exists")
        cat.slug = payload.slug
    
    cat.description = payload.description
    cat.image_url = payload.image_url
    cat.icon_name = payload.icon_name
    cat.display_order = payload.display_order
    cat.is_active = payload.is_active

    db.commit()
    db.refresh(cat)
    count = db.query(Product).filter(Product.category_id == cat.id).count()
    return AdminCategoryResponse(
        id=cat.id,
        name=cat.name,
        slug=cat.slug,
        description=cat.description,
        image_url=cat.image_url,
        icon_name=cat.icon_name,
        display_order=cat.display_order,
        is_active=cat.is_active,
        product_count=count,
        created_at=cat.created_at
    )


@router.delete("/categories/{category_id}")
async def delete_category(category_id: int, db: Session = Depends(get_db)):
    cat = db.query(Category).filter(Category.id == category_id).first()
    if not cat:
        raise HTTPException(status_code=404, detail="Category not found")
    
    p_count = db.query(Product).filter(Product.category_id == category_id).count()
    if p_count > 0:
        raise HTTPException(status_code=400, detail=f"Cannot delete category because it contains {p_count} product(s). Move or delete the products first.")

    db.delete(cat)
    db.commit()
    return {"message": f"Category '{cat.name}' deleted successfully"}

# ================= SETTINGS MANAGEMENT =================
@router.get("/settings")
async def get_all_settings(db: Session = Depends(get_db)):
    settings_list = db.query(SystemSetting).all()
    return {s.key_name: s.value for s in settings_list}


@router.put("/settings")
async def update_settings(payload: List[AdminSettingUpdate], db: Session = Depends(get_db)):
    for item in payload:
        setting = db.query(SystemSetting).filter(SystemSetting.key_name == item.key_name).first()
        if setting:
            setting.value = item.value
            if item.description:
                setting.description = item.description
        else:
            db.add(SystemSetting(key_name=item.key_name, value=item.value, description=item.description))
    db.commit()
    return {"message": "Settings updated successfully"}