Records and Data Handling

public interface Record extends Attachable {
    /**
     * Get the number of fields in this record
     * @return Number of fields
     */
    int size();
    
    /**
     * Get a field value by field reference
     * @param field Field to get value for
     * @return Value of the specified field
     */
    <T> T get(Field<T> field);
    
    /**
     * Get a field value by field name
     * @param fieldName Name of the field
     * @return Value of the named field
     */
    Object get(String fieldName);
    
    /**
     * Get a field value by field index
     * @param index Zero-based field index
     * @return Value at the specified index
     */
    Object get(int index);
    
    /**
     * Get a typed field value by field name
     * @param fieldName Name of the field
     * @param type Expected type class
     * @return Typed value of the named field
     */
    <T> T get(String fieldName, Class<T> type);
    
    /**
     * Get a typed field value by field index
     * @param index Zero-based field index
     * @param type Expected type class
     * @return Typed value at the specified index
     */
    <T> T get(int index, Class<T> type);
    
    /**
     * Set a field value
     * @param field Field to set
     * @param value New value
     */
    <T> void set(Field<T> field, T value);
    
    /**
     * Set a field value by name
     * @param fieldName Name of the field
     * @param value New value
     */
    void set(String fieldName, Object value);
    
    /**
     * Set a field value by index
     * @param index Zero-based field index
     * @param value New value
     */
    void set(int index, Object value);
    
    /**
     * Create a copy of this record with a field value changed
     * @param field Field to set
     * @param value New value
     * @return New record with the field value changed
     */
    <T> Record with(Field<T> field, T value);
    
    /**
     * Check if this record has been modified
     * @return true if any field has been changed
     */
    boolean changed();
    
    /**
     * Check if a specific field has been modified
     * @param field Field to check
     * @return true if the field has been changed
     */
    boolean changed(Field<?> field);
    
    /**
     * Check if a field by name has been modified
     * @param fieldName Name of the field to check
     * @return true if the field has been changed
     */
    boolean changed(String fieldName);
    
    /**
     * Get the original record values before changes
     * @return Record with original values
     */
    Record original();
    
    /**
     * Reset all changes, reverting to original values
     */
    void reset();
    
    /**
     * Reset changes for a specific field
     * @param field Field to reset
     */
    void reset(Field<?> field);
    
    /**
     * Convert record values to an array
     * @return Array containing all field values
     */
    Object[] intoArray();
    
    /**
     * Convert record to a Map
     * @return Map with field names as keys and values
     */
    Map<String, Object> intoMap();
    
    /**
     * Convert record to a custom type
     * @param type Target type class
     * @return Instance of the target type populated with record data
     */
    <E> E into(Class<? extends E> type);
    
    /**
     * Get the record's values as a Row
     * @return Row containing the record values
     */
    Row valuesRow();
    
    /**
     * Get the record's field structure as a Row
     * @return Row describing the record structure
     */
    Row fieldsRow();
}

// Generic record access
Record record = create.select().from(AUTHOR).where(AUTHOR.ID.eq(1)).fetchOne();

// Type-safe field access
String firstName = record.get(AUTHOR.FIRST_NAME);
Integer id = record.get(AUTHOR.ID);

// Access by field name or index
String lastName = record.get("LAST_NAME", String.class);
Object firstValue = record.get(0);

// Modify record values
record.set(AUTHOR.FIRST_NAME, "Johnny");
record.set("ACTIVE", true);

// Check for changes
if (record.changed()) {
    System.out.println("Record has been modified");
    if (record.changed(AUTHOR.FIRST_NAME)) {
        System.out.println("First name was changed");
    }
}

// Convert to different formats
Object[] values = record.intoArray();
Map<String, Object> map = record.intoMap();
AuthorPojo pojo = record.into(AuthorPojo.class);

public interface UpdatableRecord<R extends UpdatableRecord<R>> extends Record {
    /**
     * Store this record (INSERT or UPDATE based on primary key)
     * @return Number of affected rows (0 or 1)
     */
    int store();
    
    /**
     * Insert this record into the database
     * @return Number of affected rows (0 or 1)
     */
    int insert();
    
    /**
     * Update this record in the database
     * @return Number of affected rows (0 or 1)
     */
    int update();
    
    /**
     * Delete this record from the database
     * @return Number of affected rows (0 or 1)
     */
    int delete();
    
    /**
     * Refresh this record from the database
     * Reloads all field values from the database
     */
    void refresh();
    
    /**
     * Refresh specific fields from the database
     * @param fields Fields to refresh
     */
    void refresh(Field<?>... fields);
    
    /**
     * Get the table this record belongs to
     * @return Table instance
     */
    Table<R> getTable();
    
    /**
     * Create a copy of this record
     * @return New record instance with same values
     */
    R copy();
}

// Create new record
AuthorRecord author = create.newRecord(AUTHOR);
author.setFirstName("Jane");
author.setLastName("Doe");
author.setDateOfBirth(LocalDate.of(1985, 3, 20));

// Store (INSERT since it's new)
int result = author.store();
Integer newId = author.getId(); // Auto-generated ID

// Modify and update
author.setFirstName("Janet");
int updated = author.update();

// Refresh from database
author.refresh();

// Delete record
int deleted = author.delete();

// Work with existing record
AuthorRecord existing = create.selectFrom(AUTHOR)
    .where(AUTHOR.ID.eq(5))
    .fetchOne();

if (existing != null) {
    existing.setActive(false);
    existing.store(); // UPDATE since it exists
}

public interface Result<R extends Record> extends List<R>, Attachable {
    /**
     * Check if this result is empty
     * @return true if no records
     */
    boolean isEmpty();
    
    /**
     * Get the fields in this result
     * @return Array of fields
     */
    Field<?>[] fields();
    
    /**
     * Get field by index
     * @param index Zero-based field index
     * @return Field at the specified index
     */
    Field<?> field(int index);
    
    /**
     * Get field by name
     * @param name Field name
     * @return Field with the specified name
     */
    Field<?> field(String name);
    
    /**
     * Get all values for a specific field
     * @param field Field to get values for
     * @return List of values for the field
     */
    <T> List<T> getValues(Field<T> field);
    
    /**
     * Get all values for a field by name
     * @param fieldName Name of the field
     * @return List of values for the named field
     */
    List<?> getValues(String fieldName);
    
    /**
     * Get all values for a field by index
     * @param index Zero-based field index
     * @return List of values at the specified index
     */
    List<?> getValues(int index);
    
    /**
     * Convert all records to arrays
     * @return List of Object arrays
     */
    List<Object[]> intoArrays();
    
    /**
     * Convert all records to Maps
     * @return List of Maps with field names as keys
     */
    List<Map<String, Object>> intoMaps();
    
    /**
     * Convert all records to custom type instances
     * @param type Target type class
     * @return List of instances of the target type
     */
    <E> List<E> into(Class<? extends E> type);
    
    /**
     * Group records by a field value
     * @param field Field to group by
     * @return Map with field values as keys and record lists as values
     */
    <K> Map<K, Result<R>> intoGroups(Field<K> field);
    
    /**
     * Convert to a Map using a field as key
     * @param keyField Field to use as map key
     * @return Map with field values as keys and records as values
     */
    <K> Map<K, R> intoMap(Field<K> keyField);
    
    /**
     * Format result as a table string
     * @return String representation of the result as a table
     */
    String format();
    
    /**
     * Format result with specific number of records
     * @param maxRecords Maximum records to include in format
     * @return String representation with limited records
     */
    String format(int maxRecords);
}

// Fetch multiple records
Result<AuthorRecord> authors = create.selectFrom(AUTHOR)
    .where(AUTHOR.ACTIVE.eq(true))
    .fetch();

// Work with result metadata
Field<?>[] fields = authors.fields();
int recordCount = authors.size();
boolean hasData = !authors.isEmpty();

// Extract field values
List<String> firstNames = authors.getValues(AUTHOR.FIRST_NAME);
List<Integer> ids = authors.getValues(AUTHOR.ID);

// Convert to different formats
List<Object[]> arrays = authors.intoArrays();
List<Map<String, Object>> maps = authors.intoMaps();
List<AuthorPojo> pojos = authors.into(AuthorPojo.class);

// Group and organize data
Map<Boolean, Result<AuthorRecord>> byActive = authors.intoGroups(AUTHOR.ACTIVE);
Map<Integer, AuthorRecord> byId = authors.intoMap(AUTHOR.ID);

// Display results
System.out.println(authors.format(10)); // Show first 10 records as table

public interface DSLContext {
    /**
     * Create a new record for a table
     * @param table Table to create record for
     * @return New record instance
     */
    <R extends Record> R newRecord(Table<R> table);
    
    /**
     * Create a new record with field values
     * @param table Table to create record for
     * @param source Source record or object to copy values from
     * @return New record with copied values
     */
    <R extends Record> R newRecord(Table<R> table, Object source);
    
    /**
     * Execute a batch of records
     * @param records Records to execute in batch
     * @return Array of affected row counts
     */
    int[] batchStore(UpdatableRecord<?>... records);
    
    /**
     * Execute a batch insert
     * @param records Records to insert in batch
     * @return Array of affected row counts
     */
    int[] batchInsert(UpdatableRecord<?>... records);
    
    /**
     * Execute a batch update
     * @param records Records to update in batch
     * @return Array of affected row counts
     */
    int[] batchUpdate(UpdatableRecord<?>... records);
    
    /**
     * Execute a batch delete
     * @param records Records to delete in batch
     * @return Array of affected row counts
     */
    int[] batchDelete(UpdatableRecord<?>... records);
}

// Create new records
AuthorRecord author1 = create.newRecord(AUTHOR);
author1.setFirstName("Alice");
author1.setLastName("Smith");

AuthorRecord author2 = create.newRecord(AUTHOR);
author2.setFirstName("Bob");
author2.setLastName("Jones");

// Batch operations
int[] insertResults = create.batchInsert(author1, author2);

// Create record from existing data
AuthorPojo pojo = new AuthorPojo("Charlie", "Brown", LocalDate.of(1990, 1, 1));
AuthorRecord author3 = create.newRecord(AUTHOR, pojo);
author3.store();

// Bulk operations with collections
List<AuthorRecord> authors = Arrays.asList(author1, author2, author3);
authors.forEach(author -> {
    author.setActive(true);
});

int[] updateResults = create.batchUpdate(authors.toArray(new AuthorRecord[0]));

public interface RecordType<R extends Record> {
    /**
     * Get all fields in this record type
     * @return Array of fields
     */
    Field<?>[] fields();
    
    /**
     * Get field by index
     * @param index Zero-based field index
     * @return Field at the specified index
     */
    Field<?> field(int index);
    
    /**
     * Get field by name
     * @param name Field name
     * @return Field with the specified name
     */
    Field<?> field(String name);
    
    /**
     * Get the number of fields
     * @return Field count
     */
    int size();
}