from fastapi import FastAPI, HTTPException, Depends
from fastapi.middleware.cors import CORSMiddleware
from fastapi.staticfiles import StaticFiles
from fastapi.responses import FileResponse
import requests
import os
import pandas as pd

app = FastAPI(title="Trading Dashboard API")

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_methods=["*"],
    allow_headers=["*"],
)

# Serve static files
app.mount("/static", StaticFiles(directory="dashboard/public"), name="static")

@app.get("/")
async def read_index():
    return FileResponse('dashboard/public/index.html')

DB_USER = os.getenv("DB_USER", "admin")
DB_PASSWORD = os.getenv("DB_PASSWORD", "quest")
DB_AUTH = (DB_USER, DB_PASSWORD) if DB_USER and DB_PASSWORD else None
DB_HOST = os.getenv("DB_HOST", "questdb")

@app.get("/api/trades")
async def get_trades(isin: str = None, days: int = 7):
    query = f"select * from trades where timestamp > dateadd('d', -{days}, now())"
    if isin:
        query += f" and isin = '{isin}'"
    query += " order by timestamp asc"
    
    try:
        response = requests.get(f"http://{DB_HOST}:9000/exec", params={'query': query}, auth=DB_AUTH)
        if response.status_code == 200:
            return response.json()
        throw_http_error(response)
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/metadata")
async def get_metadata():
    query = "select * from metadata"
    try:
        response = requests.get(f"http://{DB_HOST}:9000/exec", params={'query': query}, auth=DB_AUTH)
        if response.status_code == 200:
            return response.json()
        throw_http_error(response)
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/summary")
async def get_summary():
    # Coalesce null values to 'Unknown' and group properly
    query = """
    select 
        coalesce(m.continent, 'Unknown') as continent, 
        count(*) as trade_count, 
        sum(t.price * t.quantity) as total_volume
    from trades t
    left join metadata m on t.isin = m.isin
    group by continent
    """
    try:
        response = requests.get(f"http://{DB_HOST}:9000/exec", params={'query': query}, auth=DB_AUTH)
        if response.status_code == 200:
            return response.json()
        throw_http_error(response)
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/analytics")
async def get_analytics(
    metric: str = "volume", 
    group_by: str = "day", 
    sub_group_by: str = None,
    date_from: str = None, 
    date_to: str = None,
    isins: str = None,
    continents: str = None
):
    metrics_map = {
        "volume": "sum(t.price * t.quantity)",
        "count": "count(*)",
        "avg_price": "avg(t.price)"
    }
    
    groups_map = {
        "day": "date_trunc('day', t.timestamp)",
        "month": "date_trunc('month', t.timestamp)",
        "exchange": "t.exchange",
        "isin": "t.isin",
        "name": "coalesce(m.name, t.isin)",
        "continent": "coalesce(m.continent, 'Unknown')",
        "sector": "coalesce(m.sector, 'Unknown')"
    }
    
    selected_metric = metrics_map.get(metric, metrics_map["volume"])
    selected_group = groups_map.get(group_by, groups_map["day"])
    
    # We always join metadata to allow filtering by continent/sector even if not grouping by them
    query = f"select {selected_group} as label"
    
    if sub_group_by and sub_group_by in groups_map:
        query += f", {groups_map[sub_group_by]} as sub_label"
        
    query += f", {selected_metric} as value from trades t"
    query += " left join metadata m on t.isin = m.isin where 1=1"
    
    if date_from:
        query += f" and t.timestamp >= '{date_from}'"
    if date_to:
        query += f" and t.timestamp <= '{date_to}'"
        
    if isins:
        isins_list = ",".join([f"'{i.strip()}'" for i in isins.split(",")])
        query += f" and t.isin in ({isins_list})"

    if continents:
        cont_list = ",".join([f"'{c.strip()}'" for c in continents.split(",")])
        query += f" and m.continent in ({cont_list})"
        
    query += " group by label"
    if sub_group_by and sub_group_by in groups_map:
        query += ", sub_label"
    
    query += " order by label asc"
    
    try:
        response = requests.get(f"http://{DB_HOST}:9000/exec", params={'query': query}, auth=DB_AUTH)
        if response.status_code == 200:
            return response.json()
        throw_http_error(response)
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/api/metadata/search")
async def search_metadata(q: str):
    # Case-insensitive search for ISIN or Name
    query = f"select isin, name from metadata where isin ilike '%{q}%' or name ilike '%{q}%' limit 10"
    try:
        response = requests.get(f"http://{DB_HOST}:9000/exec", params={'query': query}, auth=DB_AUTH)
        if response.status_code == 200:
            return response.json()
        throw_http_error(response)
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

def throw_http_error(res):
    raise HTTPException(status_code=res.status_code, detail=f"QuestDB error: {res.text}")

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="0.0.0.0", port=8000)