types

type Columns interface {
	// Col retrieves the vendor-quoted column name for the given struct field name.
	// If aliased via As(), returns qualified column (e.g., "u.id").
	// Panics if the field name is not found (fail-fast for development-time typos).
	//
	// Example (unaliased):
	//   cols.Col("ID")    // Returns: "id" (PostgreSQL) or "id" (Oracle, not a reserved word)
	//   cols.Col("Level") // Returns: "level" (PostgreSQL) or "\"level\"" (Oracle, reserved word — double-quoted lowercase)
	//
	// Example (aliased):
	//   u := cols.As("u")
	//   u.Col("ID")       // Returns: "u.id" (PostgreSQL) or "u.id" (Oracle, not a reserved word)
	Col(fieldName string) string

	// As returns a new Columns instance bound to the specified table alias.
	// The returned instance shares the underlying column metadata (zero-copy).
	// This method is immutable - the original Columns instance remains unchanged.
	//
	// Example:
	//   cols := qb.Columns(&User{})
	//   u := cols.As("u")
	//   p := cols.As("p")
	//   u.Col("ID")  // "u.id"
	//   p.Col("ID")  // "p.id"
	//   cols.Col("ID") // "id" (original unaffected)
	//
	// The alias becomes SQL syntax, so it is validated against the same
	// bare-identifier grammar the table argument applies to the alias half of
	// "users u" (ADR-082). Anything else, an empty alias included, panics with a
	// *InvalidAliasError: As has no error channel, and an alias is a developer
	// constant, so a violation is a programming error surfaced at construction
	// rather than a deferred ToSQL() error.
	As(alias string) Columns

	// Alias returns the current table alias, or empty string if unaliased.
	//
	// Example:
	//   cols.Alias()        // ""
	//   cols.As("u").Alias() // "u"
	Alias() string

	// Cols retrieves vendor-quoted column names for multiple struct field names.
	// If aliased, returns qualified columns.
	// Panics if any field name is not found.
	//
	// Example:
	//   cols.Cols("ID", "Name", "Email") // ["id", "name", "email"]
	//   cols.As("u").Cols("ID", "Name")  // ["u.id", "u.name"]
	Cols(fieldNames ...string) []string

	// All returns vendor-quoted column names for all columns in the struct,
	// in the order they were declared in the struct definition.
	//
	// Example:
	//   cols.All()        // ["id", "name", "email"] (unaliased)
	//   cols.As("u").All() // ["u.id", "u.name", "u.email"] (aliased)
	All() []string

	// FieldMap extracts field values from a struct instance into a map.
	// The map keys are vendor-quoted column names (respecting alias if set).
	// Only fields with `db` tags are included. Zero values are included.
	//
	// Map iteration order is UNSPECIFIED, so building DML straight from this map
	// emits a different column order per process. Sort the keys, or use AllFields,
	// which returns columns and values in declaration order (#1157).
	//
	// Example:
	//   user := User{ID: 123, Name: "Alice", Email: "alice@example.com"}
	//   cols.FieldMap(&user)
	//   // Returns: {"id": 123, "name": "Alice", "email": "alice@example.com"}
	//
	//   cols.As("u").FieldMap(&user)
	//   // Returns: {"u.id": 123, "u.name": "Alice", "u.email": "alice@example.com"}
	//
	// Panics if instance is not a struct or pointer to struct.
	FieldMap(instance any) map[string]any

	// AllFields extracts all field values from a struct instance as separate slices.
	// Returns (columns, values) suitable for bulk INSERT/UPDATE operations.
	// Only fields with `db` tags are included. Zero values are included.
	//
	// Example:
	//   user := User{ID: 123, Name: "Alice", Email: "alice@example.com"}
	//   cols, vals := cols.AllFields(&user)
	//   // cols: ["id", "name", "email"]
	//   // vals: [123, "Alice", "alice@example.com"]
	//
	// Panics if instance is not a struct or pointer to struct.
	AllFields(instance any) ([]string, []any)
}

type DeleteQueryBuilder interface {
	// Filtering
	Where(filter Filter) DeleteQueryBuilder

	// Batch operations
	Limit(limit uint64) DeleteQueryBuilder
	OrderBy(orderBys ...string) DeleteQueryBuilder

	// SQL generation
	ToSQL() (sql string, args []any, err error)
}

type Filter interface {
	squirrel.Sqlizer

	// ToSQL is a convenience method with idiomatic Go naming.
	// It should delegate to ToSql() in implementations.
	ToSQL() (sql string, args []any, err error)
}

type FilterFactory interface {
	// Comparison operators
	Eq(column string, value any) Filter
	NotEq(column string, value any) Filter
	Lt(column string, value any) Filter
	Lte(column string, value any) Filter
	Gt(column string, value any) Filter
	Gte(column string, value any) Filter
	In(column string, values any) Filter
	NotIn(column string, values any) Filter
	Like(column, pattern string) Filter
	Null(column string) Filter
	NotNull(column string) Filter
	Between(column string, lowerBound, upperBound any) Filter

	// Regex matching (vendor-specific):
	//   PostgreSQL: ~ (CS), ~* (CI), !~ (NOT CS), !~* (NOT CI)
	//   Oracle:     REGEXP_LIKE(col, pat[, 'i']), optionally negated with NOT
	Regex(column, pattern string) Filter
	RegexI(column, pattern string) Filter
	NotRegex(column, pattern string) Filter
	NotRegexI(column, pattern string) Filter

	// JSONContains tests JSON containment (PostgreSQL @>). Currently
	// PostgreSQL-only; calling on Oracle yields an error filter.
	JSONContains(column string, value any) Filter

	// Logical operators
	And(filters ...Filter) Filter
	Or(filters ...Filter) Filter
	Not(filter Filter) Filter

	// Raw escape hatch
	Raw(condition string, args ...any) Filter

	// Subquery support (v2.1+)
	Exists(subquery SelectQueryBuilder) Filter
	NotExists(subquery SelectQueryBuilder) Filter
	InSubquery(column string, subquery SelectQueryBuilder) Filter
}

type InsertQueryBuilder interface {
	// Columns passes names through unchanged. For Oracle reserved-word quoting,
	// use qb.InsertWithColumns or pre-quote via a Columns helper.
	Columns(columns ...string) InsertQueryBuilder

	// Values and SetMap parameterize every value except a RawExpression, which
	// is validated and spliced inline; an alias on it is a ToSQL() error naming
	// the column or one-based position (#1318). See RawExpression.
	Values(values ...any) InsertQueryBuilder
	SetMap(clauses map[string]any) InsertQueryBuilder
	Options(options ...string) InsertQueryBuilder
	Prefix(sql string, args ...any) InsertQueryBuilder
	Suffix(sql string, args ...any) InsertQueryBuilder

	// Select returns an error (deferred to ToSQL()) if sb is not the concrete *SelectQueryBuilder from this package.
	Select(sb SelectQueryBuilder) InsertQueryBuilder

	ToSQL() (sql string, args []any, err error)
}

type Interface interface {
	// Querier provides core query execution operations
	Querier

	// Transactor provides transaction management operations
	Transactor

	// Prepared statements
	Prepare(ctx context.Context, query string) (Statement, error)

	// Health and diagnostics
	Health(ctx context.Context) error
	Stats() (map[string]any, error)

	// Session acquires a handle pinned to a single physical connection, for
	// session-scoped state a pooled statement can silently lose. See Session
	// for the error, concurrency and lifetime contract.
	Session(ctx context.Context) (Session, error)

	// Connection management
	Close() error

	// Migration support
	MigrationTable() string
	CreateMigrationTable(ctx context.Context) error
}

type InvalidAliasError struct {
	// Alias is the refused alias, exactly as it was passed.
	Alias string
}

type JoinFilter interface {
	squirrel.Sqlizer

	// ToSQL is a convenience method with idiomatic Go naming.
	// It should delegate to ToSql() in implementations.
	ToSQL() (sql string, args []any, err error)
}

type JoinFilterFactory interface {
	// Column-to-column comparison operators
	EqColumn(leftColumn, rightColumn string) JoinFilter
	NotEqColumn(leftColumn, rightColumn string) JoinFilter
	LtColumn(leftColumn, rightColumn string) JoinFilter
	LteColumn(leftColumn, rightColumn string) JoinFilter
	GtColumn(leftColumn, rightColumn string) JoinFilter
	GteColumn(leftColumn, rightColumn string) JoinFilter

	// Column-to-value comparison operators (v2.2+)
	// These methods accept any value type, including RawExpression for complex SQL.
	// Regular values generate placeholders; RawExpression values are inserted verbatim.
	Eq(column string, value any) JoinFilter
	NotEq(column string, value any) JoinFilter
	Lt(column string, value any) JoinFilter
	Lte(column string, value any) JoinFilter
	Gt(column string, value any) JoinFilter
	Gte(column string, value any) JoinFilter
	In(column string, values any) JoinFilter
	NotIn(column string, values any) JoinFilter
	Like(column, pattern string) JoinFilter
	Null(column string) JoinFilter
	NotNull(column string) JoinFilter
	Between(column string, lowerBound, upperBound any) JoinFilter

	// Logical operators for complex JOIN conditions
	And(filters ...JoinFilter) JoinFilter
	Or(filters ...JoinFilter) JoinFilter

	// Raw escape hatch for complex JOIN conditions
	Raw(condition string, args ...any) JoinFilter
}

type Querier interface {
	// Query executes a SQL query that returns rows, typically a SELECT statement.
	// The caller is responsible for closing the returned rows.
	//
	// The query should use vendor-specific placeholders:
	//   - PostgreSQL: $1, $2, $3
	//   - Oracle: :1, :2, :3
	//
	// For vendor-agnostic query construction, use the QueryBuilder.
	Query(ctx context.Context, query string, args ...any) (*sql.Rows, error)

	// QueryRow executes a SQL query that is expected to return at most one row.
	// QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called.
	//
	// For vendor-agnostic query construction, use the QueryBuilder.
	QueryRow(ctx context.Context, query string, args ...any) Row

	// Exec executes a SQL statement that doesn't return rows, typically INSERT, UPDATE, or DELETE.
	// The returned sql.Result provides RowsAffected and LastInsertId (if supported by the vendor).
	//
	// For vendor-agnostic query construction, use the QueryBuilder.
	Exec(ctx context.Context, query string, args ...any) (sql.Result, error)

	// DatabaseType returns the vendor identifier for this database connection.
	// Valid values are defined as constants: PostgreSQL, Oracle.
	//
	// This is included in Querier (despite being metadata) because the query builder
	// requires vendor information for placeholder generation and identifier quoting.
	// Including it here prevents forcing all test mocks to implement additional interfaces.
	DatabaseType() string
}

type QueryBuilderInterface interface {
	// Vendor information
	Vendor() string

	// Filter factories
	Filter() FilterFactory
	JoinFilter() JoinFilterFactory

	// Expression builder (v2.1+)
	// Returns error if SQL is empty, too many aliases provided, or alias contains dangerous characters.
	// Call sites carry the SECURITY annotation; see RawExpression.
	Expr(sql string, alias ...string) (RawExpression, error)

	// MustExpr is like Expr but panics on error.
	// Use this only in static initialization or tests where errors indicate programming bugs.
	// Call sites carry the SECURITY annotation; see RawExpression.
	MustExpr(sql string, alias ...string) RawExpression

	// Column metadata extraction (v2.4+)
	// Extracts column metadata from structs with `db:"column_name"` tags.
	// Lazily parses struct on first use, caches forever.
	// Returns vendor-specific quoted column names (e.g., Oracle reserved words).
	//
	// Example:
	//   type User struct {
	//       ID    int64  `db:"id"`
	//       Level string `db:"level"` // Oracle reserved word
	//   }
	//   cols := qb.Columns(&User{})
	//   qb.Select(cols.Col("ID"), cols.Col("Level")).From("users")
	//
	//   // With aliasing:
	//   u := cols.As("u")
	//   qb.Select(u.Col("ID"), u.Col("Level")).From(Table("users").As("u"))
	//
	// Panics if structPtr is not a pointer to a struct with db tags.
	Columns(structPtr any) Columns

	// Query builders
	Select(columns ...any) SelectQueryBuilder
	Insert(table string) InsertQueryBuilder
	InsertWithColumns(table string, columns ...string) InsertQueryBuilder

	// Struct-based INSERT (v2.4+)
	// InsertStruct extracts all fields from a struct instance and creates an INSERT query.
	// Zero-value ID fields (int64/string) are automatically excluded to support auto-increment.
	InsertStruct(table string, instance any) InsertQueryBuilder

	// InsertFields extracts only specified fields from a struct instance for INSERT.
	// Useful for partial inserts or when you need explicit control over included fields.
	InsertFields(table string, instance any, fields ...string) InsertQueryBuilder

	Update(table string) UpdateQueryBuilder
	Delete(table string) DeleteQueryBuilder

	// Vendor-specific helpers
	BuildCaseInsensitiveLike(column, value string) squirrel.Sqlizer

	// BuildUpsert requires a non-empty conflictColumns that names no column twice
	// and whose every entry also names a column of insertColumns, on every vendor;
	// a call violating any of those returns an error before any SQL is produced.
	// Both questions — whether two entries are the same column, and whether a
	// conflict column is among the inserted ones — are decided by the column each
	// key NAMES, not by how it is spelled. On Oracle that is the name the database
	// stores: a key the builder emits unquoted folds to upper case, while one it
	// emits quoted (a caller-quoted key, or a reserved word) is taken verbatim. So
	// id, ID and "ID" are one column there — a case variant is a duplicate and also
	// satisfies membership, and so does a quoted spelling of the same name — while
	// "id" is a second column and level and LEVEL stay two, both being quoted.
	// PostgreSQL quotes every identifier, so a case variant is never a duplicate
	// there: it is a second column, a legitimate composite conflict target, and not
	// a match for an insert key spelled differently.
	//
	// insertColumns and updateColumns must each name every column at most once,
	// judged by that same vendor identity rule. A map cannot hold an exact repeat,
	// but two keys can still name one column: on Oracle {"id": 1, "ID": 2} is one
	// column written twice and is rejected, while on PostgreSQL it is two columns
	// and builds. Every key is TRIMMED before it is judged, and the trimmed
	// spelling is what the statement renders, so {"id": 1, " id ": 2} names one
	// column on both vendors and is rejected on both, naming both spellings.
	//
	// ONE acceptance rule decides what a key may be, on every vendor: a single
	// column name — no qualifier, no function call, no empty name — carrying no
	// quote of its own beyond a plain wrapping pair. COUNT(*), t.name, a""b and
	// "a""b" are therefore refused on PostgreSQL as well as on Oracle. An
	// identifier argument carries no escaping beyond the accepted wrapping quote
	// pair; the door quotes. A column whose name
	// genuinely holds a quote has no key form here at all — conflictColumns is
	// []string and both maps are keyed by string, so there is no expression key to
	// pass; such a schema needs a hand-written statement. A column literally named
	// count(*) is unaffected: it stays reachable as the quoted key "count(*)" — PostgreSQL used to refuse every quoted key and now takes them
	// like Oracle, bounded by that same no-interior-quote rule. The rule is Oracle's grammar
	// in origin — its MERGE names conflict and insert keys as column aliases in
	// the USING clause and in the INSERT list, neither of which admits anything
	// else — and PostgreSQL applies it so that one spelling of a column works
	// everywhere the call names it. Oracle update keys become UPDATE SET targets,
	// where Oracle itself would also accept an alias-qualified one; refusing those
	// is this API's restriction, for the same reason.
	//
	// BuildUpsert rejects a column present in both conflictColumns and
	// updateColumns on every vendor, because Oracle's MERGE cannot update a
	// column referenced in its ON clause (ORA-38104). Identity is the same rule the
	// checks above use — the column each key names, so on Oracle conflict "ID" and
	// update id collide, while PostgreSQL keeps every spelling distinct. When its update value equals
	// its insert value, drop it from updateColumns and no column value changes:
	// the conflict match pins it on a matched row and the INSERT supplies it on an
	// unmatched one. Dropping the only update column empties the set, which builds
	// DO NOTHING (and omits Oracle's WHEN MATCHED arm), so a matched row is no
	// longer updated at all: its UPDATE triggers stop firing and RETURNING yields
	// no row. Where that matters keep a genuine non-conflict column, or issue the
	// UPDATE explicitly under the same transaction and locking rule as below.
	// When the two values differ, the call was rewriting the
	// conflict column itself, which no vendor-portable upsert can express — issue
	// a separate UPDATE instead: in the same transaction as the insert, keyed on
	// the conflict columns, and holding the row lock the single statement took for
	// you (SELECT ... FOR UPDATE or equivalent). Splitting one atomic upsert into
	// two statements lets a concurrent writer interleave, and under READ COMMITTED
	// a shared transaction alone does not stop it.
	//
	// Precondition failures match the ErrUpsert* sentinels in this package via errors.Is.
	BuildUpsert(table string, conflictColumns []string, insertColumns, updateColumns map[string]any) (query string, args []any, err error)

	// Database function builders
	BuildCurrentTimestamp() string
	BuildUUIDGeneration() string
	BuildBooleanValue(value bool) any

	// Identifier escaping
	EscapeIdentifier(identifier string) string
}

type RawExpression struct {
	SQL   string // The raw SQL expression
	Alias string // Optional alias (AS clause)
}

type Row interface {
	Scan(dest ...any) error
	Err() error
}

type SelectQueryBuilder interface {
	// Core SELECT builder methods
	// From accepts either string table names or *TableRef instances with optional aliases.
	// String table identifiers are validated before interpolation (see ADR-031).
	From(from ...any) SelectQueryBuilder

	// Type-safe JOIN methods with JoinFilter (v2.0+)
	// Each accepts either string table name or *TableRef instance with optional alias
	JoinOn(table any, filter JoinFilter) SelectQueryBuilder
	LeftJoinOn(table any, filter JoinFilter) SelectQueryBuilder
	RightJoinOn(table any, filter JoinFilter) SelectQueryBuilder
	InnerJoinOn(table any, filter JoinFilter) SelectQueryBuilder
	CrossJoinOn(table any) SelectQueryBuilder

	GroupBy(groupBys ...any) SelectQueryBuilder

	// Having adds a HAVING predicate. Prefer a RawExpression from qb.Expr() —
	// Having(qb.MustExpr("SUM(amount) > ?"), 100), or qb.Expr when you handle its
	// (RawExpression, error) return — which is the expression form for
	// the aggregate comparisons HAVING exists for; an alias on that expression is
	// an error (ErrAliasInHaving), since a predicate projects nothing. Both forms are
	// raw SQL needing the SECURITY annotation at every call site; see dbtypes.RawExpression.
	Having(pred any, rest ...any) SelectQueryBuilder
	OrderBy(orderBys ...any) SelectQueryBuilder
	Limit(limit uint64) SelectQueryBuilder
	Offset(offset uint64) SelectQueryBuilder
	Paginate(limit, offset uint64) SelectQueryBuilder

	// ForUpdate appends `FOR UPDATE`, rendered after pagination; ForUpdateNoWait
	// appends `FOR UPDATE NOWAIT` instead; the last call wins. On Oracle a lock
	// combined with Limit/Offset fails ToSQL with ErrRowLockWithPagination
	// (row_limiting_clause restriction). When to use which, the transaction
	// requirement and the error to match are in wiki/database.md ("Row locks").
	ForUpdate() SelectQueryBuilder
	ForUpdateNoWait() SelectQueryBuilder

	// SubqueryColumn appends `(sub) AS alias` to the projection — one scalar
	// subquery per result column, the shape a stats snapshot takes in one round
	// trip. sub is validated like an EXISTS subquery (ValidateSubquery) and its
	// placeholders are renumbered with the outer statement's; alias must be an
	// unquoted identifier (ErrInvalidAlias). A sub carrying a row lock is
	// refused. On Oracle a SELECT with no From renders `FROM dual`.
	SubqueryColumn(sub SelectQueryBuilder, alias string) SelectQueryBuilder

	// Composable WHERE clause
	Where(filter Filter) SelectQueryBuilder

	// SQL generation
	ToSQL() (sql string, args []any, err error)
}

type Session interface {
	Querier
	Transactor

	// Close releases the pinned physical connection back to the pool.
	Close() error
}

type Statement interface {
	// Query execution
	Query(ctx context.Context, args ...any) (*sql.Rows, error)
	QueryRow(ctx context.Context, args ...any) Row
	Exec(ctx context.Context, args ...any) (sql.Result, error)

	// Statement management
	Close() error
}

type TableRef struct {
	// contains filtered or unexported fields
}

type Transactor interface {
	// Begin starts a new transaction with default isolation level.
	// The returned Tx must be committed or rolled back to release resources.
	//
	// Common usage pattern:
	//   tx, err := db.Begin(ctx)
	//   if err != nil { return err }
	//   defer tx.Rollback(ctx)  // No-op if already committed
	//   // ... execute operations on tx ...
	//   return tx.Commit(ctx)
	Begin(ctx context.Context) (Tx, error)

	// BeginTx starts a new transaction with explicit isolation level and read-only settings.
	// Use this when you need precise control over transaction behavior.
	//
	// Common isolation levels (from database/sql):
	//   - sql.LevelDefault: Use database's default isolation
	//   - sql.LevelReadUncommitted: Lowest isolation, allows dirty reads
	//   - sql.LevelReadCommitted: Prevents dirty reads (PostgreSQL default)
	//   - sql.LevelRepeatableRead: Prevents dirty and non-repeatable reads
	//   - sql.LevelSerializable: Highest isolation, full transaction isolation
	//
	// Example (read-only transaction for complex reporting):
	//   tx, err := db.BeginTx(ctx, &sql.TxOptions{
	//       Isolation: sql.LevelRepeatableRead,
	//       ReadOnly:  true,
	//   })
	//
	// Note: Not all databases support all isolation levels. Consult vendor documentation.
	BeginTx(ctx context.Context, opts *sql.TxOptions) (Tx, error)
}

type Tx interface {
	// Query execution within transaction
	Query(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRow(ctx context.Context, query string, args ...any) Row
	Exec(ctx context.Context, query string, args ...any) (sql.Result, error)

	// Prepared statements within transaction
	Prepare(ctx context.Context, query string) (Statement, error)

	// Transaction control
	// Note: Context is passed to support proper cancellation and tracing.
	// The context should be the same one used to begin the transaction.
	Commit(ctx context.Context) error
	Rollback(ctx context.Context) error
}

type UpdateQueryBuilder interface {
	// Set and SetMap parameterize every value except a RawExpression, which is
	// validated and spliced inline; an alias on it is a ToSQL() error naming the
	// column (#1318). See RawExpression.
	Set(column string, value any) UpdateQueryBuilder
	SetMap(clauses map[string]any) UpdateQueryBuilder

	// SetExpr assigns a RawExpression that carries bound arguments —
	// `SetExpr("lease_until", qb.MustExpr("NOW() + (? * INTERVAL '1 second')"), secs)` —
	// which Set cannot: Set splices a RawExpression without arguments. The
	// expression's `?` placeholders are renumbered with the statement's. This is
	// a raw-SQL door on par with f.Raw: the SQL body is never validated, so every
	// call site carries the inline `// SECURITY: Manual SQL review completed -
	// <what was verified>` annotation. An alias on expr is a ToSQL() error.
	SetExpr(column string, expr RawExpression, args ...any) UpdateQueryBuilder

	// Struct-based UPDATE (v2.4+)
	// SetStruct extracts field values from a struct instance for UPDATE.
	// If fields are specified, only those fields are updated.
	// If no fields specified, all struct fields are updated.
	SetStruct(instance any, fields ...string) UpdateQueryBuilder

	// Filtering
	Where(filter Filter) UpdateQueryBuilder

	// SQL generation
	ToSQL() (sql string, args []any, err error)
}

type Vendor = string