type JoinType = 'INNER JOIN' | 'LEFT JOIN' | 'RIGHT JOIN' | 'JOIN'

interface JoinClause {
  type: JoinType
  table: string
  on: string
}

interface WhereClause {
  clause: string
  params: any[]
  op: 'AND' | 'OR'
}

export class MSSQLSelectQueryBuilder {
  private _fields: string[] = []
  private _table: string | null = null
  private _joins: JoinClause[] = []
  private _wheres: WhereClause[] = []
  private _orders: string[] = []
  private _groups: string[] = []
  private _limit: number | null = null
  private _offset: number | null = null

  select(...fields: string[]): this {
    this._fields = fields.length ? fields : ['*']
    return this
  }

  from(table: string): this {
    this._table = table
    return this
  }

  join(table: string, on: string, type: JoinType = 'JOIN'): this {
    this._joins.push({ type, table, on })
    return this
  }

  leftJoin(table: string, on: string): this {
    return this.join(table, on, 'LEFT JOIN')
  }

  rightJoin(table: string, on: string): this {
    return this.join(table, on, 'RIGHT JOIN')
  }

  where(clause: string, ...params: any[]): this {
    this._wheres.push({ clause, params, op: 'AND' })
    return this
  }

  andWhere(clause: string, ...params: any[]): this {
    return this.where(clause, ...params)
  }

  orWhere(clause: string, ...params: any[]): this {
    this._wheres.push({ clause, params, op: 'OR' })
    return this
  }

  orderBy(...fields: string[]): this {
    this._orders.push(...fields)
    return this
  }

  groupBy(...fields: string[]): this {
    this._groups.push(...fields)
    return this
  }

  limit(n: number): this {
    this._limit = n
    return this
  }

  offset(n: number): this {
    this._offset = n
    return this
  }

  build(): { sqlQuery: string; params: Record<string, any> } {
    if (!this._table) throw new Error('Table not specified')

    let sql = `SELECT ${this._fields.join(', ')} FROM ${this._table}`
    if (this._joins.length) {
      sql +=
        ' ' +
        this._joins.map((j) => `${j.type} ${j.table} ON ${j.on}`).join(' ')
    }

    // WHERE clause with parameter replacement
    let params: Record<string, any> = {}
    if (this._wheres.length) {
      let whereSql = ''
      let paramIdx = 0
      this._wheres.forEach((w, i) => {
        let clause = w.clause.replace(/\?/g, () => {
          const paramName = `@p${paramIdx}`
          paramIdx++
          return paramName
        })
        if (i === 0) {
          whereSql += `(${clause})`
        } else {
          whereSql += ` ${w.op} (${clause})`
        }
        w.params.forEach((val, idx) => {
          params[`p${paramIdx - w.params.length + idx}`] = val
        })
      })
      sql += ` WHERE ${whereSql}`
    }

    if (this._groups.length) {
      sql += ` GROUP BY ${this._groups.join(', ')}`
    }
    if (this._orders.length) {
      sql += ` ORDER BY ${this._orders.join(', ')}`
    } else if (this._limit !== null || this._offset !== null) {
      // MSSQL requires ORDER BY for OFFSET/FETCH
      sql += ` ORDER BY (SELECT NULL)`
    }
    if (this._limit !== null) {
      sql += ` OFFSET ${this._offset || 0} ROWS FETCH NEXT ${
        this._limit
      } ROWS ONLY`
    } else if (this._offset !== null) {
      sql += ` OFFSET ${this._offset} ROWS`
    }

    return { sqlQuery: sql.trim(), params }
  }
}