1package db
  2
  3import (
  4	"context"
  5	"database/sql"
  6	"fmt"
  7	"time"
  8
  9	_ "modernc.org/sqlite"
 10)
 11
 12// DB wraps a *sql.DB connection to a SQLite database.
 13type DB struct {
 14	db *sql.DB
 15}
 16
 17// Open opens (or creates) a SQLite database at the given path.
 18func Open(path string) (*DB, error) {
 19	db, err := sql.Open("sqlite", path)
 20	if err != nil {
 21		return nil, fmt.Errorf("opening database: %w", err)
 22	}
 23
 24	// SQLite is safe with a single connection for reads and serialises writes.
 25	db.SetMaxOpenConns(1)
 26	db.SetMaxIdleConns(1)
 27	db.SetConnMaxLifetime(0)
 28
 29	return &DB{db: db}, nil
 30}
 31
 32// Close closes the database connection.
 33func (d *DB) Close() error {
 34	return d.db.Close()
 35}
 36
 37// DB returns the underlying *sql.DB for use in query methods.
 38func (d *DB) DB() *sql.DB {
 39	return d.db
 40}
 41
 42// Migrate runs all pending schema migrations.
 43func (d *DB) Migrate(ctx context.Context) error {
 44	_, err := d.db.ExecContext(ctx, `
 45		CREATE TABLE IF NOT EXISTS schema_migrations (
 46			version INTEGER PRIMARY KEY,
 47			applied_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
 48		)
 49	`)
 50	if err != nil {
 51		return fmt.Errorf("creating migrations table: %w", err)
 52	}
 53
 54	migrations := []struct {
 55		version int
 56		sql     string
 57	}{
 58		{
 59			version: 1,
 60			sql: `
 61				CREATE TABLE IF NOT EXISTS users (
 62					id            INTEGER PRIMARY KEY AUTOINCREMENT,
 63					username      TEXT    UNIQUE NOT NULL,
 64					password_hash TEXT    NOT NULL,
 65					is_admin      INTEGER DEFAULT 0,
 66					created_at    TEXT    NOT NULL,
 67					updated_at    TEXT    NOT NULL
 68				)
 69			`,
 70		},
 71		{
 72			version: 2,
 73			sql: `
 74				CREATE TABLE IF NOT EXISTS sessions (
 75					id         INTEGER PRIMARY KEY AUTOINCREMENT,
 76					user_id    INTEGER NOT NULL REFERENCES users(id),
 77					token      TEXT    UNIQUE NOT NULL,
 78					expires_at TEXT    NOT NULL,
 79					created_at TEXT    NOT NULL
 80				)
 81			`,
 82		},
 83		{
 84			version: 3,
 85			sql: `
 86				CREATE TABLE IF NOT EXISTS images (
 87					id             INTEGER PRIMARY KEY AUTOINCREMENT,
 88					filename       TEXT    NOT NULL,
 89					storage_path   TEXT    NOT NULL,
 90					thumbnail_path TEXT,
 91					uploaded_by    INTEGER NOT NULL REFERENCES users(id),
 92					file_size      INTEGER NOT NULL,
 93					width          INTEGER NOT NULL,
 94					height         INTEGER NOT NULL,
 95					mime_type      TEXT    NOT NULL,
 96					created_at     TEXT    NOT NULL,
 97					updated_at     TEXT    NOT NULL
 98				)
 99			`,
100		},
101		{
102			version: 4,
103			sql: `
104				CREATE TABLE IF NOT EXISTS image_tags (
105					image_id INTEGER NOT NULL REFERENCES images(id) ON DELETE CASCADE,
106					tag      TEXT    NOT NULL,
107					PRIMARY KEY (image_id, tag)
108				)
109			`,
110		},
111		{
112			version: 5,
113			sql: `
114				CREATE TABLE IF NOT EXISTS tags (
115					tag         TEXT PRIMARY KEY,
116					usage_count INTEGER DEFAULT 0
117				)
118			`,
119		},
120		{
121			version: 6,
122			sql: `
123				CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
124				CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
125				CREATE INDEX IF NOT EXISTS idx_images_uploaded_by ON images(uploaded_by);
126				CREATE INDEX IF NOT EXISTS idx_images_created_at ON images(created_at);
127				CREATE INDEX IF NOT EXISTS idx_image_tags_tag ON image_tags(tag);
128			`,
129		},
130		{
131			version: 7,
132			sql: `
133				ALTER TABLE users ADD COLUMN avatar_path TEXT;
134			`,
135		},
136	}
137
138	for _, m := range migrations {
139		var count int
140		err := d.db.QueryRowContext(ctx,
141			"SELECT COUNT(*) FROM schema_migrations WHERE version = ?", m.version,
142		).Scan(&count)
143		if err != nil {
144			return fmt.Errorf("checking migration %d: %w", m.version, err)
145		}
146		if count > 0 {
147			continue
148		}
149
150		if _, err := d.db.ExecContext(ctx, m.sql); err != nil {
151			return fmt.Errorf("running migration %d: %w", m.version, err)
152		}
153
154		if _, err := d.db.ExecContext(ctx,
155			"INSERT INTO schema_migrations (version) VALUES (?)", m.version,
156		); err != nil {
157			return fmt.Errorf("recording migration %d: %w", m.version, err)
158		}
159	}
160
161	return nil
162}
163
164// now returns the current UTC time formatted as ISO 8601 (RFC 3339).
165func now() string {
166	return time.Now().UTC().Format(time.RFC3339)
167}
168
169// scanTime parses an ISO 8601 string into time.Time.
170func scanTime(s string) (time.Time, error) {
171	return time.Parse(time.RFC3339, s)
172}