# Table Class

The `Table` class provides a high-level interface for common database table operations. It abstracts away SQL syntax by offering methods for fetching records, inserting data, performing joins, and managing table metadata. This class is ideal when you don't want to write raw SQL but still need direct table access without the full ORM features of Models.

## Constructor

```typescript
const table = new Table({ name: string, adapter?: string });
```

**Parameters:**
| Parameter | Type | Description | Optional? |
|-----------|------|-------------|-----------|
| `name` | `string` | The name of the database table | No |
| `adapter` | `string` | The name of the database adapter to use (uses default if not specified) | Yes |

**Example:**

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

// Using default adapter
const usersTable = new Table({ name: 'users' });

// Using a specific adapter
const postsTable = new Table({ name: 'posts', adapter: 'secondary' });
```

---

## Methods

### TableColumnInformation()

Returns raw column information from the database schema.

**Parameters:**
| Parameter | Type | Description | Optional? |
|-----------|------|-------------|-----------|
| `tableName` | `string` | Override the table name to query (defaults to the table's own name) | Yes |

**Returns:** `Promise<TableColumnInfo[]>`

**Example:**

```typescript
const columns = await usersTable.TableColumnInformation();
console.log(columns);
// Output: [{ cid: 0, name: 'id', type: 'INTEGER', notnull: 1, pk: 1, dflt_value: null }, ...]
```

---

### ReadableTableColumnInformation()

Returns formatted, human-readable column information.

**Returns:** `Promise<ReadableTableColumnInfo[]>`

**Example:**

```typescript
const columns = await usersTable.ReadableTableColumnInformation();
console.log(columns);
// Output: [
//   {
//     name: 'id',
//     type: 'INTEGER',
//     nullable: false,
//     isPrimaryKey: true,
//     defaultValue: null
//   },
//   ...
// ]
```

---

### exists()

Checks whether the table exists in the database.

**Returns:** `Promise<boolean>`

**Example:**

```typescript
const tableExists = await usersTable.exists();
if (!tableExists) {
  console.log('Table does not exist yet.');
}
```

---

### FetchRecords()

Fetches multiple records from the table using the `QueryLayers` query structure.

**Parameters:**
| Parameter | Type | Description | Optional? |
|-----------|------|-------------|-----------|
| `queryLayers` | `QueryLayers` | Structured query configuration object | No |

**QueryLayers Structure:**

```typescript
type QueryLayers = {
  base: BaseQueryOptions; // FROM, JOIN, WHERE, ORDER BY, expressions, SELECT
  pretty?: PrettyQueryOptions; // HAVING, GROUP BY, additional WHERE/SELECT
  final?: FinalQueryOptions; // LIMIT, OFFSET, ORDER BY overrides
};
```

**Returns:** `Promise<Record<Type>[]>`

**Examples:**

**Fetch all records:**

```typescript
const allUsers = await usersTable.FetchRecords({ base: { from: 'users' } });
```

**Fetch with filtering:**

```typescript
const adults = await usersTable.FetchRecords({
  base: {
    from: 'users',
    where: [{ column: 'age', operator: '>=', value: 18 }],
  },
});
```

**Fetch with ordering and pagination:**

```typescript
const results = await usersTable.FetchRecords({
  base: {
    from: 'users',
    select: ['id', 'name', 'email'],
    where: [
      { column: 'status', operator: '=', value: 'active' },
      { column: 'role', operator: '=', value: 'admin' },
    ],
    orderBy: [{ column: 'name', direction: 'ASC' }],
  },
  final: {
    limit: 10,
    offset: 20,
  },
});
```

---

### FetchSingleRecord()

Fetches a single record from the table. Internally calls `FetchRecords()` with `limit: 1`.

**Parameters:**
| Parameter | Type | Description | Optional? |
|-----------|------|-------------|-----------|
| `queryLayers` | `QueryLayers` | Structured query configuration object | No |

**Returns:** `Promise<Record<Type> | undefined>`

**Examples:**

**Fetch first record:**

```typescript
const user = await usersTable.FetchSingleRecord({ base: { from: 'users' } });
```

**Fetch with filtering:**

```typescript
const admin = await usersTable.FetchSingleRecord({
  base: {
    from: 'users',
    where: [{ column: 'email', operator: '=', value: 'admin@example.com' }],
  },
});
```

---

### RecordsCount()

Returns the total count of records in the table.

**Returns:** `Promise<number>`

**Example:**

```typescript
const totalUsers = await usersTable.RecordsCount();
console.log(`Total users: ${totalUsers}`);
```

---

### CreateRecord()

Inserts a new record into the table.

**Parameters:**
| Parameter | Type | Description | Optional? |
|-----------|------|-------------|-----------|
| `values` | `Type` | Key-value pairs representing the record data | No |

**Returns:** `Promise<Record<Type> | undefined>`

**Example:**

```typescript
const newUser = await usersTable.CreateRecord({
  name: 'John Doe',
  email: 'john@example.com',
  age: 30,
});

console.log('Inserted user:', newUser?.values);
```

---

### FetchJoined()

Performs JOIN operations with other tables. The results automatically split joined data into separate nested objects for each joined table.

**Parameters:**
| Parameter | Type | Description | Optional? |
|-----------|------|-------------|-----------|
| `queryLayers` | `QueryLayers` | Query configuration with `base.joins` defined | No |

**Join Configuration (`Join` type):**
| Property | Type | Description |
|----------|------|-------------|
| `fromTable` | `string` | The table to join |
| `baseTable` | `string` | The base (left-hand) table |
| `joinType` | `'INNER' \| 'LEFT' \| 'RIGHT' \| 'FULL'` | Type of join |
| `on` | `QueryIsEqualParameter \| QueryIsEqualParameter[]` | Join conditions |
| `where` | `QueryIsEqualParameter \| QueryIsEqualParameter[]` | Optional additional WHERE on the join | Yes |
| `name` | `string` | Optional alias for the join | Yes |

**Returns:** `Promise<Record<Type>[]>`

**Examples:**

**Simple JOIN:**

```typescript
const usersWithPosts = await usersTable.FetchJoined({
  base: {
    from: 'users',
    joins: [
      {
        fromTable: 'posts',
        baseTable: 'users',
        joinType: 'LEFT',
        on: { user_id: 'id' },
      },
    ],
  },
});

// Result structure - joined table data is nested under the joined table's name:
// [
//   {
//     id: 1,
//     name: 'John',
//     posts: { id: 1, title: 'First Post', user_id: 1 }
//   },
//   ...
// ]
```

**Multiple JOINs with filtering:**

```typescript
const results = await usersTable.FetchJoined({
  base: {
    from: 'users',
    joins: [
      {
        fromTable: 'posts',
        baseTable: 'users',
        joinType: 'LEFT',
        on: { user_id: 'id' },
      },
      {
        fromTable: 'comments',
        baseTable: 'posts',
        joinType: 'LEFT',
        on: { post_id: 'id' },
      },
    ],
    where: [{ column: 'users.status', operator: '=', value: 'active' }],
  },
  final: {
    limit: 50,
  },
});
```

---

### Drop()

Drops the table from the database. **Use with caution** - this permanently deletes the table and all its data.

**Returns:** `Promise<void>`

**Example:**

```typescript
await usersTable.Drop();
console.log('Table dropped successfully');
```

---

### toSql()

Returns the SQL string that would be executed for a given `QueryLayers` configuration, without actually running the query.

**Parameters:**
| Parameter | Type | Description | Optional? |
|-----------|------|-------------|-----------|
| `queryLayers` | `QueryLayers` | Structured query configuration object | No |

**Returns:** `Promise<string>`

**Example:**

```typescript
const sql = await usersTable.toSql({
  base: {
    from: 'users',
    where: [{ column: 'status', operator: '=', value: 'active' }],
  },
  final: { limit: 10 },
});

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

---

### QueryHelperClient (getter)

Returns the internal `Query` instance used by this table. Useful for advanced operations that bypass the `Table` abstraction.

**Returns:** `Query`

---

## Complete Usage Example

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

// Create a table instance
const usersTable = new Table({ name: 'users' });

// Check if table exists
const exists = await usersTable.exists();
console.log('Table exists:', exists);

// Get table information
const columns = await usersTable.ReadableTableColumnInformation();
console.log('Table columns:', columns);

// Insert a new user
const newUser = await usersTable.CreateRecord({
  name: 'Jane Doe',
  email: 'jane@example.com',
  age: 25,
  status: 'active',
});

// Fetch active users
const activeUsers = await usersTable.FetchRecords({
  base: {
    from: 'users',
    where: [{ column: 'status', operator: '=', value: 'active' }],
    orderBy: [{ column: 'name', direction: 'ASC' }],
  },
  final: { limit: 10 },
});

// Fetch a specific user
const user = await usersTable.FetchSingleRecord({
  base: {
    from: 'users',
    where: [{ column: 'email', operator: '=', value: 'jane@example.com' }],
  },
});

// Get total count
const totalUsers = await usersTable.RecordsCount();

// Join with posts table
const usersWithPosts = await usersTable.FetchJoined({
  base: {
    from: 'users',
    joins: [
      {
        fromTable: 'posts',
        baseTable: 'users',
        joinType: 'LEFT',
        on: { user_id: 'id' },
      },
    ],
    where: [{ column: 'users.status', operator: '=', value: 'active' }],
  },
});

console.log('Users with posts:', usersWithPosts);
```

---

## Notes

- All query operations use parameterized queries to prevent SQL injection
- The `where` parameter in `QueryLayers` accepts `QueryComparisonParameters[]` arrays, supporting operators like `=`, `!=`, `<`, `<=`, `>`, `>=`, `LIKE`, `IN`, and `NOT IN`
- `FetchJoined()` automatically organizes results by table, nesting joined table columns under the joined table's name
- Use `base.select` to fetch only specific columns for better performance
- `FetchSingleRecord()` is a convenience wrapper around `FetchRecords()` with `final.limit: 1`
- `toSql()` is useful for debugging — it returns the SQL that would run without executing it