"""Run the briefing API in the no-Docker safety sandbox.

The sandbox has no system PostgreSQL service. `pgserver` supplies a user-space
PostgreSQL binary, and this launcher keeps it alive for the lifetime of the
Uvicorn process. n8n is intentionally not started here; the workstation n8n
instance remains the scheduler.
"""

from __future__ import annotations

import os
import sys
from pathlib import Path

import psycopg
import uvicorn
from pgserver import PostgresServer


ROOT = Path(__file__).resolve().parents[1]
PGDATA = Path(os.environ.get("SANDBOX_PGDATA", ROOT / "runtime" / "pgdata"))
SANDBOX_MIGRATIONS = ROOT / "runtime" / "migrations"
DATABASE_NAME = "city_briefs_ops"


def ensure_database(server: PostgresServer) -> str:
    base_uri = server.get_uri()
    with psycopg.connect(base_uri, autocommit=True) as connection:
        exists = connection.execute(
            "SELECT 1 FROM pg_database WHERE datname = %s",
            (DATABASE_NAME,),
        ).fetchone()
        if not exists:
            connection.execute(f"CREATE DATABASE {DATABASE_NAME}")
    return server.get_uri(DATABASE_NAME)


def prepare_sandbox_migrations() -> None:
    """Remove the optional pgcrypto dependency from the sandbox copy only."""
    SANDBOX_MIGRATIONS.mkdir(parents=True, exist_ok=True)
    sql = (ROOT / "migrations" / "001_initial.sql").read_text()
    sql = sql.replace(
        "CREATE EXTENSION IF NOT EXISTS pgcrypto;",
        """CREATE OR REPLACE FUNCTION city_briefs_uuid()
RETURNS UUID AS $$
BEGIN
    RETURN md5(random()::text || clock_timestamp()::text)::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;""",
    )
    sql = sql.replace("gen_random_uuid()", "city_briefs_uuid()")
    (SANDBOX_MIGRATIONS / "001_initial.sql").write_text(sql)


def main() -> None:
    os.chdir(ROOT)
    sys.path.insert(0, str(ROOT))
    PGDATA.mkdir(parents=True, exist_ok=True)
    # Keep the database alive while Uvicorn runs. Stop the launcher to stop it.
    server = PostgresServer(PGDATA, cleanup_mode=None)
    os.environ["DATABASE_URL"] = ensure_database(server)
    os.environ.setdefault("BRIEF_OUTPUT_DIR", str(ROOT / "brief-output"))
    os.environ.setdefault("APP_HOST", "127.0.0.1")
    os.environ.setdefault("APP_PORT", "8002")
    prepare_sandbox_migrations()
    import app.db as app_db

    app_db.MIGRATIONS_DIR = SANDBOX_MIGRATIONS
    print("sandbox postgres ready; starting city briefing agent", flush=True)
    uvicorn.run(
        "app.main:app",
        host=os.environ["APP_HOST"],
        port=int(os.environ["APP_PORT"]),
        workers=1,
    )


if __name__ == "__main__":
    main()