package main

import (
	"database/sql"
	"fmt"

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

type Staff struct {
	id        int
	age       int
	name      string
	email     string
	create_at string
}

func main() {
	db, err := sql.Open("mysql", "zy:123456@tcp(localhost:3306)/company")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	/* create table */
	createTableQuery := `CREATE TABLE IF NOT EXISTS department (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) NOT NULL,
		age INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )`

	_, err = db.Exec(createTableQuery)
	if err != nil {
		panic(err)
	} else {
		fmt.Println("Table created successfully")
	}

	/* insert data */
	insertCmd := `INSERT INTO department (name, email, age) VALUES ("xiaoming", "xiaoming@email.com", 25)`
	if result, err := db.Exec(insertCmd); err != nil {
		panic(err)
	} else {
		if id, err := result.LastInsertId(); err != nil {
			panic(err)
		} else {
			fmt.Printf("insert id %d\n", id)
		}
	}

	/* query data */
	queryCmd := `SELECT * FROM department`
	rows, err := db.Query(queryCmd)
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	for rows.Next() {
		var person Staff
		rows.Scan(&person.id, &person.name, &person.email, &person.age, &person.create_at)
		fmt.Printf("%v\n", person)
	}

	/* update data */
	updateCmd := `UPDATE department set age = 30 WHERE name = "xiaoming"`
	result, err := db.Exec(updateCmd)
	if err != nil {
		panic(err)
	} else {
		if affectId, err := result.RowsAffected(); err != nil {
			panic(err)
		} else {
			fmt.Printf("update success, rows count %d\n", affectId)
		}
	}

	/* delete */
	deleteCmd := `DELETE FROM department WHERE name = "xiaoming"`
	result, err = db.Exec(deleteCmd)
	if err != nil {
		panic(err)
	} else {
		if affectId, err := result.RowsAffected(); err != nil {
			panic(err)
		} else {
			fmt.Printf("delete success, rows count %d\n", affectId)
		}
	}
}