khwajai/spreadsheet

1import os2import pickle3import pandas as pd4import numpy as np5import io6import json7import PyPDF28from fastapi import FastAPI, UploadFile, File, Form, Request9from fastapi.responses import JSONResponse10from fastapi.staticfiles import StaticFiles11from fastapi.middleware.cors import CORSMiddleware12from pydantic import BaseModel13import google.generativeai as genai14from PIL import Image15import config16 17app = FastAPI()18app.add_middleware(CORSMiddleware, allow_origins=["*"], allow_methods=["*"], allow_headers=["*"])19 20model = None21if config.GOOGLE_API_KEY:22    try:23        genai.configure(api_key=config.GOOGLE_API_KEY)24        model = genai.GenerativeModel('gemini-3.5-flash')25    except: pass26 27class WorkspaceState:28    def __init__(self):29        self.sheets = {"Sheet 1": []}30        self.active_sheet = "Sheet 1"31        self.columns = {"Sheet 1": ["Date","Department","Revenue","Expenses","Status"]}32        self.load_from_disk()33 34    def get_active_data(self): return self.sheets.get(self.active_sheet, [])35    def set_active_data(self, data, columns=None):36        self.sheets[self.active_sheet] = data37        if columns: self.columns[self.active_sheet] = columns38        self.save_to_disk()39 40    def add_sheet(self, name, data, columns):41        base_name = name.replace(".csv", "").replace(".xlsx", "").replace(".pdf", "")42        counter = 1; unique_name = base_name43        while unique_name in self.sheets: unique_name = f"{base_name} ({counter})"; counter += 144        self.sheets[unique_name] = data45        self.columns[unique_name] = columns46        self.active_sheet = unique_name47        self.save_to_disk()48        return unique_name49 50    def remove_sheet(self, name):51        if name in self.sheets:52            del self.sheets[name]; del self.columns[name]53            if self.active_sheet == name:54                self.active_sheet = list(self.sheets.keys())[0] if self.sheets else "Sheet 1"55                if not self.sheets: self.__init__()56            self.save_to_disk()57 58    def save_to_disk(self):59        try:60            with open(config.STATE_FILE, "wb") as f: pickle.dump((self.sheets, self.active_sheet, self.columns), f)61        except: pass62 63    def load_from_disk(self):64        if os.path.exists(config.STATE_FILE):65            try:66                with open(config.STATE_FILE, "rb") as f:67                    data = pickle.load(f)68                    if len(data) == 3: self.sheets, self.active_sheet, self.columns = data69                    else: self.sheets, self.active_sheet = data70            except: self.__init__()71 72workspace = WorkspaceState()73 74def find_header_row(df):75    max_non_nulls = 0; header_idx = 076    for i, row in df.head(15).iterrows():77        non_null_count = row.dropna().astype(str).str.strip().replace('', np.nan).count()78        if non_null_count > max_non_nulls: max_non_nulls = non_null_count; header_idx = i79    if header_idx > 0:80        df.columns = df.iloc[header_idx].astype(str).str.strip()81        df = df.iloc[header_idx+1:].reset_index(drop=True)82    return df83 84def standardize_grid(df):85    df = df.dropna(how='all').fillna("")86    cols = []87    for c in df.columns:88        clean_c = str(c).strip()89        if clean_c.lower() in ["nan", "unnamed", "none", ""] or not clean_c: clean_c = "Data_Col"90        original = clean_c; count = 191        while clean_c in cols: clean_c = f"{original} ({count})"; count += 192        cols.append(clean_c)93    df.columns = cols94    if df.empty: return [], ["Col 1", "Col 2"]95    return df.to_dict(orient="records"), list(df.columns)96 97@app.get("/business-stats")98async def get_business_stats():99    data = workspace.get_active_data()100    if not data: return {"revenue": 0.0, "expenses": 0.0, "profit": 0.0, "margin": 0.0, "count": 0}101    df = pd.DataFrame(data)102    rev_col = next((c for c in df.columns if any(k in c.lower() for k in ['rev', 'sale', 'income', 'total', 'amount'])), None)103    exp_col = next((c for c in df.columns if any(k in c.lower() for k in ['exp', 'cost', 'tax', 'debit'])), None)104    revenue = float(pd.to_numeric(df[rev_col].astype(str).str.replace(r'[^\d.-]', '', regex=True), errors='coerce').sum()) if rev_col else 0.0105    expenses = float(pd.to_numeric(df[exp_col].astype(str).str.replace(r'[^\d.-]', '', regex=True), errors='coerce').sum()) if exp_col else 0.0106    profit = float(revenue - expenses)107    margin = float((profit / revenue * 100)) if revenue > 0 else 0.0108    return {"revenue": revenue, "expenses": expenses, "profit": profit, "margin": round(margin, 2), "count": len(df)}109 110@app.post("/upload")111async def upload_file(file: UploadFile = File(...), mode: str = Form("replace")):112    try:113        content = await file.read()114        filename = file.filename.lower()115        file_io = io.BytesIO(content)116        if filename.endswith('.csv'): df = pd.read_csv(file_io, low_memory=False)117        elif filename.endswith(('.xls', '.xlsx')): df = pd.read_excel(file_io, engine='openpyxl')118        else: return JSONResponse(status_code=400, content={"message": "Unsupported format."})119        120        new_data, new_cols = standardize_grid(df)121        if mode == "append":122            current_data = workspace.get_active_data()123            workspace.set_active_data(current_data + new_data, new_cols)124        else: workspace.add_sheet(file.filename, new_data, new_cols)125        return {"status": "success", "rows_loaded": len(new_data)}126    except Exception as e: return JSONResponse(status_code=500, content={"message": str(e)})127 128@app.post("/promote-header")129async def promote_header():130    try:131        df = pd.DataFrame(workspace.get_active_data())132        if df.empty: return {"status": "error"}133        new_header = df.iloc[0]; df = df[1:]; df.columns = new_header 134        cleaned_data, cols = standardize_grid(df)135        workspace.set_active_data(cleaned_data, cols)136        return {"status": "success"}137    except Exception as e: return {"error": str(e)}138 139@app.post("/cleanup")140async def cleanup_data():141    try:142        df = pd.DataFrame(workspace.get_active_data())143        for col in df.columns:144            if 'date' in col.lower() or df[col].astype(str).str.match(r'^\d{2,4}[-/]\d{2}[-/]\d{2,4}').any():145                df[col] = pd.to_datetime(df[col], errors='coerce').dt.strftime('%Y-%m-%d')146        df = df.ffill().fillna("")147        for col in df.columns:148            if any(k in col.lower() for k in ['amount', 'debit', 'credit', 'sales', 'revenue', 'expenses', 'profit', 'tax', 'cost', 'price', 'total']):149                df[col] = df[col].astype(str).str.replace(r'[^\d.-]', '', regex=True)150                df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)151        df.dropna(how='all', inplace=True); df.drop_duplicates(inplace=True); df.fillna("", inplace=True)152        cleaned_data, cols = standardize_grid(df)153        workspace.set_active_data(cleaned_data, cols)154        return {"status": "cleaned"}155    except Exception as e: return JSONResponse(status_code=500, content={"message": str(e)})156 157class UpdateRequest(BaseModel): data: list; columns: list158@app.post("/sheet/update")159async def update_sheet(req: UpdateRequest):160    try:161        workspace.set_active_data(req.data, req.columns)162        return {"status": "success"}163    except Exception as e: return {"error": str(e)}164 165class ModelRequest(BaseModel): selected_cols: list; filter_val: str166@app.post("/model-data")167async def model_data(req: ModelRequest):168    try:169        df = pd.DataFrame(workspace.get_active_data())170        if req.selected_cols: df = df[[c for c in req.selected_cols if c in df.columns]]171        if req.filter_val:172            mask = np.column_stack([df[col].astype(str).str.contains(req.filter_val, case=False, na=False) for col in df])173            df = df.loc[mask.any(axis=1)]174        trans_data, trans_cols = standardize_grid(df)175        sheet_name = workspace.add_sheet(f"Custom Model - {len(workspace.sheets)}", trans_data, trans_cols)176        return {"status": "success", "sheet": sheet_name}177    except Exception as e: return {"error": str(e)}178 179class PivotRequest(BaseModel): group_col: str180@app.post("/pivot")181async def generate_pivot(req: PivotRequest):182    try:183        df = pd.DataFrame(workspace.get_active_data())184        if req.group_col not in df.columns: return {"error": "Invalid column"}185        for col in df.columns:186            if col != req.group_col: df[col] = pd.to_numeric(df[col].astype(str).str.replace(r'[^\d.-]', '', regex=True), errors='coerce')187        pivot_df = df.groupby(req.group_col).sum(numeric_only=True).reset_index()188        pivot_data, pivot_cols = standardize_grid(pivot_df)189        sheet_name = workspace.add_sheet(f"Pivot - {req.group_col}", pivot_data, pivot_cols)190        return {"status": "success", "sheet": sheet_name}191    except Exception as e: return {"error": str(e)}192 193@app.get("/generate-insights")194async def generate_insights():195    if not model: return {"narrative": "AI not configured."}196    try:197        df = pd.DataFrame(workspace.get_active_data())198        prompt = f"Analyze this dataset summary: {df.describe().to_string()}. Provide a concise, 3-sentence executive summary identifying core trends. Use <b> tags."199        return {"narrative": model.generate_content(prompt).text.strip()}200    except Exception as e: return {"narrative": "Failed to generate insights."}201 202@app.post("/visualize/chart")203async def get_chart_data(request: Request):204    try:205        body = await request.json()206        chart_type, x_col, y_col, agg = body.get("type"), body.get("x"), body.get("y"), body.get("agg", "sum")207        df = pd.DataFrame(workspace.get_active_data())208        if df.empty: return {"error": "No data"}209 210        if y_col in df.columns:211            df[y_col] = pd.to_numeric(df[y_col].astype(str).str.replace(r'[^\d.-]', '', regex=True), errors='coerce').fillna(0)212        213        if x_col in df.columns: df[x_col] = df[x_col].astype(str)214 215        chart_data = {"x": [], "y": [], "type": chart_type}216        if x_col in df.columns and y_col in df.columns:217            if agg == 'sum': df = df.groupby(x_col, as_index=False)[y_col].sum()218            elif agg == 'avg': df = df.groupby(x_col, as_index=False)[y_col].mean()219            elif agg == 'count': df = df.groupby(x_col, as_index=False)[y_col].count()220            221            df = df.sort_values(by=y_col, ascending=False)222            chart_data["x"] = df[x_col].tolist(); chart_data["y"] = df[y_col].tolist()223            224        return {"chart_data": chart_data}225    except Exception as e: return {"error": str(e)}226 227@app.get("/grid")228async def get_grid():229    data = workspace.get_active_data()230    cols = workspace.columns.get(workspace.active_sheet, ["A","B","C","D"])231    return {"sheets": list(workspace.sheets.keys()), "active": workspace.active_sheet, "data": data, "columns": cols}232 233@app.post("/sheet/add")234async def add_sheet(name: str = Form(...), cols: str = Form(None)):235    col_list = json.loads(cols) if cols else ["Date","Department","Revenue","Expenses","Status"]236    workspace.add_sheet(name, [], col_list)237    return await get_grid()238 239@app.post("/sheet/switch")240async def switch_sheet(name: str = Form(...)):241    if name in workspace.sheets: workspace.active_sheet = name; workspace.save_to_disk()242    return await get_grid()243 244@app.post("/sheet/close")245async def close_sheet(name: str = Form(...)):246    workspace.remove_sheet(name)247    return await get_grid()248 249@app.post("/chat")250async def chat_ai(request: Request):251    if not model: return {"response": "⚠️ AI Model Key not verified."}252    try:253        body = await request.json()254        user_msg = body.get("message", "")255        context = body.get("context", "grid")256        df = pd.DataFrame(workspace.get_active_data())257        headers = list(df.columns) if not df.empty else []258        default_x = headers[0] if headers else 'Date'259        default_y = headers[1] if len(headers)>1 else 'Value'260        261        prompt = f"""Role: Enterprise BI Analyst. Active Tab: {context}. Columns: {headers}. Query: {user_msg}. 262        If user asks to generate, build, or combine sample data into the sheet, append the data as JSON: <<GRID_MERGE:[{{"New Col":"Val1"}}]>>. Make sure the length matches the row count ({len(df)} rows).263        If user asks for a chart, append: <<CHART_ACTION:{{"type":"bar","x":"{default_x}","y":"{default_y}"}}>>264        """265        response = model.generate_content(prompt)266        return {"response": response.text}267    except Exception as e: return {"response": f"AI Engine Exception: {str(e)}"}268 269class ReportRequest(BaseModel): report_type: str; layout_style: str270@app.post("/generate-report")271async def generate_executive_report(req: ReportRequest):272    if not model: return {"narrative": "AI Error: Gemini API key missing."}273    df = pd.DataFrame(workspace.get_active_data())274    prompt = f"Write an executive '{req.report_type}'. Style: {req.layout_style}. Data summary: {df.describe().to_string()}. Use HTML tags like <h2>, <p>, <ul>. NO markdown."275    return {"narrative": model.generate_content(prompt).text.replace("```html", "").replace("```", "").strip()}276 277class TextAction(BaseModel): action: str; text: str278@app.post("/publisher-ai")279async def publisher_ai(req: TextAction):280    if not model: return {"result": req.text}281    prompts = {"expand": f"Expand into a professional paragraph: '{req.text}'", "summarize": f"Summarize concisely: '{req.text}'", "professional": f"Rewrite to sound like a consultant: '{req.text}'"}282    return {"result": model.generate_content(prompts.get(req.action, req.text)).text.replace("```html", "").replace("```", "").strip()}283 284app.mount("/", StaticFiles(directory=".", html=True), name="static")285if __name__ == "__main__":286    import uvicorn287    uvicorn.run(app, host="0.0.0.0", port=7860)