client2server/server/store.go

// client2server - Persistent storage (SQLite)
//
// Tables:
//   users(id, username, password_hash, role, created_at)
//   events(id, router_id, event_type, hostname, payload_json, ts, received_at)
//   commands(id, router_id, command, args_json, issued_by, status, result, ts)
//   alerts(id, router_id, kind, message, created_at, acknowledged_at)

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"sync"
	"time"

	_ "modernc.org/sqlite"
)

var (
	db   *sql.DB
	dbMu sync.Mutex
)

func initStore(path string) error {
	d, err := sql.Open("sqlite", path+"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
	if err != nil {
		return fmt.Errorf("open sqlite: %w", err)
	}
	d.SetMaxOpenConns(1) // SQLite single-writer; many readers OK
	if err := d.Ping(); err != nil {
		return fmt.Errorf("ping sqlite: %w", err)
	}
	db = d

	schema := `
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS events (
    id TEXT PRIMARY KEY,
    router_id TEXT NOT NULL,
    event_type TEXT NOT NULL,
    hostname TEXT,
    payload_json TEXT,
    ts DATETIME,
    received_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_events_router ON events(router_id);
CREATE INDEX IF NOT EXISTS idx_events_ts ON events(received_at);
CREATE INDEX IF NOT EXISTS idx_events_type ON events(event_type);

CREATE TABLE IF NOT EXISTS commands (
    id TEXT PRIMARY KEY,
    router_id TEXT NOT NULL,
    command TEXT NOT NULL,
    args_json TEXT,
    issued_by TEXT,
    status TEXT NOT NULL,
    result_json TEXT,
    ts DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_commands_router ON commands(router_id);
CREATE INDEX IF NOT EXISTS idx_commands_ts ON commands(ts);

CREATE TABLE IF NOT EXISTS alerts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    router_id TEXT NOT NULL,
    kind TEXT NOT NULL,
    message TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    acknowledged_at DATETIME
);
CREATE INDEX IF NOT EXISTS idx_alerts_unack ON alerts(acknowledged_at);
`
	if _, err := db.Exec(schema); err != nil {
		return fmt.Errorf("schema: %w", err)
	}

	// Default admin user (username: admin, password: admin)
	// Only if no users exist
	var count int
	if err := db.QueryRow("SELECT COUNT(*) FROM users").Scan(&count); err != nil {
		return err
	}
	if count == 0 {
		hash, err := HashPassword("admin")
		if err != nil {
			return err
		}
		_, err = db.Exec(
			"INSERT INTO users (username, password_hash, role) VALUES (?, ?, ?)",
			"admin", hash, "system_admin",
		)
		if err != nil {
			return err
		}
		log.Println("created default admin user (username=admin password=admin) — CHANGE IT")
	}

	return nil
}

func saveEvent(ev RouterEvent) error {
	dbMu.Lock()
	defer dbMu.Unlock()
	payload, _ := json.Marshal(ev.Payload)
	var hostname *string
	if ev.Hostname != "" {
		hostname = &ev.Hostname
	}
	// Use ReceivedAt as fallback so listEvents never sees a zero time.
	ts := ev.ReceivedAt
	if !ev.Timestamp.IsZero() {
		ts = ev.Timestamp
	}
	_, err := db.Exec(
		`INSERT OR REPLACE INTO events (id, router_id, event_type, hostname, payload_json, ts, received_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?)`,
		ev.ID, ev.RouterID, ev.EventType, hostname, string(payload), ts, ev.ReceivedAt,
	)
	return err
}

func listEvents(limit int, routerID, eventType string) ([]RouterEvent, error) {
	dbMu.Lock()
	defer dbMu.Unlock()
	q := "SELECT id, router_id, event_type, COALESCE(hostname, ''), COALESCE(payload_json, '{}'), ts, received_at FROM events WHERE 1=1"
	args := []interface{}{}
	if routerID != "" {
		q += " AND router_id = ?"
		args = append(args, routerID)
	}
	if eventType != "" {
		q += " AND event_type = ?"
		args = append(args, eventType)
	}
	q += " ORDER BY received_at DESC LIMIT ?"
	args = append(args, limit)

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

	out := make([]RouterEvent, 0, limit)
	for rows.Next() {
		var ev RouterEvent
		var payloadJSON string
		var ts time.Time
		if err := rows.Scan(&ev.ID, &ev.RouterID, &ev.EventType, &ev.Hostname, &payloadJSON, &ts, &ev.ReceivedAt); err != nil {
			return nil, err
		}
		_ = json.Unmarshal([]byte(payloadJSON), &ev.Payload)
		ev.Timestamp = ts
		out = append(out, ev)
	}
	return out, nil
}

func saveCommand(cmd RouterCommand, issuedBy, status string) error {
	dbMu.Lock()
	defer dbMu.Unlock()
	argsJSON, _ := json.Marshal(cmd.Args)
	_, err := db.Exec(
		`INSERT OR REPLACE INTO commands (id, router_id, command, args_json, issued_by, status) VALUES (?, ?, ?, ?, ?, ?)`,
		cmd.ID, cmd.RouterID, cmd.Command, string(argsJSON), issuedBy, status,
	)
	return err
}

func updateCommandResult(cmdID, status string, result *CommandResult) error {
	dbMu.Lock()
	defer dbMu.Unlock()
	var resultJSON []byte
	if result != nil {
		resultJSON, _ = json.Marshal(result)
	}
	_, err := db.Exec(
		"UPDATE commands SET status = ?, result_json = ? WHERE id = ?",
		status, string(resultJSON), cmdID,
	)
	return err
}

func listCommands(limit int, routerID string) ([]map[string]interface{}, error) {
	dbMu.Lock()
	defer dbMu.Unlock()
	q := `SELECT id, router_id, command, COALESCE(args_json, '{}'), COALESCE(issued_by, ''), status, COALESCE(result_json, ''), ts
	      FROM commands WHERE 1=1`
	args := []interface{}{}
	if routerID != "" {
		q += " AND router_id = ?"
		args = append(args, routerID)
	}
	q += " ORDER BY ts DESC LIMIT ?"
	args = append(args, limit)

	rows, err := db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []map[string]interface{}{}
	for rows.Next() {
		var id, routerID, command, argsJSON, issuedBy, status, resultJSON, ts string
		if err := rows.Scan(&id, &routerID, &command, &argsJSON, &issuedBy, &status, &resultJSON, &ts); err != nil {
			return nil, err
		}
		entry := map[string]interface{}{
			"id": id, "router_id": routerID, "command": command,
			"issued_by": issuedBy, "status": status, "ts": ts,
		}
		var args map[string]string
		_ = json.Unmarshal([]byte(argsJSON), &args)
		entry["args"] = args
		if resultJSON != "" {
			var res map[string]interface{}
			_ = json.Unmarshal([]byte(resultJSON), &res)
			entry["result"] = res
		}
		out = append(out, entry)
	}
	return out, nil
}

func createAlert(routerID, kind, message string) {
	dbMu.Lock()
	defer dbMu.Unlock()
	_, _ = db.Exec(
		`INSERT INTO alerts (router_id, kind, message) VALUES (?, ?, ?)`,
		routerID, kind, message,
	)
}

func listAlerts(unackOnly bool, limit int) ([]map[string]interface{}, error) {
	dbMu.Lock()
	defer dbMu.Unlock()
	q := `SELECT id, router_id, kind, message, created_at, COALESCE(acknowledged_at, '') FROM alerts`
	if unackOnly {
		q += ` WHERE acknowledged_at IS NULL`
	}
	q += ` ORDER BY created_at DESC LIMIT ?`
	rows, err := db.Query(q, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []map[string]interface{}{}
	for rows.Next() {
		var id int64
		var routerID, kind, message, createdAt, ackedAt string
		if err := rows.Scan(&id, &routerID, &kind, &message, &createdAt, &ackedAt); err != nil {
			return nil, err
		}
		entry := map[string]interface{}{
			"id": id, "router_id": routerID, "kind": kind,
			"message": message, "created_at": createdAt,
		}
		if ackedAt != "" {
			entry["acknowledged_at"] = ackedAt
		}
		out = append(out, entry)
	}
	return out, nil
}

func acknowledgeAlert(id int64) error {
	dbMu.Lock()
	defer dbMu.Unlock()
	_, err := db.Exec("UPDATE alerts SET acknowledged_at = CURRENT_TIMESTAMP WHERE id = ?", id)
	return err
}