package db

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/duckdb/duckdb-go/v2" // DuckDB driver
)

// OpenReadOnlyDB opens a DuckDB connection in read-only mode
// Provides additional security layer for query-only operations
// Caller must close the connection when done
func OpenReadOnlyDB(dbPath string) (*sql.DB, error) {
	connStr := dbPath + "?access_mode=read_only"
	db, err := sql.Open("duckdb", connStr)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	if err = db.Ping(); err != nil {
		closeErr := db.Close()
		if closeErr != nil {
			return nil, fmt.Errorf("failed to ping database: %w (close error: %v)", err, closeErr)
		}
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	return db, nil
}

// WithReadDB opens a read-only DB connection, calls fn, and closes the connection.
// This is a convenience wrapper that ensures the connection is always closed.
func WithReadDB(dbPath string, fn func(*sql.DB) error) error {
	database, err := OpenReadOnlyDB(dbPath)
	if err != nil {
		return fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()
	return fn(database)
}

// WithWriteTx opens a writeable DB connection, begins a logged transaction, calls fn,
// and commits on success (or rollbacks on error). The connection is always closed.
// The fn callback receives both the *sql.DB (for pre-validation queries) and the
// *LoggedTx (for mutation operations).
func WithWriteTx(ctx context.Context, dbPath, toolName string, fn func(*sql.DB, *LoggedTx) error) error {
	database, err := OpenWriteableDB(dbPath)
	if err != nil {
		return fmt.Errorf("database connection failed: %w", err)
	}
	defer database.Close()

	tx, err := BeginLoggedTx(ctx, database, toolName)
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback() // no-op after commit

	if err := fn(database, tx); err != nil {
		return err
	}
	return tx.Commit()
}

// OpenWriteableDB opens a DuckDB connection in read-write mode
// Used for write operations (insert, update, delete)
// Caller must close the connection when done
func OpenWriteableDB(dbPath string) (*sql.DB, error) {
	connStr := dbPath + "?access_mode=read_write"
	db, err := sql.Open("duckdb", connStr)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	if err = db.Ping(); err != nil {
		closeErr := db.Close()
		if closeErr != nil {
			return nil, fmt.Errorf("failed to ping database: %w (close error: %v)", err, closeErr)
		}
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	return db, nil
}