package db

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

// TestCreateRuleMarkAsSpamWorksAfterLegacyCheckMigration reproduces a live bug: a DB
// created before 'mark_as_spam' was added to esrv_mailbox_filter_rules' action CHECK
// constraint kept the old constraint forever, since SQLite can't ALTER a CHECK on an
// existing table — so CreateRule with action "mark_as_spam" (webmail's "Mark as Junk"
// auto-blacklist, see webui.ensureJunkRuleForSender) failed with "CHECK constraint
// failed: action IN ('move_to_folder','delete','mark_read')" on any pre-existing
// installation, confirmed live via the webmail context menu.
func TestCreateRuleMarkAsSpamWorksAfterLegacyCheckMigration(t *testing.T) {
	path := filepath.Join(t.TempDir(), "test.db")

	raw, err := sql.Open("sqlite", path)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := raw.Exec(`
		CREATE TABLE esrv_mailboxes (id INTEGER PRIMARY KEY AUTOINCREMENT)
	`); err != nil {
		t.Fatal(err)
	}
	if _, err := raw.Exec(`
		CREATE TABLE esrv_mailbox_filter_rules (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			mailbox_id INTEGER NOT NULL REFERENCES esrv_mailboxes(id),
			priority INTEGER NOT NULL DEFAULT 0,
			condition_field TEXT NOT NULL CHECK(condition_field IN ('from','to','subject')),
			condition_op TEXT NOT NULL CHECK(condition_op IN ('contains','equals','starts_with')),
			condition_value TEXT NOT NULL,
			action TEXT NOT NULL CHECK(action IN ('move_to_folder','delete','mark_read')),
			action_value TEXT NOT NULL DEFAULT '',
			is_active INTEGER NOT NULL DEFAULT 1,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)
	`); err != nil {
		t.Fatal(err)
	}
	if _, err := raw.Exec(`INSERT INTO esrv_mailboxes (id) VALUES (1)`); err != nil {
		t.Fatal(err)
	}
	if _, err := raw.Exec(`
		INSERT INTO esrv_mailbox_filter_rules (mailbox_id, priority, condition_field, condition_op, condition_value, action, action_value)
		VALUES (1, 0, 'subject', 'contains', 'existing-rule', 'move_to_folder', 'Archive')
	`); err != nil {
		t.Fatal(err)
	}
	if err := raw.Close(); err != nil {
		t.Fatal(err)
	}

	database, err := Open(path)
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() { database.Close() })

	if _, err := database.CreateRule(1, 0, "from", "contains", "spammer@example.com", "mark_as_spam", ""); err != nil {
		t.Fatalf("CreateRule with mark_as_spam after migrating a legacy DB: %v", err)
	}

	rules, err := database.ListRulesForMailbox(1)
	if err != nil {
		t.Fatal(err)
	}
	if len(rules) != 2 {
		t.Fatalf("expected the pre-existing rule to survive the table rebuild alongside the new one, got %d rules", len(rules))
	}
	found := false
	for _, r := range rules {
		if r.ConditionValue == "existing-rule" && r.ActionValue == "Archive" {
			found = true
		}
	}
	if !found {
		t.Fatalf("pre-existing rule's data was not preserved across the migration: %+v", rules)
	}
}
