from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig

# Create SQLSpec instance and configure database
db_manager = SQLSpec()
config = SqliteConfig(pool_config={"database": ":memory:"}) # Thread local pooling
db_manager.add_config(config)

# Execute queries with automatic result mapping
with db_manager.provide_session(config) as session:
    # Simple query
    result = session.execute("SELECT 'Hello, SQLSpec!' as message")
    print(result.get_first())  # {'message': 'Hello, SQLSpec!'}

    # Type-safe single row query
    row = session.select_one("SELECT 'Hello, SQLSpec!' as message")
    print(row)  # {'message': 'Hello, SQLSpec!'}

from sqlspec import sql

# Build a simple query
query = sql.select("id", "name", "email").from_("users").where("active = ?")
statement = query.to_statement()
print(statement.sql)  # SELECT id, name, email FROM users WHERE active = ?

# More complex example with joins
query = (
    sql.select("u.name", "COUNT(o.id) as order_count")
    .from_("users u")
    .left_join("orders o", "u.id = o.user_id")
    .where("u.created_at > ?")
    .group_by("u.name")
    .having("COUNT(o.id) > ?")
    .order_by("order_count", desc=True)
)

# Execute the built query with parameters
with db_manager.provide_session(config) as session:
    results = session.execute(query, "2024-01-01", 5)

from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
from pydantic import BaseModel

class User(BaseModel):
    id: int
    name: str
    email: str

db_manager = SQLSpec()
config = SqliteConfig(pool_config={"database": ":memory:"})
db_manager.add_config(config)

with db_manager.provide_session(config) as session:
    # Create and populate test data
    session.execute_script("""
        CREATE TABLE users (id INTEGER, name TEXT, email TEXT);
        INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
    """)
    # Map single result to typed model
    user = session.select_one("SELECT * FROM users WHERE id = ?", 1, schema_type=User)
    print(f"User: {user.name} ({user.email})")

    # Map multiple results
    users = session.select("SELECT * FROM users", schema_type=User)
    for user in users:
        print(f"User: {user.name}")

with db_manager.provide_session(config) as session:
    # Execute any SQL and get full result set
    result = session.execute("SELECT * FROM users")

    # Get single row (raises error if not found)
    user = session.select_one("SELECT * FROM users WHERE id = ?", 1)

    # Get single row or None (no error if not found)
    maybe_user = session.select_one_or_none("SELECT * FROM users WHERE id = ?", 999)

    # Execute with many parameter sets (bulk operations)
    session.execute_many(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        [("Bob", "bob@example.com"), ("Carol", "carol@example.com")]
    )

    # Execute multiple statements as a script
    session.execute_script("""
        CREATE TABLE IF NOT EXISTS logs (id INTEGER, message TEXT);
        INSERT INTO logs (message) VALUES ('System started');
    """)

from sqlspec import SQLSpec
from sqlspec.loader import SQLFileLoader
from sqlspec.adapters.sqlite import SqliteConfig

# Initialize with SQL file loader
db_manager = SQLSpec(loader=SQLFileLoader())
config = SqliteConfig(pool_config={"database": ":memory:"})
db_manager.add_config(config)

# Load SQL files from directory
db_manager.load_sql_files("./sql")

# SQL file: ./sql/users.sql
# -- name: get_user
# SELECT * FROM users WHERE id = ?
#
# -- name: create_user
# INSERT INTO users (name, email) VALUES (?, ?)

with db_manager.provide_session(config) as session:
    # Use named queries from files
    user = session.execute(db_manager.get_sql("get_user"), 1)
    session.execute(db_manager.get_sql("create_user"), "Alice", "alice@example.com")

# Initialize migration directory
sqlspec --config myapp.config init

# Generate new migration file
sqlspec --config myapp.config create-migration -m "Add user table"

# Apply all pending migrations
sqlspec --config myapp.config upgrade

# Show current migration status
sqlspec --config myapp.config show-current-revision

# Using Litestar CLI integration
litestar database create-migration -m "Add user table"
litestar database upgrade
litestar database show-current-revision

# Bash - add to ~/.bashrc
eval "$(_SQLSPEC_COMPLETE=bash_source sqlspec)"

# Zsh - add to ~/.zshrc
eval "$(_SQLSPEC_COMPLETE=zsh_source sqlspec)"

# Fish - add to ~/.config/fish/completions/sqlspec.fish
eval (env _SQLSPEC_COMPLETE=fish_source sqlspec)

sqlspec <TAB>         # Shows: create-migration, downgrade, init, ...
sqlspec upgrade --<TAB>  # Shows: --bind-key, --help, --no-prompt, ...

# /// script
# dependencies = [
#   "sqlspec[aiosqlite]",
#   "litestar[standard]",
# ]
# ///

from litestar import Litestar, get
from sqlspec import SQLSpec
from sqlspec.adapters.aiosqlite import AiosqliteConfig, AiosqliteDriver
from sqlspec.extensions.litestar import SQLSpecPlugin

@get("/")
async def simple_sqlite(db_session: AiosqliteDriver) -> dict[str, str]:
    return await db_session.select_one("SELECT 'Hello, world!' AS greeting")


sqlspec = SQLSpec()
sqlspec.add_config(AiosqliteConfig(pool_config={"database": ":memory:"}))
app = Litestar(route_handlers=[simple_sqlite], plugins=[SQLSpecPlugin(sqlspec)])

# SQLite
SqliteConfig(pool_config={"database": "/path/to/database.db"})
AiosqliteConfig(pool_config={"database": "/path/to/database.db"})  # Async
AdbcConfig(connection_config={"uri": "sqlite:///path/to/database.db"})  # ADBC

# PostgreSQL (multiple drivers available)
PsycopgSyncConfig(pool_config={"host": "localhost", "database": "mydb", "user": "user", "password": "pass"})
PsycopgAsyncConfig(pool_config={"host": "localhost", "database": "mydb", "user": "user", "password": "pass"})  # Async
AsyncpgConfig(pool_config={"host": "localhost", "database": "mydb", "user": "user", "password": "pass"})
PsqlpyConfig(pool_config={"dsn": "postgresql://user:pass@localhost/mydb"})
AdbcConfig(connection_config={"uri": "postgresql://user:pass@localhost/mydb"})  # ADBC

# DuckDB
DuckDBConfig(pool_config={"database": ":memory:"})  # or file path
AdbcConfig(connection_config={"uri": "duckdb:///path/to/database.duckdb"})  # ADBC

# MySQL
AsyncmyConfig(pool_config={"host": "localhost", "database": "mydb", "user": "user", "password": "pass"})  # Async

# Oracle
OracleSyncConfig(pool_config={"host": "localhost", "service_name": "XEPDB1", "user": "user", "password": "pass"})
OracleAsyncConfig(pool_config={"host": "localhost", "service_name": "XEPDB1", "user": "user", "password": "pass"})  # Async

# BigQuery
BigQueryConfig(pool_config={"project": "my-project", "dataset": "my_dataset"})
AdbcConfig(connection_config={"driver_name": "adbc_driver_bigquery", "project_id": "my-project", "dataset_id": "my_dataset"})  # ADBC