package main

import (
	"bytes"
	"context"
	"crypto/rand"
	"database/sql"
	"encoding/base64"
	"encoding/csv"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"io/ioutil"
	"net"
	"net/http"
	"os"
	"path/filepath"
	"runtime"
	"strings"
	"time"

	_ "github.com/mattn/go-sqlite3"
	wailsruntime "github.com/wailsapp/wails/v2/pkg/runtime"
	"github.com/xuri/excelize/v2"
	"golang.org/x/crypto/bcrypt"
)

// Employee 员工结构体
type Employee struct {
	ID          int    `json:"id"`
	Name        string `json:"name"`
	Department  string `json:"department"`
	Position    string `json:"position"`
	Phone       string `json:"phone"`
	Email       string `json:"email"`
	Type        string `json:"type"`        // "合同制", "外包人员" 等
	Status      string `json:"status"`      // "在职", "离职"
	JoinDate    string `json:"join_date"`
	PhotoBase64 string `json:"photo_base64"` // 存储图片的Base64编码
}

// User 用户结构体
type User struct {
	ID              int    `json:"id"`
	Username        string `json:"username"`
	PasswordHash    string `json:"-"` // 不返回给前端
	Email           string `json:"email"`
	FullName        string `json:"full_name"`
	Department      string `json:"department"`
	Position        string `json:"position"`
	Phone           string `json:"phone"`
	AvatarBase64    string `json:"avatar_base64"`
	Role            string `json:"role"`
	Status          string `json:"status"`
	JoinDate        string `json:"join_date"`
	CreatedAt       string `json:"created_at"`
	UpdatedAt       string `json:"updated_at"`
	LastLoginTime   string `json:"last_login_time"`
	LastLoginMethod string `json:"last_login_method"`
}

// UserActivity 用户活动记录结构体
type UserActivity struct {
	ID          int    `json:"id"`
	UserID      int    `json:"user_id"`
	Action      string `json:"action"`
	IPAddress   string `json:"ip_address"`
	UserAgent   string `json:"user_agent"`
	CreatedAt   string `json:"created_at"`
}

// UserSetting 用户设置结构体
type UserSetting struct {
	ID           int    `json:"id"`
	UserID       int    `json:"user_id"`
	SettingKey   string `json:"setting_key"`
	SettingValue string `json:"setting_value"`
	CreatedAt    string `json:"created_at"`
	UpdatedAt    string `json:"updated_at"`
}

// AIChatSession AI聊天会话结构体
type AIChatSession struct {
	ID        int    `json:"id"`
	UserID    int    `json:"user_id"`
	SessionID string `json:"session_id"` // 前端会话ID
	Title     string `json:"title"`
	CreatedAt string `json:"created_at"`
	UpdatedAt string `json:"updated_at"`
}

// AIChatMessage AI聊天消息结构体
type AIChatMessage struct {
	ID        int    `json:"id"`
	SessionID int    `json:"session_id"` // 关联到AIChatSession的ID
	MessageID string `json:"message_id"` // 前端消息ID
	Content   string `json:"content"`
	Role      string `json:"role"` // 'user' 或 'assistant'
	CreatedAt string `json:"created_at"`
}

// AIUsageStats AI使用统计结构体
type AIUsageStats struct {
	ID                int     `json:"id"`
	UserID            int     `json:"user_id"`           // 用户ID
	StatType          string  `json:"stat_type"`         // 统计类型：connectivity_test(连通性测试) 或 ai_chat(AI聊天)
	TotalCalls        int     `json:"total_calls"`       // 总调用次数
	TodayCalls        int     `json:"today_calls"`       // 今日调用次数
	SuccessfulCalls   int     `json:"successful_calls"`  // 成功调用次数
	FailedCalls       int     `json:"failed_calls"`      // 失败调用次数
	SuccessRate       float64 `json:"success_rate"`      // 成功率（百分比）
	FastestResponse   float64 `json:"fastest_response"`  // 最快响应时间（秒）
	SlowestResponse   float64 `json:"slowest_response"`  // 最慢响应时间（秒）
	TotalResponseTime float64 `json:"total_response_time"` // 总响应时间（秒）
	LastCallTime      string  `json:"last_call_time"`    // 最后调用时间
	LastCallDate      string  `json:"last_call_date"`    // 最后调用日期
	CreatedAt         string  `json:"created_at"`        // 创建时间
	UpdatedAt         string  `json:"updated_at"`        // 更新时间
}

// SystemSetting 系统设置结构体
type SystemSetting struct {
	ID           int    `json:"id"`
	SettingKey   string `json:"setting_key"`
	SettingValue string `json:"setting_value"`
	Description  string `json:"description"`
	CreatedAt    string `json:"created_at"`
	UpdatedAt    string `json:"updated_at"`
}

// AIModel AI模型配置结构体
type AIModel struct {
	ID          int    `json:"id"`
	UserID      int    `json:"user_id"`
	ConfigID    string `json:"config_id"`    // 前端生成的唯一标识符
	Name        string `json:"name"`         // 模型显示名称
	ModelID     string `json:"model_id"`     // 实际的模型名称，如 "deepseek-ai/DeepSeek-V3"
	Description string `json:"description"`  // 模型描述
	Icon        string `json:"icon"`         // 模型图标路径
	CreatedAt   string `json:"created_at"`
	UpdatedAt   string `json:"updated_at"`
}

// AIConfig AI基本配置结构体
type AIConfig struct {
	ID               int    `json:"id"`
	UserID           int    `json:"user_id"`
	APIUrl           string `json:"api_url"`           // API地址
	APIKey           string `json:"api_key"`           // API密钥
	ModelName        string `json:"model_name"`        // 当前使用的模型名称，如 "deepseek-ai/DeepSeek-V3"
	SelectedModelID  string `json:"selected_model_id"` // 当前选中的模型配置ID
	CreatedAt        string `json:"created_at"`
	UpdatedAt        string `json:"updated_at"`
}

// LoginRequest 登录请求结构体
type LoginRequest struct {
	Username    string `json:"username"`
	Password    string `json:"password"`
	RememberMe  bool   `json:"remember_me"`
	LoginMethod string `json:"login_method"` // 登录方式：密码登录、快速登录、会话恢复等
}

// LoginResponse 登录响应结构体
type LoginResponse struct {
	Success      bool   `json:"success"`
	Message      string `json:"message"`
	User         *User  `json:"user,omitempty"`
	SessionToken string `json:"session_token,omitempty"`
}

// App 应用结构体
type App struct {
	ctx context.Context
	db  *sql.DB
}

// NewApp 创建新的应用实例
func NewApp() *App {
	return &App{}
}

// startup 应用启动时调用，初始化数据库
func (a *App) startup(ctx context.Context) {
	a.ctx = ctx
	a.initDatabase()
}

// initDatabase 初始化数据库
func (a *App) initDatabase() {
	// 获取数据库路径
	dbPath := a.getDatabasePath()
	if dbPath == "" {
		wailsruntime.LogError(a.ctx, "无法确定数据库路径")
		return
	}
	
	
	// 打开数据库连接
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		wailsruntime.LogError(a.ctx, "打开数据库失败: "+err.Error())
		return
	}
	
	a.db = db
	
	// 启用外键约束
	_, err = db.Exec("PRAGMA foreign_keys = ON")
	if err != nil {
		wailsruntime.LogError(a.ctx, "启用外键约束失败: "+err.Error())
		return
	}
	wailsruntime.LogInfo(a.ctx, "外键约束已启用")
	
	// 创建员工表
	createEmployeeTableSQL := `
	CREATE TABLE IF NOT EXISTS employees (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		department TEXT,
		position TEXT,
		phone TEXT,
		email TEXT,
		type TEXT,
		status TEXT DEFAULT '在职',
		join_date TEXT,
		photo_base64 TEXT
	);`
	
	// 创建用户表
	createUserTableSQL := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT NOT NULL UNIQUE,
		password_hash TEXT NOT NULL,
		email TEXT,
		full_name TEXT,
		department TEXT,
		position TEXT,
		phone TEXT,
		avatar_base64 TEXT,
		role TEXT DEFAULT '用户',
		status TEXT DEFAULT 'active',
		join_date TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		last_login_time DATETIME,
		last_login_method TEXT
	);`
	
	// 创建用户活动记录表
	createUserActivityTableSQL := `
	CREATE TABLE IF NOT EXISTS user_activities (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		activity_type TEXT NOT NULL,
		ip_address TEXT,
		user_agent TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (user_id) REFERENCES users(id)
	);`
	
	// 创建用户设置表
	createUserSettingTableSQL := `
	CREATE TABLE IF NOT EXISTS user_settings (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		setting_key TEXT NOT NULL,
		setting_value TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (user_id) REFERENCES users(id),
		UNIQUE(user_id, setting_key)
	);`
	
	// 创建系统设置表
	createSystemSettingTableSQL := `
	CREATE TABLE IF NOT EXISTS system_settings (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		setting_key TEXT NOT NULL UNIQUE,
		setting_value TEXT,
		description TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);`
	
	// 创建AI聊天会话表
	createAIChatSessionTableSQL := `
	CREATE TABLE IF NOT EXISTS ai_chat_sessions (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		session_id TEXT NOT NULL UNIQUE,
		title TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (user_id) REFERENCES users(id)
	);`
	
	// 创建AI聊天消息表
	createAIChatMessageTableSQL := `
	CREATE TABLE IF NOT EXISTS ai_chat_messages (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		session_id INTEGER NOT NULL,
		message_id TEXT NOT NULL,
		content TEXT NOT NULL,
		role TEXT NOT NULL CHECK (role IN ('user', 'assistant')),
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (session_id) REFERENCES ai_chat_sessions(id) ON DELETE CASCADE
	);`
	
	// 创建AI使用统计表（带中文备注）
	createAIUsageStatsTableSQL := `
	CREATE TABLE IF NOT EXISTS ai_usage_stats (
		id INTEGER PRIMARY KEY AUTOINCREMENT,                    -- 主键ID
		user_id INTEGER NOT NULL,                                -- 用户ID，关联users表
		stat_type TEXT NOT NULL DEFAULT 'ai_chat',              -- 统计类型：connectivity_test(连通性测试) 或 ai_chat(AI聊天)
		total_calls INTEGER DEFAULT 0,                          -- 总调用次数
		today_calls INTEGER DEFAULT 0,                          -- 今日调用次数
		successful_calls INTEGER DEFAULT 0,                     -- 成功调用次数
		failed_calls INTEGER DEFAULT 0,                         -- 失败调用次数
		success_rate REAL DEFAULT 0.0,                          -- 成功率（百分比）
		fastest_response REAL DEFAULT 0.0,                      -- 最快响应时间（秒）
		slowest_response REAL DEFAULT 0.0,                      -- 最慢响应时间（秒）
		total_response_time REAL DEFAULT 0.0,                   -- 总响应时间（秒）
		last_call_time DATETIME,                                -- 最后调用时间
		last_call_date TEXT,                                    -- 最后调用日期（YYYY-MM-DD格式）
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,          -- 创建时间
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,          -- 更新时间
		FOREIGN KEY (user_id) REFERENCES users(id),             -- 外键约束
		UNIQUE(user_id, stat_type)                              -- 每个用户每种统计类型只能有一条记录
	);`
	
	// 创建AI模型配置表
	createAIModelsTableSQL := `
	CREATE TABLE IF NOT EXISTS ai_models (
		id INTEGER PRIMARY KEY AUTOINCREMENT,                    -- 主键ID
		user_id INTEGER NOT NULL,                                -- 用户ID，关联users表
		config_id TEXT NOT NULL,                                 -- 前端生成的唯一标识符（如时间戳）
		name TEXT NOT NULL,                                      -- 模型显示名称
		model_id TEXT NOT NULL,                                  -- 实际的模型名称，如 "deepseek-ai/DeepSeek-V3"
		description TEXT,                                        -- 模型描述
		icon TEXT,                                               -- 模型图标路径
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,          -- 创建时间
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,          -- 更新时间
		FOREIGN KEY (user_id) REFERENCES users(id),             -- 外键约束
		UNIQUE(user_id, config_id)                              -- 每个用户的配置ID必须唯一
	);`
	
	// 创建AI配置表
	createAIConfigTableSQL := `
	CREATE TABLE IF NOT EXISTS ai_config (
		id INTEGER PRIMARY KEY AUTOINCREMENT,                    -- 主键ID
		user_id INTEGER NOT NULL UNIQUE,                         -- 用户ID，关联users表，每个用户只能有一条配置
		api_url TEXT NOT NULL DEFAULT 'https://api.deepseek.com/v1', -- API地址
		api_key TEXT,                                            -- API密钥
		model_name TEXT DEFAULT 'deepseek-chat',                -- 当前使用的模型名称
		selected_model_id TEXT,                                 -- 当前选中的模型配置ID
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,          -- 创建时间
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,          -- 更新时间
		FOREIGN KEY (user_id) REFERENCES users(id)              -- 外键约束
	);`
	
	// 执行所有表创建语句
	tables := []struct {
		name string
		sql  string
	}{
		{"employees", createEmployeeTableSQL},
		{"users", createUserTableSQL},
		{"user_activities", createUserActivityTableSQL},
		{"user_settings", createUserSettingTableSQL},
		{"system_settings", createSystemSettingTableSQL},
		{"ai_chat_sessions", createAIChatSessionTableSQL},
		{"ai_chat_messages", createAIChatMessageTableSQL},
		{"ai_usage_stats", createAIUsageStatsTableSQL},
		{"ai_models", createAIModelsTableSQL},
		{"ai_config", createAIConfigTableSQL},
	}
	
	for _, table := range tables {
		_, err = db.Exec(table.sql)
		if err != nil {
			wailsruntime.LogError(a.ctx, fmt.Sprintf("创建%s表失败: %s", table.name, err.Error()))
			return
		}
		wailsruntime.LogInfo(a.ctx, fmt.Sprintf("%s表创建成功", table.name))
	}
	
	// 为现有的ai_config表添加selected_model_id字段（如果不存在）
	_, err = db.Exec(`
		ALTER TABLE ai_config ADD COLUMN selected_model_id TEXT;
	`)
	if err != nil {
		// 如果字段已存在，ALTER TABLE会失败，这是正常的
		wailsruntime.LogInfo(a.ctx, "ai_config表的selected_model_id字段可能已存在")
	} else {
		wailsruntime.LogInfo(a.ctx, "ai_config表已添加selected_model_id字段")
	}
	
	// 初始化默认管理员账号
	a.initDefaultAdmin()
	
	wailsruntime.LogInfo(a.ctx, "数据库初始化成功，数据库路径: "+dbPath)
}

// getLocalIP 获取本地IP地址
func (a *App) getLocalIP() string {
	// 获取所有网络接口
	interfaces, err := net.Interfaces()
	if err != nil {
		wailsruntime.LogError(a.ctx, "获取网络接口失败: "+err.Error())
		return "127.0.0.1"
	}

	for _, iface := range interfaces {
		// 跳过回环接口和未启用的接口
		if iface.Flags&net.FlagUp == 0 || iface.Flags&net.FlagLoopback != 0 {
			continue
		}

		// 获取接口的IP地址
		addrs, err := iface.Addrs()
		if err != nil {
			continue
		}

		for _, addr := range addrs {
			var ip net.IP
			switch v := addr.(type) {
			case *net.IPNet:
				ip = v.IP
			case *net.IPAddr:
				ip = v.IP
			}

			// 只返回IPv4地址，并且不是回环地址
			if ip != nil && ip.To4() != nil && !ip.IsLoopback() {
				// 优先返回私有网络地址
				if ip.IsPrivate() {
					return ip.String()
				}
			}
		}
	}

	// 如果没有找到私有IP，再找公网IP
	interfaces, _ = net.Interfaces()
	for _, iface := range interfaces {
		if iface.Flags&net.FlagUp == 0 || iface.Flags&net.FlagLoopback != 0 {
			continue
		}

		addrs, err := iface.Addrs()
		if err != nil {
			continue
		}

		for _, addr := range addrs {
			var ip net.IP
			switch v := addr.(type) {
			case *net.IPNet:
				ip = v.IP
			case *net.IPAddr:
				ip = v.IP
			}

			if ip != nil && ip.To4() != nil && !ip.IsLoopback() {
				return ip.String()
			}
		}
	}

	// 如果都没找到，返回默认值
	return "127.0.0.1"
}

// getDatabasePath 智能获取数据库路径 - 强制使用系统目录
func (a *App) getDatabasePath() string {
	// 获取用户主目录
	homeDir, err := os.UserHomeDir()
	if err != nil {
		wailsruntime.LogError(a.ctx, fmt.Sprintf("无法获取用户主目录: %v", err))
		return ""
	}
	
	var appDataDir string
	// 根据操作系统选择合适的应用数据目录
	switch runtime.GOOS {
	case "darwin":
		// macOS: ~/Library/Application Support/ContactsManager
		appDataDir = filepath.Join(homeDir, "Library", "Application Support", "ContactsManager")
	case "windows":
		// Windows: %APPDATA%\ContactsManager 
		appDataDir = filepath.Join(homeDir, "AppData", "Roaming", "ContactsManager")
	case "linux":
		// Linux: ~/.local/share/ContactsManager
		appDataDir = filepath.Join(homeDir, ".local", "share", "ContactsManager")
	default:
		// 其他系统：~/.ContactsManager
		appDataDir = filepath.Join(homeDir, ".ContactsManager")
	}
	
	// 确保应用数据目录存在
	if err := os.MkdirAll(appDataDir, 0755); err != nil {
		wailsruntime.LogError(a.ctx, fmt.Sprintf("创建应用数据目录失败: %v", err))
		return ""
	}
	
	// 返回数据库文件路径
	dbPath := filepath.Join(appDataDir, "roster.db")
	wailsruntime.LogInfo(a.ctx, fmt.Sprintf("数据库路径: %s", dbPath))
	return dbPath
}

// testWritePermission 测试目录是否有写权限
func (a *App) testWritePermission(dir string) bool {
	testFile := filepath.Join(dir, ".write_test")
	file, err := os.Create(testFile)
	if err != nil {
		return false
	}
	file.Close()
	os.Remove(testFile)
	return true
}

// GetAllEmployees 获取所有员工
func (a *App) GetAllEmployees() ([]Employee, error) {
	if a.db == nil {
		return []Employee{}, fmt.Errorf("数据库未初始化")
	}
	
	rows, err := a.db.Query("SELECT id, name, department, position, phone, email, type, status, join_date, photo_base64 FROM employees ORDER BY id DESC")
	if err != nil {
		return []Employee{}, err
	}
	defer rows.Close()
	
	var employees []Employee
	for rows.Next() {
		var emp Employee
		var photoBase64 sql.NullString
		
		err := rows.Scan(&emp.ID, &emp.Name, &emp.Department, &emp.Position, 
			&emp.Phone, &emp.Email, &emp.Type, &emp.Status, &emp.JoinDate, &photoBase64)
		if err != nil {
			return []Employee{}, err
		}
		
		if photoBase64.Valid {
			emp.PhotoBase64 = photoBase64.String
		}
		
		employees = append(employees, emp)
	}
	
	// 确保始终返回空数组而不是nil
	if employees == nil {
		employees = []Employee{}
	}
	
	return employees, nil
}

// GetEmployeeByID 根据ID获取员工
func (a *App) GetEmployeeByID(id int) (Employee, error) {
	var emp Employee
	var photoBase64 sql.NullString
	
	if a.db == nil {
		return emp, fmt.Errorf("数据库未初始化")
	}
	
	err := a.db.QueryRow("SELECT id, name, department, position, phone, email, type, status, join_date, photo_base64 FROM employees WHERE id = ?", id).
		Scan(&emp.ID, &emp.Name, &emp.Department, &emp.Position, 
			&emp.Phone, &emp.Email, &emp.Type, &emp.Status, &emp.JoinDate, &photoBase64)
	
	if err != nil {
		return emp, err
	}
	
	if photoBase64.Valid {
		emp.PhotoBase64 = photoBase64.String
	}
	
	return emp, nil
}

// CreateEmployee 创建新员工
func (a *App) CreateEmployee(employee Employee) (Employee, error) {
	if a.db == nil {
		return employee, fmt.Errorf("数据库未初始化")
	}
	
	result, err := a.db.Exec(`
		INSERT INTO employees (name, department, position, phone, email, type, status, join_date, photo_base64) 
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		employee.Name, employee.Department, employee.Position, employee.Phone, 
		employee.Email, employee.Type, employee.Status, employee.JoinDate, employee.PhotoBase64)
	
	if err != nil {
		return employee, err
	}
	
	id, err := result.LastInsertId()
	if err != nil {
		return employee, err
	}
	
	employee.ID = int(id)
	return employee, nil
}

// UpdateEmployee 更新员工信息
func (a *App) UpdateEmployee(employee Employee) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		UPDATE employees 
		SET name=?, department=?, position=?, phone=?, email=?, type=?, status=?, join_date=?, photo_base64=? 
		WHERE id=?`,
		employee.Name, employee.Department, employee.Position, employee.Phone, 
		employee.Email, employee.Type, employee.Status, employee.JoinDate, employee.PhotoBase64, employee.ID)
	
	return err
}

// DeleteEmployee 删除员工
func (a *App) DeleteEmployee(id int) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec("DELETE FROM employees WHERE id = ?", id)
	return err
}

// SelectImage 选择图片文件并转换为Base64
func (a *App) SelectImage() (string, error) {
	// 打开文件选择对话框
	filePath, err := wailsruntime.OpenFileDialog(a.ctx, wailsruntime.OpenDialogOptions{
		Title: "选择员工照片",
		Filters: []wailsruntime.FileFilter{
			{
				DisplayName: "图片文件",
				Pattern:     "*.jpg;*.jpeg;*.png;*.gif;*.bmp",
			},
		},
	})
	
	if err != nil {
		return "", err
	}
	
	if filePath == "" {
		return "", fmt.Errorf("未选择文件")
	}
	
	// 读取文件内容
	fileData, err := os.ReadFile(filePath)
	if err != nil {
		return "", fmt.Errorf("读取文件失败: %v", err)
	}
	
	// 检查文件大小（限制为2MB）
	if len(fileData) > 2*1024*1024 {
		return "", fmt.Errorf("图片文件过大，请选择小于2MB的图片")
	}
	
	// 转换为Base64
	base64String := base64.StdEncoding.EncodeToString(fileData)
	
	// 获取文件扩展名来确定MIME类型
	ext := strings.ToLower(filepath.Ext(filePath))
	var mimeType string
	switch ext {
	case ".jpg", ".jpeg":
		mimeType = "image/jpeg"
	case ".png":
		mimeType = "image/png"
	case ".gif":
		mimeType = "image/gif"
	case ".bmp":
		mimeType = "image/bmp"
	default:
		mimeType = "image/jpeg"
	}
	
	// 返回完整的Data URL格式
	return fmt.Sprintf("data:%s;base64,%s", mimeType, base64String), nil
}

// ImportFromCSV 从CSV文件导入员工数据
func (a *App) ImportFromCSV() (string, error) {
	// 打开文件选择对话框
	filePath, err := wailsruntime.OpenFileDialog(a.ctx, wailsruntime.OpenDialogOptions{
		Title: "选择CSV文件",
		Filters: []wailsruntime.FileFilter{
			{
				DisplayName: "CSV文件",
				Pattern:     "*.csv",
			},
		},
	})
	
	if err != nil {
		return "", err
	}
	
	if filePath == "" {
		return "", fmt.Errorf("未选择文件")
	}
	
	// 打开CSV文件
	file, err := os.Open(filePath)
	if err != nil {
		return "", fmt.Errorf("打开文件失败: %v", err)
	}
	defer file.Close()
	
	// 创建CSV读取器
	reader := csv.NewReader(file)
	
	// 读取所有记录
	records, err := reader.ReadAll()
	if err != nil {
		return "", fmt.Errorf("读取CSV文件失败: %v", err)
	}
	
	if len(records) == 0 {
		return "CSV文件为空", nil
	}
	
	// 跳过表头
	if len(records) > 0 {
		records = records[1:]
	}
	
	successCount := 0
	failCount := 0
	
	// 逐行处理数据
	for i, record := range records {
		if len(record) < 8 {
			failCount++
			wailsruntime.LogWarning(a.ctx, fmt.Sprintf("第%d行数据不完整，跳过", i+2))
			continue
		}
		
		employee := Employee{
			Name:       strings.TrimSpace(record[0]),
			Department: strings.TrimSpace(record[1]),
			Position:   strings.TrimSpace(record[2]),
			Phone:      strings.TrimSpace(record[3]),
			Email:      strings.TrimSpace(record[4]),
			Type:       strings.TrimSpace(record[5]),
			Status:     strings.TrimSpace(record[6]),
			JoinDate:   strings.TrimSpace(record[7]),
		}
		
		// 验证必填字段
		if employee.Name == "" {
			failCount++
			wailsruntime.LogWarning(a.ctx, fmt.Sprintf("第%d行姓名为空，跳过", i+2))
			continue
		}
		
		// 设置默认值
		if employee.Status == "" {
			employee.Status = "在职"
		}
		if employee.Type == "" {
			employee.Type = "合同制"
		}
		
		// 创建员工记录
		_, err := a.CreateEmployee(employee)
		if err != nil {
			failCount++
			wailsruntime.LogWarning(a.ctx, fmt.Sprintf("第%d行数据导入失败: %v", i+2, err))
		} else {
			successCount++
		}
	}
	
	return fmt.Sprintf("导入完成！成功导入 %d 条，失败 %d 条", successCount, failCount), nil
}

// GenerateCSVTemplate 生成CSV模板文件
func (a *App) GenerateCSVTemplate() (string, error) {
	// 打开保存文件对话框
	filePath, err := wailsruntime.SaveFileDialog(a.ctx, wailsruntime.SaveDialogOptions{
		Title:           "保存CSV模板",
		DefaultFilename: "员工导入模板.csv",
		Filters: []wailsruntime.FileFilter{
			{
				DisplayName: "CSV文件",
				Pattern:     "*.csv",
			},
		},
	})
	
	if err != nil {
		return "", err
	}
	
	if filePath == "" {
		return "", fmt.Errorf("未选择保存位置")
	}
	
	// 创建CSV文件
	file, err := os.Create(filePath)
	if err != nil {
		return "", fmt.Errorf("创建文件失败: %v", err)
	}
	defer file.Close()
	
	// 写入BOM以支持中文
	file.WriteString("\xEF\xBB\xBF")
	
	// 创建CSV写入器
	writer := csv.NewWriter(file)
	defer writer.Flush()
	
	// 写入表头
	header := []string{"姓名", "团队", "岗位", "电话", "邮箱", "用工类型", "状态", "入职日期"}
	if err := writer.Write(header); err != nil {
		return "", fmt.Errorf("写入表头失败: %v", err)
	}
	
	// 写入示例数据
	examples := [][]string{
		{"张三", "解决方案", "解决方案经理", "13800138001", "zhangsan@company.com", "合同制", "在职", "2024-01-15"},
		{"李四", "二开", "开发工程师", "13800138002", "lisi@company.com", "外包人员", "在职", "2024-02-01"},
	}
	
	for _, example := range examples {
		if err := writer.Write(example); err != nil {
			return "", fmt.Errorf("写入示例数据失败: %v", err)
		}
	}
	
	return fmt.Sprintf("CSV模板已保存到: %s", filePath), nil
}

// GetDatabasePath 获取数据库文件路径
func (a *App) GetDatabasePath() string {
	dbPath := a.getDatabasePath()
	if dbPath == "" {
		return "获取路径失败"
	}
	return dbPath
}

// SystemInfo 系统信息结构体
type SystemInfo struct {
	DatabasePath     string `json:"database_path"`     // 数据库路径
	DatabaseSize     string `json:"database_size"`     // 数据库文件大小
	DatabaseExists   bool   `json:"database_exists"`   // 数据库文件是否存在
	OperatingSystem  string `json:"operating_system"`  // 操作系统
	Architecture     string `json:"architecture"`      // 系统架构
	GoVersion        string `json:"go_version"`        // Go版本
	AppVersion       string `json:"app_version"`       // 应用版本
	BuildTime        string `json:"build_time"`        // 构建时间
}

// GetSystemInfo 获取系统信息
// getFriendlyOSName 将操作系统名称转换为友好显示名称
func (a *App) getFriendlyOSName(osName string) string {
	switch osName {
	case "darwin":
		return "macOS"
	case "windows":
		return "Windows"
	case "linux":
		return "Linux"
	case "freebsd":
		return "FreeBSD"
	case "openbsd":
		return "OpenBSD"
	case "netbsd":
		return "NetBSD"
	default:
		return strings.Title(osName) // 首字母大写
	}
}

// getFriendlyArchName 将架构名称转换为友好显示名称
func (a *App) getFriendlyArchName(archName string) string {
	switch archName {
	case "amd64":
		return "x64"
	case "386":
		return "x86"
	case "arm64":
		return "ARM64"
	case "arm":
		return "ARM"
	default:
		return strings.ToUpper(archName)
	}
}

func (a *App) GetSystemInfo() (SystemInfo, error) {
	info := SystemInfo{
		OperatingSystem: a.getFriendlyOSName(runtime.GOOS),
		Architecture:    a.getFriendlyArchName(runtime.GOARCH),
		GoVersion:       runtime.Version(),
		AppVersion:      "v1.0.0",
		BuildTime:       time.Now().Format("2006-01-02"),
	}
	
	// 获取数据库路径
	dbPath := a.getDatabasePath()
	info.DatabasePath = dbPath
	
	// 检查数据库文件是否存在并获取大小
	if dbPath != "" {
		if stat, err := os.Stat(dbPath); err == nil {
			info.DatabaseExists = true
			// 格式化文件大小
			size := stat.Size()
			if size < 1024 {
				info.DatabaseSize = fmt.Sprintf("%d B", size)
			} else if size < 1024*1024 {
				info.DatabaseSize = fmt.Sprintf("%.2f KB", float64(size)/1024)
			} else {
				info.DatabaseSize = fmt.Sprintf("%.2f MB", float64(size)/(1024*1024))
			}
		} else {
			info.DatabaseExists = false
			info.DatabaseSize = "文件不存在"
		}
	} else {
		info.DatabaseExists = false
		info.DatabaseSize = "路径获取失败"
	}
	
	return info, nil
}

// ExportToExcel 导出员工数据到Excel文件
func (a *App) ExportToExcel() (string, error) {
	if a.db == nil {
		return "", fmt.Errorf("数据库未初始化")
	}

	// 获取所有员工数据
	employees, err := a.GetAllEmployees()
	if err != nil {
		return "", fmt.Errorf("获取员工数据失败: %v", err)
	}

	// 创建Excel文件
	f := excelize.NewFile()
	defer func() {
		if err := f.Close(); err != nil {
			wailsruntime.LogError(a.ctx, "关闭Excel文件失败: "+err.Error())
		}
	}()

	// 设置工作表名称
	sheetName := "员工花名册"
	index, err := f.NewSheet(sheetName)
	if err != nil {
		return "", fmt.Errorf("创建工作表失败: %v", err)
	}

	// 设置表头
	headers := []string{"ID", "姓名", "团队", "岗位", "电话", "邮箱", "用工类型", "状态", "入职日期"}
	for i, header := range headers {
		cell := fmt.Sprintf("%c1", 'A'+i)
		f.SetCellValue(sheetName, cell, header)
	}

	// 设置表头样式
	headerStyle, err := f.NewStyle(&excelize.Style{
		Font: &excelize.Font{
			Bold: true,
			Size: 12,
		},
		Fill: excelize.Fill{
			Type:    "pattern",
			Color:   []string{"#E6F3FF"},
			Pattern: 1,
		},
		Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		},
		Border: []excelize.Border{
			{Type: "left", Color: "000000", Style: 1},
			{Type: "top", Color: "000000", Style: 1},
			{Type: "bottom", Color: "000000", Style: 1},
			{Type: "right", Color: "000000", Style: 1},
		},
	})
	if err != nil {
		return "", fmt.Errorf("创建表头样式失败: %v", err)
	}

	// 应用表头样式
	f.SetCellStyle(sheetName, "A1", fmt.Sprintf("%c1", 'A'+len(headers)-1), headerStyle)

	// 填充数据
	for i, emp := range employees {
		row := i + 2
		f.SetCellValue(sheetName, fmt.Sprintf("A%d", row), emp.ID)
		f.SetCellValue(sheetName, fmt.Sprintf("B%d", row), emp.Name)
		f.SetCellValue(sheetName, fmt.Sprintf("C%d", row), emp.Department)
		f.SetCellValue(sheetName, fmt.Sprintf("D%d", row), emp.Position)
		f.SetCellValue(sheetName, fmt.Sprintf("E%d", row), emp.Phone)
		f.SetCellValue(sheetName, fmt.Sprintf("F%d", row), emp.Email)
		f.SetCellValue(sheetName, fmt.Sprintf("G%d", row), emp.Type)
		f.SetCellValue(sheetName, fmt.Sprintf("H%d", row), emp.Status)
		f.SetCellValue(sheetName, fmt.Sprintf("I%d", row), emp.JoinDate)
	}

	// 设置数据样式
	dataStyle, err := f.NewStyle(&excelize.Style{
		Border: []excelize.Border{
			{Type: "left", Color: "000000", Style: 1},
			{Type: "top", Color: "000000", Style: 1},
			{Type: "bottom", Color: "000000", Style: 1},
			{Type: "right", Color: "000000", Style: 1},
		},
		Alignment: &excelize.Alignment{
			Vertical: "center",
		},
	})
	if err == nil && len(employees) > 0 {
		f.SetCellStyle(sheetName, "A2", fmt.Sprintf("I%d", len(employees)+1), dataStyle)
	}

	// 自动调整列宽
	for i := 0; i < len(headers); i++ {
		col := fmt.Sprintf("%c", 'A'+i)
		f.SetColWidth(sheetName, col, col, 15)
	}

	// 设置活动工作表
	f.SetActiveSheet(index)

	// 删除默认的Sheet1
	f.DeleteSheet("Sheet1")

	// 生成文件名
	timestamp := time.Now().Format("20060102_150405")
	filename := fmt.Sprintf("员工花名册_%s.xlsx", timestamp)

	// 选择保存位置
	savePath, err := wailsruntime.SaveFileDialog(a.ctx, wailsruntime.SaveDialogOptions{
		Title:           "保存Excel文件",
		DefaultFilename: filename,
		Filters: []wailsruntime.FileFilter{
			{
				DisplayName: "Excel文件 (*.xlsx)",
				Pattern:     "*.xlsx",
			},
		},
	})

	if err != nil {
		return "", fmt.Errorf("选择保存位置失败: %v", err)
	}

	if savePath == "" {
		return "", fmt.Errorf("用户取消了保存操作")
	}

	// 保存文件
	if err := f.SaveAs(savePath); err != nil {
		return "", fmt.Errorf("保存Excel文件失败: %v", err)
	}

	return fmt.Sprintf("Excel文件已成功导出到: %s，共导出 %d 条记录", savePath, len(employees)), nil
}

// BatchDeleteEmployees 批量删除员工
func (a *App) BatchDeleteEmployees(ids []int) (string, error) {
	if a.db == nil {
		return "", fmt.Errorf("数据库未初始化")
	}

	if len(ids) == 0 {
		return "", fmt.Errorf("没有选择要删除的员工")
	}

	// 开始事务
	tx, err := a.db.Begin()
	if err != nil {
		return "", fmt.Errorf("开始事务失败: %v", err)
	}
	defer tx.Rollback()

	// 批量删除
	deletedCount := 0
	for _, id := range ids {
		result, err := tx.Exec("DELETE FROM employees WHERE id = ?", id)
		if err != nil {
			return "", fmt.Errorf("删除员工ID %d 失败: %v", id, err)
		}
		
		rowsAffected, err := result.RowsAffected()
		if err == nil && rowsAffected > 0 {
			deletedCount++
		}
	}

	// 提交事务
	if err := tx.Commit(); err != nil {
		return "", fmt.Errorf("提交事务失败: %v", err)
	}

	return fmt.Sprintf("成功删除 %d 名员工", deletedCount), nil
}

// BatchUpdateEmployeeStatus 批量更新员工状态
func (a *App) BatchUpdateEmployeeStatus(ids []int, status string) (string, error) {
	if a.db == nil {
		return "", fmt.Errorf("数据库未初始化")
	}

	if len(ids) == 0 {
		return "", fmt.Errorf("没有选择要更新的员工")
	}

	// 开始事务
	tx, err := a.db.Begin()
	if err != nil {
		return "", fmt.Errorf("开始事务失败: %v", err)
	}
	defer tx.Rollback()

	// 批量更新状态
	updatedCount := 0
	for _, id := range ids {
		result, err := tx.Exec("UPDATE employees SET status = ? WHERE id = ?", status, id)
		if err != nil {
			return "", fmt.Errorf("更新员工ID %d 状态失败: %v", id, err)
		}
		
		rowsAffected, err := result.RowsAffected()
		if err == nil && rowsAffected > 0 {
			updatedCount++
		}
	}

	// 提交事务
	if err := tx.Commit(); err != nil {
		return "", fmt.Errorf("提交事务失败: %v", err)
	}

	return fmt.Sprintf("成功更新 %d 名员工状态为: %s", updatedCount, status), nil
}

// TestAIConnection 测试AI API连接
func (a *App) TestAIConnection(apiUrl, apiKey, model string) (map[string]interface{}, error) {
	// 参数验证
	if apiUrl == "" || apiKey == "" || model == "" {
		return map[string]interface{}{
			"success": false,
			"message": "API地址、密钥和模型名称不能为空",
		}, nil
	}
	
	// 确保API URL格式正确
	if !strings.HasSuffix(apiUrl, "/") {
		apiUrl += "/"
	}
	testUrl := apiUrl + "chat/completions"
	
	// 构建测试请求体
	requestBody := map[string]interface{}{
		"model": model,
		"messages": []map[string]string{
			{
				"role":    "user",
				"content": "Hello, this is a connection test.",
			},
		},
		"max_tokens": 5,
	}
	
	// 将请求体转换为JSON
	jsonData, err := json.Marshal(requestBody)
	if err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "请求数据构建失败: " + err.Error(),
		}, nil
	}
	
	// 创建HTTP请求
	req, err := http.NewRequest("POST", testUrl, bytes.NewBuffer(jsonData))
	if err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "创建请求失败: " + err.Error(),
		}, nil
	}
	
	// 设置请求头
	req.Header.Set("Content-Type", "application/json")
	req.Header.Set("Authorization", "Bearer "+apiKey)
	
	// 创建HTTP客户端（设置超时）
	client := &http.Client{
		Timeout: 30 * time.Second,
	}
	
	// 发送请求
	resp, err := client.Do(req)
	if err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "连接失败: " + err.Error(),
		}, nil
	}
	defer resp.Body.Close()
	
	// 读取响应体
	body, err := ioutil.ReadAll(resp.Body)
	if err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "读取响应失败: " + err.Error(),
		}, nil
	}
	
	// 检查HTTP状态码
	if resp.StatusCode != 200 {
		var errorResp map[string]interface{}
		if err := json.Unmarshal(body, &errorResp); err == nil {
			if errorInfo, ok := errorResp["error"].(map[string]interface{}); ok {
				if message, ok := errorInfo["message"].(string); ok {
					return map[string]interface{}{
						"success": false,
						"message": fmt.Sprintf("API错误 (%d): %s", resp.StatusCode, message),
					}, nil
				}
			}
		}
		return map[string]interface{}{
			"success": false,
			"message": fmt.Sprintf("API请求失败，状态码: %d", resp.StatusCode),
		}, nil
	}
	
	// 解析响应
	var response map[string]interface{}
	if err := json.Unmarshal(body, &response); err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "响应解析失败: " + err.Error(),
		}, nil
	}
	
	// 检查响应结构
	if choices, ok := response["choices"].([]interface{}); ok && len(choices) > 0 {
		return map[string]interface{}{
			"success": true,
			"message": "连接成功！API运行正常",
			"model":   model,
			"apiUrl":  apiUrl,
		}, nil
	}
	
	return map[string]interface{}{
		"success": false,
		"message": "API响应格式异常",
	}, nil
}

// CallAI 调用AI进行对话
func (a *App) CallAI(apiUrl, apiKey, model, prompt string) (map[string]interface{}, error) {
	// 参数验证
	if apiUrl == "" || apiKey == "" || model == "" || prompt == "" {
		return map[string]interface{}{
			"success": false,
			"message": "API地址、密钥、模型名称和提示内容不能为空",
		}, nil
	}
	
	// 确保API URL格式正确
	if !strings.HasSuffix(apiUrl, "/") {
		apiUrl += "/"
	}
	chatUrl := apiUrl + "chat/completions"
	
	// 构建请求体
	requestBody := map[string]interface{}{
		"model": model,
		"messages": []map[string]string{
			{
				"role":    "user",
				"content": prompt,
			},
		},
		"max_tokens":    500,
		"temperature":   0.7,
		"stream":        false,
	}
	
	// 将请求体转换为JSON
	jsonData, err := json.Marshal(requestBody)
	if err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "请求数据构建失败: " + err.Error(),
		}, nil
	}
	
	// 创建HTTP请求
	req, err := http.NewRequest("POST", chatUrl, bytes.NewBuffer(jsonData))
	if err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "创建请求失败: " + err.Error(),
		}, nil
	}
	
	// 设置请求头
	req.Header.Set("Content-Type", "application/json")
	req.Header.Set("Authorization", "Bearer "+apiKey)
	
	// 创建HTTP客户端（设置超时）
	client := &http.Client{
		Timeout: 60 * time.Second, // 增加超时时间用于AI对话
	}
	
	// 发送请求
	resp, err := client.Do(req)
	if err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "连接失败: " + err.Error(),
		}, nil
	}
	defer resp.Body.Close()
	
	// 读取响应体
	body, err := ioutil.ReadAll(resp.Body)
	if err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "读取响应失败: " + err.Error(),
		}, nil
	}
	
	// 检查HTTP状态码
	if resp.StatusCode != 200 {
		var errorResp map[string]interface{}
		if err := json.Unmarshal(body, &errorResp); err == nil {
			if errorInfo, ok := errorResp["error"].(map[string]interface{}); ok {
				if message, ok := errorInfo["message"].(string); ok {
					return map[string]interface{}{
						"success": false,
						"message": fmt.Sprintf("AI错误 (%d): %s", resp.StatusCode, message),
					}, nil
				}
			}
		}
		return map[string]interface{}{
			"success": false,
			"message": fmt.Sprintf("AI请求失败，状态码: %d", resp.StatusCode),
		}, nil
	}
	
	// 解析响应
	var response map[string]interface{}
	if err := json.Unmarshal(body, &response); err != nil {
		return map[string]interface{}{
			"success": false,
			"message": "响应解析失败: " + err.Error(),
		}, nil
	}
	
	// 提取AI响应内容
	if choices, ok := response["choices"].([]interface{}); ok && len(choices) > 0 {
		if choice, ok := choices[0].(map[string]interface{}); ok {
			if message, ok := choice["message"].(map[string]interface{}); ok {
				if content, ok := message["content"].(string); ok {
					return map[string]interface{}{
						"success": true,
						"message": "AI调用成功",
						"content": content,
						"model":   model,
					}, nil
				}
			}
		}
	}
	
	return map[string]interface{}{
		"success": false,
		"message": "AI响应格式异常",
	}, nil
}

// SelectExportDirectory 选择导出目录
func (a *App) SelectExportDirectory() (string, error) {
	// 使用Wails的原生目录选择对话框
	dirPath, err := wailsruntime.OpenDirectoryDialog(a.ctx, wailsruntime.OpenDialogOptions{
		Title:                "选择导出目录",
		DefaultDirectory:     "", // 空字符串使用系统默认位置
		CanCreateDirectories: true,
		ShowHiddenFiles:      false,
	})
	
	if err != nil {
		wailsruntime.LogError(a.ctx, "选择导出目录失败: "+err.Error())
		return "", fmt.Errorf("选择导出目录失败: %v", err)
	}
	
	if dirPath == "" {
		// 用户取消了选择
		return "", nil
	}
	
	// 验证目录是否可写
	testFile := filepath.Join(dirPath, ".wails_test_write")
	file, err := os.Create(testFile)
	if err != nil {
		return "", fmt.Errorf("选择的目录没有写入权限: %v", err)
	}
	file.Close()
	os.Remove(testFile) // 清理测试文件
	
	wailsruntime.LogInfo(a.ctx, "导出目录已设置为: "+dirPath)
	return dirPath, nil
}

// ==================== 用户管理相关API ====================

// initDefaultAdmin 初始化默认管理员账号
func (a *App) initDefaultAdmin() {
	if a.db == nil {
		return
	}
	
	// 检查是否已存在管理员账号
	var count int
	err := a.db.QueryRow("SELECT COUNT(*) FROM users WHERE role = ?", "系统管理员").Scan(&count)
	if err != nil {
		wailsruntime.LogError(a.ctx, "检查管理员账号失败: "+err.Error())
		return
	}
	
	if count > 0 {
		wailsruntime.LogInfo(a.ctx, "管理员账号已存在")
		return
	}
	
	// 创建默认管理员账号
	passwordHash, err := bcrypt.GenerateFromPassword([]byte("admin123"), bcrypt.DefaultCost)
	if err != nil {
		wailsruntime.LogError(a.ctx, "生成密码哈希失败: "+err.Error())
		return
	}
	
	admin := User{
		Username:     "admin",
		PasswordHash: string(passwordHash),
		Email:        "admin@company.com",
		FullName:     "系统管理员",
		Department:   "信息技术团队",
		Position:     "系统管理员",
		Phone:        "138-0000-0000",
		Role:         "系统管理员",
		Status:       "active",
		JoinDate:     "2024-01-01",
	}
	
	_, err = a.db.Exec(`
		INSERT INTO users (username, password_hash, email, full_name, department, position, phone, role, status, join_date) 
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		admin.Username, admin.PasswordHash, admin.Email, admin.FullName, 
		admin.Department, admin.Position, admin.Phone, admin.Role, admin.Status, admin.JoinDate)
	
	if err != nil {
		wailsruntime.LogError(a.ctx, "创建默认管理员失败: "+err.Error())
		return
	}
	
	wailsruntime.LogInfo(a.ctx, "默认管理员账号创建成功 (用户名: admin, 密码: admin123)")
}

// generateSessionToken 生成会话令牌
func (a *App) generateSessionToken() (string, error) {
	bytes := make([]byte, 32)
	if _, err := rand.Read(bytes); err != nil {
		return "", err
	}
	return hex.EncodeToString(bytes), nil
}

// AuthenticateUser 用户认证
func (a *App) AuthenticateUser(request LoginRequest) (LoginResponse, error) {
	var response LoginResponse
	
	if a.db == nil {
		response.Success = false
		response.Message = "数据库未初始化"
		return response, nil
	}
	
	// 验证参数
	if request.Username == "" || request.Password == "" {
		response.Success = false
		response.Message = "用户名和密码不能为空"
		return response, nil
	}
	
	// 查询用户
	var user User
	var avatarBase64 sql.NullString
	err := a.db.QueryRow(`
		SELECT id, username, password_hash, email, full_name, department, position, phone, 
			   avatar_base64, role, status, join_date, created_at, updated_at, 
			   IFNULL(last_login_time, ''), IFNULL(last_login_method, '') 
		FROM users WHERE username = ? AND status = 'active'`, request.Username).Scan(
		&user.ID, &user.Username, &user.PasswordHash, &user.Email, &user.FullName,
		&user.Department, &user.Position, &user.Phone, &avatarBase64,
		&user.Role, &user.Status, &user.JoinDate, &user.CreatedAt, &user.UpdatedAt,
		&user.LastLoginTime, &user.LastLoginMethod)
	
	// 处理avatar_base64的NULL值
	if avatarBase64.Valid {
		user.AvatarBase64 = avatarBase64.String
	} else {
		user.AvatarBase64 = ""
	}
	
	if err != nil {
		if err == sql.ErrNoRows {
			response.Success = false
			response.Message = "用户名或密码错误"
		} else {
			response.Success = false
			response.Message = "查询用户失败: " + err.Error()
		}
		return response, nil
	}
	
	// 验证密码
	err = bcrypt.CompareHashAndPassword([]byte(user.PasswordHash), []byte(request.Password))
	if err != nil {
		response.Success = false
		response.Message = "用户名或密码错误"
		return response, nil
	}
	
	// 生成会话令牌
	sessionToken, err := a.generateSessionToken()
	if err != nil {
		response.Success = false
		response.Message = "生成会话令牌失败"
		return response, nil
	}
	
	// 更新最后登录时间和登录方式
	loginMethod := request.LoginMethod
	if loginMethod == "" {
		loginMethod = "密码登录" // 默认值
	}
	_, err = a.db.Exec(`
		UPDATE users SET last_login_time = datetime('now', 'localtime'), last_login_method = ? 
		WHERE id = ?`, loginMethod, user.ID)
	if err != nil {
		wailsruntime.LogError(a.ctx, "更新登录时间失败: "+err.Error())
	}
	
	// 记录登录活动
	localIP := a.getLocalIP()
	a.AddUserActivity(user.ID, "用户登录系统", localIP)
	
	// 构建响应
	user.PasswordHash = "" // 清除密码哈希
	response.Success = true
	response.Message = "登录成功"
	response.User = &user
	response.SessionToken = sessionToken
	
	return response, nil
}

// GetUserByID 根据ID获取用户信息
func (a *App) GetUserByID(id int) (User, error) {
	var user User
	
	if a.db == nil {
		return user, fmt.Errorf("数据库未初始化")
	}
	
	var avatarBase64 sql.NullString
	err := a.db.QueryRow(`
		SELECT id, username, email, full_name, department, position, phone, 
			   avatar_base64, role, status, join_date, created_at, updated_at, 
			   IFNULL(last_login_time, ''), IFNULL(last_login_method, '') 
		FROM users WHERE id = ?`, id).Scan(
		&user.ID, &user.Username, &user.Email, &user.FullName,
		&user.Department, &user.Position, &user.Phone, &avatarBase64,
		&user.Role, &user.Status, &user.JoinDate, &user.CreatedAt, &user.UpdatedAt,
		&user.LastLoginTime, &user.LastLoginMethod)
	
	// 处理avatar_base64的NULL值
	if avatarBase64.Valid {
		user.AvatarBase64 = avatarBase64.String
	} else {
		user.AvatarBase64 = ""
	}
	
	return user, err
}

// GetUserByUsername 根据用户名获取用户信息
func (a *App) GetUserByUsername(username string) (User, error) {
	var user User
	
	if a.db == nil {
		return user, fmt.Errorf("数据库未初始化")
	}
	
	var avatarBase64 sql.NullString
	err := a.db.QueryRow(`
		SELECT id, username, email, full_name, department, position, phone, 
			   avatar_base64, role, status, join_date, created_at, updated_at, 
			   IFNULL(last_login_time, ''), IFNULL(last_login_method, '') 
		FROM users WHERE username = ?`, username).Scan(
		&user.ID, &user.Username, &user.Email, &user.FullName,
		&user.Department, &user.Position, &user.Phone, &avatarBase64,
		&user.Role, &user.Status, &user.JoinDate, &user.CreatedAt, &user.UpdatedAt,
		&user.LastLoginTime, &user.LastLoginMethod)
	
	// 处理avatar_base64的NULL值
	if avatarBase64.Valid {
		user.AvatarBase64 = avatarBase64.String
	} else {
		user.AvatarBase64 = ""
	}
	
	return user, err
}

// UpdateUser 更新用户信息
func (a *App) UpdateUser(user User) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		UPDATE users SET 
			email = ?, full_name = ?, department = ?, position = ?, phone = ?, 
			join_date = ?, avatar_base64 = ?, updated_at = datetime('now', 'localtime')
		WHERE id = ?`,
		user.Email, user.FullName, user.Department, user.Position, user.Phone,
		user.JoinDate, user.AvatarBase64, user.ID)
	
	if err != nil {
		return err
	}
	
	// 记录用户活动
	localIP := a.getLocalIP()
	a.AddUserActivity(user.ID, "更新个人信息", localIP)
	
	return nil
}

// UpdateUserAvatar 更新用户头像（专用函数，避免重复活动记录）
func (a *App) UpdateUserAvatar(userID int, avatarBase64 string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		UPDATE users SET avatar_base64 = ?, updated_at = datetime('now', 'localtime')
		WHERE id = ?`, avatarBase64, userID)
	
	if err != nil {
		return err
	}
	
	// 只记录头像更换活动，不记录通用的"更新个人信息"
	localIP := a.getLocalIP()
	a.AddUserActivity(userID, "更换头像", localIP)
	
	return nil
}

// UpdateUserPassword 修改用户密码
func (a *App) UpdateUserPassword(userID int, oldPassword, newPassword string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	// 获取当前密码哈希
	var currentPasswordHash string
	err := a.db.QueryRow("SELECT password_hash FROM users WHERE id = ?", userID).Scan(&currentPasswordHash)
	if err != nil {
		return fmt.Errorf("用户不存在")
	}
	
	// 验证旧密码
	err = bcrypt.CompareHashAndPassword([]byte(currentPasswordHash), []byte(oldPassword))
	if err != nil {
		return fmt.Errorf("原密码错误")
	}
	
	// 生成新密码哈希
	newPasswordHash, err := bcrypt.GenerateFromPassword([]byte(newPassword), bcrypt.DefaultCost)
	if err != nil {
		return fmt.Errorf("生成密码哈希失败")
	}
	
	// 更新密码
	_, err = a.db.Exec(`
		UPDATE users SET password_hash = ?, updated_at = datetime('now', 'localtime') 
		WHERE id = ?`, string(newPasswordHash), userID)
	
	if err != nil {
		return err
	}
	
	// 记录用户活动
	localIP := a.getLocalIP()
	a.AddUserActivity(userID, "修改登录密码", localIP)
	
	return nil
}

// AddUserActivity 添加用户活动记录
func (a *App) AddUserActivity(userID int, action, ipAddress string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	// 如果IP地址为空，自动获取本地IP
	if ipAddress == "" {
		ipAddress = a.getLocalIP()
	}
	
	// 生成默认的用户代理信息
	userAgent := "ContactsManager Desktop App"
	
	_, err := a.db.Exec(`
		INSERT INTO user_activities (user_id, activity_type, ip_address, user_agent, created_at) 
		VALUES (?, ?, ?, ?, datetime('now', 'localtime'))`, userID, action, ipAddress, userAgent)
	
	return err
}

// GetUserActivities 获取用户活动记录
func (a *App) GetUserActivities(userID int, limit int) ([]UserActivity, error) {
	if a.db == nil {
		return nil, fmt.Errorf("数据库未初始化")
	}
	
	if limit <= 0 {
		limit = 50 // 默认限制50条
	}
	
	rows, err := a.db.Query(`
		SELECT id, user_id, activity_type, ip_address, COALESCE(user_agent, ''), IFNULL(created_at, '') 
		FROM user_activities 
		WHERE user_id = ? 
		ORDER BY created_at DESC 
		LIMIT ?`, userID, limit)
	
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	var activities []UserActivity
	for rows.Next() {
		var activity UserActivity
		
		err := rows.Scan(&activity.ID, &activity.UserID, &activity.Action,
			&activity.IPAddress, &activity.UserAgent, &activity.CreatedAt)
		if err != nil {
			return nil, err
		}
		
		activities = append(activities, activity)
	}
	
	return activities, nil
}

// GetUserSetting 获取用户设置
func (a *App) GetUserSetting(userID int, settingKey string) (string, error) {
	if a.db == nil {
		return "", fmt.Errorf("数据库未初始化")
	}
	
	var settingValue string
	err := a.db.QueryRow(`
		SELECT setting_value FROM user_settings 
		WHERE user_id = ? AND setting_key = ?`, userID, settingKey).Scan(&settingValue)
	
	if err != nil {
		if err == sql.ErrNoRows {
			return "", nil // 设置不存在，返回空字符串
		}
		return "", err
	}
	
	return settingValue, nil
}

// SaveAIChatSession 保存AI聊天会话
func (a *App) SaveAIChatSession(userID int, sessionID, title string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	// 先检查是否已存在
	var existingID int
	err := a.db.QueryRow("SELECT id FROM ai_chat_sessions WHERE session_id = ?", sessionID).Scan(&existingID)
	
	if err == sql.ErrNoRows {
		// 不存在，插入新记录
		_, err = a.db.Exec(`
			INSERT INTO ai_chat_sessions (user_id, session_id, title, created_at, updated_at) 
			VALUES (?, ?, ?, datetime('now', 'localtime'), datetime('now', 'localtime'))`,
			userID, sessionID, title)
	} else if err == nil {
		// 存在，更新标题和更新时间
		_, err = a.db.Exec(`
			UPDATE ai_chat_sessions SET title = ?, updated_at = datetime('now', 'localtime') 
			WHERE session_id = ?`, title, sessionID)
	}
	
	return err
}

// SaveAIChatMessage 保存AI聊天消息
func (a *App) SaveAIChatMessage(sessionID, messageID, content, role string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	// 获取会话的数据库ID
	var dbSessionID int
	err := a.db.QueryRow("SELECT id FROM ai_chat_sessions WHERE session_id = ?", sessionID).Scan(&dbSessionID)
	if err != nil {
		return fmt.Errorf("会话不存在: %v", err)
	}
	
	// 检查消息是否已存在
	var existingID int
	err = a.db.QueryRow("SELECT id FROM ai_chat_messages WHERE message_id = ?", messageID).Scan(&existingID)
	
	if err == sql.ErrNoRows {
		// 不存在，插入新消息
		_, err = a.db.Exec(`
			INSERT INTO ai_chat_messages (session_id, message_id, content, role, created_at) 
			VALUES (?, ?, ?, ?, datetime('now', 'localtime'))`,
			dbSessionID, messageID, content, role)
	} else if err == nil {
		// 存在，更新内容（用于流式传输的消息更新）
		_, err = a.db.Exec(`
			UPDATE ai_chat_messages SET content = ? WHERE message_id = ?`,
			content, messageID)
	}
	
	return err
}

// GetAIChatSessions 获取用户的AI聊天会话列表
func (a *App) GetAIChatSessions(userID int) ([]AIChatSession, error) {
	if a.db == nil {
		return nil, fmt.Errorf("数据库未初始化")
	}
	
	rows, err := a.db.Query(`
		SELECT id, user_id, session_id, title, created_at, updated_at
		FROM ai_chat_sessions 
		WHERE user_id = ? 
		ORDER BY updated_at DESC`,
		userID)
	
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	var sessions []AIChatSession
	for rows.Next() {
		var session AIChatSession
		err := rows.Scan(&session.ID, &session.UserID, &session.SessionID,
			&session.Title, &session.CreatedAt, &session.UpdatedAt)
		if err != nil {
			return nil, err
		}
		sessions = append(sessions, session)
	}
	
	return sessions, nil
}

// GetAIChatMessages 获取会话的所有消息
func (a *App) GetAIChatMessages(sessionID string) ([]AIChatMessage, error) {
	if a.db == nil {
		return nil, fmt.Errorf("数据库未初始化")
	}
	
	// 先获取会话的数据库ID
	var dbSessionID int
	err := a.db.QueryRow("SELECT id FROM ai_chat_sessions WHERE session_id = ?", sessionID).Scan(&dbSessionID)
	if err != nil {
		return nil, fmt.Errorf("会话不存在: %v", err)
	}
	
	rows, err := a.db.Query(`
		SELECT id, session_id, message_id, content, role, created_at
		FROM ai_chat_messages 
		WHERE session_id = ? 
		ORDER BY created_at ASC`,
		dbSessionID)
	
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	var messages []AIChatMessage
	for rows.Next() {
		var message AIChatMessage
		err := rows.Scan(&message.ID, &message.SessionID, &message.MessageID,
			&message.Content, &message.Role, &message.CreatedAt)
		if err != nil {
			return nil, err
		}
		messages = append(messages, message)
	}
	
	return messages, nil
}

// DeleteAIChatSession 删除AI聊天会话（级联删除消息）
func (a *App) DeleteAIChatSession(sessionID string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	// 开启事务确保数据一致性
	tx, err := a.db.Begin()
	if err != nil {
		wailsruntime.LogError(a.ctx, "开启事务失败: "+err.Error())
		return err
	}
	defer tx.Rollback()
	
	// 先获取会话的数据库ID
	var sessionDbID int
	err = tx.QueryRow("SELECT id FROM ai_chat_sessions WHERE session_id = ?", sessionID).Scan(&sessionDbID)
	if err != nil {
		if err == sql.ErrNoRows {
			wailsruntime.LogWarning(a.ctx, "聊天会话不存在: "+sessionID)
			return nil
		}
		wailsruntime.LogError(a.ctx, "查找会话失败: "+err.Error())
		return err
	}
	
	// 删除相关的消息
	result, err := tx.Exec("DELETE FROM ai_chat_messages WHERE session_id = ?", sessionDbID)
	if err != nil {
		wailsruntime.LogError(a.ctx, "删除聊天消息失败: "+err.Error())
		return err
	}
	
	deletedMessages, _ := result.RowsAffected()
	wailsruntime.LogInfo(a.ctx, fmt.Sprintf("删除了 %d 条聊天消息", deletedMessages))
	
	// 删除会话
	_, err = tx.Exec("DELETE FROM ai_chat_sessions WHERE session_id = ?", sessionID)
	if err != nil {
		wailsruntime.LogError(a.ctx, "删除聊天会话失败: "+err.Error())
		return err
	}
	
	// 提交事务
	err = tx.Commit()
	if err != nil {
		wailsruntime.LogError(a.ctx, "提交事务失败: "+err.Error())
		return err
	}
	
	wailsruntime.LogInfo(a.ctx, fmt.Sprintf("成功删除聊天会话及其消息，会话ID: %s", sessionID))
	return nil
}

// UpdateAIUsageStats 更新AI使用统计
func (a *App) UpdateAIUsageStats(userID int, statType string, success bool, responseTime float64) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	// 获取当前日期
	now := time.Now()
	today := now.Format("2006-01-02")
	
	// 检查用户统计记录是否存在（按类型查询）
	var existingID int
	err := a.db.QueryRow("SELECT id FROM ai_usage_stats WHERE user_id = ? AND stat_type = ?", userID, statType).Scan(&existingID)
	
	if err == sql.ErrNoRows {
		// 不存在，创建新记录
		successfulCalls := 0
		failedCalls := 0
		if success {
			successfulCalls = 1
		} else {
			failedCalls = 1
		}
		
		successRate := 0.0
		if success {
			successRate = 100.0
		}
		
		fastestResponse := 0.0
		slowestResponse := 0.0
		totalResponseTime := 0.0
		
		if success {
			fastestResponse = responseTime
			slowestResponse = responseTime
			totalResponseTime = responseTime
		}
		
		_, err = a.db.Exec(`
			INSERT INTO ai_usage_stats (
				user_id, stat_type, total_calls, today_calls, successful_calls, failed_calls,
				success_rate, fastest_response, slowest_response, total_response_time,
				last_call_time, last_call_date, created_at, updated_at
			) VALUES (?, ?, 1, 1, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'), ?, 
					 datetime('now', 'localtime'), datetime('now', 'localtime'))`,
			userID, statType, successfulCalls, failedCalls, successRate, fastestResponse,
			slowestResponse, totalResponseTime, today)
		
		return err
	} else if err != nil {
		return err
	}
	
	// 存在，更新统计
	// 获取当前统计数据
	var stats AIUsageStats
	err = a.db.QueryRow(`
		SELECT total_calls, today_calls, successful_calls, failed_calls,
		       fastest_response, slowest_response, total_response_time, last_call_date
		FROM ai_usage_stats WHERE user_id = ? AND stat_type = ?`, userID, statType).Scan(
		&stats.TotalCalls, &stats.TodayCalls, &stats.SuccessfulCalls, &stats.FailedCalls,
		&stats.FastestResponse, &stats.SlowestResponse, &stats.TotalResponseTime, &stats.LastCallDate)
	
	if err != nil {
		return err
	}
	
	// 更新统计数据
	stats.TotalCalls++
	
	// 检查是否是新的一天
	if stats.LastCallDate != today {
		stats.TodayCalls = 1
	} else {
		stats.TodayCalls++
	}
	
	if success {
		stats.SuccessfulCalls++
		stats.TotalResponseTime += responseTime
		
		// 更新最快和最慢响应时间
		if stats.FastestResponse == 0 || responseTime < stats.FastestResponse {
			stats.FastestResponse = responseTime
		}
		if responseTime > stats.SlowestResponse {
			stats.SlowestResponse = responseTime
		}
	} else {
		stats.FailedCalls++
	}
	
	// 重新计算成功率
	stats.SuccessRate = float64(stats.SuccessfulCalls) / float64(stats.TotalCalls) * 100
	
	// 更新数据库
	_, err = a.db.Exec(`
		UPDATE ai_usage_stats SET 
			total_calls = ?, today_calls = ?, successful_calls = ?, failed_calls = ?,
			success_rate = ?, fastest_response = ?, slowest_response = ?, total_response_time = ?,
			last_call_time = datetime('now', 'localtime'), last_call_date = ?, 
			updated_at = datetime('now', 'localtime')
		WHERE user_id = ? AND stat_type = ?`,
		stats.TotalCalls, stats.TodayCalls, stats.SuccessfulCalls, stats.FailedCalls,
		stats.SuccessRate, stats.FastestResponse, stats.SlowestResponse, stats.TotalResponseTime,
		today, userID, statType)
	
	return err
}

// GetAIUsageStats 获取AI使用统计
func (a *App) GetAIUsageStats(userID int, statType string) (AIUsageStats, error) {
	var stats AIUsageStats
	
	if a.db == nil {
		return stats, fmt.Errorf("数据库未初始化")
	}
	
	err := a.db.QueryRow(`
		SELECT id, user_id, stat_type, total_calls, today_calls, successful_calls, failed_calls,
		       success_rate, fastest_response, slowest_response, total_response_time,
		       IFNULL(last_call_time, '') as last_call_time, 
		       IFNULL(last_call_date, '') as last_call_date,
		       created_at, updated_at
		FROM ai_usage_stats WHERE user_id = ? AND stat_type = ?`, userID, statType).Scan(
		&stats.ID, &stats.UserID, &stats.StatType, &stats.TotalCalls, &stats.TodayCalls,
		&stats.SuccessfulCalls, &stats.FailedCalls, &stats.SuccessRate,
		&stats.FastestResponse, &stats.SlowestResponse, &stats.TotalResponseTime,
		&stats.LastCallTime, &stats.LastCallDate, &stats.CreatedAt, &stats.UpdatedAt)
	
	if err == sql.ErrNoRows {
		// 如果没有记录，返回空的统计数据
		stats.UserID = userID
		stats.StatType = statType
		return stats, nil
	}
	
	return stats, err
}

// SetUserSetting 设置用户配置
func (a *App) SetUserSetting(userID int, settingKey, settingValue string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		INSERT OR REPLACE INTO user_settings (user_id, setting_key, setting_value, updated_at) 
		VALUES (?, ?, ?, CURRENT_TIMESTAMP)`, userID, settingKey, settingValue)
	
	return err
}

// GetAllUserSettings 获取用户的所有设置
func (a *App) GetAllUserSettings(userID int) (map[string]string, error) {
	if a.db == nil {
		return nil, fmt.Errorf("数据库未初始化")
	}
	
	rows, err := a.db.Query(`
		SELECT setting_key, setting_value FROM user_settings 
		WHERE user_id = ?`, userID)
	
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	settings := make(map[string]string)
	for rows.Next() {
		var key, value string
		if err := rows.Scan(&key, &value); err != nil {
			return nil, err
		}
		settings[key] = value
	}
	
	return settings, nil
}

// GetSystemSetting 获取系统设置
func (a *App) GetSystemSetting(settingKey string) (string, error) {
	if a.db == nil {
		return "", fmt.Errorf("数据库未初始化")
	}
	
	var settingValue string
	err := a.db.QueryRow(`
		SELECT setting_value FROM system_settings 
		WHERE setting_key = ?`, settingKey).Scan(&settingValue)
	
	if err != nil {
		if err == sql.ErrNoRows {
			return "", nil // 设置不存在，返回空字符串
		}
		return "", err
	}
	
	return settingValue, nil
}

// SetSystemSetting 设置系统配置
func (a *App) SetSystemSetting(settingKey, settingValue, description string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		INSERT OR REPLACE INTO system_settings (setting_key, setting_value, description, updated_at) 
		VALUES (?, ?, ?, CURRENT_TIMESTAMP)`, settingKey, settingValue, description)
	
	return err
}

// GetAllSystemSettings 获取所有系统设置
func (a *App) GetAllSystemSettings() (map[string]string, error) {
	if a.db == nil {
		return nil, fmt.Errorf("数据库未初始化")
	}
	
	rows, err := a.db.Query(`
		SELECT setting_key, setting_value FROM system_settings`)
	
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	settings := make(map[string]string)
	for rows.Next() {
		var key, value string
		if err := rows.Scan(&key, &value); err != nil {
			return nil, err
		}
		settings[key] = value
	}
	
	return settings, nil
}

// ================= AI配置相关方法 =================

// GetUserAIConfig 获取用户AI配置
func (a *App) GetUserAIConfig(userID int) (AIConfig, error) {
	var config AIConfig
	
	if a.db == nil {
		return config, fmt.Errorf("数据库未初始化")
	}
	
	err := a.db.QueryRow(`
		SELECT id, user_id, api_url, IFNULL(api_key, '') as api_key, 
		       IFNULL(model_name, '') as model_name, IFNULL(selected_model_id, '') as selected_model_id,
		       created_at, updated_at
		FROM ai_config WHERE user_id = ?`, userID).Scan(
		&config.ID, &config.UserID, &config.APIUrl, &config.APIKey,
		&config.ModelName, &config.SelectedModelID, &config.CreatedAt, &config.UpdatedAt)
	
	if err == sql.ErrNoRows {
		// 如果没有记录，返回默认配置
		config.UserID = userID
		config.APIUrl = "https://api.deepseek.com/v1"
		config.ModelName = "deepseek-chat"
		return config, nil
	}
	
	return config, err
}

// SaveUserAIConfig 保存用户AI配置
func (a *App) SaveUserAIConfig(userID int, apiUrl, apiKey, modelName string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		INSERT OR REPLACE INTO ai_config (user_id, api_url, api_key, model_name, updated_at) 
		VALUES (?, ?, ?, ?, datetime('now', 'localtime'))`, 
		userID, apiUrl, apiKey, modelName)
	
	return err
}

// SaveUserAIConfigWithSelectedModel 保存用户AI配置（包含选中的模型ID）
func (a *App) SaveUserAIConfigWithSelectedModel(userID int, apiUrl, apiKey, modelName, selectedModelID string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		INSERT OR REPLACE INTO ai_config (user_id, api_url, api_key, model_name, selected_model_id, updated_at) 
		VALUES (?, ?, ?, ?, ?, datetime('now', 'localtime'))`, 
		userID, apiUrl, apiKey, modelName, selectedModelID)
	
	return err
}

// SetSelectedAIModel 设置选中的AI模型
func (a *App) SetSelectedAIModel(userID int, selectedModelID string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		UPDATE ai_config SET selected_model_id = ?, updated_at = datetime('now', 'localtime')
		WHERE user_id = ?`, 
		selectedModelID, userID)
	
	return err
}

// GetUserAIModels 获取用户AI模型列表
func (a *App) GetUserAIModels(userID int) ([]AIModel, error) {
	if a.db == nil {
		return nil, fmt.Errorf("数据库未初始化")
	}
	
	rows, err := a.db.Query(`
		SELECT id, user_id, config_id, name, model_id, 
		       IFNULL(description, '') as description, IFNULL(icon, '') as icon,
		       created_at, updated_at
		FROM ai_models WHERE user_id = ? ORDER BY created_at ASC`, userID)
	
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	var models []AIModel
	for rows.Next() {
		var model AIModel
		if err := rows.Scan(&model.ID, &model.UserID, &model.ConfigID, &model.Name,
			&model.ModelID, &model.Description, &model.Icon, &model.CreatedAt, &model.UpdatedAt); err != nil {
			return nil, err
		}
		models = append(models, model)
	}
	
	return models, nil
}

// AddUserAIModel 添加用户AI模型
func (a *App) AddUserAIModel(userID int, configID, name, modelID, description, icon string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		INSERT INTO ai_models (user_id, config_id, name, model_id, description, icon, 
		                      created_at, updated_at) 
		VALUES (?, ?, ?, ?, ?, ?, datetime('now', 'localtime'), datetime('now', 'localtime'))`, 
		userID, configID, name, modelID, description, icon)
	
	return err
}

// UpdateUserAIModel 更新用户AI模型
func (a *App) UpdateUserAIModel(userID int, configID, name, modelID, description, icon string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		UPDATE ai_models SET name = ?, model_id = ?, description = ?, icon = ?,
		                    updated_at = datetime('now', 'localtime')
		WHERE user_id = ? AND config_id = ?`, 
		name, modelID, description, icon, userID, configID)
	
	return err
}

// DeleteUserAIModel 删除用户AI模型
func (a *App) DeleteUserAIModel(userID int, configID string) error {
	if a.db == nil {
		return fmt.Errorf("数据库未初始化")
	}
	
	_, err := a.db.Exec(`
		DELETE FROM ai_models WHERE user_id = ? AND config_id = ?`, 
		userID, configID)
	
	return err
}

// EmployeeBasicInfo 员工基础信息结构体 - 用于AI查询的详细员工信息
type EmployeeBasicInfo struct {
	ID          int    `json:"id"`
	Name        string `json:"name"`
	Department  string `json:"department"`
	Position    string `json:"position"`
	Phone       string `json:"phone"`
	Email       string `json:"email"`
	Type        string `json:"type"`
	Status      string `json:"status"`
	JoinDate    string `json:"join_date"`
}

// EmployeeStatistics 员工统计数据结构体
type EmployeeStatistics struct {
	TotalEmployees       int                     `json:"total_employees"`       // 员工总数
	ActiveEmployees      int                     `json:"active_employees"`      // 在职员工数
	InactiveEmployees    int                     `json:"inactive_employees"`    // 离职员工数
	DepartmentStats      map[string]int          `json:"department_stats"`      // 部门分布统计
	PositionStats        map[string]int          `json:"position_stats"`        // 职位分布统计
	TypeStats            map[string]int          `json:"type_stats"`            // 员工类型分布统计
	StatusStats          map[string]int          `json:"status_stats"`          // 员工状态分布统计
	MonthlyJoinTrend     map[string]int          `json:"monthly_join_trend"`    // 月度入职趋势
	HasEmailCount        int                     `json:"has_email_count"`       // 有邮箱的员工数
	HasPhoneCount        int                     `json:"has_phone_count"`       // 有电话的员工数
	UpdatedAt            string                  `json:"updated_at"`            // 统计数据更新时间
	EmployeeList         []EmployeeBasicInfo     `json:"employee_list"`         // 完整员工列表 - 供AI查询使用
}

// GetEmployeeStatistics 获取员工统计数据 - 为AI问数工具提供数据
func (a *App) GetEmployeeStatistics() (EmployeeStatistics, error) {
	var stats EmployeeStatistics
	
	if a.db == nil {
		return stats, fmt.Errorf("数据库未初始化")
	}
	
	// 初始化统计数据
	stats.DepartmentStats = make(map[string]int)
	stats.PositionStats = make(map[string]int)
	stats.TypeStats = make(map[string]int)
	stats.StatusStats = make(map[string]int)
	stats.MonthlyJoinTrend = make(map[string]int)
	stats.UpdatedAt = time.Now().Format("2006-01-02 15:04:05")
	
	// 获取所有员工数据 - 包含ID用于完整信息
	rows, err := a.db.Query(`
		SELECT id, name, department, position, type, status, join_date, email, phone
		FROM employees
		ORDER BY id ASC
	`)
	if err != nil {
		return stats, fmt.Errorf("查询员工数据失败: %v", err)
	}
	defer rows.Close()
	
	var totalCount int
	var activeCount int
	var inactiveCount int
	var hasEmailCount int
	var hasPhoneCount int
	var employeeList []EmployeeBasicInfo
	
	for rows.Next() {
		var id int
		var name, department, position, empType, status, joinDate, email, phone string
		err := rows.Scan(&id, &name, &department, &position, &empType, &status, &joinDate, &email, &phone)
		if err != nil {
			wailsruntime.LogError(a.ctx, "扫描员工数据失败: "+err.Error())
			continue
		}
		
		// 添加到员工列表
		employeeList = append(employeeList, EmployeeBasicInfo{
			ID:          id,
			Name:        name,
			Department:  department,
			Position:    position,
			Phone:       phone,
			Email:       email,
			Type:        empType,
			Status:      status,
			JoinDate:    joinDate,
		})
		
		totalCount++
		
		// 统计在职/离职状态
		if status == "在职" {
			activeCount++
		} else {
			inactiveCount++
		}
		
		// 统计有邮箱和电话的员工
		if email != "" {
			hasEmailCount++
		}
		if phone != "" {
			hasPhoneCount++
		}
		
		// 部门统计
		if department != "" {
			stats.DepartmentStats[department]++
		} else {
			stats.DepartmentStats["未设置部门"]++
		}
		
		// 职位统计
		if position != "" {
			stats.PositionStats[position]++
		} else {
			stats.PositionStats["未设置职位"]++
		}
		
		// 员工类型统计
		if empType != "" {
			stats.TypeStats[empType]++
		} else {
			stats.TypeStats["未设置类型"]++
		}
		
		// 状态统计
		if status != "" {
			stats.StatusStats[status]++
		} else {
			stats.StatusStats["未设置状态"]++
		}
		
		// 月度入职趋势统计
		if joinDate != "" {
			// 解析日期，提取年月
			if t, err := time.Parse("2006-01-02", joinDate); err == nil {
				monthKey := t.Format("2006-01")
				stats.MonthlyJoinTrend[monthKey]++
			} else if t, err := time.Parse("2006/01/02", joinDate); err == nil {
				monthKey := t.Format("2006-01")
				stats.MonthlyJoinTrend[monthKey]++
			} else {
				// 如果日期格式无法解析，统计为"日期格式错误"
				stats.MonthlyJoinTrend["日期格式错误"]++
			}
		} else {
			stats.MonthlyJoinTrend["未设置入职日期"]++
		}
	}
	
	// 设置统计结果
	stats.TotalEmployees = totalCount
	stats.ActiveEmployees = activeCount
	stats.InactiveEmployees = inactiveCount
	stats.HasEmailCount = hasEmailCount
	stats.HasPhoneCount = hasPhoneCount
	stats.EmployeeList = employeeList // 添加完整员工列表
	
	wailsruntime.LogInfo(a.ctx, fmt.Sprintf("员工统计数据生成完成: 总数=%d, 在职=%d, 离职=%d, 员工列表=%d", 
		totalCount, activeCount, inactiveCount, len(employeeList)))
	
	return stats, nil
}
