from fastapi import APIRouter , UploadFile , File , Form  , HTTPException
from src.service.models import FileMeta
from src.pipeline.gen_query import generate_query
import time  , os
from src.pipeline.schema_extract import extract_schema 
from src.pipeline.sql_engine import execute_query
import shutil 


router = APIRouter() 


@router.get("/")
async def test():
    return {"status":"success" , "messages":"Everything is Fine"}

UPLOAD_DIR = "uploads"
os.makedirs(UPLOAD_DIR , exist_ok=True)

@router.post("/upload") 
async def upload_file_gen_query(file: UploadFile = File(...) ,
                                user_query: str = Form(...)):

    try: 
        FileMeta(filename=file.filename ,
                user_query=user_query) 
    except Exception as e: 
        raise HTTPException(status_code=400, detail=str(e)) 
    
    allowed_types = [
        "text/csv",
        "application/sql",
        "application/octet-stream"  # for .db
    ]

    if file.content_type not in allowed_types:
        raise HTTPException(
            status_code=400,
            detail=f"Invalid file type: {file.content_type}"
        )
    db_save_path = os.path.join(UPLOAD_DIR, file.filename)
    try:
        with open(db_save_path, "wb") as f:
            shutil.copyfileobj(file.file, f)
    except Exception as e:
        return {"status": "error", "message": f"Failed to save file: {e}"}


    try: 
        schema = extract_schema(file.filename) 
        print(f"Schema : {schema} ")
        start_time = time.time()
        sql_result = generate_query(user_query, schema) 
        print(f"Generated SQL stages: {sql_result}")

        results = execute_query(db_save_path, sql_result["final"])  


        print("✅✅  Final Query : ", sql_result["final"])
        print("results : " , results)
        return {
            "status": "success",
            "query_stages": {                        
                "stage_1": sql_result["stage_1"],
                "stage_2": sql_result["stage_2"],
                "stage_3": sql_result["stage_3"],
            },
            "final_query": sql_result["final"],
            "data": results,
            "row_count": len(results),
            "time_required": f"{time.time() - start_time:.2f}s"
        }
    
    
    except Exception as e: 
        
        return {"status":"error" , "message":f"got error {e}"}