# API Reference

All endpoints are under the `/api` prefix. Responses are JSON unless noted.
Authentication is required for all endpoints except `/api/login`, `/api/session`, and `/api/logout`.

---

## Authentication

### `POST /api/login`

Test MariaDB credentials and store them in the session. The database host and port are taken from server-side environment variables (`DB_HOST`, `DB_PORT`) — they are not part of the request.

**Request body (JSON)**

```json
{ "user": "dbuser", "pass": "secret" }
```

**Response**

```json
{ "ok": true }
```

On failure: HTTP 401 with `{ "ok": false, "error": "..." }`.

---

### `GET /api/session`

Returns current session authentication status. Does not require authentication.

**Response**

```json
{ "authenticated": true, "user": "dbuser" }
```

---

### `POST /api/logout`

Destroys the server-side session and clears the session cookie.

**Response**

```json
{ "ok": true }
```

---

## Schema Tree

### `GET /api/tree`

Returns the list of schemas (databases) and their tables that the session user can access. System schemas (`information_schema`, `mysql`, `performance_schema`, `sys`) are excluded.

**Response**

```json
[
  {
    "name": "mydb",
    "tables": ["orders", "products", "customers"]
  }
]
```

---

## Table Metadata

### `GET /api/table/meta`

Returns column definitions, primary key info, and foreign key relationships for one table.

**Query params**

| Param | Required | Description |
|-------|----------|-------------|
| `schema` | yes | Database name |
| `table` | yes | Table name |

**Response**

```json
{
  "columns": [
    {
      "COLUMN_NAME": "id",
      "DATA_TYPE": "int",
      "COLUMN_TYPE": "int(11)",
      "COLUMN_KEY": "PRI",
      "IS_NULLABLE": false,
      "COLUMN_DEFAULT": null,
      "HAS_DEFAULT": false,
      "EXTRA": "auto_increment",
      "COLUMN_COMMENT": "",
      "IS_AUTO_INCREMENT": true,
      "ORDINAL_POSITION": 1,
      "IS_REQUIRED": false,
      "EDITOR_TYPE": "number",
      "IS_FOREIGN_KEY": false,
      "FOREIGN_KEY": null
    }
  ],
  "primaryKey": ["id"],
  "totalColumns": 5,
  "foreignKeys": [
    {
      "COLUMN_NAME": "customer_id",
      "REFERENCED_TABLE_SCHEMA": "mydb",
      "REFERENCED_TABLE_NAME": "customers",
      "REFERENCED_COLUMN_NAME": "id"
    }
  ]
}
```

`EDITOR_TYPE` values: `"number"`, `"datetime"`, `"time"`, `"tickCross"`, `"input"`.

---

## Data Operations

### `POST /api/table/data`

Paginated, filtered, optionally sorted SELECT.

**Request body (JSON)**

| Field | Required | Description |
|-------|----------|-------------|
| `schema` | yes | Database name |
| `table` | yes | Table name |
| `page` | no | Page number (default 1) |
| `size` | no | Rows per page (default 50, max 5000) |
| `filters` | no | Array of `{ "field": "col", "value": "val" }` — matched with `LIKE %val%` |
| `sort` | no | Array with one element: `[{ "field": "col", "dir": "ASC" }]` |

**Response**

```json
{ "data": [ {…}, {…} ], "total": 342, "last_page": 7, "current_page": 1 }
```

---

### `POST /api/table/insert`

Insert a new row. `auto_increment` columns are ignored even if present.

**Request body**

```json
{ "schema": "mydb", "table": "orders", "row": { "name": "Test", "qty": 5 } }
```

**Response**

```json
{ "inserted": true, "id": "123" }
```

On failure: HTTP 400 with `{ "error": true, "message": "..." }`.

---

### `POST /api/table/update`

Update a row. The primary key is read from table metadata on the server; `row` must include PK field(s).

**Request body**

```json
{ "schema": "mydb", "table": "orders", "row": { "id": 123, "name": "Updated", "qty": 10 } }
```

**Response**

```json
{ "updated": 1 }
```

On failure: HTTP 400 with `{ "error": true, "message": "..." }`.

---

### `POST /api/table/delete`

Delete a single row. `row` must include PK field(s).

**Request body**

```json
{ "schema": "mydb", "table": "orders", "row": { "id": 123 } }
```

**Response**

```json
{ "deleted": 1 }
```

---

### `POST /api/table/delete-batch`

Delete multiple rows in a single transaction. For single-column PKs uses batched `WHERE … IN (…)` (batch size 500). For composite PKs deletes row by row within the same transaction.

**Request body**

```json
{
  "schema": "mydb",
  "table": "orders",
  "rows": [ { "id": 1 }, { "id": 2 }, { "id": 5 } ]
}
```

**Response**

```json
{ "deleted": 3, "errors": 0, "errorMessages": [] }
```

---

## CSV

### `POST /api/table/import-csv`

Bulk-insert pre-parsed rows into the target table. CSV parsing is done on the frontend; the backend receives an array of row objects. Runs inside a single transaction — if a critical error occurs the whole import is rolled back; per-row errors are collected and reported without stopping the import.

**Request body (JSON)**

```json
{ "schema": "mydb", "table": "orders", "rows": [ { "name": "A", "qty": 1 }, … ] }
```

**Response**

```json
{
  "inserted": 98,
  "errors": 2,
  "errorMessages": ["Row CSV #5: …"],
  "failedRows": [ { "row": {…}, "error": "…", "line": 5 } ]
}
```

On critical failure: HTTP 500 with `{ "error": "…", "inserted": 0, "errors": 1 }`.

---

### `POST /api/table/export-csv`

Export table data as a CSV string. Active filters and sort are applied. Only the columns listed in the `columns` field are exported. CSV uses `;` as delimiter.

**Request body (JSON)**

| Field | Required | Description |
|-------|----------|-------------|
| `schema` | yes | Database name |
| `table` | yes | Table name |
| `columns` | yes | Array of column objects (same shape as from `/api/table/meta`) |
| `filters` | no | Same format as `/api/table/data` |
| `sort` | no | Same format as `/api/table/data` |

**Response**

```json
{ "csv": "col1;col2\r\nval1;val2\r\n…", "rowCount": 342 }
```

---

## Foreign Key Values

### `GET /api/fk-values`

Returns distinct values from a referenced column for autocomplete. Without `search`: returns up to 1000 values. With `search`: returns all matching values (no limit).

**Query params**

| Param | Required | Description |
|-------|----------|-------------|
| `schema` | yes | Referenced schema |
| `table` | yes | Referenced table |
| `column` | yes | Referenced column |
| `search` | no | Filter string — matched with `LIKE %search%` |

**Response**

```json
{ "values": ["foo", "bar"], "total": 500, "loaded": 42 }
```

`total` — total distinct count before filtering. `loaded` — count of values returned.

---

## Backup

### `GET /api/backup/database/{name}`

Download a gzip-compressed mysqldump of a single database.

**URL param**: `name` — database name.

**Response**: `application/gzip` attachment, filename `YYYY-MM-DD-{name}.sql.gz`.

---

### `GET /api/backup/all`

Download a gzip-compressed mysqldump of all databases the user can access (system schemas excluded).

**Response**: `application/gzip` attachment, filename `YYYY-MM-DD-backup_all.sql.gz`.