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;")

    # Email Database
    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}")

        # Sample data
        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','manoj@email.com')
            ) AS t(mobile,name,fname,address,alt,circle,id,email)
        """)

        print("✅ Sample data loaded")

    yield
    con.close()

app.router.lifespan_context = lifespan


@app.get("/")
def home():
    return {
        "status": "online",
        "message": "Email Search API",
        "endpoint": "/search?email=test@gmail.com"
    }


@app.get("/search")
async def search(email: str = Query(..., description="Email Address")):

    email = email.strip().lower()

    if not re.match(r"^[^@]+@[^@]+\.[^@]+$", email):
        return {
            "status": "error",
            "message": "Invalid email address"
        }

    try:
        query = """
        SELECT *
        FROM users_view
        WHERE lower(email)=?
        LIMIT 1
        """

        result = con.execute(query, [email]).fetchone()

        if not result:
            return {
                "status": "not_found",
                "message": f"{email} not found"
            }

        columns = [
            "mobile",
            "name",
            "fname",
            "address",
            "alt",
            "circle",
            "id",
            "email"
        ]

        return {
            "status": "success",
            "data": dict(zip(columns, result))
        }

    except Exception as e:
        return {
            "status": "error",
            "message": str(e)
        }


@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"
        }