package app

import (
	"bufio"
	"bytes"
	"context"
	"encoding/json"
	"os"
	"strings"
	"testing"
	"time"

	"GoNavi-Wails/internal/connection"
)

type fakeExportQueryDB struct {
	data []map[string]interface{}
	cols []string
	err  error
	defs []connection.ColumnDefinition

	lastQuery          string
	lastContextTimeout time.Duration
	hasContextDeadline bool
}

func (f *fakeExportQueryDB) Connect(config connection.ConnectionConfig) error { return nil }
func (f *fakeExportQueryDB) Close() error                                     { return nil }
func (f *fakeExportQueryDB) Ping() error                                      { return nil }
func (f *fakeExportQueryDB) Query(query string) ([]map[string]interface{}, []string, error) {
	f.lastQuery = query
	return f.data, f.cols, f.err
}
func (f *fakeExportQueryDB) QueryContext(ctx context.Context, query string) ([]map[string]interface{}, []string, error) {
	f.lastQuery = query
	if deadline, ok := ctx.Deadline(); ok {
		f.hasContextDeadline = true
		f.lastContextTimeout = time.Until(deadline)
	}
	return f.data, f.cols, f.err
}
func (f *fakeExportQueryDB) Exec(query string) (int64, error) { return 0, nil }
func (f *fakeExportQueryDB) GetDatabases() ([]string, error)  { return nil, nil }
func (f *fakeExportQueryDB) GetTables(dbName string) ([]string, error) {
	return nil, nil
}
func (f *fakeExportQueryDB) GetCreateStatement(dbName, tableName string) (string, error) {
	return "", nil
}
func (f *fakeExportQueryDB) GetColumns(dbName, tableName string) ([]connection.ColumnDefinition, error) {
	return f.defs, nil
}
func (f *fakeExportQueryDB) GetAllColumns(dbName string) ([]connection.ColumnDefinitionWithTable, error) {
	return nil, nil
}
func (f *fakeExportQueryDB) GetIndexes(dbName, tableName string) ([]connection.IndexDefinition, error) {
	return nil, nil
}
func (f *fakeExportQueryDB) GetForeignKeys(dbName, tableName string) ([]connection.ForeignKeyDefinition, error) {
	return nil, nil
}
func (f *fakeExportQueryDB) GetTriggers(dbName, tableName string) ([]connection.TriggerDefinition, error) {
	return nil, nil
}

func TestFormatExportCellText_FloatNoScientificNotation(t *testing.T) {
	got := formatExportCellText(1.445663e+06)
	if strings.Contains(strings.ToLower(got), "e+") || strings.Contains(strings.ToLower(got), "e-") {
		t.Fatalf("不应输出科学计数法，got=%q", got)
	}
	if got != "1445663" {
		t.Fatalf("浮点整值导出异常，want=%q got=%q", "1445663", got)
	}
}

func TestWriteRowsToFile_Markdown_NumberKeepPlainText(t *testing.T) {
	f, err := os.CreateTemp("", "gonavi-export-*.md")
	if err != nil {
		t.Fatalf("创建临时文件失败: %v", err)
	}
	defer os.Remove(f.Name())
	defer f.Close()

	data := []map[string]interface{}{
		{"id": 1.445663e+06},
	}
	columns := []string{"id"}

	if err := writeRowsToFile(f, data, columns, "md"); err != nil {
		t.Fatalf("写入 md 失败: %v", err)
	}

	contentBytes, err := os.ReadFile(f.Name())
	if err != nil {
		t.Fatalf("读取 md 失败: %v", err)
	}
	content := string(contentBytes)
	if strings.Contains(strings.ToLower(content), "e+") || strings.Contains(strings.ToLower(content), "e-") {
		t.Fatalf("md 导出包含科学计数法: %s", content)
	}
	if !strings.Contains(content, "| 1445663 |") {
		t.Fatalf("md 导出未保留整数字面量，content=%s", content)
	}
}

func TestWriteRowsToFile_JSON_NumberKeepPlainText(t *testing.T) {
	f, err := os.CreateTemp("", "gonavi-export-*.json")
	if err != nil {
		t.Fatalf("创建临时文件失败: %v", err)
	}
	defer os.Remove(f.Name())
	defer f.Close()

	data := []map[string]interface{}{
		{"id": 1.445663e+06},
	}
	columns := []string{"id"}

	if err := writeRowsToFile(f, data, columns, "json"); err != nil {
		t.Fatalf("写入 json 失败: %v", err)
	}

	contentBytes, err := os.ReadFile(f.Name())
	if err != nil {
		t.Fatalf("读取 json 失败: %v", err)
	}
	content := string(contentBytes)
	if strings.Contains(strings.ToLower(content), "e+") || strings.Contains(strings.ToLower(content), "e-") {
		t.Fatalf("json 导出包含科学计数法: %s", content)
	}

	var decoded []map[string]json.Number
	decoder := json.NewDecoder(bytes.NewReader(contentBytes))
	decoder.UseNumber()
	if err := decoder.Decode(&decoded); err != nil {
		t.Fatalf("解析导出 json 失败: %v", err)
	}
	if len(decoded) != 1 {
		t.Fatalf("导出行数异常，got=%d", len(decoded))
	}
	if decoded[0]["id"].String() != "1445663" {
		t.Fatalf("json 数值格式异常，want=1445663 got=%s", decoded[0]["id"].String())
	}
}

func TestNormalizeExportJSONValue_LocalDateTimeString_NoTimezoneShift(t *testing.T) {
	originalLocal := time.Local
	time.Local = time.FixedZone("UTC+8", 8*60*60)
	defer func() { time.Local = originalLocal }()

	got := normalizeExportJSONValue("2026-04-07 18:44:32")
	if got != "2026-04-07 18:44:32" {
		t.Fatalf("本地无时区字符串不应发生时区偏移，want=%q got=%v", "2026-04-07 18:44:32", got)
	}
}

func TestFormatExportCellText_TimeValue_KeepWallClock(t *testing.T) {
	originalLocal := time.Local
	time.Local = time.FixedZone("UTC+8", 8*60*60)
	defer func() { time.Local = originalLocal }()

	utc := time.Date(2026, 4, 7, 10, 44, 32, 0, time.UTC)
	got := formatExportCellText(utc)
	if got != "2026-04-07 10:44:32" {
		t.Fatalf("time.Time 导出应保持原始钟表时间，want=%q got=%q", "2026-04-07 10:44:32", got)
	}
}

func TestParseTemporalString_LocalDateTime_NoTimezoneShift(t *testing.T) {
	originalLocal := time.Local
	time.Local = time.FixedZone("UTC+8", 8*60*60)
	defer func() { time.Local = originalLocal }()

	parsed, ok := parseTemporalString("2026-04-07 18:44:32")
	if !ok {
		t.Fatal("parseTemporalString 应成功解析本地日期时间")
	}
	if parsed.Local().Format("2006-01-02 15:04:05") != "2026-04-07 18:44:32" {
		t.Fatalf("无时区时间解析后不应发生偏移，got=%q", parsed.Local().Format("2006-01-02 15:04:05"))
	}
}

func TestParseTemporalString_RFC3339_KeepWallClock(t *testing.T) {
	originalLocal := time.Local
	time.Local = time.FixedZone("UTC+8", 8*60*60)
	defer func() { time.Local = originalLocal }()

	parsed, ok := parseTemporalString("2026-04-07T10:44:32Z")
	if !ok {
		t.Fatal("parseTemporalString 应成功解析 RFC3339")
	}
	if parsed.Format("2006-01-02 15:04:05") != "2026-04-07 10:44:32" {
		t.Fatalf("RFC3339 解析后应保持原始钟表时间，got=%q", parsed.Format("2006-01-02 15:04:05"))
	}
}

func TestNormalizeExportJSONValue_TimeValue_KeepWallClock(t *testing.T) {
	originalLocal := time.Local
	time.Local = time.FixedZone("UTC+8", 8*60*60)
	defer func() { time.Local = originalLocal }()

	utc := time.Date(2026, 4, 7, 18, 44, 32, 0, time.UTC)
	got := normalizeExportJSONValue(utc)
	if got != "2026-04-07 18:44:32" {
		t.Fatalf("JSON 导出 time.Time 应保持原始钟表时间，want=%q got=%v", "2026-04-07 18:44:32", got)
	}
}

func TestQueryDataForExport_UsesMinimumTimeout(t *testing.T) {
	fake := &fakeExportQueryDB{
		data: []map[string]interface{}{{"v": 1}},
		cols: []string{"v"},
	}
	_, _, err := queryDataForExport(fake, connection.ConnectionConfig{Timeout: 10}, "SELECT 1")
	if err != nil {
		t.Fatalf("queryDataForExport 返回错误: %v", err)
	}
	if !fake.hasContextDeadline {
		t.Fatal("queryDataForExport 应设置 context deadline")
	}
	if fake.lastQuery != "SELECT 1" {
		t.Fatalf("queryDataForExport 查询语句异常，want=%q got=%q", "SELECT 1", fake.lastQuery)
	}
	lowerBound := minExportQueryTimeout - 5*time.Second
	upperBound := minExportQueryTimeout + 5*time.Second
	if fake.lastContextTimeout < lowerBound || fake.lastContextTimeout > upperBound {
		t.Fatalf("导出最小超时异常，want≈%s got=%s", minExportQueryTimeout, fake.lastContextTimeout)
	}
}

func TestQueryDataForExport_UsesLargerConfiguredTimeout(t *testing.T) {
	fake := &fakeExportQueryDB{
		data: []map[string]interface{}{{"v": 1}},
		cols: []string{"v"},
	}
	_, _, err := queryDataForExport(fake, connection.ConnectionConfig{Timeout: 900}, "SELECT 1")
	if err != nil {
		t.Fatalf("queryDataForExport 返回错误: %v", err)
	}
	if !fake.hasContextDeadline {
		t.Fatal("queryDataForExport 应设置 context deadline")
	}
	expected := 900 * time.Second
	lowerBound := expected - 5*time.Second
	upperBound := expected + 5*time.Second
	if fake.lastContextTimeout < lowerBound || fake.lastContextTimeout > upperBound {
		t.Fatalf("导出配置超时异常，want≈%s got=%s", expected, fake.lastContextTimeout)
	}
}

func TestGetExportQueryTimeout_ClickHouseUsesLongerMinimum(t *testing.T) {
	timeout := getExportQueryTimeout(connection.ConnectionConfig{
		Type:    "clickhouse",
		Timeout: 30,
	})
	if timeout != minClickHouseExportQueryTimeout {
		t.Fatalf("clickhouse 导出超时下限异常，want=%s got=%s", minClickHouseExportQueryTimeout, timeout)
	}
}

func TestGetExportQueryTimeout_CustomClickHouseUsesLongerMinimum(t *testing.T) {
	timeout := getExportQueryTimeout(connection.ConnectionConfig{
		Type:    "custom",
		Driver:  "clickhouse",
		Timeout: 30,
	})
	if timeout != minClickHouseExportQueryTimeout {
		t.Fatalf("custom clickhouse 导出超时下限异常，want=%s got=%s", minClickHouseExportQueryTimeout, timeout)
	}
}

func TestLooksLikeSelectOrWith_AllowsInnerJoinQueryAfterLeadingComments(t *testing.T) {
	query := `
-- query result export
/* generated by query editor */
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
`

	if !looksLikeSelectOrWith(query) {
		t.Fatalf("带前置注释的 INNER JOIN 查询应允许导出，query=%q", query)
	}
}

func TestWriteRowsToFile_HTML_EscapeAndStyle(t *testing.T) {
	f, err := os.CreateTemp("", "gonavi-export-*.html")
	if err != nil {
		t.Fatalf("创建临时文件失败: %v", err)
	}
	defer os.Remove(f.Name())
	defer f.Close()

	data := []map[string]interface{}{
		{
			"name":     "<script>alert(1)</script>",
			"note":     "line1\nline2",
			"nullable": nil,
		},
	}
	columns := []string{"name", "note", "nullable"}

	if err := writeRowsToFile(f, data, columns, "html"); err != nil {
		t.Fatalf("写入 html 失败: %v", err)
	}

	contentBytes, err := os.ReadFile(f.Name())
	if err != nil {
		t.Fatalf("读取 html 失败: %v", err)
	}
	content := string(contentBytes)

	if !strings.Contains(content, "<!DOCTYPE html>") {
		t.Fatalf("html 导出缺少 doctype: %s", content)
	}
	if !strings.Contains(content, "position: sticky") {
		t.Fatalf("html 导出缺少表头吸顶样式: %s", content)
	}
	if !strings.Contains(content, "tbody tr:nth-child(even)") {
		t.Fatalf("html 导出缺少斑马纹样式: %s", content)
	}
	if !strings.Contains(content, "&lt;script&gt;alert(1)&lt;/script&gt;") {
		t.Fatalf("html 导出未进行 XSS 转义: %s", content)
	}
	if strings.Contains(content, "<script>alert(1)</script>") {
		t.Fatalf("html 导出包含未转义脚本: %s", content)
	}
	if !strings.Contains(content, "line1<br>line2") {
		t.Fatalf("html 导出换行未转为 <br>: %s", content)
	}
	if !strings.Contains(content, "<td>NULL</td>") {
		t.Fatalf("html 导出空值显示异常: %s", content)
	}
}

func TestWriteRowsToFile_HTML_EscapeHeader(t *testing.T) {
	f, err := os.CreateTemp("", "gonavi-export-*.html")
	if err != nil {
		t.Fatalf("创建临时文件失败: %v", err)
	}
	defer os.Remove(f.Name())
	defer f.Close()

	columnName := "<b>name</b>"
	data := []map[string]interface{}{{columnName: "ok"}}
	if err := writeRowsToFile(f, data, []string{columnName}, "html"); err != nil {
		t.Fatalf("写入 html 失败: %v", err)
	}
	contentBytes, _ := os.ReadFile(f.Name())
	content := string(contentBytes)
	if !strings.Contains(content, "<th>&lt;b&gt;name&lt;/b&gt;</th>") || strings.Contains(content, "<th><b>name</b></th>") {
		t.Fatalf("html 表头未正确转义: %s", content)
	}
}

func TestFormatImportSQLValue_NormalizesTimestampWithoutTimezone(t *testing.T) {
	got := formatImportSQLValue("postgres", "timestamp without time zone", "2026-01-21T18:32:26+08:00")
	if got != "'2026-01-21 18:32:26'" {
		t.Fatalf("时间字面量归一化异常，want=%q got=%q", "'2026-01-21 18:32:26'", got)
	}
}

func TestFormatImportSQLValue_LeavesTextLiteralUntouched(t *testing.T) {
	got := formatImportSQLValue("postgres", "text", "2026-01-21T18:32:26+08:00")
	if got != "'2026-01-21T18:32:26+08:00'" {
		t.Fatalf("文本字段不应被归一化，want=%q got=%q", "'2026-01-21T18:32:26+08:00'", got)
	}
}

func TestFormatImportSQLValue_PostgresBooleanColumnUsesBooleanLiteral(t *testing.T) {
	cases := []struct {
		name       string
		dbType     string
		columnType string
		value      interface{}
		want       string
	}{
		{name: "postgres bool true", dbType: "postgres", columnType: "boolean", value: true, want: "true"},
		{name: "postgres bool false", dbType: "postgres", columnType: "bool", value: false, want: "false"},
		{name: "pg catalog bool string", dbType: "postgres", columnType: "pg_catalog.bool", value: "t", want: "true"},
		{name: "highgo boolean bytes", dbType: "highgo", columnType: "boolean", value: []byte("0"), want: "false"},
		{name: "mysql keeps numeric bool", dbType: "mysql", columnType: "tinyint(1)", value: true, want: "1"},
	}

	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got := formatImportSQLValue(tc.dbType, tc.columnType, tc.value)
			if got != tc.want {
				t.Fatalf("布尔字面量异常，want=%q got=%q", tc.want, got)
			}
		})
	}
}

func TestDumpTableSQL_PostgresBooleanBackupUsesBooleanLiterals(t *testing.T) {
	fake := &fakeExportQueryDB{
		data: []map[string]interface{}{
			{"active": true, "archived": false},
		},
		cols: []string{"active", "archived"},
		defs: []connection.ColumnDefinition{
			{Name: "active", Type: "boolean"},
			{Name: "archived", Type: "bool"},
		},
	}
	var buf bytes.Buffer
	writer := bufio.NewWriter(&buf)

	err := dumpTableSQL(
		writer,
		fake,
		connection.ConnectionConfig{Type: "postgres"},
		"public",
		"orders",
		false,
		true,
		map[string]string{},
	)
	if err != nil {
		t.Fatalf("dumpTableSQL 返回错误: %v", err)
	}
	if err := writer.Flush(); err != nil {
		t.Fatalf("flush 导出 SQL 失败: %v", err)
	}

	content := buf.String()
	if !strings.Contains(content, `INSERT INTO "public"."orders" ("active", "archived") VALUES (true, false);`) {
		t.Fatalf("PostgreSQL bool 备份应使用 true/false 字面量，content=%s", content)
	}
	if strings.Contains(content, "VALUES (1, 0)") {
		t.Fatalf("PostgreSQL bool 备份不应输出数字布尔值，content=%s", content)
	}
}
