ai-status-light/internal/database/database.go

package database

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"path/filepath"

	_ "modernc.org/sqlite"

	"ai-status-light/internal/logger"
)

type DB struct {
	db   *sql.DB
	conn *sql.Conn
}

type MQTTConfig struct {
	ID       int    `json:"id"`
	Broker   string `json:"broker"`
	ClientID string `json:"client_id"`
	Username string `json:"username,omitempty"`
	Password string `json:"password,omitempty"`
	Topic    string `json:"topic"`
	Enabled  bool   `json:"enabled"`
}

type BLEConfig struct {
	ID          int    `json:"id"`
	DeviceName  string `json:"device_name"`
	ServiceUUID string `json:"service_uuid"`
	CharUUID    string `json:"char_uuid"`
	Enabled     bool   `json:"enabled"`
}

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

	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		logger.Error("打开数据库失败: %v", err)
		return nil, fmt.Errorf("打开数据库失败: %w", err)
	}

	conn, err := db.Conn(context.Background())
	if err != nil {
		logger.Error("获取数据库连接失败: %v", err)
		return nil, fmt.Errorf("获取连接失败: %w", err)
	}

	d := &DB{db: db, conn: conn}
	if err := d.init(); err != nil {
		logger.Error("初始化数据库失败: %v", err)
		return nil, err
	}
	logger.Debug("数据库已打开: %s", dbPath)
	return d, nil
}

func (d *DB) init() error {
	query := `
	CREATE TABLE IF NOT EXISTS mqtt_config (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		broker TEXT NOT NULL,
		client_id TEXT NOT NULL,
		username TEXT DEFAULT '',
		password TEXT DEFAULT '',
		topic TEXT NOT NULL,
		enabled BOOLEAN DEFAULT 1
	);
	`
	_, err := d.conn.ExecContext(context.Background(), query)
	if err != nil {
		return err
	}

	for _, col := range []string{"username", "password"} {
		alterQuery := fmt.Sprintf("ALTER TABLE mqtt_config ADD COLUMN %s TEXT DEFAULT ''", col)
		d.conn.ExecContext(context.Background(), alterQuery)
	}

	bleQuery := `
	CREATE TABLE IF NOT EXISTS ble_config (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		device_name TEXT NOT NULL,
		service_uuid TEXT NOT NULL,
		char_uuid TEXT NOT NULL,
		enabled BOOLEAN DEFAULT 1
	);
	`
	_, err = d.conn.ExecContext(context.Background(), bleQuery)
	if err != nil {
		return err
	}

	for _, col := range []string{"service_uuid", "char_uuid"} {
		alterQuery := fmt.Sprintf("ALTER TABLE ble_config ADD COLUMN %s TEXT NOT NULL DEFAULT ''", col)
		d.conn.ExecContext(context.Background(), alterQuery)
	}

	return nil
}

func (d *DB) GetMQTTConfig() (*MQTTConfig, error) {
	query := "SELECT id, broker, client_id, username, password, topic, enabled FROM mqtt_config WHERE enabled = 1 LIMIT 1"
	row := d.conn.QueryRowContext(context.Background(), query)

	var cfg MQTTConfig
	err := row.Scan(&cfg.ID, &cfg.Broker, &cfg.ClientID, &cfg.Username, &cfg.Password, &cfg.Topic, &cfg.Enabled)
	if err == sql.ErrNoRows {
		logger.Debug("未找到启用的 MQTT 配置")
		return nil, nil
	}
	if err != nil {
		logger.Error("查询 MQTT 配置失败: %v", err)
		return nil, fmt.Errorf("查询配置失败: %w", err)
	}
	logger.Debug("获取到 MQTT 配置: id=%d, broker=%s", cfg.ID, cfg.Broker)
	return &cfg, nil
}

func (d *DB) SaveMQTTConfig(cfg *MQTTConfig) error {
	if cfg.ID == 0 {
		query := "INSERT INTO mqtt_config (broker, client_id, username, password, topic, enabled) VALUES (?, ?, ?, ?, ?, ?)"
		_, err := d.conn.ExecContext(context.Background(), query, cfg.Broker, cfg.ClientID, cfg.Username, cfg.Password, cfg.Topic, cfg.Enabled)
		if err != nil {
			logger.Error("插入 MQTT 配置失败: %v", err)
		}
		return err
	}
	query := "UPDATE mqtt_config SET broker = ?, client_id = ?, username = ?, password = ?, topic = ?, enabled = ? WHERE id = ?"
	_, err := d.conn.ExecContext(context.Background(), query, cfg.Broker, cfg.ClientID, cfg.Username, cfg.Password, cfg.Topic, cfg.Enabled, cfg.ID)
	if err != nil {
		logger.Error("更新 MQTT 配置失败: id=%d, %v", cfg.ID, err)
	}
	return err
}

func (d *DB) DeleteMQTTConfig(id int) error {
	query := "DELETE FROM mqtt_config WHERE id = ?"
	_, err := d.conn.ExecContext(context.Background(), query, id)
	if err != nil {
		logger.Error("删除 MQTT 配置失败: id=%d, %v", id, err)
	} else {
		logger.Debug("MQTT 配置已删除: id=%d", id)
	}
	return err
}

func (d *DB) ListMQTTConfigs() ([]MQTTConfig, error) {
	query := "SELECT id, broker, client_id, username, password, topic, enabled FROM mqtt_config ORDER BY id"
	rows, err := d.conn.QueryContext(context.Background(), query)
	if err != nil {
		logger.Error("查询 MQTT 配置列表失败: %v", err)
		return nil, err
	}
	defer rows.Close()

	var configs []MQTTConfig
	for rows.Next() {
		var cfg MQTTConfig
		if err := rows.Scan(&cfg.ID, &cfg.Broker, &cfg.ClientID, &cfg.Username, &cfg.Password, &cfg.Topic, &cfg.Enabled); err != nil {
			logger.Warn("扫描 MQTT 配置行失败: %v", err)
			continue
		}
		configs = append(configs, cfg)
	}
	logger.Debug("查询到 %d 条 MQTT 配置", len(configs))
	return configs, nil
}

func (d *DB) Close() error {
	logger.Debug("数据库连接已关闭")
	if err := d.conn.Close(); err != nil {
		return err
	}
	return d.db.Close()
}

func (d *DB) GetBLEConfig() (*BLEConfig, error) {
	query := "SELECT id, device_name, service_uuid, char_uuid, enabled FROM ble_config WHERE enabled = 1 LIMIT 1"
	row := d.conn.QueryRowContext(context.Background(), query)

	var cfg BLEConfig
	err := row.Scan(&cfg.ID, &cfg.DeviceName, &cfg.ServiceUUID, &cfg.CharUUID, &cfg.Enabled)
	if err == sql.ErrNoRows {
		logger.Debug("未找到启用的 BLE 配置")
		return nil, nil
	}
	if err != nil {
		logger.Error("查询 BLE 配置失败: %v", err)
		return nil, fmt.Errorf("查询配置失败: %w", err)
	}
	logger.Debug("获取到 BLE 配置: id=%d, device=%s", cfg.ID, cfg.DeviceName)
	return &cfg, nil
}

func (d *DB) SaveBLEConfig(cfg *BLEConfig) error {
	if cfg.ID == 0 {
		query := "INSERT INTO ble_config (device_name, service_uuid, char_uuid, enabled) VALUES (?, ?, ?, ?)"
		_, err := d.conn.ExecContext(context.Background(), query, cfg.DeviceName, cfg.ServiceUUID, cfg.CharUUID, cfg.Enabled)
		if err != nil {
			logger.Error("插入 BLE 配置失败: %v", err)
		}
		return err
	}
	query := "UPDATE ble_config SET device_name = ?, service_uuid = ?, char_uuid = ?, enabled = ? WHERE id = ?"
	_, err := d.conn.ExecContext(context.Background(), query, cfg.DeviceName, cfg.ServiceUUID, cfg.CharUUID, cfg.Enabled, cfg.ID)
	if err != nil {
		logger.Error("更新 BLE 配置失败: id=%d, %v", cfg.ID, err)
	}
	return err
}

func (d *DB) DeleteBLEConfig(id int) error {
	query := "DELETE FROM ble_config WHERE id = ?"
	_, err := d.conn.ExecContext(context.Background(), query, id)
	if err != nil {
		logger.Error("删除 BLE 配置失败: id=%d, %v", id, err)
	} else {
		logger.Debug("BLE 配置已删除: id=%d", id)
	}
	return err
}

func (d *DB) ListBLEConfigs() ([]BLEConfig, error) {
	query := "SELECT id, device_name, service_uuid, char_uuid, enabled FROM ble_config ORDER BY id"
	rows, err := d.conn.QueryContext(context.Background(), query)
	if err != nil {
		logger.Error("查询 BLE 配置列表失败: %v", err)
		return nil, err
	}
	defer rows.Close()

	var configs []BLEConfig
	for rows.Next() {
		var cfg BLEConfig
		if err := rows.Scan(&cfg.ID, &cfg.DeviceName, &cfg.ServiceUUID, &cfg.CharUUID, &cfg.Enabled); err != nil {
			logger.Warn("扫描 BLE 配置行失败: %v", err)
			continue
		}
		configs = append(configs, cfg)
	}
	logger.Debug("查询到 %d 条 BLE 配置", len(configs))
	return configs, nil
}