<?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'){
        $this->sql = "GRANT ".$grant." ON ".$database.".* TO '".$user."'@'".$this->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 '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(strtoupper($condition) == '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;
    }

    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;
    }
}