# Dynamic Therapist API Documentation

## Overview

The Dynamic Therapist API is a **schema-less, multi-tenant system** designed for rapid MVP development without requiring predefined database schemas. It uses JSON columns to store dynamic data while maintaining type safety on the frontend and tenant isolation.

**Base URL**: `https://{{host}}/api/api` 
**EXAMPLE**: `https://ithebula.tybo.co.za/api/api/collection-data/list.php?collectionId=services&websiteId=HOGO`

## Architecture

### Core Concept
- **Schema-less**: Store any data structure without database migrations
- **Multi-tenant**: Multiple projects share one database instance via `website_id`
- **Collection-based**: Different `collection_id` values act as "virtual tables"
- **Hierarchical**: Built-in parent-child relationships with `parent_id`
- **Type-safe**: Frontend maintains TypeScript interfaces while backend stays flexible

### Database Structure
```sql
collection_data (
  id INT PRIMARY KEY,
  collection_id VARCHAR(255),  -- Acts as "table name"
  parent_id INT DEFAULT 0,     -- For hierarchical data
  website_id VARCHAR(255),     -- Tenant isolation
  data JSON,                   -- Flexible data storage
  created_at TIMESTAMP,
  updated_at TIMESTAMP
)
```

## Authentication & Tenancy

All requests require a `website_id` parameter for tenant isolation. Current supported tenants:
- `HOGO` (Primary)
- `ONLINETHALENTE` (Alternative)

## Collection Types

The system supports various collection types defined in the frontend:

```typescript
type CollectionIds = 
  | 'services'
  | 'service_categories' 
  | 'service_requests'
  | 'provinces'
  | 'cities'
  | 'referrals'
  | 'referral_settings'
```

## Core Endpoints

### Collection Data Endpoints

Base path: `/collection-data/`

#### 1. List Collection Items
```http
GET /collection-data/list.php?collectionId={collection}&websiteId={website_id}&childrenCollection={children}
```

**Parameters:**
- `collectionId` (required): The collection to query
- `websiteId` (required): Tenant identifier  
- `childrenCollection` (optional): Include child items from specified collection
- `limit` (optional): Results limit
- `offset` (optional): Pagination offset
- `sortBy` (optional): Sort field (id, created_at, or JSON field)
- `sortOrder` (optional): ASC or DESC

**Response:**
```json
[
  {
    "id": 1,
    "collection_id": "services",
    "parent_id": 0,
    "website_id": "HOGO",
    "data": {
      "name": "Therapy Session",
      "price": 150.00,
      "duration": 60
    },
    "created_at": "2025-01-01T10:00:00Z",
    "updated_at": "2025-01-01T10:00:00Z",
    "children": []
  }
]
```

#### 2. Get Single Item
```http
GET /collection-data/get.php?id={id}&websiteId={website_id}
```

#### 3. Create/Update Item
```http
POST /collection-data/save.php
PUT /collection-data/save.php
```

**Request Body:**
```json
{
  "id": 1,                    // Optional for updates
  "collection_id": "services",
  "parent_id": 0,
  "website_id": "HOGO", 
  "data": {
    "name": "New Service",
    "description": "Service description",
    "price": 200.00
  },
  "children": [               // Optional child items
    {
      "collection_id": "service_options",
      "data": { "option": "Online" }
    }
  ]
}
```

#### 4. Delete Item
```http
DELETE /collection-data/delete.php?id={id}&websiteId={website_id}
```

#### 5. Get Children
```http
GET /collection-data/get-children.php?parentId={parent_id}&collectionId={collection}
```

#### 6. Get Item With Children
```http
GET /collection-data/get-with-children.php?parentId={parent_id}
```

#### 7. Category Tree
```http
GET /collection-data/category-tree.php?websiteId={website_id}
```

#### 8. Find Parents That Have Children
```http
GET /collection-data/find-parents-that-have-children.php?collectionId={collection}&websiteId={website_id}
```

### Advanced Querying

#### 9. Search with Filters (find)
```http
POST /collection-data/find.php
```

The most powerful query endpoint. Accepts a JSON body to filter, sort, and paginate any collection. All filtering is done on JSON data fields using server-side `JSON_EXTRACT` — no schema changes needed.

**Request Body:**
```json
{
  "collection_id": "products",
  "website_id": "hot-gas",
  "filters": [
    {
      "field": "price",
      "operator": "greater_than",
      "value": 300,
      "type": "number"
    },
    {
      "field": "status",
      "operator": "equals",
      "value": "active",
      "type": "string"
    },
    {
      "field": "category",
      "operator": "in",
      "value": ["Gas Cylinders", "Accessories"],
      "type": "string"
    }
  ],
  "sortBy": "price",
  "sortOrder": "ASC",
  "limit": 20,
  "offset": 0
}
```

**All fields:**
| Field | Type | Required | Description |
|---|---|---|---|
| `collection_id` | string | ✅ | The collection to query |
| `website_id` | string | ✅ | Tenant identifier |
| `filters` | array | — | Zero or more filter objects |
| `sortBy` | string | — | Column (`id`, `created_at`, `updated_at`) or any JSON key |
| `sortOrder` | string | — | `ASC` (default) or `DESC` |
| `limit` | number | — | Max results (0 = no limit) |
| `offset` | number | — | Pagination offset |

**Filter object:**
| Field | Type | Description |
|---|---|---|
| `field` | string | JSON data key (e.g. `"price"`, `"status"`) |
| `operator` | string | See supported operators below |
| `value` | any | The value to compare against |
| `type` | string | `"string"` (default), `"number"`, or `"date"` |

**Supported Operators:**
| Operator | Description | Example |
|---|---|---|
| `equals` | Exact match | `"value": "active"` |
| `contains` | Substring match (case-insensitive) | `"value": "gas"` |
| `greater_than` | Numeric / date greater than | `"value": 100` |
| `less_than` | Numeric / date less than | `"value": 500` |
| `in` | Value is one of array | `"value": ["a", "b"]` |

**Supported Types:**
- `string` — Text comparison (default)
- `number` — Casts to `DECIMAL(18,6)` for accurate numeric comparisons
- `date` — Casts to `DATETIME` for date range comparisons

**Response:** Array of matching `ICollectionData` items (same shape as `list.php`).

**Error responses:**
```json
{ "error": "Invalid or missing collection_id" }
{ "error": "Unsupported operator: xyz" }
```

### User Collection Data

Base path: `/user-collection-data/`

#### List User Collections
```http
GET /user-collection-data/list.php?collectionId={collection}&websiteId={website_id}&limit={limit}&offset={offset}&sortBy={field}&sortOrder={order}
```

### Collection Links

Base path: `/collection-link/`

#### Add Link Between Collections
```http
POST /collection-link/add.php
```

**Request Body:**
```json
{
  "source_id": 1,
  "source_collection": "services",
  "target_id": 2, 
  "target_collection": "service_categories",
  "relation_type": "belongs_to",
  "data": { "metadata": "additional info" }
}
```

Returns the created link row. If the same source, source collection, target, target collection, and relation type already exists, the existing row is returned instead of inserting a duplicate.

#### Get Links
```http
GET /collection-link/get-links.php?sourceId={id}&relationType={relation_type}
```

Current behavior:
- `sourceId` is required.
- `relationType` is required.
- The endpoint returns raw link rows filtered by `source_id` and `relation_type`.
- The endpoint does not currently filter by `source_collection` or `target_collection`.

**Response:**
```json
[
  {
    "id": 14,
    "source_id": 54,
    "source_collection": "jobs",
    "target_id": 2,
    "target_collection": "users",
    "relation_type": "job_technician",
    "data": "{\"assigned_at\":\"2026-03-14T08:00:00Z\",\"role\":\"assistant\"}",
    "created_at": "2026-03-14 08:00:00",
    "updated_at": "2026-03-14 08:00:00"
  }
]
```

#### Delete Link
```http
DELETE /collection-link/delete.php?id={link_id}
```

Deletes a link by its row id and returns the database execution result.

#### Current Limitations
- There is no public endpoint yet for reverse lookups by target id.
- There is no public endpoint yet that hydrates the target rows.
- There is no bulk add or bulk delete endpoint.
- There is no tenant or collection scoping on the public read and delete endpoints yet.

## TypeScript Integration

### Frontend Service Example

```typescript
import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { Observable } from 'rxjs';

export type FilterOperator = 'equals' | 'contains' | 'greater_than' | 'less_than' | 'in';
export type FilterType = 'string' | 'number' | 'date';

export interface ICollectionFilter {
  field: string;
  operator: FilterOperator;
  value: any;
  type?: FilterType;
}

export interface ICollectionFindParams {
  collection_id: string;
  website_id: string;
  filters?: ICollectionFilter[];
  sortBy?: string;
  sortOrder?: 'ASC' | 'DESC';
  limit?: number;
  offset?: number;
}

@Injectable({ providedIn: 'root' })
export class CollectionDataService<T = any, J = any> {
  private apiUrl = `${Constants.ApiBase}/collection-data`;

  constructor(private http: HttpClient) {}

  getDataByCollectionId(
    collectionId: string,
    childrenCollection = ''
  ): Observable<ICollectionData<T, J>[]> {
    const url = `${this.apiUrl}/list.php?collectionId=${collectionId}&childrenCollection=${childrenCollection}&websiteId=${CollectionNames.WebsiteId}`;
    return this.http.get<ICollectionData<T, J>[]>(url);
  }

  /**
   * Advanced filter/sort/paginate search against any collection.
   * Filters are applied to JSON data fields on the server.
   *
   * @example
   * this.collectionService.find<Product>({
   *   collection_id: 'products',
   *   website_id: 'hot-gas',
   *   filters: [
   *     { field: 'status', operator: 'equals', value: 'active' },
   *     { field: 'price', operator: 'less_than', value: 500, type: 'number' }
   *   ],
   *   sortBy: 'price',
   *   sortOrder: 'ASC',
   *   limit: 20,
   *   offset: 0
   * }).subscribe(products => console.log(products));
   */
  find<T = any>(params: ICollectionFindParams): Observable<ICollectionData<T>[]> {
    return this.http.post<ICollectionData<T>[]>(`${this.apiUrl}/find.php`, params);
  }

  addData(data: ICollectionData<T, J>): Observable<ICollectionData<T, J>> {
    return this.http.post<ICollectionData<T, J>>(`${this.apiUrl}/save.php`, data);
  }
}
```

### Type Definitions

```typescript
export interface ICollectionData<T = any, J = any> {
  id: number;
  collection_id: CollectionIds;
  parent_id: number;
  website_id: string;
  data: T;                    // Strongly typed JSON data
  created_at?: string;
  updated_at?: string;
  selected?: boolean;         // UI state
  children?: ICollectionData<J>[];
}

export interface ICollectionLink {
  id: number;
  source_id: number;
  source_collection: CollectionIds;
  target_id: number;
  target_collection: CollectionIds;
  relation_type: string;
  data?: any;
  created_at?: string;
}

export type CollectionIds = 
  | 'services'
  | 'service_categories'
  | 'service_requests'
  | 'provinces' 
  | 'cities'
  | 'referrals'
  | 'referral_settings';

export enum CollectionNames {
  WebsiteId = 'HOGO',
  Services = 'services',
  ServiceCategories = 'service_categories',
  ServiceRequests = 'service_requests',
  Provinces = 'provinces',
  Cities = 'cities',
  Referrals = 'referrals',
  ReferralSettings = 'referral_settings'
}
```

## Data Sanitization

The API automatically sanitizes data before saving:
- Removes underscore-prefixed keys (e.g., `_meta`, `_temp`)
- Recursively cleans nested objects and arrays
- Preserves data integrity while removing editor metadata

## Error Handling

### Standard HTTP Status Codes
- `200`: Success
- `400`: Bad Request (validation errors)
- `404`: Resource not found  
- `500`: Internal server error

### Error Response Format
```json
{
  "error": true,
  "message": "Error description",
  "code": "ERROR_CODE"
}
```

## Performance Considerations

### Indexing Recommendations
```sql
-- Essential indexes for performance
CREATE INDEX idx_collection_website ON collection_data(collection_id, website_id);
CREATE INDEX idx_parent_website ON collection_data(parent_id, website_id);
CREATE INDEX idx_website_created ON collection_data(website_id, created_at);

-- JSON field indexes (MySQL 5.7+)
CREATE INDEX idx_json_name ON collection_data((JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))));
CREATE INDEX idx_json_price ON collection_data((CAST(JSON_UNQUOTE(JSON_EXTRACT(data, '$.price')) AS DECIMAL(10,2))));
```

### Best Practices
1. **Limit large queries**: Use pagination with reasonable limits
2. **Index JSON fields**: Create indexes for frequently queried JSON fields
3. **Batch operations**: Use bulk update methods for multiple items
4. **Cache results**: Implement caching for frequently accessed data
5. **Monitor performance**: Watch for N+1 queries in hierarchical data

## Migration Strategy

When ready to normalize the database:

1. **Analyze data patterns** from JSON columns
2. **Create normalized tables** based on usage patterns  
3. **Implement migration scripts** to move data
4. **Update API endpoints** to use new schema
5. **Maintain backward compatibility** during transition

## Security Features

- **Tenant isolation**: All queries filtered by `website_id`
- **Input sanitization**: Automatic cleaning of metadata
- **Prepared statements**: SQL injection prevention
- **Transaction safety**: Atomic operations for data integrity

## Usage Examples

### Creating a Service
```typescript
const service = {
  collection_id: 'services',
  parent_id: 0,
  website_id: 'HOGO',
  data: {
    name: 'Individual Therapy',
    description: 'One-on-one counseling session',
    price: 150.00,
    duration: 60,
    category: 'mental_health'
  }
};

this.collectionService.addData(service).subscribe(result => {
  console.log('Service created:', result);
});
```

### Querying with Filters
```typescript
// Find all active products under R500, sorted cheapest first
this.collectionService.find<Product>({
  collection_id: 'products',
  website_id: 'hot-gas',
  filters: [
    { field: 'status',   operator: 'equals',    value: 'active' },
    { field: 'price',    operator: 'less_than', value: 500, type: 'number' },
    { field: 'category', operator: 'in',        value: ['Gas Cylinders'] }
  ],
  sortBy: 'price',
  sortOrder: 'ASC',
  limit: 20,
  offset: 0
}).subscribe(products => {
  this.products = products.map(p => p.data);
});

// Find delivered orders for a specific user
this.collectionService.find<Order>({
  collection_id: 'orders',
  website_id: 'hot-gas',
  filters: [
    { field: 'userId', operator: 'equals', value: 18,          type: 'number' },
    { field: 'status', operator: 'equals', value: 'delivered' }
  ],
  sortBy: 'created_at',
  sortOrder: 'DESC'
}).subscribe(orders => {
  this.orders = orders;
});
```

### Building Hierarchical Data
```typescript
const category = {
  collection_id: 'service_categories',
  parent_id: 0,
  website_id: 'HOGO',
  data: { name: 'Therapy Services' },
  children: [
    {
      collection_id: 'services',
      data: { name: 'Individual Therapy', price: 150 }
    },
    {
      collection_id: 'services', 
      data: { name: 'Group Therapy', price: 100 }
    }
  ]
};
```

## Conclusion

This Dynamic Therapist API provides a powerful foundation for rapid MVP development while maintaining the flexibility to evolve into a fully normalized system. The combination of schema-less storage with type-safe frontend integration offers the best of both worlds: development speed and code reliability.

For additional support or feature requests, please refer to the API endpoint documentation or contact the development team.