package gosqljson

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"
)

// QueryDbToArrayJSON - run the sql and return a a JSON string of array
func QueryDbToArrayJSON(db *sql.DB, theCase string, sqlStatement string, sqlParams ...interface{}) (string, error) {
	headers, data, err := QueryDbToArray(db, theCase, sqlStatement, sqlParams...)
	result := map[string]interface{}{
		"headers": headers,
		"data":    data,
	}
	jsonString, err := json.Marshal(result)
	return string(jsonString), err
}

// QueryDbToMapJSON - run the sql and return a JSON string of array of objects.
func QueryDbToMapJSON(db *sql.DB, theCase string, sqlStatement string, sqlParams ...interface{}) (string, error) {
	data, err := QueryDbToMap(db, theCase, sqlStatement, sqlParams...)
	jsonString, err := json.Marshal(data)
	return string(jsonString), err
}

// QueryDbToArray - headers, data, error
func QueryDbToArray(db *sql.DB, theCase string, sqlStatement string, sqlParams ...interface{}) ([]string, [][]string, error) {
	defer func() {
		if err := recover(); err != nil {
			fmt.Println(err)
		}
	}()

	data := [][]string{}
	headers := []string{}
        db.Exec("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;START TRANSACTION;")
	rows, err := db.Query(sqlStatement, sqlParams...)
	if err != nil {
		fmt.Println("Error executing: ", sqlStatement)
		return headers, data, err
	}
	cols, _ := rows.Columns()
	if theCase == "lower" {
		colsLower := make([]string, len(cols))
		for i, v := range cols {
			colsLower[i] = strings.ToLower(v)
		}
		headers = colsLower
	} else if theCase == "upper" {
		colsUpper := make([]string, len(cols))
		for i, v := range cols {
			colsUpper[i] = strings.ToUpper(v)
		}
		headers = colsUpper
	} else if theCase == "camel" {
		colsCamel := make([]string, len(cols))
		for i, v := range cols {
			colsCamel[i] = toCamel(v)
		}
		headers = colsCamel
	} else {
		headers = cols
	}

	rawResult := make([][]byte, len(cols))

	dest := make([]interface{}, len(cols)) // A temporary interface{} slice
	for i := range rawResult {
		dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
	}

	for rows.Next() {
		result := make([]string, len(cols))
		rows.Scan(dest...)
		for i, raw := range rawResult {
			if raw == nil {
				result[i] = ""
			} else {
				result[i] = string(raw)
			}
		}
		data = append(data, result)
	}
        defer db.Exec("COMMIT;")
	return headers, data, nil
}

// QueryTxToArray - headers, data, error
func QueryTxToArray(tx *sql.Tx, theCase string, sqlStatement string, sqlParams ...interface{}) ([]string, [][]string, error) {
	defer func() {
		if err := recover(); err != nil {
			fmt.Println(err)
		}
	}()

	data := [][]string{}
	headers := []string{}
	rows, err := tx.Query(sqlStatement, sqlParams...)
	if err != nil {
		fmt.Println("Error executing: ", sqlStatement)
		return headers, data, err
	}
	cols, _ := rows.Columns()
	if theCase == "lower" {
		colsLower := make([]string, len(cols))
		for i, v := range cols {
			colsLower[i] = strings.ToLower(v)
		}
		headers = colsLower
	} else if theCase == "upper" {
		colsUpper := make([]string, len(cols))
		for i, v := range cols {
			colsUpper[i] = strings.ToUpper(v)
		}
		headers = colsUpper
	} else if theCase == "camel" {
		colsCamel := make([]string, len(cols))
		for i, v := range cols {
			colsCamel[i] = toCamel(v)
		}
		headers = colsCamel
	} else {
		headers = cols
	}

	rawResult := make([][]byte, len(cols))

	dest := make([]interface{}, len(cols)) // A temporary interface{} slice
	for i := range rawResult {
		dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
	}

	for rows.Next() {
		result := make([]string, len(cols))
		rows.Scan(dest...)
		for i, raw := range rawResult {
			if raw == nil {
				result[i] = ""
			} else {
				result[i] = string(raw)
			}
		}
		data = append(data, result)
	}
	return headers, data, nil
}

// QueryDbToMap - run sql and return an array of maps
func QueryDbToMap(db *sql.DB, theCase string, sqlStatement string, sqlParams ...interface{}) ([]map[string]string, error) {
	defer func() {
		if err := recover(); err != nil {
			fmt.Println(err)
		}
	}()

	results := []map[string]string{}
	rows, err := db.Query(sqlStatement, sqlParams...)
	if err != nil {
		fmt.Println("Error executing: ", sqlStatement)
		return results, err
	}
	cols, _ := rows.Columns()
	colsLower := make([]string, len(cols))
	colsCamel := make([]string, len(cols))

	if theCase == "lower" {
		for i, v := range cols {
			colsLower[i] = strings.ToLower(v)
		}
	} else if theCase == "upper" {
		for i, v := range cols {
			cols[i] = strings.ToUpper(v)
		}
	} else if theCase == "camel" {
		for i, v := range cols {
			colsCamel[i] = toCamel(v)
		}
	}

	rawResult := make([][]byte, len(cols))

	dest := make([]interface{}, len(cols)) // A temporary interface{} slice
	for i := range rawResult {
		dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
	}

	for rows.Next() {
		result := make(map[string]string, len(cols))
		rows.Scan(dest...)
		for i, raw := range rawResult {
			if raw == nil {
				if theCase == "lower" {
					result[colsLower[i]] = ""
				} else if theCase == "upper" {
					result[cols[i]] = ""
				} else if theCase == "camel" {
					result[colsCamel[i]] = ""
				} else {
					result[cols[i]] = ""
				}
			} else {
				if theCase == "lower" {
					result[colsLower[i]] = string(raw)
				} else if theCase == "upper" {
					result[cols[i]] = string(raw)
				} else if theCase == "camel" {
					result[colsCamel[i]] = string(raw)
				} else {
					result[cols[i]] = string(raw)
				}
			}
		}
		results = append(results, result)
	}
	return results, nil
}

// QueryTxToMap - run sql and return an array of maps
func QueryTxToMap(tx *sql.Tx, theCase string, sqlStatement string, sqlParams ...interface{}) ([]map[string]string, error) {
	defer func() {
		if err := recover(); err != nil {
			fmt.Println(err)
		}
	}()

	results := []map[string]string{}
	rows, err := tx.Query(sqlStatement, sqlParams...)
	if err != nil {
		fmt.Println("Error executing: ", sqlStatement)
		return results, err
	}
	cols, _ := rows.Columns()
	colsLower := make([]string, len(cols))
	colsCamel := make([]string, len(cols))

	if theCase == "lower" {
		for i, v := range cols {
			colsLower[i] = strings.ToLower(v)
		}
	} else if theCase == "upper" {
		for i, v := range cols {
			cols[i] = strings.ToUpper(v)
		}
	} else if theCase == "camel" {
		for i, v := range cols {
			colsCamel[i] = toCamel(v)
		}
	}

	rawResult := make([][]byte, len(cols))

	dest := make([]interface{}, len(cols)) // A temporary interface{} slice
	for i := range rawResult {
		dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
	}

	for rows.Next() {
		result := make(map[string]string, len(cols))
		rows.Scan(dest...)
		for i, raw := range rawResult {
			if raw == nil {
				if theCase == "lower" {
					result[colsLower[i]] = ""
				} else if theCase == "upper" {
					result[cols[i]] = ""
				} else if theCase == "camel" {
					result[colsCamel[i]] = ""
				} else {
					result[cols[i]] = ""
				}
			} else {
				if theCase == "lower" {
					result[colsLower[i]] = string(raw)
				} else if theCase == "upper" {
					result[cols[i]] = string(raw)
				} else if theCase == "camel" {
					result[colsCamel[i]] = string(raw)
				} else {
					result[cols[i]] = string(raw)
				}
			}
		}
		results = append(results, result)
	}
	return results, nil
}

// ExecDb - run the sql and returns rows affected.
func ExecDb(db *sql.DB, sqlStatement string, sqlParams ...interface{}) (int64, error) {
	defer func() {
		if err := recover(); err != nil {
			fmt.Println(err)
		}
	}()
	result, err := db.Exec(sqlStatement, sqlParams...)
	if err != nil {
		fmt.Println("Error executing: ", sqlStatement)
		fmt.Println(err)
		return 0, err
	}
	return result.RowsAffected()
}

// ExecTx - run the sql and returns rows affected.
func ExecTx(tx *sql.Tx, sqlStatement string, sqlParams ...interface{}) (int64, error) {
	defer func() {
		if err := recover(); err != nil {
			fmt.Println(err)
		}
	}()

	result, err := tx.Exec(sqlStatement, sqlParams...)
	if err != nil {
		fmt.Println("Error executing: ", sqlStatement)
		fmt.Println(err)
		return 0, err
	}
	return result.RowsAffected()
}

func toCamel(s string) (ret string) {
	s = strings.ToLower(s)
	a := strings.Split(s, "_")
	for i, v := range a {
		if i == 0 {
			ret += v
		} else {
			f := strings.ToUpper(string(v[0]))
			n := string(v[1:])
			ret += fmt.Sprint(f, n)
		}
	}
	return
}