LINUX-AETHERFORGE/internal/db/db.go

package db

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

	_ "github.com/mattn/go-sqlite3"
)

const schema = `
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS hosts (
	id TEXT PRIMARY KEY,
	hostname TEXT NOT NULL,
	fingerprint TEXT,
	phenotype TEXT,
	status TEXT NOT NULL DEFAULT 'offline',
	hashrate REAL NOT NULL DEFAULT 0,
	hashrate_hps REAL NOT NULL DEFAULT 0,
	current_tier INTEGER NOT NULL DEFAULT 0,
	tier_type TEXT NOT NULL DEFAULT '',
	tier_state TEXT NOT NULL DEFAULT 'idle',
	clearance_level INTEGER NOT NULL DEFAULT 0,
	mining_profile_id TEXT,
	last_seen_at TEXT,
	created_at TEXT NOT NULL DEFAULT (datetime('now')),
	updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS tiers (
	id TEXT PRIMARY KEY,
	host_id TEXT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
	tier_order INTEGER NOT NULL,
	tier_type TEXT NOT NULL,
	config_json TEXT NOT NULL DEFAULT '{}',
	status TEXT NOT NULL DEFAULT 'pending',
	started_at TEXT,
	ended_at TEXT,
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_tiers_host_id ON tiers(host_id);

CREATE TABLE IF NOT EXISTS campaigns (
	id TEXT PRIMARY KEY,
	code TEXT NOT NULL UNIQUE,
	name TEXT NOT NULL,
	pin TEXT,
	heat INTEGER NOT NULL DEFAULT 0,
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS lotl_attempts (
	id TEXT PRIMARY KEY,
	host_id TEXT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
	tier INTEGER NOT NULL,
	phase TEXT NOT NULL,
	status TEXT NOT NULL,
	error TEXT,
	metadata_json TEXT NOT NULL DEFAULT '{}',
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_lotl_attempts_host_id ON lotl_attempts(host_id);

CREATE TABLE IF NOT EXISTS mining_profiles (
	id TEXT PRIMARY KEY,
	name TEXT NOT NULL,
	wallet_address TEXT NOT NULL DEFAULT '',
	tiers_json TEXT NOT NULL DEFAULT '[]',
	policy_from_server INTEGER NOT NULL DEFAULT 1,
	created_at TEXT NOT NULL DEFAULT (datetime('now')),
	updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS builds (
	id TEXT PRIMARY KEY,
	os TEXT NOT NULL,
	arch TEXT NOT NULL,
	version TEXT NOT NULL,
	checksum TEXT NOT NULL,
	signature TEXT,
	public INTEGER NOT NULL DEFAULT 0,
	path TEXT,
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_builds_public ON builds(public, os, arch);

CREATE TABLE IF NOT EXISTS failure_atlas (
	id TEXT PRIMARY KEY,
	phenotype TEXT NOT NULL,
	tier INTEGER NOT NULL,
	failure_count INTEGER NOT NULL DEFAULT 0,
	immune_until TEXT,
	created_at TEXT NOT NULL DEFAULT (datetime('now')),
	updated_at TEXT NOT NULL DEFAULT (datetime('now')),
	UNIQUE(phenotype, tier)
);

CREATE TABLE IF NOT EXISTS subnet_cidrs (
	id TEXT PRIMARY KEY,
	cidr TEXT NOT NULL UNIQUE,
	enabled INTEGER NOT NULL DEFAULT 1,
	last_scan_at TEXT,
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS subnet_immune (
	prefix TEXT PRIMARY KEY,
	failure_count INTEGER NOT NULL DEFAULT 0,
	paused_until TEXT,
	updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS phenotype_tier_orders (
	phenotype TEXT PRIMARY KEY,
	tier_order_json TEXT NOT NULL DEFAULT '[]',
	wins INTEGER NOT NULL DEFAULT 0,
	updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS erasure_bundles (
	id TEXT PRIMARY KEY,
	name TEXT NOT NULL,
	total_shards INTEGER NOT NULL DEFAULT 6,
	data_shards INTEGER NOT NULL DEFAULT 4,
	parity_shards INTEGER NOT NULL DEFAULT 2,
	checksum TEXT NOT NULL,
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS erasure_shards (
	id TEXT PRIMARY KEY,
	bundle_id TEXT NOT NULL REFERENCES erasure_bundles(id) ON DELETE CASCADE,
	shard_index INTEGER NOT NULL,
	data BLOB NOT NULL,
	created_at TEXT NOT NULL DEFAULT (datetime('now')),
	UNIQUE(bundle_id, shard_index)
);

CREATE TABLE IF NOT EXISTS policy_snapshots (
	token TEXT PRIMARY KEY,
	policy_json TEXT NOT NULL,
	expires_at TEXT,
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS court_sessions (
	id TEXT PRIMARY KEY,
	host_id TEXT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
	status TEXT NOT NULL DEFAULT 'pending',
	transcript_json TEXT NOT NULL DEFAULT '[]',
	verdict TEXT,
	clearance_required INTEGER NOT NULL DEFAULT 4,
	created_at TEXT NOT NULL DEFAULT (datetime('now')),
	resolved_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_court_sessions_host_id ON court_sessions(host_id);

CREATE TABLE IF NOT EXISTS seer_events (
	id TEXT PRIMARY KEY,
	host_id TEXT,
	event_type TEXT NOT NULL,
	payload_json TEXT NOT NULL DEFAULT '{}',
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_seer_events_created_at ON seer_events(created_at);

CREATE TABLE IF NOT EXISTS wireguard_peers (
	id TEXT PRIMARY KEY,
	host_id TEXT,
	public_key TEXT NOT NULL,
	endpoint TEXT,
	allowed_ips TEXT NOT NULL DEFAULT '10.66.66.2/32',
	config_json TEXT NOT NULL DEFAULT '{}',
	created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
`

// Open opens (or creates) the SQLite database and applies the schema.
func Open(path string) (*sql.DB, error) {
	if err := os.MkdirAll(filepath.Dir(path), 0o755); err != nil {
		return nil, fmt.Errorf("mkdir db dir: %w", err)
	}

	dsn := fmt.Sprintf("file:%s?_foreign_keys=on&_journal_mode=WAL", path)
	conn, err := sql.Open("sqlite3", dsn)
	if err != nil {
		return nil, fmt.Errorf("open sqlite: %w", err)
	}

	if err := conn.Ping(); err != nil {
		conn.Close()
		return nil, fmt.Errorf("ping sqlite: %w", err)
	}

	if _, err := conn.Exec(schema); err != nil {
		conn.Close()
		return nil, fmt.Errorf("apply schema: %w", err)
	}

	if err := migrate(conn); err != nil {
		conn.Close()
		return nil, fmt.Errorf("migrate: %w", err)
	}

	return conn, nil
}

func migrate(conn *sql.DB) error {
	columns := []string{
		"ALTER TABLE hosts ADD COLUMN hashrate_hps REAL NOT NULL DEFAULT 0",
		"ALTER TABLE hosts ADD COLUMN current_tier INTEGER NOT NULL DEFAULT 0",
		"ALTER TABLE hosts ADD COLUMN tier_type TEXT NOT NULL DEFAULT ''",
		"ALTER TABLE hosts ADD COLUMN tier_state TEXT NOT NULL DEFAULT 'idle'",
	}
	for _, stmt := range columns {
		if _, err := conn.Exec(stmt); err != nil {
			if !isDuplicateColumn(err) {
				return err
			}
		}
	}
	return nil
}

func isDuplicateColumn(err error) bool {
	if err == nil {
		return false
	}
	msg := err.Error()
	return strings.Contains(msg, "duplicate column") || strings.Contains(msg, "already exists")
}