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

app = FastAPI()
con = None

@asynccontextmanager
async def lifespan(app: FastAPI):
    global con
    print("🟢 Starting...")
    con = duckdb.connect()
    con.execute("INSTALL httpfs; LOAD httpfs;")
    
    PARQUET_URL = "https://huggingface.co/datasets/tfqdeadlo/Inddatainonefile/resolve/main/users_data.parquet"
    
    try:
        print("🟡 Loading parquet...")
        con.execute(f"CREATE OR REPLACE VIEW users_view AS SELECT * FROM read_parquet('{PARQUET_URL}')")
        count = con.execute("SELECT COUNT(*) FROM users_view").fetchone()[0]
        print(f"✅ Loaded! {count:,} records")
    except Exception as e:
        print(f"❌ Error: {e}")
        con.execute("""
            CREATE OR REPLACE VIEW users_view AS 
            SELECT * FROM (VALUES 
                ('9999999891', 'Krishan Bidhuri', 'Dharamveer', 'Delhi', '9999599891', 'AIRTEL DELHI', '417479131232', 'krishan@email.com'),
                ('9999999751', 'Manoj Sharawat', 'Rajender', 'Delhi', NULL, 'AIRTEL DELHI', '423900570509', NULL),
            ) AS t(mobile, name, fname, address, alt, circle, id, email)
        """)
    
    yield
    con.close()

app.router.lifespan_context = lifespan

# Helper function to format result
def format_result(result):
    columns = ['mobile', 'name', 'fname', 'address', 'alt', 'circle', 'id', 'email']
    return {
        "status": "success",
        "data": dict(zip(columns, result))
    }

# 1️⃣ API: Mobile Number se data fetch karein
@app.get("/search/mobile")
async def search_by_mobile(mobile: str = Query(..., description="10-digit mobile number")):
    mobile_clean = str(mobile).strip()
    if not re.match(r'^\d{10}$', mobile_clean):
        return {"status": "error", "message": "Mobile number must be 10 digits"}
    
    try:
        result = con.execute("SELECT * FROM users_view WHERE mobile = ? LIMIT 1", [mobile_clean]).fetchone()
        if not result:
            return {"status": "not_found", "message": f"Mobile {mobile_clean} not found"}
        return format_result(result)
    except Exception as e:
        return {"status": "error", "message": str(e)}

# 2️⃣ API: Gmail / Email se data fetch karein
@app.get("/search/email")
async def search_by_email(email: str = Query(..., description="Email address")):
    email_clean = str(email).strip().lower()
    
    try:
        # Email validation regex
        if not re.match(r"^[\w\.-]+@[\w\.-]+\.\w+$", email_clean):
            return {"status": "error", "message": "Invalid email format"}
            
        result = con.execute("SELECT * FROM users_view WHERE LOWER(email) = ? LIMIT 1", [email_clean]).fetchone()
        if not result:
            return {"status": "not_found", "message": f"Email {email_clean} not found"}
        return format_result(result)
    except Exception as e:
        return {"status": "error", "message": str(e)}

# 3️⃣ API: 12-digit ID number se data fetch karein
@app.get("/search/id")
async def search_by_id(id_num: str = Query(..., description="12-digit ID number")):
    id_clean = str(id_num).strip()
    # 12 digits validation
    if not re.match(r'^\d{12}$', id_clean):
        return {"status": "error", "message": "ID must be exactly 12 digits"}
        
    try:
        result = con.execute("SELECT * FROM users_view WHERE id = ? LIMIT 1", [id_clean]).fetchone()
        if not result:
            return {"status": "not_found", "message": f"ID {id_clean} not found"}
        return format_result(result)
    except Exception as e:
        return {"status": "error", "message": str(e)}

# Health / Stats Endpoints
@app.get("/stats")
async def stats():
    try:
        count = con.execute("SELECT COUNT(*) FROM users_view").fetchone()[0]
        return {"status": "success", "total_records": count}
    except:
        return {"status": "error"}

@app.get("/health")
async def health():
    try:
        con.execute("SELECT 1").fetchone()
        return {"status": "healthy", "database": "connected"}
    except:
        return {"status": "unhealthy"}