package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
    //"github.com/go-sql-driver/mysql"
	"github.com/gorilla/mux"
	"reflect"
	"time"
	"strings"
	//"strconv"
)
import _ "github.com/go-sql-driver/mysql"
var people []Person

type Person struct {
	ID      string   `json:"id,omitempty"`
	Name    string   `json:"name,omitempty"`
	Address *Address `json:"address,omitempty"`
}
type Stock struct {
    Date      string    `json:"date"`
    Open string `json:"open"`
	High   string `json:"high"`
	Low     string    `json:"low"`
    Close string `json:"close"`
    Volume   string `json:"volume"`
}
type Address struct {
	Country string `json:"country,omitempty"`
	City    string `json:"city,omitempty"`
}

func DefaultHandler(w http.ResponseWriter, r *http.Request) {
	fmt.Fprint(w, "Home")
	fmt.Fprint(w, "Home")
	fmt.Fprint(w, "Home")

}
// 定义一个结构体, 需要大写开头哦, 字段名也需要大写开头哦, 否则json模块会识别不了
// 结构体成员仅大写开头外界才能访问
type User struct {
    User      string    `json:"user"`
    Password string `json:"password"`
    Host   string `json:"host"`
}
func PeopleHandler(w http.ResponseWriter, r *http.Request) {

	w.Header().Set("Access-Control-Allow-Origin", "*")
	json.NewEncoder(w).Encode(people)

}

func PersonHandler(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "*")
	params := mux.Vars(r)
	for _, item := range people {
		if item.ID == params["id"] {
			json.NewEncoder(w).Encode(item)
		}
	}

}
func HttpFileHandler(response http.ResponseWriter, request *http.Request) {
	//fmt.Fprintf(w, "Hi from e %s!", r.URL.Path[1:])
	http.ServeFile(response, request, "index.html")
}
func HttpFileHandler2(response http.ResponseWriter, request *http.Request) {
	//fmt.Fprintf(w, "Hi from e %s!", r.URL.Path[1:])
	http.ServeFile(response, request, "details.html")
}
func HttpFileHandler3(response http.ResponseWriter, request *http.Request) {
	//fmt.Fprintf(w, "Hi from e %s!", r.URL.Path[1:])
	http.ServeFile(response, request, "123.html")
}
func getFieldString(e *Stock, field string) string {
	r := reflect.ValueOf(e)
	f := reflect.Indirect(r).FieldByName(field)
	return f.String()
}

func checkCount(rows *sql.Rows) (count int) {
	for rows.Next() {
	   err:= rows.Scan(&count)
	   checkErr(err)
   }   
   return count
}

func checkErr(err error) {
   if err != nil {
	   panic(err)
   }
}
func HttpFileHandler4(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Access-Control-Allow-Origin", "http://172.16.7.21:8080")
	w.Header().Set("Access-Control-Allow-Credentials", "true")
	
	db, e := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if e != nil { //如果连接出错,e将不是nil的
        print("ERROR?")
        return
	}

    // 提醒一句, 运行到这里, 并不代表数据库连接是完全OK的, 因为发送第一条SQL才会校验密码 汗~!
    _, e2 := db.Query("select 1")
    if e2 == nil {
		println("DB OK")
		ttt,err := db.Exec("USE stock")
		if err != nil {
			print("ERROR?")
			return
		}
		fmt.Print(ttt)
        rows, e := db.Query("SELECT date,open,high,low,close,volume FROM `1216tw`;")
        if e != nil {
			fmt.Print("query error!!%v\n", e)
			
            return
		}
		fmt.Print(rows)
        if rows == nil {
            print("Rows is nil")   
            return
		}
	
		var first int
		first=0
		fmt.Fprintln(w,"[")
		for rows.Next() { //跟java的ResultSet一样,需要先next读取
			if first!=0{
				if rows.Next()!=false{
					fmt.Fprintln(w,",")
				}
			}
			if first==0{
				first=1
			}
		
			
            stock := new(Stock)
			// rows貌似只支持Scan方法 继续汗~! 当然,可以通过GetColumns()来得到字段顺序

            row_err := rows.Scan(&stock.Date,&stock.Open,&stock.High,&stock.Low,&stock.Close,&stock.Volume)
            if row_err != nil {
				//print("Row error!!")
				fmt.Fprintln(w,"]")
                return
			}
			fmt.Fprint(w,"[")
			tm2, _ := time.Parse("2006-01-02 15:04:05", strings.Replace(getFieldString(stock, "Date"), "/", "-", 2)+" 00:00:00")
	
			//fmt.Println(fmt.Sprint((int64(tm2.Unix()))))
			
			//fmt.Print(int32(tm2.Unix()))
			//fmt.Println(strings.Replace(getFieldString(stock, "Date"), "/", "-", 2)+" 00:00:00")
			//fmt.Print(int32(tm2.Unix()))
			fmt.Fprint(w,fmt.Sprint((int64(tm2.Unix()*1000)))+",")
			fmt.Fprint(w,getFieldString(stock, "Open")+",")
			fmt.Fprint(w,getFieldString(stock, "High")+",")
			fmt.Fprint(w,getFieldString(stock, "Low")+",")
			fmt.Fprint(w,getFieldString(stock, "Close")+",")
			fmt.Fprint(w,getFieldString(stock, "Volume"))
			fmt.Fprintln(w,"]")
			
			// fmt.Fprintln(w,checkCount(&rows))
            // b, _ := json.Marshal(stock)
			// fmt.Println(string(b)) // 这里没有判断错误, 呵呵, 一般都不会有错吧
			//fmt.Fprint(w,string(b))
        }
		fmt.Fprintln(w,"]")
	
    }

}

func getFromdatabase() {

	db, e := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if e != nil { 
        print("ERROR?")
        return
    }
   
    _, e2 := db.Query("select 1")
    if e2 == nil {
        println("DB OK")
        rows, e := db.Query("select user,password,host from mysql.user")
        if e != nil {
            fmt.Print("query error!!%v\n", e)
            return
        }
        if rows == nil {
            print("Rows is nil")   
            return
        }
        for rows.Next() { 
            user := new(User)
         
            row_err := rows.Scan(&user.User,&user.Password, &user.Host)
            if row_err != nil {
                print("Row error!!")
                return
            }
            b, _ := json.Marshal(user)
            fmt.Println(string(b)) 
        }

    }

}
func getFromdatabase2() {

	db, e := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
    if e != nil { //如果连接出错,e将不是nil的
        print("ERROR?")
        return
	}

    // 提醒一句, 运行到这里, 并不代表数据库连接是完全OK的, 因为发送第一条SQL才会校验密码 汗~!
    _, e2 := db.Query("select 1")
    if e2 == nil {
		println("DB OK")
		ttt,err := db.Exec("USE stock")
		if err != nil {
			print("ERROR?")
			return
		}
		fmt.Print(ttt)
        rows, e := db.Query("SELECT date,open,high,low,close,volume FROM `1216tw`;")
        if e != nil {
			fmt.Print("query error!!%v\n", e)
			
            return
		}
		fmt.Print(rows)
        if rows == nil {
            print("Rows is nil")   
            return
		}

		for rows.Next() { //跟java的ResultSet一样,需要先next读取

            stock := new(Stock)
			// rows貌似只支持Scan方法 继续汗~! 当然,可以通过GetColumns()来得到字段顺序

            row_err := rows.Scan(&stock.Date,&stock.Open,&stock.High,&stock.Low,&stock.Close,&stock.Volume)
            if row_err != nil {
                print("Row error!!")
                return
            }
            b, _ := json.Marshal(stock)
            fmt.Println(string(b)) // 这里没有判断错误, 呵呵, 一般都不会有错吧
        }

    }

}
//插入demo
func insert() {
    db, err := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")

    stmt, err := db.Prepare(`INSERT user (Host,user,password) values (?,?,?)`)
	if err != nil {
		panic(err)
	}
    res, err := stmt.Exec("tony", 20, 1)
	if err != nil {
		panic(err)
	}
    id, err := res.LastInsertId()
	if err != nil {
		panic(err)
	}
    fmt.Println(id)
}
func create(name string) {

	db, err := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
	if err != nil {
		panic(err)
	}
	defer db.Close()
 

 
	_,err = db.Exec("USE stock")
	if err != nil {
		panic(err)
	}
	var  query string
	
	query="CREATE TABLE "+name+"  (data varchar(32) ,open float, high float, low float ,close float , volume float)"

	fmt.Println(query)

	_,err = db.Exec(query)
	if err != nil {
		panic(err)
	}
 }
 func update() {
    db, err := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
	if err != nil {
		panic(err)
	}
    stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
	if err != nil {
		panic(err)
	}
    res, err := stmt.Exec(21, 2, 1)
	if err != nil {
		panic(err)
	}
    num, err := res.RowsAffected()
	if err != nil {
		panic(err)
	}
    fmt.Println(num)
}
 
//删除数据
func remove() {
    db, err := sql.Open("mysql", "root:1234@tcp(localhost:3306)/mysql?charset=utf8")
	if err != nil {
		panic(err)
	}
    stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
	if err != nil {
		panic(err)
	}
    res, err := stmt.Exec(1)
	if err != nil {
		panic(err)
	}
    num, err := res.RowsAffected()
	if err != nil {
		panic(err)
	}
    fmt.Println(num)
}
func main() {
	route := mux.NewRouter()
	people = append(people, Person{ID: "1", Name: "sojib", Address: &Address{Country: "bangladesh", City: "Dhaka"}})
	people = append(people, Person{ID: "2", Name: "hasan", Address: &Address{Country: "china", City: "C"}})
	people = append(people, Person{ID: "3", Name: "kamal", Address: &Address{Country: "japan", City: "J"}})
	route.HandleFunc("/", DefaultHandler).Methods("Get")
	route.HandleFunc("/people", PeopleHandler).Methods("Get")
	route.HandleFunc("/people/{id}", PersonHandler).Methods("Get")
	route.HandleFunc("/index", HttpFileHandler)
	route.HandleFunc("/details", HttpFileHandler2)
	route.HandleFunc("/123", HttpFileHandler3)
	route.HandleFunc("/321", HttpFileHandler4)
	//route.Handle("/details", http.FileServer(http.Dir("/mnt/c/golangtmp")))
	//getFromdatabase2()
	//create("2015tw")
	//create("qwdwqd")
	log.Fatal(http.ListenAndServe(":9000", route))
}