package store

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

	_ "github.com/mattn/go-sqlite3"
	"task-dashboard/internal/models"
)

func TestRunMigrations_LegacyDB_SeedsTrackingTable(t *testing.T) {
	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")
	migDir := filepath.Join(tempDir, "migrations")
	if err := os.MkdirAll(migDir, 0755); err != nil {
		t.Fatal(err)
	}

	// Simulate a legacy DB: tables exist but no schema_migrations
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := db.Exec("CREATE TABLE legacy_items (id INTEGER PRIMARY KEY); ALTER TABLE legacy_items ADD COLUMN created_at DATETIME;"); err != nil {
		t.Fatal(err)
	}
	db.Close()

	migration := []byte("CREATE TABLE legacy_items (id INTEGER PRIMARY KEY);\nALTER TABLE legacy_items ADD COLUMN created_at DATETIME;\n")
	if err := os.WriteFile(filepath.Join(migDir, "001_test.sql"), migration, 0644); err != nil {
		t.Fatal(err)
	}

	// Should not fail even though migration SQL would fail if re-run
	store, err := New(dbPath, migDir)
	if err != nil {
		t.Fatalf("New() on legacy DB failed: %v", err)
	}
	defer store.Close()

	// Confirm migration was recorded as applied
	var count int
	store.db.QueryRow("SELECT COUNT(*) FROM schema_migrations WHERE filename = '001_test.sql'").Scan(&count)
	if count != 1 {
		t.Errorf("expected migration to be seeded in schema_migrations, got count=%d", count)
	}
}

func TestRunMigrations_IdempotentOnRestart(t *testing.T) {
	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")
	migDir := filepath.Join(tempDir, "migrations")
	if err := os.MkdirAll(migDir, 0755); err != nil {
		t.Fatal(err)
	}

	// Write a migration with a non-idempotent ALTER TABLE
	migration := []byte("CREATE TABLE foo (id INTEGER PRIMARY KEY);\nALTER TABLE foo ADD COLUMN bar TEXT;\n")
	if err := os.WriteFile(filepath.Join(migDir, "001_test.sql"), migration, 0644); err != nil {
		t.Fatal(err)
	}

	// First run — should succeed
	store1, err := New(dbPath, migDir)
	if err != nil {
		t.Fatalf("first New() failed: %v", err)
	}
	store1.Close()

	// Second run (simulating service restart) — should also succeed
	store2, err := New(dbPath, migDir)
	if err != nil {
		t.Fatalf("second New() failed (migration re-run not skipped): %v", err)
	}
	store2.Close()
}

// setupTestStoreWithCards creates a test store with boards and cards tables
func setupTestStoreWithCards(t *testing.T) *Store {
	t.Helper()

	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		t.Fatalf("Failed to open test database: %v", err)
	}

	db.SetMaxOpenConns(1)

	store := &Store{db: db}

	schema := `
		CREATE TABLE IF NOT EXISTS boards (
			id TEXT PRIMARY KEY,
			name TEXT NOT NULL,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		);
		CREATE TABLE IF NOT EXISTS cards (
			id TEXT PRIMARY KEY,
			name TEXT NOT NULL,
			description TEXT DEFAULT '',
			board_id TEXT NOT NULL,
			list_id TEXT,
			list_name TEXT,
			due_date DATETIME,
			url TEXT,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		);
	`
	if _, err := db.Exec(schema); err != nil {
		t.Fatalf("Failed to create schema: %v", err)
	}

	return store
}

func setupTestStoreWithGoogleTasks(t *testing.T) *Store {
	t.Helper()

	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		t.Fatalf("Failed to open test database: %v", err)
	}
	db.SetMaxOpenConns(1)

	store := &Store{db: db}

	schema := `
		CREATE TABLE IF NOT EXISTS google_tasks (
			id TEXT PRIMARY KEY,
			title TEXT NOT NULL,
			notes TEXT,
			status TEXT NOT NULL,
			completed BOOLEAN NOT NULL DEFAULT 0,
			due_date TEXT,
			updated_at TEXT,
			list_id TEXT NOT NULL,
			url TEXT
		);
	`
	if _, err := db.Exec(schema); err != nil {
		t.Fatalf("Failed to create schema: %v", err)
	}

	return store
}

func setupTestStoreWithNativeTasks(t *testing.T) *Store {
	t.Helper()

	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		t.Fatalf("Failed to open test database: %v", err)
	}
	db.SetMaxOpenConns(1)

	store := &Store{db: db}

	schema := `
		CREATE TABLE IF NOT EXISTS native_tasks (
			id TEXT PRIMARY KEY,
			content TEXT NOT NULL,
			description TEXT DEFAULT '',
			project_name TEXT DEFAULT '',
			project_id TEXT DEFAULT '',
			due_date DATETIME,
			priority INTEGER DEFAULT 1,
			completed BOOLEAN DEFAULT 0,
			labels TEXT DEFAULT '[]',
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			recurrence_freq TEXT DEFAULT '',
			recurrence_interval INTEGER DEFAULT 1,
			recurrence_weekdays TEXT DEFAULT '',
			recurrence_series_id TEXT DEFAULT '',
			next_occurrence_override TEXT DEFAULT '',
			estimated_minutes INTEGER DEFAULT 0
		);
	`
	if _, err := db.Exec(schema); err != nil {
		t.Fatalf("Failed to create schema: %v", err)
	}

	return store
}

// TestGetNativeTasksByDateRange_ExcludesOverdue documents the deliberate contract after
// 2026-07-13's reconciliation: GetNativeTasksByDateRange is scoped to [start, end) only.
// Overdue tasks (due before start) are BuildTimeline's job to fetch separately via
// GetOverdueNativeTasks -- see that test below and timeline_logic.go's "6." section --
// so this function must NOT also return them, or BuildTimeline would double them up.
func TestGetNativeTasksByDateRange_ExcludesOverdue(t *testing.T) {
	store := setupTestStoreWithNativeTasks(t)

	now := time.Now()
	overdue := now.Add(-48 * time.Hour)
	today := now
	future := now.Add(72 * time.Hour) // outside the window

	for _, task := range []models.Task{
		{ID: "t-overdue", Content: "Overdue task", DueDate: &overdue},
		{ID: "t-today", Content: "Today task", DueDate: &today},
		{ID: "t-future", Content: "Future task", DueDate: &future},
	} {
		if err := store.CreateNativeTask(task); err != nil {
			t.Fatalf("CreateNativeTask(%s) failed: %v", task.ID, err)
		}
	}

	start := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
	end := start.Add(48 * time.Hour)

	results, err := store.GetNativeTasksByDateRange(start, end)
	if err != nil {
		t.Fatalf("GetNativeTasksByDateRange failed: %v", err)
	}

	ids := make(map[string]bool)
	for _, r := range results {
		ids[r.ID] = true
	}
	if ids["t-overdue"] {
		t.Error("expected overdue task to be excluded from the ranged fetch")
	}
	if !ids["t-today"] {
		t.Error("expected today's task to be included")
	}
	if ids["t-future"] {
		t.Error("expected far-future task to be excluded")
	}
}

// TestGetOverdueNativeTasks_IncludesOnlyPastDue is the store-level counterpart to
// TestGetNativeTasksByDateRange_ExcludesOverdue: this is the function BuildTimeline relies on
// to actually surface overdue tasks (see timeline_logic.go's "6." section and
// TestBuildTimeline_IncludesOverdueNativeTasks for the integration-level proof).
func TestGetOverdueNativeTasks_IncludesOnlyPastDue(t *testing.T) {
	store := setupTestStoreWithNativeTasks(t)

	now := time.Now()
	overdue := now.Add(-48 * time.Hour)
	today := now

	for _, task := range []models.Task{
		{ID: "t-overdue", Content: "Overdue task", DueDate: &overdue},
		{ID: "t-today", Content: "Today task", DueDate: &today},
	} {
		if err := store.CreateNativeTask(task); err != nil {
			t.Fatalf("CreateNativeTask(%s) failed: %v", task.ID, err)
		}
	}

	start := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())

	results, err := store.GetOverdueNativeTasks(start)
	if err != nil {
		t.Fatalf("GetOverdueNativeTasks failed: %v", err)
	}
	if len(results) != 1 || results[0].ID != "t-overdue" {
		t.Errorf("expected only the overdue task, got %+v", results)
	}
}

// TestSaveAndGetGoogleTasks_RoundTripsTimestamps guards against a regression where
// due_date/updated_at (TEXT columns, not DATETIME) failed to scan back into time.Time
// via sql.NullTime whenever a row had a non-null timestamp.
func TestSaveAndGetGoogleTasks_RoundTripsTimestamps(t *testing.T) {
	store := setupTestStoreWithGoogleTasks(t)

	due := time.Date(2026, 8, 1, 0, 0, 0, 0, time.UTC)
	updated := time.Now()

	err := store.SaveGoogleTasks([]models.GoogleTask{
		{ID: "g1", Title: "Renew passport", Notes: "bring photo", Status: "needsAction", ListID: "list-a", DueDate: &due, UpdatedAt: updated},
	})
	if err != nil {
		t.Fatalf("SaveGoogleTasks failed: %v", err)
	}

	tasks, err := store.GetGoogleTasks()
	if err != nil {
		t.Fatalf("GetGoogleTasks failed: %v", err)
	}
	if len(tasks) != 1 {
		t.Fatalf("expected 1 task, got %d", len(tasks))
	}
	got := tasks[0]
	if got.DueDate == nil || !got.DueDate.Equal(due) {
		t.Errorf("DueDate: got %v, want %v", got.DueDate, due)
	}
	if got.UpdatedAt.IsZero() {
		t.Error("UpdatedAt should have round-tripped, got zero value")
	}
}

// TestGetGoogleTasksByDateRange_IncludesOverdue guards against a regression where a task due
// before the window's start (e.g. yesterday, still incomplete) silently dropped out of the
// widget/timeline the moment the day rolled over, because the query required due_date >= start.
func TestGetGoogleTasksByDateRange_IncludesOverdue(t *testing.T) {
	store := setupTestStoreWithGoogleTasks(t)

	now := time.Now()
	overdue := now.Add(-48 * time.Hour)
	today := now
	future := now.Add(72 * time.Hour) // outside the window

	err := store.SaveGoogleTasks([]models.GoogleTask{
		{ID: "g-overdue", Title: "Overdue task", ListID: "list-a", DueDate: &overdue, UpdatedAt: now},
		{ID: "g-today", Title: "Today task", ListID: "list-a", DueDate: &today, UpdatedAt: now},
		{ID: "g-future", Title: "Future task", ListID: "list-a", DueDate: &future, UpdatedAt: now},
	})
	if err != nil {
		t.Fatalf("SaveGoogleTasks failed: %v", err)
	}

	start := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
	end := start.Add(48 * time.Hour)

	results, err := store.GetGoogleTasksByDateRange(start, end)
	if err != nil {
		t.Fatalf("GetGoogleTasksByDateRange failed: %v", err)
	}

	ids := make(map[string]bool)
	for _, r := range results {
		ids[r.ID] = true
	}
	if !ids["g-overdue"] {
		t.Error("expected overdue task to be included, but it was excluded")
	}
	if !ids["g-today"] {
		t.Error("expected today's task to be included")
	}
	if ids["g-future"] {
		t.Error("expected far-future task to be excluded")
	}
}

// TestDeleteCard verifies that DeleteCard removes a card from the cache
func TestDeleteCard(t *testing.T) {
	store := setupTestStoreWithCards(t)
	defer func() { _ = store.Close() }()

	// First create a board
	_, err := store.db.Exec(`INSERT INTO boards (id, name) VALUES (?, ?)`, "board1", "Test Board")
	if err != nil {
		t.Fatalf("Failed to create board: %v", err)
	}

	// Insert cards directly
	cards := []struct {
		id      string
		name    string
		boardID string
	}{
		{"card1", "Card 1", "board1"},
		{"card2", "Card 2", "board1"},
		{"card3", "Card 3", "board1"},
	}

	for _, card := range cards {
		_, err := store.db.Exec(
			`INSERT INTO cards (id, name, board_id, list_id, list_name) VALUES (?, ?, ?, ?, ?)`,
			card.id, card.name, card.boardID, "list1", "To Do",
		)
		if err != nil {
			t.Fatalf("Failed to insert card: %v", err)
		}
	}

	// Verify all 3 cards exist
	var count int
	err = store.db.QueryRow(`SELECT COUNT(*) FROM cards`).Scan(&count)
	if err != nil {
		t.Fatalf("Failed to count cards: %v", err)
	}
	if count != 3 {
		t.Fatalf("Expected 3 cards, got %d", count)
	}

	// Delete card2
	if err := store.DeleteCard("card2"); err != nil {
		t.Fatalf("DeleteCard failed: %v", err)
	}

	// Verify only 2 cards remain
	err = store.db.QueryRow(`SELECT COUNT(*) FROM cards`).Scan(&count)
	if err != nil {
		t.Fatalf("Failed to count cards after delete: %v", err)
	}
	if count != 2 {
		t.Errorf("Expected 2 cards after delete, got %d", count)
	}

	// Verify card2 is gone
	var exists int
	err = store.db.QueryRow(`SELECT COUNT(*) FROM cards WHERE id = ?`, "card2").Scan(&exists)
	if err != nil {
		t.Fatalf("Failed to check card2: %v", err)
	}
	if exists != 0 {
		t.Errorf("card2 should have been deleted")
	}
}

// TestDeleteCard_NonExistent verifies that deleting a non-existent card doesn't error
func TestDeleteCard_NonExistent(t *testing.T) {
	store := setupTestStoreWithCards(t)
	defer func() { _ = store.Close() }()

	// Delete a card that doesn't exist - should not error
	err := store.DeleteCard("nonexistent")
	if err != nil {
		t.Errorf("DeleteCard on non-existent card should not error, got: %v", err)
	}
}

// TestSaveAndGetBoards_MultipleBoards verifies that all boards and cards are
// correctly saved and retrieved. This tests the fix for slice reallocation bug
// where pointers in boardMap became stale when the boards slice grew.
func TestSaveAndGetBoards_MultipleBoards(t *testing.T) {
	store := setupTestStoreWithCards(t)
	defer func() { _ = store.Close() }()

	// Create multiple boards with varying numbers of cards
	// Use enough boards to trigger slice reallocation
	boards := []models.Board{
		{
			ID:   "board1",
			Name: "Board 1",
			Cards: []models.Card{
				{ID: "card1a", Name: "Card 1A", ListID: "list1", ListName: "To Do"},
				{ID: "card1b", Name: "Card 1B", ListID: "list1", ListName: "To Do"},
			},
		},
		{
			ID:   "board2",
			Name: "Board 2",
			Cards: []models.Card{
				{ID: "card2a", Name: "Card 2A", ListID: "list2", ListName: "Doing"},
				{ID: "card2b", Name: "Card 2B", ListID: "list2", ListName: "Doing"},
				{ID: "card2c", Name: "Card 2C", ListID: "list2", ListName: "Doing"},
			},
		},
		{
			ID:   "board3",
			Name: "Board 3",
			Cards: []models.Card{
				{ID: "card3a", Name: "Card 3A", ListID: "list3", ListName: "Done"},
			},
		},
		{
			ID:   "board4",
			Name: "Board 4",
			Cards: []models.Card{
				{ID: "card4a", Name: "Card 4A", ListID: "list4", ListName: "Backlog"},
				{ID: "card4b", Name: "Card 4B", ListID: "list4", ListName: "Backlog"},
			},
		},
		{
			ID:   "board5",
			Name: "Board 5",
			Cards: []models.Card{
				{ID: "card5a", Name: "Card 5A", ListID: "list5", ListName: "Ideas"},
				{ID: "card5b", Name: "Card 5B", ListID: "list5", ListName: "Ideas"},
				{ID: "card5c", Name: "Card 5C", ListID: "list5", ListName: "Ideas"},
				{ID: "card5d", Name: "Card 5D", ListID: "list5", ListName: "Ideas"},
			},
		},
	}

	// Calculate expected totals
	expectedBoards := len(boards)
	expectedCards := 0
	for _, b := range boards {
		expectedCards += len(b.Cards)
	}

	// Save boards
	if err := store.SaveBoards(boards); err != nil {
		t.Fatalf("SaveBoards failed: %v", err)
	}

	// Retrieve boards
	result, err := store.GetBoards()
	if err != nil {
		t.Fatalf("GetBoards failed: %v", err)
	}

	// Verify board count
	if len(result) != expectedBoards {
		t.Errorf("Expected %d boards, got %d", expectedBoards, len(result))
	}

	// Verify total card count
	totalCards := 0
	for _, b := range result {
		totalCards += len(b.Cards)
	}
	if totalCards != expectedCards {
		t.Errorf("Expected %d total cards, got %d", expectedCards, totalCards)
	}

	// Verify each board has correct card count
	expectedCardCounts := map[string]int{
		"board1": 2,
		"board2": 3,
		"board3": 1,
		"board4": 2,
		"board5": 4,
	}
	for _, b := range result {
		expected, ok := expectedCardCounts[b.ID]
		if !ok {
			t.Errorf("Unexpected board ID: %s", b.ID)
			continue
		}
		if len(b.Cards) != expected {
			t.Errorf("Board %s: expected %d cards, got %d", b.ID, expected, len(b.Cards))
		}
	}
}

// TestSaveAndGetBoards_ManyBoards verifies correct behavior with many boards
// to ensure slice reallocation is thoroughly tested
func TestSaveAndGetBoards_ManyBoards(t *testing.T) {
	store := setupTestStoreWithCards(t)
	defer func() { _ = store.Close() }()

	// Create 20 boards with 5 cards each = 100 cards total
	numBoards := 20
	cardsPerBoard := 5
	boards := make([]models.Board, numBoards)

	for i := 0; i < numBoards; i++ {
		boards[i] = models.Board{
			ID:    string(rune('A' + i)),
			Name:  "Board " + string(rune('A'+i)),
			Cards: make([]models.Card, cardsPerBoard),
		}
		for j := 0; j < cardsPerBoard; j++ {
			boards[i].Cards[j] = models.Card{
				ID:       string(rune('A'+i)) + "_card" + string(rune('0'+j)),
				Name:     "Card " + string(rune('0'+j)),
				ListID:   "list1",
				ListName: "To Do",
			}
		}
	}

	// Save boards
	if err := store.SaveBoards(boards); err != nil {
		t.Fatalf("SaveBoards failed: %v", err)
	}

	// Retrieve boards
	result, err := store.GetBoards()
	if err != nil {
		t.Fatalf("GetBoards failed: %v", err)
	}

	// Verify counts
	if len(result) != numBoards {
		t.Errorf("Expected %d boards, got %d", numBoards, len(result))
	}

	totalCards := 0
	for _, b := range result {
		totalCards += len(b.Cards)
		if len(b.Cards) != cardsPerBoard {
			t.Errorf("Board %s: expected %d cards, got %d", b.ID, cardsPerBoard, len(b.Cards))
		}
	}

	expectedTotal := numBoards * cardsPerBoard
	if totalCards != expectedTotal {
		t.Errorf("Expected %d total cards, got %d", expectedTotal, totalCards)
	}
}

func TestGetCardsByDateRange(t *testing.T) {
	store := setupTestStoreWithCards(t)
	defer func() { _ = store.Close() }()

	now := time.Now()
	tomorrow := now.Add(24 * time.Hour)

	// Create board
	_, err := store.db.Exec(`INSERT INTO boards (id, name) VALUES (?, ?)`, "board1", "Test Board")
	if err != nil {
		t.Fatalf("Failed to create board: %v", err)
	}

	// Create cards
	_, err = store.db.Exec(`
		INSERT INTO cards (id, name, board_id, list_id, list_name, due_date, url)
		VALUES
		(?, ?, ?, ?, ?, ?, ?),
		(?, ?, ?, ?, ?, ?, ?)
	`,
	"card1", "Card 1", "board1", "list1", "To Do", now, "https://trello.com/c/card1",
	"card2", "Card 2", "board1", "list1", "To Do", tomorrow, "https://trello.com/c/card2")
	if err != nil {
		t.Fatalf("Failed to insert cards: %v", err)
	}

	start := now.Add(-1 * time.Hour)
	end := now.Add(1 * time.Hour)

	results, err := store.GetCardsByDateRange(start, end)
	if err != nil {
		t.Fatalf("GetCardsByDateRange failed: %v", err)
	}

	if len(results) != 1 {
		t.Errorf("Expected 1 card, got %d", len(results))
	}
	if results[0].ID != "card1" {
		t.Errorf("Expected card1, got %s", results[0].ID)
	}
}

// =============================================================================
// User Shopping Items Tests
// =============================================================================

// =============================================================================
// Feature Toggles Tests
// =============================================================================

func setupTestStoreWithFeatureToggles(t *testing.T) *Store {
	t.Helper()

	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		t.Fatalf("Failed to open test database: %v", err)
	}

	db.SetMaxOpenConns(1)
	store := &Store{db: db}

	schema := `
		CREATE TABLE IF NOT EXISTS feature_toggles (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT UNIQUE NOT NULL,
			description TEXT,
			enabled BOOLEAN DEFAULT FALSE,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		);
	`
	if _, err := db.Exec(schema); err != nil {
		t.Fatalf("Failed to create schema: %v", err)
	}

	return store
}

func TestFeatureToggles_CRUD(t *testing.T) {
	store := setupTestStoreWithFeatureToggles(t)
	defer func() { _ = store.Close() }()

	// Create feature toggle
	if err := store.CreateFeatureToggle("new_feature", "A new feature", false); err != nil {
		t.Fatalf("Failed to create feature toggle: %v", err)
	}

	// Get all toggles
	toggles, err := store.GetFeatureToggles()
	if err != nil {
		t.Fatalf("Failed to get feature toggles: %v", err)
	}
	if len(toggles) != 1 {
		t.Errorf("Expected 1 toggle, got %d", len(toggles))
	}
	if toggles[0].Name != "new_feature" {
		t.Errorf("Expected name 'new_feature', got '%s'", toggles[0].Name)
	}
	if toggles[0].Enabled {
		t.Error("New feature should be disabled")
	}

	// Check if enabled
	if store.IsFeatureEnabled("new_feature") {
		t.Error("IsFeatureEnabled should return false for disabled feature")
	}

	// Enable feature
	if err := store.SetFeatureEnabled("new_feature", true); err != nil {
		t.Fatalf("Failed to enable feature: %v", err)
	}

	if !store.IsFeatureEnabled("new_feature") {
		t.Error("IsFeatureEnabled should return true after enabling")
	}

	// Delete feature
	if err := store.DeleteFeatureToggle("new_feature"); err != nil {
		t.Fatalf("Failed to delete feature toggle: %v", err)
	}

	toggles, _ = store.GetFeatureToggles()
	if len(toggles) != 0 {
		t.Errorf("Expected 0 toggles after delete, got %d", len(toggles))
	}
}

func TestIsFeatureEnabled_NonExistent(t *testing.T) {
	store := setupTestStoreWithFeatureToggles(t)
	defer func() { _ = store.Close() }()

	// Non-existent feature should return false
	if store.IsFeatureEnabled("does_not_exist") {
		t.Error("Non-existent feature should return false")
	}
}

// =============================================================================
// Completed Tasks Tests
// =============================================================================

func setupTestStoreWithCompletedTasks(t *testing.T) *Store {
	t.Helper()

	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		t.Fatalf("Failed to open test database: %v", err)
	}

	db.SetMaxOpenConns(1)
	store := &Store{db: db}

	schema := `
		CREATE TABLE IF NOT EXISTS completed_tasks (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			source TEXT NOT NULL,
			source_id TEXT NOT NULL,
			title TEXT NOT NULL,
			due_date TEXT,
			completed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			UNIQUE(source, source_id)
		);
	`
	if _, err := db.Exec(schema); err != nil {
		t.Fatalf("Failed to create schema: %v", err)
	}

	return store
}

func TestCompletedTasks_SaveAndGet(t *testing.T) {
	store := setupTestStoreWithCompletedTasks(t)
	defer func() { _ = store.Close() }()

	now := time.Now()

	// Save completed task with due date
	if err := store.SaveCompletedTask("doot", "task-123", "Buy groceries", &now); err != nil {
		t.Fatalf("Failed to save completed task: %v", err)
	}

	// Save completed task without due date
	if err := store.SaveCompletedTask("trello", "card-456", "Review PR", nil); err != nil {
		t.Fatalf("Failed to save second completed task: %v", err)
	}

	// Get completed tasks
	tasks, err := store.GetCompletedTasks(10)
	if err != nil {
		t.Fatalf("Failed to get completed tasks: %v", err)
	}
	if len(tasks) != 2 {
		t.Errorf("Expected 2 completed tasks, got %d", len(tasks))
	}

	// Verify task data
	var dootTask models.CompletedTask
	for _, task := range tasks {
		if task.Source == "doot" {
			dootTask = task
			break
		}
	}
	if dootTask.Title != "Buy groceries" {
		t.Errorf("Expected title 'Buy groceries', got '%s'", dootTask.Title)
	}
	if dootTask.DueDate == nil {
		t.Error("Expected due date to be set")
	}
}

func TestCompletedTasks_Limit(t *testing.T) {
	store := setupTestStoreWithCompletedTasks(t)
	defer func() { _ = store.Close() }()

	// Save multiple tasks
	for i := 0; i < 10; i++ {
		_ = store.SaveCompletedTask("doot", "task-"+string(rune('0'+i)), "Task "+string(rune('0'+i)), nil)
	}

	// Get with limit
	tasks, _ := store.GetCompletedTasks(5)
	if len(tasks) != 5 {
		t.Errorf("Expected 5 tasks with limit, got %d", len(tasks))
	}
}

// TestGetCompletedTasks_CompletedAtIsParsed inserts a row with a known
// completed_at string and verifies that CompletedAt is parsed correctly (not
// left as the zero value).
func TestGetCompletedTasks_CompletedAtIsParsed(t *testing.T) {
	s := setupTestStoreWithCompletedTasks(t)
	defer func() { _ = s.Close() }()

	// Insert directly with a known timestamp in the format GetCompletedTasks parses.
	knownTimestamp := "2026-03-15 14:30:00"
	_, err := s.db.Exec(
		`INSERT INTO completed_tasks (source, source_id, title, completed_at) VALUES (?, ?, ?, ?)`,
		"doot", "task-ts-test", "Timestamp Test Task", knownTimestamp,
	)
	if err != nil {
		t.Fatalf("Failed to insert task: %v", err)
	}

	tasks, err := s.GetCompletedTasks(10)
	if err != nil {
		t.Fatalf("GetCompletedTasks failed: %v", err)
	}
	if len(tasks) != 1 {
		t.Fatalf("Expected 1 task, got %d", len(tasks))
	}

	got := tasks[0].CompletedAt
	if got.IsZero() {
		t.Fatal("Expected CompletedAt to be parsed, got zero time")
	}

	// GetCompletedTasks parses with "2006-01-02 15:04:05" (no timezone → UTC).
	want := time.Date(2026, 3, 15, 14, 30, 0, 0, time.UTC)
	if !got.Equal(want) {
		t.Errorf("CompletedAt: got %v, want %v", got, want)
	}
}

// =============================================================================
// Source Configuration Tests
// =============================================================================

func setupTestStoreWithSourceConfig(t *testing.T) *Store {
	t.Helper()

	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		t.Fatalf("Failed to open test database: %v", err)
	}

	db.SetMaxOpenConns(1)
	store := &Store{db: db}

	schema := `
		CREATE TABLE IF NOT EXISTS source_config (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			source TEXT NOT NULL,
			item_type TEXT NOT NULL,
			item_id TEXT NOT NULL,
			item_name TEXT NOT NULL,
			enabled BOOLEAN DEFAULT TRUE,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			UNIQUE(source, item_type, item_id)
		);
	`
	if _, err := db.Exec(schema); err != nil {
		t.Fatalf("Failed to create schema: %v", err)
	}

	return store
}

func TestSourceConfig_UpsertAndGet(t *testing.T) {
	store := setupTestStoreWithSourceConfig(t)
	defer func() { _ = store.Close() }()

	// Upsert configs
	cfg1 := models.SourceConfig{
		Source:   "trello",
		ItemType: "board",
		ItemID:   "board-123",
		ItemName: "Work Board",
		Enabled:  true,
	}
	if err := store.UpsertSourceConfig(cfg1); err != nil {
		t.Fatalf("Failed to upsert config: %v", err)
	}

	cfg2 := models.SourceConfig{
		Source:   "trello",
		ItemType: "board",
		ItemID:   "board-456",
		ItemName: "Personal Board",
		Enabled:  false,
	}
	if err := store.UpsertSourceConfig(cfg2); err != nil {
		t.Fatalf("Failed to upsert second config: %v", err)
	}

	// Get all configs
	configs, err := store.GetSourceConfigs()
	if err != nil {
		t.Fatalf("Failed to get configs: %v", err)
	}
	if len(configs) != 2 {
		t.Errorf("Expected 2 configs, got %d", len(configs))
	}

	// Get by source
	trelloConfigs, err := store.GetSourceConfigsBySource("trello")
	if err != nil {
		t.Fatalf("Failed to get trello configs: %v", err)
	}
	if len(trelloConfigs) != 2 {
		t.Errorf("Expected 2 trello configs, got %d", len(trelloConfigs))
	}

	// Get enabled IDs
	enabledIDs, err := store.GetEnabledSourceIDs("trello", "board")
	if err != nil {
		t.Fatalf("Failed to get enabled IDs: %v", err)
	}
	if len(enabledIDs) != 1 {
		t.Errorf("Expected 1 enabled ID, got %d", len(enabledIDs))
	}
	if enabledIDs[0] != "board-123" {
		t.Errorf("Expected 'board-123', got '%s'", enabledIDs[0])
	}
}

func TestSourceConfig_SetEnabled(t *testing.T) {
	store := setupTestStoreWithSourceConfig(t)
	defer func() { _ = store.Close() }()

	// Create a config
	cfg := models.SourceConfig{
		Source:   "calendar",
		ItemType: "calendar",
		ItemID:   "cal-1",
		ItemName: "Primary",
		Enabled:  true,
	}
	_ = store.UpsertSourceConfig(cfg)

	// Disable it
	if err := store.SetSourceConfigEnabled("calendar", "calendar", "cal-1", false); err != nil {
		t.Fatalf("Failed to set enabled: %v", err)
	}

	// Verify
	enabledIDs, _ := store.GetEnabledSourceIDs("calendar", "calendar")
	if len(enabledIDs) != 0 {
		t.Error("Expected no enabled calendars after disabling")
	}
}

// =============================================================================
// Cache Metadata Tests
// =============================================================================

func setupTestStoreWithCacheMetadata(t *testing.T) *Store {
	t.Helper()

	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		t.Fatalf("Failed to open test database: %v", err)
	}

	db.SetMaxOpenConns(1)
	store := &Store{db: db}

	schema := `
		CREATE TABLE IF NOT EXISTS cache_metadata (
			key TEXT PRIMARY KEY,
			last_fetch DATETIME NOT NULL,
			ttl_minutes INTEGER DEFAULT 5,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		);
	`
	if _, err := db.Exec(schema); err != nil {
		t.Fatalf("Failed to create schema: %v", err)
	}

	return store
}

func TestCacheMetadata_UpdateAndCheck(t *testing.T) {
	store := setupTestStoreWithCacheMetadata(t)
	defer func() { _ = store.Close() }()

	// Initially no metadata
	valid, _ := store.IsCacheValid("test_key")
	if valid {
		t.Error("Cache should be invalid when no metadata exists")
	}

	// Update cache metadata
	if err := store.UpdateCacheMetadata("test_key", 5); err != nil {
		t.Fatalf("Failed to update cache metadata: %v", err)
	}

	// Now cache should be valid
	valid, err := store.IsCacheValid("test_key")
	if err != nil {
		t.Fatalf("Failed to check cache validity: %v", err)
	}
	if !valid {
		t.Error("Cache should be valid after update")
	}

	// Get metadata
	metadata, err := store.GetCacheMetadata("test_key")
	if err != nil {
		t.Fatalf("Failed to get cache metadata: %v", err)
	}
	if metadata == nil {
		t.Fatal("Expected metadata to exist")
	}
	if metadata.TTLMinutes != 5 {
		t.Errorf("Expected TTL 5, got %d", metadata.TTLMinutes)
	}

	// Invalidate cache
	if err := store.InvalidateCache("test_key"); err != nil {
		t.Fatalf("Failed to invalidate cache: %v", err)
	}

	valid, _ = store.IsCacheValid("test_key")
	if valid {
		t.Error("Cache should be invalid after invalidation")
	}
}

func TestCacheMetadata_ExpiredCache(t *testing.T) {
	store := setupTestStoreWithCacheMetadata(t)
	defer func() { _ = store.Close() }()

	// Insert old cache entry directly
	oldTime := time.Now().Add(-10 * time.Minute)
	_, err := store.db.Exec(`
		INSERT INTO cache_metadata (key, last_fetch, ttl_minutes)
		VALUES (?, ?, ?)
	`, "expired_key", oldTime, 5)
	if err != nil {
		t.Fatalf("Failed to insert old metadata: %v", err)
	}

	// Cache should be invalid (expired)
	valid, _ := store.IsCacheValid("expired_key")
	if valid {
		t.Error("Expired cache should be invalid")
	}
}

// =============================================================================
// Sync Token Tests
// =============================================================================

func setupTestStoreWithSyncTokens(t *testing.T) *Store {
	t.Helper()

	tempDir := t.TempDir()
	dbPath := filepath.Join(tempDir, "test.db")

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		t.Fatalf("Failed to open test database: %v", err)
	}

	db.SetMaxOpenConns(1)
	store := &Store{db: db}

	schema := `
		CREATE TABLE IF NOT EXISTS sync_tokens (
			service TEXT PRIMARY KEY,
			token TEXT NOT NULL,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		);
	`
	if _, err := db.Exec(schema); err != nil {
		t.Fatalf("Failed to create schema: %v", err)
	}

	return store
}

func TestSyncTokens_SetGetClear(t *testing.T) {
	store := setupTestStoreWithSyncTokens(t)
	defer func() { _ = store.Close() }()

	// Get non-existent token
	token, err := store.GetSyncToken("trello")
	if err != nil {
		t.Fatalf("Failed to get token: %v", err)
	}
	if token != "" {
		t.Errorf("Expected empty token, got '%s'", token)
	}

	// Set token
	if err := store.SetSyncToken("trello", "sync-token-123"); err != nil {
		t.Fatalf("Failed to set token: %v", err)
	}

	// Get token
	token, err = store.GetSyncToken("trello")
	if err != nil {
		t.Fatalf("Failed to get token after set: %v", err)
	}
	if token != "sync-token-123" {
		t.Errorf("Expected 'sync-token-123', got '%s'", token)
	}

	// Clear token
	if err := store.ClearSyncToken("trello"); err != nil {
		t.Fatalf("Failed to clear token: %v", err)
	}

	token, _ = store.GetSyncToken("trello")
	if token != "" {
		t.Errorf("Expected empty token after clear, got '%s'", token)
	}
}