FTP-Server/database/db.go

package database

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"time"

	_ "modernc.org/sqlite"
)

// DB 数据库实例
type DB struct {
	*sql.DB
}

// FTPUser FTP用户
type FTPUser struct {
	ID           int64  `json:"id"`
	Username     string `json:"username"`
	Password     string `json:"password,omitempty"`
	HomeDir      string `json:"home_dir"`
	Permissions  string `json:"permissions"`   // "read", "write", "read,write", "admin"
	QuotaSize    int64  `json:"quota_size"`    // 字节, 0 = 无限制
	QuotaFiles   int    `json:"quota_files"`   // 文件数, 0 = 无限制
	UploadRate   int    `json:"upload_rate"`   // KB/s, 0 = 无限制
	DownloadRate int    `json:"download_rate"` // KB/s, 0 = 无限制
	Enabled      bool   `json:"enabled"`
	CreatedAt    string `json:"created_at"`
	UpdatedAt    string `json:"updated_at"`
}

// FTPLog FTP操作日志
type FTPLog struct {
	ID        int64  `json:"id"`
	Username  string `json:"username"`
	IP        string `json:"ip"`
	Action    string `json:"action"`
	FilePath  string `json:"file_path"`
	FileSize  int64  `json:"file_size"`
	Status    string `json:"status"`
	CreatedAt string `json:"created_at"`
}

// OnlineUser 在线用户
type OnlineUser struct {
	ID           int       `json:"id"`
	Username     string    `json:"username"`
	IP           string    `json:"ip"`
	LoginTime    time.Time `json:"login_time"`
	LastActivity time.Time `json:"last_activity"`
	CurrentDir   string    `json:"current_dir"`
}

// IPAccessRule IP访问规则
type IPAccessRule struct {
	ID        int64  `json:"id"`
	Username  string `json:"username"` // 为空表示全局规则，有值表示用户专属规则
	IP        string `json:"ip"`       // 支持单IP、CIDR(192.168.1.0/24)、IP范围(192.168.1.1-192.168.1.100)
	Type      string `json:"type"`     // "whitelist" 或 "blacklist"
	Note      string `json:"note"`     // 备注说明
	Enabled   bool   `json:"enabled"`
	CreatedAt string `json:"created_at"`
}

// Open 打开数据库
func Open(dbPath string) (*DB, error) {
	dir := filepath.Dir(dbPath)
	if err := os.MkdirAll(dir, 0755); err != nil {
		return nil, fmt.Errorf("创建数据库目录失败: %w", err)
	}

	db, err := sql.Open("sqlite", dbPath+"?_journal_mode=WAL&_busy_timeout=5000")
	if err != nil {
		return nil, fmt.Errorf("打开数据库失败: %w", err)
	}

	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("连接数据库失败: %w", err)
	}

	d := &DB{db}
	if err := d.initTables(); err != nil {
		return nil, err
	}

	return d, nil
}

// initTables 初始化数据表
func (db *DB) initTables() error {
	schema := `
	CREATE TABLE IF NOT EXISTS ftp_users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT NOT NULL UNIQUE,
		password TEXT NOT NULL,
		home_dir TEXT NOT NULL,
		permissions TEXT DEFAULT 'read',
		quota_size INTEGER DEFAULT 0,
		quota_files INTEGER DEFAULT 0,
		upload_rate INTEGER DEFAULT 0,
		download_rate INTEGER DEFAULT 0,
		enabled INTEGER DEFAULT 1,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE TABLE IF NOT EXISTS ftp_logs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT DEFAULT '',
		ip TEXT DEFAULT '',
		action TEXT NOT NULL,
		file_path TEXT DEFAULT '',
		file_size INTEGER DEFAULT 0,
		status TEXT DEFAULT 'success',
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_logs_username ON ftp_logs(username);
	CREATE INDEX IF NOT EXISTS idx_logs_created ON ftp_logs(created_at);
	CREATE INDEX IF NOT EXISTS idx_logs_action ON ftp_logs(action);

	CREATE TABLE IF NOT EXISTS system_stats (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		total_uploads INTEGER DEFAULT 0,
		total_downloads INTEGER DEFAULT 0,
		total_upload_bytes INTEGER DEFAULT 0,
		total_download_bytes INTEGER DEFAULT 0,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);
	CREATE TABLE IF NOT EXISTS ip_access_rules (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT DEFAULT '',
		ip TEXT NOT NULL,
		type TEXT NOT NULL DEFAULT 'blacklist',
		note TEXT DEFAULT '',
		enabled INTEGER DEFAULT 1,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_ip_rules_type ON ip_access_rules(type);
	CREATE INDEX IF NOT EXISTS idx_ip_rules_enabled ON ip_access_rules(enabled);
	CREATE INDEX IF NOT EXISTS idx_ip_rules_username ON ip_access_rules(username);
	`

	_, err := db.Exec(schema)
	return err
}

// --- FTP用户 CRUD ---

// CreateUser 创建FTP用户
func (db *DB) CreateUser(user *FTPUser) error {
	now := time.Now().Format("2006-01-02 15:04:05")
	result, err := db.Exec(`
		INSERT INTO ftp_users (username, password, home_dir, permissions, quota_size, quota_files, 
			upload_rate, download_rate, enabled, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		user.Username, user.Password, user.HomeDir, user.Permissions,
		user.QuotaSize, user.QuotaFiles, user.UploadRate, user.DownloadRate,
		user.Enabled, now, now)
	if err != nil {
		return fmt.Errorf("创建用户失败: %w", err)
	}
	user.ID, _ = result.LastInsertId()
	user.CreatedAt = now
	user.UpdatedAt = now
	return nil
}

// GetUser 获取用户
func (db *DB) GetUser(username string) (*FTPUser, error) {
	user := &FTPUser{}
	var enabled int
	err := db.QueryRow(`
		SELECT id, username, password, home_dir, permissions, quota_size, quota_files,
			upload_rate, download_rate, enabled, created_at, updated_at
		FROM ftp_users WHERE username = ?`, username,
	).Scan(&user.ID, &user.Username, &user.Password, &user.HomeDir, &user.Permissions,
		&user.QuotaSize, &user.QuotaFiles, &user.UploadRate, &user.DownloadRate,
		&enabled, &user.CreatedAt, &user.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	user.Enabled = enabled == 1
	return user, nil
}

// ListUsers 列出所有用户
func (db *DB) ListUsers() ([]FTPUser, error) {
	rows, err := db.Query(`
		SELECT id, username, password, home_dir, permissions, quota_size, quota_files,
			upload_rate, download_rate, enabled, created_at, updated_at
		FROM ftp_users ORDER BY id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []FTPUser
	for rows.Next() {
		var user FTPUser
		var enabled int
		err := rows.Scan(&user.ID, &user.Username, &user.Password, &user.HomeDir, &user.Permissions,
			&user.QuotaSize, &user.QuotaFiles, &user.UploadRate, &user.DownloadRate,
			&enabled, &user.CreatedAt, &user.UpdatedAt)
		if err != nil {
			return nil, err
		}
		user.Enabled = enabled == 1
		users = append(users, user)
	}
	return users, nil
}

// UpdateUser 更新用户
func (db *DB) UpdateUser(user *FTPUser) error {
	now := time.Now().Format("2006-01-02 15:04:05")
	_, err := db.Exec(`
		UPDATE ftp_users SET home_dir=?, permissions=?, quota_size=?, quota_files=?,
			upload_rate=?, download_rate=?, enabled=?, updated_at=?
		WHERE username=?`,
		user.HomeDir, user.Permissions, user.QuotaSize, user.QuotaFiles,
		user.UploadRate, user.DownloadRate, user.Enabled, now, user.Username)
	return err
}

// UpdateUserPassword 更新用户密码
func (db *DB) UpdateUserPassword(username, password string) error {
	now := time.Now().Format("2006-01-02 15:04:05")
	_, err := db.Exec(`UPDATE ftp_users SET password=?, updated_at=? WHERE username=?`,
		password, now, username)
	return err
}

// DeleteUser 删除用户
func (db *DB) DeleteUser(username string) error {
	_, err := db.Exec(`DELETE FROM ftp_users WHERE username=?`, username)
	return err
}

// --- 日志 ---

// AddLog 添加操作日志
func (db *DB) AddLog(log *FTPLog) error {
	now := time.Now().Format("2006-01-02 15:04:05")
	result, err := db.Exec(`
		INSERT INTO ftp_logs (username, ip, action, file_path, file_size, status, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)`,
		log.Username, log.IP, log.Action, log.FilePath, log.FileSize, log.Status, now)
	if err != nil {
		return err
	}
	log.ID, _ = result.LastInsertId()
	log.CreatedAt = now
	return nil
}

// QueryLogs 查询日志
func (db *DB) QueryLogs(username, action string, page, pageSize int) ([]FTPLog, int, error) {
	where := "WHERE 1=1"
	args := []interface{}{}

	if username != "" {
		where += " AND username LIKE ?"
		args = append(args, "%"+username+"%")
	}
	if action != "" {
		where += " AND action = ?"
		args = append(args, action)
	}

	// 获取总数
	var total int
	countSQL := fmt.Sprintf("SELECT COUNT(*) FROM ftp_logs %s", where)
	err := db.QueryRow(countSQL, args...).Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	// 分页查询
	offset := (page - 1) * pageSize
	querySQL := fmt.Sprintf(`
		SELECT id, username, ip, action, file_path, file_size, status, created_at
		FROM ftp_logs %s ORDER BY id DESC LIMIT ? OFFSET ?`, where)
	queryArgs := append(args, pageSize, offset)

	rows, err := db.Query(querySQL, queryArgs...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var logs []FTPLog
	for rows.Next() {
		var log FTPLog
		err := rows.Scan(&log.ID, &log.Username, &log.IP, &log.Action, &log.FilePath,
			&log.FileSize, &log.Status, &log.CreatedAt)
		if err != nil {
			return nil, 0, err
		}
		logs = append(logs, log)
	}
	return logs, total, nil
}

// GetLogStats 获取日志统计
func (db *DB) GetLogStats() (map[string]interface{}, error) {
	stats := make(map[string]interface{})

	var totalUsers int
	db.QueryRow("SELECT COUNT(*) FROM ftp_users").Scan(&totalUsers)
	stats["total_users"] = totalUsers

	var enabledUsers int
	db.QueryRow("SELECT COUNT(*) FROM ftp_users WHERE enabled=1").Scan(&enabledUsers)
	stats["enabled_users"] = enabledUsers

	var todayLogins int
	db.QueryRow("SELECT COUNT(DISTINCT username) FROM ftp_logs WHERE action='login' AND created_at >= date('now')").Scan(&todayLogins)
	stats["today_logins"] = todayLogins

	var todayUploads int
	db.QueryRow("SELECT COUNT(*) FROM ftp_logs WHERE action='upload' AND created_at >= date('now')").Scan(&todayUploads)
	stats["today_uploads"] = todayUploads

	var todayDownloads int
	db.QueryRow("SELECT COUNT(*) FROM ftp_logs WHERE action='download' AND created_at >= date('now')").Scan(&todayDownloads)
	stats["today_downloads"] = todayDownloads

	var totalUploadBytes int64
	db.QueryRow("SELECT COALESCE(SUM(file_size),0) FROM ftp_logs WHERE action='upload'").Scan(&totalUploadBytes)
	stats["total_upload_bytes"] = totalUploadBytes

	var totalDownloadBytes int64
	db.QueryRow("SELECT COALESCE(SUM(file_size),0) FROM ftp_logs WHERE action='download'").Scan(&totalDownloadBytes)
	stats["total_download_bytes"] = totalDownloadBytes

	return stats, nil
}

// CleanOldLogs 清理旧日志(保留最近N天)
func (db *DB) CleanOldLogs(days int) (int64, error) {
	result, err := db.Exec(`DELETE FROM ftp_logs WHERE created_at < datetime('now', ?||' days')`,
		fmt.Sprintf("-%d", days))
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// --- IP访问规则 CRUD ---

// CreateIPRule 创建IP规则
func (db *DB) CreateIPRule(rule *IPAccessRule) error {
	now := time.Now().Format("2006-01-02 15:04:05")
	result, err := db.Exec(`
		INSERT INTO ip_access_rules (username, ip, type, note, enabled, created_at)
		VALUES (?, ?, ?, ?, ?, ?)`,
		rule.Username, rule.IP, rule.Type, rule.Note, rule.Enabled, now)
	if err != nil {
		return fmt.Errorf("创建IP规则失败: %w", err)
	}
	rule.ID, _ = result.LastInsertId()
	rule.CreatedAt = now
	return nil
}

// ListIPRules 列出IP规则，ruleType和username为空时列出全部
func (db *DB) ListIPRules(ruleType, username string) ([]IPAccessRule, error) {
	query := `SELECT id, username, ip, type, note, enabled, created_at FROM ip_access_rules`
	var conditions []string
	var args []interface{}

	if ruleType != "" {
		conditions = append(conditions, "type=?")
		args = append(args, ruleType)
	}
	if username != "" {
		if username == "__empty__" {
			conditions = append(conditions, "username='')")
		} else if username == "__has__" {
			conditions = append(conditions, "username!='')")
		} else {
			conditions = append(conditions, "username=?")
			args = append(args, username)
		}
	}
	if len(conditions) > 0 {
		query += " WHERE " + strings.Join(conditions, " AND ")
	}
	query += " ORDER BY id"

	rows, err := db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var rules []IPAccessRule
	for rows.Next() {
		var rule IPAccessRule
		var enabled int
		if err := rows.Scan(&rule.ID, &rule.Username, &rule.IP, &rule.Type, &rule.Note, &enabled, &rule.CreatedAt); err != nil {
			return nil, err
		}
		rule.Enabled = enabled == 1
		rules = append(rules, rule)
	}
	return rules, nil
}

// DeleteIPRule 删除IP规则
func (db *DB) DeleteIPRule(id int64) error {
	_, err := db.Exec(`DELETE FROM ip_access_rules WHERE id=?`, id)
	return err
}

// UpdateIPRule 更新IP规则
func (db *DB) UpdateIPRule(rule *IPAccessRule) error {
	_, err := db.Exec(`UPDATE ip_access_rules SET username=?, ip=?, type=?, note=?, enabled=? WHERE id=?`,
		rule.Username, rule.IP, rule.Type, rule.Note, rule.Enabled, rule.ID)
	return err
}

// GetEnabledIPRules 获取所有启用的IP规则（全局+指定用户）
func (db *DB) GetEnabledIPRules(username string) ([]IPAccessRule, error) {
	rows, err := db.Query(`SELECT id, username, ip, type, note, enabled, created_at
		FROM ip_access_rules WHERE enabled=1 AND (username='' OR username=?) ORDER BY id`, username)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var rules []IPAccessRule
	for rows.Next() {
		var rule IPAccessRule
		var enabled int
		if err := rows.Scan(&rule.ID, &rule.Username, &rule.IP, &rule.Type, &rule.Note, &enabled, &rule.CreatedAt); err != nil {
			return nil, err
		}
		rule.Enabled = enabled == 1
		rules = append(rules, rule)
	}
	return rules, nil
}