package test

import (
	"fmt"
	"log"
	"testing"
	"time"
)

var timeCreate = `CREATE TABLE example_table (
    time_field TIME,                           -- 时间数据类型，时分秒字段
    datetime_field DATETIME,                   -- 时间戳数据类型，年月日时分秒字段
    date_field DATE,                           -- 日期数据类型，年月日字段
    time_with_zone_field TIME WITH TIME ZONE,  -- 时间数据类型，时分秒，时区字段
    datetime_with_zone_field TIMESTAMP WITH TIME ZONE, -- 时间戳数据类型，年月日时分秒,时区字段
    interval_year_field INTERVAL YEAR,         -- 年间隔
    interval_month_field INTERVAL MONTH,       -- 月间隔
    interval_day_field INTERVAL DAY,           -- 日间隔
    interval_hour_field INTERVAL HOUR,         -- 小时间隔
    interval_minute_field INTERVAL MINUTE,     -- 分钟间隔
    interval_second_field INTERVAL SECOND,     -- 秒间隔
    interval_day_to_hour_field INTERVAL DAY TO HOUR,    -- 日时间隔
    interval_day_to_minute_field INTERVAL DAY TO MINUTE, -- 日时分间隔
    interval_day_to_second_field INTERVAL DAY TO SECOND, -- 日时分秒间隔
    interval_hour_to_minute_field INTERVAL HOUR TO MINUTE, -- 时分间隔
    interval_hour_to_second_field INTERVAL HOUR TO SECOND, -- 时分秒间隔
    interval_minute_to_second_field INTERVAL MINUTE TO SECOND -- 分秒间隔
);
`

func TestTypeTimeCreate(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 TypeTimeInsert = `INSERT INTO example_table (
	time_field,
	datetime_field,
	date_field,
	time_with_zone_field,
	datetime_with_zone_field,
	interval_year_field,
	interval_month_field,
	interval_day_field,
	interval_hour_field,
	interval_minute_field,
	interval_second_field,
	interval_day_to_hour_field,
	interval_day_to_minute_field,
	interval_day_to_second_field,
	interval_hour_to_minute_field,
	interval_hour_to_second_field,
	interval_minute_to_second_field
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

func TestTypeTimeInsert(t *testing.T) {
	stmt, err := db.Prepare(TypeTimeInsert)
	if err != nil {
		log.Fatalf("Failed to prepare statement: %v", err)
	}
	defer stmt.Close()
	// 创建一个 time.Duration 表示 90 分钟
	ninetyMinutes := time.Duration(90) * time.Minute
	fmt.Println("Ninety Minutes:", ninetyMinutes)
	currentTime := time.Now()
	fmt.Println("Current Time:", currentTime)

	_, err = stmt.Exec(
		"12:34:56",            // time_field: 时分秒
		"2024-07-30 14:32:59", // datetime_field: 年月日时分秒
		"2024-07-30",          // date_field: 年月日
		"12:34:56+02:00",      // time_with_zone_field: 时分秒，时区字段
		currentTime,           // datetime_with_zone_field: 年月日时分秒，时区字段 //"2024-07-30 14:32:59+02:00"
		1,                     // interval_year_field: 年间隔
		2,                     // interval_month_field: 月间隔
		3,                     // interval_day_field: 日间隔
		ninetyMinutes,         // interval_hour_field: 小时间隔
		5,                     // interval_minute_field: 分钟间隔
		6,                     // interval_second_field: 秒间隔
		"3 4",                 // interval_day_to_hour_field: 日时间隔
		"3 4:5",               // interval_day_to_minute_field: 日时分间隔
		"4:5:6",               // interval_day_to_second_field: 日时分秒间隔
		"4:5",                 // interval_hour_to_minute_field: 时分间隔
		"4:5:6",               // interval_hour_to_second_field: 时分秒间隔
		"5:6",                 // interval_minute_to_second_field: 分秒间隔
	)
	if err != nil {
		log.Fatalf("Failed to execute statement: %v", err)
	}

	fmt.Println("Data inserted successfully")

}

type ExampleRecord struct {
	TimeField                   time.Time
	DatetimeField               time.Time
	DateField                   time.Time
	TimeWithZoneField           time.Time
	DatetimeWithZoneField       time.Time
	IntervalYearField           string
	IntervalMonthField          string
	IntervalDayField            string
	IntervalHourField           string
	IntervalMinuteField         string
	IntervalSecondField         string
	IntervalDayToHourField      string
	IntervalDayToMinuteField    string
	IntervalDayToSecondField    string
	IntervalHourToMinuteField   string
	IntervalHourToSecondField   string
	IntervalMinuteToSecondField string
}

func TestTypeTimeQuery(t *testing.T) {
	query := `SELECT 
	time_field, 
	datetime_field, 
	date_field, 
	time_with_zone_field, 
	datetime_with_zone_field,
	interval_year_field,
	interval_month_field,
	interval_day_field,
	interval_hour_field,
	interval_minute_field,
	interval_second_field,
	interval_day_to_hour_field,
	interval_day_to_minute_field,
	interval_day_to_second_field,
	interval_hour_to_minute_field,
	interval_hour_to_second_field,
	interval_minute_to_second_field
FROM example_table`

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

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

	for rows.Next() {
		var example ExampleRecord
		err := rows.Scan(
			&example.TimeField,
			&example.DatetimeField,
			&example.DateField,
			&example.TimeWithZoneField,
			&example.DatetimeWithZoneField,
			&example.IntervalYearField,
			&example.IntervalMonthField,
			&example.IntervalDayField,
			&example.IntervalHourField,
			&example.IntervalMinuteField,
			&example.IntervalSecondField,
			&example.IntervalDayToHourField,
			&example.IntervalDayToMinuteField,
			&example.IntervalDayToSecondField,
			&example.IntervalHourToMinuteField,
			&example.IntervalHourToSecondField,
			&example.IntervalMinuteToSecondField,
		)
		if err != nil {
			log.Fatalf("Failed to scan row: %v", err)
		}

		fmt.Printf("ExampleRecord:\n"+
			"TimeField: %v\n"+
			"DatetimeField: %v\n"+
			"DateField: %v\n"+
			"TimeWithZoneField: %v\n"+
			"DatetimeWithZoneField: %v\n"+
			"IntervalYearField: %s\n"+
			"IntervalMonthField: %s\n"+
			"IntervalDayField: %s\n"+
			"IntervalHourField: %s\n"+
			"IntervalMinuteField: %s\n"+
			"IntervalSecondField: %s\n"+
			"IntervalDayToHourField: %s\n"+
			"IntervalDayToMinuteField: %s\n"+
			"IntervalDayToSecondField: %s\n"+
			"IntervalHourToMinuteField: %s\n"+
			"IntervalHourToSecondField: %s\n"+
			"IntervalMinuteToSecondField: %s\n",
			example.TimeField,
			example.DatetimeField,
			example.DateField,
			example.TimeWithZoneField,
			example.DatetimeWithZoneField,
			example.IntervalYearField,
			example.IntervalMonthField,
			example.IntervalDayField,
			example.IntervalHourField,
			example.IntervalMinuteField,
			example.IntervalSecondField,
			example.IntervalDayToHourField,
			example.IntervalDayToMinuteField,
			example.IntervalDayToSecondField,
			example.IntervalHourToMinuteField,
			example.IntervalHourToSecondField,
			example.IntervalMinuteToSecondField)

	}

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