source/teaql-ts/tests/sql-relation.test.ts

import { UserContext } from '../src/core/context';
import { OrderBy, SelectQuery } from '../src/core/ast';
import { EntitySchema } from '../src/sql/core';
import { SQLiteDriver, SQLiteTeaQLClient } from '../src/sql/sqlite';
import { executeRelationFacets } from '../src/core/facet';
import { RuntimeOperation, RuntimeTelemetry } from '../src/core/telemetry';

const schemas: Record<string, EntitySchema> = {
  Order: {
    table: 'orders',
    columns: {
      id: { columnName: 'id', logicalType: 'integer', decode: 'string' },
      version: { columnName: 'version', logicalType: 'integer', decode: 'number' },
    },
    relations: {
      lines: { targetEntity: 'OrderLine', localKey: 'id', foreignKey: 'orderId', many: true },
    },
  },
  OrderLine: {
    table: 'orderline',
    columns: {
      id: { columnName: 'id', logicalType: 'integer', decode: 'string' },
      version: { columnName: 'version', logicalType: 'integer', decode: 'number' },
      orderId: { columnName: 'order_id', logicalType: 'integer', decode: 'string' },
      name: { columnName: 'name', logicalType: 'text', decode: 'native' },
      state: { columnName: 'state', logicalType: 'text', decode: 'native' },
      amount: { columnName: 'amount', logicalType: 'decimal', decode: 'number' },
    },
  },
  QueryRecord: {
    table: 'query_record',
    columns: {
      id: { columnName: 'id', logicalType: 'integer', decode: 'string' },
      version: { columnName: 'version', logicalType: 'integer', decode: 'number' },
      requiredText: { columnName: 'required_text', logicalType: 'text', decode: 'native' },
      optionalText: { columnName: 'optional_text', logicalType: 'text', decode: 'native' },
      requiredInteger: { columnName: 'required_integer', logicalType: 'integer', decode: 'number' },
      optionalLong: { columnName: 'optional_long', logicalType: 'integer', decode: 'string' },
      requiredDecimal: { columnName: 'required_decimal', logicalType: 'decimal', decode: 'string' },
      requiredFloat: { columnName: 'required_float', logicalType: 'double', decode: 'number' },
      requiredDouble: { columnName: 'required_double', logicalType: 'double', decode: 'number' },
      requiredDate: { columnName: 'required_date', logicalType: 'date', decode: 'date' },
      requiredTime: { columnName: 'required_time', logicalType: 'integer', decode: 'number' },
      requiredTimestamp: { columnName: 'required_timestamp', logicalType: 'datetime', decode: 'date' },
      active: { columnName: 'active', logicalType: 'boolean', decode: 'native' },
      reviewed: { columnName: 'reviewed', logicalType: 'boolean', decode: 'native' },
    },
  },
};

describe('SQLite relation loading', () => {
  it('executes the complete scalar fixture including nullable boolean on SQLite', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    const rows = [
      ['1', 'Alpha', 'optional', 42, '42000000000', '42.125', 42.5, 42.75, '2026-08-29', 34_200_000, '2026-08-29T09:30:00.000Z', true, false],
      ['2', 'Beta', null, 7, null, '7.500', 7.5, 7.75, '2026-08-30', 36_000_000, '2026-08-30T10:00:00.000Z', false, null],
      ['3', 'Gamma', 'tail', 99, '99000000000', '99.875', 99.5, 99.75, '2026-08-31', 37_800_000, '2026-08-31T10:30:00.000Z', true, true],
    ] as const;
    for (const [id, requiredText, optionalText, requiredInteger, optionalLong,
      requiredDecimal, requiredFloat, requiredDouble, requiredDate, requiredTime,
      requiredTimestamp, active, reviewed] of rows) {
      await client.executeMutation({
        entity: 'QueryRecord', action: 'Create', id,
        payload: {
          requiredText, optionalText, requiredInteger, optionalLong, requiredDecimal,
          requiredFloat, requiredDouble, requiredDate: new Date(`${requiredDate}T00:00:00.000Z`),
          requiredTime, requiredTimestamp: new Date(requiredTimestamp), active, reviewed,
        },
        comment: 'seed complete query scalar fixture',
      });
    }

    const query = (filter: Record<string, unknown>) => client.executeQuery<any>(
      new SelectQuery('QueryRecord').filter(filter)
        .comment('what: execute complete scalar predicate')
        .purpose('why: retain Query conformance evidence')
        .order(OrderBy.asc('id')),
    );
    expect((await query({ requiredText: { $eq: 'Alpha' } })).map(row => row.id)).toEqual(['1']);
    expect((await query({ requiredText: { $ne: 'Alpha' } })).map(row => row.id)).toEqual(['2', '3']);
    expect((await query({ requiredText: { $in: ['Alpha', 'Gamma'] } })).map(row => row.id)).toEqual(['1', '3']);
    expect((await query({ requiredText: { $startsWith: 'Al' } })).map(row => row.id)).toEqual(['1']);
    expect((await query({ requiredText: { $endsWith: 'ma' } })).map(row => row.id)).toEqual(['3']);
    expect((await query({ requiredText: { $contains: 'et' } })).map(row => row.id)).toEqual(['2']);
    expect((await query({ requiredInteger: { $between: [40, 100] } })).map(row => row.id)).toEqual(['1', '3']);
    expect((await query({ requiredDecimal: { $gt: '50' } })).map(row => row.id)).toEqual(['3']);
    expect((await query({ requiredFloat: { $lte: 7.5 } })).map(row => row.id)).toEqual(['2']);
    expect((await query({ requiredDouble: { $gte: 99.75 } })).map(row => row.id)).toEqual(['3']);
    expect((await query({ requiredDate: { $between: [new Date('2026-08-30T00:00:00.000Z'), new Date('2026-08-31T00:00:00.000Z')] } })).map(row => row.id)).toEqual(['2', '3']);
    expect((await query({ requiredTime: { $gt: 36_000_000 } })).map(row => row.id)).toEqual(['3']);
    expect((await query({ requiredTimestamp: { $lt: new Date('2026-08-30T12:00:00.000Z') } })).map(row => row.id)).toEqual(['1', '2']);
    expect((await query({ optionalText: { $isNull: true } })).map(row => row.id)).toEqual(['2']);
    expect((await query({ optionalLong: { $isNull: false } })).map(row => row.id)).toEqual(['1', '3']);
    expect((await query({ active: { $eq: false } })).map(row => row.id)).toEqual(['2']);
    expect((await query({ reviewed: { $eq: true } })).map(row => row.id)).toEqual(['3']);
    expect((await query({ reviewed: { $eq: false } })).map(row => row.id)).toEqual(['1']);
    expect((await query({ reviewed: { $isNull: true } })).map(row => row.id)).toEqual(['2']);
    await client.close();
  });

  it('executes positive and negative relation subqueries with bound values', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    await client.executeMutation({ entity: 'Order', action: 'Create', id: '1', payload: {}, comment: 'seed first order' });
    await client.executeMutation({ entity: 'Order', action: 'Create', id: '2', payload: {}, comment: 'seed second order' });
    await client.executeMutation({ entity: 'Order', action: 'Create', id: '3', payload: {}, comment: 'seed empty order' });
    await client.executeMutation({ entity: 'OrderLine', action: 'Create', id: '11', payload: { orderId: '1', name: 'included' }, comment: 'seed included line' });
    await client.executeMutation({ entity: 'OrderLine', action: 'Create', id: '12', payload: { orderId: '2', name: 'excluded' }, comment: 'seed excluded line' });
    await client.executeMutation({ entity: 'OrderLine', action: 'Create', id: '13', payload: { orderId: null, name: 'orphan' }, comment: 'seed orphan line' });
    const firstOrder = new SelectQuery('Order').filter({ id: { $eq: '1' } });

    const included = await client.executeQuery<any>(
      new SelectQuery('OrderLine')
        .filter({ orderId: { $inSubquery: { query: firstOrder, field: 'id' } } })
        .comment('what: select lines of first order')
        .purpose('why: verify positive relation predicate'),
    );
    const excluded = await client.executeQuery<any>(
      new SelectQuery('OrderLine')
        .filter({ orderId: { $notInSubquery: { query: firstOrder, field: 'id' } } })
        .comment('what: exclude lines of first order')
        .purpose('why: verify negative relation predicate'),
    );

    expect(included.map(row => row.name)).toEqual(['included']);
    expect(excluded.map(row => row.name)).toEqual(['excluded']);

    const lineIds = async (filter: Record<string, unknown>) => (await client.executeQuery<any>(
      new SelectQuery('OrderLine').filter(filter).order(OrderBy.asc('id'))
        .comment('what: execute complete forward relation predicate')
        .purpose('why: retain complete relation fixture evidence'),
    )).map(row => row.id);
    const orderIds = async (filter: Record<string, unknown>) => (await client.executeQuery<any>(
      new SelectQuery('Order').filter(filter).order(OrderBy.asc('id'))
        .comment('what: execute complete reverse relation predicate')
        .purpose('why: retain complete relation fixture evidence'),
    )).map(row => row.id);
    expect(await lineIds({ orderId: { $isNull: false } })).toEqual(['11', '12']);
    expect(await lineIds({ orderId: { $isNull: true } })).toEqual(['13']);
    expect(await lineIds({ orderId: { $inSubquery: { query: firstOrder, field: 'id' } } })).toEqual(['11']);
    expect(await lineIds({ orderId: { $notInSubquery: { query: firstOrder, field: 'id' } } })).toEqual(['12']);

    const allLines = new SelectQuery('OrderLine');
    expect(await orderIds({ id: { $inSubquery: { query: allLines, field: 'orderId' } } })).toEqual(['1', '2']);
    expect(await orderIds({ id: { $notInSubquery: { query: allLines, field: 'orderId' } } })).toEqual(['3']);
    expect(client.sqlTrace.some(sql => sql.includes(' IN (SELECT '))).toBe(true);
    expect(client.sqlTrace.some(sql => sql.includes(' NOT IN (SELECT '))).toBe(true);
    await client.close();
  });

  it('counts the exact filtered set without page, order, projection, or relations', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    for (const id of ['11', '12', '13', '14', '15', '16']) {
      await client.executeMutation({ entity: 'Order', action: 'Create', id, payload: {}, comment: 'seed page' });
    }
    const query = new SelectQuery('Order')
      .filter({ id: { $lte: '15' } }).order(OrderBy.asc('id')).offset(2).limit(2)
      .relationQuery('lines', new SelectQuery('OrderLine'))
      .purpose('render page').comment('list filtered orders');
    const rows = await client.executeQuery<any>(query);
    const total = await client.executeCount(query);
    expect(rows.map(row => row.id)).toEqual(['13', '14']);
    expect(total).toBe(5);
    const countSql = client.sqlTrace[client.sqlTrace.length - 1];
    expect(countSql).toContain('COUNT(');
    expect(countSql).not.toContain(' ORDER BY ');
    expect(countSql).not.toContain(' OFFSET ');
    await client.close();
  });
  it('enforces the formal runtime hard limit at the SQL list boundary', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    const query = new SelectQuery('Order').purpose('test hard limit').comment('bounded list');

    await client.executeQuery(query);

    expect(query.limitValue).toBe(10_000);
    expect(client.sqlTrace[client.sqlTrace.length - 1]).toContain(' LIMIT ');
    await expect(client.executeQuery(
      new SelectQuery('Order')
        .purpose('test hard limit rejection')
        .comment('reject oversized list')
        .limit(10_001),
    )).rejects.toThrow('QUERY_HARD_LIMIT_EXCEEDED');
    await client.close();
  });

  it('enforces runtime governance and sends immutable audit events to the app sink', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    await expect(client.executeQuery(new SelectQuery('Order'))).rejects.toMatchObject({
      code: 'REQUEST_COMMENT_REQUIRED', field: 'comment', requestKind: 'query',
    });
    await expect(client.executeMutation({ entity: 'Order', action: 'Create', payload: {} }))
      .rejects.toMatchObject({ code: 'REQUEST_COMMENT_REQUIRED', field: 'comment', requestKind: 'mutation' });
    const sink: Readonly<Record<string, unknown>>[] = [];
    client.setAuditSink(event => { sink.push(event); });
    await client.executeMutation({ entity: 'Order', action: 'Create', id: '10', payload: {}, comment: 'create governed order' });
    expect(client.auditTrace).toHaveLength(1);
    expect(sink).toHaveLength(1);
    expect(Object.isFrozen(sink[0])).toBe(true);
    expect(sink[0].reason).toBe('create governed order');
    await client.close();
  });

  it('executes inclusive range and IN predicates in SQL', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    for (const id of ['11', '12', '13']) {
      await client.executeMutation({ entity: 'Order', action: 'Create', id, payload: {}, comment: 'seed order' });
    }
    const rows = await client.executeQuery<any>(
      new SelectQuery('Order').purpose('test range').comment('range query').filter({
        $and: [
          { id: { $in: ['11', '12', '99'] } },
          { id: { $gte: '11' } },
          { id: { $lte: '12' } },
        ],
      }).order(OrderBy.asc('id')),
    );
    expect(rows.map(row => row.id)).toEqual(['11', '12']);
    const sql = client.sqlTrace[client.sqlTrace.length - 1];
    expect(sql).toContain(' IN (');
    expect(sql).toContain(' >= ');
    expect(sql).toContain(' <= ');
    await client.close();
  });

  it('registers Soundex during ensureSchema and executes SoundingLike on SQLite', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    for (const [id, name] of [['101', 'Robert'], ['102', 'Rupert'], ['103', 'Alice']]) {
      await client.executeMutation({ entity: 'OrderLine', action: 'Create', id, payload: { name }, comment: 'seed phonetic query' });
    }
    const rows = await client.executeQuery<any>(
      new SelectQuery('OrderLine').purpose('test phonetic query').comment('execute registered soundex')
        .filter({ name: { $soundLike: 'Robert' } }).order(OrderBy.asc('id')),
    );
    expect(rows.map(row => row.name)).toEqual(['Robert', 'Rupert']);
    await client.close();
  });

  it('computes relation facet membership with grouped SQL and include-all semantics', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    for (const id of ['11', '12', '13']) {
      await client.executeMutation({ entity: 'Order', action: 'Create', id, payload: {}, comment: 'seed order facet' });
    }
    for (const [id, orderId, name, amount] of [
      ['101', '11', 'Riverside one', 120], ['102', '11', 'Riverside two', 80], ['103', '12', 'Other', 50],
    ]) {
      await client.executeMutation({
        entity: 'OrderLine', action: 'Create', id, payload: { orderId, name, amount }, comment: 'seed line facet',
      });
    }
    const outer = new SelectQuery('OrderLine')
      .purpose('render order facet').comment('count Riverside lines')
      .filter({ name: { $contains: 'Riverside' } });
    outer.facetBy('orders', 'orderId', {
      toQuery: () => new SelectQuery('Order')
        .aggregate('Count', 'id', 'lineCount')
        .aggregate('Sum', 'amount', 'totalAmount')
        .purpose('render order facet').comment('load order facet values'),
    });

    const facets = await executeRelationFacets(client, query => query, outer, outer.facets);
    expect(facets.orders.map(row => [row.id, row.lineCount, row.totalAmount])).toEqual([
      ['11', 2, 200], ['12', 0, 0], ['13', 0, 0],
    ]);
    expect(client.sqlTrace.some(sql => sql.includes('GROUP BY')
      && sql.includes('COUNT(') && sql.includes('SUM('))).toBe(true);
    await client.close();
  });

  it('applies nested limit independently to every parent', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    for (const orderId of ['11', '12']) {
      await client.executeMutation({ entity: 'Order', action: 'Create', id: orderId, payload: {}, comment: 'seed order' });
      for (let index = 1; index <= 5; index++) {
        const id = `${orderId}${index}`;
        await client.executeMutation({
          entity: 'OrderLine',
          action: 'Create',
          id,
          payload: { orderId, name: `line-${id}` },
          comment: 'seed line',
        });
      }
    }
    const query = new SelectQuery('Order').purpose('test relations').comment('relation query')
      .order(OrderBy.asc('id'))
      .relationQuery('lines', new SelectQuery('OrderLine').order(OrderBy.desc('id')).limit(3));

    const rows = await client.executeQuery<any>(query);

    expect(rows).toHaveLength(2);
    expect(rows.map(row => row.lines.length)).toEqual([3, 3]);
    expect(rows.flatMap(row => row.lines).every(
      (line: any) => line.__teaql_partition_rank === undefined,
    )).toBe(true);
    await client.close();
  });

  it('loads relation assembly keys implicitly without exposing them in the requested shape', async () => {
    const client = new SQLiteTeaQLClient(':memory:', schemas);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    await client.executeMutation({
      entity: 'Order', action: 'Create', id: '21', payload: {}, comment: 'seed projected relation parent',
    });
    await client.executeMutation({
      entity: 'OrderLine', action: 'Create', id: '211',
      payload: { orderId: '21', name: 'visible name', state: 'internal state' },
      comment: 'seed projected relation child',
    });
    const child = new SelectQuery('OrderLine').select(['name']);
    const rows = await client.executeQuery<any>(
      new SelectQuery('Order')
        .relationQuery('lines', child)
        .comment('what: load a projected child relation')
        .purpose('why: prove relation keys remain runtime-internal'),
    );

    expect(child.selectItems).toEqual(['name']);
    expect(rows[0].lines).toHaveLength(1);
    expect(rows[0].lines[0]).toMatchObject({ name: 'visible name' });
    expect(rows[0].lines[0]).not.toHaveProperty('orderId');
    expect(rows[0].lines[0]).not.toHaveProperty('state');
    expect(client.sqlTrace[1]).toContain('order_id');
    await client.close();
  });

  it('TOPN-001..011 selects governed plans with equivalent SQLite results', async () => {
    const operations: RuntimeOperation[] = [];
    const telemetry: RuntimeTelemetry = {
      start(operation) {
        operations.push(operation);
        return { success: () => undefined, failure: () => undefined };
      },
    };
    const client = new SQLiteTeaQLClient(':memory:', schemas).setRuntimeTelemetry(telemetry);
    await new UserContext().insertResource('dataService', client).ensureSchema();
    for (const orderId of ['1', '2', '3']) {
      await client.executeMutation({
        entity: 'Order', action: 'Create', id: orderId, payload: {}, comment: 'seed Top-N order',
      });
    }
    for (const [id, orderId, state] of [
      ['11', '1', 'visible'], ['12', '1', 'visible'], ['13', '1', 'visible'],
      ['14', '1', 'hidden'], ['21', '2', 'visible'], ['22', '2', 'visible'],
      ['23', '2', 'visible'],
    ]) {
      await client.executeMutation({
        entity: 'OrderLine', action: 'Create', id,
        payload: { orderId, name: 'same', state }, comment: 'seed Top-N line',
      });
    }

    const load = async (threshold?: number) => {
      client.sqlTrace.length = 0;
      const child = new SelectQuery('OrderLine')
        .filter({ state: { $eq: 'visible' } })
        .order(OrderBy.desc('name')).limit(2);
      if (threshold !== undefined) child.topNProbeParentThreshold(threshold);
      const rows = await client.executeQuery<any>(
        new SelectQuery('Order').order(OrderBy.asc('id'))
          .relationQuery('lines', child)
          .comment('what: load per-parent Top-N fixture')
          .purpose('why: verify plan equivalence'),
      );
      return {
        result: Object.fromEntries(rows.map(row => [row.id, row.lines.map((line: any) => line.id)])),
        sql: [...client.sqlTrace],
      };
    };

    const probe = await load();
    const window = await load(0);
    expect(probe.result).toEqual(window.result);
    expect(window.result).toEqual({ '1': ['11', '12'], '2': ['21', '22'], '3': [] });
    expect(probe.sql).toHaveLength(4);
    expect(window.sql).toHaveLength(2);
    expect(window.sql[1]).toContain('ROW_NUMBER() OVER');
    expect([...probe.sql, ...window.sql].some(sql => /count\s*\(/i.test(sql))).toBe(false);

    expect((await load(3)).sql).toHaveLength(4);
    expect((await load(2)).sql).toHaveLength(2);
    expect((await load(3)).result).toEqual((await load(3)).result);

    client.sqlTrace.length = 0;
    const empty = await client.executeQuery<any>(
      new SelectQuery('Order').filter({ id: { $eq: '999' } })
        .relationQuery('lines', new SelectQuery('OrderLine').limit(2))
        .comment('what: load empty Top-N parents').purpose('why: verify typed empty result'),
    );
    expect(empty).toEqual([]);
    expect(client.sqlTrace).toHaveLength(1);

    const relationOperation = [...operations].reverse().find(operation =>
      operation.family === 'relation_load' && operation.attributes?.['teaql.relation.parent_count'] === 3,
    );
    expect(relationOperation?.attributes).toMatchObject({
      'teaql.relation.parent_count': 3,
      'teaql.relation.per_parent_limit': 2,
      'teaql.relation.configured_probe_threshold': 3,
      'teaql.relation.selected_plan': 'bounded_probes',
      'teaql.relation.probe_count': 3,
    });
    await client.close();
  });

  it('TOPN-012 ensures the canonical relation index idempotently on SQLite', async () => {
    const driver = new SQLiteDriver(':memory:');
    await driver.ensureSchema(schemas);
    await driver.ensureSchema(schemas);
    const indexes = await driver.query(
      "SELECT name, sql FROM sqlite_master WHERE type='index' " +
      "AND tbl_name='orderline' AND name='idx_orderline_order_id_id'",
    );
    expect(indexes.rows).toHaveLength(1);
    await driver.close();
  });
});