from fastapi import FastAPI, HTTPException
from fastapi.staticfiles import StaticFiles
from fastapi.responses import FileResponse
from pydantic import BaseModel
import anthropic
import httpx
import os
import re

app = FastAPI()

TURSO_URL = os.environ["TURSO_URL"].replace("libsql://", "https://")
TURSO_TOKEN = os.environ["TURSO_AUTH_TOKEN"]
ANTHROPIC_KEY = os.environ["ANTHROPIC_API_KEY"]

class DescribeRequest(BaseModel):
    description: str
    database: str = "second-brain"

class ExecuteRequest(BaseModel):
    sql: str

def split_sql_statements(sql: str) -> list[str]:
    """Split SQL into individual statements, handling triggers correctly."""
    statements = []
    current = ""
    in_trigger = False

    for line in sql.splitlines():
        stripped = line.strip().upper()

        if stripped.startswith("CREATE TRIGGER"):
            in_trigger = True

        if in_trigger:
            current += line + "\n"
            if stripped == "END;" or stripped == "END":
                # Skip triggers — Turso doesn't support them
                current = ""
                in_trigger = False
            continue

        current += line + "\n"

        if stripped.endswith(";") and not in_trigger:
            stmt = current.strip()
            if stmt and stmt != ";":
                statements.append(stmt)
            current = ""

    # Catch any trailing statement without semicolon
    if current.strip():
        statements.append(current.strip())

    return statements

def turso_execute(sql: str):
    url = f"{TURSO_URL}/v2/pipeline"
    headers = {
        "Authorization": f"Bearer {TURSO_TOKEN}",
        "Content-Type": "application/json"
    }
    payload = {
        "requests": [
            {"type": "execute", "stmt": {"sql": sql}},
            {"type": "close"}
        ]
    }
    r = httpx.post(url, json=payload, headers=headers, timeout=10)
    r.raise_for_status()
    return r.json()

def turso_execute_multi(sql: str):
    """Execute multiple SQL statements, one at a time, skipping triggers."""
    statements = split_sql_statements(sql)
    results = []
    errors = []

    for stmt in statements:
        upper = stmt.upper().strip()
        # Skip triggers entirely
        if upper.startswith("CREATE TRIGGER"):
            continue
        try:
            result = turso_execute(stmt)
            results.append({"sql": stmt[:80], "status": "ok"})
        except Exception as e:
            errors.append({"sql": stmt[:80], "error": str(e)})

    return {"results": results, "errors": errors}

@app.post("/generate")
async def generate_schema(req: DescribeRequest):
    client = anthropic.Anthropic(api_key=ANTHROPIC_KEY)
    message = client.messages.create(
        model="claude-sonnet-4-5",
        max_tokens=1000,
        messages=[{
            "role": "user",
            "content": f"""You are a SQLite/Turso database schema expert.
            
The user wants to store: {req.description}

Generate a complete, production-ready CREATE TABLE statement with:
- Appropriate column names and types (TEXT, INTEGER, REAL, BLOB)
- Primary key using: id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(8))))
- created_at and updated_at timestamp columns
- Appropriate indexes as separate CREATE INDEX statements
- A comment explaining the table purpose

Do NOT include CREATE TRIGGER statements — they are not supported.
Return ONLY the SQL statements, nothing else. No markdown, no backticks, no explanation."""
        }]
    )
    sql = message.content[0].text.strip()
    return {"sql": sql}

@app.post("/execute")
async def execute_sql(req: ExecuteRequest):
    try:
        result = turso_execute_multi(req.sql)
        if result["errors"]:
            raise HTTPException(status_code=500, detail=str(result["errors"]))
        return {"success": True, "result": result}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@app.get("/tables")
async def list_tables():
    try:
        result = turso_execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
        rows = result["results"][0]["response"]["result"]["rows"]
        tables = [row[0]["value"] if isinstance(row[0], dict) else row[0] for row in rows]
        return {"tables": tables}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

app.mount("/", StaticFiles(directory="static", html=True), name="static")