from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from sqlalchemy import func
from app.database import get_db
from app.models.tender import TenderModel
from app.models.oc import OCModel
from app.models.analysis import AnalysisHistoryModel
from app.services.sync import sync_tenders_to_db, sync_purchase_orders_to_db
from datetime import datetime

router = APIRouter()

@router.get("/admin/db-stats")
def get_detailed_stats(db: Session = Depends(get_db)):
    try:
        tenders_count = db.query(TenderModel).count()
        ocs_count = db.query(OCModel).count()
        analysis_count = db.query(AnalysisHistoryModel).count()
        
        # Get top 5 buyers by tender count
        top_buyers = db.query(
            TenderModel.buyer, 
            func.count(TenderModel.code).label("count")
        ).group_by(TenderModel.buyer).order_by(func.count(TenderModel.code).desc()).limit(5).all()
        
        top_buyers_list = [{"name": b[0], "count": b[1]} for b in top_buyers]
        
        # Get last sync date (max of last_updated)
        last_tender = db.query(func.max(TenderModel.last_updated)).scalar()
        
        return {
            "total_records": tenders_count,
            "total_ocs": ocs_count,
            "total_analysis": analysis_count,
            "top_buyers": top_buyers_list,
            "last_sync": last_tender.isoformat() if last_tender else None,
            "status": "Healthy"
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@router.delete("/admin/db-clear")
def clear_database(db: Session = Depends(get_db)):
    try:
        num_tenders = db.query(TenderModel).delete()
        num_ocs = db.query(OCModel).delete()
        db.commit()
        return {
            "message": "Database cleared successfully",
            "deleted": {
                "tenders": num_tenders,
                "purchase_orders": num_ocs
            }
        }
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))

@router.post("/admin/sync-all")
async def sync_all_data(db: Session = Depends(get_db)):
    try:
        tender_results = await sync_tenders_to_db(db)
        oc_results = await sync_purchase_orders_to_db(db)
        return {
            "tenders": tender_results,
            "purchase_orders": oc_results,
            "timestamp": datetime.utcnow().isoformat()
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))