database.go

package main

import (
	"database/sql"
	"os"
	"path/filepath"
	"time"

	_ "modernc.org/sqlite"
)

// ─── SQLite Database ────────────────────────────────────────────────

var blocksDB *sql.DB

func dbPath() string {
	exe, err := os.Executable()
	if err != nil {
		return "blocks.db"
	}
	return filepath.Join(filepath.Dir(exe), "blocks.db")
}

func initDB() {
	path := dbPath()
	dir := filepath.Dir(path)
	_ = os.MkdirAll(dir, 0o755)

	var err error
	blocksDB, err = sql.Open("sqlite", path)
	if err != nil {
		return
	}

	blocksDB.Exec(`CREATE TABLE IF NOT EXISTS blocked_users (
		username TEXT PRIMARY KEY
	)`)
	blocksDB.Exec(`CREATE TABLE IF NOT EXISTS blocked_stories (
		story_id INTEGER PRIMARY KEY
	)`)
	blocksDB.Exec(`CREATE TABLE IF NOT EXISTS blocked_keywords (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		word TEXT NOT NULL,
		scope TEXT NOT NULL DEFAULT 'both'
	)`)

	blocksDB.Exec(`CREATE TABLE IF NOT EXISTS blocked_domains (
		domain TEXT PRIMARY KEY
	)`)

	blocksDB.Exec(`CREATE TABLE IF NOT EXISTS bookmarks (
		story_id INTEGER PRIMARY KEY,
		title TEXT NOT NULL DEFAULT '',
		url TEXT NOT NULL DEFAULT '',
		domain TEXT NOT NULL DEFAULT '',
		by_user TEXT NOT NULL DEFAULT '',
		score INTEGER NOT NULL DEFAULT 0,
		num_comments INTEGER NOT NULL DEFAULT 0,
		bookmarked_at TEXT NOT NULL
	)`)

}

func loadBlocks() blockList {
	b := newBlockList()
	if blocksDB == nil {
		return b
	}

	// Load users
	rows, err := blocksDB.Query("SELECT username FROM blocked_users")
	if err == nil {
		defer rows.Close()
		for rows.Next() {
			var u string
			if rows.Scan(&u) == nil {
				b.Users[u] = true
			}
		}
	}

	// Load stories
	rows2, err := blocksDB.Query("SELECT story_id FROM blocked_stories")
	if err == nil {
		defer rows2.Close()
		for rows2.Next() {
			var id int
			if rows2.Scan(&id) == nil {
				b.Stories[id] = true
			}
		}
	}

	// Load keywords
	rows3, err := blocksDB.Query("SELECT word, scope FROM blocked_keywords ORDER BY id")
	if err == nil {
		defer rows3.Close()
		for rows3.Next() {
			var word, scope string
			if rows3.Scan(&word, &scope) == nil {
				b.Keywords = append(b.Keywords, BlockedKeyword{
					Word:  word,
					Scope: parseScopeString(scope),
				})
			}
		}
	}

	// Load domains
	rows4, err := blocksDB.Query("SELECT domain FROM blocked_domains")
	if err == nil {
		defer rows4.Close()
		for rows4.Next() {
			var d string
			if rows4.Scan(&d) == nil {
				b.Domains[d] = true
			}
		}
	}

	return b
}

func saveBlocks(b blockList) {
	if blocksDB == nil {
		return
	}

	tx, err := blocksDB.Begin()
	if err != nil {
		return
	}
	defer tx.Rollback()

	// Clear and reinsert all data
	tx.Exec("DELETE FROM blocked_users")
	tx.Exec("DELETE FROM blocked_stories")
	tx.Exec("DELETE FROM blocked_keywords")
	tx.Exec("DELETE FROM blocked_domains")

	for u := range b.Users {
		tx.Exec("INSERT INTO blocked_users (username) VALUES (?)", u)
	}
	for id := range b.Stories {
		tx.Exec("INSERT INTO blocked_stories (story_id) VALUES (?)", id)
	}
	for _, kw := range b.Keywords {
		tx.Exec("INSERT INTO blocked_keywords (word, scope) VALUES (?, ?)", kw.Word, kw.Scope.String())
	}
	for d := range b.Domains {
		tx.Exec("INSERT INTO blocked_domains (domain) VALUES (?)", d)
	}

	_ = tx.Commit()
}

// ─── Bookmarks ──────────────────────────────────────────────────────

func loadBookmarks() map[int]Bookmark {
	bm := make(map[int]Bookmark)
	if blocksDB == nil {
		return bm
	}
	rows, err := blocksDB.Query("SELECT story_id, title, url, domain, by_user, score, num_comments, bookmarked_at FROM bookmarks")
	if err == nil {
		defer rows.Close()
		for rows.Next() {
			var b Bookmark
			var ts string
			if rows.Scan(&b.Story.ID, &b.Story.Title, &b.Story.URL, &b.Story.Domain, &b.Story.By, &b.Story.Score, &b.Story.Comments, &ts) == nil {
				b.BookmarkedAt, _ = time.Parse(time.RFC3339, ts)
				bm[b.Story.ID] = b
			}
		}
	}
	return bm
}

func addBookmark(b Bookmark) {
	if blocksDB == nil {
		return
	}
	blocksDB.Exec(`INSERT OR REPLACE INTO bookmarks (story_id, title, url, domain, by_user, score, num_comments, bookmarked_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		b.Story.ID, b.Story.Title, b.Story.URL, b.Story.Domain, b.Story.By, b.Story.Score, b.Story.Comments, b.BookmarkedAt.Format(time.RFC3339))
}

func removeBookmark(storyID int) {
	if blocksDB == nil {
		return
	}
	blocksDB.Exec("DELETE FROM bookmarks WHERE story_id = ?", storyID)
}