package main

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

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

// User represents a database row
type User struct {
	ID        int    `db:"id"`
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
	Email     string `db:"email"`
	Active    bool   `db:"active"`
}

func main() {
	// Connect to database (using SQLite for example)
	db, err := sqlx.Connect("sqlite3", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Create table
	schema := `
		CREATE TABLE users (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			first_name TEXT NOT NULL,
			last_name TEXT NOT NULL,
			email TEXT UNIQUE NOT NULL,
			active BOOLEAN DEFAULT true
		)`
	db.MustExec(schema)

	// Insert with named parameters
	result, err := db.NamedExec(`
		INSERT INTO users (first_name, last_name, email, active)
		VALUES (:first_name, :last_name, :email, :active)`,
		User{FirstName: "Alice", LastName: "Smith", Email: "alice@example.com", Active: true},
	)
	if err != nil {
		log.Fatal(err)
	}
	id, _ := result.LastInsertId()
	fmt.Println("Inserted user ID:", id)

	// Insert multiple rows
	users := []User{
		{FirstName: "Bob", LastName: "Jones", Email: "bob@example.com", Active: true},
		{FirstName: "Charlie", LastName: "Brown", Email: "charlie@example.com", Active: false},
	}
	_, err = db.NamedExec(`
		INSERT INTO users (first_name, last_name, email, active)
		VALUES (:first_name, :last_name, :email, :active)`, users)
	if err != nil {
		log.Fatal(err)
	}

	// Query single row into struct
	var user User
	err = db.Get(&user, "SELECT * FROM users WHERE id = ?", 1)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Single user: %+v\n", user)

	// Query multiple rows into slice
	var allUsers []User
	err = db.Select(&allUsers, "SELECT * FROM users WHERE active = ?", true)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Active users: %d\n", len(allUsers))

	// Named query with map
	rows, err := db.NamedQuery(`
		SELECT * FROM users WHERE last_name = :name`,
		map[string]interface{}{"name": "Smith"},
	)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var u User
		rows.StructScan(&u)
		fmt.Printf("Named query result: %+v\n", u)
	}

	// Transaction support
	tx, err := db.Beginx()
	if err != nil {
		log.Fatal(err)
	}

	tx.MustExec(`UPDATE users SET active = ? WHERE id = ?`, false, 1)
	tx.MustExec(`UPDATE users SET active = ? WHERE id = ?`, true, 3)

	err = tx.Commit()
	if err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	fmt.Println("Transaction committed")

	// IN clause expansion
	var selected []User
	query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", []int{1, 2})
	if err != nil {
		log.Fatal(err)
	}
	query = db.Rebind(query) // Convert ? to $1, $2 for postgres, etc.
	err = db.Select(&selected, query, args...)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("IN query: %d users\n", len(selected))

	// Handle nullable fields with sql.NullString
	type UserWithNullable struct {
		ID       int            `db:"id"`
		Nickname sql.NullString `db:"nickname"`
	}
}
