<?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) {
				$dbLogin = isset($source['dbLogin']) ? $source['dbLogin'] : '';
				$dbPassword = isset($source['dbPassword']) ? $source['dbPassword'] : '';
				$dbLink = new wpdb($dbLogin, $dbPassword, $dbExternal, DB_HOST);
				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);
		if ($this->dbLink->last_error) {
			throw new RuntimeException($this->dbLink->last_error);
		}

		foreach ($result 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;
	}

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

	/**
	 * Returns all rows for database table.
	 */
	public function getRows($table, $fields, $editFields = array(), $uniqFields = array(), $nums = array())
	{
		if(empty($this->dbLink)) {
			$this->connectToExternalDB();
		}
		$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);

		$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) ? '' : $cell, 'y' => $index + 1);
						if(in_array($field, $editFields)) {
							$data['meta'] = array('editable');
						}
						$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();
		}
		$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;
	}
}
