Cyrene/backend/gateway/internal/store/automation_store.go

package store

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"git.yeij.top/AskaEth/Cyrene/pkg/logger"
	"time"
)

// AutomationRule 自动化规则模型
type AutomationRule struct {
	ID              string           `json:"id"`
	UserID          string           `json:"user_id"`
	Name            string           `json:"name"`
	Description     string           `json:"description"`
	TriggerType     string           `json:"trigger_type"`
	TriggerConfig   *json.RawMessage `json:"trigger_config"`
	Conditions      *json.RawMessage `json:"conditions"`
	Actions         *json.RawMessage `json:"actions"`
	Enabled         bool             `json:"enabled"`
	LastTriggeredAt *time.Time       `json:"last_triggered_at,omitempty"`
	CreatedAt       time.Time        `json:"created_at"`
	UpdatedAt       time.Time        `json:"updated_at"`
}

// AutomationScene 自动化场景模型
type AutomationScene struct {
	ID        string           `json:"id"`
	UserID    string           `json:"user_id"`
	Name      string           `json:"name"`
	Icon      string           `json:"icon"`
	RuleIDs   *json.RawMessage `json:"rule_ids"`
	CreatedAt time.Time        `json:"created_at"`
}

// AutomationStore 自动化持久化存储
type AutomationStore struct {
	db *sql.DB
}

// NewAutomationStore 使用已有数据库连接初始化自动化存储并自动建表
func NewAutomationStore(db *sql.DB) (*AutomationStore, error) {
	store := &AutomationStore{db: db}

	if err := store.migrate(); err != nil {
		return nil, fmt.Errorf("自动化表迁移失败: %w", err)
	}

	logger.Println("[AutomationStore] 自动化持久化存储已初始化")
	return store, nil
}

// migrate 自动创建表结构
func (s *AutomationStore) migrate() error {
	queries := []string{
		`CREATE TABLE IF NOT EXISTS automation_rules (
			id VARCHAR(64) PRIMARY KEY,
			user_id VARCHAR(64) NOT NULL,
			name VARCHAR(255) NOT NULL,
			description TEXT DEFAULT '',
			trigger_type VARCHAR(32) NOT NULL,
			trigger_config JSONB DEFAULT '{}',
			conditions JSONB DEFAULT '[]',
			actions JSONB NOT NULL DEFAULT '[]',
			enabled BOOLEAN DEFAULT TRUE,
			last_triggered_at TIMESTAMP,
			created_at TIMESTAMP DEFAULT NOW(),
			updated_at TIMESTAMP DEFAULT NOW()
		)`,
		`CREATE INDEX IF NOT EXISTS idx_automation_rules_user_id ON automation_rules(user_id)`,
		`CREATE INDEX IF NOT EXISTS idx_automation_rules_enabled ON automation_rules(enabled)`,

		`CREATE TABLE IF NOT EXISTS automation_scenes (
			id VARCHAR(64) PRIMARY KEY,
			user_id VARCHAR(64) NOT NULL,
			name VARCHAR(255) NOT NULL,
			icon VARCHAR(64) DEFAULT '',
			rule_ids JSONB DEFAULT '[]',
			created_at TIMESTAMP DEFAULT NOW()
		)`,
		`CREATE INDEX IF NOT EXISTS idx_automation_scenes_user_id ON automation_scenes(user_id)`,
	}

	for _, q := range queries {
		if _, err := s.db.Exec(q); err != nil {
			return fmt.Errorf("迁移SQL执行失败: %w\nSQL: %s", err, q)
		}
	}
	return nil
}

// ========== Rule CRUD ==========

// CreateRule 创建新规则
func (s *AutomationStore) CreateRule(rule *AutomationRule) error {
	now := time.Now()
	rule.CreatedAt = now
	rule.UpdatedAt = now

	triggerConfig := jsonNull(rule.TriggerConfig)
	conditions := jsonNull(rule.Conditions)
	actions := jsonNull(rule.Actions)

	_, err := s.db.Exec(
		`INSERT INTO automation_rules (id, user_id, name, description, trigger_type, trigger_config, conditions, actions, enabled, created_at, updated_at)
		 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)`,
		rule.ID, rule.UserID, rule.Name, rule.Description, rule.TriggerType,
		triggerConfig, conditions, actions, rule.Enabled, rule.CreatedAt, rule.UpdatedAt,
	)
	if err != nil {
		return fmt.Errorf("创建规则失败: %w", err)
	}
	return nil
}

// GetRulesByUser 获取用户的所有规则
func (s *AutomationStore) GetRulesByUser(userID string) ([]AutomationRule, error) {
	rows, err := s.db.Query(
		`SELECT id, user_id, name, description, trigger_type, trigger_config, conditions, actions, enabled, last_triggered_at, created_at, updated_at
		 FROM automation_rules WHERE user_id = $1
		 ORDER BY created_at DESC`,
		userID,
	)
	if err != nil {
		return nil, fmt.Errorf("查询用户规则失败: %w", err)
	}
	defer rows.Close()

	var rules []AutomationRule
	for rows.Next() {
		var r AutomationRule
		if err := rows.Scan(&r.ID, &r.UserID, &r.Name, &r.Description, &r.TriggerType,
			&r.TriggerConfig, &r.Conditions, &r.Actions, &r.Enabled, &r.LastTriggeredAt,
			&r.CreatedAt, &r.UpdatedAt); err != nil {
			return nil, fmt.Errorf("扫描规则行失败: %w", err)
		}
		rules = append(rules, r)
	}

	if rules == nil {
		rules = []AutomationRule{}
	}
	return rules, rows.Err()
}

// GetRule 获取单个规则
func (s *AutomationStore) GetRule(id string) (*AutomationRule, error) {
	var r AutomationRule
	err := s.db.QueryRow(
		`SELECT id, user_id, name, description, trigger_type, trigger_config, conditions, actions, enabled, last_triggered_at, created_at, updated_at
		 FROM automation_rules WHERE id = $1`,
		id,
	).Scan(&r.ID, &r.UserID, &r.Name, &r.Description, &r.TriggerType,
		&r.TriggerConfig, &r.Conditions, &r.Actions, &r.Enabled, &r.LastTriggeredAt,
		&r.CreatedAt, &r.UpdatedAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("查询规则失败: %w", err)
	}
	return &r, nil
}

// UpdateRule 更新规则
func (s *AutomationStore) UpdateRule(rule *AutomationRule) error {
	triggerConfig := jsonNull(rule.TriggerConfig)
	conditions := jsonNull(rule.Conditions)
	actions := jsonNull(rule.Actions)

	_, err := s.db.Exec(
		`UPDATE automation_rules SET name = $1, description = $2, trigger_type = $3,
		 trigger_config = $4, conditions = $5, actions = $6, enabled = $7, updated_at = NOW()
		 WHERE id = $8`,
		rule.Name, rule.Description, rule.TriggerType,
		triggerConfig, conditions, actions, rule.Enabled, rule.ID,
	)
	if err != nil {
		return fmt.Errorf("更新规则失败: %w", err)
	}
	return nil
}

// DeleteRule 删除规则
func (s *AutomationStore) DeleteRule(id string) error {
	_, err := s.db.Exec(`DELETE FROM automation_rules WHERE id = $1`, id)
	if err != nil {
		return fmt.Errorf("删除规则失败: %w", err)
	}
	return nil
}

// GetEnabledRules 获取所有启用的规则（供引擎使用）
func (s *AutomationStore) GetEnabledRules() ([]AutomationRule, error) {
	rows, err := s.db.Query(
		`SELECT id, user_id, name, description, trigger_type, trigger_config, conditions, actions, enabled, last_triggered_at, created_at, updated_at
		 FROM automation_rules WHERE enabled = TRUE
		 ORDER BY created_at ASC`,
	)
	if err != nil {
		return nil, fmt.Errorf("查询启用的规则失败: %w", err)
	}
	defer rows.Close()

	var rules []AutomationRule
	for rows.Next() {
		var r AutomationRule
		if err := rows.Scan(&r.ID, &r.UserID, &r.Name, &r.Description, &r.TriggerType,
			&r.TriggerConfig, &r.Conditions, &r.Actions, &r.Enabled, &r.LastTriggeredAt,
			&r.CreatedAt, &r.UpdatedAt); err != nil {
			return nil, fmt.Errorf("扫描规则行失败: %w", err)
		}
		rules = append(rules, r)
	}

	if rules == nil {
		rules = []AutomationRule{}
	}
	return rules, rows.Err()
}

// MarkRuleTriggered 更新 last_triggered_at
func (s *AutomationStore) MarkRuleTriggered(id string) error {
	_, err := s.db.Exec(
		`UPDATE automation_rules SET last_triggered_at = NOW(), updated_at = NOW() WHERE id = $1`,
		id,
	)
	if err != nil {
		return fmt.Errorf("标记规则触发失败: %w", err)
	}
	return nil
}

// ========== Scene CRUD ==========

// CreateScene 创建新场景
func (s *AutomationStore) CreateScene(scene *AutomationScene) error {
	ruleIDs := jsonNull(scene.RuleIDs)

	_, err := s.db.Exec(
		`INSERT INTO automation_scenes (id, user_id, name, icon, rule_ids, created_at)
		 VALUES ($1, $2, $3, $4, $5, NOW())`,
		scene.ID, scene.UserID, scene.Name, scene.Icon, ruleIDs,
	)
	if err != nil {
		return fmt.Errorf("创建场景失败: %w", err)
	}
	return nil
}

// GetScenesByUser 获取用户的所有场景
func (s *AutomationStore) GetScenesByUser(userID string) ([]AutomationScene, error) {
	rows, err := s.db.Query(
		`SELECT id, user_id, name, icon, rule_ids, created_at
		 FROM automation_scenes WHERE user_id = $1
		 ORDER BY created_at DESC`,
		userID,
	)
	if err != nil {
		return nil, fmt.Errorf("查询用户场景失败: %w", err)
	}
	defer rows.Close()

	var scenes []AutomationScene
	for rows.Next() {
		var sc AutomationScene
		if err := rows.Scan(&sc.ID, &sc.UserID, &sc.Name, &sc.Icon, &sc.RuleIDs, &sc.CreatedAt); err != nil {
			return nil, fmt.Errorf("扫描场景行失败: %w", err)
		}
		scenes = append(scenes, sc)
	}

	if scenes == nil {
		scenes = []AutomationScene{}
	}
	return scenes, rows.Err()
}

// GetScene 获取单个场景
func (s *AutomationStore) GetScene(id string) (*AutomationScene, error) {
	var sc AutomationScene
	err := s.db.QueryRow(
		`SELECT id, user_id, name, icon, rule_ids, created_at
		 FROM automation_scenes WHERE id = $1`,
		id,
	).Scan(&sc.ID, &sc.UserID, &sc.Name, &sc.Icon, &sc.RuleIDs, &sc.CreatedAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("查询场景失败: %w", err)
	}
	return &sc, nil
}

// UpdateScene 更新场景
func (s *AutomationStore) UpdateScene(scene *AutomationScene) error {
	ruleIDs := jsonNull(scene.RuleIDs)

	_, err := s.db.Exec(
		`UPDATE automation_scenes SET name = $1, icon = $2, rule_ids = $3 WHERE id = $4`,
		scene.Name, scene.Icon, ruleIDs, scene.ID,
	)
	if err != nil {
		return fmt.Errorf("更新场景失败: %w", err)
	}
	return nil
}

// DeleteScene 删除场景
func (s *AutomationStore) DeleteScene(id string) error {
	_, err := s.db.Exec(`DELETE FROM automation_scenes WHERE id = $1`, id)
	if err != nil {
		return fmt.Errorf("删除场景失败: %w", err)
	}
	return nil
}

// GetSceneRules 根据 scene 的 rule_ids 取出所有关联的 rules
func (s *AutomationStore) GetSceneRules(sceneID string) ([]AutomationRule, error) {
	sc, err := s.GetScene(sceneID)
	if err != nil {
		return nil, err
	}
	if sc == nil {
		return []AutomationRule{}, nil
	}

	var ruleIDs []string
	if sc.RuleIDs != nil {
		if err := json.Unmarshal(*sc.RuleIDs, &ruleIDs); err != nil {
			return nil, fmt.Errorf("解析场景规则ID失败: %w", err)
		}
	}

	if len(ruleIDs) == 0 {
		return []AutomationRule{}, nil
	}

	// 构建 IN 查询
	var rules []AutomationRule
	for _, rid := range ruleIDs {
		r, err := s.GetRule(rid)
		if err != nil {
			return nil, fmt.Errorf("查询场景关联规则失败: %w", err)
		}
		if r != nil {
			rules = append(rules, *r)
		}
	}

	if rules == nil {
		rules = []AutomationRule{}
	}
	return rules, nil
}

// jsonNull 将 *json.RawMessage 转为可写入数据库的 JSON 或 null
func jsonNull(raw *json.RawMessage) interface{} {
	if raw == nil {
		return nil
	}
	return []byte(*raw)
}