package filter

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"testing"

	"github.com/stretchr/testify/require"
	_ "modernc.org/sqlite"
)

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	_, err = engine.Compile(context.Background(), `tags.exists(t, t == "1231")`)
	require.NoError(t, err)
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	_, err = engine.Compile(context.Background(), `pinned && 1`)
	require.Error(t, err)
	require.Contains(t, err.Error(), "failed to compile filter")
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	_, err = engine.Compile(context.Background(), `1`)
	require.EqualError(t, err, "filter must evaluate to a boolean value")
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	_, err = engine.Compile(context.Background(), `content.matches("(")`)
	require.Error(t, err)
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	stmt, err := engine.CompileToStatement(context.Background(), `content.matches("v[0-9]+")`, RenderOptions{Dialect: DialectPostgres})
	require.NoError(t, err)
	require.Contains(t, stmt.SQL, "~")
	require.Equal(t, []any{"v[0-9]+"}, stmt.Args)
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	_, err = engine.Compile(context.Background(), `visibility.startsWith("P")`)
	require.Error(t, err)
	require.Contains(t, err.Error(), "does not support text matching")
}

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

	for _, schema := range []Schema{NewSchema(), NewAttachmentSchema()} {
		engine, err := NewEngine(schema)
		require.NoError(t, err)
		for _, dialect := range []DialectName{DialectSQLite, DialectMySQL, DialectPostgres} {
			assigned, err := engine.CompileToStatement(
				context.Background(),
				`space == "spaces/team"`,
				RenderOptions{Dialect: dialect},
			)
			require.NoError(t, err, schema.Name, dialect)
			require.Contains(t, assigned.SQL, "spaces/", schema.Name, dialect)
			require.Equal(t, []any{"spaces/team"}, assigned.Args, schema.Name, dialect)

			unassigned, err := engine.CompileToStatement(context.Background(), `space == null`, RenderOptions{Dialect: dialect})
			require.NoError(t, err, schema.Name, dialect)
			require.Contains(t, unassigned.SQL, "IS NULL", schema.Name, dialect)
			require.Empty(t, unassigned.Args, schema.Name, dialect)
		}

		for _, dialect := range []DialectName{DialectSQLite, DialectMySQL, DialectPostgres} {
			assigned, err := engine.CompileToStatement(context.Background(), `space != null`, RenderOptions{Dialect: dialect})
			require.NoError(t, err, schema.Name, dialect)
			require.Contains(t, assigned.SQL, "IS NOT NULL", schema.Name, dialect)
			require.Empty(t, assigned.Args, schema.Name, dialect)
		}

		_, err = engine.Compile(context.Background(), `space != "spaces/team"`)
		require.ErrorContains(t, err, `operator != not allowed for field "space"`, "NULL != value would silently drop unassigned memos")
	}
}

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

	engine, err := NewEngine(NewAttachmentSchema())
	require.NoError(t, err)
	for _, dialect := range []DialectName{DialectSQLite, DialectMySQL, DialectPostgres} {
		statement, err := engine.CompileToStatement(context.Background(), `creator == "users/alice"`, RenderOptions{Dialect: dialect})
		require.NoError(t, err, dialect)
		require.Contains(t, statement.SQL, "CASE WHEN", dialect)
		require.Equal(t, []any{"users/alice"}, statement.Args, dialect)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	stmt, err := engine.CompileToStatement(context.Background(), `content.contains("50%_off")`, RenderOptions{Dialect: DialectSQLite})
	require.NoError(t, err)
	// The % and _ in the value must be escaped so they are matched literally,
	// and SQLite needs an explicit ESCAPE clause.
	require.Contains(t, stmt.SQL, `ESCAPE '\'`)
	require.Equal(t, []any{`%50\%\_off%`}, stmt.Args)
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	const tag = `work_%"quoted"`
	cases := []struct {
		dialect   DialectName
		fragments []string
	}{
		{DialectSQLite, []string{"json_each(", "COLLATE BINARY"}},
		{DialectMySQL, []string{"JSON_TABLE(", "CAST(tag_item.value AS BINARY)"}},
		{DialectPostgres, []string{"jsonb_array_elements_text(", `(tag_item.value COLLATE "C")`}},
	}
	for _, tc := range cases {
		stmt, err := engine.CompileToStatement(context.Background(), `tag in ["work_%\"quoted\""]`, RenderOptions{Dialect: tc.dialect})
		require.NoError(t, err, tc.dialect)
		for _, fragment := range tc.fragments {
			require.Contains(t, stmt.SQL, fragment, tc.dialect)
		}
		require.NotContains(t, stmt.SQL, " LIKE ", tc.dialect)
		require.Equal(t, []any{tag}, stmt.Args, tc.dialect)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	const tag = `work_%"quoted"`
	cases := []struct {
		dialect   DialectName
		fragments []string
	}{
		{DialectSQLite, []string{"json_each(", "COLLATE BINARY"}},
		{DialectMySQL, []string{"JSON_TABLE(", "CAST(tag_item.value AS BINARY)"}},
		{DialectPostgres, []string{"jsonb_array_elements_text(", `(tag_item.value COLLATE "C")`}},
	}
	for _, tc := range cases {
		stmt, err := engine.CompileToStatement(context.Background(), `tags.exists(t, t == "work_%\"quoted\"")`, RenderOptions{Dialect: tc.dialect})
		require.NoError(t, err, tc.dialect)
		for _, fragment := range tc.fragments {
			require.Contains(t, stmt.SQL, fragment, tc.dialect)
		}
		require.NotContains(t, stmt.SQL, " LIKE ", tc.dialect)
		require.Equal(t, []any{tag}, stmt.Args, tc.dialect)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)
	for _, expression := range []string{`tags.all(t, t == "Work")`, `tags.exists_one(t, t == "Work")`} {
		stmt, err := engine.CompileToStatement(context.Background(), expression, RenderOptions{Dialect: DialectMySQL})
		require.NoError(t, err)
		require.Contains(t, stmt.SQL, "value LONGTEXT PATH '$'")
		require.Contains(t, stmt.SQL, "CAST(tag_item.value AS BINARY) = CAST(? AS BINARY)")
		require.NotContains(t, stmt.SQL, "VARCHAR(512)")
		require.Equal(t, []any{"Work"}, stmt.Args)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)
	for _, expression := range []string{`tags.all(t, t == "Work")`, `tags.exists_one(t, t == "Work")`} {
		stmt, err := engine.CompileToStatement(context.Background(), expression, RenderOptions{Dialect: DialectPostgres})
		require.NoError(t, err)
		require.Contains(t, stmt.SQL, `(tag_item.value COLLATE "C") = ($1::text COLLATE "C")`)
		require.Equal(t, []any{"Work"}, stmt.Args)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	predicates := []struct {
		name        string
		expression  string
		sqliteSQL   string
		sqliteArgs  []any
		likePattern string
		usesLike    bool
	}{
		{name: "equals", expression: `t == "Work_%!"`, sqliteSQL: "COLLATE BINARY", sqliteArgs: []any{"Work_%!"}},
		{name: "startsWith", expression: `t.startsWith("Work_%!")`, sqliteSQL: "instr(tag_item.value, ?) = 1", sqliteArgs: []any{"Work_%!"}, likePattern: "Work!_!%!!%", usesLike: true},
		{name: "endsWith", expression: `t.endsWith("Work_%!")`, sqliteSQL: "substr(tag_item.value, -length(?))", sqliteArgs: []any{"Work_%!", "Work_%!"}, likePattern: "%Work!_!%!!", usesLike: true},
		{name: "contains", expression: `t.contains("Work_%!")`, sqliteSQL: "instr(tag_item.value, ?) > 0", sqliteArgs: []any{"Work_%!"}, likePattern: "%Work!_!%!!%", usesLike: true},
	}

	// MySQL cannot use EXISTS here: its semi-join rewrite drops JSON_TABLE's lateral
	// dependency on the outer row, so it counts rows instead. See
	// TestTagComprehensionAvoidsMySQLExistsSemiJoin.
	for _, comprehension := range []struct {
		kind        string
		sqlFragment string
		mysqlSQL    string
	}{
		{kind: "exists", sqlFragment: "EXISTS (SELECT 1", mysqlSQL: "SELECT COUNT(*)"},
		{kind: "all", sqlFragment: "NOT EXISTS (SELECT 1", mysqlSQL: "SELECT COUNT(*)"},
		{kind: "exists_one", sqlFragment: "SELECT COUNT(*)", mysqlSQL: "SELECT COUNT(*)"},
	} {
		for _, predicate := range predicates {
			expression := fmt.Sprintf("tags.%s(t, %s)", comprehension.kind, predicate.expression)
			t.Run(comprehension.kind+"/"+predicate.name, func(t *testing.T) {
				t.Parallel()

				sqliteStmt, err := engine.CompileToStatement(context.Background(), expression, RenderOptions{Dialect: DialectSQLite})
				require.NoError(t, err)
				require.Contains(t, sqliteStmt.SQL, "json_each(")
				require.Contains(t, sqliteStmt.SQL, comprehension.sqlFragment)
				require.Contains(t, sqliteStmt.SQL, predicate.sqliteSQL)
				require.NotContains(t, sqliteStmt.SQL, "memos_unicode_lower")
				require.Equal(t, predicate.sqliteArgs, sqliteStmt.Args)

				mysqlStmt, err := engine.CompileToStatement(context.Background(), expression, RenderOptions{Dialect: DialectMySQL})
				require.NoError(t, err)
				require.Contains(t, mysqlStmt.SQL, "JSON_TABLE(")
				require.Contains(t, mysqlStmt.SQL, comprehension.mysqlSQL)
				require.NotContains(t, mysqlStmt.SQL, "EXISTS (SELECT 1")
				require.Contains(t, mysqlStmt.SQL, "CAST(tag_item.value AS BINARY)")

				postgresStmt, err := engine.CompileToStatement(context.Background(), expression, RenderOptions{Dialect: DialectPostgres})
				require.NoError(t, err)
				require.Contains(t, postgresStmt.SQL, "jsonb_array_elements_text(")
				require.Contains(t, postgresStmt.SQL, comprehension.sqlFragment)
				require.Contains(t, postgresStmt.SQL, `(tag_item.value COLLATE "C")`)

				if predicate.usesLike {
					require.Contains(t, mysqlStmt.SQL, " LIKE ")
					require.Contains(t, mysqlStmt.SQL, "ESCAPE '!'")
					require.Contains(t, postgresStmt.SQL, " LIKE ")
					require.Contains(t, postgresStmt.SQL, "ESCAPE '!'")
					require.Equal(t, []any{predicate.likePattern}, mysqlStmt.Args)
					require.Equal(t, []any{predicate.likePattern}, postgresStmt.Args)
				} else {
					require.NotContains(t, mysqlStmt.SQL, " LIKE ")
					require.NotContains(t, postgresStmt.SQL, " LIKE ")
					require.Equal(t, []any{"Work_%!"}, mysqlStmt.Args)
					require.Equal(t, []any{"Work_%!"}, postgresStmt.Args)
				}
			})
		}
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)
	for _, dialect := range []DialectName{DialectSQLite, DialectMySQL, DialectPostgres} {
		stmt, err := engine.CompileToStatement(context.Background(), `tag in []`, RenderOptions{Dialect: dialect})
		require.NoError(t, err, dialect)
		require.Equal(t, "1 = 0", stmt.SQL, dialect)
		require.Empty(t, stmt.Args, dialect)
	}
}

func TestRenderTagStringPredicatesSQLiteBehavior(t *testing.T) {
	db, err := sql.Open("sqlite", ":memory:")
	require.NoError(t, err)
	db.SetMaxOpenConns(1)
	t.Cleanup(func() { require.NoError(t, db.Close()) })

	_, err = db.Exec(`CREATE TABLE memo (id INTEGER PRIMARY KEY, payload TEXT)`)
	require.NoError(t, err)
	for _, fixture := range []struct {
		id      int
		payload string
	}{
		{1, `{"tags":["Work_%done"]}`},
		{2, `{"tags":["work_%done"]}`},
		{3, `{"tags":["WorkX%done"]}`},
		{4, `{"tags":["Work_XXdone"]}`},
		{5, `{"tags":["Work_%done","other"]}`},
		{6, `{"tags":["Work_%done","Work_%done"]}`},
		{7, `{"tags":["preWork_%done"]}`},
		{8, `{"tags":["Work_%doneSuffix"]}`},
	} {
		_, err = db.Exec(`INSERT INTO memo (id, payload) VALUES (?, ?)`, fixture.id, fixture.payload)
		require.NoError(t, err)
	}

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	tests := []struct {
		name      string
		predicate string
		exists    []int
		all       []int
		existsOne []int
	}{
		{
			name:      "equality",
			predicate: `t == "Work_%done"`,
			exists:    []int{1, 5, 6},
			all:       []int{1, 6},
			existsOne: []int{1, 5},
		},
		{
			name:      "startsWith",
			predicate: `t.startsWith("Work_%done")`,
			exists:    []int{1, 5, 6, 8},
			all:       []int{1, 6, 8},
			existsOne: []int{1, 5, 8},
		},
		{
			name:      "endsWith",
			predicate: `t.endsWith("Work_%done")`,
			exists:    []int{1, 5, 6, 7},
			all:       []int{1, 6, 7},
			existsOne: []int{1, 5, 7},
		},
		{
			name:      "contains",
			predicate: `t.contains("Work_%done")`,
			exists:    []int{1, 5, 6, 7, 8},
			all:       []int{1, 6, 7, 8},
			existsOne: []int{1, 5, 7, 8},
		},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			for _, comprehension := range []struct {
				name string
				want []int
			}{
				{name: "exists", want: tc.exists},
				{name: "all", want: tc.all},
				{name: "exists_one", want: tc.existsOne},
			} {
				t.Run(comprehension.name, func(t *testing.T) {
					stmt, err := engine.CompileToStatement(
						context.Background(),
						fmt.Sprintf("tags.%s(t, %s)", comprehension.name, tc.predicate),
						RenderOptions{Dialect: DialectSQLite},
					)
					require.NoError(t, err)
					require.Equal(t, comprehension.want, selectMemoIDs(t, db, stmt))
				})
			}
		})
	}
}

func selectMemoIDs(t *testing.T, db *sql.DB, stmt Statement) []int {
	t.Helper()

	rows, err := db.Query(`SELECT id FROM memo WHERE `+stmt.SQL+` ORDER BY id`, stmt.Args...)
	require.NoError(t, err)
	defer rows.Close()

	var ids []int
	for rows.Next() {
		var id int
		require.NoError(t, rows.Scan(&id))
		ids = append(ids, id)
	}
	require.NoError(t, rows.Err())
	return ids
}

// =============================================================================
// Cross-dialect rendering tests (no DB required; complements the SQLite-only
// behavioral tests in store/test by asserting MySQL/Postgres SQL generation).
// =============================================================================

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	cases := []struct {
		dialect   DialectName
		fragments []string
	}{
		{DialectSQLite, []string{"memos_unicode_lower(", "`memo`.`content`", `ESCAPE '\'`}},
		{DialectPostgres, []string{"memo.content ILIKE $1"}},
		{DialectMySQL, []string{"`memo`.`content` LIKE ?"}},
	}
	for _, tc := range cases {
		stmt, err := engine.CompileToStatement(context.Background(), `content.startsWith("TODO")`, RenderOptions{Dialect: tc.dialect})
		require.NoError(t, err, tc.dialect)
		for _, frag := range tc.fragments {
			require.Contains(t, stmt.SQL, frag, "dialect %s", tc.dialect)
		}
		require.Equal(t, []any{"TODO%"}, stmt.Args, "dialect %s", tc.dialect)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	for _, dialect := range []DialectName{DialectSQLite, DialectPostgres, DialectMySQL} {
		stmt, err := engine.CompileToStatement(context.Background(), `content.endsWith(".md")`, RenderOptions{Dialect: dialect})
		require.NoError(t, err, dialect)
		require.Equal(t, []any{"%.md"}, stmt.Args, "dialect %s", dialect)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	cases := []struct {
		dialect  DialectName
		fragment string
	}{
		{DialectSQLite, "`memo`.`content` REGEXP ?"},
		{DialectMySQL, "`memo`.`content` REGEXP ?"},
		{DialectPostgres, "memo.content ~ $1"},
	}
	for _, tc := range cases {
		stmt, err := engine.CompileToStatement(context.Background(), `content.matches("v[0-9]+")`, RenderOptions{Dialect: tc.dialect})
		require.NoError(t, err, tc.dialect)
		require.Contains(t, stmt.SQL, tc.fragment, "dialect %s", tc.dialect)
		require.Equal(t, []any{"v[0-9]+"}, stmt.Args, "dialect %s", tc.dialect)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	cases := []struct {
		dialect   DialectName
		fragments []string
		args      []any
	}{
		{DialectSQLite, []string{"NOT EXISTS", "json_each(", "json_array_length(", "instr(tag_item.value, ?) = 1"}, []any{"work/"}},
		{DialectPostgres, []string{"NOT EXISTS", "jsonb_array_elements_text(", "jsonb_array_length(", `(tag_item.value COLLATE "C") LIKE`}, []any{"work/%"}},
		// MySQL counts non-matching rows instead of using NOT EXISTS; see
		// TestTagComprehensionAvoidsMySQLExistsSemiJoin.
		{DialectMySQL, []string{") = 0", "JSON_TABLE(", "JSON_LENGTH(", "CAST(tag_item.value AS BINARY) LIKE"}, []any{"work/%"}},
	}
	for _, tc := range cases {
		stmt, err := engine.CompileToStatement(context.Background(), `tags.all(t, t.startsWith("work/"))`, RenderOptions{Dialect: tc.dialect})
		require.NoError(t, err, tc.dialect)
		for _, frag := range tc.fragments {
			require.Contains(t, stmt.SQL, frag, "dialect %s", tc.dialect)
		}
		require.Equal(t, tc.args, stmt.Args, "dialect %s", tc.dialect)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	// Both % and _ in the value must be escaped so they match literally.
	stmt, err := engine.CompileToStatement(context.Background(), `content.contains("a%b_c")`, RenderOptions{Dialect: DialectSQLite})
	require.NoError(t, err)
	require.Equal(t, []any{`%a\%b\_c%`}, stmt.Args)
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	// size() is not a valid per-element predicate inside all().
	_, err = engine.CompileToStatement(context.Background(), `tags.all(t, size(t) > 2)`, RenderOptions{Dialect: DialectSQLite})
	require.Error(t, err)
}

// TestTagComprehensionAvoidsMySQLExistsSemiJoin pins the reason MySQL renders tag
// comprehensions as row counts rather than EXISTS.
//
// MySQL rewrites `EXISTS (SELECT ... FROM JSON_TABLE(<outer column>, ...))` into a
// semi-join, and that rewrite drops JSON_TABLE's lateral dependency on the outer row:
// the subquery evaluates as empty for every row, so the predicate is silently always
// false and every tag filter returns nothing. It fails with no SQL error, which is why
// this is guarded here rather than left to the container suite. `NOT EXISTS` happens to
// take the anti-join path and works, but relying on that asymmetry is not worth it, so
// both directions use counts.
func TestTagComprehensionAvoidsMySQLExistsSemiJoin(t *testing.T) {
	t.Parallel()

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	for _, expression := range []string{
		`tags.exists(t, t == "work")`,
		`tags.exists(t, t.startsWith("work/"))`,
		`tags.all(t, t == "work")`,
		`tags.all(t, t.contains("work"))`,
		`tags.exists_one(t, t == "work")`,
	} {
		stmt, err := engine.CompileToStatement(context.Background(), expression, RenderOptions{Dialect: DialectMySQL})
		require.NoError(t, err, expression)
		require.NotContains(t, stmt.SQL, "EXISTS", "%s must not use EXISTS on MySQL", expression)
		require.Contains(t, stmt.SQL, "SELECT COUNT(*)", "%s should count JSON_TABLE rows on MySQL", expression)
		require.Contains(t, stmt.SQL, "JSON_TABLE(", expression)
	}

	// The other dialects correlate EXISTS correctly and keep using it.
	for _, dialect := range []DialectName{DialectSQLite, DialectPostgres} {
		stmt, err := engine.CompileToStatement(context.Background(), `tags.exists(t, t == "work")`, RenderOptions{Dialect: dialect})
		require.NoError(t, err, dialect)
		require.Contains(t, stmt.SQL, "EXISTS (SELECT 1", "dialect %s", dialect)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	cases := []struct {
		dialect DialectName
		sql     string
	}{
		{DialectSQLite, "JSON_EXTRACT(`memo`.`payload`, '$.location') IS NOT NULL"},
		{DialectMySQL, "COALESCE(JSON_TYPE(JSON_EXTRACT(`memo`.`payload`, '$.location')), 'NULL') != 'NULL'"},
		{DialectPostgres, "memo.payload->>'location' IS NOT NULL"},
	}
	for _, tc := range cases {
		stmt, err := engine.CompileToStatement(context.Background(), `has_location`, RenderOptions{Dialect: tc.dialect})
		require.NoError(t, err, tc.dialect)
		require.Equal(t, tc.sql, stmt.SQL, tc.dialect)
		require.Empty(t, stmt.Args, tc.dialect)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	const exists = "JSON_EXTRACT(`memo`.`payload`, '$.location') IS NOT NULL"
	cases := []struct {
		expr string
		sql  string
	}{
		{`has_location`, exists},
		{`!has_location`, "NOT (" + exists + ")"},
		{`has_location == true`, exists},
		{`has_location == false`, "NOT (" + exists + ")"},
		{`has_location != true`, "NOT (" + exists + ")"},
		{`has_location != false`, exists},
	}
	for _, tc := range cases {
		stmt, err := engine.CompileToStatement(context.Background(), tc.expr, RenderOptions{Dialect: DialectSQLite})
		require.NoError(t, err, tc.expr)
		require.Equal(t, tc.sql, stmt.SQL, tc.expr)
		require.Empty(t, stmt.Args, tc.expr)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	// Only ==/!= are meaningful for a presence flag.
	_, err = engine.Compile(context.Background(), `has_location < true`)
	require.Error(t, err)
}

// TestHasLocationSQLiteBehavior pins the presence semantics against a real
// database: a missing key, an explicit JSON null, and a NULL payload all count
// as absent, while any location object — even an empty one — counts as present.
func TestHasLocationSQLiteBehavior(t *testing.T) {
	db, err := sql.Open("sqlite", ":memory:")
	require.NoError(t, err)
	db.SetMaxOpenConns(1)
	t.Cleanup(func() { require.NoError(t, db.Close()) })

	_, err = db.Exec(`CREATE TABLE memo (id INTEGER PRIMARY KEY, payload TEXT)`)
	require.NoError(t, err)
	for _, fixture := range []struct {
		id      int
		payload any
	}{
		{1, `{}`},
		{2, `{"location":{"placeholder":"Tokyo","latitude":35.6,"longitude":139.7}}`},
		{3, `{"location":{}}`},
		{4, `{"location":null}`},
		{5, nil},
	} {
		_, err = db.Exec(`INSERT INTO memo (id, payload) VALUES (?, ?)`, fixture.id, fixture.payload)
		require.NoError(t, err)
	}

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	cases := []struct {
		expr string
		want []int
	}{
		{`has_location`, []int{2, 3}},
		{`!has_location`, []int{1, 4, 5}},
		{`has_location == false`, []int{1, 4, 5}},
		{`has_location != false`, []int{2, 3}},
	}
	for _, tc := range cases {
		stmt, err := engine.CompileToStatement(context.Background(), tc.expr, RenderOptions{Dialect: DialectSQLite})
		require.NoError(t, err, tc.expr)
		require.Equal(t, tc.want, selectMemoIDs(t, db, stmt), tc.expr)
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	predicates := map[DialectName]string{
		DialectSQLite:   "JSON_EXTRACT(`memo`.`payload`, '$.property.hasLink') IS TRUE",
		DialectMySQL:    "COALESCE(JSON_EXTRACT(`memo`.`payload`, '$.property.hasLink'), CAST('false' AS JSON)) = CAST('true' AS JSON)",
		DialectPostgres: "(memo.payload->'property'->>'hasLink')::boolean IS TRUE",
	}
	for dialect, predicate := range predicates {
		cases := []struct {
			expr string
			sql  string
		}{
			{`has_link`, predicate},
			{`!has_link`, "NOT (" + predicate + ")"},
			{`has_link == true`, predicate},
			{`has_link == false`, "NOT (" + predicate + ")"},
			{`has_link != true`, "NOT (" + predicate + ")"},
			{`has_link != false`, predicate},
		}
		for _, tc := range cases {
			stmt, err := engine.CompileToStatement(context.Background(), tc.expr, RenderOptions{Dialect: dialect})
			require.NoError(t, err, dialect, tc.expr)
			require.Equal(t, tc.sql, stmt.SQL, dialect, tc.expr)
			require.Empty(t, stmt.Args, dialect, tc.expr)
		}
	}
}

// TestJSONBoolComparisonSQLiteBehavior pins the comparison semantics of the
// JSON boolean flags against a real database. Memo payloads omit false flags,
// so a missing key, a missing property object and a NULL payload all count as
// false, and `flag == false` must match the same rows as `!flag`.
func TestJSONBoolComparisonSQLiteBehavior(t *testing.T) {
	db, err := sql.Open("sqlite", ":memory:")
	require.NoError(t, err)
	db.SetMaxOpenConns(1)
	t.Cleanup(func() { require.NoError(t, db.Close()) })

	_, err = db.Exec(`CREATE TABLE memo (id INTEGER PRIMARY KEY, payload TEXT)`)
	require.NoError(t, err)

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	flags := map[string]string{
		"has_task_list":        "hasTaskList",
		"has_link":             "hasLink",
		"has_code":             "hasCode",
		"has_incomplete_tasks": "hasIncompleteTasks",
	}
	for field, key := range flags {
		_, err = db.Exec(`DELETE FROM memo`)
		require.NoError(t, err)
		for _, fixture := range []struct {
			id      int
			payload any
		}{
			{1, `{}`},
			{2, fmt.Sprintf(`{"property":{"%s":true}}`, key)},
			{3, fmt.Sprintf(`{"property":{"%s":false}}`, key)},
			{4, `{"property":{}}`},
			{5, nil},
		} {
			_, err = db.Exec(`INSERT INTO memo (id, payload) VALUES (?, ?)`, fixture.id, fixture.payload)
			require.NoError(t, err)
		}

		cases := []struct {
			expr string
			want []int
		}{
			{field, []int{2}},
			{"!" + field, []int{1, 3, 4, 5}},
			{field + " == true", []int{2}},
			{field + " == false", []int{1, 3, 4, 5}},
			{field + " != true", []int{1, 3, 4, 5}},
			{field + " != false", []int{2}},
		}
		for _, tc := range cases {
			stmt, err := engine.CompileToStatement(context.Background(), tc.expr, RenderOptions{Dialect: DialectSQLite})
			require.NoError(t, err, tc.expr)
			require.Equal(t, tc.want, selectMemoIDs(t, db, stmt), tc.expr)
		}
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	predicates := map[string]map[DialectName]string{
		"has_attachments": {
			DialectSQLite:   "EXISTS (SELECT 1 FROM `attachment` WHERE `attachment`.`memo_id` = `memo`.`id`)",
			DialectMySQL:    "EXISTS (SELECT 1 FROM `attachment` WHERE `attachment`.`memo_id` = `memo`.`id`)",
			DialectPostgres: "EXISTS (SELECT 1 FROM attachment WHERE attachment.memo_id = memo.id)",
		},
		"has_image": {
			DialectSQLite: "(JSON_EXTRACT(`memo`.`payload`, '$.property.hasInlineImage') IS TRUE OR " +
				"EXISTS (SELECT 1 FROM `attachment` WHERE `attachment`.`memo_id` = `memo`.`id` AND LOWER(`attachment`.`type`) LIKE 'image/%'))",
			DialectMySQL: "(COALESCE(JSON_EXTRACT(`memo`.`payload`, '$.property.hasInlineImage'), CAST('false' AS JSON)) = CAST('true' AS JSON) OR " +
				"EXISTS (SELECT 1 FROM `attachment` WHERE `attachment`.`memo_id` = `memo`.`id` AND LOWER(`attachment`.`type`) LIKE 'image/%'))",
			DialectPostgres: "((memo.payload->'property'->>'hasInlineImage')::boolean IS TRUE OR " +
				"EXISTS (SELECT 1 FROM attachment WHERE attachment.memo_id = memo.id AND LOWER(attachment.type) LIKE 'image/%'))",
		},
	}
	for field, byDialect := range predicates {
		for dialect, predicate := range byDialect {
			cases := []struct {
				expr string
				sql  string
			}{
				{field, predicate},
				{"!" + field, "NOT (" + predicate + ")"},
				{field + " == true", predicate},
				{field + " == false", "NOT (" + predicate + ")"},
				{field + " != true", "NOT (" + predicate + ")"},
				{field + " != false", predicate},
			}
			for _, tc := range cases {
				stmt, err := engine.CompileToStatement(context.Background(), tc.expr, RenderOptions{Dialect: dialect})
				require.NoError(t, err, dialect, tc.expr)
				require.Equal(t, tc.sql, stmt.SQL, dialect, tc.expr)
				require.Empty(t, stmt.Args, dialect, tc.expr)
			}
		}
	}
}

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

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)
	for _, expr := range []string{`has_image > false`, `has_attachments == "yes"`, `"image" in has_image`} {
		_, err := engine.CompileToStatement(context.Background(), expr, RenderOptions{Dialect: DialectSQLite})
		require.Error(t, err, expr)
	}
}

// TestExistsSQLiteBehavior pins has_attachments and has_image against real
// tables: has_attachments follows attachment.memo_id alone, and has_image is
// true for Markdown image syntax or an attached image/* file, whatever the
// MIME type's case, and false for other attachments and unbound uploads.
func TestExistsSQLiteBehavior(t *testing.T) {
	db, err := sql.Open("sqlite", ":memory:")
	require.NoError(t, err)
	db.SetMaxOpenConns(1)
	t.Cleanup(func() { require.NoError(t, db.Close()) })

	_, err = db.Exec(`CREATE TABLE memo (id INTEGER PRIMARY KEY, payload TEXT)`)
	require.NoError(t, err)
	_, err = db.Exec(`CREATE TABLE attachment (id INTEGER PRIMARY KEY, memo_id INTEGER, type TEXT NOT NULL DEFAULT '')`)
	require.NoError(t, err)

	for _, fixture := range []struct {
		id      int
		payload any
	}{
		{1, `{}`}, // nothing
		{2, `{"property":{"hasInlineImage":true}}`}, // external image in content, no attachment
		{3, `{}`}, // uploaded photo, not referenced in content
		{4, `{}`}, // PDF attachment only
		{5, `{"property":{"hasInlineImage":true}}`}, // managed inline image, also bound
		{6, nil}, // NULL payload, upper-case MIME image
		{7, `{"property":{"hasInlineImage":false}}`}, // explicit false flag, nothing attached
	} {
		_, err = db.Exec(`INSERT INTO memo (id, payload) VALUES (?, ?)`, fixture.id, fixture.payload)
		require.NoError(t, err)
	}
	for _, attachment := range []struct {
		memoID   any
		mimeType string
	}{
		{3, "image/jpeg"},
		{4, "application/pdf"},
		{5, "image/png"},
		{6, "IMAGE/HEIC"},
		{nil, "image/png"}, // uploaded but not bound to any memo
	} {
		_, err = db.Exec(`INSERT INTO attachment (memo_id, type) VALUES (?, ?)`, attachment.memoID, attachment.mimeType)
		require.NoError(t, err)
	}

	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	cases := []struct {
		expr string
		want []int
	}{
		{`has_attachments`, []int{3, 4, 5, 6}},
		{`!has_attachments`, []int{1, 2, 7}},
		{`has_attachments == false`, []int{1, 2, 7}},
		{`has_image`, []int{2, 3, 5, 6}},
		{`!has_image`, []int{1, 4, 7}},
		{`has_image != true`, []int{1, 4, 7}},
		{`has_attachments && !has_image`, []int{4}},
		{`has_image && !has_attachments`, []int{2}},
	}
	for _, tc := range cases {
		stmt, err := engine.CompileToStatement(context.Background(), tc.expr, RenderOptions{Dialect: DialectSQLite})
		require.NoError(t, err, tc.expr)
		require.Equal(t, tc.want, selectMemoIDs(t, db, stmt), tc.expr)
	}
}

func TestCompileRejectsOversizedExpression(t *testing.T) {
	t.Parallel()
	engine, err := NewEngine(NewSchema())
	require.NoError(t, err)

	var builder strings.Builder
	builder.WriteString(`content.contains("a")`)
	for builder.Len() < maxFilterExpressionSize+64 {
		builder.WriteString(` || content.contains("a")`)
	}
	_, err = engine.Compile(context.Background(), builder.String())
	require.Error(t, err)

	_, err = engine.Compile(context.Background(), `content.contains("a") || content.contains("b")`)
	require.NoError(t, err)
}