IDataEngine Contract

import type {
  BaseEngineOptions,
  EngineQueryOptions,
  DataEngineInsertOptions,
  EngineUpdateOptions,
  EngineDeleteOptions,
  EngineAggregateOptions,
  EngineCountOptions,
  DataEngineRequest,
} from '@objectstack/spec/data';

export interface IDataEngine {
  // Query (reads take the execution context in a TRAILING options argument —
  // the same position the write methods take theirs)
  find(objectName: string, query?: EngineQueryOptions, options?: BaseEngineOptions): Promise<any[]>;
  findOne(objectName: string, query?: EngineQueryOptions, options?: BaseEngineOptions): Promise<any>;
  count(objectName: string, query?: EngineCountOptions, options?: BaseEngineOptions): Promise<number>;
  aggregate(objectName: string, query: EngineAggregateOptions, options?: BaseEngineOptions): Promise<any[]>;

  // Mutation (write ops also accept in-process WriteObservabilityOptions — see `update`)
  insert(objectName: string, data: any | any[], options?: DataEngineInsertOptions & WriteObservabilityOptions): Promise<any>;
  update(objectName: string, data: any, options?: EngineUpdateOptions & WriteObservabilityOptions): Promise<any>;
  delete(objectName: string, options?: EngineDeleteOptions): Promise<any>;

  // AI / Vector Search (optional)
  vectorFind?(objectName: string, vector: number[], options?: {
    where?: any; limit?: number; fields?: string[]; threshold?: number;
  }): Promise<any[]>;

  // Batch Operations (optional, transactional)
  batch?(requests: DataEngineRequest[], options?: { transaction?: boolean }): Promise<any[]>;

  // Raw Command Escape Hatch (optional)
  execute?(command: any, options?: Record<string, any>): Promise<any>;

  // Driver Registry (optional — engines that own named drivers)
  getDefaultDriverName?(): string | undefined;
  getDriverByName?(name: string): IDataDriver | undefined;
}

const tasks = await engine.find('task', {
  where: {
    status: 'open',
    priority: { $in: ['high', 'critical'] },
  },
  orderBy: [{ field: 'due_date', order: 'asc' }],
  limit: 20,
  offset: 0,
  fields: ['id', 'title', 'status', 'priority', 'due_date'],
});

interface EngineQueryOptions {
  where?: FilterCondition;                   // WHERE clause — MongoDB-style $op
  fields?: FieldNode[];                      // SELECT — the object's OWN column names; related
                                             // data comes from `expand`, not a dotted path (#7532)
  orderBy?: SortNode[];                      // ORDER BY
  limit?: number;                            // LIMIT
  offset?: number;                           // OFFSET
  top?: number;                              // Alias for limit (OData compat)
  search?: string | FullTextSearch;          // Full-text search — the bare query text
                                             // is canonical (ADR-0061 D1); the object
                                             // form carries the Tier-2 knobs (#7178)
  searchFields?: string[];                   // Fields the `search` expansion may match
                                             // against — a validated override, intersected
                                             // with the object's declared/derived
                                             // searchable set (ADR-0061 D1)
  expand?: Record<string, QueryAST>;         // Recursive relation loading
  context?: ExecutionContext;            // Identity, tenant, transaction — any subset
}

// Implicit equality
where: { status: 'active' }

// Explicit operators
where: { amount: { $gt: 50000 } }

// Logical combinations
where: {
  $and: [
    { status: 'open' },
    { priority: { $in: ['high', 'critical'] } },
  ],
}

// Logical OR
where: {
  $or: [
    { role: 'admin' },
    { email: { $contains: '@company.com' } },
  ],
}

// Nested relation filter
where: {
  account: { industry: 'tech' },
}

const task = await engine.findOne('task', {
  where: { title: 'Implement login page' },
  fields: ['id', 'title', 'status'],
});

await engine.findOne('task', {});                    // throws
await engine.findOne('task', {                       // the newest task
  orderBy: [{ field: 'created_at', order: 'desc' }],
});
await engine.find('task', { limit: 1 });             // any task will do

const openTasks = await engine.count('task', {
  where: { status: 'open' },
});
// 23

const task = await engine.insert('task', {
  title: 'Implement login page',
  status: 'open',
  priority: 'high',
  assigned_to: 'usr_01HQ3V5K8N2M4P6R7T9W',
});

console.log(task.id);         // "tsk_01HQ4A7B9D3F5G8J2K4L"
console.log(task.created_at); // "2025-01-20T10:30:00.000Z"

const tasks = await engine.insert('task', [
  { title: 'Task 1', status: 'open', priority: 'high' },
  { title: 'Task 2', status: 'open', priority: 'medium' },
  { title: 'Task 3', status: 'open', priority: 'low' },
]);

interface DataEngineInsertOptions {
  returning?: boolean;          // Return inserted record(s)? Default: true
  context?: ExecutionContext;  // Identity, tenant, transaction — any subset
}

const updated = await engine.update('task', 
  { status: 'in_progress', estimated_hours: 12 },
  { where: { id: 'tsk_01HQ4A7B9D3F5G8J2K4L' } }
);

interface EngineUpdateOptions {
  where?: FilterCondition;      // Filter to identify records (WHERE)
  upsert?: boolean;             // Insert if not found? Default: false
  multi?: boolean;              // Update multiple records? Default: false
  returning?: boolean;          // Return updated record(s)? Default: false
  context?: ExecutionContext;
}

interface WriteObservabilityOptions {
  onFieldsDropped?: (event: DroppedFieldsEvent) => void;
  strictReadonlyWrites?: boolean;  // refuse instead of stripping. Default: false
}

interface DroppedFieldsEvent {
  object: string;   // resolved object name
  fields: string[]; // caller-supplied fields that were dropped
  // why they were dropped — an OPEN vocabulary that grows with the write
  // path's legal strips; branch on it exhaustively, never with a binary test
  reason: 'readonly' | 'readonly_when' | 'primary_key';
}

await engine.delete('task', {
  where: { id: 'tsk_01HQ4A7B9D3F5G8J2K4L' },
});

interface EngineDeleteOptions {
  where?: FilterCondition;      // Filter to identify records (WHERE)
  multi?: boolean;              // Delete multiple records? Default: false
  context?: ExecutionContext;
}

const result = await engine.aggregate('task', {
  where: { project: 'prj_01HQ3V5K8N' },
  groupBy: ['status'],
  aggregations: [
    { function: 'count', alias: 'task_count' },
    { function: 'sum', field: 'estimated_hours', alias: 'total_hours' },
    { function: 'avg', field: 'estimated_hours', alias: 'avg_hours' },
  ],
});

// result:
// [
//   { status: 'open', task_count: 15, total_hours: 120, avg_hours: 8 },
//   { status: 'in_progress', task_count: 8, total_hours: 96, avg_hours: 12 },
//   { status: 'done', task_count: 24, total_hours: 192, avg_hours: 8 },
// ]

interface EngineAggregateOptions {
  where?: FilterCondition;                 // Pre-aggregation filter (WHERE)
  groupBy?: string[];                      // GROUP BY fields
  aggregations?: AggregationNode[];        // Aggregation definitions
  context?: ExecutionContext;
}

interface AggregationNode {
  function: 'count' | 'sum' | 'avg' | 'min' | 'max' | 'count_distinct';
  field?: string;           // Field to aggregate (optional for COUNT(*))
  alias: string;            // Result column alias
  filter?: FilterCondition; // Per-aggregation FILTER WHERE
}

const results = await engine.vectorFind?.('document', embeddingVector, {
  where: { category: 'technical' },
  fields: ['title', 'content'],
  limit: 5,
  threshold: 0.8,
});

const results = await engine.batch?.([
  { method: 'insert', object: 'project', data: { name: 'Website Redesign', status: 'active' } },
  { method: 'insert', object: 'task', data: { title: 'Design mockups', status: 'open' } },
  { method: 'insert', object: 'task', data: { title: 'Implement frontend', status: 'open' } },
], { transaction: true });

const result = await engine.execute?.(
  'SELECT * FROM tasks WHERE status = $1',
  { params: ['open'] }
);

const driverName = engine.getDefaultDriverName?.();
const driver = driverName ? engine.getDriverByName?.(driverName) : undefined;