import {Inject, Injectable} from '../../di'
import {DatabaseService} from '../DatabaseService'
import {
    Constraint, ConstraintConnectionOperator,
    ConstraintOperator,
    OrderDirection,
    OrderStatement, QueryResult,
    QuerySource,
    SpecifiedField,
} from '../types'
import {Connection} from '../connection/Connection'
import {Collectable, deepCopy, ErrorWithContext, Maybe} from '../../util'
import {EscapeValue, QuerySafeValue, raw, ScalarEscapeValue, VectorEscapeValue} from '../dialect/SQLDialect'
import {ResultCollection} from './result/ResultCollection'
import {AbstractResultIterable} from './result/AbstractResultIterable'
import {AppClass} from '../../lifecycle/AppClass'

/**
 * Type alias for a function that applies some constraints to a builder group.
 */
export type ConstraintGroupClosure<T> = (group: AbstractBuilder<T>) => any

/**
 * A base class that facilitates building database queries using a fluent interface.
 * This can be specialized by child-classes to yield query results of the given type `T`.
 */
@Injectable()
export abstract class AbstractBuilder<T> extends AppClass {
    @Inject()
    protected readonly databaseService!: DatabaseService

    /** Constraints applied to this query. */
    protected constraints: Constraint[] = []

    /** The source table to query from. */
    protected source?: QuerySource

    /** The fields to query from the table. */
    protected registeredFields: SpecifiedField[] = []

    /** The number of records to skip before the result set. */
    protected registeredSkip?: number

    /** The max number of records to include in the result set. */
    protected registeredTake?: number

    /** If true, the query should refer to distinct records. */
    protected registeredDistinct = false

    /** Array of SQL group-by clauses. */
    protected registeredGroupings: string[] = []

    /** Array of SQL order-by clauses. */
    protected registeredOrders: OrderStatement[] = []

    /** The connection on which the query should be executed. */
    protected registeredConnection?: Connection

    /** Raw SQL to use instead. Overrides builder methods. */
    protected rawSql?: string

    /**
     * Create a new, empty, instance of the current builder.
     */
    public abstract getNewInstance(): AbstractBuilder<T>

    /**
     * Get a result iterable for the built query.
     */
    public abstract getResultIterable(): AbstractResultIterable<T>

    /**
     * Get a copy of this builder with its values finalized.
     */
    public finalize(): AbstractBuilder<T> {
        return this.clone()
    }

    /**
     * Clone the current query to a new AbstractBuilder instance with the same properties.
     */
    public clone(): AbstractBuilder<T> {
        const bldr = this.getNewInstance()

        bldr.constraints = deepCopy(this.constraints)
        bldr.source = deepCopy(this.source)
        bldr.registeredFields = deepCopy(this.registeredFields)
        bldr.registeredSkip = deepCopy(this.registeredSkip)
        bldr.registeredTake = deepCopy(this.registeredTake)
        bldr.registeredDistinct = deepCopy(this.registeredDistinct)
        bldr.registeredGroupings = deepCopy(this.registeredGroupings)
        bldr.registeredOrders = deepCopy(this.registeredOrders)
        bldr.registeredConnection = this.registeredConnection
        bldr.rawSql = this.rawSql

        return bldr
    }

    /** Get the constraints applied to this query. */
    public get appliedConstraints(): Constraint[] {
        return deepCopy(this.constraints)
    }

    /** Get the fields that should be included in this query. */
    public get appliedFields(): SpecifiedField[] {
        return deepCopy(this.registeredFields)
    }

    /** Get the skip/take values of this query. */
    public get appliedPagination(): { skip: number | undefined, take: number | undefined} {
        return { skip: this.registeredSkip,
            take: this.registeredTake }
    }

    /** True if the query should be DISTINCT */
    public get appliedDistinction(): boolean {
        return this.registeredDistinct
    }

    /** Get the SQL group-by clauses applied to this query. */
    public get appliedGroupings(): string[] {
        return deepCopy(this.registeredGroupings)
    }

    /** Get the SQL order-by clauses applied to this query. */
    public get appliedOrder(): OrderStatement[] {
        return deepCopy(this.registeredOrders)
    }

    /** Get the raw SQL overriding the builder methods, if it exists. */
    public get appliedRawSql(): Maybe<string> {
        return this.rawSql
    }

    /** Get the source table for this query. */
    public get querySource(): QuerySource | undefined {
        if ( this.source ) {
            return deepCopy(this.source)
        }
    }

    /**
     * Set the source table (and optional alias) for this query.
     * @param table
     * @param alias
     */
    from(table: string|QuerySafeValue, alias?: string): this {
        if ( alias ) {
            this.source = { table,
                alias }
        } else {
            this.source = table
        }
        return this
    }

    /**
     * Alias of `from()`.
     * @param table
     * @param alias
     */
    table(table: string, alias?: string): this {
        return this.from(table, alias)
    }

    /**
     * Include the given field (and optional alias) in the query.
     * @param field
     * @param alias
     */
    field(field: string | QuerySafeValue, alias?: string): this {
        if ( alias ) {
            this.registeredFields.push({ field,
                alias })
        } else {
            this.registeredFields.push(field)
        }
        return this
    }

    /**
     * Include the given fields in the query.
     * @param fields
     */
    fields(...fields: SpecifiedField[]): this {
        this.registeredFields = [...this.registeredFields, ...fields]
        return this
    }

    /**
     * Alias of `fields()`.
     * @param fields
     */
    returning(...fields: SpecifiedField[]): this {
        return this.fields(...fields)
    }

    /**
     * Alias of `fields()`.
     * @param fields
     */
    select(...fields: SpecifiedField[]): this {
        return this.fields(...fields)
    }

    /**
     * Remove all selected fields from this query.
     */
    clearFields(): this {
        this.registeredFields = []
        return this
    }

    /**
     * Apply a new WHERE constraint to the query.
     * @param field
     * @param operator
     * @param operand
     */
    where(field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: EscapeValue): this {
        this.createConstraint('AND', field, operator, operand)
        return this
    }

    /** Apply a WHERE ... IS NULL constraint to the query. */
    whereNull(field: string): this {
        return this.whereRawValue(field, 'IS', 'NULL')
    }

    /** Apply a WHERE ... IS NOT NULL constraint to the query. */
    whereNotNull(field: string): this {
        return this.whereRawValue(field, 'IS NOT', 'NULL')
    }

    /**
     * Apply a new WHERE constraint to the query, without escaping `operand`. Prefer `where()`.
     * @param field
     * @param operator
     * @param operand
     */
    whereRawValue(field: string, operator: ConstraintOperator, operand: string): this {
        this.createConstraint('AND', field, operator, raw(operand))
        return this
    }

    /**
     * Add raw SQL as a constraint to the query.
     * @param clause
     */
    whereRaw(clause: string|QuerySafeValue): this {
        if ( !(clause instanceof QuerySafeValue) ) {
            clause = raw(clause)
        }

        this.constraints.push(raw(clause))
        return this
    }

    /** Apply an impossible constraint to the query, causing it to match 0 rows. */
    whereMatchNone(): this {
        return this.whereRaw('1=0')
    }

    /**
     * Apply a new WHERE NOT constraint to the query.
     * @param field
     * @param operator
     * @param operand
     */
    whereNot(field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: EscapeValue): this {
        this.createConstraint('AND NOT', field, operator, operand)
        return this
    }

    /**
     * Apply an OR WHERE constraint to the query.
     * @param field
     * @param operator
     * @param operand
     */
    orWhere(field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: EscapeValue): this {
        this.createConstraint('OR', field, operator, operand)
        return this
    }

    /**
     * Apply an OR WHERE NOT constraint to the query.
     * @param field
     * @param operator
     * @param operand
     */
    orWhereNot(field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: EscapeValue): this {
        this.createConstraint('OR NOT', field, operator, operand)
        return this
    }

    /**
     * Apply an OR WHERE constraint to the query, without escaping `operand`. Prefer `orWhere()`.
     * @param field
     * @param operator
     * @param operand
     */
    orWhereRaw(field: string, operator: ConstraintOperator, operand: string): this {
        this.createConstraint('OR', field, operator, raw(operand))
        return this
    }

    /**
     * Apply a WHERE IN constraint to the query, escaping the values in the set.
     * @param field
     * @param values
     */
    whereIn<TConstraint extends ScalarEscapeValue>(field: string, values: VectorEscapeValue<TConstraint>): this {
        this.constraints.push({
            field,
            operator: 'IN',
            operand: values,
            preop: 'AND',
        })
        return this
    }

    /**
     * Apply a WHERE NOT IN constraint to the query, escaping the values in the set.
     * @param field
     * @param values
     */
    whereNotIn<TConstraint extends ScalarEscapeValue>(field: string, values: VectorEscapeValue<TConstraint>): this {
        this.constraints.push({
            field,
            operator: 'NOT IN',
            operand: values,
            preop: 'AND',
        })
        return this
    }

    /**
     * Apply an OR WHERE IN constraint to the query, escaping the values in the set.
     * @param field
     * @param values
     */
    orWhereIn<TConstraint extends ScalarEscapeValue>(field: string, values: VectorEscapeValue<TConstraint>): this {
        this.constraints.push({
            field,
            operator: 'IN',
            operand: values,
            preop: 'OR',
        })
        return this
    }

    /**
     * Apply an OR WHERE NOT IN constraint to the query, escaping the values in the set.
     * @param field
     * @param values
     */
    orWhereNotIn<TConstraint extends ScalarEscapeValue>(field: string, values: VectorEscapeValue<TConstraint>): this {
        this.constraints.push({
            field,
            operator: 'NOT IN',
            operand: values,
            preop: 'OR',
        })
        return this
    }

    /**
     * Limit the query to a maximum number of rows.
     * @param rows
     */
    limit(rows: number): this {
        this.registeredTake = rows
        return this
    }

    /**
     * Alias of `limit()`.
     * @param rows
     */
    take(rows: number): this {
        return this.limit(rows)
    }

    /**
     * Skip the first `rows` many rows in the result set.
     * @param rows
     */
    skip(rows: number): this {
        this.registeredSkip = rows
        return this
    }

    /**
     * Alias of `skip()`.
     * @param rows
     */
    offset(rows: number): this {
        return this.skip(rows)
    }

    /**
     * Make the query return only distinct rows.
     */
    distinct(): this {
        this.registeredDistinct = true
        return this
    }

    /**
     * Allow the query to return non-distinct rows. (Undoes `distinct()`.)
     */
    notDistinct(): this {
        this.registeredDistinct = false
        return this
    }

    /**
     * Apply `skip()` and `take()` calls to retrieve the records that should appear on
     * the `pageNum` page, assuming each page has `pageSize` many records.
     * @param pageNum
     * @param pageSize
     */
    page(pageNum = 1, pageSize = 20): this {
        this.skip(pageSize * (pageNum - 1))
        this.take(pageSize)
        return this
    }

    /**
     * Apply one or more GROUP-BY clauses to the query.
     * @param groupings
     */
    groupBy(...groupings: string[]): this {
        this.registeredGroupings = groupings
        return this
    }

    /**
     * Order the query by the given field.
     * @param field
     * @param direction
     */
    orderBy(field: string, direction: OrderDirection = 'ASC'): this {
        this.registeredOrders.push({ field,
            direction })
        return this
    }

    /**
     * Order the query by the given field, ascending.
     * @param field
     */
    orderByAscending(field: string): this {
        return this.orderBy(field, 'ASC')
    }

    /**
     * Order the query by the given field, descending.
     * @param field
     */
    orderByDescending(field: string): this {
        return this.orderBy(field, 'DESC')
    }

    /**
     * Specify the connection name or instance to execute the query on.
     * @param nameOrInstance
     */
    connection(nameOrInstance: string | Connection): this {
        if ( nameOrInstance instanceof Connection ) {
            this.registeredConnection = nameOrInstance
        } else {
            this.registeredConnection = this.databaseService.get(nameOrInstance)
        }

        return this
    }

    /**
     * Get a result iterable for the rows of this query.
     */
    iterator(): AbstractResultIterable<T> {
        if ( !this.registeredConnection ) {
            throw new ErrorWithContext(`No connection specified to fetch iterator for query.`)
        }

        return this.getResultIterable()
    }

    /**
     * Get an async collection of the rows resulting from this query.
     */
    get(): ResultCollection<T> {
        return new ResultCollection<T>(this.iterator())
    }

    /**
     * Get the first record matched by this query, if it exists.
     */
    async first(): Promise<T | undefined> {
        return this.iterator().at(0)
    }

    /**
     * Run an UPDATE query for all rows matched by this query, setting the given data.
     *
     * @example
     * ```typescript
     * query.table('my_table').update({ my_col: 4 })
     * ```
     *
     * This is equivalent to:
     * ```sql
     * UPDATE TO my_table
     * SET
     *  my_col = 4
     * ```
     *
     * @param data
     */
    async update(data: {[key: string]: EscapeValue}): Promise<QueryResult> {
        if ( !this.registeredConnection ) {
            throw new ErrorWithContext(`No connection specified to execute update query.`)
        }

        const query = this.registeredConnection.dialect().renderUpdate(this.finalize(), data)
        return this.registeredConnection.query(query)
    }

    /**
     * Run a batch update on all rows matched by this query, setting the values for discrete
     * rows based on some key.
     *
     * This is a more efficient way of combining discrete update queries.
     *
     * @example
     * ```ts
     * query.table('my_table')
     *  .updateMany('id_col', [
     *      {id_col: 1, val1_col: 'a'},
     *      {id_col: 2, val2_col: 'b'},
     *  ])
     * ```
     *
     * This will set the `val1_col` to `a` for rows where `id_col` is `1` and so on.
     *
     * @param key
     * @param rows
     */
    async updateMany(key: string, rows: Collectable<{[key: string]: EscapeValue}>): Promise<QueryResult> {
        if ( !this.registeredConnection ) {
            throw new ErrorWithContext(`No connection specified to execute update query.`)
        }

        const query = this.registeredConnection.dialect().renderBatchUpdate(this, key, rows)
        return this.registeredConnection.query(query)
    }

    /**
     * Execute a DELETE based on this query.
     *
     * @example
     * ```typescript
     * query.table('my_table').where('id', <, 44).delete()
     * ```
     *
     * This is equivalent to:
     * ```sql
     * DELETE
     * FROM my_table
     * WHERE
     *  id < 44
     * ```
     *
     */
    async delete(): Promise<QueryResult> {
        if ( !this.registeredConnection ) {
            throw new ErrorWithContext(`No connection specified to execute update query.`)
        }

        const query = this.registeredConnection.dialect().renderDelete(this.finalize())
        return this.registeredConnection.query(query)
    }

    /**
     * Insert the given rows into the table for this query, returning the fields specified in this query.
     *
     * @example
     * ```typescript
     * const rows = [
     *  { name: 'A' },
     *  { name: 'B' },
     * ]
     *
     * query.table('my_table')
     *  .returning('id', 'name')
     *  .insert(rows)
     * ```
     *
     * This is equivalent to:
     * ```sql
     * INSERT INTO my_table (name)
     * VALUES ('A'), ('B')
     * RETURNING id, name
     * ```
     *
     * @param rowOrRows
     */
    async insert(rowOrRows: {[key: string]: EscapeValue}|{[key: string]: EscapeValue}[]): Promise<QueryResult> {
        if ( !this.registeredConnection ) {
            throw new ErrorWithContext(`No connection specified to execute update query.`)
        }

        const query = this.registeredConnection.dialect().renderInsert(this.finalize(), rowOrRows)
        return this.registeredConnection.query(query)
    }

    /**
     * Returns true if at least one row matches the current query.
     */
    async exists(): Promise<boolean> {
        if ( !this.registeredConnection ) {
            throw new ErrorWithContext(`No connection specified to execute update query.`)
        }

        const query = this.registeredConnection.dialect().renderExistential(this.finalize())
        const result = await this.registeredConnection.query(query)
        return Boolean(result.rows.first())
    }

    /** Render the query as a string. */
    toString(): string {
        if ( !this.registeredConnection ) {
            throw new ErrorWithContext('No connection specified to render query.')
        }

        return this.registeredConnection.dialect().renderSelect(this.finalize())
    }

    /**
     * Set the query manually. Overrides any builder methods.
     * @example
     * ```ts
     * (new Builder())
     *  .raw('SELECT NOW() AS example_column')
     *  .get()
     * ```
     * @param sql
     */
    raw(sql: string): this {
        this.rawSql = sql
        return this
    }

    /** Pass this instance into a callback, then return this instance for chaining. */
    tap(callback: (inst: this) => unknown): this {
        callback(this)
        return this
    }

    /**
     * Adds a constraint to this query. This is used internally by the various `where`, `whereIn`, `orWhereNot`, &c.
     * @param preop
     * @param field
     * @param operator
     * @param operand
     * @private
     */
    private createConstraint(preop: ConstraintConnectionOperator, field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: any): void {
        if ( typeof field === 'function' ) {
            const builder = this.getNewInstance()
            field(builder)
            this.constraints.push({
                preop,
                items: builder.appliedConstraints,
            })
        } else if ( field && operator && typeof operand !== 'undefined' ) {
            this.constraints.push({
                field,
                operator,
                operand,
                preop,  // FIXME escape operand
            })
        }
    }
}