import os import time import sqlite3 import pandas as pd import uvicorn from starlette.applications import Starlette from starlette.responses import JSONResponse, FileResponse, PlainTextResponse from starlette.routing import Route # Path configuration BASE_DIR = os.path.dirname(os.path.abspath(__file__)) # Initialize Database Connection # check_same_thread=False allows sharing connection between async request threads in Starlette safely conn = sqlite3.connect(':memory:', check_same_thread=False) def seed_database(): print("Seeding in-memory SQLite database from CSV files...") try: # Load CSVs using Pandas books_df = pd.read_csv(os.path.join(BASE_DIR, 'Books.csv')) customers_df = pd.read_csv(os.path.join(BASE_DIR, 'Customers.csv')) orders_df = pd.read_csv(os.path.join(BASE_DIR, 'Orders.csv')) # Write DataFrames to SQLite tables books_df.to_sql('Books', conn, if_exists='replace', index=False) customers_df.to_sql('Customers', conn, if_exists='replace', index=False) orders_df.to_sql('Orders', conn, if_exists='replace', index=False) # Verify row counts book_count = conn.execute("SELECT COUNT(*) FROM Books;").fetchone()[0] cust_count = conn.execute("SELECT COUNT(*) FROM Customers;").fetchone()[0] order_count = conn.execute("SELECT COUNT(*) FROM Orders;").fetchone()[0] print(f"Database seeded successfully!") print(f"Tables: Books ({book_count} rows), Customers ({cust_count} rows), Orders ({order_count} rows)") except Exception as e: print(f"Error seeding database: {e}") raise e # --- Request Handlers --- # Serves index.html at root async def serve_homepage(request): return FileResponse(os.path.join(BASE_DIR, 'index.html')) # Serves style.css async def serve_css(request): return FileResponse(os.path.join(BASE_DIR, 'style.css')) # Serves app.js async def serve_js(request): return FileResponse(os.path.join(BASE_DIR, 'app.js')) # Serves KPI summaries via SQL aggregations async def get_kpis(request): try: revenue = conn.execute("SELECT SUM(Total_Amount) FROM Orders;").fetchone()[0] or 0 books_sold = conn.execute("SELECT SUM(Quantity) FROM Orders;").fetchone()[0] or 0 customers = conn.execute("SELECT COUNT(*) FROM Customers;").fetchone()[0] or 0 genres = conn.execute("SELECT COUNT(DISTINCT Genre) FROM Books;").fetchone()[0] or 0 return JSONResponse({ 'status': 'success', 'revenue': f"${revenue:,.2f}", 'books_sold': f"{books_sold:,}", 'customers': f"{customers:,}", 'genres': f"{genres:,}" }) except Exception as e: return JSONResponse({'status': 'error', 'message': str(e)}, status_code=500) # Serves chart datasets via SQL aggregations async def get_chart_data(request): try: # Chart 1: Revenue by Genre c1 = conn.execute(""" SELECT b.Genre, ROUND(SUM(o.Total_Amount), 2) AS Revenue FROM Orders o JOIN Books b ON o.Book_ID = b.Book_ID GROUP BY b.Genre ORDER BY Revenue DESC LIMIT 5; """).fetchall() # Chart 2: Monthly sales trend c2 = conn.execute(""" SELECT strftime('%Y-%m', Order_Date) AS Month, ROUND(SUM(Total_Amount), 2) AS Revenue FROM Orders GROUP BY Month ORDER BY Month; """).fetchall() # Chart 3: Top Selling Authors c3 = conn.execute(""" SELECT b.Author, SUM(o.Quantity) AS Units FROM Orders o JOIN Books b ON o.Book_ID = b.Book_ID GROUP BY b.Author ORDER BY Units DESC LIMIT 5; """).fetchall() # Chart 4: Inventory levels vs Units Sold by Genre c4 = conn.execute(""" SELECT b.Genre, SUM(b.Stock) AS Inventory, COALESCE(SUM(o.Quantity), 0) AS Sold FROM Books b LEFT JOIN Orders o ON b.Book_ID = o.Book_ID GROUP BY b.Genre ORDER BY Inventory DESC; """).fetchall() return JSONResponse({ 'status': 'success', 'genre_revenue': {'labels': [r[0] for r in c1], 'data': [r[1] for r in c1]}, 'sales_trend': {'labels': [r[0] for r in c2], 'data': [r[1] for r in c2]}, 'top_authors': {'labels': [r[0] for r in c3], 'data': [r[1] for r in c3]}, 'inventory': { 'labels': [r[0] for r in c4], 'inventory': [r[1] for r in c4], 'sold': [r[2] for r in c4] } }) except Exception as e: return JSONResponse({'status': 'error', 'message': str(e)}, status_code=500) # Serves first 50 rows of raw tables for Data Explorer async def get_raw_table(request): try: table_name = request.query_params.get('table', 'Books') if table_name not in ['Books', 'Customers', 'Orders']: return JSONResponse({'status': 'error', 'message': 'Invalid table name'}, status_code=400) cursor = conn.cursor() cursor.execute(f"SELECT * FROM {table_name} LIMIT 50;") columns = [col[0] for col in cursor.description] rows = cursor.fetchall() return JSONResponse({ 'status': 'success', 'columns': columns, 'values': rows }) except Exception as e: return JSONResponse({'status': 'error', 'message': str(e)}, status_code=500) # Executes custom/presets queries from SQL editor async def execute_query(request): try: body = await request.json() sql_query = body.get('sql', '').strip() if not sql_query: return JSONResponse({'status': 'error', 'message': 'Empty query'}, status_code=400) # Time SQL execution start_time = time.perf_counter() cursor = conn.cursor() cursor.execute(sql_query) # Check if query returned rows (e.g. SELECT) or just modified database columns = [col[0] for col in cursor.description] if cursor.description else [] rows = cursor.fetchall() end_time = time.perf_counter() exec_time_ms = (end_time - start_time) * 1000 return JSONResponse({ 'status': 'success', 'columns': columns, 'values': rows, 'time': f"{exec_time_ms:.2f} ms" }) except Exception as e: return JSONResponse({ 'status': 'error', 'message': str(e) }, status_code=400) # --- Routing --- routes = [ Route('/', serve_homepage), Route('/style.css', serve_css), Route('/app.js', serve_js), Route('/api/kpis', get_kpis), Route('/api/charts', get_chart_data), Route('/api/raw', get_raw_table), Route('/api/execute', execute_query, methods=['POST']), ] from contextlib import asynccontextmanager @asynccontextmanager async def lifespan(app): seed_database() yield # Instantiate application app = Starlette(routes=routes, lifespan=lifespan) if __name__ == '__main__': # Render sets the PORT environment variable. Fall back to 8080 for local testing. port = int(os.environ.get("PORT", 8080)) # Bind to 0.0.0.0 to allow Render/external routing to access the container uvicorn.run("app:app", host="0.0.0.0", port=port, log_level="info", reload=False)