// deno-lint-ignore-file no-explicit-any

interface WhereClauseResponse {
  clause: string
  args: unknown[]
}

function buildWhereClause(filter: Record<string, any>, startParamIndex = 1): WhereClauseResponse {
  const clauses: string[] = [];
  const args: unknown[] = [];
  let paramIndex = startParamIndex;

  for (const [field, value] of Object.entries(filter)) {
    if(field == '$and') {
      if(!Array.isArray(value)) throw new Error('$and must be an array!');

      const subClauses: string[] = [];

      for(const subFilter of value) {
        const sub = buildWhereClause(subFilter, paramIndex);
        subClauses.push(`(${sub.clause.replace(/^WHERE /, '')})`);
        args.push(...sub.args);
        paramIndex += sub.args.length;
      }

      clauses.push(subClauses.filter(sc => sc.replace(/[\(\)]/g, '').length > 0).join(` AND `));
      continue;
    } else if(field == '$or') {
      if(!Array.isArray(value)) throw new Error('$or must be an array!');

      const subClauses: string[] = [];

      for(const subFilter of value) {
        const sub = buildWhereClause(subFilter, paramIndex);
        subClauses.push(`(${sub.clause.replace(/^WHERE /, '')})`);
        args.push(...sub.args);
        paramIndex += sub.args.length;
      }

      clauses.push(`(${subClauses.filter(sc => sc !== '()').join(` OR `)})`);
      continue;
    } else if(field == '$relationship') {
      const { via, match, select, reference = 'id' } = filter.$relationship as DB.Filtering.RelationshipFilter<any, any>;

      const { clause: subClause, args: subArgs } = buildWhereClause({ $and: [ match, { deleted_at: null } ]}, paramIndex);
      paramIndex += subArgs.length;
      args.push(...subArgs);

      const table = typeof via === 'string' ? via : via.table;

      const subquery = `SELECT ${select as string} FROM "${table}" ${subClause}`;
      clauses.push(`${reference.toString()} IN (${subquery})`);

      continue;
    } else if(field == '$noRelationship') {
      const { via, match, select, reference = 'id' } = filter.$noRelationship as DB.Filtering.RelationshipFilter<any, any>;

      const { clause: subClause, args: subArgs } = buildWhereClause({ $and: [ match, { deleted_at: null } ]}, paramIndex);
      paramIndex += subArgs.length;
      args.push(...subArgs);

      const table = typeof via === 'string' ? via : via.table;

      const subquery = `SELECT ${select as string} FROM "${table}" ${subClause}`;
      clauses.push(`${reference.toString()} NOT IN (${subquery})`);

      continue;
    }

    if (value !== null && typeof value === 'object' && !Array.isArray(value)) {
      for (const [op, val] of Object.entries(value)) {
        switch(op) {
          case '$eq': {
            if(val === null) {
              clauses.push(`"${field}" IS NULL`);
              continue;
            }

            clauses.push(`"${field}" = $${paramIndex++}`);
            args.push(val);
            continue;
          }
          case '$ne': {
            if(val === null) {
              clauses.push(`"${field}" IS NOT NULL`);
              continue;
            }

            clauses.push(`"${field}" != $${paramIndex++}`);
            args.push(val);
            continue;
          }

          case '$lt': {
            clauses.push(`"${field}" < $${paramIndex++}`);
            args.push(val);
            continue;
          }
          case '$lte': {
            clauses.push(`"${field}" <= $${paramIndex++}`);
            args.push(val);
            continue;
          }
          case '$gt': {
            clauses.push(`"${field}" > $${paramIndex++}`);
            args.push(val);
            continue;
          }
          case '$gte': {
            clauses.push(`"${field}" >= $${paramIndex++}`);
            args.push(val);
            continue;
          }

          case '$like': {
            clauses.push(`"${field}" like $${paramIndex++}`);
            args.push(val);
            continue;
          }
          case '$ilike': {
            clauses.push(`"${field}" ilike $${paramIndex++}`);
            args.push(val);
            continue;
          }

          case '$in': {
            clauses.push(`"${field}" = ANY($${paramIndex++})`);
            args.push(val);
            continue;
          }
          case '$nin': {
            clauses.push(`NOT ("${field}" = ANY($${paramIndex++}))`);
            args.push(val);
            continue;
          }
          default: throw new Error(`Unsupported MPG operator: ${op}`);
        }
      }
    } else {
      if(value === null) {
        clauses.push(`"${field}" IS NULL`);
        continue;
      }

      clauses.push(`"${field}" = $${paramIndex++}`);
      args.push(value);
    }
  }

  const clause = clauses.length > 0 ? 'WHERE ' + clauses.join(' AND ') : '';
  return { clause, args };
}


export const TranslateMongoishToPostgreSQL = <T = any>(filter: DB.Filterable<T>, startParamIndex = 1) => {
  return buildWhereClause(filter, startParamIndex);
}