<commit_before>import { Pool, Client, QueryResult, PoolClient, Notification } from 'pg';
import migrate from 'node-pg-migrate';
import logger from '@cardstack/logger';
import postgresConfig from './postgres-config';
import { join } from 'path';
import * as JSON from 'json-typescript';
import {
  upsert,
  param,
  safeName,
  every,
  CardExpression,
  Expression,
  any,
  isParam,
  addExplicitParens,
  PgPrimitive,
  fieldQuery,
  fieldArity,
  fieldValue,
  FieldQuery,
  FieldValue,
  FieldArity,
} from './util';
import { AddressableCard, CardId, Card, FieldCard, cardstackFieldPattern } from '../card';
import CardstackError from '../error';
import { Query, baseType, Filter, EqFilter, RangeFilter } from '../query';
import { Sorts } from './sorts';
import snakeCase from 'lodash/snakeCase';
import flatten from 'lodash/flatten';
import assertNever from 'assert-never';
import { ScopedCardService } from '../cards-service';

const log = logger('cardstack/pgsearch');

function config() {
  return postgresConfig({
    database: `pgsearch_${process.env.PGSEARCH_NAMESPACE || 'dev'}`,
  });
}

export default class PgClient {
  private pool: Pool;

  constructor() {
    let c = config();
    this.pool = new Pool({
      user: c.user,
      host: c.host,
      database: c.database,
      password: <PASSWORD>,
      port: c.port,
    });
  }

  async teardown() {
    if (this.pool) {
      await this.pool.end();
    }
  }

  async ready() {
    await this.migrateDb();
  }

  private async migrateDb() {
    const config = postgresConfig();
    let client = new Client(Object.assign({}, config, { database: 'postgres' }));
    try {
      await client.connect();
      let response = await client.query(`select count(*)=1 as has_database from pg_database where datname=$1`, [
        config.database,
      ]);
      if (!response.rows[0].has_database) {
        await client.query(`create database ${safeName(config.database)}`);
      }
    } finally {
      client.end();
    }

    await migrate({
      direction: 'up',
      migrationsTable: 'migrations',
      singleTransaction: true,
      checkOrder: false,
      databaseUrl: {
        user: config.user,
        host: config.host,
        database: config.database,
        password: <PASSWORD>,
        port: config.port,
      },
      count: Infinity,
      ignorePattern: '.*\\.(?!js)[^.]+',
      dir: join(__dirname, 'migrations'),
      log: (...args) => log.debug(...args),
    });
  }

  static async deleteSearchIndexIHopeYouKnowWhatYouAreDoing() {
    let c = config();
    let client = new Client(Object.assign({}, c, { database: 'postgres' }));
    try {
      await client.connect();
      await client.query(`drop database if exists ${safeName(c.database)}`);
    } finally {
      client.end();
    }
  }

  async queryCards(cards: ScopedCardService, query: CardExpression): Promise<QueryResult> {
    let client = await this.pool.connect();
    let sql = await this.cardQueryToSQL(cards, query);
    log.trace('search: %s trace: %j', sql.text, sql.values);
    try {
      return await client.query(sql);
    } finally {
      client.release();
    }
  }

  async query(query: Expression): Promise<QueryResult> {
    let client = await this.pool.connect();
    let sql = await this.expressionToSql(query);
    log.trace('search: %s trace: %j', sql.text, sql.values);
    try {
      return await client.query(sql);
    } finally {
      client.release();
    }
  }

  async withConnection<T>(
    fn: (connection: { client: PoolClient; query: (e: Expression) => Promise<QueryResult> }) => Promise<T>
  ): Promise<T> {
    let client = await this.pool.connect();
    let query = async (expression: Expression) => {
      let sql = this.expressionToSql(expression);
      log.trace('search: %s trace: %j', sql.text, sql.values);
      return await client.query(sql);
    };
    try {
      return await fn({ query, client });
    } finally {
      client.release();
    }
  }

  async listen(channel: string, handler: (notification: Notification) => void, fn: () => Promise<void>) {
    // I found that LISTEN/NOTIFY doesn't work reliably on connections from the
    // Pool, and this is substantiated by commentary on GitHub:
    //   https://github.com/brianc/node-postgres/issues/1543#issuecomment-353622236
    // So for listen purposes, we establish a completely separate connection.
    let c = config();
    let client = new Client({
      user: c.user,
      host: c.host,
      database: c.database,
      password: <PASSWORD>,
      port: c.port,
    });
    await client.connect();
    try {
      client.on('notification', n => {
        log.trace(`heard pg notification for channel %s`, n.channel);
        handler(n);
      });
      await client.query(`LISTEN ${safeName(channel)}`);
      await fn();
    } finally {
      await client.end();
    }
  }

  private async cardQueryToSQL(cards: ScopedCardService, query: CardExpression) {
    return this.expressionToSql(await this.makeExpression(cards, query));
  }

  private async makeExpression(cards: ScopedCardService, query: CardExpression): Promise<Expression> {
    return flatten(
      await Promise.all(
        query.map(element => {
          if (isParam(element) || typeof element === 'string') {
            return Promise.resolve([element]);
          } else if (element.kind === 'field-query') {
            return this.handleFieldQuery(cards, element);
          } else if (element.kind === 'field-value') {
            return this.handleFieldValue(cards, element);
          } else if (element.kind === 'field-arity') {
            return this.handleFieldArity(cards, element);
          } else {
            throw assertNever(element);
          }
        })
      )
    );
  }

  private expressionToSql(query: Expression) {
    let values: PgPrimitive[] = [];
    let text = query
      .map(element => {
        if (isParam(element)) {
          values.push(element.param);
          return `$${values.length}`;
        } else if (typeof element === 'string') {
          return element;
        } else {
          assertNever(element);
        }
      })
      .join(' ');
    return {
      text,
      values,
    };
  }

  beginCardBatch(cards: ScopedCardService) {
    return new Batch(this, cards);
  }
  private async handleFieldQuery(cards: ScopedCardService, fieldQuery: FieldQuery): Promise<Expression> {
    let { path } = fieldQuery;
    if (path === 'csRealm' || path === 'csOriginalRealm' || path === 'csId') {
      return [snakeCase(path)];
    }

    return await this.walkFilterFieldPath(
      cards,
      fieldQuery.typeContext,
      path,
      ['search_doc'],
      async (fieldCard, expression, fieldName) => {
        let buildQueryExpression = await fieldCard.loadFeature('field-buildQueryExpression');
        if (buildQueryExpression) {
          return buildQueryExpression(expression, fieldName);
        } else {
          return [...expression, '->>', param(fieldName)];
        }
      },
      async (_fieldCard, expression, fieldName) => {
        return [...expression, '->', param(fieldName)];
      }
    );
  }

  private async handleFieldValue(cards: ScopedCardService, fieldValue: FieldValue): Promise<Expression> {
    let { path, value } = fieldValue;
    let expression = await this.makeExpression(cards, value);
    if (path === 'csRealm' || path === 'csOriginalRealm' || path === 'csId') {
      return expression;
    }

    return await this.walkFilterFieldPath(
      cards,
      fieldValue.typeContext,
      path,
      expression,
      async (fieldCard, expression) => {
        let buildValueExpression = await fieldCard.loadFeature('field-buildValueExpression');
        if (buildValueExpression) {
          return buildValueExpression(expression);
        }
        return expression;
      }
    );
  }

  private async handleFieldArity(cards: ScopedCardService, fieldArity: FieldArity): Promise<Expression> {
    let { path, singular, plural } = fieldArity;

    let chosenExpression: CardExpression;
    if (path === 'csRealm' || path === 'csOriginalRealm' || path === 'csId') {
      chosenExpression = singular;
    } else {
      chosenExpression = await this.walkFilterFieldPath(
        cards,
        fieldArity.typeContext,
        path,
        [],
        async (fieldCard, _expression, _fieldName) => {
          if (fieldCard.csFieldArity === 'plural') {
            return plural;
          }
          return singular;
        }
      );
    }
    return await this.makeExpression(cards, chosenExpression);
  }

  private async walkFilterFieldPath(
    cards: ScopedCardService,
    filterTypeContext: CardId,
    path: string,
    expression: Expression,
    handleLeafField: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<Expression>,
    handleInteriorField?: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<Expression>
  ): Promise<Expression>;
  private async walkFilterFieldPath(
    cards: ScopedCardService,
    filterTypeContext: CardId,
    path: string,
    expression: Expression,
    handleLeafField: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<CardExpression>,
    handleInteriorField?: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<CardExpression>
  ): Promise<CardExpression>;
  private async walkFilterFieldPath(
    cards: ScopedCardService,
    filterTypeContext: CardId,
    path: string,
    expression: Expression,
    handleLeafField: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<Expression>,
    handleInteriorField?: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<Expression>
  ): Promise<Expression> {
    let pathSegments = path.split('.');
    let enclosingCard: Card = await cards.get(filterTypeContext);
    for (let [index, pathSegment] of pathSegments.entries()) {
      let fieldCard = await enclosingCard.field(pathSegment);
      let fullFieldName = cardstackFieldPattern.test(pathSegment)
        ? pathSegment
        : `${fieldCard.enclosingCard.canonicalURL}/${pathSegment}`;
      if (index === pathSegments.length - 1) {
        expression = await handleLeafField(fieldCard, expression, fullFieldName);
      } else if (handleInteriorField) {
        expression = await handleInteriorField(fieldCard, expression, fullFieldName);
      }
      enclosingCard = fieldCard;
    }
    return expression;
  }

  async get(cards: ScopedCardService, id: CardId): Promise<AddressableCard> {
    let condition = every([
      [`cs_realm = `, param(id.csRealm)],
      [`cs_original_realm = `, param(id.csOriginalRealm ?? id.csRealm)],
      [`cs_id = `, param(id.csId)],<commit_msg>better implementation signature for walkFilterFieldPath()<commit_after>import { Pool, Client, QueryResult, PoolClient, Notification } from 'pg';
import migrate from 'node-pg-migrate';
import logger from '@cardstack/logger';
import postgresConfig from './postgres-config';
import { join } from 'path';
import * as JSON from 'json-typescript';
import {
  upsert,
  param,
  safeName,
  every,
  CardExpression,
  Expression,
  any,
  isParam,
  addExplicitParens,
  PgPrimitive,
  fieldQuery,
  fieldArity,
  fieldValue,
  FieldQuery,
  FieldValue,
  FieldArity,
} from './util';
import { AddressableCard, CardId, Card, FieldCard, cardstackFieldPattern } from '../card';
import CardstackError from '../error';
import { Query, baseType, Filter, EqFilter, RangeFilter } from '../query';
import { Sorts } from './sorts';
import snakeCase from 'lodash/snakeCase';
import flatten from 'lodash/flatten';
import assertNever from 'assert-never';
import { ScopedCardService } from '../cards-service';

const log = logger('cardstack/pgsearch');

function config() {
  return postgresConfig({
    database: `pgsearch_${process.env.PGSEARCH_NAMESPACE || 'dev'}`,
  });
}

export default class PgClient {
  private pool: Pool;

  constructor() {
    let c = config();
    this.pool = new Pool({
      user: c.user,
      host: c.host,
      database: c.database,
      password: <PASSWORD>,
      port: c.port,
    });
  }

  async teardown() {
    if (this.pool) {
      await this.pool.end();
    }
  }

  async ready() {
    await this.migrateDb();
  }

  private async migrateDb() {
    const config = postgresConfig();
    let client = new Client(Object.assign({}, config, { database: 'postgres' }));
    try {
      await client.connect();
      let response = await client.query(`select count(*)=1 as has_database from pg_database where datname=$1`, [
        config.database,
      ]);
      if (!response.rows[0].has_database) {
        await client.query(`create database ${safeName(config.database)}`);
      }
    } finally {
      client.end();
    }

    await migrate({
      direction: 'up',
      migrationsTable: 'migrations',
      singleTransaction: true,
      checkOrder: false,
      databaseUrl: {
        user: config.user,
        host: config.host,
        database: config.database,
        password: <PASSWORD>,
        port: config.port,
      },
      count: Infinity,
      ignorePattern: '.*\\.(?!js)[^.]+',
      dir: join(__dirname, 'migrations'),
      log: (...args) => log.debug(...args),
    });
  }

  static async deleteSearchIndexIHopeYouKnowWhatYouAreDoing() {
    let c = config();
    let client = new Client(Object.assign({}, c, { database: 'postgres' }));
    try {
      await client.connect();
      await client.query(`drop database if exists ${safeName(c.database)}`);
    } finally {
      client.end();
    }
  }

  async queryCards(cards: ScopedCardService, query: CardExpression): Promise<QueryResult> {
    let client = await this.pool.connect();
    let sql = await this.cardQueryToSQL(cards, query);
    log.trace('search: %s trace: %j', sql.text, sql.values);
    try {
      return await client.query(sql);
    } finally {
      client.release();
    }
  }

  async query(query: Expression): Promise<QueryResult> {
    let client = await this.pool.connect();
    let sql = await this.expressionToSql(query);
    log.trace('search: %s trace: %j', sql.text, sql.values);
    try {
      return await client.query(sql);
    } finally {
      client.release();
    }
  }

  async withConnection<T>(
    fn: (connection: { client: PoolClient; query: (e: Expression) => Promise<QueryResult> }) => Promise<T>
  ): Promise<T> {
    let client = await this.pool.connect();
    let query = async (expression: Expression) => {
      let sql = this.expressionToSql(expression);
      log.trace('search: %s trace: %j', sql.text, sql.values);
      return await client.query(sql);
    };
    try {
      return await fn({ query, client });
    } finally {
      client.release();
    }
  }

  async listen(channel: string, handler: (notification: Notification) => void, fn: () => Promise<void>) {
    // I found that LISTEN/NOTIFY doesn't work reliably on connections from the
    // Pool, and this is substantiated by commentary on GitHub:
    //   https://github.com/brianc/node-postgres/issues/1543#issuecomment-353622236
    // So for listen purposes, we establish a completely separate connection.
    let c = config();
    let client = new Client({
      user: c.user,
      host: c.host,
      database: c.database,
      password: <PASSWORD>,
      port: c.port,
    });
    await client.connect();
    try {
      client.on('notification', n => {
        log.trace(`heard pg notification for channel %s`, n.channel);
        handler(n);
      });
      await client.query(`LISTEN ${safeName(channel)}`);
      await fn();
    } finally {
      await client.end();
    }
  }

  private async cardQueryToSQL(cards: ScopedCardService, query: CardExpression) {
    return this.expressionToSql(await this.makeExpression(cards, query));
  }

  private async makeExpression(cards: ScopedCardService, query: CardExpression): Promise<Expression> {
    return flatten(
      await Promise.all(
        query.map(element => {
          if (isParam(element) || typeof element === 'string') {
            return Promise.resolve([element]);
          } else if (element.kind === 'field-query') {
            return this.handleFieldQuery(cards, element);
          } else if (element.kind === 'field-value') {
            return this.handleFieldValue(cards, element);
          } else if (element.kind === 'field-arity') {
            return this.handleFieldArity(cards, element);
          } else {
            throw assertNever(element);
          }
        })
      )
    );
  }

  private expressionToSql(query: Expression) {
    let values: PgPrimitive[] = [];
    let text = query
      .map(element => {
        if (isParam(element)) {
          values.push(element.param);
          return `$${values.length}`;
        } else if (typeof element === 'string') {
          return element;
        } else {
          assertNever(element);
        }
      })
      .join(' ');
    return {
      text,
      values,
    };
  }

  beginCardBatch(cards: ScopedCardService) {
    return new Batch(this, cards);
  }
  private async handleFieldQuery(cards: ScopedCardService, fieldQuery: FieldQuery): Promise<Expression> {
    let { path } = fieldQuery;
    if (path === 'csRealm' || path === 'csOriginalRealm' || path === 'csId') {
      return [snakeCase(path)];
    }

    return await this.walkFilterFieldPath(
      cards,
      fieldQuery.typeContext,
      path,
      ['search_doc'],
      async (fieldCard, expression, fieldName) => {
        let buildQueryExpression = await fieldCard.loadFeature('field-buildQueryExpression');
        if (buildQueryExpression) {
          return buildQueryExpression(expression, fieldName);
        } else {
          return [...expression, '->>', param(fieldName)];
        }
      },
      async (_fieldCard, expression, fieldName) => {
        return [...expression, '->', param(fieldName)];
      }
    );
  }

  private async handleFieldValue(cards: ScopedCardService, fieldValue: FieldValue): Promise<Expression> {
    let { path, value } = fieldValue;
    let expression = await this.makeExpression(cards, value);
    if (path === 'csRealm' || path === 'csOriginalRealm' || path === 'csId') {
      return expression;
    }

    return await this.walkFilterFieldPath(
      cards,
      fieldValue.typeContext,
      path,
      expression,
      async (fieldCard, expression) => {
        let buildValueExpression = await fieldCard.loadFeature('field-buildValueExpression');
        if (buildValueExpression) {
          return buildValueExpression(expression);
        }
        return expression;
      }
    );
  }

  private async handleFieldArity(cards: ScopedCardService, fieldArity: FieldArity): Promise<Expression> {
    let { path, singular, plural } = fieldArity;

    let chosenExpression: CardExpression;
    if (path === 'csRealm' || path === 'csOriginalRealm' || path === 'csId') {
      chosenExpression = singular;
    } else {
      chosenExpression = await this.walkFilterFieldPath(
        cards,
        fieldArity.typeContext,
        path,
        [],
        async (fieldCard, _expression, _fieldName) => {
          if (fieldCard.csFieldArity === 'plural') {
            return plural;
          }
          return singular;
        }
      );
    }
    return await this.makeExpression(cards, chosenExpression);
  }

  private async walkFilterFieldPath(
    cards: ScopedCardService,
    filterTypeContext: CardId,
    path: string,
    expression: Expression,
    handleLeafField: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<Expression>,
    handleInteriorField?: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<Expression>
  ): Promise<Expression>;
  private async walkFilterFieldPath(
    cards: ScopedCardService,
    filterTypeContext: CardId,
    path: string,
    expression: Expression,
    handleLeafField: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<CardExpression>,
    handleInteriorField?: (fieldCard: FieldCard, expression: Expression, fieldName: string) => Promise<CardExpression>
  ): Promise<CardExpression>;
  private async walkFilterFieldPath(
    cards: ScopedCardService,
    filterTypeContext: CardId,
    path: string,
    expression: Expression,
    handleLeafField: (fieldCard: FieldCard, expression: any[], fieldName: string) => Promise<any>,
    handleInteriorField?: (fieldCard: FieldCard, expression: any[], fieldName: string) => Promise<any>
  ): Promise<any> {
    let pathSegments = path.split('.');
    let enclosingCard: Card = await cards.get(filterTypeContext);
    for (let [index, pathSegment] of pathSegments.entries()) {
      let fieldCard = await enclosingCard.field(pathSegment);
      let fullFieldName = cardstackFieldPattern.test(pathSegment)
        ? pathSegment
        : `${fieldCard.enclosingCard.canonicalURL}/${pathSegment}`;
      if (index === pathSegments.length - 1) {
        expression = await handleLeafField(fieldCard, expression, fullFieldName);
      } else if (handleInteriorField) {
        expression = await handleInteriorField(fieldCard, expression, fullFieldName);
      }
      enclosingCard = fieldCard;
    }
    return expression;
  }

  async get(cards: ScopedCardService, id: CardId): Promise<AddressableCard> {
    let condition = every([
      [`cs_realm = `, param(id.csRealm)],
      [`cs_original_realm = `, param(id.csOriginalRealm ?? id.csRealm)],
      [`cs_id = `, param(id.csId)],
{'fraction_non_alphanumeric': 0.13072381572218864, 'fraction_numerical': 0.0015805866765840732, 'mean_word_length': 2.9478803450174342, 'pattern_counts': {'":': 0, '<': 51, '<?xml version=': 0, '>': 95, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5981087', 'n_tokens_mistral': 6238, 'n_tokens_neox': 6208, 'n_words': 1533}