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.video_core import video_manager
import openpyxl
import io

router = APIRouter()

@router.get("", response_model=List[schemas.Camera])
def read_cameras(
    db: Session = Depends(get_db),
    skip: int = 0,
    limit: int = 100,
    current_user: sql_models.User = Depends(deps.get_current_user)
) -> Any:
    cameras = db.query(sql_models.Camera).offset(skip).limit(limit).all()
    # Update status from video_manager
    for cam in cameras:
        stream = video_manager.streams.get(cam.id)
        if stream:
            cam.status = stream.status
        else:
            cam.status = 0
    return cameras

@router.get("/export_template")
def export_template(
    db: Session = Depends(get_db),
    current_user: sql_models.User = Depends(deps.get_current_user)
) -> Any:
    """
    Export camera configuration template (including current data)
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Cameras"
    
    # Headers
    headers = ["Name", "Stream URL"]
    ws.append(headers)
    
    # Data
    cameras = db.query(sql_models.Camera).all()
    for cam in cameras:
        ws.append([cam.name, cam.stream_url])
        
    # Adjust column width
    ws.column_dimensions['A'].width = 30
    ws.column_dimensions['B'].width = 60
    
    # 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=cameras_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_user)
) -> Any:
    """
    Import cameras 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
        if "Name" not in headers or "Stream URL" not in headers:
            raise HTTPException(status_code=400, detail="Invalid template format. Required columns: Name, Stream URL")
            
        name_idx = headers.index("Name")
        url_idx = headers.index("Stream URL")
        
        added_count = 0
        updated_count = 0
        
        # Process data
        for row in rows[1:]:
            name = row[name_idx].value
            url = row[url_idx].value
            
            if not name or not url:
                continue
                
            # Check if camera exists by URL
            existing_cam = db.query(sql_models.Camera).filter(sql_models.Camera.stream_url == url).first()
            
            if existing_cam:
                # Update name if changed
                if existing_cam.name != name:
                    existing_cam.name = name
                    updated_count += 1
            else:
                # Create new camera
                new_cam = sql_models.Camera(name=name, stream_url=url)
                db.add(new_cam)
                db.flush() # Get ID
                video_manager.add_stream(new_cam.id, new_cam.stream_url)
                added_count += 1
                
        db.commit()
        
        return {
            "message": "Import successful",
            "added": added_count,
            "updated": updated_count
        }
        
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Import failed: {str(e)}")

@router.post("", response_model=schemas.Camera)
def create_camera(
    *,
    db: Session = Depends(get_db),
    camera_in: schemas.CameraCreate,
    current_user: sql_models.User = Depends(deps.get_current_user)
) -> Any:
    camera = sql_models.Camera(name=camera_in.name, stream_url=camera_in.stream_url)
    db.add(camera)
    db.commit()
    db.refresh(camera)
    
    # Start stream
    video_manager.add_stream(camera.id, camera.stream_url)
    
    return camera

@router.put("/{id}", response_model=schemas.Camera)
def update_camera(
    *,
    db: Session = Depends(get_db),
    id: int,
    camera_in: schemas.CameraUpdate,
    current_user: sql_models.User = Depends(deps.get_current_user)
) -> Any:
    camera = db.query(sql_models.Camera).filter(sql_models.Camera.id == id).first()
    if not camera:
        raise HTTPException(status_code=404, detail="Camera not found")
    
    camera.name = camera_in.name
    camera.stream_url = camera_in.stream_url
    
    db.commit()
    db.refresh(camera)
    
    # Restart stream with new URL
    video_manager.add_stream(camera.id, camera.stream_url)
    
    return camera

@router.delete("/{id}", response_model=schemas.Camera)
def delete_camera(
    *,
    db: Session = Depends(get_db),
    id: int,
    current_user: sql_models.User = Depends(deps.get_current_user)
) -> Any:
    camera = db.query(sql_models.Camera).filter(sql_models.Camera.id == id).first()
    if not camera:
        raise HTTPException(status_code=404, detail="Camera not found")
    
    # Stop stream
    video_manager.stop_stream(id)
    
    db.delete(camera)
    db.commit()
    return camera

@router.post("/test")
def test_camera(
    *,
    camera_in: schemas.CameraTest,
    current_user: sql_models.User = Depends(deps.get_current_user)
) -> Any:
    snapshot = video_manager.test_camera_connection(camera_in.stream_url)
    if not snapshot:
        raise HTTPException(status_code=400, detail="Unable to connect to camera stream")
    return {"status": "ok", "image": snapshot}