<?php
/* $Id$ */
namespace Mammut\DB\Postgre;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\Cache\iCache;
use Mammut\Exception\ImplementationException;
use Mammut\Exception\IllegalStateException;
use Mammut\DB\DBException;
use Mammut\DB\SQLException;
use Mammut\DB\SQLFunction;
use Mammut\DB\DB;
use Mammut\DB\Model\ColumnInfo;
use Mammut\DB\Model\TableInfo;

/**
 * PostgreSQL table implementation
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\PostgreSQL
 */
class Table extends \Mammut\DB\Table {

	public function __construct(DB &$db, $name) {
		parent::__construct($db, $name);
	}

	/**
	 * fetches a (cached) table info object
	 *
	 * @param boolean $refresh
	 *        	to true to reread data from db
	 * @return TableInfo info object
	 */
	public function getInfo($refresh = false) {
		if(is_object($this->info) && !$refresh)
			return $this->info;
		$name = $this->getName();
		$info = new TableInfo($name);
		$charset = NULL;
		$collate = NULL;
		
		$sql = "SELECT ci.table_catalog db_name, 
						ci.table_schema schema_name,
						ci.table_name table_name,
						ci.ordinal_position-1 as column_pos,
						ci.column_name column_name,
						CASE WHEN pt.typtype='e' THEN 'enum' ELSE ci.data_type END AS  data_type,
						ci.character_maximum_length size,
						ci.column_default default_value,
						ci.is_nullable nullable,
						pd.description description,
						pt.typname type_name,
						pt.oid type_oid
					FROM information_schema.columns ci
					LEFT JOIN pg_class pc
					  ON pc.relname = ci.table_name
					LEFT JOIN pg_attribute pa
					  ON pa.attname = ci.column_name AND pa.attrelid = pc.oid 
					LEFT JOIN pg_description  pd
					  ON pd.objoid = pc.oid AND pd.objsubid = pa.attnum
					LEFT JOIN pg_type pt
					  ON data_type='USER-DEFINED' AND pt.typname=ci.udt_name
					WHERE ci.table_catalog=current_database() AND  ci.table_schema='" . $this->db->getSchema() . "' AND ci.table_name='{$name}' ORDER BY column_pos";
		
		$result = $this->db->query($sql);
		
		while($row = $result->fetchArray()) {
			$param = array();
			$name = $row['column_name'];
			$type = $row['data_type'];
			if (strtoupper($row['nullable']) == 'YES')
				$param[] = ColumnInfo::P_ALLOW_NULL;
			$size = -1;
			switch(strtolower($type)) {
				case 'boolean':
					$type = ColumnInfo::TYPE_BOOLEAN;
				break;
				case 'character':
					$type = ColumnInfo::TYPE_CHAR;
					$size = $row['size'];
				break;
				case 'character varying':
					$type = ColumnInfo::TYPE_VCHAR;
					$size = $row['size'];
				break;
				case 'smallint':
					if($row['description'] == 'tiny')
						$type = ColumnInfo::TYPE_TINY;
					else
						$type = ColumnInfo::TYPE_SHORT;
				break;
				case 'integer':
					$type = ColumnInfo::TYPE_INT;
				break;
				case 'bigint':
					$type = ColumnInfo::TYPE_LONG;
				break;
				case 'real':
				case 'float4':
					$type = ColumnInfo::TYPE_FLOAT;
				break;
				case 'float8':
					$type = ColumnInfo::TYPE_DOUBLE;
				break;
				case 'date':
					$type = ColumnInfo::TYPE_DATE;
				break;
				case 'timestamp without time zone':
					$type = ColumnInfo::TYPE_DATETIME;
				break;
				case 'bytea':
					$type = ColumnInfo::TYPE_BLOB;
				break;
				case 'enum':
					$type = ColumnInfo::TYPE_ENUM;
					$evalues = $this->db->getArrayListP("SELECT enumlabel FROM pg_catalog.pg_enum WHERE enumtypid=?", array(
							$row['type_oid']));
					$values = array();
					foreach($evalues as $e)
						array_push($values, $e['enumlabel']);
					$param[] = 'values(' . implode(',', $values) . ')';
					unset($evalues, $e, $values);
				break;
			}
			$cinfo = new ColumnInfo($name, $type, $size, $param, $charset, $collate);
			$info->addColumn($cinfo);
		}
		$this->info = $info;
		return $info;
	}

	public function setPKey(array $fields) {
		$d = $this->db->getDialect();
		array_walk($fields, function (&$item, $key) use ($d) {
			$item = $d->quoteIdent($item);
		});
		$this->db->query("ALTER TABLE ".$d->quoteIdent($this->name)." ADD PRIMARY KEY (".implode(',', $fields).")");
	}

	public function getPKeyFields() {
		$sql = "SELECT c.column_name, c.data_type FROM information_schema.table_constraints tc
		JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name)
		JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
		WHERE constraint_type = 'PRIMARY KEY' AND tc.table_catalog=? AND tc.table_schema='public' AND tc.table_name=?";
				
		$param = [
			$this->db->getDatabase(),
			$this->name
		];
		
		$data = $this->db->getObjectListP($sql, $param);
		$r = array();
		foreach($data as $i)
			$r[] = $i->column_name;
		$r = array_unique($r);
		return $r;
	}

	public function removePKey() {
		$d = $this->db->getDialect();
		
		$select = "SELECT constraint_name FROM  information_schema.table_constraints
		WHERE constraint_type = 'PRIMARY KEY' AND table_catalog=? AND table_schema='public' AND table_name=?";
		
		$data = $this->db->getObjectP($select, [$this->db->getDatabase(), $this->name]);
		$this->db->query("ALTER TABLE ".$d->quoteIdent($this->name)." DROP CONSTRAINT ".$d->quoteIdent($data->constraint_name));
	}

	public function isIndexDefined($name) {
		// NOTE: you may need to add $TABLENAME_ to the index name
		$indexes = $this->getIndexList();
		return in_array($name, $indexes);
	}
	
	public function getIndexFields($name) {
		// NOTE: you may need to add $TABLENAME_ to the index name
		$d = $this->db->getDialect();
		$sql = 'SELECT
		t.relname AS table_name,
		i.relname AS index_name,
		a.attname AS column_name
		FROM
		pg_class t,
		pg_class i,
		pg_index ix,
		pg_attribute a
		WHERE
		t.oid = ix.indrelid
		AND i.oid = ix.indexrelid
		AND a.attrelid = t.oid
		AND a.attnum = ANY(ix.indkey)
		AND t.relkind = \'r\'
		AND t.relname=?
		AND i.relname=?';
		
		$data = $this->db->getObjectListP($sql,[$this->getName(),$name]);
		if (!is_array($data))
			return null;
		$result = [];
		foreach ($data as $d)
			$result[] = $d->column_name;
		return $result;
	}
	
	public function removeIndex($name) {
		// NOTE: you may need to add $TABLENAME_ to the index name
		$d = $this->db->getDialect();
		$sql = 'DROP INDEX '.$d->quoteIdent($name);
		$this->db->query($sql);
	}
	
	public function addColumn(ColumnInfo $info, $after = NULL) {
		$sqls = PostGreUtil::createAddColumnSQLs($this->db, $this->getName(), $info);
		
		// var_dump($sqls);
		// die();

		foreach($sqls['pre'] as $sql) {
			$this->db->query($sql);
		}
		foreach($sqls['main'] as $sql) {
			$this->db->query($sql);
		}
		foreach($sqls['post'] as $sql) {
			$this->db->query($sql);
		}
		$this->info = NULL;
	}

	public function renameColumn($column, $newName) {
		if($column instanceof ColumnInfo)
			$column = $column->getName();
		$this->db->query("ALTER TABLE " . $this->db->escapeTableName($this->getName()) . " RENAME COLUMN " . $this->db->escapeColumnName($column) . ' TO ' . $this->db->escapeColumnName($newName));
	}

	public function alterColumn($column, ColumnInfo $newInfo) {
		$sqls = PostGreUtil::createAlterColumnSQLs($this->db, $this->getName(), $column, $newInfo);
		
		// var_dump($sqls);
		// die();
		
		foreach($sqls['pre'] as $sql) {
			$this->db->query($sql);
		}
		foreach($sqls['main'] as $sql) {
			$this->db->query($sql);
		}
		foreach($sqls['post'] as $sql) {
			$this->db->query($sql);
		}
		$this->info = NULL;
	}
	
	public function getIndexList() {
//		$sql = 'SELECT * FROM pg_indexes WHERE tablename=? AND indexdef LIKE \'CREATE INDEX%\'';
		$sql = 'SELECT DISTINCT i.relname as indexname FROM pg_class t,pg_class i, pg_index ix, pg_attribute a';
		$sql .= ' WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)';
		$sql .= ' AND t.relkind = \'r\' AND t.relname = ?';
		$stmt = $this->db->prepareStatement($sql);
		$stmt->execute([$this->getName()]);
		$idx = $stmt->fetchObjectList();
		$stmt->close();
		
		$r = array();
		foreach($idx as $i)
			$r[] = $i->indexname;
		$r = array_unique($r);
		return $r;
	}
	
	public function getFKeyList() {
		$d = $this->db->getDialect();
	
		$sql = 'SELECT tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,ccu.column_name AS foreign_column_name';
		$sql .= ' FROM information_schema.table_constraints AS tc';
		$sql .= ' JOIN information_schema.key_column_usage AS kcu	ON tc.constraint_name = kcu.constraint_name';
		$sql .= ' JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name';
		$sql .= ' WHERE constraint_type = \'FOREIGN KEY\' AND tc.table_name=?';
		
		$stmt = $this->db->prepareStatement($sql);
		$stmt->execute([$this->getName()]);
		$idx = $stmt->fetchObjectList();
		$stmt->close();
	
		$r = array();
		foreach($idx as $i)
			$r[] = $i->constraint_name;
		$r = array_unique($r);
		return $r;
	}
}