tera-clone-web/internal/store/sqlite_store.go

package store

import (
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"errors"
	"os"
	"path/filepath"
	"sort"
	"strings"
	"time"

	_ "modernc.org/sqlite"
)

var ErrDependencyExists = errors.New("group is assigned to one or more users")

type SQLiteStore struct {
	db *sql.DB
}

type ActivityRecord struct {
	ID        int64
	Kind      string
	Path      string
	Title     string
	Action    string
	CreatedAt time.Time
}

type UserRecord struct {
	ID           int64
	Username     string
	GroupName    string
	Enabled      bool
	PasswordHash string
	CreatedAt    time.Time
	UpdatedAt    time.Time
}

type CreateUserParams struct {
	Username  string
	Password  string
	GroupName string
	Enabled   bool
}

type UpdateUserParams struct {
	ID        int64
	Username  string
	Password  string
	GroupName string
	Enabled   bool
}

type GroupRecord struct {
	ID          int64
	Name        string
	Description string
	Permissions []string
	CreatedAt   time.Time
	UpdatedAt   time.Time
}

type CreateGroupParams struct {
	Name        string
	Description string
	Permissions []string
}

type UpdateGroupParams struct {
	ID          int64
	Name        string
	Description string
	Permissions []string
}

func NewSQLiteStore(dbPath string) (*SQLiteStore, error) {
	if err := os.MkdirAll(filepath.Dir(dbPath), 0o755); err != nil {
		return nil, err
	}

	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, err
	}

	store := &SQLiteStore{db: db}
	if err := store.init(); err != nil {
		_ = db.Close()
		return nil, err
	}

	return store, nil
}

func (s *SQLiteStore) Close() error {
	if s == nil || s.db == nil {
		return nil
	}
	return s.db.Close()
}

func (s *SQLiteStore) RecordPageVisit(path string, title string) error {
	_, err := s.db.Exec(`
		INSERT INTO activity_logs (kind, path, title, action)
		VALUES ('page_visit', ?, ?, '')
	`, path, title)
	return err
}

func (s *SQLiteStore) RecordMockAction(action string, path string) error {
	_, err := s.db.Exec(`
		INSERT INTO activity_logs (kind, path, title, action)
		VALUES ('mock_action', ?, '', ?)
	`, path, action)
	return err
}

func (s *SQLiteStore) RecentActivity(limit int) ([]ActivityRecord, error) {
	if limit <= 0 {
		limit = 20
	}

	rows, err := s.db.Query(`
		SELECT id, kind, path, title, action, created_at
		FROM activity_logs
		ORDER BY id DESC
		LIMIT ?
	`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	records := make([]ActivityRecord, 0, limit)
	for rows.Next() {
		var record ActivityRecord
		if err := rows.Scan(&record.ID, &record.Kind, &record.Path, &record.Title, &record.Action, &record.CreatedAt); err != nil {
			return nil, err
		}
		records = append(records, record)
	}

	return records, rows.Err()
}

func (s *SQLiteStore) ListUsers() ([]UserRecord, error) {
	rows, err := s.db.Query(`
		SELECT id, username, group_name, enabled, created_at, updated_at
		FROM users
		ORDER BY username COLLATE NOCASE ASC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	users := make([]UserRecord, 0)
	for rows.Next() {
		var user UserRecord
		if err := rows.Scan(&user.ID, &user.Username, &user.GroupName, &user.Enabled, &user.CreatedAt, &user.UpdatedAt); err != nil {
			return nil, err
		}
		users = append(users, user)
	}

	return users, rows.Err()
}

func (s *SQLiteStore) GetUser(id int64) (UserRecord, error) {
	var user UserRecord
	err := s.db.QueryRow(`
		SELECT id, username, group_name, enabled, created_at, updated_at
		FROM users
		WHERE id = ?
	`, id).Scan(&user.ID, &user.Username, &user.GroupName, &user.Enabled, &user.CreatedAt, &user.UpdatedAt)
	return user, err
}

func (s *SQLiteStore) GetUserByUsername(username string) (UserRecord, error) {
	var user UserRecord
	err := s.db.QueryRow(`
		SELECT id, username, group_name, enabled, password_hash, created_at, updated_at
		FROM users
		WHERE username = ?
	`, strings.TrimSpace(username)).Scan(
		&user.ID,
		&user.Username,
		&user.GroupName,
		&user.Enabled,
		&user.PasswordHash,
		&user.CreatedAt,
		&user.UpdatedAt,
	)
	return user, err
}

func (s *SQLiteStore) ListGroups() ([]GroupRecord, error) {
	rows, err := s.db.Query(`
		SELECT id, name, description, permissions, created_at, updated_at
		FROM groups
		ORDER BY name COLLATE NOCASE ASC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	groups := make([]GroupRecord, 0)
	for rows.Next() {
		var group GroupRecord
		var permissions string
		if err := rows.Scan(&group.ID, &group.Name, &group.Description, &permissions, &group.CreatedAt, &group.UpdatedAt); err != nil {
			return nil, err
		}
		group.Permissions = decodePermissions(permissions)
		groups = append(groups, group)
	}

	return groups, rows.Err()
}

func (s *SQLiteStore) GetGroup(id int64) (GroupRecord, error) {
	var group GroupRecord
	var permissions string
	err := s.db.QueryRow(`
		SELECT id, name, description, permissions, created_at, updated_at
		FROM groups
		WHERE id = ?
	`, id).Scan(&group.ID, &group.Name, &group.Description, &permissions, &group.CreatedAt, &group.UpdatedAt)
	if err != nil {
		return GroupRecord{}, err
	}
	group.Permissions = decodePermissions(permissions)
	return group, nil
}

func (s *SQLiteStore) GetGroupByName(name string) (GroupRecord, error) {
	var group GroupRecord
	var permissions string
	err := s.db.QueryRow(`
		SELECT id, name, description, permissions, created_at, updated_at
		FROM groups
		WHERE name = ?
	`, strings.TrimSpace(name)).Scan(&group.ID, &group.Name, &group.Description, &permissions, &group.CreatedAt, &group.UpdatedAt)
	if err != nil {
		return GroupRecord{}, err
	}
	group.Permissions = decodePermissions(permissions)
	return group, nil
}

func (s *SQLiteStore) CreateGroup(params CreateGroupParams) (GroupRecord, error) {
	result, err := s.db.Exec(`
		INSERT INTO groups (name, description, permissions)
		VALUES (?, ?, ?)
	`, strings.TrimSpace(params.Name), strings.TrimSpace(params.Description), encodePermissions(params.Permissions))
	if err != nil {
		return GroupRecord{}, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return GroupRecord{}, err
	}

	return s.GetGroup(id)
}

func (s *SQLiteStore) UpdateGroup(params UpdateGroupParams) (GroupRecord, error) {
	current, err := s.GetGroup(params.ID)
	if err != nil {
		return GroupRecord{}, err
	}

	tx, err := s.db.Begin()
	if err != nil {
		return GroupRecord{}, err
	}

	if _, err := tx.Exec(`
		UPDATE groups
		SET name = ?, description = ?, permissions = ?, updated_at = CURRENT_TIMESTAMP
		WHERE id = ?
	`, strings.TrimSpace(params.Name), strings.TrimSpace(params.Description), encodePermissions(params.Permissions), params.ID); err != nil {
		_ = tx.Rollback()
		return GroupRecord{}, err
	}

	if current.Name != strings.TrimSpace(params.Name) {
		if _, err := tx.Exec(`
			UPDATE users
			SET group_name = ?, updated_at = CURRENT_TIMESTAMP
			WHERE group_name = ?
		`, strings.TrimSpace(params.Name), current.Name); err != nil {
			_ = tx.Rollback()
			return GroupRecord{}, err
		}
	}

	if err := tx.Commit(); err != nil {
		return GroupRecord{}, err
	}

	return s.GetGroup(params.ID)
}

func (s *SQLiteStore) DeleteGroup(id int64) error {
	group, err := s.GetGroup(id)
	if err != nil {
		return err
	}

	var userCount int
	if err := s.db.QueryRow(`
		SELECT COUNT(*)
		FROM users
		WHERE group_name = ?
	`, group.Name).Scan(&userCount); err != nil {
		return err
	}
	if userCount > 0 {
		return ErrDependencyExists
	}

	result, err := s.db.Exec(`DELETE FROM groups WHERE id = ?`, id)
	if err != nil {
		return err
	}
	rows, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if rows == 0 {
		return sql.ErrNoRows
	}
	return nil
}

func (s *SQLiteStore) CreateUser(params CreateUserParams) (UserRecord, error) {
	result, err := s.db.Exec(`
		INSERT INTO users (username, password_hash, group_name, enabled)
		VALUES (?, ?, ?, ?)
	`, strings.TrimSpace(params.Username), hashPassword(params.Password), strings.TrimSpace(params.GroupName), params.Enabled)
	if err != nil {
		return UserRecord{}, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return UserRecord{}, err
	}

	return s.GetUser(id)
}

func (s *SQLiteStore) UpdateUser(params UpdateUserParams) (UserRecord, error) {
	if strings.TrimSpace(params.Password) == "" {
		result, err := s.db.Exec(`
			UPDATE users
			SET username = ?, group_name = ?, enabled = ?, updated_at = CURRENT_TIMESTAMP
			WHERE id = ?
		`, strings.TrimSpace(params.Username), strings.TrimSpace(params.GroupName), params.Enabled, params.ID)
		if err != nil {
			return UserRecord{}, err
		}
		rows, err := result.RowsAffected()
		if err != nil {
			return UserRecord{}, err
		}
		if rows == 0 {
			return UserRecord{}, sql.ErrNoRows
		}
		return s.GetUser(params.ID)
	}

	result, err := s.db.Exec(`
		UPDATE users
		SET username = ?, password_hash = ?, group_name = ?, enabled = ?, updated_at = CURRENT_TIMESTAMP
		WHERE id = ?
	`, strings.TrimSpace(params.Username), hashPassword(params.Password), strings.TrimSpace(params.GroupName), params.Enabled, params.ID)
	if err != nil {
		return UserRecord{}, err
	}
	rows, err := result.RowsAffected()
	if err != nil {
		return UserRecord{}, err
	}
	if rows == 0 {
		return UserRecord{}, sql.ErrNoRows
	}
	return s.GetUser(params.ID)
}

func (s *SQLiteStore) DeleteUser(id int64) error {
	result, err := s.db.Exec(`DELETE FROM users WHERE id = ?`, id)
	if err != nil {
		return err
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if rows == 0 {
		return sql.ErrNoRows
	}
	return nil
}

func (s *SQLiteStore) init() error {
	_, err := s.db.Exec(`
		PRAGMA journal_mode = WAL;

		CREATE TABLE IF NOT EXISTS activity_logs (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			kind TEXT NOT NULL,
			path TEXT NOT NULL DEFAULT '',
			title TEXT NOT NULL DEFAULT '',
			action TEXT NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
		);

		CREATE TABLE IF NOT EXISTS users (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			username TEXT NOT NULL UNIQUE,
			password_hash TEXT NOT NULL,
			group_name TEXT NOT NULL,
			enabled INTEGER NOT NULL DEFAULT 1,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
		);

		CREATE TABLE IF NOT EXISTS groups (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL UNIQUE,
			description TEXT NOT NULL DEFAULT '',
			permissions TEXT NOT NULL DEFAULT '[]',
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
		);
	`)
	if err != nil {
		return err
	}

	if err := s.ensureDefaultGroups(); err != nil {
		return err
	}
	return s.ensureDefaultUsers()
}

func (s *SQLiteStore) ensureDefaultGroups() error {
	defaultGroups := []CreateGroupParams{
		{Name: "administrator", Description: "전체 접근 가능", Permissions: defaultPermissionKeys()},
		{Name: "operator", Description: "운영 담당 권한 그룹", Permissions: []string{"network", "ports", "admin", "common", "status", "password"}},
		{Name: "guest", Description: "조회 전용", Permissions: []string{"status"}},
	}

	tx, err := s.db.Begin()
	if err != nil {
		return err
	}

	for _, group := range defaultGroups {
		if _, err := tx.Exec(`
			INSERT OR IGNORE INTO groups (name, description, permissions)
			VALUES (?, ?, ?)
		`, group.Name, group.Description, encodePermissions(group.Permissions)); err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	return tx.Commit()
}

func (s *SQLiteStore) ensureDefaultUsers() error {
	defaultUsers := []CreateUserParams{
		{Username: "terauser", Password: "terauser", GroupName: "administrator", Enabled: true},
		{Username: "guest", Password: "guest", GroupName: "guest", Enabled: true},
		{Username: "admin", Password: "admin", GroupName: "administrator", Enabled: true},
	}

	tx, err := s.db.Begin()
	if err != nil {
		return err
	}

	for _, user := range defaultUsers {
		if _, err := tx.Exec(`
			INSERT OR IGNORE INTO users (username, password_hash, group_name, enabled)
			VALUES (?, ?, ?, ?)
		`, user.Username, hashPassword(user.Password), user.GroupName, user.Enabled); err != nil {
			_ = tx.Rollback()
			return err
		}
	}

	return tx.Commit()
}

func hashPassword(password string) string {
	sum := sha256.Sum256([]byte(password))
	return hex.EncodeToString(sum[:])
}

func VerifyPassword(password string, encoded string) bool {
	return hashPassword(password) == encoded
}

func IsUniqueConstraintError(err error) bool {
	return err != nil && strings.Contains(strings.ToLower(err.Error()), "unique")
}

func IsNotFoundError(err error) bool {
	return errors.Is(err, sql.ErrNoRows)
}

func IsDependencyError(err error) bool {
	return errors.Is(err, ErrDependencyExists)
}

func encodePermissions(permissions []string) string {
	if len(permissions) == 0 {
		return "[]"
	}

	normalized := normalizePermissions(permissions)
	payload, err := json.Marshal(normalized)
	if err != nil {
		return "[]"
	}
	return string(payload)
}

func decodePermissions(raw string) []string {
	if strings.TrimSpace(raw) == "" {
		return nil
	}

	var permissions []string
	if err := json.Unmarshal([]byte(raw), &permissions); err != nil {
		return nil
	}
	return normalizePermissions(permissions)
}

func normalizePermissions(permissions []string) []string {
	seen := make(map[string]struct{})
	result := make([]string, 0, len(permissions))
	for _, permission := range permissions {
		permission = strings.TrimSpace(permission)
		if permission == "" {
			continue
		}
		if _, ok := seen[permission]; ok {
			continue
		}
		seen[permission] = struct{}{}
		result = append(result, permission)
	}
	sort.Strings(result)
	return result
}

func defaultPermissionKeys() []string {
	return []string{"admin", "common", "login", "network", "operate", "password", "ports", "status", "tools"}
}