## Advanced Features: Abstract Classes

These abstract classes provide the foundation for building custom implementations and extending the package functionality. They define the structure and contracts that concrete implementations must follow.

### Model

The `Model` abstract class provides an ORM-style interface for database interactions. It offers Active Record pattern functionality, allowing you to define models that represent database tables with built-in methods for querying, inserting, updating, managing relationships, and advanced query expressions.

## Key Features:

- **Active Record Pattern**: Each model instance represents a single database record
- **Query Builder Methods**: Chainable methods for building complex queries
- **Relationship Management**: Define and load relationships (hasMany, belongsTo, etc.)
- **Automatic Timestamps**: Optional automatic `created_at` and `updated_at` management
- **Primary Key Management**: Configurable primary key handling
- **Spatial Queries**: Built-in `near()` method for distance-based filtering and ordering
- **Full-Text Relevance**: Built-in `isTextRelevant()` method for relevance scoring
- **JSON Aggregation**: Built-in `JsonAggregate()` method for aggregating related rows into JSON

## Creating a Model:

To create a model, extend the `Model` class and configure it:

```typescript
import { Model } from '@kirbkis/database-handler-core';

class User extends Model<UserType> {
  protected configuration = {
    table: 'users',
    primaryKey: 'id',
    incrementing: true,
    keyType: 'number',
    timestamps: true,
    createdAtColumn: 'created_at',
    updatedAtColumn: 'updated_at',
    guarded: ['*'], // Fields that cannot be mass-assigned (Not implemented yet)
    customAdapter: 'default', // Optional: specify adapter
  };
}
```

## Basic Usage:

**Fetching Records:**

```typescript
// Find by primary key (returns instance with query set, call first() to execute)
const user = await User.find(1).first();

// Find or throw error
const user = await User.findOrFail(1);

// Get first record
const user = await User.first();

// Get all records
const users = await User.all();
```

**Query Building:**

```typescript
// Where clause (simple key-value)
const adults = await User.where({ age: 18 }).get();

// Where clause (comparison operators)
const adults = await User.where([
  { column: 'age', operator: '>=', value: 18 },
]).get();

// Where by ID shorthand
const user = await User.whereId(42).first();

// Ordering
const users = await User.orderBy('name', 'ASC').get();

// Limit and offset (offset requires limit)
const users = await User.limit(10).offset(20).get();

// Combining methods
const users = await User.where({ status: 'active' })
  .orderBy('created_at', 'DESC')
  .limit(5)
  .get();
```

**Creating and Updating:**

```typescript
// Create a new record
const user = User.set({
  name: 'John Doe',
  email: 'john@example.com',
  age: 30,
});
await user.save();

// Update existing record (pass new values to update())
const user = await User.find(1).first();
await user.update({ name: 'Jane Doe' });

// Or set attributes then save
user.set({ name: 'Jane Doe' });
await user.save();
```

**Inspecting generated SQL:**

```typescript
const sql = await User.where({ status: 'active' }).limit(10).toSql();

console.log(sql);
// SELECT * FROM "users" WHERE status = @status LIMIT 10
```

**Relationships:**

Define relationships in your model:

```typescript
class User extends Model<UserType> {
  protected configuration = {
    table: 'users',
    primaryKey: 'id',
  };

  // Define a one-to-many relationship
  protected posts() {
    return this.hasMany(new Post(), 'user_id', 'id');
  }

  // Define a belongs-to relationship
  protected role() {
    return this.belongsTo(new Role(), 'role_id', 'id');
  }

  // Define a many-to-many relationship (Not implemented yet)
  protected groups() {
    return this.ManyToMany(
      new Group(),
      'user_groups', // pivot table
      'id', // local key
      'id', // foreign key
      'user_id', // pivot foreign key
      'group_id', // pivot local key
    );
  }
}

// Load relationships
const userWithPosts = await User.with('posts').find(1).first();
const userWithRole = await User.with('role').find(1).first();

// Load relationships with conditions
const userWithActivePosts = await User.with('posts', { status: 'published' })
  .find(1)
  .first();
```

**Spatial / Distance Queries:**

Use `near()` to filter and order records by geographic distance:

```typescript
const nearbyUsers = await User.near({
  referencePoint: { lat: 48.8566, lon: 2.3522 }, // Paris
  targetColumns: { lat: 'latitude', lon: 'longitude' },
  maxDistance: 10,
  unit: 'km',
  orderByDistance: 'ASC',
  alias: 'distance',
  valueClauseKeywords: { lat: 'ref_lat', lon: 'ref_lon' },
}).get();

// Each result will have a `distance` field with the computed distance
nearbyUsers.forEach((user) => console.log(user.values.distance));
```

**Parameters for `near()`:**

| Parameter             | Type                             | Description                                                    | Optional? |
| --------------------- | -------------------------------- | -------------------------------------------------------------- | --------- |
| `referencePoint`      | `{ lat: number, lon: number }`   | The origin point to measure distance from                      | No        |
| `targetColumns`       | `{ lat: string, lon: string }`   | Column names storing lat/lon in the table                      | No        |
| `unit`                | `'km' \| 'miles'`                | Distance unit                                                  | No        |
| `orderByDistance`     | `'ASC' \| 'DESC'`                | Sort direction by distance                                     | No        |
| `valueClauseKeywords` | `Record<'lon' \| 'lat', string>` | Parameter name aliases for the query                           | No        |
| `maxDistance`         | `number`                         | Maximum distance filter                                        | Yes       |
| `minDistance`         | `number`                         | Minimum distance filter                                        | Yes       |
| `alias`               | `string`                         | Alias for the computed distance column (default: `'distance'`) | Yes       |

---

**Full-Text Relevance Queries:**

Use `isTextRelevant()` to score records by how well they match a search term:

```typescript
const results = await User.isTextRelevant({
  targetColumns: ['name', 'bio'],
  searchTerm: 'developer',
  minimumRelevance: 0.3,
  alias: 'relevance',
  orderByRelevance: 'DESC',
}).get();

// Each result will include a `relevance` score
results.forEach((user) => console.log(user.values.relevance));
```

**Parameters for `isTextRelevant()`:**

| Parameter          | Type              | Description                                                   | Optional? |
| ------------------ | ----------------- | ------------------------------------------------------------- | --------- |
| `targetColumns`    | `string[]`        | Columns to search within                                      | No        |
| `searchTerm`       | `string`          | The text to search for                                        | No        |
| `minimumRelevance` | `number`          | Minimum relevance threshold (default: `1`)                    | Yes       |
| `alias`            | `string`          | Alias for the relevance score column (default: `'relevance'`) | Yes       |
| `orderByRelevance` | `'ASC' \| 'DESC'` | Sort direction by relevance (default: `'ASC'`)                | Yes       |

---

**JSON Aggregation:**

Use `JsonAggregate()` to aggregate related rows into a nested JSON structure within the query result:

```typescript
const usersWithPosts = await User.JsonAggregate({
  table: 'posts',
  columns: ['id', 'title', 'created_at'],
  groupByColumns: ['user_id'],
  alias: 'posts',
}).get();

// Each user will have a `posts` field containing an array of post objects
usersWithPosts.forEach((user) => console.log(user.values.posts));
```

**Parameters for `JsonAggregate()`:**

| Parameter        | Type                              | Description                                                | Optional? |
| ---------------- | --------------------------------- | ---------------------------------------------------------- | --------- |
| `table`          | `string`                          | The related table to aggregate from                        | No        |
| `columns`        | `string[]`                        | Columns to include from the related table                  | No        |
| `groupByColumns` | `string[]`                        | Columns to group by (default: `[]`)                        | Yes       |
| `alias`          | `string`                          | Alias for the aggregated JSON column (default: table name) | Yes       |
| `nested`         | `NestedJsonAggregateDefinition[]` | Nested aggregations within this aggregate                  | Yes       |
| `having`         | `QueryWhereCondition`             | HAVING clause for the aggregate                            | Yes       |

---

**Configuration Options:**

| Option            | Type                   | Description                                        | Default        |
| ----------------- | ---------------------- | -------------------------------------------------- | -------------- |
| `table`           | `string`               | The database table name                            | Required       |
| `primaryKey`      | `string`               | The primary key column name                        | `'id'`         |
| `incrementing`    | `boolean`              | Whether the primary key is auto-incrementing       | `true`         |
| `keyType`         | `'number' \| 'string'` | The type of the primary key                        | `'number'`     |
| `timestamps`      | `boolean`              | Whether to automatically manage timestamps         | `true`         |
| `createdAtColumn` | `string`               | The name of the created_at column                  | `'created_at'` |
| `updatedAtColumn` | `string`               | The name of the updated_at column                  | `'updated_at'` |
| `deletedAtColumn` | `string`               | The name of the deleted_at column for soft deletes | `'deleted_at'` |
| `guarded`         | `string[]`             | Fields that cannot be mass-assigned                | `['*']`        |
| `fillable`        | `string[]`             | Mass assignable attributes (whitelist)             | `undefined`    |
| `hidden`          | `string[]`             | Attributes hidden during serialization             | `undefined`    |
| `customAdapter`   | `string`               | Name of the adapter to use                         | Uses default   |

<!-- **Note:**

For more detailed information about the Model class and its methods, check the [Model class documentation](./api/Model.md). -->

---

### SchemaTableBuilder

The `SchemaTableBuilder` abstract class provides a fluent interface for building database table schemas. It's the foundation for adapter-specific schema builders and must be implemented by each database adapter to handle database-specific syntax.

## Purpose:

This class defines the contract that all database adapters must follow when implementing schema building functionality. It provides a consistent API across different database systems while allowing each adapter to generate the appropriate SQL for their specific database.

## Implementation:

Each database adapter provides its own concrete implementation. For example:

- `BetterSqlite3TableSchemaBuilder` for SQLite
- `PostgresTableSchemaBuilder` for PostgreSQL

## Methods to Implement:

All implementations must provide these methods:

**Data Type Methods:**

- `increments()` - Auto-incrementing primary key
- `uuid(name)` - UUID column
- `string(name, length?)` - String/VARCHAR column
- `text(name)` - Text column
- `integer(name)` - Integer column
- `decimal(name, precision?, scale?)` - Decimal column
- `float(name)` - Float column
- `boolean(name)` - Boolean column
- `json(name)` - JSON column
- `enum(name, values)` - Enum column
- `time(name)` - Time column
- `timestamp(name)` - Timestamp column
- `timestamps()` - Adds created_at and updated_at columns

**Constraint Methods:**

- `primaryKey()` - Mark column as primary key
- `nullable()` - Allow NULL values
- `unique()` - Add unique constraint
- `defaultTo(value)` - Set default value
- `foreignKey(referenceTable, referenceColumn)` - Add foreign key constraint

**Special Methods:**

- `softDeletes()` - Add soft delete column (deleted_at)
- `morphs(name)` - Add polymorphic relation columns
- `build()` - Generate the final SQL CREATE TABLE statement

## Usage Example:

**Note:** You don't typically use `SchemaTableBuilder` directly. Instead, use the adapter's schema builder:

```typescript
import { BetterSqlite3Adapter } from '@kirbkis/database-handler-better-sqlite3';

const adapter = new BetterSqlite3Adapter();
await adapter.connect('./database.db');

// Create a schema
const schema = adapter.createSchema();

// Define a users table
await schema.createTable('users', (table) => {
  table.integer('id').primaryKey().increments(); // id column (auto-increment primary key)
  table.string('name', 255); // name VARCHAR(255) NOT NULL
  table.string('email').unique(); // email VARCHAR(255) UNIQUE
  table.integer('age').nullable(); // age INTEGER NULL
  table.boolean('active').defaultTo(true);
  table.timestamps(); // created_at and updated_at
});

// Define a posts table with foreign key
await schema.createTable('posts', (table) => {
  table.integer('id').primaryKey().increments();
  table.string('title');
  table.text('content');
  table.integer('user_id').foreignKey('users', 'id');
  table.timestamps();
  table.softDeletes();
});
```

**Chaining Methods:**

Most methods return `this`, allowing you to chain multiple constraints:

```typescript
await schema.createTable('products', (table) => {
  table.integer('id').primaryKey().increments();
  table.string('sku', 50).unique().nullable();
  table.decimal('price', 10, 2).defaultTo(0.0);
  table.integer('category_id').foreignKey('categories', 'id');
});
```

**Creating Custom Implementations:**

If you're creating a custom database adapter, you must extend `SchemaTableBuilder` and implement all abstract methods:

```typescript
import { SchemaTableBuilder } from '@kirbkis/database-handler-core';

export class MyCustomTableSchemaBuilder extends SchemaTableBuilder {
  build(): string {
    // Generate CREATE TABLE SQL for your database
    let sql = `CREATE TABLE ${this.tableName} (`;
    // ... build column definitions
    sql += ');';
    return sql;
  }

  increments(): this {
    // Add auto-increment primary key
    return this.addColumn({
      name: 'id',
      datatype: 'INTEGER',
      constraints: ['PRIMARY KEY', 'AUTOINCREMENT'],
    });
  }

  // Implement all other abstract methods...
}
```

**Note:**

For making use of custom adapters and their schema builders, refer to the adapter documentation:

- [Custom Adapter Guide](../adapters/CustomAdapterGuide.md)