from fastapi import FastAPI, HTTPException, Response, Request, BackgroundTasks
from fastapi.responses import JSONResponse, RedirectResponse
from nepse import AsyncNepse
import logging
import time
from datetime import date

# Import database utilities
from app.db.connector import get_historical_data, get_live_ticks, get_db, upsert_historical_candles, get_historical_data_async, get_live_ticks_async

# Import validation utilities
from app.utils.validator import validate_stock_symbol, validate_index_name, validator

# Import rate limiting
from app.core.rate_limiter import check_rate_limit, get_rate_limit_headers, rate_limiter

# Import auth
from app.db.auth_db import init_db
from app.api.routes.auth import router as auth_router, decode_jwt
from app.api.routes.admin import router as admin_router
from app.api.routes.ws import router as ws_router

app = FastAPI(title="Nepse Unofficial API Server")

# Include Routers
app.include_router(auth_router)
app.include_router(admin_router)
app.include_router(ws_router)

# Configure Nepse API
nepseAsync = AsyncNepse()
nepseAsync.setTLSVerification(False)

# Add custom exception handler for all exceptions
@app.exception_handler(Exception)
async def global_exception_handler(request: Request, exc: Exception):
    import traceback
    error_details = traceback.format_exc()
    logger.error(f"Global exception: {error_details}")
    return JSONResponse(
        status_code=200,
        content={"error": "Internal Server Error", "details": str(exc)}
    )

def _serve_html(path: str):
    """Sync file read — avoids anyio thread creation which fails on cPanel shared hosting."""
    try:
        with open(path, "rb") as f:
            content = f.read()
        return Response(content=content, media_type="text/html; charset=utf-8")
    except FileNotFoundError:
        return Response(b"Page not found", status_code=404, media_type="text/plain")

# Clean URL Page Routes
@app.get("/")
async def get_index():
    return _serve_html("frontend/pages/index.html")

@app.get("/login")
async def get_login():
    return _serve_html("frontend/pages/login.html")

@app.get("/register")
async def get_register():
    return _serve_html("frontend/pages/register.html")

@app.get("/nepse")
async def get_nepse():
    return _serve_html("frontend/pages/nepse.html")

@app.get("/advanced_charts")
@app.get("/advanced-charts")
async def get_charts():
    return _serve_html("frontend/pages/advanced_charts.html")

@app.get("/profile")
async def get_profile():
    return _serve_html("frontend/pages/profile.html")

@app.get("/scanner")
async def get_scanner():
    return _serve_html("frontend/pages/scanner.html")

@app.get("/nepse-scan")
async def get_nepse_scan():
    return _serve_html("frontend/pages/nepse-scan.html")

@app.get("/health")
async def get_health():
    return _serve_html("frontend/pages/health.html")

@app.get("/admin-dashboard")
async def get_admin_dashboard():
    return _serve_html("frontend/pages/admin-dashboard.html")

# Redirects for legacy links with /public/ or .html
@app.get("/frontend/pages/{page_name}.html")
async def redirect_clean_urls(page_name: str):
    # e.g., /public/login.html -> /login
    if page_name == "index":
        return RedirectResponse(url="/", status_code=301)
    if page_name == "advanced_charts":
        return RedirectResponse(url="/advanced-charts", status_code=301)
    return RedirectResponse(url=f"/{page_name}", status_code=301)

# Global handler: catch unconfigured Supabase / any RuntimeError and return JSON (not HTML 500)
@app.exception_handler(RuntimeError)
async def runtime_error_handler(_request: Request, exc: RuntimeError):
    return JSONResponse(status_code=503, content={"detail": str(exc)})

async def generic_error_handler(_request: Request, exc: Exception):
    logging.getLogger(__name__).error(f"Unhandled error: {exc}")
    return JSONResponse(status_code=200, content={"detail": "Internal server error"})

# Initialize auth database on startup
init_db()

# Protected HTML pages — require valid hq_token cookie
_PROTECTED_PAGES = {
    "/frontend/pages/nepse.html",
    "/frontend/pages/scanner.html",
    "/frontend/pages/health.html",
    "/frontend/pages/advanced_charts.html",
    "/frontend/pages/nepse-scan.html",
    "/frontend/pages/profile.html",
    "/frontend/pages/admin-dashboard.html",
}

@app.get("/assets/{path:path}")
async def serve_static(path: str):
    """Sync static file serve — avoids anyio thread creation on cPanel shared hosting."""
    import mimetypes
    file_path = f"frontend/assets/{path}"
    try:
        with open(file_path, "rb") as f:
            content = f.read()
        mime_type, _ = mimetypes.guess_type(file_path)
        return Response(content=content, media_type=mime_type or "application/octet-stream",
                        headers={"Cache-Control": "public, max-age=3600"})
    except FileNotFoundError:
        return Response(b"Not Found", status_code=404)

# Auth middleware — protect HTML pages server-side (belt + suspenders with JS guard)
@app.middleware("http")
async def auth_page_middleware(request: Request, call_next):
    if request.url.path in _PROTECTED_PAGES:
        token = request.cookies.get("hq_token")
        if token:
            try:
                decode_jwt(token)
                return await call_next(request)
            except Exception:
                pass
        return RedirectResponse(f"/frontend/pages/login.html?next={request.url.path}", status_code=302)
    return await call_next(request)

# Anti-Scraping middleware — validate User-Agent
@app.middleware("http")
async def anti_scraping_middleware(request: Request, call_next):
    user_agent = request.headers.get("user-agent", "").lower()
    
    # Block empty or common bot signatures unless they have a valid token
    suspicious_agents = ["python-requests", "curl", "wget", "urllib", "bot", "spider", "scraper"]
    
    is_suspicious = not user_agent or any(bot in user_agent for bot in suspicious_agents)
    
    if is_suspicious:
        # Check if they have an authorization token
        token = request.cookies.get("hq_token") or request.headers.get("Authorization")
        if not token:
            logger.warning(f"Blocked scraping attempt from {request.client.host} with User-Agent: {user_agent}")
            return JSONResponse(status_code=403, content={"error": "Access forbidden. Invalid User-Agent."})
            
    return await call_next(request)

# Rate limiting middleware
@app.middleware("http")
async def rate_limit_middleware(request: Request, call_next):
    # Get client IP
    client_ip = request.client.host
    if hasattr(request, 'headers'):
        # Check for forwarded IP (useful when behind proxy)
        forwarded_for = request.headers.get('X-Forwarded-For')
        if forwarded_for:
            client_ip = forwarded_for.split(',')[0].strip()

    # Skip rate limiting for local development (localhost / 127.0.0.1)
    if client_ip in ("127.0.0.1", "localhost", "::1"):
        return await call_next(request)

    # Check rate limit
    endpoint = request.url.path
    allowed, info = check_rate_limit(client_ip, endpoint)

    if not allowed:
        headers = get_rate_limit_headers(info)
        retry_after = info["reset_time"] - int(time.time())
        headers["Retry-After"] = str(retry_after)

        return JSONResponse(
            status_code=429,
            content={
                "error": "Rate limit exceeded",
                "message": f"Too many requests. Try again in {retry_after} seconds.",
                "limit": info["limit"],
                "reset_time": info["reset_time"]
            },
            headers=headers
        )

    # Process request
    response = await call_next(request)

    # Add rate limit headers to response
    headers = get_rate_limit_headers(info)
    for key, value in headers.items():
        response.headers[key] = value

    return response

import re

def is_valid_scrip(item: dict) -> bool:
    """Check if a stock should be included in the API response."""
    symbol = item.get("symbol", "")
    name = item.get("securityName", item.get("name", item.get("companyName", "")))
    
    text_to_check = f"{symbol} {name}".lower()
    
    # 1) Contains digits
    if re.search(r'\d', text_to_check):
        return False
        
    forbidden_words = ["yojana", "debenture", "mutual fund", "bluechip fund", "balanced", "equity fund"]
    for word in forbidden_words:
        if word in text_to_check:
            return False
            
    return True

# Common validation helper functions
def validate_stock_or_raise(symbol: str) -> str:
    """Validate stock symbol and raise HTTPException if invalid"""
    validation_result = validate_stock_symbol(symbol)
    if not validation_result["valid"]:
        error_msg = validation_result["error"]
        if validation_result.get("suggestions"):
            error_msg += f" Suggestions: {', '.join(validation_result['suggestions'])}"
        raise HTTPException(status_code=400, detail=error_msg)
    return validation_result["symbol"]

def validate_index_or_raise(index_name: str) -> str:
    """Validate index name and raise HTTPException if invalid"""
    validation_result = validate_index_name(index_name)
    if not validation_result["valid"]:
        error_msg = validation_result["error"]
        if validation_result.get("available_indices"):
            error_msg += f" Available indices: {', '.join(validation_result['available_indices'])}"
        raise HTTPException(status_code=400, detail=error_msg)
    return validation_result["index_name"]

import json
import os
import sys

class JSONFormatter(logging.Formatter):
    def format(self, record):
        log_record = {
            "timestamp": self.formatTime(record, self.datefmt),
            "level": record.levelname,
            "name": record.name,
            "message": record.getMessage(),
        }
        if record.exc_info:
            log_record["exc_info"] = self.formatException(record.exc_info)
        return json.dumps(log_record)

log_handler = logging.FileHandler(os.path.join("logs", "app.log"))
log_handler.setFormatter(JSONFormatter())
console_handler = logging.StreamHandler(sys.stdout)
console_handler.setFormatter(JSONFormatter())

logging.basicConfig(level=logging.INFO, handlers=[log_handler, console_handler])
logger = logging.getLogger(__name__)

routes = {
    "Health": "/health",
    "Docs": "/docs",
    "PriceVolume": "/PriceVolume",
    "Summary": "/Summary",
    "SupplyDemand": "/SupplyDemand",
    "TopGainers": "/TopGainers",
    "TopLosers": "/TopLosers",
    "TopTenTradeScrips": "/TopTenTradeScrips",
    "TopTenTurnoverScrips": "/TopTenTurnoverScrips",
    "TopTenTransactionScrips": "/TopTenTransactionScrips",
    "IsNepseOpen": "/IsNepseOpen",
    "NepseIndex": "/NepseIndex",
    "NepseSubIndices": "/NepseSubIndices",
    "DailyScripPriceGraph": "/DailyScripPriceGraph",
    "CompanyList": "/CompanyList",
    "SectorScrips": "/SectorScrips",
    "MarketDepth": "/MarketDepth",
    "CompanyDetails": "/CompanyDetails",
    "Floorsheet": "/Floorsheet",
    "FloorsheetOf": "/FloorsheetOf",
    "PriceVolumeHistory": "/PriceVolumeHistory",
    "SecurityList": "/SecurityList",
    "TradeTurnoverTransactionSubindices": "/TradeTurnoverTransactionSubindices",
    "LiveMarket": "/LiveMarket",
    "DailyNepseIndexGraph": "/DailyNepseIndexGraph",
    "DailySensitiveIndexGraph": "/DailySensitiveIndexGraph",
    "DailyFloatIndexGraph": "/DailyFloatIndexGraph",
    "DailySensitiveFloatIndexGraph": "/DailySensitiveFloatIndexGraph",
    "DailyBankSubindexGraph": "/DailyBankSubindexGraph",
    "DailyDevelopmentBankSubindexGraph": "/DailyDevelopmentBankSubindexGraph",
    "DailyFinanceSubindexGraph": "/DailyFinanceSubindexGraph",
    "DailyHotelTourismSubindexGraph": "/DailyHotelTourismSubindexGraph",
    "DailyHydroPowerSubindexGraph": "/DailyHydroPowerSubindexGraph",
    "DailyInvestmentSubindexGraph": "/DailyInvestmentSubindexGraph",
    "DailyLifeInsuranceSubindexGraph": "/DailyLifeInsuranceSubindexGraph",
    "DailyManufacturingProcessingSubindexGraph": "/DailyManufacturingProcessingSubindexGraph",
    "DailyMicrofinanceSubindexGraph": "/DailyMicrofinanceSubindexGraph",
    "DailyMutualFundSubindexGraph": "/DailyMutualFundSubindexGraph",
    "DailyNonLifeInsuranceSubindexGraph": "/DailyNonLifeInsuranceSubindexGraph",
    "DailyOthersSubindexGraph": "/DailyOthersSubindexGraph",
    "DailyTradingSubindexGraph": "/DailyTradingSubindexGraph",
}

HEADERS = {
    "Access-Control-Allow-Origin": "*",
    "Cache-Control": "public, max-age=30"
}

@app.get("/health")
async def health_check():
    return {"status": "healthy"}

@app.get("/rate-limit/stats")
async def get_rate_limit_stats():
    """Get rate limiting statistics"""
    stats = rate_limiter.get_stats()
    return JSONResponse(content=stats, headers=HEADERS)

@app.get("/validate/stock/{symbol}")
async def validate_stock(symbol: str):
    """Validate a stock symbol and return validation result"""
    validation_result = validate_stock_symbol(symbol)
    if validation_result["valid"]:
        return JSONResponse(content=validation_result, headers=HEADERS)
    else:
        return JSONResponse(
            content=validation_result,
            status_code=404,
            headers={"Access-Control-Allow-Origin": "*"}
        )

@app.get("/validate/index/{index_name}")
async def validate_index(index_name: str):
    """Validate an index name and return validation result"""
    validation_result = validate_index_name(index_name)
    if validation_result["valid"]:
        return JSONResponse(content=validation_result, headers=HEADERS)
    else:
        return JSONResponse(
            content=validation_result,
            status_code=404,
            headers={"Access-Control-Allow-Origin": "*"}
        )

@app.get("/validation/stats")
async def get_validation_stats():
    """Get validation statistics"""
    stats = validator.get_stats()
    return JSONResponse(content=stats, headers=HEADERS)

@app.get(routes["Summary"])
async def get_summary():
    data = await _get_summary()
    return JSONResponse(content= data, headers=HEADERS)


async def _get_summary():
    response = dict()
    for obj in await nepseAsync.getSummary():
        response[obj["detail"]] = obj["value"]
    return response

@app.get(routes["NepseIndex"])
async def get_nepse_index():
    return JSONResponse(content= await _get_nepse_index(), headers=HEADERS)


async def _get_nepse_index():
    response = dict()
    for obj in await nepseAsync.getNepseIndex():
        response[obj["index"]] = obj
    return response

@app.get(routes["LiveMarket"])
async def get_live_market():
    data = await nepseAsync.getLiveMarket()
    return JSONResponse(content=data, headers=HEADERS)


#Bugged, hoping for fix from the library
@app.get(routes["MarketDepth"])
async def get_market_depth(symbol: str):
    validated_symbol = validate_stock_or_raise(symbol)
    data = await nepseAsync.getSymbolMarketDepth(validated_symbol)
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["NepseSubIndices"])
async def get_nepse_subindices():
    data = await _get_nepse_subindices()
    return JSONResponse(content=data, headers=HEADERS)

async def _get_nepse_subindices():
    response = dict()
    for obj in await nepseAsync.getNepseSubIndices():
        response[obj["index"]] = obj
    return response

@app.get(routes["TopTenTradeScrips"])
async def get_top_ten_trade_scrips():
    data = await nepseAsync.getTopTenTradeScrips()
    filtered = [x for x in data if is_valid_scrip(x)]
    return JSONResponse(content=filtered, headers=HEADERS)


@app.get(routes["TopTenTransactionScrips"])
async def get_top_ten_transaction_scrips():
    data = await nepseAsync.getTopTenTransactionScrips()
    filtered = [x for x in data if is_valid_scrip(x)]
    return JSONResponse(content=filtered, headers=HEADERS)


@app.get(routes["TopTenTurnoverScrips"])
async def get_top_ten_turnover_scrips():
    data = await nepseAsync.getTopTenTurnoverScrips()
    filtered = [x for x in data if is_valid_scrip(x)]
    return JSONResponse(content=filtered, headers=HEADERS)


@app.get(routes["SupplyDemand"])
async def get_supply_demand():
    data = await nepseAsync.getSupplyDemand()
    return JSONResponse(content=data, headers=HEADERS)


@app.get(routes["TopGainers"])
async def get_top_gainers():
    try:
        data = await nepseAsync.getTopGainers()
        if data and len(data) > 0:
            filtered = [x for x in data if is_valid_scrip(x)]
            return JSONResponse(content=filtered, headers=HEADERS)
    except Exception as e:
        logger.error(f"Error fetching top gainers from library: {e}")
    
    # Compute from PriceVolume (highly accurate fallback)
    pv_data = await _get_all_price_volume()
    valid_data = [x for x in pv_data if x.get("symbol") and x.get("percentageChange", 0) > 0]
    valid_data.sort(key=lambda x: x.get("percentageChange", 0), reverse=True)
    formatted = []
    for x in valid_data[:30]:
        formatted.append({
            "symbol": x["symbol"],
            "ltp": x["ltp"],
            "pointChange": x["pointChange"],
            "percentageChange": x["percentageChange"]
        })
    return JSONResponse(content=formatted, headers=HEADERS)


@app.get(routes["TopLosers"])
async def get_top_losers():
    try:
        data = await nepseAsync.getTopLosers()
        if data and len(data) > 0:
            filtered = [x for x in data if is_valid_scrip(x)]
            return JSONResponse(content=filtered, headers=HEADERS)
    except Exception as e:
        logger.error(f"Error fetching top losers from library: {e}")
        
    # Compute from PriceVolume (highly accurate fallback)
    pv_data = await _get_all_price_volume()
    valid_data = [x for x in pv_data if x.get("symbol") and x.get("percentageChange", 0) < 0]
    valid_data.sort(key=lambda x: x.get("percentageChange", 0))
    formatted = []
    for x in valid_data[:30]:
        formatted.append({
            "symbol": x["symbol"],
            "ltp": x["ltp"],
            "pointChange": x["pointChange"],
            "percentageChange": x["percentageChange"]
        })
    return JSONResponse(content=formatted, headers=HEADERS)


@app.get(routes["IsNepseOpen"])
async def is_nepse_open():
    logger.info("IsNepseOpen endpoint called")
    data = await nepseAsync.isNepseOpen()
    return JSONResponse(content=data, headers=HEADERS)


@app.get(routes["DailyNepseIndexGraph"])
async def get_daily_nepse_index_graph():
    data = await nepseAsync.getDailyNepseIndexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailySensitiveIndexGraph"])
async def get_daily_sensitive_index_graph():
    data = await nepseAsync.getDailySensitiveIndexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyFloatIndexGraph"])
async def get_daily_float_index_graph():
    data = await nepseAsync.getDailyFloatIndexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailySensitiveFloatIndexGraph"])
async def get_daily_sensitive_float_index_graph():
    data = await nepseAsync.getDailySensitiveFloatIndexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyBankSubindexGraph"])
async def get_daily_bank_subindex_graph():
    data = await nepseAsync.getDailyBankSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyDevelopmentBankSubindexGraph"])
async def get_daily_development_bank_subindex_graph():
    data = await nepseAsync.getDailyDevelopmentBankSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyFinanceSubindexGraph"])
async def get_daily_finance_subindex_graph():
    data = await nepseAsync.getDailyFinanceSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyHotelTourismSubindexGraph"])
async def get_daily_hotel_tourism_subindex_graph():
    data = await nepseAsync.getDailyHotelTourismSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyHydroPowerSubindexGraph"])
async def get_daily_hydro_power_subindex_graph():
    data = await nepseAsync.getDailyHydroSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)


@app.get(routes["DailyInvestmentSubindexGraph"])
async def get_daily_investment_subindex_graph():
    data = await nepseAsync.getDailyInvestmentSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyLifeInsuranceSubindexGraph"])
async def get_daily_life_insurance_subindex_graph():
    data = await nepseAsync.getDailyLifeInsuranceSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyManufacturingProcessingSubindexGraph"])
async def get_daily_manufacturing_processing_subindex_graph():
    data = await nepseAsync.getDailyManufacturingSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyMicrofinanceSubindexGraph"])
async def get_daily_microfinance_subindex_graph():
    data = await nepseAsync.getDailyMicrofinanceSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyMutualFundSubindexGraph"])
async def get_daily_mutual_fund_subindex_graph():
    data = await nepseAsync.getDailyMutualfundSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyNonLifeInsuranceSubindexGraph"])
async def get_daily_non_life_insurance_subindex_graph():
    data = await nepseAsync.getDailyNonLifeInsuranceSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyOthersSubindexGraph"])
async def get_daily_others_subindex_graph():
    data = await nepseAsync.getDailyOthersSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyTradingSubindexGraph"])
async def get_daily_trading_subindex_graph():
    data = await nepseAsync.getDailyTradingSubindexGraph()
    return JSONResponse(content=data, headers=HEADERS)

@app.get(routes["DailyScripPriceGraph"])
async def get_daily_scrip_price_graph(symbol: str):
    validated_symbol = validate_stock_or_raise(symbol)
    data = await nepseAsync.getDailyScripPriceGraph(validated_symbol)
    return JSONResponse(content=data, headers=HEADERS)


@app.get(routes["CompanyList"])
async def get_company_list():
    data = await nepseAsync.getCompanyList()
    filtered = [x for x in data if is_valid_scrip(x)]
    return JSONResponse(content=filtered, headers=HEADERS)


@app.get(routes["SectorScrips"])
async def get_sector_scrips():
    data = await nepseAsync.getSectorScrips()
    filtered = [x for x in data if is_valid_scrip(x)]
    return JSONResponse(content=filtered, headers=HEADERS)


@app.get(routes["CompanyDetails"])
async def get_company_details(symbol: str):
    validated_symbol = validate_stock_or_raise(symbol)
    data = await nepseAsync.getCompanyDetails(validated_symbol)
    return JSONResponse(content=data, headers=HEADERS)


async def _get_all_price_volume():
    # Try fetching live ticks from Supabase
    db_data = await get_live_ticks_async()
    if db_data:
        # Valid if any row has a real LTP — columns always exist now after migration
        has_valid_ohlc = any(row.get("ltp") and float(row["ltp"]) > 0 for row in db_data)
        if has_valid_ohlc:
            formatted_data = []
            for row in db_data:
                formatted_data.append({
                    "symbol": row["symbol"],
                    "ltp": float(row["ltp"]) if row.get("ltp") else 0.0,
                    "pointChange": float(row["point_change"]) if row.get("point_change") else 0.0,
                    "percentageChange": float(row["percentage_change"]) if row.get("percentage_change") else 0.0,
                    "shareTraded": int(row["volume"]) if row.get("volume") else 0,
                    "openPrice": float(row["open_price"]) if row.get("open_price") else 0.0,
                    "highPrice": float(row["high_price"]) if row.get("high_price") else 0.0,
                    "lowPrice": float(row["low_price"]) if row.get("low_price") else 0.0,
                    "previousClose": float(row["previous_close"]) if row.get("previous_close") else 0.0
                })
            return [x for x in formatted_data if is_valid_scrip(x)]
            
    try:
        data = await nepseAsync.getPriceVolume()
        return [x for x in data if is_valid_scrip(x)]
    except Exception as e:
        logger.error(f"Error fetching from live scraper: {e}")
        return []


@app.get(routes["PriceVolume"])
async def get_price_volume():
    data = await _get_all_price_volume()
    db_data = await get_live_ticks_async()
    source = "Supabase" if (db_data and any(row.get("open_price") is not None for row in db_data)) else "Live Scraper"
    return JSONResponse(content=data, headers={**HEADERS, "X-Source": source})


async def _fill_gaps_background(symbol: str):
    """Scrape the last 7 candles and upsert into Supabase.
    Async so FastAPI awaits it directly instead of running in a thread (cPanel thread limit)."""
    try:
        from app.services.scraper import scrape_sharesansar_history
        candles = scrape_sharesansar_history(symbol, length=7)
        if candles:
            upsert_historical_candles(candles)
            logger.info(f"Gap-fill complete for {symbol}: {len(candles)} bars upserted")
        else:
            logger.warning(f"Gap-fill for {symbol}: scraper returned no candles")
    except Exception as exc:
        logger.warning(f"Gap-fill failed for {symbol}: {exc}")


# Simple in-memory cache for historical data to fix slow chart load times
_history_cache = {}
CACHE_TTL_SECONDS = 300 # 5 minutes

@app.get(routes["PriceVolumeHistory"])
async def get_price_volume_history(symbol: str, background_tasks: BackgroundTasks):
    validated_symbol = validate_stock_or_raise(symbol)
    
    current_time = time.time()
    if validated_symbol in _history_cache:
        cached_entry = _history_cache[validated_symbol]
        if current_time - cached_entry["timestamp"] < CACHE_TTL_SECONDS:
            return JSONResponse(content=cached_entry["data"], headers={**HEADERS, "X-Source": "Supabase (Cached)"})

    # Try fetching from Supabase Database first
    db_data = await get_historical_data_async(validated_symbol)
    if db_data and len(db_data) >= 5:
        formatted_data = []
        for row in db_data:
            entry = {
                "businessDate": str(row["business_date"]),
                "openPrice": float(row["open_price"]) if row.get("open_price") else 0.0,
                "highPrice": float(row["high_price"]) if row.get("high_price") else 0.0,
                "lowPrice": float(row["low_price"]) if row.get("low_price") else 0.0,
                "closePrice": float(row["close_price"]) if row.get("close_price") else 0.0,
                "totalTradedQuantity": int(row["volume"]) if row.get("volume") else 0,
            }
            # previousClose is the NEPSE-set reference price; on bonus/rights ex-dates
            # it equals (prior close / adjustment factor), making it the authoritative
            # source for detecting and applying corporate action adjustments.
            if row.get("previous_close"):
                entry["previousClose"] = float(row["previous_close"])
            formatted_data.append(entry)
        # Sort ascending for Lightweight Charts compatibility
        formatted_data.sort(key=lambda x: x["businessDate"])

        # Auto gap-fill: if the most recent stored bar is stale (≥1 calendar day old),
        # schedule a background scrape so the next reload has current data.
        # This covers the case where the daily scraper missed a trading session
        # (e.g. Sunday in Nepal when the cron only runs Mon–Fri).
        most_recent_date = date.fromisoformat(formatted_data[-1]["businessDate"])
        days_stale = (date.today() - most_recent_date).days
        if days_stale >= 1:
            logger.info(f"Stale data for {validated_symbol}: last bar {most_recent_date}, scheduling gap-fill")
            background_tasks.add_task(_fill_gaps_background, validated_symbol)

        # Update cache
        _history_cache[validated_symbol] = {
            "timestamp": time.time(),
            "data": formatted_data
        }

        return JSONResponse(content=formatted_data, headers={**HEADERS, "X-Source": "Supabase"})

    # Fallback to live scraper if DB has no data
    logger.info(f"Supabase cache miss for {validated_symbol}. Falling back to live fetch.")
    data = await nepseAsync.getCompanyPriceVolumeHistory(validated_symbol)
    
    if data and len(data) > 0:
        _history_cache[validated_symbol] = {
            "timestamp": time.time(),
            "data": data
        }
        
    return JSONResponse(content=data, headers={**HEADERS, "X-Source": "Live Scraper"})


@app.get(routes["Floorsheet"])
async def get_floorsheet():
    floorsheet_data = await nepseAsync.getFloorSheet()
    return JSONResponse(content=floorsheet_data, headers=HEADERS)


@app.get(routes["FloorsheetOf"])
async def get_floorsheet_of(symbol: str):
    validated_symbol = validate_stock_or_raise(symbol)
    data = await nepseAsync.getFloorSheetOf(validated_symbol)
    return JSONResponse(content=data, headers=HEADERS)


@app.get(routes["SecurityList"])
async def getSecurityList():
    data = await nepseAsync.getSecurityList()
    filtered = [x for x in data if is_valid_scrip(x)]
    return JSONResponse (content=filtered, headers=HEADERS)

@app.get(routes["TradeTurnoverTransactionSubindices"])
async def getTradeTurnoverTransactionSubindices():
    companies = {company["symbol"]: company for company in await nepseAsync.getCompanyList() if is_valid_scrip(company)}

    turnover = {obj["symbol"]: obj for obj in await nepseAsync.getTopTenTurnoverScrips()}
    transaction = {obj["symbol"]: obj for obj in await nepseAsync.getTopTenTransactionScrips()}
    trade = {obj["symbol"]: obj for obj in await nepseAsync.getTopTenTradeScrips()}

    price_vol_list = await _get_all_price_volume()
    price_vol_info = {obj["symbol"]: obj for obj in price_vol_list}

    sector_sub_indices = await _getNepseSubIndices()
    # this is done since nepse sub indices and sector name are different
    sector_mapper = {
        "Commercial Banks": "Banking SubIndex",
        "Development Banks": "Development Bank Index",
        "Finance": "Finance Index",
        "Hotels And Tourism": "Hotels And Tourism Index",
        "Hydro Power": "HydroPower Index",
        "Investment": "Investment Index",
        "Life Insurance": "Life Insurance",
        "Manufacturing And Processing": "Manufacturing And Processing",
        "Microfinance": "Microfinance Index",
        "Mutual Fund": "Mutual Fund",
        "Non Life Insurance": "Non Life Insurance",
        "Others": "Others Index",
        "Tradings": "Trading Index",
    }

    scrips_details = {}
    for symbol, company in companies.items():
        p_info = price_vol_info.get(symbol, {})
        company_details = {
            "symbol": symbol,
            "sector": company["sectorName"],
            "Turnover": turnover.get(symbol, {}).get("turnover", 0),
            "transaction": transaction.get(symbol, {}).get("totalTrades", 0),
            "volume": trade.get(symbol, {}).get("shareTraded", 0),
            "previousClose": p_info.get("previousClose", 0),
            "name": company.get("securityName", ""),
            "lastUpdatedDateTime": p_info.get("lastUpdatedDateTime", 0),
            "category": company.get("instrumentType"),
            "pointChange": p_info.get("pointChange", 0),
            "percentageChange": p_info.get("percentageChange", 0),
            "ltp": p_info.get("ltp", 0),
        }

        #let's filter here based on ltp and previos close, if ltp = 0 or previous close = 0, then the company is not trading
        if company_details["ltp"] == 0 or company_details["previousClose"] == 0:
            continue
        scrips_details[symbol] = company_details

    sector_details = dict()
    sectors = {company["sectorName"] for company in companies.values()}
    for sector in sectors:
        total_trades, total_trade_quantity, total_turnover = 0, 0, 0
        for scrip_details in scrips_details.values():
            if scrip_details["sector"] == sector:
                total_trades += scrip_details["transaction"]
                total_trade_quantity += scrip_details["volume"]
                total_turnover += scrip_details["Turnover"]

        sector_details[sector] = {
            "transaction": total_trades,
            "volume": total_trade_quantity,
            "totalTurnover": total_turnover,
            "turnover": sector_sub_indices[sector_mapper[sector]],
            "sectorName": sector,
        }

    return JSONResponse({"scripsDetails": scrips_details, "sectorsDetails": sector_details}, headers=HEADERS)

async def _getNepseSubIndices():
    return {obj["index"]: obj for obj in await nepseAsync.getNepseSubIndices()}

from app.services.trendline_engine.engine import TrendlineSignalEngine
import pandas as pd
import math
import dataclasses

@app.get("/api/trendlines")
async def get_trendlines(
    symbol: str,
    pivot_length: int = 3,
    min_bar_separation: int = 3,
    max_lines: int = 3,
    background_tasks: BackgroundTasks = None
):
    validated_symbol = validate_stock_or_raise(symbol)
    
    db_data = await get_historical_data_async(validated_symbol)
    data = []
    if db_data and len(db_data) >= 5:
        for row in db_data:
            entry = {
                "time": str(row["business_date"]),
                "open": float(row["open_price"]) if row.get("open_price") else 0.0,
                "high": float(row["high_price"]) if row.get("high_price") else 0.0,
                "low": float(row["low_price"]) if row.get("low_price") else 0.0,
                "close": float(row["close_price"]) if row.get("close_price") else 0.0,
                "volume": int(row["volume"]) if row.get("volume") else 0,
            }
            data.append(entry)
        data.sort(key=lambda x: x["time"])
    else:
        raw_data = await nepseAsync.getCompanyPriceVolumeHistory(validated_symbol)
        if raw_data:
            for row in raw_data:
                 data.append({
                     "time": row["businessDate"],
                     "open": float(row["openPrice"]),
                     "high": float(row["highPrice"]),
                     "low": float(row["lowPrice"]),
                     "close": float(row["closePrice"]),
                     "volume": int(row["totalTradedQuantity"])
                 })
            data.sort(key=lambda x: x["time"])

    if not data:
        raise HTTPException(status_code=404, detail="No historical data found")

    df = pd.DataFrame(data)
    engine = TrendlineSignalEngine(
        pivot_length=pivot_length,
        min_bar_separation=min_bar_separation,
        max_lines=max_lines
    )
    result = engine.run(df)
    
    def clean_for_json(obj):
        import datetime
        import numpy as np
        if hasattr(obj, '__dataclass_fields__'):
            return clean_for_json(dataclasses.asdict(obj))
        if isinstance(obj, float) and math.isnan(obj):
            return None
        if isinstance(obj, dict):
            return {k: clean_for_json(v) for k, v in obj.items()}
        if isinstance(obj, list):
            return [clean_for_json(v) for v in obj]
        if isinstance(obj, (datetime.date, datetime.datetime)):
            return obj.isoformat()
        if isinstance(obj, np.integer):
            return int(obj)
        if isinstance(obj, np.floating):
            if np.isnan(obj): return None
            return float(obj)
        if isinstance(obj, np.ndarray):
            return clean_for_json(obj.tolist())
        return obj

    cleaned_result = clean_for_json(result)
    return JSONResponse(content=cleaned_result, headers=HEADERS)

if __name__ == "__main__":
    import uvicorn

    uvicorn.run("server:app", host="0.0.0.0", port=8088, reload=True)