package main

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

	_ "modernc.org/sqlite"
)

// 用户模型
type User struct {
	ID        int
	Name      string
	Email     string
	Age       int
	CreatedAt time.Time
}

// 数据库连接
var db *sql.DB

func main() {
	var err error

	// 1. 初始化数据库连接
	db, err = initDatabase("test.db")
	if err != nil {
		log.Fatal("初始化数据库失败:", err)
	}
	defer db.Close()

	fmt.Println("SQLite数据库CRUD示例")
	fmt.Println("====================")

	//// 2. 创建用户表
	//err = createTable()
	//if err != nil {
	//	log.Fatal("创建表失败:", err)
	//}
	//fmt.Println("✓ 用户表创建成功")
	//
	//// 3. 创建用户
	//user1 := User{
	//	Name:  "张三",
	//	Email: "zhangsan@example.com",
	//	Age:   25,
	//}
	//
	//id, err := createUser(user1)
	//if err != nil {
	//	log.Fatal("创建用户失败:", err)
	//}
	//fmt.Printf("✓ 创建用户成功，ID: %d\n", id)
	//
	user2 := User{
		Name:  "李四",
		Email: "lisi@example.com",
		Age:   30,
	}
	id2, _ := createUser(user2)
	fmt.Printf("✓ 创建用户成功，ID: %d\n", id2)

	//// 4. 查询单个用户
	//fmt.Println("\n查询用户ID=1:")
	//user, err := getUserByID(1)
	//if err != nil {
	//	log.Fatal("查询用户失败:", err)
	//}
	//printUser(user)

	// 5. 查询所有用户
	fmt.Println("\n查询所有用户:")
	users, err := getAllUsers()
	if err != nil {
		log.Fatal("查询所有用户失败:", err)
	}
	for _, u := range users {
		printUser(u)
	}

	//// 6. 更新用户
	//fmt.Println("\n更新用户ID=1的信息:")
	//updateData := map[string]interface{}{
	//	"name":  "张三丰",
	//	"email": "zhangsanfeng@example.com",
	//	"age":   28,
	//}
	//rowsAffected, err := updateUser(1, updateData)
	//if err != nil {
	//	log.Fatal("更新用户失败:", err)
	//}
	//fmt.Printf("✓ 更新成功，影响行数: %d\n", rowsAffected)
	//
	//// 7. 再次查询验证更新
	//updatedUser, _ := getUserByID(1)
	//printUser(updatedUser)
	//
	//// 8. 删除用户
	//fmt.Println("\n删除用户ID=2:")
	//rowsAffected, err = deleteUser(2)
	//if err != nil {
	//	log.Fatal("删除用户失败:", err)
	//}
	//fmt.Printf("✓ 删除成功，影响行数: %d\n", rowsAffected)
	//
	//// 9. 查询删除后的用户列表
	//fmt.Println("\n删除后的所有用户:")
	//remainingUsers, _ := getAllUsers()
	//if len(remainingUsers) == 0 {
	//	fmt.Println("没有用户数据")
	//}
	//for _, u := range remainingUsers {
	//	printUser(u)
	//}
	//
	//// 10. 批量插入示例
	//fmt.Println("\n批量插入用户:")
	//err = batchCreateUsers()
	//if err != nil {
	//	log.Fatal("批量插入失败:", err)
	//}
	//
	//// 11. 分页查询示例
	//fmt.Println("\n分页查询（每页2条）:")
	//pageSize := 2
	//fmt.Println("第1页:")
	//page1Users, _ := getUsersByPage(1, pageSize)
	//for _, u := range page1Users {
	//	printUser(u)
	//}
	//
	//fmt.Println("\n第2页:")
	//page2Users, _ := getUsersByPage(2, pageSize)
	//for _, u := range page2Users {
	//	printUser(u)
	//}
	//
	//// 12. 统计用户数量
	//count, _ := countUsers()
	//fmt.Printf("\n用户总数: %d\n", count)
}

// 初始化数据库连接
func initDatabase(dbPath string) (*sql.DB, error) {
	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, err
	}

	// 设置连接池参数
	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)

	// 验证连接
	err = db.Ping()
	if err != nil {
		return nil, err
	}

	return db, nil
}

// 创建用户表
func createTable() error {
	query := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		email TEXT UNIQUE NOT NULL,
		age INTEGER,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
	CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);
	`

	_, err := db.Exec(query)
	return err
}

// 创建用户（返回生成的ID）
func createUser(user User) (int64, error) {
	query := `INSERT INTO users (name, email, age) VALUES (?, ?, ?)`
	result, err := db.Exec(query, user.Name, user.Email, user.Age)
	if err != nil {
		return 0, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return 0, err
	}

	return id, nil
}

// 根据ID查询用户
func getUserByID(id int) (User, error) {
	var user User
	query := `SELECT id, name, email, age, created_at FROM users WHERE id = ?`
	row := db.QueryRow(query, id)

	err := row.Scan(&user.ID, &user.Name, &user.Email, &user.Age, &user.CreatedAt)
	if err != nil {
		return User{}, err
	}

	return user, nil
}

// 查询所有用户
func getAllUsers() ([]User, error) {
	query := `SELECT id, name, email, age, created_at FROM users ORDER BY id`
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.Age, &user.CreatedAt)
		if err != nil {
			return nil, err
		}
		users = append(users, user)
	}

	return users, nil
}

// 更新用户
func updateUser(id int, updates map[string]interface{}) (int64, error) {
	// 构建SQL语句
	query := "UPDATE users SET "
	args := []interface{}{}
	i := 0

	for key, value := range updates {
		if i > 0 {
			query += ", "
		}
		query += key + " = ?"
		args = append(args, value)
		i++
	}

	query += " WHERE id = ?"
	args = append(args, id)

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

	return result.RowsAffected()
}

// 删除用户
func deleteUser(id int) (int64, error) {
	query := `DELETE FROM users WHERE id = ?`
	result, err := db.Exec(query, id)
	if err != nil {
		return 0, err
	}

	return result.RowsAffected()
}

// 批量创建用户
func batchCreateUsers() error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	stmt, err := tx.Prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)")
	if err != nil {
		return err
	}
	defer stmt.Close()

	users := []User{
		{Name: "王五", Email: "wangwu@example.com", Age: 22},
		{Name: "赵六", Email: "zhaoliu@example.com", Age: 35},
		{Name: "孙七", Email: "sunqi@example.com", Age: 28},
	}

	for _, user := range users {
		_, err := stmt.Exec(user.Name, user.Email, user.Age)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

// 分页查询用户
func getUsersByPage(page, pageSize int) ([]User, error) {
	offset := (page - 1) * pageSize
	query := `SELECT id, name, email, age, created_at FROM users 
	          ORDER BY id LIMIT ? OFFSET ?`

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

	var users []User
	for rows.Next() {
		var user User
		err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.Age, &user.CreatedAt)
		if err != nil {
			return nil, err
		}
		users = append(users, user)
	}

	return users, nil
}

// 统计用户数量
func countUsers() (int, error) {
	var count int
	query := `SELECT COUNT(*) FROM users`
	err := db.QueryRow(query).Scan(&count)
	return count, err
}

// 打印用户信息
func printUser(user User) {
	fmt.Printf("ID: %d, 姓名: %s, 邮箱: %s, 年龄: %d, 创建时间: %s\n",
		user.ID, user.Name, user.Email, user.Age, user.CreatedAt.Format("2006-01-02 15:04:05"))
}