app.py

import os
import requests
from fastapi import FastAPI, HTTPException, Query
from fastapi.responses import HTMLResponse
from pydantic import BaseModel

app = FastAPI(title="SQLite Multi-Project on HF Space")

SQLITE_URL = "http://localhost:8000"

class QueryRequest(BaseModel):
    sql: str
    project: str = "default"

class ExecuteRequest(BaseModel):
    sql: str
    project: str = "default"

class ProjectRequest(BaseModel):
    name: str
    description: str = ""

@app.get("/", response_class=HTMLResponse)
def read_root():
    return """
    <html>
        <head>
            <title>SQLite Multi-Project Server</title>
            <style>
                body { font-family: Arial; margin: 40px; background: #f5f5f5; }
                h1 { color: #333; }
                .info { background: white; padding: 20px; border-radius: 8px; margin: 10px 0; }
                code { background: #e0e0e0; padding: 2px 6px; border-radius: 3px; }
                ul { line-height: 1.8; }
            </style>
        </head>
        <body>
            <h1>✅ SQLite Multi-Project Server</h1>
            <div class="info">
                <p><strong>Persistent Storage:</strong> /data/ (organized by project)</p>
                <p><strong>API Docs:</strong> <a href="/docs">Swagger UI</a></p>
            </div>

            <h2>Project Management</h2>
            <ul>
                <li><code>GET /api/v1/projects</code> - List all projects</li>
                <li><code>POST /api/v1/projects</code> - Create new project</li>
                <li><code>DELETE /api/v1/projects/{name}</code> - Delete project</li>
            </ul>

            <h2>Database Operations</h2>
            <ul>
                <li><code>GET /api/v1/tables?project=myproject</code> - List tables</li>
                <li><code>GET /api/v1/schema?project=myproject</code> - Get schema</li>
                <li><code>POST /api/v1/query</code> - Execute SELECT</li>
                <li><code>POST /api/v1/execute</code> - Execute INSERT/UPDATE/DELETE</li>
            </ul>

            <h2>Example: Multiple Projects</h2>
            <pre>
📁 /data/
├── project1.db (users, orders tables)
├── project2.db (products, inventory tables)
└── project3.db (logs, analytics tables)
            </pre>
        </body>
    </html>
    """

@app.get("/portal", response_class=HTMLResponse)
def portal():
    return """
    <!doctype html>
    <html lang="en">
      <head>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1" />
        <title>SQLite Portal</title>
        <style>
          body {
            font-family: Arial, sans-serif;
            margin: 24px;
            color: #1f2328;
            background: #f6f8fa;
          }
          h1 { margin: 0 0 8px; }
          .meta { color: #57606a; margin-bottom: 16px; }
          .grid {
            display: grid;
            gap: 16px;
            grid-template-columns: 280px 1fr;
          }
          section {
            background: #ffffff;
            border: 1px solid #d0d7de;
            border-radius: 8px;
            padding: 16px;
          }
          textarea {
            width: 100%;
            min-height: 160px;
            font-family: ui-monospace, SFMono-Regular, Menlo, Monaco, Consolas,
              "Liberation Mono", "Courier New", monospace;
            border: 1px solid #d0d7de;
            border-radius: 6px;
            padding: 8px;
            box-sizing: border-box;
          }
          button {
            background: #0969da;
            color: #ffffff;
            border: none;
            border-radius: 6px;
            padding: 8px 12px;
            cursor: pointer;
          }
          button.secondary { background: #6e7781; }
          #tables { list-style: none; padding: 0; margin: 12px 0 0; }
          #tables li { padding: 6px 8px; border-bottom: 1px solid #d8dee4; }
          #tables li:last-child { border-bottom: none; }
          .actions { display: flex; gap: 8px; margin-top: 8px; }
          #status { margin-top: 12px; color: #57606a; white-space: pre-wrap; }
          table { width: 100%; border-collapse: collapse; margin-top: 12px; }
          th, td {
            border: 1px solid #d0d7de;
            padding: 6px 8px;
            text-align: left;
            vertical-align: top;
            font-size: 14px;
          }
          th { background: #f6f8fa; }
          pre {
            background: #f6f8fa;
            border: 1px solid #d0d7de;
            border-radius: 6px;
            padding: 12px;
            overflow: auto;
          }
          @media (max-width: 900px) {
            .grid { grid-template-columns: 1fr; }
          }
        </style>
      </head>
      <body>
        <h1>SQLite Portal</h1>
        <div class="meta" id="meta">Loading database info...</div>
        <div class="grid">
          <section>
            <h2>Projects</h2>
            <select id="project"></select>
            <div class="actions">
              <button id="refresh">Refresh</button>
            </div>
            <h3>Tables</h3>
            <ul id="tables"></ul>
          </section>
          <section>
            <h2>SQL Editor</h2>
            <textarea id="sql">SELECT * FROM products LIMIT 50;</textarea>
            <div class="actions">
              <button id="run">Run</button>
              <button id="clear" class="secondary">Clear Output</button>
            </div>
            <div id="status"></div>
            <div id="results"></div>
          </section>
        </div>

        <script>
          const projectEl = document.getElementById("project");
          const tablesEl = document.getElementById("tables");
          const metaEl = document.getElementById("meta");
          const statusEl = document.getElementById("status");
          const resultsEl = document.getElementById("results");
          const sqlEl = document.getElementById("sql");

          function setStatus(message) {
            statusEl.textContent = message || "";
          }

          function clearResults() {
            resultsEl.innerHTML = "";
            setStatus("");
          }

          async function fetchJson(url, options) {
            const response = await fetch(url, options);
            const data = await response.json();
            if (!response.ok) {
              const errorMessage = data.detail || data.error || data.message || response.statusText;
              throw new Error(errorMessage);
            }
            return data;
          }

          function renderTable(rows) {
            if (!Array.isArray(rows) || rows.length === 0) {
              resultsEl.innerHTML = "<em>No rows returned.</em>";
              return;
            }
            const columns = Object.keys(rows[0]);
            const table = document.createElement("table");
            const thead = document.createElement("thead");
            const headerRow = document.createElement("tr");
            columns.forEach((col) => {
              const th = document.createElement("th");
              th.textContent = col;
              headerRow.appendChild(th);
            });
            thead.appendChild(headerRow);
            table.appendChild(thead);

            const tbody = document.createElement("tbody");
            rows.forEach((row) => {
              const tr = document.createElement("tr");
              columns.forEach((col) => {
                const td = document.createElement("td");
                const value = row[col] === null || row[col] === undefined ? "" : row[col];
                td.textContent = value;
                tr.appendChild(td);
              });
              tbody.appendChild(tr);
            });
            table.appendChild(tbody);
            resultsEl.innerHTML = "";
            resultsEl.appendChild(table);
          }

          function currentProject() {
            return projectEl.value || "default";
          }

          async function loadInfo() {
            try {
              metaEl.textContent = `Server: ${window.location.origin} | Project: ${currentProject()}`;
            } catch (err) {
              metaEl.textContent = `Failed to load info: ${err.message}`;
            }
          }

          async function loadProjects() {
            projectEl.innerHTML = "";
            try {
              const data = await fetchJson("/api/v1/projects");
              const projects = Array.isArray(data)
                ? data
                : data.projects || data.data || [];
              const names = projects
                .map((p) => (typeof p === "string" ? p : p.name))
                .filter(Boolean);
              const uniqueNames = names.length ? names : ["default"];
              uniqueNames.forEach((name) => {
                const option = document.createElement("option");
                option.value = name;
                option.textContent = name;
                projectEl.appendChild(option);
              });
            } catch (err) {
              const option = document.createElement("option");
              option.value = "default";
              option.textContent = "default";
              projectEl.appendChild(option);
              setStatus(`Failed to load projects: ${err.message}`);
            }
          }

          async function loadTables() {
            tablesEl.innerHTML = "";
            try {
              const project = currentProject();
              const data = await fetchJson(`/api/v1/tables?project=${project}`);
              const tables = data.tables || data.data || [];
              const names = tables
                .map((t) => (typeof t === "string" ? t : t.name))
                .filter(Boolean);
              if (!names.length) {
                tablesEl.innerHTML = "<li>No tables found.</li>";
                return;
              }
              names.forEach((name) => {
                const li = document.createElement("li");
                li.textContent = name;
                li.addEventListener("click", () => {
                  sqlEl.value = `SELECT * FROM ${name} LIMIT 50;`;
                });
                tablesEl.appendChild(li);
              });
            } catch (err) {
              tablesEl.innerHTML = `<li>Error: ${err.message}</li>`;
            }
          }

          async function runSql() {
            clearResults();
            const sql = sqlEl.value.trim();
            if (!sql) {
              setStatus("Enter SQL to run.");
              return;
            }
            setStatus("Running...");
            const verb = sql.split(/\s+/)[0]?.toUpperCase();
            const isQuery = ["SELECT", "PRAGMA", "WITH", "EXPLAIN"].includes(verb);
            const endpoint = isQuery ? "/api/v1/query" : "/api/v1/execute";
            const project = currentProject();
            try {
              const result = await fetchJson(endpoint, {
                method: "POST",
                headers: { "Content-Type": "application/json" },
                body: JSON.stringify({ sql, project }),
              });
              if (isQuery) {
                const rows = result.data || [];
                setStatus(`Query returned ${rows.length} rows.`);
                renderTable(rows);
              } else {
                setStatus(`Statement executed.`);
                resultsEl.innerHTML = `<pre>${JSON.stringify(result, null, 2)}</pre>`;
              }
            } catch (err) {
              setStatus(`Error: ${err.message}`);
            }
          }

          document.getElementById("refresh").addEventListener("click", () => {
            loadProjects().then(loadTables).then(loadInfo);
          });
          projectEl.addEventListener("change", () => {
            loadTables();
            loadInfo();
          });
          document.getElementById("run").addEventListener("click", runSql);
          document.getElementById("clear").addEventListener("click", clearResults);

          loadProjects().then(loadTables).then(loadInfo);
        </script>
      </body>
    </html>
    """

@app.get("/health")
def health_check():
    try:
        response = requests.get(f"{SQLITE_URL}/api/v1/heartbeat", timeout=5)
        if response.status_code == 200:
            return {"status": "healthy", "sqlite": "up"}
        else:
            return {"status": "unhealthy", "detail": response.text}
    except Exception as e:
        return {"status": "error", "detail": str(e)}

@app.get("/api/v1/heartbeat")
def heartbeat():
    try:
        response = requests.get(f"{SQLITE_URL}/api/v1/heartbeat", timeout=5)
        return response.json()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"SQLite server error: {str(e)}")

@app.get("/api/v1/projects")
def list_projects():
    try:
        response = requests.get(f"{SQLITE_URL}/api/v1/projects", timeout=5)
        if response.status_code != 200:
            raise HTTPException(status_code=response.status_code, detail=response.text)
        return response.json()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Projects error: {str(e)}")

@app.post("/api/v1/projects")
def create_project(request: ProjectRequest):
    try:
        response = requests.post(f"{SQLITE_URL}/api/v1/projects", json={"name": request.name, "description": request.description}, timeout=5)
        if response.status_code != 200:
            raise HTTPException(status_code=response.status_code, detail=response.text)
        return response.json()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Create project error: {str(e)}")

@app.delete("/api/v1/projects/{project_name}")
def delete_project(project_name: str):
    try:
        response = requests.delete(f"{SQLITE_URL}/api/v1/projects/{project_name}", timeout=5)
        if response.status_code != 200:
            raise HTTPException(status_code=response.status_code, detail=response.text)
        return response.json()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Delete project error: {str(e)}")

@app.post("/api/v1/query")
def query(request: QueryRequest):
    try:
        response = requests.post(f"{SQLITE_URL}/api/v1/query", json={"sql": request.sql, "project": request.project}, timeout=10)
        if response.status_code != 200:
            raise HTTPException(status_code=response.status_code, detail=response.text)
        return response.json()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Query error: {str(e)}")

@app.post("/api/v1/execute")
def execute(request: ExecuteRequest):
    try:
        response = requests.post(f"{SQLITE_URL}/api/v1/execute", json={"sql": request.sql, "project": request.project}, timeout=10)
        if response.status_code != 200:
            raise HTTPException(status_code=response.status_code, detail=response.text)
        return response.json()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Execute error: {str(e)}")

@app.get("/api/v1/tables")
def list_tables(project: str = Query("default")):
    try:
        response = requests.get(f"{SQLITE_URL}/api/v1/tables?project={project}", timeout=5)
        if response.status_code != 200:
            raise HTTPException(status_code=response.status_code, detail=response.text)
        return response.json()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Tables error: {str(e)}")

@app.get("/api/v1/schema")
def get_schema(project: str = Query("default")):
    try:
        response = requests.get(f"{SQLITE_URL}/api/v1/schema?project={project}", timeout=5)
        if response.status_code != 200:
            raise HTTPException(status_code=response.status_code, detail=response.text)
        return response.json()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Schema error: {str(e)}")

if __name__ == "__main__":
    import uvicorn
    port = int(os.getenv("PORT", 7860))
    uvicorn.run(app, host="0.0.0.0", port=port)