package db

import (
	"path/filepath"
	"testing"
)

func TestOpenAppliesMigrations(t *testing.T) {
	database, err := Open(filepath.Join(t.TempDir(), "test.db"))
	if err != nil {
		t.Fatalf("Open: %v", err)
	}
	defer database.Close()

	for _, table := range []string{"users", "sessions", "repos", "tokens", "issues", "issue_comments", "pulls", "pull_comments", "releases", "release_assets", "schema_migrations"} {
		var name string
		err := database.QueryRow(
			`SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?`, table,
		).Scan(&name)
		if err != nil {
			t.Errorf("table %s missing: %v", table, err)
		}
	}

	var applied int
	if err := database.QueryRow(`SELECT COUNT(*) FROM schema_migrations`).Scan(&applied); err != nil {
		t.Fatalf("count migrations: %v", err)
	}
	if applied != 5 {
		t.Errorf("applied migrations = %d, want 5", applied)
	}
}

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

	first, err := Open(path)
	if err != nil {
		t.Fatalf("first Open: %v", err)
	}
	first.Close()

	second, err := Open(path)
	if err != nil {
		t.Fatalf("second Open: %v", err)
	}
	defer second.Close()

	var applied int
	if err := second.QueryRow(`SELECT COUNT(*) FROM schema_migrations`).Scan(&applied); err != nil {
		t.Fatalf("count migrations: %v", err)
	}
	if applied != 5 {
		t.Errorf("applied migrations after reopen = %d, want 5", applied)
	}
}

func TestSchemaAcceptsRows(t *testing.T) {
	database, err := Open(filepath.Join(t.TempDir(), "test.db"))
	if err != nil {
		t.Fatalf("Open: %v", err)
	}
	defer database.Close()

	res, err := database.Exec(
		`INSERT INTO users (username, password_hash) VALUES (?, ?)`, "josie", "x",
	)
	if err != nil {
		t.Fatalf("insert user: %v", err)
	}
	userID, err := res.LastInsertId()
	if err != nil {
		t.Fatalf("last insert id: %v", err)
	}

	if _, err := database.Exec(
		`INSERT INTO repos (owner_id, name) VALUES (?, ?)`, userID, "simplegit",
	); err != nil {
		t.Fatalf("insert repo: %v", err)
	}

	var visibility, branch string
	var createdAt int64
	if err := database.QueryRow(
		`SELECT visibility, default_branch, created_at FROM repos WHERE owner_id = ? AND name = ?`,
		userID, "simplegit",
	).Scan(&visibility, &branch, &createdAt); err != nil {
		t.Fatalf("select repo: %v", err)
	}
	if visibility != "private" {
		t.Errorf("default visibility = %q, want private", visibility)
	}
	if branch != "main" {
		t.Errorf("default branch = %q, want main", branch)
	}
	if createdAt <= 0 {
		t.Errorf("created_at = %d, want > 0", createdAt)
	}
}

func TestForeignKeysEnforced(t *testing.T) {
	database, err := Open(filepath.Join(t.TempDir(), "test.db"))
	if err != nil {
		t.Fatalf("Open: %v", err)
	}
	defer database.Close()

	var enabled int
	if err := database.QueryRow(`PRAGMA foreign_keys`).Scan(&enabled); err != nil {
		t.Fatalf("pragma foreign_keys: %v", err)
	}
	if enabled != 1 {
		t.Fatalf("foreign_keys = %d, want 1", enabled)
	}

	if _, err := database.Exec(
		`INSERT INTO repos (owner_id, name) VALUES (?, ?)`, 999, "orphan",
	); err == nil {
		t.Error("insert with missing owner_id succeeded, want foreign key error")
	}
}