from typing import Any, List
from fastapi import APIRouter, Depends, HTTPException, UploadFile, File
from fastapi.responses import Response
from sqlalchemy.orm import Session
from backend.app.api import deps
from backend.app.core.database import get_db
from backend.app.models import sql_models
from backend.app.schemas import schemas
from backend.app.services.llm_agent import test_connection
import openpyxl
import io

router = APIRouter()

@router.get("", response_model=List[schemas.ModelConfig])
def read_models(
    db: Session = Depends(get_db),
    skip: int = 0,
    limit: int = 100,
    current_user: sql_models.User = Depends(deps.get_current_active_superuser)
) -> Any:
    return db.query(sql_models.ModelConfig).offset(skip).limit(limit).all()

@router.get("/export_template")
def export_template(
    db: Session = Depends(get_db),
    current_user: sql_models.User = Depends(deps.get_current_active_superuser)
) -> Any:
    """
    Export model configuration template (including current data)
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Models"
    
    # Headers
    headers = ["Name", "Base URL", "API Key", "Model Name"]
    ws.append(headers)
    
    # Data
    models = db.query(sql_models.ModelConfig).all()
    for model in models:
        ws.append([model.name, model.base_url, model.api_key, model.model_name])
        
    # Adjust column width
    ws.column_dimensions['A'].width = 20
    ws.column_dimensions['B'].width = 40
    ws.column_dimensions['C'].width = 40
    ws.column_dimensions['D'].width = 20
    
    # Save to buffer
    buffer = io.BytesIO()
    wb.save(buffer)
    buffer.seek(0)
    
    return Response(
        content=buffer.getvalue(),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=models_template.xlsx"}
    )

@router.post("/import_template")
async def import_template(
    file: UploadFile = File(...),
    db: Session = Depends(get_db),
    current_user: sql_models.User = Depends(deps.get_current_active_superuser)
) -> Any:
    """
    Import models from template
    """
    if not file.filename.endswith(('.xlsx', '.xls')):
        raise HTTPException(status_code=400, detail="Invalid file format. Please upload an Excel file.")
    
    try:
        contents = await file.read()
        wb = openpyxl.load_workbook(io.BytesIO(contents))
        ws = wb.active
        
        # Read headers
        rows = list(ws.rows)
        if not rows:
            return {"message": "Empty file"}
            
        header_row = rows[0]
        headers = [cell.value for cell in header_row]
        
        # Validate headers
        required_headers = ["Name", "Base URL", "API Key", "Model Name"]
        if not all(h in headers for h in required_headers):
            raise HTTPException(status_code=400, detail=f"Invalid template format. Required columns: {', '.join(required_headers)}")
            
        name_idx = headers.index("Name")
        url_idx = headers.index("Base URL")
        key_idx = headers.index("API Key")
        model_name_idx = headers.index("Model Name")
        
        added_count = 0
        updated_count = 0
        
        # Process data
        for row in rows[1:]:
            name = row[name_idx].value
            base_url = row[url_idx].value
            api_key = row[key_idx].value
            model_name = row[model_name_idx].value
            
            if not name or not base_url or not model_name:
                continue
                
            # Check if model exists by Name
            existing_model = db.query(sql_models.ModelConfig).filter(sql_models.ModelConfig.name == name).first()
            
            if existing_model:
                # Update details
                existing_model.base_url = base_url
                existing_model.api_key = api_key
                existing_model.model_name = model_name
                updated_count += 1
            else:
                # Create new model
                new_model = sql_models.ModelConfig(
                    name=name,
                    base_url=base_url,
                    api_key=api_key,
                    model_name=model_name
                )
                db.add(new_model)
                added_count += 1
                
        db.commit()
        
        return {
            "message": "Import successful",
            "added": added_count,
            "updated": updated_count
        }
        
    except HTTPException as e:
        raise e
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Import failed: {str(e)}")

@router.post("", response_model=schemas.ModelConfig)
def create_model(
    *,
    db: Session = Depends(get_db),
    model_in: schemas.ModelConfigCreate,
    current_user: sql_models.User = Depends(deps.get_current_active_superuser)
) -> Any:
    model = sql_models.ModelConfig(**model_in.model_dump())
    db.add(model)
    db.commit()
    db.refresh(model)
    return model

@router.put("/{id}", response_model=schemas.ModelConfig)
def update_model(
    *,
    db: Session = Depends(get_db),
    id: int,
    model_in: schemas.ModelConfigCreate,
    current_user: sql_models.User = Depends(deps.get_current_active_superuser)
) -> Any:
    model = db.query(sql_models.ModelConfig).filter(sql_models.ModelConfig.id == id).first()
    if not model:
        raise HTTPException(status_code=404, detail="Model config not found")
    
    for key, value in model_in.model_dump().items():
        setattr(model, key, value)
    
    db.commit()
    db.refresh(model)
    return model

@router.delete("/{id}", response_model=schemas.ModelConfig)
def delete_model(
    *,
    db: Session = Depends(get_db),
    id: int,
    current_user: sql_models.User = Depends(deps.get_current_active_superuser)
) -> Any:
    model = db.query(sql_models.ModelConfig).filter(sql_models.ModelConfig.id == id).first()
    if not model:
        raise HTTPException(status_code=404, detail="Model config not found")
    
    db.delete(model)
    db.commit()
    return model

@router.post("/test")
async def test_model_config(
    *,
    model_in: schemas.ModelConfigCreate,
    current_user: sql_models.User = Depends(deps.get_current_active_superuser)
) -> Any:
    try:
        result = await test_connection(
            base_url=model_in.base_url,
            api_key=model_in.api_key,
            model_name=model_in.model_name
        )
        return {"status": "success", "message": result}
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))
