app.py

import os, json, subprocess, tempfile, shutil, base64, sqlite3, io
import bcrypt
from datetime import datetime, timedelta
from pathlib import Path
from typing import Optional, List

import httpx
from fastapi import FastAPI, UploadFile, File, HTTPException, Depends, Form, Request
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import HTMLResponse, Response, StreamingResponse
from pydantic import BaseModel
from jose import JWTError, jwt

# ── Config ────────────────────────────────────────────────────────────────────
SECRET_KEY        = os.environ.get("SECRET_KEY", "php-hosting-secret-key-change-me")
ALGORITHM         = "HS256"
TOKEN_EXPIRE_MINS = 60 * 24 * 7
MAX_FILE_BYTES    = 25 * 1024 * 1024

# ── GitHub Config ─────────────────────────────────────────────────────────────
GITHUB_TOKEN    = os.environ.get("GITHUB_TOKEN")
GITHUB_USERNAME = os.environ.get("GITHUB_USERNAME")
GITHUB_REPO     = "Master"
GITHUB_BASE     = "PHP/Users"
GITHUB_API      = "https://api.github.com"

# ── Local storage ─────────────────────────────────────────────────────────────
DATA_DIR  = Path("/data")
DB_FILE   = DATA_DIR / "db" / "users.json"
DBS_DIR   = DATA_DIR / "databases"
DB_FILE.parent.mkdir(parents=True, exist_ok=True)
DBS_DIR.mkdir(parents=True, exist_ok=True)

# ── User DB (JSON) ────────────────────────────────────────────────────────────
def load_db() -> dict:
    if DB_FILE.exists():
        try:
            return json.loads(DB_FILE.read_text())
        except:
            return {}
    return {}

def save_db(db: dict):
    DB_FILE.write_text(json.dumps(db, indent=2))

# ── Security ──────────────────────────────────────────────────────────────────
def hash_password(p: str) -> str:
    try:
        pwd_bytes = p.encode("utf-8")[:72]
        salt = bcrypt.gensalt(rounds=10)
        return bcrypt.hashpw(pwd_bytes, salt).decode("utf-8")
    except Exception as e:
        raise HTTPException(500, f"Password hashing error: {e}")

def verify_password(plain: str, hashed: str) -> bool:
    try:
        return bcrypt.checkpw(plain.encode("utf-8")[:72], hashed.encode("utf-8"))
    except:
        return False

def create_jwt(data: dict) -> str:
    to_encode = data.copy()
    expire = datetime.utcnow() + timedelta(minutes=TOKEN_EXPIRE_MINS)
    to_encode.update({"exp": expire})
    return jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM)

def decode_jwt(token: str):
    try:
        return jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM])
    except JWTError:
        return None

# ── GitHub helpers ─────────────────────────────────────────────────────────────
def gh_headers() -> dict:
    if not GITHUB_TOKEN:
        raise HTTPException(500, "GitHub token not configured")
    return {
        "Authorization": f"token {GITHUB_TOKEN}",
        "Accept": "application/vnd.github+json",
        "X-GitHub-Api-Version": "2022-11-28",
    }

def gh_path(username: str, repo: str = None, filename: str = None) -> str:
    path = f"{GITHUB_BASE}/{username}"
    if repo:
        path += f"/{repo}"
    if filename:
        path += f"/{filename}"
    return path

def gh_db_path(username: str, dbname: str) -> str:
    return f"{GITHUB_BASE}/{username}/_databases/{dbname}.db"

async def gh_get_file(path: str) -> Optional[dict]:
    url = f"{GITHUB_API}/repos/{GITHUB_USERNAME}/{GITHUB_REPO}/contents/{path}"
    async with httpx.AsyncClient(timeout=30) as client:
        res = await client.get(url, headers=gh_headers())
        if res.status_code == 404:
            return None
        if res.status_code != 200:
            raise HTTPException(res.status_code, f"GitHub error: {res.text}")
        return res.json()

async def gh_put_file(path: str, content_bytes: bytes, message: str, sha: str = None):
    url  = f"{GITHUB_API}/repos/{GITHUB_USERNAME}/{GITHUB_REPO}/contents/{path}"
    body = {
        "message": message,
        "content": base64.b64encode(content_bytes).decode("utf-8"),
    }
    if sha:
        body["sha"] = sha
    async with httpx.AsyncClient(timeout=60) as client:
        res = await client.put(url, headers=gh_headers(), json=body)
        if res.status_code not in (200, 201):
            raise HTTPException(res.status_code, f"GitHub upload error: {res.text}")
        return res.json()

async def gh_delete_file(path: str, sha: str, message: str):
    url  = f"{GITHUB_API}/repos/{GITHUB_USERNAME}/{GITHUB_REPO}/contents/{path}"
    body = {"message": message, "sha": sha}
    async with httpx.AsyncClient(timeout=30) as client:
        res = await client.delete(url, headers=gh_headers(), json=body)
        if res.status_code not in (200, 204):
            raise HTTPException(res.status_code, f"GitHub delete error: {res.text}")

async def gh_list_folder(path: str) -> list:
    url = f"{GITHUB_API}/repos/{GITHUB_USERNAME}/{GITHUB_REPO}/contents/{path}"
    async with httpx.AsyncClient(timeout=30) as client:
        res = await client.get(url, headers=gh_headers())
        if res.status_code == 404:
            return []
        if res.status_code != 200:
            return []
        return res.json()

async def gh_create_placeholder(path: str, username: str):
    placeholder_path = f"{path}/.gitkeep"
    existing = await gh_get_file(placeholder_path)
    if not existing:
        await gh_put_file(placeholder_path, b"", f"Create folder for {username}")

async def gh_ensure_repo_exists():
    url = f"{GITHUB_API}/repos/{GITHUB_USERNAME}/{GITHUB_REPO}"
    async with httpx.AsyncClient(timeout=30) as client:
        res = await client.get(url, headers=gh_headers())
        if res.status_code == 404:
            create_url = f"{GITHUB_API}/user/repos"
            await client.post(create_url, headers=gh_headers(), json={
                "name": GITHUB_REPO,
                "private": True,
                "description": "PHP Hosting Storage"
            })

# ── SQLite database helpers ────────────────────────────────────────────────────
def get_user_db_dir(username: str) -> Path:
    p = DBS_DIR / username
    p.mkdir(parents=True, exist_ok=True)
    return p

def get_db_path(username: str, dbname: str) -> Path:
    return get_user_db_dir(username) / f"{dbname}.db"

def get_conn(username: str, dbname: str) -> sqlite3.Connection:
    db_path = get_db_path(username, dbname)
    if not db_path.exists():
        raise HTTPException(404, f"Database '{dbname}' not found")
    conn = sqlite3.connect(str(db_path))
    conn.row_factory = sqlite3.Row
    return conn

async def backup_db_to_github(username: str, dbname: str):
    db_path = get_db_path(username, dbname)
    if not db_path.exists():
        return
    content = db_path.read_bytes()
    gh_path_str = gh_db_path(username, dbname)
    existing = await gh_get_file(gh_path_str)
    sha = existing["sha"] if existing else None
    await gh_put_file(
        gh_path_str,
        content,
        f"Backup database {dbname} for {username}",
        sha
    )

def list_user_databases(username: str) -> list:
    db_dir = get_user_db_dir(username)
    dbs = []
    for f in sorted(db_dir.glob("*.db")):
        conn = sqlite3.connect(str(f))
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
        tables = [row[0] for row in cursor.fetchall()]
        conn.close()
        size = f.stat().st_size
        dbs.append({
            "name":       f.stem,
            "tables":     tables,
            "table_count": len(tables),
            "size_kb":    round(size / 1024, 2),
            "created_at": datetime.fromtimestamp(f.stat().st_ctime).isoformat(),
        })
    return dbs

# ── Auth dependency ────────────────────────────────────────────────────────────
def get_current_user(request: Request) -> dict:
    auth = request.headers.get("Authorization", "")
    if not auth.startswith("Bearer "):
        raise HTTPException(401, "Missing token")
    payload = decode_jwt(auth.split(" ")[1])
    if not payload:
        raise HTTPException(401, "Invalid or expired token")
    username = payload.get("sub")
    db = load_db()
    if username not in db:
        raise HTTPException(401, "User not found")
    return db[username]

# ── App ────────────────────────────────────────────────────────────────────────
app = FastAPI(title="PHP Hosting Backend")

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

# ── Health ─────────────────────────────────────────────────────────────────────
@app.get("/")
def root():
    return {"status": "PHP Hosting Backend is running", "storage": "GitHub + SQLite"}

@app.get("/health")
def health():
    return {"status": "ok", "users": len(load_db())}

# ── Register ───────────────────────────────────────────────────────────────────
class RegisterBody(BaseModel):
    username: str
    password: str

@app.post("/auth/register")
async def register(body: RegisterBody):
    try:
        if len(body.username) < 3:
            raise HTTPException(400, "Username must be at least 3 characters")
        if not body.username.isalnum():
            raise HTTPException(400, "Username must be letters and numbers only")
        if len(body.password) < 8:
            raise HTTPException(400, "Password must be at least 8 characters")
        db = load_db()
        if body.username in db:
            raise HTTPException(400, "Username already taken")
        hashed = hash_password(body.password)
        await gh_ensure_repo_exists()
        user_folder = gh_path(body.username)
        await gh_create_placeholder(user_folder, body.username)
        db[body.username] = {
            "username":      body.username,
            "password_hash": hashed,
            "created_at":    datetime.utcnow().isoformat(),
            "github_path":   user_folder,
        }
        save_db(db)
        get_user_db_dir(body.username)
        token = create_jwt({"sub": body.username})
        return {
            "access_token": token,
            "token_type":   "bearer",
            "username":     body.username,
            "message":      "Account created successfully!"
        }
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Login ──────────────────────────────────────────────────────────────────────
class LoginBody(BaseModel):
    username: str
    password: str

@app.post("/auth/login")
def login(body: LoginBody):
    try:
        db = load_db()
        user = db.get(body.username)
        if not user or not verify_password(body.password, user["password_hash"]):
            raise HTTPException(401, "Invalid username or password")
        token = create_jwt({"sub": body.username})
        return {
            "access_token": token,
            "token_type":   "bearer",
            "username":     body.username,
        }
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Upload files ───────────────────────────────────────────────────────────────
@app.post("/upload")
async def upload_files(
    repo: str = Form(...),
    visibility: str = Form("public"),
    files: list[UploadFile] = File(...),
    user: dict = Depends(get_current_user)
):
    try:
        if not repo.isalnum():
            raise HTTPException(400, "Repo name must be letters and numbers only")
        uploaded = []
        for f in files:
            content = await f.read()
            if len(content) > MAX_FILE_BYTES:
                raise HTTPException(413, f"{f.filename} exceeds 25 MB limit")
            safe_name = Path(f.filename).name
            file_path = gh_path(user["username"], repo, safe_name)
            existing  = await gh_get_file(file_path)
            sha       = existing["sha"] if existing else None
            await gh_put_file(
                file_path, content,
                f"Upload {safe_name} to {user['username']}/{repo}", sha
            )
            uploaded.append({
                "filename": safe_name,
                "size":     len(content),
                "url":      f"https://php-hosting.emalawi19.workers.dev/{user['username']}/{repo}/{safe_name}"
            })

        # Save visibility metadata
        meta_path = gh_path(user["username"], repo, ".meta.json")
        meta_existing = await gh_get_file(meta_path)
        meta_sha = meta_existing["sha"] if meta_existing else None
        meta = {"visibility": visibility, "updated_at": datetime.utcnow().isoformat()}
        await gh_put_file(
            meta_path,
            json.dumps(meta).encode(),
            f"Update metadata for {user['username']}/{repo}",
            meta_sha
        )

        return {
            "uploaded":   uploaded,
            "repo":       repo,
            "visibility": visibility,
            "repo_url":   f"https://php-hosting.emalawi19.workers.dev/{user['username']}/{repo}"
        }
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── List repos and files ───────────────────────────────────────────────────────
@app.get("/files")
async def list_files(user: dict = Depends(get_current_user)):
    try:
        user_folder = gh_path(user["username"])
        items       = await gh_list_folder(user_folder)
        repos       = []
        total_size  = 0
        for item in items:
            if item.get("type") == "dir":
                repo_name  = item["name"]
                if repo_name.startswith("_"):
                    continue
                repo_items = await gh_list_folder(f"{user_folder}/{repo_name}")
                files      = []
                visibility = "public"
                for fi in repo_items:
                    if fi.get("type") == "file":
                        if fi["name"] == ".meta.json":
                            try:
                                meta_info = await gh_get_file(f"{user_folder}/{repo_name}/.meta.json")
                                if meta_info:
                                    meta = json.loads(base64.b64decode(meta_info["content"].replace("\n","")).decode())
                                    visibility = meta.get("visibility","public")
                            except:
                                pass
                            continue
                        if fi["name"] == ".gitkeep":
                            continue
                        size = fi.get("size", 0)
                        total_size += size
                        files.append({
                            "filename": fi["name"],
                            "size":     size,
                            "sha":      fi["sha"],
                            "url":      f"https://php-hosting.emalawi19.workers.dev/{user['username']}/{repo_name}/{fi['name']}"
                        })
                repos.append({
                    "repo":       repo_name,
                    "visibility": visibility,
                    "repo_url":   f"https://php-hosting.emalawi19.workers.dev/{user['username']}/{repo_name}",
                    "files":      files
                })
        return {
            "username": user["username"],
            "repos":    repos,
            "used_mb":  round(total_size / (1024 * 1024), 2),
            "limit_mb": 500
        }
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Delete file ────────────────────────────────────────────────────────────────
@app.delete("/files/{repo}/{filename}")
async def delete_file(repo: str, filename: str, user: dict = Depends(get_current_user)):
    try:
        file_path = gh_path(user["username"], repo, filename)
        existing  = await gh_get_file(file_path)
        if not existing:
            raise HTTPException(404, "File not found")
        await gh_delete_file(file_path, existing["sha"], f"Delete {filename} from {user['username']}/{repo}")
        return {"message": f"{filename} deleted"}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Delete repo ────────────────────────────────────────────────────────────────
@app.delete("/repo/{repo}")
async def delete_repo(repo: str, user: dict = Depends(get_current_user)):
    try:
        repo_folder = gh_path(user["username"], repo)
        items       = await gh_list_folder(repo_folder)
        for item in items:
            if item.get("type") == "file":
                await gh_delete_file(
                    f"{repo_folder}/{item['name']}",
                    item["sha"],
                    f"Delete {item['name']} from {user['username']}/{repo}"
                )
        return {"message": f"Repo {repo} deleted"}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Serve PHP / static files ───────────────────────────────────────────────────
@app.get("/serve/{username}/{repo}/{filename:path}")
async def serve_file(username: str, repo: str, filename: str):
    try:
        file_path = gh_path(username, repo, filename)
        file_info = await gh_get_file(file_path)
        if not file_info:
            if filename == "index.php":
                file_info = await gh_get_file(gh_path(username, repo, "index.html"))
                if file_info:
                    filename = "index.html"
            if not file_info:
                raise HTTPException(404, "File not found")
        content = base64.b64decode(file_info["content"].replace("\n", ""))
        if filename.endswith(".php"):
            with tempfile.NamedTemporaryFile(suffix=".php", delete=False, dir="/tmp") as tmp:
                tmp.write(content)
                tmp_path = tmp.name
            try:
                result = subprocess.run(
                    ["php", tmp_path],
                    capture_output=True, text=True, timeout=30
                )
                output = result.stdout
                if result.returncode != 0:
                    output = f"<pre style='color:red;padding:20px'>PHP Error:\n{result.stderr}</pre>"
            except subprocess.TimeoutExpired:
                output = "<pre style='color:red'>Error: Script timed out</pre>"
            except FileNotFoundError:
                output = "<pre style='color:red'>Error: PHP not installed</pre>"
            finally:
                try:
                    os.unlink(tmp_path)
                except:
                    pass
            return HTMLResponse(content=output)
        ext = filename.split(".")[-1].lower()
        mime_map = {
            "html": "text/html", "css": "text/css",
            "js": "application/javascript", "json": "application/json",
            "png": "image/png", "jpg": "image/jpeg", "jpeg": "image/jpeg",
            "gif": "image/gif", "svg": "image/svg+xml",
            "txt": "text/plain", "ico": "image/x-icon",
        }
        mime = mime_map.get(ext, "application/octet-stream")
        return Response(content=content, media_type=mime)
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ══════════════════════════════════════════════════════════════════════════════
# ── DATABASE ROUTES ───────────────────────────────────────────────────────────
# ══════════════════════════════════════════════════════════════════════════════

# ── List user databases ────────────────────────────────────────────────────────
@app.get("/db")
def list_databases(user: dict = Depends(get_current_user)):
    try:
        dbs = list_user_databases(user["username"])
        return {"databases": dbs, "count": len(dbs)}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Create database ────────────────────────────────────────────────────────────
class CreateDBBody(BaseModel):
    name: str

@app.post("/db/create")
async def create_database(body: CreateDBBody, user: dict = Depends(get_current_user)):
    try:
        if not body.name.replace("_","").isalnum():
            raise HTTPException(400, "Database name must be letters, numbers, underscores only")
        if len(body.name) < 2:
            raise HTTPException(400, "Database name must be at least 2 characters")
        db_path = get_db_path(user["username"], body.name)
        if db_path.exists():
            raise HTTPException(400, f"Database '{body.name}' already exists")
        conn = sqlite3.connect(str(db_path))
        conn.execute("PRAGMA journal_mode=WAL")
        conn.execute("PRAGMA foreign_keys=ON")
        conn.close()
        await backup_db_to_github(user["username"], body.name)
        return {
            "message":  f"Database '{body.name}' created successfully",
            "name":     body.name,
            "php_code": generate_php_connection(user["username"], body.name)
        }
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Drop database ──────────────────────────────────────────────────────────────
@app.delete("/db/{dbname}")
async def drop_database(dbname: str, user: dict = Depends(get_current_user)):
    try:
        db_path = get_db_path(user["username"], dbname)
        if not db_path.exists():
            raise HTTPException(404, f"Database '{dbname}' not found")
        db_path.unlink()
        gh_path_str = gh_db_path(user["username"], dbname)
        existing = await gh_get_file(gh_path_str)
        if existing:
            await gh_delete_file(gh_path_str, existing["sha"], f"Drop database {dbname}")
        return {"message": f"Database '{dbname}' deleted"}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── List tables ────────────────────────────────────────────────────────────────
@app.get("/db/{dbname}/tables")
def list_tables(dbname: str, user: dict = Depends(get_current_user)):
    try:
        conn = get_conn(user["username"], dbname)
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name")
        tables = []
        for row in cursor.fetchall():
            tname = row[0]
            cursor.execute(f"PRAGMA table_info({tname})")
            cols = [{"name": c[1], "type": c[2], "notnull": bool(c[3]), "pk": bool(c[5])} for c in cursor.fetchall()]
            cursor.execute(f"SELECT COUNT(*) FROM `{tname}`")
            count = cursor.fetchone()[0]
            tables.append({"name": tname, "columns": cols, "row_count": count})
        conn.close()
        return {"tables": tables}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Create table ───────────────────────────────────────────────────────────────
class ColumnDef(BaseModel):
    name: str
    type: str
    primary_key: bool = False
    not_null: bool = False
    default: Optional[str] = None
    auto_increment: bool = False

class CreateTableBody(BaseModel):
    table_name: str
    columns: List[ColumnDef]

@app.post("/db/{dbname}/tables")
async def create_table(dbname: str, body: CreateTableBody, user: dict = Depends(get_current_user)):
    try:
        if not body.table_name.replace("_","").isalnum():
            raise HTTPException(400, "Table name must be letters, numbers, underscores only")
        col_defs = []
        for col in body.columns:
            col_sql = f"`{col.name}` {col.type}"
            if col.primary_key:
                col_sql += " PRIMARY KEY"
                if col.auto_increment:
                    col_sql += " AUTOINCREMENT"
            if col.not_null and not col.primary_key:
                col_sql += " NOT NULL"
            if col.default is not None:
                col_sql += f" DEFAULT {col.default}"
            col_defs.append(col_sql)
        sql = f"CREATE TABLE IF NOT EXISTS `{body.table_name}` ({', '.join(col_defs)})"
        conn = get_conn(user["username"], dbname)
        conn.execute(sql)
        conn.commit()
        conn.close()
        await backup_db_to_github(user["username"], dbname)
        return {"message": f"Table '{body.table_name}' created", "sql": sql}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Drop table ─────────────────────────────────────────────────────────────────
@app.delete("/db/{dbname}/tables/{table_name}")
async def drop_table(dbname: str, table_name: str, user: dict = Depends(get_current_user)):
    try:
        conn = get_conn(user["username"], dbname)
        conn.execute(f"DROP TABLE IF EXISTS `{table_name}`")
        conn.commit()
        conn.close()
        await backup_db_to_github(user["username"], dbname)
        return {"message": f"Table '{table_name}' dropped"}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Get table data ─────────────────────────────────────────────────────────────
@app.get("/db/{dbname}/tables/{table_name}/data")
def get_table_data(
    dbname: str,
    table_name: str,
    limit: int = 100,
    offset: int = 0,
    user: dict = Depends(get_current_user)
):
    try:
        conn = get_conn(user["username"], dbname)
        cursor = conn.cursor()
        cursor.execute(f"SELECT COUNT(*) FROM `{table_name}`")
        total = cursor.fetchone()[0]
        cursor.execute(f"SELECT * FROM `{table_name}` LIMIT ? OFFSET ?", (limit, offset))
        rows = cursor.fetchall()
        cols = [d[0] for d in cursor.description]
        data = [dict(zip(cols, row)) for row in rows]
        conn.close()
        return {"columns": cols, "rows": data, "total": total, "limit": limit, "offset": offset}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Run SQL query ──────────────────────────────────────────────────────────────
class RunSQLBody(BaseModel):
    sql: str

@app.post("/db/{dbname}/query")
async def run_query(dbname: str, body: RunSQLBody, user: dict = Depends(get_current_user)):
    try:
        sql = body.sql.strip()
        if not sql:
            raise HTTPException(400, "SQL query cannot be empty")

        # Block dangerous operations
        sql_upper = sql.upper()
        blocked = ["DROP DATABASE", "ATTACH", "DETACH", "PRAGMA"]
        for b in blocked:
            if b in sql_upper:
                raise HTTPException(400, f"Operation '{b}' is not allowed")

        conn = get_conn(user["username"], dbname)
        cursor = conn.cursor()

        try:
            cursor.executescript(sql) if ";" in sql and sql.count(";") > 1 else cursor.execute(sql)
            is_select = sql_upper.startswith("SELECT") or sql_upper.startswith("PRAGMA")
            if is_select:
                rows = cursor.fetchall()
                cols = [d[0] for d in cursor.description] if cursor.description else []
                data = [dict(zip(cols, row)) for row in rows]
                conn.close()
                return {
                    "type":    "select",
                    "columns": cols,
                    "rows":    data,
                    "count":   len(data)
                }
            else:
                conn.commit()
                affected = cursor.rowcount
                conn.close()
                await backup_db_to_github(user["username"], dbname)
                return {
                    "type":     "modify",
                    "message":  "Query executed successfully",
                    "affected": affected
                }
        except sqlite3.Error as e:
            conn.close()
            raise HTTPException(400, f"SQL Error: {str(e)}")

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Insert row ─────────────────────────────────────────────────────────────────
@app.post("/db/{dbname}/tables/{table_name}/rows")
async def insert_row(
    dbname: str,
    table_name: str,
    request: Request,
    user: dict = Depends(get_current_user)
):
    try:
        row_data = await request.json()
        if not row_data:
            raise HTTPException(400, "Row data cannot be empty")
        cols   = ", ".join([f"`{k}`" for k in row_data.keys()])
        placeholders = ", ".join(["?" for _ in row_data])
        values = list(row_data.values())
        sql    = f"INSERT INTO `{table_name}` ({cols}) VALUES ({placeholders})"
        conn   = get_conn(user["username"], dbname)
        cursor = conn.cursor()
        cursor.execute(sql, values)
        conn.commit()
        last_id = cursor.lastrowid
        conn.close()
        await backup_db_to_github(user["username"], dbname)
        return {"message": "Row inserted", "id": last_id}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Delete row ─────────────────────────────────────────────────────────────────
@app.delete("/db/{dbname}/tables/{table_name}/rows/{row_id}")
async def delete_row(
    dbname: str,
    table_name: str,
    row_id: int,
    pk_col: str = "id",
    user: dict = Depends(get_current_user)
):
    try:
        conn = get_conn(user["username"], dbname)
        conn.execute(f"DELETE FROM `{table_name}` WHERE `{pk_col}` = ?", (row_id,))
        conn.commit()
        conn.close()
        await backup_db_to_github(user["username"], dbname)
        return {"message": f"Row {row_id} deleted"}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Export database as SQL ─────────────────────────────────────────────────────
@app.get("/db/{dbname}/export")
def export_database(dbname: str, user: dict = Depends(get_current_user)):
    try:
        db_path = get_db_path(user["username"], dbname)
        if not db_path.exists():
            raise HTTPException(404, "Database not found")
        conn = sqlite3.connect(str(db_path))
        sql_lines = []
        sql_lines.append(f"-- PHP Hosting Database Export")
        sql_lines.append(f"-- Database: {dbname}")
        sql_lines.append(f"-- Exported: {datetime.utcnow().isoformat()}")
        sql_lines.append(f"-- User: {user['username']}")
        sql_lines.append("")
        for line in conn.iterdump():
            sql_lines.append(line)
        conn.close()
        sql_content = "\n".join(sql_lines)
        return Response(
            content=sql_content,
            media_type="application/sql",
            headers={"Content-Disposition": f"attachment; filename={dbname}.sql"}
        )
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Generate PHP connection code ───────────────────────────────────────────────
def generate_php_connection(username: str, dbname: str) -> str:
    return f'''<?php
// ── Database Connection ────────────────────────────────────────────
// Database: {dbname}
// Generated by PHP Hosting Platform

$db_path = __DIR__ . "/{dbname}.db";

try {{
    $pdo = new PDO("sqlite:" . $db_path);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    // Connection successful!
}} catch(PDOException $e) {{
    die("Connection failed: " . $e->getMessage());
}}

// Example usage:
// $stmt = $pdo->prepare("SELECT * FROM your_table");
// $stmt->execute();
// $rows = $stmt->fetchAll();
?>'''

@app.get("/db/{dbname}/phpcode")
def get_php_connection_code(dbname: str, user: dict = Depends(get_current_user)):
    try:
        db_path = get_db_path(user["username"], dbname)
        if not db_path.exists():
            raise HTTPException(404, "Database not found")
        return {"php_code": generate_php_connection(user["username"], dbname)}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))

# ── Storage stats ──────────────────────────────────────────────────────────────
@app.get("/storage")
async def storage_stats(user: dict = Depends(get_current_user)):
    try:
        user_folder = gh_path(user["username"])
        items       = await gh_list_folder(user_folder)
        total_size  = 0
        for item in items:
            if item.get("type") == "dir" and not item["name"].startswith("_"):
                repo_items = await gh_list_folder(f"{user_folder}/{item['name']}")
                for fi in repo_items:
                    if fi.get("type") == "file":
                        total_size += fi.get("size", 0)
        db_dir   = get_user_db_dir(user["username"])
        db_size  = sum(f.stat().st_size for f in db_dir.glob("*.db"))
        dbs      = list_user_databases(user["username"])
        return {
            "files_used_mb":  round(total_size / (1024 * 1024), 2),
            "db_used_mb":     round(db_size / (1024 * 1024), 2),
            "total_used_mb":  round((total_size + db_size) / (1024 * 1024), 2),
            "limit_mb":       500,
            "percent_used":   round(((total_size + db_size) / (500 * 1024 * 1024)) * 100, 2),
            "databases":      len(dbs)
        }
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(500, str(e))