// {{generatedComment}}
package {{config.package}};

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Struct;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.OffsetDateTime;
import java.time.OffsetTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.HashMap;
import java.util.Collections;
import java.util.NoSuchElementException;
import java.util.Spliterator;
import java.util.Spliterators;
import java.util.UUID;
import java.util.function.Function;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;
{{#if tables.length}}
{{#if (isDuckDB)}}
import org.duckdb.DuckDBAppender;
import org.duckdb.DuckDBConnection;
{{else if (isPostgres)}}
// Requires dependency: de.bytefish:pgbulkinsert:9.0.0
import de.bytefish.pgbulkinsert.PgBulkInsert;
import java.io.IOException;
{{/if}}
{{/if}}

public class {{className}} {
    private final Connection connection;
    {{#if (observerEnabled)}}
    private final SqgObserver observer;
    {{/if}}

    public {{className}}(Connection connection) {
        this.connection = connection;
        {{#if (observerEnabled)}}
        this.observer = null;
        {{/if}}
    }
    {{#if (observerEnabled)}}

    /** Construct with an instrumentation observer invoked around every generated operation. */
    public {{className}}(Connection connection, SqgObserver observer) {
        this.connection = connection;
        this.observer = observer;
    }
    {{/if}}

    /** Options for streaming queries. fetchSize hints at the JDBC driver's batch size; 0 means driver default. */
    public record StreamOptions(int fetchSize) {
        public static final StreamOptions DEFAULT = new StreamOptions({{#if (isPostgres)}}1000{{else}}0{{/if}});
        public StreamOptions withFetchSize(int fetchSize) { return new StreamOptions(fetchSize); }
    }

    private static Object[] getObjectArray(Array array) {
        if (array == null) {
            return null;
        }
        try {
            return (Object[]) array.getArray();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    private static Object[] getAttr(Struct struct) {
        if (struct == null) {
            return null;
        }
        try {
            return struct.getAttributes();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static LocalDateTime toLocalDateTime(Object ts) {
        if (ts == null) return null;
        if (ts instanceof LocalDateTime ldt) return ldt;
        return ((java.sql.Timestamp) ts).toLocalDateTime();
    }

    private static LocalDate toLocalDate(Object d) {
        if (d == null) return null;
        if (d instanceof LocalDate ld) return ld;
        return ((java.sql.Date) d).toLocalDate();
    }

    private static LocalTime toLocalTime(Object t) {
        if (t == null) return null;
        if (t instanceof LocalTime lt) return lt;
        return ((java.sql.Time) t).toLocalTime();
    }

    private static OffsetDateTime toOffsetDateTime(Object ts) {
        if (ts == null) return null;
        if (ts instanceof OffsetDateTime odt) return odt;
        return ((java.sql.Timestamp) ts).toInstant().atOffset(java.time.ZoneOffset.UTC);
    }
    
    private static <K> List<K> arrayToList(
        Array array,
        Class<? extends K[]> baseType
    )  {
        var objectArray = getObjectArray(array);
        if (objectArray == null) {
            return null;
        }
        return Collections.unmodifiableList(
            Arrays.asList(
                Arrays.copyOf(objectArray, objectArray.length, baseType)
            )
        );
    }

    private static <K> List<K> arrayOfStructToList(
        Array array,
        Function<Object[], K> convert
    )  {
        var objectArray = getObjectArray(array);
        if (objectArray == null) {
            return null;
        }
        var list = new ArrayList<K>(objectArray.length);
        for (Object obj : objectArray) {
            list.add(obj != null ? convert.apply(getAttr((Struct)obj)) : null);
        }
        return Collections.unmodifiableList(list);
    }

    @SuppressWarnings("unchecked")
    private static <K> List<List<K>> multiDimArrayToList(
        Array array,
        Class<? extends K[]> baseType
    )  {
        var objectArray = getObjectArray(array);
        if (objectArray == null) {
            return null;
        }
        var list = new ArrayList<List<K>>(objectArray.length);
        for (Object obj : objectArray) {
            if (obj == null) {
                list.add(null);
            } else if (obj instanceof Array) {
                list.add(arrayToList((Array)obj, baseType));
            } else if (obj instanceof Object[]) {
                var inner = (Object[]) obj;
                list.add(Collections.unmodifiableList(
                    Arrays.asList(Arrays.copyOf(inner, inner.length, baseType))
                ));
            } else {
                throw new RuntimeException("Unexpected type in multi-dimensional array: " + obj.getClass());
            }
        }
        return Collections.unmodifiableList(list);
    }

    private static final List<String> migrations = List.of(
        {{#each migrations}}"""
{{{sqlQuery}}}"""{{#unless @last}},{{/unless}}
        {{/each}}
    );

{{#if config.migrations}}
    private static final List<String> migrationIds = List.of(
        {{#each migrations}}"{{id}}"{{#unless @last}},{{/unless}}
        {{/each}}
    );
{{/if}}

    public static List<String> getMigrations() {
        return migrations;
    }

{{#if config.migrations}}
    public static void applyMigrations(Connection connection) throws SQLException {
        applyMigrations(connection, "{{projectName}}");
    }

    public static void applyMigrations(Connection connection, String projectName) throws SQLException {
        try (var stmt = connection.createStatement()) {
            stmt.execute("""
                CREATE TABLE IF NOT EXISTS _sqg_migrations (
                    project TEXT NOT NULL,
                    migration_id TEXT NOT NULL,
                    applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (project, migration_id)
                )""");
        }
        boolean wasAutoCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);
        try {
            var applied = new java.util.HashSet<String>();
            try (var stmt = connection.prepareStatement("SELECT migration_id FROM _sqg_migrations WHERE project = ?")) {
                stmt.setString(1, projectName);
                try (var rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        applied.add(rs.getString(1));
                    }
                }
            }
            for (int i = 0; i < migrations.size(); i++) {
                var id = migrationIds.get(i);
                if (!applied.contains(id)) {
                    try (var stmt = connection.createStatement()) {
                        stmt.execute(migrations.get(i));
                    }
                    try (var stmt = connection.prepareStatement("INSERT INTO _sqg_migrations (project, migration_id) VALUES (?, ?)")) {
                        stmt.setString(1, projectName);
                        stmt.setString(2, id);
                        stmt.executeUpdate();
                    }
                }
            }
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(wasAutoCommit);
        }
    }
{{/if}}

    {{{declareEnums queries}}}

    {{#each attachers}}
    /** Attach the "{{alias}}" postgres source under the same alias used during generation. Loads the postgres extension (idempotent) so callers need not rely on DuckDB autoloading. */
    public void {{functionName}}(String connectionString) throws SQLException {
        try (var stmt = connection.createStatement()) {
            stmt.execute("INSTALL postgres");
            stmt.execute("LOAD postgres");
            stmt.execute("ATTACH '" + connectionString + "' AS {{alias}} (TYPE postgres)");
        }
    }

    {{/each}}
    {{#each queries}}
    {{#unless skipGenerateFunction}}
    {{>columnTypesRecord}}
    public {{> returnType}} {{functionName}}({{#each variables}}{{{type}}} {{name}}{{#unless @last}}, {{/unless}}{{/each}}) throws SQLException {
        {{#if (observerEnabled)}}
        SqgObserver.Scope __obs = observer == null ? null : observer.start("{{functionName}}");
        long __rows = -1; Throwable __err = null;
        try {
        {{/if}}
        try (var stmt = connection.prepareStatement({{{partsToString sqlQueryParts}}})) {
            {{> execute}}
        }
        {{#if (observerEnabled)}}
        } catch (SQLException __e) { __err = __e; throw __e; }
        finally { if (__obs != null) __obs.end(__rows, __err); }
        {{/if}}
    }
    {{#if isBatch}}
    {{#if (hasMultipleParams variables)}}
    public record {{batchParamsType}}({{#each variables}}{{{type}}} {{name}}{{#unless @last}}, {{/unless}}{{/each}}) {}

    public int[] {{functionName}}Batch(Iterable<{{batchParamsType}}> params) throws SQLException {
        {{#if (observerEnabled)}}
        SqgObserver.Scope __obs = observer == null ? null : observer.start("{{functionName}}Batch");
        long __rows = -1; Throwable __err = null;
        try {
        {{/if}}
        try (var stmt = connection.prepareStatement({{{partsToString sqlQueryParts}}})) {
            {{#if (observerEnabled)}}
            long __n = 0;
            {{/if}}
            for (var row : params) {
                {{#each parameters}}{{#if isArray}}stmt.setArray({{plusOne @index}}, connection.createArrayOf("{{arrayBaseType}}", row.{{name}}().toArray()));
                {{else if isEnum}}stmt.setObject({{plusOne @index}}, row.{{name}}().getValue());
                {{else}}{{{jdbcSet type (plusOne @index) (concat "row." name "()")}}}
                {{/if}}{{/each}}
                stmt.addBatch();
                {{#if (observerEnabled)}}
                __n++;
                {{/if}}
            }
            {{#if (observerEnabled)}}
            __rows = __n;
            {{/if}}
            return stmt.executeBatch();
        }
        {{#if (observerEnabled)}}
        } catch (SQLException __e) { __err = __e; throw __e; }
        finally { if (__obs != null) __obs.end(__rows, __err); }
        {{/if}}
    }
    {{else}}
    public int[] {{functionName}}Batch(Iterable<{{#each variables}}{{{type}}}{{/each}}> params) throws SQLException {
        {{#if (observerEnabled)}}
        SqgObserver.Scope __obs = observer == null ? null : observer.start("{{functionName}}Batch");
        long __rows = -1; Throwable __err = null;
        try {
        {{/if}}
        try (var stmt = connection.prepareStatement({{{partsToString sqlQueryParts}}})) {
            {{#if (observerEnabled)}}
            long __n = 0;
            {{/if}}
            for (var value : params) {
                {{#each parameters}}{{#if isArray}}stmt.setArray({{plusOne @index}}, connection.createArrayOf("{{arrayBaseType}}", value.toArray()));
                {{else if isEnum}}stmt.setObject({{plusOne @index}}, value.getValue());
                {{else}}{{{jdbcSet type (plusOne @index) "value"}}}
                {{/if}}{{/each}}
                stmt.addBatch();
                {{#if (observerEnabled)}}
                __n++;
                {{/if}}
            }
            {{#if (observerEnabled)}}
            __rows = __n;
            {{/if}}
            return stmt.executeBatch();
        }
        {{#if (observerEnabled)}}
        } catch (SQLException __e) { __err = __e; throw __e; }
        finally { if (__obs != null) __obs.end(__rows, __err); }
        {{/if}}
    }
    {{/if}}
    {{/if}}
    {{#if isQuery}}{{#unless isOne}}
    public Stream<{{rowType}}> {{functionName}}Stream({{#each variables}}{{{type}}} {{name}}{{#unless @last}}, {{/unless}}{{/each}}) throws SQLException {
        return {{functionName}}Stream({{#each variables}}{{name}}, {{/each}}StreamOptions.DEFAULT);
    }

    public Stream<{{rowType}}> {{functionName}}Stream({{#each variables}}{{{type}}} {{name}}, {{/each}}StreamOptions options) throws SQLException {
        {{#if (observerEnabled)}}
        SqgObserver.Scope __obs = observer == null ? null : observer.start("{{functionName}}Stream");
        try {
        {{/if}}
        {{#if (isPostgres)}}
        boolean wasAutoCommit = connection.getAutoCommit();
        if (wasAutoCommit) connection.setAutoCommit(false);
        {{/if}}
        var stmt = connection.prepareStatement({{{partsToString sqlQueryParts}}});
        if (options.fetchSize() > 0) stmt.setFetchSize(options.fetchSize());
        {{#each parameters}}{{#if isArray}}stmt.setArray({{plusOne @index}}, connection.createArrayOf("{{arrayBaseType}}", {{name}}.toArray()));
        {{else if isEnum}}stmt.setObject({{plusOne @index}}, {{name}}.getValue());
        {{else}}{{{jdbcSet type (plusOne @index) name}}}
        {{/if}}{{/each}}
        var rs = stmt.executeQuery();
        {{#if (observerEnabled)}}
        final long[] __count = { 0 };
        {{/if}}
        var iter = new Iterator<{{rowType}}>() {
            private Boolean hasNext = null;
            public boolean hasNext() {
                if (hasNext == null) {
                    try { hasNext = rs.next(); }
                    catch (SQLException e) { throw new RuntimeException(e); }
                }
                return hasNext;
            }
            public {{rowType}} next() {
                if (!hasNext()) throw new NoSuchElementException();
                hasNext = null;
                try { {{#if (observerEnabled)}}__count[0]++; {{/if}}return {{> readRow}}; }
                catch (SQLException e) { throw new RuntimeException(e); }
            }
        };
        return StreamSupport.stream(
            Spliterators.spliteratorUnknownSize(iter, Spliterator.ORDERED), false
        ).onClose(() -> {
            try {
                rs.close();
                stmt.close();
                {{#if (isPostgres)}}
                if (wasAutoCommit) connection.setAutoCommit(true);
                {{/if}}
                {{#if (observerEnabled)}}
                if (__obs != null) __obs.end(__count[0], null);
                {{/if}}
            }
            catch (SQLException e) {
                {{#if (observerEnabled)}}if (__obs != null) __obs.end(__count[0], e);{{/if}}
                throw new RuntimeException(e);
            }
        });
        {{#if (observerEnabled)}}
        } catch (SQLException __e) { if (__obs != null) __obs.end(-1, __e); throw __e; }
        {{/if}}
    }
    {{/unless}}{{/if}}
    {{/unless}}
    {{/each}}

    {{#if tables.length}}
    // ==================== Appenders ====================
    {{#each tables}}

{{#if (isDuckDB)}}
    /** Create an appender for bulk inserts into {{tableName}} */
    public {{className}} {{functionName}}() throws SQLException {
        return new {{className}}(((DuckDBConnection) connection).createAppender(DuckDBConnection.DEFAULT_SCHEMA, "{{tableName}}"));
    }
{{else if (isPostgres)}}
    /** Bulk insert rows into {{tableName}} using PostgreSQL COPY BINARY (via PgBulkInsert). */
    public void bulkInsert{{rowTypeName}}(Iterable<{{rowTypeName}}> rows) throws SQLException, IOException {
        {{constantName}}_WRITER.saveAll(connection, "{{tableName}}", rows);
    }
{{/if}}
    {{/each}}
    {{/if}}


{{#each tables}}
/** Row type for {{tableName}} appender */
{{#if (isPostgres)}}
public record {{rowTypeName}}({{#each columns}}{{{appenderType this}}} {{javaVarName name}}{{#unless @last}}, {{/unless}}{{/each}}) {}
{{else}}
public record {{rowTypeName}}({{#each columns}}{{{appenderType this}}} {{javaVarName name}}{{#unless @last}}, {{/unless}}{{/each}}) {}
{{/if}}

{{#if (isDuckDB)}}
/** Appender for bulk inserts into {{tableName}} */
public static class {{className}} implements AutoCloseable {
    private final DuckDBAppender appender;

    {{className}}(DuckDBAppender appender) {
        this.appender = appender;
    }

    /** Get the underlying DuckDB appender for advanced operations */
    public DuckDBAppender getAppender() {
        return appender;
    }

    /** Append a single row */
    public {{className}} append({{rowTypeName}} row) throws SQLException {
        appender.beginRow();
        {{#each columns}}
        appender.append(row.{{javaVarName name}}());
        {{/each}}
        appender.endRow();
        return this;
    }

    /** Append a single row with individual values */
    public {{className}} append({{#each columns}}{{{appenderType this}}} {{javaVarName name}}{{#unless @last}}, {{/unless}}{{/each}}) throws SQLException {
        appender.beginRow();
        {{#each columns}}
        appender.append({{javaVarName name}});
        {{/each}}
        appender.endRow();
        return this;
    }

    /** Append multiple rows */
    public {{className}} appendMany(Iterable<{{rowTypeName}}> rows) throws SQLException {
        for (var row : rows) {
            append(row);
        }
        return this;
    }

    /** Flush and close the appender */
    @Override
    public void close() throws SQLException {
        appender.close();
    }
}
{{else if (isPostgres)}}
private static final PgBulkInsert.PgMapper<{{rowTypeName}}> {{constantName}}_MAPPER =
    PgBulkInsert.PgMapper.forClass({{rowTypeName}}.class)
        {{#each columns}}
        .map("{{name}}", PgBulkInsert.PostgresTypes.{{{pgBulkType this}}}.{{{pgBulkAccessor this}}}({{../rowTypeName}}::{{javaVarName name}}))
        {{/each}};

private static final PgBulkInsert.PgBulkWriter<{{rowTypeName}}> {{constantName}}_WRITER =
    new PgBulkInsert.PgBulkWriter<>({{constantName}}_MAPPER);
{{/if}}
{{/each}}
}

{{#*inline "columnTypesRecord"}}
{{#if isQuery}}
{{{declareTypes this}}}
{{/if}}
{{/inline}}

{{#*inline "returnType"}}
{{#if isQuery~}}
{{functionReturnType}}
{{~else~}}
int
{{~/if~}}
{{/inline~}}

{{#*inline "readRow"}}
{{{readColumns this}}}
{{/inline~}}

{{#*inline "execute"}}
{{#each parameters}}{{#if isArray}}stmt.setArray({{plusOne @index}}, connection.createArrayOf("{{arrayBaseType}}", {{name}}.toArray()));
{{else if isEnum}}stmt.setObject({{plusOne @index}}, {{name}}.getValue());
{{else}}{{{jdbcSet type (plusOne @index) name}}}
{{/if}}{{/each}}
{{#if isQuery}}
try(var rs = stmt.executeQuery()) {
{{#if isOne}}
{{#if (observerEnabled)}}
        var __one = rs.next() ? {{> readRow}} : null;
        __rows = __one != null ? 1 : 0;
        return __one;
{{else}}
        return rs.next() ? {{> readRow}} : null;
{{/if}}
{{else}}
var results = new ArrayList<{{rowType}}>();
while (rs.next()) {
    results.add({{> readRow}});
}
{{#if (observerEnabled)}}
__rows = results.size();
{{/if}}
return results;
{{/if}}
}
{{else}}
{{#if (observerEnabled)}}
int __updated = stmt.executeUpdate();
__rows = __updated;
return __updated;
{{else}}
return stmt.executeUpdate();
{{/if}}
{{/if}}
{{/inline}}