import duckdb
from fastapi import FastAPI, Query
import os
from contextlib import asynccontextmanager

FILE_PATH = "/data/HDFC-Breach By @Majestic_Garden.csv"
con = duckdb.connect()

@asynccontextmanager
async def lifespan(app: FastAPI):
    print(f"🔍 Checking: {FILE_PATH}")
    if os.path.exists(FILE_PATH):
        size = os.path.getsize(FILE_PATH) / (1024**3)
        print(f"✅ File loaded: {size:.2f} GB")
        # Test read with all_varchar
        try:
            df = con.execute(f"SELECT * FROM read_csv_auto('{FILE_PATH}', header=True, ignore_errors=True, all_varchar=True) LIMIT 1").fetchdf()
            print(f"📋 Columns: {list(df.columns)}")
        except Exception as e:
            print(f"⚠️ Read error: {e}")
    else:
        print(f"❌ File NOT found at {FILE_PATH}")
    yield

app = FastAPI(lifespan=lifespan)

# CSV read function with all_varchar=True
def read_csv():
    return f"read_csv_auto('{FILE_PATH}', header=True, ignore_errors=True, all_varchar=True)"

@app.get("/")
async def root():
    return {
        "developer": "Gopal Parmar",
        "message": "HDFC Loan Database API",
        "endpoints": {
            "/stats": "Statistics",
            "/columns": "Column names",
            "/search/phone?q=NUMBER": "Search by phone",
            "/search/name?q=NAME": "Search by name",
            "/random": "Random person"
        }
    }

@app.get("/columns")
async def show_columns():
    if not os.path.exists(FILE_PATH):
        return {"error": "File not found", "developer": "Gopal Parmar"}
    try:
        df = con.execute(f"SELECT * FROM {read_csv()} LIMIT 0").fetchdf()
        return {
            "developer": "Gopal Parmar",
            "columns": list(df.columns),
            "count": len(df.columns)
        }
    except Exception as e:
        return {"error": str(e), "developer": "Gopal Parmar"}

@app.get("/stats")
async def stats():
    if not os.path.exists(FILE_PATH):
        return {"error": "File not found", "developer": "Gopal Parmar"}
    try:
        size = os.path.getsize(FILE_PATH) / (1024**3)
        count = con.execute(f"SELECT COUNT(*) FROM {read_csv()}").fetchone()[0]
        return {
            "developer": "Gopal Parmar",
            "size_gb": round(size, 2),
            "rows": count
        }
    except Exception as e:
        return {"error": str(e), "developer": "Gopal Parmar"}

@app.get("/search/phone")
async def search_phone(q: str = Query(...)):
    if not os.path.exists(FILE_PATH):
        return {"error": "File not found", "developer": "Gopal Parmar"}
    try:
        # Get column names first
        df_cols = con.execute(f"SELECT * FROM {read_csv()} LIMIT 0").fetchdf()
        columns = list(df_cols.columns)
        # Find phone column (search for 'mobile', 'phone', 'number')
        phone_col = None
        for col in columns:
            if 'mobile' in col.lower() or 'phone' in col.lower() or 'number' in col.lower():
                phone_col = col
                break
        if not phone_col and len(columns) > 20:
            phone_col = columns[20]  # fallback
        
        # Simple query with all_varchar=True so ILIKE works directly
        query = f"SELECT * FROM {read_csv()} WHERE \"{phone_col}\" ILIKE '%{q}%' LIMIT 10"
        df = con.execute(query).fetchdf()
        
        if len(df) == 0:
            return {"developer": "Gopal Parmar", "query": q, "count": 0, "results": []}
        
        return {
            "developer": "Gopal Parmar",
            "query": q,
            "phone_column": phone_col,
            "count": len(df),
            "results": df.to_dict(orient='records')
        }
    except Exception as e:
        return {"error": str(e), "developer": "Gopal Parmar"}

@app.get("/search/name")
async def search_name(q: str = Query(...)):
    if not os.path.exists(FILE_PATH):
        return {"error": "File not found", "developer": "Gopal Parmar"}
    try:
        df_cols = con.execute(f"SELECT * FROM {read_csv()} LIMIT 0").fetchdf()
        columns = list(df_cols.columns)
        # Find name column
        name_col = None
        for col in columns:
            if 'first' in col.lower() and 'name' in col.lower():
                name_col = col
                break
        if not name_col and len(columns) > 13:
            name_col = columns[13]  # fallback
        
        query = f"SELECT * FROM {read_csv()} WHERE \"{name_col}\" ILIKE '%{q}%' LIMIT 10"
        df = con.execute(query).fetchdf()
        
        return {
            "developer": "Gopal Parmar",
            "query": q,
            "name_column": name_col,
            "count": len(df),
            "results": df.to_dict(orient='records')
        }
    except Exception as e:
        return {"error": str(e), "developer": "Gopal Parmar"}

@app.get("/random")
async def random_person():
    if not os.path.exists(FILE_PATH):
        return {"error": "File not found", "developer": "Gopal Parmar"}
    try:
        query = f"SELECT * FROM {read_csv()} USING SAMPLE 1"
        df = con.execute(query).fetchdf()
        if len(df) == 0:
            return {"error": "No data", "developer": "Gopal Parmar"}
        return {
            "developer": "Gopal Parmar",
            "result": df.to_dict(orient='records')[0]
        }
    except Exception as e:
        return {"error": str(e), "developer": "Gopal Parmar"}