package db_test

import (
	"context"
	"database/sql"
	"os"
	"path/filepath"
	"runtime"
	"strings"
	"testing"

	"update_server/internal/db"
)

func TestMigrateAppliesCoreSchema(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	sqlitePath := filepath.Join(t.TempDir(), "update-server.sqlite")

	database, err := db.Open(ctx, sqlitePath)
	if err != nil {
		t.Fatalf("open database: %v", err)
	}
	defer database.Close()

	migrationsDir := projectMigrationsDir(t)
	if err := db.Migrate(ctx, database, migrationsDir); err != nil {
		t.Fatalf("apply migrations: %v", err)
	}

	if err := db.Migrate(ctx, database, migrationsDir); err != nil {
		t.Fatalf("reapply migrations: %v", err)
	}

	expectedTables := []string{
		"schema_migrations",
		"users",
		"projects",
		"tags",
		"project_tags",
		"releases",
		"api_keys",
		"api_key_project_access",
		"api_key_tag_access",
		"sessions",
		"audit_logs",
	}

	for _, tableName := range expectedTables {
		if !tableExists(t, database, tableName) {
			t.Fatalf("expected table %q to exist", tableName)
		}
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO users (email, password_hash, role) VALUES (?, ?, ?)`,
		"admin@example.com",
		"hashed-password",
		"admin",
	); err != nil {
		t.Fatalf("insert user: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO projects (name, slug, description) VALUES (?, ?, ?)`,
		"Desktop App",
		"desktop-app",
		"Primary desktop client",
	); err != nil {
		t.Fatalf("insert project: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO tags (name, slug, description) VALUES (?, ?, ?)`,
		"Windows",
		"windows",
		"Windows releases",
	); err != nil {
		t.Fatalf("insert tag: %v", err)
	}

	var projectID int64
	if err := database.QueryRowContext(ctx, `SELECT id FROM projects WHERE slug = ?`, "desktop-app").Scan(&projectID); err != nil {
		t.Fatalf("load project id: %v", err)
	}

	var tagID int64
	if err := database.QueryRowContext(ctx, `SELECT id FROM tags WHERE slug = ?`, "windows").Scan(&tagID); err != nil {
		t.Fatalf("load tag id: %v", err)
	}

	var userID int64
	if err := database.QueryRowContext(ctx, `SELECT id FROM users WHERE email = ?`, "admin@example.com").Scan(&userID); err != nil {
		t.Fatalf("load user id: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO project_tags (project_id, tag_id) VALUES (?, ?)`,
		projectID,
		tagID,
	); err != nil {
		t.Fatalf("insert project tag: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO releases (project_id, version, build, filename, storage_path, checksum_sha256, size_bytes, content_type, release_notes, uploaded_by_user_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		projectID,
		"1.0.0",
		"",
		"desktop-app-1.0.0.zip",
		"artifacts/desktop-app/1.0.0/desktop-app-1.0.0.zip",
		"0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef",
		1024,
		"application/zip",
		"Initial release",
		userID,
	); err != nil {
		t.Fatalf("insert release: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO api_keys (name, key_prefix, key_hash, description, scope_mode, can_download, created_by_user_id) VALUES (?, ?, ?, ?, ?, ?, ?)`,
		"Desktop Clients",
		"updsrv_project",
		"hash-project",
		"Project-scoped desktop client access",
		"project_allow_list",
		1,
		userID,
	); err != nil {
		t.Fatalf("insert project api key: %v", err)
	}

	var projectAPIKeyID int64
	if err := database.QueryRowContext(ctx, `SELECT id FROM api_keys WHERE key_prefix = ?`, "updsrv_project").Scan(&projectAPIKeyID); err != nil {
		t.Fatalf("load project api key id: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO api_key_project_access (api_key_id, project_id) VALUES (?, ?)`,
		projectAPIKeyID,
		projectID,
	); err != nil {
		t.Fatalf("insert api key project access: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO api_keys (name, key_prefix, key_hash, description, scope_mode, can_download, created_by_user_id) VALUES (?, ?, ?, ?, ?, ?, ?)`,
		"Tagged Clients",
		"updsrv_tag",
		"hash-tag",
		"Tag-scoped desktop client access",
		"tag_allow_list",
		1,
		userID,
	); err != nil {
		t.Fatalf("insert tag api key: %v", err)
	}

	var tagAPIKeyID int64
	if err := database.QueryRowContext(ctx, `SELECT id FROM api_keys WHERE key_prefix = ?`, "updsrv_tag").Scan(&tagAPIKeyID); err != nil {
		t.Fatalf("load tag api key id: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO api_key_tag_access (api_key_id, tag_id) VALUES (?, ?)`,
		tagAPIKeyID,
		tagID,
	); err != nil {
		t.Fatalf("insert api key tag access: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO sessions (user_id, token_hash, expires_at, ip_address, user_agent) VALUES (?, ?, ?, ?, ?)`,
		userID,
		"session-hash",
		"2030-01-01T00:00:00Z",
		"127.0.0.1",
		"test-agent",
	); err != nil {
		t.Fatalf("insert session: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO audit_logs (actor_user_id, api_key_id, action, target_type, target_id, target_identifier, metadata_json, ip_address) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		userID,
		tagAPIKeyID,
		"api_key.created",
		"api_key",
		tagAPIKeyID,
		"updsrv_tag",
		`{"source":"test"}`,
		"127.0.0.1",
	); err != nil {
		t.Fatalf("insert audit log: %v", err)
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO api_key_project_access (api_key_id, project_id) VALUES (?, ?)`,
		tagAPIKeyID,
		projectID,
	); err == nil {
		t.Fatal("expected project access insert for tag-scoped key to fail")
	}

	if _, err := database.ExecContext(
		ctx,
		`INSERT INTO api_keys (name, key_prefix, key_hash, scope_mode) VALUES (?, ?, ?, ?)`,
		"Broken Key",
		"updsrv_invalid",
		"hash-invalid",
		"invalid_scope",
	); err == nil {
		t.Fatal("expected invalid scope_mode insert to fail")
	}
}

func projectMigrationsDir(t *testing.T) string {
	t.Helper()

	_, filename, _, ok := runtime.Caller(0)
	if !ok {
		t.Fatal("resolve caller path")
	}

	return filepath.Join(filepath.Dir(filename), "..", "..", "migrations")
}

func tableExists(t *testing.T, database *sql.DB, tableName string) bool {
	t.Helper()

	var exists int
	query := `SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?)`
	if err := database.QueryRowContext(context.Background(), query, tableName).Scan(&exists); err != nil {
		t.Fatalf("check table %s: %v", tableName, err)
	}

	return exists == 1
}

func TestMigrateRejectsEditedAppliedMigrations(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	tempDir := t.TempDir()
	sqlitePath := filepath.Join(tempDir, "update-server.sqlite")

	database, err := db.Open(ctx, sqlitePath)
	if err != nil {
		t.Fatalf("open database: %v", err)
	}
	defer database.Close()

	migrationsDir := filepath.Join(tempDir, "migrations")
	if err := os.MkdirAll(migrationsDir, 0o755); err != nil {
		t.Fatalf("create migrations dir: %v", err)
	}

	firstMigrationPath := filepath.Join(migrationsDir, "0001_test.sql")
	if err := os.WriteFile(firstMigrationPath, []byte(`CREATE TABLE sample (id INTEGER PRIMARY KEY);`), 0o644); err != nil {
		t.Fatalf("write migration: %v", err)
	}

	if err := db.Migrate(ctx, database, migrationsDir); err != nil {
		t.Fatalf("apply migration: %v", err)
	}

	if err := os.WriteFile(firstMigrationPath, []byte(`CREATE TABLE sample (id INTEGER PRIMARY KEY, name TEXT);`), 0o644); err != nil {
		t.Fatalf("rewrite migration: %v", err)
	}

	err = db.Migrate(ctx, database, migrationsDir)
	if err == nil {
		t.Fatal("expected checksum mismatch error")
	}

	expectedMessage := "checksum mismatch"
	if !strings.Contains(err.Error(), expectedMessage) {
		t.Fatalf("expected error containing %q, got %v", expectedMessage, err)
	}
}