Database

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"
    
    _ "github.com/go-sql-driver/mysql"
)

// 1. Model
type User struct {
    ID        int64
    Username  string
    Email     string
    CreatedAt time.Time
    UpdatedAt time.Time
}

// 2. Database wrapper
type DB struct {
    *sql.DB
}

// 3. Database connection
func NewDB() (*DB, error) {
    // Format: username:password@tcp(host:port)/dbname?parseTime=true
    db, err := sql.Open("mysql",
        "root:password@tcp(localhost:3306)/testdb?parseTime=true")
    if err != nil {
        return nil, err
    }

    // Set connection pool settings
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)

    // Test connection
    if err := db.Ping(); err != nil {
        return nil, err
    }

    return &DB{db}, nil
}

// 4. Create table
func (db *DB) CreateTable() error {
    query := `
    CREATE TABLE IF NOT EXISTS users (
        id BIGINT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(255) NOT NULL UNIQUE,
        email VARCHAR(255) NOT NULL UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )`

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

// 5. CRUD operations
func (db *DB) CreateUser(user *User) error {
    query := `
    INSERT INTO users (username, email)
    VALUES (?, ?)`

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

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

    user.ID = id
    return nil
}

func (db *DB) GetUser(id int64) (*User, error) {
    user := &User{}
    query := `
    SELECT id, username, email, created_at, updated_at
    FROM users
    WHERE id = ?`

    err := db.QueryRow(query, id).Scan(
        &user.ID,
        &user.Username,
        &user.Email,
        &user.CreatedAt,
        &user.UpdatedAt,
    )

    if err == sql.ErrNoRows {
        return nil, fmt.Errorf("user not found")
    }
    if err != nil {
        return nil, err
    }

    return user, nil
}

func (db *DB) UpdateUser(user *User) error {
    query := `
    UPDATE users
    SET username = ?, email = ?
    WHERE id = ?`

    result, err := db.Exec(query, user.Username, user.Email, user.ID)
    if err != nil {
        return err
    }

    rows, err := result.RowsAffected()
    if err != nil {
        return err
    }
    if rows == 0 {
        return fmt.Errorf("user not found")
    }

    return nil
}

func (db *DB) DeleteUser(id int64) error {
    query := `DELETE FROM users WHERE id = ?`

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

    rows, err := result.RowsAffected()
    if err != nil {
        return err
    }
    if rows == 0 {
        return fmt.Errorf("user not found")
    }

    return nil
}

func (db *DB) ListUsers(limit, offset int) ([]User, error) {
    query := `
    SELECT id, username, email, created_at, updated_at
    FROM users
    ORDER BY id
    LIMIT ? OFFSET ?`

    rows, err := db.Query(query, limit, 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.Username,
            &user.Email,
            &user.CreatedAt,
            &user.UpdatedAt,
        )
        if err != nil {
            return nil, err
        }
        users = append(users, user)
    }

    if err = rows.Err(); err != nil {
        return nil, err
    }

    return users, nil
}

// 6. Transaction example
func (db *DB) TransferCredits(fromID, toID int64, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Deduct from source
    _, err = tx.Exec(`
        UPDATE accounts 
        SET balance = balance - ? 
        WHERE id = ? AND balance >= ?`,
        amount, fromID, amount)
    if err != nil {
        return err
    }

    // Add to destination
    _, err = tx.Exec(`
        UPDATE accounts 
        SET balance = balance + ? 
        WHERE id = ?`,
        amount, toID)
    if err != nil {
        return err
    }

    return tx.Commit()
}

// 7. Prepared statement example
type UserStore struct {
    createStmt *sql.Stmt
    getStmt    *sql.Stmt
    updateStmt *sql.Stmt
    deleteStmt *sql.Stmt
}

func NewUserStore(db *DB) (*UserStore, error) {
    createStmt, err := db.Prepare(`
        INSERT INTO users (username, email)
        VALUES (?, ?)`)
    if err != nil {
        return nil, err
    }

    getStmt, err := db.Prepare(`
        SELECT id, username, email, created_at, updated_at
        FROM users
        WHERE id = ?`)
    if err != nil {
        return nil, err
    }

    updateStmt, err := db.Prepare(`
        UPDATE users
        SET username = ?, email = ?
        WHERE id = ?`)
    if err != nil {
        return nil, err
    }

    deleteStmt, err := db.Prepare(`
        DELETE FROM users
        WHERE id = ?`)
    if err != nil {
        return nil, err
    }

    return &UserStore{
        createStmt: createStmt,
        getStmt:    getStmt,
        updateStmt: updateStmt,
        deleteStmt: deleteStmt,
    }, nil
}

func (s *UserStore) Close() error {
    if err := s.createStmt.Close(); err != nil {
        return err
    }
    if err := s.getStmt.Close(); err != nil {
        return err
    }
    if err := s.updateStmt.Close(); err != nil {
        return err
    }
    return s.deleteStmt.Close()
}

func main() {
    // Connect to database
    db, err := NewDB()
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create table
    if err := db.CreateTable(); err != nil {
        log.Fatal(err)
    }

    // Create user
    user := &User{
        Username: "john_doe",
        Email:    "john@example.com",
    }
    if err := db.CreateUser(user); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Created user: %+v\n", user)

    // Get user
    fetchedUser, err := db.GetUser(user.ID)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Fetched user: %+v\n", fetchedUser)

    // Update user
    user.Email = "john.doe@example.com"
    if err := db.UpdateUser(user); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Updated user: %+v\n", user)

    // List users
    users, err := db.ListUsers(10, 0)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Listed users: %+v\n", users)

    // Delete user
    if err := db.DeleteUser(user.ID); err != nil {
        log.Fatal(err)
    }
    fmt.Println("Deleted user")

    // Example with prepared statements
    store, err := NewUserStore(db)
    if err != nil {
        log.Fatal(err)
    }
    defer store.Close()
}