package main

import (
    "encoding/json"
    "fmt"
    "time"

    "database/sql"

    _ "github.com/go-sql-driver/mysql"
)

type Users struct {
    ID         int     `json:"id"`
    Name       string  `json:"name"`
    Age        int     `json:"age"`
    Created_At *string `json:"created_at"`
    Updated_At *string `json:"updated_at"`
}

// create
func create() {
    db, err := sql.Open("mysql", "root:root@/go_sql")
    if err != nil {
        fmt.Println("create connetion failed.....", err)
        return
    }
    defer db.Close()

    sql := "insert into users(name,age,created_at)values(?,?,?)"
    stmt, _ := db.Prepare(sql)
    defer stmt.Close()
    rs, err := stmt.Exec("BOB", 12, GetTime())
    if err != nil {
        fmt.Println("create info faield.......", err)
        return
    }
    id, _ := rs.LastInsertId()
    fmt.Println("last_insert_id value is: ", id)
    affert_num, _ := rs.RowsAffected()
    fmt.Println("number of affcted rows", affert_num)
}

// edit
func edit() {
    db, err := sql.Open("mysql", "root:root@/go_sql")
    if err != nil {
        fmt.Println("create connetion failed.....", err)
        return
    }
    defer db.Close()
    sql := "update users set name=?,updated_at=? where id=?"
    stmt, _ := db.Prepare(sql)
    defer stmt.Close()
    rs, err := stmt.Exec("Allent", GetTime(), 1)
    if err != nil {
        fmt.Println("edit faield.....", err)
        return
    }
    affert_num, _ := rs.RowsAffected()
    fmt.Println(affert_num)
}

// delete
func delete() {
    db, err := sql.Open("mysql", "root:root@/go_sql")
    if err != nil {
        fmt.Println("create connetion failed.....", err)
        return
    }
    defer db.Close()
    sql := "delete from users where id=?"
    stmt, _ := db.Prepare(sql)
    defer stmt.Close()
    rs, _ := stmt.Exec(1)
    affert_num, _ := rs.RowsAffected()
    fmt.Println(affert_num)
}

// one query
func query() {
    db, err := sql.Open("mysql", "root:root@/go_sql")
    if err != nil {
        fmt.Println("create connetion failed.....", err)
        return
    }
    defer db.Close()
    var user Users
    db.QueryRow("select * from users where id=?", 3).Scan(&user.ID, &user.Name, &user.Age, &user.Created_At, &user.Updated_At)
    rs, _ := json.Marshal(user)
    fmt.Println(string(rs))
}

// more query
func more_query() {
    db, err := sql.Open("mysql", "root:root@/go_sql")
    if err != nil {
        fmt.Println("create connetion failed.....", err)
        return
    }
    defer db.Close()
    rows, err := db.Query("select * from users")
    if err != nil {
        fmt.Println("more query faield......", err)
        return
    }
    defer rows.Close()
    var user Users
    users := make([]Users, 0)

    for rows.Next() {
        err := rows.Scan(&user.ID, &user.Name, &user.Age, &user.Created_At, &user.Updated_At)
        if err != nil {
            fmt.Println("row.scan failed......", err)
            return
        }
        users = append(users, user)
    }
    rs, _ := json.Marshal(users)
    fmt.Println(string(rs))
}

func main() {
    create()
    edit()
    delete()
    query()
    more_query()

}

func GetTime() string {
    return time.Now().Format("2006-01-02 15:04:05")
}