<?php
/**
 * Simple table access
 *
 * $Id$
 *
 * @author gERD Schaufelberger <gerd@php-tools.net>
 * @license PHP License
 * @package WB
 * @subpackage db
 */

WBClass::load('WBDatasource'
            , 'WBDatasource_SQLCommon'
            , 'WBDatasource_Callback'
            , 'WBDatasource_TableMicroCache'
            , 'WBEvent'
            , 'WBLog');

/**
 * Simple table access
 *
 * @version 0.7.6
 * @package WB
 * @subpackage db
 */
class WBDatasource_Table extends WBDatasource_SQLCommon
{
    /**
     * Prefix for triggered events
     * @var string
     */
    protected $eventPrefix  =   'table';

    /**
     * database connection
     * @var WBDatasource_SQL
     */
    protected $db;

    /**
     * microcache
     * @var WBDatasource_TableMicroCache
     */
    private $mc;

   /**
    * database name
    * @var string
    */
    protected $dbName = '';

   /**
    * micro cache
    * @var array
    */
    static protected $cache =   array();

    /**
     * list of ids to preload
     * @var array
     */
    static protected $preload =   array();

   /**
    * number of rows found by last SELECT
    * @var int
    */
    protected $lastFound = 0;

   /**
    * logger
    * @var WBLog
    */
    protected $log;

   /**
    * constructor
    *
    * The parameter will be loaded from config.xml, section "db", you may
    * overwrite the list of tables. This will cause to load the table list
    * from a different file.
    *
    * Parameter:
    * - "tablelist": config file which contains the list of known tables.
    *
    * This class utilizes @link WBDatasource_SQL this implies that you may
    * use all constructor parameter of this class, as well.
    *
    * @param array $parameter
    */
    public function __construct( $parameter = array() )
    {
        parent::__construct( $parameter );

        $this->log      = 	WBLog::start( __CLASS__ );
        $this->db       =   WBClass::create( 'WBDatasource_SQL', $parameter );
        $this->loadTables( $parameter );

        $this->dbName   =   $this->db->getId();
        $this->mc       =   WBClass::create('WBDatasource_TableMicroCache', array('name' => $this->dbName));
    }

    /**
     * Get database name
     *
     * Return database identifier name.
     *
     * @return string
     */
    public function getDbName()
    {
        return $this->dbName;
    }

   /**
    * Save row
    *
    * Save $data to table
    *
    * @param string $table
    * @param string $id or '__new'
    * @param array $data
    * @return string $id of save row
    */
    public function save($table, $id, $data, $clause = array())
    {
        $c  =   $this->getTableInfo($table);

        $query  =   array(
            'UPDATE',
            $c['table'],
            );

        $log    =   array(
                'action'    =>  'save',
                'table'     =>  $table,
                'type'      =>  'update',
                'id'        =>  null
            );

        $now    =   gmdate('Y-m-d H:i:s');

        if (empty($c['primary'])) {
            $c['primary']   =   array();
        }
        if (!is_array($c['primary'])) {
            $c['primary']   =   array($c['primary']);
        }

        $transaction    =   array();

        // INSERT record(s)
        if ($id == '__new') {

/*
START TRANSACTION;
SELECT @n:=IF(MAX(scopeid) > @keymin, MAX(scopeid), @keymin) FROM tmp WHERE mandatorid=1;
INSERT INTO tmp (mandatorid, scopeid, title) VALUES (1, @n + 1,"foo"), (1,@n + 2,"bar");
COMMIT;
SELECT mandatorid,scopeid,title FROM tmp;
*/
            $query[0]       =   'INSERT INTO';

            // force numeric array
            if (!isset($data[0])) {
                $data   =   array($data);
            }

            $pLast  =   '';
            if (1 < count($c['primary'])) {
                $pLast  =   end($c['primary']);
                $pWhere =   array();
                foreach ($c['primary'] as $p) {
                    if ($p == $pLast) {
                        continue;
                    }

                    // make shure primary fields are complete
                    foreach ($data as &$x) {
                        if (empty($x[$p])) {
                            $x[$p]  =   0;
                        }
                    }
                    $pWhere[]   =   $c['table'] . '.' . $p . '=' . $this->db->quote($data[0][$p]);
                }

                $min            =   WBDatasource_SQLCommon::KEYMIN;
                $transaction[]  =   'START TRANSACTION';
                $transaction[]  =   sprintf('SELECT @n:=IF(MAX(%1$s) > %2$d, MAX(%1$s), %2$d) AS n FROM %3$s WHERE %4$s'
                                        , $c['table'] . '.' . $pLast
                                        , $min, $c['table']
                                        , implode(' AND ', $pWhere)
                                    );
            }

            $keys   =   array_keys($data[0]);
            if (!empty($pLast)) {
                $keys[] =   $pLast;
            }

            if (isset($data[0]['email']) && isset($c['email']) && 'split' == $c['email']) {
                $eKey   =   array_search('email', $keys);
                unset($keys[$eKey]);
                $keys   =   array_values($keys);
                $keys[] =   'emaillocal';
                $keys[] =   'emaildomain';
            }
            if (!empty($c['created']) && !isset($data[0][$c['created']])) {
                $keys[] =   $c['created'];
            }
            if (!empty($c['changed']) && !isset($data[0][$c['changed']])) {
                $keys[] =   $c['changed'];
            }
            $query[]    =   '(' . implode(', ', $keys) . ')';

            $values =   array();
            foreach ($data as $i => $d) {

                // split e-mail column?
                if (isset($d['email']) && isset($c['email']) && 'split' == $c['email']) {
                    $email  =   explode('@', $d['email']);
                    $d['emaillocal']    =   $email[0];
                    if (empty($email[1])) {
                        $email[1]   =   '';
                    }
                    $d['emaildomain']   =   $email[1];
                    unset($d['email']);
                }

                $this->trigger('beforesave', $table, '__new', array(), $d);
                if (!empty($c['created'])) {
                    $d[$c['created']]   =   $now;
                }
                if (!empty($c['changed'])) {
                    $d[$c['changed']]   =   $now;
                }

                // make sure all coulumns are set properly
                $v        =   array();
                foreach ($keys as $k) {
                    if ($pLast == $k) {
                        $v[]    =   '@n + ' . ($i + 1);
                        continue;
                    }
                    if (!isset($d[$k])){
                        $d[$k]  =   '';
                    }

                    $v[]    =   $this->db->quote($d[$k]);
                }
                $values[]   =   '(' . implode(', ', $v) . ')';
            }

            $query[]    =   'VALUES';
            $query[]    =   implode(', ', $values);

            // execute query
            $q      =   implode(' ', $query);
            if (empty($transaction)) {
                $res    =   $this->db->query($q);
                $dbc    =   $this->db->connect();
                $id     =   $dbc->insert_id;
            }
            else {
                foreach ($transaction as $t) {
                    $res=   $this->db->query($t);
                }
                $res    =   $this->db->query($q);
                $res    =   $this->db->query('COMMIT');

                // finally calculate insert id
                $res    =   $this->db->query('SELECT @n');
                $n      =   $res->fetch_array(MYSQLI_NUM);
                $n      =   $n[0];
                $id =   array();
                foreach ($c['primary'] as $p) {
                    if ($pLast == $p) {
                        $id[]   =   $n + count($data);
                        continue;
                    }
                    $id[]   =   $data[0][$p];
                }
                $id =   implode('-', $id);
            }

            $log['type']    =   'insert';
            $log['id']      =   $id;
            $this->log->notice($log);
            if (1 < count($data)) {
                return $id;
            }
            $this->trigger('save', $table, $id, array(), $data[0]);
            return $id;
        }

        if (!is_array($clause)) {
            $clause =   array();
        }


        // UPDATE record(s)
        if (empty($id) && empty($clause)) {
            WBClass::load('WBException_Argument');
            throw new WBException_Argument('Neither id nore clause given.', 6, __CLASS__);
        }

        $this->trigger('beforesave', $table, $id, $clause, $data);

        // id given
        if (!empty($id)) {
            $this->mc->setTable($table)->flush($id);
            $this->injectId2Clause($id, $c, $clause);
        }

        // add changed timestamp
        if (!empty($c['changed'])) {
            $data[$c['changed']]    =   $now;
        }

        // build set
        $set        =   array();
        if (isset($data['email']) && isset($c['email']) && 'split' == $c['email']) {
            list($data['emaillocal'], $data['emaildomain'])    =   explode('@', $data['email']);
            unset($data['email']);
        }

        foreach ($data as $k => $v) {
            $set[]  =   sprintf('%s.%s=%s', $c['table'], $k, $this->db->quote($v));
        }
        $query[]    =   'SET';
        $query[]    =   implode(', ', $set);

        // build where
        $where      =   array();
        $this->clause2Where($table, $clause, $where);
        $query[]    =   'WHERE';
        $query[]    =   implode(' AND ', $where);

        $q          =   implode(' ', $query);
        $res        =   $this->db->query($q);

        // return new id, if id was saved as well!
        $tmp    =   array();
        foreach ($c['primary'] as $p) {
            if (!isset($data[$p])) {
                continue;
            }
            $tmp[]  =   $data[$p];
        }
        if (count($tmp) == count($c['primary'])) {
            $id =   implode('-', $tmp);
        }

        $log['id']  =   $id;
        $this->log->notice($log);
        $this->trigger('save', $table, $id, $clause, $data);
        return $id;
    }

   /**
    * Fetch last found rows
    *
    * @return int
    */
    public function getLastCount()
    {
        return $this->lastFound;
    }

   /**
    * fetch list of ids from table
    *
    * @param string $table
    * @param string|null $foreign
    * @param array $clause
    * @param array $option
    * @return array list of ids
    */
    public function getIds($table, $foreign = null, $clause = array(), $option = array())
    {
        $c  =   $this->getTableInfo( $table );
        if (!isset($c['primary']) || empty($c['primary'])) {
            WBClass::load('WBException_Config');
            throw new WBException_Config( 'The table "' . $table.'" does not have a primary key! Maybe the config is corrupt.', 2, __CLASS__ );
        }

        if (!is_array($option)) {
            $option = array();
        }
        $ids    =   array();
        $list   =   $this->get($table, null, $foreign, $clause, $option);
        foreach ($list as $l) {
            $ids[]  =   $l['id'];
        }
        return $ids;
    }

   /**
    * fetch single column from table
    *
    * Get list of column values instead of associative array.
    * This method actually fetches ALL columns from the database
    * to fill preload caches.
    *
    * @param string $table
    * @param string|null $foreign
    * @param array $clause
    * @param array $options
    * @return array list of ids
    */
    public function getColumn($table, $foreign = null, $clause = array(), $options = array())
    {
        if( !is_array( $options ) ) {
            $options = array();
        }

        if (!isset($options['column'])) {
            WBClass::load( 'WBException_Argument' );
            throw new WBException_Argument( 'The option "column" is required but not given.', 2, __CLASS__ );
        }
        $col    =   $options['column'];
        unset($options['column']);
        if (is_array($col)) {
            $col    =   $col[0];
        }

        $list   =   array();
        $all    =   $this->get($table, null, $foreign, $clause, $options );
        foreach ($all as $a) {
            $list[] =   $a[$col];
        }
        return $list;
    }

   /**
    * fetch pager
    *
    * Use the same parameter as for @link get(), but skip the parameter "id"!
    *
    * @param string $pagerId
    * @param string $table
    * @param string $foreign
    * @param array $clause
    * @param array $options
    * @return WBDatasource_Pager
    */
    public function getPager( $pagerId, $table, $foreign = null, $clause = array(), $options = array() )
    {
        if( isset( $options['limit'] ) ) {
            $limit  =   $options['limit'];
        } else {
            $c  =   $this->getTableInfo($table);
            if( !isset( $c['limit'] ) ) {
                WBClass::load( 'WBException_Config' );
                throw new WBException_Config( 'The pager only works in case a limit is set. Set limit in config or set via options.', 5, __CLASS__ );
            }
            $limit  =  $c['limit'];
        }

        $parameter  =   array(
                                'id'        =>  $pagerId,
                                'source'    =>  $this,
                                'limit'     =>  $limit,
                                'options'   =>  4
                            );

        $pager  =   WBClass::create( 'WBDatasource_Pager', $parameter );
        $pager->setArgs( $table, null, $foreign, $clause, $options );
        return $pager;
    }

   /**
    * Count rows in table
    *
    * Use SELECT count() to bla bla bla
    *
    * @param string $table
    * @param string $id
    * @param string|null $foreign if foreign key
    * @param array $clause additional clause
    * @param array $options parameter
    * @return int $count
    */
    public function count($table, $id = null, $foreign = null, $clause = array(), $options = array())
    {
        $query      =   array( 'SELECT' );
        $c          =   $this->getTableInfo($table);
        $count      =   $this->getDistinct($table, $options);
        if (empty($count)) {
            $count  =   '*';
        }
        $query[]    =   sprintf('count(%s)', $count);
        $query[]    =   'FROM ' . $c['table'];

        $this->addJoin($table, $options, $query);

        // add id
        if($id) {
            $clause[]   =   array('field' => $c['primary'], 'value' => $id);
        }

        $this->_addDeletedFlag2Clause($table, $clause, $options);
        $this->_addForeignKey2Clause($table, $foreign, $clause);

        $where  =   array();
        $this->clause2Where($table, $clause, $where);
        if (!empty($where)) {
            $query[]    =   'WHERE ' . implode(' AND ', $where);
        }

        $res    =   $this->db->query(implode(' ', $query));
        $row    =   $res->fetch_row();
        $count  =   $row[0];
        $res->free_result();

        $log    =   array(
            'action'    =>  'count',
            'table'     =>  $table,
            'foreign'   =>  $foreign,
            'count'     =>  $count
        );
        $this->log->debug($log);
        return $count;
    }

   /**
    * fetch rows
    *
    * Options may contain:
    *  - keepfound
    *  - limit
    *  - order
    *  - column
    *  - callback
    *  - flush
    *  - withIdAlias add alias for primary key
    *
    * @param string $table
    * @param string|null $id key
    * @param string|null $foreign if foreign key
    * @param array $clause additional clause
    * @param array $options general options
    * @return array list
    */
    public function get($table, $id = null, $foreign = null, $clause = array(), $options = array())
    {
        $c  =   $this->getTableInfo($table);
        $this->normalizePrimary($c);

        // primary key
        $p  =   null;
        if (!empty($c['primary'])) {
            $p  =   $c['primary'];
        }

        // fetch preload entries
        $pIds   =   $this->mc->setTable($table)->getPreload();
        if ($id && 1 == count($p) && !empty($pIds)) {
            $pClause    =   array();
            $pClause[]  =   array(
                            'field'     =>  $p[0],
                            'value'     =>  $pIds,
                            'relation'  =>  'in'
                            );
            $this->get($table, null, null, $pClause);
        }

        if (!isset($options['flush'])) {
            $options['flush']   =   false;
        }
        if (!isset($options['withIdAlias'])) {
            $options['withIdAlias']  =   true;
        }
        if ($options['withIdAlias'] && empty($p)) {
            $options['withIdAlias']  =   false;
        }

        // verify callback
        if (isset($options['callback'])) {
            if (!($options['callback'] instanceof WBDatasource_Callback)) {
                WBClass::load('WBException_Argument');
                throw new WBException_Argument('The callback object must be an instance of "WBDatasource_Callback"', 3, __CLASS__);
            }
        } else {
            $options['callback']    =   null;
        }

        $cache  =   'miss';
        $cached =   $this->mc->setTable($table)->get($id);
        if ($id && !empty($cached)) {

            // load from cache
            $this->translate($table, $cached);
            $list   =   array($cached);
            $cache  =   'hit';
        } else {

            // fetch from database
            $res    =   $this->_queryGet($table, $id, $foreign, $clause, $options);
            $list   =   array();
            while ($l = $res->fetch_assoc()) {
                $idStr  =   $this->getIdString($c, $l);

                // merge e-mail column?
                if (isset($c['email']) && 'split' == $c['email'] && isset($l['emaillocal'])) {
                    $l['email'] =   $l['emaillocal'] . '@' . $l['emaildomain'];
                    if (empty($l['emaillocal'])) {
                        $l['email'] =   '';
                    }
                }

                // destroy after walkthrough
                if ($options['flush']) {
                    if ($options['withIdAlias']) {
                        $l['id']    =   $idStr;
                    }

                    if ($options['callback']) {
                        $k  =   null;
                        if (!empty($p)) {
                            $k  =   $idStr;
                        }
                        $options['callback']->onDatasourceGet($table, $k, $l);
                    }

                    continue;
                }

                // cache only complete records
                if (!isset($options['column'])) {

                    // cache by primary key
                    if (!empty($idStr)) {
                        $this->mc->setTable($table)->add($idStr, $l);
                    }

                    // remember foreign ids for preload
                    foreach ($c['foreign'] as $fTable) {

                        // if not cached, add to preload list
                        $f  =   $this->getTableInfo($fTable);
                        $this->normalizePrimary($f);
                        if (empty($l[$f['primary'][0]])) {
                            continue;
                        }
                        $this->mc->setTable($fTable)->addPreload($l[$f['primary'][0]]);
                    }
                }

                $this->translate($table, $l);
                $list[] =   $l;
            }
            $res->free_result();
        }

        // second run through list
        foreach ($list as $i => &$l) {
            if ($options['withIdAlias']) {
                $l['id']    =   $this->getIdString($c, $l);
            }

            if ($options['callback']) {
                $k  =   null;
                if (!empty($p)) {
                    $k  =   $this->getIdString($c, $l);
                }
                $options['callback']->onDatasourceGet($table, $k, $l);
            }

            // flush result
            if ($options['flush']) {
                unset($list[$i]);
            }
        }

        $log    =   array(
                'action'    =>  'get',
                'table'     =>  $table,
                'id'        =>  strval($id),
                'foreign'   =>  $foreign,
                'count'     =>  count($list),
                'cache'     =>  $cache
            );
        $this->log->debug($log);
        return $list;
    }

   /**
    * Internal interface to execute query
    *
    * @param string $table
    * @param string|null $id key
    * @param string|null $foreign if foreign key
    * @param array $clause additional clause
    * @param array $options general options
    * @return array list
    */
    protected function _queryGet( $table, $id = null, $foreign = null, $clause = array(), $options = array() )
    {
        $query  =   array('SELECT');

        $this->lastFound   =   null;
        if( isset( $options['keepfound'] ) && $options['keepfound'] ) {
            $query[]    =   'SQL_CALC_FOUND_ROWS';
        }

        $c  =   $this->getTableInfo($table);
        $this->normalizePrimary($c);

        $this->addColumn($table, $options, $query);
        $query[]    =   'FROM ' . $c['table'];

        $this->addJoin($table, $options, $query);

        // prepare clause
        if (!is_array($clause)) {
            $clause =   array();
        }

        // add id
        if ($id) {
            if (1 == count($c['primary'])) {
                $clause[]   =   array(
                                        'field' => $c['primary'][0],
                                        'value' => $id
                                    );
            }
            else {
                $tmp    =   explode('-', $id);
                for ($i = count($c['primary']) - 1; $i >= 0; --$i) {
                    $p  =   0;
                    if (!empty($tmp)) {
                        $p  =   array_pop($tmp);

                    }
                    $clause[]   =   array(
                                        'field' =>  $c['primary'][$i],
                                        'value' =>  $p
                                    );
                }
            }
        }

        $this->_addDeletedFlag2Clause($table, $clause, $options);
        $this->_addForeignKey2Clause( $table, $foreign, $clause );

        $where  =   array();
        $this->clause2Where($table, $clause, $where);
        if( !empty( $where ) ) {
            $query[]    =   'WHERE ' . implode( ' AND ', $where );
        }

        $this->addGroupBy( $table, $options, $query );
        $this->_addOrderBy( $table, $options, $query );
        $this->_addLimit( $table, $options, $query );

        $q  =   implode( ' ', $query );
        $res =  $this->db->query( $q );

        if( isset( $options['keepfound'] ) && $options['keepfound'] ) {
            $foundRes           =   $this->db->query( 'SELECT FOUND_ROWS()' );
            $lastFound          =   $foundRes->fetch_array( MYSQLI_NUM );
            $this->lastFound    =   $lastFound[0];
            $foundRes->free_result();
        }
        return $res;

    }

   /**
    * Delete entry from table
    *
    * SQL delete
    *
    * @param string $table name of table
    * @param string|null $id
    * @param string|id $foreign id to verify delete
    * @param array $clause
    * @return int deleted rows
    */
    public function delete($table, $id, $foreign = null, $clause = array())
    {
        $c  =   $this->getTableInfo($table);
        $this->normalizePrimary($c);

        // prepare clause
        if (!is_array($clause)) {
            $clause =   array();
        }
        // add id
        if ($id) {
        	// clear cache
            $this->mc->setTable($table)->flush($id);

            // add id to clause
            $this->injectId2Clause($id, $c, $clause);
        }
        $this->_addForeignKey2Clause($table, $foreign, $clause);

        $q      =   array();
        $q[]    =   'DELETE FROM';
        $q[]    =   $c['table'];
        if (isset($c['delete']) && !empty($c['delete'])) {
            $q[0]  =   'UPDATE';
            $q[]   =   'SET';
            $q[]   =   $c['delete'] . '=1';
            if (!empty($c['changed'])) {
                $q[]   =   ', ' . $c['changed'] . '="' . gmdate('Y-m-d H:i:s') . '"';
            }
        }

        $where	=	array();
        $this->clause2Where($table, $clause, $where);
        $q[]    =   'WHERE';
        $q[]    =   implode(' AND ', $where);

        $res    =  $this->db->query(implode(' ', $q));

        $eData  =   array(
                        'table'     =>  $table,
                        'id'        =>  $id,
                        'foreign'   =>  $foreign,
                        'clause'    =>  $clause
                );
        WBEvent::trigger('table:delete:' . $table, 'Deleted record from table', $eData);
        return 1;
    }

   /**
    * Undelete entry from table
    *
    * Remove deleted-flag from record. This funtionality has a view requirements:
    * - "id" column is neccessary
    * - deleted-flag, so rows are marked as deleted, not just removed
    *
    * @throws WBException_Argument
    * @throws WBException_Config
    * @param string $table name of table
    * @param string|null $id
    * @return
    */
    public function undelete($table, $id)
    {
        if (empty($id)) {
            $ex =   array(
                        'msg'   =>  'ID required for undelete',
                        'code'  =>  7,
                        'class' =>  __CLASS__
                    );
            throw WBClass::create('WBException_Argument', $ex);
        }

        $c      =   $this->getTableInfo($table);
        $this->normalizePrimary($c);

        if (empty($c['primary'])) {
            $ex =   array(
                        'msg'   =>  'Primary key required for undelete',
                        'code'  =>  8,
                        'class' =>  __CLASS__
                    );
            throw WBClass::create('WBException_Config', $ex);
        }

        if (empty($c['delete'])) {
            $ex =   array(
                        'msg'   =>  'Delete-flag required for undelete',
                        'code'  =>  9,
                        'class' =>  __CLASS__
                    );
            throw WBClass::create('WBException_Config', $ex);
        }

        $this->mc->setTable($table)->flush($id);

        // add id to clause
        $this->injectId2Clause($id, $c, $clause);

        $q      =   array();
        $q[]  =   'UPDATE';
        $q[]   =   $c['table'];
        $q[]   =   'SET';
        $q[]   =   $c['delete'] . '=0';

        $where  =   array();
        $this->clause2Where($table, $clause, $where);
        $q[]    =   'WHERE';
        $q[]    =   implode(' AND ', $where);

        $res    =  $this->db->query(implode(' ', $q));

        $eData  =   array(
                        'table'     =>  $table,
                        'id'        =>  $id,
                );
        WBEvent::trigger('table:undelete:' . $table, 'Undeleted record of table', $eData);
        return true;

    }

    /**
     * Wipe out cached data
     *
     * Remove all cached entries and start from scratch
     */
    static public function flushCache()
    {
        $mc =   WBClass::create('WBDatasource_TableMicroCache');
        $mc->flushAll();
    }
}