package database

import (
	"database/sql"
	"path/filepath"
	"testing"

	"vidarchive/internal/config"
)

func newTestDB(t *testing.T) *sql.DB {
	t.Helper()
	db, err := New(&config.Config{DBPath: filepath.Join(t.TempDir(), "test.db")})
	if err != nil {
		t.Fatalf("New: %v", err)
	}
	t.Cleanup(func() { db.Close() })
	return db
}

func TestMigrateIsIdempotent(t *testing.T) {
	path := filepath.Join(t.TempDir(), "test.db")

	db, err := New(&config.Config{DBPath: path})
	if err != nil {
		t.Fatalf("first migrate: %v", err)
	}
	var first int
	if err := db.QueryRow(`SELECT MAX(version) FROM schema_migrations`).Scan(&first); err != nil {
		t.Fatalf("read version: %v", err)
	}
	db.Close()

	db, err = New(&config.Config{DBPath: path})
	if err != nil {
		t.Fatalf("second migrate: %v", err)
	}
	defer db.Close()

	var second int
	if err := db.QueryRow(`SELECT MAX(version) FROM schema_migrations`).Scan(&second); err != nil {
		t.Fatalf("read version: %v", err)
	}
	if first != second {
		t.Errorf("version changed on re-open: %d -> %d", first, second)
	}
}

func TestForeignKeysAreEnforced(t *testing.T) {
	db := newTestDB(t)

	var on int
	if err := db.QueryRow(`PRAGMA foreign_keys`).Scan(&on); err != nil {
		t.Fatalf("read pragma: %v", err)
	}
	if on != 1 {
		t.Fatal("foreign_keys pragma is off; declared constraints would be ignored")
	}

	if _, err := db.Exec(`INSERT INTO downloads (url, preset_id) VALUES ('u', 99999)`); err == nil {
		t.Error("expected insert with a nonexistent preset_id to be rejected")
	}
}

// Deleting a preset must not fail just because downloads reference it: the
// reference is nulled and ExecuteDownload falls back to the default preset.
func TestDeletingPresetNullsReferences(t *testing.T) {
	db := newTestDB(t)

	res, err := db.Exec(`INSERT INTO presets (name) VALUES ('temp')`)
	if err != nil {
		t.Fatalf("insert preset: %v", err)
	}
	presetID, err := res.LastInsertId()
	if err != nil {
		t.Fatal(err)
	}

	if _, err := db.Exec(`INSERT INTO downloads (url, preset_id) VALUES ('u', ?)`, presetID); err != nil {
		t.Fatalf("insert download: %v", err)
	}

	if _, err := db.Exec(`DELETE FROM presets WHERE id = ?`, presetID); err != nil {
		t.Fatalf("delete preset: %v", err)
	}

	var nulls int
	if err := db.QueryRow(`SELECT COUNT(*) FROM downloads WHERE preset_id IS NULL`).Scan(&nulls); err != nil {
		t.Fatal(err)
	}
	if nulls != 1 {
		t.Errorf("preset_id not nulled after preset delete: got %d rows with NULL", nulls)
	}
}

func TestExpectedIndexesExist(t *testing.T) {
	db := newTestDB(t)

	want := []string{
		"idx_downloads_status",
		"idx_downloads_subscription_id",
		"idx_subscriptions_due",
	}
	for _, name := range want {
		var got string
		err := db.QueryRow(`SELECT name FROM sqlite_master WHERE type='index' AND name = ?`, name).Scan(&got)
		if err != nil {
			t.Errorf("missing index %s: %v", name, err)
		}
	}
}