package test

import (
	"encoding/binary"
	"fmt"
	"log"
	"math"
	"testing"
)

var TypeAllCreate = `CREATE TABLE typeALL_table (
    char_field CHAR(255),                      -- 固定长度为n的字符串
    varchar_field VARCHAR(255),                -- 可变长度字符串
    binary_field BINARY(255),                  -- 固定长度为n的二进制数据
    tinyint_field TINYINT,                     -- 精度为3，标度为0的有符号精确数值
    smallint_field SMALLINT,                   -- 精度为5，标度为0的有符号精确数值
    integer_field INTEGER,                     -- 精度为10，标度为0的有符号精确数值
    bigint_field BIGINT,                       -- 精度为19，标度为0的有符号精确数值
    float_field FLOAT,                         -- 单精度浮点数
    double_field DOUBLE,                       -- 双精度浮点数
    bool_field BOOL,                           -- 布尔类型
    numeric_field NUMERIC(20, 2)              -- 精度为p，标度为s的有符号精确数值
);`

func TestTypeAllCreate(t *testing.T) {
	_, err := db.Exec(TypeAllCreate)

	if err != nil {

		fmt.Printf("[ERROR] %s\n", err.Error())

	} else {
		fmt.Printf("%s ... ok\n", TypeAllCreate)
	}

}

var insSql1 = `INSERT INTO typeALL_table (
    char_field,
    varchar_field,
    binary_field,
    tinyint_field,
    smallint_field,
    integer_field,
    bigint_field,
    float_field,
    double_field,
    bool_field,
    numeric_field
) VALUES (
    'A',                                       -- char_field: 固定长度为255的字符串，只插入一个字符，剩余填充空格
    'This is a varchar',                       -- varchar_field: 可变长度字符串
    0x010203,                                  -- binary_field: 固定长度为255的二进制数据，这里插入一个3字节的示例
    127,                                       -- tinyint_field: 范围在 -128 到 127 之间
    32767,                                     -- smallint_field: 范围在 -32768 到 32767 之间
    2147483647,                                -- integer_field: 范围在 -2147483648 到 2147483647 之间
    9223372036854775807,                       -- bigint_field: 范围在 -9223372036854775808 到 9223372036854775807 之间
    3.14,                                      -- float_field: 单精度浮点数
    2.718281828459045,                         -- double_field: 双精度浮点数
    true,                                      -- bool_field: 布尔类型，true 表示1，false 表示0
    123456789012345670.12                    -- numeric_field: 精度为20，标度为2的有符号精确数值
);`

func TestTypeAllInsert(t *testing.T) {

	_, err := db.Exec(insSql1)
	if err != nil {

		fmt.Printf("[ERROR] %s\n", err.Error())
		return
	}

	fmt.Printf("%s ... ok\n", Ins2)

	err = db.Close()
	if err != nil {

		fmt.Printf("[ERROR] %s\n", err.Error())
	}
}

type TypeAll struct {
	CharField     string
	VarcharField  string
	BinaryField   []byte
	TinyintField  int8
	SmallintField int16
	IntegerField  int32
	BigintField   int64
	FloatField    float32
	DoubleField   float64
	BoolField     bool
	NumericField  float64
}

func TestTypeAllSelect(t *testing.T) {

	rows, err := db.Query("SELECT char_field, varchar_field, binary_field, tinyint_field, smallint_field, integer_field, bigint_field, float_field, double_field, bool_field, numeric_field FROM typeALL_table")
	if err != nil {
		log.Fatalf("Failed to query database: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		var record TypeAll
		err := rows.Scan(&record.CharField, &record.VarcharField, &record.BinaryField, &record.TinyintField, &record.SmallintField, &record.IntegerField, &record.BigintField, &record.FloatField, &record.DoubleField, &record.BoolField, &record.NumericField)
		if err != nil {
			log.Fatalf("Failed to scan row: %v", err)
		}

		f := 2.718281828459045
		bits := math.Float64bits(f)             // 将 float64 转换为 uint64
		bytes := make([]byte, 8)                // 创建一个长度为 8 的字节切片
		binary.BigEndian.PutUint64(bytes, bits) // 将 uint64 转换为 []byte 并存储在 bytes 中

		var bb byte
		bb = byte(104)
		bytes[7] = bb
		fmt.Println("B", bytes)
		fl := math.Float64frombits(binary.BigEndian.Uint64(bytes))
		fmt.Println("fl", fl)
		fmt.Println("F", f)
		fmt.Printf("CharField: %s\n", record.CharField)
		fmt.Printf("VarcharField: %s\n", record.VarcharField)
		fmt.Printf("BinaryField: %v\n", record.BinaryField)
		fmt.Printf("TinyintField: %d\n", record.TinyintField)
		fmt.Printf("SmallintField: %d\n", record.SmallintField)
		fmt.Printf("IntegerField: %d\n", record.IntegerField)
		fmt.Printf("BigintField: %d\n", record.BigintField)
		fmt.Printf("FloatField: %f\n", record.FloatField)
		fmt.Printf("DoubleField: %g\n", record.DoubleField)

		fmt.Printf("BoolField: %t\n", record.BoolField)
		fmt.Printf("NumericField: %f\n", record.NumericField)
		fmt.Println("----------")
	}

	if err = rows.Err(); err != nil {
		log.Fatalf("Error occurred during row iteration: %v", err)
	}

	err = db.Close()
	if err != nil {

		fmt.Printf("[ERROR] %s\n", err.Error())
	}
}

// -----------------------stmt------------------------------
var insSql2 = `INSERT INTO typeALL_table (
    char_field,
    varchar_field,
    binary_field,
    tinyint_field,
    smallint_field,
    integer_field,
    bigint_field,
    float_field,
    double_field,
    bool_field,
    numeric_field
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

func TestTypeAllInsertStmt(t *testing.T) {

	stmt, err := db.Prepare(insSql2)
	if err != nil {
		t.Fatalf("Failed to prepare statement: %v", err)
	}
	defer stmt.Close()

	_, err = stmt.Exec(
		"A",                            // char_field: 固定长度为255的字符串，只插入一个字符，剩余填充空格
		"This is a varchar",            // varchar_field: 可变长度字符串
		[]byte{0x01, 0x02, 0x03},       // binary_field: 固定长度为255的二进制数据，这里插入一个3字节的示例
		int8(127),                      // tinyint_field: 范围在 -128 到 127 之间
		int16(32767),                   // smallint_field: 范围在 -32768 到 32767 之间
		int32(2147483647),              // integer_field: 范围在 -2147483648 到 2147483647 之间
		int64(9223372036854775807),     // bigint_field: 范围在 -9223372036854775808 到 9223372036854775807 之间
		float32(3.14),                  // float_field: 单精度浮点数
		float64(2.718281828459045),     // double_field: 双精度浮点数
		true,                           // bool_field: 布尔类型，true 表示1，false 表示0
		float64(123456789012345670.12), // numeric_field: 精度为20，标度为2的有符号精确数值
	)
	if err != nil {
		t.Fatalf("Failed to execute statement: %v", err)
	}

	err = db.Close()
	if err != nil {

		fmt.Printf("[ERROR] %s\n", err.Error())
	}
}

func TestTypeAllQueryStmt(t *testing.T) {
	querySql := `SELECT 
	char_field, 
	varchar_field, 
	binary_field, 
	tinyint_field, 
	smallint_field, 
	integer_field, 
	bigint_field, 
	float_field, 
	double_field, 
	bool_field, 
	numeric_field 
FROM typeALL_table`

	stmt, err := db.Prepare(querySql)
	if err != nil {
		t.Fatalf("Failed to prepare statement: %v", err)
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		t.Fatalf("Failed to execute query: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		var record TypeAll
		//var smallintBytes, floatBytes []byte

		err := rows.Scan(
			&record.CharField,
			&record.VarcharField,
			&record.BinaryField,
			&record.TinyintField,
			&record.SmallintField,
			&record.IntegerField,
			&record.BigintField,
			&record.FloatField,
			&record.DoubleField,
			&record.BoolField,
			&record.NumericField,
		)
		if err != nil {
			t.Fatalf("Failed to scan row: %v", err)
		}

		// 将 smallintBytes 转换为 int16
		// if len(smallintBytes) == 2 {
		// 	record.SmallintField = int16(binary.BigEndian.Uint16(smallintBytes))
		// }

		// //将 floatBytes 转换为 float32
		// if len(floatBytes) == 4 {
		// 	bits := binary.BigEndian.Uint32(floatBytes)
		// 	record.FloatField = math.Float32frombits(bits)
		// }

		fmt.Printf("CharField: %s\n", record.CharField)
		fmt.Printf("VarcharField: %s\n", record.VarcharField)
		fmt.Printf("BinaryField: %v\n", record.BinaryField)
		fmt.Printf("TinyintField: %d\n", record.TinyintField)
		fmt.Printf("SmallintField: %d\n", record.SmallintField)
		fmt.Printf("IntegerField: %d\n", record.IntegerField)
		fmt.Printf("BigintField: %d\n", record.BigintField)
		fmt.Printf("FloatField: %f\n", record.FloatField)
		fmt.Printf("DoubleField: %f\n", record.DoubleField)
		fmt.Printf("BoolField: %t\n", record.BoolField)
		fmt.Printf("NumericField: %f\n", record.NumericField)
		fmt.Println("----------")
	}

	if err = rows.Err(); err != nil {
		t.Fatalf("Error occurred during row iteration: %v", err)
	}
}
