const { throwTypeError, throwEnumError } = require("./Errors");

/**
 * Prepares a new Selection
 */
class Select {
    #instance;
    #database;
    #from;
    #columns = [];

    #distinct = false;

    #where;
    #whereValues;
    #order = {
        asc: [],
        desc: []
    };
    #group;

    #aggregator;
    #joins = [];
    #having;
    #havingValues;
    #limit;
    #aggregatorParse;
    constructor(instance, defaultDatabase, from, columns){
        this.#database = defaultDatabase;
        this.#from = from;
        this.#columns = columns||[];
        this.#instance = instance;
    }

    /**
     * Selects a database for this query
     * @param {String} database - Name of the database
     * @returns {this}
     */
    selectDatabase(database){
        if (!database) throw new Error("database must not be empty");
        if (typeof database !== "string") throwTypeError("string", database);
        this.#database = database;
        return this;
    }

    /**
     * Adds the 'distinct' keyword for this query
     * Should be called on only selected columns.
     * - With 'distinct' only unique values are returned
     * @returns {this}
     */
    distinct(){
        this.#distinct = true;
        return this;
    }

    /**
     * Adds a where-clause to the query
     * - Values should be set as ? in the string and given in left-to-right order via the 'values'-array to minimize the risk of sql-injection
     * - If you are using joins, specify the table and column together: table.column
     * @param {String} string - The where-clause as a string with ? representing each values.
     * @param {Array<Any>} values - Array containing values replacing the ? in the string (from left to right) 
     * @returns {this}
     */
    where(string, values=[]){
        if (!string) throw new Error("string must not be empty");
        if (typeof string !== "string") throwTypeError("string", string);
        if (!Array.isArray(values)) throwTypeError("array", values);
        this.#where = string;
        this.#whereValues = values;
        return this;
    }

    /**
     * Same as a where-clause, but allows for aggregation
     * - Values should be set as ? in the string and given in left-to-right order via the 'values'-array to minimize the risk of sql-injection
     * - If you are using joins, specify the table and column together: table.column
     * @param {String} string - The having-clause with possible aggregation and ? representing each values 
     * @param {Array<Any>} values - Array containing values replacing the ? in the string (from left to right) 
     * @returns {this}
     */
    having(string, values = []){
        if (!string) throw new Error("string must not be empty");
        if (typeof string !== "string") throwTypeError("string", string);
        if (!Array.isArray(values)) throwTypeError("array", values);
        this.#having = string;
        this.#havingValues = values;
        return this;
    }

    /**
     * Adds a new sort order
     * - Can be used multiple times to order by multiple columns
     * @param {String} column - Column to order by 
     * @param {Boolean} desc - Sorty descending
     * @param {"MIN"|"MAX"|"COUNT"|"SUM"|"AVG"} aggregation - The aggregation type to use 
     * @returns {this}
     */
    order(column, desc=false, aggregation){
        if (!column) throw new Error("column must not be empty");
        if (typeof column !== "string") throwTypeError("string", column);
        if (typeof desc !== "boolean") throwTypeError("boolean", desc);
        const POSSIBLE_AGGREGATION = ["MIN", "MAX", "COUNT", "SUM", "AVG"];
        if (aggregation && POSSIBLE_AGGREGATION.includes(aggregation)){
            throwEnumError(POSSIBLE_AGGREGATION, aggregation);
        }
        if (POSSIBLE_AGGREGATION.includes(aggregation)){
            switch(aggregation){
                case "MIN":
                    column = `MIN(${column})`;
                    break;
                case "MAX":
                    column = `MAX(${column})`;
                    break;
                case "COUNT":
                    column = `COUNT(${column})`;
                    break;
                case "SUM":
                    column = `SUM(${column})`;
                    break;
                case "AVG":
                    column = `AVG(${column})`;
                    break;
            }
        }
        if (!desc){
            this.#order.asc.push(column);
        }else{
            this.#order.desc.push(column);
        }
        return this;
    }

    /**
     * Counts number of entries of the first selected column
     * @param {Boolean} doParse - Return only an integer, not the full query result
     * @returns {this}
     */
    count(doParse=false){
        if (typeof doParse !== "boolean") throwTypeError("boolean", doParse);
        this.#aggregator = "COUNT";
        this.#aggregatorParse = doParse;
        return this;
    }

    /**
     * Sums numerical rows of the first selected column
     * @param {Boolean} doParse - Return only an integer, not the full query result
     * @returns {this}
     */
    sum(doParse=false){
        if (typeof doParse !== "boolean") throwTypeError("boolean", doParse);
        this.#aggregator ="SUM";
        this.#aggregatorParse = doParse;
        return this;
    }

    /**
     * Averages numerical rows of the first selected column
     * @param {Boolean} doParse - Return only an integer, not the full query result
     * @returns {this}
     */
    avg(doParse=false){
        if (typeof doParse !== "boolean") throwTypeError("boolean", doParse);
        this.#aggregator = "AVG";
        this.#aggregatorParse = doParse;
        return this;
    }

    /**
     * Groups rows that have the same values into summary rows
     * @param  {...String} columns - The columns to group by
     * @returns {this}
     */
    group(...columns){
        if (columns.length===0) throw new Error("Arguments must not be empty");
        this.#group = columns;
        return this;
    }

    /**
     * Adds a new join to the querry
     * @param {"LEFT"|"INNER"|"RIGHT"|"FULL OUTER"} type - Join-type 
     * @param {String} table - Table to join on
     * @param {String} onOriginalColumn - Column name on the original table to check against
     * @param {String} onJoinedColumn - Column name of the join table to check against
     * @param  {...any} columns - The columns to join. OG-Columns must be set! 
     * @returns {this}
     */
    join(type, table, onOriginalColumn, onJoinedColumn, ...columns){
        const POSSIBLE_TYPES = ["LEFT", "INNER", "RIGHT", "FULL OUTER"];
        if (!POSSIBLE_TYPES.includes(type)) throwEnumError(POSSIBLE_TYPES, type);
        if (!table) throw new Error("table must not be empty");
        if (typeof table !== "string") throwTypeError("string", table);
        if (!onOriginalColumn) throw new Error("onOriginalColumn must not be empty");
        if (typeof onOriginalColumn !== "string") throwTypeError("string", onOriginalColumn);
        if (!onJoinedColumn) throw new Error("onOriginalColumn must not be empty");
        if (typeof onJoinedColumn !== "string") throwTypeError("string", onJoinedColumn);
        if (columns.length===0) throw new Error("columns must not be empty");
        this.#joins.push({
            type,
            on: `%%FROM%%.${onOriginalColumn}=${table}.${onJoinedColumn}`,
            columns,
            table
        })
        return this;
    }

    /**
     * Limits the query and specifies an offset
     * @param {Number} number - Limits the query by specified rows 
     * @param {Number} offset - Offset to start at
     * @returns {this}
     */
    limit(number, offset){
        if (!number) throw new Error("number must not be empty");
        if (typeof number !== "number") throwTypeError("number", number);
        if (offset===undefined) throw new Error("offset must not be empty");
        if (typeof offset !== "number") throwTypeError("number", offset);
        this.#limit = {
            number,
            offset
        }
        return this;
    }

    /**
     * Paginates the query
     * @param {Number} page - The page to get (Minimum 1)
     * @param {Number} itemsPerPage - How many items a page should have 
     * @returns {this}
     */
    pagination(page, itemsPerPage){
        if (!page) throw new Error("page must not be empty");
        if (typeof page !== "number") throwTypeError("number", page);
        if (!itemsPerPage) throw new Error("itemsPerPage must not be empty");
        if (typeof itemsPerPage !== "number") throwTypeError("number", itemsPerPage);
        if (page<1) page=1;
        this.#limit = {
            number: itemsPerPage,
            offset: itemsPerPage*(page-1)
        }
        return this;
    }

    /**
     * Executes the prepared querry
     * @returns {Any}
     */
    async execute(){
        if (!this.#instance.isConnected()) throw new Error(`Can't execute query: Instance has no connection`);
        if (!this.#database) throw new Error(`Can't execute query: Database not selected`);

        const values = [];
        let columnString;
        if (this.#joins.length>0 && this.#columns.length>0){
            columnString = `${this.#columns.toString()}`;
            for (let join of this.#joins){
                columnString+=`,${join.columns.toString()}`;
            }
        }else{
            columnString = this.#columns.length>0?this.#columns.toString():"*"
        }
        const distinctString = this.#distinct?"DISTINCT ":"";
        

        const whereString = this.#where?` WHERE ${this.#where}`:"";
        this.#where&&values.push(...this.#whereValues);
        const havingString = this.#having?` HAVING ${this.#having}`:"";
        this.#having&&values.push(...this.#havingValues);

        const orderString = (this.#order.asc.length>0||this.#order.desc.length>0)?` ORDER BY ${this.#order.asc.length>0?this.#order.asc.toString()+" ASC":""}${this.#order.desc.length>0?this.#order.desc.toString()+" DESC":""}`:"";

        const groupString = this.#group?` GROUP BY ${this.#group.toString()}`:"";

        let joinString = "";
        for (let join of this.#joins){
            joinString+=` ${join.type} JOIN ${this.#database}.${join.table} ON ${join.on.replace("%%FROM%%", this.#from)}`;
        }

        const limitString = this.#limit?` LIMIT ${this.#limit.number}${this.#limit.offset?` OFFSET ${this.#limit.offset}`:""}`:"";

        const queryString = `SELECT ${this.#aggregator?this.#aggregator+"(":""}${distinctString}${columnString}${this.#aggregator?")":""} FROM ${this.#database}.${this.#from}${joinString}${whereString}${havingString}${orderString}${limitString}${groupString};`;
        if (this.#aggregatorParse){
            const result = await this.#instance.queryRaw(queryString, values);
            return result[0][Object.keys(result[0])];
        }
        return await this.#instance.queryRaw(queryString, values);
    }
}

module.exports = Select;