<?php

namespace apexl\Vault\operators\drivers;
use apexl\Vault\interfaces\driver;
use apexl\Vault\operators\pdo;

class mysql extends pdo implements driver {

    /**
     * Function to build a create database statement.
     * @param $dbName
     * @param bool $forceLower
     * @return $this
     */
    public function createDatabase($dbName, $forceLower = TRUE){
        $this->sql = "CREATE DATABASE ". ($forceLower ? strtolower($dbName) : $dbName);
        return $this->execute();
    }

    public function createUser($user, $pass){
        $this->sql = "CREATE USER '".$user."'@'".$this->host."' IDENTIFIED BY '".$pass."'";
        return $this->execute();
    }

    public function assignUserToDB($user, $database, $grant = 'CREATE, DELETE, INSERT, SELECT, UPDATE'){
        // It turns out simply assuming the host provided is the host for the user causes... issues. So, we need to find out what the current user is, what the host is host is, and if that's the same as the one we're assigning to, before we try this.
        $this->sql = 'select current_user()';
        $result = $this->execute()->fetchAssoc();
        $currentUser = explode('@', array_shift($result));
        $DB_HOST = $this->host;
        if($currentUser[0] == $user){ //The same user? then we can assume we need to check the host value.
            $DB_HOST = $currentUser[1];
        }

        $this->sql = "GRANT ".$grant." ON ".$database.".* TO '".$user."'@'".$DB_HOST."'";
        $this->execute();
        //next flush privileges
        $this->sql = "FLUSH PRIVILEGES";
        $this->execute();
    }

    /**
     * Function to create a table with the provided columns.
     * @param $table
     * @param $colList
     * @param bool $existsCheck
     * @return $this
     */
    public function createTable($table, $colList, $existsCheck = TRUE, $engine = 'INNODB'){
        //create the table
        $this->sql = "CREATE TABLE ";
        $this->sql .= $existsCheck ? "IF NOT EXISTS " : "";
        $this->sql .= $table."(".$this->formatColumns($colList, "CREATE").") ";
        $this->sql .= "ENGINE=".$engine;
        $this->execute();

        //next, check if we were passed any indexes, loop over them and apply those too.s
        if(isset($colList->{'_indexes'})){
            foreach ($colList->{'_indexes'} as $index){
                $this->sql = 'create index '.$table.$index.' on '.$table.' ('.$index.')';
                $this->execute();
            }
        }
        //next, check if we were passed any unique indexes, loop over them and apply those too.s
        if(isset($colList->{'_unique'})){
            foreach ($colList->{'_unique'} as $index){
                $this->sql = 'create unique index '.$table.$index.'_unique on '.$table.' ('.$index.')';
                $this->execute();
            }
        }
        return $this;
    }

    /**
     * Initiate database select query
     * @param string $fields
     */
    public function select($table = ''){
        $this->setTable($table);
        $this->sql = "SELECT ";
        return $this;
    }

    public function describe($table){
        $this->setTable($table);
        $this->sql = "DESCRIBE ".$table;
        return $this;
    }

    public function setTable($table){
        if(!empty($table)){
            $this->table = $table;
        } else {
            throw new \Exception("Cannot set a mysql table to blank string");
        }
    }

    public function join($table, $field1, $field2, $condition = '='){
        $this->addJoin("JOIN", $table, $field1, $field2, $condition);
        return $this;
    }

    public function innerJoin($table, $field1, $field2, $condition = '='){
        $this->addJoin("INNER JOIN", $table, $field1, $field2, $condition);
        return $this;
    }

    public function leftJoin($table, $field1, $field2, $condition = '='){
        $this->addJoin("LEFT JOIN", $table, $field1, $field2, $condition);
        return $this;
    }

    protected function addJoin($join, $table, $field1, $field2, $condition){
        $this->sql .= " ".$join." ".$table." ON ".$field1." ".$condition." ".$field2;
        return $this;
    }

    public function update($table = ''){
        $this->setTable($table);
        $this->sql = "UPDATE ".$this->table;
        return $this;
    }

    public function insert($table = ''){
        $this->setTable($table);
        $this->sql = "INSERT INTO ".$this->table;
        return $this;
    }

    public function fields($fields = NULL){
        $this->queryFields = []; //empty the fields array.
        $fields = !is_null($fields)? (array)$fields : NULL;
        switch(TRUE){
            case strpos($this->sql, "INSERT INTO") !== FALSE:
                if ((isset($fields[0]))) {
                    return $this->setInsertValues(array_keys($fields[0]), array_values($fields), TRUE);
                } else {
                    return $this->setInsertValues(array_keys($fields), array_values($fields));
                }
                break;
            case strpos($this->sql, "UPDATE ") !== FALSE:
                return $this->setUpdateValues(array_keys($fields), array_values($fields), (isset($fields[0])));
                break;
            case strpos($this->sql, "SELECT ") !== FALSE:
                $this->setSelectValues($fields);
                break;
        }
        return $this;
    }

    public function delete($table = ''){
        $this->setTable($table);
        $this->queryFields = []; //empty the fields array.
        $this->sql = "DELETE FROM ".$this->table;
        return $this;
    }

    private function setUpdateValues($fields, $values){
        $this->sql .= " SET ";
        if(is_array($fields)){
            foreach($fields as $key => $field){
                $this->sql .= $field.' = '.$this->setQueryField($field, $values[$key]). ', ';
            }
            $this->sql = rtrim($this->sql, ', ');
        } else {
            $this->sql .= $fields.'='.$this->setQueryField($fields, $values);
        }

        return $this;
    }

    private function setInsertValues($fields, $values, $batch=false){
        $this->sql .= " (";
        $this->sql .= is_array($fields) ? implode(',', $fields) : $fields;
        $this->sql .= ")";
        $rows = [];
        $values = $batch ? $values : [$values];
        foreach ($values as $row) {
            if ($batch) $row = array_values($row);
            $rows[] = "(".implode(',', $this->getFieldIds($fields, $row)).")";
        }
        $this->sql .= ' VALUES ' .implode(",", $rows);
        if ($batch) {
            $clauses = [];
            foreach ($fields as $field) {
                $clauses[] = " $field = VALUES($field)";
            }
            $this->sql .= ' ON DUPLICATE KEY UPDATE'.implode(", ", $clauses);
        }
        return $this;
    }

    private function getFieldIds($fields, $values)
    {
        $fieldIds = [];
        foreach($fields as $key => $field){
            $fieldIds[] = $this->setQueryField($field, $values[$key]);
        }

        return $fieldIds;
    }

    private function setSelectValues($fields){
        if(!is_null($fields)) {
            $fields = is_array($fields) ? implode(', ', $fields) : $fields;
        } else {
            $fields = "*";
        }
        $this->sql .= $fields." FROM ".$this->table;
        return $this;
    }

    private function formatColumns($tableData, $operation){
        if(!isset($tableData->{'_columns'})){
            throw new \Exception("MYSQL Table definition must contain columns");
        }
        $noCols = count($tableData->{'_columns'});
        $columns = $tableData->{'_columns'};
        $formattedColumns = [];
        for($i=0; $i < $noCols; ++$i){
            if (!isset($columns[$i]->name)) {
                throw new \Exception("MYSQL Column name missing from column operation .$operation");
            }
            if (!isset($columns[$i]->type) && $operation == "CREATE") {
                throw new \Exception("MYSQL Column type missing from column ".$operation." operation");
            }
            $formattedColumns[$i] = $this->buildColumnDefinition($columns[$i], $tableData->primary_key);
        }
        return implode(', ', $formattedColumns);
    }

    private function buildColumnDefinition($column, $pkey = NULL){
        $definition = $column->name;
        //build based on type.
        switch(strtolower($column->type)){
            case 'varchar':
                $length = $column->length ?? 255;
                $definition .= ' varchar('.$length.')';
                break;
            case 'bool':
            case 'boolean':
                $definition .= ' tinyint(1)';
            break;
            case 'decimal':
                $length = $column->length ?? 11;
                $decimalPlaces = $column->decimal_places ?? 2;
                $definition .= " DECIMAL($length,$decimalPlaces)";
                break;
            case 'int':
                $length = $column->length ?? 11;
                $definition .= ' int('.$length.')';
                break;
            case 'json':
                $definition .= ' JSON';
                break;
            default:
                $definition .= ' '.strtolower($column->type);
                break;
        }
        if(isset($column->auto_increment) && $column->auto_increment){
            $definition .= ' auto_increment';
        } else {
            if(isset($column->default)){
                $definition .= ' default '.(is_string($column->default) ? "'".$column->default."'" : $column->default);
            }
            if(strtolower($column->type) != 'json'){
                if(isset($column->is_null) && $column->is_null){
                    $definition .= ' null';
                } else {
                    //assume not nulls
                    $definition .= ' not null';
                }
            }
        }
        if($pkey && $pkey == $column->name){
            $definition .= ' primary key';
        }
        //build row
        return $definition;
    }

    /**
     * Function to add a WHERE clause to the query
     * @param $field
     * @param $value
     * @param string $condition
     * @return $this
     */
    public function where($field, $value, $condition = '=', $type = 'AND', $postCondition = ''){
        if(strtoupper($condition) != 'IS NULL' && strtoupper($condition) != 'IS NOT NULL') {
            $fieldInc = $this->setQueryField($field, $value);
        }
        $this->sql .= strpos($this->sql, " WHERE") === FALSE ? " WHERE " : ' '.strtoupper($type).' ';
        if(in_array(strtoupper($condition), ['IN', 'NOT IN'])){
            $this->sql .= $field .' '. $condition. ' ('. $fieldInc . ')';
        } else if(strtoupper($condition) == 'IS NULL' || strtoupper($condition) == 'IS NOT NULL') {
            $this->sql .= $field . ' ' . $condition;
        } else {
            $this->sql .= $field . ' ' . $condition . ' ' . $fieldInc;
        }
        $this->sql .= $postCondition;
        return $this;
    }

    /**
     * Function to add a WHERE clause to the query
     * @param $field
     * @param $value
     * @param string $condition
     * @return $this
     */
    public function having($condition){
        $this->sql .= ' HAVING '.$condition;
        return $this;
    }

    public function orWhere($field, $value, $condition = '='){
        return $this->where($field, $value, $condition, 'OR');
    }

    public function whereNestedOr($field1, $value1, $field2, $value2){
        $arg = strpos($this->sql, " WHERE") === FALSE ? " WHERE " : " AND ";
        $this->sql .= $arg."(".$field1."=".$this->setQueryField($field1, $value1)
            ." OR ".$field2."=".$this->setQueryField($field2, $value2).")";
        return $this;
    }

    public function addExtra($value){
        $this->sql .=' '.$value;
        return $this;
    }

    /**
     * Add fields to the active query, account for multiple fields with the same name.
     * @param $field
     * @param $value
     * @return string
     */
    private function setQueryField($field, $value){
        if(is_array($value)){
            $fieldInc = [];
            foreach($value as $val){
                $fieldInc[] = $fieldRaw = $this->addQueryField($field);
                $this->queryFields[$fieldRaw] = $val;
            }
            $fieldInc = implode(',', $fieldInc);
        } else {
            $fieldInc = $this->addQueryField($field);
            $this->queryFields[$fieldInc] = $value;
        }
        return $fieldInc;
    }

    private function addQueryField($field){
        $num = 1;
        $field = str_replace(['.', ' ', ')', '('], ['', '_', '', ''], $field);
        if(isset($this->queryFields[':'.$field])){
            while(TRUE){
                if(!isset($this->queryFields[':'.$field.'_'.$num])){
                    return ':'.$field.'_'.$num;
                    break;
                }
                $num++;
            }
        }
        return ':'.$field;
    }

    /**
     * Function to add Order By to a query
     * @param $field
     * @param string $sort
     * @return $this
     */
    public function orderBy($field, $sort = 'ASC'){
        $this->sql .= strpos($this->sql, " ORDER BY") === FALSE ? " ORDER BY " : ' ,';
        $this->sql .= ' '.$field.' '.$sort;
        return $this;
    }

    public function groupBy($field){
        $this->sql .= ' GROUP BY '.$field;
        return $this;
    }

    /**
     * Function to add Limit to a query
     * @param $limit
     * @param $offset
     * @return $this
     */
    public function limit($limit, $offset = 0){
        $this->sql .= ' LIMIT '.$offset.','.$limit;
        return $this;
    }

    /**
     * Function to Set or update field values for the stored query
     * @param $fields
     * @param bool $empty
     * @return $this
     */
    public function setFieldValues($fields, $empty = FALSE){
        if($empty){
            $this->queryFields = [];
        }
        foreach($fields as $field => $value){
            $this->setQueryField($field, $value);
        }
        return $this;
    }

    public function __get($name)
    {
        $this->table = $name;
        return $this;
    }
}