Web Database Go Samples

// Web Go SQLite Connection Examples
// Database connectivity and management with SQLite

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/mattn/go-sqlite3" // SQLite driver
)

// 1. Basic Connection

// OpenDatabase opens SQLite database connection
func OpenDatabase(dbPath string) (*sql.DB, error) {
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, fmt.Errorf("error opening database: %v", err)
	}

	// Test connection
	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("error connecting to database: %v", err)
	}

	return db, nil
}

// OpenDatabaseWithTimeout opens database with timeout
func OpenDatabaseWithTimeout(dbPath string, timeout time.Duration) (*sql.DB, error) {
	db, err := sql.Open("sqlite3", dbPath+"?_timeout="+timeout.String())
	if err != nil {
		return nil, fmt.Errorf("error opening database: %v", err)
	}

	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("error connecting to database: %v", err)
	}

	return db, nil
}

// OpenInMemoryDatabase opens in-memory SQLite database
func OpenInMemoryDatabase() (*sql.DB, error) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		return nil, fmt.Errorf("error opening in-memory database: %v", err)
	}

	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("error connecting to database: %v", err)
	}

	return db, nil
}

// CloseDatabase closes database connection properly
func CloseDatabase(db *sql.DB) error {
	return db.Close()
}

// 2. Connection Management

// ConfigureConnectionPool configures connection pool settings
func ConfigureConnectionPool(db *sql.DB, maxOpen, maxIdle int, maxLifetime time.Duration) {
	db.SetMaxOpenConns(maxOpen)
	db.SetMaxIdleConns(maxIdle)
	db.SetConnMaxLifetime(maxLifetime)
}

// GetConnectionStats returns connection pool statistics
func GetConnectionStats(db *sql.DB) map[string]interface{} {
	stats := db.Stats()
	return map[string]interface{}{
		"max_open_connections": stats.MaxOpenConnections,
		"open_connections":     stats.OpenConnections,
		"in_use":               stats.InUse,
		"idle":                 stats.Idle,
		"wait_count":           stats.WaitCount,
		"wait_duration":        stats.WaitDuration.String(),
		"max_idle_closed":      stats.MaxIdleClosed,
		"max_lifetime_closed":  stats.MaxLifetimeClosed,
	}
}

// TestConnection tests database connection
func TestConnection(db *sql.DB) error {
	return db.Ping()
}

// 3. Table Creation

// CreateUsersTable creates users table
func CreateUsersTable(db *sql.DB) error {
	query := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT NOT NULL UNIQUE,
		email TEXT NOT NULL UNIQUE,
		password TEXT NOT NULL,
		full_name TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	)
	`

	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error creating users table: %v", err)
	}

	return nil
}

// CreateProductsTable creates products table
func CreateProductsTable(db *sql.DB) error {
	query := `
	CREATE TABLE IF NOT EXISTS products (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		description TEXT,
		price REAL NOT NULL,
		quantity INTEGER DEFAULT 0,
		category TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	)
	`

	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error creating products table: %v", err)
	}

	return nil
}

// CreateOrdersTable creates orders table with foreign key
func CreateOrdersTable(db *sql.DB) error {
	query := `
	CREATE TABLE IF NOT EXISTS orders (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		total_amount REAL NOT NULL,
		status TEXT DEFAULT 'pending',
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (user_id) REFERENCES users(id)
	)
	`

	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error creating orders table: %v", err)
	}

	return nil
}

// CreateOrderItemsTable creates order_items table with many-to-many relationship
func CreateOrderItemsTable(db *sql.DB) error {
	query := `
	CREATE TABLE IF NOT EXISTS order_items (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		order_id INTEGER NOT NULL,
		product_id INTEGER NOT NULL,
		quantity INTEGER NOT NULL,
		price REAL NOT NULL,
		FOREIGN KEY (order_id) REFERENCES orders(id),
		FOREIGN KEY (product_id) REFERENCES products(id)
	)
	`

	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error creating order_items table: %v", err)
	}

	return nil
}

// 4. Index Management

// CreateIndex creates an index on table
func CreateIndex(db *sql.DB, indexName, tableName, columns string) error {
	query := fmt.Sprintf("CREATE INDEX IF NOT EXISTS %s ON %s(%s)",
		indexName, tableName, columns)

	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error creating index: %v", err)
	}

	return nil
}

// CreateUniqueIndex creates unique index
func CreateUniqueIndex(db *sql.DB, indexName, tableName, columns string) error {
	query := fmt.Sprintf("CREATE UNIQUE INDEX IF NOT EXISTS %s ON %s(%s)",
		indexName, tableName, columns)

	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error creating unique index: %v", err)
	}

	return nil
}

// DropIndex removes an index
func DropIndex(db *sql.DB, indexName string) error {
	query := fmt.Sprintf("DROP INDEX IF EXISTS %s", indexName)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error dropping index: %v", err)
	}

	return nil
}

// ListIndexes lists all indexes
func ListIndexes(db *sql.DB) ([]string, error) {
	query := "SELECT name FROM sqlite_master WHERE type = 'index'"
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var indexes []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			continue
		}
		indexes = append(indexes, name)
	}

	return indexes, nil
}

// 5. Schema Management

// GetTableSchema retrieves table schema
func GetTableSchema(db *sql.DB, tableName string) ([]map[string]interface{}, error) {
	query := fmt.Sprintf("PRAGMA table_info(%s)", tableName)
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var schema []map[string]interface{}
	for rows.Next() {
		var cid int
		var name, datatype string
		var notnull, pk int
		var dfltValue interface{}

		err := rows.Scan(&cid, &name, &datatype, &notnull, &dfltValue, &pk)
		if err != nil {
			continue
		}

		schema = append(schema, map[string]interface{}{
			"cid":        cid,
			"name":       name,
			"datatype":   datatype,
			"notnull":    notnull == 1,
			"dflt_value": dfltValue,
			"pk":         pk == 1,
		})
	}

	return schema, nil
}

// ListTables lists all tables in database
func ListTables(db *sql.DB) ([]string, error) {
	query := "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			continue
		}
		tables = append(tables, name)
	}

	return tables, nil
}

// TableExists checks if table exists
func TableExists(db *sql.DB, tableName string) bool {
	query := "SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?"
	var name string
	err := db.QueryRow(query, tableName).Scan(&name)
	return err == nil
}

// 6. Database Information

// GetDatabaseVersion returns SQLite version
func GetDatabaseVersion(db *sql.DB) (string, error) {
	var version string
	err := db.QueryRow("SELECT sqlite_version()").Scan(&version)
	if err != nil {
		return "", err
	}
	return version, nil
}

// GetDatabaseSize returns database file size
func GetDatabaseSize(db *sql.DB) (int64, error) {
	var size int64
	var pageCount, pageSize int

	err := db.QueryRow("PRAGMA page_count").Scan(&pageCount)
	if err != nil {
		return 0, err
	}

	err = db.QueryRow("PRAGMA page_size").Scan(&pageSize)
	if err != nil {
		return 0, err
	}

	size = int64(pageCount) * int64(pageSize)
	return size, nil
}

// GetDatabaseInfo returns database information
func GetDatabaseInfo(db *sql.DB) (map[string]interface{}, error) {
	info := make(map[string]interface{})

	// Get version
	version, err := GetDatabaseVersion(db)
	if err == nil {
		info["version"] = version
	}

	// Get size
	size, err := GetDatabaseSize(db)
	if err == nil {
		info["size_bytes"] = size
	}

	// Get tables
	tables, err := ListTables(db)
	if err == nil {
		info["tables"] = tables
		info["table_count"] = len(tables)
	}

	// Get connection stats
	stats := GetConnectionStats(db)
	info["connection_stats"] = stats

	return info, nil
}

// 7. Backup and Restore

// BackupDatabase creates database backup
func BackupDatabase(db *sql.DB, backupPath string) error {
	query := fmt.Sprintf("VACUUM INTO '%s'", backupPath)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error creating backup: %v", err)
	}
	return nil
}

// OptimizeDatabase optimizes database
func OptimizeDatabase(db *sql.DB) error {
	_, err := db.Exec("VACUUM")
	if err != nil {
		return fmt.Errorf("error vacuuming database: %v", err)
	}

	_, err = db.Exec("ANALYZE")
	if err != nil {
		return fmt.Errorf("error analyzing database: %v", err)
	}

	return nil
}

// 8. Database Maintenance

// EnableForeignKeys enables foreign key constraints
func EnableForeignKeys(db *sql.DB) error {
	_, err := db.Exec("PRAGMA foreign_keys = ON")
	if err != nil {
		return fmt.Errorf("error enabling foreign keys: %v", err)
	}
	return nil
}

// EnableWALMode enables Write-Ahead Logging mode
func EnableWALMode(db *sql.DB) error {
	_, err := db.Exec("PRAGMA journal_mode = WAL")
	if err != nil {
		return fmt.Errorf("error enabling WAL mode: %v", err)
	}
	return nil
}

// SetSynchronousMode sets synchronous mode
func SetSynchronousMode(db *sql.DB, mode string) error {
	query := fmt.Sprintf("PRAGMA synchronous = %s", mode)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error setting synchronous mode: %v", err)
	}
	return nil
}

// SetCacheSize sets cache size
func SetCacheSize(db *sql.DB, size int) error {
	query := fmt.Sprintf("PRAGMA cache_size = -%d", size)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error setting cache size: %v", err)
	}
	return nil
}

// 9. Database Operations

// DropTable drops a table
func DropTable(db *sql.DB, tableName string) error {
	query := fmt.Sprintf("DROP TABLE IF EXISTS %s", tableName)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error dropping table: %v", err)
	}
	return nil
}

// TruncateTable removes all rows from table
func TruncateTable(db *sql.DB, tableName string) error {
	query := fmt.Sprintf("DELETE FROM %s", tableName)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error truncating table: %v", err)
	}

	// Reset autoincrement
	_, err = db.Exec(fmt.Sprintf("DELETE FROM sqlite_sequence WHERE name='%s'", tableName))
	if err != nil {
		return fmt.Errorf("error resetting sequence: %v", err)
	}

	return nil
}

// RenameTable renames a table
func RenameTable(db *sql.DB, oldName, newName string) error {
	query := fmt.Sprintf("ALTER TABLE %s RENAME TO %s", oldName, newName)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error renaming table: %v", err)
	}
	return nil
}

// AddColumn adds column to table
func AddColumn(db *sql.DB, tableName, columnName, columnDef string) error {
	query := fmt.Sprintf("ALTER TABLE %s ADD COLUMN %s %s", tableName, columnName, columnDef)
	_, err := db.Exec(query)
	if err != nil {
		return fmt.Errorf("error adding column: %v", err)
	}
	return nil
}

// Usage Examples
func main() {
	fmt.Println("=== Web Go SQLite Connection Examples ===
")

	// 1. Open database
	fmt.Println("--- 1. Open Database ---")
	db, err := OpenDatabase("test.db")
	if err != nil {
		log.Fatal(err)
	}
	defer CloseDatabase(db)
	fmt.Println("Database opened successfully")

	// 2. Configure connection pool
	fmt.Println("
--- 2. Configure Connection Pool ---")
	ConfigureConnectionPool(db, 10, 5, time.Hour)
	stats := GetConnectionStats(db)
	fmt.Printf("Connection stats: %+v
", stats)

	// 3. Create tables
	fmt.Println("
--- 3. Create Tables ---")
	if err := CreateUsersTable(db); err != nil {
		log.Fatal(err)
	}
	fmt.Println("Users table created")

	if err := CreateProductsTable(db); err != nil {
		log.Fatal(err)
	}
	fmt.Println("Products table created")

	// 4. Create indexes
	fmt.Println("
--- 4. Create Indexes ---")
	if err := CreateIndex(db, "idx_users_email", "users", "email"); err != nil {
		log.Fatal(err)
	}
	fmt.Println("Index created")

	// 5. List tables
	fmt.Println("
--- 5. List Tables ---")
	tables, err := ListTables(db)
	if err == nil {
		fmt.Printf("Tables: %v
", tables)
	}

	// 6. Get table schema
	fmt.Println("
--- 6. Table Schema ---")
	schema, err := GetTableSchema(db, "users")
	if err == nil {
		for _, col := range schema {
			fmt.Printf("Column: %+v
", col)
		}
	}

	// 7. Database info
	fmt.Println("
--- 7. Database Info ---")
	info, err := GetDatabaseInfo(db)
	if err == nil {
		fmt.Printf("Database info: %+v
", info)
	}

	// 8. Enable foreign keys
	fmt.Println("
--- 8. Enable Foreign Keys ---")
	if err := EnableForeignKeys(db); err != nil {
		log.Fatal(err)
	}
	fmt.Println("Foreign keys enabled")

	// 9. Optimize database
	fmt.Println("
--- 9. Optimize Database ---")
	if err := OptimizeDatabase(db); err != nil {
		log.Printf("Warning: %v", err)
	} else {
		fmt.Println("Database optimized")
	}

	fmt.Println("
=== All SQLite Connection Examples Completed ===")
}

// Web Go SQL Query Execution Examples
// CRUD operations with parameterized queries

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

// 1. INSERT Operations

// InsertUser inserts a new user
func InsertUser(db *sql.DB, username, email, password, fullName string) (int64, error) {
	query := `
	INSERT INTO users (username, email, password, full_name)
	VALUES (?, ?, ?, ?)
	`

	result, err := db.Exec(query, username, email, password, fullName)
	if err != nil {
		return 0, fmt.Errorf("error inserting user: %v", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("error getting last insert id: %v", err)
	}

	return id, nil
}

// InsertUserWithTimestamp inserts user with custom timestamp
func InsertUserWithTimestamp(db *sql.DB, username, email, password, fullName string, createdAt time.Time) (int64, error) {
	query := `
	INSERT INTO users (username, email, password, full_name, created_at)
	VALUES (?, ?, ?, ?, ?)
	`

	result, err := db.Exec(query, username, email, password, fullName, createdAt)
	if err != nil {
		return 0, fmt.Errorf("error inserting user: %v", err)
	}

	return result.LastInsertId()
}

// BatchInsertUsers inserts multiple users efficiently
func BatchInsertUsers(db *sql.DB, users []struct{ Username, Email, Password, FullName string }) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("error beginning transaction: %v", err)
	}
	defer tx.Rollback()

	stmt, err := tx.Prepare(`
		INSERT INTO users (username, email, password, full_name)
		VALUES (?, ?, ?, ?)
	`)
	if err != nil {
		return fmt.Errorf("error preparing statement: %v", err)
	}
	defer stmt.Close()

	for _, user := range users {
		_, err := stmt.Exec(user.Username, user.Email, user.Password, user.FullName)
		if err != nil {
			return fmt.Errorf("error inserting user: %v", err)
		}
	}

	return tx.Commit()
}

// InsertOrUpdateUser inserts or updates user (UPSERT)
func InsertOrUpdateUser(db *sql.DB, username, email, fullName string) error {
	query := `
	INSERT INTO users (username, email, full_name)
	VALUES (?, ?, ?)
	ON CONFLICT(username) DO UPDATE SET
		email = excluded.email,
		full_name = excluded.full_name,
		updated_at = CURRENT_TIMESTAMP
	`

	_, err := db.Exec(query, username, email, fullName)
	if err != nil {
		return fmt.Errorf("error upserting user: %v", err)
	}

	return nil
}

// 2. SELECT Operations

// GetUserByID retrieves user by ID
func GetUserByID(db *sql.DB, userID int) (map[string]interface{}, error) {
	query := `
	SELECT id, username, email, full_name, created_at, updated_at
	FROM users
	WHERE id = ?
	`

	var id int
	var username, email, fullName, createdAt, updatedAt string

	err := db.QueryRow(query, userID).Scan(
		&id, &username, &email, &fullName, &createdAt, &updatedAt,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("user not found")
		}
		return nil, fmt.Errorf("error querying user: %v", err)
	}

	return map[string]interface{}{
		"id":         id,
		"username":   username,
		"email":      email,
		"full_name":  fullName,
		"created_at": createdAt,
		"updated_at": updatedAt,
	}, nil
}

// GetUserByUsername retrieves user by username
func GetUserByUsername(db *sql.DB, username string) (map[string]interface{}, error) {
	query := `
	SELECT id, username, email, full_name, created_at
	FROM users
	WHERE username = ?
	`

	var id int
	var user, email, fullName, createdAt string

	err := db.QueryRow(query, username).Scan(
		&id, &user, &email, &fullName, &createdAt,
	)
	if err != nil {
		return nil, err
	}

	return map[string]interface{}{
		"id":         id,
		"username":   user,
		"email":      email,
		"full_name":  fullName,
		"created_at": createdAt,
	}, nil
}

// GetAllUsers retrieves all users
func GetAllUsers(db *sql.DB) ([]map[string]interface{}, error) {
	query := `
	SELECT id, username, email, full_name, created_at
	FROM users
	ORDER BY created_at DESC
	`

	rows, err := db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("error querying users: %v", err)
	}
	defer rows.Close()

	var users []map[string]interface{}
	for rows.Next() {
		var id int
		var username, email, fullName, createdAt string

		err := rows.Scan(&id, &username, &email, &fullName, &createdAt)
		if err != nil {
			continue
		}

		users = append(users, map[string]interface{}{
			"id":         id,
			"username":   username,
			"email":      email,
			"full_name":  fullName,
			"created_at": createdAt,
		})
	}

	return users, nil
}

// GetUsersWithPagination retrieves users with pagination
func GetUsersWithPagination(db *sql.DB, limit, offset int) ([]map[string]interface{}, int, error) {
	// Get total count
	var total int
	err := db.QueryRow("SELECT COUNT(*) FROM users").Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	// Get paginated results
	query := `
	SELECT id, username, email, full_name
	FROM users
	ORDER BY created_at DESC
	LIMIT ? OFFSET ?
	`

	rows, err := db.Query(query, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var users []map[string]interface{}
	for rows.Next() {
		var id int
		var username, email, fullName string

		err := rows.Scan(&id, &username, &email, &fullName)
		if err != nil {
			continue
		}

		users = append(users, map[string]interface{}{
			"id":        id,
			"username":  username,
			"email":     email,
			"full_name": fullName,
		})
	}

	return users, total, nil
}

// SearchUsersByUsername searches users by username pattern
func SearchUsersByUsername(db *sql.DB, pattern string) ([]map[string]interface{}, error) {
	query := `
	SELECT id, username, email, full_name
	FROM users
	WHERE username LIKE ?
	ORDER BY username
	`

	rows, err := db.Query(query, "%"+pattern+"%")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []map[string]interface{}
	for rows.Next() {
		var id int
		var username, email, fullName string

		err := rows.Scan(&id, &username, &email, &fullName)
		if err != nil {
			continue
		}

		users = append(users, map[string]interface{}{
			"id":        id,
			"username":  username,
			"email":     email,
			"full_name": fullName,
		})
	}

	return users, nil
}

// 3. UPDATE Operations

// UpdateUser updates user information
func UpdateUser(db *sql.DB, userID int, email, fullName string) error {
	query := `
	UPDATE users
	SET email = ?, full_name = ?, updated_at = CURRENT_TIMESTAMP
	WHERE id = ?
	`

	result, err := db.Exec(query, email, fullName, userID)
	if err != nil {
		return fmt.Errorf("error updating user: %v", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return err
	}

	if rowsAffected == 0 {
		return fmt.Errorf("no user found with id %d", userID)
	}

	return nil
}

// UpdateUserPassword updates user password
func UpdateUserPassword(db *sql.DB, userID int, newPassword string) error {
	query := `
	UPDATE users
	SET password = ?, updated_at = CURRENT_TIMESTAMP
	WHERE id = ?
	`

	result, err := db.Exec(query, newPassword, userID)
	if err != nil {
		return fmt.Errorf("error updating password: %v", err)
	}

	rowsAffected, _ := result.RowsAffected()
	if rowsAffected == 0 {
		return fmt.Errorf("user not found")
	}

	return nil
}

// IncrementFieldValue increments a numeric field
func IncrementFieldValue(db *sql.DB, table, column string, id int) error {
	query := fmt.Sprintf(`
	UPDATE %s
	SET %s = %s + 1
	WHERE id = ?
	`, table, column, column)

	result, err := db.Exec(query, id)
	if err != nil {
		return err
	}

	rowsAffected, _ := result.RowsAffected()
	if rowsAffected == 0 {
		return fmt.Errorf("record not found")
	}

	return nil
}

// 4. DELETE Operations

// DeleteUser deletes user by ID
func DeleteUser(db *sql.DB, userID int) error {
	query := "DELETE FROM users WHERE id = ?"

	result, err := db.Exec(query, userID)
	if err != nil {
		return fmt.Errorf("error deleting user: %v", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return err
	}

	if rowsAffected == 0 {
		return fmt.Errorf("no user found with id %d", userID)
	}

	return nil
}

// DeleteUsersByCondition deletes users matching condition
func DeleteUsersByCondition(db *sql.DB, condition string, args ...interface{}) (int64, error) {
	query := "DELETE FROM users WHERE " + condition

	result, err := db.Exec(query, args...)
	if err != nil {
		return 0, fmt.Errorf("error deleting users: %v", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}

	return rowsAffected, nil
}

// DeleteAllUsers deletes all users (use with caution)
func DeleteAllUsers(db *sql.DB) error {
	_, err := db.Exec("DELETE FROM users")
	if err != nil {
		return fmt.Errorf("error deleting all users: %v", err)
	}

	// Reset autoincrement
	_, err = db.Exec("DELETE FROM sqlite_sequence WHERE name='users'")
	return err
}

// 5. Complex Queries

// GetUsersWithOrders retrieves users with their order count
func GetUsersWithOrders(db *sql.DB) ([]map[string]interface{}, error) {
	query := `
	SELECT
		u.id,
		u.username,
		u.email,
		COUNT(o.id) as order_count,
		COALESCE(SUM(o.total_amount), 0) as total_spent
	FROM users u
	LEFT JOIN orders o ON u.id = o.user_id
	GROUP BY u.id
	ORDER BY total_spent DESC
	`

	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var results []map[string]interface{}
	for rows.Next() {
		var id, orderCount int
		var username, email string
		var totalSpent float64

		err := rows.Scan(&id, &username, &email, &orderCount, &totalSpent)
		if err != nil {
			continue
		}

		results = append(results, map[string]interface{}{
			"id":           id,
			"username":     username,
			"email":        email,
			"order_count":  orderCount,
			"total_spent":  totalSpent,
		})
	}

	return results, nil
}

// GetTopCustomers retrieves top customers by spending
func GetTopCustomers(db *sql.DB, limit int) ([]map[string]interface{}, error) {
	query := `
	SELECT
		u.id,
		u.username,
		u.email,
		COUNT(o.id) as order_count,
		COALESCE(SUM(o.total_amount), 0) as total_spent
	FROM users u
	INNER JOIN orders o ON u.id = o.user_id
	GROUP BY u.id
	ORDER BY total_spent DESC
	LIMIT ?
	`

	rows, err := db.Query(query, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var customers []map[string]interface{}
	for rows.Next() {
		var id, orderCount int
		var username, email string
		var totalSpent float64

		err := rows.Scan(&id, &username, &email, &orderCount, &totalSpent)
		if err != nil {
			continue
		}

		customers = append(customers, map[string]interface{}{
			"id":          id,
			"username":    username,
			"email":       email,
			"order_count": orderCount,
			"total_spent": totalSpent,
		})
	}

	return customers, nil
}

// 6. Aggregate Functions

// GetUserStatistics returns user statistics
func GetUserStatistics(db *sql.DB) (map[string]interface{}, error) {
	stats := make(map[string]interface{})

	// Total users
	var totalUsers int
	db.QueryRow("SELECT COUNT(*) FROM users").Scan(&totalUsers)
	stats["total_users"] = totalUsers

	// Users created today
	var usersToday int
	db.QueryRow("SELECT COUNT(*) FROM users WHERE DATE(created_at) = DATE('now')").Scan(&usersToday)
	stats["users_today"] = usersToday

	// Users created this week
	var usersWeek int
	db.QueryRow("SELECT COUNT(*) FROM users WHERE created_at >= DATE('now', '-7 days')").Scan(&usersWeek)
	stats["users_this_week"] = usersWeek

	// Users created this month
	var usersMonth int
	db.QueryRow("SELECT COUNT(*) FROM users WHERE created_at >= DATE('now', 'start of month')").Scan(&usersMonth)
	stats["users_this_month"] = usersMonth

	return stats, nil
}

// GetColumnStatistics returns statistics for numeric column
func GetColumnStatistics(db *sql.DB, table, column string) (map[string]interface{}, error) {
	query := fmt.Sprintf(`
	SELECT
		COUNT(*) as count,
		COALESCE(AVG(%s), 0) as average,
		COALESCE(MIN(%s), 0) as minimum,
		COALESCE(MAX(%s), 0) as maximum,
		COALESCE(SUM(%s), 0) as total
	FROM %s
	`, column, column, column, column, table)

	var count int
	var average, minimum, maximum, total float64

	err := db.QueryRow(query).Scan(&count, &average, &minimum, &maximum, &total)
	if err != nil {
		return nil, err
	}

	return map[string]interface{}{
		"count":   count,
		"average": average,
		"minimum": minimum,
		"maximum": maximum,
		"total":   total,
	}, nil
}

// 7. Prepared Statements

// CreatePreparedStatement creates and returns prepared statement
func CreatePreparedStatement(db *sql.DB, query string) (*sql.Stmt, error) {
	return db.Prepare(query)
}

// ExecutePreparedStatement executes prepared statement
func ExecutePreparedStatement(stmt *sql.Stmt, args ...interface{}) (sql.Result, error) {
	return stmt.Exec(args...)
}

// QueryPreparedStatement queries with prepared statement
func QueryPreparedStatement(stmt *sql.Stmt, args ...interface{}) (*sql.Rows, error) {
	return stmt.Query(args...)
}

// Usage Examples
func main() {
	fmt.Println("=== Web Go SQL Query Execution Examples ===
")

	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Create table
	_, err = db.Exec(`
	CREATE TABLE users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT NOT NULL UNIQUE,
		email TEXT NOT NULL UNIQUE,
		password TEXT NOT NULL,
		full_name TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	)
	`)
	if err != nil {
		log.Fatal(err)
	}

	// 1. INSERT
	fmt.Println("--- 1. Insert Users ---")
	id, err := InsertUser(db, "john_doe", "[email protected]", "hashed_password", "John Doe")
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Inserted user with ID: %d
", id)

	InsertUser(db, "jane_smith", "[email protected]", "hashed_password", "Jane Smith")
	InsertUser(db, "bob_wilson", "[email protected]", "hashed_password", "Bob Wilson")

	// 2. SELECT
	fmt.Println("
--- 2. Query Users ---")
	user, err := GetUserByID(db, 1)
	if err == nil {
		fmt.Printf("User: %+v
", user)
	}

	users, err := GetAllUsers(db)
	if err == nil {
		fmt.Printf("Total users: %d
", len(users))
	}

	// 3. SEARCH
	fmt.Println("
--- 3. Search Users ---")
	results, err := SearchUsersByUsername(db, "john")
	if err == nil {
		fmt.Printf("Found %d users
", len(results))
	}

	// 4. UPDATE
	fmt.Println("
--- 4. Update User ---")
	err = UpdateUser(db, 1, "[email protected]", "John Doe Jr.")
	if err == nil {
		fmt.Println("User updated successfully")
	}

	// 5. STATISTICS
	fmt.Println("
--- 5. Statistics ---")
	stats, err := GetUserStatistics(db)
	if err == nil {
		fmt.Printf("Statistics: %+v
", stats)
	}

	// 6. DELETE
	fmt.Println("
--- 6. Delete User ---")
	err = DeleteUser(db, 2)
	if err == nil {
		fmt.Println("User deleted successfully")
	}

	// 7. BATCH
	fmt.Println("
--- 7. Batch Insert ---")
	newUsers := []struct{ Username, Email, Password, FullName string }{
		{"user1", "[email protected]", "pass", "User 1"},
		{"user2", "[email protected]", "pass", "User 2"},
		{"user3", "[email protected]", "pass", "User 3"},
	}
	err = BatchInsertUsers(db, newUsers)
	if err == nil {
		fmt.Println("Batch insert successful")
	}

	fmt.Println("
=== All SQL Query Execution Examples Completed ===")
}

// Web Go Transaction Handling Examples
// ACID transactions and proper error handling

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

// 1. Basic Transaction

// ExecuteInTransaction executes function within transaction
func ExecuteInTransaction(db *sql.DB, fn func(*sql.Tx) error) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("error beginning transaction: %v", err)
	}

	defer func() {
		if r := recover(); r != nil {
			tx.Rollback()
			panic(r) // re-panic after rollback
		}
	}()

	if err := fn(tx); err != nil {
		if rbErr := tx.Rollback(); rbErr != nil {
			return fmt.Errorf("error: %v, rollback error: %v", err, rbErr)
		}
		return err
	}

	return tx.Commit()
}

// TransferFunds transfers money between accounts with transaction
func TransferFunds(db *sql.DB, fromAccount, toAccount int, amount float64) error {
	return ExecuteInTransaction(db, func(tx *sql.Tx) error {
		// Check sender balance
		var balance float64
		err := tx.QueryRow("SELECT balance FROM accounts WHERE id = ?", fromAccount).
			Scan(&balance)
		if err != nil {
			return fmt.Errorf("sender account not found")
		}

		if balance < amount {
			return fmt.Errorf("insufficient funds")
		}

		// Deduct from sender
		_, err = tx.Exec(`
			UPDATE accounts
			SET balance = balance - ?
			WHERE id = ?
		`, amount, fromAccount)
		if err != nil {
			return fmt.Errorf("error deducting from sender: %v", err)
		}

		// Add to receiver
		_, err = tx.Exec(`
			UPDATE accounts
			SET balance = balance + ?
			WHERE id = ?
		`, amount, toAccount)
		if err != nil {
			return fmt.Errorf("error adding to receiver: %v", err)
		}

		// Record transaction
		_, err = tx.Exec(`
			INSERT INTO transactions (from_account, to_account, amount)
			VALUES (?, ?, ?)
		`, fromAccount, toAccount, amount)
		if err != nil {
			return fmt.Errorf("error recording transaction: %v", err)
		}

		return nil
	})
}

// 2. Nested Transactions (Savepoints)

// ExecuteWithSavepoint executes operation with savepoint
func ExecuteWithSavepoint(tx *sql.Tx, savepointName string, fn func(*sql.Tx) error) error {
	_, err := tx.Exec(fmt.Sprintf("SAVEPOINT %s", savepointName))
	if err != nil {
		return fmt.Errorf("error creating savepoint: %v", err)
	}

	if err := fn(tx); err != nil {
		_, rbErr := tx.Exec(fmt.Sprintf("ROLLBACK TO SAVEPOINT %s", savepointName))
		if rbErr != nil {
			return fmt.Errorf("error: %v, rollback error: %v", err, rbErr)
		}
		return err
	}

	_, err = tx.Exec(fmt.Sprintf("RELEASE SAVEPOINT %s", savepointName))
	return err
}

// BatchUpdateWithSavepoints updates multiple records with savepoints
func BatchUpdateWithSavepoints(db *sql.DB, updates []struct{ ID int; Value string }) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	for i, update := range updates {
		savepoint := fmt.Sprintf("sp%d", i)
		err := ExecuteWithSavepoint(tx, savepoint, func(tx *sql.Tx) error {
			_, err := tx.Exec(`
				UPDATE products
				SET description = ?
				WHERE id = ?
			`, update.Value, update.ID)
			return err
		})
		if err != nil {
			log.Printf("Warning: update %d failed: %v (continuing)
", i, err)
		}
	}

	return tx.Commit()
}

// 3. Transaction Isolation Levels

// SetIsolationLevel sets transaction isolation level
func SetIsolationLevel(db *sql.DB, level string) error {
	// SQLite has limited isolation level support
	// By default, it uses SERIALIZABLE
	query := fmt.Sprintf("PRAGMA read_uncommitted = %t", level == "READ UNCOMMITTED")
	_, err := db.Exec(query)
	return err
}

// BeginReadOnlyTransaction begins read-only transaction
func BeginReadOnlyTransaction(db *sql.DB) (*sql.Tx, error) {
	return db.BeginTx(&sql.TxOptions{
		Isolation: sql.LevelSerializable,
		ReadOnly:  true,
	})
}

// 4. Transaction Patterns

// CreateOrderWithItems creates order with items in transaction
func CreateOrderWithItems(db *sql.DB, userID int, items []struct{ ProductID, Quantity int; Price float64 }) (int64, error) {
	var orderID int64

	err := ExecuteInTransaction(db, func(tx *sql.Tx) error {
		// Create order
		result, err := tx.Exec(`
			INSERT INTO orders (user_id, total_amount, status)
			VALUES (?, 0, 'pending')
		`, userID)
		if err != nil {
			return fmt.Errorf("error creating order: %v", err)
		}

		orderID, err = result.LastInsertId()
		if err != nil {
			return err
		}

		// Add items and update total
		var totalAmount float64
		for _, item := range items {
			itemTotal := float64(item.Quantity) * item.Price
			totalAmount += itemTotal

			// Insert order item
			_, err = tx.Exec(`
				INSERT INTO order_items (order_id, product_id, quantity, price)
				VALUES (?, ?, ?, ?)
			`, orderID, item.ProductID, item.Quantity, item.Price)
			if err != nil {
				return fmt.Errorf("error adding item: %v", err)
			}

			// Update product quantity
			_, err = tx.Exec(`
				UPDATE products
				SET quantity = quantity - ?
				WHERE id = ? AND quantity >= ?
			`, item.Quantity, item.ProductID, item.Quantity)
			if err != nil {
				return fmt.Errorf("error updating product: %v", err)
			}
		}

		// Update order total
		_, err = tx.Exec(`
			UPDATE orders
			SET total_amount = ?
			WHERE id = ?
		`, totalAmount, orderID)
		if err != nil {
			return fmt.Errorf("error updating total: %v", err)
		}

		return nil
	})

	return orderID, err
}

// BatchInsertWithTransaction batch inserts records in transaction
func BatchInsertWithTransaction(db *sql.DB, records []struct{ Name, Email string }) (int, error) {
	inserted := 0

	err := ExecuteInTransaction(db, func(tx *sql.Tx) error {
		stmt, err := tx.Prepare(`
			INSERT INTO users (username, email, password)
			VALUES (?, ?, 'default')
		`)
		if err != nil {
			return err
		}
		defer stmt.Close()

		for _, record := range records {
			_, err := stmt.Exec(record.Name, record.Email)
			if err != nil {
				log.Printf("Warning: failed to insert %s: %v
", record.Name, err)
				continue
			}
			inserted++
		}

		return nil
	})

	return inserted, err
}

// UpdateMultipleTables updates multiple tables in transaction
func UpdateMultipleTables(db *sql.DB, userID int, newEmail string) error {
	return ExecuteInTransaction(db, func(tx *sql.Tx) error {
		// Update users table
		_, err := tx.Exec(`
			UPDATE users
			SET email = ?
			WHERE id = ?
		`, newEmail, userID)
		if err != nil {
			return fmt.Errorf("error updating users: %v", err)
		}

		// Update related records
		_, err = tx.Exec(`
			UPDATE orders
			SET status = 'email_updated'
			WHERE user_id = ?
		`, userID)
		if err != nil {
			return fmt.Errorf("error updating orders: %v", err)
		}

		return nil
	})
}

// 5. Error Handling

// ExecuteWithRetry executes operation with retry on failure
func ExecuteWithRetry(db *sql.DB, maxRetries int, fn func(*sql.Tx) error) error {
	var lastErr error

	for i := 0; i < maxRetries; i++ {
		err := ExecuteInTransaction(db, fn)
		if err == nil {
			return nil
		}

		lastErr = err
		log.Printf("Attempt %d failed: %v
", i+1, err)
	}

	return fmt.Errorf("failed after %d attempts: %v", maxRetries, lastErr)
}

// ExecuteTransactionWithHandler executes with custom error handler
func ExecuteTransactionWithHandler(db *sql.DB, fn func(*sql.Tx) error, errorHandler func(error)) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}

	defer tx.Rollback()

	if err := fn(tx); err != nil {
		if errorHandler != nil {
			errorHandler(err)
		}
		return err
	}

	return tx.Commit()
}

// 6. Transaction Monitoring

// GetActiveTransactions returns active transaction info
func GetActiveTransactions(db *sql.DB) (map[string]interface{}, error) {
	// SQLite doesn't expose active transactions directly
	// This is a placeholder for monitoring
	info := make(map[string]interface{})

	var locked int
	err := db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type = 'table'").Scan(&locked)
	if err == nil {
		info["tables"] = locked
	}

	return info, nil
}

// GetTransactionStatus checks transaction status
func GetTransactionStatus(tx *sql.Tx) (string, error) {
	// Try a simple query to check if transaction is active
	var dummy int
	err := tx.QueryRow("SELECT 1").Scan(&dummy)
	if err != nil {
		if err == sql.ErrTxDone {
			return "closed", nil
		}
		return "error", err
	}
	return "active", nil
}

// 7. Transaction Cleanup

// CleanupOldTransactions cleans up old transaction data
func CleanupOldTransactions(db *sql.DB, days int) error {
	return ExecuteInTransaction(db, func(tx *sql.Tx) error {
		_, err := tx.Exec(`
			DELETE FROM transactions
			WHERE DATE(created_at) < DATE('now', '-' || ? || ' days')
		`, days)
		if err != nil {
			return fmt.Errorf("error cleaning up: %v", err)
		}

		// Vacuum to reclaim space
		_, err = tx.Exec("VACUUM")
		return err
	})
}

// 8. Concurrent Transactions

// ExecuteConcurrentTransactions executes multiple transactions concurrently
func ExecuteConcurrentTransactions(db *sql.DB, operations []func(*sql.Tx) error) []error {
	errChan := make(chan error, len(operations))

	for _, op := range operations {
		go func(fn func(*sql.Tx) error) {
			errChan <- ExecuteInTransaction(db, fn)
		}(op)
	}

	var errors []error
	for i := 0; i < len(operations); i++ {
		if err := <-errChan; err != nil {
			errors = append(errors, err)
		}
	}

	return errors
}

// Usage Examples
func main() {
	fmt.Println("=== Web Go Transaction Handling Examples ===
")

	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Create tables
	_, err = db.Exec(`
	CREATE TABLE accounts (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		balance REAL NOT NULL DEFAULT 0
	)

	CREATE TABLE transactions (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		from_account INTEGER NOT NULL,
		to_account INTEGER NOT NULL,
		amount REAL NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (from_account) REFERENCES accounts(id),
		FOREIGN KEY (to_account) REFERENCES accounts(id)
	)

	CREATE TABLE products (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		description TEXT,
		quantity INTEGER NOT NULL DEFAULT 0,
		price REAL NOT NULL
	)

	CREATE TABLE orders (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		total_amount REAL NOT NULL,
		status TEXT DEFAULT 'pending'
	)

	CREATE TABLE order_items (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		order_id INTEGER NOT NULL,
		product_id INTEGER NOT NULL,
		quantity INTEGER NOT NULL,
		price REAL NOT NULL,
		FOREIGN KEY (order_id) REFERENCES orders(id),
		FOREIGN KEY (product_id) REFERENCES products(id)
	)

	CREATE TABLE users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT NOT NULL UNIQUE,
		email TEXT NOT NULL UNIQUE,
		password TEXT NOT NULL
	)
	`)
	if err != nil {
		log.Fatal(err)
	}

	// 1. Basic transaction
	fmt.Println("--- 1. Basic Transaction ---")
	_, err = db.Exec("INSERT INTO accounts (name, balance) VALUES (?, ?)", "Alice", 1000.0)
	_, err = db.Exec("INSERT INTO accounts (name, balance) VALUES (?, ?)", "Bob", 500.0)
	if err != nil {
		log.Fatal(err)
	}

	err = TransferFunds(db, 1, 2, 200.0)
	if err != nil {
		log.Printf("Transfer failed: %v
", err)
	} else {
		fmt.Println("Transfer successful")
	}

	// 2. Create order with items
	fmt.Println("
--- 2. Create Order with Items ---")
	_, err = db.Exec("INSERT INTO products (name, quantity, price) VALUES (?, ?, ?)", "Laptop", 10, 999.99)
	_, err = db.Exec("INSERT INTO products (name, quantity, price) VALUES (?, ?, ?)", "Mouse", 50, 29.99)
	_, err = db.Exec("INSERT INTO users (username, email, password) VALUES (?, ?, ?)", "john", "[email protected]", "pass")
	if err != nil {
		log.Fatal(err)
	}

	items := []struct{ ProductID, Quantity int; Price float64 }{
		{1, 1, 999.99},
		{2, 2, 29.99},
	}
	orderID, err := CreateOrderWithItems(db, 1, items)
	if err != nil {
		log.Printf("Order creation failed: %v
", err)
	} else {
		fmt.Printf("Order created with ID: %d
", orderID)
	}

	// 3. Batch insert
	fmt.Println("
--- 3. Batch Insert ---")
	users := []struct{ Name, Email string }{
		{"user1", "[email protected]"},
		{"user2", "[email protected]"},
		{"user3", "[email protected]"},
	}
	inserted, err := BatchInsertWithTransaction(db, users)
	if err == nil {
		fmt.Printf("Inserted %d users
", inserted)
	}

	// 4. Error handling
	fmt.Println("
--- 4. Transaction with Error Handling ---")
	err = ExecuteWithRetry(db, 3, func(tx *sql.Tx) error {
		// Simulate operation
		_, err := tx.Exec("INSERT INTO accounts (name, balance) VALUES (?, ?)", "Charlie", 100.0)
		return err
	})
	if err != nil {
		log.Printf("Operation failed: %v
", err)
	} else {
		fmt.Println("Operation successful")
	}

	// 5. Update multiple tables
	fmt.Println("
--- 5. Update Multiple Tables ---")
	err = UpdateMultipleTables(db, 1, "[email protected]")
	if err != nil {
		log.Printf("Update failed: %v
", err)
	} else {
		fmt.Println("Multiple tables updated")
	}

	fmt.Println("
=== All Transaction Handling Examples Completed ===")
}