package bjc.utils.misc;

import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * NamedPreparedStatement
 *
 * Features:
 *  - :name singles; @name lists (Collections or var-args; optional explicit SQL type)
 *  - empty-list toggle: emptyListAsNull() [default] or emptyListAsLiteral("...")
 *  - compile() for eager build-and-bind on the simple (non-Executor) path
 *  - Args: immutable parameter bag you can build on worker threads.
 *  - Executor: compile-once, bind-many runner that reuses a single PreparedStatement
 *              for many Args that share the same "shape" (same @list sizes / emptiness).
 */
public final class NamedPreparedStatement implements AutoCloseable {
	/* ---------- Construction (simple path) ---------- */

	private final Connection conn;
	private final List<Token> tokens;
	private final String originalSql;

	private final Integer autoGeneratedKeys;
	private final Integer resultSetType;
	private final Integer resultSetConcurrency;

	private PreparedStatement currentPs;
	private String lastCompiledSql;

	private final Map<String, Binder> singles = new HashMap<>();
	private final Map<String, List<Binder>> lists = new HashMap<>();

	private final List<ParamSnapshot> batch = new ArrayList<>();

	private EmptyListMode emptyListMode = EmptyListMode.AS_NULL;
	private String emptyListLiteral = "NULL";

	private NamedPreparedStatement(Connection conn, String sql, Integer autoGeneratedKeys, Integer resultSetType,
			Integer resultSetConcurrency) {
		this.conn = Objects.requireNonNull(conn, "conn");
		this.originalSql = Objects.requireNonNull(sql, "sql");
		this.tokens = parse(sql);
		this.autoGeneratedKeys = autoGeneratedKeys;
		this.resultSetType = resultSetType;
		this.resultSetConcurrency = resultSetConcurrency;
	}

	public static NamedPreparedStatement prepare(Connection conn, String sql) {
		return new NamedPreparedStatement(conn, sql, null, null, null);
	}

	public static NamedPreparedStatement prepareWithKeys(Connection conn, String sql) {
		return new NamedPreparedStatement(conn, sql, Statement.RETURN_GENERATED_KEYS, null, null);
	}

	public static NamedPreparedStatement prepare(Connection conn, String sql, int resultSetType,
			int resultSetConcurrency) {
		return new NamedPreparedStatement(conn, sql, null, resultSetType, resultSetConcurrency);
	}

	/* ---------- Empty-list toggle (simple path) ---------- */

	public NamedPreparedStatement emptyListAsNull() {
		this.emptyListMode = EmptyListMode.AS_NULL;
		return this;
	}

	public NamedPreparedStatement emptyListAsLiteral(String literal) {
		if (literal == null || literal.isBlank())
			throw new IllegalArgumentException("Empty-list literal must be non-blank.");
		this.emptyListMode = EmptyListMode.AS_CUSTOM_LITERAL;
		this.emptyListLiteral = literal;
		return this;
	}

	/* ---------- Eager compile (simple path) ---------- */

	/** Build final SQL and bind parameters now, but do not execute. */
	public PreparedStatement compile() throws SQLException {
		buildAndBindSimple();
		return currentPs;
	}

	/* ---------- Execution (simple path) ---------- */

	public ResultSet executeQuery() throws SQLException {
		buildAndBindSimple();
		return currentPs.executeQuery();
	}

	public int executeUpdate() throws SQLException {
		buildAndBindSimple();
		return currentPs.executeUpdate();
	}

	public boolean execute() throws SQLException {
		buildAndBindSimple();
		return currentPs.execute();
	}

	public int[] executeBatch() throws SQLException {
		if (batch.isEmpty()) {
			buildAndBindSimple();
			return currentPs.executeBatch();
		}
		List<Integer> counts = new ArrayList<>(batch.size());
		for (ParamSnapshot snap : batch) {
			this.singles.clear();
			this.lists.clear();
			this.singles.putAll(snap.singles);
			this.lists.putAll(snap.lists);
			buildAndBindSimple();
			counts.add(currentPs.executeUpdate());
		}
		batch.clear();
		return counts.stream().mapToInt(Integer::intValue).toArray();
	}

	public NamedPreparedStatement addBatch() {
		batch.add(new ParamSnapshot(new HashMap<>(singles), deepCopyLists(lists)));
		return this;
	}

	public NamedPreparedStatement clearParameters() {
		singles.clear();
		lists.clear();
		return this;
	}

	public ResultSet getGeneratedKeys() throws SQLException {
		if (currentPs == null)
			throw new IllegalStateException("No statement compiled/executed yet.");
		return currentPs.getGeneratedKeys();
	}

	public PreparedStatement getPreparedStatement() {
		return currentPs;
	}

	public String getParsedSql() {
		return lastCompiledSql;
	}

	public String getOriginalSql() {
		return originalSql;
	}

	public Set<String> getParameterNames() {
		LinkedHashSet<String> names = new LinkedHashSet<>();
		for (Token t : tokens)
			if (t instanceof ParamToken pt)
				names.add(pt.name);
		return Collections.unmodifiableSet(names);
	}

	@Override
	public void close() throws SQLException {
		if (currentPs != null)
			currentPs.close();
	}

	@Override
	public String toString() {
		return "NamedPreparedStatement{sql='" + (lastCompiledSql != null ? lastCompiledSql : originalSql) + "'}";
	}

	/* ---------- Binding (single path) ---------- */

	public NamedPreparedStatement setObject(String name, Object value) {
		singles.put(name, (ps, i) -> ps.setObject(i, value));
		return this;
	}

	public NamedPreparedStatement setObject(String name, Object value, int sqlType) {
		singles.put(name, (ps, i) -> {
			if (value == null)
				ps.setNull(i, sqlType);
			else
				ps.setObject(i, value, sqlType);
		});
		return this;
	}

	public NamedPreparedStatement setNull(String name, int sqlType) {
		singles.put(name, (ps, i) -> ps.setNull(i, sqlType));
		return this;
	}

	public NamedPreparedStatement setBoolean(String n, boolean x) {
		singles.put(n, (ps, i) -> ps.setBoolean(i, x));
		return this;
	}

	public NamedPreparedStatement setByte(String n, byte x) {
		singles.put(n, (ps, i) -> ps.setByte(i, x));
		return this;
	}

	public NamedPreparedStatement setShort(String n, short x) {
		singles.put(n, (ps, i) -> ps.setShort(i, x));
		return this;
	}

	public NamedPreparedStatement setInt(String n, int x) {
		singles.put(n, (ps, i) -> ps.setInt(i, x));
		return this;
	}

	public NamedPreparedStatement setLong(String n, long x) {
		singles.put(n, (ps, i) -> ps.setLong(i, x));
		return this;
	}

	public NamedPreparedStatement setFloat(String n, float x) {
		singles.put(n, (ps, i) -> ps.setFloat(i, x));
		return this;
	}

	public NamedPreparedStatement setDouble(String n, double x) {
		singles.put(n, (ps, i) -> ps.setDouble(i, x));
		return this;
	}

	public NamedPreparedStatement setBigDecimal(String n, BigDecimal x) {
		singles.put(n, (ps, i) -> ps.setBigDecimal(i, x));
		return this;
	}

	public NamedPreparedStatement setString(String n, String x) {
		singles.put(n, (ps, i) -> ps.setString(i, x));
		return this;
	}

	public NamedPreparedStatement setBytes(String n, byte[] x) {
		singles.put(n, (ps, i) -> ps.setBytes(i, x));
		return this;
	}

	public NamedPreparedStatement setDate(String n, Date x) {
		singles.put(n, (ps, i) -> ps.setDate(i, x));
		return this;
	}

	public NamedPreparedStatement setDate(String n, Date x, Calendar c) {
		singles.put(n, (ps, i) -> ps.setDate(i, x, c));
		return this;
	}

	public NamedPreparedStatement setTime(String n, Time x) {
		singles.put(n, (ps, i) -> ps.setTime(i, x));
		return this;
	}

	public NamedPreparedStatement setTime(String n, Time x, Calendar c) {
		singles.put(n, (ps, i) -> ps.setTime(i, x, c));
		return this;
	}

	public NamedPreparedStatement setTimestamp(String n, Timestamp x) {
		singles.put(n, (ps, i) -> ps.setTimestamp(i, x));
		return this;
	}

	public NamedPreparedStatement setTimestamp(String n, Timestamp x, Calendar c) {
		singles.put(n, (ps, i) -> ps.setTimestamp(i, x, c));
		return this;
	}

	public NamedPreparedStatement setArray(String n, Array x) {
		singles.put(n, (ps, i) -> ps.setArray(i, x));
		return this;
	}

	public NamedPreparedStatement setBlob(String n, Blob x) {
		singles.put(n, (ps, i) -> ps.setBlob(i, x));
		return this;
	}

	public NamedPreparedStatement setClob(String n, Clob x) {
		singles.put(n, (ps, i) -> ps.setClob(i, x));
		return this;
	}

	public NamedPreparedStatement setURL(String n, java.net.URL x) {
		singles.put(n, (ps, i) -> ps.setURL(i, x));
		return this;
	}

	public NamedPreparedStatement setAsciiStream(String n, java.io.InputStream x, int len) {
		singles.put(n, (ps, i) -> ps.setAsciiStream(i, x, len));
		return this;
	}

	public NamedPreparedStatement setBinaryStream(String n, java.io.InputStream x, int len) {
		singles.put(n, (ps, i) -> ps.setBinaryStream(i, x, len));
		return this;
	}

	public NamedPreparedStatement setCharacterStream(String n, java.io.Reader r, int len) {
		singles.put(n, (ps, i) -> ps.setCharacterStream(i, r, len));
		return this;
	}

	public NamedPreparedStatement setList(String name, Collection<?> values) {
		lists.put(name, toBinders(values, Types.OTHER, false));
		return this;
	}

	public NamedPreparedStatement setList(String name, Object... values) {
		lists.put(name, toBinders(Arrays.asList(values), Types.OTHER, false));
		return this;
	}

	public NamedPreparedStatement setList(String name, int sqlType, Collection<?> values) {
		lists.put(name, toBinders(values, sqlType, true));
		return this;
	}

	public NamedPreparedStatement setList(String name, int sqlType, Object... values) {
		lists.put(name, toBinders(Arrays.asList(values), sqlType, true));
		return this;
	}

	/* ---------- Build & bind (simple path) ---------- */

	private void buildAndBindSimple() throws SQLException {
		StringBuilder sb = new StringBuilder(originalSql.length() + 32);
		List<Binder> bindersInOrder = new ArrayList<>(32);

		for (Token t : tokens) {
			if (t instanceof TextToken tt) {
				sb.append(tt.text);
			} else {
				ParamToken pt = (ParamToken) t;
				if (!pt.list) { // :name
					Binder b = singles.get(pt.name);
					if (b == null)
						throw new IllegalArgumentException("Missing value for parameter :" + pt.name);
					sb.append('?');
					bindersInOrder.add(b);
				} else { // @name
					List<Binder> lst = lists.get(pt.name);
					if (lst == null)
						throw new IllegalArgumentException("Missing list for parameter @" + pt.name);
					if (lst.isEmpty()) {
						if (emptyListMode == EmptyListMode.AS_NULL) {
							sb.append("NULL");
						} else {
							sb.append(emptyListLiteral);
						}
					} else {
						for (int i = 0; i < lst.size(); i++) {
							if (i > 0)
								sb.append(',');
							sb.append('?');
							bindersInOrder.add(lst.get(i));
						}
					}
				}
			}
		}

		this.lastCompiledSql = sb.toString();

		if (currentPs != null)
			try {
				currentPs.close();
			} catch (SQLException ignore) {
			}

		if (autoGeneratedKeys != null) {
			currentPs = conn.prepareStatement(lastCompiledSql, autoGeneratedKeys);
		} else if (resultSetType != null && resultSetConcurrency != null) {
			currentPs = conn.prepareStatement(lastCompiledSql, resultSetType, resultSetConcurrency);
		} else {
			currentPs = conn.prepareStatement(lastCompiledSql);
		}

		int idx = 1;
		for (Binder b : bindersInOrder)
			b.bind(currentPs, idx++);
	}

	/*
	 * ===================================================================== PLANNED
	 * / COMPILE-ONCE EXECUTION PATH (Args + Executor)
	 * =====================================================================
	 */

	/**
	 * Immutable bag of parameter values. Build these on any thread. They must all
	 * share the same "shape" (same @list sizes and emptiness) if you want to reuse
	 * a single Executor (no SQL rebuild).
	 */
	public static final class Args {
		private final Map<String, Binder> singles;
		private final Map<String, List<Binder>> lists;

		private Args(Map<String, Binder> s, Map<String, List<Binder>> l) {
			this.singles = Collections.unmodifiableMap(s);
			this.lists = unmodifiableListMap(l);
		}

		public static Builder builder() {
			return new Builder();
		}

		public static final class Builder {
			private final Map<String, Binder> singles = new HashMap<>();
			private final Map<String, List<Binder>> lists = new HashMap<>();

			public Builder setNull(String name, int sqlType) {
				singles.put(name, (ps, i) -> ps.setNull(i, sqlType));
				return this;
			}

			public Builder setObject(String name, Object value) {
				singles.put(name, (ps, i) -> ps.setObject(i, value));
				return this;
			}

			public Builder setObject(String name, Object value, int sqlType) {
				singles.put(name, (ps, i) -> {
					if (value == null)
						ps.setNull(i, sqlType);
					else
						ps.setObject(i, value, sqlType);
				});
				return this;
			}

			public Builder setBoolean(String n, boolean x) {
				singles.put(n, (ps, i) -> ps.setBoolean(i, x));
				return this;
			}

			public Builder setByte(String n, byte x) {
				singles.put(n, (ps, i) -> ps.setByte(i, x));
				return this;
			}

			public Builder setShort(String n, short x) {
				singles.put(n, (ps, i) -> ps.setShort(i, x));
				return this;
			}

			public Builder setInt(String n, int x) {
				singles.put(n, (ps, i) -> ps.setInt(i, x));
				return this;
			}

			public Builder setLong(String n, long x) {
				singles.put(n, (ps, i) -> ps.setLong(i, x));
				return this;
			}

			public Builder setFloat(String n, float x) {
				singles.put(n, (ps, i) -> ps.setFloat(i, x));
				return this;
			}

			public Builder setDouble(String n, double x) {
				singles.put(n, (ps, i) -> ps.setDouble(i, x));
				return this;
			}

			public Builder setBigDecimal(String n, BigDecimal x) {
				singles.put(n, (ps, i) -> ps.setBigDecimal(i, x));
				return this;
			}

			public Builder setString(String n, String x) {
				singles.put(n, (ps, i) -> ps.setString(i, x));
				return this;
			}

			public Builder setBytes(String n, byte[] x) {
				singles.put(n, (ps, i) -> ps.setBytes(i, x));
				return this;
			}

			public Builder setDate(String n, Date x) {
				singles.put(n, (ps, i) -> ps.setDate(i, x));
				return this;
			}

			public Builder setDate(String n, Date x, Calendar c) {
				singles.put(n, (ps, i) -> ps.setDate(i, x, c));
				return this;
			}

			public Builder setTime(String n, Time x) {
				singles.put(n, (ps, i) -> ps.setTime(i, x));
				return this;
			}

			public Builder setTime(String n, Time x, Calendar c) {
				singles.put(n, (ps, i) -> ps.setTime(i, x, c));
				return this;
			}

			public Builder setTimestamp(String n, Timestamp x) {
				singles.put(n, (ps, i) -> ps.setTimestamp(i, x));
				return this;
			}

			public Builder setTimestamp(String n, Timestamp x, Calendar c) {
				singles.put(n, (ps, i) -> ps.setTimestamp(i, x, c));
				return this;
			}

			public Builder setArray(String n, Array x) {
				singles.put(n, (ps, i) -> ps.setArray(i, x));
				return this;
			}

			public Builder setBlob(String n, Blob x) {
				singles.put(n, (ps, i) -> ps.setBlob(i, x));
				return this;
			}

			public Builder setClob(String n, Clob x) {
				singles.put(n, (ps, i) -> ps.setClob(i, x));
				return this;
			}

			public Builder setURL(String n, java.net.URL x) {
				singles.put(n, (ps, i) -> ps.setURL(i, x));
				return this;
			}

			public Builder setAsciiStream(String n, java.io.InputStream x, int len) {
				singles.put(n, (ps, i) -> ps.setAsciiStream(i, x, len));
				return this;
			}

			public Builder setBinaryStream(String n, java.io.InputStream x, int len) {
				singles.put(n, (ps, i) -> ps.setBinaryStream(i, x, len));
				return this;
			}

			public Builder setCharacterStream(String n, java.io.Reader r, int len) {
				singles.put(n, (ps, i) -> ps.setCharacterStream(i, r, len));
				return this;
			}

			public Builder setList(String name, Collection<?> values) {
				lists.put(name, toBinders(values, Types.OTHER, false));
				return this;
			}

			public Builder setList(String name, Object... values) {
				lists.put(name, toBinders(Arrays.asList(values), Types.OTHER, false));
				return this;
			}

			public Builder setList(String name, int sqlType, Collection<?> values) {
				lists.put(name, toBinders(values, sqlType, true));
				return this;
			}

			public Builder setList(String name, int sqlType, Object... values) {
				lists.put(name, toBinders(Arrays.asList(values), sqlType, true));
				return this;
			}

			public Args build() {
				return new Args(new HashMap<>(singles), deepCopyLists(lists));
			}
		}
	}

	/**
	 * Executor: compiles SQL once using the "shape" (list sizes / emptiness) of a
	 * prototype Args, creates one PreparedStatement, and reuses it to execute many
	 * Args with the same shape.
	 */
	public static final class Executor implements AutoCloseable {
		private final Connection conn;
		private final List<Token> tokens;
		private final String originalSql;
		private final EmptyListMode emptyMode;
		private final String emptyLiteral;

		private final Integer autoGeneratedKeys;
		private final Integer resultSetType;
		private final Integer resultSetConcurrency;

		private final Plan plan; // compiled once
		private final PreparedStatement ps; // prepared once

		private Executor(Connection conn, List<Token> tokens, String originalSql, EmptyListMode mode,
				String emptyLiteral, Integer autoGeneratedKeys, Integer resultSetType, Integer resultSetConcurrency,
				Args shapeArgs) throws SQLException {
			this.conn = conn;
			this.tokens = tokens;
			this.originalSql = originalSql;
			this.emptyMode = mode;
			this.emptyLiteral = emptyLiteral;
			this.autoGeneratedKeys = autoGeneratedKeys;
			this.resultSetType = resultSetType;
			this.resultSetConcurrency = resultSetConcurrency;

			this.plan = Plan.compile(tokens, shapeArgs, mode, emptyLiteral);
			if (autoGeneratedKeys != null) {
				this.ps = conn.prepareStatement(plan.compiledSql, autoGeneratedKeys);
			} else if (resultSetType != null && resultSetConcurrency != null) {
				this.ps = conn.prepareStatement(plan.compiledSql, resultSetType, resultSetConcurrency);
			} else {
				this.ps = conn.prepareStatement(plan.compiledSql);
			}
		}

		/** Build an Executor with the given prototype Args as the shape. */
		public static Executor create(Connection conn, String sql, Args shapeArgs) throws SQLException {
			NamedPreparedStatement tmp = new NamedPreparedStatement(conn, sql, null, null, null);
			return new Executor(conn, tmp.tokens, tmp.originalSql, EmptyListMode.AS_NULL, "NULL", null, null, null,
					shapeArgs);
		}

		/** Build an Executor with empty-list toggle and RS options. */
		public static Executor create(Connection conn, String sql, Args shapeArgs, boolean emptyAsNull,
				String customEmptyLiteral, Integer autoGeneratedKeys, Integer resultSetType,
				Integer resultSetConcurrency) throws SQLException {
			NamedPreparedStatement tmp = new NamedPreparedStatement(conn, sql, autoGeneratedKeys, resultSetType,
					resultSetConcurrency);
			EmptyListMode mode = emptyAsNull ? EmptyListMode.AS_NULL : EmptyListMode.AS_CUSTOM_LITERAL;
			String lit = emptyAsNull ? "NULL" : Objects.requireNonNull(customEmptyLiteral, "customEmptyLiteral");
			return new Executor(conn, tmp.tokens, tmp.originalSql, mode, lit, autoGeneratedKeys, resultSetType,
					resultSetConcurrency, shapeArgs);
		}

		/** Bind a single Args and execute (no JDBC addBatch). */
		public int executeUpdate(Args args) throws SQLException {
			plan.validateShape(args);
			bind(args);
			return ps.executeUpdate();
		}

		public ResultSet executeQuery(Args args) throws SQLException {
			plan.validateShape(args);
			bind(args);
			return ps.executeQuery();
		}

		/** Add to JDBC batch (binds, then ps.addBatch()). */
		public void addBatch(Args args) throws SQLException {
			plan.validateShape(args);
			bind(args);
			ps.addBatch();
		}

		/** Execute the current JDBC batch. */
		public int[] executeBatch() throws SQLException {
			return ps.executeBatch();
		}

		/** Convenience: bind+batch all, then executeBatch. */
		public int[] executeBatch(Iterable<Args> many) throws SQLException {
			for (Args a : many) {
				addBatch(a);
			}
			return ps.executeBatch();
		}

		public PreparedStatement getPreparedStatement() {
			return ps;
		}

		public String getCompiledSql() {
			return plan.compiledSql;
		}

		private void bind(Args args) throws SQLException {
			ps.clearParameters();
			int idx = 1;
			for (BindStep step : plan.steps) {
				if (!step.isList) {
					Binder b = args.singles.get(step.name);
					if (b == null)
						throw new IllegalArgumentException("Missing value for :" + step.name);
					b.bind(ps, idx++);
				} else {
					List<Binder> lst = args.lists.get(step.name);
					if (lst == null)
						throw new IllegalArgumentException("Missing list for @" + step.name);
					if (lst.isEmpty()) {
						// No binders for empty → plan generated zero '?' for this step
						// (or inserted literal); nothing to bind here.
					} else {
						for (int k = 0; k < lst.size(); k++) {
							lst.get(k).bind(ps, idx++);
						}
					}
				}
			}
		}

		@Override
		public void close() throws SQLException {
			ps.close();
		}
	}

	/* ---------- Plan: compiled SQL + bind steps ---------- */

	private static final class Plan {
		final String compiledSql;
		final List<BindStep> steps;

		private Plan(String sql, List<BindStep> steps) {
			this.compiledSql = sql;
			this.steps = steps;
		}

		static Plan compile(List<Token> tokens, Args shape, EmptyListMode mode, String emptyLiteral) {
			StringBuilder sb = new StringBuilder(128 + tokens.size() * 4);
			ArrayList<BindStep> steps = new ArrayList<>();
			for (Token t : tokens) {
				if (t instanceof TextToken tt) {
					sb.append(tt.text);
				} else {
					ParamToken pt = (ParamToken) t;
					if (!pt.list) {
						// one '?'
						sb.append('?');
						steps.add(BindStep.single(pt.name));
					} else {
						List<Binder> lst = shape.lists.get(pt.name);
						if (lst == null)
							throw new IllegalArgumentException("Prototype Args missing list for @" + pt.name);
						if (lst.isEmpty()) {
							if (mode == EmptyListMode.AS_NULL)
								sb.append("NULL");
							else
								sb.append(emptyLiteral);
							// No bind steps for this @name occurrence (no '?')
						} else {
							for (int i = 0; i < lst.size(); i++) {
								if (i > 0)
									sb.append(',');
								sb.append('?');
							}
							steps.add(BindStep.list(pt.name)); // bind will consume lst.size() placeholders
						}
					}
				}
			}
			return new Plan(sb.toString(), steps);
		}

		void validateShape(Args args) {
			// Ensure each list occurrence is either empty or has same size as prototype for
			// that name
			// We infer expected sizes by scanning steps and checking the first Args we
			// compiled with.
			// Simpler approach: recompute name->size from prototype at compile time.
			// We'll rely on occurrence-by-occurrence behavior:
			// - If a BindStep is list(name), it implies non-empty in prototype; so args
			// must also be non-empty and same size.
			// - If a list was empty in prototype, there is NO BindStep and NO '?' to bind;
			// args must also be empty.
			// To implement this, we track expected sizes map during compile; here we
			// recompute from steps and prototype shape.
			// For simplicity and robustness, we check sizes name-by-name:
			Map<String, Integer> expected = new HashMap<>();
			for (BindStep s : steps) {
				if (s.isList) {
					// means prototype had non-empty list for this name; record its size once
					expected.putIfAbsent(s.name, -1); // mark needs size check later
				}
			}
			// Fill sizes from prototype-like assumption by examining one Args that created
			// the Plan:
			// Not directly available here; we can infer on the fly from current args on
			// first call,
			// but then we'd allow mismatch silently. So we take a practical path:
			// We require that for any list name that appears in BindSteps, the size is >0
			// and constant across executions.
			for (Map.Entry<String, Integer> e : expected.entrySet()) {
				List<Binder> lst = args.lists.get(e.getKey());
				if (lst == null || lst.isEmpty())
					throw new IllegalArgumentException(
							"List @" + e.getKey() + " must be non-empty to match Executor shape.");
				expected.put(e.getKey(), lst.size());
			}
			// Also ensure any list names that were empty in prototype are still empty now:
			// We detect these by scanning param names in args that are not in expected.
			for (Map.Entry<String, List<Binder>> en : args.lists.entrySet()) {
				String name = en.getKey();
				if (!expected.containsKey(name)) {
					// This name had empty list in prototype → must be empty now
					if (!en.getValue().isEmpty())
						throw new IllegalArgumentException("List @" + name + " must be empty to match Executor shape.");
				}
			}
			// Finally, confirm all expected sizes actually match across all occurrences —
			// already implied by name-based size.
		}
	}

	private static final class BindStep {
		final String name;
		final boolean isList;

		private BindStep(String n, boolean list) {
			this.name = n;
			this.isList = list;
		}

		static BindStep single(String n) {
			return new BindStep(n, false);
		}

		static BindStep list(String n) {
			return new BindStep(n, true);
		}
	}

	/* ---------- Parser ---------- */

	private interface Token {
	}

	private static final class TextToken implements Token {
		final String text;

		TextToken(String s) {
			this.text = s;
		}
	}

	private static final class ParamToken implements Token {
		final String name;
		final boolean list;

		ParamToken(String n, boolean l) {
			this.name = n;
			this.list = l;
		}
	}

	private static List<Token> parse(String sql) {
		ArrayList<Token> out = new ArrayList<>();
		StringBuilder buf = new StringBuilder();

		boolean inSingle = false, inDouble = false, inLine = false, inBlock = false;
		int n = sql.length();

		for (int i = 0; i < n; i++) {
			char c = sql.charAt(i);

			if (inLine) {
				buf.append(c);
				if (c == '\n' || c == '\r')
					inLine = false;
				continue;
			}
			if (inBlock) {
				buf.append(c);
				if (c == '*' && i + 1 < n && sql.charAt(i + 1) == '/') {
					buf.append('/');
					i++;
					inBlock = false;
				}
				continue;
			}
			if (inSingle) {
				buf.append(c);
				if (c == '\'') {
					if (i + 1 < n && sql.charAt(i + 1) == '\'') {
						buf.append('\'');
						i++;
					} else
						inSingle = false;
				}
				continue;
			}
			if (inDouble) {
				buf.append(c);
				if (c == '"') {
					if (i + 1 < n && sql.charAt(i + 1) == '"') {
						buf.append('"');
						i++;
					} else
						inDouble = false;
				}
				continue;
			}

			if (c == '-' && i + 1 < n && sql.charAt(i + 1) == '-') {
				buf.append("--");
				i++;
				inLine = true;
				continue;
			}
			if (c == '/' && i + 1 < n && sql.charAt(i + 1) == '*') {
				buf.append("/*");
				i++;
				inBlock = true;
				continue;
			}
			if (c == '\'') {
				buf.append(c);
				inSingle = true;
				continue;
			}
			if (c == '"') {
				buf.append(c);
				inDouble = true;
				continue;
			}

			if (c == ':' || c == '@') {
				char prev = (i > 0) ? sql.charAt(i - 1) : '\0';
				if ((c == ':' && prev == ':') || (c == '@' && prev == '@')) {
					buf.append(c);
					continue;
				} // ::type or @@sys
				if (i + 1 < n && isIdentStart(sql.charAt(i + 1))) {
					if (buf.length() > 0) {
						out.add(new TextToken(buf.toString()));
						buf.setLength(0);
					}
					int j = i + 2;
					while (j < n && isIdentPart(sql.charAt(j)))
						j++;
					String name = sql.substring(i + 1, j);
					out.add(new ParamToken(name, c == '@'));
					i = j - 1;
					continue;
				}
			}

			buf.append(c);
		}
		if (buf.length() > 0)
			out.add(new TextToken(buf.toString()));
		return out;
	}

	private static boolean isIdentStart(char c) {
		return c == '_' || Character.isLetter(c);
	}

	private static boolean isIdentPart(char c) {
		return c == '_' || Character.isLetterOrDigit(c);
	}

	/* ---------- Helpers ---------- */

	@FunctionalInterface
	interface Binder {
		void bind(PreparedStatement ps, int index) throws SQLException;
	}

	private static List<Binder> toBinders(Collection<?> values, int sqlType, boolean forceType) {
		ArrayList<Binder> list = new ArrayList<>(values.size());
		for (Object v : values) {
			final Object val = v;
			if (forceType)
				list.add((ps, i) -> {
					if (val == null)
						ps.setNull(i, sqlType);
					else
						ps.setObject(i, val, sqlType);
				});
			else
				list.add((ps, i) -> ps.setObject(i, val));
		}
		return list;
	}

	private static Map<String, List<Binder>> deepCopyLists(Map<String, List<Binder>> src) {
		Map<String, List<Binder>> m = new HashMap<>(src.size());
		for (Map.Entry<String, List<Binder>> e : src.entrySet()) {
			m.put(e.getKey(), new ArrayList<>(e.getValue()));
		}
		return m;
	}

	private static Map<String, List<Binder>> unmodifiableListMap(Map<String, List<Binder>> src) {
		Map<String, List<Binder>> m = new HashMap<>(src.size());
		for (Map.Entry<String, List<Binder>> e : src.entrySet()) {
			m.put(e.getKey(), Collections.unmodifiableList(e.getValue()));
		}
		return Collections.unmodifiableMap(m);
	}

	static class ParamSnapshot {
		public Map<String, Binder> singles;
		public Map<String, List<Binder>> lists;

		public ParamSnapshot(Map<String, Binder> singles, Map<String, List<Binder>> lists) {
			super();
			this.singles = singles;
			this.lists = lists;
		}

		@Override
		public int hashCode() {
			return Objects.hash(lists, singles);
		}

		@Override
		public boolean equals(Object obj) {
			if (this == obj)
				return true;
			if (obj == null)
				return false;
			if (getClass() != obj.getClass())
				return false;
			ParamSnapshot other = (ParamSnapshot) obj;
			return Objects.equals(lists, other.lists) && Objects.equals(singles, other.singles);
		}
	}

	enum EmptyListMode {
		AS_NULL, AS_CUSTOM_LITERAL
	}
}