path: root/base/src/main/java/bjc/utils/misc/NamedPreparedStatement.java

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 }
}