<?php

/**
 * Class SupsysticTablesPro_Tables_Model_DBTables
 */
class SupsysticTablesPro_Tables_Model_DBTables extends SupsysticTables_Core_BaseModel
{
	private $dbLink;
	private $dbName;

	/**
	 * Returns all available databases.
	 */
	public function connectToExternalDB($source = array())
	{
		$dbLink = $this->db;
		$dbName = isset($source['dbName']) ? $source['dbName'] : DB_NAME;

		if($dbName == 'External DB') {
			$dbExternal = isset($source['dbExternal']) ? $source['dbExternal'] : '';
			if(!empty($dbExternal) && $dbExternal != DB_NAME) {
                $dbExternalHost = isset($source['dbExternalHost']) ? $source['dbExternalHost'] : DB_HOST;
				$dbLogin = isset($source['dbLogin']) ? $source['dbLogin'] : '';
				$dbPassword = isset($source['dbPassword']) ? $source['dbPassword'] : '';
				$dbLink = new wpdb($dbLogin, $dbPassword, $dbExternal, $dbExternalHost);
				if(!empty($dbLink->error)) {
					return $this->environment->translate('Error establishing a database connection');
				}
				$dbName = $dbExternal;
			}
		} elseif($dbName != DB_NAME) {
			$dbLink = new wpdb(DB_USER, DB_PASSWORD, $dbName, DB_HOST);
			if(!empty($dbLink->error)) {
				return $this->environment->translate('Error establishing a database connection');
			}
		}

		$this->dbLink = $dbLink;
		$this->dbName = $dbName;

		return true;
	}

	/**
	 * Returns all available databases.
	 */
	public function getDBNames()
	{
		$names = array();
		$result = $this->db->get_results('SHOW DATABASES', ARRAY_N);
		if($this->db->last_error) {
			//return $this->db->last_error;
			return $names;
		}

		$exclusions = array('information_schema', 'mysql', 'performance_schema', DB_NAME);
		foreach($result as $i => $name) {
			if(!in_array($name[0], $exclusions)) {
				$names[] = $name[0];
			}
		}
		return $names;
	}

	/**
	 * Returns all tables from WP database.
	 */
	public function getDBTables()
	{
		if(empty($this->dbLink)) {
			$this->connectToExternalDB();
		}
		$tables = array();
		$result = $this->dbLink->get_results('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE="BASE TABLE" AND TABLE_SCHEMA="'.$this->dbName.'"', ARRAY_N);
		$result2 = $this->dbLink->get_results('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE="VIEW" AND TABLE_SCHEMA="'.$this->dbName.'"', ARRAY_N);
		if ($this->dbLink->last_error) {
			throw new RuntimeException($this->dbLink->last_error);
		}

		foreach ($result as $i => $table) {
			$tables[] = $table[0];
		}
		foreach ($result2 as $i => $table) {
			$tables[] = $table[0];
		}
		return $tables;
	}

	/**
	 * Returns all fields for database table.
	 */
	public function getFields($table)
	{
		if(empty($this->dbLink)) {
			$this->connectToExternalDB();
		}

		$result = $this->dbLink->get_results('SHOW COLUMNS FROM '.$this->dbName.'.'.$table, ARRAY_N);
		if ($this->dbLink->last_error) {
            throw new RuntimeException($this->dbLink->last_error);
        }

		$fields = array();
		foreach ($result as $i => $field) {
			$fields[] = $field[0];
		}
		return $fields;
	}

    public function getFieldType($table, $field)
    {
        if(empty($this->dbLink)) {
            $this->connectToExternalDB();
        }

        $result = $this->dbLink->get_row('SHOW COLUMNS FROM '.$this->dbName.'.'.$table. ' LIKE \''. $field. '%\'', ARRAY_N);
        if ($this->dbLink->last_error) {
            throw new RuntimeException($this->dbLink->last_error);
        }

        return array('type' => $result[1], 'default' => $result[4]);
    }

	/**
	 * Returns all fields for database table.
	 */
	public function updateField($table, $field, $value, $uniqFields, $uniqData)
	{
		if(empty($this->dbLink)) {
			$this->connectToExternalDB();
		}

		$update = $this->getQueryBuilder()
			->update($table)
			->set(array($field => $value));

		foreach($uniqFields as $i => $field) {
			if($i == 0) {
				$update->where($field, '=', $uniqData[$i]);
			} else {
				$update->andWhere($field, '=', $uniqData[$i]);
			}
		}

		$updated = $this->dbLink->query($update->build());
		if($this->dbLink->last_error) {
			throw new RuntimeException($this->dbLink->last_error);
		}

		return $updated;
	}

	/**
	 * Returns table's indexes.
	 */
	public function getUniqueFields($table)
	{
		if(empty($this->dbLink)) {
			$this->connectToExternalDB();
		}
		$result = $this->dbLink->get_results('SHOW INDEX FROM '.$table, ARRAY_A);
		if($this->dbLink->last_error) {
			throw new RuntimeException($this->dbLink->last_error);
		}

		$uniques = array();
		foreach ($result as $i => $row) {
			if($row['Non_unique'] == 0) {
				$uniques[$row['Key_name']][] = $row['Column_name'];
			}
		}

		$fields = array();
		foreach($uniques as $name => $columns) {
			if($name == 'PRIMARY') {
				$fields = $columns;
				break;
			}
			$cnt = count($fields);
			if($cnt == 0 || $cnt > count($columns)) {
				$fields = $columns;
			}
		}
		return $fields;
	}

	/**
	 * Returns rows data.
	 */
	public function getRowsData(&$settings, $nums = array(), $attributes = array())
	{
		$source = $settings['source'];
		$table = $source['dbTable'];
		$fields = (isset($source['dbFields']) ? $source['dbFields'] : array());

		$editable = false;
		$doSave = false;
		$rows = array();
		if(!is_array($nums)) $nums = array();

		$connected = $this->connectToExternalDB($source);
		if($connected !== true) {
			return array(array('cells' => array(array('data' => $connected))));
		}

		if($table == 'SQL Query') {
			if(isset($source['dbSQL']) && !empty($source['dbSQL'])) {
				$sql = $source['dbSQL'];
				$variables = array();
				if(is_array($attributes)) {
					foreach ($attributes as $key => $value) {
						if(strpos($key, 'sql') === 0) {
							$sql = str_replace('{'.$key.'}', $value, $sql);
							$variables[$key] = $value;
						}
					}
				}
				if(strpos($sql, '{') === false) {
					$data = $this->getSQLRows($sql, isset($settings['elements']['head']) && $settings['elements']['head'] == 'on', $nums);
				} else {
					$data = 'Not all request variables are passed in shortcode.';
				}
				if(is_array($data)) {
					$rows = $data;
				} else {
					$rows = array(array('cells' => array(array('data' => $data))));
				}
				unset($settings['saveEditableFields']);
				$settings['sqlValues'] = json_encode($variables);
			}
		} else {
			if(isset($source['dbEditable']) && $source['dbEditable'] == 'on') {
				$editable = true;
				$editFields = (isset($source['dbEditFields']) ? $source['dbEditFields'] : array());
				if(isset($source['dbSaveFields']) && $source['dbSaveFields'] == 'on') {
					$doSave = true;
					$settings['saveEditableFields'] = 'on';
				} else {
					unset($settings['saveEditableFields']);
				}
			}

			$tFields = $this->getFields($table);

			if(count($fields) == 0) {
				$fields = $tFields;
			}
			if($editable && count($editFields) == 0) {
				$editFields = $tFields;
			}

			$dbFields = array();
			$dbEditFields = array();
			$f = 0;
			foreach($fields as $n => $field) {
				if(in_array($field, $tFields)) {
					$dbFields[$f] = $field;
					if($editable && in_array($field, $editFields)) {
						$dbEditFields[$f] = $field;
					}
					$f++;
				}
			}

			if(count($dbFields) > 0) {
				$uniqFields = ($doSave && count($dbEditFields) > 0 ? $this->getUniqueFields($table) : array());

				$data = $this->getRows($table, $dbFields, $dbEditFields, $uniqFields, $nums);
				$rows = $data['rows'];
				$uniq = $data['uniq'];
				if(count($uniq) > 0) {
					$settings['source']['dbFields'] = $dbFields;
					$settings['source']['dbUniqFields'] = $uniqFields;
					$settings['source']['dbUniqData'] = $uniq;
				} elseif($doSave) {
					$settings['source']['error'] = $this->environment->translate('This table does not contain a unique index. Editing is not possible.');
				}

				if(isset($settings['elements']['head']) && $settings['elements']['head'] == 'on') {
					$header = array();
					foreach($dbFields as $field) {
						$header[] = array('data' => $field, 'y' => 0);
					}
					array_unshift($rows, array('cells' => $header));
				}
			} else {
				$rows = array();
			}
		}

		if(count($rows) == 0) {
			$rows[] = array('cells' => array(array('data' => $this->environment->translate('No data available in table'))));
		}
		return $rows;
	}

	public function getRowsByPart(&$settings, $orderCol = false, $orderAsc = true, $start = 0, $length = 1000, $searchAll = false, $searchCols = array(), $searchParams = array())
    {
        $source = $settings['source'];
        $table = $source['dbTable'];
        $fields = (isset($source['dbFields']) ? $source['dbFields'] : array());
        $sort = $orderCol !== false;
        $sorted = array();
        $search = (sizeof($searchCols) > 0 || $searchAll !== false);
        $isWord = (isset($searchParams['strictMatching']) && ($searchParams['strictMatching'] == 'on'));
        $searchAll = '%'. $searchAll. '%';

        $doSave = false;
        $connected = $this->connectToExternalDB($source);
        if($connected !== true) {
            return array('data' => array(), 'recordsTotal' => 0, 'recordsFiltered' => 0);
        } else {
            $tbSettings = array(
                'limit' => $length,
                'order' => ($orderAsc ? 'asc' : 'desc')
            );
            $start && $tbSettings['offset'] = $start;

            $editable = false;

            if(isset($source['dbEditable']) && $source['dbEditable'] == 'on') {
                $editable = true;
                $editFields = (isset($source['dbEditFields']) ? $source['dbEditFields'] : array());
                if(isset($source['dbSaveFields']) && $source['dbSaveFields'] == 'on') {
                    $doSave = true;
                    $settings['saveEditableFields'] = 'on';
                } else {
                    unset($settings['saveEditableFields']);
                }
            }

            if ($table != 'SQL Query') {
                $tFields = $this->getFields($table);

                if (count($fields) == 0) {
                    $fields = $tFields;
                }
                if ($editable && count($editFields) == 0) {
                    $editFields = $tFields;
                }

                $dbFields = array();
                $dbEditFields = array();
                $f = 0;
                foreach ($fields as $n => $field) {
                    if (in_array($field, $tFields)) {
                        $dbFields[$f] = $field;
                        if ($orderCol !== false && $orderCol == $f) {
                            $tbSettings['order_by'][$f] = $field;
                        }
                        if ($editable && in_array($field, $editFields)) {
                            $dbEditFields[$f] = $field;
                        }
                        $f++;
                    }
                }
                $uniqFields = ($doSave && count($dbEditFields) > 0 ? $this->getUniqueFields($table) : array());

                $totalQuery = $this->getQueryBuilder()->select('count(*) as total')->from($table);
                if ($search) {
                    if ($searchCols) {
                        $seachCounter = 0;
                        foreach ($searchCols as $col => $val) {
                            $val = !is_array($val) ? '%' . $val . '%' : '%' . $val[0] . '%';
                            $tbSettings['andWhere'][$dbFields[$col]] = array($dbFields[$col], 'LIKE', $val);
                            if ($seachCounter) {
                                $totalQuery->andWhere($dbFields[$col], 'LIKE', $val);
                            } else {
                                $totalQuery->where($dbFields[$col], 'LIKE', $val);
                            }
                            $seachCounter++;
                        }
                    } elseif ($searchAll) {
                        $seachCounter = 0;
                        foreach ($dbFields as $num => $field) {
                            if (isset($tbSettings['andWhere'][$field])) {
                                continue;
                            }
                            $tbSettings['orWhere'][] = array($field, 'LIKE', $searchAll);
                            if ($seachCounter) {
                                $totalQuery->orWhere($field, 'LIKE', $searchAll);
                            } else {
                                $totalQuery->where($field, 'LIKE', $searchAll);
                            }
                            $seachCounter++;
                        }
                    }
                }

                $total = $this->dbLink->get_results($totalQuery->build(), ARRAY_A);
                if ($this->dbLink->last_error) {
                    throw new RuntimeException($this->dbLink->last_error);
                }
                $recordsTotal = $recordsFiltered = $total[0]['total'];

                $rows = $this->getRows($table, $dbFields, $dbEditFields, $uniqFields, array(), $tbSettings)['rows'];
            } else {
                $rows = $this->getSQLRows($source['dbSQL'], isset($settings['elements']['head']) && $settings['elements']['head'] == 'on', $nums);
                $recordsTotal = $recordsFiltered = count($rows);

                foreach ($rows as $k => $val) {
                    if ($k < $start || $k >= $start + $length) {
                        unset($rows[$k]);
                    }
                }
            }

            return array('data' => $rows, 'recordsTotal' => $recordsTotal, 'recordsFiltered' => $recordsFiltered);
        }
    }

	/**
	 * Returns all rows for database table.
	 */
	public function getRows($table, $fields, $editFields = array(), $uniqFields = array(), $nums = array(), $selectSettings = false)
	{
		if(empty($this->dbLink)) {
			$this->connectToExternalDB();
		}
        $trueIndex = 0;
		$uniqCount = count($uniqFields);

		$list = (is_array($fields) && sizeof($fields) > 0 ? '`'.implode('`,`', $fields).'`' : '*');
		if(count($editFields) > 0 && $uniqCount > 0) {
			foreach($uniqFields as $i => $field) {
				$list .= ',`'.$field.'` as '.$field.'_uniq';
				$uniqFields[$i] .= '_uniq';
			}
			$fieldsCount = count($fields);
		}
		$query = $this->getQueryBuilder()
			->select($list)
			->from($table);
		if ($selectSettings){
            $selectSettings['limit'] && $query->limit($selectSettings['limit']);
            $selectSettings['offset'] && $query->offset($selectSettings['offset']);
            $selectSettings['order_by'] && $query->orderBy($selectSettings['order_by']);
            $selectSettings['order'] && $query->order($selectSettings['order']);
            if ($selectSettings['offset']) {
                $trueIndex = (int)$selectSettings['offset'];
            }
            if (isset($selectSettings['andWhere'])) {
                $num = 0;
                foreach ($selectSettings['andWhere'] as $and) {
                    if ($num) {
                        $query->andWhere($and[0], $and[1], $and[2]);
                    } else {
                        $query->where($and[0], $and[1], $and[2]);
                    }
                    $num++;
                }
            }elseif (isset($selectSettings['orWhere'])) {
                foreach ($selectSettings['orWhere'] as $num => $or) {
                    if ($num) {
                        $query->orWhere($or[0], $or[1], $or[2]);
                    } else {
                        $query->where($or[0], $or[1], $or[2]);
                    }
                }
            }
        }

		$result = $this->dbLink->get_results($query->build(), ARRAY_A);

		if($this->dbLink->last_error) {
			throw new RuntimeException($this->dbLink->last_error);
		}

		$rows = array();
		$uniq = array();
		$all = (sizeof($nums) == 0);
		if(count($result) > 0) {
			foreach($result as $r => $row) {
				if($all) {
					$index = $r;
				} else {
					$index = array_search($r + 1, $nums);
					if($index === false) continue;
				}

				$cells = array();
				foreach($row as $field => $cell) {
					if(in_array($field, $uniqFields)) {
						$uniq[$index + 1][] = $cell;
					} else {
						$data = array('data' => is_null($cell) ? '' : preg_replace("/\[supsystic-tables(.*?)\]/", '', $cell), 'y' => $index + 1, 'dbIndex' => $trueIndex + $index + 1);
						if(in_array($field, $editFields)) {
							$data['meta'] = array('editable');
						}
                        $aFieldType = $this->getFieldType($table, $field);
                        if (strpos($aFieldType['type'], 'enum') !== false || strpos($aFieldType['type'], 'set') !== false) {
                            $data['meta'] = isset($data['meta']) && is_array($data['meta'])
                                ? array($data['meta'][0]. ' selectable')
                                : array('selectable');
                            $data['type'] = 'dropdown';
                            $source = str_replace(array('enum(', 'set(', ')', '\'', '\\'), '', $aFieldType['type']);
                            $data['source'] = explode(',', $source);
                        }

						$cells[] = $data;
					}
				}
				$rows[$index] = array('cells' => $cells);
			}
		}
		if(!$all) {
			ksort($rows);
			ksort($uniq);
		}
		return array('rows' => $rows, 'uniq' => $uniq);
	}

	/**
	 * Returns all rows for sql query.
	 */
	public function getSQLRows($sql, $head, $nums = array())
	{
		if(empty($this->dbLink)) {
			$this->connectToExternalDB();
		}
		if(stristr($sql, 'create view') != FALSE) {
			$sqlArr = explode(';', $sql);
			foreach ($sqlArr as $sqlOne) {
				if(stristr($sqlOne, 'create view') != FALSE) {
					/**
					 * Use this if need (DROP VIEW) before create;
					 */
					// if  ( ( preg_match('/create view (.*?) as/', $sqlOne, $viewName) === 1 ) || ( preg_match('/CREATE VIEW (.*?) AS/', $sqlOne, $viewName) === 1 ) ) {
					// 		$viewName = !empty($viewName[1]) ? $viewName[1] : '';
					// 		if (!empty($viewName)) {
					// 			$sqlDropView = 'DROP VIEW IF EXISTS '.$viewName.';';
					// 			$resultDropView = $this->dbLink->get_results($sqlDropView);
					// 		}
					// }
					$resultCreateView = $this->dbLink->get_results($sqlOne);
				} else {
					if (!empty($sqlOne)) {
						$result = $this->dbLink->get_results($sqlOne, ARRAY_A);
					}
				}
			}
		} else {
			$result = $this->dbLink->get_results($sql, ARRAY_A);
		}
		if($this->dbLink->last_error) {
			return $this->dbLink->last_error;
		}
		$rows = array();
		$all = (sizeof($nums) == 0);
		$addIndex = 1;
		if(count($result) > 0) {
			if($head && ($all || in_array(0, $nums))) {
				$row = $result[0];
				$cells = array();
				foreach($row as $field => $cell) {
					$cells[] = array('data' => $field, 'y' => 0);
				}
				$rows[] = array('cells' => $cells);
			}

			foreach($result as $r => $row) {
				$cells = array();
				if(!$all && !in_array($r + $addIndex, $nums)) continue;

				foreach($row as $field => $cell) {
					$cells[] = array('data' => is_null($cell) ? '' : $cell, 'y' => $r + 1);
				}
				$rows[] = array('cells' => $cells);
			}
		}
		return $rows;
	}
}
