import { CustomError } from '../error'

const mapOperatorToSql = (operator: string) => {
  const operatorMap = {
    equals: '=',
    notEquals: '!=',
    lessThan: '<',
    lessOrEquals: '<=',
    greaterThan: '>',
    greaterOrEquals: '>=',
    includes: 'LIKE',
    notIncludes: 'NOT LIKE',
    in: 'IN',
    notIn: 'NOT IN',
    // Add more operators as needed
  }

  if (operatorMap[operator as keyof typeof operatorMap] === undefined) {
    throw new Error(`Operator ${operator} is not supported`)
  }

  return operatorMap[operator as keyof typeof operatorMap]
}

const formatValueForSql = (value: any) => {
  if (typeof value === 'string') {
    if (value.includes('(') && value.includes(')')) {
      return value
    }
    return `'${value}'`
  }
  
  return value
}

const parseFilterToSql = (filter: any, alias: string = '') => {
  if (!filter) {
    return ''
  }

  if (filter.or) {
    const orConditions = filter.or.map((f) => parseFilterToSql(f, alias))
    return `(${orConditions.join(' OR ')})`
  }

  if (filter.and) {
    const andConditions = filter.and.map((f) => parseFilterToSql(f, alias))
    return `(${andConditions.join(' AND ')})`
  }

  if (filter.operator && filter.field && filter.value) {
    const { operator, field, value } = filter
    if (operator === 'in' || operator === 'notIn') {
      return  `${formatValueForSql(value)} ${mapOperatorToSql(operator)} UNNEST(${alias ? alias + '.' : ''}${field})`
    }
    return `${alias ? alias + '.' : ''}${field} ${mapOperatorToSql(operator)} ${formatValueForSql(value)}`
  }

  return ''
}

const defineQueryHandler = (props: IBuildRequestHandler) => {
  try {
    const {
      primaryEntity,
      requestBody,
      entity,
      dataset,
      table,
      primaryKey,
      foreignKey,
      entityDef,
    } = props

    let query = '',
      metaQuery = ''

    // Always retrieve all fields.  We will remove unwanted
    // fields after building relationships.
    query = `SELECT * FROM ${dataset}.${table}`
    metaQuery = `SELECT COUNT(*) AS count FROM ${dataset}.${table}`

    /**
     * Filter handler
     */
    if (
      (requestBody.filter && requestBody.filter[entity]) ||
      primaryKey ||
      foreignKey
    ) {
      if (requestBody.filter && requestBody.filter[entity]) {
        query += ' WHERE '
        query += parseFilterToSql(requestBody.filter[entity])
        metaQuery += ' WHERE '
        metaQuery += parseFilterToSql(requestBody.filter[entity])
      }

      if (primaryKey || foreignKey) {
        const relationshipType = entityDef.type
        if (relationshipType === 'has-many' && foreignKey) {
          if (query.indexOf('WHERE') === -1) {
            query += ' WHERE '
          } else {
            query += ' AND '
          }
          query += `${foreignKey} = {{foreignIdentifier}}`
        }
        if (relationshipType === 'has-one' && primaryKey) {
          if (query.indexOf('WHERE') === -1) {
            query += ' WHERE '
          } else {
            query += ' AND '
          }
          query += `${primaryKey} = {{foreignIdentifier}}`
        }
      }
    }
    /* END Filter handler */

    /**
     * Sort handler
     */
    if (requestBody.sort && requestBody.sort[entity]) {
      const sorts = requestBody.sort[entity]
      query += ' ORDER BY'

      for (let i = 0; i < sorts.length; i++) {
        const sort = sorts[i]
        if (sort[0] === '-') {
          query += ` ${sort.slice(1).trim()} DESC`
          if (i < sorts.length - 1) {
            query += ','
          } else {
            query += ' '
          }
        } else {
          query += ` ${sort.trim()}`
          if (i < sorts.length - 1) {
            query += ','
          } else {
            query += ' '
          }
        }
      }
    }
    /* END Sort handler */

    /**
     * Pagination handler
     */
    if (
      primaryEntity &&
      requestBody.pagination &&
      requestBody.pagination.size
    ) {
      const size = requestBody.pagination.size
      const page = requestBody.pagination.page || 1
      const offset = (page - 1) * size
      query += ` LIMIT ${size} OFFSET ${offset}`
    } else {
      query += ' LIMIT 1000'
    }
    /* END Pagination handler */

    return { query, metaQuery }
  } catch (error: any) {
    throw new CustomError(
      `[buildBigQueryDataStoreRequest] ${error.message}`,
      400
    )
  }
}

export default (req: any) => {
  try {
    const { body, entityDef, path } = req
    const pathParts = path.split('/')
    const primaryEntity = pathParts[pathParts.length - 1]
    const entity = req.params.children ?? req.params.entity
    const { repo, dataset, table } = entityDef
    const requestBody = body

    const response = {
      primary: '',
      secondary: {},
      metaQuery: '',
    }
    let result = defineQueryHandler({
      primaryEntity,
      requestBody,
      entity,
      dataset,
      table,
      entityDef,
    })
    response.primary = result.query
    response.metaQuery = result.metaQuery

    if (req.settings.requestType === 'json') return response

    // Includes handler
    if (requestBody.includes) {
      const includes = requestBody.includes
      for (let childEntity of includes) {
        response.secondary[childEntity] = {}
        childEntity = childEntity.trim()
        const childEntityDef = entityDef.relationships[childEntity]
        const { dataset, table, foreignKey, primaryKey } = childEntityDef
        result = defineQueryHandler({
          primaryEntity: false,
          requestBody,
          entity: childEntity,
          dataset,
          table,
          primaryKey,
          foreignKey,
          entityDef: childEntityDef,
        })
        response.secondary[childEntity] = result.query
      }
    }

    return response
  } catch (error: any) {
    throw new CustomError(`[defineQueryBigQuery] ${error.message}`, 400)
  }
}

export { parseFilterToSql }