package app

import (
	"bufio"
	"context"
	"encoding/csv"
	"encoding/json"
	"fmt"
	"html"
	"io"
	"math"
	"os"
	"path/filepath"
	"reflect"
	"regexp"
	"sort"
	"strconv"
	"strings"
	"time"

	"GoNavi-Wails/internal/connection"
	"GoNavi-Wails/internal/db"
	"GoNavi-Wails/internal/logger"
	"GoNavi-Wails/internal/utils"

	"github.com/wailsapp/wails/v2/pkg/runtime"
	"github.com/xuri/excelize/v2"
)

const minExportQueryTimeout = 5 * time.Minute
const minClickHouseExportQueryTimeout = 2 * time.Hour
const maxSQLFileSizeBytes int64 = 50 * 1024 * 1024
const sqlFileBatchMaxStatements = 1000
const sqlFileBatchMaxBytes = 4 * 1024 * 1024
const sqlFileProgressStatementInterval = 100
const sqlFileProgressTimeInterval = time.Second
const defaultAppLogTailLineLimit = 80
const maxAppLogTailLineLimit = 200
const appLogTailReadWindowBytes int64 = 256 * 1024

var mysqlCreateViewPrefixPattern = regexp.MustCompile(`(?is)^\s*create\s+(?:algorithm\s*=\s*\w+\s+)?(?:definer\s*=\s*(?:` + "`[^`]+`" + `|\S+)\s*@\s*(?:` + "`[^`]+`" + `|\S+)\s+)?(?:sql\s+security\s+(?:definer|invoker)\s+)?view\s+`)

type sqlFileExecutionProgress struct {
	Status     string
	Executed   int
	Failed     int
	Total      int
	BytesRead  int64
	CurrentSQL string
	Error      string
}

type sqlFileExecutionOptions struct {
	DBType             string
	BatchMaxStatements int
	BatchMaxBytes      int
	OnProgress         func(sqlFileExecutionProgress)
}

type sqlFileExecutionResult struct {
	Executed int
	Failed   int
	Errors   []string
}

type sqlFilePendingStatement struct {
	Index int
	SQL   string
}

type sqlFileStatementExecer interface {
	Exec(query string) (int64, error)
}

type sqlFileContextStatementExecer interface {
	ExecContext(ctx context.Context, query string) (int64, error)
}

type sqlFileBatchStatementExecer interface {
	ExecBatchContext(ctx context.Context, query string) (int64, error)
}

type SQLDirectoryEntry struct {
	Name     string              `json:"name"`
	Path     string              `json:"path"`
	IsDir    bool                `json:"isDir"`
	Children []SQLDirectoryEntry `json:"children,omitempty"`
}

type appLogTailSnapshot struct {
	LogPath              string            `json:"logPath"`
	Keyword              string            `json:"keyword,omitempty"`
	RequestedLineLimit   int               `json:"requestedLineLimit"`
	ReturnedLineCount    int               `json:"returnedLineCount"`
	FileWindowTruncated  bool              `json:"fileWindowTruncated"`
	MatchedLinesTruncated bool             `json:"matchedLinesTruncated"`
	LevelBreakdown       map[string]int    `json:"levelBreakdown"`
	Lines                []string          `json:"lines"`
}

func normalizeSQLFileName(rawName string) (string, error) {
	name := strings.TrimSpace(rawName)
	if name == "" {
		return "", fmt.Errorf("SQL 文件名不能为空")
	}
	if strings.ContainsAny(name, `/\`) || name == "." || name == ".." {
		return "", fmt.Errorf("SQL 文件名不能包含路径分隔符")
	}
	if !strings.EqualFold(filepath.Ext(name), ".sql") {
		name += ".sql"
	}
	return name, nil
}

func normalizeSQLDirectoryName(rawName string) (string, error) {
	name := strings.TrimSpace(rawName)
	if name == "" {
		return "", fmt.Errorf("目录名不能为空")
	}
	if strings.ContainsAny(name, `/\`) || name == "." || name == ".." {
		return "", fmt.Errorf("目录名不能包含路径分隔符")
	}
	return name, nil
}

func normalizeSQLDirectoryPath(directoryPath string) (string, error) {
	target := strings.TrimSpace(directoryPath)
	if target == "" {
		return "", fmt.Errorf("目录路径不能为空")
	}
	if abs, err := filepath.Abs(target); err == nil {
		target = abs
	}
	info, err := os.Stat(target)
	if err != nil {
		return "", fmt.Errorf("无法读取目录信息: %w", err)
	}
	if !info.IsDir() {
		return "", fmt.Errorf("所选路径不是目录")
	}
	return target, nil
}

func normalizeExistingSQLDirectoryPath(directoryPath string) (string, os.FileInfo, error) {
	target := strings.TrimSpace(directoryPath)
	if target == "" {
		return "", nil, fmt.Errorf("目录路径不能为空")
	}
	if abs, err := filepath.Abs(target); err == nil {
		target = abs
	}
	info, err := os.Stat(target)
	if err != nil {
		return "", nil, fmt.Errorf("无法读取目录信息: %w", err)
	}
	if !info.IsDir() {
		return "", nil, fmt.Errorf("所选路径不是目录")
	}
	return target, info, nil
}

func normalizeExistingSQLFilePath(filePath string) (string, os.FileInfo, error) {
	target := strings.TrimSpace(filePath)
	if target == "" {
		return "", nil, fmt.Errorf("文件路径不能为空")
	}
	if abs, err := filepath.Abs(target); err == nil {
		target = abs
	}
	info, err := os.Stat(target)
	if err != nil {
		return "", nil, fmt.Errorf("无法读取文件信息: %w", err)
	}
	if info.IsDir() {
		return "", nil, fmt.Errorf("所选路径不是 SQL 文件")
	}
	if !strings.EqualFold(filepath.Ext(target), ".sql") {
		return "", nil, fmt.Errorf("仅支持 SQL 文件")
	}
	return target, info, nil
}

func createSQLFileInDirectory(directoryPath string, rawName string) connection.QueryResult {
	directory, err := normalizeSQLDirectoryPath(directoryPath)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	name, err := normalizeSQLFileName(rawName)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	target := filepath.Join(directory, name)
	if _, err := os.Stat(target); err == nil {
		return connection.QueryResult{Success: false, Message: "SQL 文件已存在"}
	} else if !os.IsNotExist(err) {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法读取文件信息: %v", err)}
	}
	if err := os.WriteFile(target, []byte(""), 0o644); err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法创建 SQL 文件: %v", err)}
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"filePath": target, "name": filepath.Base(target)}}
}

func createSQLDirectoryInDirectory(parentPath string, rawName string) connection.QueryResult {
	parent, err := normalizeSQLDirectoryPath(parentPath)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	name, err := normalizeSQLDirectoryName(rawName)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	target := filepath.Join(parent, name)
	if _, err := os.Stat(target); err == nil {
		return connection.QueryResult{Success: false, Message: "目录已存在"}
	} else if !os.IsNotExist(err) {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法读取目录信息: %v", err)}
	}
	if err := os.Mkdir(target, 0o755); err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法创建目录: %v", err)}
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"directoryPath": target, "name": filepath.Base(target)}}
}

func deleteSQLFileByPath(filePath string) connection.QueryResult {
	target, _, err := normalizeExistingSQLFilePath(filePath)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	if err := os.Remove(target); err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法删除 SQL 文件: %v", err)}
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"filePath": target}}
}

func deleteSQLDirectoryByPath(directoryPath string) connection.QueryResult {
	target, _, err := normalizeExistingSQLDirectoryPath(directoryPath)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	if err := os.Remove(target); err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法删除目录: %v（仅支持删除空目录）", err)}
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"directoryPath": target}}
}

func renameSQLFileByPath(filePath string, rawName string) connection.QueryResult {
	source, _, err := normalizeExistingSQLFilePath(filePath)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	name, err := normalizeSQLFileName(rawName)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	target := filepath.Join(filepath.Dir(source), name)
	if source == target {
		return connection.QueryResult{Success: true, Data: map[string]interface{}{"filePath": target, "name": filepath.Base(target)}}
	}
	if _, err := os.Stat(target); err == nil {
		return connection.QueryResult{Success: false, Message: "目标 SQL 文件已存在"}
	} else if !os.IsNotExist(err) {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法读取目标文件信息: %v", err)}
	}
	if err := os.Rename(source, target); err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法重命名 SQL 文件: %v", err)}
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"filePath": target, "name": filepath.Base(target)}}
}

func renameSQLDirectoryByPath(directoryPath string, rawName string) connection.QueryResult {
	source, _, err := normalizeExistingSQLDirectoryPath(directoryPath)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	name, err := normalizeSQLDirectoryName(rawName)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	target := filepath.Join(filepath.Dir(source), name)
	if source == target {
		return connection.QueryResult{Success: true, Data: map[string]interface{}{"directoryPath": target, "name": filepath.Base(target)}}
	}
	if _, err := os.Stat(target); err == nil {
		return connection.QueryResult{Success: false, Message: "目标目录已存在"}
	} else if !os.IsNotExist(err) {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法读取目标目录信息: %v", err)}
	}
	if err := os.Rename(source, target); err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法重命名目录: %v", err)}
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"directoryPath": target, "name": filepath.Base(target)}}
}

func normalizeDirectoryDialogPath(currentDir string) string {
	defaultDir := strings.TrimSpace(currentDir)
	if defaultDir == "" {
		if home, err := os.UserHomeDir(); err == nil {
			defaultDir = home
		}
	}
	if filepath.Ext(defaultDir) != "" {
		defaultDir = filepath.Dir(defaultDir)
	}
	if defaultDir != "" && !filepath.IsAbs(defaultDir) {
		if abs, err := filepath.Abs(defaultDir); err == nil {
			defaultDir = abs
		}
	}
	return defaultDir
}

func readSQLFileByPath(filePath string) connection.QueryResult {
	selection := strings.TrimSpace(filePath)
	if selection == "" {
		return connection.QueryResult{Success: false, Message: "文件路径不能为空"}
	}
	if abs, err := filepath.Abs(selection); err == nil {
		selection = abs
	}

	fi, err := os.Stat(selection)
	if err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法读取文件信息: %v", err)}
	}
	if fi.IsDir() {
		return connection.QueryResult{Success: false, Message: "所选路径不是 SQL 文件"}
	}

	if fi.Size() > maxSQLFileSizeBytes {
		sizeMB := float64(fi.Size()) / (1024 * 1024)
		return connection.QueryResult{
			Success: true,
			Data: map[string]interface{}{
				"isLargeFile": true,
				"filePath":    selection,
				"fileSize":    fi.Size(),
				"fileSizeMB":  fmt.Sprintf("%.1f", sizeMB),
			},
		}
	}

	content, err := os.ReadFile(selection)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	return connection.QueryResult{Success: true, Data: string(content)}
}

func readSQLFileWithMetadataByPath(filePath string) connection.QueryResult {
	result := readSQLFileByPath(filePath)
	if !result.Success {
		return result
	}
	if data, ok := result.Data.(map[string]interface{}); ok {
		return connection.QueryResult{Success: true, Data: data}
	}
	selection := strings.TrimSpace(filePath)
	if abs, err := filepath.Abs(selection); err == nil {
		selection = abs
	}
	return connection.QueryResult{
		Success: true,
		Data: map[string]interface{}{
			"content":  result.Data,
			"filePath": selection,
			"name":     filepath.Base(selection),
		},
	}
}

func writeSQLFileByPath(filePath string, content string) connection.QueryResult {
	target := strings.TrimSpace(filePath)
	if target == "" {
		return connection.QueryResult{Success: false, Message: "文件路径不能为空"}
	}
	if abs, err := filepath.Abs(target); err == nil {
		target = abs
	}

	info, err := os.Stat(target)
	if err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法读取文件信息: %v", err)}
	}
	if info.IsDir() {
		return connection.QueryResult{Success: false, Message: "所选路径不是 SQL 文件"}
	}

	if err := os.WriteFile(target, []byte(content), info.Mode().Perm()); err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法写入 SQL 文件: %v", err)}
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"filePath": target}}
}

func normalizeSQLExportDefaultFilename(rawName string) string {
	name := strings.TrimSpace(rawName)
	if name == "" {
		name = "query"
	}
	if idx := strings.LastIndexAny(name, `/\`); idx >= 0 {
		name = name[idx+1:]
	}
	if name == "." || name == string(filepath.Separator) {
		name = "query"
	}
	name = strings.NewReplacer(
		"/", "_",
		"\\", "_",
		":", "_",
		"*", "_",
		"?", "_",
		"\"", "_",
		"<", "_",
		">", "_",
		"|", "_",
	).Replace(strings.TrimSpace(name))
	if name == "" {
		name = "query"
	}
	if !strings.EqualFold(filepath.Ext(name), ".sql") {
		name += ".sql"
	}
	return name
}

func normalizeSQLExportTargetPath(filePath string) string {
	target := strings.TrimSpace(filePath)
	if target == "" {
		return ""
	}
	if !strings.EqualFold(filepath.Ext(target), ".sql") {
		target += ".sql"
	}
	if abs, err := filepath.Abs(target); err == nil {
		target = abs
	}
	return target
}

func writeExportedSQLFileByPath(filePath string, content string) connection.QueryResult {
	target := normalizeSQLExportTargetPath(filePath)
	if target == "" {
		return connection.QueryResult{Success: false, Message: "文件路径不能为空"}
	}
	if info, err := os.Stat(target); err == nil && info.IsDir() {
		return connection.QueryResult{Success: false, Message: "所选路径不是 SQL 文件"}
	} else if err != nil && !os.IsNotExist(err) {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法读取文件信息: %v", err)}
	}
	if err := os.WriteFile(target, []byte(content), 0o644); err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法写入 SQL 文件: %v", err)}
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"filePath": target}}
}

func buildSQLDirectoryEntries(directory string) ([]SQLDirectoryEntry, error) {
	entries, err := os.ReadDir(directory)
	if err != nil {
		return nil, err
	}

	result := make([]SQLDirectoryEntry, 0, len(entries))
	for _, entry := range entries {
		entryPath := filepath.Join(directory, entry.Name())
		if entry.IsDir() {
			children, childErr := buildSQLDirectoryEntries(entryPath)
			if childErr != nil {
				return nil, childErr
			}
			result = append(result, SQLDirectoryEntry{
				Name:     entry.Name(),
				Path:     entryPath,
				IsDir:    true,
				Children: children,
			})
			continue
		}
		if !strings.EqualFold(filepath.Ext(entry.Name()), ".sql") {
			continue
		}
		result = append(result, SQLDirectoryEntry{
			Name:  entry.Name(),
			Path:  entryPath,
			IsDir: false,
		})
	}

	sort.Slice(result, func(i, j int) bool {
		if result[i].IsDir != result[j].IsDir {
			return result[i].IsDir
		}
		return strings.ToLower(result[i].Name) < strings.ToLower(result[j].Name)
	})
	return result, nil
}

func (a *App) OpenSQLFile() connection.QueryResult {
	selection, err := runtime.OpenFileDialog(a.ctx, runtime.OpenDialogOptions{
		Title: "Select SQL File",
		Filters: []runtime.FileFilter{
			{
				DisplayName: "SQL Files (*.sql)",
				Pattern:     "*.sql",
			},
			{
				DisplayName: "All Files (*.*)",
				Pattern:     "*.*",
			},
		},
	})

	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	if selection == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}

	return readSQLFileWithMetadataByPath(selection)
}

func (a *App) SelectSQLDirectory(currentDir string) connection.QueryResult {
	selection, err := runtime.OpenDirectoryDialog(a.ctx, runtime.OpenDialogOptions{
		Title:            "选择 SQL 目录",
		DefaultDirectory: normalizeDirectoryDialogPath(currentDir),
	})
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	if strings.TrimSpace(selection) == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}
	if abs, err := filepath.Abs(selection); err == nil {
		selection = abs
	}
	name := filepath.Base(selection)
	if name == "." || name == string(filepath.Separator) {
		name = selection
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"path": selection, "name": name}}
}

func (a *App) ListSQLDirectory(directory string) connection.QueryResult {
	target := strings.TrimSpace(directory)
	if target == "" {
		return connection.QueryResult{Success: false, Message: "目录路径不能为空"}
	}
	if abs, err := filepath.Abs(target); err == nil {
		target = abs
	}

	info, err := os.Stat(target)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	if !info.IsDir() {
		return connection.QueryResult{Success: false, Message: "所选路径不是目录"}
	}

	entries, err := buildSQLDirectoryEntries(target)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	return connection.QueryResult{Success: true, Data: entries}
}

func (a *App) ReadSQLFile(filePath string) connection.QueryResult {
	return readSQLFileByPath(filePath)
}

func (a *App) ReadAppLogTail(lineLimit int, keyword string) connection.QueryResult {
	return readAppLogTailByPath(logger.Path(), lineLimit, keyword)
}

func (a *App) WriteSQLFile(filePath string, content string) connection.QueryResult {
	return writeSQLFileByPath(filePath, content)
}

func normalizeAppLogTailLineLimit(input int) int {
	if input <= 0 {
		return defaultAppLogTailLineLimit
	}
	if input > maxAppLogTailLineLimit {
		return maxAppLogTailLineLimit
	}
	return input
}

func readAppLogTailWindow(filePath string, maxBytes int64) ([]byte, bool, error) {
	f, err := os.Open(filePath)
	if err != nil {
		return nil, false, err
	}
	defer f.Close()

	fi, err := f.Stat()
	if err != nil {
		return nil, false, err
	}
	size := fi.Size()
	if size <= 0 {
		return []byte{}, false, nil
	}

	offset := int64(0)
	truncated := false
	if maxBytes > 0 && size > maxBytes {
		offset = size - maxBytes
		truncated = true
	}

	buf := make([]byte, size-offset)
	if _, err := f.ReadAt(buf, offset); err != nil && err != io.EOF {
		return nil, false, err
	}
	if !truncated {
		return buf, false, nil
	}

	text := string(buf)
	if idx := strings.IndexByte(text, '\n'); idx >= 0 && idx+1 < len(text) {
		return []byte(text[idx+1:]), true, nil
	}
	return []byte{}, true, nil
}

func buildAppLogLevelBreakdown(lines []string) map[string]int {
	breakdown := map[string]int{
		"INFO":  0,
		"WARN":  0,
		"ERROR": 0,
		"OTHER": 0,
	}
	for _, line := range lines {
		switch {
		case strings.Contains(line, "[INFO]"):
			breakdown["INFO"]++
		case strings.Contains(line, "[WARN]"):
			breakdown["WARN"]++
		case strings.Contains(line, "[ERROR]"):
			breakdown["ERROR"]++
		default:
			breakdown["OTHER"]++
		}
	}
	return breakdown
}

func readAppLogTailByPath(filePath string, lineLimit int, keyword string) connection.QueryResult {
	target := strings.TrimSpace(filePath)
	if target == "" {
		return connection.QueryResult{Success: false, Message: "当前未找到 GoNavi 日志文件"}
	}

	if _, err := os.Stat(target); err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	windowBytes, fileWindowTruncated, err := readAppLogTailWindow(target, appLogTailReadWindowBytes)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	normalizedKeyword := strings.ToLower(strings.TrimSpace(keyword))
	normalizedLineLimit := normalizeAppLogTailLineLimit(lineLimit)
	rawLines := strings.Split(strings.ReplaceAll(string(windowBytes), "\r\n", "\n"), "\n")
	lines := make([]string, 0, len(rawLines))
	for _, rawLine := range rawLines {
		line := strings.TrimSpace(rawLine)
		if line == "" {
			continue
		}
		lines = append(lines, line)
	}

	filteredLines := make([]string, 0, len(lines))
	for _, line := range lines {
		if normalizedKeyword != "" && !strings.Contains(strings.ToLower(line), normalizedKeyword) {
			continue
		}
		filteredLines = append(filteredLines, line)
	}

	matchedLinesTruncated := len(filteredLines) > normalizedLineLimit
	if matchedLinesTruncated {
		filteredLines = filteredLines[len(filteredLines)-normalizedLineLimit:]
	}

	snapshot := appLogTailSnapshot{
		LogPath:               target,
		Keyword:               strings.TrimSpace(keyword),
		RequestedLineLimit:    normalizedLineLimit,
		ReturnedLineCount:     len(filteredLines),
		FileWindowTruncated:   fileWindowTruncated,
		MatchedLinesTruncated: matchedLinesTruncated,
		LevelBreakdown:        buildAppLogLevelBreakdown(filteredLines),
		Lines:                 filteredLines,
	}
	return connection.QueryResult{Success: true, Data: snapshot}
}

func (a *App) CreateSQLFile(directoryPath string, name string) connection.QueryResult {
	return createSQLFileInDirectory(directoryPath, name)
}

func (a *App) CreateSQLDirectory(directoryPath string, name string) connection.QueryResult {
	return createSQLDirectoryInDirectory(directoryPath, name)
}

func (a *App) DeleteSQLFile(filePath string) connection.QueryResult {
	return deleteSQLFileByPath(filePath)
}

func (a *App) DeleteSQLDirectory(directoryPath string) connection.QueryResult {
	return deleteSQLDirectoryByPath(directoryPath)
}

func (a *App) RenameSQLFile(filePath string, name string) connection.QueryResult {
	return renameSQLFileByPath(filePath, name)
}

func (a *App) RenameSQLDirectory(directoryPath string, name string) connection.QueryResult {
	return renameSQLDirectoryByPath(directoryPath, name)
}

func (a *App) ExportSQLFile(defaultName string, content string) connection.QueryResult {
	filename, err := runtime.SaveFileDialog(a.ctx, runtime.SaveDialogOptions{
		Title:           "导出 SQL 文件",
		DefaultFilename: normalizeSQLExportDefaultFilename(defaultName),
		Filters: []runtime.FileFilter{
			{
				DisplayName: "SQL Files (*.sql)",
				Pattern:     "*.sql",
			},
			{
				DisplayName: "All Files (*.*)",
				Pattern:     "*.*",
			},
		},
	})
	if err != nil || strings.TrimSpace(filename) == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}
	result := writeExportedSQLFileByPath(filename, content)
	if result.Success {
		result.Message = "SQL 文件已导出"
	}
	return result
}

func normalizeSQLFileExecutionOptions(options sqlFileExecutionOptions) sqlFileExecutionOptions {
	if options.BatchMaxStatements <= 0 {
		options.BatchMaxStatements = sqlFileBatchMaxStatements
	}
	if options.BatchMaxBytes <= 0 {
		options.BatchMaxBytes = sqlFileBatchMaxBytes
	}
	return options
}

func appendSQLFileBatchStatement(batch []sqlFilePendingStatement, index int, stmt string) []sqlFilePendingStatement {
	return append(batch, sqlFilePendingStatement{
		Index: index,
		SQL:   stmt,
	})
}

func joinSQLFileBatchStatements(batch []sqlFilePendingStatement) string {
	if len(batch) == 0 {
		return ""
	}
	var builder strings.Builder
	for i, item := range batch {
		if i > 0 {
			builder.WriteString(";\n")
		}
		builder.WriteString(item.SQL)
	}
	return builder.String()
}

func sqlFileStatementSnippet(stmt string, maxLen int) string {
	snippet := strings.TrimSpace(stmt)
	if maxLen > 0 && len(snippet) > maxLen {
		return snippet[:maxLen] + "..."
	}
	return snippet
}

func execSQLFileStatement(ctx context.Context, execer sqlFileStatementExecer, stmt string) (int64, error) {
	if ctxErr := ctx.Err(); ctxErr != nil {
		return 0, ctxErr
	}
	if e, ok := execer.(sqlFileContextStatementExecer); ok {
		return e.ExecContext(ctx, stmt)
	}
	return execer.Exec(stmt)
}

func isSQLFileBatchableWriteStatement(dbType string, stmt string) bool {
	if isReadOnlySQLQuery(dbType, stmt) {
		return false
	}
	if isPLSQLBlockStatement(stmt) {
		return false
	}
	switch leadingSQLKeyword(stmt) {
	case "insert", "update", "delete", "replace", "merge", "upsert":
		return true
	default:
		return false
	}
}

func sqlFileBatchTransactionSQL(dbType string) (beginSQL string, commitSQL string, rollbackSQL string, ok bool) {
	switch strings.ToLower(strings.TrimSpace(dbType)) {
	case "mysql", "mariadb", "diros", "starrocks", "sphinx", "oceanbase":
		return "START TRANSACTION", "COMMIT", "ROLLBACK", true
	case "sqlserver":
		return "BEGIN TRANSACTION", "COMMIT TRANSACTION", "ROLLBACK TRANSACTION", true
	case "postgres", "kingbase", "highgo", "vastbase", "opengauss", "sqlite", "duckdb", "iris":
		return "BEGIN", "COMMIT", "ROLLBACK", true
	default:
		return "", "", "", false
	}
}

func updateSQLFileTransactionState(inTransaction bool, stmt string) bool {
	switch leadingSQLKeyword(stmt) {
	case "begin":
		return true
	case "start":
		return strings.Contains(strings.ToLower(stmt), "transaction")
	case "commit":
		return false
	case "rollback":
		lower := strings.ToLower(stmt)
		if strings.Contains(lower, " rollback to ") || strings.Contains(lower, "rollback to ") {
			return inTransaction
		}
		return false
	default:
		return inTransaction
	}
}

func executeSQLFileBatch(ctx context.Context, execer sqlFileStatementExecer, batcher sqlFileBatchStatementExecer, dbType string, batchSQL string, useTransaction bool) (bool, error) {
	if !useTransaction {
		_, err := batcher.ExecBatchContext(ctx, batchSQL)
		return false, err
	}

	beginSQL, commitSQL, rollbackSQL, ok := sqlFileBatchTransactionSQL(dbType)
	if !ok {
		_, err := batcher.ExecBatchContext(ctx, batchSQL)
		return false, err
	}

	if _, err := execSQLFileStatement(ctx, execer, beginSQL); err != nil {
		return true, err
	}
	if _, err := batcher.ExecBatchContext(ctx, batchSQL); err != nil {
		if _, rollbackErr := execSQLFileStatement(ctx, execer, rollbackSQL); rollbackErr != nil {
			return false, fmt.Errorf("批量执行失败: %v；回滚失败: %w", err, rollbackErr)
		}
		return true, err
	}
	if _, err := execSQLFileStatement(ctx, execer, commitSQL); err != nil {
		_, _ = execSQLFileStatement(ctx, execer, rollbackSQL)
		return false, err
	}
	return false, nil
}

func executeSQLFileStream(ctx context.Context, dbInst db.Database, reader io.Reader, options sqlFileExecutionOptions, bytesRead func() int64) (sqlFileExecutionResult, error) {
	options = normalizeSQLFileExecutionOptions(options)
	var result sqlFileExecutionResult
	var batch []sqlFilePendingStatement
	var batchBytes int
	var lastProgressAt time.Time
	var inUserTransaction bool
	var useTransactionalBatch bool
	execer := sqlFileStatementExecer(dbInst)
	batcher, supportsBatch := dbInst.(sqlFileBatchStatementExecer)
	if provider, ok := dbInst.(db.SessionExecerProvider); ok {
		sessionExecer, err := provider.OpenSessionExecer(ctx)
		if err != nil {
			return result, err
		}
		defer sessionExecer.Close()
		execer = sessionExecer
		if supportsBatch {
			var ok bool
			batcher, ok = sessionExecer.(sqlFileBatchStatementExecer)
			supportsBatch = ok
		}
		useTransactionalBatch = supportsBatch
	}

	readBytes := func() int64 {
		if bytesRead == nil {
			return 0
		}
		return bytesRead()
	}

	emitProgress := func(currentSQL string) {
		if options.OnProgress == nil {
			return
		}
		total := result.Executed + result.Failed
		options.OnProgress(sqlFileExecutionProgress{
			Status:     "running",
			Executed:   result.Executed,
			Failed:     result.Failed,
			Total:      total,
			BytesRead:  readBytes(),
			CurrentSQL: currentSQL,
		})
		lastProgressAt = time.Now()
	}

	shouldEmitProgress := func() bool {
		total := result.Executed + result.Failed
		if total <= 10 {
			return true
		}
		if total%sqlFileProgressStatementInterval == 0 {
			return true
		}
		return !lastProgressAt.IsZero() && time.Since(lastProgressAt) >= sqlFileProgressTimeInterval
	}

	recordError := func(index int, stmt string, err error) {
		result.Failed++
		errLog := fmt.Sprintf("第 %d 条语句执行失败: %v\n  SQL: %s", index+1, err, sqlFileStatementSnippet(stmt, 200))
		result.Errors = append(result.Errors, errLog)
		logger.Warnf("ExecuteSQLFile %s", errLog)
	}

	executeSingle := func(item sqlFilePendingStatement) error {
		if _, err := execSQLFileStatement(ctx, execer, item.SQL); err != nil {
			if ctx.Err() != nil {
				return fmt.Errorf("已取消")
			}
			recordError(item.Index, item.SQL, err)
		} else {
			result.Executed++
		}
		if shouldEmitProgress() {
			emitProgress(sqlFileStatementSnippet(item.SQL, 100))
		}
		return nil
	}

	executeBatchSequentially := func(items []sqlFilePendingStatement) error {
		for _, item := range items {
			if err := executeSingle(item); err != nil {
				return err
			}
		}
		return nil
	}

	flushBatch := func() error {
		if len(batch) == 0 {
			return nil
		}
		select {
		case <-ctx.Done():
			return fmt.Errorf("已取消")
		default:
		}

		startIndex := batch[0].Index
		batchSQL := joinSQLFileBatchStatements(batch)
		canFallback, err := executeSQLFileBatch(ctx, execer, batcher, options.DBType, batchSQL, useTransactionalBatch)
		if err != nil {
			logger.Warnf("ExecuteSQLFile 批量执行 %d 条语句失败，将降级逐条执行：第 %d 条起: %v", len(batch), startIndex+1, err)
			pending := append([]sqlFilePendingStatement(nil), batch...)
			batch = batch[:0]
			batchBytes = 0
			if !canFallback {
				return fmt.Errorf("第 %d 条起的批量语句执行失败: %w", startIndex+1, err)
			}
			return executeBatchSequentially(pending)
		}
		result.Executed += len(batch)
		if shouldEmitProgress() {
			emitProgress(sqlFileStatementSnippet(batch[len(batch)-1].SQL, 100))
		}
		batch = batch[:0]
		batchBytes = 0
		return nil
	}

	_, streamErr := streamSQLFile(reader, func(index int, stmt string) error {
		select {
		case <-ctx.Done():
			return fmt.Errorf("已取消")
		default:
		}

		stmt = strings.TrimSpace(stmt)
		if stmt == "" {
			return nil
		}

		if supportsBatch && !inUserTransaction && isSQLFileBatchableWriteStatement(options.DBType, stmt) {
			stmtBytes := len(stmt)
			if len(batch) > 0 && (len(batch) >= options.BatchMaxStatements || batchBytes+2+stmtBytes > options.BatchMaxBytes) {
				if err := flushBatch(); err != nil {
					return err
				}
			}
			if stmtBytes > options.BatchMaxBytes {
				if err := flushBatch(); err != nil {
					return err
				}
				canFallback, err := executeSQLFileBatch(ctx, execer, batcher, options.DBType, stmt, useTransactionalBatch)
				if err != nil {
					logger.Warnf("ExecuteSQLFile 超大语句批量执行失败，将降级单条执行：第 %d 条: %v", index+1, err)
					if !canFallback {
						return fmt.Errorf("第 %d 条语句执行失败: %w", index+1, err)
					}
					return executeSingle(sqlFilePendingStatement{Index: index, SQL: stmt})
				}
				result.Executed++
				if shouldEmitProgress() {
					emitProgress(sqlFileStatementSnippet(stmt, 100))
				}
				return nil
			}
			batch = appendSQLFileBatchStatement(batch, index, stmt)
			if batchBytes == 0 {
				batchBytes = stmtBytes
			} else {
				batchBytes += 2 + stmtBytes
			}
			return nil
		}

		if err := flushBatch(); err != nil {
			return err
		}
		if err := executeSingle(sqlFilePendingStatement{Index: index, SQL: stmt}); err != nil {
			return err
		}
		inUserTransaction = updateSQLFileTransactionState(inUserTransaction, stmt)
		return nil
	})
	if streamErr != nil {
		return result, streamErr
	}
	if err := flushBatch(); err != nil {
		return result, err
	}
	return result, nil
}

// ExecuteSQLFile 在后端流式读取并执行大 SQL 文件，通过事件推送进度。
// 前端通过 EventsOn("sqlfile:progress", ...) 监听进度。
func (a *App) ExecuteSQLFile(config connection.ConnectionConfig, dbName string, filePath string, jobID string) connection.QueryResult {
	if strings.TrimSpace(filePath) == "" {
		return connection.QueryResult{Success: false, Message: "文件路径为空"}
	}
	if strings.TrimSpace(jobID) == "" {
		jobID = fmt.Sprintf("sqlfile-%d", time.Now().UnixMilli())
	}

	logger.Warnf("ExecuteSQLFile 开始：file=%s db=%s jobID=%s", filePath, dbName, jobID)

	// 获取数据库连接
	runConfig := normalizeRunConfig(config, dbName)
	dbInst, err := a.getDatabase(runConfig)
	if err != nil {
		logger.Error(err, "ExecuteSQLFile 获取连接失败：%s", formatConnSummary(runConfig))
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	// 打开文件
	f, err := os.Open(filePath)
	if err != nil {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("无法打开文件: %v", err)}
	}
	defer f.Close()

	// 获取文件大小用于计算进度
	fi, _ := f.Stat()
	totalSize := fi.Size()

	// 设置取消上下文
	ctx, cancel := context.WithCancel(context.Background())
	defer cancel()

	a.queryMu.Lock()
	a.runningQueries[jobID] = queryContext{
		cancel:  cancel,
		started: time.Now(),
	}
	a.queryMu.Unlock()
	defer func() {
		a.queryMu.Lock()
		delete(a.runningQueries, jobID)
		a.queryMu.Unlock()
	}()

	// 发送进度事件的辅助函数
	emitProgress := func(status string, executed, failed, total int, bytesRead int64, currentSQL string, errMsg string) {
		percent := 0.0
		if totalSize > 0 {
			percent = float64(bytesRead) / float64(totalSize) * 100
			if percent > 100 {
				percent = 100
			}
		}
		runtime.EventsEmit(a.ctx, "sqlfile:progress", map[string]interface{}{
			"jobId":      jobID,
			"status":     status,
			"executed":   executed,
			"failed":     failed,
			"total":      total,
			"percent":    percent,
			"bytesRead":  bytesRead,
			"totalBytes": totalSize,
			"currentSQL": currentSQL,
			"error":      errMsg,
		})
	}

	emitProgress("running", 0, 0, 0, 0, "", "")

	// 使用 countingReader 追踪已读取字节数
	cr := &countingReader{r: f}

	startTime := time.Now()
	execResult, streamErr := executeSQLFileStream(ctx, dbInst, cr, sqlFileExecutionOptions{
		DBType: resolveDDLDBType(runConfig),
		OnProgress: func(progress sqlFileExecutionProgress) {
			emitProgress(
				progress.Status,
				progress.Executed,
				progress.Failed,
				progress.Total,
				progress.BytesRead,
				progress.CurrentSQL,
				progress.Error,
			)
		},
	}, func() int64 {
		return cr.n
	})

	duration := time.Since(startTime)
	executedCount := execResult.Executed
	failedCount := execResult.Failed
	errorLogs := execResult.Errors

	if streamErr != nil && streamErr.Error() == "已取消" {
		emitProgress("cancelled", executedCount, failedCount, executedCount+failedCount, cr.n, "", "用户取消执行")
		logger.Warnf("ExecuteSQLFile 已取消：executed=%d failed=%d duration=%v", executedCount, failedCount, duration)
		return connection.QueryResult{
			Success: false,
			Message: fmt.Sprintf("执行已取消。已执行 %d 条，失败 %d 条，耗时 %v。", executedCount, failedCount, duration.Round(time.Millisecond)),
		}
	}

	if streamErr != nil {
		emitProgress("error", executedCount, failedCount, executedCount+failedCount, cr.n, "", streamErr.Error())
		return connection.QueryResult{
			Success: false,
			Message: fmt.Sprintf("文件读取错误: %v。已执行 %d 条。", streamErr, executedCount),
		}
	}

	emitProgress("done", executedCount, failedCount, executedCount+failedCount, totalSize, "", "")

	summary := fmt.Sprintf("执行完成。成功 %d 条，失败 %d 条，耗时 %v。", executedCount, failedCount, duration.Round(time.Millisecond))
	if len(errorLogs) > 0 {
		maxShow := 20
		if len(errorLogs) < maxShow {
			maxShow = len(errorLogs)
		}
		summary += "\n\n错误详情（前 " + fmt.Sprintf("%d", maxShow) + " 条）：\n" + strings.Join(errorLogs[:maxShow], "\n")
		if len(errorLogs) > maxShow {
			summary += fmt.Sprintf("\n...还有 %d 条错误未显示", len(errorLogs)-maxShow)
		}
	}

	logger.Warnf("ExecuteSQLFile 完成：executed=%d failed=%d duration=%v", executedCount, failedCount, duration)
	return connection.QueryResult{Success: failedCount == 0, Message: summary}
}

// CancelSQLFileExecution 取消正在执行的 SQL 文件任务。
func (a *App) CancelSQLFileExecution(jobID string) connection.QueryResult {
	a.queryMu.Lock()
	defer a.queryMu.Unlock()

	if ctx, exists := a.runningQueries[jobID]; exists {
		ctx.cancel()
		delete(a.runningQueries, jobID)
		return connection.QueryResult{Success: true, Message: "已发送取消请求"}
	}
	return connection.QueryResult{Success: false, Message: "未找到该任务"}
}

// countingReader 包装 io.Reader，追踪已读取的字节数。
type countingReader struct {
	r io.Reader
	n int64
}

func (cr *countingReader) Read(p []byte) (int, error) {
	n, err := cr.r.Read(p)
	cr.n += int64(n)
	return n, err
}

func readImportedConnectionConfigFile(path string) (string, error) {
	info, err := os.Stat(path)
	if err != nil {
		return "", err
	}
	if info.Size() > connectionImportMaxFileBytes {
		return "", errConnectionImportFileTooLarge
	}

	content, err := os.ReadFile(path)
	if err != nil {
		return "", err
	}
	return string(content), nil
}

func (a *App) ImportConfigFile() connection.QueryResult {
	selection, err := runtime.OpenFileDialog(a.ctx, runtime.OpenDialogOptions{
		Title: "Select Config File",
		Filters: []runtime.FileFilter{
			{
				DisplayName: "GoNavi Connection Package (*.gonavi-conn)",
				Pattern:     "*.gonavi-conn",
			},
			{
				DisplayName: "JSON Files (*.json)",
				Pattern:     "*.json",
			},
			{
				DisplayName: "MySQL Workbench Connections (*.xml)",
				Pattern:     "*.xml",
			},
		},
	})

	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	if selection == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}

	content, err := readImportedConnectionConfigFile(selection)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	return connection.QueryResult{Success: true, Data: content}
}

func (a *App) ExportConnectionsPackage(options ConnectionExportOptions) connection.QueryResult {
	filename, err := runtime.SaveFileDialog(a.ctx, runtime.SaveDialogOptions{
		Title:           "Export Connections",
		DefaultFilename: "connections" + connectionPackageExtension,
		Filters: []runtime.FileFilter{
			{
				DisplayName: "GoNavi Connection Package (*.gonavi-conn)",
				Pattern:     "*.gonavi-conn",
			},
		},
	})
	if err != nil || strings.TrimSpace(filename) == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}
	filename = normalizeConnectionPackageExportFilename(filename)

	content, err := a.buildExportedConnectionPackage(options)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	if len(content) > connectionImportMaxFileBytes {
		return connection.QueryResult{Success: false, Message: errConnectionImportFileTooLarge.Error()}
	}
	if err := os.WriteFile(filename, content, 0o644); err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	return connection.QueryResult{Success: true, Message: "导出完成"}
}

func normalizeConnectionPackageExportFilename(filename string) string {
	trimmed := strings.TrimSpace(filename)
	if trimmed == "" {
		return ""
	}
	if strings.EqualFold(filepath.Ext(trimmed), connectionPackageExtension) {
		return trimmed
	}
	return trimmed + connectionPackageExtension
}

func (a *App) SelectSSHKeyFile(currentPath string) connection.QueryResult {
	defaultDir := strings.TrimSpace(currentPath)
	if defaultDir == "" {
		if home, err := os.UserHomeDir(); err == nil {
			defaultDir = filepath.Join(home, ".ssh")
		}
	}
	if filepath.Ext(defaultDir) != "" {
		defaultDir = filepath.Dir(defaultDir)
	}
	if defaultDir != "" && !filepath.IsAbs(defaultDir) {
		if abs, err := filepath.Abs(defaultDir); err == nil {
			defaultDir = abs
		}
	}

	selection, err := runtime.OpenFileDialog(a.ctx, runtime.OpenDialogOptions{
		Title:            "选择 SSH 私钥文件",
		DefaultDirectory: defaultDir,
		Filters: []runtime.FileFilter{
			{
				DisplayName: "私钥文件",
				Pattern:     "*.pem;*.key;*.ppk;*id_rsa*",
			},
			{
				DisplayName: "所有文件",
				Pattern:     "*",
			},
		},
	})
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	if strings.TrimSpace(selection) == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}
	if abs, err := filepath.Abs(selection); err == nil {
		selection = abs
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"path": selection}}
}

func (a *App) SelectCertificateFile(currentPath string, certKind string) connection.QueryResult {
	defaultDir := strings.TrimSpace(currentPath)
	if defaultDir == "" {
		if home, err := os.UserHomeDir(); err == nil {
			defaultDir = home
		}
	}
	if filepath.Ext(defaultDir) != "" {
		defaultDir = filepath.Dir(defaultDir)
	}
	if defaultDir != "" && !filepath.IsAbs(defaultDir) {
		if abs, err := filepath.Abs(defaultDir); err == nil {
			defaultDir = abs
		}
	}

	kind := strings.ToLower(strings.TrimSpace(certKind))
	title := "选择 TLS 证书文件"
	displayName := "证书文件"
	switch kind {
	case "ca":
		title = "选择 CA/服务端证书文件"
	case "client-cert":
		title = "选择客户端证书文件"
	case "client-key":
		title = "选择客户端私钥文件"
		displayName = "私钥文件"
	}

	selection, err := runtime.OpenFileDialog(a.ctx, runtime.OpenDialogOptions{
		Title:            title,
		DefaultDirectory: defaultDir,
		Filters: []runtime.FileFilter{
			{
				DisplayName: displayName,
				Pattern:     "*.pem;*.crt;*.cer;*.cert;*.key",
			},
			{
				DisplayName: "所有文件",
				Pattern:     "*",
			},
		},
	})
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	if strings.TrimSpace(selection) == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}
	if abs, err := filepath.Abs(selection); err == nil {
		selection = abs
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"path": selection}}
}

func (a *App) SelectDatabaseFile(currentPath string, driverType string) connection.QueryResult {
	defaultDir := strings.TrimSpace(currentPath)
	if defaultDir == "" {
		if home, err := os.UserHomeDir(); err == nil {
			defaultDir = home
		}
	}
	if filepath.Ext(defaultDir) != "" {
		defaultDir = filepath.Dir(defaultDir)
	}
	if defaultDir != "" && !filepath.IsAbs(defaultDir) {
		if abs, err := filepath.Abs(defaultDir); err == nil {
			defaultDir = abs
		}
	}

	normalizedType := strings.ToLower(strings.TrimSpace(driverType))
	filters := []runtime.FileFilter{
		{
			DisplayName: "数据库文件",
			Pattern:     "*.db;*.sqlite;*.sqlite3;*.db3;*.duckdb;*.ddb",
		},
		{
			DisplayName: "所有文件",
			Pattern:     "*",
		},
	}
	title := "选择数据库文件"
	switch normalizedType {
	case "sqlite":
		title = "选择 SQLite 数据文件"
		filters = []runtime.FileFilter{
			{
				DisplayName: "SQLite 文件",
				Pattern:     "*.db;*.sqlite;*.sqlite3;*.db3",
			},
			{
				DisplayName: "所有文件",
				Pattern:     "*",
			},
		}
	case "duckdb":
		title = "选择 DuckDB 数据文件"
		filters = []runtime.FileFilter{
			{
				DisplayName: "DuckDB 文件",
				Pattern:     "*.duckdb;*.ddb;*.db",
			},
			{
				DisplayName: "所有文件",
				Pattern:     "*",
			},
		}
	}

	selection, err := runtime.OpenFileDialog(a.ctx, runtime.OpenDialogOptions{
		Title:            title,
		DefaultDirectory: defaultDir,
		Filters:          filters,
	})
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	if strings.TrimSpace(selection) == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}
	if abs, err := filepath.Abs(selection); err == nil {
		selection = abs
	}
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"path": selection}}
}

// PreviewImportFile 解析导入文件，返回字段列表、总行数、前 5 行预览数据
func (a *App) PreviewImportFile(filePath string) connection.QueryResult {
	if filePath == "" {
		return connection.QueryResult{Success: false, Message: "文件路径不能为空"}
	}

	rows, columns, err := parseImportFile(filePath)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	totalRows := len(rows)
	previewRows := rows
	if len(rows) > 5 {
		previewRows = rows[:5]
	}

	result := map[string]interface{}{
		"columns":     columns,
		"totalRows":   totalRows,
		"previewRows": previewRows,
		"filePath":    filePath,
	}

	return connection.QueryResult{Success: true, Data: result}
}

func (a *App) ImportData(config connection.ConnectionConfig, dbName, tableName string) connection.QueryResult {
	selection, err := runtime.OpenFileDialog(a.ctx, runtime.OpenDialogOptions{
		Title: fmt.Sprintf("Import into %s", tableName),
		Filters: []runtime.FileFilter{
			{
				DisplayName: "Data Files",
				Pattern:     "*.csv;*.json;*.xlsx;*.xls",
			},
		},
	})

	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	if selection == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}

	// 返回文件路径供前端预览
	return connection.QueryResult{Success: true, Data: map[string]interface{}{"filePath": selection}}
}

// parseImportFile 解析导入文件，返回数据行和列名
func parseImportFile(filePath string) ([]map[string]interface{}, []string, error) {
	var rows []map[string]interface{}
	var columns []string
	lower := strings.ToLower(filePath)

	if strings.HasSuffix(lower, ".json") {
		f, err := os.Open(filePath)
		if err != nil {
			return nil, nil, err
		}
		defer f.Close()
		decoder := json.NewDecoder(f)
		if err := decoder.Decode(&rows); err != nil {
			return nil, nil, fmt.Errorf("JSON Parse Error: %w", err)
		}
		if len(rows) > 0 {
			for k := range rows[0] {
				columns = append(columns, k)
			}
		}
	} else if strings.HasSuffix(lower, ".csv") {
		f, err := os.Open(filePath)
		if err != nil {
			return nil, nil, err
		}
		defer f.Close()
		reader := csv.NewReader(f)
		records, err := reader.ReadAll()
		if err != nil {
			return nil, nil, fmt.Errorf("CSV Parse Error: %w", err)
		}
		if len(records) < 2 {
			return nil, nil, fmt.Errorf("CSV empty or missing header")
		}
		columns = records[0]
		for _, record := range records[1:] {
			row := make(map[string]interface{})
			for i, val := range record {
				if i < len(columns) {
					if val == "NULL" {
						row[columns[i]] = nil
					} else {
						row[columns[i]] = val
					}
				}
			}
			rows = append(rows, row)
		}
	} else if strings.HasSuffix(lower, ".xlsx") || strings.HasSuffix(lower, ".xls") {
		xlsx, err := excelize.OpenFile(filePath)
		if err != nil {
			return nil, nil, fmt.Errorf("Excel Parse Error: %w", err)
		}
		defer xlsx.Close()

		sheetName := xlsx.GetSheetName(0)
		if sheetName == "" {
			return nil, nil, fmt.Errorf("Excel file has no sheets")
		}

		xlRows, err := xlsx.GetRows(sheetName)
		if err != nil {
			return nil, nil, fmt.Errorf("Excel Read Error: %w", err)
		}
		if len(xlRows) < 2 {
			return nil, nil, fmt.Errorf("Excel empty or missing header")
		}

		columns = xlRows[0]
		for _, record := range xlRows[1:] {
			row := make(map[string]interface{})
			for i, val := range record {
				if i < len(columns) && columns[i] != "" {
					if val == "NULL" {
						row[columns[i]] = nil
					} else {
						row[columns[i]] = val
					}
				}
			}
			if len(row) > 0 {
				rows = append(rows, row)
			}
		}
	} else {
		return nil, nil, fmt.Errorf("Unsupported file format")
	}

	return rows, columns, nil
}

func normalizeColumnName(name string) string {
	return strings.ToLower(strings.TrimSpace(name))
}

func buildImportColumnTypeMap(defs []connection.ColumnDefinition) map[string]string {
	result := make(map[string]string, len(defs))
	for _, def := range defs {
		key := normalizeColumnName(def.Name)
		if key == "" {
			continue
		}
		result[key] = strings.TrimSpace(def.Type)
	}
	return result
}

func isTimezoneAwareColumnType(columnType string) bool {
	typ := strings.ToLower(strings.TrimSpace(columnType))
	if typ == "" {
		return false
	}
	return strings.Contains(typ, "with time zone") ||
		strings.Contains(typ, "with timezone") ||
		strings.Contains(typ, "datetimeoffset") ||
		strings.Contains(typ, "timestamptz")
}

func isDateTimeColumnType(columnType string) bool {
	typ := strings.ToLower(strings.TrimSpace(columnType))
	if typ == "" {
		return false
	}
	return strings.Contains(typ, "datetime") || strings.Contains(typ, "timestamp") || strings.Contains(typ, "timestamptz")
}

func isTimeOnlyColumnType(columnType string) bool {
	typ := strings.ToLower(strings.TrimSpace(columnType))
	if typ == "" {
		return false
	}
	if strings.Contains(typ, "datetime") || strings.Contains(typ, "timestamp") {
		return false
	}
	return strings.Contains(typ, "time") || strings.Contains(typ, "timetz")
}

func isDateOnlyColumnType(dbType, columnType string) bool {
	typ := strings.ToLower(strings.TrimSpace(columnType))
	if typ == "" {
		return false
	}
	if strings.Contains(typ, "datetime") || strings.Contains(typ, "timestamp") || strings.Contains(typ, "time") {
		return false
	}
	if !strings.Contains(typ, "date") {
		return false
	}
	db := strings.ToLower(strings.TrimSpace(dbType))
	// Oracle/Dameng 的 DATE 带时间语义，不能按纯日期裁剪。
	return db != "oracle" && db != "dameng"
}

func isTemporalColumnType(dbType, columnType string) bool {
	return isDateTimeColumnType(columnType) || isTimeOnlyColumnType(columnType) || isDateOnlyColumnType(dbType, columnType)
}

func parseTemporalString(raw string) (time.Time, bool) {
	text := strings.TrimSpace(raw)
	if text == "" {
		return time.Time{}, false
	}

	layoutsWithZone := []string{
		"2006-01-02 15:04:05.999999999 -0700 MST",
		"2006-01-02 15:04:05 -0700 MST",
		"2006-01-02 15:04:05.999999999 -0700",
		"2006-01-02 15:04:05 -0700",
		time.RFC3339Nano,
		time.RFC3339,
	}

	for _, layout := range layoutsWithZone {
		parsed, err := time.Parse(layout, text)
		if err == nil {
			return parsed, true
		}
	}

	layoutsWithoutZone := []string{
		"2006-01-02 15:04:05.999999999",
		"2006-01-02 15:04:05",
		"2006-01-02",
		"15:04:05.999999999",
		"15:04:05",
	}

	for _, layout := range layoutsWithoutZone {
		parsed, err := time.ParseInLocation(layout, text, time.Local)
		if err == nil {
			return parsed, true
		}
	}

	return time.Time{}, false
}

func normalizeImportTemporalValue(dbType, columnType, raw string) string {
	text := strings.TrimSpace(raw)
	if text == "" {
		return text
	}

	parsed, ok := parseTemporalString(text)
	if !ok {
		if isDateTimeColumnType(columnType) {
			candidate := strings.ReplaceAll(text, "T", " ")
			if len(candidate) >= 19 {
				prefix := candidate[:19]
				if _, err := time.Parse("2006-01-02 15:04:05", prefix); err == nil {
					return prefix
				}
			}
		}
		return text
	}

	if isTimeOnlyColumnType(columnType) {
		return parsed.Format("15:04:05")
	}
	if isDateOnlyColumnType(dbType, columnType) {
		return parsed.Format("2006-01-02")
	}
	if isTimezoneAwareColumnType(columnType) {
		return parsed.Format("2006-01-02 15:04:05-07:00")
	}
	return parsed.Format("2006-01-02 15:04:05")
}

func isPgLikeBooleanDBType(dbType string) bool {
	switch strings.ToLower(strings.TrimSpace(dbType)) {
	case "postgres", "postgresql", "pg", "pq", "pgx", "kingbase", "kingbase8", "kingbasees", "kingbasev8", "highgo", "vastbase", "opengauss", "open_gauss", "open-gauss":
		return true
	default:
		return false
	}
}

func isBooleanColumnType(columnType string) bool {
	typ := strings.ToLower(strings.TrimSpace(columnType))
	if typ == "" {
		return false
	}
	typ = strings.ReplaceAll(typ, `"`, "")
	if idx := strings.IndexAny(typ, " ("); idx >= 0 {
		typ = typ[:idx]
	}
	typ = strings.TrimPrefix(typ, "pg_catalog.")
	return typ == "bool" || typ == "boolean"
}

func booleanSQLLiteral(v bool) string {
	if v {
		return "true"
	}
	return "false"
}

func formatSignedBooleanSQLValue(v int64) (string, bool) {
	switch v {
	case 0:
		return "false", true
	case 1:
		return "true", true
	default:
		return "", false
	}
}

func formatUnsignedBooleanSQLValue(v uint64) (string, bool) {
	switch v {
	case 0:
		return "false", true
	case 1:
		return "true", true
	default:
		return "", false
	}
}

func formatFloatBooleanSQLValue(v float64) (string, bool) {
	if v == 0 {
		return "false", true
	}
	if v == 1 {
		return "true", true
	}
	return "", false
}

func formatBooleanStringSQLValue(raw string) (string, bool) {
	switch strings.ToLower(strings.TrimSpace(raw)) {
	case "true", "t", "1", "yes", "y", "on":
		return "true", true
	case "false", "f", "0", "no", "n", "off":
		return "false", true
	default:
		return "", false
	}
}

func formatPostgresBooleanSQLValue(value interface{}) (string, bool) {
	switch val := value.(type) {
	case bool:
		return booleanSQLLiteral(val), true
	case int:
		return formatSignedBooleanSQLValue(int64(val))
	case int8:
		return formatSignedBooleanSQLValue(int64(val))
	case int16:
		return formatSignedBooleanSQLValue(int64(val))
	case int32:
		return formatSignedBooleanSQLValue(int64(val))
	case int64:
		return formatSignedBooleanSQLValue(val)
	case uint:
		return formatUnsignedBooleanSQLValue(uint64(val))
	case uint8:
		return formatUnsignedBooleanSQLValue(uint64(val))
	case uint16:
		return formatUnsignedBooleanSQLValue(uint64(val))
	case uint32:
		return formatUnsignedBooleanSQLValue(uint64(val))
	case uint64:
		return formatUnsignedBooleanSQLValue(val)
	case float32:
		return formatFloatBooleanSQLValue(float64(val))
	case float64:
		return formatFloatBooleanSQLValue(val)
	case []byte:
		if len(val) == 1 && (val[0] == 0 || val[0] == 1) {
			return booleanSQLLiteral(val[0] == 1), true
		}
		return formatBooleanStringSQLValue(string(val))
	case string:
		return formatBooleanStringSQLValue(val)
	default:
		return "", false
	}
}

func formatImportSQLValue(dbType, columnType string, value interface{}) string {
	if value == nil {
		return "NULL"
	}

	if isPgLikeBooleanDBType(dbType) && isBooleanColumnType(columnType) {
		if literal, ok := formatPostgresBooleanSQLValue(value); ok {
			return literal
		}
	}

	if isTemporalColumnType(dbType, columnType) {
		normalized := normalizeImportTemporalValue(dbType, columnType, fmt.Sprintf("%v", value))
		escaped := strings.ReplaceAll(normalized, "'", "''")
		return "'" + escaped + "'"
	}

	return formatSQLValue(dbType, value)
}

// ImportDataWithProgress 执行导入并发送进度事件
func (a *App) ImportDataWithProgress(config connection.ConnectionConfig, dbName, tableName, filePath string) connection.QueryResult {
	rows, columns, err := parseImportFile(filePath)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	if len(rows) == 0 {
		return connection.QueryResult{Success: true, Message: "无可导入数据"}
	}

	runConfig := normalizeRunConfig(config, dbName)
	dbInst, err := a.getDatabase(runConfig)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	dbType := resolveDDLDBType(config)
	schemaName, pureTableName := normalizeSchemaAndTable(config, dbName, tableName)
	columnTypeMap := map[string]string{}
	if defs, colErr := dbInst.GetColumns(schemaName, pureTableName); colErr == nil {
		columnTypeMap = buildImportColumnTypeMap(defs)
	}

	totalRows := len(rows)
	successCount := 0
	var errorLogs []string

	quotedCols := make([]string, len(columns))
	for i, c := range columns {
		quotedCols[i] = quoteIdentByType(dbType, c)
	}

	for idx, row := range rows {
		var values []string
		for _, col := range columns {
			val := row[col]
			colType := columnTypeMap[normalizeColumnName(col)]
			values = append(values, formatImportSQLValue(dbType, colType, val))
		}

		query := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)",
			quoteQualifiedIdentByType(dbType, tableName),
			strings.Join(quotedCols, ", "),
			strings.Join(values, ", "))

		_, err := dbInst.Exec(query)
		if err != nil {
			errorLogs = append(errorLogs, fmt.Sprintf("Row %d: %s", idx+1, err.Error()))
		} else {
			successCount++
		}

		// 每 10 行发送一次进度事件
		if (idx+1)%10 == 0 || idx == totalRows-1 {
			runtime.EventsEmit(a.ctx, "import:progress", map[string]interface{}{
				"current": idx + 1,
				"total":   totalRows,
				"success": successCount,
				"errors":  len(errorLogs),
			})
		}
	}

	result := map[string]interface{}{
		"success":      successCount,
		"failed":       len(errorLogs),
		"total":        totalRows,
		"errorLogs":    errorLogs,
		"errorSummary": fmt.Sprintf("Imported: %d, Failed: %d", successCount, len(errorLogs)),
	}

	return connection.QueryResult{Success: true, Data: result, Message: fmt.Sprintf("Imported: %d, Failed: %d", successCount, len(errorLogs))}
}

func (a *App) ApplyChanges(config connection.ConnectionConfig, dbName, tableName string, changes connection.ChangeSet) connection.QueryResult {
	runConfig := normalizeRunConfig(config, dbName)

	dbInst, err := a.getDatabase(runConfig)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	if applier, ok := dbInst.(db.BatchApplier); ok {
		err := applier.ApplyChanges(tableName, changes)
		if err != nil {
			return connection.QueryResult{Success: false, Message: err.Error()}
		}
		return connection.QueryResult{Success: true, Message: "事务提交成功"}
	}

	return connection.QueryResult{Success: false, Message: "当前数据库类型不支持批量提交"}
}

// ChangePreview 变更预览结果
type ChangePreview struct {
	Deletes []string `json:"deletes"`
	Updates []string `json:"updates"`
	Inserts []string `json:"inserts"`
}

func (a *App) PreviewChanges(config connection.ConnectionConfig, dbName, tableName string, changes connection.ChangeSet) connection.QueryResult {
	runConfig := normalizeRunConfig(config, dbName)

	dbInst, err := a.getDatabase(runConfig)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	var cp ChangePreview
	// 优先使用驱动的 PreviewChanges（若实现了 ChangePreviewer 接口）
	if previewer, ok := dbInst.(db.ChangePreviewer); ok {
		deletes, updates, inserts := previewer.PreviewChanges(tableName, changes)
		cp = ChangePreview{Deletes: deletes, Updates: updates, Inserts: inserts}
	} else {
		// 回退到通用生成，使用 quoteIdentByType 处理标识符转义
		dbType := resolveDDLDBType(config)
		quoter := func(s string) string { return quoteIdentByType(dbType, s) }
		deletes, updates, inserts := db.GenerateChangePreview(tableName, changes, quoter)
		cp = ChangePreview{Deletes: deletes, Updates: updates, Inserts: inserts}
	}
	return connection.QueryResult{Success: true, Data: cp}
}

func (a *App) ExportTable(config connection.ConnectionConfig, dbName string, tableName string, format string) connection.QueryResult {
	filename, err := runtime.SaveFileDialog(a.ctx, runtime.SaveDialogOptions{
		Title:           fmt.Sprintf("Export %s", tableName),
		DefaultFilename: fmt.Sprintf("%s.%s", tableName, format),
	})

	if err != nil || filename == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}

	runConfig := normalizeRunConfig(config, dbName)

	dbInst, err := a.getDatabase(runConfig)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	format = strings.ToLower(format)
	if format == "sql" {
		f, err := os.Create(filename)
		if err != nil {
			return connection.QueryResult{Success: false, Message: err.Error()}
		}
		defer f.Close()

		w := bufio.NewWriterSize(f, 1024*1024)
		defer w.Flush()

		if err := writeSQLHeader(w, runConfig, dbName); err != nil {
			return connection.QueryResult{Success: false, Message: err.Error()}
		}
		viewLookup := listViewNameLookup(dbInst, runConfig, dbName)
		if err := dumpTableSQL(w, dbInst, runConfig, dbName, tableName, true, true, viewLookup); err != nil {
			return connection.QueryResult{Success: false, Message: err.Error()}
		}
		if err := writeSQLFooter(w, runConfig); err != nil {
			return connection.QueryResult{Success: false, Message: err.Error()}
		}

		return connection.QueryResult{Success: true, Message: "导出完成"}
	}

	dbType := resolveDDLDBType(config)
	query := fmt.Sprintf("SELECT * FROM %s", quoteQualifiedIdentByType(dbType, tableName))

	data, columns, err := queryDataForExport(dbInst, runConfig, query)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	f, err := os.Create(filename)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	defer f.Close()
	if err := writeRowsToFile(f, data, columns, format); err != nil {
		return connection.QueryResult{Success: false, Message: "写入失败：" + err.Error()}
	}

	return connection.QueryResult{Success: true, Message: "导出完成"}
}

func (a *App) ExportTablesSQL(config connection.ConnectionConfig, dbName string, tableNames []string, includeData bool) connection.QueryResult {
	return a.exportTablesSQL(config, dbName, tableNames, true, includeData)
}

func (a *App) ExportTablesDataSQL(config connection.ConnectionConfig, dbName string, tableNames []string) connection.QueryResult {
	return a.exportTablesSQL(config, dbName, tableNames, false, true)
}

func (a *App) exportTablesSQL(config connection.ConnectionConfig, dbName string, tableNames []string, includeSchema bool, includeData bool) connection.QueryResult {
	if !includeSchema && !includeData {
		return connection.QueryResult{Success: false, Message: "无效的导出模式"}
	}

	safeDbName := strings.TrimSpace(dbName)
	if safeDbName == "" {
		safeDbName = "export"
	}
	suffix := "schema"
	if includeSchema && includeData {
		suffix = "backup"
	} else if !includeSchema && includeData {
		suffix = "data"
	}
	defaultFilename := fmt.Sprintf("%s_%s_%dtables.sql", safeDbName, suffix, len(tableNames))
	if len(tableNames) == 1 && strings.TrimSpace(tableNames[0]) != "" {
		defaultFilename = fmt.Sprintf("%s_%s.sql", strings.TrimSpace(tableNames[0]), suffix)
	}

	filename, err := runtime.SaveFileDialog(a.ctx, runtime.SaveDialogOptions{
		Title:           "Export Tables (SQL)",
		DefaultFilename: defaultFilename,
	})
	if err != nil || filename == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}

	runConfig := normalizeRunConfig(config, dbName)
	dbInst, err := a.getDatabase(runConfig)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	objects := make([]string, 0, len(tableNames))
	seen := make(map[string]struct{}, len(tableNames))
	for _, t := range tableNames {
		t = strings.TrimSpace(t)
		if t == "" {
			continue
		}
		if _, ok := seen[t]; ok {
			continue
		}
		seen[t] = struct{}{}
		objects = append(objects, t)
	}
	viewLookup := listViewNameLookup(dbInst, runConfig, dbName)
	objects = buildExportObjectOrder(runConfig, dbName, objects, viewLookup, false)

	f, err := os.Create(filename)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	defer f.Close()

	w := bufio.NewWriterSize(f, 1024*1024)
	defer w.Flush()

	if err := writeSQLHeader(w, runConfig, dbName); err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	for _, objectName := range objects {
		if err := dumpTableSQL(w, dbInst, runConfig, dbName, objectName, includeSchema, includeData, viewLookup); err != nil {
			return connection.QueryResult{Success: false, Message: err.Error()}
		}
	}
	if err := writeSQLFooter(w, runConfig); err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	return connection.QueryResult{Success: true, Message: "导出完成"}
}

func (a *App) ExportDatabaseSQL(config connection.ConnectionConfig, dbName string, includeData bool) connection.QueryResult {
	safeDbName := strings.TrimSpace(dbName)
	if safeDbName == "" {
		return connection.QueryResult{Success: false, Message: "数据库名称不能为空"}
	}
	suffix := "schema"
	if includeData {
		suffix = "backup"
	}

	filename, err := runtime.SaveFileDialog(a.ctx, runtime.SaveDialogOptions{
		Title:           fmt.Sprintf("Export %s (SQL)", safeDbName),
		DefaultFilename: fmt.Sprintf("%s_%s.sql", safeDbName, suffix),
	})
	if err != nil || filename == "" {
		return connection.QueryResult{Success: false, Message: "已取消"}
	}

	runConfig := normalizeRunConfig(config, dbName)
	dbInst, err := a.getDatabase(runConfig)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	tables, err := dbInst.GetTables(dbName)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	viewLookup := listViewNameLookup(dbInst, runConfig, dbName)
	objects := buildExportObjectOrder(runConfig, dbName, tables, viewLookup, true)

	f, err := os.Create(filename)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	defer f.Close()

	w := bufio.NewWriterSize(f, 1024*1024)
	defer w.Flush()

	if err := writeSQLHeader(w, runConfig, dbName); err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	for _, objectName := range objects {
		if err := dumpTableSQL(w, dbInst, runConfig, dbName, objectName, true, includeData, viewLookup); err != nil {
			return connection.QueryResult{Success: false, Message: err.Error()}
		}
	}
	if err := writeSQLFooter(w, runConfig); err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	return connection.QueryResult{Success: true, Message: "导出完成"}
}

type tableDataClearMode string

const (
	tableDataClearModeTruncate  tableDataClearMode = "truncate"
	tableDataClearModeDeleteAll tableDataClearMode = "delete_all"
)

func supportsTruncateTableForDBType(dbType string) bool {
	switch strings.ToLower(strings.TrimSpace(dbType)) {
	case "mysql", "mariadb", "oceanbase", "starrocks", "postgres", "kingbase", "highgo", "vastbase", "opengauss", "sqlserver", "iris", "oracle", "dameng", "clickhouse", "duckdb":
		return true
	default:
		return false
	}
}

func buildTableDataClearSQL(config connection.ConnectionConfig, objectName string, mode tableDataClearMode) (string, error) {
	dbType := resolveDDLDBType(config)
	quotedObject := quoteQualifiedIdentByType(dbType, objectName)

	switch mode {
	case tableDataClearModeTruncate:
		if !supportsTruncateTableForDBType(dbType) {
			return "", fmt.Errorf("当前数据库类型 %s 不支持截断表，请改用清空表", strings.TrimSpace(dbType))
		}
		return fmt.Sprintf("TRUNCATE TABLE %s", quotedObject), nil
	case tableDataClearModeDeleteAll:
		if dbType == "mongodb" {
			return fmt.Sprintf(`{"delete":"%s","deletes":[{"q":{},"limit":0}]}`, objectName), nil
		}
		return fmt.Sprintf("DELETE FROM %s", quotedObject), nil
	default:
		return "", fmt.Errorf("不支持的表数据清理模式: %s", mode)
	}
}

func tableDataClearActionLabels(mode tableDataClearMode) (actionLabel string, progressLabel string) {
	switch mode {
	case tableDataClearModeTruncate:
		return "截断表", "截断"
	default:
		return "清空表", "清空"
	}
}

func (a *App) runTableDataClear(config connection.ConnectionConfig, dbName string, tableNames []string, mode tableDataClearMode) connection.QueryResult {
	runConfig := normalizeRunConfig(config, dbName)

	// 参数校验
	if len(tableNames) == 0 {
		return connection.QueryResult{Success: false, Message: "未指定要处理的表"}
	}

	objects := make([]string, 0, len(tableNames))
	seen := make(map[string]struct{}, len(tableNames))
	for _, t := range tableNames {
		tt := strings.TrimSpace(t)
		if tt == "" {
			continue
		}
		if _, ok := seen[tt]; ok {
			continue
		}
		seen[tt] = struct{}{}
		objects = append(objects, tt)
	}

	if len(objects) == 0 {
		return connection.QueryResult{Success: false, Message: "未指定要处理的表"}
	}
	const maxBatchSize = 200
	if len(objects) > maxBatchSize {
		return connection.QueryResult{Success: false, Message: fmt.Sprintf("单次最多处理 %d 张表，当前选中 %d 张", maxBatchSize, len(objects))}
	}

	dbInst, err := a.getDatabase(runConfig)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	actionLabel, progressLabel := tableDataClearActionLabels(mode)
	logger.Warnf("%s 开始：%s db=%s tables=%v（共 %d 张）", actionLabel, formatConnSummary(runConfig), dbName, objects, len(objects))

	var executedSQLs []string
	for i, objectName := range objects {
		sql, sqlErr := buildTableDataClearSQL(runConfig, objectName, mode)
		if sqlErr != nil {
			return connection.QueryResult{
				Success: false,
				Message: sqlErr.Error(),
				Data: map[string]interface{}{
					"executedSQLs": executedSQLs,
					"count":        len(executedSQLs),
				},
			}
		}

		if _, err := dbInst.Exec(sql); err != nil {
			logger.Warnf("%s 第 %d/%d 张表失败：%s table=%s err=%v（已成功%s %d 张）", actionLabel, i+1, len(objects), formatConnSummary(runConfig), objectName, err, progressLabel, len(executedSQLs))
			errMsg := fmt.Sprintf("%s %s 失败: %v", progressLabel, objectName, err)
			if len(executedSQLs) > 0 {
				errMsg += fmt.Sprintf("（注意：前 %d 张表已%s且无法恢复）", len(executedSQLs), progressLabel)
			}
			return connection.QueryResult{
				Success: false,
				Message: errMsg,
				Data: map[string]interface{}{
					"executedSQLs": executedSQLs,
					"count":        len(executedSQLs),
				},
			}
		}
		executedSQLs = append(executedSQLs, sql)
	}

	logger.Warnf("%s 完成：%s db=%s 共%s %d 张表", actionLabel, formatConnSummary(runConfig), dbName, progressLabel, len(executedSQLs))

	return connection.QueryResult{
		Success: true,
		Message: progressLabel + "成功",
		Data: map[string]interface{}{
			"executedSQLs": executedSQLs,
			"count":        len(executedSQLs),
		},
	}
}

// TruncateTables 截断指定表的数据；仅在明确支持 TRUNCATE TABLE 的数据库类型上执行。
func (a *App) TruncateTables(config connection.ConnectionConfig, dbName string, tableNames []string) connection.QueryResult {
	return a.runTableDataClear(config, dbName, tableNames, tableDataClearModeTruncate)
}

// ClearTables 清空指定表的数据；关系型数据库使用 DELETE FROM，MongoDB 使用 delete 命令。
func (a *App) ClearTables(config connection.ConnectionConfig, dbName string, tableNames []string) connection.QueryResult {
	return a.runTableDataClear(config, dbName, tableNames, tableDataClearModeDeleteAll)
}

func quoteIdentByType(dbType string, ident string) string {
	if ident == "" {
		return ident
	}

	dbType = resolveDDLDBType(connection.ConnectionConfig{Type: dbType})
	switch dbType {
	case "mysql", "mariadb", "oceanbase", "diros", "starrocks", "sphinx", "tdengine", "clickhouse":
		return "`" + strings.ReplaceAll(ident, "`", "``") + "`"
	case "kingbase":
		return db.QuoteKingbaseIdentifier(ident)
	case "sqlserver":
		escaped := strings.ReplaceAll(ident, "]", "]]")
		return "[" + escaped + "]"
	default:
		return `"` + strings.ReplaceAll(ident, `"`, `""`) + `"`
	}
}

func quoteQualifiedIdentByType(dbType string, ident string) string {
	raw := strings.TrimSpace(ident)
	if raw == "" {
		return raw
	}

	dbType = resolveDDLDBType(connection.ConnectionConfig{Type: dbType})
	if dbType == "kingbase" {
		schema, table := db.SplitKingbaseQualifiedName(raw)
		if table == "" {
			return quoteIdentByType(dbType, raw)
		}
		if schema == "" {
			return quoteIdentByType(dbType, table)
		}
		return quoteIdentByType(dbType, schema) + "." + quoteIdentByType(dbType, table)
	}

	parts := strings.Split(raw, ".")
	if len(parts) <= 1 {
		return quoteIdentByType(dbType, raw)
	}

	quotedParts := make([]string, 0, len(parts))
	for _, part := range parts {
		part = strings.TrimSpace(part)
		if part == "" {
			continue
		}
		quotedParts = append(quotedParts, quoteIdentByType(dbType, part))
	}

	if len(quotedParts) == 0 {
		return quoteIdentByType(dbType, raw)
	}
	return strings.Join(quotedParts, ".")
}

func writeSQLHeader(w *bufio.Writer, config connection.ConnectionConfig, dbName string) error {
	now := time.Now().Format("2006-01-02 15:04:05")
	if _, err := w.WriteString(fmt.Sprintf("-- GoNavi SQL Export\n-- Time: %s\n", now)); err != nil {
		return err
	}
	if strings.TrimSpace(dbName) != "" {
		if _, err := w.WriteString(fmt.Sprintf("-- Database: %s\n\n", dbName)); err != nil {
			return err
		}
	}

	if strings.ToLower(strings.TrimSpace(config.Type)) == "mysql" && strings.TrimSpace(dbName) != "" {
		if _, err := w.WriteString(fmt.Sprintf("USE %s;\n\n", quoteIdentByType("mysql", dbName))); err != nil {
			return err
		}
		if _, err := w.WriteString("SET FOREIGN_KEY_CHECKS=0;\n\n"); err != nil {
			return err
		}
	}

	return nil
}

func writeSQLFooter(w *bufio.Writer, config connection.ConnectionConfig) error {
	if strings.ToLower(strings.TrimSpace(config.Type)) == "mysql" {
		if _, err := w.WriteString("\nSET FOREIGN_KEY_CHECKS=1;\n"); err != nil {
			return err
		}
	}
	return nil
}

func qualifyTable(schemaName, tableName string) string {
	schemaName = strings.TrimSpace(schemaName)
	tableName = strings.TrimSpace(tableName)
	if schemaName == "" {
		return tableName
	}
	return schemaName + "." + tableName
}

func ensureSQLTerminator(sql string) string {
	trimmed := strings.TrimSpace(sql)
	if trimmed == "" {
		return sql
	}
	if strings.HasSuffix(trimmed, ";") {
		return sql
	}
	return sql + ";"
}

func buildExportObjectOrder(
	config connection.ConnectionConfig,
	dbName string,
	rawObjects []string,
	viewLookup map[string]string,
	includeAllViews bool,
) []string {
	tableSet := make(map[string]string, len(rawObjects))
	viewSet := make(map[string]string, len(rawObjects))

	for _, rawName := range rawObjects {
		objectName := strings.TrimSpace(rawName)
		if objectName == "" {
			continue
		}
		key := normalizeExportObjectKey(config, dbName, objectName)
		if key == "" {
			continue
		}
		if canonicalViewName, ok := viewLookup[key]; ok {
			if strings.TrimSpace(canonicalViewName) == "" {
				canonicalViewName = objectName
			}
			viewSet[key] = canonicalViewName
			delete(tableSet, key)
			continue
		}
		if _, isView := viewSet[key]; isView {
			continue
		}
		if _, exists := tableSet[key]; !exists {
			tableSet[key] = objectName
		}
	}

	if includeAllViews {
		for key, viewName := range viewLookup {
			canonicalViewName := strings.TrimSpace(viewName)
			if canonicalViewName == "" {
				continue
			}
			viewSet[key] = canonicalViewName
			delete(tableSet, key)
		}
	}

	tables := mapValuesSorted(tableSet)
	views := mapValuesSorted(viewSet)
	return append(tables, views...)
}

func mapValuesSorted(values map[string]string) []string {
	if len(values) == 0 {
		return nil
	}
	result := make([]string, 0, len(values))
	for _, value := range values {
		value = strings.TrimSpace(value)
		if value == "" {
			continue
		}
		result = append(result, value)
	}
	sort.Strings(result)
	return result
}

func normalizeExportObjectKey(config connection.ConnectionConfig, dbName string, objectName string) string {
	schemaName, pureName := normalizeSchemaAndTable(config, dbName, objectName)
	return normalizeExportObjectKeyByParts(schemaName, pureName)
}

func normalizeExportObjectKeyByParts(schemaName, objectName string) string {
	return strings.ToLower(strings.TrimSpace(qualifyTable(schemaName, objectName)))
}

func listViewNameLookup(dbInst db.Database, config connection.ConnectionConfig, dbName string) map[string]string {
	viewLookup := make(map[string]string)
	queries := buildListViewQueries(config, dbName)
	for _, query := range queries {
		if strings.TrimSpace(query) == "" {
			continue
		}
		rows, _, err := queryDataForExport(dbInst, config, query)
		if err != nil {
			continue
		}
		for _, row := range rows {
			tableType := strings.ToUpper(exportRowValueCI(row, "table_type", "type"))
			if tableType != "" && tableType != "VIEW" {
				continue
			}
			schemaName := exportRowValueCI(row, "schema_name", "table_schema", "owner", "schema", "db")
			viewName := exportRowValueCI(row, "object_name", "view_name", "table_name", "name")
			if viewName == "" {
				viewName = exportInferObjectName(row)
			}
			if strings.TrimSpace(viewName) == "" {
				continue
			}
			fullName := strings.TrimSpace(qualifyTable(schemaName, viewName))
			if fullName == "" {
				fullName = strings.TrimSpace(viewName)
			}
			key := normalizeExportObjectKey(config, dbName, fullName)
			if key == "" {
				continue
			}
			if _, exists := viewLookup[key]; !exists {
				viewLookup[key] = fullName
			}
		}
	}
	return viewLookup
}

func buildListViewQueries(config connection.ConnectionConfig, dbName string) []string {
	dbType := resolveDDLDBType(config)
	escapedDbName := escapeSQLLiteral(dbName)
	switch dbType {
	case "mysql", "mariadb", "oceanbase", "diros", "starrocks", "sphinx":
		queries := []string{
			fmt.Sprintf(`SELECT TABLE_SCHEMA AS schema_name, TABLE_NAME AS object_name, TABLE_TYPE AS table_type FROM information_schema.tables WHERE TABLE_TYPE='VIEW' AND TABLE_SCHEMA='%s' ORDER BY TABLE_NAME`, escapedDbName),
		}
		if strings.TrimSpace(dbName) != "" {
			queries = append(queries, fmt.Sprintf("SHOW FULL TABLES FROM %s WHERE Table_type = 'VIEW'", quoteIdentByType("mysql", dbName)))
		}
		return queries
	case "postgres", "kingbase", "highgo", "vastbase", "opengauss":
		return []string{
			`SELECT table_schema AS schema_name, table_name AS object_name FROM information_schema.views WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name`,
		}
	case "sqlserver":
		safeDBName := strings.TrimSpace(config.Database)
		if safeDBName == "" {
			safeDBName = strings.TrimSpace(dbName)
		}
		if safeDBName == "" {
			return nil
		}
		safeDB := quoteIdentByType("sqlserver", safeDBName)
		return []string{
			fmt.Sprintf(`SELECT s.name AS schema_name, v.name AS object_name FROM %s.sys.views v JOIN %s.sys.schemas s ON v.schema_id = s.schema_id ORDER BY s.name, v.name`, safeDB, safeDB),
		}
	case "oracle", "dameng":
		if strings.TrimSpace(dbName) == "" {
			return []string{
				`SELECT VIEW_NAME AS object_name FROM user_views ORDER BY VIEW_NAME`,
			}
		}
		return []string{
			fmt.Sprintf("SELECT OWNER AS schema_name, VIEW_NAME AS object_name FROM all_views WHERE OWNER = '%s' ORDER BY VIEW_NAME", strings.ToUpper(escapedDbName)),
		}
	case "sqlite":
		return []string{
			"SELECT name AS object_name FROM sqlite_master WHERE type='view' ORDER BY name",
		}
	case "duckdb":
		return []string{
			`SELECT table_schema AS schema_name, table_name AS object_name FROM information_schema.views WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY table_schema, table_name`,
		}
	case "clickhouse":
		if strings.TrimSpace(dbName) == "" {
			return []string{
				`SELECT database AS schema_name, name AS object_name FROM system.tables WHERE engine LIKE '%View%' ORDER BY database, name`,
			}
		}
		return []string{
			fmt.Sprintf(`SELECT database AS schema_name, name AS object_name FROM system.tables WHERE engine LIKE '%%View%%' AND database='%s' ORDER BY name`, escapedDbName),
		}
	default:
		if strings.TrimSpace(dbName) == "" {
			return []string{
				`SELECT table_schema AS schema_name, table_name AS object_name FROM information_schema.views`,
			}
		}
		return []string{
			fmt.Sprintf(`SELECT table_schema AS schema_name, table_name AS object_name FROM information_schema.views WHERE table_schema='%s'`, escapedDbName),
		}
	}
}

func tryGetViewCreateStatement(
	dbInst db.Database,
	config connection.ConnectionConfig,
	dbName string,
	schemaName string,
	viewName string,
) (string, bool) {
	queries := buildViewCreateQueries(config, dbName, schemaName, viewName)
	for _, query := range queries {
		if strings.TrimSpace(query) == "" {
			continue
		}
		rows, _, err := queryDataForExport(dbInst, config, query)
		if err != nil || len(rows) == 0 {
			continue
		}
		createSQL := strings.TrimSpace(extractViewCreateSQL(rows[0]))
		if createSQL == "" {
			continue
		}
		if looksLikeSelectOrWith(createSQL) {
			dbType := resolveDDLDBType(config)
			createSQL = fmt.Sprintf("CREATE VIEW %s AS %s", quoteTableIdentByType(dbType, schemaName, viewName), strings.TrimSuffix(strings.TrimSpace(createSQL), ";"))
		}
		return ensureSQLTerminator(createSQL), true
	}
	return "", false
}

func buildViewCreateQueries(config connection.ConnectionConfig, dbName, schemaName, viewName string) []string {
	dbType := resolveDDLDBType(config)
	safeSchema := strings.TrimSpace(schemaName)
	safeView := strings.TrimSpace(viewName)
	if safeView == "" {
		return nil
	}
	escapedSchema := escapeSQLLiteral(safeSchema)
	escapedView := escapeSQLLiteral(safeView)
	escapedDB := escapeSQLLiteral(dbName)

	switch dbType {
	case "mysql", "mariadb", "oceanbase", "diros", "starrocks", "sphinx":
		if safeSchema == "" {
			safeSchema = strings.TrimSpace(dbName)
		}
		if safeSchema != "" {
			return []string{
				fmt.Sprintf("SHOW CREATE VIEW %s.%s", quoteIdentByType("mysql", safeSchema), quoteIdentByType("mysql", safeView)),
			}
		}
		return []string{
			fmt.Sprintf("SHOW CREATE VIEW %s", quoteIdentByType("mysql", safeView)),
		}
	case "postgres", "kingbase", "highgo", "vastbase", "opengauss":
		if safeSchema == "" {
			safeSchema = "public"
		}
		regClassName := fmt.Sprintf(`"%s"."%s"`, strings.ReplaceAll(safeSchema, `"`, `""`), strings.ReplaceAll(safeView, `"`, `""`))
		regClassName = strings.ReplaceAll(regClassName, "'", "''")
		return []string{
			fmt.Sprintf("SELECT pg_get_viewdef('%s'::regclass, true) AS ddl", regClassName),
		}
	case "sqlserver":
		schema := safeSchema
		if schema == "" {
			schema = "dbo"
		}
		safeDBName := strings.TrimSpace(dbName)
		if safeDBName == "" {
			safeDBName = strings.TrimSpace(config.Database)
		}
		if safeDBName == "" {
			return nil
		}
		safeDB := quoteIdentByType("sqlserver", safeDBName)
		return []string{
			fmt.Sprintf(`SELECT m.definition AS ddl
FROM %s.sys.views v
JOIN %s.sys.schemas s ON v.schema_id = s.schema_id
JOIN %s.sys.sql_modules m ON v.object_id = m.object_id
WHERE s.name = '%s' AND v.name = '%s'`,
				safeDB, safeDB, safeDB, escapeSQLLiteral(schema), escapedView),
		}
	case "oracle", "dameng":
		if safeSchema == "" {
			safeSchema = strings.TrimSpace(dbName)
		}
		if safeSchema != "" {
			return []string{
				fmt.Sprintf("SELECT DBMS_METADATA.GET_DDL('VIEW', '%s', '%s') AS ddl FROM DUAL", strings.ToUpper(escapedView), strings.ToUpper(escapeSQLLiteral(safeSchema))),
			}
		}
		return []string{
			fmt.Sprintf("SELECT DBMS_METADATA.GET_DDL('VIEW', '%s') AS ddl FROM DUAL", strings.ToUpper(escapedView)),
		}
	case "sqlite":
		return []string{
			fmt.Sprintf("SELECT sql AS ddl FROM sqlite_master WHERE type='view' AND name='%s'", escapedView),
		}
	case "duckdb":
		if safeSchema == "" {
			safeSchema = "main"
			escapedSchema = "main"
		}
		return []string{
			fmt.Sprintf("SELECT sql AS ddl FROM duckdb_views() WHERE view_name = '%s' AND schema_name = '%s' LIMIT 1", escapedView, escapedSchema),
			fmt.Sprintf("SELECT view_definition AS ddl FROM information_schema.views WHERE table_name = '%s' AND table_schema = '%s' LIMIT 1", escapedView, escapedSchema),
		}
	case "clickhouse":
		if safeSchema == "" {
			safeSchema = strings.TrimSpace(dbName)
		}
		if safeSchema != "" {
			return []string{
				fmt.Sprintf("SHOW CREATE TABLE %s.%s", quoteIdentByType("clickhouse", safeSchema), quoteIdentByType("clickhouse", safeView)),
			}
		}
		return []string{
			fmt.Sprintf("SHOW CREATE TABLE %s", quoteIdentByType("clickhouse", safeView)),
		}
	default:
		if safeSchema != "" {
			return []string{
				fmt.Sprintf("SELECT view_definition AS ddl FROM information_schema.views WHERE table_name = '%s' AND table_schema = '%s' LIMIT 1", escapedView, escapedSchema),
			}
		}
		if strings.TrimSpace(dbName) != "" {
			return []string{
				fmt.Sprintf("SELECT view_definition AS ddl FROM information_schema.views WHERE table_name = '%s' AND table_schema = '%s' LIMIT 1", escapedView, escapedDB),
			}
		}
		return []string{
			fmt.Sprintf("SELECT view_definition AS ddl FROM information_schema.views WHERE table_name = '%s' LIMIT 1", escapedView),
		}
	}
}

func extractViewCreateSQL(row map[string]interface{}) string {
	if row == nil {
		return ""
	}
	ddl := exportRowValueCI(row, "create view", "create_statement", "create_sql", "ddl", "sql", "view_definition", "definition")
	if ddl != "" {
		return normalizeMySQLViewCreateSQL(ddl)
	}
	for _, value := range row {
		if value == nil {
			continue
		}
		text := strings.TrimSpace(fmt.Sprintf("%v", value))
		if text == "" || text == "<nil>" {
			continue
		}
		lower := strings.ToLower(text)
		if strings.HasPrefix(lower, "create ") || strings.HasPrefix(lower, "select ") || strings.HasPrefix(lower, "with ") {
			return normalizeMySQLViewCreateSQL(text)
		}
	}
	return ""
}

func normalizeMySQLViewCreateSQL(sql string) string {
	trimmed := strings.TrimSpace(strings.TrimSuffix(strings.TrimSpace(sql), ";"))
	if trimmed == "" {
		return ""
	}
	if mysqlCreateViewPrefixPattern.MatchString(trimmed) {
		return mysqlCreateViewPrefixPattern.ReplaceAllString(trimmed, "CREATE OR REPLACE VIEW ")
	}
	return trimmed
}

func exportRowValueCI(row map[string]interface{}, candidates ...string) string {
	if len(row) == 0 || len(candidates) == 0 {
		return ""
	}
	for _, candidate := range candidates {
		candidate = strings.ToLower(strings.TrimSpace(candidate))
		if candidate == "" {
			continue
		}
		for key, value := range row {
			normalizedKey := strings.ToLower(strings.TrimSpace(key))
			if normalizedKey != candidate {
				continue
			}
			if value == nil {
				return ""
			}
			text := strings.TrimSpace(fmt.Sprintf("%v", value))
			if text == "<nil>" {
				return ""
			}
			return text
		}
	}
	return ""
}

func exportInferObjectName(row map[string]interface{}) string {
	if len(row) == 0 {
		return ""
	}
	for key, value := range row {
		normalizedKey := strings.ToLower(strings.TrimSpace(key))
		if normalizedKey == "" {
			continue
		}
		if strings.Contains(normalizedKey, "type") {
			continue
		}
		if strings.Contains(normalizedKey, "table") || strings.Contains(normalizedKey, "view") || strings.Contains(normalizedKey, "name") || strings.Contains(normalizedKey, "ddl") || strings.Contains(normalizedKey, "sql") {
			if value == nil {
				continue
			}
			text := strings.TrimSpace(fmt.Sprintf("%v", value))
			if text == "" || text == "<nil>" {
				continue
			}
			return text
		}
	}
	for _, value := range row {
		if value == nil {
			continue
		}
		text := strings.TrimSpace(fmt.Sprintf("%v", value))
		if text == "" || text == "<nil>" {
			continue
		}
		return text
	}
	return ""
}

func trimLeadingSQLComments(sql string) string {
	trimmed := strings.TrimSpace(sql)
	for trimmed != "" {
		switch {
		case strings.HasPrefix(trimmed, "--"):
			if newline := strings.IndexByte(trimmed, '\n'); newline >= 0 {
				trimmed = strings.TrimSpace(trimmed[newline+1:])
				continue
			}
			return ""
		case strings.HasPrefix(trimmed, "#"):
			if newline := strings.IndexByte(trimmed, '\n'); newline >= 0 {
				trimmed = strings.TrimSpace(trimmed[newline+1:])
				continue
			}
			return ""
		case strings.HasPrefix(trimmed, "/*"):
			if end := strings.Index(trimmed, "*/"); end >= 0 {
				trimmed = strings.TrimSpace(trimmed[end+2:])
				continue
			}
			return ""
		}
		break
	}
	return trimmed
}

func looksLikeSelectOrWith(sql string) bool {
	trimmed := trimLeadingSQLComments(strings.TrimSuffix(sql, ";"))
	if trimmed == "" {
		return false
	}
	lower := strings.ToLower(trimmed)
	return strings.HasPrefix(lower, "select ") || strings.HasPrefix(lower, "with ") || lower == "select" || lower == "with"
}

func escapeSQLLiteral(value string) string {
	return strings.ReplaceAll(strings.TrimSpace(value), "'", "''")
}

func isMySQLHexLiteral(s string) bool {
	if len(s) < 3 || !(strings.HasPrefix(s, "0x") || strings.HasPrefix(s, "0X")) {
		return false
	}
	for i := 2; i < len(s); i++ {
		c := s[i]
		if !((c >= '0' && c <= '9') || (c >= 'a' && c <= 'f') || (c >= 'A' && c <= 'F')) {
			return false
		}
	}
	return true
}

func formatSQLValue(dbType string, v interface{}) string {
	if v == nil {
		return "NULL"
	}

	switch val := v.(type) {
	case bool:
		if val {
			return "1"
		}
		return "0"
	case int:
		return strconv.Itoa(val)
	case int8, int16, int32, int64:
		return fmt.Sprintf("%d", val)
	case uint, uint8, uint16, uint32, uint64:
		return fmt.Sprintf("%d", val)
	case float32:
		f := float64(val)
		if math.IsNaN(f) || math.IsInf(f, 0) {
			return "NULL"
		}
		return strconv.FormatFloat(f, 'f', -1, 32)
	case float64:
		if math.IsNaN(val) || math.IsInf(val, 0) {
			return "NULL"
		}
		return strconv.FormatFloat(val, 'f', -1, 64)
	case time.Time:
		return "'" + val.Format("2006-01-02 15:04:05") + "'"
	case string:
		normalizedType := strings.ToLower(strings.TrimSpace(dbType))
		if (normalizedType == "mysql" || normalizedType == "oceanbase" || normalizedType == "diros" || normalizedType == "starrocks") && isMySQLHexLiteral(val) {
			return val
		}
		escaped := strings.ReplaceAll(val, "'", "''")
		return "'" + escaped + "'"
	default:
		escaped := strings.ReplaceAll(fmt.Sprintf("%v", v), "'", "''")
		return "'" + escaped + "'"
	}
}

func dumpTableSQL(
	w *bufio.Writer,
	dbInst db.Database,
	config connection.ConnectionConfig,
	dbName,
	tableName string,
	includeSchema bool,
	includeData bool,
	viewLookup map[string]string,
) error {
	schemaName, pureTableName := normalizeSchemaAndTable(config, dbName, tableName)
	objectKey := normalizeExportObjectKeyByParts(schemaName, pureTableName)
	_, isView := viewLookup[objectKey]
	var createSQL string

	if includeSchema {
		if isView {
			viewDDL, ok := tryGetViewCreateStatement(dbInst, config, dbName, schemaName, pureTableName)
			if ok {
				createSQL = viewDDL
			} else {
				ddl, err := dbInst.GetCreateStatement(schemaName, pureTableName)
				if err != nil {
					return err
				}
				createSQL = ddl
			}
		} else {
			ddl, err := resolveCreateStatementWithFallback(dbInst, config, dbName, tableName)
			if err != nil {
				if viewDDL, ok := tryGetViewCreateStatement(dbInst, config, dbName, schemaName, pureTableName); ok {
					createSQL = viewDDL
					isView = true
				} else {
					return err
				}
			} else {
				createSQL = ddl
			}
		}
	}

	if includeData && !includeSchema && !isView {
		if _, ok := tryGetViewCreateStatement(dbInst, config, dbName, schemaName, pureTableName); ok {
			isView = true
		}
	}

	objectLabel := "Table"
	if isView {
		objectLabel = "View"
	}

	if _, err := w.WriteString("\n-- ----------------------------\n"); err != nil {
		return err
	}
	if _, err := w.WriteString(fmt.Sprintf("-- %s: %s\n", objectLabel, qualifyTable(schemaName, pureTableName))); err != nil {
		return err
	}
	if _, err := w.WriteString("-- ----------------------------\n\n"); err != nil {
		return err
	}

	if includeSchema {
		if _, err := w.WriteString(ensureSQLTerminator(createSQL)); err != nil {
			return err
		}
		if _, err := w.WriteString("\n\n"); err != nil {
			return err
		}
	}

	if !includeData {
		return nil
	}

	if isView {
		if _, err := w.WriteString("-- View data export skipped (INSERT for views is not emitted).\n"); err != nil {
			return err
		}
		return nil
	}

	qualified := qualifyTable(schemaName, pureTableName)
	dbType := resolveDDLDBType(config)
	selectSQL := fmt.Sprintf("SELECT * FROM %s", quoteQualifiedIdentByType(dbType, qualified))
	data, columns, err := queryDataForExport(dbInst, config, selectSQL)
	if err != nil {
		return err
	}
	columnTypeMap := map[string]string{}
	if defs, colErr := dbInst.GetColumns(schemaName, pureTableName); colErr == nil {
		columnTypeMap = buildImportColumnTypeMap(defs)
	}
	if len(data) == 0 {
		if _, err := w.WriteString("-- (0 rows)\n"); err != nil {
			return err
		}
		return nil
	}

	quotedCols := make([]string, 0, len(columns))
	for _, c := range columns {
		quotedCols = append(quotedCols, quoteIdentByType(dbType, c))
	}
	quotedTable := quoteQualifiedIdentByType(dbType, qualified)

	for _, row := range data {
		values := make([]string, 0, len(columns))
		for _, c := range columns {
			values = append(values, formatImportSQLValue(dbType, columnTypeMap[normalizeColumnName(c)], row[c]))
		}
		if _, err := w.WriteString(fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s);\n", quotedTable, strings.Join(quotedCols, ", "), strings.Join(values, ", "))); err != nil {
			return err
		}
	}

	return nil
}

// ExportData exports provided data to a file
func (a *App) ExportData(data []map[string]interface{}, columns []string, defaultName string, format string) connection.QueryResult {
	if defaultName == "" {
		defaultName = "export"
	}
	logger.Infof("ExportData 开始：rows=%d cols=%d format=%s defaultName=%s", len(data), len(columns), strings.ToLower(strings.TrimSpace(format)), strings.TrimSpace(defaultName))
	filename, err := runtime.SaveFileDialog(a.ctx, runtime.SaveDialogOptions{
		Title:           "Export Data",
		DefaultFilename: fmt.Sprintf("%s.%s", defaultName, strings.ToLower(format)),
	})

	if err != nil || filename == "" {
		logger.Infof("ExportData 已取消或未选择文件：err=%v", err)
		return connection.QueryResult{Success: false, Message: "已取消"}
	}
	logger.Infof("ExportData 选定文件：%s", filename)

	f, err := os.Create(filename)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	defer f.Close()
	if err := writeRowsToFile(f, data, columns, format); err != nil {
		logger.Warnf("ExportData 写入失败：file=%s err=%v", filename, err)
		return connection.QueryResult{Success: false, Message: "写入失败：" + err.Error()}
	}

	logger.Infof("ExportData 完成：file=%s rows=%d", filename, len(data))
	return connection.QueryResult{Success: true, Message: "导出完成"}
}

// ExportQuery exports by executing the provided SELECT query on backend side.
// This avoids frontend IPC payload limits when exporting very large/long-text columns (e.g. base64).
func (a *App) ExportQuery(config connection.ConnectionConfig, dbName string, query string, defaultName string, format string) connection.QueryResult {
	query = strings.TrimSpace(query)
	if query == "" {
		return connection.QueryResult{Success: false, Message: "查询语句不能为空"}
	}

	if defaultName == "" {
		defaultName = "export"
	}

	filename, err := runtime.SaveFileDialog(a.ctx, runtime.SaveDialogOptions{
		Title:           "Export Query Result",
		DefaultFilename: fmt.Sprintf("%s.%s", defaultName, strings.ToLower(format)),
	})
	if err != nil || filename == "" {
		logger.Infof("ExportQuery 已取消或未选择文件：err=%v", err)
		return connection.QueryResult{Success: false, Message: "已取消"}
	}
	logger.Infof("ExportQuery 开始：type=%s db=%s format=%s file=%s sql=%q", strings.TrimSpace(config.Type), strings.TrimSpace(dbName), strings.ToLower(strings.TrimSpace(format)), filename, sqlSnippet(query))

	runConfig := normalizeRunConfig(config, dbName)
	dbInst, err := a.getDatabase(runConfig)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	query = sanitizeSQLForPgLike(resolveDDLDBType(config), query)
	if !looksLikeSelectOrWith(query) {
		return connection.QueryResult{Success: false, Message: "仅支持 SELECT/WITH 查询导出"}
	}

	data, columns, err := queryDataForExport(dbInst, runConfig, query)
	if err != nil {
		logger.Warnf("ExportQuery 查询失败：type=%s db=%s err=%v sql=%q", strings.TrimSpace(config.Type), strings.TrimSpace(dbName), err, sqlSnippet(query))
		return connection.QueryResult{Success: false, Message: err.Error()}
	}

	f, err := os.Create(filename)
	if err != nil {
		return connection.QueryResult{Success: false, Message: err.Error()}
	}
	defer f.Close()

	if err := writeRowsToFile(f, data, columns, format); err != nil {
		logger.Warnf("ExportQuery 写入失败：file=%s err=%v", filename, err)
		return connection.QueryResult{Success: false, Message: "写入失败：" + err.Error()}
	}

	logger.Infof("ExportQuery 完成：file=%s rows=%d cols=%d", filename, len(data), len(columns))
	return connection.QueryResult{Success: true, Message: "导出完成"}
}

func queryDataForExport(dbInst db.Database, config connection.ConnectionConfig, query string) ([]map[string]interface{}, []string, error) {
	timeout := getExportQueryTimeout(config)
	dbType := resolveDDLDBType(config)
	if dbType == "clickhouse" {
		logger.Infof("ClickHouse 导出查询开始：timeout=%s SQL片段=%q", timeout, sqlSnippet(query))
	}
	if q, ok := dbInst.(interface {
		QueryContext(context.Context, string) ([]map[string]interface{}, []string, error)
	}); ok {
		ctx, cancel := utils.ContextWithTimeout(timeout)
		defer cancel()
		data, columns, err := q.QueryContext(ctx, query)
		if err != nil && dbType == "clickhouse" {
			logger.Warnf("ClickHouse 导出查询失败：timeout=%s SQL片段=%q err=%v", timeout, sqlSnippet(query), err)
		}
		return data, columns, err
	}
	data, columns, err := dbInst.Query(query)
	if err != nil && dbType == "clickhouse" {
		logger.Warnf("ClickHouse 导出查询失败（无 QueryContext）：timeout=%s SQL片段=%q err=%v", timeout, sqlSnippet(query), err)
	}
	return data, columns, err
}

func getExportQueryTimeout(config connection.ConnectionConfig) time.Duration {
	timeout := time.Duration(config.Timeout) * time.Second
	if timeout <= 0 {
		timeout = minExportQueryTimeout
	}
	if resolveDDLDBType(config) == "clickhouse" {
		if timeout < minClickHouseExportQueryTimeout {
			timeout = minClickHouseExportQueryTimeout
		}
		return timeout
	}
	if timeout < minExportQueryTimeout {
		timeout = minExportQueryTimeout
	}
	return timeout
}

func writeRowsToFile(f *os.File, data []map[string]interface{}, columns []string, format string) error {
	format = strings.ToLower(strings.TrimSpace(format))
	if f == nil {
		return fmt.Errorf("file required")
	}

	// xlsx 使用 excelize 写入真正的 Excel 格式
	if format == "xlsx" {
		return writeRowsToXlsx(f.Name(), data, columns)
	}

	// html 使用内嵌 CSS 输出可直接浏览器预览的独立页面
	if format == "html" {
		return writeRowsToHTML(f, data, columns)
	}

	// 如果列名为空但数据不为空，从所有数据行提取所有键
	if len(columns) == 0 && len(data) > 0 {
		keySet := make(map[string]bool)
		for _, row := range data {
			for key := range row {
				keySet[key] = true
			}
		}
		// 排序以确保输出一致
		for key := range keySet {
			columns = append(columns, key)
		}
		sort.Strings(columns)
	}

	var csvWriter *csv.Writer
	var jsonEncoder *json.Encoder
	isJsonFirstRow := true

	switch format {
	case "csv":
		if _, err := f.Write([]byte{0xEF, 0xBB, 0xBF}); err != nil {
			return err
		}
		csvWriter = csv.NewWriter(f)
		if err := csvWriter.Write(columns); err != nil {
			return err
		}
	case "json":
		if _, err := f.WriteString("[\n"); err != nil {
			return err
		}
		jsonEncoder = json.NewEncoder(f)
		jsonEncoder.SetIndent("  ", "  ")
	case "md":
		if _, err := fmt.Fprintf(f, "| %s |\n", strings.Join(columns, " | ")); err != nil {
			return err
		}
		seps := make([]string, len(columns))
		for i := range seps {
			seps[i] = "---"
		}
		if _, err := fmt.Fprintf(f, "| %s |\n", strings.Join(seps, " | ")); err != nil {
			return err
		}
	default:
		return fmt.Errorf("unsupported format: %s", format)
	}

	for _, rowMap := range data {
		record := make([]string, len(columns))
		for i, col := range columns {
			val := rowMap[col]
			if val == nil {
				record[i] = "NULL"
				continue
			}

			s := formatExportCellText(val)
			if format == "md" {
				s = strings.ReplaceAll(s, "|", "\\|")
				s = strings.ReplaceAll(s, "\n", "<br>")
			}
			record[i] = s
		}

		switch format {
		case "csv":
			if err := csvWriter.Write(record); err != nil {
				return err
			}
		case "json":
			if !isJsonFirstRow {
				if _, err := f.WriteString(",\n"); err != nil {
					return err
				}
			}
			exportedRow := make(map[string]interface{}, len(columns))
			for _, col := range columns {
				exportedRow[col] = normalizeExportJSONValue(rowMap[col])
			}
			if err := jsonEncoder.Encode(exportedRow); err != nil {
				return err
			}
			isJsonFirstRow = false
		case "md":
			if _, err := fmt.Fprintf(f, "| %s |\n", strings.Join(record, " | ")); err != nil {
				return err
			}
		}
	}

	if format == "csv" {
		csvWriter.Flush()
		if err := csvWriter.Error(); err != nil {
			return err
		}
	}

	if format == "json" {
		if _, err := f.WriteString("\n]"); err != nil {
			return err
		}
	}

	return nil
}

func formatExportHTMLCell(val interface{}) string {
	text := formatExportCellText(val)
	escaped := html.EscapeString(text)
	escaped = strings.ReplaceAll(escaped, "\r\n", "\n")
	escaped = strings.ReplaceAll(escaped, "\r", "\n")
	return strings.ReplaceAll(escaped, "\n", "<br>")
}

func writeRowsToHTML(f *os.File, data []map[string]interface{}, columns []string) error {
	w := bufio.NewWriterSize(f, 1024*256)

	if _, err := w.WriteString(`<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>GoNavi Export</title>
  <style>
    :root {
      color-scheme: light;
      --bg: #f8f9fa;
      --card: #ffffff;
      --line: #dee2e6;
      --text: #212529;
      --muted: #6c757d;
      --hover: #f1f3f5;
      --zebra: #f8f9fa;
      --head: #ffffff;
    }
    * { box-sizing: border-box; }
    body {
      margin: 0;
      padding: 24px;
      background: var(--bg);
      color: var(--text);
      font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, "Noto Sans", "PingFang SC", "Microsoft YaHei", sans-serif;
      line-height: 1.6;
    }
    .export-wrap {
      max-width: 100%;
      margin: 0 auto;
      background: var(--card);
      border: 1px solid var(--line);
      border-radius: 8px;
      overflow: hidden;
    }
    .export-head {
      padding: 16px 20px;
      background: var(--head);
      border-bottom: 2px solid var(--line);
    }
    .export-head h1 {
      margin: 0;
      font-size: 16px;
      font-weight: 600;
      color: var(--text);
    }
    .export-meta {
      margin-top: 6px;
      color: var(--muted);
      font-size: 13px;
    }
    .table-wrap {
      width: 100%;
      overflow: auto;
      padding: 16px;
    }
    table {
      border-collapse: collapse;
      width: auto;
      font-size: 13px;
    }
    thead th {
      position: sticky;
      top: 0;
      z-index: 2;
      background: var(--head);
      text-align: left;
      font-weight: 600;
      white-space: nowrap;
      border-bottom: 2px solid var(--line);
      color: var(--text);
      padding: 12px 16px;
    }
    td {
      padding: 10px 16px;
      border-bottom: 1px solid var(--line);
      vertical-align: top;
      white-space: pre-wrap;
      word-wrap: break-word;
      overflow-wrap: anywhere;
      max-width: 500px;
      color: var(--text);
    }
    tbody tr:nth-child(even) {
      background: var(--zebra);
    }
    tbody tr:hover {
      background: var(--hover);
    }
    td.empty {
      text-align: center;
      color: var(--muted);
      font-style: italic;
    }
    @media (max-width: 768px) {
      body { padding: 16px; }
      .export-head { padding: 12px 16px; }
      .table-wrap { padding: 12px; }
      th, td { padding: 8px 12px; font-size: 12px; }
    }
    @media print {
      body { background: white; padding: 0; }
      .export-wrap { border: none; }
    }
  </style>
</head>
<body>
  <div class="export-wrap">
    <div class="export-head">
      <h1>GoNavi Data Export</h1>
      <div class="export-meta">`); err != nil {
		return err
	}

	if _, err := fmt.Fprintf(w, "Rows: %d · Columns: %d · Generated: %s", len(data), len(columns), time.Now().Format("2006-01-02 15:04:05")); err != nil {
		return err
	}

	if _, err := w.WriteString(`</div>
    </div>
    <div class="table-wrap">
      <table>
        <thead><tr>`); err != nil {
		return err
	}

	for _, col := range columns {
		if _, err := fmt.Fprintf(w, "<th>%s</th>", html.EscapeString(col)); err != nil {
			return err
		}
	}

	if _, err := w.WriteString(`</tr></thead><tbody>`); err != nil {
		return err
	}

	if len(data) == 0 {
		colspan := len(columns)
		if colspan <= 0 {
			colspan = 1
		}
		if _, err := fmt.Fprintf(w, `<tr><td class="empty" colspan="%d">(0 rows)</td></tr>`, colspan); err != nil {
			return err
		}
	} else {
		for _, rowMap := range data {
			if _, err := w.WriteString("<tr>"); err != nil {
				return err
			}
			for _, col := range columns {
				if _, err := fmt.Fprintf(w, "<td>%s</td>", formatExportHTMLCell(rowMap[col])); err != nil {
					return err
				}
			}
			if _, err := w.WriteString("</tr>"); err != nil {
				return err
			}
		}
	}

	if _, err := w.WriteString(`</tbody></table>
    </div>
  </div>
</body>
</html>`); err != nil {
		return err
	}

	return w.Flush()
}

func formatExportCellText(val interface{}) string {
	if val == nil {
		return "NULL"
	}

	switch v := val.(type) {
	case time.Time:
		return v.Format("2006-01-02 15:04:05")
	case *time.Time:
		if v == nil {
			return "NULL"
		}
		return v.Format("2006-01-02 15:04:05")
	case float32:
		f := float64(v)
		if math.IsNaN(f) || math.IsInf(f, 0) {
			return "NULL"
		}
		return strconv.FormatFloat(f, 'f', -1, 32)
	case float64:
		if math.IsNaN(v) || math.IsInf(v, 0) {
			return "NULL"
		}
		return strconv.FormatFloat(v, 'f', -1, 64)
	case json.Number:
		text := strings.TrimSpace(v.String())
		if text == "" {
			return "NULL"
		}
		return text
	default:
		text := fmt.Sprintf("%v", val)
		// 字符串型日期时间值（如 RFC3339 "2026-03-10T17:01:55+08:00"）统一格式化为 yyyy-MM-dd HH:mm:ss
		if parsed, ok := parseTemporalString(text); ok {
			return parsed.Format("2006-01-02 15:04:05")
		}
		return text
	}
}

func normalizeExportJSONValue(val interface{}) interface{} {
	if val == nil {
		return nil
	}

	switch v := val.(type) {
	case time.Time:
		return v.Format("2006-01-02 15:04:05")
	case *time.Time:
		if v == nil {
			return nil
		}
		return v.Format("2006-01-02 15:04:05")
	case string:
		if parsed, ok := parseTemporalString(v); ok {
			return parsed.Format("2006-01-02 15:04:05")
		}
		return v
	case float32:
		f := float64(v)
		if math.IsNaN(f) || math.IsInf(f, 0) {
			return nil
		}
		return json.Number(strconv.FormatFloat(f, 'f', -1, 32))
	case float64:
		if math.IsNaN(v) || math.IsInf(v, 0) {
			return nil
		}
		return json.Number(strconv.FormatFloat(v, 'f', -1, 64))
	case json.Number:
		text := strings.TrimSpace(v.String())
		if text == "" {
			return nil
		}
		return json.Number(text)
	case map[string]interface{}:
		out := make(map[string]interface{}, len(v))
		for key, item := range v {
			out[key] = normalizeExportJSONValue(item)
		}
		return out
	case []interface{}:
		items := make([]interface{}, len(v))
		for i, item := range v {
			items[i] = normalizeExportJSONValue(item)
		}
		return items
	}

	rv := reflect.ValueOf(val)
	switch rv.Kind() {
	case reflect.Pointer, reflect.Interface:
		if rv.IsNil() {
			return nil
		}
		return normalizeExportJSONValue(rv.Elem().Interface())
	case reflect.Map:
		if rv.IsNil() {
			return nil
		}
		out := make(map[string]interface{}, rv.Len())
		iter := rv.MapRange()
		for iter.Next() {
			out[fmt.Sprint(iter.Key().Interface())] = normalizeExportJSONValue(iter.Value().Interface())
		}
		return out
	case reflect.Slice:
		if rv.IsNil() {
			return nil
		}
		if rv.Type().Elem().Kind() == reflect.Uint8 {
			return val
		}
		fallthrough
	case reflect.Array:
		size := rv.Len()
		items := make([]interface{}, size)
		for i := 0; i < size; i++ {
			items[i] = normalizeExportJSONValue(rv.Index(i).Interface())
		}
		return items
	default:
		return val
	}
}

// writeRowsToXlsx 使用 excelize 写入真正的 xlsx 格式文件
func writeRowsToXlsx(filename string, data []map[string]interface{}, columns []string) error {
	xlsx := excelize.NewFile()
	defer xlsx.Close()

	sheet := "Sheet1"

	// 写入表头
	for i, col := range columns {
		cell, _ := excelize.CoordinatesToCellName(i+1, 1)
		xlsx.SetCellValue(sheet, cell, col)
	}

	// 写入数据行
	for rowIdx, rowMap := range data {
		for colIdx, col := range columns {
			cell, _ := excelize.CoordinatesToCellName(colIdx+1, rowIdx+2)
			val := rowMap[col]
			if val == nil {
				xlsx.SetCellValue(sheet, cell, "NULL")
			} else {
				xlsx.SetCellValue(sheet, cell, formatExportCellText(val))
			}
		}
	}

	return xlsx.SaveAs(filename)
}
