package storage

import (
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
	"netease_control_center/interfaces/log"
	"netease_control_center/utils"
	"os"
	"os/exec"
	"sync"
)

const (
	DB_PATH              = "file:/mnt/UDISK/golangsql.db?cache=shared"
	SQLTABLENAME_CONFIG  = "config"
	SQLFMT_CREATE_CONFIG = `CREATE TABLE IF NOT EXISTS ` + SQLTABLENAME_CONFIG + ` (
	"id" INTEGER PRIMARY KEY AUTOINCREMENT,
	"key" VCHAR(100) NOT NULL UNIQUE,
	"value" TEXT NULL
)`
	SQLFMT_CONFIG_SET = `INSERT OR REPLACE INTO ` + SQLTABLENAME_CONFIG + ` (key,value) VALUES(?,?)`
	SQLFMT_CONFIG_GET = `SELECT value FROM ` + SQLTABLENAME_CONFIG + ` WHERE key = ? LIMIT 1`
)

var (
	db         *sql.DB
	initDriver = false
	initTable  = false

	lockdb sync.Mutex
)

func lockDb() {
	lockdb.Lock()
}

func unlockDb() {
	lockdb.Unlock()
}

func dumpUdisk() {
	by, byerr := exec.Command("ls", "/mnt/UDISK/").Output()
	log.Println(log.DebugLog, "the UDISK documnet: ", byerr, string(by))
}

func InitSql() {
	lockDb()
	defer unlockDb()
	sqlInit()
}

func sqlInit() error {
	var err error
	if initDriver == false {
		dumpUdisk()
		log.Println(log.DebugLog, "Begin to init sql")
		db, err = sql.Open("sqlite3",DB_PATH)
		if err != nil {
			log.Println(log.ErrorLog, "db open fail", err)
			return err
		} else {
			db.SetMaxOpenConns(1)
			log.Println(log.DebugLog, "Init sqlite success!")
			initDriver = true
		}
	}

	if initTable == false {
		rst, err := db.Exec(SQLFMT_CREATE_CONFIG)
		if err != nil {
			log.Println(log.ErrorLog, "Create table err ", SQLFMT_CREATE_CONFIG, err)
			return err
		}
		//fmt.Println(SQLFMT_CREATE_CONFIG)
		fmt.Println(rst.LastInsertId())
		fmt.Println(rst.RowsAffected())
		initTable = true
	}

	return nil
}

func Config_set(modIndex int, key interface{}, value interface{}) error {
	lockDb()
	defer unlockDb()

	if err := sqlInit(); err != nil {
		return err
	}

	keyName := utils.ToString(modIndex) + "_" + utils.ToString(key)
	valString := utils.ToString(value)

	rst, err := db.Exec(SQLFMT_CONFIG_SET, keyName, valString)
	if err != nil {
		fmt.Println(rst, err)
	}

	return err
}

func Config_get(modIndex int, key interface{}, value interface{}) error {
	lockDb()
	defer unlockDb()
	if err := sqlInit(); err != nil {
		return err
	}

	var val string
	keyName := utils.ToString(modIndex) + "_" + utils.ToString(key)

	err := db.QueryRow(SQLFMT_CONFIG_GET, keyName).Scan(&val)

	if err != nil {
		fmt.Println(err)
		return err
	}

	return utils.StringStore(val, value)
}

func Sqlite3test() {
	var setString = "wzj"
	var getString string

	var setInt int = 66
	var getInt int
	return

	Config_set(-1, 1, setString)
	Config_set(-1, 2, setInt)

	Config_get(-1, 1, &getString)
	Config_get(-1, 2, &getInt)

	fmt.Println(getString, getInt)
	Config_set(-1, 2, 128)
	Config_get(-1, 2, &getInt)
	fmt.Println(getInt)

	for i := 0; i < 512; i++ {
		Config_set(-1, 2, i)
		Config_get(-1, 2, &getInt)

		fmt.Println("Set ", i, " Get: ", getInt)
	}
}

func Sqlite3test2() {
	os.Remove("./foo.db")

	db, err := sql.Open("sqlite3", "./foo.db")
	if err != nil {
		log.Println(log.ErrorLog, err)
	}
	defer db.Close()

	sqlStmt := `
	create table foo (id integer not null primary key, name text);
	delete from foo;
	`
	_, err = db.Exec(sqlStmt)
	if err != nil {
		log.Printf(log.DebugLog, "%q: %s\n", err, sqlStmt)
		return
	}

	tx, err := db.Begin()
	if err != nil {
		log.Println(log.ErrorLog, err)
	}
	stmt, err := tx.Prepare("insert into foo(id, name) values(?, ?)")
	if err != nil {
		log.Println(log.ErrorLog, err)
	}
	defer stmt.Close()
	for i := 0; i < 100; i++ {
		_, err = stmt.Exec(i, fmt.Sprintf("こんにちわ世界%03d", i))
		if err != nil {
			log.Println(log.ErrorLog, err)
		}
	}
	tx.Commit()

	rows, err := db.Query("select id, name from foo")
	if err != nil {
		log.Println(log.ErrorLog, err)
	}
	defer rows.Close()
	for rows.Next() {
		var id int
		var name string
		err = rows.Scan(&id, &name)
		if err != nil {
			log.Println(log.ErrorLog, err)
		}
		fmt.Println(id, name)
	}
	err = rows.Err()
	if err != nil {
		log.Println(log.ErrorLog, err)
	}

	stmt, err = db.Prepare("select name from foo where id = ?")
	if err != nil {
		log.Println(log.ErrorLog, err)
	}
	defer stmt.Close()
	var name string
	err = stmt.QueryRow("3").Scan(&name)
	if err != nil {
		log.Println(log.ErrorLog, err)
	}
	fmt.Println(name)

	_, err = db.Exec("delete from foo")
	if err != nil {
		log.Println(log.ErrorLog, err)
	}

	_, err = db.Exec("insert into foo(id, name) values(1, 'foo'), (2, 'bar'), (3, 'baz')")
	if err != nil {
		log.Println(log.ErrorLog, err)
	}

	rows, err = db.Query("select id, name from foo")
	if err != nil {
		log.Println(log.ErrorLog, err)
	}
	defer rows.Close()
	for rows.Next() {
		var id int
		var name string
		err = rows.Scan(&id, &name)
		if err != nil {
			log.Println(log.ErrorLog, err)
		}
		fmt.Println(id, name)
	}
	err = rows.Err()
	if err != nil {
		log.Println(log.ErrorLog, err)
	}
}
