package db

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	_ "modernc.org/sqlite"
)

// DB wraps a *sql.DB connection to a SQLite database.
type DB struct {
	db *sql.DB
}

// Open opens (or creates) a SQLite database at the given path.
func Open(path string) (*DB, error) {
	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}

	// SQLite is safe with a single connection for reads and serialises writes.
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)
	db.SetConnMaxLifetime(0)

	return &DB{db: db}, nil
}

// Close closes the database connection.
func (d *DB) Close() error {
	return d.db.Close()
}

// DB returns the underlying *sql.DB for use in query methods.
func (d *DB) DB() *sql.DB {
	return d.db
}

// Migrate runs all pending schema migrations.
func (d *DB) Migrate(ctx context.Context) error {
	_, err := d.db.ExecContext(ctx, `
		CREATE TABLE IF NOT EXISTS schema_migrations (
			version INTEGER PRIMARY KEY,
			applied_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
		)
	`)
	if err != nil {
		return fmt.Errorf("creating migrations table: %w", err)
	}

	migrations := []struct {
		version int
		sql     string
	}{
		{
			version: 1,
			sql: `
				CREATE TABLE IF NOT EXISTS users (
					id            INTEGER PRIMARY KEY AUTOINCREMENT,
					username      TEXT    UNIQUE NOT NULL,
					password_hash TEXT    NOT NULL,
					is_admin      INTEGER DEFAULT 0,
					created_at    TEXT    NOT NULL,
					updated_at    TEXT    NOT NULL
				)
			`,
		},
		{
			version: 2,
			sql: `
				CREATE TABLE IF NOT EXISTS sessions (
					id         INTEGER PRIMARY KEY AUTOINCREMENT,
					user_id    INTEGER NOT NULL REFERENCES users(id),
					token      TEXT    UNIQUE NOT NULL,
					expires_at TEXT    NOT NULL,
					created_at TEXT    NOT NULL
				)
			`,
		},
		{
			version: 3,
			sql: `
				CREATE TABLE IF NOT EXISTS images (
					id             INTEGER PRIMARY KEY AUTOINCREMENT,
					filename       TEXT    NOT NULL,
					storage_path   TEXT    NOT NULL,
					thumbnail_path TEXT,
					uploaded_by    INTEGER NOT NULL REFERENCES users(id),
					file_size      INTEGER NOT NULL,
					width          INTEGER NOT NULL,
					height         INTEGER NOT NULL,
					mime_type      TEXT    NOT NULL,
					created_at     TEXT    NOT NULL,
					updated_at     TEXT    NOT NULL
				)
			`,
		},
		{
			version: 4,
			sql: `
				CREATE TABLE IF NOT EXISTS image_tags (
					image_id INTEGER NOT NULL REFERENCES images(id) ON DELETE CASCADE,
					tag      TEXT    NOT NULL,
					PRIMARY KEY (image_id, tag)
				)
			`,
		},
		{
			version: 5,
			sql: `
				CREATE TABLE IF NOT EXISTS tags (
					tag         TEXT PRIMARY KEY,
					usage_count INTEGER DEFAULT 0
				)
			`,
		},
		{
			version: 6,
			sql: `
				CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
				CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
				CREATE INDEX IF NOT EXISTS idx_images_uploaded_by ON images(uploaded_by);
				CREATE INDEX IF NOT EXISTS idx_images_created_at ON images(created_at);
				CREATE INDEX IF NOT EXISTS idx_image_tags_tag ON image_tags(tag);
			`,
		},
		{
			version: 7,
			sql: `
				ALTER TABLE users ADD COLUMN avatar_path TEXT;
			`,
		},
	}

	for _, m := range migrations {
		var count int
		err := d.db.QueryRowContext(ctx,
			"SELECT COUNT(*) FROM schema_migrations WHERE version = ?", m.version,
		).Scan(&count)
		if err != nil {
			return fmt.Errorf("checking migration %d: %w", m.version, err)
		}
		if count > 0 {
			continue
		}

		if _, err := d.db.ExecContext(ctx, m.sql); err != nil {
			return fmt.Errorf("running migration %d: %w", m.version, err)
		}

		if _, err := d.db.ExecContext(ctx,
			"INSERT INTO schema_migrations (version) VALUES (?)", m.version,
		); err != nil {
			return fmt.Errorf("recording migration %d: %w", m.version, err)
		}
	}

	return nil
}

// now returns the current UTC time formatted as ISO 8601 (RFC 3339).
func now() string {
	return time.Now().UTC().Format(time.RFC3339)
}

// scanTime parses an ISO 8601 string into time.Time.
func scanTime(s string) (time.Time, error) {
	return time.Parse(time.RFC3339, s)
}