// Package dbquery is the one read-only query runner `carlos db query` uses:
// on a member's machine (the CLI, db.go) and inside the db-read worker on a
// box (internal/dbreads, spec 2026-10-22 §8.3). It moved out of db.go so the
// guards, the open flags and the output format cannot drift between the two.
package dbquery

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"io"
	"strings"
	"time"

	"modernc.org/sqlite"
	sqlitelib "modernc.org/sqlite/lib"
)

// Options are the only difference between the two callers. Immutable adds
// immutable=1 to the open: the worker's copy is a file nothing else ever
// writes (spec §8.3).
type Options struct {
	Immutable bool
}

// ErrOneStatement and ErrPragmaChange are query's two refusals before SQLite
// sees anything. Their text is the CLI's, unchanged.
var (
	ErrOneStatement = errors.New("carlos db query runs one statement at a time. Run each statement on its own")
	ErrPragmaChange = errors.New("this pragma changes how the copy is read, so carlos db query does not run it. Reading a pragma's value is fine")
)

// dsn is the open string. temp_store(2): sorts and temp b-trees stay in
// memory, so a large query cannot spill plaintext pages to a file outside the
// private directory.
func dsn(path string, opt Options) string {
	mode := "mode=ro"
	if opt.Immutable {
		mode = "mode=ro&immutable=1"
	}
	return "file:" + path + "?" + mode + "&_pragma=query_only(1)&_pragma=busy_timeout(5000)&_pragma=temp_store(2)"
}

// RunContext opens path read-only, both ways (mode=ro and query_only, db
// spec §4), runs sqlText, and writes a header row then one tab-separated
// line per row. A write fails with SQLite's own "attempt to write a readonly
// database", returned as it is: the error is the truth. It is db.go's
// runQueryContext with the open string from dsn.
func RunContext(ctx context.Context, w io.Writer, path, sqlText string, opt Options) error {
	if err := CheckSQL(sqlText); err != nil {
		return err
	}
	db, err := sql.Open("sqlite", dsn(path, opt))
	if err != nil {
		return err
	}
	defer db.Close()
	conn, err := db.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn.Close()
	// mode=ro covers only main, and query_only is a pragma the statement
	// list itself can switch off. ATTACH opens with the connection's
	// read-write flags, so it is the way around both: a second name for the
	// copy that writes silently, or a new file anywhere the user can write.
	// VACUUM INTO attaches internally. No attached databases at all closes
	// every one of those.
	if _, err := sqlite.Limit(conn, sqlitelib.SQLITE_LIMIT_ATTACHED, 0); err != nil {
		return err
	}
	rows, err := conn.QueryContext(ctx, sqlText)
	if err != nil {
		return err
	}
	defer rows.Close()
	cols, err := rows.Columns()
	if err != nil {
		return err
	}
	if len(cols) > 0 {
		if _, err := fmt.Fprintln(w, strings.Join(cols, "\t")); err != nil {
			return err
		}
	}
	vals := make([]any, len(cols))
	ptrs := make([]any, len(cols))
	for i := range vals {
		ptrs[i] = &vals[i]
	}
	line := make([]string, len(cols))
	for rows.Next() {
		if err := rows.Scan(ptrs...); err != nil {
			return err
		}
		for i, v := range vals {
			line[i] = Cell(v)
		}
		// A reader that went away (EPIPE, once SIGPIPE is caught) ends the
		// query here rather than after every remaining row.
		if _, err := fmt.Fprintln(w, strings.Join(line, "\t")); err != nil {
			return err
		}
	}
	return rows.Err()
}

// Cell renders one value. NULL is spelled out so it cannot be mistaken for
// an empty string; tabs and newlines inside a value are escaped so a row
// stays one line.
func Cell(v any) string {
	var s string
	switch x := v.(type) {
	case nil:
		return "NULL"
	case []byte:
		s = string(x)
	case time.Time:
		s = x.UTC().Format(time.RFC3339Nano)
	default:
		s = fmt.Sprint(x)
	}
	return strings.NewReplacer("\\", `\\`, "\t", `\t`, "\n", `\n`, "\r", `\r`).Replace(s)
}

// dbReadPragmas are the pragmas that may take an argument here: each reads
// the schema or checks the file, and none changes the connection. Any
// other pragma may be read bare but not given a value.
var dbReadPragmas = map[string]bool{
	"table_info": true, "table_xinfo": true, "table_list": true,
	"index_info": true, "index_xinfo": true, "index_list": true,
	"foreign_key_list": true, "foreign_key_check": true,
	"integrity_check": true, "quick_check": true,
}

// CheckSQL is the guard modernc.org/sqlite v1.55.0 leaves to its
// caller: it exposes no authorizer and no sqlite3_stmt_readonly, and it runs
// every statement in a list. mode=ro, query_only and the attach limit hold
// for any ONE statement, so the rule is one statement, and no pragma that
// changes the connection (query_only, temp_store, temp_store_directory,
// writable_schema and the rest), since a pragma is the one statement that
// can loosen what follows it.
//
// The pragma rule reads the statement's tokens, not its raw prefix: SQLite
// skips byte-order marks, comments and whitespace anywhere, and applies a
// pragma at prepare even under EXPLAIN. So when the statement is a PRAGMA
// (first token, or after EXPLAIN [QUERY PLAN]), the pragma it names may be
// given a value only if it is a read-only one, and only in the (argument)
// form. Elsewhere "pragma" is an identifier and a read may use it.
func CheckSQL(q string) error {
	_, rest := splitFirstStatement(q)
	if len(sqlTokens(rest)) > 0 {
		return ErrOneStatement
	}
	toks := sqlTokens(q)
	// A pragma is a statement, so it can only stand in statement position:
	// first, or after EXPLAIN [QUERY PLAN]. Anywhere else "pragma" is an
	// ordinary identifier (a column, an alias). One statement is enforced
	// above, so there is no later statement position to check.
	i := 0
	if i < len(toks) && toks[i].kind == sqlWord && toks[i].text == "explain" {
		i++
		if i+1 < len(toks) && toks[i].kind == sqlWord && toks[i].text == "query" &&
			toks[i+1].kind == sqlWord && toks[i+1].text == "plan" {
			i += 2
		}
	}
	if i < len(toks) && toks[i].kind == sqlWord && toks[i].text == "pragma" {
		// PRAGMA [schema .] name [= value | (value)]
		j := i + 1
		name := ""
		if j < len(toks) && toks[j].kind != sqlPunct {
			name = toks[j].text
			j++
		}
		if j+1 < len(toks) && toks[j].kind == sqlPunct && toks[j].text == "." && toks[j+1].kind != sqlPunct {
			name = toks[j+1].text
			j += 2
		}
		if j >= len(toks) || toks[j].kind != sqlPunct || (toks[j].text != "=" && toks[j].text != "(") {
			return nil // a bare read of the pragma's value
		}
		if toks[j].text == "(" && dbReadPragmas[name] {
			return nil
		}
		return ErrPragmaChange
	}
	return nil
}

type sqlTokenKind int

const (
	sqlWord   sqlTokenKind = iota // keyword or bare identifier, lower case
	sqlQuoted                     // string literal or quoted identifier, unquoted, lower case
	sqlPunct                      // any other single byte
)

type sqlToken struct {
	kind sqlTokenKind
	text string
}

const utf8BOM = "\uFEFF"

// sqlTokens splits q the way SQLite's tokenizer does, for the guard's
// purposes: comments and whitespace (space, tab, newline, carriage return,
// form feed, vertical tab, and a byte-order mark anywhere) separate tokens
// and vanish; string literals and quoted identifiers ('...', "...", `...`,
// [...]) are one token each; runs of identifier bytes are words; anything
// else is one punctuation byte.
func sqlTokens(q string) []sqlToken {
	var out []sqlToken
	for i := 0; i < len(q); {
		c := q[i]
		switch {
		case strings.HasPrefix(q[i:], utf8BOM):
			i += len(utf8BOM)
		case c == ' ' || c == '\t' || c == '\n' || c == '\r' || c == '\f' || c == '\v':
			i++
		case c == '-' && i+1 < len(q) && q[i+1] == '-':
			if j := strings.IndexByte(q[i:], '\n'); j >= 0 {
				i += j + 1
			} else {
				i = len(q)
			}
		case c == '/' && i+1 < len(q) && q[i+1] == '*':
			if j := strings.Index(q[i+2:], "*/"); j >= 0 {
				i += j + 4
			} else {
				i = len(q)
			}
		case c == '\'' || c == '"' || c == '`':
			end := min(skipQuoted(q, i, c), len(q))
			body := strings.ReplaceAll(q[i+1:end], string([]byte{c, c}), string(c))
			out = append(out, sqlToken{sqlQuoted, strings.ToLower(body)})
			i = end + 1
		case c == '[':
			j := strings.IndexByte(q[i:], ']')
			if j < 0 {
				j = len(q) - i
			}
			out = append(out, sqlToken{sqlQuoted, strings.ToLower(q[i+1 : i+j])})
			i += j + 1
		case isSQLIdentByte(c):
			j := i
			for j < len(q) && isSQLIdentByte(q[j]) && !strings.HasPrefix(q[j:], utf8BOM) {
				j++
			}
			out = append(out, sqlToken{sqlWord, strings.ToLower(q[i:j])})
			i = j
		default:
			out = append(out, sqlToken{sqlPunct, string(c)})
			i++
		}
	}
	return out
}

// isSQLIdentByte reports a byte that can continue an unquoted identifier:
// ASCII letters, digits, _ and $, and any byte of a multi-byte UTF-8
// character (the byte-order mark is checked for before this).
func isSQLIdentByte(c byte) bool {
	return c == '_' || c == '$' || c >= 'a' && c <= 'z' || c >= 'A' && c <= 'Z' || c >= '0' && c <= '9' || c >= 0x80
}

// splitFirstStatement returns the text up to and including the first
// top-level semicolon, and everything after it. Semicolons inside string
// literals, quoted identifiers and comments do not count.
func splitFirstStatement(q string) (first, rest string) {
	for i := 0; i < len(q); i++ {
		switch c := q[i]; c {
		case '\'', '"', '`':
			i = skipQuoted(q, i, c)
		case '[':
			if j := strings.IndexByte(q[i:], ']'); j >= 0 {
				i += j
			} else {
				i = len(q)
			}
		case '-':
			if i+1 < len(q) && q[i+1] == '-' {
				if j := strings.IndexByte(q[i:], '\n'); j >= 0 {
					i += j
				} else {
					i = len(q)
				}
			}
		case '/':
			if i+1 < len(q) && q[i+1] == '*' {
				if j := strings.Index(q[i+2:], "*/"); j >= 0 {
					i += j + 3
				} else {
					i = len(q)
				}
			}
		case ';':
			return q[:i+1], q[i+1:]
		}
	}
	return q, ""
}

// skipQuoted returns the index of the quote closing the one at i, where a
// doubled quote is an escaped one, or len(q) when it never closes.
func skipQuoted(q string, i int, quote byte) int {
	for j := i + 1; j < len(q); j++ {
		if q[j] == quote {
			if j+1 < len(q) && q[j+1] == quote {
				j++
				continue
			}
			return j
		}
	}
	return len(q)
}